Drupal: Working With Databases

This is the first in my series of short notes on the book Pro Drupal Development by John K. Vandyk and Matt Westgate. It is for those of you interested in writing your own Drupal modules. This notes corresponds to “Chapter 5: Working With Databases” of the book.

Code samples are taken from the book; some with slight modifications for brevity. I’m sorry about the lack of indentations, as WordPress sucks in formatting <code> tags.

Drupal’s database abstraction layer works as the middle man between our code and database. By using the database abstraction layer, we can avoid database-specific function calls and queries, and sanitize user-submitted input to prevent SQL injection attacks.

Defining Database Parameter

Settings for connecting to the database are kept in settings.php, which is located in sites/example.com or sites/default, in the form of

$db_url = 'mysql://username:password@localhost/databasename';

Understanding the Database Abstraction Layer

The type of database is determined by the value of $db_url in settings.php. Depending on the type of database, the corresponding includes/database.dbtype.inc is included. For example, it is includes/database.mysql.inc for MySQL.

Connecting to the Database

No explicit command is required. Connection is automatically carried out in the bootstrap process.

To connect to the database from outside Drupal, call

include_once('includes/bootstrap.inc');
drupal_bootstrap(DRUPAL_BOOTSTRAP_DATABASE); //generates active connection
//proceed with db_query() ...

Performing Simple Queries

db_query(query string [, value(s) of placeholders(s), ...]);

E.g.

db_query("INSERT INTO {joke} (nid, vid, punchline) VALUES (%d, %d, '%s')", $node->nid, $node->vid, $node->punchline);

• Placeholders of string values should be in single quotes.
• Table names must be enclosed by { }, so that they can be automatically prefixed to avoid table name collisions.
• Always use placeholders for user-submitted input, with the actual values following as parameters. The values will be sanitized to prevent SQL injection attacks.
• Placeholders are %s for string, %d for integer, %f for float, %b for binary data (don’t enclose in ''), %% for literal %
• NULL, TRUE, FALSE are casted to decimal equivalents.

Retrieving Query Results

Getting Multiple Rows

$sql = "SELECT * FROM {node} WHERE type='blog' AND status=1"; //status=1 published, status=0 unpublished

$result = db_query(db_rewrite_sql($sql));

while ($data = db_fetch_object($result)) {
$node = node_load( $data->nid );
print node_view( $node, TRUE );
}

Getting a Single Value

• Use db_result(), which is equivalent to db_query() + db_fetch_object().

$sql = "SELECT COUNT(*) FROM {node} WHERE type='blog' AND status=1";

$total = db_result(db_query($sql));

Getting a Limited Range of Results

• Use db_query_range(), which is equivalent to db_query() + LIMIT clause, because not all database types use the same LIMIT syntax.
• Placeholder parameters come before limit parameters.

$sql = "SELECT * FROM {node} WHERE type='%s' AND status=%d ORDER BY n.created DESC";

$result = db_query_range(db_rewrite_sql($sql), $type, $status, 0, 10);

Getting Results for Paged Display

A paged display is a page of formatted results with links to more results and the “first and last” links.

$sql = "SELECT * FROM {node} n WHERE type='blog' AND status=1 ORDER BY n.created DESC";

$result = pager_query(db_rewrite_sql($sql), 0, 10);

while ($data = db_fetch_object($result)) {
$node = node_load($data->nid);
print node_view($node, TRUE);
}

print theme('pager', NULL, 10); //add links to remaining pages of results

theme('pager') knows the number of results from pager_query().

Using a Temporary Table

When a lot of processing is needed

$result = db_query_temporary($sql, $arguments, $temporary_table_name);

• Names of temporary tables don’t require table prefixing and therefore { }.
• Then query the temporary table as normal using the temporary table name.

Exposing Queries to Other Modules

Lets other modules modify our queries. For example, node module modifies queries for node listings to filter nodes protected by access fields.

In module that issues queries

Wrap query in db_rewrite_sql() before sending to db_query().

Note: db_rewrite_sql() cannot do complex queries. Implicitly joining tables in the FROM clause does not work.

Incorrect

SELECT * FROM node AS n, comment AS c WHERE n.nid=c.nid

Correct

SELECT * FROM node n INNER JOIN comment c on n.nid=c.nid

In module that modifies queries

Implement this hook.

function hook_db_rewrite_sql ($query, $primary_table='n', $primary_field='nid', $args=array())

Connecting to Multiple Databases

Make $db_url in setting.php an array.

$db_url['default'] = ... //reserved for Drupal site's database

$db_url['legacy'] = ... //another database; array key can be any name

Create more elements if needed.

To work with the legacy database, do

db_set_active('legacy');

When done, always switch back to the default database by

db_set_active('default');

Multiple kinds of database back-ends, such as MySQL and PostgreSQL, cannot be used simultaneously. To allow for this, see handbook.

Using Module .install Files

Creating Tables

To create database tables at the time your module is installed, create an .install file that contains an implementation of hook_install() with the SQL to create the tables. Provide all SQL statements specific to the most common database systems, such as MySQL and PostgreSQL. For example,

function book_install() {

switch ($GLOBALS['db_type']) { //Determines the database type
case 'mysql': //Same as mysqli
case 'mysqli':
db_query("CREATE TABLE {book} (...)
/*!40100 DEFAULT CHARACTER SET UTF8 */ ");
break;
case 'pgsql':
db_query("CREATE TABLE {book} (...) ");
db_query("CREATE INDEX ...");
break;
}
}

/*!40100 DEFAULT CHARACTER SET UTF8 */ will get executed only by MySQL version 4.1 or higher; otherwise, it is ignored.

Maintaining Tables

When a new version of your module calls for a change in the database table structure,

1. Update hook_install() so that the new structure will be installed for new users.
2. Implement an update function for existing users. Update functions are named starting at 1.

function book_update_1() {


$items = array();
$items[] = update_sql("ALTER TABLE ...");

}

Drupal keeps track of which version a module is using in its system table.

Deleting Tables on Uninstall

Implement hook_uninstall() in your module’s .install file.

function book_uninstall() {

db_query("DROP TABLE {book}"); //Delete table
variable_del('book_nodetypes"); //Delete any variables defined

}

Writing Your Own Database Abstraction Layer

1. Copy includes/database.mysql.inc and rename it as includes/database.yourdb.inc.
2. Change each wrapper function to map to your database’s functionality instead of MySQL’s.
3. Connect to your database by updating $db_url in settings.php.
4. Create a test module that calls the wrapper functions directly to make sure that they work.