Install Room Booking System on Windows and SQL Server


The Oxalto Room Booking System is a free open source room booking solution developed using ColdFusion and  CFWheels MVC framework on the backend, and FullCalendar.js, jQuery and Bootstrap on the front-end. For more information about this open source system (including submitting issues), visit: https://roombooking.readme.io/.

There is a set of instruction for installing it on an environment running ColdFusion Application Server, Railo or Lucee with MySQL as the database server. However in this guide, we will install it in the following environment:

  • Windows Server 2008 R2
  • Adobe ColdFusion 10 Enterprise
  • MS SQL Server 2008 R2

We will need to convert the sql script from MySQL to T-SQL (MS SQL Server) before we can run it to create the required tables for the system.

Here are the steps for installing the Oxalto Room Booking System:

  • Getting the source files
  • Setting up the database
  • Create the initial administrator account
  • Other tweaks
  • Final confirmation
  • Post installation errors resolution

Getting the source files

  • Download the 1.2 release
  • Unzip into the web root of the application server

Setting up the database

  • Create a blank database in MS SQL Server called roomBooking
  • Assign db_dataread, and db_datawrite database roles to the user that will access this database via ColdFusion Admin
  • Create a new datasource call roomBooking in ColdFusion Admin
  • Convert the new-installation.sql from MySQL to T-SQL (MS SQL Server). Launch the file in MS SQL Server Management Studio, and make the following changes:
    • Remove this line: SET FOREIGN_KEY_CHECKS=0;
    • Change all drop table if exists MySQL to T-SQL equivalence:
      IF OBJECT_ID(N'[table-name]’, N’U’) IS NOT NULL
      DROP TABLE [table-name]
      GO
    • Replace ` with square brackets for table names and field names, i.e. [table-name], [fieldname]
    • Change AUTO INCREMENT to IDENTITY(1,1)
    • Remove COLLATE statement from table column definitions
    • Surround column type with square brackets, i.e. [int],[nvarchar]
    • Replace this: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; with ON [PRIMARY]
    • Define primary key using one of these formats:
      CONSTRAINT PK_<table-name>_id PRIMARY KEY ([id]) or
      CONSTRAINT PK_<field-name> PRIMARY KEY ([field-name]) or
      CONSTRAINT PK_<primary-key-1>_<primary-key-2> PRIMARY KEY ([primary-key-1],[primary-key-2])
    • Define column default values using this format:
      CONSTRAINT df_<column-name> DEFAULT ‘<default-value>
      if definition is DEFAULT NULL, just remove the word DEFAULT, leaving only NULL, i.e.
      [description] [nvarchar] (255)  NULL
    • For the events table, change the column end to endDate
    • For the permissions table, change the column user to users (or some other non-reserved word)
    • Change table column types from MySQL to their T-SQL (SQL Server) equivalences:
      int(11) to [int]
      varchar to [nvarchar]
      longtext to [ntext]
      smallint to [smallint]
      tinyint to [tinyint]
      datetime to [datetime]
  • Run the converted new-installation.sql script in SQL Server Management Studio to create the tables

Create the initial administrator account

  • Browse to this page to do a few quick checks and create an initial administrator account:
    http://your.website.com/roomBooking/install/
  • To confirm, check:
    • the admin user you specified was created in the users table
    • a new file auth.cfm is created under /config folder
  • The install folder should be deleted or renamed after the initial administrator account has been successfully created.

Other tweaks

  • Open /config/settings.cfm and change set(reloadPassword=”roombooking”) to something unique
  • Open /config/production/settings.cfm and set an error handling email address.

Final confirmation

Post installation errors resolution

1. Replace all instances of end with endDate

This is needed as end may be a reserved MS SQL Server word, and is causing the event not to load. Change it in the following locations:

  • /controllers/eventdata.cfc
    • getevents() function – lines 46,47,51,52,56,57
    • prepeventdata() function – line 87
  • /controllers/Booking.cfc
    • day() function – lines 99,104,110,111,115,126
    • create() function – lines 251,252,258,259
    • _dayListWC() function – lines 352,357
    • _agendaListWC() function – line 383 (change from start to endDate)
    • check() function – lines 431,433
  • /controllers/Api.cfc
    • display() function – lines 36,39
    • ical() function – lines 99,100
  • /views/eventdata/_details.cfm – line 61

2. users.email is invalid

Open /controllers/Users.cfc. On line 83, remove this: group=”id”,

Reload the application and ensure that the errors are now resolved. To reload the application, append the following to the main page, i.e. :

?reload=production&password=YOURRELOADPASSWORD

3. Remove MySQL escape characters for table fields

Open controllers/Controller.cfc and remove the ` characters from any of the table columns in queries. Save the file and reload the application, then click on Settings > Users to confirm the error is fixed.

Congratulations! You have now fixed all the errors and the Room Booking System is now successfully installed.

This is the screen you see when you’re not logged in.

roomBooking-8

This is the screen you see once you’re logged in.

roomBooking-9

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s