A common problem for programmers is handling inserts of new database rows and updates to existing rows. The code
typically tests whether the operation is an insert or an update, then performs the appropriate operation.
Because each table that Drupal uses is described using a schema, Drupal knows what fields a table has and what the
default values are for each field. By passing a keyed array of fields and values to drupal_write_record(), you can let
Drupal generate and execute the SQL instead of writing it by hand. Suppose you have a table that keeps track of your
collection of giant bunnies. The schema hook for your module that describes the table looks like this:
/**
* Implements hook_schema().
*/
function bunny_schema()
{
$schema['bunnies'] = array('description' => t('Stores information about giant rabbits.'), 'fields' => array('bid' => array(
'type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE,'description' => t("Primary key: A unique ID for each
bunny."),),'name' => array('type' => 'varchar', 'length' => 64,'not null' => TRUE,'description' => t("Each bunny gets a name."),),'tons' => array( 'type' => 'int', 'unsigned' => TRUE,'not null' => TRUE,'description' => t('The weight of the bunny to the nearest ton.'),),), 'primary key' => array('bid'), 'indexes' => array('tons' => array('tons'),),);
return $schema;}
Inserting a new record is easy, as is updating a record:
$table = 'bunnies';
$record = new stdClass();
$record->name = t('Bortha');
$record->tons = 2; drupal_write_record($table, $record);
// The new bunny ID, $record->bid, was set by drupal_write_record()
// since $record is passed by reference.
watchdog('bunny', 'Added bunny with id %id.', array('%id' => $record->bid));
// Change our mind about the name.
$record->name = t('Bertha');
// Now update the record in the database.
// For updates we pass in the name of the table's primary key.
drupal_write_record($table, $record, 'bid');
watchdog('bunny', 'Updated bunny with id %id.', array('%id' => $record->bid));
Array syntax is also supported, though if $record is an array, drupal_write_record() will convert the array to an object internally.
The Schema API
Drupal supports multiple databases (MySQL, PostreSQL, SQLite, etc.) through its database abstraction layer. Each module
that wants to have a database table describes that table to Drupal using a schema definition. Drupal then translates the
definition intosyntax that is appropriate for the database.
Using Module .install Files
As shown in Chapter 2, when you write a module that needs to create one or more database tables for storage, the instructions to create and maintain the table structure go into an .install file that is distributed with the module.
Creating Tables
During the installation of a new module, Drupal automatically checks to see whether a schema definition exists in the modules .install file (see Figure 5-3). If a schema definition exists, Drupal creates the database table(s) defined with in the schema. The following example demonstrates the general structure of a schema definition.
$schema['tablename'] = array(
// Table description.
'description' => t('Description of what the table is used for.'), 'fields' => array(
// Field definition.
'field1' => array( 'type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,
'description' => t('Description of what this field is used for.'),),),
// Index declarations.
'primary key' => array('field1'),);
Figure 5-3. The schema definition is used to create the database tables.
Let’s take a look at the schema definition for Drupal’s book table, found in modules/book/book.install:
/**
* Implements hook_schema().
*/
function book_schema() {
$schema['book'] = array(
'description' => 'Stores book outline information. Uniquely connects each node in the outline to a link in
{menu_links}','fields' => array( 'mlid' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE,'default' => 0,
'description' => "The book page's {menu_links}.mlid.",),'nid' => array( 'type' => 'int', 'unsigned' => TRUE,
'not null' => TRUE, 'default' => 0,'description' => "The book page's {node}.nid.",),
'bid' => array( 'type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,
'description' => "The book ID is the {book}.nid of the top-level page.",),),
'primary key' => array('mlid'), 'unique keys' => array('nid' => array('nid'),),'indexes' => array('bid' => array('bid'),),);
return $schema;
This schema definition describes the book table, which has three fields of type int. It also has a primary key, a unique index
(which means all entries in that field are unique), and a regular index. Notice that when a field from another table is referred to inthe field description, curly brackets are used. That enables the schema module (see the next section) to build handy
hyperlinks to table descriptions.
Using the Schema Module
At this point, you may be thinking, “What a pain! Building these big descriptive arrays to tell Drupal about my tables is going to be sheer drudgery.” But do not fret. Simply download the schema module from http://drupal.org/project/schema and enable it onyour site. Going to Structure -> Schema will give you the ability to see a schema definition for any
database table by clicking the Inspect tab. So if you have used SQL to create your table, you can get the schema definition by using the schema module, and thencopy and paste it into your .install file.
The schema module also allows you to view the schema of any module. For example, Figure 5- 4 shows the schema
module’s display of the book module’s schema.Note how the table names that were in curly brackets in the table and field descriptions have been turned into helpful links.
Figure 5-4. The schema module displays the schema of the book module
Field Type Mapping from Schema to Database
The field type that is declared in the schema definition maps to a native field type in the database. For example, an
integer field with the declared size of tiny becomes a TINYINT field in MySQL but a small int field in PostgreSQL. The actual map can be viewed by printing the results of getFieldTypeMap() or by looking in Table 5-1 later in this chapter.
Textual
Textual fields contain text.
Varchar
The varchar, or variable length character field, is the most frequently used field type for storing text less than 256 characters in
length. A maximum length, in characters, is defined by the length key. MySQL varchar field lengths are 0-255 characters
(MySQL 5.0.2and earlier) and 0–65,535 characters (MySQL and later); PostgreSQL varchar field lengths may be larger.
$field['fieldname'] = array('type' => 'varchar', // Required.'length' => 255,
// Required. 'not null' => TRUE, // Defaults to FALSE.
'default' => 'chocolate', // See below.
'description' => t('Always state the purpose of your field.'),);
If the default key has not been set and the not null key has been set to FALSE, the default will be set to NULL.
Char
Char fields are fixed length character fields. The length of the field, in characters, is defined by the length key. MySQL char field
lengths are 0–255 characters.
$field['fieldname'] = array(
'type' => 'char', // Required.
'length' => 64, // Required.
'not null' => TRUE, // Defaults to FALSE.
'default' => 'strawberry', // See below.
'description' => t('Always state the purpose of your field.'),);
If the default key has not been set and the not null key has been set to FALSE, the default will be set to NULL.
Text
Text fields are used for textual data that can be quite large. For example, the body field of the node_revisions table (where node body text is stored) is of this type. Default values may not be used for text fields.
$field['fieldname'] = array(
'type' => 'text', // Required.
'size' => 'small', // tiny | small | normal | medium | big 'not null' => TRUE, // Defaults to FALSE.
'description' => t('Always state the purpose of your field.'),);
Numerical
Numerical data types are used for storing numbers and include the integer, serial, float, and numeric types.
Integer
This field type is used for storing integers, such as node IDs. If the unsigned key is TRUE, negative integers will not be allowed.
$field['fieldname'] = array(
'type' => 'int', // Required.
'unsigned' => TRUE, // Defaults to FALSE.
'size' => 'small', // tiny | small | medium | normal | big 'not null' => TRUE, // Defaults to FALSE.
'description' => t('Always state the purpose of your field.'),);
Serial
A serial field keeps a number that increments. For example, when a node is added, the nid field of the node table is incremented. This is done by inserting a row and calling db_last_insert_id(). If a row is added by another thread between the insertion of a row and
the retrieval of the last ID, the correct ID is still returned because it is tracked on a per-connection basis.
A serial field must be indexed; it is usually indexed as the primary key.
$field['fieldname'] = array(
'type' => 'serial', // Required.
'unsigned' => TRUE, // Defaults to FALSE. Serial numbers are usually positive.
'size' => 'small', // tiny | small | medium | normal | big
'not null' => TRUE, // Defaults to FALSE. Typically TRUE for serial fields.
'description' => t('Always state the purpose of your field.'),
);
Float
Floating point numbers are stored using the float data type. There is typically no difference between the tiny, small,
medium, and normal sizes for a floating point number; in contrast, the big size specifies a double-precision field.
$field['fieldname'] = array(
'type' => 'float', // Required.
'unsigned' => TRUE, // Defaults to FALSE.
'size' => 'normal', // tiny | small | medium | normal | big 'not null' => TRUE, // Defaults to FALSE.
'description' => t('Always state the purpose of your field.'),
);
Numeric
The numeric data type allows you to specify the precision and scale of a number. Precision is the total number of significant
digits in the number; scale is the total number of digits to the right of the decimal point. For example, 123.45 has a precision of 5 and a scale of 2. The size key is not used. At the time of this writing, numeric fields are not used in the schema of the Drupal
core.
$field['fieldname'] = array('type' => 'numeric', // Required.'unsigned' => TRUE, // Defaults to FALSE.
'precision' => 5, // Significant digits.'scale' => 2, // Digits to the right of the decimal.
'not null' => TRUE, // Defaults to FALSE.'description' => t('Always state the purpose of your field.'),);
Date and Time: Datetime
The Drupal core does not use this data type, preferring to use Unix timestamps in integer fields. The datetime format is a combined format containing both the date and the time.
$field['fieldname'] = array(
'type' => 'datetime', // Required.
'not null' => TRUE, // Defaults to FALSE.
'description' => t('Always state the purpose of your field.'),);
Binary: Blob
The binary large object data (blob) type is used to store binary data (for example, Drupal’s cache table to store the cached data). Binary data may include music, images, or video. Two sizes are available, normal and big.
$field['fieldname'] = array('type' => 'blob', // Required.
'size' => 'normal' // normal | big
'not null' => TRUE, // Defaults to FALSE.'description' => t('Always state the purpose of your field.'),);
Declaring a Specific Column Type with mysql_type
If you know the exact column type for your database engine, you can set the mysql_type (or pgsql_type) key in your schema
definition. This will override the type and size keys for that database engine. For example, MySQL has a field type called
TINYBLOB for small binary large objects. To specify that Drupal should use TINYBLOB if it is running on MySQL but fall back to using the regular BLOB type if it is running on a different database engine, the field could be declared like so:
'mysql_type' > 'TINYBLOB', // MySQL will use this.
'type' => 'blob', // Other databases will use this. 'size' => 'normal',
// Other databases will use this. 'not null' => TRUE,
'description' => t('Wee little blobs.'));
The native types for MySQL and PostgreSQL are shown in Table 5-1.
|
Table 5-1. How Type and Size Keys in Schema Definitions Map to Native
Database Types
|
||
|
Type Size MySQL
|
Postgre SQL
|
SQLite
|
|
varchar normal
|
VARCHAR varchar
|
VARCHAR
|
|
char normal
|
CHAR character
|
VARCHAR
|
|
text tiny
|
TINYTEXT text
|
TEXT
|
|
text small
|
TINYTEXT text
|
TEXT
|
|
text medium
|
MEDIUMTEXT text
|
TEXT
|
|
text big
|
LONGTEXT text
|
TEXT
|
|
text normal
|
TEXT text
|
TEXT
|
|
serial tiny
|
TINYINT serial
|
INTEGER
|
|
serial small
|
SMALLINT serial
|
INTEGER
|
|
serial medium
|
MEDIUMINT serial
|
INTEGER
|
|
serial big
|
BIGINT bigserial
|
INTEGER
|
|
serial normal
|
INT serial
|
INTEGER
|
|
int tiny
|
TINYINT smallint
|
INTEGER
|
|
int small
|
SMALLINT smallint
|
INTEGER
|
|
int medium
|
MEDIUMINT int
|
INTEGER
|
|
int big
|
BIGINT bigint
|
INTEGER
|
|
int normal
|
INT int
|
INTEGER
|
|
float tiny
|
FLOAT
real
|
FLOAT
|
|
float small
|
FLOAT real
|
FLOAT
|
|
float medium
|
FLOAT real precision
|
FLOAT
|
|
float big
|
DOUBLE double
|
FLOAT
|
|
float normal
|
FLOAT real
|
FLOAT
|
|
numeric normal
|
DECIMAL numeric
|
NUMERIC
|
|
blob big
|
LONGBLOB bytea
|
BLOB
|
|
blob normal
|
LONGBLOB bytea
|
BLOB
|
|
datetimedatetime normal
|
DATETIME timestamp
|
TIMESTAMP
|
Maintaining Tables
When you create a new version of a module, you might have to change the database schema. Perhaps you’ve added a
column or added an index to a column. You can’t just drop and recreate the table, because the table contains data. Here’s how to ensure that thedatabase is changed smoothly:
1. Update the hook_schema() implementation in your .install file so that new users who install your module will have
the new schema installed. The schema definition in your .install file will always be the latest schema for your module’s tables and fields.
2. Give existing users an upgrade path by writing an update function. Update functions are named sequentially, starting with a number that is based on the Drupal version. For example, the first update function for Drupal 7 would bemodulename_update_7000() and the second would be modulename_ update_7001(). Here’s an example from modules/comment/
comment.install where the table used to store comments was renamed from comments to comment:
/**
* Rename {comments} table to {comment}.
*/
function comment_update_7002() { db_rename_table('comments', 'comment');}
3. This function will be run when the user runs http://example.com/update.php after upgrading the module.
A full list of functions for dealing with schemas can be found at http://api.drupal.org/api/ group/schemaapi/7.
Deleting Tables on Uninstall
When a module is disabled, any data that the module has stored in the database is left untouched, in case the
administrator has a change of heart and reenables the module. The Modules page has an Uninstall tab that automatically
removes the data from thedatabase. You might want to delete any variables you’ve defined at the same time. Here’s an
example for the annotation module we wrote in Chapter 2:
/**
* Implements hook_uninstall().
*/
function annotate_uninstall() {
// Clean up our entry in the variables table.
variable_del('annotate_nodetypes');
}
Changing Existing Schemas with hook_schema_alter()
Generally modules create and use their own tables. But what if your module wants to alter an existing table? Suppose
your module absolutely has to add a column to the node table. The simple way would be to go to your database and add
the column. Butthen Drupal’s schema definitions, which should reflect the actual database table, would be inconsistent. There is a better way: hook_schema_alter().
Suppose you have a module that marks nodes in some way, and for performance reasons, you are dead set on using the
existing node table instead of using your own table and joining it using node IDs.
Your module will have to do two things: alter the node table during your module’s installation and modify the schema so
that it actually reflects what is in the database. The former is accomplished with hook_install(), the latter with hook_schema_alter().Assuming your module is called markednode.module, your markednode.install file would include the following
functions:
/**
* Implements hook_install().
*/
function markednode_install() {
$field = array( 'type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0,'initial' => 0, // Sets initial value for preexisting nodes.'description' => t('Whether the node has been marked by the markednode module.'),);
// Create a regular index called 'marked' on the field named 'marked'.
$keys['indexes'] = array( 'marked' => array('marked'));
db_add_field('node', 'marked', $field, $keys);}
/**
* Implements hook_schema_alter(). We alter $schema by reference.
*
* @param $schema
* The system-wide schema collected by drupal_get_schema().
*/
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 marked node 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 iscompiled. 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 ismodified 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.
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);}
...?>
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',
),
),
);
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');
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);
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.
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
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.
Không có nhận xét nào:
Đăng nhận xét