Database abstraction layer


Enumerations

enum  DB_QUERY_REGEXP

Functions

 update_sql ($sql)
 db_prefix_tables ($sql)
 db_set_active ($name= 'default')
 _db_error_page ($error= '')
 db_is_active ()
 _db_query_callback ($match, $init=FALSE)
 db_placeholders ($arguments, $type= 'int')
 _db_rewrite_sql ($query= '', $primary_table= 'n', $primary_field= 'nid', $args=array())
 db_rewrite_sql ($query, $primary_table= 'n', $primary_field= 'nid', $args=array())
 db_escape_table ($string)
 pager_query ($query, $limit=10, $element=0, $count_query=NULL)
 tablesort_sql ($header, $before= '')
 db_status_report ()
 db_version ()
 db_connect ($url)
 db_query ($query)
 _db_query ($query, $debug=0)
 db_fetch_object ($result)
 db_fetch_array ($result)
 db_result ($result)
 db_error ()
 db_last_insert_id ($table, $field)
 db_affected_rows ()
 db_query_range ($query)
 db_query_temporary ($query)
 db_encode_blob ($data)
 db_decode_blob ($data)
 db_escape_string ($text)
 db_lock_table ($table)
 db_unlock_tables ()
 db_table_exists ($table)
 db_column_exists ($table, $column)
 db_check_setup ()
 db_distinct_field ($table, $field, $query)

Detailed Description

Allow the use of different database servers using the same code base.

Drupal provides a slim database abstraction layer to provide developers with the ability to support multiple database servers easily. The intent of this layer is to preserve the syntax and power of SQL as much as possible, while letting Drupal control the pieces of queries that need to be written differently for different servers and provide basic security checks.

Most Drupal database queries are performed by a call to db_query() or db_query_range(). Module authors should also consider using pager_query() for queries that return results that need to be presented on multiple pages, and tablesort_sql() for generating appropriate queries for sortable tables.

For example, one might wish to return a list of the most recent 10 nodes authored by a given user. Instead of directly issuing the SQL query 

   SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
