SchemaTest.php 48 KB
Newer Older
1 2
<?php

3
namespace Drupal\KernelTests\Core\Database;
4

5
use Drupal\Component\Render\FormattableMarkup;
6
use Drupal\Core\Database\Database;
7
use Drupal\Core\Database\SchemaException;
8 9
use Drupal\Core\Database\SchemaObjectDoesNotExistException;
use Drupal\Core\Database\SchemaObjectExistsException;
10
use Drupal\KernelTests\KernelTestBase;
11
use Drupal\Component\Utility\Unicode;
12
use Drupal\Tests\Core\Database\SchemaIntrospectionTestTrait;
13

14
/**
15 16
 * Tests table creation and modification via the schema API.
 *
17 18
 * @coversDefaultClass \Drupal\Core\Database\Schema
 *
19
 * @group Database
20
 */
21
class SchemaTest extends KernelTestBase {
22

23 24
  use SchemaIntrospectionTestTrait;

25 26
  /**
   * A global counter for table and field creation.
27 28
   *
   * @var int
29
   */
30
  protected $counter;
31

32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
  /**
   * Connection to the database.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected $connection;

  /**
   * Database schema instance.
   *
   * @var \Drupal\Core\Database\Schema
   */
  protected $schema;

  /**
   * {@inheritdoc}
   */
49
  protected function setUp(): void {
50 51 52 53 54
    parent::setUp();
    $this->connection = Database::getConnection();
    $this->schema = $this->connection->schema();
  }

55
  /**
56
   * Tests database interactions.
57
   */
58
  public function testSchema() {
59
    // Try creating a table.
60
    $table_specification = [
61
      'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
62 63
      'fields' => [
        'id'  => [
64 65
          'type' => 'int',
          'default' => NULL,
66 67
        ],
        'test_field'  => [
68 69
          'type' => 'int',
          'not null' => TRUE,
70
          'description' => 'Schema table description may contain "quotes" and could be long—very long indeed. There could be "multiple quoted regions".',
71 72
        ],
        'test_field_string'  => [
73 74 75 76 77
          'type' => 'varchar',
          'length' => 20,
          'not null' => TRUE,
          'default' => "'\"funky default'\"",
          'description' => 'Schema column description for string.',
78 79
        ],
        'test_field_string_ascii'  => [
80 81 82
          'type' => 'varchar_ascii',
          'length' => 255,
          'description' => 'Schema column description for ASCII string.',
83 84 85
        ],
      ],
    ];
86
    $this->schema->createTable('test_table', $table_specification);
87 88

    // Assert that the table exists.
89
    $this->assertTrue($this->schema->tableExists('test_table'), 'The table exists.');
90

91 92 93 94 95 96
    // Assert that the table comment has been set.
    $this->checkSchemaComment($table_specification['description'], 'test_table');

    // Assert that the column comment has been set.
    $this->checkSchemaComment($table_specification['fields']['test_field']['description'], 'test_table', 'test_field');

97
    if ($this->connection->databaseType() === 'mysql') {
98
      // Make sure that varchar fields have the correct collation.
99
      $columns = $this->connection->query('SHOW FULL COLUMNS FROM {test_table}');
100 101
      foreach ($columns as $column) {
        if ($column->Field == 'test_field_string') {
102
          $string_check = ($column->Collation == 'utf8mb4_general_ci' || $column->Collation == 'utf8mb4_0900_ai_ci');
103 104 105 106 107 108 109 110 111
        }
        if ($column->Field == 'test_field_string_ascii') {
          $string_ascii_check = ($column->Collation == 'ascii_general_ci');
        }
      }
      $this->assertTrue(!empty($string_check), 'string field has the right collation.');
      $this->assertTrue(!empty($string_ascii_check), 'ASCII string field has the right collation.');
    }

112
    // An insert without a value for the column 'test_table' should fail.
113
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
114 115

    // Add a default value to the column.
116
    $this->schema->changeField('test_table', 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE, 'default' => 0]);
117
    // The insert should now succeed.
118
    $this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
119 120

    // Remove the default.
121
    $this->schema->changeField('test_table', 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE]);
122
    // The insert should fail again.
123
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
124

125
    // Test for fake index and test for the boolean result of indexExists().
126
    $index_exists = $this->schema->indexExists('test_table', 'test_field');
127
    $this->assertFalse($index_exists, 'Fake index does not exist');
128
    // Add index.
129
    $this->schema->addIndex('test_table', 'test_field', ['test_field'], $table_specification);
130
    // Test for created index and test for the boolean result of indexExists().
