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

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

9
10
11
12
13
14
15
/**
 * A hash value to check when outputting database errors, md5('DB_ERROR').
 *
 * @see drupal_error_handler
 */
define('DB_ERROR', 'a515ac9c2796ca0e23adbe92c68fc9fc');

Dries's avatar
   
Dries committed
16
17
18
/**
 * @defgroup database Database abstraction layer
 * @{
Dries's avatar
   
Dries committed
19
 * Allow the use of different database servers using the same code base.
Dries's avatar
   
Dries committed
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
 *
 * 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().
 */

53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
/**
 * 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));
}

Dries's avatar
   
Dries committed
69
70
71
72
73
74
75
76
77
78
79
80
81
/**
 * 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's avatar
   
Dries committed
82
83
84
function db_prefix_tables($sql) {
  global $db_prefix;

Dries's avatar
   
Dries committed
85
  if (is_array($db_prefix)) {
86
87
88
89
    if (array_key_exists('default', $db_prefix)) {
      $tmp = $db_prefix;
      unset($tmp['default']);
      foreach ($tmp as $key => $val) {
90
        $sql = strtr($sql, array('{'. $key .'}' => $val . $key));
Dries's avatar
   
Dries committed
91
      }
92
93
94
95
      return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));
    }
    else {
      foreach ($db_prefix as $key => $val) {
96
        $sql = strtr($sql, array('{'. $key .'}' => $val . $key));
97
98
      }
      return strtr($sql, array('{' => '', '}' => ''));
Dries's avatar
   
Dries committed
99
100
101
    }
  }
  else {
102
    return strtr($sql, array('{' => $db_prefix, '}' => ''));
Dries's avatar
   
Dries committed
103
  }
Dries's avatar
   
Dries committed
104
}
Dries's avatar
   
Dries committed
105

Dries's avatar
   
Dries committed
106
/**
Dries's avatar
   
Dries committed
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 * 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.
121
 *
122
 * @return the name of the previously active database or FALSE if non was found.
Dries's avatar
   
Dries committed
123
 */
Dries's avatar
   
Dries committed
124
function db_set_active($name = 'default') {
125
  global $db_url, $db_type, $active_db;
Dries's avatar
   
Dries committed
126
127
  static $db_conns;

128
129
130
131
132
  if (empty($db_url)) {
    include_once 'includes/install.inc';
    install_goto('install.php');
  }

Dries's avatar
   
Dries committed
133
  if (!isset($db_conns[$name])) {
Dries's avatar
   
Dries committed
134
    // Initiate a new connection, using the named DB URL specified.
Dries's avatar
   
Dries committed
135
    if (is_array($db_url)) {
Dries's avatar
   
Dries committed
136
      $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
Dries's avatar
   
Dries committed
137
138
139
140
141
    }
    else {
      $connect_url = $db_url;
    }

Dries's avatar
   
Dries committed
142
    $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
143
    $handler = "./includes/database.$db_type.inc";
Dries's avatar
   
Dries committed
144

Dries's avatar
   
Dries committed
145
    if (is_file($handler)) {
146
      include_once $handler;
Dries's avatar
   
Dries committed
147
148
    }
    else {
149
150
      drupal_maintenance_theme();
      drupal_set_title('Unsupported database type');
151
      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>
152
153
<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's avatar
   
Dries committed
154
155
156
157
    }

    $db_conns[$name] = db_connect($connect_url);
  }
158
159

  $previous_db = $active_db;
Dries's avatar
   
Dries committed
160
  // Set the active connection.
Dries's avatar
   
Dries committed
161
  $active_db = $db_conns[$name];
162

163
  return array_search($previous_db, $db_conns);
164
165
}

166
167
168
169
170
171
172
173
174
175
176
/**
 * Helper function for db_query().
 */
function _db_query_callback($match, $init = FALSE) {
  static $args = NULL;
  if ($init) {
    $args = $match;
    return;
  }

  switch ($match[1]) {
177
    case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
178
179
180
181
182
183
184
185
186
187
188
189
      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));
  }
}

190
191
192
/**
 * Indicates the place holders that should be replaced in _db_query_callback().
 */
