SchemaTest.php 28.9 KB
Newer Older
1 2 3 4
<?php

/**
 * @file
5
 * Contains \Drupal\system\Tests\Database\SchemaTest.
6 7
 */

8
namespace Drupal\system\Tests\Database;
9 10

use Drupal\Core\Database\Database;
11
use Drupal\Core\Database\SchemaException;
12 13
use Drupal\Core\Database\SchemaObjectDoesNotExistException;
use Drupal\Core\Database\SchemaObjectExistsException;
14
use Drupal\simpletest\KernelTestBase;
15
use Drupal\Component\Utility\Unicode;
16

17
/**
18 19 20
 * Tests table creation and modification via the schema API.
 *
 * @group Database
21
 */
22
class SchemaTest extends KernelTestBase {
23

24 25 26
  /**
   * A global counter for table and field creation.
   */
27
  protected $counter;
28

29
  /**
30
   * Tests database interactions.
31 32 33 34
   */
  function testSchema() {
    // Try creating a table.
    $table_specification = array(
35
      'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
36 37 38 39 40
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'default' => NULL,
        ),
41
        'test_field'  => array(
42 43
          'type' => 'int',
          'not null' => TRUE,
44
          'description' => 'Schema table description may contain "quotes" and could be long—very long indeed. There could be "multiple quoted regions".',
45
        ),
46 47 48 49 50 51 52
        'test_field_string'  => array(
          'type' => 'varchar',
          'length' => 20,
          'not null' => TRUE,
          'default' => "'\"funky default'\"",
          'description' => 'Schema column description for string.',
        ),
53 54 55 56 57
        'test_field_string_ascii'  => array(
          'type' => 'varchar_ascii',
          'length' => 255,
          'description' => 'Schema column description for ASCII string.',
        ),
58 59
      ),
    );
60
    db_create_table('test_table', $table_specification);
61 62

    // Assert that the table exists.
63
    $this->assertTrue(db_table_exists('test_table'), 'The table exists.');
64

65 66 67 68 69 70
    // 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');

71 72 73 74 75
    if (Database::getConnection()->databaseType() == 'mysql') {
      // Make sure that varchar fields have the correct collation.
      $columns = db_query('SHOW FULL COLUMNS FROM {test_table}');
      foreach ($columns as $column) {
        if ($column->Field == 'test_field_string') {
76
          $string_check = ($column->Collation == 'utf8mb4_general_ci');
77 78 79 80 81 82 83 84 85
        }
        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.');
    }

86
    // An insert without a value for the column 'test_table' should fail.
87
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
88 89

    // Add a default value to the column.
90
    db_field_set_default('test_table', 'test_field', 0);
91
    // The insert should now succeed.
92
    $this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
93 94

    // Remove the default.
95
    db_field_set_no_default('test_table', 'test_field');
96
    // The insert should fail again.
97
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
98

99
    // Test for fake index and test for the boolean result of indexExists().
100
    $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
101
    $this->assertIdentical($index_exists, FALSE, 'Fake index does not exists');
102
    // Add index.
103
    db_add_index('test_table', 'test_field', array('test_field'), $table_specification);
104
    // Test for created index and test for the boolean result of indexExists().
105
    $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
106
    $this->assertIdentical($index_exists, TRUE, 'Index created.');
107

108
    // Rename the table.
109
    db_rename_table('test_table', 'test_table2');
110 111 112

    // Index should be renamed.
    $index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
113
    $this->assertTrue($index_exists, 'Index was renamed.');
114

115
    // We need the default so that we can insert after the rename.
116
    db_field_set_default('test_table2', 'test_field', 0);
117 118
    $this->assertFalse($this->tryInsert(), 'Insert into the old table failed.');
    $this->assertTrue($this->tryInsert('test_table2'), 'Insert into the new table succeeded.');
119 120 121

    // We should have successfully inserted exactly two rows.
    $count = db_query('SELECT COUNT(*) FROM {test_table2}')->fetchField();
122
    $this->assertEqual($count, 2, 'Two fields were successfully inserted.');
123 124

    // Try to drop the table.
125
    db_drop_table('test_table2');
126
    $this->assertFalse(db_table_exists('test_table2'), 'The dropped table does not exist.');
127 128

    // Recreate the table.
129 130 131
    db_create_table('test_table', $table_specification);
    db_field_set_default('test_table', 'test_field', 0);
    db_add_field('test_table', 'test_serial', array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'description' => 'Added column description.'));
