[Sommaire] [Imprimer]

   1  <?php
   2  // $Id: database.inc,v 1.62.2.4 2007/07/12 06:25:47 drumm Exp $
   3  
   4  /**
   5   * @file
   6   * Wrapper for database interface code.
   7   */
   8  
   9  /**
  10   * @defgroup database Database abstraction layer
  11   * @{
  12   * Allow the use of different database servers using the same code base.
  13   *
  14   * Drupal provides a slim database abstraction layer to provide developers with
  15   * the ability to support multiple database servers easily. The intent of this
  16   * layer is to preserve the syntax and power of SQL as much as possible, while
  17   * letting Drupal control the pieces of queries that need to be written
  18   * differently for different servers and provide basic security checks.
  19   *
  20   * Most Drupal database queries are performed by a call to db_query() or
  21   * db_query_range(). Module authors should also consider using pager_query() for
  22   * queries that return results that need to be presented on multiple pages, and
  23   * tablesort_sql() for generating appropriate queries for sortable tables.
  24   *
  25   * For example, one might wish to return a list of the most recent 10 nodes
  26   * authored by a given user. Instead of directly issuing the SQL query
  27   * @code
  28   *   SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
  29   * @endcode
  30   * one would instead call the Drupal functions:
  31   * @code
  32   *   $result = db_query_range('SELECT n.title, n.body, n.created
  33   *     FROM {node} n WHERE n.uid = %d', $uid, 0, 10);
  34   *   while ($node = db_fetch_object($result)) {
  35   *     // Perform operations on $node->body, etc. here.
  36   *   }
  37   * @endcode
  38   * Curly braces are used around "node" to provide table prefixing via
  39   * db_prefix_tables(). The explicit use of a user ID is pulled out into an
  40   * argument passed to db_query() so that SQL injection attacks from user input
  41   * can be caught and nullified. The LIMIT syntax varies between database servers,
  42   * so that is abstracted into db_query_range() arguments. Finally, note the
  43   * common pattern of iterating over the result set using db_fetch_object().
  44   */
  45  
  46  /**
  47   * Append a database prefix to all tables in a query.
  48   *
  49   * Queries sent to Drupal should wrap all table names in curly brackets. This
  50   * function searches for this syntax and adds Drupal's table prefix to all
  51   * tables, allowing Drupal to coexist with other systems in the same database if
  52   * necessary.
  53   *
  54   * @param $sql
  55   *   A string containing a partial or entire SQL query.
  56   * @return
  57   *   The properly-prefixed string.
  58   */
  59  function db_prefix_tables($sql) {
  60    global $db_prefix;
  61  
  62    if (is_array($db_prefix)) {
  63      if (array_key_exists('default', $db_prefix)) {
  64        $tmp = $db_prefix;
  65        unset($tmp['default']);
  66        foreach ($tmp as $key => $val) {
  67          $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
  68        }
  69        return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));
  70      }
  71      else {
  72        foreach ($db_prefix as $key => $val) {
  73          $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
  74        }
  75        return strtr($sql, array('{' => '', '}' => ''));
  76      }
  77    }
  78    else {
  79      return strtr($sql, array('{' => $db_prefix, '}' => ''));
  80    }
  81  }
  82  
  83  /**
  84   * Activate a database for future queries.
  85   *
  86   * If it is necessary to use external databases in a project, this function can
  87   * be used to change where database queries are sent. If the database has not
  88   * yet been used, it is initialized using the URL specified for that name in
  89   * Drupal's configuration file. If this name is not defined, a duplicate of the
  90   * default connection is made instead.
  91   *
  92   * Be sure to change the connection back to the default when done with custom
  93   * code.
  94   *
  95   * @param $name
  96   *   The name assigned to the newly active database connection. If omitted, the
  97   *   default connection will be made active.
  98   *
  99   * @return the name of the previously active database or FALSE if non was found.
 100   */
 101  function db_set_active($name = 'default') {
 102    global $db_url, $db_type, $active_db;
 103    static $db_conns;
 104  
 105    if (!isset($db_conns[$name])) {
 106      // Initiate a new connection, using the named DB URL specified.
 107      if (is_array($db_url)) {
 108        $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
 109      }
 110      else {
 111        $connect_url = $db_url;
 112      }
 113  
 114      $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
 115      $handler = "./includes/database.$db_type.inc";
 116  
 117      if (is_file($handler)) {
 118        include_once $handler;
 119      }
 120      else {
 121        drupal_maintenance_theme();
 122        drupal_set_title('Unsupported database type');
 123        print theme('maintenance_page', '<p>The database type '. theme('placeholder', $db_type) .' is unsupported. Please use either <var>mysql</var> for MySQL 3.x &amp; 4.0.x databases, <var>mysqli</var> for MySQL 4.1.x+ databases, or <var>pgsql</var> for PostgreSQL databases. The database information is in your <code>settings.php</code> file.</p>
 124  <p>For more help, see the <a href="http://drupal.org/node/258">Installation and upgrading handbook</a>. If you are unsure what these terms mean you should probably contact your hosting provider.</p>');
 125        exit;
 126      }
 127  
 128      $db_conns[$name] = db_connect($connect_url);
 129    }
 130  
 131    $previous_db = $active_db;
 132    // Set the active connection.
 133    $active_db = $db_conns[$name];
 134  
 135    return array_search($previous_db, $db_conns);
 136  }
 137  
 138  /**
 139   * Helper function for db_query().
 140   */
 141  function _db_query_callback($match, $init = FALSE) {
 142    static $args = NULL;
 143    if ($init) {
 144      $args = $match;
 145      return;
 146    }
 147  
 148    switch ($match[1]) {
 149      case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
 150        return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
 151      case '%s':
 152        return db_escape_string(array_shift($args));
 153      case '%%':
 154        return '%';
 155      case '%f':
 156        return (float) array_shift($args);
 157      case '%b': // binary data
 158        return db_encode_blob(array_shift($args));
 159    }
 160  }
 161  
 162  /**
 163   * Indicates the place holders that should be replaced in _db_query_callback().
 164   */
 165  define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');
 166  
 167  /**
 168   * Runs a basic query in the active database.
 169   *
 170   * User-supplied arguments to the query should be passed in as separate
 171   * parameters so that they can be properly escaped to avoid SQL injection
 172   * attacks.
 173   *
 174   * @param $query
 175   *   A string containing an SQL query.
 176   * @param ...
 177   *   A variable number of arguments which are substituted into the query
 178   *   using printf() syntax. Instead of a variable number of query arguments,
 179   *   you may also pass a single array containing the query arguments.
 180   *
 181   *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
 182   *   in '') and %%.
 183   *
 184   *   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
 185   *   and TRUE values to decimal 1.
 186   *
 187   * @return
 188   *   A database query result resource, or FALSE if the query was not
 189   *   executed correctly.
 190   */
 191  function db_query($query) {
 192    $args = func_get_args();
 193    array_shift($args);
 194    $query = db_prefix_tables($query);
 195    if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
 196      $args = $args[0];
 197    }
 198    _db_query_callback($args, TRUE);
 199    $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
 200    return _db_query($query);
 201  }
 202  
 203  /**
 204   * Helper function for db_rewrite_sql.
 205   *
 206   * Collects JOIN and WHERE statements via hook_db_rewrite_sql().
 207   * Decides whether to select primary_key or DISTINCT(primary_key)
 208   *
 209   * @param $query
 210   *   Query to be rewritten.
 211   * @param $primary_table
 212   *   Name or alias of the table which has the primary key field for this query. Possible values are: comments, forum, node, menu, term_data, vocabulary.
 213   * @param $primary_field
 214   *   Name of the primary field.
 215   * @param $args
 216   *   Array of additional arguments.
 217   * @return
 218   *   An array: join statements, where statements, field or DISTINCT(field).
 219   */
 220  function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
 221    $where = array();
 222    $join = array();
 223    $distinct = FALSE;
 224    foreach (module_implements('db_rewrite_sql') as $module) {
 225      $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
 226      if (isset($result) && is_array($result)) {
 227        if (isset($result['where'])) {
 228          $where[] = $result['where'];
 229        }
 230        if (isset($result['join'])) {
 231          $join[] = $result['join'];
 232        }
 233        if (isset($result['distinct']) && $result['distinct']) {
 234          $distinct = TRUE;
 235        }
 236      }
 237      elseif (isset($result)) {
 238        $where[] = $result;
 239      }
 240    }
 241  
 242    $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
 243    $join = empty($join) ? '' : implode(' ', $join);
 244  
 245    return array($join, $where, $distinct);
 246  }
 247  
 248  /**
 249   * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
 250   * use FROM table1, table2 syntax, use JOIN instead.
 251   *
 252   * @param $query
 253   *   Query to be rewritten.
 254   * @param $primary_table
 255   *   Name or alias of the table which has the primary key field for this query. Possible values are: comments, forum, node, menu, term_data, vocabulary.
 256   * @param $primary_field
 257   *   Name of the primary field.
 258   * @param $args
 259   *   An array of arguments, passed to the implementations of hook_db_rewrite_sql.
 260   * @return
 261   *   The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.
 262   */
 263  function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid',  $args = array()) {
 264    list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);
 265  
 266    if ($distinct) {
 267      $query = db_distinct_field($primary_table, $primary_field, $query);
 268    }
 269  
 270    if (!empty($where) || !empty($join)) {
 271      if (!empty($where)) {
 272        $new = "WHERE $where ";
 273      }
 274      $new = " $join $new";
 275      if (strpos($query, 'WHERE')) {
 276        $query = str_replace('WHERE', $new .'AND (', $query);
 277        $insert = ') ';
 278      }
 279      else {
 280        $insert = $new;
 281      }
 282      if (strpos($query, 'GROUP')) {
 283        $replace = 'GROUP';
 284      }
 285      elseif (strpos($query, 'HAVING')) {
 286        $replace = 'HAVING';
 287      }
 288      elseif (strpos($query, 'ORDER')) {
 289        $replace = 'ORDER';
 290      }
 291      elseif (strpos($query, 'LIMIT')) {
 292        $replace = 'LIMIT';
 293      }
 294      else {
 295        $query .= $insert;
 296      }
 297      if (isset($replace)) {
 298        $query = str_replace($replace, $insert . $replace, $query);
 299      }
 300    }
 301  
 302    return $query;
 303  }
 304  
 305  /**
 306   * Restrict a dynamic tablename to safe characters.
 307   *
 308   * Only keeps alphanumeric and underscores.
 309   */
 310  function db_escape_table($string) {
 311    return preg_replace('/[^A-Za-z0-9_]+/', '', $string);
 312  }
 313  
 314  /**
 315   * @} End of "defgroup database".
 316   */
 317  
 318