Database layer in Drupal 7

Evenin,

Okay, this ain't a joke, I'm really talking about db_query(). Well, the upcoming Drupal 7 equivalent of it.

The Drupal 7 database layer got a major rewrite, actually all the old backends that relied on PHP's outdated db_**() layer got replaced by one database abstraction layer (wrapped by Drupal functions), PDO. PDO has been designed to get rid of all the different implementations out there that abstracted object handling, much like Perl's DBI and Python's DB2.

PDO is an advanced beast, it supports prepared statements, enables you to route different queries to different database servers (for example sending all selects to a slave) and has comprehensive error handling trough OOP exceptions.

Larry Garfield started his porting efforts long ago and his code got merged (dr.o/n/225450) back to head. The DR3/4/5/6 helper for all queries is db_query(), which executes the statement directly.

For convenience db_query() still exists (works different though) and executes the statement directly. All long term replacements (db_select()) return PdoStatement instances that need to be ->execute()'d manually. Manually executing your queries makes it easier for strange PDO backends like Solid to enforce database commits, it also enables queries to be changed, inspected, dumped, canceled and a lot more.

$rids = db_query("SELECT rid FROM {role_permission} WHERE permission = :perm", array(':perm' => 'administer nodes'))->fetchCol();

$select = db_select('registry')->distinct()->execute();

Inserting something into the database looks like this now:
$id = db_insert('test_one_blob')->fields(array('blob1' => $data))->execute();

Updating records:
db_update('test_one_blob')->condition('id', $id)->fields(array('blob1' => $data))->execute();

Delete records:
db_delete('upload')->condition('fid', $file->fid)->execute();

This very cool change gives us a more scaling system, no more Drupal specific backend maintenance and above all, an impressive list of fully supported backends:

• MS SQL Server
• Firebird/Interbase
• IBM
• Informix
• MySQL
• Oracle
• ODBC and DB2
• PostgreSQL
• SQLite

Well, that's it for today. You might have noticed my enthusiasm towards the new layer being pushed, despite the big task of porting we have to do.

Niels