function SchemaTest::testSchema in Zircon Profile 8

Same name and namespace in other branches

1. 8.0 core/modules/system/src/Tests/Database/SchemaTest.php \Drupal\system\Tests\Database\SchemaTest::testSchema()

Tests database interactions.

File

core/modules/system/src/Tests/Database/SchemaTest.php, line 32

Contains \Drupal\system\Tests\Database\SchemaTest.

Class

SchemaTest

Tests table creation and modification via the schema API.

Namespace

Drupal\system\Tests\Database

Code

function testSchema() {

  // Try creating a table.
  $table_specification = array(
    'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
    'fields' => array(
      'id' => array(
        'type' => 'int',
        'default' => NULL,
      ),
      'test_field' => array(
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'Schema table description may contain "quotes" and could be long—very long indeed. There could be "multiple quoted regions".',
      ),
      'test_field_string' => array(
        'type' => 'varchar',
        'length' => 20,
        'not null' => TRUE,
        'default' => "'\"funky default'\"",
        'description' => 'Schema column description for string.',
      ),
      'test_field_string_ascii' => array(
        'type' => 'varchar_ascii',
        'length' => 255,
        'description' => 'Schema column description for ASCII string.',
      ),
    ),
  );
  db_create_table('test_table', $table_specification);

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

  // 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');
  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') {
        $string_check = $column->Collation == 'utf8mb4_general_ci';
      }
      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.');
  }

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

  // Add a default value to the column.
  db_field_set_default('test_table', 'test_field', 0);

  // The insert should now succeed.
  $this
    ->assertTrue($this
    ->tryInsert(), 'Insert with a default succeeded.');

  // Remove the default.
  db_field_set_no_default('test_table', 'test_field');

  // The insert should fail again.
  $this
    ->assertFalse($this
    ->tryInsert(), 'Insert without a default failed.');

  // Test for fake index and test for the boolean result of indexExists().
  $index_exists = Database::getConnection()
    ->schema()
    ->indexExists('test_table', 'test_field');
  $this
    ->assertIdentical($index_exists, FALSE, 'Fake index does not exists');

  // Add index.
  db_add_index('test_table', 'test_field', array(
    'test_field',
  ), $table_specification);

  // Test for created index and test for the boolean result of indexExists().
  $index_exists = Database::getConnection()
    ->schema()
    ->indexExists('test_table', 'test_field');
  $this
    ->assertIdentical($index_exists, TRUE, 'Index created.');

  // Rename the table.
  db_rename_table('test_table', 'test_table2');

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

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

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

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

  // Recreate the table.
  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.',
  ));

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

  // Change the new field to a serial column.
  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',
    ),
  ));

  // Assert that the column comment has been set.
  $this
    ->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');
  $this
    ->assertTrue($this
    ->tryInsert(), 'Insert with a serial succeeded.');
  $max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')
    ->fetchField();
  $this
    ->assertTrue($this
    ->tryInsert(), 'Insert with a serial succeeded.');
  $max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')
    ->fetchField();
  $this
    ->assertTrue($max2 > $max1, 'The serial is monotone.');
  $count = db_query('SELECT COUNT(*) FROM {test_table}')
    ->fetchField();
  $this
    ->assertEqual($count, 2, 'There were two rows.');

  // 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.');
  }

  // 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);
  } catch (\Exception $e) {
  }
  $this
    ->assertTrue(db_table_exists('test_timestamp'), 'Table with database specific datatype was created.');
}