Code coverage for /20080809/includes/database.mysql-common.inc

Line #Times calledCode
1
<?php
2
// $Id: database.mysql-common.inc,v 1.19 2008/04/14 17:48:33 dries 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
 */
332027
function db_query($query) {
342087
  $args = func_get_args();
352087
  array_shift($args);
362087
  $query = db_prefix_tables($query);
372087
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one
array' syntax
381995
    $args = $args[0];
391995
  }
402087
  _db_query_callback($args, TRUE);
412087
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback',
$query);
422087
  return _db_query($query);
430
}
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
 */
602027
function db_create_table_sql($name, $table) {
61
6264
  if (empty($table['mysql_suffix'])) {
6364
    $table['mysql_suffix'] = "/*!40100 DEFAULT CHARACTER SET UTF8 */";
6464
  }
65
6664
  $sql = "CREATE TABLE {" . $name . "} (\n";
67
68
  // Add the SQL statement for each field.
6964
  foreach ($table['fields'] as $field_name => $field) {
7064
    $sql .= _db_create_field_sql($field_name, _db_process_field($field)) .
", \n";
7164
  }
72
73
  // Process keys & indexes.
7464
  $keys = _db_create_keys_sql($table);
7564
  if (count($keys)) {
7664
    $sql .= implode(", \n", $keys) . ", \n";
7764
  }
78
79
  // Remove the last comma and space.
8064
  $sql = substr($sql, 0, -3) . "\n) ";
81
8264
  $sql .= $table['mysql_suffix'];
83
8464
  return array($sql);
850
}
86
872027
function _db_create_keys_sql($spec) {
8864
  $keys = array();
89
9064
  if (!empty($spec['primary key'])) {
9164
    $keys[] = 'PRIMARY KEY (' . _db_create_key_sql($spec['primary key']) .
')';
9264
  }
9364
  if (!empty($spec['unique keys'])) {
9463
    foreach ($spec['unique keys'] as $key => $fields) {
9563
      $keys[] = 'UNIQUE KEY ' . $key . ' (' . _db_create_key_sql($fields) .
')';
9663
    }
9763
  }
9864
  if (!empty($spec['indexes'])) {
9964
    foreach ($spec['indexes'] as $index => $fields) {
10064
      $keys[] = 'INDEX ' . $index . ' (' . _db_create_key_sql($fields) .
')';
10164
    }
10264
  }
103
10464
  return $keys;
1050
}
106
1072027
function _db_create_key_sql($fields) {
10864
  $ret = array();
10964
  foreach ($fields as $field) {
11064
    if (is_array($field)) {
11163
      $ret[] = $field[0] . '(' . $field[1] . ')';
11263
    }
113
    else {
11464
      $ret[] = $field;
115
    }
11664
  }
11764
  return implode(', ', $ret);
1180
}
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
 */
1262027
function _db_process_field($field) {
127
12864
  if (!isset($field['size'])) {
12964
    $field['size'] = 'normal';
13064
  }
131
132
  // Set the correct database-engine specific datatype.
13364
  if (!isset($field['mysql_type'])) {
13464
    $map = db_type_map();
13564
    $field['mysql_type'] = $map[$field['type'] . ':' . $field['size']];
13664
  }
137
13864
  if ($field['type'] == 'serial') {
13964
    $field['auto_increment'] = TRUE;
14064
  }
141
14264
  return $field;
1430
}
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
 */
1562027
function _db_create_field_sql($name, $spec) {
15764
  $sql = "`" . $name . "` " . $spec['mysql_type'];
158
15964
  if (isset($spec['length'])) {
16064
    $sql .= '(' . $spec['length'] . ')';
16164
  }
16264
  elseif (isset($spec['precision']) && isset($spec['scale'])) {
1630
    $sql .= '(' . $spec['precision'] . ', ' . $spec['scale'] . ')';
1640
  }
165
16664
  if (!empty($spec['unsigned'])) {
16762
    $sql .= ' unsigned';
16862
  }
169
17064
  if (!empty($spec['not null'])) {
17164
    $sql .= ' NOT NULL';
17264
  }
173
17464
  if (!empty($spec['auto_increment'])) {
17564
    $sql .= ' auto_increment';
17664
  }
177
17864
  if (isset($spec['default'])) {
17964
    if (is_string($spec['default'])) {
18064
      $spec['default'] = "'" . $spec['default'] . "'";
18164
    }
18264
    $sql .= ' DEFAULT ' . $spec['default'];
18364
  }
184
18564
  if (empty($spec['not null']) && !isset($spec['default'])) {
18663
    $sql .= ' DEFAULT NULL';
18763
  }
188
18964
  return $sql;
1900
}
191
192
/**
193
 * This maps a generic data type in combination with its data size
194
 * to the engine-specific data type.
195
 */
