function SchemaTest::testSchema

Tests database interactions.

File

core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php, line 57

Class

SchemaTest

Tests table creation and modification via the schema API.

Namespace

Code

public function testSchema() {
  // Try creating a table.
  $table_specification = [
    'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
    'fields' => [
      'id' => [
        'type' => 'int',
        'default' => NULL,
      ],
      'test_field' => [
        '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' => [
        'type' => 'varchar',
        'length' => 20,
        'not null' => TRUE,
        'default' => "'\"funky default'\"",
        'description' => 'Schema column description for string.',
      ],
      'test_field_string_ascii' => [
        'type' => 'varchar_ascii',
        'length' => 255,
        'description' => 'Schema column description for ASCII string.',
      ],
    ],
  ];
  $this->schema
    ->createTable('test_table', $table_specification);
  // Assert that the table exists.
  $this->assertTrue($this->schema
    ->tableExists('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 ($this->connection
    ->databaseType() === 'mysql') {
    // Make sure that varchar fields have the correct collation.
    $columns = $this->connection
      ->query('SHOW FULL COLUMNS FROM {test_table}');
    foreach ($columns as $column) {
      if ($column->Field == 'test_field_string') {
        $string_check = $column->Collation == 'utf8mb4_general_ci' || $column->Collation == 'utf8mb4_0900_ai_ci';
      }
      if ($column->Field == 'test_field_string_ascii') {
        $string_ascii_check = $column->Collation == 'ascii_general_ci';
      }
    }
    $this->assertNotEmpty($string_check, 'string field has the right collation.');
    $this->assertNotEmpty($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.
  $this->schema
    ->changeField('test_table', 'test_field', 'test_field', [
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
  ]);
  // The insert should now succeed.
  $this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
  // Remove the default.
  $this->schema
    ->changeField('test_table', 'test_field', 'test_field', [
    'type' => 'int',
    'not null' => TRUE,
  ]);
  // 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 = $this->schema
    ->indexExists('test_table', 'test_field');
  $this->assertFalse($index_exists, 'Fake index does not exist');
  // Add index.
  $this->schema
    ->addIndex('test_table', 'test_field', [
    'test_field',
  ], $table_specification);
  // Test for created index and test for the boolean result of indexExists().
  $index_exists = $this->schema
    ->indexExists('test_table', 'test_field');
  $this->assertTrue($index_exists, 'Index created.');
  // Rename the table.
  $this->assertNull($this->schema
    ->renameTable('test_table', 'test_table2'));
  // Index should be renamed.
  $index_exists = $this->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.
  $this->schema
    ->changeField('test_table2', 'test_field', 'test_field', [
    'type' => 'int',
    'not null' => TRUE,
    'default' => 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 = $this->connection
    ->query('SELECT COUNT(*) FROM {test_table2}')
    ->fetchField();
  $this->assertEquals(2, $count, 'Two fields were successfully inserted.');
  // Try to drop the table.
  $this->schema
    ->dropTable('test_table2');
  $this->assertFalse($this->schema
    ->tableExists('test_table2'), 'The dropped table does not exist.');
  // Recreate the table.
  $this->schema
    ->createTable('test_table', $table_specification);
  $this->schema
    ->changeField('test_table', 'test_field', 'test_field', [
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
  ]);
  $this->schema
    ->addField('test_table', 'test_serial', [
    '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.
  $this->schema
    ->changeField('test_table', 'test_serial', 'test_serial', [
    'type' => 'serial',
    'not null' => TRUE,
    'description' => 'Changed column description.',
  ], [
    'primary key' => [
      '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 = $this->connection
    ->query('SELECT MAX([test_serial]) FROM {test_table}')
    ->fetchField();
  $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
  $max2 = $this->connection
    ->query('SELECT MAX([test_serial]) FROM {test_table}')
    ->fetchField();
  $this->assertTrue($max2 > $max1, 'The serial is monotone.');
  $count = $this->connection
    ->query('SELECT COUNT(*) FROM {test_table}')
    ->fetchField();
  $this->assertEquals(2, $count, 'There were two rows.');
  // Test adding a serial field to an existing table.
  $this->schema
    ->dropTable('test_table');
  $this->schema
    ->createTable('test_table', $table_specification);
  $this->schema
    ->changeField('test_table', 'test_field', 'test_field', [
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
  ]);
  $this->schema
    ->addField('test_table', 'test_serial', [
    'type' => 'serial',
    'not null' => TRUE,
  ], [
    'primary key' => [
      'test_serial',
    ],
  ]);
  // Test the primary key columns.
  $method = new \ReflectionMethod(get_class($this->schema), 'findPrimaryKeyColumns');
  $method->setAccessible(TRUE);
  $this->assertSame([
    'test_serial',
  ], $method->invoke($this->schema, 'test_table'));
  $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
  $max1 = $this->connection
    ->query('SELECT MAX([test_serial]) FROM {test_table}')
    ->fetchField();
  $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
  $max2 = $this->connection
    ->query('SELECT MAX([test_serial]) FROM {test_table}')
    ->fetchField();
  $this->assertTrue($max2 > $max1, 'The serial is monotone.');
  $count = $this->connection
    ->query('SELECT COUNT(*) FROM {test_table}')
    ->fetchField();
  $this->assertEquals(2, $count, 'There were two rows.');
  // Test adding a new column and form a composite primary key with it.
  $this->schema
    ->addField('test_table', 'test_composite_primary_key', [
    'type' => 'int',
    'not null' => TRUE,
    'default' => 0,
  ], [
    'primary key' => [
      'test_serial',
      'test_composite_primary_key',
    ],
  ]);
  // Test the primary key columns.
  $this->assertSame([
    'test_serial',
    'test_composite_primary_key',
  ], $method->invoke($this->schema, 'test_table'));
  // Test renaming of keys and constraints.
  $this->schema
    ->dropTable('test_table');
  $table_specification = [
    'fields' => [
      'id' => [
        'type' => 'serial',
        'not null' => TRUE,
      ],
      'test_field' => [
        'type' => 'int',
        'default' => 0,
      ],
    ],
    'primary key' => [
      'id',
    ],
    'unique keys' => [
      'test_field' => [
        'test_field',
      ],
    ],
  ];
  // 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);
  $this->assertIndexOnColumns($table_name, [
    'id',
  ], 'primary');
  $this->assertIndexOnColumns($table_name, [
    'test_field',
  ], 'unique');
  $new_table_name = strtolower($this->getRandomGenerator()
    ->name(63 - strlen($this->getDatabasePrefix())));
  $this->assertNull($this->schema
    ->renameTable($table_name, $new_table_name));
  // Test for renamed primary and unique keys.
  $this->assertIndexOnColumns($new_table_name, [
    'id',
  ], 'primary');
  $this->assertIndexOnColumns($new_table_name, [
    'test_field',
  ], 'unique');
  // 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.
  if ($this->connection
    ->databaseType() == 'pgsql') {
    $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.');
  }
  // Use database specific data type and ensure that table is created.
  $table_specification = [
    'description' => 'Schema table description.',
    'fields' => [
      'timestamp' => [
        'mysql_type' => 'timestamp',
        'pgsql_type' => 'timestamp',
        'sqlite_type' => 'datetime',
        'not null' => FALSE,
        'default' => NULL,
      ],
    ],
  ];
  try {
    $this->schema
      ->createTable('test_timestamp', $table_specification);
  } catch (\Exception $e) {
  }
  $this->assertTrue($this->schema
    ->tableExists('test_timestamp'), 'Table with database specific datatype was created.');
}