Schema.php 18.3 KB
Newer Older
1
2
<?php

Crell's avatar
Crell committed
3
4
5
6
7
/**
 * @file
 * Definition of Drupal\Core\Database\Driver\mysql\Schema
 */

8
namespace Drupal\Core\Database\Driver\mysql;
9

10
11
12
13
14
use Drupal\Core\Database\Database;
use Drupal\Core\Database\Query\Condition;
use Drupal\Core\Database\SchemaObjectExistsException;
use Drupal\Core\Database\SchemaObjectDoesNotExistException;
use Drupal\Core\Database\Schema as DatabaseSchema;
15

16
17
use Exception;

18
19
20
21
22
/**
 * @ingroup schemaapi
 * @{
 */

23
class Schema extends DatabaseSchema {
24

25
26
27
28
29
30
31
32
33
34
  /**
   * Maximum length of a table comment in MySQL.
   */
  const COMMENT_MAX_TABLE = 60;

  /**
   * Maximum length of a column comment in MySQL.
   */
  const COMMENT_MAX_COLUMN = 255;

35
  /**
36
   * Get information about the table and database name from the prefix.
37
38
39
40
   *
   * @return
   *   A keyed array with information about the database, table name and prefix.
   */
41
  protected function getPrefixInfo($table = 'default', $add_prefix = TRUE) {
42
    $info = array('prefix' => $this->connection->tablePrefix($table));
43
44
45
46
47
48
    if ($add_prefix) {
      $table = $info['prefix'] . $table;
    }
    if (($pos = strpos($table, '.')) !== FALSE) {
      $info['database'] = substr($table, 0, $pos);
      $info['table'] = substr($table, ++$pos);
49
50
51
52
    }
    else {
      $db_info = Database::getConnectionInfo();
      $info['database'] = $db_info['default']['database'];
53
      $info['table'] = $table;
54
55
56
57
    }
    return $info;
  }

58
  /**
Dries's avatar
Dries committed
59
   * Build a condition to match a table name against a standard information_schema.
60
61
62
   *
   * MySQL uses databases like schemas rather than catalogs so when we build
   * a condition to query the information_schema.tables, we set the default
Dries's avatar
Dries committed
63
   * database as the schema unless specified otherwise, and exclude table_catalog
64
65
   * from the condition criteria.
   */
66
  protected function buildTableNameCondition($table_name, $operator = '=', $add_prefix = TRUE) {
67
    $info = $this->connection->getConnectionOptions();
68

69
    $table_info = $this->getPrefixInfo($table_name, $add_prefix);
70

71
    $condition = new Condition('AND');
72
73
    $condition->condition('table_schema', $table_info['database']);
    $condition->condition('table_name', $table_info['table'], $operator);
74
75
    return $condition;
  }
76
77
78
79
80
81
82
83
84
85
86
87

  /**
   * Generate SQL to create a new table from a Drupal schema definition.
   *
   * @param $name
   *   The name of the table to create.
   * @param $table
   *   A Schema API table definition array.
   * @return
   *   An array of SQL statements to create the table.
   */
  protected function createTableSql($name, $table) {
88
89
90
    $info = $this->connection->getConnectionOptions();

    // Provide defaults if needed.
91
92
    $table += array(
      'mysql_engine' => 'InnoDB',
93
      'mysql_character_set' => 'utf8',
94
    );
95

96
    $sql = "CREATE TABLE {" . $name . "} (\n";
97

98
99
100
101
    // Add the SQL statement for each field.
    foreach ($table['fields'] as $field_name => $field) {
      $sql .= $this->createFieldSql($field_name, $this->processField($field)) . ", \n";
    }
102

103
104
105
106
107
    // Process keys & indexes.
    $keys = $this->createKeysSql($table);
    if (count($keys)) {
      $sql .= implode(", \n", $keys) . ", \n";
    }
108

109
110
    // Remove the last comma and space.
    $sql = substr($sql, 0, -3) . "\n) ";
111

112
    $sql .= 'ENGINE = ' . $table['mysql_engine'] . ' DEFAULT CHARACTER SET ' . $table['mysql_character_set'];
113
114
115
116
117
118
119
    // By default, MySQL uses the default collation for new tables, which is
    // 'utf8_general_ci' for utf8. If an alternate collation has been set, it
    // needs to be explicitly specified.
    // @see DatabaseConnection_mysql
    if (!empty($info['collation'])) {
      $sql .= ' COLLATE ' . $info['collation'];
    }
120

121
122
123
124
125
    // Add table comment.
    if (!empty($table['description'])) {
      $sql .= ' COMMENT ' . $this->prepareComment($table['description'], self::COMMENT_MAX_TABLE);
    }

126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
    return array($sql);
  }