193
194
define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');

195
196
197
/**
 * Helper function for db_rewrite_sql.
 *
198
 * Collects JOIN and WHERE statements via hook_db_rewrite_sql()
199
200
201
 * Decides whether to select primary_key or DISTINCT(primary_key)
 *
 * @param $query
202
 *   Query to be rewritten.
203
 * @param $primary_table
204
205
206
 *   Name or alias of the table which has the primary key field for this query.
 *   Possible values are: blocks, comments, forum, node, menu, term_data,
 *   vocabulary.
207
208
 * @param $primary_field
 *   Name of the primary field.
209
 * @param $args
210
 *   Array of additional arguments.
211
 * @return
212
 *   An array: join statements, where statements, field or DISTINCT(field).
213
 */
214
function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
215
216
217
218
  $where = array();
  $join = array();
  $distinct = FALSE;
  foreach (module_implements('db_rewrite_sql') as $module) {
219
    $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
220
    if (isset($result) && is_array($result)) {
221
      if (isset($result['where'])) {
222
        $where[] = $result['where'];
223
224
      }
      if (isset($result['join'])) {
225
        $join[] = $result['join'];
226
227
228
229
230
231
      }
      if (isset($result['distinct']) && $result['distinct']) {
        $distinct = TRUE;
      }
    }
    elseif (isset($result)) {
232
      $where[] = $result;
233
234
235
    }
  }

236
237
  $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
  $join = empty($join) ? '' : implode(' ', $join);
238

239
  return array($join, $where, $distinct);
240
241
242
}

/**
243
244
 * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
 * use FROM table1, table2 syntax, use JOIN instead.
245
246
 *
 * @param $query
247
 *   Query to be rewritten.
248
 * @param $primary_table
249
 *   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.
250
251
 * @param $primary_field
 *   Name of the primary field.
252
 * @param $args
253
 *   An array of arguments, passed to the implementations of hook_db_rewrite_sql.
254
255
256
 * @return
 *   The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.
 */
257
258
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);
259

260
  if ($distinct) {
261
    $query = db_distinct_field($primary_table, $primary_field, $query);
262
  }
263

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

296
297
298
  return $query;
}

299
300
301
302
303
304
305
306
307
/**
 * 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's avatar
   
Dries committed
308
/**
Dries's avatar
   
Dries committed
309
 * @} End of "defgroup database".
Dries's avatar
   
Dries committed
310
 */
Dries's avatar
   
Dries committed
311

312
313
314
315
316
317
318
319
/**
 * @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.schema file.
 *
Dries's avatar
Dries committed
320
 * By implementing hook_schema() and specifying the tables your module
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 * 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 in the table definition are processed during
 * table creation:
 *
 *   - '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:
 *
 *     - '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'.
 *
Dries's avatar
Dries committed
370
 *  - 'primary key': An array of one or more key column specifiers (see below)
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
 *    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':
 *
391
 * @code
392
393
394
395
396
 * $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' => ''),
397
 *     'title'    => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''),
398
399
400
401
402
403
404
405
406
407
 *   ),
 *   'primary key' => array('nid'),
 *   'unique keys' => array(
 *     'vid'     => array('vid')
 *   ),
 *   'indexes' => array(
 *     'nid'                 => array('nid'),
 *     'node_title_type'     => array('title', array('type', 4)),
 *   ),
 * );
408
 * @endcode
409
410
411
412
413
414
415
416
417
 *
 * @see drupal_install_schema()
 */

 /**
 * Create a new table from a Drupal table definition.
 *
 * @param $ret
 *   Array to which query results will be added.
418
419
 * @param $name
 *   The name of the table to create.
420
 * @param $table
421
 *   A Schema API table definition array.
422
 */
423
424
function db_create_table(&$ret, $name, $table) {
  $statements = db_create_table_sql($name, $table);
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
  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;
}

454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
/**
 * Given a Schema API field type, return the correct %-placeholder to
 * embed in a query to be passed to db_query along with a value from a
 * column 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 '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';
}

496
497
498
/**
 * @} End of "defgroup schemaapi".
 */