132 133 134

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

    // Change the new field to a serial column.
137
    db_change_field('test_table', 'test_serial', 'test_serial', array('type' => 'serial', 'not null' => TRUE, 'description' => 'Changed column description.'), array('primary key' => array('test_serial')));
138 139 140

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

142
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
143
    $max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
144
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
145
    $max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
146
    $this->assertTrue($max2 > $max1, 'The serial is monotone.');
147

148
    $count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();
149
    $this->assertEqual($count, 2, 'There were two rows.');
150

151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221
    // Test renaming of keys and constraints.
    db_drop_table('test_table');
    $table_specification = array(
      'fields' => array(
        'id'  => array(
          'type' => 'serial',
          'not null' => TRUE,
        ),
        'test_field'  => array(
          'type' => 'int',
          'default' => 0,
        ),
      ),
      'primary key' => array('id'),
      'unique keys' => array(
        'test_field' => array('test_field'),
      ),
    );
    db_create_table('test_table', $table_specification);

    // Tests for indexes are Database specific.
    $db_type = Database::getConnection()->databaseType();

    // Test for existing primary and unique keys.
    switch ($db_type) {
      case 'pgsql':
        $primary_key_exists = Database::getConnection()->schema()->constraintExists('test_table', '__pkey');
        $unique_key_exists = Database::getConnection()->schema()->constraintExists('test_table', 'test_field' . '__key');
        break;
      case 'sqlite':
        // SQLite does not create a standalone index for primary keys.
        $primary_key_exists = TRUE;
        $unique_key_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
        break;
      default:
        $primary_key_exists = Database::getConnection()->schema()->indexExists('test_table', 'PRIMARY');
        $unique_key_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
        break;
    }
    $this->assertIdentical($primary_key_exists, TRUE, 'Primary key created.');
    $this->assertIdentical($unique_key_exists, TRUE, 'Unique key created.');

    db_rename_table('test_table', 'test_table2');

    // Test for renamed primary and unique keys.
    switch ($db_type) {
      case 'pgsql':
        $renamed_primary_key_exists = Database::getConnection()->schema()->constraintExists('test_table2', '__pkey');
        $renamed_unique_key_exists = Database::getConnection()->schema()->constraintExists('test_table2', 'test_field' . '__key');
        break;
      case 'sqlite':
        // SQLite does not create a standalone index for primary keys.
        $renamed_primary_key_exists = TRUE;
        $renamed_unique_key_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
        break;
      default:
        $renamed_primary_key_exists = Database::getConnection()->schema()->indexExists('test_table2', 'PRIMARY');
        $renamed_unique_key_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
        break;
    }
    $this->assertIdentical($renamed_primary_key_exists, TRUE, 'Primary key was renamed.');
    $this->assertIdentical($renamed_unique_key_exists, TRUE, 'Unique key was renamed.');

    // For PostgreSQL check in addition that sequence was renamed.
    if ($db_type == 'pgsql') {
      // Get information about new table.
      $info = Database::getConnection()->schema()->queryTableInformation('test_table2');
      $sequence_name = Database::getConnection()->schema()->prefixNonTable('test_table2', 'id', 'seq');
      $this->assertEqual($sequence_name, current($info->sequences), 'Sequence was renamed.');
    }

222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237
    // Use database specific data type and ensure that table is created.
    $table_specification = array(
      'description' => 'Schema table description.',
      'fields' => array(
        'timestamp'  => array(
          'mysql_type' => 'timestamp',
          'pgsql_type' => 'timestamp',
          'sqlite_type' => 'datetime',
          'not null' => FALSE,
          'default' => NULL,
        ),
      ),
    );
    try {
      db_create_table('test_timestamp', $table_specification);
    }
