Geeklog 1.4.1 Microsoft SQL Server Support

The objective of the Microsoft SQL (MSSQL) port is to provide another database alternative to MYSQL to take advantage of SQL server in business environments where Mysql may be a barrier to entry. The goal was to provide the ability for GL1.4.0 to work in either Mysql or MS SQL server with zero code changes required to be done by the end user. The version(s) of SQL supported are SQL 2000 and above, including the MSDE.

An example plugin that was created to show an approach to isolate the database layer from the application logic can be found here in the downloads library.

Since Mysql has a few proprietary functions, it's impossible to have a MS SQL server equivalent function. A few examples are LIMIT, REPLACE INTO, UNIX_TIMESTAMP, TO_DAYS, DATE_FORMAT, DESCRIBE.... the list goes on and on. However the MSSQL class takes many of these into account and automatically translates some MYSQL specific function calls into SQL server equivalents.
Using the abilities of MS SQL server, I have written helper User-Defined-Functions (UDFs), views, triggers and stored procedures to assist SQL server in coping with MYSQL function calls as well as help GL1.4's code base cope with data differences between MYSQL and SQL server.

The MSSQL class that I've written mimicks the MYSQL class' members that already exist in the /system/databases directory.

Installation of a SQL instance of Geeklog 1.4.1 follows the same steps as creating a fresh MySQL instance, however it has these slightly different dependencies:

• PHP installed and configured to use the Microsoft SQL server extensions.
• MS SQL server (MSDE) installed - any version 2000 or greater.
• A SQL database and database user created for Geeklog to use. This item is no different than the comperable MySQL step for installation, but needs to be carried out on the SQL server by a user who has enough access rights to create new databases.

Once you have Geeklog 1.4.1 downloaded and the dependencies configured, you'll have to edit the config.php file to ensure that the database selection is changed from 'mysql' to 'mssql' (the $_DB_dbms parameter setting in config.php). Please note that the database configuration parameters for database, user and password within config.php sill require configuration to match the 3rd bulleted item above.

Important Notes:
Plugins which require MSSQL support will require a MSSQL installer file and database detection routine developed to support SQL Server. I have personally converted some plugins to work on a MSSQL instance - just be aware that plugins specifically written for MySQL will not work "out of the box" on the SQL Server instance.

Also note that the MSSQL class does not overcome badly coded SQL routines which MySQL may be more forgiving with. Properly formatted SQL statements are a must and become very important - so watch out for things like including the right columns for a group by clause.

The MSSQL class does NOT approximate each and every MySQL function for SQL server. Only those critical functions which Geeklog 1.4.1 relies on is approximated by the MSSQL class and supporting database functions, triggers and stored procedures.

Enjoy the new database abilities of Geeklog!