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