SchemaTest.php 15.9 KB
Newer Older
1 2 3 4
<?php

/**
 * @file
5
 * Contains Drupal\system\Tests\Database\SchemaTest.
6 7
 */

8
namespace Drupal\system\Tests\Database;
9 10

use Drupal\Core\Database\Database;
11 12
use Drupal\Core\Database\SchemaObjectDoesNotExistException;
use Drupal\Core\Database\SchemaObjectExistsException;
13
use Drupal\simpletest\KernelTestBase;
14
use Drupal\Component\Utility\Unicode;
15

16
/**
17 18 19
 * Tests table creation and modification via the schema API.
 *
 * @group Database
20
 */
21
class SchemaTest extends KernelTestBase {
22

23 24 25 26 27
  /**
   * A global counter for table and field creation.
   */
  var $counter;

28
  /**
29
   * Tests database interactions.
30 31 32 33
   */
  function testSchema() {
    // Try creating a table.
    $table_specification = array(
34
      'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
35 36 37 38 39
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'default' => NULL,
        ),
40
        'test_field'  => array(
41 42
          'type' => 'int',
          'not null' => TRUE,
43
          'description' => 'Schema table description may contain "quotes" and could be long—very long indeed. There could be "multiple quoted regions".',
44
        ),
45 46 47 48 49 50 51
        'test_field_string'  => array(
          'type' => 'varchar',
          'length' => 20,
          'not null' => TRUE,
          'default' => "'\"funky default'\"",
          'description' => 'Schema column description for string.',
        ),
52 53
      ),
    );
54
    db_create_table('test_table', $table_specification);
55 56

    // Assert that the table exists.
57
    $this->assertTrue(db_table_exists('test_table'), 'The table exists.');
58

59 60 61 62 63 64
    // 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');

65
    // An insert without a value for the column 'test_table' should fail.
66
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
67 68

    // Add a default value to the column.
69
    db_field_set_default('test_table', 'test_field', 0);
70
    // The insert should now succeed.
71
    $this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
72 73

    // Remove the default.
74
    db_field_set_no_default('test_table', 'test_field');
75
    // The insert should fail again.
76
    $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
77

78
    // Test for fake index and test for the boolean result of indexExists().
79
    $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
80
    $this->assertIdentical($index_exists, FALSE, 'Fake index does not exists');
81 82
    // Add index.
    db_add_index('test_table', 'test_field', array('test_field'));
83
    // Test for created index and test for the boolean result of indexExists().
84
    $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
85
    $this->assertIdentical($index_exists, TRUE, 'Index created.');
86

87
    // Rename the table.
88
    db_rename_table('test_table', 'test_table2');
89 90 91

    // Index should be renamed.
    $index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
92
    $this->assertTrue($index_exists, 'Index was renamed.');
93

94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
    // 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.');

126
    // We need the default so that we can insert after the rename.
127
    db_field_set_default('test_table2', 'test_field', 0);
128 129
    $this->assertFalse($this->tryInsert(), 'Insert into the old table failed.');
    $this->assertTrue($this->tryInsert('test_table2'), 'Insert into the new table succeeded.');
130 131 132

    // We should have successfully inserted exactly two rows.
    $count = db_query('SELECT COUNT(*) FROM {test_table2}')->fetchField();
133
    $this->assertEqual($count, 2, 'Two fields were successfully inserted.');
134 135

    // Try to drop the table.
136
    db_drop_table('test_table2');
137
    $this->assertFalse(db_table_exists('test_table2'), 'The dropped table does not exist.');
138 139

    // Recreate the table.
140 141 142
    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.'));
143 144 145

    // Assert that the column comment has been set.
    $this->checkSchemaComment('Added column description.', 'test_table', 'test_serial');
146 147

    // Change the new field to a serial column.
148
    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')));
149 150 151

    // Assert that the column comment has been set.
    $this->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');
152

153
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
154
    $max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
155
    $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
156
    $max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
157
    $this->assertTrue($max2 > $max1, 'The serial is monotone.');
158

159
    $count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();
160
    $this->assertEqual($count, 2, 'There were two rows.');
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177

    // 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);
    }
