/**
* Tests database interactions.
*/
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.');
}
/**
* Tests database interactions.
*/
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.')));
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');
// 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'));
// 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.');
// Copy the schema of the table.
db_copy_table_schema('test_table2', 'test_table3');
// Index should be copied.
$index_exists = Database::getConnection()->schema()->indexExists('test_table3', 'test_field');
$this->assertTrue($index_exists, 'Index was copied.');
// Data should still exist on the old table but not on the new one.
$count = db_select('test_table2')->countQuery()->execute()->fetchField();
$this->assertEqual($count, 1, 'The old table still has its content.');
$count = db_select('test_table3')->countQuery()->execute()->fetchField();
$this->assertEqual($count, 0, 'The new table has no content.');
// Ensure that the proper exceptions are thrown for db_copy_table_schema().
$fail = FALSE;
try {
db_copy_table_schema('test_table4', 'test_table5');
} catch (SchemaObjectDoesNotExistException $e) {
$fail = TRUE;
}
$this->assertTrue($fail, 'Ensure that db_copy_table_schema() throws an exception when the source table does not exist.');
$fail = FALSE;
try {
db_copy_table_schema('test_table2', 'test_table3');
} catch (SchemaObjectExistsException $e) {
$fail = TRUE;
}
$this->assertTrue($fail, 'Ensure that db_copy_table_schema() throws an exception when the destination table already exists.');
// 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.');
// 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.');
}