238
    catch (\Exception $e) {}
239
    $this->assertTrue(db_table_exists('test_timestamp'), 'Table with database specific datatype was created.');
240 241
  }

242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298
  /**
   * Tests that indexes on string fields are limited to 191 characters on MySQL.
   *
   * @see \Drupal\Core\Database\Driver\mysql\Schema::getNormalizedIndexes()
   */
  function testIndexLength() {
    if (Database::getConnection()->databaseType() != 'mysql') {
      return;
    }
    $table_specification = array(
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'default' => NULL,
        ),
        'test_field_text'  => array(
          'type' => 'text',
          'not null' => TRUE,
        ),
        'test_field_string_long'  => array(
          'type' => 'varchar',
          'length' => 255,
          'not null' => TRUE,
        ),
        'test_field_string_ascii_long'  => array(
          'type' => 'varchar_ascii',
          'length' => 255,
        ),
        'test_field_string_short'  => array(
          'type' => 'varchar',
          'length' => 128,
          'not null' => TRUE,
        ),
      ),
      'indexes' => array(
        'test_regular' => array(
          'test_field_text',
          'test_field_string_long',
          'test_field_string_ascii_long',
          'test_field_string_short',
        ),
        'test_length' => array(
          array('test_field_text', 128),
          array('test_field_string_long', 128),
          array('test_field_string_ascii_long', 128),
          array('test_field_string_short', 128),
        ),
        'test_mixed' => array(
          array('test_field_text', 200),
          'test_field_string_long',
          array('test_field_string_ascii_long', 200),
          'test_field_string_short',
        ),
      ),
    );
    db_create_table('test_table_index_length', $table_specification);

299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335
    $schema_object = Database::getConnection()->schema();

    // 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 {
      $schema_object->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $missing_field_spec);
      $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.
    $schema_object->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $table_specification);
    $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 {
      $schema_object->addIndex('test_table_index_length', 'test_separate', [['test_field_text', 200]], $table_specification);
      $this->fail('\Drupal\Core\Database\SchemaObjectExistsException exception missed.');
    }
    catch (SchemaObjectExistsException $e) {
      $this->pass('\Drupal\Core\Database\SchemaObjectExistsException thrown when index already exists.');
    }

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

336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356
    // Get index information.
    $results = db_query('SHOW INDEX FROM {test_table_index_length}');
    $expected_lengths = array(
      'test_regular' => array(
        'test_field_text' => 191,
        'test_field_string_long' => 191,
        'test_field_string_ascii_long' => NULL,
        'test_field_string_short' => NULL,
      ),
      'test_length' => array(
        'test_field_text' => 128,
        'test_field_string_long' => 128,
        'test_field_string_ascii_long' => 128,
        'test_field_string_short' => NULL,
      ),
      'test_mixed' => array(
        'test_field_text' => 191,
        'test_field_string_long' => 191,
        'test_field_string_ascii_long' => 200,
        'test_field_string_short' => NULL,
      ),
357 358 359
      'test_separate' => array(
        'test_field_text' => 191,
      ),
360 361 362 363
    );

    // Count the number of columns defined in the indexes.
    $column_count = 0;
364
    foreach ($table_specification_with_new_index['indexes'] as $index) {
365 366 367 368 369 370 371 372 373 374 375 376
      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.');
  }

377 378 379 380 381 382 383 384 385
  /**
   * Tests inserting data into an existing table.
   *
   * @param $table
   *   The database table to insert data into.
   *
   * @return
   *   TRUE if the insert succeeded, FALSE otherwise.
   */
386
  function tryInsert($table = 'test_table') {
387
    try {
388 389 390
      db_insert($table)
        ->fields(array('id' => mt_rand(10, 20)))
        ->execute();
391 392
      return TRUE;
    }
393
    catch (\Exception $e) {
394 395 396
      return FALSE;
    }
  }
