schema.test 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384
  1. <?php
  2. /**
  3. * @file
  4. * Tests for the Database Schema API.
  5. */
  6. /**
  7. * Unit tests for the Schema API.
  8. */
  9. class SchemaTestCase extends DrupalWebTestCase {
  10. /**
  11. * A global counter for table and field creation.
  12. */
  13. var $counter;
  14. public static function getInfo() {
  15. return array(
  16. 'name' => 'Schema API',
  17. 'description' => 'Tests table creation and modification via the schema API.',
  18. 'group' => 'Database',
  19. );
  20. }
  21. /**
  22. *
  23. */
  24. function testSchema() {
  25. // Try creating a table.
  26. $table_specification = array(
  27. 'description' => 'Schema table description.',
  28. 'fields' => array(
  29. 'id' => array(
  30. 'type' => 'int',
  31. 'default' => NULL,
  32. ),
  33. 'test_field' => array(
  34. 'type' => 'int',
  35. 'not null' => TRUE,
  36. 'description' => 'Schema column description.',
  37. ),
  38. ),
  39. );
  40. db_create_table('test_table', $table_specification);
  41. // Assert that the table exists.
  42. $this->assertTrue(db_table_exists('test_table'), t('The table exists.'));
  43. // Assert that the table comment has been set.
  44. $this->checkSchemaComment($table_specification['description'], 'test_table');
  45. // Assert that the column comment has been set.
  46. $this->checkSchemaComment($table_specification['fields']['test_field']['description'], 'test_table', 'test_field');
  47. // An insert without a value for the column 'test_table' should fail.
  48. $this->assertFalse($this->tryInsert(), t('Insert without a default failed.'));
  49. // Add a default value to the column.
  50. db_field_set_default('test_table', 'test_field', 0);
  51. // The insert should now succeed.
  52. $this->assertTrue($this->tryInsert(), t('Insert with a default succeeded.'));
  53. // Remove the default.
  54. db_field_set_no_default('test_table', 'test_field');
  55. // The insert should fail again.
  56. $this->assertFalse($this->tryInsert(), t('Insert without a default failed.'));
  57. // Test for fake index and test for the boolean result of indexExists().
  58. $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
  59. $this->assertIdentical($index_exists, FALSE, t('Fake index does not exists'));
  60. // Add index.
  61. db_add_index('test_table', 'test_field', array('test_field'));
  62. // Test for created index and test for the boolean result of indexExists().
  63. $index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
  64. $this->assertIdentical($index_exists, TRUE, t('Index created.'));
  65. // Rename the table.
  66. db_rename_table('test_table', 'test_table2');
  67. // Index should be renamed.
  68. $index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
  69. $this->assertTrue($index_exists, t('Index was renamed.'));
  70. // We need the default so that we can insert after the rename.
  71. db_field_set_default('test_table2', 'test_field', 0);
  72. $this->assertFalse($this->tryInsert(), t('Insert into the old table failed.'));
  73. $this->assertTrue($this->tryInsert('test_table2'), t('Insert into the new table succeeded.'));
  74. // We should have successfully inserted exactly two rows.
  75. $count = db_query('SELECT COUNT(*) FROM {test_table2}')->fetchField();
  76. $this->assertEqual($count, 2, t('Two fields were successfully inserted.'));
  77. // Try to drop the table.
  78. db_drop_table('test_table2');
  79. $this->assertFalse(db_table_exists('test_table2'), t('The dropped table does not exist.'));
  80. // Recreate the table.
  81. db_create_table('test_table', $table_specification);
  82. db_field_set_default('test_table', 'test_field', 0);
  83. db_add_field('test_table', 'test_serial', array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'description' => 'Added column description.'));
  84. // Assert that the column comment has been set.
  85. $this->checkSchemaComment('Added column description.', 'test_table', 'test_serial');
  86. // Change the new field to a serial column.
  87. 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')));
  88. // Assert that the column comment has been set.
  89. $this->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');
  90. $this->assertTrue($this->tryInsert(), t('Insert with a serial succeeded.'));
  91. $max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
  92. $this->assertTrue($this->tryInsert(), t('Insert with a serial succeeded.'));
  93. $max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
  94. $this->assertTrue($max2 > $max1, t('The serial is monotone.'));
  95. $count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();
  96. $this->assertEqual($count, 2, t('There were two rows.'));
  97. // Use database specific data type and ensure that table is created.
  98. $table_specification = array(
  99. 'description' => 'Schema table description.',
  100. 'fields' => array(
  101. 'timestamp' => array(
  102. 'mysql_type' => 'timestamp',
  103. 'pgsql_type' => 'timestamp',
  104. 'sqlite_type' => 'datetime',
  105. 'not null' => FALSE,
  106. 'default' => NULL,
  107. ),
  108. ),
  109. );
  110. try {
  111. db_create_table('test_timestamp', $table_specification);
  112. }
  113. catch (Exception $e) {}
  114. $this->assertTrue(db_table_exists('test_timestamp'), t('Table with database specific datatype was created.'));
  115. }
  116. function tryInsert($table = 'test_table') {
  117. try {
  118. db_insert($table)
  119. ->fields(array('id' => mt_rand(10, 20)))
  120. ->execute();
  121. return TRUE;
  122. }
  123. catch (Exception $e) {
  124. return FALSE;
  125. }
  126. }
  127. /**
  128. * Checks that a table or column comment matches a given description.
  129. *
  130. * @param $description
  131. * The asserted description.
  132. * @param $table
  133. * The table to test.
  134. * @param $column
  135. * Optional column to test.
  136. */
  137. function checkSchemaComment($description, $table, $column = NULL) {
  138. if (method_exists(Database::getConnection()->schema(), 'getComment')) {
  139. $comment = Database::getConnection()->schema()->getComment($table, $column);
  140. $this->assertEqual($comment, $description, t('The comment matches the schema description.'));
  141. }
  142. }
  143. /**
  144. * Tests creating unsigned columns and data integrity thereof.
  145. */
  146. function testUnsignedColumns() {
  147. // First create the table with just a serial column.
  148. $table_name = 'unsigned_table';
  149. $table_spec = array(
  150. 'fields' => array('serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)),
  151. 'primary key' => array('serial_column'),
  152. );
  153. $ret = array();
  154. db_create_table($table_name, $table_spec);
  155. // Now set up columns for the other types.
  156. $types = array('int', 'float', 'numeric');
  157. foreach ($types as $type) {
  158. $column_spec = array('type' => $type, 'unsigned'=> TRUE);
  159. if ($type == 'numeric') {
  160. $column_spec += array('precision' => 10, 'scale' => 0);
  161. }
  162. $column_name = $type . '_column';
  163. $table_spec['fields'][$column_name] = $column_spec;
  164. db_add_field($table_name, $column_name, $column_spec);
  165. }
  166. // Finally, check each column and try to insert invalid values into them.
  167. foreach ($table_spec['fields'] as $column_name => $column_spec) {
  168. $this->assertTrue(db_field_exists($table_name, $column_name), t('Unsigned @type column was created.', array('@type' => $column_spec['type'])));
  169. $this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), t('Unsigned @type column rejected a negative value.', array('@type' => $column_spec['type'])));
  170. }
  171. }
  172. /**
  173. * Tries to insert a negative value into columns defined as unsigned.
  174. *
  175. * @param $table_name
  176. * The table to insert
  177. * @param $column_name
  178. * The column to insert
  179. * @return
  180. * TRUE if the insert succeeded, FALSE otherwise
  181. */
  182. function tryUnsignedInsert($table_name, $column_name) {
  183. try {
  184. db_insert($table_name)
  185. ->fields(array($column_name => -1))
  186. ->execute();
  187. return TRUE;
  188. }
  189. catch (Exception $e) {
  190. return FALSE;
  191. }
  192. }
  193. /**
  194. * Test adding columns to an existing table.
  195. */
  196. function testSchemaAddField() {
  197. // Test varchar types.
  198. foreach (array(1, 32, 128, 256, 512) as $length) {
  199. $base_field_spec = array(
  200. 'type' => 'varchar',
  201. 'length' => $length,
  202. );
  203. $variations = array(
  204. array('not null' => FALSE),
  205. array('not null' => FALSE, 'default' => '7'),
  206. array('not null' => TRUE, 'initial' => 'd'),
  207. array('not null' => TRUE, 'initial' => 'd', 'default' => '7'),
  208. );
  209. foreach ($variations as $variation) {
  210. $field_spec = $variation + $base_field_spec;
  211. $this->assertFieldAdditionRemoval($field_spec);
  212. }
  213. }
  214. // Test int and float types.
  215. foreach (array('int', 'float') as $type) {
  216. foreach (array('tiny', 'small', 'medium', 'normal', 'big') as $size) {
  217. $base_field_spec = array(
  218. 'type' => $type,
  219. 'size' => $size,
  220. );
  221. $variations = array(
  222. array('not null' => FALSE),
  223. array('not null' => FALSE, 'default' => 7),
  224. array('not null' => TRUE, 'initial' => 1),
  225. array('not null' => TRUE, 'initial' => 1, 'default' => 7),
  226. );
  227. foreach ($variations as $variation) {
  228. $field_spec = $variation + $base_field_spec;
  229. $this->assertFieldAdditionRemoval($field_spec);
  230. }
  231. }
  232. }
  233. // Test numeric types.
  234. foreach (array(1, 5, 10, 40, 65) as $precision) {
  235. foreach (array(0, 2, 10, 30) as $scale) {
  236. if ($precision <= $scale) {
  237. // Precision must be smaller then scale.
  238. continue;
  239. }
  240. $base_field_spec = array(
  241. 'type' => 'numeric',
  242. 'scale' => $scale,
  243. 'precision' => $precision,
  244. );
  245. $variations = array(
  246. array('not null' => FALSE),
  247. array('not null' => FALSE, 'default' => 7),
  248. array('not null' => TRUE, 'initial' => 1),
  249. array('not null' => TRUE, 'initial' => 1, 'default' => 7),
  250. );
  251. foreach ($variations as $variation) {
  252. $field_spec = $variation + $base_field_spec;
  253. $this->assertFieldAdditionRemoval($field_spec);
  254. }
  255. }
  256. }
  257. }
  258. /**
  259. * Assert that a given field can be added and removed from a table.
  260. *
  261. * The addition test covers both defining a field of a given specification
  262. * when initially creating at table and extending an existing table.
  263. *
  264. * @param $field_spec
  265. * The schema specification of the field.
  266. */
  267. protected function assertFieldAdditionRemoval($field_spec) {
  268. // Try creating the field on a new table.
  269. $table_name = 'test_table_' . ($this->counter++);
  270. $table_spec = array(
  271. 'fields' => array(
  272. 'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
  273. 'test_field' => $field_spec,
  274. ),
  275. 'primary key' => array('serial_column'),
  276. );
  277. db_create_table($table_name, $table_spec);
  278. $this->pass(t('Table %table created.', array('%table' => $table_name)));
  279. // Check the characteristics of the field.
  280. $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);
  281. // Clean-up.
  282. db_drop_table($table_name);
  283. // Try adding a field to an existing table.
  284. $table_name = 'test_table_' . ($this->counter++);
  285. $table_spec = array(
  286. 'fields' => array(
  287. 'serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
  288. ),
  289. 'primary key' => array('serial_column'),
  290. );
  291. db_create_table($table_name, $table_spec);
  292. $this->pass(t('Table %table created.', array('%table' => $table_name)));
  293. // Insert some rows to the table to test the handling of initial values.
  294. for ($i = 0; $i < 3; $i++) {
  295. db_insert($table_name)
  296. ->useDefaults(array('serial_column'))
  297. ->execute();
  298. }
  299. db_add_field($table_name, 'test_field', $field_spec);
  300. $this->pass(t('Column %column created.', array('%column' => 'test_field')));
  301. // Check the characteristics of the field.
  302. $this->assertFieldCharacteristics($table_name, 'test_field', $field_spec);
  303. // Clean-up.
  304. db_drop_field($table_name, 'test_field');
  305. db_drop_table($table_name);
  306. }
  307. /**
  308. * Assert that a newly added field has the correct characteristics.
  309. */
  310. protected function assertFieldCharacteristics($table_name, $field_name, $field_spec) {
  311. // Check that the initial value has been registered.
  312. if (isset($field_spec['initial'])) {
  313. // There should be no row with a value different then $field_spec['initial'].
  314. $count = db_select($table_name)
  315. ->fields($table_name, array('serial_column'))
  316. ->condition($field_name, $field_spec['initial'], '<>')
  317. ->countQuery()
  318. ->execute()
  319. ->fetchField();
  320. $this->assertEqual($count, 0, t('Initial values filled out.'));
  321. }
  322. // Check that the default value has been registered.
  323. if (isset($field_spec['default'])) {
  324. // Try inserting a row, and check the resulting value of the new column.
  325. $id = db_insert($table_name)
  326. ->useDefaults(array('serial_column'))
  327. ->execute();
  328. $field_value = db_select($table_name)
  329. ->fields($table_name, array($field_name))
  330. ->condition('serial_column', $id)
  331. ->execute()
  332. ->fetchField();
  333. $this->assertEqual($field_value, $field_spec['default'], t('Default value registered.'));
  334. }
  335. db_drop_field($table_name, $field_name);
  336. }
  337. }