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

3
namespace Drupal\Core\Database\Driver\mysql;
4

5 6 7 8 9
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;
10

11 12
use Exception;

13 14 15 16 17
/**
 * @ingroup schemaapi
 * @{
 */

18
class Schema extends DatabaseSchema {
19

20 21 22 23 24 25 26 27 28 29
  /**
   * 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;

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

53
  /**
Dries's avatar
Dries committed
54
   * Build a condition to match a table name against a standard information_schema.
55 56 57
   *
   * 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
58
   * database as the schema unless specified otherwise, and exclude table_catalog
59 60
   * from the condition criteria.
   */
61
  protected function buildTableNameCondition($table_name, $operator = '=', $add_prefix = TRUE) {
62
    $info = $this->connection->getConnectionOptions();
63

64
    $table_info = $this->getPrefixInfo($table_name, $add_prefix);
65

66
    $condition = new Condition('AND');
67 68
    $condition->condition('table_schema', $table_info['database']);
    $condition->condition('table_name', $table_info['table'], $operator);
69 70
    return $condition;
  }
71 72 73 74 75 76 77 78 79 80 81 82

  /**
   * 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) {
83 84 85
    $info = $this->connection->getConnectionOptions();

    // Provide defaults if needed.
86 87
    $table += array(
      'mysql_engine' => 'InnoDB',
88
      'mysql_character_set' => 'utf8',
89
    );
90

91
    $sql = "CREATE TABLE {" . $name . "} (\n";
92

93 94 95 96
    // Add the SQL statement for each field.
    foreach ($table['fields'] as $field_name => $field) {
      $sql .= $this->createFieldSql($field_name, $this->processField($field)) . ", \n";
    }
97

98 99 100 101 102
    // Process keys & indexes.
    $keys = $this->createKeysSql($table);
    if (count($keys)) {
      $sql .= implode(", \n", $keys) . ", \n";
    }
103

104 105
    // Remove the last comma and space.
    $sql = substr($sql, 0, -3) . "\n) ";
106

107
    $sql .= 'ENGINE = ' . $table['mysql_engine'] . ' DEFAULT CHARACTER SET ' . $table['mysql_character_set'];
108 109 110 111 112 113 114
    // 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'];
    }
115

116 117 118 119 120
    // Add table comment.
    if (!empty($table['description'])) {
      $sql .= ' COMMENT ' . $this->prepareComment($table['description'], self::COMMENT_MAX_TABLE);
    }

121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
    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'];
137

138
    if (in_array($spec['mysql_type'], array('VARCHAR', 'CHAR', 'TINYTEXT', 'MEDIUMTEXT', 'LONGTEXT', 'TEXT')) && isset($spec['length'])) {
139 140 141 142 143
      $sql .= '(' . $spec['length'] . ')';
    }
    elseif (isset($spec['precision']) && isset($spec['scale'])) {
      $sql .= '(' . $spec['precision'] . ', ' . $spec['scale'] . ')';
    }
144

145 146 147
    if (!empty($spec['unsigned'])) {
      $sql .= ' unsigned';
    }
148

149 150 151 152 153 154 155
    if (isset($spec['not null'])) {
      if ($spec['not null']) {
        $sql .= ' NOT NULL';
      }
      else {
        $sql .= ' NULL';
      }
156
    }
157

158 159 160
    if (!empty($spec['auto_increment'])) {
      $sql .= ' auto_increment';
    }
161

162
    // $spec['default'] can be NULL, so we explicitly check for the key here.
163
    if (array_key_exists('default', $spec)) {
164 165 166
      if (is_string($spec['default'])) {
        $spec['default'] = "'" . $spec['default'] . "'";
      }
167
      elseif (!isset($spec['default'])) {
168 169
        $spec['default'] = 'NULL';
      }
170 171
      $sql .= ' DEFAULT ' . $spec['default'];
    }
172

173 174 175
    if (empty($spec['not null']) && !isset($spec['default'])) {
      $sql .= ' DEFAULT NULL';
    }
176

177 178 179 180 181
    // Add column comment.
    if (!empty($spec['description'])) {
      $sql .= ' COMMENT ' . $this->prepareComment($spec['description'], self::COMMENT_MAX_COLUMN);
    }

182 183
    return $sql;
  }
184

185 186 187 188 189 190 191
  /**
   * Set database-engine specific properties for a field.
   *
   * @param $field
   *   A field description array, as specified in the schema documentation.
   */
  protected function processField($field) {
192

193 194 195
    if (!isset($field['size'])) {
      $field['size'] = 'normal';
    }
196

197
    // Set the correct database-engine specific datatype.
198 199 200 201 202
    // In case one is already provided, force it to uppercase.
    if (isset($field['mysql_type'])) {
      $field['mysql_type'] = drupal_strtoupper($field['mysql_type']);
    }
    else {
203
      $map = $this->getFieldTypeMap();
204 205
      $field['mysql_type'] = $map[$field['type'] . ':' . $field['size']];
    }
206

207
    if (isset($field['type']) && $field['type'] == 'serial') {
208 209
      $field['auto_increment'] = TRUE;
    }
210

211 212 213 214
    return $field;
  }

