CHAPTER 5 ■ WORKING WITH DATABASES

function markednode_schema_alter(&$schema) { // Add field to existing schema. $schema['node']['fields']['marked'] = array(

'type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,

'description' => t('Whether the node has been marked by the markednode module.'),

);

}

Modifying Other Modules’ Queries with hook_query_alter()

This hook is used to modify queries created elsewhere in Drupal so that you do not have to hack modules directly. All Dynamic Select query objects are passed through hook_query_alter() by the execute() method, immediately before the query string is compiled. That gives modules the opportunity to manipulate the query as desired. hook_query_alter() accepts a single parameter: the Select query object itself.

As an example of how hook_query_alter() works, a module named dbtest utilizes hook_query_alter() to modify two queries. The first modification happens when a query is found that has a tag of “db_test_alter_add_range”. If that tag is found, the query is modified by adding range(0,2) to the query. The second modification occurs when a query with a tag of “db_test_alter_add_join” is found. In this case, a join is added between the test and people tables.

■ Note Tags are strings that identify a query. A query may have any number of tags. Tags are used to mark a query so that alter hooks may decide if they wish to take action. Tags should be all lower-case and contain only letters, numbers, and underscores, and start with a letter. That is, they should follow the same rules as PHP identifiers in general.

function dbtest_query_alter(SelectQuery $query) {

// you might add a range

if ($query->hasTag('db_test_alter_add_range')) { $query->range(0, 2);

}

// or add a join

if ($query->hasTag('db_test_alter_add_join')) {

$people_alias = $query->join('test', 'people', "test_task.pid=people.id"); $name_field = $query->addField('name', 'people', 'name'); $query->condition($people_alias . '.id', 2);

}

...

?>

111

CHAPTER 5 ■ WORKING WITH DATABASES

Connecting to Multiple Databases Within Drupal

While the database abstraction layer makes remembering function names easier, it also adds built-in security to queries. Sometimes, we need to connect to third-party or legacy databases, and it would be great to use Drupal’s database API for this need as well and get the security benefits. The good news is that we can! For example, your module can open a connection to a non-Drupal database and retrieve data.

In the settings.php file, $databases is an array composed of multiple database connection strings. Here’s the default syntax, specifying a single connection:

array(

'driver' => 'mysql', 'database' => 'databasename', 'username' => 'username', 'password' => 'password', 'host' => 'localhost', 'port' => 3306,

'prefix' => 'myprefix_',

);

As an example, you might have two databases, the default database (in this case named D7) and a legacy database as defined here.

$databases = array ( 'default' => array (

'default' => array (

'driver' => 'mysql', 'database' => 'd7', 'username' => 'username', 'password' => 'userpassword', 'host' => 'localhost', 'port' => '',

'prefix' => '',

),

), 'legacy' => array (

'default' => array (

'driver' => 'mysql', 'database' => 'legacydatabase', 'username' => 'legacyusername',

'password' => 'legacyuserpassword', 'host' => '122.185.22.1',

'port' => '6060',

),

),

);

112

CHAPTER 5 ■ WORKING WITH DATABASES

■ Note The database that is used for your Drupal site should always be keyed as default.

When you need to connect to one of the other databases in Drupal, you activate it by its key name and switch back to the default connection when finished:

//Get some information from a non-Drupal database. db_set_active('legacy');

$result = db_query("SELECT * FROM ldap_user WHERE uid = :uid", array(':uid' => $user->uid));

//Switch back to the default connection when finished.

db_set_active('default');

■ Caution If you switch to a different database connection and then try to do something like t(“text”), it will cause an error. The t() function requires database activity, and the database connection stays switched, even outside of the code scope where you switched it. Therefore always be careful to switch the database connection back to default as soon as possible, and in particular take care that you don’t call code that will in turn make database requests.

Because the database abstraction layer is designed to use identical function names for each database, multiple kinds of database back ends (e.g., MySQL and PostgreSQL) cannot be used simultaneously. However, see http://drupal.org/node/19522 for more information on how to allow both MySQL and PostgreSQL connections from within the same site.

Using a Temporary Table

If you are doing a lot of processing, you may need to create a temporary table during the course of the request. You can do that using db_query_temporary() with a call of the following form:

$tablename = db_query_temporary($query, $arguments, $options);

$query is the prepared statement query to run.

$args is an array of values that will be substituted into the query. $options is an array of options to control how the query operates. The return value is the name of the temporary table.

You can then query the temporary table using the temporary table name.

$final_result = db_query('SELECT foo FROM '.$tablename);

113

CHAPTER 5 ■ WORKING WITH DATABASES

Notice how the temporary tables never require curly brackets for table prefixing, as a temporary table is short-lived and does not go through the table prefixing process. In contrast, names of permanent tables are always surrounded by curly brackets to support table prefixing.

■ Note Temporary tables are not used in the Drupal core, and the database user that Drupal is using to connect to the database may not have permission to create temporary tables. Thus, module authors should not assume that everyone running Drupal will have this permission.

Writing Your Own Database Driver

Suppose we want to write a database abstraction layer for a new, futuristic database system, named DNAbase, which uses molecular computing to increase performance. Rather than start from scratch, we’ll copy an existing abstraction layer and modify it. We’ll use the PostgreSQL implementation.

First, we make a copy of includes/database/pgsql/database.inc and rename it as includes/database/dnabase/database.inc. Then we change the logic inside each wrapper function to map to DNAbase’s functionality instead of PostgreSQL’s functionality.

We test the system by connecting to the DNAbase database within Drupal by updating $databases in settings.php.

For additional details on writing your own database driver, please see http://drupal.org/node/310087.

Summary

After reading this chapter, you should be able to

•Understand Drupal’s database abstraction layer.

•Perform basic queries.

•Get single and multiple results from the database.

•Get a limited range of results.

•Use the pager.

•Understand Drupal’s schema API.

•Write queries so other developers can modify them.

•Cleanly modify the queries from other modules.

•Connect to multiple databases, including legacy databases.

•Write an abstraction layer driver.

114