397 398 399 400 401 402 403 404 405 406 407 408 409 410

  /**
   * 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.
   */
  function checkSchemaComment($description, $table, $column = NULL) {
    if (method_exists(Database::getConnection()->schema(), 'getComment')) {
      $comment = Database::getConnection()->schema()->getComment($table, $column);
411 412 413 414 415
      // The schema comment truncation for mysql is different.
      if (Database::getConnection()->databaseType() == 'mysql') {
        $max_length = $column ? 255 : 60;
        $description = Unicode::truncate($description, $max_length, TRUE, TRUE);
      }
416
      $this->assertEqual($comment, $description, 'The comment matches the schema description.');
417 418
    }
  }
419

420 421 422 423 424 425 426 427 428 429
  /**
   * Tests creating unsigned columns and data integrity thereof.
   */
  function testUnsignedColumns() {
    // First create the table with just a serial column.
    $table_name = 'unsigned_table';
    $table_spec = array(
      'fields' => array('serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)),
      'primary key' => array('serial_column'),
    );
430
    db_create_table($table_name, $table_spec);
431 432 433 434

    // Now set up columns for the other types.
    $types = array('int', 'float', 'numeric');
    foreach ($types as $type) {
435
      $column_spec = array('type' => $type, 'unsigned'=> TRUE);
436 437 438 439 440
      if ($type == 'numeric') {
        $column_spec += array('precision' => 10, 'scale' => 0);
      }
      $column_name = $type . '_column';
      $table_spec['fields'][$column_name] = $column_spec;
441
      db_add_field($table_name, $column_name, $column_spec);
442 443 444
    }

    // Finally, check each column and try to insert invalid values into them.
445
    foreach ($table_spec['fields'] as $column_name => $column_spec) {
446 447
      $this->assertTrue(db_field_exists($table_name, $column_name), format_string('Unsigned @type column was created.', array('@type' => $column_spec['type'])));
      $this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), format_string('Unsigned @type column rejected a negative value.', array('@type' => $column_spec['type'])));
448 449 450 451 452 453 454
    }
  }

  /**
   * Tries to insert a negative value into columns defined as unsigned.
   *
   * @param $table_name
455
   *   The table to insert.
456
   * @param $column_name
457 458
   *   The column to insert.
   *
459
   * @return
460
   *   TRUE if the insert succeeded, FALSE otherwise.
461 462 463 464
   */
  function tryUnsignedInsert($table_name, $column_name) {
    try {
      db_insert($table_name)
465 466
        ->fields(array($column_name => -1))
        ->execute();
467 468
      return TRUE;
    }
469
    catch (\Exception $e) {
470 471 472
      return FALSE;
    }
  }
