Database Abstraction

[freebie]

I've been using PEAR:😃B for database abstraction. One thing I failed to realize though is that when switching to another database, many of the queries will need to be re-written (if they use database specific functionality).
For many websites, this will not be that difficult, but if I wanted to develop a product that support multiple databases, and wanted to distribute it as 1 product (configurable to the type of db), then this wouldn't work.
Can someone recommend an article or a design pattern or something that will lead me in the right direction to learn how to handle this abstraction?
Thanks for any suggestions.

[The_Terminator2]

Hi, I use a Factory (design pattern) to decide which database API to use, such as MySql, Postgre and SQLite for example 🙂

The Factory decided based on a configuration variable DB_TYPE. This will solve the API issue for you, as for actual scripting I would suggest that you could use HEREDOC to create your database specific SQL ?

That way you do not actually need to alter the script, just the HEREDOCs, which can be in their own file which you just include as required 🙂

[freebie]

Thanks for the insight on this.

[drawmack]

Create a database class and make one of the methods a query method. Then have the query method take a list of columns a table name and a where clause. Then make the query function smart enough to parse the where clause so that any db specific functions are turned into acceptable sql for this specific database before the query is executed.