  public function getFieldTypeMap() {
215
    // Put :normal last so it gets preserved by array_flip. This makes
216 217
    // it much easier for modules (such as schema.module) to map
    // database types back into schema types.
218
    // $map does not use drupal_static as its value never changes.
219 220 221
    static $map = array(
      'varchar:normal'  => 'VARCHAR',
      'char:normal'     => 'CHAR',
222

223 224 225 226 227
      'text:tiny'       => 'TINYTEXT',
      'text:small'      => 'TINYTEXT',
      'text:medium'     => 'MEDIUMTEXT',
      'text:big'        => 'LONGTEXT',
      'text:normal'     => 'TEXT',
228

229 230 231 232 233
      'serial:tiny'     => 'TINYINT',
      'serial:small'    => 'SMALLINT',
      'serial:medium'   => 'MEDIUMINT',
      'serial:big'      => 'BIGINT',
      'serial:normal'   => 'INT',
234

235 236 237 238 239
      'int:tiny'        => 'TINYINT',
      'int:small'       => 'SMALLINT',
      'int:medium'      => 'MEDIUMINT',
      'int:big'         => 'BIGINT',
      'int:normal'      => 'INT',
240

241 242 243 244 245
      'float:tiny'      => 'FLOAT',
      'float:small'     => 'FLOAT',
      'float:medium'    => 'FLOAT',
      'float:big'       => 'DOUBLE',
      'float:normal'    => 'FLOAT',
246

247
      'numeric:normal'  => 'DECIMAL',
248

249 250 251 252 253
      'blob:big'        => 'LONGBLOB',
      'blob:normal'     => 'BLOB',
    );
    return $map;
  }
254

255 256 257 258 259 260 261 262
  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) {
263
        $keys[] = 'UNIQUE KEY `' . $key . '` (' . $this->createKeysSqlHelper($fields) . ')';
264 265 266 267
      }
    }
    if (!empty($spec['indexes'])) {
      foreach ($spec['indexes'] as $index => $fields) {
268
        $keys[] = 'INDEX `' . $index . '` (' . $this->createKeysSqlHelper($fields) . ')';
269 270 271 272 273
      }
    }

    return $keys;
  }
274

275
  protected function createKeySql($fields) {
276
    $return = array();
277 278
    foreach ($fields as $field) {
      if (is_array($field)) {
279
        $return[] = '`' . $field[0] . '`(' . $field[1] . ')';
280 281
      }
      else {
282
        $return[] = '`' . $field . '`';
283 284
      }
    }
285
    return implode(', ', $return);
286 287 288
  }

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

