Thursday, July 23, 2009

How to write database independent code

In order to ensure that your module works with all compatible database servers (currently Postgres and MySQL), you'll need to remember a few points.

* When you need to LIMIT your result set to certain number of records, you should use the db_query_range() function instead of db_query(). The syntax of the two functions is the same, with the addition of two required parameters at the end of db_query_range(). Those parameters are $from and then $count. Usually, $from is 0 and $count is the maximum number of records you want returned.
* If possible, provide SQL setup scripts for each supported database platform. The differences between each platform are slight - we hope documentation on these differences will be forthcoming.
* Reserved words checker for all database management systems.
* You should test any complex queries for ANSI compatibility using this tool by Mimer.
* If you are developing on MySQL, use it's ANSI compatibility mode.
* If you can install all database servers in your environment, it is helpful to create shell databases in each and then run sample queries in each platform's query dispatch tool. Once your query succeeds in all tools, congratulate yourself.
* Don't use '' when you mean NULL.
* Avoid table and field names that might be reserved words on any platform.
* Drupal 5 and below did not use auto-increment or SERIAL fields. Instead, it used integers and provided a function to generate identifiers: db_next_id(). First you db_next_id needs to be called and then its value can be used where unique identifiers are needed. In Drupal 6 however, you can use auto increment and after insert, it's possible to read the last insert id by using db_last_insert_id($table, $field).
* Use curly brackets when referencing table names in your SQL statements. This ensures that Drupal installations that use a database prefix will work correctly. Example: SELECT * FROM {accesslog} WHERE ... instead of SELECT * FROM accesslog WHERE ....
* Avoid calculating dates/times in the SQL itself. These functions vary widely in different DBMSes and MySQLs performance suffers if you do this. In a lot of cases MySQL won't be able to use indices and will need to calculate these dates/times for every row it scans.

No comments: