Mercurial > defr > drupal > core

comparison includes/database.inc @ 1:c1f4ac30525a 6.0

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Drupal 6.0
author Franck Deroche <webmaster@defr.org>
date Tue, 23 Dec 2008 14:28:28 +0100
parents
children fff6d4c8c043
comparison
equal deleted inserted replaced
0:5a113a1c4740 1:c1f4ac30525a
1 <?php
2 // $Id: database.inc,v 1.92.2.1 2008/02/08 22:44:59 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_maintenance_theme();
176 drupal_set_header('HTTP/1.1 503 Service Unavailable');
177 drupal_set_title('Site off-line');
178
179 $message = '<p>The site is currently not available due to technical problems. Please try again later. Thank you for your understanding.</p>';
180 $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>';
181
182 if ($error && ini_get('display_errors')) {
183 $message .= '<p><small>The '. theme('placeholder', $db_type) .' error was: '. theme('placeholder', $error) .'.</small></p>';
184 }
185
186 print theme('maintenance_page', $message);
187 exit;
188 }
189
190 /**
191 * Returns a boolean depending on the availability of the database.
192 */
193 function db_is_active() {
194 global $active_db;
195 return !empty($active_db);
196 }
197
198 /**
199 * Helper function for db_query().
200 */
201 function _db_query_callback($match, $init = FALSE) {
202 static $args = NULL;
203 if ($init) {
204 $args = $match;
205 return;
206 }
207
208 switch ($match[1]) {
209 case '%d': // We must use type casting to int to convert FALSE/NULL/(TRUE?)
210 return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
211 case '%s':
212 return db_escape_string(array_shift($args));
213 case '%%':
214 return '%';
215 case '%f':
216 return (float) array_shift($args);
217 case '%b': // binary data
218 return db_encode_blob(array_shift($args));
219 }
220 }
221
222 /**
223 * Generate placeholders for an array of query arguments of a single type.
224 *
225 * Given a Schema API field type, return correct %-placeholders to
226 * embed in a query
227 *
228 * @param $arguments
229 * An array with at least one element.
230 * @param $type
231 * The Schema API type of a field (e.g. 'int', 'text', or 'varchar').
232 */
233 function db_placeholders($arguments, $type = 'int') {
234 $placeholder = db_type_placeholder($type);
235 return implode(',', array_fill(0, count($arguments), $placeholder));
236 }
237
238 /**
239 * Indicates the place holders that should be replaced in _db_query_callback().
240 */
241 define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');
242
243 /**
244 * Helper function for db_rewrite_sql.
245 *
246 * Collects JOIN and WHERE statements via hook_db_rewrite_sql()
247 * Decides whether to select primary_key or DISTINCT(primary_key)
248 *
249 * @param $query
250 * Query to be rewritten.
251 * @param $primary_table
252 * Name or alias of the table which has the primary key field for this query.
253 * Typical table names would be: {blocks}, {comments}, {forum}, {node},
254 * {menu}, {term_data} or {vocabulary}. However, in most cases the usual
255 * table alias (b, c, f, n, m, t or v) is used instead of the table name.
256 * @param $primary_field
257 * Name of the primary field.
258 * @param $args
259 * Array of additional arguments.
260 * @return
261 * An array: join statements, where statements, field or DISTINCT(field).
262 */
263 function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
264 $where = array();
265 $join = array();
266 $distinct = FALSE;
267 foreach (module_implements('db_rewrite_sql') as $module) {
268 $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
269 if (isset($result) && is_array($result)) {
270 if (isset($result['where'])) {
271 $where[] = $result['where'];
272 }
273 if (isset($result['join'])) {
274 $join[] = $result['join'];
275 }
276 if (isset($result['distinct']) && $result['distinct']) {
277 $distinct = TRUE;
278 }
279 }
280 elseif (isset($result)) {
281 $where[] = $result;
282 }
283 }
284
285 $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
286 $join = empty($join) ? '' : implode(' ', $join);
287
288 return array($join, $where, $distinct);
289 }
290
291 /**
292 * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
293 * use FROM table1, table2 syntax, use JOIN instead.
294 *
295 * @param $query
296 * Query to be rewritten.
297 * @param $primary_table
298 * Name or alias of the table which has the primary key field for this query.
299 * Typical table names would be: {blocks}, {comments}, {forum}, {node},
300 * {menu}, {term_data} or {vocabulary}. However, it is more common to use the
301 * the usual table aliases: b, c, f, n, m, t or v.
302 * @param $primary_field
303 * Name of the primary field.
304 * @param $args
305 * An array of arguments, passed to the implementations of hook_db_rewrite_sql.
306 * @return
307 * The original query with JOIN and WHERE statements inserted from
308 * hook_db_rewrite_sql implementations. nid is rewritten if needed.
309 */
310 function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) {
311 list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);
312
313 if ($distinct) {
314 $query = db_distinct_field($primary_table, $primary_field, $query);
315 }
316
317 if (!empty($where) || !empty($join)) {
318 $pattern = '{
319 # Beginning of the string
320 ^
321 ((?P<anonymous_view>
322 # Everything within this set of parentheses is named "anonymous view"
323 (?:
324 [^()]++ # anything not parentheses
325 |
326 \( (?P>anonymous_view) \) # an open parenthesis, more "anonymous view" and finally a close parenthesis.
327 )*
328 )[^()]+WHERE)
329 }x';
330 preg_match($pattern, $query, $matches);
331 if (!$where) {
332 $where = '1 = 1';
333 }
334 if ($matches) {
335 $n = strlen($matches[1]);
336 $second_part = substr($query, $n);
337 $first_part = substr($matches[1], 0, $n - 5) ." $join WHERE $where AND ( ";
338 // PHP 4 does not support strrpos for strings. We emulate it.
339 $haystack_reverse = strrev($second_part);
340 }
341 else {
342 $haystack_reverse = strrev($query);
343 }
344 // No need to use strrev on the needle, we supply GROUP, ORDER, LIMIT
345 // reversed.
346 foreach (array('PUORG', 'REDRO', 'TIMIL') as $needle_reverse) {
347 $pos = strpos($haystack_reverse, $needle_reverse);
348 if ($pos !== FALSE) {
349 // All needles are five characters long.
350 $pos += 5;
351 break;
352 }
353 }
354 if ($matches) {
355 if ($pos === FALSE) {
356 $query = $first_part . $second_part .')';
357 }
358 else {
359 $query = $first_part . substr($second_part, 0, -$pos) .')'. substr($second_part, -$pos);
360 }
361 }
362 elseif ($pos === FALSE) {
363 $query .= " $join WHERE $where";
364 }
365 else {
366 $query = substr($query, 0, -$pos) . " $join WHERE $where " . substr($query, -$pos);
367 }
368 }
369
370 return $query;
371 }
372
373 /**
374 * Restrict a dynamic table, column or constraint name to safe characters.
375 *
376 * Only keeps alphanumeric and underscores.
377 */
378 function db_escape_table($string) {
379 return preg_replace('/[^A-Za-z0-9_]+/', '', $string);
380 }
381
382 /**
383 * @} End of "defgroup database".
384 */
385
386 /**
387 * @defgroup schemaapi Schema API
388 * @{
389 *
390 * A Drupal schema definition is an array structure representing one or
391 * more tables and their related keys and indexes. A schema is defined by
392 * hook_schema(), which usually lives in a modulename.install file.
393 *
394 * By implementing hook_schema() and specifying the tables your module
395 * declares, you can easily create and drop these tables on all
396 * supported database engines. You don't have to deal with the
397 * different SQL dialects for table creation and alteration of the
398 * supported database engines.
399 *
400 * hook_schema() should return an array with a key for each table that
401 * the module defines.
402 *
403 * The following keys are defined:
404 *
405 * - 'description': A string describing this table and its purpose.
406 * References to other tables should be enclosed in
407 * curly-brackets. For example, the node_revisions table
408 * description field might contain "Stores per-revision title and
409 * body data for each {node}."
410 * - 'fields': An associative array ('fieldname' => specification)
411 * that describes the table's database columns. The specification
412 * is also an array. The following specification parameters are defined:
413 *
414 * - 'description': A string describing this field and its purpose.
415 * References to other tables should be enclosed in
416 * curly-brackets. For example, the node table vid field
417 * description might contain "Always holds the largest (most
418 * recent) {node_revisions}.vid value for this nid."
419 * - 'type': The generic datatype: 'varchar', 'int', 'serial'
420 * 'float', 'numeric', 'text', 'blob' or 'datetime'. Most types
421 * just map to the according database engine specific
422 * datatypes. Use 'serial' for auto incrementing fields. This
423 * will expand to 'int auto_increment' on mysql.
424 * - 'size': The data size: 'tiny', 'small', 'medium', 'normal',
425 * 'big'. This is a hint about the largest value the field will
426 * store and determines which of the database engine specific
427 * datatypes will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT).
428 * 'normal', the default, selects the base type (e.g. on MySQL,
429 * INT, VARCHAR, BLOB, etc.).
430 *
431 * Not all sizes are available for all data types. See
432 * db_type_map() for possible combinations.
433 * - 'not null': If true, no NULL values will be allowed in this
434 * database column. Defaults to false.
435 * - 'default': The field's default value. The PHP type of the
436 * value matters: '', '0', and 0 are all different. If you
437 * specify '0' as the default value for a type 'int' field it
438 * will not work because '0' is a string containing the
439 * character "zero", not an integer.
440 * - 'length': The maximal length of a type 'varchar' or 'text'
441 * field. Ignored for other field types.
442 * - 'unsigned': A boolean indicating whether a type 'int', 'float'
443 * and 'numeric' only is signed or unsigned. Defaults to
444 * FALSE. Ignored for other field types.
445 * - 'precision', 'scale': For type 'numeric' fields, indicates
446 * the precision (total number of significant digits) and scale
447 * (decimal digits right of the decimal point). Both values are
448 * mandatory. Ignored for other field types.
449 *
450 * All parameters apart from 'type' are optional except that type
451 * 'numeric' columns must specify 'precision' and 'scale'.
452 *
453 * - 'primary key': An array of one or more key column specifiers (see below)
454 * that form the primary key.
455 * - 'unique key': An associative array of unique keys ('keyname' =>
456 * specification). Each specification is an array of one or more
457 * key column specifiers (see below) that form a unique key on the table.
458 * - 'indexes': An associative array of indexes ('indexame' =>
459 * specification). Each specification is an array of one or more
460 * key column specifiers (see below) that form an index on the
461 * table.
462 *
463 * A key column specifier is either a string naming a column or an
464 * array of two elements, column name and length, specifying a prefix
465 * of the named column.
466 *
467 * As an example, here is a SUBSET of the schema definition for
468 * Drupal's 'node' table. It show four fields (nid, vid, type, and
469 * title), the primary key on field 'nid', a unique key named 'vid' on
470 * field 'vid', and two indexes, one named 'nid' on field 'nid' and
471 * one named 'node_title_type' on the field 'title' and the first four
472 * bytes of the field 'type':
473 *
474 * @code
475 * $schema['node'] = array(
476 * 'fields' => array(
477 * 'nid' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
478 * 'vid' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
479 * 'type' => array('type' => 'varchar', 'length' => 32, 'not null' => TRUE, 'default' => ''),
480 * 'title' => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''),
481 * ),
482 * 'primary key' => array('nid'),
483 * 'unique keys' => array(
484 * 'vid' => array('vid')
485 * ),
486 * 'indexes' => array(
487 * 'nid' => array('nid'),
488 * 'node_title_type' => array('title', array('type', 4)),
489 * ),
490 * );
491 * @endcode
492 *
493 * @see drupal_install_schema()
494 */
495
496 /**
497 * Create a new table from a Drupal table definition.
498 *
499 * @param $ret
500 * Array to which query results will be added.
501 * @param $name
502 * The name of the table to create.
503 * @param $table
504 * A Schema API table definition array.
505 */
506 function db_create_table(&$ret, $name, $table) {
507 $statements = db_create_table_sql($name, $table);
508 foreach ($statements as $statement) {
509 $ret[] = update_sql($statement);
510 }
511 }
512
513 /**
514 * Return an array of field names from an array of key/index column specifiers.
515 *
516 * This is usually an identity function but if a key/index uses a column prefix
517 * specification, this function extracts just the name.
518 *
519 * @param $fields
520 * An array of key/index column specifiers.
521 * @return
522 * An array of field names.
523 */
524 function db_field_names($fields) {
525 $ret = array();
526 foreach ($fields as $field) {
527 if (is_array($field)) {
528 $ret[] = $field[0];
529 }
530 else {
531 $ret[] = $field;
532 }
533 }
534 return $ret;
535 }
536
537 /**
538 * Given a Schema API field type, return the correct %-placeholder.
539 *
540 * Embed the placeholder in a query to be passed to db_query and and pass as an
541 * argument to db_query a value of the specified type.
542 *
543 * @param $type
544 * The Schema API type of a field.
545 * @return
546 * The placeholder string to embed in a query for that type.
547 */
548 function db_type_placeholder($type) {
549 switch ($type) {
550 case 'varchar':
551 case 'char':
552 case 'text':
553 case 'datetime':
554 return '\'%s\'';
555
556 case 'numeric':
557 // For 'numeric' values, we use '%s', not '\'%s\'' as with
558 // string types, because numeric values should not be enclosed
559 // in quotes in queries (though they can be, at least on mysql
560 // and pgsql). Numerics should only have [0-9.+-] and
561 // presumably no db's "escape string" function will mess with
562 // those characters.
563 return '%s';
564
565 case 'serial':
566 case 'int':
567 return '%d';
568
569 case 'float':
570 return '%f';
571
572 case 'blob':
573 return '%b';
574 }
575
576 // There is no safe value to return here, so return something that
577 // will cause the query to fail.
578 return 'unsupported type '. $type .'for db_type_placeholder';
579 }
580
581 /**
582 * @} End of "defgroup schemaapi".
583 */