301
  public function renameTable($table, $new_name) {
302
    if (!$this->tableExists($table)) {
303
      throw new SchemaObjectDoesNotExistException(t("Cannot rename %table to %table_new: table %table doesn't exist.", array('%table' => $table, '%table_new' => $new_name)));
304 305
    }
    if ($this->tableExists($new_name)) {
306
      throw new SchemaObjectExistsException(t("Cannot rename %table to %table_new: table %table_new already exists.", array('%table' => $table, '%table_new' => $new_name)));
307 308
    }

309 310
    $info = $this->getPrefixInfo($new_name);
    return $this->connection->query('ALTER TABLE {' . $table . '} RENAME TO `' . $info['table'] . '`');
311
  }
312

313
  public function dropTable($table) {
314 315 316 317
    if (!$this->tableExists($table)) {
      return FALSE;
    }

318
    $this->connection->query('DROP TABLE {' . $table . '}');
319
    return TRUE;
320 321
  }

322
  public function addField($table, $field, $spec, $keys_new = array()) {
323
    if (!$this->tableExists($table)) {
324
      throw new SchemaObjectDoesNotExistException(t("Cannot add field %table.%field: table doesn't exist.", array('%field' => $field, '%table' => $table)));
325
    }
326
    if ($this->fieldExists($table, $field)) {
327
      throw new SchemaObjectExistsException(t("Cannot add field %table.%field: field already exists.", array('%field' => $field, '%table' => $table)));
328 329
    }

330 331 332 333 334 335 336
    $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));
337 338
    if ($keys_sql = $this->createKeysSql($keys_new)) {
      $query .= ', ADD ' . implode(', ADD ', $keys_sql);
339
    }
340
    $this->connection->query($query);
341
    if (isset($spec['initial'])) {
342
      $this->connection->update($table)
343
        ->fields(array($field => $spec['initial']))
344
        ->execute();
345 346 347
    }
    if ($fixnull) {
      $spec['not null'] = TRUE;
348
      $this->changeField($table, $field, $field, $spec);
349 350 351
    }
  }

352
  public function dropField($table, $field) {
353
    if (!$this->fieldExists($table, $field)) {
354 355 356
      return FALSE;
    }

357
    $this->connection->query('ALTER TABLE {' . $table . '} DROP `' . $field . '`');
358
    return TRUE;
359 360
  }

361
  public function fieldSetDefault($table, $field, $default) {
362
    if (!$this->fieldExists($table, $field)) {
363
      throw new SchemaObjectDoesNotExistException(t("Cannot set default value of field %table.%field: field doesn't exist.", array('%table' => $table, '%field' => $field)));
364 365
    }

366
    if (!isset($default)) {
367 368 369 370 371
      $default = 'NULL';
    }
    else {
      $default = is_string($default) ? "'$default'" : $default;
    }
372

373
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER COLUMN `' . $field . '` SET DEFAULT ' . $default);
374 375
  }

376
  public function fieldSetNoDefault($table, $field) {
377
    if (!$this->fieldExists($table, $field)) {
378
      throw new SchemaObjectDoesNotExistException(t("Cannot remove default value of field %table.%field: field doesn't exist.", array('%table' => $table, '%field' => $field)));
379 380
    }

381 382 383 384
    $this->connection->query('ALTER TABLE {' . $table . '} ALTER COLUMN `' . $field . '` DROP DEFAULT');
  }

  public function indexExists($table, $name) {
385 386 387 388
    // 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();
    return isset($row['key_name']);
389 390
  }

391
  public function addPrimaryKey($table, $fields) {
392
    if (!$this->tableExists($table)) {
393
      throw new SchemaObjectDoesNotExistException(t("Cannot add primary key to table %table: table doesn't exist.", array('%table' => $table)));
394 395
    }
    if ($this->indexExists($table, 'PRIMARY')) {
396
      throw new SchemaObjectExistsException(t("Cannot add primary key to table %table: primary key already exists.", array('%table' => $table)));
397 398
    }

399
    $this->connection->query('ALTER TABLE {' . $table . '} ADD PRIMARY KEY (' . $this->createKeySql($fields) . ')');
400
  }
401

