1 <?php

     2 

     3 /**

     4  * @file

     5  * Tests for the Database Schema API.

     6  */

     7 

     8 /**

     9  * Unit tests for the Schema API.

    10  */

    11 class SchemaTestCase extends DrupalWebTestCase {

    12   /**

    13    * A global counter for table and field creation.

    14    */

    15   var $counter;

    16 

    17   public static function getInfo() {

    18     return array(

    19       'name' => 'Schema API',

    20       'description' => 'Tests table creation and modification via the schema API.',

    21       'group' => 'Database',

    22     );

    23   }

    24 

    25   /**

    26    *

    27    */

    28   function testSchema() {

    29     // Try creating a table.

    30     $table_specification = array(

    31       'description' => 'Schema table description.',

    32       'fields' => array(

    33         'id'  => array(

    34           'type' => 'int',

    35           'default' => NULL,

    36         ),

    37         'test_field'  => array(

    38           'type' => 'int',

    39           'not null' => TRUE,

    40           'description' => 'Schema column description.',

    41         ),

    42       ),

    43     );

    44     db_create_table('test_table', $table_specification);

    45 

    46     // Assert that the table exists.

    47     $this->assertTrue(db_table_exists('test_table'), 'The table exists.');

    48 

    49     // Assert that the table comment has been set.

    50     $this->checkSchemaComment($table_specification['description'], 'test_table');

    51 

    52     // Assert that the column comment has been set.

    53     $this->checkSchemaComment($table_specification['fields']['test_field']['description'], 'test_table', 'test_field');

    54 

    55     // An insert without a value for the column 'test_table' should fail.

    56     $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');

    57 

    58     // Add a default value to the column.

    59     db_field_set_default('test_table', 'test_field', 0);

    60     // The insert should now succeed.

    61     $this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');

    62 

    63     // Remove the default.

    64     db_field_set_no_default('test_table', 'test_field');

    65     // The insert should fail again.

    66     $this->assertFalse($this->tryInsert(), 'Insert without a default failed.');

    67 

    68     // Test for fake index and test for the boolean result of indexExists().

    69     $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');

    70     $this->assertIdentical($index_exists, FALSE, 'Fake index does not exists');

    71     // Add index.

    72     db_add_index('test_table', 'test_field', array('test_field'));

    73     // Test for created index and test for the boolean result of indexExists().

    74     $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');

    75     $this->assertIdentical($index_exists, TRUE, 'Index created.');

    76 

    77     // Rename the table.

    78     db_rename_table('test_table', 'test_table2');

    79 

    80     // Index should be renamed.

    81     $index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');

    82     $this->assertTrue($index_exists, 'Index was renamed.');

    83 

    84     // We need the default so that we can insert after the rename.

    85     db_field_set_default('test_table2', 'test_field', 0);

    86     $this->assertFalse($this->tryInsert(), 'Insert into the old table failed.');

    87     $this->assertTrue($this->tryInsert('test_table2'), 'Insert into the new table succeeded.');

    88 

    89     // We should have successfully inserted exactly two rows.

    90     $count = db_query('SELECT COUNT(*) FROM {test_table2}')->fetchField();

    91     $this->assertEqual($count, 2, 'Two fields were successfully inserted.');

    92 

    93     // Try to drop the table.

    94     db_drop_table('test_table2');

    95     $this->assertFalse(db_table_exists('test_table2'), 'The dropped table does not exist.');

    96 

    97     // Recreate the table.

    98     db_create_table('test_table', $table_specification);

    99     db_field_set_default('test_table', 'test_field', 0);

   100     db_add_field('test_table', 'test_serial', array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'description' => 'Added column description.'));

   101 

   102     // Assert that the column comment has been set.

   103     $this->checkSchemaComment('Added column description.', 'test_table', 'test_serial');

   104 

   105     // Change the new field to a serial column.

   106     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')));

   107 

   108     // Assert that the column comment has been set.

   109     $this->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');

   110 

   111     $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');

   112     $max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();

   113     $this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');

   114     $max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();

   115     $this->assertTrue($max2 > $max1, 'The serial is monotone.');

   116 

   117     $count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();

   118     $this->assertEqual($count, 2, 'There were two rows.');

   119 

   120     // Use database specific data type and ensure that table is created.

   121     $table_specification = array(

   122       'description' => 'Schema table description.',

   123       'fields' => array(

   124         'timestamp'  => array(

   125           'mysql_type' => 'timestamp',

   126           'pgsql_type' => 'timestamp',

   127           'sqlite_type' => 'datetime',

   128           'not null' => FALSE,

   129           'default' => NULL,

   130         ),

   131       ),

   132     );

   133     try {

   134       db_create_table('test_timestamp', $table_specification);

   135     }

   136     catch (Exception $e) {}

   137     $this->assertTrue(db_table_exists('test_timestamp'), 'Table with database specific datatype was created.');

   138   }

   139 

   140   function tryInsert($table = 'test_table') {

   141     try {

   142        db_insert($table)

   143          ->fields(array('id' => mt_rand(10, 20)))

   144          ->execute();

   145       return TRUE;

   146     }

   147     catch (Exception $e) {

   148       return FALSE;

   149     }

   150   }

   151 

   152   /**

   153    * Checks that a table or column comment matches a given description.

   154    *

   155    * @param $description

   156    *   The asserted description.

   157    * @param $table

   158    *   The table to test.

   159    * @param $column

   160    *   Optional column to test.

   161    */

   162   function checkSchemaComment($description, $table, $column = NULL) {

   163     if (method_exists(Database::getConnection()->schema(), 'getComment')) {

   164       $comment = Database::getConnection()->schema()->getComment($table, $column);

   165       $this->assertEqual($comment, $description, 'The comment matches the schema description.');

   166     }

   167   }

   168 

   169   /**

   170    * Tests creating unsigned columns and data integrity thereof.

   171    */

   172   function testUnsignedColumns() {

   173     // First create the table with just a serial column.

   174     $table_name = 'unsigned_table';

   175     $table_spec = array(

   176       'fields' => array('serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)),

   177       'primary key' => array('serial_column'),

   178     );

   179     $ret = array();

   180     db_create_table($table_name, $table_spec);

   181 

   182     // Now set up columns for the other types.

   183     $types = array('int', 'float', 'numeric');

   184     foreach ($types as $type) {

   185       $column_spec = array('type' => $type, 'unsigned'=> TRUE);

   186       if ($type == 'numeric') {

   187         $column_spec += array('precision' => 10, 'scale' => 0);

   188       }

   189       $column_name = $type . '_column';

   190       $table_spec['fields'][$column_name] = $column_spec;

   191       db_add_field($table_name, $column_name, $column_spec);

   192     }

   193 

   194     // Finally, check each column and try to insert invalid values into them.

   195     foreach ($table_spec['fields'] as $column_name => $column_spec) {

   196       $this->assertTrue(db_field_exists($table_name, $column_name), format_string('Unsigned @type column was created.', array('@type' => $column_spec['type'])));

   197       $this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), format_string('Unsigned @type column rejected a negative value.', array('@type' => $column_spec['type'])));

   198     }

   199   }

   200 

   201   /**

   202    * Tries to insert a negative value into columns defined as unsigned.

   203    *

   204    * @param $table_name

   205    *   The table to insert

   206    * @param $column_name

   207    *   The column to insert

   208    * @return

   209    *   TRUE if the insert succeeded, FALSE otherwise

   210    */

   211   function tryUnsignedInsert($table_name, $column_name) {

   212     try {

   213       db_insert($table_name)

   214          ->fields(array($column_name => -1))

   215          ->execute();

   216       return TRUE;

   217     }

   218     catch (Exception $e) {

   219       return FALSE;

   220     }

   221   }

   222 

   223   /**

   224    * Test adding columns to an existing table.

   225    */

   226   function testSchemaAddField() {

   227     // Test varchar types.

   228     foreach (array(1, 32, 128, 256, 512) as $length) {

   229       $base_field_spec = array(

   230         'type' => 'varchar',

   231         'length' => $length,

   232       );

   233       $variations = array(

   234         array('not null' => FALSE),

   235         array('not null' => FALSE, 'default' => '7'),

   236         array('not null' => TRUE, 'initial' => 'd'),

   237         array('not null' => TRUE, 'initial' => 'd', 'default' => '7'),

   238       );

   239 

   240       foreach ($variations as $variation) {

   241         $field_spec = $variation + $base_field_spec;

   242         $this->assertFieldAdditionRemoval($field_spec);

   243       }

   244     }

   245 

   246     // Test int and float types.

   247     foreach (array('int', 'float') as $type) {

   248       foreach (array('tiny', 'small', 'medium', 'normal', 'big') as $size) {

   249         $base_field_spec = array(

   250           'type' => $type,

   251           'size' => $size,

   252         );

   253         $variations = array(

   254           array('not null' => FALSE),

   255           array('not null' => FALSE, 'default' => 7),

   256           array('not null' => TRUE, 'initial' => 1),

   257           array('not null' => TRUE, 'initial' => 1, 'default' => 7),

   258         );

   259 

   260         foreach ($variations as $variation) {

   261           $field_spec = $variation + $base_field_spec;

   262           $this->assertFieldAdditionRemoval($field_spec);

   263         }

   264       }

   265     }

   266 

   267     // Test numeric types.

   268     foreach (array(1, 5, 10, 40, 65) as $precision) {

   269       foreach (array(0, 2, 10, 30) as $scale) {

   270         if ($precision <= $scale) {

   271           // Precision must be smaller then scale.

   272           continue;

   273         }

   274 

   275         $base_field_spec = array(

   276           'type' => 'numeric',

   277           'scale' => $scale,

   278           'precision' => $precision,

   279         );

   280         $variations = array(

   281           array('not null' => FALSE),

   282           array('not null' => FALSE, 'default' => 7),

   283           array('not null' => TRUE, 'initial' => 1),

   284           array('not null' => TRUE, 'initial' => 1, 'default' => 7),

   285         );

   286 

   287         foreach ($variations as $variation) {

   288           $field_spec = $variation + $base_field_spec;

   289           $this->assertFieldAdditionRemoval($field_spec);

   290         }

   291       }

   292     }

   293   }

   294 

   295   /**

   296    * Assert that a given field can be added and removed from a table.

   297    *

   298    * The addition test covers both defining a field of a given specification

   299    * when initially creating at table and extending an existing table.

   300    *

   301    * @param $field_spec

   302    *   The schema specification of the field.

   303    */

   304   protected function assertFieldAdditionRemoval($field_spec) {

   305     // Try creating the field on a new table.

   306     $table_name = 'test_table_' . ($this->counter++);

   307     $table_spec = array(

   308       'fields' => array(

   309         'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),

   310         'test_field' => $field_spec,

   311       ),

   312       'primary key' => array('serial_column'),

   313     );

   314     db_create_table($table_name, $table_spec);

   315     $this->pass(format_string('Table %table created.', array('%table' => $table_name)));

   316 

   317     // Check the characteristics of the field.

   318     $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);

   319 

   320     // Clean-up.

   321     db_drop_table($table_name);

   322 

   323     // Try adding a field to an existing table.

   324     $table_name = 'test_table_' . ($this->counter++);

   325     $table_spec = array(

   326       'fields' => array(

   327         'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),

   328       ),

   329       'primary key' => array('serial_column'),

   330     );

   331     db_create_table($table_name, $table_spec);

   332     $this->pass(format_string('Table %table created.', array('%table' => $table_name)));

   333 

   334     // Insert some rows to the table to test the handling of initial values.

   335     for ($i = 0; $i < 3; $i++) {

   336       db_insert($table_name)

   337         ->useDefaults(array('serial_column'))

   338         ->execute();

   339     }

   340 

   341     db_add_field($table_name, 'test_field', $field_spec);

   342     $this->pass(format_string('Column %column created.', array('%column' => 'test_field')));

   343 

   344     // Check the characteristics of the field.

   345     $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);

   346 

   347     // Clean-up.

   348     db_drop_field($table_name, 'test_field');

   349     db_drop_table($table_name);

   350   }

   351 

   352   /**

   353    * Assert that a newly added field has the correct characteristics.

   354    */

   355   protected function assertFieldCharacteristics($table_name, $field_name, $field_spec) {

   356     // Check that the initial value has been registered.

   357     if (isset($field_spec['initial'])) {

   358       // There should be no row with a value different then $field_spec['initial'].

   359       $count = db_select($table_name)

   360         ->fields($table_name, array('serial_column'))

   361         ->condition($field_name, $field_spec['initial'], '<>')

   362         ->countQuery()

   363         ->execute()

   364         ->fetchField();

   365       $this->assertEqual($count, 0, 'Initial values filled out.');

   366     }

   367 

   368     // Check that the default value has been registered.

   369     if (isset($field_spec['default'])) {

   370       // Try inserting a row, and check the resulting value of the new column.

   371       $id = db_insert($table_name)

   372         ->useDefaults(array('serial_column'))

   373         ->execute();

   374       $field_value = db_select($table_name)

   375         ->fields($table_name, array($field_name))

   376         ->condition('serial_column', $id)

   377         ->execute()

   378         ->fetchField();

   379       $this->assertEqual($field_value, $field_spec['default'], 'Default value registered.');

   380     }

   381 

   382     db_drop_field($table_name, $field_name);

   383   }

   384 }