Quick guide to the new Drupal 7 database layer
Settings.php
We should start by taking a look at the renewed database
definition in the settings.php. In most cases you will be using a drupal
site with only one database. For this you should use the following
structure.
<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb’,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘localhost’,
);
?>
The first thing you should notice are the values “default”
in the $database array. The first one is the CONNECTION KEY, the second
one is the TARGET.
The connection key is a unique identifier for a database connection, there must ALWAYS be a ‘default’ connection key available.
The target is used to define master/slave
database structures. If the master (default) isn’t available, the system
will search for the slave database. It is also possible to flag a query
to run on the slave database. To make this a bit clearer I’ll give you a
more complicated database structure.
<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['default']['slave'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>
In this case, the first database is the default database, the second is the slave databases.
It is also possible to define separate database structures.
<?php
$databases['default']['default'] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb1′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver1′,
);
$databases['extra']['default'][] = array(
‘driver’ => ‘mysql’,
‘database’ => ‘drupaldb2′,
‘username’ => ‘username’,
‘password’ => ’secret’,
‘host’ => ‘dbserver2‘,
);
?>Select Query
So far for the database connections, now lets take a look
at the actual use of query’s in this new database layer. For the regular
select query’s not much changes. Here is an example of a select query
with a short explanation.
$result = db_query(“SELECT nid, title FROM {node} WHERE type = :type”, array(
‘:type’ => ‘page’,
));
The db_query function uses three arguments, first one is
the query string, the second one are the values used to fill up the
placeholders. The third one will be explained at the next example.
Note that the placeholder (:type) doesn’t use quotes.
Another thing you should take in to account is to put your database
names between {}. This is needed for the database system to attache a
prefix string if this is defined in your settings.
Now for the third argument of the db_query we will take a look at the following code:
<?php
$result = db_query(“SELECT nid, title FROM {node}”, array(), array(
‘target’ => ’slave’,
));
?>
The third argument is an array of configuration directives
to detect the way the query should run. In this case the query runs on
the slave database. I won’t go into the details here, because (as I said
before) in this blog post I will only pick up the basics of the new
database system.
The following is just a handy guide about the way you can
use the database query’s results. (Not 100% relevant to this post, but
this might come in handy for some of you. The others should just scroll
through the code, as if it doesn’t exist).
$result = db_query(“SELECT nid, title FROM {node}”);
foreach ($result as $record) {
// Do something with each $record
}$record = $result->fetch(); // Use the default fetch mode.$record = $result->fetchObject(); // Fetch as a stdClass object$record = $result->fetchField($column_index); // Fetch only one field.$number_of_rows = $result->rowCount(); //Count the results.Insert Query
Now we’ve arrived to he fun part. The INSERT, DELETE and
UPDATE query’s require that you use the query builder object in order to
behave consistently across all different databases. This is where the
new object-oriented query API comes in.
The compact INSERT form is the following:
<?php
$nid = db_insert(‘node’)
->fields(array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
))
->execute();
?>
This will result in the following query:
INSERT INTO {node} (title, uid, created) VALUES (’Example’, 1, 1221717405);
Note: If you don’t call the execute() method, the query will not run!
The insert query object can also be used with multiple
values. To insert multiple rows you shouldn’t only use fields() but also
values(). In this case fields() only defines the fields, but doesn’t
put any content into the selected fields. The values() may be called
multiple times in order to add more than one line to your database.
<?php
$query = db_insert(’node’)->fields(array(‘title’, ‘uid’, ‘created’))
->values(array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
)))
->execute();
?>
Using a ‘foreach’ the code will look like this:
<?php
$values = array(
array(
‘title’ => ‘Example’,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
array(
‘title’ => ‘Example 2′,
‘uid’ => 1,
‘created’ => REQUEST_TIME,
),
);
$query = db_insert(‘node’)->fields(array(‘title’, ‘uid’, ‘created’));
foreach ($values as $record) {
$query->values($record);
}
$query->execute();
?>Update Query
Next stop is the UPDATE query. The update query is pretty
straight forward, if you understand how the insert query’s work, it
shouldn’t be a problem to understand the update query. Here it goes:
<?php
$num_updated = db_update(‘node’)
->fields(array(
‘uid’ => 5,
’status’ => 1,
))
->condition(‘created’, REQUEST_TIME - 3600, ‘>=’)
->execute();
?>
This will result in the following query:
UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405;
Not much to explain here, so let’s go to the DELETE query’s.
Delete Query
Again the same story here. The DELETE query is probabily the easiest form of the query object:
<?php
$num_deleted = db_delete(‘node’)
->condition(‘nid’, 5)
->execute();
?>
This will result in the following query:
DELETE FROM {node} WHERE nid=5;
Merge Query
Finally we’ve got to the last one. The MERGE query. This
one is a bit more complicated. If you would strip this one down to it’s
original form, you will finde that a merge query is actually just the
combination of an insert and an update query. In php it would be
something like this:
<?php
if (db_query(”SELECT COUNT(*) FROM {example} WHERE id=:id”, array(’:id’ => $id)->fetchField()) {
// Run an update using WHERE id = $id
}
else {
// Run an insert, inserting $id for id
}
?>
In the new database API structure the merge query’s are build up like this:
<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->execute();
?>
Here the “example” table is used. The specified key field ‘name’ has the value of $name. Now two things could happen.
First option: If the $name value exists in the database,
then fields “field1” and “field2” will get an update with the
correspondingvalues.
Second option: If the $name value doesn’t exist in the
database, a new row will be created in which “name” gets the value
$name, “field1” gets the value $field1 and “field2” gets the value
$field2.
In some cases the values you want to set will have to be
different, according to the fact that the key field does or doesn’t
already exist. This can be handled in two ways.
<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->update(array(
‘field1′ => $alternate1,
))
->execute();
?>
In this case, if the “name” already exists the value of
“field1” will be $alternate1, and the value of “field2” will be $value2.
If the “name” doesn’t allready exist, $value1 and $value2 will be used.
It is also possible to use expressions. I’ll give you an
example in which, if the ‘name’ already exists, the “value1” field will
become the current value +1:
<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->expression(‘field1′, ‘field1 + :inc’, array(‘:inc’ => 1))
->execute();
?>
Note that expression() can be used multiple times, 1 time for each field.
Field updates can also be limited, if the row already
exists. In this case, if the “name” already exists, only “field2” will
be updated, and “field1” will be ignored:
<?php
db_merge(‘example’)
->key(array(‘name’ => $name))
->fields(array(
‘field1′ => $value1,
‘field2′ => $value2,
))
->updateExcept(‘field1′)
->execute();
?>
This is the end of my quick guide into the new Drupal 7
database layer. Hope you’ve enjoyed it. For more detailed information
about this system, I would like to refere to the official drupal
database API: http://drupal.org/developing/api/database