473 474

  /**
475
   * Tests adding columns to an existing table.
476 477 478 479 480 481 482 483 484 485 486
   */
  function testSchemaAddField() {
    // Test varchar types.
    foreach (array(1, 32, 128, 256, 512) as $length) {
      $base_field_spec = array(
        'type' => 'varchar',
        'length' => $length,
      );
      $variations = array(
        array('not null' => FALSE),
        array('not null' => FALSE, 'default' => '7'),
487 488
        array('not null' => FALSE, 'default' => substr('"thing"', 0, $length)),
        array('not null' => FALSE, 'default' => substr("\"'hing", 0, $length)),
489
        array('not null' => TRUE, 'initial' => 'd'),
490
        array('not null' => FALSE, 'default' => NULL),
491 492 493 494 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
        array('not null' => TRUE, 'initial' => 'd', 'default' => '7'),
      );

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

    // Test int and float types.
    foreach (array('int', 'float') as $type) {
      foreach (array('tiny', 'small', 'medium', 'normal', 'big') as $size) {
        $base_field_spec = array(
          'type' => $type,
          'size' => $size,
        );
        $variations = array(
          array('not null' => FALSE),
          array('not null' => FALSE, 'default' => 7),
          array('not null' => TRUE, 'initial' => 1),
          array('not null' => TRUE, 'initial' => 1, 'default' => 7),
        );

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

    // Test numeric types.
    foreach (array(1, 5, 10, 40, 65) as $precision) {
      foreach (array(0, 2, 10, 30) as $scale) {
524
        // Skip combinations where precision is smaller than scale.
525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549
        if ($precision <= $scale) {
          continue;
        }

        $base_field_spec = array(
          'type' => 'numeric',
          'scale' => $scale,
          'precision' => $precision,
        );
        $variations = array(
          array('not null' => FALSE),
          array('not null' => FALSE, 'default' => 7),
          array('not null' => TRUE, 'initial' => 1),
          array('not null' => TRUE, 'initial' => 1, 'default' => 7),
        );

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

  /**
550
   * Asserts that a given field can be added and removed from a table.
551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568
   *
   * 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++);
    $table_spec = array(
      'fields' => array(
        'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
        'test_field' => $field_spec,
      ),
      'primary key' => array('serial_column'),
    );
    db_create_table($table_name, $table_spec);
569
    $this->pass(format_string('Table %table created.', array('%table' => $table_name)));
570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585

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

    // Clean-up.
    db_drop_table($table_name);

    // Try adding a field to an existing table.
    $table_name = 'test_table_' . ($this->counter++);
    $table_spec = array(
      'fields' => array(
        'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
      ),
      'primary key' => array('serial_column'),
    );
    db_create_table($table_name, $table_spec);
586
    $this->pass(format_string('Table %table created.', array('%table' => $table_name)));
587 588 589 590 591 592 593 594 595

    // Insert some rows to the table to test the handling of initial values.
    for ($i = 0; $i < 3; $i++) {
      db_insert($table_name)
        ->useDefaults(array('serial_column'))
        ->execute();
    }

    db_add_field($table_name, 'test_field', $field_spec);
596
    $this->pass(format_string('Column %column created.', array('%column' => 'test_field')));
597 598 599 600 601 602

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

    // Clean-up.
    db_drop_field($table_name, 'test_field');
603 604 605 606 607

    // Add back the field and then try to delete a field which is also a primary
    // key.
    db_add_field($table_name, 'test_field', $field_spec);
    db_drop_field($table_name, 'serial_column');
608 609 610 611
    db_drop_table($table_name);
  }

  /**
612
   * Asserts that a newly added field has the correct characteristics.
613 614 615 616 617 618 619 620 621 622 623
   */
  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'].
      $count = db_select($table_name)
        ->fields($table_name, array('serial_column'))
        ->condition($field_name, $field_spec['initial'], '<>')
        ->countQuery()
        ->execute()
        ->fetchField();
624
      $this->assertEqual($count, 0, 'Initial values filled out.');
625 626 627 628 629 630 631 632 633 634 635 636 637
    }

    // 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.
      $id = db_insert($table_name)
        ->useDefaults(array('serial_column'))
        ->execute();
      $field_value = db_select($table_name)
        ->fields($table_name, array($field_name))
        ->condition('serial_column', $id)
        ->execute()
        ->fetchField();
638
      $this->assertEqual($field_value, $field_spec['default'], 'Default value registered.');
639 640
    }
  }
641 642

  /**
643
   * Tests changing columns between types.
644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663
   */
  function testSchemaChangeField() {
    $field_specs = array(
      array('type' => 'int', 'size' => 'normal','not null' => FALSE),
      array('type' => 'int', 'size' => 'normal', 'not null' => TRUE, 'initial' => 1, 'default' => 17),
      array('type' => 'float', 'size' => 'normal', 'not null' => FALSE),
      array('type' => 'float', 'size' => 'normal', 'not null' => TRUE, 'initial' => 1, 'default' => 7.3),
      array('type' => 'numeric', 'scale' => 2, 'precision' => 10, 'not null' => FALSE),
      array('type' => 'numeric', 'scale' => 2, 'precision' => 10, 'not null' => TRUE, 'initial' => 1, 'default' => 7),
    );

    foreach ($field_specs as $i => $old_spec) {
      foreach ($field_specs as $j => $new_spec) {
        if ($i === $j) {
          // Do not change a field into itself.
          continue;
        }
        $this->assertFieldChange($old_spec, $new_spec);
      }
    }
664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684

    $field_specs = array(
      array('type' => 'varchar_ascii', 'length' => '255'),
      array('type' => 'varchar', 'length' => '255'),
      array('type' => 'text'),
      array('type' => 'blob', 'size' => 'big'),
    );

    foreach ($field_specs as $i => $old_spec) {
      foreach ($field_specs as $j => $new_spec) {
        if ($i === $j) {
          // Do not change a field into itself.
          continue;
        }
        // Note if the serialized data contained an object this would fail on
        // Postgres.
        // @see https://www.drupal.org/node/1031122
        $this->assertFieldChange($old_spec, $new_spec, serialize(['string' => "This \n has \\\\ some backslash \"*string action.\\n"]));
      }
    }

685 686 687 688 689 690 691 692 693 694
  }

  /**
   * Asserts that a field can be changed from one spec to another.
   *
   * @param $old_spec
   *   The beginning field specification.
   * @param $new_spec
   *   The ending field specification.
   */
695
  protected function assertFieldChange($old_spec, $new_spec, $test_data = NULL) {
696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712
    $table_name = 'test_table_' . ($this->counter++);
    $table_spec = array(
      'fields' => array(
        'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
        'test_field' => $old_spec,
      ),
      'primary key' => array('serial_column'),
    );
    db_create_table($table_name, $table_spec);
    $this->pass(format_string('Table %table created.', array('%table' => $table_name)));

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

    // Remove inserted rows.
    db_truncate($table_name)->execute();

713 714 715 716 717 718
    if ($test_data) {
      $id = db_insert($table_name)
        ->fields(['test_field'], [$test_data])
        ->execute();
    }

719 720 721
    // Change the field.
    db_change_field($table_name, 'test_field', 'test_field', $new_spec);

722 723 724 725 726 727 728 729 730
    if ($test_data) {
      $field_value = db_select($table_name)
        ->fields($table_name, ['test_field'])
        ->condition('serial_column', $id)
        ->execute()
        ->fetchField();
      $this->assertIdentical($field_value, $test_data);
    }

731 732 733 734 735 736
    // Check the field was changed.
    $this->assertFieldCharacteristics($table_name, 'test_field', $new_spec);

    // Clean-up.
    db_drop_table($table_name);
  }
737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795

  /**
   * Tests the findTables() method.
   */
  public function testFindTables() {
    // We will be testing with three tables, two of them using the default
    // prefix and the third one with an individually specified prefix.

    // Set up a new connection with different connection info.
    $connection_info = Database::getConnectionInfo();

    // Add per-table prefix to the second table.
    $new_connection_info = $connection_info['default'];
    $new_connection_info['prefix']['test_2_table'] = $new_connection_info['prefix']['default'] . '_shared_';
    Database::addConnectionInfo('test', 'default', $new_connection_info);

    Database::setActiveConnection('test');

    // Create the tables.
    $table_specification = [
      'description' => 'Test table.',
      'fields' => [
        'id'  => [
          'type' => 'int',
          'default' => NULL,
        ],
      ],
    ];
    Database::getConnection()->schema()->createTable('test_1_table', $table_specification);
    Database::getConnection()->schema()->createTable('test_2_table', $table_specification);
    Database::getConnection()->schema()->createTable('the_third_table', $table_specification);

    // Check the "all tables" syntax.
    $tables = Database::getConnection()->schema()->findTables('%');
    sort($tables);
    $expected = [
      // The 'config' table is added by
      // \Drupal\simpletest\KernelTestBase::containerBuild().
      'config',
      'test_1_table',
      // This table uses a per-table prefix, yet it is returned as un-prefixed.
      'test_2_table',
      'the_third_table',
    ];
    $this->assertEqual($tables, $expected, 'All tables were found.');

    // Check the restrictive syntax.
    $tables = Database::getConnection()->schema()->findTables('test_%');
    sort($tables);
    $expected = [
      'test_1_table',
      'test_2_table',
    ];
    $this->assertEqual($tables, $expected, 'Two tables were found.');

    // Go back to the initial connection.
    Database::setActiveConnection('default');
  }

796
}