131
    $index_exists = $this->schema->indexExists('test_table', 'test_field');
132
    $this->assertTrue($index_exists, 'Index created.');
133

134
    // Rename the table.
135
    $this->assertNull($this->schema->renameTable('test_table', 'test_table2'));
136 137

    // Index should be renamed.
138
    $index_exists = $this->schema->indexExists('test_table2', 'test_field');
139
    $this->assertTrue($index_exists, 'Index was renamed.');
140

141
    // We need the default so that we can insert after the rename.
142
    $this->schema->changeField('test_table2', 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE, 'default' => 0]);
143 144
    $this->assertFalse($this->tryInsert(), 'Insert into the old table failed.');
    $this->assertTrue($this->tryInsert('test_table2'), 'Insert into the new table succeeded.');
145 146

    // We should have successfully inserted exactly two rows.
147
    $count = $this->connection->query('SELECT COUNT(*) FROM {test_table2}')->fetchField();
148
    $this->assertEqual($count, 2, 'Two fields were successfully inserted.');
149 150

    // Try to drop the table.
151
    $this->schema->dropTable('test_table2');
152
    $this->assertFalse($this->schema->tableExists('test_table2'), 'The dropped table does not exist.');
153 154

    // Recreate the table.
155
    $this->schema->createTable('test_table', $table_specification);
156
    $this->schema->changeField('test_table', 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE, 'default' => 0]);
157
    $this->schema->addField('test_table', 'test_serial', ['type' => 'int', 'not null' => TRUE, 'default' => 0, 'description' => 'Added column description.']);
158 159 160

    // Assert that the column comment has been set.
    $this->checkSchemaComment('Added column description.', 'test_table', 'test_serial');
161 162

    // Change the new field to a serial column.
163
    $this->schema->changeField('test_table', 'test_serial', 'test_serial', ['type' => 'serial', 'not null' => TRUE, 'description' => 'Changed column description.'], ['primary key' => ['test_serial']]);
164 165 166

    // Assert that the column comment has been set.
    $this->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');
167

168
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
169
    $max1 = $this->connection->query('SELECT MAX([test_serial]) FROM {test_table}')->fetchField();
170
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
171
    $max2 = $this->connection->query('SELECT MAX([test_serial]) FROM {test_table}')->fetchField();
172
    $this->assertTrue($max2 > $max1, 'The serial is monotone.');
173

174
    $count = $this->connection->query('SELECT COUNT(*) FROM {test_table}')->fetchField();
175
    $this->assertEqual($count, 2, 'There were two rows.');
176

177
    // Test adding a serial field to an existing table.
178
    $this->schema->dropTable('test_table');
179
    $this->schema->createTable('test_table', $table_specification);
180
    $this->schema->changeField('test_table', 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE, 'default' => 0]);
181
    $this->schema->addField('test_table', 'test_serial', ['type' => 'serial', 'not null' => TRUE], ['primary key' => ['test_serial']]);
182

183
    // Test the primary key columns.
184
    $method = new \ReflectionMethod(get_class($this->schema), 'findPrimaryKeyColumns');
185
    $method->setAccessible(TRUE);
186
    $this->assertSame(['test_serial'], $method->invoke($this->schema, 'test_table'));
187 188

    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
189
    $max1 = $this->connection->query('SELECT MAX([test_serial]) FROM {test_table}')->fetchField();
190
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
191
    $max2 = $this->connection->query('SELECT MAX([test_serial]) FROM {test_table}')->fetchField();
192 193
    $this->assertTrue($max2 > $max1, 'The serial is monotone.');

194
    $count = $this->connection->query('SELECT COUNT(*) FROM {test_table}')->fetchField();
195 196 197
    $this->assertEqual($count, 2, 'There were two rows.');

    // Test adding a new column and form a composite primary key with it.
198
    $this->schema->addField('test_table', 'test_composite_primary_key', ['type' => 'int', 'not null' => TRUE, 'default' => 0], ['primary key' => ['test_serial', 'test_composite_primary_key']]);
199

200
    // Test the primary key columns.
201
    $this->assertSame(['test_serial', 'test_composite_primary_key'], $method->invoke($this->schema, 'test_table'));
202

203
    // Test renaming of keys and constraints.
204
    $this->schema->dropTable('test_table');
205 206 207
    $table_specification = [
      'fields' => [
        'id'  => [
208 209
          'type' => 'serial',
          'not null' => TRUE,
210 211
        ],
        'test_field'  => [
212 213
          'type' => 'int',
          'default' => 0,
214 215 216 217 218 219 220
        ],
      ],
      'primary key' => ['id'],
      'unique keys' => [
        'test_field' => ['test_field'],
      ],
    ];
