Top Clicks

Milestone

Drupal 7 database layer

This post is my effort to guide you through the whole new abstraction layer for accessing the database server, which comes with the Drupal 7 core distribution. In this blog-post I’ll try to explain to you guys (girls) how this new layer works, without diving into all the details, in order to give you a clear view on this new system.

First of all I’ll explain the benefits of this new system to you. Drupal needed a database system that could easily support multiple database servers in a unified way which preserves the syntax power of SQL. The system was also build to enforce the security checks. There are 6 different types of Query’s which can be called in this database system: Insert, Update, Delete, Merge, Static and Dynamic.

Now lets get it started!

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.

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.

Note that no matter how many connections are defined in the settings file, These connections will not be used by Drupal until they are actually opened.

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.

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:

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
}

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 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.

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:

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

After looking over a few of the articles on your site, I seriously like your technique of blogging. I book marked it to my bookmark website list and will be checking back soon. Take a look at my website too and tell me how you feel.

Hello, Neat post. There’s an issue together with your site in web explorer, might test this? IE still is the marketplace leader and a huge portion of folks will pass over your fantastic writing due to this problem.