one would instead call the Drupal functions: 
   $result = db_query_range('SELECT n.title, n.body, n.created
     FROM {node} n WHERE n.uid = %d', $uid, 0, 10);
   while ($node = db_fetch_object($result)) {
     // Perform operations on $node->body, etc. here.
   }
Curly braces are used around "node" to provide table prefixing via db_prefix_tables(). The explicit use of a user ID is pulled out into an argument passed to db_query() so that SQL injection attacks from user input can be caught and nullified. The LIMIT syntax varies between database servers, so that is abstracted into db_query_range() arguments. Finally, note the common pattern of iterating over the result set using db_fetch_object().

Enumeration Type Documentation

enum DB_QUERY_REGEXP

Indicates the place holders that should be replaced in _db_query_callback().

Function Documentation

_db_error_page ( $  error = ''  ) 

Helper function to show fatal database errors.

Prints a themed maintenance page with the 'Site off-line' text, adding the provided error message in the case of 'display_errors' set to on. Ends the page request; no return.

Parameters:
$error The error message to be appended if 'display_errors' is on.

_db_query ( $  query,
$  debug = 0 
)

Helper function for db_query().

_db_query_callback ( $  match,
$  init = FALSE 
)

Helper function for db_query().

_db_rewrite_sql ( $  query = '',
$  primary_table = 'n',
$  primary_field = 'nid',
$  args = array() 
)

Helper function for db_rewrite_sql.

Collects JOIN and WHERE statements via hook_db_rewrite_sql() Decides whether to select primary_key or DISTINCT(primary_key)

Parameters:
$query Query to be rewritten.
$primary_table Name or alias of the table which has the primary key field for this query. Typical table names would be: {blocks}, {comments}, {forum}, {node}, {menu}, {term_data} or {vocabulary}. However, in most cases the usual table alias (b, c, f, n, m, t or v) is used instead of the table name.
$primary_field Name of the primary field.
$args Array of additional arguments.
Returns:
An array: join statements, where statements, field or DISTINCT(field).

db_affected_rows (  ) 

Determine the number of rows changed by the preceding query.

db_check_setup (  ) 

Verify if the database is set up correctly.

db_column_exists ( $  table,
$  column 
)

Check if a column exists in the given table.

db_connect ( $  url  ) 

Initialize a database connection.

db_decode_blob ( $  data  ) 

Returns text from a Binary Large OBject value. In case of PostgreSQL decodes data after select from bytea field.

Parameters:
$data Data to decode.
Returns:
Decoded data.

db_distinct_field ( $  table,
$  field,
$  query 
)

Wraps the given table.field entry with a DISTINCT(). The wrapper is added to the SELECT list entry of the given query and the resulting query is returned. This function only applies the wrapper if a DISTINCT doesn't already exist in the query.

Parameters:
$table Table containing the field to set as DISTINCT
$field Field to set as DISTINCT
$query Query to apply the wrapper to
Returns:
SQL query with the DISTINCT wrapper surrounding the given table.field.

db_encode_blob ( $  data  ) 

Returns a properly formatted Binary Large OBject value. In case of PostgreSQL encodes data for insert into bytea field.

Parameters:
$data Data to encode.
Returns:
Encoded data.

db_error (  ) 

Determine whether the previous query caused an error.

db_escape_string ( $  text  ) 

Prepare user input for use in a database query, preventing SQL injection attacks. Note: This function requires PostgreSQL 7.2 or later.

db_escape_table ( $  string  ) 

Restrict a dynamic table, column or constraint name to safe characters.

Only keeps alphanumeric and underscores.

db_fetch_array ( $  result  ) 

Fetch one result row from the previous query as an array.

Parameters:
$result A database query result resource, as returned from db_query().
Returns:
An associative array representing the next row of the result, or FALSE. The keys of this object are the names of the table fields selected by the query, and the values are the field values for this result row.

db_fetch_object ( $  result  ) 

Fetch one result row from the previous query as an object.

Parameters:
$result A database query result resource, as returned from db_query().
Returns:
An object representing the next row of the result, or FALSE. The attributes of this object are the table fields selected by the query.

db_is_active (  ) 

Returns a boolean depending on the availability of the database.

db_last_insert_id ( $  table,
$  field 
)

Returns the last insert id. This function is thread safe.

Parameters:
$table The name of the table you inserted into.
$field The name of the autoincrement field.

db_lock_table ( $  table  ) 

Lock a table. This function automatically starts a transaction.

db_placeholders ( $  arguments,
$  type = 'int' 
)

Generate placeholders for an array of query arguments of a single type.

Given a Schema API field type, return correct -placeholders to embed in a query

Parameters:
$arguments An array with at least one element.
$type The Schema API type of a field (e.g. 'int', 'text', or 'varchar').

db_prefix_tables ( $  sql  ) 

Append a database prefix to all tables in a query.

Queries sent to Drupal should wrap all table names in curly brackets. This function searches for this syntax and adds Drupal's table prefix to all tables, allowing Drupal to coexist with other systems in the same database if necessary.

Parameters:
$sql A string containing a partial or entire SQL query.
Returns:
The properly-prefixed string.

db_query ( $  query  ) 

Runs a basic query in the active database.

User-supplied arguments to the query should be passed in as separate parameters so that they can be properly escaped to avoid SQL injection attacks.

Parameters:
$query A string containing an SQL query.
... A variable number of arguments which are substituted into the query using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments.
Valid -modifiers are: s, d, f, b (binary data, do not enclose in '') and %%.

NOTE: using this syntax will cast NULL and FALSE values to decimal 0, and TRUE values to decimal 1.

Returns:
A database query result resource, or FALSE if the query was not executed correctly.

db_query_range ( $  query  ) 

Runs a limited-range query in the active database.

Use this as a substitute for db_query() when a subset of the query is to be returned. User-supplied arguments to the query should be passed in as separate parameters so that they can be properly escaped to avoid SQL injection attacks.

Parameters:
$query A string containing an SQL query.
... A variable number of arguments which are substituted into the query using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments. Valid -modifiers are: s, d, f, b (binary data, do not enclose in '') and %%.
NOTE: using this syntax will cast NULL and FALSE values to decimal 0, and TRUE values to decimal 1.

Parameters:
$from The first result row to return.
$count The maximum number of result rows to return.
Returns:
A database query result resource, or FALSE if the query was not executed correctly.

db_query_temporary ( $  query  ) 

Runs a SELECT query and stores its results in a temporary table.

Use this as a substitute for db_query() when the results need to stored in a temporary table. Temporary tables exist for the duration of the page request. User-supplied arguments to the query should be passed in as separate parameters so that they can be properly escaped to avoid SQL injection attacks.

Note that if you need to know how many results were returned, you should do a SELECT COUNT(*) on the temporary table afterwards. db_affected_rows() does not give consistent result across different database types in this case.

Parameters:
$query A string containing a normal SELECT SQL query.
... A variable number of arguments which are substituted into the query using printf() syntax. The query arguments can be enclosed in one array instead. Valid -modifiers are: s, d, f, b (binary data, do not enclose in '') and %%.
NOTE: using this syntax will cast NULL and FALSE values to decimal 0, and TRUE values to decimal 1.

Parameters:
$table The name of the temporary table to select into. This name will not be prefixed as there is no risk of collision.
Returns:
A database query result resource, or FALSE if the query was not executed correctly.

db_result ( $  result  ) 

Return an individual result field from the previous query.

Only use this function if exactly one field is being selected; otherwise, use db_fetch_object() or db_fetch_array().

Parameters:
$result A database query result resource, as returned from db_query().
Returns:
The resulting field or FALSE.

db_rewrite_sql ( $  query,
$  primary_table = 'n',
$  primary_field = 'nid',
$  args = array() 
)

Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not use FROM table1, table2 syntax, use JOIN instead.

Parameters:
$query Query to be rewritten.
$primary_table Name or alias of the table which has the primary key field for this query. Typical table names would be: {blocks}, {comments}, {forum}, {node}, {menu}, {term_data} or {vocabulary}. However, it is more common to use the the usual table aliases: b, c, f, n, m, t or v.
$primary_field Name of the primary field.
$args An array of arguments, passed to the implementations of hook_db_rewrite_sql.
Returns:
The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.

db_set_active ( $  name = 'default'  ) 

Activate a database for future queries.

If it is necessary to use external databases in a project, this function can be used to change where database queries are sent. If the database has not yet been used, it is initialized using the URL specified for that name in Drupal's configuration file. If this name is not defined, a duplicate of the default connection is made instead.

Be sure to change the connection back to the default when done with custom code.

Parameters:
$name The name assigned to the newly active database connection. If omitted, the default connection will be made active.
Returns:
the name of the previously active database or FALSE if non was found.

db_status_report (  ) 

Report database status.

db_table_exists ( $  table  ) 

Check if a table exists.

db_unlock_tables (  ) 

Unlock all locked tables. This function automatically commits a transaction.

db_version (  ) 

Returns the version of the database server currently in use.

Returns:
Database server version

pager_query ( $  query,
$  limit = 10,
$  element = 0,
$  count_query = NULL 
)

Perform a paged database query.

Use this function when doing select queries you wish to be able to page. The pager uses LIMIT-based queries to fetch only the records required to render a certain page. However, it has to learn the total number of records returned by the query to compute the number of pages (the number of records / records per page). This is done by inserting "COUNT(*)" in the original query. For example, the query "SELECT nid, type FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC" would be rewritten to read "SELECT COUNT(*) FROM node WHERE status = '1' ORDER BY sticky DESC, created DESC". Rewriting the query is accomplished using a regular expression.

Unfortunately, the rewrite rule does not always work as intended for queries that already have a "COUNT(*)" or a "GROUP BY" clause, and possibly for other complex queries. In those cases, you can optionally pass a query that will be used to count the records.

For example, if you want to page the query "SELECT COUNT(*), TYPE FROM node GROUP BY TYPE", pager_query() would invoke the incorrect query "SELECT COUNT(*) FROM node GROUP BY TYPE". So instead, you should pass "SELECT COUNT(DISTINCT(TYPE)) FROM node" as the optional $count_query parameter.

Parameters:
$query The SQL query that needs paging.
$limit The number of query results to display per page.
$element An optional integer to distinguish between multiple pagers on one page.
$count_query An SQL query used to count matching records.
... A variable number of arguments which are substituted into the query (and the count query) using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments.
Returns:
A database query result resource, or FALSE if the query was not executed correctly.

tablesort_sql ( $  header,
$  before = '' 
)

Create an SQL sort clause.

This function produces the ORDER BY clause to insert in your SQL queries, assuring that the returned database table rows match the sort order chosen by the user.

Parameters:
$header An array of column headers in the format described in theme_table().
$before An SQL string to insert after ORDER BY and before the table sorting code. Useful for sorting by important attributes like "sticky" first.
Returns:
An SQL string to append to the end of a query.

update_sql ( $  sql  ) 

Perform an SQL query and return success or failure.

Parameters:
$sql A string containing a complete SQL query. -substitution parameters are not supported.
Returns:
An array containing the keys: success: a boolean indicating whether the query succeeded query: the SQL query executed, passed through check_plain()