1962027
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(
20164
    'varchar:normal'  => 'VARCHAR',
20264
    'char:normal'     => 'CHAR',
203
20464
    'text:tiny'       => 'TINYTEXT',
20564
    'text:small'      => 'TINYTEXT',
20664
    'text:medium'     => 'MEDIUMTEXT',
20764
    'text:big'        => 'LONGTEXT',
20864
    'text:normal'     => 'TEXT',
209
21064
    'serial:tiny'     => 'TINYINT',
21164
    'serial:small'    => 'SMALLINT',
21264
    'serial:medium'   => 'MEDIUMINT',
21364
    'serial:big'      => 'BIGINT',
21464
    'serial:normal'   => 'INT',
215
21664
    'int:tiny'        => 'TINYINT',
21764
    'int:small'       => 'SMALLINT',
21864
    'int:medium'      => 'MEDIUMINT',
21964
    'int:big'         => 'BIGINT',
22064
    'int:normal'      => 'INT',
221
22264
    'float:tiny'      => 'FLOAT',
22364
    'float:small'     => 'FLOAT',
22464
    'float:medium'    => 'FLOAT',
22564
    'float:big'       => 'DOUBLE',
22664
    'float:normal'    => 'FLOAT',
227
22864
    'numeric:normal'  => 'DECIMAL',
229
23064
    'blob:big'        => 'LONGBLOB',
23164
    'blob:normal'     => 'BLOB',
232
23364
    'datetime:normal' => 'DATETIME',
23464
  );
23564
  return $map;
2360
}
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
 */
2482027
function db_rename_table(&$ret, $table, $new_name) {
2490
  $ret[] = update_sql('ALTER TABLE {' . $table . '} RENAME TO {' .
$new_name . '}');
2500
}
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
 */
2602027
function db_drop_table(&$ret, $table) {
26163
  $ret[] = update_sql('DROP TABLE {' . $table . '}');
26263
}
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
 */
2872027
function db_add_field(&$ret, $table, $field, $spec, $keys_new = array()) {
2880
  $fixnull = FALSE;
2890
  if (!empty($spec['not null']) && !isset($spec['default'])) {
2900
    $fixnull = TRUE;
2910
    $spec['not null'] = FALSE;
2920
  }
2930
  $query = 'ALTER TABLE {' . $table . '} ADD ';
2940
  $query .= _db_create_field_sql($field, _db_process_field($spec));
2950
  if (count($keys_new)) {
2960
    $query .= ', ADD ' . implode(', ADD ',
_db_create_keys_sql($keys_new));
2970
  }
2980
  $ret[] = update_sql($query);
2990
  if (isset($spec['initial'])) {
300
    // All this because update_sql does not support %-placeholders.
3010
    $sql = 'UPDATE {' . $table . '} SET ' . $field . ' = ' .
db_type_placeholder($spec['type']);
3020
    $result = db_query($sql, $spec['initial']);
3030
    $ret[] = array('success' => $result !== FALSE, 'query' =>
check_plain($sql . ' (' . $spec['initial'] . ')'));
3040
  }
3050
  if ($fixnull) {
3060
    $spec['not null'] = TRUE;
3070
    db_change_field($ret, $table, $field, $field, $spec);
3080
  }
3090
}
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
 */
3212027
function db_drop_field(&$ret, $table, $field) {
3220
  $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP ' . $field);
3230
}
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
 */
3372027
function db_field_set_default(&$ret, $table, $field, $default) {
3380
  if ($default == NULL) {
3390
    $default = 'NULL';
3400
  }
341
  else {
3420
    $default = is_string($default) ? "'$default'" : $default;
343
  }
344
3450
  $ret[] = update_sql('ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field
. ' SET DEFAULT ' . $default);
3460
}
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
 */
3582027
function db_field_set_no_default(&$ret, $table, $field) {
3590
  $ret[] = update_sql('ALTER TABLE {' . $table . '} ALTER COLUMN ' . $field
. ' DROP DEFAULT');
3600
}
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
 */
3722027
function db_add_primary_key(&$ret, $table, $fields) {
3730
  $ret[] = update_sql('ALTER TABLE {' . $table . '} ADD PRIMARY KEY (' .
3740
    _db_create_key_sql($fields) . ')');
3750
}
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
 */
3852027
function db_drop_primary_key(&$ret, $table) {
3860
  $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP PRIMARY KEY');
3870
}
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
 */
4012027
function db_add_unique_key(&$ret, $table, $name, $fields) {
4020
  $ret[] = update_sql('ALTER TABLE {' . $table . '} ADD UNIQUE KEY ' .
4030
    $name . ' (' . _db_create_key_sql($fields) . ')');
4040
}
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
 */
4162027
function db_drop_unique_key(&$ret, $table, $name) {
4170
  $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP KEY ' . $name);
4180
}
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
 */
4322027
function db_add_index(&$ret, $table, $name, $fields) {
4330
  $query = 'ALTER TABLE {' . $table . '} ADD INDEX ' . $name . ' (' .
_db_create_key_sql($fields) . ')';
4340
  $ret[] = update_sql($query);
4350
}
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
 */
4472027
function db_drop_index(&$ret, $table, $name) {
4480
  $ret[] = update_sql('ALTER TABLE {' . $table . '} DROP INDEX ' . $name);
4490
}
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
5142027
function db_change_field(&$ret, $table, $field, $field_new, $spec,
$keys_new = array()) {
5150
  $sql = 'ALTER TABLE {' . $table . '} CHANGE ' . $field . ' ' .
5160
    _db_create_field_sql($field_new, _db_process_field($spec));
5170
  if (count($keys_new)) {
5180
    $sql .= ', ADD ' . implode(', ADD ', _db_create_keys_sql($keys_new));
5190
  }
5200
  $ret[] = update_sql($sql);
5210
}
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
 */
5312027
function db_last_insert_id($table, $field) {
532152
  return db_result(db_query('SELECT LAST_INSERT_ID()'));
5330
}
5342027