database.inc 10.2 KB
Newer Older
Dries Buytaert's avatar
   
Dries Buytaert committed
1
<?php
Dries Buytaert's avatar
   
Dries Buytaert committed
2
3
// $Id$

Dries Buytaert's avatar
   
Dries Buytaert committed
4
5
6
7
8
9
10
11
/**
 * @file
 * Wrapper for database interface code.
 */

/**
 * @defgroup database Database abstraction layer
 * @{
Dries Buytaert's avatar
   
Dries Buytaert committed
12
 * Allow the use of different database servers using the same code base.
Dries Buytaert's avatar
   
Dries Buytaert committed
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 *
 * 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().
 */

/**
 * 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.
 */
Dries Buytaert's avatar
   
Dries Buytaert committed
59
60
61
function db_prefix_tables($sql) {
  global $db_prefix;

Dries Buytaert's avatar
   
Dries Buytaert committed
62
  if (is_array($db_prefix)) {
63
64
65
66
    if (array_key_exists('default', $db_prefix)) {
      $tmp = $db_prefix;
      unset($tmp['default']);
      foreach ($tmp as $key => $val) {
Dries Buytaert's avatar
   
Dries Buytaert committed
67
        $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
Dries Buytaert's avatar
   
Dries Buytaert committed
68
      }
69
70
71
72
73
74
75
      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('{' => '', '}' => ''));
Dries Buytaert's avatar
   
Dries Buytaert committed
76
77
78
    }
  }
  else {
79
    return strtr($sql, array('{' => $db_prefix, '}' => ''));
Dries Buytaert's avatar
   
Dries Buytaert committed
80
  }
Dries Buytaert's avatar
   
Dries Buytaert committed
81
}
Dries Buytaert's avatar
   
Dries Buytaert committed
82

Dries Buytaert's avatar
   
Dries Buytaert committed
83
/**
Dries Buytaert's avatar
   
Dries Buytaert committed
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 * 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.
98
 *
99
 * @return the name of the previously active database or FALSE if non was found.
Dries Buytaert's avatar
   
Dries Buytaert committed
100
 */
Dries Buytaert's avatar
   
Dries Buytaert committed
101
function db_set_active($name = 'default') {
102
  global $db_url, $db_type, $active_db;
Dries Buytaert's avatar
   
Dries Buytaert committed
103
104
105
  static $db_conns;

  if (!isset($db_conns[$name])) {
Dries Buytaert's avatar
   
Dries Buytaert committed
106
    // Initiate a new connection, using the named DB URL specified.
Dries Buytaert's avatar
   
Dries Buytaert committed
107
    if (is_array($db_url)) {
Dries Buytaert's avatar
   
Dries Buytaert committed
108
      $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
Dries Buytaert's avatar
   
Dries Buytaert committed
109
110
111
112
113
    }
    else {
      $connect_url = $db_url;
    }

Dries Buytaert's avatar
   
Dries Buytaert committed
114
    $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
115
    $handler = "./includes/database.$db_type.inc";
Dries Buytaert's avatar
   
Dries Buytaert committed
116

Dries Buytaert's avatar
   
Dries Buytaert committed
117
    if (is_file($handler)) {
118
      include_once $handler;
Dries Buytaert's avatar
   
Dries Buytaert committed
119
120
    }
    else {
121
122
      drupal_maintenance_theme();
      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
125
<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>');
      exit;
Dries Buytaert's avatar
   
Dries Buytaert committed
126
127
128
129
    }

    $db_conns[$name] = db_connect($connect_url);
  }
130
131

  $previous_db = $active_db;
Dries Buytaert's avatar
   
Dries Buytaert committed
132
  // Set the active connection.
Dries Buytaert's avatar
   
Dries Buytaert committed
133
  $active_db = $db_conns[$name];
134

135
  return array_search($previous_db, $db_conns);
136
137
}

138
139
140
141
142
143
144
145
146
147
148
/**
 * Helper function for db_query().
 */
function _db_query_callback($match, $init = FALSE) {
  static $args = NULL;
  if ($init) {
    $args = $match;
    return;
  }

  switch ($match[1]) {
149
    case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
150
151
152
153
154
155
156
157
158
159
160
161
      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));
  }
}

162
163
164
/**
 * Indicates the place holders that should be replaced in _db_query_callback().
 */
165
166
define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');

167
168
169
/**
 * Runs a basic query in the active database.
 *
170
171
172
 * 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.
173
174
175
176
 *
 * @param $query
 *   A string containing an SQL query.
 * @param ...
177
178
179
180
181
182
183
184
185
186
 *   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.
 *
187
 * @return
188
189
 *   A database query result resource, or FALSE if the query was not
 *   executed correctly.
190
191
192
 */
function db_query($query) {
  $args = func_get_args();
193
  array_shift($args);
194
  $query = db_prefix_tables($query);
195
196
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
197
  }
198
199
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
200
201
202
  return _db_query($query);
}

203
204
205
206
207
208
209
/**
 * Helper function for db_rewrite_sql.
 *
 * Collects JOIN and WHERE statements via hook_sql.
 * Decides whether to select primary_key or DISTINCT(primary_key)
 *
 * @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
214
 * @param $primary_field
 *   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
222
223
224
  $where = array();
  $join = array();
  $distinct = FALSE;
  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
234
235
236
237
      }
      if (isset($result['distinct']) && $result['distinct']) {
        $distinct = TRUE;
      }
    }
    elseif (isset($result)) {
238
      $where[] = $result;
239
240
241
    }
  }

242
243
  $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
  $join = empty($join) ? '' : implode(' ', $join);
244

245
  return array($join, $where, $distinct);
246
247
248
}

/**
249
250
 * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
 * 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
257
 * @param $primary_field
 *   Name of the primary field.
258
 * @param $args
259
 *   An array of arguments, passed to the implementations of hook_db_rewrite_sql.
260
261
262
 * @return
 *   The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.
 */
263
264
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);
265

266
  if ($distinct) {
267
    $query = db_distinct_field($primary_table, $primary_field, $query);
268
  }
269

270
271
  if (!empty($where) || !empty($join)) {
    if (!empty($where)) {
272
      $new = "WHERE $where ";
273
274
    }
    $new = " $join $new";
275
    if (strpos($query, 'WHERE')) {
276
277
      $query = str_replace('WHERE', $new .'AND (', $query);
      $insert = ') ';
278
    }
279
280
281
282
    else {
      $insert = $new;
    }
    if (strpos($query, 'GROUP')) {
283
284
285
286
287
288
289
290
291
      $replace = 'GROUP';
    }
    elseif (strpos($query, 'ORDER')) {
      $replace = 'ORDER';
    }
    elseif (strpos($query, 'LIMIT')) {
      $replace = 'LIMIT';
    }
    else {
292
      $query .= $insert;
293
294
    }
    if (isset($replace)) {
295
      $query = str_replace($replace, $insert . $replace, $query);
296
    }
297
  }
298

299
300
301
  return $query;
}

302
303
304
305
306
307
308
309
310
/**
 * Restrict a dynamic tablename to safe characters.
 *
 * Only keeps alphanumeric and underscores.
 */
function db_escape_table($string) {
  return preg_replace('/[^A-Za-z0-9_]+/', '', $string);
}

Dries Buytaert's avatar
   
Dries Buytaert committed
311
/**
Dries Buytaert's avatar
   
Dries Buytaert committed
312
 * @} End of "defgroup database".
Dries Buytaert's avatar
   
Dries Buytaert committed
313
 */
Dries Buytaert's avatar
   
Dries Buytaert committed
314

315