221 222 223 224 225 226 227

    // PostgreSQL has a max identifier length of 63 characters, MySQL has 64 and
    // SQLite does not have any limit. Use the lowest common value and create a
    // table name as long as possible in order to cover edge cases around
    // identifier names for the table's primary or unique key constraints.
    $table_name = strtolower($this->getRandomGenerator()->name(63 - strlen($this->getDatabasePrefix())));
    $this->schema->createTable($table_name, $table_specification);
228

229 230
    $this->assertIndexOnColumns($table_name, ['id'], 'primary');
    $this->assertIndexOnColumns($table_name, ['test_field'], 'unique');
231

232 233
    $new_table_name = strtolower($this->getRandomGenerator()->name(63 - strlen($this->getDatabasePrefix())));
    $this->assertNull($this->schema->renameTable($table_name, $new_table_name));
234 235

    // Test for renamed primary and unique keys.
236 237
    $this->assertIndexOnColumns($new_table_name, ['id'], 'primary');
    $this->assertIndexOnColumns($new_table_name, ['test_field'], 'unique');
238

239 240 241 242 243 244
    // For PostgreSQL, we also need to check that the sequence has been renamed.
    // The initial name of the sequence has been generated automatically by
    // PostgreSQL when the table was created, however, on subsequent table
    // renames the name is generated by Drupal and can not be easily
    // re-constructed. Hence we can only check that we still have a sequence on
    // the new table name.
245
    if ($this->connection->databaseType() == 'pgsql') {
246 247 248 249 250 251 252 253 254
      $sequence_exists = (bool) $this->connection->query("SELECT pg_get_serial_sequence('{" . $new_table_name . "}', 'id')")->fetchField();
      $this->assertTrue($sequence_exists, 'Sequence was renamed.');

      // Rename the table again and repeat the check.
      $another_table_name = strtolower($this->getRandomGenerator()->name(63 - strlen($this->getDatabasePrefix())));
      $this->schema->renameTable($new_table_name, $another_table_name);

      $sequence_exists = (bool) $this->connection->query("SELECT pg_get_serial_sequence('{" . $another_table_name . "}', 'id')")->fetchField();
      $this->assertTrue($sequence_exists, 'Sequence was renamed.');
255 256
    }

257
    // Use database specific data type and ensure that table is created.
258
    $table_specification = [
259
      'description' => 'Schema table description.',
260 261
      'fields' => [
        'timestamp'  => [
262 263 264 265 266
          'mysql_type' => 'timestamp',
          'pgsql_type' => 'timestamp',
          'sqlite_type' => 'datetime',
          'not null' => FALSE,
          'default' => NULL,
267 268 269
        ],
      ],
    ];
270
    try {
271
      $this->schema->createTable('test_timestamp', $table_specification);
272
    }
273 274
    catch (\Exception $e) {
    }
275
    $this->assertTrue($this->schema->tableExists('test_timestamp'), 'Table with database specific datatype was created.');
276 277
  }

