home *** CD-ROM | disk | FTP | other *** search
<?php // $Id: database.inc,v 1.92.2.1 2008/02/08 22:44:59 goba Exp $ /** * @file * Wrapper for database interface code. */ /** * A hash value to check when outputting database errors, md5('DB_ERROR'). * * @see drupal_error_handler() */ define('DB_ERROR', 'a515ac9c2796ca0e23adbe92c68fc9fc'); /** * @defgroup database Database abstraction layer * @{ * 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 * @code * SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10; * @endcode * one would instead call the Drupal functions: * @code * $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. * } * @endcode * 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(). */ /** * Perform an SQL query and return success or failure. * * @param $sql * A string containing a complete SQL query. %-substitution * parameters are not supported. * @return * An array containing the keys: * success: a boolean indicating whether the query succeeded * query: the SQL query executed, passed through check_plain() */ function update_sql($sql) { $result = db_query($sql, true); return array('success' => $result !== FALSE, 'query' => check_plain($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. * * @param $sql * A string containing a partial or entire SQL query. * @return * The properly-prefixed string. */ function db_prefix_tables($sql) { global $db_prefix; if (is_array($db_prefix)) { if (array_key_exists('default', $db_prefix)) { $tmp = $db_prefix; unset($tmp['default']); foreach ($tmp as $key => $val) { $sql = strtr($sql, array('{'. $key .'}' => $val . $key)); } return strtr($sql, array('{' => $db_prefix['default'], '}' => '')); } else { foreach ($db_prefix as $key => $val) { $sql = strtr($sql, array('{'. $key .'}' => $val . $key)); } return strtr($sql, array('{' => '', '}' => '')); } } else { return strtr($sql, array('{' => $db_prefix, '}' => '')); } } /** * 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. * * @param $name * The name assigned to the newly active database connection. If omitted, the * default connection will be made active. * * @return the name of the previously active database or FALSE if non was found. */ function db_set_active($name = 'default') { global $db_url, $db_type, $active_db; static $db_conns, $active_name = FALSE; if (empty($db_url)) { include_once 'includes/install.inc'; install_goto('install.php'); } if (!isset($db_conns[$name])) { // Initiate a new connection, using the named DB URL specified. if (is_array($db_url)) { $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default']; } else { $connect_url = $db_url; } $db_type = substr($connect_url, 0, strpos($connect_url, '://')); $handler = "./includes/database.$db_type.inc"; if (is_file($handler)) { include_once $handler; } else { _db_error_page("The database type '". $db_type ."' is unsupported. Please use either 'mysql' or 'mysqli' for MySQL, or 'pgsql' for PostgreSQL databases."); } $db_conns[$name] = db_connect($connect_url); } $previous_name = $active_name; // Set the active connection. $active_name = $name; $active_db = $db_conns[$name]; return $previous_name; } /** * 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. * * @param $error * The error message to be appended if 'display_errors' is on. */ function _db_error_page($error = '') { global $db_type; drupal_maintenance_theme(); drupal_set_header('HTTP/1.1 503 Service Unavailable'); drupal_set_title('Site off-line'); $message = '<p>The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.</p>'; $message .= '<hr /><p><small>If you are the maintainer of this site, please check your database settings in the <code>settings.php</code> file and ensure that your hosting provider\'s database server is running. For more help, see the <a href="http://drupal.org/node/258">handbook</a>, or contact your hosting provider.</small></p>'; if ($error && ini_get('display_errors')) { $message .= '<p><small>The '. theme('placeholder', $db_type) .' error was: '. theme('placeholder', $error) .'.</small></p>'; } print theme('maintenance_page', $message); exit; } /** * Returns a boolean depending on the availability of the database. */ function db_is_active() { global $active_db; return !empty($active_db); } /** * Helper function for db_query(). */ function _db_query_callback($match, $init = FALSE) { static $args = NULL; if ($init) { $args = $match; return; } switch ($match[1]) { case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?) return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe case '%s': return db_escape_string(array_shift($args)); case '%%': return '%'; case '%f': return (float) array_shift($args); case '%b': // binary data return db_encode_blob(array_shift($args)); } } /** * 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 * * @param $arguments * An array with at least one element. * @param $type * The Schema API type of a field (e.g. 'int', 'text', or 'varchar'). */ function db_placeholders($arguments, $type = 'int') { $placeholder = db_type_placeholder($type); return implode(',', array_fill(0, count($arguments), $placeholder)); } /** * Indicates the place holders that should be replaced in _db_query_callback(). */ define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/'); /** * 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) * * @param $query * Query to be rewritten. * @param $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. * @param $primary_field * Name of the primary field. * @param $args * Array of additional arguments. * @return * An array: join statements, where statements, field or DISTINCT(field). */ function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) { $where = array(); $join = array(); $distinct = FALSE; foreach (module_implements('db_rewrite_sql') as $module) { $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args); if (isset($result) && is_array($result)) { if (isset($result['where'])) { $where[] = $result['where']; } if (isset($result['join'])) { $join[] = $result['join']; } if (isset($result['distinct']) && $result['distinct']) { $distinct = TRUE; } } elseif (isset($result)) { $where[] = $result; } } $where = empty($where) ? '' : '('. implode(') AND (', $where) .')'; $join = empty($join) ? '' : implode(' ', $join); return array($join, $where, $distinct); } /** * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not * use FROM table1, table2 syntax, use JOIN instead. * * @param $query * Query to be rewritten. * @param $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. * @param $primary_field * Name of the primary field. * @param $args * An array of arguments, passed to the implementations of hook_db_rewrite_sql. * @return * The original query with JOIN and WHERE statements inserted from * hook_db_rewrite_sql implementations. nid is rewritten if needed. */ function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) { list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args); if ($distinct) { $query = db_distinct_field($primary_table, $primary_field, $query); } if (!empty($where) || !empty($join)) { $pattern = '{ # Beginning of the string ^ ((?P<anonymous_view> # Everything within this set of parentheses is named "anonymous view" (?: [^()]++ # anything not parentheses | \( (?P>anonymous_view) \) # an open parenthesis, more "anonymous view" and finally a close parenthesis. )* )[^()]+WHERE) }x'; preg_match($pattern, $query, $matches); if (!$where) { $where = '1 = 1'; } if ($matches) { $n = strlen($matches[1]); $second_part = substr($query, $n); $first_part = substr($matches[1], 0, $n - 5) ." $join WHERE $where AND ( "; // PHP 4 does not support strrpos for strings. We emulate it. $haystack_reverse = strrev($second_part); } else { $haystack_reverse = strrev($query); } // No need to use strrev on the needle, we supply GROUP, ORDER, LIMIT // reversed. foreach (array('PUORG', 'REDRO', 'TIMIL') as $needle_reverse) { $pos = strpos($haystack_reverse, $needle_reverse); if ($pos !== FALSE) { // All needles are five characters long. $pos += 5; break; } } if ($matches) { if ($pos === FALSE) { $query = $first_part . $second_part .')'; } else { $query = $first_part . substr($second_part, 0, -$pos) .')'. substr($second_part, -$pos); } } elseif ($pos === FALSE) { $query .= " $join WHERE $where"; } else { $query = substr($query, 0, -$pos) . " $join WHERE $where " . substr($query, -$pos); } } return $query; } /** * Restrict a dynamic table, column or constraint name to safe characters. * * Only keeps alphanumeric and underscores. */ function db_escape_table($string) { return preg_replace('/[^A-Za-z0-9_]+/', '', $string); } /** * @} End of "defgroup database". */ /** * @defgroup schemaapi Schema API * @{ * * A Drupal schema definition is an array structure representing one or * more tables and their related keys and indexes. A schema is defined by * hook_schema(), which usually lives in a modulename.install file. * * By implementing hook_schema() and specifying the tables your module * declares, you can easily create and drop these tables on all * supported database engines. You don't have to deal with the * different SQL dialects for table creation and alteration of the * supported database engines. * * hook_schema() should return an array with a key for each table that * the module defines. * * The following keys are defined: * * - 'description': A string describing this table and its purpose. * References to other tables should be enclosed in * curly-brackets. For example, the node_revisions table * description field might contain "Stores per-revision title and * body data for each {node}." * - 'fields': An associative array ('fieldname' => specification) * that describes the table's database columns. The specification * is also an array. The following specification parameters are defined: * * - 'description': A string describing this field and its purpose. * References to other tables should be enclosed in * curly-brackets. For example, the node table vid field * description might contain "Always holds the largest (most * recent) {node_revisions}.vid value for this nid." * - 'type': The generic datatype: 'varchar', 'int', 'serial' * 'float', 'numeric', 'text', 'blob' or 'datetime'. Most types * just map to the according database engine specific * datatypes. Use 'serial' for auto incrementing fields. This * will expand to 'int auto_increment' on mysql. * - 'size': The data size: 'tiny', 'small', 'medium', 'normal', * 'big'. This is a hint about the largest value the field will * store and determines which of the database engine specific * datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). * 'normal', the default, selects the base type (e.g. on MySQL, * INT, VARCHAR, BLOB, etc.). * * Not all sizes are available for all data types. See * db_type_map() for possible combinations. * - 'not null': If true, no NULL values will be allowed in this * database column. Defaults to false. * - 'default': The field's default value. The PHP type of the * value matters: '', '0', and 0 are all different. If you * specify '0' as the default value for a type 'int' field it * will not work because '0' is a string containing the * character "zero", not an integer. * - 'length': The maximal length of a type 'varchar' or 'text' * field. Ignored for other field types. * - 'unsigned': A boolean indicating whether a type 'int', 'float' * and 'numeric' only is signed or unsigned. Defaults to * FALSE. Ignored for other field types. * - 'precision', 'scale': For type 'numeric' fields, indicates * the precision (total number of significant digits) and scale * (decimal digits right of the decimal point). Both values are * mandatory. Ignored for other field types. * * All parameters apart from 'type' are optional except that type * 'numeric' columns must specify 'precision' and 'scale'. * * - 'primary key': An array of one or more key column specifiers (see below) * that form the primary key. * - 'unique key': An associative array of unique keys ('keyname' => * specification). Each specification is an array of one or more * key column specifiers (see below) that form a unique key on the table. * - 'indexes': An associative array of indexes ('indexame' => * specification). Each specification is an array of one or more * key column specifiers (see below) that form an index on the * table. * * A key column specifier is either a string naming a column or an * array of two elements, column name and length, specifying a prefix * of the named column. * * As an example, here is a SUBSET of the schema definition for * Drupal's 'node' table. It show four fields (nid, vid, type, and * title), the primary key on field 'nid', a unique key named 'vid' on * field 'vid', and two indexes, one named 'nid' on field 'nid' and * one named 'node_title_type' on the field 'title' and the first four * bytes of the field 'type': * * @code * $schema['node'] = array( * 'fields' => array( * 'nid' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE), * 'vid' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0), * 'type' => array('type' => 'varchar', 'length' => 32, 'not null' => TRUE, 'default' => ''), * 'title' => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''), * ), * 'primary key' => array('nid'), * 'unique keys' => array( * 'vid' => array('vid') * ), * 'indexes' => array( * 'nid' => array('nid'), * 'node_title_type' => array('title', array('type', 4)), * ), * ); * @endcode * * @see drupal_install_schema() */ /** * Create a new table from a Drupal table definition. * * @param $ret * Array to which query results will be added. * @param $name * The name of the table to create. * @param $table * A Schema API table definition array. */ function db_create_table(&$ret, $name, $table) { $statements = db_create_table_sql($name, $table); foreach ($statements as $statement) { $ret[] = update_sql($statement); } } /** * Return an array of field names from an array of key/index column specifiers. * * This is usually an identity function but if a key/index uses a column prefix * specification, this function extracts just the name. * * @param $fields * An array of key/index column specifiers. * @return * An array of field names. */ function db_field_names($fields) { $ret = array(); foreach ($fields as $field) { if (is_array($field)) { $ret[] = $field[0]; } else { $ret[] = $field; } } return $ret; } /** * Given a Schema API field type, return the correct %-placeholder. * * Embed the placeholder in a query to be passed to db_query and and pass as an * argument to db_query a value of the specified type. * * @param $type * The Schema API type of a field. * @return * The placeholder string to embed in a query for that type. */ function db_type_placeholder($type) { switch ($type) { case 'varchar': case 'char': case 'text': case 'datetime': return '\'%s\''; case 'numeric': // For 'numeric' values, we use '%s', not '\'%s\'' as with // string types, because numeric values should not be enclosed // in quotes in queries (though they can be, at least on mysql // and pgsql). Numerics should only have [0-9.+-] and // presumably no db's "escape string" function will mess with // those characters. return '%s'; case 'serial': case 'int': return '%d'; case 'float': return '%f'; case 'blob': return '%b'; } // There is no safe value to return here, so return something that // will cause the query to fail. return 'unsupported type '. $type .'for db_type_placeholder'; } /** * @} End of "defgroup schemaapi". */ 
