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

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.