HowTo: Convert a module to DBTNG

DBTNG is the new database abstraction layer of Drupal 7. It uses several OOP principles and provides far more features than Drupal 6 did.

Most queries need to be slighty changed to work with DBTNG, this blog post will explain some of the most common conversions.

Simple SELECT queries

Most queries don't need much work. Usually, two things need to be done:

First, replace the arguments, if any. instead of '%s', %d and so on, use :unique_name. Additionally, the keys of the params array need to match these unique names. When doing this, you have to follow a few rules:

• Don't use quotes characters (', ") for strings
• Use a unique name for every parameter
• A named parameter can also be used for an array of values, for example IN(:keys)

Example:

Second, update the fetching. If you just want to loop over the results, you can use a simple foreach statement. By default, the result is fetched into objects, if you need a different behavior, there are two possible ways to change that:

1. Set the fetch option, for example: db_query($sql, $params, array('fetch' => PDO::FETCH_ASSOC))
2. Use specific fetch method of the $result object, for example: foreach ($result->fetchAllAssoc() as $row)

Examples:

More information about static queries and fetching can be found here: http://drupal.org/node/310072

INSERT, UPDATE, DELETE queries

All these queries need to be converted to the new DBTNG syntax: db_insert(), db_update() and db_delete().

Example UPDATE query:

Two hints to improve the performance of your queries:

1. If you insert multiple rows in a line, consider to use the multi-insert feature of db_insert(), which allows to supply multiple arrays of values. If supported by the database, these will be inserted in a single query.
2. If you either need to update or insert a new row, depending if an entry with a specific key already exists, consider to use db_merge().

Dynamic SELECT Queries

DBTNG provides a flexible Query Builder, which needs to be used if one or multiple of the following things apply to a specific query:

• Others need to be able or might want to extend the query (Especially if the query used db_rewrite_sql() before)
• If the query is very dynamic and does add different conditions, joins and other things based on something.
• If so called query extenders (see below) are being used.

The query builder is one of the really powerful features of DBTNG and with power often comes a fair amount of complexity. However, while it might look complex at the beginning, it is *far* more robust than assembling arbitrary query strings together. This howto will only shortly explain the concept of the query builder, the exact documentation can be found here : http://drupal.org/node/310075.

If a dynamic query needs to be created, it's best to start from the beginning, though the following points might help to get started. Note that it is obviously necessary to create the query object first and execute it at the end but except of that, the order of the steps/commands should not be relevant and can be mixed and changed if it makes sense.

1. First, you need to create a query object. This is easy:
   <?php
$query = db_select('node', 'n');
?>
2. Now, we can add the necessary joins. Note that join methods (and generally most methods that start with add</cod or <code>set) can't be "chained". This is because they return something else than the query object, in most cases the used alias. As an example, we are adding a join to the node_comment_statistics table:
   <?php
$query->join('node_comment_statistics', 'l', 'n.nid = l.nid');
?>
3. Because we are querying the node table, we should add the node_access tag. This is the replacement of db_rewrite_sql() that is now longer needed in Drupal 7. The linked documentation contains a list of commonly used tags but you can always define your own if you want to give other modules the possibility to extend your query. Note that addTag() method can be chained.
   <?php
$query
  ->addTag('node_access')
  ->addTag('custom_tag');
?>
4. Adding some chainable methods to our query object...
   <?php
$query
  ->fields('n', array('nid', 'title', 'body')) // Select the specified fields from the node table.
  ->orderBy('n.nid', 'DESC') // Adds a ORDER BY n.nid DESC.
  ->condition('n.type', 'page') // Only load nodes with the type page.
  ->range(0, 5); // Load the first 5 rows.
?>
5. Now is the time to add things dynamically. As an example, we only load the nodes of a specific user if the variable $uid contains something.
   <?php
if (!empty($uid)) {
  $query->condition('n.uid', $uid);
}
?>
6. Last step, execute the query and fetch the results into an array keyed by nid
   <?php
$nodes = $query
  ->execute()
  ->fetchAllAssoc('nid');
?>

Now putting everything together, note that the order has changed so that almost everything can be chained...

Replace pager_query()

The function pager_query() has been replaced by the so called Query Extender PagerDefault. To use it, you need a dynamic query.

Query extenders allow to extend the query builder with additional features. Multiple extenders can be used at the same time.

There are two things to do:

1. Extend the query:
   
   <?php
$query = db_select('node', 'n')->extend('PagerDefault');
?>
2. Next, define the number of rows per page:
   <?php
$query->limit(10);
?>

Important: extend() returns a new query object. Because of that, it is adviced to extend the query object early as shown in the example. If that is not possible, overwrite the existing object reference as shown below:

Replace tablesort_sql()

For table based sorting, there is another Query extender. As before, first extend the query object and then use the orderByHeader($header) method.

Please comment here or ping me in #drupal if you have any questions.