278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314
  /**
   * @covers \Drupal\Core\Database\Driver\mysql\Schema::introspectIndexSchema
   * @covers \Drupal\Core\Database\Driver\pgsql\Schema::introspectIndexSchema
   * @covers \Drupal\Core\Database\Driver\sqlite\Schema::introspectIndexSchema
   */
  public function testIntrospectIndexSchema() {
    $table_specification = [
      'fields' => [
        'id'  => [
          'type' => 'int',
          'not null' => TRUE,
          'default' => 0,
        ],
        'test_field_1'  => [
          'type' => 'int',
          'not null' => TRUE,
          'default' => 0,
        ],
        'test_field_2'  => [
          'type' => 'int',
          'default' => 0,
        ],
        'test_field_3'  => [
          'type' => 'int',
          'default' => 0,
        ],
        'test_field_4'  => [
          'type' => 'int',
          'default' => 0,
        ],
        'test_field_5'  => [
          'type' => 'int',
          'default' => 0,
        ],
      ],
      'primary key' => ['id', 'test_field_1'],
      'unique keys' => [
315 316
        'test_field_2' => ['test_field_2'],
        'test_field_3_test_field_4' => ['test_field_3', 'test_field_4'],
317 318
      ],
      'indexes' => [
319 320
        'test_field_4' => ['test_field_4'],
        'test_field_4_test_field_5' => ['test_field_4', 'test_field_5'],
321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
      ],
    ];

    $table_name = strtolower($this->getRandomGenerator()->name());
    $this->schema->createTable($table_name, $table_specification);

    unset($table_specification['fields']);

    $introspect_index_schema = new \ReflectionMethod(get_class($this->schema), 'introspectIndexSchema');
    $introspect_index_schema->setAccessible(TRUE);
    $index_schema = $introspect_index_schema->invoke($this->schema, $table_name);

    // The PostgreSQL driver is using a custom naming scheme for its indexes, so
    // we need to adjust the initial table specification.
    if ($this->connection->databaseType() === 'pgsql') {
      $ensure_identifier_length = new \ReflectionMethod(get_class($this->schema), 'ensureIdentifiersLength');
      $ensure_identifier_length->setAccessible(TRUE);

      foreach ($table_specification['unique keys'] as $original_index_name => $columns) {
        unset($table_specification['unique keys'][$original_index_name]);
        $new_index_name = $ensure_identifier_length->invoke($this->schema, $table_name, $original_index_name, 'key');
        $table_specification['unique keys'][$new_index_name] = $columns;
      }

      foreach ($table_specification['indexes'] as $original_index_name => $columns) {
        unset($table_specification['indexes'][$original_index_name]);
        $new_index_name = $ensure_identifier_length->invoke($this->schema, $table_name, $original_index_name, 'idx');
        $table_specification['indexes'][$new_index_name] = $columns;
      }
    }

    $this->assertEquals($table_specification, $index_schema);
  }

355 356 357 358 359
  /**
   * Tests that indexes on string fields are limited to 191 characters on MySQL.
   *
   * @see \Drupal\Core\Database\Driver\mysql\Schema::getNormalizedIndexes()
   */
360
  public function testIndexLength() {
361 362
    if ($this->connection->databaseType() !== 'mysql') {
      $this->markTestSkipped("The '{$this->connection->databaseType()}' database type does not support setting column length for indexes.");
363
    }
364

365 366 367
    $table_specification = [
      'fields' => [
        'id'  => [
368 369
          'type' => 'int',
          'default' => NULL,
370 371
        ],
        'test_field_text'  => [
372 373
          'type' => 'text',
          'not null' => TRUE,
374 375
        ],
        'test_field_string_long'  => [
376 377 378
          'type' => 'varchar',
          'length' => 255,
          'not null' => TRUE,
379 380
        ],
        'test_field_string_ascii_long'  => [
381 382
          'type' => 'varchar_ascii',
          'length' => 255,
383 384
        ],
        'test_field_string_short'  => [
385 386 387
          'type' => 'varchar',
          'length' => 128,
          'not null' => TRUE,
388 389 390 391
        ],
      ],
      'indexes' => [
        'test_regular' => [
392 393 394 395
          'test_field_text',
          'test_field_string_long',
          'test_field_string_ascii_long',
          'test_field_string_short',
396 397 398 399 400 401 402 403 404
        ],
        'test_length' => [
          ['test_field_text', 128],
          ['test_field_string_long', 128],
          ['test_field_string_ascii_long', 128],
          ['test_field_string_short', 128],
        ],
        'test_mixed' => [
          ['test_field_text', 200],
405
          'test_field_string_long',
406
          ['test_field_string_ascii_long', 200],
407
          'test_field_string_short',
408 409 410
        ],
      ],
    ];
411
    $this->schema->createTable('test_table_index_length', $table_specification);
412 413 414 415 416 417

    // Ensure expected exception thrown when adding index with missing info.
    $expected_exception_message = "MySQL needs the 'test_field_text' field specification in order to normalize the 'test_regular' index";
    $missing_field_spec = $table_specification;
    unset($missing_field_spec['fields']['test_field_text']);
    try {
418
      $this->schema->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $missing_field_spec);
419 420 421 422 423 424 425
      $this->fail('SchemaException not thrown when adding index with missing information.');
    }
    catch (SchemaException $e) {
      $this->assertEqual($expected_exception_message, $e->getMessage());
    }

    // Add a separate index.
426
    $this->schema->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $table_specification);
427 428 429 430 431
    $table_specification_with_new_index = $table_specification;
    $table_specification_with_new_index['indexes']['test_separate'] = [['test_field_text', 200]];

    // Ensure that the exceptions of addIndex are thrown as expected.
    try {
432
      $this->schema->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $table_specification);