178
    catch (\Exception $e) {}
179
    $this->assertTrue(db_table_exists('test_timestamp'), 'Table with database specific datatype was created.');
180 181
  }

182 183 184 185 186 187 188 189 190
  /**
   * Tests inserting data into an existing table.
   *
   * @param $table
   *   The database table to insert data into.
   *
   * @return
   *   TRUE if the insert succeeded, FALSE otherwise.
   */
191
  function tryInsert($table = 'test_table') {
192
    try {
193 194 195
      db_insert($table)
        ->fields(array('id' => mt_rand(10, 20)))
        ->execute();
196 197
      return TRUE;
    }
198
    catch (\Exception $e) {
199 200 201
      return FALSE;
    }
  }
202 203 204 205 206 207 208 209 210 211 212 213 214 215

  /**
   * 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);
216 217 218 219 220
      // 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);
      }
221
      $this->assertEqual($comment, $description, 'The comment matches the schema description.');
222 223
    }
  }
224

225 226 227 228 229 230 231 232 233 234
  /**
   * 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'),
    );
235
    db_create_table($table_name, $table_spec);
236 237 238 239

    // Now set up columns for the other types.
    $types = array('int', 'float', 'numeric');
    foreach ($types as $type) {
240
      $column_spec = array('type' => $type, 'unsigned'=> TRUE);
241 242 243 244 245
      if ($type == 'numeric') {
        $column_spec += array('precision' => 10, 'scale' => 0);
      }
      $column_name = $type . '_column';
      $table_spec['fields'][$column_name] = $column_spec;
246
      db_add_field($table_name, $column_name, $column_spec);
247 248 249
    }

    // Finally, check each column and try to insert invalid values into them.
250
    foreach ($table_spec['fields'] as $column_name => $column_spec) {
251 252
      $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'])));
253 254 255 256 257 258 259
    }
  }

  /**
   * Tries to insert a negative value into columns defined as unsigned.
   *
   * @param $table_name
260
   *   The table to insert.
261
   * @param $column_name
262 263
   *   The column to insert.
   *
264
   * @return
265
   *   TRUE if the insert succeeded, FALSE otherwise.
266 267 268 269
   */
  function tryUnsignedInsert($table_name, $column_name) {
    try {
      db_insert($table_name)
270 271
        ->fields(array($column_name => -1))
        ->execute();
272 273
      return TRUE;
    }
274
    catch (\Exception $e) {
275 276 277
      return FALSE;
    }
  }
278 279

  /**
280
   * Tests adding columns to an existing table.
281 282 283 284 285 286 287 288 289 290 291
   */
  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'),
292 293
        array('not null' => FALSE, 'default' => substr('"thing"', 0, $length)),
        array('not null' => FALSE, 'default' => substr("\"'hing", 0, $length)),
294
        array('not null' => TRUE, 'initial' => 'd'),
295
        array('not null' => FALSE, 'default' => NULL),
296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
        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) {
        if ($precision <= $scale) {
          // Precision must be smaller then 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);
        }
      }
    }
  }

  /**
355
   * Asserts that a given field can be added and removed from a table.
356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373
   *
   * 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);
374
    $this->pass(format_string('Table %table created.', array('%table' => $table_name)));
375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390

    // 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);
391
    $this->pass(format_string('Table %table created.', array('%table' => $table_name)));
392 393 394 395 396 397 398 399 400

    // 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);
401
    $this->pass(format_string('Column %column created.', array('%column' => 'test_field')));
402 403 404 405 406 407 408 409 410 411

    // Check the characteristics of the field.
    $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);

    // Clean-up.
    db_drop_field($table_name, 'test_field');
    db_drop_table($table_name);
  }

  /**
412
   * Asserts that a newly added field has the correct characteristics.
413 414 415 416 417 418 419 420 421 422 423
   */
  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();
424
      $this->assertEqual($count, 0, 'Initial values filled out.');
425 426 427 428 429 430 431 432 433 434 435 436 437
    }

    // 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();
438
      $this->assertEqual($field_value, $field_spec['default'], 'Default value registered.');
439 440 441 442
    }

    db_drop_field($table_name, $field_name);
  }
443
}