 | Level: Intermediate Alister Lewis-Bowen (alister.lewisbowen@gmail.com), Senior Software Engineer, IBM
Stephen Evanchik (evanchsa@gmail.com), Software Engineer, IBM
Louis Weitzman (louis.weitzman@gmail.com ), Senior Software Engineer, IBM
17 Oct 2006 Follow along in this series of articles as the the IBM® Internet Technology Group designs, develops, and deploys an extranet Web site for a fictitious company, International Business Council, using a suite of freely available software. In this installment, explore the Drupal database abstraction layer and learn how to use it effectively while developing a Web site. Discover best practices for module developers and details about using the Drupal database functions. You also learn to implement the necessary code to support a new database -- IBM DB2® Express-C.
Introduction
In this series of articles, learn how to create a customized Web site for a fictitious company, International Business Council (IBC). The site requires document storage, discussion groups, specialized workgroups, conference scheduling, and schedule session descriptions.
In this article you'll explore the Drupal database abstraction layer, a very thin layer of code that wraps the low-level PHP database routines.
Drupal comes standard with support for MySQL and PostgreSQL, but adding an additional database is relatively easy if it supports a PHP interface. As a module developer, you should use this database layer as much as possible to reduce your development time and increase your module's portability.
Drupal's database layer is split between two main files. The first file, includes/database.inc, defines functions that are not specific to a particular database implementation. This file contains two functions that you will encounter while developing modules:
db_query()
| The main function that issues queries to the database |
db_query_range()
| Limits the results of a query to the specified range |
The second file, includes/database.<type>.inc, defines all of the functions that provide an abstraction from the raw PHP database functions that are specific to each database type. Drupal supports MySQL and PostgreSQL by default, but other databases can be added by simply implementing the functions found in includes/database.mysql.inc for that database. The functions you will commonly use from this file are:
db_result()
| Returns an individual result from the previous query |
db_fetch_object()
| Returns the next row as a PHP object |
db_fetch_array()
| Returns the next row as an array of values |
db_num_rows()
| Returns the number of result rows returned by the previous query |
db_affected_rows()
| Returns the number of rows changed by the previous query |
Drupal uses its own database abstraction layer throughout its code base. However, there are two specific uses that provide good, isolated examples of proper usage:
- The pager system, which provides a mechanism to split the output of large result sets across multiple pages.
- The module installation system, which simplifies the installation and maintenance of additional database objects for third party modules.
These systems are described in detail below.
Pager system
Many Web sites feature content that is displayed across multiple, sequential pages. Navigating search results is an example of this behavior. The user enters search terms and then submits them to the site. If the number of search results exceeds the total number that can be displayed on a single page, the pager system is invoked. Figure 1 shows an example of the pager system in action.
Figure 1. Search results on Drupal Web site using the pager system
You may be asking, "How does this relate to the Drupal database abstraction layer?" The answer: The pager_query() function uses the db_query_range() function to select the appropriate page of results.
Essentially, the pager system is a small application built on top of the database abstraction layer that provides a richer interface to the database, as well as the necessary theme elements for output customization. The key functions of the pager system shown in Figure 2 are as follows:
pager_query()
| Sends the query to the database for a specific page of results |
theme_pager()
| Main theme function for displaying a query pager. All other pager theme functions are used in this function. |
theme_pager_first()
| Displays the link to the first page of the results (Figure 2, section A) |
theme_pager_last()
| Displays a link to the last page of the results (Figure 2, section E) |
theme_pager_link()
| Displays a link to a specific result page (Figure 2, all sections) |
theme_pager_list()
| Displays a list of nearby result pages (Figure 2, section C) |
theme_pager_next()
| Displays a link to the next result page (Figure 2, section D) |
theme_pager_previous()
| Displays a link to the previous result page (Figure 2, section B) |
Figure 2. Example of pager controls to move through multi-paged search results
Now that we've covered the components of the pager system, let's look at an example. We'll use the announcement module, described in previous articles, in our example.
Pager example
In this example we want to allow the site administrator to view all announcements that have been entered into the IBC Web site. This is a very common pattern and is an excellent use case for the Drupal pager system. We begin with defining the URL and its corresponding menu item entry in announcement_menu(). The code is shown in Listing 1.
Listing 1. Addition to the announcement_menu to invoke the pager code
$items[] = array('path' => 'announcements/pager',
'title' => t('Announcements Pager Example'),
'access' => user_access('administer site'),
'type' => MENU_CALLBACK,
'callback' => 'announcement_pager');
|
We are connecting the URL announcements/pager to the function announcement_pager() and only allow site administrators to view the complete listing of announcements. Now we create the code that displays the page with the pager controls for navigation, as shown in Listing 2.
Listing 2. Callback function announcement_pager
function announcement_pager() {
$query = "SELECT n.nid, n.created FROM {node} " .
"n WHERE type = 'announcement' ORDER BY n.created DESC";
$result = pager_query(db_rewrite_sql($query), 10);
while ($announcement = db_fetch_object($result)) {
$output .= node_view(node_load($announcement->nid), 1);
}
$output .= theme('pager', NULL, 10);
return $output;
}
|
This code issues the query using the pager system with a limit of 10 results per page. When you visit the announcements/pager URL, you will see a page similar to Figure 3.
Figure 3. Announcements using the pager system
The Drupal pager system is a great example of a small application of the database application layer. It uses db_query() and db_query_range() to provide paginated results of a potentially large dataset.
Managing module database schemas
Part 6 of this series describes the steps for creating a new module for announcements. The announcements module is needed to store extra data in the database. We describe the extra database table and provided the SQL CREATE TABLE statement that you used to add the table to your database. Adding the table outside of the Drupal framework is problematic, because it makes upgrading difficult or nearly impossible in some cases.
To ease module maintenance and provide a clean upgrade path for the module developer, Drupal provides a mechanism to update the database automatically through a module.install file. This file contains two pieces of information, which we'll describe in detail using our announcement module from Part 6 as an example. The announcement module used the SQL in Listing 3 to add a new database table.
Listing 3. SQL for creating additional database table supporting the announcement module
CREATE TABLE announcement (
nid int(10) unsigned NOT NULL default '0',
abstract varchar(255) default '',
publish_date integer NOT NULL default '0',
expiration_date integer NOT NULL default '0',
PRIMARY KEY (nid)
);
|
Creating an .install file
An .install file is a very simple file that contains two or more functions. The first function, the Drupal hook hook_install(), is described in this section. It is used to install the necessary database structures when the module is first enabled. The second and all subsequent functions are for managing database schema updates for your module. These functions, and the update process, are described in the next section, Managing schema updates.
Creating an .install file is easy. Just create a new file named <module name>.install and place it in the same directory as your module. In our case, the name of the file is announcement.install. We must now create the announcement module's implementation of hook_install(). Following the formula outlined in Part 5 and Part 6 for naming hooks, Listing 4 shows the skeleton implementation of announcement_install.
Listing 4. Skeleton of the announcement_install function in the .install file
function announcement_install() {
global $db_type;
switch ($db_type) {
case 'mysql':
case 'mysqli':
break;
case 'pgsql':
break;
}
}
|
This simple hook contains a single switch / case statement block that is keyed on the database type. The strings mysql and mysqli indicate MySQL-compatible database code, and the string pgsql indicates PostgreSQL-compatible database code. Now, insert the table creation SQL in the MySQL case statement, remembering to use db_query() as shown in Listing 5.
Listing 5. Part of install hook with SQL to create announcement table
case 'mysql':
case 'mysqli':
db_query("
CREATE TABLE {announcement} (
nid int(10) unsigned NOT NULL default '0',
abstract varchar(255) default '',
publish_date integer NOT NULL default '0',
expiration_date integer NOT NULL default '0',
PRIMARY KEY (nid)
);
");
break;
|
The first change you should notice is that we are using the Drupal database layer to create the announcement table. Using the Drupal database layer is very important. In Part 6 you learn how to install the table in a fashion that completely bypasses the Drupal database layer. That was not good practice, especially if you intend to distribute your module to the community. You should use the database and installation functions to provide flexibilty during the deployment of your site.
The second change you will notice is the seemingly illegal SQL used for the name of the table: {announcement}. Whenever you reference a table in your SQL, you should always enclose it in curly brackets, like this: {announcement}. This is part of a mechanism that allows the site administrator to prefix all Drupal tables with a string. For example, if the administrator decides that all Drupal tables must be prefixed with ibc_, the table name {announcement} will expand to ibc_announcement during the call to db_query(). This feature is necessary if you share a database between Drupal and another application, and in many other situations.
Another potentially confusing point: This hook is only called when the module is enabled for the first time in the module administration page. The module's database tables are not removed if you subsequently disable the module. If you decide to re-enable it, hook_install() will not be executed again.
Managing schema updates
Now that you've created a module installation hook that creates the necessary database objects, what happens when you need to modify those database objects? Fortunately, Drupal provides another hook, hook_update_<N>(), where <N> represents the number of the update starting with 1 for the first update. The first update function for any module is, therefore, of the form <module name>_update_1(). This function updates the module's schema version and associated database objects.
Every time you update your module's database schema, such as adding a NOT NULL modifier to an attribute, you should record that update in the next available hook_update_<N>(), incrementing <N>. When you release or deploy your module, you and all of your users can update to the newest version of your module in one easy step by picking the latest schema version available.
Using the announcement module from Part 6, let's add another attribute that records how important the announcement is. We will call this attribute priority. The new CREATE TABLE operation is shown in Listing 6.
Listing 6. Updated announcement table creation to include new priority attribute
case 'mysql':
case 'mysqli':
db_query("
CREATE TABLE {announcement} (
nid int(10) unsigned NOT NULL default '0',
abstract varchar(255) default '',
publish_date integer NOT NULL default '0',
expiration_date integer NOT NULL default '0',
priority tinyint(2) default '0',
PRIMARY KEY (nid)
);
");
break;
|
We need to change the SQL in hook_install() for any new installations of the announcement module. Now we must provide the proper hook_update_<N>() for preexisting installations of the announcement module. In the case of the announcement module, the necessary PHP code is shown in Listing 7.
Listing 7. First database update hook for announcement module adding priority attribute
function announcement_update_1() {
global $db_type;
switch ($db_type) {
case 'mysql':
case 'mysqli':
db_query('ALTER TABLE {announcement} ADD COLUMN priority
tinyint(2) default \'0\' ');
break;
case 'pgsql':
break;
}
}
|
Just as with hook_install(), we provide a separate case statement for each database type. Notice we are still using db_query() and that we enclosed the table name in { and }. We cannot stress enough the importance of writing portable database code. Your module could be used by many people in a variety of situations, and you must offer them the greatest amount of flexibility possible. If you had more database updates for the new version of the announcement module, you would add them to the announcement_update_1() function.
Now that you've created all of the supporting PHP and SQL for installing and updating the announcement module, you need to instruct Drupal to perform the schema updates. This is easy and initiated using your browser and the Drupal site. First, back up your database before you attempt to update its schema, and verify the backup is correct and complete. Then log in as the Drupal administrative user (the user that is uid == 1), and go to http://your.drupal.site/update.php. The resulting page is shown in Figure 4.
Figure 4. Drupal update page
Select the link Run the database upgrade script to begin the upgrade process. You should see a screen that looks similar to Figure 5. Your installation of Drupal may have more drop-down boxes to choose from, depending on how many modules you have installed. Since we are updating the announcements module, make sure its drop-down box has the highest version number selected, then select Update. Drupal processes all updates to a module starting from the installed version and ending on the version that you select.
Figure 5. Update page for IBC installation
Expand the updates section and verify that the update you have just created for the announcement module is selected. Next, select Update. If you see any errors, restore your database to the backup you just created and try again.
As described earlier, when the module is enabled for the first time in the module administration page, hook_install() is run and the database tables are created. Drupal will also set the installed module's schema version to the greatest numbered hook_update_<N>(). This effectively marks that the module as installed and up to date.
Although this discussion of the .install file functions is centered on the database layer, there is nothing constraining you to database structure installation. If your module requires additional initialization and updates, place those initialization items in hook_install() or hook_update_<N>(). This could be adding Drupal variables, creating a vocabulary with a set of default terms, or anything else you can imagine.
Maintaining database portability
Drupal's database abstraction layer is a thin veneer over PHP's database routines. This is great from a performance and complexity standpoint, but it can tempt you into writing nonportable database code. When we use the term database portability we mean code that uses the Drupal database abstraction layer properly and to its fullest potential, in addition to writing SQL that works across multiple databases. Your Drupal module could be used in a variety of situations and configurations, so it's important to design well and plan ahead.
The following examples show the difference between good and bad database calls.
Table names
Listing 8. Incorrect: Usage of plain table names
db_query("SELECT * FROM node");
|
Listing 9. Correct: Enclose names of all tables in curly brackets for table prefix to be honored
db_query("SELECT * FROM {node}");
|
Drupal allows the administrator to add prefixes to Drupal's tables. Prefixes allow the administrator to share tables between two Drupal sites, have more than one Drupal site contained in a single database, and in the case of many hosting environments, organize their database better. If the administrator has set the table prefix to be drupal_, then the node table is named drupal_node, and this query will fail because a table named node does not exist.
The query in Listing 9 will honor any database table prefix settings the Drupal site administrator has used. This simple change is very important when your module is used in complicated Drupal setups or in hosting environments that limit database functions.
Embedded parameters
Listing 10. Incorrect: Embedding parameters directly into the query string
db_query("SELECT * FROM {node} WHERE nid = $nid");
|
Listing 11. Correct: Use variable placeholders set to the appropriate data type
db_query("SELECT * FROM {node} WHERE nid = %d", $nid);
|
The main problem with this approach is that if the variable $nid contains any special characters, such as ' or ", you could become more vulnerable to an SQL injection attack. (This is where a malicious user tries to get more or different information out of the database in order to cause trouble.) To prevent this, you must escape any special characters before sending them to the database. Unfortunately, the method used to escape values varies from database to database, which means you would need to know which type of database Drupal was using and adjust your code accordingly. Fortunately, Drupal handles all of this for you if you use the db_query() function properly.
In Listing 11, now the variable $nid will be escaped according to the rules of the database type, and its value substituted into the query properly based on its type.
Range of values
Listing 12. Incorrect: Embedding MySQL's LIMIT clause in your query
db_query("SELECT * FROM {node} WHERE nid = %d LIMIT 1, 10", $nid);
|
Listing 13. Correct: Use db_query_range() to select ranges of values from the database
db_query_range("SELECT * FROM {node} WHERE nid = %d", $nid, 1, 10);
|
The LIMIT clause is not part of the SQL standard and is not portable across all databases. For instance, this query will not work on DB2 Express-C, and any modules that use a query of this form would have to be rewritten before being used.
The query in Listing 13 makes use of any database-specific SQL to retrieve the first 10 rows in the result set.
These are some of the many "best practices" in Drupal database usage. As Drupal evolves, so too will its database layer and the best methods for using it.
IBM DB2 Express-C
DB2 Express-C is a version of DB2 for the community. It is a fully functional version of the DB2 database server that can be used in everyday production and development environments. There are limitations to the amount of resources it can take advantage of (up to 2 dual-core CPUs, up to 4GB of RAM), but there are no other limitations to speak of. See Resources to find out more about DB2 Express-C .
Adding support for DB2 Express-C in Drupal is a good way to learn about the database abstraction layer in detail. We can also see how well the abstraction layer works while implementing a third database. In the following section we'll briefly describe how to install DB2 Express-C, and review how we created the necessary code for Drupal. It is important to understand that this code is just a starting point for the community and not a finished implementation.
Installation
Once you have the DB2 Express-C installation package downloaded to your local system, unpack the archive on your machine and double click db2setup.exe to begin the installation process. You should see the panel in Figure 6.
Figure 6. IBM DB2 Express-C welcome panel
Select Next to begin the installation process. You should now see the panel in Figure 7. The Typical installation type is sufficient for our needs. If you are low on space or are an expert, you can use the other installation methods. Choose Next to proceed.
Figure 7. Select installation type
Select the installation, response file, or both, as shown in Figure 8. Then select Next.
Figure 8. Select installation, response file, or both
Select the installation folder, as shown in Figure 9, and select Next.
Figure 9. Select installation folder
Enter the User name and Password for the administration server, as shown in Figure 10. Select Next to continue.
Figure 10. Set user information for DB2 Administration Server
Figure 11 shows how you can configure the DB2 instance. Select Next to continue.
Figure 11. Configure DB2 instances
Figure 12 is the last dialog before installation proceeds. Select Finish to start copying files.
Figure 12. Start copying files and create a response file
The dialog in Figure 13 allows you to create a First Steps browser profile.
Figure 13. Creating a DB2 First Steps browser profile
The installation of IBM DB2 Express-C is now complete.
Configuring DB2 and PHP
We must install an extension to PHP before it can talk to a DB2 database. There are two methods for connecting to DB2 from PHP. The first method is using the Open Database Connectivity (ODBC) PHP application program interface (API) and the underlying ODBC-compatible, database-specific module. This method is generic and portable, but can suffer from minor performance degradation.
The second method is to use the native DB2 module, which uses the DB2 Call Level Interface (CLI) and speaks directly to the database. Using this module allows you to take advantage of any DB2-specific functions while exposing the Drupal database API. See Resources for more information about the native DB2 module.
Installing this module is simple, but you should follow the instructions found on the module's reference page. (If you don't know how to install PHP extensions, see Resources for more information.) Before you can install the PHP DB2 extension, you must have a version of DB2 installed with the application development packages. The commands for the basic installation on Linux™ are shown in Listing 14.
Listing 14. Commands to build and install IBM DB2 PHP extension in Linux
$ cd ibm_db2
$ phpize
$ ./configure --with-IBM_DB2=/path/to/DB2
$ make
$ su - root
# make install
|
These commands initialize the PHP extension build environment, configure the build environment to use the specified version of DB2, and compile and install the extension. Depending on your distribution, you may have to vary the command in Listing 14.
If you are using Windows®, then you can download the IBM DB2 PHP extension.
Choose the extension for your version of PHP and save the file in the extensions directory. If you followed the steps in Part 3 of this series, that directory is C:\PHP\ext. As soon as you have the file saved, add the line in Listing 15 to your php.ini file where the other extensions are enabled.
Listing 15. Enabling the IBM DB2 PHP extension
extension=php_ibm_db2.dll
|
Once you have the IBM DB2 PHP extension installed, you need to tell PHP which DB2 instance it should use. Listing 16 shows the necessary information that must be added to the php.ini file.
Listing 16. Add IBM DB2 instance information to php.ini file
ibm_db2.instance_name = <name of your instance here>
|
DB2 should now be enabled and configured from within PHP. You can verify your installation by listing the extensions PHP knows about. You should see ibm_db2 in your list, similar to the list in Listing 17.
Listing 17. Verify successful installation of IBM DB2 PHP extension
[PHP Modules]
bcmath
calendar
com_dotnet
ctype
date
dom
ftp
hash
iconv
ibm_db2
ldap
libxml
mysql
odbc
pcre
Reflection
session
SimpleXML
SPL
standard
tokenizer
wddx
xml
xmlreader
xmlwriter
zlib
|
Drupal with IBM DB2 Express-C
Enabling Drupal to use a database back end other than MySQL or PostgreSQL involves a moderate amount of work. There are two development tasks that must be completed before your database can be used by Drupal. The first task is to generate the initial database schema script. The following files are used to initialize MySQL and PostgreSQL databases, respectively:
- database/database.4.1.mysql
- database/database.pgsql
These files contain the SQL table definitions and their initial values. Listing 18 shows the poll table for MySQL databases, and Listing 19 shows the same table for DB2 databases.
Listing 18. Poll table for MySQL databases
CREATE TABLE poll (
nid int(10) unsigned NOT NULL default '0',
runtime int(10) NOT NULL default '0',
active int(2) unsigned NOT NULL default '0',
PRIMARY KEY (nid)
)
DEFAULT CHARACTER SET utf8;
|
Listing 19. Poll table for DB2 databases
CREATE TABLE "POLL" (
"NID" INTEGER NOT NULL WITH DEFAULT 0,
"RUNTIME" INTEGER NOT NULL WITH DEFAULT 0,
"ACTIVE" INTEGER NOT NULL WITH DEFAULT 0)
IN "TS_DRPL"
;
|
Each database has its own peculiarities with the syntax, which means you'll have to translate the MySQL or PostgreSQL to your database's syntax.
The second task is to implement the PHP code that provides the database-specific functions. You can find the PHP code that supports MySQL in the file includes/database.mysql.inc. There are over a dozen functions that must be implemented specifically for your database. Finally, once you've completed these tasks, you must test your Drupal installation thoroughly to ensure your new database is being used properly.
In the following sections we describe the process and tools used to create the SQL initialization script and Drupal PHP database code. It is important to understand that this is only a jumping-off point for enabling Drupal to work with DB2 Express-C and not a complete, fully tested solution.
Translating the database schema
The first step in enabling a new database for Drupal is to translate the database schema to the appropriate form from MySQL. We recommend that you use whatever tools are available to automate this task; why spend hours at something that can be done by a machine in seconds? The IBM Migration Toolkit (MTK) provides an excellent starting point for this task. We used the MTK to successfully migrate an initial, empty Drupal database to a DB2 database in a matter of minutes. (Find out more about the MTK and its usage in Resources.)
While the MTK automated a tremendous amount of this process, it did not provide a complete initialization script that we could use to set up Drupal. Several issues were encountered in the migration process:
- Bufferpool and tablespace configuration
- Long Object (LOB) consistency
- Table attributes with default values
- Default data initialization
These items can be addressed quickly and simply. The first item we thought important was to create a bufferpool, tablespace, and temporary tablespace for the database, as shown in Listing 20.
Listing 20. Creating a bufferpool, tablespace, and temporary tablespace
------------------------------------------------
-- Change to your own bufferpool length and pagesize
------------------------------------------------
CREATE BUFFERPOOL "BP_DRPL" IMMEDIATE SIZE 1000 AUTOMATIC PAGESIZE 4K;
CREATE TABLESPACE "TS_DRPL" PAGESIZE 4K BUFFERPOOL "BP_DRPL";
CREATE SYSTEM TEMPORARY TABLESPACE "TMPTS_DRPL" PAGESIZE 4K BUFFERPOOL "BP_DRPL";
|
The final component of this item is to update the table creation definitions in order to place the tables in the new tablespace, TS_DRPL. You can do this by adding the fragment shown in Listing 21 to the CREATE TABLE directive for every table in the database.
Listing 21. Fragment to add to CREATE TABLE directive to place table in the proper tablespace
The next step is to change all of the various LOBs to be CLOBs with the appropriate size. We used a global find and replace to change all of our LOBs to be redefined, as shown in Listing 22.
Listing 22. Replacing LOBs to CLOBs
CLOB(10M) NOT LOGGED NOT COMPACT
|
There may be other clauses added to this, such as NOT NULL or WITH DEFAULT, depending on the original MySQL table definition.
The third task is to add the necessary WITH DEFAULT clauses to the database initialization script. This is the most tedious segment of the migration process. We went through table by table, adding the necessary default value clauses based on the original MySQL initialization script. This is because Drupal makes extensive use of the default values in its queries; without them Drupal will not function properly.
Finally, the MySQL initialization script inserts many default values into the newly created tables. We duplicated this for the DB2 initialization script to be consistent with the Drupal setup mechanism. You can download the completed initialization script now.
Creating the database interface file
The next step in the process is to create the necessary PHP code that interacts with the database. This code follows a predefined interface of functions in includes/database.mysql.inc. We began the implementation process by copying the complete MySQL implementation file to includes/database.ibmdb2.inc. Notice the ibmdb2 in the filename. This token is used as the database type identifier that's used in the database connection string that is found in the sites/.../settings.php file. Implementing the Drupal database interface using the PHP DB2 module is relatively straightforward, but there are a couple of "gotcha's" that need to be addressed.
- Implementing
db_query_range()
- MySQL provides a clause that lets you limit the number of results returned from a query. These results can be from the beginning, middle, or end of the results returned. DB2 Express-C does not provide the same syntactical element as MySQL; this function must be implemented using the form in Listing 23.
Listing 23. Implementing the db_query_range equivalent
SELECT c1, c2, ... FROM (SELECT ROW_NUMBER() OVER() AS rn, t.* FROM (<original query>)
AS X WHERE rn BETWEEN n AND m
|
This function uses the ROW_NUMBER() OVER() structure to add the necessary range information to the rows returned from the original query. The limit is achieved using the BETWEEN n AND m structure, and is analogous to the LIMIT num OFFSET o. Listing 24 shows the relevant section of code for the db_query_range() function.
Listing 24. Relevant code for db_query_range function
$query = preg_replace("/SELECT/i", "SELECT O.* FROM
(SELECT I.*, ROW_NUMBER() OVER () sys_row_num FROM (SELECT", $query);
if(!$query) {
return false;
}
$last_record = $count + $from;
$query .= ") AS I) AS O WHERE sys_row_num BETWEEN $from AND $last_record";
|
This piece of code creates the proper limiting query that is eventually sent to the database specific implementation found in _db_query().
- Implementing
db_num_rows()
- After reading the documentation of the
mysql_num_rows() and db2_num_rows() functions, you'll notice that they are used to solve different problems. The DB2 function is more like mysql_affected_rows(), used after an UPDATE, INSERT or DELETE query. In order to implement the Drupal db_num_rows() function, we need to rerun the last query that requests the number of results and not the results themselves. This is a two-step process. The first step requires a small modification to the _db_query() function, as shown in Listing 25.
Listing 25. Modification of _db_query() for implementing db_num_rows function
global $last_query;
if ($result) {
$last_query = $query;
return $query
}
|
This code records the last query issued in a global variable so that it can be reused in calls to db_num_rows().
The second step is to count the number of results returned by the query stored in the first step. You can see the usage in the code snippet in Listing 26.
Listing 26. Code snippet for query results
return db_result(db_query("SELECT COUNT(*) FROM ($last_query) AS C"));
|
This function issues the same query but only requests the number of results found. This implementation is not ideal because it uses global variables to persist information across function calls, but it is an effective method to support DB2 Express-C in Drupal without modifying the core Drupal database abstraction layer.
See Resources for complete information about our initial implementation of the Drupal database abstraction layer for IBM DB2 Express-C.
Summary
In this article you explored the Drupal database abstraction layer, which allows Drupal to support many different databases through one common API. You also learned about the Drupal pager system, which allows you to navigate large datasets in a page-oriented manner. The pager system is an example application of the database abstraction layer. You discovered how to begin enabling Drupal to use IBM DB2 Express-C, the free version of IBM's DB2 product. And you also learned how to write portable database code as well as how to install and maintain your module's database schema from one version to another.
Stay tuned for the next article in this series. You'll see our definition of an extranet as it applies to the client requirements and learn the implementation techniques used to create the IBC site as an extranet Web site.
Download | Description | Name | Size | Download method |
|---|
| ibmdb2 code | i-osource9code.zip | 9KB | HTTP |
|---|
Resources Learn
Get products and technologies
Discuss
About the authors | 
| | Alister Lewis-Bowen is a senior software engineer in IBM's Internet Technology Group. He has worked on Internet and
Web technologies as an IBM UK employee since 1993. Alister was brought to the U.S. to work on the Web sites for the
IBM-sponsored sports events, then as senior Webmaster for ibm.com. He is currently helping create semantic Web prototypes. Contact Alister at alister@us.ibm.com. |
| 
| | Stephen Evanchik is a software engineer in IBM's Internet Technology Group.
He has been a contributor to many open source software projects, the most notable being his IBM TrackPoint driver in the
Linux kernel. Stephen is currently working with emerging semantic Web technologies. Contact Stephen at evanchik@us.ibm.com. |
| 
| | Louis Weitzman is a senior software engineer in IBM's Internet Technology Group. For 30 years
he has worked at the intersection of design and computation. He helped develop an
XML, fragment-based content management system in use by ibm.com, and currently is involved with
bringing the design process to emerging projects. Contact Louis at louisw@us.ibm.com. |
Rate this page
| |