433 434 435 436 437 438 439
      $this->fail('\Drupal\Core\Database\SchemaObjectExistsException exception missed.');
    }
    catch (SchemaObjectExistsException $e) {
      $this->pass('\Drupal\Core\Database\SchemaObjectExistsException thrown when index already exists.');
    }

    try {
440
      $this->schema->addIndex('test_table_non_existing', 'test_separate', [['test_field_text', 200]], $table_specification);
441 442 443 444 445 446
      $this->fail('\Drupal\Core\Database\SchemaObjectDoesNotExistException exception missed.');
    }
    catch (SchemaObjectDoesNotExistException $e) {
      $this->pass('\Drupal\Core\Database\SchemaObjectDoesNotExistException thrown when index already exists.');
    }

447
    // Get index information.
448
    $results = $this->connection->query('SHOW INDEX FROM {test_table_index_length}');
449 450
    $expected_lengths = [
      'test_regular' => [
451 452 453 454
        'test_field_text' => 191,
        'test_field_string_long' => 191,
        'test_field_string_ascii_long' => NULL,
        'test_field_string_short' => NULL,
455 456
      ],
      'test_length' => [
457 458 459 460
        'test_field_text' => 128,
        'test_field_string_long' => 128,
        'test_field_string_ascii_long' => 128,
        'test_field_string_short' => NULL,
461 462
      ],
      'test_mixed' => [
463 464 465 466
        'test_field_text' => 191,
        'test_field_string_long' => 191,
        'test_field_string_ascii_long' => 200,
        'test_field_string_short' => NULL,
467 468
      ],
      'test_separate' => [
469
        'test_field_text' => 191,
470 471
      ],
    ];
472 473 474

    // Count the number of columns defined in the indexes.
    $column_count = 0;
475
    foreach ($table_specification_with_new_index['indexes'] as $index) {
476 477 478 479 480 481 482 483 484 485 486 487
      foreach ($index as $field) {
        $column_count++;
      }
    }
    $test_count = 0;
    foreach ($results as $result) {
      $this->assertEqual($result->Sub_part, $expected_lengths[$result->Key_name][$result->Column_name], 'Index length matches expected value.');
      $test_count++;
    }
    $this->assertEqual($test_count, $column_count, 'Number of tests matches expected value.');
  }

488 489 490
  /**
   * Tests inserting data into an existing table.
   *
491
   * @param string $table
492 493
   *   The database table to insert data into.
   *
494
   * @return bool
495 496
   *   TRUE if the insert succeeded, FALSE otherwise.
   */
497
  public function tryInsert($table = 'test_table') {
498
    try {
499 500
      $this->connection
        ->insert($table)
501
        ->fields(['id' => mt_rand(10, 20)])
502
        ->execute();
503 504
      return TRUE;
    }
505
    catch (\Exception $e) {
506 507 508
      return FALSE;
    }
  }
509 510 511 512 513 514 515 516 517 518 519

  /**
   * Checks that a table or column comment matches a given description.
   *
   * @param $description
   *   The asserted description.
   * @param $table
   *   The table to test.
   * @param $column
   *   Optional column to test.
   */
520
  public function checkSchemaComment($description, $table, $column = NULL) {
521 522
    if (method_exists($this->schema, 'getComment')) {
      $comment = $this->schema->getComment($table, $column);
523
      // The schema comment truncation for mysql is different.
524
      if ($this->connection->databaseType() === 'mysql') {
525 526 527
        $max_length = $column ? 255 : 60;
        $description = Unicode::truncate($description, $max_length, TRUE, TRUE);
      }
528
      $this->assertEqual($comment, $description, 'The comment matches the schema description.');
529 530
    }
  }
531

532 533 534
  /**
   * Tests creating unsigned columns and data integrity thereof.
   */
535
  public function testUnsignedColumns() {
536 537
    // First create the table with just a serial column.
    $table_name = 'unsigned_table';
538 539 540 541
    $table_spec = [
      'fields' => ['serial_column' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE]],
      'primary key' => ['serial_column'],
    ];
542
    $this->schema->createTable($table_name, $table_spec);
543 544

    // Now set up columns for the other types.
545
    $types = ['int', 'float', 'numeric'];
546
    foreach ($types as $type) {
547
      $column_spec = ['type' => $type, 'unsigned' => TRUE];
548
      if ($type == 'numeric') {
549
        $column_spec += ['precision' => 10, 'scale' => 0];
550 551 552
      }
      $column_name = $type . '_column';
      $table_spec['fields'][$column_name] = $column_spec;
553
      $this->schema->addField($table_name, $column_name, $column_spec);
554 555 556
    }

    // Finally, check each column and try to insert invalid values into them.