402
  public function dropPrimaryKey($table) {
403 404 405 406
    if (!$this->indexExists($table, 'PRIMARY')) {
      return FALSE;
    }

407
    $this->connection->query('ALTER TABLE {' . $table . '} DROP PRIMARY KEY');
408
    return TRUE;
409 410
  }

411
  public function addUniqueKey($table, $name, $fields) {
412
    if (!$this->tableExists($table)) {
413
      throw new SchemaObjectDoesNotExistException(t("Cannot add unique key %name to table %table: table doesn't exist.", array('%table' => $table, '%name' => $name)));
414 415
    }
    if ($this->indexExists($table, $name)) {
416
      throw new SchemaObjectExistsException(t("Cannot add unique key %name to table %table: unique key already exists.", array('%table' => $table, '%name' => $name)));
417 418
    }

419
    $this->connection->query('ALTER TABLE {' . $table . '} ADD UNIQUE KEY `' . $name . '` (' . $this->createKeySql($fields) . ')');
420 421
  }

422
  public function dropUniqueKey($table, $name) {
423 424 425 426
    if (!$this->indexExists($table, $name)) {
      return FALSE;
    }

427
    $this->connection->query('ALTER TABLE {' . $table . '} DROP KEY `' . $name . '`');
428
    return TRUE;
429
  }
430

431
  public function addIndex($table, $name, $fields) {
432
    if (!$this->tableExists($table)) {
433
      throw new SchemaObjectDoesNotExistException(t("Cannot add index %name to table %table: table doesn't exist.", array('%table' => $table, '%name' => $name)));
434 435
    }
    if ($this->indexExists($table, $name)) {
436
      throw new SchemaObjectExistsException(t("Cannot add index %name to table %table: index already exists.", array('%table' => $table, '%name' => $name)));
437 438
    }

439
    $this->connection->query('ALTER TABLE {' . $table . '} ADD INDEX `' . $name . '` (' . $this->createKeySql($fields) . ')');
440 441
  }

442
  public function dropIndex($table, $name) {
443 444 445 446
    if (!$this->indexExists($table, $name)) {
      return FALSE;
    }

447
    $this->connection->query('ALTER TABLE {' . $table . '} DROP INDEX `' . $name . '`');
448
    return TRUE;
449
  }
450

451
  public function changeField($table, $field, $field_new, $spec, $keys_new = array()) {
452
    if (!$this->fieldExists($table, $field)) {
453
      throw new SchemaObjectDoesNotExistException(t("Cannot change the definition of field %table.%name: field doesn't exist.", array('%table' => $table, '%name' => $field)));
454
    }
455
    if (($field != $field_new) && $this->fieldExists($table, $field_new)) {
456
      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)));
457 458
    }

459
    $sql = 'ALTER TABLE {' . $table . '} CHANGE `' . $field . '` ' . $this->createFieldSql($field_new, $this->processField($spec));
460 461
    if ($keys_sql = $this->createKeysSql($keys_new)) {
      $sql .= ', ADD ' . implode(', ADD ', $keys_sql);
462
    }
463
    $this->connection->query($sql);
464 465
  }

466 467 468
  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);
469

470 471 472 473 474 475 476 477 478 479 480 481 482
    // 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) {
483
    $condition = $this->buildTableNameCondition($table);
484 485
    if (isset($column)) {
      $condition->condition('column_name', $column);
486
      $condition->compile($this->connection, $this);
487
      // Don't use {} around information_schema.columns table.
488
      return $this->connection->query("SELECT column_comment FROM information_schema.columns WHERE " . (string) $condition, $condition->arguments())->fetchField();
489
    }
490
    $condition->compile($this->connection, $this);
491
    // Don't use {} around information_schema.tables table.
492
    $comment = $this->connection->query("SELECT table_comment FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments())->fetchField();
493 494
    // Work-around for MySQL 5.0 bug http://bugs.mysql.com/bug.php?id=11379
    return preg_replace('/; InnoDB free:.*$/', '', $comment);
495 496
  }

497 498 499 500 501 502 503 504 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
  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;
    }
  }

531 532 533 534 535
}

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