Some suggestions for database portability guidelines

Almost every table definition I've evaluated so far could be rewritten into portable code. That is, most MySQLisms I have come across are pointless and outdated - and could easily be avoided, making Drupal a much more cross-database solution than it currently is. That is, we should develop some essential database coding guidelines!

The boringly obvious point is of course to use standard SQL statements only - the MySQL and PostgreSQL documentation usually state whether a statement is compatible to standard SQL. As many people will rarely look up whether the statements they have been using for years are deprecated, here are a couple of recommendations to start with:

• do not copy&paste your table definitions from a dump, or you will end up with the particular backends' database specific view of your design, often with some obsoleted indices and columns from discarded design stages thrown in. Design is a result of planning and consideration, not a snapshot from a trial-and-error process. Rethink, reorder and rewrite your code to be concise, logical and generic...
• use standard generic data types, that is, integer rather than int(x). And choose text over varchar(y) unless you have very good reasons to use the latter
• don't force table types - "MyISAM" won't only annoy people using other databases, but also the InnoDB users...
• " and ` are not ' - portable SQL string literals are in single quotes (') only.
• there is no UNSIGNED in SQL
• there is no REPLACE in SQL
• define default values for everything that could ever appear in a where, order by or group by clause - the definition and behaviour of undefined or empty values varies massively among different SQL databases.
• don't ever make assumptions about the table ordering. That is, always explicitly use column names in your queries - "insert into foo(bar,baz) values(1,2)" rather than "insert into foo values(1,2)", "select bar, baz from foo" rather than "select * from foo" etc.
• don't refer to, order or group by columns that do not appear in the results.
• split your database definition code into a file with the generic table definition and extra files for database specific stuff to ease porting
• check your values for validity before inserting - or at the very latest, after a insert failure...
• ... which brings me to: Check your return values, and handle all errors!

If you have any further suggestions, please follow up on this post.

Sevo