3 namespace Drupal\Core\Database;
5 use Drupal\Core\Database\Query\Condition;
6 use Drupal\Core\Database\Query\PlaceholderInterface;
9 * Provides a base implementation for Database Schema.
11 abstract class Schema implements PlaceholderInterface {
14 * The database connection.
16 * @var \Drupal\Core\Database\Connection
18 protected $connection;
21 * The placeholder counter.
25 protected $placeholder = 0;
28 * Definition of prefixInfo array structure.
30 * Rather than redefining DatabaseSchema::getPrefixInfo() for each driver,
31 * by defining the defaultSchema variable only MySQL has to re-write the
34 * @see DatabaseSchema::getPrefixInfo()
38 protected $defaultSchema = 'public';
41 * A unique identifier for this query object.
43 protected $uniqueIdentifier;
45 public function __construct($connection) {
46 $this->uniqueIdentifier = uniqid('', TRUE);
47 $this->connection = $connection;
51 * Implements the magic __clone function.
53 public function __clone() {
54 $this->uniqueIdentifier = uniqid('', TRUE);
60 public function uniqueIdentifier() {
61 return $this->uniqueIdentifier;
67 public function nextPlaceholder() {
68 return $this->placeholder++;
72 * Get information about the table name and schema from the prefix.
75 * Name of table to look prefix up for. Defaults to 'default' because that's
76 * default key for prefix.
78 * Boolean that indicates whether the given table name should be prefixed.
81 * A keyed array with information about the schema, table name and prefix.
83 protected function getPrefixInfo($table = 'default', $add_prefix = TRUE) {
85 'schema' => $this->defaultSchema,
86 'prefix' => $this->connection->tablePrefix($table),
89 $table = $info['prefix'] . $table;
91 // If the prefix contains a period in it, then that means the prefix also
92 // contains a schema reference in which case we will change the schema key
93 // to the value before the period in the prefix. Everything after the dot
94 // will be prefixed onto the front of the table.
95 if (($pos = strpos($table, '.')) !== FALSE) {
96 // Grab everything before the period.
97 $info['schema'] = substr($table, 0, $pos);
98 // Grab everything after the dot.
99 $info['table'] = substr($table, ++$pos);
102 $info['table'] = $table;
108 * Create names for indexes, primary keys and constraints.
110 * This prevents using {} around non-table names like indexes and keys.
112 public function prefixNonTable($table) {
113 $args = func_get_args();
114 $info = $this->getPrefixInfo($table);
115 $args[0] = $info['table'];
116 return implode('_', $args);
120 * Build a condition to match a table name against a standard information_schema.
122 * The information_schema is a SQL standard that provides information about the
123 * database server and the databases, schemas, tables, columns and users within
124 * it. This makes information_schema a useful tool to use across the drupal
125 * database drivers and is used by a few different functions. The function below
126 * describes the conditions to be meet when querying information_schema.tables
127 * for drupal tables or information associated with drupal tables. Even though
128 * this is the standard method, not all databases follow standards and so this
129 * method should be overwritten by a database driver if the database provider
130 * uses alternate methods. Because information_schema.tables is used in a few
131 * different functions, a database driver will only need to override this function
132 * to make all the others work. For example see
133 * core/includes/databases/mysql/schema.inc.
136 * The name of the table in question.
138 * The operator to apply on the 'table' part of the condition.
140 * Boolean to indicate whether the table name needs to be prefixed.
142 * @return \Drupal\Core\Database\Query\Condition
143 * A Condition object.
145 protected function buildTableNameCondition($table_name, $operator = '=', $add_prefix = TRUE) {
146 $info = $this->connection->getConnectionOptions();
148 // Retrieve the table name and schema
149 $table_info = $this->getPrefixInfo($table_name, $add_prefix);
151 $condition = new Condition('AND');
152 $condition->condition('table_catalog', $info['database']);
153 $condition->condition('table_schema', $table_info['schema']);
154 $condition->condition('table_name', $table_info['table'], $operator);
159 * Check if a table exists.
162 * The name of the table in drupal (no prefixing).
165 * TRUE if the given table exists, otherwise FALSE.
167 public function tableExists($table) {
168 $condition = $this->buildTableNameCondition($table);
169 $condition->compile($this->connection, $this);
170 // Normally, we would heartily discourage the use of string
171 // concatenation for conditionals like this however, we
172 // couldn't use db_select() here because it would prefix
173 // information_schema.tables and the query would fail.
174 // Don't use {} around information_schema.tables table.
175 return (bool) $this->connection->query("SELECT 1 FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments())->fetchField();
179 * Finds all tables that are like the specified base table name.
181 * @param string $table_expression
182 * An SQL expression, for example "cache_%" (without the quotes).
185 * Both the keys and the values are the matching tables.
187 public function findTables($table_expression) {
188 // Load all the tables up front in order to take into account per-table
189 // prefixes. The actual matching is done at the bottom of the method.
190 $condition = $this->buildTableNameCondition('%', 'LIKE');
191 $condition->compile($this->connection, $this);
193 $individually_prefixed_tables = $this->connection->getUnprefixedTablesMap();
194 $default_prefix = $this->connection->tablePrefix();
195 $default_prefix_length = strlen($default_prefix);
197 // Normally, we would heartily discourage the use of string
198 // concatenation for conditionals like this however, we
199 // couldn't use db_select() here because it would prefix
200 // information_schema.tables and the query would fail.
201 // Don't use {} around information_schema.tables table.
202 $results = $this->connection->query("SELECT table_name as table_name FROM information_schema.tables WHERE " . (string) $condition, $condition->arguments());
203 foreach ($results as $table) {
204 // Take into account tables that have an individual prefix.
205 if (isset($individually_prefixed_tables[$table->table_name])) {
206 $prefix_length = strlen($this->connection->tablePrefix($individually_prefixed_tables[$table->table_name]));
208 elseif ($default_prefix && substr($table->table_name, 0, $default_prefix_length) !== $default_prefix) {
209 // This table name does not start the default prefix, which means that
210 // it is not managed by Drupal so it should be excluded from the result.
214 $prefix_length = $default_prefix_length;
217 // Remove the prefix from the returned tables.
218 $unprefixed_table_name = substr($table->table_name, $prefix_length);
220 // The pattern can match a table which is the same as the prefix. That
221 // will become an empty string when we remove the prefix, which will
222 // probably surprise the caller, besides not being a prefixed table. So
224 if (!empty($unprefixed_table_name)) {
225 $tables[$unprefixed_table_name] = $unprefixed_table_name;
229 // Convert the table expression from its SQL LIKE syntax to a regular
230 // expression and escape the delimiter that will be used for matching.
231 $table_expression = str_replace(['%', '_'], ['.*?', '.'], preg_quote($table_expression, '/'));
232 $tables = preg_grep('/^' . $table_expression . '$/i', $tables);
238 * Check if a column exists in the given table.
241 * The name of the table in drupal (no prefixing).
243 * The name of the column.
246 * TRUE if the given column exists, otherwise FALSE.
248 public function fieldExists($table, $column) {
249 $condition = $this->buildTableNameCondition($table);
250 $condition->condition('column_name', $column);
251 $condition->compile($this->connection, $this);
252 // Normally, we would heartily discourage the use of string
253 // concatenation for conditionals like this however, we
254 // couldn't use db_select() here because it would prefix
255 // information_schema.tables and the query would fail.
256 // Don't use {} around information_schema.columns table.
257 return (bool) $this->connection->query("SELECT 1 FROM information_schema.columns WHERE " . (string) $condition, $condition->arguments())->fetchField();
261 * Returns a mapping of Drupal schema field names to DB-native field types.
263 * Because different field types do not map 1:1 between databases, Drupal has
264 * its own normalized field type names. This function returns a driver-specific
265 * mapping table from Drupal names to the native names for each database.
268 * An array of Schema API field types to driver-specific field types.
270 abstract public function getFieldTypeMap();
276 * The table to be renamed.
278 * The new name for the table.
280 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
281 * If the specified table doesn't exist.
282 * @throws \Drupal\Core\Database\SchemaObjectExistsException
283 * If a table with the specified new name already exists.
285 abstract public function renameTable($table, $new_name);
291 * The table to be dropped.
294 * TRUE if the table was successfully dropped, FALSE if there was no table
295 * by that name to begin with.
297 abstract public function dropTable($table);
300 * Add a new field to a table.
303 * Name of the table to be altered.
305 * Name of the field to be added.
307 * The field specification array, as taken from a schema definition.
308 * The specification may also contain the key 'initial', the newly
309 * created field will be set to the value of the key in all rows.
310 * This is most useful for creating NOT NULL columns with no default
311 * value in existing tables.
312 * Alternatively, the 'initial_form_field' key may be used, which will
313 * auto-populate the new field with values from the specified field.
315 * (optional) Keys and indexes specification to be created on the
316 * table along with adding the field. The format is the same as a
317 * table specification but without the 'fields' element. If you are
318 * adding a type 'serial' field, you MUST specify at least one key
319 * or index including it in this array. See db_change_field() for more
322 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
323 * If the specified table doesn't exist.
324 * @throws \Drupal\Core\Database\SchemaObjectExistsException
325 * If the specified table already has a field by that name.
327 abstract public function addField($table, $field, $spec, $keys_new = []);
333 * The table to be altered.
335 * The field to be dropped.
338 * TRUE if the field was successfully dropped, FALSE if there was no field
339 * by that name to begin with.
341 abstract public function dropField($table, $field);
344 * Set the default value for a field.
347 * The table to be altered.
349 * The field to be altered.
351 * Default value to be set. NULL for 'default NULL'.
353 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
354 * If the specified table or field doesn't exist.
356 abstract public function fieldSetDefault($table, $field, $default);
359 * Set a field to have no default value.
362 * The table to be altered.
364 * The field to be altered.
366 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
367 * If the specified table or field doesn't exist.
369 abstract public function fieldSetNoDefault($table, $field);
372 * Checks if an index exists in the given table.
375 * The name of the table in drupal (no prefixing).
377 * The name of the index in drupal (no prefixing).
380 * TRUE if the given index exists, otherwise FALSE.
382 abstract public function indexExists($table, $name);
388 * The table to be altered.
390 * Fields for the primary key.
392 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
393 * If the specified table doesn't exist.
394 * @throws \Drupal\Core\Database\SchemaObjectExistsException
395 * If the specified table already has a primary key.
397 abstract public function addPrimaryKey($table, $fields);
400 * Drop the primary key.
403 * The table to be altered.
406 * TRUE if the primary key was successfully dropped, FALSE if there was no
407 * primary key on this table to begin with.
409 abstract public function dropPrimaryKey($table);
412 * Finds the primary key columns of a table, from the database.
414 * @param string $table
415 * The name of the table.
417 * @return string[]|false
418 * A simple array with the names of the columns composing the table's
419 * primary key, or FALSE if the table does not exist.
421 * @throws \RuntimeException
422 * If the driver does not override this method.
424 protected function findPrimaryKeyColumns($table) {
425 if (!$this->tableExists($table)) {
428 throw new \RuntimeException("The '" . $this->connection->driver() . "' database driver does not implement " . __METHOD__);
435 * The table to be altered.
437 * The name of the key.
439 * An array of field names.
441 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
442 * If the specified table doesn't exist.
443 * @throws \Drupal\Core\Database\SchemaObjectExistsException
444 * If the specified table already has a key by that name.
446 abstract public function addUniqueKey($table, $name, $fields);
452 * The table to be altered.
454 * The name of the key.
457 * TRUE if the key was successfully dropped, FALSE if there was no key by
458 * that name to begin with.
460 abstract public function dropUniqueKey($table, $name);
466 * The table to be altered.
468 * The name of the index.
470 * An array of field names or field information; if field information is
471 * passed, it's an array whose first element is the field name and whose
472 * second is the maximum length in the index. For example, the following
473 * will use the full length of the `foo` field, but limit the `bar` field to
476 * $fields = ['foo', ['bar', 4]];
479 * The table specification for the table to be altered. This is used in
480 * order to be able to ensure that the index length is not too long.
481 * This schema definition can usually be obtained through hook_schema(), or
482 * in case the table was created by the Entity API, through the schema
483 * handler listed in the entity class definition. For reference, see
484 * SqlContentEntityStorageSchema::getDedicatedTableSchema() and
485 * SqlContentEntityStorageSchema::getSharedTableFieldSchema().
487 * In order to prevent human error, it is recommended to pass in the
488 * complete table specification. However, in the edge case of the complete
489 * table specification not being available, we can pass in a partial table
490 * definition containing only the fields that apply to the index:
493 * // Example partial specification for a table:
495 * 'example_field' => [
496 * 'description' => 'An example field',
497 * 'type' => 'varchar',
499 * 'not null' => TRUE,
504 * 'table_example_field' => ['example_field'],
508 * Note that the above is a partial table definition and that we would
509 * usually pass a complete table definition as obtained through
510 * hook_schema() instead.
515 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
516 * If the specified table doesn't exist.
517 * @throws \Drupal\Core\Database\SchemaObjectExistsException
518 * If the specified table already has an index by that name.
520 * @todo remove the $spec argument whenever schema introspection is added.
522 abstract public function addIndex($table, $name, $fields, array $spec);
528 * The table to be altered.
530 * The name of the index.
533 * TRUE if the index was successfully dropped, FALSE if there was no index
534 * by that name to begin with.
536 abstract public function dropIndex($table, $name);
539 * Change a field definition.
541 * IMPORTANT NOTE: To maintain database portability, you have to explicitly
542 * recreate all indices and primary keys that are using the changed field.
544 * That means that you have to drop all affected keys and indexes with
545 * db_drop_{primary_key,unique_key,index}() before calling db_change_field().
546 * To recreate the keys and indices, pass the key definitions as the
547 * optional $keys_new argument directly to db_change_field().
549 * For example, suppose you have:
551 * $schema['foo'] = array(
553 * 'bar' => array('type' => 'int', 'not null' => TRUE)
555 * 'primary key' => array('bar')
558 * and you want to change foo.bar to be type serial, leaving it as the
559 * primary key. The correct sequence is:
561 * db_drop_primary_key('foo');
562 * db_change_field('foo', 'bar', 'bar',
563 * array('type' => 'serial', 'not null' => TRUE),
564 * array('primary key' => array('bar')));
567 * The reasons for this are due to the different database engines:
569 * On PostgreSQL, changing a field definition involves adding a new field
570 * and dropping an old one which* causes any indices, primary keys and
571 * sequences (from serial-type fields) that use the changed field to be dropped.
573 * On MySQL, all type 'serial' fields must be part of at least one key
574 * or index as soon as they are created. You cannot use
575 * db_add_{primary_key,unique_key,index}() for this purpose because
576 * the ALTER TABLE command will fail to add the column without a key
577 * or index specification. The solution is to use the optional
578 * $keys_new argument to create the key or index at the same time as
581 * You could use db_add_{primary_key,unique_key,index}() in all cases
582 * unless you are converting a field to be type serial. You can use
583 * the $keys_new argument in all cases.
588 * Name of the field to change.
590 * New name for the field (set to the same as $field if you don't want to change the name).
592 * The field specification for the new field.
594 * (optional) Keys and indexes specification to be created on the
595 * table along with changing the field. The format is the same as a
596 * table specification but without the 'fields' element.
598 * @throws \Drupal\Core\Database\SchemaObjectDoesNotExistException
599 * If the specified table or source field doesn't exist.
600 * @throws \Drupal\Core\Database\SchemaObjectExistsException
601 * If the specified destination field already exists.
603 abstract public function changeField($table, $field, $field_new, $spec, $keys_new = []);
606 * Create a new table from a Drupal table definition.
609 * The name of the table to create.
611 * A Schema API table definition array.
613 * @throws \Drupal\Core\Database\SchemaObjectExistsException
614 * If the specified table already exists.
616 public function createTable($name, $table) {
617 if ($this->tableExists($name)) {
618 throw new SchemaObjectExistsException(t('Table @name already exists.', ['@name' => $name]));
620 $statements = $this->createTableSql($name, $table);
621 foreach ($statements as $statement) {
622 $this->connection->query($statement);
627 * Return an array of field names from an array of key/index column specifiers.
629 * This is usually an identity function but if a key/index uses a column prefix
630 * specification, this function extracts just the name.
633 * An array of key/index column specifiers.
636 * An array of field names.
638 public function fieldNames($fields) {
640 foreach ($fields as $field) {
641 if (is_array($field)) {
642 $return[] = $field[0];
652 * Prepare a table or column comment for database query.
655 * The comment string to prepare.
657 * Optional upper limit on the returned string length.
660 * The prepared comment.
662 public function prepareComment($comment, $length = NULL) {
663 // Remove semicolons to avoid triggering multi-statement check.
664 $comment = strtr($comment, [';' => '.']);
665 return $this->connection->quote($comment);
669 * Return an escaped version of its parameter to be used as a default value
672 * @param mixed $value
673 * The value to be escaped (int, float, null or string).
675 * @return string|int|float
678 protected function escapeDefaultValue($value) {
679 if (is_null($value)) {
682 return is_string($value) ? $this->connection->quote($value) : $value;
686 * Ensures that all the primary key fields are correctly defined.
688 * @param array $primary_key
689 * An array containing the fields that will form the primary key of a table.
690 * @param array $fields
691 * An array containing the field specifications of the table, as per the
692 * schema data structure format.
694 * @throws \Drupal\Core\Database\SchemaException
695 * Thrown if any primary key field specification does not exist or if they
696 * do not define 'not null' as TRUE.
698 protected function ensureNotNullPrimaryKey(array $primary_key, array $fields) {
699 foreach (array_intersect($primary_key, array_keys($fields)) as $field_name) {
700 if (!isset($fields[$field_name]['not null']) || $fields[$field_name]['not null'] !== TRUE) {
701 throw new SchemaException("The '$field_name' field specification does not define 'not null' as TRUE.");