557
    foreach ($table_spec['fields'] as $column_name => $column_spec) {
558 559
      $this->assertTrue($this->schema->fieldExists($table_name, $column_name), new FormattableMarkup('Unsigned @type column was created.', ['@type' => $column_spec['type']]));
      $this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), new FormattableMarkup('Unsigned @type column rejected a negative value.', ['@type' => $column_spec['type']]));
560 561 562 563 564 565
    }
  }

  /**
   * Tries to insert a negative value into columns defined as unsigned.
   *
566
   * @param string $table_name
567
   *   The table to insert.
568
   * @param string $column_name
569 570
   *   The column to insert.
   *
571
   * @return bool
572
   *   TRUE if the insert succeeded, FALSE otherwise.
573
   */
574
  public function tryUnsignedInsert($table_name, $column_name) {
575
    try {
576 577
      $this->connection
        ->insert($table_name)
578
        ->fields([$column_name => -1])
579
        ->execute();
580 581
      return TRUE;
    }
582
    catch (\Exception $e) {
583 584 585
      return FALSE;
    }
  }
586 587

  /**
588
   * Tests adding columns to an existing table with default and initial value.
589
   */
590
  public function testSchemaAddFieldDefaultInitial() {
591
    // Test varchar types.
592 593
    foreach ([1, 32, 128, 256, 512] as $length) {
      $base_field_spec = [
594 595
        'type' => 'varchar',
        'length' => $length,
596 597 598 599 600 601 602 603 604 605
      ];
      $variations = [
        ['not null' => FALSE],
        ['not null' => FALSE, 'default' => '7'],
        ['not null' => FALSE, 'default' => substr('"thing"', 0, $length)],
        ['not null' => FALSE, 'default' => substr("\"'hing", 0, $length)],
        ['not null' => TRUE, 'initial' => 'd'],
        ['not null' => FALSE, 'default' => NULL],
        ['not null' => TRUE, 'initial' => 'd', 'default' => '7'],
      ];
606 607 608 609 610 611 612 613

      foreach ($variations as $variation) {
        $field_spec = $variation + $base_field_spec;
        $this->assertFieldAdditionRemoval($field_spec);
      }
    }

    // Test int and float types.
614 615 616
    foreach (['int', 'float'] as $type) {
      foreach (['tiny', 'small', 'medium', 'normal', 'big'] as $size) {
        $base_field_spec = [
617 618
          'type' => $type,
          'size' => $size,
619 620 621 622 623 624 625
        ];
        $variations = [
          ['not null' => FALSE],
          ['not null' => FALSE, 'default' => 7],
          ['not null' => TRUE, 'initial' => 1],
          ['not null' => TRUE, 'initial' => 1, 'default' => 7],
          ['not null' => TRUE, 'initial_from_field' => 'serial_column'],
626 627 628 629 630
          [
            'not null' => TRUE,
            'initial_from_field' => 'test_nullable_field',
            'initial'  => 100,
          ],
631
        ];
632 633 634 635 636 637 638 639 640

        foreach ($variations as $variation) {
          $field_spec = $variation + $base_field_spec;
          $this->assertFieldAdditionRemoval($field_spec);
        }
      }
    }

    // Test numeric types.
641 642
    foreach ([1, 5, 10, 40, 65] as $precision) {
      foreach ([0, 2, 10, 30] as $scale) {
643
        // Skip combinations where precision is smaller than scale.
644 645 646 647
        if ($precision <= $scale) {
          continue;
        }

648
        $base_field_spec = [
649 650 651
          'type' => 'numeric',
          'scale' => $scale,
          'precision' => $precision,
652 653 654 655 656 657 658 659
        ];
        $variations = [
          ['not null' => FALSE],
          ['not null' => FALSE, 'default' => 7],
          ['not null' => TRUE, 'initial' => 1],
          ['not null' => TRUE, 'initial' => 1, 'default' => 7],
          ['not null' => TRUE, 'initial_from_field' => 'serial_column'],
        ];
660 661 662 663 664 665 666 667 668 669

        foreach ($variations as $variation) {
          $field_spec = $variation + $base_field_spec;
          $this->assertFieldAdditionRemoval($field_spec);
        }
      }
    }
  }

  /**
670
   * Asserts that a given field can be added and removed from a table.
671 672 673 674 675 676 677 678 679 680
   *
   * The addition test covers both defining a field of a given specification
   * when initially creating at table and extending an existing table.
   *
   * @param $field_spec
   *   The schema specification of the field.
   */
  protected function assertFieldAdditionRemoval($field_spec) {
    // Try creating the field on a new table.
    $table_name = 'test_table_' . ($this->counter++);
681 682 683
    $table_spec = [
      'fields' => [
        'serial_column' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
684
        'test_nullable_field' => ['type' => 'int', 'not null' => FALSE],
685
        'test_field' => $field_spec,
686 687 688
      ],
      'primary key' => ['serial_column'],
    ];
689
    $this->schema->createTable($table_name, $table_spec);
690
    $this->pass(new FormattableMarkup('Table %table created.', ['%table' => $table_name]));
691 692 693 694 695

    // Check the characteristics of the field.
    $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);

    // Clean-up.