  /**
   * Create an SQL string for a field to be used in table creation or alteration.
   *
   * Before passing a field out of a schema definition into this function it has
   * to be processed by _db_process_field().
   *
   * @param $name
   *   Name of the field.
   * @param $spec
   *   The field specification, as per the schema data structure format.
   */
  protected function createFieldSql($name, $spec) {
    $sql = "`" . $name . "` " . $spec['mysql_type'];
142

143
144
145
146
147
148
149
    if (in_array($spec['mysql_type'], array('VARCHAR', 'CHAR', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'TEXT'))) {
      if (isset($spec['length'])) {
        $sql .= '(' . $spec['length'] . ')';
      }
      if (!empty($spec['binary'])) {
        $sql .= ' BINARY';
      }
150
151
152
153
    }
    elseif (isset($spec['precision']) && isset($spec['scale'])) {
      $sql .= '(' . $spec['precision'] . ', ' . $spec['scale'] . ')';
    }
154

155
156
157
    if (!empty($spec['unsigned'])) {
      $sql .= ' unsigned';
    }
158

159
160
161
162
163
164
165
    if (isset($spec['not null'])) {
      if ($spec['not null']) {
        $sql .= ' NOT NULL';
      }
      else {
        $sql .= ' NULL';
      }
166
    }
167

168
169
170
    if (!empty($spec['auto_increment'])) {
      $sql .= ' auto_increment';
    }
171

172
    // $spec['default'] can be NULL, so we explicitly check for the key here.
173
    if (array_key_exists('default', $spec)) {
174
175
176
      if (is_string($spec['default'])) {
        $spec['default'] = "'" . $spec['default'] . "'";
      }
177
      elseif (!isset($spec['default'])) {
178
179
        $spec['default'] = 'NULL';
      }
180
181
      $sql .= ' DEFAULT ' . $spec['default'];
    }
182

183
184
185
    if (empty($spec['not null']) && !isset($spec['default'])) {
      $sql .= ' DEFAULT NULL';
    }
186

187
188
189
190
191
    // Add column comment.
    if (!empty($spec['description'])) {
      $sql .= ' COMMENT ' . $this->prepareComment($spec['description'], self::COMMENT_MAX_COLUMN);
    }

192
193
    return $sql;
  }
194

195
196
197
198
199
200
201
  /**
   * Set database-engine specific properties for a field.
   *
   * @param $field
   *   A field description array, as specified in the schema documentation.
   */
  protected function processField($field) {
202

203
204
205
    if (!isset($field['size'])) {
      $field['size'] = 'normal';
    }
206

207
    // Set the correct database-engine specific datatype.
208
209
210
211
212
    // In case one is already provided, force it to uppercase.
    if (isset($field['mysql_type'])) {
      $field['mysql_type'] = drupal_strtoupper($field['mysql_type']);
    }
    else {
213
      $map = $this->getFieldTypeMap();
214
215
      $field['mysql_type'] = $map[$field['type'] . ':' . $field['size']];
    }
216

217
    if (isset($field['type']) && $field['type'] == 'serial') {
218
219
      $field['auto_increment'] = TRUE;
    }
220

221
222
223
224
    return $field;
  }

