Newer
Older
<?php
/**
* @file
Dries Buytaert
committed
* Contains Drupal\system\Tests\Database\SchemaTest.
Dries Buytaert
committed
namespace Drupal\system\Tests\Database;
catch
committed
use Drupal\Core\Database\Database;
use Drupal\Core\Database\SchemaObjectDoesNotExistException;
use Drupal\Core\Database\SchemaObjectExistsException;
use Drupal\simpletest\KernelTestBase;
Alex Pott
committed
use Drupal\Component\Utility\Unicode;
catch
committed
* Tests table creation and modification via the schema API.
*
* @group Database
class SchemaTest extends KernelTestBase {
Jennifer Hodgdon
committed
Angie Byron
committed
/**
* A global counter for table and field creation.
*/
protected $counter;
Angie Byron
committed
Jennifer Hodgdon
committed
* Tests database interactions.
*/
function testSchema() {
// Try creating a table.
$table_specification = array(
Alex Pott
committed
'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,
Alex Pott
committed
'description' => 'Schema table description may contain "quotes" and could be long—very long indeed. There could be "multiple quoted regions".',
Alex Pott
committed
'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.',
),
Dries Buytaert
committed
db_create_table('test_table', $table_specification);
// Assert that the table exists.
Jennifer Hodgdon
committed
$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');
// 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 == 'utf8_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.
Jennifer Hodgdon
committed
$this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
// Add a default value to the column.
Dries Buytaert
committed
db_field_set_default('test_table', 'test_field', 0);
// The insert should now succeed.
Jennifer Hodgdon
committed
$this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
// Remove the default.
Dries Buytaert
committed
db_field_set_no_default('test_table', 'test_field');
// The insert should fail again.
Jennifer Hodgdon
committed
$this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
Dries Buytaert
committed
// Test for fake index and test for the boolean result of indexExists().
Angie Byron
committed
$index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
Jennifer Hodgdon
committed
$this->assertIdentical($index_exists, FALSE, 'Fake index does not exists');
Angie Byron
committed
// Add index.
db_add_index('test_table', 'test_field', array('test_field'));
Dries Buytaert
committed
// Test for created index and test for the boolean result of indexExists().
Angie Byron
committed
$index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
Jennifer Hodgdon
committed
$this->assertIdentical($index_exists, TRUE, 'Index created.');
Angie Byron
committed
// Rename the table.
Dries Buytaert
committed
db_rename_table('test_table', 'test_table2');
Angie Byron
committed
// Index should be renamed.
$index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
Jennifer Hodgdon
committed
$this->assertTrue($index_exists, 'Index was renamed.');
Angie Byron
committed
// We need the default so that we can insert after the rename.
Dries Buytaert
committed
db_field_set_default('test_table2', 'test_field', 0);
Jennifer Hodgdon
committed
$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();
Jennifer Hodgdon
committed
$this->assertEqual($count, 2, 'Two fields were successfully inserted.');
// Try to drop the table.
Dries Buytaert
committed
db_drop_table('test_table2');
Jennifer Hodgdon
committed
$this->assertFalse(db_table_exists('test_table2'), 'The dropped table does not exist.');
// Recreate the table.
Dries Buytaert
committed
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.
Dries Buytaert
committed
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');
Jennifer Hodgdon
committed
$this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
$max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
Jennifer Hodgdon
committed
$this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
$max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
Jennifer Hodgdon
committed
$this->assertTrue($max2 > $max1, 'The serial is monotone.');
$count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();
Jennifer Hodgdon
committed
$this->assertEqual($count, 2, 'There were two rows.');
Angie Byron
committed
Angie Byron
committed
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
// 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.');
}
Angie Byron
committed
// 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);
}
Jennifer Hodgdon
committed
$this->assertTrue(db_table_exists('test_timestamp'), 'Table with database specific datatype was created.');
Jennifer Hodgdon
committed
/**
* Tests inserting data into an existing table.
*
* @param $table
* The database table to insert data into.
*
* @return
* TRUE if the insert succeeded, FALSE otherwise.
*/
function tryInsert($table = 'test_table') {
db_insert($table)
->fields(array('id' => mt_rand(10, 20)))
->execute();
return TRUE;
}
return FALSE;
}
}
/**
* Checks that a table or column comment matches a given description.
*
* @param $description
* The asserted description.
* @param $table
* The table to test.
* @param $column
* Optional column to test.
*/
function checkSchemaComment($description, $table, $column = NULL) {
if (method_exists(Database::getConnection()->schema(), 'getComment')) {
$comment = Database::getConnection()->schema()->getComment($table, $column);
Alex Pott
committed
// The schema comment truncation for mysql is different.
if (Database::getConnection()->databaseType() == 'mysql') {
$max_length = $column ? 255 : 60;
$description = Unicode::truncate($description, $max_length, TRUE, TRUE);
}
Jennifer Hodgdon
committed
$this->assertEqual($comment, $description, 'The comment matches the schema description.');
Dries Buytaert
committed
Angie Byron
committed
/**
* Tests creating unsigned columns and data integrity thereof.
*/
function testUnsignedColumns() {
// First create the table with just a serial column.
$table_name = 'unsigned_table';
$table_spec = array(
'fields' => array('serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)),
'primary key' => array('serial_column'),
);
db_create_table($table_name, $table_spec);
Angie Byron
committed
// Now set up columns for the other types.
$types = array('int', 'float', 'numeric');
foreach ($types as $type) {
$column_spec = array('type' => $type, 'unsigned'=> TRUE);
Angie Byron
committed
if ($type == 'numeric') {
$column_spec += array('precision' => 10, 'scale' => 0);
}
$column_name = $type . '_column';
$table_spec['fields'][$column_name] = $column_spec;
db_add_field($table_name, $column_name, $column_spec);
Angie Byron
committed
}
// Finally, check each column and try to insert invalid values into them.
Dries Buytaert
committed
foreach ($table_spec['fields'] as $column_name => $column_spec) {
Jennifer Hodgdon
committed
$this->assertTrue(db_field_exists($table_name, $column_name), format_string('Unsigned @type column was created.', array('@type' => $column_spec['type'])));
$this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), format_string('Unsigned @type column rejected a negative value.', array('@type' => $column_spec['type'])));
Angie Byron
committed
}
}
/**
* Tries to insert a negative value into columns defined as unsigned.
*
* @param $table_name
Jennifer Hodgdon
committed
* The table to insert.
Angie Byron
committed
* @param $column_name
Jennifer Hodgdon
committed
* The column to insert.
*
Angie Byron
committed
* @return
Jennifer Hodgdon
committed
* TRUE if the insert succeeded, FALSE otherwise.
Angie Byron
committed
*/
function tryUnsignedInsert($table_name, $column_name) {
try {
db_insert($table_name)
->fields(array($column_name => -1))
->execute();
Angie Byron
committed
return TRUE;
}
Angie Byron
committed
return FALSE;
}
}
Angie Byron
committed
/**
Jennifer Hodgdon
committed
* Tests adding columns to an existing table.
Angie Byron
committed
*/
function testSchemaAddField() {
// Test varchar types.
foreach (array(1, 32, 128, 256, 512) as $length) {
$base_field_spec = array(
'type' => 'varchar',
'length' => $length,
);
$variations = array(
array('not null' => FALSE),
array('not null' => FALSE, 'default' => '7'),
Alex Pott
committed
array('not null' => FALSE, 'default' => substr('"thing"', 0, $length)),
array('not null' => FALSE, 'default' => substr("\"'hing", 0, $length)),
Angie Byron
committed
array('not null' => TRUE, 'initial' => 'd'),
array('not null' => FALSE, 'default' => NULL),
Angie Byron
committed
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
array('not null' => TRUE, 'initial' => 'd', 'default' => '7'),
);
foreach ($variations as $variation) {
$field_spec = $variation + $base_field_spec;
$this->assertFieldAdditionRemoval($field_spec);
}
}
// Test int and float types.
foreach (array('int', 'float') as $type) {
foreach (array('tiny', 'small', 'medium', 'normal', 'big') as $size) {
$base_field_spec = array(
'type' => $type,
'size' => $size,
);
$variations = array(
array('not null' => FALSE),
array('not null' => FALSE, 'default' => 7),
array('not null' => TRUE, 'initial' => 1),
array('not null' => TRUE, 'initial' => 1, 'default' => 7),
);
foreach ($variations as $variation) {
$field_spec = $variation + $base_field_spec;
$this->assertFieldAdditionRemoval($field_spec);
}
}
}
// Test numeric types.
foreach (array(1, 5, 10, 40, 65) as $precision) {
foreach (array(0, 2, 10, 30) as $scale) {
// Skip combinations where precision is smaller than scale.
Angie Byron
committed
if ($precision <= $scale) {
continue;
}
$base_field_spec = array(
'type' => 'numeric',
'scale' => $scale,
'precision' => $precision,
);
$variations = array(
array('not null' => FALSE),
array('not null' => FALSE, 'default' => 7),
array('not null' => TRUE, 'initial' => 1),
array('not null' => TRUE, 'initial' => 1, 'default' => 7),
);
foreach ($variations as $variation) {
$field_spec = $variation + $base_field_spec;
$this->assertFieldAdditionRemoval($field_spec);
}
}
}
}
/**
Jennifer Hodgdon
committed
* Asserts that a given field can be added and removed from a table.
Angie Byron
committed
*
* The addition test covers both defining a field of a given specification
* when initially creating at table and extending an existing table.
*
* @param $field_spec
* The schema specification of the field.
*/
protected function assertFieldAdditionRemoval($field_spec) {
// Try creating the field on a new table.
$table_name = 'test_table_' . ($this->counter++);
$table_spec = array(
'fields' => array(
'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
'test_field' => $field_spec,
),
'primary key' => array('serial_column'),
);
db_create_table($table_name, $table_spec);
Jennifer Hodgdon
committed
$this->pass(format_string('Table %table created.', array('%table' => $table_name)));
Angie Byron
committed
// Check the characteristics of the field.
$this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);
// Clean-up.
db_drop_table($table_name);
// Try adding a field to an existing table.
$table_name = 'test_table_' . ($this->counter++);
$table_spec = array(
'fields' => array(
'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
),
'primary key' => array('serial_column'),
);
db_create_table($table_name, $table_spec);
Jennifer Hodgdon
committed
$this->pass(format_string('Table %table created.', array('%table' => $table_name)));
Angie Byron
committed
// Insert some rows to the table to test the handling of initial values.
for ($i = 0; $i < 3; $i++) {
db_insert($table_name)
->useDefaults(array('serial_column'))
->execute();
}
db_add_field($table_name, 'test_field', $field_spec);
Jennifer Hodgdon
committed
$this->pass(format_string('Column %column created.', array('%column' => 'test_field')));
Angie Byron
committed
// Check the characteristics of the field.
$this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);
// Clean-up.
db_drop_field($table_name, 'test_field');
Alex Pott
committed
// Add back the field and then try to delete a field which is also a primary
// key.
db_add_field($table_name, 'test_field', $field_spec);
db_drop_field($table_name, 'serial_column');
Angie Byron
committed
db_drop_table($table_name);
}
/**
Jennifer Hodgdon
committed
* Asserts that a newly added field has the correct characteristics.
Angie Byron
committed
*/
protected function assertFieldCharacteristics($table_name, $field_name, $field_spec) {
// Check that the initial value has been registered.
if (isset($field_spec['initial'])) {
// There should be no row with a value different then $field_spec['initial'].
$count = db_select($table_name)
->fields($table_name, array('serial_column'))
->condition($field_name, $field_spec['initial'], '<>')
->countQuery()
->execute()
->fetchField();
Jennifer Hodgdon
committed
$this->assertEqual($count, 0, 'Initial values filled out.');
Angie Byron
committed
}
// Check that the default value has been registered.
if (isset($field_spec['default'])) {
// Try inserting a row, and check the resulting value of the new column.
$id = db_insert($table_name)
->useDefaults(array('serial_column'))
->execute();
$field_value = db_select($table_name)
->fields($table_name, array($field_name))
->condition('serial_column', $id)
->execute()
->fetchField();
Jennifer Hodgdon
committed
$this->assertEqual($field_value, $field_spec['default'], 'Default value registered.');
Angie Byron
committed
}
}
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
/**
* Tests changing columns between numeric types.
*/
function testSchemaChangeField() {
$field_specs = array(
array('type' => 'int', 'size' => 'normal','not null' => FALSE),
array('type' => 'int', 'size' => 'normal', 'not null' => TRUE, 'initial' => 1, 'default' => 17),
array('type' => 'float', 'size' => 'normal', 'not null' => FALSE),
array('type' => 'float', 'size' => 'normal', 'not null' => TRUE, 'initial' => 1, 'default' => 7.3),
array('type' => 'numeric', 'scale' => 2, 'precision' => 10, 'not null' => FALSE),
array('type' => 'numeric', 'scale' => 2, 'precision' => 10, 'not null' => TRUE, 'initial' => 1, 'default' => 7),
);
foreach ($field_specs as $i => $old_spec) {
foreach ($field_specs as $j => $new_spec) {
if ($i === $j) {
// Do not change a field into itself.
continue;
}
$this->assertFieldChange($old_spec, $new_spec);
}
}
}
/**
* Asserts that a field can be changed from one spec to another.
*
* @param $old_spec
* The beginning field specification.
* @param $new_spec
* The ending field specification.
*/
protected function assertFieldChange($old_spec, $new_spec) {
$table_name = 'test_table_' . ($this->counter++);
$table_spec = array(
'fields' => array(
'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
'test_field' => $old_spec,
),
'primary key' => array('serial_column'),
);
db_create_table($table_name, $table_spec);
$this->pass(format_string('Table %table created.', array('%table' => $table_name)));
// Check the characteristics of the field.
$this->assertFieldCharacteristics($table_name, 'test_field', $old_spec);
// Remove inserted rows.
db_truncate($table_name)->execute();
// Change the field.
db_change_field($table_name, 'test_field', 'test_field', $new_spec);
// Check the field was changed.
$this->assertFieldCharacteristics($table_name, 'test_field', $new_spec);
// Clean-up.
db_drop_table($table_name);
}