Database Abstraction - supporting multiple syntaxes

Question

In a PHP project I'm working on we need to create some DAL extensions to support multiple database platforms. The main pitfall we have with this is that different platforms have different syntaxes - notable MySQL and MSSQL are quite different.

What would be the best solution to this?

Here are a couple we've discussed:

Class-based SQL building

This would involve creating a class that allows you to build SQL querys bit-by-bit. For example:

$stmt = new SQL_Stmt('mysql');
$stmt->set_type('select');
$stmt->set_columns('*');
$stmt->set_where(array('id' => 4));
$stmt->set_order('id', 'desc');
$stmt->set_limit(0, 30);
$stmt->exec();

It does involve quite a lot of lines for a single query though.

SQL syntax reformatting

This option is much cleaner - it would read SQL code and reformat it based on the input and output languages. I can see this being a much slower solution as far as parsing goes however.

Answer 1

A solution could be to have different sets of queries for different platforms with ID's something like

MySql: GET_USERS = "SELECT * FROM users"

MsSql: GET_USERS = ...

PgSql: GET_USERS = ...

Then on startup you load the needed set of queries and refers then

Db::loadQueries(platform):

$users = $db->query(GET_USERS)

Answer 2

Such a scheme would not take account of all the richness which SQL offers, so you would be better off with code-generated stored procs for all your tables for each DB.

Even if you use parametrized stored procs which are more database model-aware (i.e. they do joins or are user-aware and so are optimized for each vendor), that's still a great approach. I always view the database interface layer as providing more than just simple tables to the application, because that approach can be bandwidth-intensive and roundtrip wasteful.

Answer 3

I'd recommend class-based SQL building and recommend Doctrine, Zend_Db or MDB2. And yeah, if it requires more lines to write simple selects but at least you get to rely on a parser and don't need to re-invent the wheel.

Using any DBAL is a trade-off in speed, and not just database execution, but the first time you use either of those it will be more painful than when you are really familiar with it. Also, I'm almost a 100% sure that the code generated is not the fastest SQL query but that's the trade-off I meant earlier.

In the end it's up to you, so even though I wouldn't do it and it sure is not impossible, the question remains if you can actually save time and resources (in the long run) by implementing your own DBAL.

Answer 4

if you have a set of backends that support it, I would agree that generating stored procedures to form a contract is the best approach. This approach, however, doesnt work if you have a backend that is limited in capabilty with regards to stored procedures in which case you build an abstaction layer to implement SQL or generate target specific sql based on an abstract/limited sql syntax.