  public function getFieldTypeMap() {
225
    // Put :normal last so it gets preserved by array_flip. This makes
226
227
    // it much easier for modules (such as schema.module) to map
    // database types back into schema types.
228
    // $map does not use drupal_static as its value never changes.
229
230
231
    static $map = array(
      'varchar:normal'  => 'VARCHAR',
      'char:normal'     => 'CHAR',
232

233
234
235
236
237
      'text:tiny'       => 'TINYTEXT',
      'text:small'      => 'TINYTEXT',
      'text:medium'     => 'MEDIUMTEXT',
      'text:big'        => 'LONGTEXT',
      'text:normal'     => 'TEXT',
238

239
240
241
242
243
      'serial:tiny'     => 'TINYINT',
      'serial:small'    => 'SMALLINT',
      'serial:medium'   => 'MEDIUMINT',
      'serial:big'      => 'BIGINT',
      'serial:normal'   => 'INT',
244

245
246
247
248
249
      'int:tiny'        => 'TINYINT',
      'int:small'       => 'SMALLINT',
      'int:medium'      => 'MEDIUMINT',
      'int:big'         => 'BIGINT',
      'int:normal'      => 'INT',
250

251
252
253
254
255
      'float:tiny'      => 'FLOAT',
      'float:small'     => 'FLOAT',
      'float:medium'    => 'FLOAT',
      'float:big'       => 'DOUBLE',
      'float:normal'    => 'FLOAT',
256

257
      'numeric:normal'  => 'DECIMAL',
258

259
260
261
262
263
      'blob:big'        => 'LONGBLOB',
      'blob:normal'     => 'BLOB',
    );
    return $map;
  }
264

265
266
267
268
269
270
271
272
  protected function createKeysSql($spec) {
    $keys = array();

    if (!empty($spec['primary key'])) {
      $keys[] = 'PRIMARY KEY (' . $this->createKeysSqlHelper($spec['primary key']) . ')';
    }
    if (!empty($spec['unique keys'])) {
      foreach ($spec['unique keys'] as $key => $fields) {
273
        $keys[] = 'UNIQUE KEY `' . $key . '` (' . $this->createKeysSqlHelper($fields) . ')';
274
275
276
277
      }
    }
    if (!empty($spec['indexes'])) {
      foreach ($spec['indexes'] as $index => $fields) {
278
        $keys[] = 'INDEX `' . $index . '` (' . $this->createKeysSqlHelper($fields) . ')';
279
280
281
282
283
      }
    }

    return $keys;
  }
284

285
  protected function createKeySql($fields) {
286
    $return = array();
287
288
    foreach ($fields as $field) {
      if (is_array($field)) {
289
        $return[] = '`' . $field[0] . '`(' . $field[1] . ')';
290
291
      }
      else {
292
        $return[] = '`' . $field . '`';
293
294
      }
    }
295
    return implode(', ', $return);
296
297
298
  }

  protected function createKeysSqlHelper($fields) {
299
    $return = array();
300
301
    foreach ($fields as $field) {
      if (is_array($field)) {
302
        $return[] = '`' . $field[0] . '`(' . $field[1] . ')';
303
304
      }
      else {
305
        $return[] = '`' . $field . '`';
306
307
      }
    }
308
    return implode(', ', $return);
309
310
  }

311
  public function renameTable($table, $new_name) {
312
    if (!$this->tableExists($table)) {
313
      throw new SchemaObjectDoesNotExistException(t("Cannot rename %table to %table_new: table %table doesn't exist.", array('%table' => $table, '%table_new' => $new_name)));
314
315
    }
    if ($this->tableExists($new_name)) {
316
      throw new SchemaObjectExistsException(t("Cannot rename %table to %table_new: table %table_new already exists.", array('%table' => $table, '%table_new' => $new_name)));
317
318
    }

319
320
    $info = $this->getPrefixInfo($new_name);
    return $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO `' . $info['table'] . '`');
321
  }
322

323
  public function dropTable($table) {
324
325
326
327
    if (!$this->tableExists($table)) {
      return FALSE;
    }

328
    $this->connection->query('DROP TABLE {' . $table . '}');
329
    return TRUE;
330
331
  }

332
  public function addField($table, $field, $spec, $keys_new = array()) {
333
    if (!$this->tableExists($table)) {
334
      throw new SchemaObjectDoesNotExistException(t("Cannot add field %table.%field: table doesn't exist.", array('%field' => $field, '%table' => $table)));
335
    }
336
    if ($this->fieldExists($table, $field)) {
337
      throw new SchemaObjectExistsException(t("Cannot add field %table.%field: field already exists.", array('%field' => $field, '%table' => $table)));
338
339
    }

340
341
342
343
344
345
346
    $fixnull = FALSE;
    if (!empty($spec['not null']) && !isset($spec['default'])) {
      $fixnull = TRUE;
      $spec['not null'] = FALSE;
    }
    $query = 'ALTER TABLE {' . $table . '} ADD ';
    $query .= $this->createFieldSql($field, $this->processField($spec));
347
348
    if ($keys_sql = $this->createKeysSql($keys_new)) {
      $query .= ', ADD ' . implode(', ADD ', $keys_sql);
349
    }
350
    $this->connection->query($query);
351
    if (isset($spec['initial'])) {
352
      $this->connection->update($table)
353
        ->fields(array($field => $spec['initial']))
354
        ->execute();
355
356
357
    }
    if ($fixnull) {
      $spec['not null'] = TRUE;
358
      $this->changeField($table, $field, $field, $spec);
359
360
361
    }
  }

362
  public function dropField($table, $field) {
363
    if (!$this->fieldExists($table, $field)) {
364
365
366
      return FALSE;
    }

367
    $this->connection->query('ALTER TABLE {' . $table . '} DROP `' . $field . '`');
368
    return TRUE;
369
370
  }

371
  public function fieldSetDefault($table, $field, $default) {
372
    if (!$this->fieldExists($table, $field)) {
373
      throw new SchemaObjectDoesNotExistException(t("Cannot set default value of field %table.%field: field doesn't exist.", array('%table' => $table, '%field' => $field)));
374
375
    }

376
    if (!isset($default)) {
377
378
379
380
381
      $default = 'NULL';
    }
    else {
      $default = is_string($default) ? "'$default'" : $default;
    }
382

383
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER COLUMN `' . $field . '` SET DEFAULT ' . $default);
384
385
  }

386
  public function fieldSetNoDefault($table, $field) {
387
    if (!$this->fieldExists($table, $field)) {
388
      throw new SchemaObjectDoesNotExistException(t("Cannot remove default value of field %table.%field: field doesn't exist.", array('%table' => $table, '%field' => $field)));
389
390
    }

391
392
393
394
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER COLUMN `' . $field . '` DROP DEFAULT');
  }

