1 <?php

     2 // $Id: database.inc,v 1.92.2.5 2009/06/09 10:42:02 goba Exp $

     3 

     4 /**

     5  * @file

     6  * Wrapper for database interface code.

     7  */

     8 

     9 /**

    10  * A hash value to check when outputting database errors, md5('DB_ERROR').

    11  *

    12  * @see drupal_error_handler()

    13  */

    14 define('DB_ERROR', 'a515ac9c2796ca0e23adbe92c68fc9fc');

    15 

    16 /**

    17  * @defgroup database Database abstraction layer

    18  * @{

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

    20  *

    21  * Drupal provides a slim database abstraction layer to provide developers with

    22  * the ability to support multiple database servers easily. The intent of this

    23  * layer is to preserve the syntax and power of SQL as much as possible, while

    24  * letting Drupal control the pieces of queries that need to be written

    25  * differently for different servers and provide basic security checks.

    26  *

    27  * Most Drupal database queries are performed by a call to db_query() or

    28  * db_query_range(). Module authors should also consider using pager_query() for

    29  * queries that return results that need to be presented on multiple pages, and

    30  * tablesort_sql() for generating appropriate queries for sortable tables.

    31  *

    32  * For example, one might wish to return a list of the most recent 10 nodes

    33  * authored by a given user. Instead of directly issuing the SQL query

    34  * @code

    35  *   SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;

    36  * @endcode

    37  * one would instead call the Drupal functions:

    38  * @code

    39  *   $result = db_query_range('SELECT n.title, n.body, n.created

    40  *     FROM {node} n WHERE n.uid = %d', $uid, 0, 10);

    41  *   while ($node = db_fetch_object($result)) {

    42  *     // Perform operations on $node->body, etc. here.

    43  *   }

    44  * @endcode

    45  * Curly braces are used around "node" to provide table prefixing via

    46  * db_prefix_tables(). The explicit use of a user ID is pulled out into an

    47  * argument passed to db_query() so that SQL injection attacks from user input

    48  * can be caught and nullified. The LIMIT syntax varies between database servers,

    49  * so that is abstracted into db_query_range() arguments. Finally, note the

    50  * common pattern of iterating over the result set using db_fetch_object().

    51  */

    52 

    53 /**

    54  * Perform an SQL query and return success or failure.

    55  *

    56  * @param $sql

    57  *   A string containing a complete SQL query.  %-substitution

    58  *   parameters are not supported.

    59  * @return

    60  *   An array containing the keys:

    61  *      success: a boolean indicating whether the query succeeded

    62  *      query: the SQL query executed, passed through check_plain()

    63  */

    64 function update_sql($sql) {

    65   $result = db_query($sql, true);

    66   return array('success' => $result !== FALSE, 'query' => check_plain($sql));

    67 }

    68 

    69 /**

    70  * Append a database prefix to all tables in a query.

    71  *

    72  * Queries sent to Drupal should wrap all table names in curly brackets. This

    73  * function searches for this syntax and adds Drupal's table prefix to all

    74  * tables, allowing Drupal to coexist with other systems in the same database if

    75  * necessary.

    76  *

    77  * @param $sql

    78  *   A string containing a partial or entire SQL query.

    79  * @return

    80  *   The properly-prefixed string.

    81  */

    82 function db_prefix_tables($sql) {

    83   global $db_prefix;

    84 

    85   if (is_array($db_prefix)) {

    86     if (array_key_exists('default', $db_prefix)) {

    87       $tmp = $db_prefix;

    88       unset($tmp['default']);

    89       foreach ($tmp as $key => $val) {

    90         $sql = strtr($sql, array('{'. $key .'}' => $val . $key));

    91       }

    92       return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));

    93     }

    94     else {

    95       foreach ($db_prefix as $key => $val) {

    96         $sql = strtr($sql, array('{'. $key .'}' => $val . $key));

    97       }

    98       return strtr($sql, array('{' => '', '}' => ''));

    99     }

   100   }

   101   else {

   102     return strtr($sql, array('{' => $db_prefix, '}' => ''));

   103   }

   104 }

   105 

   106 /**

   107  * Activate a database for future queries.

   108  *

   109  * If it is necessary to use external databases in a project, this function can

   110  * be used to change where database queries are sent. If the database has not

   111  * yet been used, it is initialized using the URL specified for that name in

   112  * Drupal's configuration file. If this name is not defined, a duplicate of the

   113  * default connection is made instead.

   114  *

   115  * Be sure to change the connection back to the default when done with custom

   116  * code.

   117  *

   118  * @param $name

   119  *   The name assigned to the newly active database connection. If omitted, the

   120  *   default connection will be made active.

   121  *

   122  * @return the name of the previously active database or FALSE if non was found.

   123  */

   124 function db_set_active($name = 'default') {

   125   global $db_url, $db_type, $active_db;

   126   static $db_conns, $active_name = FALSE;

   127 

   128   if (empty($db_url)) {

   129     include_once 'includes/install.inc';

   130     install_goto('install.php');

   131   }

   132 

   133   if (!isset($db_conns[$name])) {

   134     // Initiate a new connection, using the named DB URL specified.

   135     if (is_array($db_url)) {

   136       $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];

   137     }

   138     else {

   139       $connect_url = $db_url;

   140     }

   141 

   142     $db_type = substr($connect_url, 0, strpos($connect_url, '://'));

   143     $handler = "./includes/database.$db_type.inc";

   144 

   145     if (is_file($handler)) {

   146       include_once $handler;

   147     }

   148     else {

   149       _db_error_page("The database type '". $db_type ."' is unsupported. Please use either 'mysql' or 'mysqli' for MySQL, or 'pgsql' for PostgreSQL databases.");

   150     }

   151 

   152     $db_conns[$name] = db_connect($connect_url);

   153   }

   154 

   155   $previous_name = $active_name;

   156   // Set the active connection.

   157   $active_name = $name;

   158   $active_db = $db_conns[$name];

   159 

   160   return $previous_name;

   161 }

   162 

   163 /**

   164  * Helper function to show fatal database errors.

   165  *

   166  * Prints a themed maintenance page with the 'Site off-line' text,

   167  * adding the provided error message in the case of 'display_errors'

   168  * set to on. Ends the page request; no return.

   169  *

   170  * @param $error

   171  *   The error message to be appended if 'display_errors' is on.

   172  */

   173 function _db_error_page($error = '') {

   174   global $db_type;

   175   drupal_init_language();

   176   drupal_maintenance_theme();

   177   drupal_set_header('HTTP/1.1 503 Service Unavailable');

   178   drupal_set_title('Site off-line');

   179 

   180   $message = '<p>The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.</p>';

   181   $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>';

   182 

   183   if ($error && ini_get('display_errors')) {

   184     $message .= '<p><small>The '. theme('placeholder', $db_type) .' error was: '. theme('placeholder', $error) .'.</small></p>';

   185   }

   186 

   187   print theme('maintenance_page', $message);

   188   exit;

   189 }

   190 

   191 /**

   192  * Returns a boolean depending on the availability of the database.

   193  */

   194 function db_is_active() {

   195   global $active_db;

   196   return !empty($active_db);

   197 }

   198 

   199 /**

   200  * Helper function for db_query().

   201  */

   202 function _db_query_callback($match, $init = FALSE) {

   203   static $args = NULL;

   204   if ($init) {

   205     $args = $match;

   206     return;

   207   }

   208 

   209   switch ($match[1]) {

   210     case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)

   211       return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe

   212     case '%s':

   213       return db_escape_string(array_shift($args));

   214     case '%n':

   215       // Numeric values have arbitrary precision, so can't be treated as float.

   216       // is_numeric() allows hex values (0xFF), but they are not valid.

   217       $value = trim(array_shift($args));

   218       return is_numeric($value) && !preg_match('/x/i', $value) ? $value : '0';

   219     case '%%':

   220       return '%';

   221     case '%f':

   222       return (float) array_shift($args);

   223     case '%b': // binary data

   224       return db_encode_blob(array_shift($args));

   225   }

   226 }

   227 

   228 /**

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

   230  *

   231  * Given a Schema API field type, return correct %-placeholders to

   232  * embed in a query

   233  *

   234  * @param $arguments

   235  *  An array with at least one element.

   236  * @param $type

   237  *   The Schema API type of a field (e.g. 'int', 'text', or 'varchar').

   238  */

   239 function db_placeholders($arguments, $type = 'int') {

   240   $placeholder = db_type_placeholder($type);

   241   return implode(',', array_fill(0, count($arguments), $placeholder));

   242 }

   243 

   244 /**

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

   246  */

   247 define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b|%n)/');

   248 

   249 /**

   250  * Helper function for db_rewrite_sql.

   251  *

   252  * Collects JOIN and WHERE statements via hook_db_rewrite_sql()

   253  * Decides whether to select primary_key or DISTINCT(primary_key)

   254  *

   255  * @param $query

   256  *   Query to be rewritten.

   257  * @param $primary_table

   258  *   Name or alias of the table which has the primary key field for this query.

   259  *   Typical table names would be: {blocks}, {comments}, {forum}, {node},

   260  *   {menu}, {term_data} or {vocabulary}. However, in most cases the usual

   261  *   table alias (b, c, f, n, m, t or v) is used instead of the table name.

   262  * @param $primary_field

   263  *   Name of the primary field.

   264  * @param $args

   265  *   Array of additional arguments.

   266  * @return

   267  *   An array: join statements, where statements, field or DISTINCT(field).

   268  */

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

   270   $where = array();

   271   $join = array();

   272   $distinct = FALSE;

   273   foreach (module_implements('db_rewrite_sql') as $module) {

   274     $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);

   275     if (isset($result) && is_array($result)) {

   276       if (isset($result['where'])) {

   277         $where[] = $result['where'];

   278       }

   279       if (isset($result['join'])) {

   280         $join[] = $result['join'];

   281       }

   282       if (isset($result['distinct']) && $result['distinct']) {

   283         $distinct = TRUE;

   284       }

   285     }

   286     elseif (isset($result)) {

   287       $where[] = $result;

   288     }

   289   }

   290 

   291   $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';

   292   $join = empty($join) ? '' : implode(' ', $join);

   293 

   294   return array($join, $where, $distinct);

   295 }

   296 

   297 /**

   298  * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not

   299  * use FROM table1, table2 syntax, use JOIN instead.

   300  *

   301  * @param $query

   302  *   Query to be rewritten.

   303  * @param $primary_table

   304  *   Name or alias of the table which has the primary key field for this query.

   305  *   Typical table names would be: {blocks}, {comments}, {forum}, {node},

   306  *   {menu}, {term_data} or {vocabulary}. However, it is more common to use the

   307  *   the usual table aliases: b, c, f, n, m, t or v.

   308  * @param $primary_field

   309  *   Name of the primary field.

   310  * @param $args

   311  *   An array of arguments, passed to the implementations of hook_db_rewrite_sql.

   312  * @return

   313  *   The original query with JOIN and WHERE statements inserted from

   314  *   hook_db_rewrite_sql implementations. nid is rewritten if needed.

   315  */

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

   317   list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);

   318 

   319   if ($distinct) {

   320     $query = db_distinct_field($primary_table, $primary_field, $query);

   321   }

   322 

   323   if (!empty($where) || !empty($join)) {

   324     $pattern = '{

   325       # Beginning of the string

   326       ^

   327       ((?P<anonymous_view>

   328         # Everything within this set of parentheses is named "anonymous view"

   329         (?:

   330           [^()]++                   # anything not parentheses

   331         |

   332           \( (?P>anonymous_view) \)          # an open parenthesis, more "anonymous view" and finally a close parenthesis.

   333         )*

   334       )[^()]+WHERE)

   335     }x';

   336     preg_match($pattern, $query, $matches);

   337     if (!$where) {

   338       $where = '1 = 1';

   339     }

   340     if ($matches) {

   341       $n = strlen($matches[1]);

   342       $second_part = substr($query, $n);

   343       $first_part = substr($matches[1], 0, $n - 5) ." $join WHERE $where AND ( ";

   344       // PHP 4 does not support strrpos for strings. We emulate it.

   345       $haystack_reverse = strrev($second_part);

   346     }

   347     else {

   348       $haystack_reverse = strrev($query);

   349     }

   350     // No need to use strrev on the needle, we supply GROUP, ORDER, LIMIT

   351     // reversed.

   352     foreach (array('PUORG', 'REDRO', 'TIMIL') as $needle_reverse) {

   353       $pos = strpos($haystack_reverse, $needle_reverse);

   354       if ($pos !== FALSE) {

   355         // All needles are five characters long.

   356         $pos += 5;

   357         break;

   358       }

   359     }

   360     if ($matches) {

   361       if ($pos === FALSE) {

   362         $query = $first_part . $second_part .')';

   363       }

   364       else {

   365         $query = $first_part . substr($second_part, 0, -$pos) .')'. substr($second_part, -$pos);

   366       }

   367     }

   368     elseif ($pos === FALSE) {

   369       $query .= " $join WHERE $where";

   370     }

   371     else {

   372       $query = substr($query, 0, -$pos) . " $join WHERE $where " . substr($query, -$pos);

   373     }

   374   }

   375 

   376   return $query;

   377 }

   378 

   379 /**

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

   381  *

   382  * Only keeps alphanumeric and underscores.

   383  */

   384 function db_escape_table($string) {

   385   return preg_replace('/[^A-Za-z0-9_]+/', '', $string);

   386 }

   387 

   388 /**

   389  * @} End of "defgroup database".

   390  */

   391 

   392 /**

   393  * @defgroup schemaapi Schema API

   394  * @{

   395  *

   396  * A Drupal schema definition is an array structure representing one or

   397  * more tables and their related keys and indexes. A schema is defined by

   398  * hook_schema(), which usually lives in a modulename.install file.

   399  *

   400  * By implementing hook_schema() and specifying the tables your module

   401  * declares, you can easily create and drop these tables on all

   402  * supported database engines. You don't have to deal with the

   403  * different SQL dialects for table creation and alteration of the

   404  * supported database engines.

   405  *

   406  * hook_schema() should return an array with a key for each table that

   407  * the module defines.

   408  *

   409  * The following keys are defined:

   410  *

   411  *   - 'description': A string describing this table and its purpose.

   412  *     References to other tables should be enclosed in

   413  *     curly-brackets.  For example, the node_revisions table

   414  *     description field might contain "Stores per-revision title and

   415  *     body data for each {node}."

   416  *   - 'fields': An associative array ('fieldname' => specification)

   417  *     that describes the table's database columns.  The specification

   418  *     is also an array.  The following specification parameters are defined:

   419  *

   420  *     - 'description': A string describing this field and its purpose.

   421  *       References to other tables should be enclosed in

   422  *       curly-brackets.  For example, the node table vid field

   423  *       description might contain "Always holds the largest (most

   424  *       recent) {node_revisions}.vid value for this nid."

   425  *     - 'type': The generic datatype: 'varchar', 'int', 'serial'

   426  *       'float', 'numeric', 'text', 'blob' or 'datetime'.  Most types

   427  *       just map to the according database engine specific

   428  *       datatypes.  Use 'serial' for auto incrementing fields. This

   429  *       will expand to 'int auto_increment' on mysql.

   430  *     - 'serialize': A boolean indicating whether the field will be stored

   431          as a serialized string.

   432  *     - 'size': The data size: 'tiny', 'small', 'medium', 'normal',

   433  *       'big'.  This is a hint about the largest value the field will

   434  *       store and determines which of the database engine specific

   435  *       datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT).

   436  *       'normal', the default, selects the base type (e.g. on MySQL,

   437  *       INT, VARCHAR, BLOB, etc.).

   438  *

   439  *       Not all sizes are available for all data types. See

   440  *       db_type_map() for possible combinations.

   441  *     - 'not null': If true, no NULL values will be allowed in this

   442  *       database column.  Defaults to false.

   443  *     - 'default': The field's default value.  The PHP type of the

   444  *       value matters: '', '0', and 0 are all different.  If you

   445  *       specify '0' as the default value for a type 'int' field it

   446  *       will not work because '0' is a string containing the

   447  *       character "zero", not an integer.

   448  *     - 'length': The maximal length of a type 'varchar' or 'text'

   449  *       field.  Ignored for other field types.

   450  *     - 'unsigned': A boolean indicating whether a type 'int', 'float'

   451  *       and 'numeric' only is signed or unsigned.  Defaults to

   452  *       FALSE.  Ignored for other field types.

   453  *     - 'precision', 'scale': For type 'numeric' fields, indicates

   454  *       the precision (total number of significant digits) and scale

   455  *       (decimal digits right of the decimal point).  Both values are

   456  *       mandatory.  Ignored for other field types.

   457  *

   458  *     All parameters apart from 'type' are optional except that type

   459  *     'numeric' columns must specify 'precision' and 'scale'.

   460  *

   461  *  - 'primary key': An array of one or more key column specifiers (see below)

   462  *    that form the primary key.

   463  *  - 'unique keys': An associative array of unique keys ('keyname' =>

   464  *    specification).  Each specification is an array of one or more

   465  *    key column specifiers (see below) that form a unique key on the table.

   466  *  - 'indexes':  An associative array of indexes ('indexame' =>

   467  *    specification).  Each specification is an array of one or more

   468  *    key column specifiers (see below) that form an index on the

   469  *    table.

   470  *

   471  * A key column specifier is either a string naming a column or an

   472  * array of two elements, column name and length, specifying a prefix

   473  * of the named column.

   474  *

   475  * As an example, here is a SUBSET of the schema definition for

   476  * Drupal's 'node' table.  It show four fields (nid, vid, type, and

   477  * title), the primary key on field 'nid', a unique key named 'vid' on

   478  * field 'vid', and two indexes, one named 'nid' on field 'nid' and

   479  * one named 'node_title_type' on the field 'title' and the first four

   480  * bytes of the field 'type':

   481  *

   482  * @code

   483  * $schema['node'] = array(

   484  *   'fields' => array(

   485  *     'nid'      => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),

   486  *     'vid'      => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),

   487  *     'type'     => array('type' => 'varchar', 'length' => 32, 'not null' => TRUE, 'default' => ''),

   488  *     'title'    => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''),

   489  *   ),

   490  *   'primary key' => array('nid'),

   491  *   'unique keys' => array(

   492  *     'vid'     => array('vid')

   493  *   ),

   494  *   'indexes' => array(

   495  *     'nid'                 => array('nid'),

   496  *     'node_title_type'     => array('title', array('type', 4)),

   497  *   ),

   498  * );

   499  * @endcode

   500  *

   501  * @see drupal_install_schema()

   502  */

   503 

   504  /**

   505  * Create a new table from a Drupal table definition.

   506  *

   507  * @param $ret

   508  *   Array to which query results will be added.

   509  * @param $name

   510  *   The name of the table to create.

   511  * @param $table

   512  *   A Schema API table definition array.

   513  */

   514 function db_create_table(&$ret, $name, $table) {

   515   $statements = db_create_table_sql($name, $table);

   516   foreach ($statements as $statement) {

   517     $ret[] = update_sql($statement);

   518   }

   519 }

   520 

   521 /**

   522  * Return an array of field names from an array of key/index column specifiers.

   523  *

   524  * This is usually an identity function but if a key/index uses a column prefix

   525  * specification, this function extracts just the name.

   526  *

   527  * @param $fields

   528  *   An array of key/index column specifiers.

   529  * @return

   530  *   An array of field names.

   531  */

   532 function db_field_names($fields) {

   533   $ret = array();

   534   foreach ($fields as $field) {

   535     if (is_array($field)) {

   536       $ret[] = $field[0];

   537     }

   538     else {

   539       $ret[] = $field;

   540     }

   541   }

   542   return $ret;

   543 }

   544 

   545 /**

   546  * Given a Schema API field type, return the correct %-placeholder.

   547  *

   548  * Embed the placeholder in a query to be passed to db_query and and pass as an

   549  * argument to db_query a value of the specified type.

   550  *

   551  * @param $type

   552  *   The Schema API type of a field.

   553  * @return

   554  *   The placeholder string to embed in a query for that type.

   555  */

   556 function db_type_placeholder($type) {

   557   switch ($type) {

   558     case 'varchar':

   559     case 'char':

   560     case 'text':

   561     case 'datetime':

   562       return "'%s'";

   563 

   564     case 'numeric':

   565       // Numeric values are arbitrary precision numbers.  Syntacically, numerics

   566       // should be specified directly in SQL. However, without single quotes

   567       // the %s placeholder does not protect against non-numeric characters such

   568       // as spaces which would expose us to SQL injection.

   569       return '%n';

   570 

   571     case 'serial':

   572     case 'int':

   573       return '%d';

   574 

   575     case 'float':

   576       return '%f';

   577 

   578     case 'blob':

   579       return '%b';

   580   }

   581 

   582   // There is no safe value to return here, so return something that

   583   // will cause the query to fail.

   584   return 'unsupported type '. $type .'for db_type_placeholder';

   585 }

   586 

   587 /**

   588  * @} End of "defgroup schemaapi".

   589  */