696
    $this->schema->dropTable($table_name);
697 698 699

    // Try adding a field to an existing table.
    $table_name = 'test_table_' . ($this->counter++);
700 701 702
    $table_spec = [
      'fields' => [
        'serial_column' => ['type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE],
703
        'test_nullable_field' => ['type' => 'int', 'not null' => FALSE],
704 705 706
      ],
      'primary key' => ['serial_column'],
    ];
707
    $this->schema->createTable($table_name, $table_spec);
708
    $this->pass(new FormattableMarkup('Table %table created.', ['%table' => $table_name]));
709 710 711

    // Insert some rows to the table to test the handling of initial values.
    for ($i = 0; $i < 3; $i++) {
712 713
      $this->connection
        ->insert($table_name)
714
        ->useDefaults(['serial_column'])
715
        ->fields(['test_nullable_field' => 100])
716 717 718
        ->execute();
    }

719
    // Add another row with no value for the 'test_nullable_field' column.
720 721
    $this->connection
      ->insert($table_name)
722 723 724
      ->useDefaults(['serial_column'])
      ->execute();

725
    $this->schema->addField($table_name, 'test_field', $field_spec);
726
    $this->pass(new FormattableMarkup('Column %column created.', ['%column' => 'test_field']));
727 728 729 730 731

    // Check the characteristics of the field.
    $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);

    // Clean-up.
732
    $this->schema->dropField($table_name, 'test_field');
733 734 735

    // Add back the field and then try to delete a field which is also a primary
    // key.
736 737
    $this->schema->addField($table_name, 'test_field', $field_spec);
    $this->schema->dropField($table_name, 'serial_column');
738
    $this->schema->dropTable($table_name);
739 740 741
  }

  /**
742
   * Asserts that a newly added field has the correct characteristics.
743 744 745 746 747
   */
  protected function assertFieldCharacteristics($table_name, $field_name, $field_spec) {
    // Check that the initial value has been registered.
    if (isset($field_spec['initial'])) {
      // There should be no row with a value different then $field_spec['initial'].
748 749
      $count = $this->connection
        ->select($table_name)
750
        ->fields($table_name, ['serial_column'])
751 752 753 754
        ->condition($field_name, $field_spec['initial'], '<>')
        ->countQuery()
        ->execute()
        ->fetchField();
755
      $this->assertEqual($count, 0, 'Initial values filled out.');
756 757
    }

758
    // Check that the initial value from another field has been registered.
759
    if (isset($field_spec['initial_from_field']) && !isset($field_spec['initial'])) {
760 761
      // There should be no row with a value different than
      // $field_spec['initial_from_field'].
762 763
      $count = $this->connection
        ->select($table_name)
764
        ->fields($table_name, ['serial_column'])
765 766 767 768 769 770
        ->where($table_name . '.' . $field_spec['initial_from_field'] . ' <> ' . $table_name . '.' . $field_name)
        ->countQuery()
        ->execute()
        ->fetchField();
      $this->assertEqual($count, 0, 'Initial values from another field filled out.');
    }
771 772
    elseif (isset($field_spec['initial_from_field']) && isset($field_spec['initial'])) {
      // There should be no row with a value different than '100'.
773 774
      $count = $this->connection
        ->select($table_name)
775 776 777 778 779 780 781
        ->fields($table_name, ['serial_column'])
        ->condition($field_name, 100, '<>')
        ->countQuery()
        ->execute()
        ->fetchField();
      $this->assertEqual($count, 0, 'Initial values from another field or a default value filled out.');
    }
782

783 784 785
    // Check that the default value has been registered.
    if (isset($field_spec['default'])) {
      // Try inserting a row, and check the resulting value of the new column.
786 787
      $id = $this->connection
        ->insert($table_name)
788
        ->useDefaults(['serial_column'])
789
        ->execute();
790 791
      $field_value = $this->connection
        ->select($table_name)
792
        ->fields($table_name, [$field_name])
793 794 795
        ->condition('serial_column', $id)
        ->execute()
        ->fetchField();
796
      $this->assertEqual($field_value, $field_spec['default'], 'Default value registered.');
797 798
    }
  }
