Cross-platform database scripting…

It might be a heavy duty title, but don’t worry – it’s a wooly-minded article!

Development is still continuing (in fits and starts) on various projects of mine including the projectGenie system, but I’ve hit a certain problem many times and never came up with a suitable solution. The certain problem is that I want my software to be truly cross-platform, and preferably database system-independent. Tricky.

The cross-platform bit is quite easy to do. PHP is a true cross-platform scripting language, able to run under all the major web server systems. Plus it’s a joy to use, is being actively developed by some of the brightest people on the planet, and is free. Completely free. Wonderful.

The database-independent thing is a little more perplexing, though. Of course, as a good LAMP holder, I should be using MySQL all the way. And in an ideal world, I would. However, in the Microsoft-infested world I inhabit, I often have to deal with other database systems, most notably SQL Server. And, in the interests of making everything talk to each other, I don’t really want to mix SQL Server and MySQL together. Yes, I know about XML, but joining multiple databases in a single SQL server or MySQL installation is so much easier. Plus a lot of my clients get all their Microsoft stuff for free. Whoop-de-do.

So, I want a set of functions (not wrapped in a class yet, but they could easily be) that would allow me to use my PHP code with pretty much any database systems. “What about ODBC?”, I hear you ask. It’s a good question, but it’s still too limited. I’ll explain a bit more about that later.

I want to be able to change the entire database system running an application from (for example) MySQL to MS SQL Server in … less than a minute. Impossible, you think, but not so.

Let’s break this down. Hammer time-stylee.

1) All major database systems use a certain server name, port number, username and password to connect to. Excellent, we have some variables.

2) Most database systems use the same basic SQL syntax (select, insert, update, delete etc). Great, we have some constants.

3) Some database systems have certain similarities between their extended functions (iif, is null, day() etc). Oh, we have some syntax differences.

4) Some database systems have wildly different methods of storing and retrieving data (MySQL – UNIX timestamp, MS SQL Server – datetime format). Hmm, we have some formatting constraints.

And there’s a lot more to think about besides, however this gives us a basic understanding with which to set up a series of functions that will perform a range of database tasks independently of the database system being used. These tasks include:

– Running a query
– Retrieving the number of records returned
– Returning any error information
– Showing the data from a particular field in a particular record
– Formatting a date
– Compiling the correct syntax for an if statement

Now do you see why ODBC isn’t enough? Yes, it allows you to connect to loads of different databases but it provides no help in translating what you want to do into the correct SQL syntax for that database system. Here’s where the Database-Independant Processing of SQL (DIPS!) code steps in.

You set up your variables – database type, server name, port number, username and password. The system then kicks in the correct set of functions for that database system, and you can use a standard set of SQL commands to query the database. Anything that doesn’t work in SQL Server, for instance, would be translated. As the functions would be extensible, you could even use the cool functions from one database system in another – the system would translate your code into something the current database system understands.

Yes, it’s a lot to think about. And it’s probably beyond my capabilities to create – at the moment it’s certainly beyond my inclinations. But this would be useful, certainly to me. No more worrying about should-this-be-MySQL-I-really-wanted-to-use-SQL-Server or good-grief-SQL-Server-is-pants-gimme-MySQLs-date-formatting-functions-any-day.

What do you think? Can you see any major pitfalls?