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