  public function indexExists($table, $name) {
395
396
397
    // Returns one row for each column in the index. Result is string or FALSE.
    // Details at http://dev.mysql.com/doc/refman/5.0/en/show-index.html
    $row = $this->connection->query('SHOW INDEX FROM {' . $table . "} WHERE key_name = '$name'")->fetchAssoc();
398
    return isset($row['Key_name']);
399
400
  }

401
  public function addPrimaryKey($table, $fields) {
402
    if (!$this->tableExists($table)) {
403
      throw new SchemaObjectDoesNotExistException(t("Cannot add primary key to table %table: table doesn't exist.", array('%table' => $table)));
404
405
    }
    if ($this->indexExists($table, 'PRIMARY')) {
406
      throw new SchemaObjectExistsException(t("Cannot add primary key to table %table: primary key already exists.", array('%table' => $table)));
407
408
    }

409
    $this->connection->query('ALTER TABLE {' . $table . '} ADD PRIMARY KEY (' . $this->createKeySql($fields) . ')');
410
  }
411

412
  public function dropPrimaryKey($table) {
413
414
415
416
    if (!$this->indexExists($table, 'PRIMARY')) {
      return FALSE;
    }

417
    $this->connection->query('ALTER TABLE {' . $table . '} DROP PRIMARY KEY');
418
    return TRUE;
419
420
  }

421
  public function addUniqueKey($table, $name, $fields) {
422
    if (!$this->tableExists($table)) {
423
      throw new SchemaObjectDoesNotExistException(t("Cannot add unique key %name to table %table: table doesn't exist.", array('%table' => $table, '%name' => $name)));
424
425
    }
    if ($this->indexExists($table, $name)) {
426
      throw new SchemaObjectExistsException(t("Cannot add unique key %name to table %table: unique key already exists.", array('%table' => $table, '%name' => $name)));
427
428
    }

429
    $this->connection->query('ALTER TABLE {' . $table . '} ADD UNIQUE KEY `' . $name . '` (' . $this->createKeySql($fields) . ')');
430
431
  }

432
  public function dropUniqueKey($table, $name) {
433
434
435
436
    if (!$this->indexExists($table, $name)) {
      return FALSE;
    }

437
    $this->connection->query('ALTER TABLE {' . $table . '} DROP KEY `' . $name . '`');
438
    return TRUE;
439
  }
440

441
  public function addIndex($table, $name, $fields) {
442
    if (!$this->tableExists($table)) {
443
      throw new SchemaObjectDoesNotExistException(t("Cannot add index %name to table %table: table doesn't exist.", array('%table' => $table, '%name' => $name)));
444
445
    }
    if ($this->indexExists($table, $name)) {
446
      throw new SchemaObjectExistsException(t("Cannot add index %name to table %table: index already exists.", array('%table' => $table, '%name' => $name)));
447
448
    }

449
    $this->connection->query('ALTER TABLE {' . $table . '} ADD INDEX `' . $name . '` (' . $this->createKeySql($fields) . ')');
450
451
  }

452
  public function dropIndex($table, $name) {
453
454
455
456
    if (!$this->indexExists($table, $name)) {
      return FALSE;
    }

457
    $this->connection->query('ALTER TABLE {' . $table . '} DROP INDEX `' . $name . '`');
458
    return TRUE;
459
  }
460

461
  public function changeField($table, $field, $field_new, $spec, $keys_new = array()) {
462
    if (!$this->fieldExists($table, $field)) {
463
      throw new SchemaObjectDoesNotExistException(t("Cannot change the definition of field %table.%name: field doesn't exist.", array('%table' => $table, '%name' => $field)));
464
    }
465
    if (($field != $field_new) && $this->fieldExists($table, $field_new)) {
466
      throw new SchemaObjectExistsException(t("Cannot rename field %table.%name to %name_new: target field already exists.", array('%table' => $table, '%name' => $field, '%name_new' => $field_new)));
467
468
    }

469
    $sql = 'ALTER TABLE {' . $table . '} CHANGE `' . $field . '` ' . $this->createFieldSql($field_new, $this->processField($spec));
470
471
    if ($keys_sql = $this->createKeysSql($keys_new)) {
      $sql .= ', ADD ' . implode(', ADD ', $keys_sql);
472
    }
473
    $this->connection->query($sql);
474
475
  }

476
477
478
  public function prepareComment($comment, $length = NULL) {
    // Work around a bug in some versions of PDO, see http://bugs.php.net/bug.php?id=41125
    $comment = str_replace("'", '’', $comment);
479

480
481
482
483
484
485
486
487
488
489
490
491
492
    // Truncate comment to maximum comment length.
    if (isset($length)) {
      // Add table prefixes before truncating.
      $comment = truncate_utf8($this->connection->prefixTables($comment), $length, TRUE, TRUE);
    }

    return $this->connection->quote($comment);
  }