799 800

  /**
801 802 803 804 805 806 807 808 809 810 811 812 813 814 815
   * Tests various schema changes' effect on the table's primary key.
   *
   * @param array $initial_primary_key
   *   The initial primary key of the test table.
   * @param array $renamed_primary_key
   *   The primary key of the test table after renaming the test field.
   *
   * @dataProvider providerTestSchemaCreateTablePrimaryKey
   *
   * @covers ::addField
   * @covers ::changeField
   * @covers ::dropField
   * @covers ::findPrimaryKeyColumns
   */
  public function testSchemaChangePrimaryKey(array $initial_primary_key, array $renamed_primary_key) {
816
    $find_primary_key_columns = new \ReflectionMethod(get_class($this->schema), 'findPrimaryKeyColumns');
817 818 819 820 821 822
    $find_primary_key_columns->setAccessible(TRUE);

    // Test making the field the primary key of the table upon creation.
    $table_name = 'test_table';
    $table_spec = [
      'fields' => [
823 824
        'test_field' => ['type' => 'int', 'not null' => TRUE],
        'other_test_field' => ['type' => 'int', 'not null' => TRUE],
825 826 827
      ],
      'primary key' => $initial_primary_key,
    ];
828 829 830
    $this->schema->createTable($table_name, $table_spec);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
831 832

    // Change the field type and make sure the primary key stays in place.
833 834 835
    $this->schema->changeField($table_name, 'test_field', 'test_field', ['type' => 'varchar', 'length' => 32, 'not null' => TRUE]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
836 837 838

    // Add some data and change the field type back, to make sure that changing
    // the type leaves the primary key in place even with existing data.
839 840
    $this->connection
      ->insert($table_name)
841 842
      ->fields(['test_field' => 1, 'other_test_field' => 2])
      ->execute();
843 844 845
    $this->schema->changeField($table_name, 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
846 847 848

    // Make sure that adding the primary key can be done as part of changing
    // a field, as well.
849 850 851 852 853
    $this->schema->dropPrimaryKey($table_name);
    $this->assertEquals([], $find_primary_key_columns->invoke($this->schema, $table_name));
    $this->schema->changeField($table_name, 'test_field', 'test_field', ['type' => 'int', 'not null' => TRUE], ['primary key' => $initial_primary_key]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
854 855

    // Rename the field and make sure the primary key was updated.
856 857 858
    $this->schema->changeField($table_name, 'test_field', 'test_field_renamed', ['type' => 'int', 'not null' => TRUE]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field_renamed'));
    $this->assertEquals($renamed_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
859 860

    // Drop the field and make sure the primary key was dropped, as well.
861 862 863
    $this->schema->dropField($table_name, 'test_field_renamed');
    $this->assertFalse($this->schema->fieldExists($table_name, 'test_field_renamed'));
    $this->assertEquals([], $find_primary_key_columns->invoke($this->schema, $table_name));
864 865 866

    // Add the field again and make sure adding the primary key can be done at
    // the same time.
867 868 869
    $this->schema->addField($table_name, 'test_field', ['type' => 'int', 'default' => 0, 'not null' => TRUE], ['primary key' => $initial_primary_key]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
870 871

    // Drop the field again and explicitly add a primary key.
872 873 874 875
    $this->schema->dropField($table_name, 'test_field');
    $this->schema->addPrimaryKey($table_name, ['other_test_field']);
    $this->assertFalse($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals(['other_test_field'], $find_primary_key_columns->invoke($this->schema, $table_name));
876 877 878

    // Test that adding a field with a primary key will work even with a
    // pre-existing primary key.
879 880 881
    $this->schema->addField($table_name, 'test_field', ['type' => 'int', 'default' => 0, 'not null' => TRUE], ['primary key' => $initial_primary_key]);
    $this->assertTrue($this->schema->fieldExists($table_name, 'test_field'));
    $this->assertEquals($initial_primary_key, $find_primary_key_columns->invoke($this->schema, $table_name));
882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908
  }

  /**
   * Provides test cases for SchemaTest::testSchemaCreateTablePrimaryKey().
   *
   * @return array
   *   An array of test cases for SchemaTest::testSchemaCreateTablePrimaryKey().
   */
  public function providerTestSchemaCreateTablePrimaryKey() {
    $tests = [];

    $tests['simple_primary_key'] = [
      'initial_primary_key' => ['test_field'],