public function SchemaTest::testSchema in Drupal 9

Same name and namespace in other branches
8 core/tests/Drupal/KernelTests/Core/Database/SchemaTest.php \Drupal\KernelTests\Core\Database\SchemaTest::testSchema()
Tests database interactions.
File

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

SchemaTest: Tests table creation and modification via the schema API.
Namespace

Drupal\KernelTests\Core\Database
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
      ->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.
  $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.');
}
You are here

public function SchemaTest::testSchema in Drupal 9

File

Class

Namespace

Code

API Navigation