  /**
   * Retrieve a table or column comment.
   */
  public function getComment($table, $column = NULL) {
493
    $condition = $this->buildTableNameCondition($table);
494
495
    if (isset($column)) {
      $condition->condition('column_name', $column);
496
      $condition->compile($this->connection, $this);
497
      // Don't use {} around information_schema.columns table.
498
      return $this->connection->query("SELECT column_comment FROM information_schema.columns WHERE " . (string) $condition, $condition->arguments())->fetchField();
499
    }
500
    $condition->compile($this->connection, $this);
501
    // Don't use {} around information_schema.tables table.
502
    $comment = $this->connection->query("SELECT table_comment FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments())->fetchField();
503
504
    // Work-around for MySQL 5.0 bug http://bugs.mysql.com/bug.php?id=11379
    return preg_replace('/; InnoDB free:.*$/', '', $comment);
505
506
  }

507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
  public function tableExists($table) {
    // The information_schema table is very slow to query under MySQL 5.0.
    // Instead, we try to select from the table in question.  If it fails,
    // the most likely reason is that it does not exist. That is dramatically
    // faster than using information_schema.
    // @link http://bugs.mysql.com/bug.php?id=19588
    // @todo: This override should be removed once we require a version of MySQL
    // that has that bug fixed.
    try {
      $this->connection->queryRange("SELECT 1 FROM {" . $table . "}", 0, 1);
      return TRUE;
    }
    catch (Exception $e) {
      return FALSE;
    }
  }

  public function fieldExists($table, $column) {
    // The information_schema table is very slow to query under MySQL 5.0.
    // Instead, we try to select from the table and field in question. If it
    // fails, the most likely reason is that it does not exist. That is
    // dramatically faster than using information_schema.
    // @link http://bugs.mysql.com/bug.php?id=19588
    // @todo: This override should be removed once we require a version of MySQL
    // that has that bug fixed.
    try {
      $this->connection->queryRange("SELECT $column FROM {" . $table . "}", 0, 1);
      return TRUE;
    }
    catch (Exception $e) {
      return FALSE;
    }
  }

541
542
543
544
545
}

/**
 * @} End of "ingroup schemaapi".
 */