Syntax differences between mysql, sqlite and pgsql

Question

I'm creating a tiny activerecord library using PDO and I'm planning to support MySQL, Sqlite and PgSQL.

My question is how I can be sure that the query string works with all adapters? There will mostly be CRUD statements with some joins etc. Is there a standard I can follow that works for all of these?

Thanks / Tobias

EDIT: Thanks for all your answers but my question was more about the SQL 'syntax' differences between them.

Answer 1

If you want to write your own DB layer, I'd suggest you:

1. Use placeholders, if you aren't already. They add security too.
2. Use bindParam/bindValue with value type (e.g. BOOLEANS don't exist in SQLite but work if bound with PARAM_BOOL)...
3. Use stored procedures from MySQL, create matching names in PostgreSQL, and define them in SQLite with sqliteCreateAggregate/sqliteCreateFunction.
4. Do all parameter checking in PHP, because SQLite won't do any (e.g. validate date variables)...
5. Use InnoDB for MySQL to get transactions.

Note: By supporting these vastly different RDBMs, you're demoting the database to just a data store. Keep in mind that SQLite is very limited. It does not have native data types save from number/string. E.g. it's missing date handling and intervals, and so on. All three databases support transactions, which are essential for data integrity when the integrity is maintained outside the DB.

Edit: Removed mention of MySQL triggers, which are availabe for 5.0.