2 // $Id: database.inc 1119 2008-01-04 11:08:59Z thierry $
6 * Wrapper for database interface code.
10 * @defgroup database Database abstraction layer
12 * Allow the use of different database servers using the same code base.
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.
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.
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
28 * SELECT n.title, n.body, n.created FROM node n WHERE n.uid = $uid LIMIT 0, 10;
30 * one would instead call the Drupal functions:
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.
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().
47 * Append a database prefix to all tables in a query.
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
55 * A string containing a partial or entire SQL query.
57 * The properly-prefixed string.
59 function db_prefix_tables($sql) {
62 if (is_array($db_prefix)) {
63 if (array_key_exists('default', $db_prefix)) {
65 unset($tmp['default']);
66 foreach ($tmp as $key => $val) {
67 $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
69 return strtr($sql, array('{' => $db_prefix['default'], '}' => ''));
72 foreach ($db_prefix as $key => $val) {
73 $sql = strtr($sql, array('{'. $key. '}' => $val. $key));
75 return strtr($sql, array('{' => '', '}' => ''));
79 return strtr($sql, array('{' => $db_prefix, '}' => ''));
84 * Fixed parse_url() function.
86 * The builtin parse_url() cannot handle passwords with @ in them.
88 function db_parse_url($url, $component = NULL) {
89 // scheme://user:pass@host:port/path?query#fragment
91 $pattern = '(.*)://'; // scheme (before ://)
92 $pattern .= '((.*)?:(.*)?@)?'; // user:pass@ (optional, before @, separated by :)
93 $pattern .= '([^:]*)'; // host (until :)
94 $pattern .= '(:(.*))?'; // port (optional, after :)
95 $pattern .= '(/[^?]*)'; // path (after and including /, until ?)
96 $pattern .= '(\?([^#]*))?'; // query (optional, after ?, until #)
97 $pattern .= '(#(.*))?'; // fragment (optional, after #)
99 preg_match('|' . $pattern . '|', $url, $matches);
106 $user_pass, $url['user'], $url['pass'],
108 $optional_port, $url['port'],
109 $url['path']) = $matches;
111 if (count($matches) > 9)
112 list($optional_query, $url['query']) = array_slice($matches, 9);
114 if (count($matches) > 10)
115 list($optional_fragment, $url['fragment']) = array_slice($matches, 10);
117 /* thierry : on fc6 an empty component matches PHP_URL_SCHEME=0 ! */
119 if (defined('PHP_URL_SCHEME')) {
120 switch ($component) {
121 case PHP_URL_SCHEME: return $url['scheme'];
122 case PHP_URL_HOST: return $url['host'];
123 case PHP_URL_PORT: return $url['port'];
124 case PHP_URL_USER: return $url['user'];
125 case PHP_URL_PASS: return $url['pass'];
126 case PHP_URL_PATH: return $url['path'];
127 case PHP_URL_QUERY: return $url['query'];
128 case PHP_URL_FRAGMENT: return $url['fragment'];
133 # Remove unmatched fields
134 $url = array_filter($url, 'strlen');
143 * Activate a database for future queries.
145 * If it is necessary to use external databases in a project, this function can
146 * be used to change where database queries are sent. If the database has not
147 * yet been used, it is initialized using the URL specified for that name in
148 * Drupal's configuration file. If this name is not defined, a duplicate of the
149 * default connection is made instead.
151 * Be sure to change the connection back to the default when done with custom
155 * The name assigned to the newly active database connection. If omitted, the
156 * default connection will be made active.
158 * @return the name of the previously active database or FALSE if non was found.
160 function db_set_active($name = 'default') {
161 global $db_url, $db_type, $active_db;
164 if (!isset($db_conns[$name])) {
165 // Initiate a new connection, using the named DB URL specified.
166 if (is_array($db_url)) {
167 $connect_url = array_key_exists($name, $db_url) ? $db_url[$name] : $db_url['default'];
170 $connect_url = $db_url;
173 $db_type = substr($connect_url, 0, strpos($connect_url, '://'));
174 $handler = "./includes/database.$db_type.inc";
176 if (is_file($handler)) {
177 include_once $handler;
180 drupal_maintenance_theme();
181 drupal_set_title('Unsupported database type');
182 print theme('maintenance_page', '<p>The database type '. theme('placeholder', $db_type) .' is unsupported. Please use either <var>mysql</var> for MySQL 3.x & 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>
183 <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>');
187 $db_conns[$name] = db_connect($connect_url);
190 $previous_db = $active_db;
191 // Set the active connection.
192 $active_db = $db_conns[$name];
194 return array_search($previous_db, $db_conns);
198 * Helper function for db_query().
200 function _db_query_callback($match, $init = FALSE) {
208 case '%d': // We must use type casting to int to convert false/null/(true?)
209 return (int) array_shift($args); // We don't need db_escape_string as numbers are db-safe
211 return db_escape_string(array_shift($args));
215 return (float) array_shift($args);
216 case '%b': // binary data
217 return db_encode_blob(array_shift($args));
221 define('DB_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');
224 * Runs a basic query in the active database.
226 * User-supplied arguments to the query should be passed in as separate
227 * parameters so that they can be properly escaped to avoid SQL injection
231 * A string containing an SQL query.
233 * A variable number of arguments which are substituted into the query
234 * using printf() syntax. Instead of a variable number of query arguments,
235 * you may also pass a single array containing the query arguments.
237 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
240 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
241 * and TRUE values to decimal 1.
244 * A database query result resource, or FALSE if the query was not
245 * executed correctly.
247 function db_query($query) {
248 $args = func_get_args();
250 $query = db_prefix_tables($query);
251 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
254 _db_query_callback($args, TRUE);
255 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
256 return _db_query($query);
260 * Debugging version of db_query().
262 * Echoes the query to the browser.
264 function db_queryd($query) {
265 $args = func_get_args();
267 $query = db_prefix_tables($query);
268 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
271 _db_query_callback($args, TRUE);
272 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
273 return _db_query($query, 1);
277 * Helper function for db_rewrite_sql.
279 * Collects JOIN and WHERE statements via hook_sql.
280 * Decides whether to select primary_key or DISTINCT(primary_key)
283 * Query to be rewritten.
284 * @param $primary_table
285 * 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.
286 * @param $primary_field
287 * Name of the primary field.
289 * Array of additional arguments.
291 * An array: join statements, where statements, field or DISTINCT(field).
293 function _db_rewrite_sql($query = '', $primary_table = 'n', $primary_field = 'nid', $args = array()) {
297 foreach (module_implements('db_rewrite_sql') as $module) {
298 $result = module_invoke($module, 'db_rewrite_sql', $query, $primary_table, $primary_field, $args);
299 if (isset($result) && is_array($result)) {
300 if (isset($result['where'])) {
301 $where[] = $result['where'];
303 if (isset($result['join'])) {
304 $join[] = $result['join'];
306 if (isset($result['distinct']) && $result['distinct']) {
310 elseif (isset($result)) {
315 $where = empty($where) ? '' : '('. implode(') AND (', $where) .')';
316 $join = empty($join) ? '' : implode(' ', $join);
318 return array($join, $where, $distinct);
322 * Rewrites node, taxonomy and comment queries. Use it for listing queries. Do not
323 * use FROM table1, table2 syntax, use JOIN instead.
326 * Query to be rewritten.
327 * @param $primary_table
328 * 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.
329 * @param $primary_field
330 * Name of the primary field.
332 * An array of arguments, passed to the implementations of hook_db_rewrite_sql.
334 * The original query with JOIN and WHERE statements inserted from hook_db_rewrite_sql implementations. nid is rewritten if needed.
336 function db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) {
337 list($join, $where, $distinct) = _db_rewrite_sql($query, $primary_table, $primary_field, $args);
340 $field_to_select = 'DISTINCT('. $primary_table .'.'. $primary_field .')';
341 // (?<!text) is a negative look-behind (no need to rewrite queries that already use DISTINCT).
342 $query = preg_replace('/(SELECT.*)('. $primary_table .'\.)?(?<!DISTINCT\()(?<!DISTINCT\('. $primary_table .'\.)'. $primary_field .'(.*FROM)/AUsi', '\1'. $field_to_select .'\3', $query);
345 if (!empty($where) || !empty($join)) {
346 if (!empty($where)) {
347 $new = " WHERE $where ";
349 $new = " $join $new";
350 if (strpos($query, 'WHERE')) {
354 elseif (strpos($query, 'GROUP')) {
358 elseif (strpos($query, 'ORDER')) {
362 elseif (strpos($query, 'LIMIT')) {
369 if (isset($replace)) {
370 $query = str_replace($replace, "$new $add ", $query);
378 * Restrict a dynamic tablename to safe characters.
380 * Only keeps alphanumeric and underscores.
382 function db_escape_table($string) {
383 return preg_replace('/[^A-Za-z0-9_]+/', '', $string);
387 * @} End of "defgroup database".