Mercurial > defr > drupal > core

comparison includes/database.mysql-common.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
comparison
equal deleted inserted replaced
0:5a113a1c4740 1:c1f4ac30525a
1 <?php
2 // $Id: database.mysql-common.inc,v 1.17.2.1 2008/02/07 10:17:26 goba Exp $
3
4 /**
5 * @file
6 * Functions shared between mysql and mysqli database engines.
7 */
8
9 /**
10 * Runs a basic query in the active database.
11 *
12 * User-supplied arguments to the query should be passed in as separate
13 * parameters so that they can be properly escaped to avoid SQL injection
14 * attacks.
15 *
16 * @param $query
17 * A string containing an SQL query.
18 * @param ...
19 * A variable number of arguments which are substituted into the query
20 * using printf() syntax. Instead of a variable number of query arguments,
21 * you may also pass a single array containing the query arguments.
22 *
23 * Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
24 * in '') and %%.
25 *
26 * NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
27 * and TRUE values to decimal 1.
28 *
29 * @return
30 * A database query result resource, or FALSE if the query was not
31 * executed correctly.
32 */
33 function db_query($query) {
34 $args = func_get_args();
35 array_shift($args);
36 $query = db_prefix_tables($query);
37 if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
38 $args = $args[0];
39 }
40 _db_query_callback($args, TRUE);
41 $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
42 return _db_query($query);
43 }
44
45 /**
46 * @ingroup schemaapi
47 * @{
48 */
49
50 /**
51 * Generate SQL to create a new table from a Drupal schema definition.
52 *
53 * @param $name
54 * The name of the table to create.
55 * @param $table
56 * A Schema API table definition array.
57 * @return
58 * An array of SQL statements to create the table.
59 */
60 function db_create_table_sql($name, $table) {
61
62 if (empty($table['mysql_suffix'])) {
63 $table['mysql_suffix'] = "/*!40100 DEFAULT CHARACTER SET UTF8 */";
64 }
65
66 $sql = "CREATE TABLE {". $name ."} (\n";
67
68 // Add the SQL statement for each field.
69 foreach ($table['fields'] as $field_name => $field) {
70 $sql .= _db_create_field_sql($field_name, _db_process_field($field)) .", \n";
71 }
72
73 // Process keys & indexes.
74 $keys = _db_create_keys_sql($table);
75 if (count($keys)) {
76 $sql .= implode(", \n", $keys) .", \n";
77 }
78
79 // Remove the last comma and space.
80 $sql = substr($sql, 0, -3) ."\n) ";
81
82 $sql .= $table['mysql_suffix'];
83
84 return array($sql);
85 }
86
87 function _db_create_keys_sql($spec) {
88 $keys = array();
89
90 if (!empty($spec['primary key'])) {
91 $keys[] = 'PRIMARY KEY ('. _db_create_key_sql($spec['primary key']) .')';
92 }
93 if (!empty($spec['unique keys'])) {
94 foreach ($spec['unique keys'] as $key => $fields) {
95 $keys[] = 'UNIQUE KEY '. $key .' ('. _db_create_key_sql($fields) .')';
96 }
97 }
98 if (!empty($spec['indexes'])) {
99 foreach ($spec['indexes'] as $index => $fields) {
100 $keys[] = 'INDEX '. $index .' ('. _db_create_key_sql($fields) .')';
101 }
102 }
103
104 return $keys;
105 }
106
107 function _db_create_key_sql($fields) {
108 $ret = array();
109 foreach ($fields as $field) {
110 if (is_array($field)) {
111 $ret[] = $field[0] .'('. $field[1] .')';
112 }
113 else {
114 $ret[] = $field;
115 }
116 }
117 return implode(', ', $ret);
118 }
119
120 /**
121 * Set database-engine specific properties for a field.
122 *
123 * @param $field
124 * A field description array, as specified in the schema documentation.
125 */
126 function _db_process_field($field) {
127
128 if (!isset($field['size'])) {
129 $field['size'] = 'normal';
130 }
131
132 // Set the correct database-engine specific datatype.
133 if (!isset($field['mysql_type'])) {
134 $map = db_type_map();
135 $field['mysql_type'] = $map[$field['type'] .':'. $field['size']];
136 }
137
138 if ($field['type'] == 'serial') {
139 $field['auto_increment'] = TRUE;
140 }
141
142 return $field;
143 }
144
145 /**
146 * Create an SQL string for a field to be used in table creation or alteration.
147 *
148 * Before passing a field out of a schema definition into this function it has
149 * to be processed by _db_process_field().
150 *
151 * @param $name
152 * Name of the field.
153 * @param $spec
154 * The field specification, as per the schema data structure format.
155 */
156 function _db_create_field_sql($name, $spec) {
157 $sql = "`". $name ."` ". $spec['mysql_type'];
158
159 if (isset($spec['length'])) {
160 $sql .= '('. $spec['length'] .')';
161 }
162 elseif (isset($spec['precision']) && isset($spec['scale'])) {
163 $sql .= '('. $spec['precision'] .', '. $spec['scale'] .')';
164 }
165
166 if (!empty($spec['unsigned'])) {
167 $sql .= ' unsigned';
168 }
169
170 if (!empty($spec['not null'])) {
171 $sql .= ' NOT NULL';
172 }
173
174 if (!empty($spec['auto_increment'])) {
175 $sql .= ' auto_increment';
176 }
177
178 if (isset($spec['default'])) {
179 if (is_string($spec['default'])) {
180 $spec['default'] = "'". $spec['default'] ."'";
181 }
182 $sql .= ' DEFAULT '. $spec['default'];
183 }
184
185 if (empty($spec['not null']) && !isset($spec['default'])) {
186 $sql .= ' DEFAULT NULL';
187 }
188
189 return $sql;
190 }
191
192 /**
193 * This maps a generic data type in combination with its data size
194 * to the engine-specific data type.
195 */
196 function db_type_map() {
197 // Put :normal last so it gets preserved by array_flip. This makes
198 // it much easier for modules (such as schema.module) to map
199 // database types back into schema types.
200 $map = array(
201 'varchar:normal' => 'VARCHAR',
202 'char:normal' => 'CHAR',
203
204 'text:tiny' => 'TINYTEXT',
205 'text:small' => 'TINYTEXT',
206 'text:medium' => 'MEDIUMTEXT',
207 'text:big' => 'LONGTEXT',
208 'text:normal' => 'TEXT',
209
210 'serial:tiny' => 'TINYINT',
211 'serial:small' => 'SMALLINT',
212 'serial:medium' => 'MEDIUMINT',
213 'serial:big' => 'BIGINT',
214 'serial:normal' => 'INT',
215
216 'int:tiny' => 'TINYINT',
217 'int:small' => 'SMALLINT',
218 'int:medium' => 'MEDIUMINT',
219 'int:big' => 'BIGINT',
220 'int:normal' => 'INT',
221
222 'float:tiny' => 'FLOAT',
223 'float:small' => 'FLOAT',
224 'float:medium' => 'FLOAT',
225 'float:big' => 'DOUBLE',
226 'float:normal' => 'FLOAT',
227
228 'numeric:normal' => 'DECIMAL',
229
230 'blob:big' => 'LONGBLOB',
231 'blob:normal' => 'BLOB',
232
233 'datetime:normal' => 'DATETIME',
234 );
235 return $map;
236 }
237
238 /**
239 * Rename a table.
240 *
241 * @param $ret
242 * Array to which query results will be added.
243 * @param $table
244 * The table to be renamed.
245 * @param $new_name
246 * The new name for the table.
247 */
248 function db_rename_table(&$ret, $table, $new_name) {
249 $ret[] = update_sql('ALTER TABLE {'. $table .'} RENAME TO {'. $new_name .'}');
250 }
251
252 /**
253 * Drop a table.
254 *
255 * @param $ret
256 * Array to which query results will be added.
257 * @param $table
258 * The table to be dropped.
259 */
260 function db_drop_table(&$ret, $table) {
261 $ret[] = update_sql('DROP TABLE {'. $table .'}');
262 }
263
264 /**
265 * Add a new field to a table.
266 *
267 * @param $ret
268 * Array to which query results will be added.
269 * @param $table
270 * Name of the table to be altered.
271 * @param $field
272 * Name of the field to be added.
273 * @param $spec
274 * The field specification array, as taken from a schema definition.
275 * The specification may also contain the key 'initial', the newly
276 * created field will be set to the value of the key in all rows.
277 * This is most useful for creating NOT NULL columns with no default
278 * value in existing tables.
279 * @param $keys_new
280 * Optional keys and indexes specification to be created on the
281 * table along with adding the field. The format is the same as a
282 * table specification but without the 'fields' element. If you are
283 * adding a type 'serial' field, you MUST specify at least one key
284 * or index including it in this array. @see db_change_field for more
285 * explanation why.
286 */
287 function db_add_field(&$ret, $table, $field, $spec, $keys_new = array()) {
288 $fixnull = FALSE;
289 if (!empty($spec['not null']) && !isset($spec['default'])) {
290 $fixnull = TRUE;
291 $spec['not null'] = FALSE;
292 }
293 $query = 'ALTER TABLE {'. $table .'} ADD ';
294 $query .= _db_create_field_sql($field, _db_process_field($spec));
295 if (count($keys_new)) {
296 $query .= ', ADD '. implode(', ADD ', _db_create_keys_sql($keys_new));
297 }
298 $ret[] = update_sql($query);
299 if (isset($spec['initial'])) {
300 // All this because update_sql does not support %-placeholders.
301 $sql = 'UPDATE {'. $table .'} SET '. $field .' = '. db_type_placeholder($spec['type']);
302 $result = db_query($sql, $spec['initial']);
303 $ret[] = array('success' => $result !== FALSE, 'query' => check_plain($sql .' ('. $spec['initial'] .')'));
304 }
305 if ($fixnull) {
306 $spec['not null'] = TRUE;
307 db_change_field($ret, $table, $field, $field, $spec);
308 }
309 }
310
311 /**
312 * Drop a field.
313 *
314 * @param $ret
315 * Array to which query results will be added.
316 * @param $table
317 * The table to be altered.
318 * @param $field
319 * The field to be dropped.
320 */
321 function db_drop_field(&$ret, $table, $field) {
322 $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP '. $field);
323 }
324
325 /**
326 * Set the default value for a field.
327 *
328 * @param $ret
329 * Array to which query results will be added.
330 * @param $table
331 * The table to be altered.
332 * @param $field
333 * The field to be altered.
334 * @param $default
335 * Default value to be set. NULL for 'default NULL'.
336 */
337 function db_field_set_default(&$ret, $table, $field, $default) {
338 if ($default == NULL) {
339 $default = 'NULL';
340 }
341 else {
342 $default = is_string($default) ? "'$default'" : $default;
343 }
344
345 $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' SET DEFAULT '. $default);
346 }
347
348 /**
349 * Set a field to have no default value.
350 *
351 * @param $ret
352 * Array to which query results will be added.
353 * @param $table
354 * The table to be altered.
355 * @param $field
356 * The field to be altered.
357 */
358 function db_field_set_no_default(&$ret, $table, $field) {
359 $ret[] = update_sql('ALTER TABLE {'. $table .'} ALTER COLUMN '. $field .' DROP DEFAULT');
360 }
361
362 /**
363 * Add a primary key.
364 *
365 * @param $ret
366 * Array to which query results will be added.
367 * @param $table
368 * The table to be altered.
369 * @param $fields
370 * Fields for the primary key.
371 */
372 function db_add_primary_key(&$ret, $table, $fields) {
373 $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD PRIMARY KEY ('.
374 _db_create_key_sql($fields) .')');
375 }
376
377 /**
378 * Drop the primary key.
379 *
380 * @param $ret
381 * Array to which query results will be added.
382 * @param $table
383 * The table to be altered.
384 */
385 function db_drop_primary_key(&$ret, $table) {
386 $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP PRIMARY KEY');
387 }
388
389 /**
390 * Add a unique key.
391 *
392 * @param $ret
393 * Array to which query results will be added.
394 * @param $table
395 * The table to be altered.
396 * @param $name
397 * The name of the key.
398 * @param $fields
399 * An array of field names.
400 */
401 function db_add_unique_key(&$ret, $table, $name, $fields) {
402 $ret[] = update_sql('ALTER TABLE {'. $table .'} ADD UNIQUE KEY '.
403 $name .' ('. _db_create_key_sql($fields) .')');
404 }
405
406 /**
407 * Drop a unique key.
408 *
409 * @param $ret
410 * Array to which query results will be added.
411 * @param $table
412 * The table to be altered.
413 * @param $name
414 * The name of the key.
415 */
416 function db_drop_unique_key(&$ret, $table, $name) {
417 $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP KEY '. $name);
418 }
419
420 /**
421 * Add an index.
422 *
423 * @param $ret
424 * Array to which query results will be added.
425 * @param $table
426 * The table to be altered.
427 * @param $name
428 * The name of the index.
429 * @param $fields
430 * An array of field names.
431 */
432 function db_add_index(&$ret, $table, $name, $fields) {
433 $query = 'ALTER TABLE {'. $table .'} ADD INDEX '. $name .' ('. _db_create_key_sql($fields) .')';
434 $ret[] = update_sql($query);
435 }
436
437 /**
438 * Drop an index.
439 *
440 * @param $ret
441 * Array to which query results will be added.
442 * @param $table
443 * The table to be altered.
444 * @param $name
445 * The name of the index.
446 */
447 function db_drop_index(&$ret, $table, $name) {
448 $ret[] = update_sql('ALTER TABLE {'. $table .'} DROP INDEX '. $name);
449 }
450
451 /**
452 * Change a field definition.
453 *
454 * IMPORTANT NOTE: To maintain database portability, you have to explicitly
455 * recreate all indices and primary keys that are using the changed field.
456 *
457 * That means that you have to drop all affected keys and indexes with
458 * db_drop_{primary_key,unique_key,index}() before calling db_change_field().
459 * To recreate the keys and indices, pass the key definitions as the
460 * optional $keys_new argument directly to db_change_field().
461 *
462 * For example, suppose you have:
463 * @code
464 * $schema['foo'] = array(
465 * 'fields' => array(
466 * 'bar' => array('type' => 'int', 'not null' => TRUE)
467 * ),
468 * 'primary key' => array('bar')
469 * );
470 * @endcode
471 * and you want to change foo.bar to be type serial, leaving it as the
472 * primary key. The correct sequence is:
473 * @code
474 * db_drop_primary_key($ret, 'foo');
475 * db_change_field($ret, 'foo', 'bar', 'bar',
476 * array('type' => 'serial', 'not null' => TRUE),
477 * array('primary key' => array('bar')));
478 * @endcode
479 *
480 * The reasons for this are due to the different database engines:
481 *
482 * On PostgreSQL, changing a field definition involves adding a new field
483 * and dropping an old one which* causes any indices, primary keys and
484 * sequences (from serial-type fields) that use the changed field to be dropped.
485 *
486 * On MySQL, all type 'serial' fields must be part of at least one key
487 * or index as soon as they are created. You cannot use
488 * db_add_{primary_key,unique_key,index}() for this purpose because
489 * the ALTER TABLE command will fail to add the column without a key
490 * or index specification. The solution is to use the optional
491 * $keys_new argument to create the key or index at the same time as
492 * field.
493 *
494 * You could use db_add_{primary_key,unique_key,index}() in all cases
495 * unless you are converting a field to be type serial. You can use
496 * the $keys_new argument in all cases.
497 *
498 * @param $ret
499 * Array to which query results will be added.
500 * @param $table
501 * Name of the table.
502 * @param $field
503 * Name of the field to change.
504 * @param $field_new
505 * New name for the field (set to the same as $field if you don't want to change the name).
506 * @param $spec
507 * The field specification for the new field.
508 * @param $keys_new
509 * Optional keys and indexes specification to be created on the
510 * table along with changing the field. The format is the same as a
511 * table specification but without the 'fields' element.
512 */
513
514 function db_change_field(&$ret, $table, $field, $field_new, $spec, $keys_new = array()) {
515 $sql = 'ALTER TABLE {'. $table .'} CHANGE '. $field .' '.
516 _db_create_field_sql($field_new, _db_process_field($spec));
517 if (count($keys_new)) {
518 $sql .= ', ADD '. implode(', ADD ', _db_create_keys_sql($keys_new));
519 }
520 $ret[] = update_sql($sql);
521 }
522
523 /**
524 * Returns the last insert id.
525 *
526 * @param $table
527 * The name of the table you inserted into.
528 * @param $field
529 * The name of the autoincrement field.
530 */
531 function db_last_insert_id($table, $field) {
532 return db_result(db_query('SELECT LAST_INSERT_ID()'));
533 }