database_test.test

File

drupal/modules/simpletest/tests/database_test.test
View source
  1. <?php
  2. /**
  3. * Dummy class for fetching into a class.
  4. *
  5. * PDO supports using a new instance of an arbitrary class for records
  6. * rather than just a stdClass or array. This class is for testing that
  7. * functionality. (See testQueryFetchClass() below)
  8. */
  9. class FakeRecord { }
  10. /**
  11. * Base test class for databases.
  12. *
  13. * Because all database tests share the same test data, we can centralize that
  14. * here.
  15. */
  16. class DatabaseTestCase extends DrupalWebTestCase {
  17. protected $profile = 'testing';
  18. function setUp() {
  19. parent::setUp('database_test');
  20. $schema['test'] = drupal_get_schema('test');
  21. $schema['test_people'] = drupal_get_schema('test_people');
  22. $schema['test_one_blob'] = drupal_get_schema('test_one_blob');
  23. $schema['test_two_blobs'] = drupal_get_schema('test_two_blobs');
  24. $schema['test_task'] = drupal_get_schema('test_task');
  25. $this->installTables($schema);
  26. $this->addSampleData();
  27. }
  28. /**
  29. * Set up several tables needed by a certain test.
  30. *
  31. * @param $schema
  32. * An array of table definitions to install.
  33. */
  34. function installTables($schema) {
  35. // This ends up being a test for table drop and create, too, which is nice.
  36. foreach ($schema as $name => $data) {
  37. if (db_table_exists($name)) {
  38. db_drop_table($name);
  39. }
  40. db_create_table($name, $data);
  41. }
  42. foreach ($schema as $name => $data) {
  43. $this->assertTrue(db_table_exists($name), format_string('Table @name created successfully.', array('@name' => $name)));
  44. }
  45. }
  46. /**
  47. * Set up tables for NULL handling.
  48. */
  49. function ensureSampleDataNull() {
  50. $schema['test_null'] = drupal_get_schema('test_null');
  51. $this->installTables($schema);
  52. db_insert('test_null')
  53. ->fields(array('name', 'age'))
  54. ->values(array(
  55. 'name' => 'Kermit',
  56. 'age' => 25,
  57. ))
  58. ->values(array(
  59. 'name' => 'Fozzie',
  60. 'age' => NULL,
  61. ))
  62. ->values(array(
  63. 'name' => 'Gonzo',
  64. 'age' => 27,
  65. ))
  66. ->execute();
  67. }
  68. /**
  69. * Setup our sample data.
  70. *
  71. * These are added using db_query(), since we're not trying to test the
  72. * INSERT operations here, just populate.
  73. */
  74. function addSampleData() {
  75. // We need the IDs, so we can't use a multi-insert here.
  76. $john = db_insert('test')
  77. ->fields(array(
  78. 'name' => 'John',
  79. 'age' => 25,
  80. 'job' => 'Singer',
  81. ))
  82. ->execute();
  83. $george = db_insert('test')
  84. ->fields(array(
  85. 'name' => 'George',
  86. 'age' => 27,
  87. 'job' => 'Singer',
  88. ))
  89. ->execute();
  90. $ringo = db_insert('test')
  91. ->fields(array(
  92. 'name' => 'Ringo',
  93. 'age' => 28,
  94. 'job' => 'Drummer',
  95. ))
  96. ->execute();
  97. $paul = db_insert('test')
  98. ->fields(array(
  99. 'name' => 'Paul',
  100. 'age' => 26,
  101. 'job' => 'Songwriter',
  102. ))
  103. ->execute();
  104. db_insert('test_people')
  105. ->fields(array(
  106. 'name' => 'Meredith',
  107. 'age' => 30,
  108. 'job' => 'Speaker',
  109. ))
  110. ->execute();
  111. db_insert('test_task')
  112. ->fields(array('pid', 'task', 'priority'))
  113. ->values(array(
  114. 'pid' => $john,
  115. 'task' => 'eat',
  116. 'priority' => 3,
  117. ))
  118. ->values(array(
  119. 'pid' => $john,
  120. 'task' => 'sleep',
  121. 'priority' => 4,
  122. ))
  123. ->values(array(
  124. 'pid' => $john,
  125. 'task' => 'code',
  126. 'priority' => 1,
  127. ))
  128. ->values(array(
  129. 'pid' => $george,
  130. 'task' => 'sing',
  131. 'priority' => 2,
  132. ))
  133. ->values(array(
  134. 'pid' => $george,
  135. 'task' => 'sleep',
  136. 'priority' => 2,
  137. ))
  138. ->values(array(
  139. 'pid' => $paul,
  140. 'task' => 'found new band',
  141. 'priority' => 1,
  142. ))
  143. ->values(array(
  144. 'pid' => $paul,
  145. 'task' => 'perform at superbowl',
  146. 'priority' => 3,
  147. ))
  148. ->execute();
  149. }
  150. }
  151. /**
  152. * Test connection management.
  153. */
  154. class DatabaseConnectionTestCase extends DatabaseTestCase {
  155. public static function getInfo() {
  156. return array(
  157. 'name' => 'Connection tests',
  158. 'description' => 'Tests of the core database system.',
  159. 'group' => 'Database',
  160. );
  161. }
  162. /**
  163. * Test that connections return appropriate connection objects.
  164. */
  165. function testConnectionRouting() {
  166. // Clone the master credentials to a slave connection.
  167. // Note this will result in two independent connection objects that happen
  168. // to point to the same place.
  169. $connection_info = Database::getConnectionInfo('default');
  170. Database::addConnectionInfo('default', 'slave', $connection_info['default']);
  171. $db1 = Database::getConnection('default', 'default');
  172. $db2 = Database::getConnection('slave', 'default');
  173. $this->assertNotNull($db1, 'default connection is a real connection object.');
  174. $this->assertNotNull($db2, 'slave connection is a real connection object.');
  175. $this->assertNotIdentical($db1, $db2, 'Each target refers to a different connection.');
  176. // Try to open those targets another time, that should return the same objects.
  177. $db1b = Database::getConnection('default', 'default');
  178. $db2b = Database::getConnection('slave', 'default');
  179. $this->assertIdentical($db1, $db1b, 'A second call to getConnection() returns the same object.');
  180. $this->assertIdentical($db2, $db2b, 'A second call to getConnection() returns the same object.');
  181. // Try to open an unknown target.
  182. $unknown_target = $this->randomName();
  183. $db3 = Database::getConnection($unknown_target, 'default');
  184. $this->assertNotNull($db3, 'Opening an unknown target returns a real connection object.');
  185. $this->assertIdentical($db1, $db3, 'An unknown target opens the default connection.');
  186. // Try to open that unknown target another time, that should return the same object.
  187. $db3b = Database::getConnection($unknown_target, 'default');
  188. $this->assertIdentical($db3, $db3b, 'A second call to getConnection() returns the same object.');
  189. }
  190. /**
  191. * Test that connections return appropriate connection objects.
  192. */
  193. function testConnectionRoutingOverride() {
  194. // Clone the master credentials to a slave connection.
  195. // Note this will result in two independent connection objects that happen
  196. // to point to the same place.
  197. $connection_info = Database::getConnectionInfo('default');
  198. Database::addConnectionInfo('default', 'slave', $connection_info['default']);
  199. Database::ignoreTarget('default', 'slave');
  200. $db1 = Database::getConnection('default', 'default');
  201. $db2 = Database::getConnection('slave', 'default');
  202. $this->assertIdentical($db1, $db2, 'Both targets refer to the same connection.');
  203. }
  204. /**
  205. * Tests the closing of a database connection.
  206. */
  207. function testConnectionClosing() {
  208. // Open the default target so we have an object to compare.
  209. $db1 = Database::getConnection('default', 'default');
  210. // Try to close the the default connection, then open a new one.
  211. Database::closeConnection('default', 'default');
  212. $db2 = Database::getConnection('default', 'default');
  213. // Opening a connection after closing it should yield an object different than the original.
  214. $this->assertNotIdentical($db1, $db2, 'Opening the default connection after it is closed returns a new object.');
  215. }
  216. /**
  217. * Tests the connection options of the active database.
  218. */
  219. function testConnectionOptions() {
  220. $connection_info = Database::getConnectionInfo('default');
  221. // Be sure we're connected to the default database.
  222. $db = Database::getConnection('default', 'default');
  223. $connectionOptions = $db->getConnectionOptions();
  224. // In the MySQL driver, the port can be different, so check individual
  225. // options.
  226. $this->assertEqual($connection_info['default']['driver'], $connectionOptions['driver'], 'The default connection info driver matches the current connection options driver.');
  227. $this->assertEqual($connection_info['default']['database'], $connectionOptions['database'], 'The default connection info database matches the current connection options database.');
  228. // Set up identical slave and confirm connection options are identical.
  229. Database::addConnectionInfo('default', 'slave', $connection_info['default']);
  230. $db2 = Database::getConnection('slave', 'default');
  231. $connectionOptions2 = $db2->getConnectionOptions();
  232. // Get a fresh copy of the default connection options.
  233. $connectionOptions = $db->getConnectionOptions();
  234. $this->assertIdentical($connectionOptions, $connectionOptions2, 'The default and slave connection options are identical.');
  235. // Set up a new connection with different connection info.
  236. $test = $connection_info['default'];
  237. $test['database'] .= 'test';
  238. Database::addConnectionInfo('test', 'default', $test);
  239. $connection_info = Database::getConnectionInfo('test');
  240. // Get a fresh copy of the default connection options.
  241. $connectionOptions = $db->getConnectionOptions();
  242. $this->assertNotEqual($connection_info['default']['database'], $connectionOptions['database'], 'The test connection info database does not match the current connection options database.');
  243. }
  244. }
  245. /**
  246. * Test cloning Select queries.
  247. */
  248. class DatabaseSelectCloneTest extends DatabaseTestCase {
  249. public static function getInfo() {
  250. return array(
  251. 'name' => 'Select tests, cloning',
  252. 'description' => 'Test cloning Select queries.',
  253. 'group' => 'Database',
  254. );
  255. }
  256. /**
  257. * Test that subqueries as value within conditions are cloned properly.
  258. */
  259. function testSelectConditionSubQueryCloning() {
  260. $subquery = db_select('test', 't');
  261. $subquery->addField('t', 'id', 'id');
  262. $subquery->condition('age', 28, '<');
  263. $query = db_select('test', 't');
  264. $query->addField('t', 'name', 'name');
  265. $query->condition('id', $subquery, 'IN');
  266. $clone = clone $query;
  267. // Cloned query should not be altered by the following modification
  268. // happening on original query.
  269. $subquery->condition('age', 25, '>');
  270. $clone_result = $clone->countQuery()->execute()->fetchField();
  271. $query_result = $query->countQuery()->execute()->fetchField();
  272. // Make sure the cloned query has not been modified
  273. $this->assertEqual(3, $clone_result, 'The cloned query returns the expected number of rows');
  274. $this->assertEqual(2, $query_result, 'The query returns the expected number of rows');
  275. }
  276. }
  277. /**
  278. * Test fetch actions, part 1.
  279. *
  280. * We get timeout errors if we try to run too many tests at once.
  281. */
  282. class DatabaseFetchTestCase extends DatabaseTestCase {
  283. public static function getInfo() {
  284. return array(
  285. 'name' => 'Fetch tests',
  286. 'description' => 'Test the Database system\'s various fetch capabilities.',
  287. 'group' => 'Database',
  288. );
  289. }
  290. /**
  291. * Confirm that we can fetch a record properly in default object mode.
  292. */
  293. function testQueryFetchDefault() {
  294. $records = array();
  295. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25));
  296. $this->assertTrue($result instanceof DatabaseStatementInterface, 'Result set is a Drupal statement object.');
  297. foreach ($result as $record) {
  298. $records[] = $record;
  299. $this->assertTrue(is_object($record), 'Record is an object.');
  300. $this->assertIdentical($record->name, 'John', '25 year old is John.');
  301. }
  302. $this->assertIdentical(count($records), 1, 'There is only one record.');
  303. }
  304. /**
  305. * Confirm that we can fetch a record to an object explicitly.
  306. */
  307. function testQueryFetchObject() {
  308. $records = array();
  309. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_OBJ));
  310. foreach ($result as $record) {
  311. $records[] = $record;
  312. $this->assertTrue(is_object($record), 'Record is an object.');
  313. $this->assertIdentical($record->name, 'John', '25 year old is John.');
  314. }
  315. $this->assertIdentical(count($records), 1, 'There is only one record.');
  316. }
  317. /**
  318. * Confirm that we can fetch a record to an array associative explicitly.
  319. */
  320. function testQueryFetchArray() {
  321. $records = array();
  322. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_ASSOC));
  323. foreach ($result as $record) {
  324. $records[] = $record;
  325. if ($this->assertTrue(is_array($record), 'Record is an array.')) {
  326. $this->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
  327. }
  328. }
  329. $this->assertIdentical(count($records), 1, 'There is only one record.');
  330. }
  331. /**
  332. * Confirm that we can fetch a record into a new instance of a custom class.
  333. *
  334. * @see FakeRecord
  335. */
  336. function testQueryFetchClass() {
  337. $records = array();
  338. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => 'FakeRecord'));
  339. foreach ($result as $record) {
  340. $records[] = $record;
  341. if ($this->assertTrue($record instanceof FakeRecord, 'Record is an object of class FakeRecord.')) {
  342. $this->assertIdentical($record->name, 'John', '25 year old is John.');
  343. }
  344. }
  345. $this->assertIdentical(count($records), 1, 'There is only one record.');
  346. }
  347. }
  348. /**
  349. * Test fetch actions, part 2.
  350. *
  351. * We get timeout errors if we try to run too many tests at once.
  352. */
  353. class DatabaseFetch2TestCase extends DatabaseTestCase {
  354. public static function getInfo() {
  355. return array(
  356. 'name' => 'Fetch tests, part 2',
  357. 'description' => 'Test the Database system\'s various fetch capabilities.',
  358. 'group' => 'Database',
  359. );
  360. }
  361. function setUp() {
  362. parent::setUp();
  363. }
  364. // Confirm that we can fetch a record into an indexed array explicitly.
  365. function testQueryFetchNum() {
  366. $records = array();
  367. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_NUM));
  368. foreach ($result as $record) {
  369. $records[] = $record;
  370. if ($this->assertTrue(is_array($record), 'Record is an array.')) {
  371. $this->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
  372. }
  373. }
  374. $this->assertIdentical(count($records), 1, 'There is only one record');
  375. }
  376. /**
  377. * Confirm that we can fetch a record into a doubly-keyed array explicitly.
  378. */
  379. function testQueryFetchBoth() {
  380. $records = array();
  381. $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_BOTH));
  382. foreach ($result as $record) {
  383. $records[] = $record;
  384. if ($this->assertTrue(is_array($record), 'Record is an array.')) {
  385. $this->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
  386. $this->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
  387. }
  388. }
  389. $this->assertIdentical(count($records), 1, 'There is only one record.');
  390. }
  391. /**
  392. * Confirm that we can fetch an entire column of a result set at once.
  393. */
  394. function testQueryFetchCol() {
  395. $records = array();
  396. $result = db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25));
  397. $column = $result->fetchCol();
  398. $this->assertIdentical(count($column), 3, 'fetchCol() returns the right number of records.');
  399. $result = db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25));
  400. $i = 0;
  401. foreach ($result as $record) {
  402. $this->assertIdentical($record->name, $column[$i++], 'Column matches direct accesss.');
  403. }
  404. }
  405. }
  406. /**
  407. * Test the insert builder.
  408. */
  409. class DatabaseInsertTestCase extends DatabaseTestCase {
  410. public static function getInfo() {
  411. return array(
  412. 'name' => 'Insert tests',
  413. 'description' => 'Test the Insert query builder.',
  414. 'group' => 'Database',
  415. );
  416. }
  417. /**
  418. * Test the very basic insert functionality.
  419. */
  420. function testSimpleInsert() {
  421. $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  422. $query = db_insert('test');
  423. $query->fields(array(
  424. 'name' => 'Yoko',
  425. 'age' => '29',
  426. ));
  427. $query->execute();
  428. $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  429. $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
  430. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Yoko'))->fetchField();
  431. $this->assertIdentical($saved_age, '29', 'Can retrieve after inserting.');
  432. }
  433. /**
  434. * Test that we can insert multiple records in one query object.
  435. */
  436. function testMultiInsert() {
  437. $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  438. $query = db_insert('test');
  439. $query->fields(array(
  440. 'name' => 'Larry',
  441. 'age' => '30',
  442. ));
  443. // We should be able to specify values in any order if named.
  444. $query->values(array(
  445. 'age' => '31',
  446. 'name' => 'Curly',
  447. ));
  448. // We should be able to say "use the field order".
  449. // This is not the recommended mechanism for most cases, but it should work.
  450. $query->values(array('Moe', '32'));
  451. $query->execute();
  452. $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  453. $this->assertIdentical($num_records_before + 3, $num_records_after, 'Record inserts correctly.');
  454. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
  455. $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  456. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
  457. $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
  458. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
  459. $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  460. }
  461. /**
  462. * Test that an insert object can be reused with new data after it executes.
  463. */
  464. function testRepeatedInsert() {
  465. $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  466. $query = db_insert('test');
  467. $query->fields(array(
  468. 'name' => 'Larry',
  469. 'age' => '30',
  470. ));
  471. $query->execute(); // This should run the insert, but leave the fields intact.
  472. // We should be able to specify values in any order if named.
  473. $query->values(array(
  474. 'age' => '31',
  475. 'name' => 'Curly',
  476. ));
  477. $query->execute();
  478. // We should be able to say "use the field order".
  479. $query->values(array('Moe', '32'));
  480. $query->execute();
  481. $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  482. $this->assertIdentical((int) $num_records_before + 3, (int) $num_records_after, 'Record inserts correctly.');
  483. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
  484. $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  485. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
  486. $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
  487. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
  488. $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  489. }
  490. /**
  491. * Test that we can specify fields without values and specify values later.
  492. */
  493. function testInsertFieldOnlyDefinintion() {
  494. // This is useful for importers, when we want to create a query and define
  495. // its fields once, then loop over a multi-insert execution.
  496. db_insert('test')
  497. ->fields(array('name', 'age'))
  498. ->values(array('Larry', '30'))
  499. ->values(array('Curly', '31'))
  500. ->values(array('Moe', '32'))
  501. ->execute();
  502. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
  503. $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  504. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
  505. $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
  506. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
  507. $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  508. }
  509. /**
  510. * Test that inserts return the proper auto-increment ID.
  511. */
  512. function testInsertLastInsertID() {
  513. $id = db_insert('test')
  514. ->fields(array(
  515. 'name' => 'Larry',
  516. 'age' => '30',
  517. ))
  518. ->execute();
  519. $this->assertIdentical($id, '5', 'Auto-increment ID returned successfully.');
  520. }
  521. /**
  522. * Test that the INSERT INTO ... SELECT ... syntax works.
  523. */
  524. function testInsertSelect() {
  525. $query = db_select('test_people', 'tp');
  526. // The query builder will always append expressions after fields.
  527. // Add the expression first to test that the insert fields are correctly
  528. // re-ordered.
  529. $query->addExpression('tp.age', 'age');
  530. $query
  531. ->fields('tp', array('name','job'))
  532. ->condition('tp.name', 'Meredith');
  533. // The resulting query should be equivalent to:
  534. // INSERT INTO test (age, name, job)
  535. // SELECT tp.age AS age, tp.name AS name, tp.job AS job
  536. // FROM test_people tp
  537. // WHERE tp.name = 'Meredith'
  538. db_insert('test')
  539. ->from($query)
  540. ->execute();
  541. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Meredith'))->fetchField();
  542. $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  543. }
  544. }
  545. /**
  546. * Insert tests using LOB fields, which are weird on some databases.
  547. */
  548. class DatabaseInsertLOBTestCase extends DatabaseTestCase {
  549. public static function getInfo() {
  550. return array(
  551. 'name' => 'Insert tests, LOB fields',
  552. 'description' => 'Test the Insert query builder with LOB fields.',
  553. 'group' => 'Database',
  554. );
  555. }
  556. /**
  557. * Test that we can insert a single blob field successfully.
  558. */
  559. function testInsertOneBlob() {
  560. $data = "This is\000a test.";
  561. $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
  562. $id = db_insert('test_one_blob')
  563. ->fields(array('blob1' => $data))
  564. ->execute();
  565. $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
  566. $this->assertTrue($r['blob1'] === $data, format_string('Can insert a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
  567. }
  568. /**
  569. * Test that we can insert multiple blob fields in the same query.
  570. */
  571. function testInsertMultipleBlob() {
  572. $id = db_insert('test_two_blobs')
  573. ->fields(array(
  574. 'blob1' => 'This is',
  575. 'blob2' => 'a test',
  576. ))
  577. ->execute();
  578. $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
  579. $this->assertTrue($r['blob1'] === 'This is' && $r['blob2'] === 'a test', 'Can insert multiple blobs per row.');
  580. }
  581. }
  582. /**
  583. * Insert tests for "database default" values.
  584. */
  585. class DatabaseInsertDefaultsTestCase extends DatabaseTestCase {
  586. public static function getInfo() {
  587. return array(
  588. 'name' => 'Insert tests, default fields',
  589. 'description' => 'Test the Insert query builder with default values.',
  590. 'group' => 'Database',
  591. );
  592. }
  593. /**
  594. * Test that we can run a query that is "default values for everything".
  595. */
  596. function testDefaultInsert() {
  597. $query = db_insert('test')->useDefaults(array('job'));
  598. $id = $query->execute();
  599. $schema = drupal_get_schema('test');
  600. $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
  601. $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  602. }
  603. /**
  604. * Test that no action will be preformed if no fields are specified.
  605. */
  606. function testDefaultEmptyInsert() {
  607. $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  608. try {
  609. $result = db_insert('test')->execute();
  610. // This is only executed if no exception has been thrown.
  611. $this->fail('Expected exception NoFieldsException has not been thrown.');
  612. } catch (NoFieldsException $e) {
  613. $this->pass('Expected exception NoFieldsException has been thrown.');
  614. }
  615. $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  616. $this->assertIdentical($num_records_before, $num_records_after, 'Do nothing as no fields are specified.');
  617. }
  618. /**
  619. * Test that we can insert fields with values and defaults in the same query.
  620. */
  621. function testDefaultInsertWithFields() {
  622. $query = db_insert('test')
  623. ->fields(array('name' => 'Bob'))
  624. ->useDefaults(array('job'));
  625. $id = $query->execute();
  626. $schema = drupal_get_schema('test');
  627. $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
  628. $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  629. }
  630. }
  631. /**
  632. * Update builder tests.
  633. */
  634. class DatabaseUpdateTestCase extends DatabaseTestCase {
  635. public static function getInfo() {
  636. return array(
  637. 'name' => 'Update tests',
  638. 'description' => 'Test the Update query builder.',
  639. 'group' => 'Database',
  640. );
  641. }
  642. /**
  643. * Confirm that we can update a single record successfully.
  644. */
  645. function testSimpleUpdate() {
  646. $num_updated = db_update('test')
  647. ->fields(array('name' => 'Tiffany'))
  648. ->condition('id', 1)
  649. ->execute();
  650. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  651. $saved_name = db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 1))->fetchField();
  652. $this->assertIdentical($saved_name, 'Tiffany', 'Updated name successfully.');
  653. }
  654. /**
  655. * Confirm updating to NULL.
  656. */
  657. function testSimpleNullUpdate() {
  658. $this->ensureSampleDataNull();
  659. $num_updated = db_update('test_null')
  660. ->fields(array('age' => NULL))
  661. ->condition('name', 'Kermit')
  662. ->execute();
  663. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  664. $saved_age = db_query('SELECT age FROM {test_null} WHERE name = :name', array(':name' => 'Kermit'))->fetchField();
  665. $this->assertNull($saved_age, 'Updated name successfully.');
  666. }
  667. /**
  668. * Confirm that we can update a multiple records successfully.
  669. */
  670. function testMultiUpdate() {
  671. $num_updated = db_update('test')
  672. ->fields(array('job' => 'Musician'))
  673. ->condition('job', 'Singer')
  674. ->execute();
  675. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  676. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  677. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  678. }
  679. /**
  680. * Confirm that we can update a multiple records with a non-equality condition.
  681. */
  682. function testMultiGTUpdate() {
  683. $num_updated = db_update('test')
  684. ->fields(array('job' => 'Musician'))
  685. ->condition('age', 26, '>')
  686. ->execute();
  687. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  688. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  689. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  690. }
  691. /**
  692. * Confirm that we can update a multiple records with a where call.
  693. */
  694. function testWhereUpdate() {
  695. $num_updated = db_update('test')
  696. ->fields(array('job' => 'Musician'))
  697. ->where('age > :age', array(':age' => 26))
  698. ->execute();
  699. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  700. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  701. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  702. }
  703. /**
  704. * Confirm that we can stack condition and where calls.
  705. */
  706. function testWhereAndConditionUpdate() {
  707. $update = db_update('test')
  708. ->fields(array('job' => 'Musician'))
  709. ->where('age > :age', array(':age' => 26))
  710. ->condition('name', 'Ringo');
  711. $num_updated = $update->execute();
  712. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  713. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  714. $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  715. }
  716. /**
  717. * Test updating with expressions.
  718. */
  719. function testExpressionUpdate() {
  720. // Set age = 1 for a single row for this test to work.
  721. db_update('test')
  722. ->condition('id', 1)
  723. ->fields(array('age' => 1))
  724. ->execute();
  725. // Ensure that expressions are handled properly. This should set every
  726. // record's age to a square of itself, which will change only three of the
  727. // four records in the table since 1*1 = 1. That means only three records
  728. // are modified, so we should get back 3, not 4, from execute().
  729. $num_rows = db_update('test')
  730. ->expression('age', 'age * age')
  731. ->execute();
  732. $this->assertIdentical($num_rows, 3, 'Number of affected rows are returned.');
  733. }
  734. /**
  735. * Confirm that we can update the primary key of a record successfully.
  736. */
  737. function testPrimaryKeyUpdate() {
  738. $num_updated = db_update('test')
  739. ->fields(array('id' => 42, 'name' => 'John'))
  740. ->condition('id', 1)
  741. ->execute();
  742. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  743. $saved_name= db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 42))->fetchField();
  744. $this->assertIdentical($saved_name, 'John', 'Updated primary key successfully.');
  745. }
  746. }
  747. /**
  748. * Tests for more complex update statements.
  749. */
  750. class DatabaseUpdateComplexTestCase extends DatabaseTestCase {
  751. public static function getInfo() {
  752. return array(
  753. 'name' => 'Update tests, Complex',
  754. 'description' => 'Test the Update query builder, complex queries.',
  755. 'group' => 'Database',
  756. );
  757. }
  758. /**
  759. * Test updates with OR conditionals.
  760. */
  761. function testOrConditionUpdate() {
  762. $update = db_update('test')
  763. ->fields(array('job' => 'Musician'))
  764. ->condition(db_or()
  765. ->condition('name', 'John')
  766. ->condition('name', 'Paul')
  767. );
  768. $num_updated = $update->execute();
  769. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  770. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  771. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  772. }
  773. /**
  774. * Test WHERE IN clauses.
  775. */
  776. function testInConditionUpdate() {
  777. $num_updated = db_update('test')
  778. ->fields(array('job' => 'Musician'))
  779. ->condition('name', array('John', 'Paul'), 'IN')
  780. ->execute();
  781. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  782. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  783. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  784. }
  785. /**
  786. * Test WHERE NOT IN clauses.
  787. */
  788. function testNotInConditionUpdate() {
  789. // The o is lowercase in the 'NoT IN' operator, to make sure the operators
  790. // work in mixed case.
  791. $num_updated = db_update('test')
  792. ->fields(array('job' => 'Musician'))
  793. ->condition('name', array('John', 'Paul', 'George'), 'NoT IN')
  794. ->execute();
  795. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  796. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  797. $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  798. }
  799. /**
  800. * Test BETWEEN conditional clauses.
  801. */
  802. function testBetweenConditionUpdate() {
  803. $num_updated = db_update('test')
  804. ->fields(array('job' => 'Musician'))
  805. ->condition('age', array(25, 26), 'BETWEEN')
  806. ->execute();
  807. $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
  808. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  809. $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  810. }
  811. /**
  812. * Test LIKE conditionals.
  813. */
  814. function testLikeConditionUpdate() {
  815. $num_updated = db_update('test')
  816. ->fields(array('job' => 'Musician'))
  817. ->condition('name', '%ge%', 'LIKE')
  818. ->execute();
  819. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  820. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  821. $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  822. }
  823. /**
  824. * Test update with expression values.
  825. */
  826. function testUpdateExpression() {
  827. $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
  828. $GLOBALS['larry_test'] = 1;
  829. $num_updated = db_update('test')
  830. ->condition('name', 'Ringo')
  831. ->fields(array('job' => 'Musician'))
  832. ->expression('age', 'age + :age', array(':age' => 4))
  833. ->execute();
  834. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  835. $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
  836. $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  837. $person = db_query('SELECT * FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetch();
  838. $this->assertEqual($person->name, 'Ringo', 'Name set correctly.');
  839. $this->assertEqual($person->age, $before_age + 4, 'Age set correctly.');
  840. $this->assertEqual($person->job, 'Musician', 'Job set correctly.');
  841. $GLOBALS['larry_test'] = 0;
  842. }
  843. /**
  844. * Test update with only expression values.
  845. */
  846. function testUpdateOnlyExpression() {
  847. $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
  848. $num_updated = db_update('test')
  849. ->condition('name', 'Ringo')
  850. ->expression('age', 'age + :age', array(':age' => 4))
  851. ->execute();
  852. $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
  853. $after_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
  854. $this->assertEqual($before_age + 4, $after_age, 'Age updated correctly');
  855. }
  856. }
  857. /**
  858. * Test update queries involving LOB values.
  859. */
  860. class DatabaseUpdateLOBTestCase extends DatabaseTestCase {
  861. public static function getInfo() {
  862. return array(
  863. 'name' => 'Update tests, LOB',
  864. 'description' => 'Test the Update query builder with LOB fields.',
  865. 'group' => 'Database',
  866. );
  867. }
  868. /**
  869. * Confirm that we can update a blob column.
  870. */
  871. function testUpdateOneBlob() {
  872. $data = "This is\000a test.";
  873. $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
  874. $id = db_insert('test_one_blob')
  875. ->fields(array('blob1' => $data))
  876. ->execute();
  877. $data .= $data;
  878. db_update('test_one_blob')
  879. ->condition('id', $id)
  880. ->fields(array('blob1' => $data))
  881. ->execute();
  882. $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
  883. $this->assertTrue($r['blob1'] === $data, format_string('Can update a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
  884. }
  885. /**
  886. * Confirm that we can update two blob columns in the same table.
  887. */
  888. function testUpdateMultipleBlob() {
  889. $id = db_insert('test_two_blobs')
  890. ->fields(array(
  891. 'blob1' => 'This is',
  892. 'blob2' => 'a test',
  893. ))
  894. ->execute();
  895. db_update('test_two_blobs')
  896. ->condition('id', $id)
  897. ->fields(array('blob1' => 'and so', 'blob2' => 'is this'))
  898. ->execute();
  899. $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
  900. $this->assertTrue($r['blob1'] === 'and so' && $r['blob2'] === 'is this', 'Can update multiple blobs per row.');
  901. }
  902. }
  903. /**
  904. * Delete/Truncate tests.
  905. *
  906. * The DELETE tests are not as extensive, as all of the interesting code for
  907. * DELETE queries is in the conditional which is identical to the UPDATE and
  908. * SELECT conditional handling.
  909. *
  910. * The TRUNCATE tests are not extensive either, because the behavior of
  911. * TRUNCATE queries is not consistent across database engines. We only test
  912. * that a TRUNCATE query actually deletes all rows from the target table.
  913. */
  914. class DatabaseDeleteTruncateTestCase extends DatabaseTestCase {
  915. public static function getInfo() {
  916. return array(
  917. 'name' => 'Delete/Truncate tests',
  918. 'description' => 'Test the Delete and Truncate query builders.',
  919. 'group' => 'Database',
  920. );
  921. }
  922. /**
  923. * Confirm that we can use a subselect in a delete successfully.
  924. */
  925. function testSubselectDelete() {
  926. $num_records_before = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
  927. $pid_to_delete = db_query("SELECT * FROM {test_task} WHERE task = 'sleep'")->fetchField();
  928. $subquery = db_select('test', 't')
  929. ->fields('t', array('id'))
  930. ->condition('t.id', array($pid_to_delete), 'IN');
  931. $delete = db_delete('test_task')
  932. ->condition('task', 'sleep')
  933. ->condition('pid', $subquery, 'IN');
  934. $num_deleted = $delete->execute();
  935. $this->assertEqual($num_deleted, 1, "Deleted 1 record.");
  936. $num_records_after = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
  937. $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  938. }
  939. /**
  940. * Confirm that we can delete a single record successfully.
  941. */
  942. function testSimpleDelete() {
  943. $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  944. $num_deleted = db_delete('test')
  945. ->condition('id', 1)
  946. ->execute();
  947. $this->assertIdentical($num_deleted, 1, 'Deleted 1 record.');
  948. $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  949. $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  950. }
  951. /**
  952. * Confirm that we can truncate a whole table successfully.
  953. */
  954. function testTruncate() {
  955. $num_records_before = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
  956. db_truncate('test')->execute();
  957. $num_records_after = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
  958. $this->assertEqual(0, $num_records_after, 'Truncate really deletes everything.');
  959. }
  960. }
  961. /**
  962. * Test the MERGE query builder.
  963. */
  964. class DatabaseMergeTestCase extends DatabaseTestCase {
  965. public static function getInfo() {
  966. return array(
  967. 'name' => 'Merge tests',
  968. 'description' => 'Test the Merge query builder.',
  969. 'group' => 'Database',
  970. );
  971. }
  972. /**
  973. * Confirm that we can merge-insert a record successfully.
  974. */
  975. function testMergeInsert() {
  976. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  977. $result = db_merge('test_people')
  978. ->key(array('job' => 'Presenter'))
  979. ->fields(array(
  980. 'age' => 31,
  981. 'name' => 'Tiffany',
  982. ))
  983. ->execute();
  984. $this->assertEqual($result, MergeQuery::STATUS_INSERT, 'Insert status returned.');
  985. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  986. $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
  987. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
  988. $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
  989. $this->assertEqual($person->age, 31, 'Age set correctly.');
  990. $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
  991. }
  992. /**
  993. * Confirm that we can merge-update a record successfully.
  994. */
  995. function testMergeUpdate() {
  996. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  997. $result = db_merge('test_people')
  998. ->key(array('job' => 'Speaker'))
  999. ->fields(array(
  1000. 'age' => 31,
  1001. 'name' => 'Tiffany',
  1002. ))
  1003. ->execute();
  1004. $this->assertEqual($result, MergeQuery::STATUS_UPDATE, 'Update status returned.');
  1005. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1006. $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
  1007. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1008. $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
  1009. $this->assertEqual($person->age, 31, 'Age set correctly.');
  1010. $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  1011. }
  1012. /**
  1013. * Confirm that we can merge-update a record successfully, with different insert and update.
  1014. */
  1015. function testMergeUpdateExcept() {
  1016. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1017. db_merge('test_people')
  1018. ->key(array('job' => 'Speaker'))
  1019. ->insertFields(array('age' => 31))
  1020. ->updateFields(array('name' => 'Tiffany'))
  1021. ->execute();
  1022. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1023. $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
  1024. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1025. $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
  1026. $this->assertEqual($person->age, 30, 'Age skipped correctly.');
  1027. $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  1028. }
  1029. /**
  1030. * Confirm that we can merge-update a record successfully, with alternate replacement.
  1031. */
  1032. function testMergeUpdateExplicit() {
  1033. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1034. db_merge('test_people')
  1035. ->key(array('job' => 'Speaker'))
  1036. ->insertFields(array(
  1037. 'age' => 31,
  1038. 'name' => 'Tiffany',
  1039. ))
  1040. ->updateFields(array(
  1041. 'name' => 'Joe',
  1042. ))
  1043. ->execute();
  1044. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1045. $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
  1046. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1047. $this->assertEqual($person->name, 'Joe', 'Name set correctly.');
  1048. $this->assertEqual($person->age, 30, 'Age skipped correctly.');
  1049. $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  1050. }
  1051. /**
  1052. * Confirm that we can merge-update a record successfully, with expressions.
  1053. */
  1054. function testMergeUpdateExpression() {
  1055. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1056. $age_before = db_query('SELECT age FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetchField();
  1057. // This is a very contrived example, as I have no idea why you'd want to
  1058. // change age this way, but that's beside the point.
  1059. // Note that we are also double-setting age here, once as a literal and
  1060. // once as an expression. This test will only pass if the expression wins,
  1061. // which is what is supposed to happen.
  1062. db_merge('test_people')
  1063. ->key(array('job' => 'Speaker'))
  1064. ->fields(array('name' => 'Tiffany'))
  1065. ->insertFields(array('age' => 31))
  1066. ->expression('age', 'age + :age', array(':age' => 4))
  1067. ->execute();
  1068. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1069. $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
  1070. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1071. $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
  1072. $this->assertEqual($person->age, $age_before + 4, 'Age updated correctly.');
  1073. $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  1074. }
  1075. /**
  1076. * Test that we can merge-insert without any update fields.
  1077. */
  1078. function testMergeInsertWithoutUpdate() {
  1079. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1080. db_merge('test_people')
  1081. ->key(array('job' => 'Presenter'))
  1082. ->execute();
  1083. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1084. $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
  1085. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
  1086. $this->assertEqual($person->name, '', 'Name set correctly.');
  1087. $this->assertEqual($person->age, 0, 'Age set correctly.');
  1088. $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
  1089. }
  1090. /**
  1091. * Confirm that we can merge-update without any update fields.
  1092. */
  1093. function testMergeUpdateWithoutUpdate() {
  1094. $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1095. db_merge('test_people')
  1096. ->key(array('job' => 'Speaker'))
  1097. ->execute();
  1098. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1099. $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
  1100. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1101. $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
  1102. $this->assertEqual($person->age, 30, 'Age skipped correctly.');
  1103. $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
  1104. db_merge('test_people')
  1105. ->key(array('job' => 'Speaker'))
  1106. ->insertFields(array('age' => 31))
  1107. ->execute();
  1108. $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
  1109. $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
  1110. $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
  1111. $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
  1112. $this->assertEqual($person->age, 30, 'Age skipped correctly.');
  1113. $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
  1114. }
  1115. /**
  1116. * Test that an invalid merge query throws an exception like it is supposed to.
  1117. */
  1118. function testInvalidMerge() {
  1119. try {
  1120. // This query should die because there is no key field specified.
  1121. db_merge('test_people')
  1122. ->fields(array(
  1123. 'age' => 31,
  1124. 'name' => 'Tiffany',
  1125. ))
  1126. ->execute();
  1127. }
  1128. catch (InvalidMergeQueryException $e) {
  1129. $this->pass('InvalidMergeQueryException thrown for invalid query.');
  1130. return;
  1131. }
  1132. $this->fail('No InvalidMergeQueryException thrown');
  1133. }
  1134. }
  1135. /**
  1136. * Test the SELECT builder.
  1137. */
  1138. class DatabaseSelectTestCase extends DatabaseTestCase {
  1139. public static function getInfo() {
  1140. return array(
  1141. 'name' => 'Select tests',
  1142. 'description' => 'Test the Select query builder.',
  1143. 'group' => 'Database',
  1144. );
  1145. }
  1146. /**
  1147. * Test rudimentary SELECT statements.
  1148. */
  1149. function testSimpleSelect() {
  1150. $query = db_select('test');
  1151. $name_field = $query->addField('test', 'name');
  1152. $age_field = $query->addField('test', 'age', 'age');
  1153. $result = $query->execute();
  1154. $num_records = 0;
  1155. foreach ($result as $record) {
  1156. $num_records++;
  1157. }
  1158. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1159. }
  1160. /**
  1161. * Test rudimentary SELECT statement with a COMMENT.
  1162. */
  1163. function testSimpleComment() {
  1164. $query = db_select('test')->comment('Testing query comments');
  1165. $name_field = $query->addField('test', 'name');
  1166. $age_field = $query->addField('test', 'age', 'age');
  1167. $result = $query->execute();
  1168. $num_records = 0;
  1169. foreach ($result as $record) {
  1170. $num_records++;
  1171. }
  1172. $query = (string)$query;
  1173. $expected = "/* Testing query comments */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
  1174. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1175. $this->assertEqual($query, $expected, 'The flattened query contains the comment string.');
  1176. }
  1177. /**
  1178. * Test query COMMENT system against vulnerabilities.
  1179. */
  1180. function testVulnerableComment() {
  1181. $query = db_select('test')->comment('Testing query comments */ SELECT nid FROM {node}; --');
  1182. $name_field = $query->addField('test', 'name');
  1183. $age_field = $query->addField('test', 'age', 'age');
  1184. $result = $query->execute();
  1185. $num_records = 0;
  1186. foreach ($result as $record) {
  1187. $num_records++;
  1188. }
  1189. $query = (string)$query;
  1190. $expected = "/* Testing query comments SELECT nid FROM {node}; -- */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
  1191. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1192. $this->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');
  1193. }
  1194. /**
  1195. * Test basic conditionals on SELECT statements.
  1196. */
  1197. function testSimpleSelectConditional() {
  1198. $query = db_select('test');
  1199. $name_field = $query->addField('test', 'name');
  1200. $age_field = $query->addField('test', 'age', 'age');
  1201. $query->condition('age', 27);
  1202. $result = $query->execute();
  1203. // Check that the aliases are being created the way we want.
  1204. $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
  1205. $this->assertEqual($age_field, 'age', 'Age field alias is correct.');
  1206. // Ensure that we got the right record.
  1207. $record = $result->fetch();
  1208. $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
  1209. $this->assertEqual($record->$age_field, 27, 'Fetched age is correct.');
  1210. }
  1211. /**
  1212. * Test SELECT statements with expressions.
  1213. */
  1214. function testSimpleSelectExpression() {
  1215. $query = db_select('test');
  1216. $name_field = $query->addField('test', 'name');
  1217. $age_field = $query->addExpression("age*2", 'double_age');
  1218. $query->condition('age', 27);
  1219. $result = $query->execute();
  1220. // Check that the aliases are being created the way we want.
  1221. $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
  1222. $this->assertEqual($age_field, 'double_age', 'Age field alias is correct.');
  1223. // Ensure that we got the right record.
  1224. $record = $result->fetch();
  1225. $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
  1226. $this->assertEqual($record->$age_field, 27*2, 'Fetched age expression is correct.');
  1227. }
  1228. /**
  1229. * Test SELECT statements with multiple expressions.
  1230. */
  1231. function testSimpleSelectExpressionMultiple() {
  1232. $query = db_select('test');
  1233. $name_field = $query->addField('test', 'name');
  1234. $age_double_field = $query->addExpression("age*2");
  1235. $age_triple_field = $query->addExpression("age*3");
  1236. $query->condition('age', 27);
  1237. $result = $query->execute();
  1238. // Check that the aliases are being created the way we want.
  1239. $this->assertEqual($age_double_field, 'expression', 'Double age field alias is correct.');
  1240. $this->assertEqual($age_triple_field, 'expression_2', 'Triple age field alias is correct.');
  1241. // Ensure that we got the right record.
  1242. $record = $result->fetch();
  1243. $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
  1244. $this->assertEqual($record->$age_double_field, 27*2, 'Fetched double age expression is correct.');
  1245. $this->assertEqual($record->$age_triple_field, 27*3, 'Fetched triple age expression is correct.');
  1246. }
  1247. /**
  1248. * Test adding multiple fields to a select statement at the same time.
  1249. */
  1250. function testSimpleSelectMultipleFields() {
  1251. $record = db_select('test')
  1252. ->fields('test', array('id', 'name', 'age', 'job'))
  1253. ->condition('age', 27)
  1254. ->execute()->fetchObject();
  1255. // Check that all fields we asked for are present.
  1256. $this->assertNotNull($record->id, 'ID field is present.');
  1257. $this->assertNotNull($record->name, 'Name field is present.');
  1258. $this->assertNotNull($record->age, 'Age field is present.');
  1259. $this->assertNotNull($record->job, 'Job field is present.');
  1260. // Ensure that we got the right record.
  1261. // Check that all fields we asked for are present.
  1262. $this->assertEqual($record->id, 2, 'ID field has the correct value.');
  1263. $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
  1264. $this->assertEqual($record->age, 27, 'Age field has the correct value.');
  1265. $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
  1266. }
  1267. /**
  1268. * Test adding all fields from a given table to a select statement.
  1269. */
  1270. function testSimpleSelectAllFields() {
  1271. $record = db_select('test')
  1272. ->fields('test')
  1273. ->condition('age', 27)
  1274. ->execute()->fetchObject();
  1275. // Check that all fields we asked for are present.
  1276. $this->assertNotNull($record->id, 'ID field is present.');
  1277. $this->assertNotNull($record->name, 'Name field is present.');
  1278. $this->assertNotNull($record->age, 'Age field is present.');
  1279. $this->assertNotNull($record->job, 'Job field is present.');
  1280. // Ensure that we got the right record.
  1281. // Check that all fields we asked for are present.
  1282. $this->assertEqual($record->id, 2, 'ID field has the correct value.');
  1283. $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
  1284. $this->assertEqual($record->age, 27, 'Age field has the correct value.');
  1285. $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
  1286. }
  1287. /**
  1288. * Test that we can find a record with a NULL value.
  1289. */
  1290. function testNullCondition() {
  1291. $this->ensureSampleDataNull();
  1292. $names = db_select('test_null', 'tn')
  1293. ->fields('tn', array('name'))
  1294. ->isNull('age')
  1295. ->execute()->fetchCol();
  1296. $this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
  1297. $this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
  1298. }
  1299. /**
  1300. * Test that we can find a record without a NULL value.
  1301. */
  1302. function testNotNullCondition() {
  1303. $this->ensureSampleDataNull();
  1304. $names = db_select('test_null', 'tn')
  1305. ->fields('tn', array('name'))
  1306. ->isNotNull('tn.age')
  1307. ->orderBy('name')
  1308. ->execute()->fetchCol();
  1309. $this->assertEqual(count($names), 2, 'Correct number of records found withNOT NULL age.');
  1310. $this->assertEqual($names[0], 'Gonzo', 'Correct record returned for NOT NULL age.');
  1311. $this->assertEqual($names[1], 'Kermit', 'Correct record returned for NOT NULL age.');
  1312. }
  1313. /**
  1314. * Test that we can UNION multiple Select queries together. This is
  1315. * semantically equal to UNION DISTINCT, so we don't explicity test that.
  1316. */
  1317. function testUnion() {
  1318. $query_1 = db_select('test', 't')
  1319. ->fields('t', array('name'))
  1320. ->condition('age', array(27, 28), 'IN');
  1321. $query_2 = db_select('test', 't')
  1322. ->fields('t', array('name'))
  1323. ->condition('age', 28);
  1324. $query_1->union($query_2);
  1325. $names = $query_1->execute()->fetchCol();
  1326. // Ensure we only get 2 records.
  1327. $this->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
  1328. $this->assertEqual($names[0], 'George', 'First query returned correct name.');
  1329. $this->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
  1330. }
  1331. /**
  1332. * Test that we can UNION ALL multiple Select queries together.
  1333. */
  1334. function testUnionAll() {
  1335. $query_1 = db_select('test', 't')
  1336. ->fields('t', array('name'))
  1337. ->condition('age', array(27, 28), 'IN');
  1338. $query_2 = db_select('test', 't')
  1339. ->fields('t', array('name'))
  1340. ->condition('age', 28);
  1341. $query_1->union($query_2, 'ALL');
  1342. $names = $query_1->execute()->fetchCol();
  1343. // Ensure we get all 3 records.
  1344. $this->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
  1345. $this->assertEqual($names[0], 'George', 'First query returned correct first name.');
  1346. $this->assertEqual($names[1], 'Ringo', 'Second query returned correct second name.');
  1347. $this->assertEqual($names[2], 'Ringo', 'Third query returned correct name.');
  1348. }
  1349. /**
  1350. * Test that random ordering of queries works.
  1351. *
  1352. * We take the approach of testing the Drupal layer only, rather than trying
  1353. * to test that the database's random number generator actually produces
  1354. * random queries (which is very difficult to do without an unacceptable risk
  1355. * of the test failing by accident).
  1356. *
  1357. * Therefore, in this test we simply run the same query twice and assert that
  1358. * the two results are reordered versions of each other (as well as of the
  1359. * same query without the random ordering). It is reasonable to assume that
  1360. * if we run the same select query twice and the results are in a different
  1361. * order each time, the only way this could happen is if we have successfully
  1362. * triggered the database's random ordering functionality.
  1363. */
  1364. function testRandomOrder() {
  1365. // Use 52 items, so the chance that this test fails by accident will be the
  1366. // same as the chance that a deck of cards will come out in the same order
  1367. // after shuffling it (in other words, nearly impossible).
  1368. $number_of_items = 52;
  1369. while (db_query("SELECT MAX(id) FROM {test}")->fetchField() < $number_of_items) {
  1370. db_insert('test')->fields(array('name' => $this->randomName()))->execute();
  1371. }
  1372. // First select the items in order and make sure we get an ordered list.
  1373. $expected_ids = range(1, $number_of_items);
  1374. $ordered_ids = db_select('test', 't')
  1375. ->fields('t', array('id'))
  1376. ->range(0, $number_of_items)
  1377. ->orderBy('id')
  1378. ->execute()
  1379. ->fetchCol();
  1380. $this->assertEqual($ordered_ids, $expected_ids, 'A query without random ordering returns IDs in the correct order.');
  1381. // Now perform the same query, but instead choose a random ordering. We
  1382. // expect this to contain a differently ordered version of the original
  1383. // result.
  1384. $randomized_ids = db_select('test', 't')
  1385. ->fields('t', array('id'))
  1386. ->range(0, $number_of_items)
  1387. ->orderRandom()
  1388. ->execute()
  1389. ->fetchCol();
  1390. $this->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
  1391. $sorted_ids = $randomized_ids;
  1392. sort($sorted_ids);
  1393. $this->assertEqual($sorted_ids, $ordered_ids, 'After sorting the random list, the result matches the original query.');
  1394. // Now perform the exact same query again, and make sure the order is
  1395. // different.
  1396. $randomized_ids_second_set = db_select('test', 't')
  1397. ->fields('t', array('id'))
  1398. ->range(0, $number_of_items)
  1399. ->orderRandom()
  1400. ->execute()
  1401. ->fetchCol();
  1402. $this->assertNotEqual($randomized_ids_second_set, $randomized_ids, 'Performing the query with random ordering a second time returns IDs in a different order.');
  1403. $sorted_ids_second_set = $randomized_ids_second_set;
  1404. sort($sorted_ids_second_set);
  1405. $this->assertEqual($sorted_ids_second_set, $sorted_ids, 'After sorting the second random list, the result matches the sorted version of the first random list.');
  1406. }
  1407. /**
  1408. * Test that aliases are renamed when duplicates.
  1409. */
  1410. function testSelectDuplicateAlias() {
  1411. $query = db_select('test', 't');
  1412. $alias1 = $query->addField('t', 'name', 'the_alias');
  1413. $alias2 = $query->addField('t', 'age', 'the_alias');
  1414. $this->assertNotIdentical($alias1, $alias2, 'Duplicate aliases are renamed.');
  1415. }
  1416. }
  1417. /**
  1418. * Test case for subselects in a dynamic SELECT query.
  1419. */
  1420. class DatabaseSelectSubqueryTestCase extends DatabaseTestCase {
  1421. public static function getInfo() {
  1422. return array(
  1423. 'name' => 'Select tests, subqueries',
  1424. 'description' => 'Test the Select query builder.',
  1425. 'group' => 'Database',
  1426. );
  1427. }
  1428. /**
  1429. * Test that we can use a subquery in a FROM clause.
  1430. */
  1431. function testFromSubquerySelect() {
  1432. // Create a subquery, which is just a normal query object.
  1433. $subquery = db_select('test_task', 'tt');
  1434. $subquery->addField('tt', 'pid', 'pid');
  1435. $subquery->addField('tt', 'task', 'task');
  1436. $subquery->condition('priority', 1);
  1437. for ($i = 0; $i < 2; $i++) {
  1438. // Create another query that joins against the virtual table resulting
  1439. // from the subquery.
  1440. $select = db_select($subquery, 'tt2');
  1441. $select->join('test', 't', 't.id=tt2.pid');
  1442. $select->addField('t', 'name');
  1443. if ($i) {
  1444. // Use a different number of conditions here to confuse the subquery
  1445. // placeholder counter, testing http://drupal.org/node/1112854.
  1446. $select->condition('name', 'John');
  1447. }
  1448. $select->condition('task', 'code');
  1449. // The resulting query should be equivalent to:
  1450. // SELECT t.name
  1451. // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt WHERE priority=1) tt
  1452. // INNER JOIN test t ON t.id=tt.pid
  1453. // WHERE tt.task = 'code'
  1454. $people = $select->execute()->fetchCol();
  1455. $this->assertEqual(count($people), 1, 'Returned the correct number of rows.');
  1456. }
  1457. }
  1458. /**
  1459. * Test that we can use a subquery in a FROM clause with a limit.
  1460. */
  1461. function testFromSubquerySelectWithLimit() {
  1462. // Create a subquery, which is just a normal query object.
  1463. $subquery = db_select('test_task', 'tt');
  1464. $subquery->addField('tt', 'pid', 'pid');
  1465. $subquery->addField('tt', 'task', 'task');
  1466. $subquery->orderBy('priority', 'DESC');
  1467. $subquery->range(0, 1);
  1468. // Create another query that joins against the virtual table resulting
  1469. // from the subquery.
  1470. $select = db_select($subquery, 'tt2');
  1471. $select->join('test', 't', 't.id=tt2.pid');
  1472. $select->addField('t', 'name');
  1473. // The resulting query should be equivalent to:
  1474. // SELECT t.name
  1475. // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt ORDER BY priority DESC LIMIT 1 OFFSET 0) tt
  1476. // INNER JOIN test t ON t.id=tt.pid
  1477. $people = $select->execute()->fetchCol();
  1478. $this->assertEqual(count($people), 1, 'Returned the correct number of rows.');
  1479. }
  1480. /**
  1481. * Test that we can use a subquery in a WHERE clause.
  1482. */
  1483. function testConditionSubquerySelect() {
  1484. // Create a subquery, which is just a normal query object.
  1485. $subquery = db_select('test_task', 'tt');
  1486. $subquery->addField('tt', 'pid', 'pid');
  1487. $subquery->condition('tt.priority', 1);
  1488. // Create another query that joins against the virtual table resulting
  1489. // from the subquery.
  1490. $select = db_select('test_task', 'tt2');
  1491. $select->addField('tt2', 'task');
  1492. $select->condition('tt2.pid', $subquery, 'IN');
  1493. // The resulting query should be equivalent to:
  1494. // SELECT tt2.name
  1495. // FROM test tt2
  1496. // WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1)
  1497. $people = $select->execute()->fetchCol();
  1498. $this->assertEqual(count($people), 5, 'Returned the correct number of rows.');
  1499. }
  1500. /**
  1501. * Test that we can use a subquery in a JOIN clause.
  1502. */
  1503. function testJoinSubquerySelect() {
  1504. // Create a subquery, which is just a normal query object.
  1505. $subquery = db_select('test_task', 'tt');
  1506. $subquery->addField('tt', 'pid', 'pid');
  1507. $subquery->condition('priority', 1);
  1508. // Create another query that joins against the virtual table resulting
  1509. // from the subquery.
  1510. $select = db_select('test', 't');
  1511. $select->join($subquery, 'tt', 't.id=tt.pid');
  1512. $select->addField('t', 'name');
  1513. // The resulting query should be equivalent to:
  1514. // SELECT t.name
  1515. // FROM test t
  1516. // INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
  1517. $people = $select->execute()->fetchCol();
  1518. $this->assertEqual(count($people), 2, 'Returned the correct number of rows.');
  1519. }
  1520. /**
  1521. * Test EXISTS subquery conditionals on SELECT statements.
  1522. *
  1523. * We essentially select all rows from the {test} table that have matching
  1524. * rows in the {test_people} table based on the shared name column.
  1525. */
  1526. function testExistsSubquerySelect() {
  1527. // Put George into {test_people}.
  1528. db_insert('test_people')
  1529. ->fields(array(
  1530. 'name' => 'George',
  1531. 'age' => 27,
  1532. 'job' => 'Singer',
  1533. ))
  1534. ->execute();
  1535. // Base query to {test}.
  1536. $query = db_select('test', 't')
  1537. ->fields('t', array('name'));
  1538. // Subquery to {test_people}.
  1539. $subquery = db_select('test_people', 'tp')
  1540. ->fields('tp', array('name'))
  1541. ->where('tp.name = t.name');
  1542. $query->exists($subquery);
  1543. $result = $query->execute();
  1544. // Ensure that we got the right record.
  1545. $record = $result->fetch();
  1546. $this->assertEqual($record->name, 'George', 'Fetched name is correct using EXISTS query.');
  1547. }
  1548. /**
  1549. * Test NOT EXISTS subquery conditionals on SELECT statements.
  1550. *
  1551. * We essentially select all rows from the {test} table that don't have
  1552. * matching rows in the {test_people} table based on the shared name column.
  1553. */
  1554. function testNotExistsSubquerySelect() {
  1555. // Put George into {test_people}.
  1556. db_insert('test_people')
  1557. ->fields(array(
  1558. 'name' => 'George',
  1559. 'age' => 27,
  1560. 'job' => 'Singer',
  1561. ))
  1562. ->execute();
  1563. // Base query to {test}.
  1564. $query = db_select('test', 't')
  1565. ->fields('t', array('name'));
  1566. // Subquery to {test_people}.
  1567. $subquery = db_select('test_people', 'tp')
  1568. ->fields('tp', array('name'))
  1569. ->where('tp.name = t.name');
  1570. $query->notExists($subquery);
  1571. // Ensure that we got the right number of records.
  1572. $people = $query->execute()->fetchCol();
  1573. $this->assertEqual(count($people), 3, 'NOT EXISTS query returned the correct results.');
  1574. }
  1575. }
  1576. /**
  1577. * Test select with order by clauses.
  1578. */
  1579. class DatabaseSelectOrderedTestCase extends DatabaseTestCase {
  1580. public static function getInfo() {
  1581. return array(
  1582. 'name' => 'Select tests, ordered',
  1583. 'description' => 'Test the Select query builder.',
  1584. 'group' => 'Database',
  1585. );
  1586. }
  1587. /**
  1588. * Test basic order by.
  1589. */
  1590. function testSimpleSelectOrdered() {
  1591. $query = db_select('test');
  1592. $name_field = $query->addField('test', 'name');
  1593. $age_field = $query->addField('test', 'age', 'age');
  1594. $query->orderBy($age_field);
  1595. $result = $query->execute();
  1596. $num_records = 0;
  1597. $last_age = 0;
  1598. foreach ($result as $record) {
  1599. $num_records++;
  1600. $this->assertTrue($record->age >= $last_age, 'Results returned in correct order.');
  1601. $last_age = $record->age;
  1602. }
  1603. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1604. }
  1605. /**
  1606. * Test multiple order by.
  1607. */
  1608. function testSimpleSelectMultiOrdered() {
  1609. $query = db_select('test');
  1610. $name_field = $query->addField('test', 'name');
  1611. $age_field = $query->addField('test', 'age', 'age');
  1612. $job_field = $query->addField('test', 'job');
  1613. $query->orderBy($job_field);
  1614. $query->orderBy($age_field);
  1615. $result = $query->execute();
  1616. $num_records = 0;
  1617. $expected = array(
  1618. array('Ringo', 28, 'Drummer'),
  1619. array('John', 25, 'Singer'),
  1620. array('George', 27, 'Singer'),
  1621. array('Paul', 26, 'Songwriter'),
  1622. );
  1623. $results = $result->fetchAll(PDO::FETCH_NUM);
  1624. foreach ($expected as $k => $record) {
  1625. $num_records++;
  1626. foreach ($record as $kk => $col) {
  1627. if ($expected[$k][$kk] != $results[$k][$kk]) {
  1628. $this->assertTrue(FALSE, 'Results returned in correct order.');
  1629. }
  1630. }
  1631. }
  1632. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1633. }
  1634. /**
  1635. * Test order by descending.
  1636. */
  1637. function testSimpleSelectOrderedDesc() {
  1638. $query = db_select('test');
  1639. $name_field = $query->addField('test', 'name');
  1640. $age_field = $query->addField('test', 'age', 'age');
  1641. $query->orderBy($age_field, 'DESC');
  1642. $result = $query->execute();
  1643. $num_records = 0;
  1644. $last_age = 100000000;
  1645. foreach ($result as $record) {
  1646. $num_records++;
  1647. $this->assertTrue($record->age <= $last_age, 'Results returned in correct order.');
  1648. $last_age = $record->age;
  1649. }
  1650. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  1651. }
  1652. }
  1653. /**
  1654. * Test more complex select statements.
  1655. */
  1656. class DatabaseSelectComplexTestCase extends DatabaseTestCase {
  1657. public static function getInfo() {
  1658. return array(
  1659. 'name' => 'Select tests, complex',
  1660. 'description' => 'Test the Select query builder with more complex queries.',
  1661. 'group' => 'Database',
  1662. );
  1663. }
  1664. /**
  1665. * Test simple JOIN statements.
  1666. */
  1667. function testDefaultJoin() {
  1668. $query = db_select('test_task', 't');
  1669. $people_alias = $query->join('test', 'p', 't.pid = p.id');
  1670. $name_field = $query->addField($people_alias, 'name', 'name');
  1671. $task_field = $query->addField('t', 'task', 'task');
  1672. $priority_field = $query->addField('t', 'priority', 'priority');
  1673. $query->orderBy($priority_field);
  1674. $result = $query->execute();
  1675. $num_records = 0;
  1676. $last_priority = 0;
  1677. foreach ($result as $record) {
  1678. $num_records++;
  1679. $this->assertTrue($record->$priority_field >= $last_priority, 'Results returned in correct order.');
  1680. $this->assertNotEqual($record->$name_field, 'Ringo', 'Taskless person not selected.');
  1681. $last_priority = $record->$priority_field;
  1682. }
  1683. $this->assertEqual($num_records, 7, 'Returned the correct number of rows.');
  1684. }
  1685. /**
  1686. * Test LEFT OUTER joins.
  1687. */
  1688. function testLeftOuterJoin() {
  1689. $query = db_select('test', 'p');
  1690. $people_alias = $query->leftJoin('test_task', 't', 't.pid = p.id');
  1691. $name_field = $query->addField('p', 'name', 'name');
  1692. $task_field = $query->addField($people_alias, 'task', 'task');
  1693. $priority_field = $query->addField($people_alias, 'priority', 'priority');
  1694. $query->orderBy($name_field);
  1695. $result = $query->execute();
  1696. $num_records = 0;
  1697. $last_name = 0;
  1698. foreach ($result as $record) {
  1699. $num_records++;
  1700. $this->assertTrue(strcmp($record->$name_field, $last_name) >= 0, 'Results returned in correct order.');
  1701. $last_priority = $record->$name_field;
  1702. }
  1703. $this->assertEqual($num_records, 8, 'Returned the correct number of rows.');
  1704. }
  1705. /**
  1706. * Test GROUP BY clauses.
  1707. */
  1708. function testGroupBy() {
  1709. $query = db_select('test_task', 't');
  1710. $count_field = $query->addExpression('COUNT(task)', 'num');
  1711. $task_field = $query->addField('t', 'task');
  1712. $query->orderBy($count_field);
  1713. $query->groupBy($task_field);
  1714. $result = $query->execute();
  1715. $num_records = 0;
  1716. $last_count = 0;
  1717. $records = array();
  1718. foreach ($result as $record) {
  1719. $num_records++;
  1720. $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
  1721. $last_count = $record->$count_field;
  1722. $records[$record->$task_field] = $record->$count_field;
  1723. }
  1724. $correct_results = array(
  1725. 'eat' => 1,
  1726. 'sleep' => 2,
  1727. 'code' => 1,
  1728. 'found new band' => 1,
  1729. 'perform at superbowl' => 1,
  1730. );
  1731. foreach ($correct_results as $task => $count) {
  1732. $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
  1733. }
  1734. $this->assertEqual($num_records, 6, 'Returned the correct number of total rows.');
  1735. }
  1736. /**
  1737. * Test GROUP BY and HAVING clauses together.
  1738. */
  1739. function testGroupByAndHaving() {
  1740. $query = db_select('test_task', 't');
  1741. $count_field = $query->addExpression('COUNT(task)', 'num');
  1742. $task_field = $query->addField('t', 'task');
  1743. $query->orderBy($count_field);
  1744. $query->groupBy($task_field);
  1745. $query->having('COUNT(task) >= 2');
  1746. $result = $query->execute();
  1747. $num_records = 0;
  1748. $last_count = 0;
  1749. $records = array();
  1750. foreach ($result as $record) {
  1751. $num_records++;
  1752. $this->assertTrue($record->$count_field >= 2, 'Record has the minimum count.');
  1753. $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
  1754. $last_count = $record->$count_field;
  1755. $records[$record->$task_field] = $record->$count_field;
  1756. }
  1757. $correct_results = array(
  1758. 'sleep' => 2,
  1759. );
  1760. foreach ($correct_results as $task => $count) {
  1761. $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
  1762. }
  1763. $this->assertEqual($num_records, 1, 'Returned the correct number of total rows.');
  1764. }
  1765. /**
  1766. * Test range queries. The SQL clause varies with the database.
  1767. */
  1768. function testRange() {
  1769. $query = db_select('test');
  1770. $name_field = $query->addField('test', 'name');
  1771. $age_field = $query->addField('test', 'age', 'age');
  1772. $query->range(0, 2);
  1773. $result = $query->execute();
  1774. $num_records = 0;
  1775. foreach ($result as $record) {
  1776. $num_records++;
  1777. }
  1778. $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  1779. }
  1780. /**
  1781. * Test distinct queries.
  1782. */
  1783. function testDistinct() {
  1784. $query = db_select('test_task');
  1785. $task_field = $query->addField('test_task', 'task');
  1786. $query->distinct();
  1787. $result = $query->execute();
  1788. $num_records = 0;
  1789. foreach ($result as $record) {
  1790. $num_records++;
  1791. }
  1792. $this->assertEqual($num_records, 6, 'Returned the correct number of rows.');
  1793. }
  1794. /**
  1795. * Test that we can generate a count query from a built query.
  1796. */
  1797. function testCountQuery() {
  1798. $query = db_select('test');
  1799. $name_field = $query->addField('test', 'name');
  1800. $age_field = $query->addField('test', 'age', 'age');
  1801. $query->orderBy('name');
  1802. $count = $query->countQuery()->execute()->fetchField();
  1803. $this->assertEqual($count, 4, 'Counted the correct number of records.');
  1804. // Now make sure we didn't break the original query! We should still have
  1805. // all of the fields we asked for.
  1806. $record = $query->execute()->fetch();
  1807. $this->assertEqual($record->$name_field, 'George', 'Correct data retrieved.');
  1808. $this->assertEqual($record->$age_field, 27, 'Correct data retrieved.');
  1809. }
  1810. function testHavingCountQuery() {
  1811. $query = db_select('test')
  1812. ->extend('PagerDefault')
  1813. ->groupBy('age')
  1814. ->having('age + 1 > 0');
  1815. $query->addField('test', 'age');
  1816. $query->addExpression('age + 1');
  1817. $count = count($query->execute()->fetchCol());
  1818. $this->assertEqual($count, 4, 'Counted the correct number of records.');
  1819. }
  1820. /**
  1821. * Test that countQuery properly removes 'all_fields' statements and
  1822. * ordering clauses.
  1823. */
  1824. function testCountQueryRemovals() {
  1825. $query = db_select('test');
  1826. $query->fields('test');
  1827. $query->orderBy('name');
  1828. $count = $query->countQuery();
  1829. // Check that the 'all_fields' statement is handled properly.
  1830. $tables = $query->getTables();
  1831. $this->assertEqual($tables['test']['all_fields'], 1, 'Query correctly sets \'all_fields\' statement.');
  1832. $tables = $count->getTables();
  1833. $this->assertFalse(isset($tables['test']['all_fields']), 'Count query correctly unsets \'all_fields\' statement.');
  1834. // Check that the ordering clause is handled properly.
  1835. $orderby = $query->getOrderBy();
  1836. $this->assertEqual($orderby['name'], 'ASC', 'Query correctly sets ordering clause.');
  1837. $orderby = $count->getOrderBy();
  1838. $this->assertFalse(isset($orderby['name']), 'Count query correctly unsets ordering caluse.');
  1839. // Make sure that the count query works.
  1840. $count = $count->execute()->fetchField();
  1841. $this->assertEqual($count, 4, 'Counted the correct number of records.');
  1842. }
  1843. /**
  1844. * Test that countQuery properly removes fields and expressions.
  1845. */
  1846. function testCountQueryFieldRemovals() {
  1847. // countQuery should remove all fields and expressions, so this can be
  1848. // tested by adding a non-existent field and expression: if it ends
  1849. // up in the query, an error will be thrown. If not, it will return the
  1850. // number of records, which in this case happens to be 4 (there are four
  1851. // records in the {test} table).
  1852. $query = db_select('test');
  1853. $query->fields('test', array('fail'));
  1854. $this->assertEqual(4, $query->countQuery()->execute()->fetchField(), 'Count Query removed fields');
  1855. $query = db_select('test');
  1856. $query->addExpression('fail');
  1857. $this->assertEqual(4, $query->countQuery()->execute()->fetchField(), 'Count Query removed expressions');
  1858. }
  1859. /**
  1860. * Test that we can generate a count query from a query with distinct.
  1861. */
  1862. function testCountQueryDistinct() {
  1863. $query = db_select('test_task');
  1864. $task_field = $query->addField('test_task', 'task');
  1865. $query->distinct();
  1866. $count = $query->countQuery()->execute()->fetchField();
  1867. $this->assertEqual($count, 6, 'Counted the correct number of records.');
  1868. }
  1869. /**
  1870. * Test that we can generate a count query from a query with GROUP BY.
  1871. */
  1872. function testCountQueryGroupBy() {
  1873. $query = db_select('test_task');
  1874. $pid_field = $query->addField('test_task', 'pid');
  1875. $query->groupBy('pid');
  1876. $count = $query->countQuery()->execute()->fetchField();
  1877. $this->assertEqual($count, 3, 'Counted the correct number of records.');
  1878. // Use a column alias as, without one, the query can succeed for the wrong
  1879. // reason.
  1880. $query = db_select('test_task');
  1881. $pid_field = $query->addField('test_task', 'pid', 'pid_alias');
  1882. $query->addExpression('COUNT(test_task.task)', 'count');
  1883. $query->groupBy('pid_alias');
  1884. $query->orderBy('pid_alias', 'asc');
  1885. $count = $query->countQuery()->execute()->fetchField();
  1886. $this->assertEqual($count, 3, 'Counted the correct number of records.');
  1887. }
  1888. /**
  1889. * Confirm that we can properly nest conditional clauses.
  1890. */
  1891. function testNestedConditions() {
  1892. // This query should translate to:
  1893. // "SELECT job FROM {test} WHERE name = 'Paul' AND (age = 26 OR age = 27)"
  1894. // That should find only one record. Yes it's a non-optimal way of writing
  1895. // that query but that's not the point!
  1896. $query = db_select('test');
  1897. $query->addField('test', 'job');
  1898. $query->condition('name', 'Paul');
  1899. $query->condition(db_or()->condition('age', 26)->condition('age', 27));
  1900. $job = $query->execute()->fetchField();
  1901. $this->assertEqual($job, 'Songwriter', 'Correct data retrieved.');
  1902. }
  1903. /**
  1904. * Confirm we can join on a single table twice with a dynamic alias.
  1905. */
  1906. function testJoinTwice() {
  1907. $query = db_select('test')->fields('test');
  1908. $alias = $query->join('test', 'test', 'test.job = %alias.job');
  1909. $query->addField($alias, 'name', 'othername');
  1910. $query->addField($alias, 'job', 'otherjob');
  1911. $query->where("$alias.name <> test.name");
  1912. $crowded_job = $query->execute()->fetch();
  1913. $this->assertEqual($crowded_job->job, $crowded_job->otherjob, 'Correctly joined same table twice.');
  1914. $this->assertNotEqual($crowded_job->name, $crowded_job->othername, 'Correctly joined same table twice.');
  1915. }
  1916. }
  1917. /**
  1918. * Test more complex select statements, part 2.
  1919. */
  1920. class DatabaseSelectComplexTestCase2 extends DatabaseTestCase {
  1921. public static function getInfo() {
  1922. return array(
  1923. 'name' => 'Select tests, complex 2',
  1924. 'description' => 'Test the Select query builder with even more complex queries.',
  1925. 'group' => 'Database',
  1926. );
  1927. }
  1928. function setUp() {
  1929. DrupalWebTestCase::setUp('database_test', 'node_access_test');
  1930. $schema['test'] = drupal_get_schema('test');
  1931. $schema['test_people'] = drupal_get_schema('test_people');
  1932. $schema['test_one_blob'] = drupal_get_schema('test_one_blob');
  1933. $schema['test_two_blobs'] = drupal_get_schema('test_two_blobs');
  1934. $schema['test_task'] = drupal_get_schema('test_task');
  1935. $this->installTables($schema);
  1936. $this->addSampleData();
  1937. }
  1938. /**
  1939. * Test that we can join on a query.
  1940. */
  1941. function testJoinSubquery() {
  1942. $acct = $this->drupalCreateUser(array('access content'));
  1943. $this->drupalLogin($acct);
  1944. $query = db_select('test_task', 'tt', array('target' => 'slave'));
  1945. $query->addExpression('tt.pid + 1', 'abc');
  1946. $query->condition('priority', 1, '>');
  1947. $query->condition('priority', 100, '<');
  1948. $subquery = db_select('test', 'tp');
  1949. $subquery->join('test_one_blob', 'tpb', 'tp.id = tpb.id');
  1950. $subquery->join('node', 'n', 'tp.id = n.nid');
  1951. $subquery->addTag('node_access');
  1952. $subquery->addMetaData('account', $acct);
  1953. $subquery->addField('tp', 'id');
  1954. $subquery->condition('age', 5, '>');
  1955. $subquery->condition('age', 500, '<');
  1956. $query->leftJoin($subquery, 'sq', 'tt.pid = sq.id');
  1957. $query->join('test_one_blob', 'tb3', 'tt.pid = tb3.id');
  1958. // Construct the query string.
  1959. // This is the same sequence that SelectQuery::execute() goes through.
  1960. $query->preExecute();
  1961. $query->getArguments();
  1962. $str = (string) $query;
  1963. // Verify that the string only has one copy of condition placeholder 0.
  1964. $pos = strpos($str, 'db_condition_placeholder_0', 0);
  1965. $pos2 = strpos($str, 'db_condition_placeholder_0', $pos + 1);
  1966. $this->assertFalse($pos2, 'Condition placeholder is not repeated.');
  1967. }
  1968. }
  1969. class DatabaseSelectPagerDefaultTestCase extends DatabaseTestCase {
  1970. public static function getInfo() {
  1971. return array(
  1972. 'name' => 'Pager query tests',
  1973. 'description' => 'Test the pager query extender.',
  1974. 'group' => 'Database',
  1975. );
  1976. }
  1977. /**
  1978. * Confirm that a pager query returns the correct results.
  1979. *
  1980. * Note that we have to make an HTTP request to a test page handler
  1981. * because the pager depends on GET parameters.
  1982. */
  1983. function testEvenPagerQuery() {
  1984. // To keep the test from being too brittle, we determine up front
  1985. // what the page count should be dynamically, and pass the control
  1986. // information forward to the actual query on the other side of the
  1987. // HTTP request.
  1988. $limit = 2;
  1989. $count = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  1990. $correct_number = $limit;
  1991. $num_pages = floor($count / $limit);
  1992. // If there is no remainder from rounding, subtract 1 since we index from 0.
  1993. if (!($num_pages * $limit < $count)) {
  1994. $num_pages--;
  1995. }
  1996. for ($page = 0; $page <= $num_pages; ++$page) {
  1997. $this->drupalGet('database_test/pager_query_even/' . $limit, array('query' => array('page' => $page)));
  1998. $data = json_decode($this->drupalGetContent());
  1999. if ($page == $num_pages) {
  2000. $correct_number = $count - ($limit * $page);
  2001. }
  2002. $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
  2003. }
  2004. }
  2005. /**
  2006. * Confirm that a pager query returns the correct results.
  2007. *
  2008. * Note that we have to make an HTTP request to a test page handler
  2009. * because the pager depends on GET parameters.
  2010. */
  2011. function testOddPagerQuery() {
  2012. // To keep the test from being too brittle, we determine up front
  2013. // what the page count should be dynamically, and pass the control
  2014. // information forward to the actual query on the other side of the
  2015. // HTTP request.
  2016. $limit = 2;
  2017. $count = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
  2018. $correct_number = $limit;
  2019. $num_pages = floor($count / $limit);
  2020. // If there is no remainder from rounding, subtract 1 since we index from 0.
  2021. if (!($num_pages * $limit < $count)) {
  2022. $num_pages--;
  2023. }
  2024. for ($page = 0; $page <= $num_pages; ++$page) {
  2025. $this->drupalGet('database_test/pager_query_odd/' . $limit, array('query' => array('page' => $page)));
  2026. $data = json_decode($this->drupalGetContent());
  2027. if ($page == $num_pages) {
  2028. $correct_number = $count - ($limit * $page);
  2029. }
  2030. $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
  2031. }
  2032. }
  2033. /**
  2034. * Confirm that a pager query with inner pager query returns valid results.
  2035. *
  2036. * This is a regression test for #467984.
  2037. */
  2038. function testInnerPagerQuery() {
  2039. $query = db_select('test', 't')->extend('PagerDefault');
  2040. $query
  2041. ->fields('t', array('age'))
  2042. ->orderBy('age')
  2043. ->limit(5);
  2044. $outer_query = db_select($query);
  2045. $outer_query->addField('subquery', 'age');
  2046. $ages = $outer_query
  2047. ->execute()
  2048. ->fetchCol();
  2049. $this->assertEqual($ages, array(25, 26, 27, 28), 'Inner pager query returned the correct ages.');
  2050. }
  2051. /**
  2052. * Confirm that a paging query with a having expression returns valid results.
  2053. *
  2054. * This is a regression test for #467984.
  2055. */
  2056. function testHavingPagerQuery() {
  2057. $query = db_select('test', 't')->extend('PagerDefault');
  2058. $query
  2059. ->fields('t', array('name'))
  2060. ->orderBy('name')
  2061. ->groupBy('name')
  2062. ->having('MAX(age) > :count', array(':count' => 26))
  2063. ->limit(5);
  2064. $ages = $query
  2065. ->execute()
  2066. ->fetchCol();
  2067. $this->assertEqual($ages, array('George', 'Ringo'), 'Pager query with having expression returned the correct ages.');
  2068. }
  2069. /**
  2070. * Confirm that every pager gets a valid non-overlaping element ID.
  2071. */
  2072. function testElementNumbers() {
  2073. $_GET['page'] = '3, 2, 1, 0';
  2074. $name = db_select('test', 't')->extend('PagerDefault')
  2075. ->element(2)
  2076. ->fields('t', array('name'))
  2077. ->orderBy('age')
  2078. ->limit(1)
  2079. ->execute()
  2080. ->fetchField();
  2081. $this->assertEqual($name, 'Paul', 'Pager query #1 with a specified element ID returned the correct results.');
  2082. // Setting an element smaller than the previous one
  2083. // should not overwrite the pager $maxElement with a smaller value.
  2084. $name = db_select('test', 't')->extend('PagerDefault')
  2085. ->element(1)
  2086. ->fields('t', array('name'))
  2087. ->orderBy('age')
  2088. ->limit(1)
  2089. ->execute()
  2090. ->fetchField();
  2091. $this->assertEqual($name, 'George', 'Pager query #2 with a specified element ID returned the correct results.');
  2092. $name = db_select('test', 't')->extend('PagerDefault')
  2093. ->fields('t', array('name'))
  2094. ->orderBy('age')
  2095. ->limit(1)
  2096. ->execute()
  2097. ->fetchField();
  2098. $this->assertEqual($name, 'John', 'Pager query #3 with a generated element ID returned the correct results.');
  2099. unset($_GET['page']);
  2100. }
  2101. }
  2102. class DatabaseSelectTableSortDefaultTestCase extends DatabaseTestCase {
  2103. public static function getInfo() {
  2104. return array(
  2105. 'name' => 'Tablesort query tests',
  2106. 'description' => 'Test the tablesort query extender.',
  2107. 'group' => 'Database',
  2108. );
  2109. }
  2110. /**
  2111. * Confirm that a tablesort query returns the correct results.
  2112. *
  2113. * Note that we have to make an HTTP request to a test page handler
  2114. * because the pager depends on GET parameters.
  2115. */
  2116. function testTableSortQuery() {
  2117. $sorts = array(
  2118. array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
  2119. array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
  2120. array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
  2121. array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
  2122. // more elements here
  2123. );
  2124. foreach ($sorts as $sort) {
  2125. $this->drupalGet('database_test/tablesort/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
  2126. $data = json_decode($this->drupalGetContent());
  2127. $first = array_shift($data->tasks);
  2128. $last = array_pop($data->tasks);
  2129. $this->assertEqual($first->task, $sort['first'], 'Items appear in the correct order.');
  2130. $this->assertEqual($last->task, $sort['last'], 'Items appear in the correct order.');
  2131. }
  2132. }
  2133. /**
  2134. * Confirm that if a tablesort's orderByHeader is called before another orderBy, that the header happens first.
  2135. *
  2136. */
  2137. function testTableSortQueryFirst() {
  2138. $sorts = array(
  2139. array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
  2140. array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
  2141. array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
  2142. array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
  2143. // more elements here
  2144. );
  2145. foreach ($sorts as $sort) {
  2146. $this->drupalGet('database_test/tablesort_first/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
  2147. $data = json_decode($this->drupalGetContent());
  2148. $first = array_shift($data->tasks);
  2149. $last = array_pop($data->tasks);
  2150. $this->assertEqual($first->task, $sort['first'], format_string('Items appear in the correct order sorting by @field @sort.', array('@field' => $sort['field'], '@sort' => $sort['sort'])));
  2151. $this->assertEqual($last->task, $sort['last'], format_string('Items appear in the correct order sorting by @field @sort.', array('@field' => $sort['field'], '@sort' => $sort['sort'])));
  2152. }
  2153. }
  2154. /**
  2155. * Confirm that if a sort is not set in a tableselect form there is no error thrown when using the default.
  2156. */
  2157. function testTableSortDefaultSort() {
  2158. $this->drupalGet('database_test/tablesort_default_sort');
  2159. // Any PHP errors or notices thrown would trigger a simpletest exception, so
  2160. // no additional assertions are needed.
  2161. }
  2162. }
  2163. /**
  2164. * Select tagging tests.
  2165. *
  2166. * Tags are a way to flag queries for alter hooks so they know
  2167. * what type of query it is, such as "node_access".
  2168. */
  2169. class DatabaseTaggingTestCase extends DatabaseTestCase {
  2170. public static function getInfo() {
  2171. return array(
  2172. 'name' => 'Query tagging tests',
  2173. 'description' => 'Test the tagging capabilities of the Select builder.',
  2174. 'group' => 'Database',
  2175. );
  2176. }
  2177. /**
  2178. * Confirm that a query has a "tag" added to it.
  2179. */
  2180. function testHasTag() {
  2181. $query = db_select('test');
  2182. $query->addField('test', 'name');
  2183. $query->addField('test', 'age', 'age');
  2184. $query->addTag('test');
  2185. $this->assertTrue($query->hasTag('test'), 'hasTag() returned true.');
  2186. $this->assertFalse($query->hasTag('other'), 'hasTag() returned false.');
  2187. }
  2188. /**
  2189. * Test query tagging "has all of these tags" functionality.
  2190. */
  2191. function testHasAllTags() {
  2192. $query = db_select('test');
  2193. $query->addField('test', 'name');
  2194. $query->addField('test', 'age', 'age');
  2195. $query->addTag('test');
  2196. $query->addTag('other');
  2197. $this->assertTrue($query->hasAllTags('test', 'other'), 'hasAllTags() returned true.');
  2198. $this->assertFalse($query->hasAllTags('test', 'stuff'), 'hasAllTags() returned false.');
  2199. }
  2200. /**
  2201. * Test query tagging "has at least one of these tags" functionality.
  2202. */
  2203. function testHasAnyTag() {
  2204. $query = db_select('test');
  2205. $query->addField('test', 'name');
  2206. $query->addField('test', 'age', 'age');
  2207. $query->addTag('test');
  2208. $this->assertTrue($query->hasAnyTag('test', 'other'), 'hasAnyTag() returned true.');
  2209. $this->assertFalse($query->hasAnyTag('other', 'stuff'), 'hasAnyTag() returned false.');
  2210. }
  2211. /**
  2212. * Test that we can attach meta data to a query object.
  2213. *
  2214. * This is how we pass additional context to alter hooks.
  2215. */
  2216. function testMetaData() {
  2217. $query = db_select('test');
  2218. $query->addField('test', 'name');
  2219. $query->addField('test', 'age', 'age');
  2220. $data = array(
  2221. 'a' => 'A',
  2222. 'b' => 'B',
  2223. );
  2224. $query->addMetaData('test', $data);
  2225. $return = $query->getMetaData('test');
  2226. $this->assertEqual($data, $return, 'Corect metadata returned.');
  2227. $return = $query->getMetaData('nothere');
  2228. $this->assertNull($return, 'Non-existent key returned NULL.');
  2229. }
  2230. }
  2231. /**
  2232. * Select alter tests.
  2233. *
  2234. * @see database_test_query_alter()
  2235. */
  2236. class DatabaseAlterTestCase extends DatabaseTestCase {
  2237. public static function getInfo() {
  2238. return array(
  2239. 'name' => 'Query altering tests',
  2240. 'description' => 'Test the hook_query_alter capabilities of the Select builder.',
  2241. 'group' => 'Database',
  2242. );
  2243. }
  2244. /**
  2245. * Test that we can do basic alters.
  2246. */
  2247. function testSimpleAlter() {
  2248. $query = db_select('test');
  2249. $query->addField('test', 'name');
  2250. $query->addField('test', 'age', 'age');
  2251. $query->addTag('database_test_alter_add_range');
  2252. $result = $query->execute();
  2253. $num_records = 0;
  2254. foreach ($result as $record) {
  2255. $num_records++;
  2256. }
  2257. $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  2258. }
  2259. /**
  2260. * Test that we can alter the joins on a query.
  2261. */
  2262. function testAlterWithJoin() {
  2263. $query = db_select('test_task');
  2264. $tid_field = $query->addField('test_task', 'tid');
  2265. $task_field = $query->addField('test_task', 'task');
  2266. $query->orderBy($task_field);
  2267. $query->addTag('database_test_alter_add_join');
  2268. $result = $query->execute();
  2269. $records = $result->fetchAll();
  2270. $this->assertEqual(count($records), 2, 'Returned the correct number of rows.');
  2271. $this->assertEqual($records[0]->name, 'George', 'Correct data retrieved.');
  2272. $this->assertEqual($records[0]->$tid_field, 4, 'Correct data retrieved.');
  2273. $this->assertEqual($records[0]->$task_field, 'sing', 'Correct data retrieved.');
  2274. $this->assertEqual($records[1]->name, 'George', 'Correct data retrieved.');
  2275. $this->assertEqual($records[1]->$tid_field, 5, 'Correct data retrieved.');
  2276. $this->assertEqual($records[1]->$task_field, 'sleep', 'Correct data retrieved.');
  2277. }
  2278. /**
  2279. * Test that we can alter a query's conditionals.
  2280. */
  2281. function testAlterChangeConditional() {
  2282. $query = db_select('test_task');
  2283. $tid_field = $query->addField('test_task', 'tid');
  2284. $pid_field = $query->addField('test_task', 'pid');
  2285. $task_field = $query->addField('test_task', 'task');
  2286. $people_alias = $query->join('test', 'people', "test_task.pid = people.id");
  2287. $name_field = $query->addField($people_alias, 'name', 'name');
  2288. $query->condition('test_task.tid', '1');
  2289. $query->orderBy($tid_field);
  2290. $query->addTag('database_test_alter_change_conditional');
  2291. $result = $query->execute();
  2292. $records = $result->fetchAll();
  2293. $this->assertEqual(count($records), 1, 'Returned the correct number of rows.');
  2294. $this->assertEqual($records[0]->$name_field, 'John', 'Correct data retrieved.');
  2295. $this->assertEqual($records[0]->$tid_field, 2, 'Correct data retrieved.');
  2296. $this->assertEqual($records[0]->$pid_field, 1, 'Correct data retrieved.');
  2297. $this->assertEqual($records[0]->$task_field, 'sleep', 'Correct data retrieved.');
  2298. }
  2299. /**
  2300. * Test that we can alter the fields of a query.
  2301. */
  2302. function testAlterChangeFields() {
  2303. $query = db_select('test');
  2304. $name_field = $query->addField('test', 'name');
  2305. $age_field = $query->addField('test', 'age', 'age');
  2306. $query->orderBy('name');
  2307. $query->addTag('database_test_alter_change_fields');
  2308. $record = $query->execute()->fetch();
  2309. $this->assertEqual($record->$name_field, 'George', 'Correct data retrieved.');
  2310. $this->assertFalse(isset($record->$age_field), 'Age field not found, as intended.');
  2311. }
  2312. /**
  2313. * Test that we can alter expressions in the query.
  2314. */
  2315. function testAlterExpression() {
  2316. $query = db_select('test');
  2317. $name_field = $query->addField('test', 'name');
  2318. $age_field = $query->addExpression("age*2", 'double_age');
  2319. $query->condition('age', 27);
  2320. $query->addTag('database_test_alter_change_expressions');
  2321. $result = $query->execute();
  2322. // Ensure that we got the right record.
  2323. $record = $result->fetch();
  2324. $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
  2325. $this->assertEqual($record->$age_field, 27*3, 'Fetched age expression is correct.');
  2326. }
  2327. /**
  2328. * Test that we can remove a range() value from a query. This also tests hook_query_TAG_alter().
  2329. */
  2330. function testAlterRemoveRange() {
  2331. $query = db_select('test');
  2332. $query->addField('test', 'name');
  2333. $query->addField('test', 'age', 'age');
  2334. $query->range(0, 2);
  2335. $query->addTag('database_test_alter_remove_range');
  2336. $num_records = count($query->execute()->fetchAll());
  2337. $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  2338. }
  2339. /**
  2340. * Test that we can do basic alters on subqueries.
  2341. */
  2342. function testSimpleAlterSubquery() {
  2343. // Create a sub-query with an alter tag.
  2344. $subquery = db_select('test', 'p');
  2345. $subquery->addField('p', 'name');
  2346. $subquery->addField('p', 'id');
  2347. // Pick out George.
  2348. $subquery->condition('age', 27);
  2349. $subquery->addExpression("age*2", 'double_age');
  2350. // This query alter should change it to age * 3.
  2351. $subquery->addTag('database_test_alter_change_expressions');
  2352. // Create a main query and join to sub-query.
  2353. $query = db_select('test_task', 'tt');
  2354. $query->join($subquery, 'pq', 'pq.id = tt.pid');
  2355. $age_field = $query->addField('pq', 'double_age');
  2356. $name_field = $query->addField('pq', 'name');
  2357. $record = $query->execute()->fetch();
  2358. $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
  2359. $this->assertEqual($record->$age_field, 27*3, 'Fetched age expression is correct.');
  2360. }
  2361. }
  2362. /**
  2363. * Regression tests.
  2364. */
  2365. class DatabaseRegressionTestCase extends DatabaseTestCase {
  2366. public static function getInfo() {
  2367. return array(
  2368. 'name' => 'Regression tests',
  2369. 'description' => 'Regression tests cases for the database layer.',
  2370. 'group' => 'Database',
  2371. );
  2372. }
  2373. /**
  2374. * Regression test for #310447.
  2375. *
  2376. * Tries to insert non-ascii UTF-8 data in a database column and checks
  2377. * if its stored properly.
  2378. */
  2379. function testRegression_310447() {
  2380. // That's a 255 character UTF-8 string.
  2381. $name = str_repeat("é", 255);
  2382. db_insert('test')
  2383. ->fields(array(
  2384. 'name' => $name,
  2385. 'age' => 20,
  2386. 'job' => 'Dancer',
  2387. ))->execute();
  2388. $from_database = db_query('SELECT name FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
  2389. $this->assertIdentical($name, $from_database, "The database handles UTF-8 characters cleanly.");
  2390. }
  2391. /**
  2392. * Test the db_table_exists() function.
  2393. */
  2394. function testDBTableExists() {
  2395. $this->assertIdentical(TRUE, db_table_exists('node'), 'Returns true for existent table.');
  2396. $this->assertIdentical(FALSE, db_table_exists('nosuchtable'), 'Returns false for nonexistent table.');
  2397. }
  2398. /**
  2399. * Test the db_field_exists() function.
  2400. */
  2401. function testDBFieldExists() {
  2402. $this->assertIdentical(TRUE, db_field_exists('node', 'nid'), 'Returns true for existent column.');
  2403. $this->assertIdentical(FALSE, db_field_exists('node', 'nosuchcolumn'), 'Returns false for nonexistent column.');
  2404. }
  2405. /**
  2406. * Test the db_index_exists() function.
  2407. */
  2408. function testDBIndexExists() {
  2409. $this->assertIdentical(TRUE, db_index_exists('node', 'node_created'), 'Returns true for existent index.');
  2410. $this->assertIdentical(FALSE, db_index_exists('node', 'nosuchindex'), 'Returns false for nonexistent index.');
  2411. }
  2412. }
  2413. /**
  2414. * Query logging tests.
  2415. */
  2416. class DatabaseLoggingTestCase extends DatabaseTestCase {
  2417. public static function getInfo() {
  2418. return array(
  2419. 'name' => 'Query logging',
  2420. 'description' => 'Test the query logging facility.',
  2421. 'group' => 'Database',
  2422. );
  2423. }
  2424. /**
  2425. * Test that we can log the existence of a query.
  2426. */
  2427. function testEnableLogging() {
  2428. $log = Database::startLog('testing');
  2429. db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
  2430. db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchCol();
  2431. // Trigger a call that does not have file in the backtrace.
  2432. call_user_func_array('db_query', array('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo')))->fetchCol();
  2433. $queries = Database::getLog('testing', 'default');
  2434. $this->assertEqual(count($queries), 3, 'Correct number of queries recorded.');
  2435. foreach ($queries as $query) {
  2436. $this->assertEqual($query['caller']['function'], __FUNCTION__, 'Correct function in query log.');
  2437. }
  2438. }
  2439. /**
  2440. * Test that we can run two logs in parallel.
  2441. */
  2442. function testEnableMultiLogging() {
  2443. Database::startLog('testing1');
  2444. db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
  2445. Database::startLog('testing2');
  2446. db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchCol();
  2447. $queries1 = Database::getLog('testing1');
  2448. $queries2 = Database::getLog('testing2');
  2449. $this->assertEqual(count($queries1), 2, 'Correct number of queries recorded for log 1.');
  2450. $this->assertEqual(count($queries2), 1, 'Correct number of queries recorded for log 2.');
  2451. }
  2452. /**
  2453. * Test that we can log queries against multiple targets on the same connection.
  2454. */
  2455. function testEnableTargetLogging() {
  2456. // Clone the master credentials to a slave connection and to another fake
  2457. // connection.
  2458. $connection_info = Database::getConnectionInfo('default');
  2459. Database::addConnectionInfo('default', 'slave', $connection_info['default']);
  2460. Database::startLog('testing1');
  2461. db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
  2462. db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'slave'));//->fetchCol();
  2463. $queries1 = Database::getLog('testing1');
  2464. $this->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
  2465. $this->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
  2466. $this->assertEqual($queries1[1]['target'], 'slave', 'Second query used slave target.');
  2467. }
  2468. /**
  2469. * Test that logs to separate targets collapse to the same connection properly.
  2470. *
  2471. * This test is identical to the one above, except that it doesn't create
  2472. * a fake target so the query should fall back to running on the default
  2473. * target.
  2474. */
  2475. function testEnableTargetLoggingNoTarget() {
  2476. Database::startLog('testing1');
  2477. db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
  2478. // We use "fake" here as a target because any non-existent target will do.
  2479. // However, because all of the tests in this class share a single page
  2480. // request there is likely to be a target of "slave" from one of the other
  2481. // unit tests, so we use a target here that we know with absolute certainty
  2482. // does not exist.
  2483. db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'fake'))->fetchCol();
  2484. $queries1 = Database::getLog('testing1');
  2485. $this->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
  2486. $this->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
  2487. $this->assertEqual($queries1[1]['target'], 'default', 'Second query used default target as fallback.');
  2488. }
  2489. /**
  2490. * Test that we can log queries separately on different connections.
  2491. */
  2492. function testEnableMultiConnectionLogging() {
  2493. // Clone the master credentials to a fake connection.
  2494. // That both connections point to the same physical database is irrelevant.
  2495. $connection_info = Database::getConnectionInfo('default');
  2496. Database::addConnectionInfo('test2', 'default', $connection_info['default']);
  2497. Database::startLog('testing1');
  2498. Database::startLog('testing1', 'test2');
  2499. db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
  2500. $old_key = db_set_active('test2');
  2501. db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'slave'))->fetchCol();
  2502. db_set_active($old_key);
  2503. $queries1 = Database::getLog('testing1');
  2504. $queries2 = Database::getLog('testing1', 'test2');
  2505. $this->assertEqual(count($queries1), 1, 'Correct number of queries recorded for first connection.');
  2506. $this->assertEqual(count($queries2), 1, 'Correct number of queries recorded for second connection.');
  2507. }
  2508. }
  2509. /**
  2510. * Query serialization tests.
  2511. */
  2512. class DatabaseSerializeQueryTestCase extends DatabaseTestCase {
  2513. public static function getInfo() {
  2514. return array(
  2515. 'name' => 'Serialize query',
  2516. 'description' => 'Test serializing and unserializing a query.',
  2517. 'group' => 'Database',
  2518. );
  2519. }
  2520. /**
  2521. * Confirm that a query can be serialized and unserialized.
  2522. */
  2523. function testSerializeQuery() {
  2524. $query = db_select('test');
  2525. $query->addField('test', 'age');
  2526. $query->condition('name', 'Ringo');
  2527. // If this doesn't work, it will throw an exception, so no need for an
  2528. // assertion.
  2529. $query = unserialize(serialize($query));
  2530. $results = $query->execute()->fetchCol();
  2531. $this->assertEqual($results[0], 28, 'Query properly executed after unserialization.');
  2532. }
  2533. }
  2534. /**
  2535. * Range query tests.
  2536. */
  2537. class DatabaseRangeQueryTestCase extends DrupalWebTestCase {
  2538. public static function getInfo() {
  2539. return array(
  2540. 'name' => 'Range query test',
  2541. 'description' => 'Test the Range query functionality.',
  2542. 'group' => 'Database',
  2543. );
  2544. }
  2545. function setUp() {
  2546. parent::setUp('database_test');
  2547. }
  2548. /**
  2549. * Confirm that range query work and return correct result.
  2550. */
  2551. function testRangeQuery() {
  2552. // Test if return correct number of rows.
  2553. $range_rows = db_query_range("SELECT name FROM {system} ORDER BY name", 2, 3)->fetchAll();
  2554. $this->assertEqual(count($range_rows), 3, 'Range query work and return correct number of rows.');
  2555. // Test if return target data.
  2556. $raw_rows = db_query('SELECT name FROM {system} ORDER BY name')->fetchAll();
  2557. $raw_rows = array_slice($raw_rows, 2, 3);
  2558. $this->assertEqual($range_rows, $raw_rows, 'Range query work and return target data.');
  2559. }
  2560. }
  2561. /**
  2562. * Temporary query tests.
  2563. */
  2564. class DatabaseTemporaryQueryTestCase extends DrupalWebTestCase {
  2565. public static function getInfo() {
  2566. return array(
  2567. 'name' => 'Temporary query test',
  2568. 'description' => 'Test the temporary query functionality.',
  2569. 'group' => 'Database',
  2570. );
  2571. }
  2572. function setUp() {
  2573. parent::setUp('database_test');
  2574. }
  2575. /**
  2576. * Return the number of rows of a table.
  2577. */
  2578. function countTableRows($table_name) {
  2579. return db_select($table_name)->countQuery()->execute()->fetchField();
  2580. }
  2581. /**
  2582. * Confirm that temporary tables work and are limited to one request.
  2583. */
  2584. function testTemporaryQuery() {
  2585. $this->drupalGet('database_test/db_query_temporary');
  2586. $data = json_decode($this->drupalGetContent());
  2587. if ($data) {
  2588. $this->assertEqual($this->countTableRows("system"), $data->row_count, 'The temporary table contains the correct amount of rows.');
  2589. $this->assertFalse(db_table_exists($data->table_name), 'The temporary table is, indeed, temporary.');
  2590. }
  2591. else {
  2592. $this->fail("The creation of the temporary table failed.");
  2593. }
  2594. // Now try to run two db_query_temporary() in the same request.
  2595. $table_name_system = db_query_temporary('SELECT status FROM {system}', array());
  2596. $table_name_users = db_query_temporary('SELECT uid FROM {users}', array());
  2597. $this->assertEqual($this->countTableRows($table_name_system), $this->countTableRows("system"), 'A temporary table was created successfully in this request.');
  2598. $this->assertEqual($this->countTableRows($table_name_users), $this->countTableRows("users"), 'A second temporary table was created successfully in this request.');
  2599. }
  2600. }
  2601. /**
  2602. * Test how the current database driver interprets the SQL syntax.
  2603. *
  2604. * In order to ensure consistent SQL handling throughout Drupal
  2605. * across multiple kinds of database systems, we test that the
  2606. * database system interprets SQL syntax in an expected fashion.
  2607. */
  2608. class DatabaseBasicSyntaxTestCase extends DatabaseTestCase {
  2609. public static function getInfo() {
  2610. return array(
  2611. 'name' => 'Basic SQL syntax tests',
  2612. 'description' => 'Test SQL syntax interpretation.',
  2613. 'group' => 'Database',
  2614. );
  2615. }
  2616. function setUp() {
  2617. parent::setUp('database_test');
  2618. }
  2619. /**
  2620. * Test for string concatenation.
  2621. */
  2622. function testBasicConcat() {
  2623. $result = db_query('SELECT CONCAT(:a1, CONCAT(:a2, CONCAT(:a3, CONCAT(:a4, :a5))))', array(
  2624. ':a1' => 'This',
  2625. ':a2' => ' ',
  2626. ':a3' => 'is',
  2627. ':a4' => ' a ',
  2628. ':a5' => 'test.',
  2629. ));
  2630. $this->assertIdentical($result->fetchField(), 'This is a test.', 'Basic CONCAT works.');
  2631. }
  2632. /**
  2633. * Test for string concatenation with field values.
  2634. */
  2635. function testFieldConcat() {
  2636. $result = db_query('SELECT CONCAT(:a1, CONCAT(name, CONCAT(:a2, CONCAT(age, :a3)))) FROM {test} WHERE age = :age', array(
  2637. ':a1' => 'The age of ',
  2638. ':a2' => ' is ',
  2639. ':a3' => '.',
  2640. ':age' => 25,
  2641. ));
  2642. $this->assertIdentical($result->fetchField(), 'The age of John is 25.', 'Field CONCAT works.');
  2643. }
  2644. /**
  2645. * Test escaping of LIKE wildcards.
  2646. */
  2647. function testLikeEscape() {
  2648. db_insert('test')
  2649. ->fields(array(
  2650. 'name' => 'Ring_',
  2651. ))
  2652. ->execute();
  2653. // Match both "Ringo" and "Ring_".
  2654. $num_matches = db_select('test', 't')
  2655. ->condition('name', 'Ring_', 'LIKE')
  2656. ->countQuery()
  2657. ->execute()
  2658. ->fetchField();
  2659. $this->assertIdentical($num_matches, '2', 'Found 2 records.');
  2660. // Match only "Ring_" using a LIKE expression with no wildcards.
  2661. $num_matches = db_select('test', 't')
  2662. ->condition('name', db_like('Ring_'), 'LIKE')
  2663. ->countQuery()
  2664. ->execute()
  2665. ->fetchField();
  2666. $this->assertIdentical($num_matches, '1', 'Found 1 record.');
  2667. }
  2668. /**
  2669. * Test LIKE query containing a backslash.
  2670. */
  2671. function testLikeBackslash() {
  2672. db_insert('test')
  2673. ->fields(array('name'))
  2674. ->values(array(
  2675. 'name' => 'abcde\f',
  2676. ))
  2677. ->values(array(
  2678. 'name' => 'abc%\_',
  2679. ))
  2680. ->execute();
  2681. // Match both rows using a LIKE expression with two wildcards and a verbatim
  2682. // backslash.
  2683. $num_matches = db_select('test', 't')
  2684. ->condition('name', 'abc%\\\\_', 'LIKE')
  2685. ->countQuery()
  2686. ->execute()
  2687. ->fetchField();
  2688. $this->assertIdentical($num_matches, '2', 'Found 2 records.');
  2689. // Match only the former using a LIKE expression with no wildcards.
  2690. $num_matches = db_select('test', 't')
  2691. ->condition('name', db_like('abc%\_'), 'LIKE')
  2692. ->countQuery()
  2693. ->execute()
  2694. ->fetchField();
  2695. $this->assertIdentical($num_matches, '1', 'Found 1 record.');
  2696. }
  2697. }
  2698. /**
  2699. * Test case sensitivity handling.
  2700. */
  2701. class DatabaseCaseSensitivityTestCase extends DatabaseTestCase {
  2702. public static function getInfo() {
  2703. return array(
  2704. 'name' => 'Case sensitivity',
  2705. 'description' => 'Test handling case sensitive collation.',
  2706. 'group' => 'Database',
  2707. );
  2708. }
  2709. /**
  2710. * Test BINARY collation in MySQL.
  2711. */
  2712. function testCaseSensitiveInsert() {
  2713. $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  2714. $john = db_insert('test')
  2715. ->fields(array(
  2716. 'name' => 'john', // <- A record already exists with name 'John'.
  2717. 'age' => 2,
  2718. 'job' => 'Baby',
  2719. ))
  2720. ->execute();
  2721. $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
  2722. $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
  2723. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'john'))->fetchField();
  2724. $this->assertIdentical($saved_age, '2', 'Can retrieve after inserting.');
  2725. }
  2726. }
  2727. /**
  2728. * Test invalid data handling.
  2729. */
  2730. class DatabaseInvalidDataTestCase extends DatabaseTestCase {
  2731. public static function getInfo() {
  2732. return array(
  2733. 'name' => 'Invalid data',
  2734. 'description' => 'Test handling of some invalid data.',
  2735. 'group' => 'Database',
  2736. );
  2737. }
  2738. function setUp() {
  2739. parent::setUp('database_test');
  2740. }
  2741. /**
  2742. * Traditional SQL database systems abort inserts when invalid data is encountered.
  2743. */
  2744. function testInsertDuplicateData() {
  2745. // Try to insert multiple records where at least one has bad data.
  2746. try {
  2747. db_insert('test')
  2748. ->fields(array('name', 'age', 'job'))
  2749. ->values(array(
  2750. 'name' => 'Elvis',
  2751. 'age' => 63,
  2752. 'job' => 'Singer',
  2753. ))->values(array(
  2754. 'name' => 'John', // <-- Duplicate value on unique field.
  2755. 'age' => 17,
  2756. 'job' => 'Consultant',
  2757. ))
  2758. ->values(array(
  2759. 'name' => 'Frank',
  2760. 'age' => 75,
  2761. 'job' => 'Singer',
  2762. ))
  2763. ->execute();
  2764. $this->fail('Insert succeedded when it should not have.');
  2765. }
  2766. catch (Exception $e) {
  2767. // Check if the first record was inserted.
  2768. $name = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 63))->fetchField();
  2769. if ($name == 'Elvis') {
  2770. if (!Database::getConnection()->supportsTransactions()) {
  2771. // This is an expected fail.
  2772. // Database engines that don't support transactions can leave partial
  2773. // inserts in place when an error occurs. This is the case for MySQL
  2774. // when running on a MyISAM table.
  2775. $this->pass("The whole transaction has not been rolled-back when a duplicate key insert occurs, this is expected because the database doesn't support transactions");
  2776. }
  2777. else {
  2778. $this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
  2779. }
  2780. }
  2781. else {
  2782. $this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
  2783. }
  2784. // Ensure the other values were not inserted.
  2785. $record = db_select('test')
  2786. ->fields('test', array('name', 'age'))
  2787. ->condition('age', array(17, 75), 'IN')
  2788. ->execute()->fetchObject();
  2789. $this->assertFalse($record, 'The rest of the insert aborted as expected.');
  2790. }
  2791. }
  2792. }
  2793. /**
  2794. * Drupal-specific SQL syntax tests.
  2795. */
  2796. class DatabaseQueryTestCase extends DatabaseTestCase {
  2797. public static function getInfo() {
  2798. return array(
  2799. 'name' => 'Custom query syntax tests',
  2800. 'description' => 'Test Drupal\'s extended prepared statement syntax..',
  2801. 'group' => 'Database',
  2802. );
  2803. }
  2804. function setUp() {
  2805. parent::setUp('database_test');
  2806. }
  2807. /**
  2808. * Test that we can specify an array of values in the query by simply passing in an array.
  2809. */
  2810. function testArraySubstitution() {
  2811. $names = db_query('SELECT name FROM {test} WHERE age IN (:ages) ORDER BY age', array(':ages' => array(25, 26, 27)))->fetchAll();
  2812. $this->assertEqual(count($names), 3, 'Correct number of names returned');
  2813. }
  2814. }
  2815. /**
  2816. * Test transaction support, particularly nesting.
  2817. *
  2818. * We test nesting by having two transaction layers, an outer and inner. The
  2819. * outer layer encapsulates the inner layer. Our transaction nesting abstraction
  2820. * should allow the outer layer function to call any function it wants,
  2821. * especially the inner layer that starts its own transaction, and be
  2822. * confident that, when the function it calls returns, its own transaction
  2823. * is still "alive."
  2824. *
  2825. * Call structure:
  2826. * transactionOuterLayer()
  2827. * Start transaction
  2828. * transactionInnerLayer()
  2829. * Start transaction (does nothing in database)
  2830. * [Maybe decide to roll back]
  2831. * Do more stuff
  2832. * Should still be in transaction A
  2833. *
  2834. */
  2835. class DatabaseTransactionTestCase extends DatabaseTestCase {
  2836. public static function getInfo() {
  2837. return array(
  2838. 'name' => 'Transaction tests',
  2839. 'description' => 'Test the transaction abstraction system.',
  2840. 'group' => 'Database',
  2841. );
  2842. }
  2843. /**
  2844. * Helper method for transaction unit test. This "outer layer" transaction
  2845. * starts and then encapsulates the "inner layer" transaction. This nesting
  2846. * is used to evaluate whether the the database transaction API properly
  2847. * supports nesting. By "properly supports," we mean the outer transaction
  2848. * continues to exist regardless of what functions are called and whether
  2849. * those functions start their own transactions.
  2850. *
  2851. * In contrast, a typical database would commit the outer transaction, start
  2852. * a new transaction for the inner layer, commit the inner layer transaction,
  2853. * and then be confused when the outer layer transaction tries to commit its
  2854. * transaction (which was already committed when the inner transaction
  2855. * started).
  2856. *
  2857. * @param $suffix
  2858. * Suffix to add to field values to differentiate tests.
  2859. * @param $rollback
  2860. * Whether or not to try rolling back the transaction when we're done.
  2861. * @param $ddl_statement
  2862. * Whether to execute a DDL statement during the inner transaction.
  2863. */
  2864. protected function transactionOuterLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
  2865. $connection = Database::getConnection();
  2866. $depth = $connection->transactionDepth();
  2867. $txn = db_transaction();
  2868. // Insert a single row into the testing table.
  2869. db_insert('test')
  2870. ->fields(array(
  2871. 'name' => 'David' . $suffix,
  2872. 'age' => '24',
  2873. ))
  2874. ->execute();
  2875. $this->assertTrue($connection->inTransaction(), 'In transaction before calling nested transaction.');
  2876. // We're already in a transaction, but we call ->transactionInnerLayer
  2877. // to nest another transaction inside the current one.
  2878. $this->transactionInnerLayer($suffix, $rollback, $ddl_statement);
  2879. $this->assertTrue($connection->inTransaction(), 'In transaction after calling nested transaction.');
  2880. if ($rollback) {
  2881. // Roll back the transaction, if requested.
  2882. // This rollback should propagate to the last savepoint.
  2883. $txn->rollback();
  2884. $this->assertTrue(($connection->transactionDepth() == $depth), 'Transaction has rolled back to the last savepoint after calling rollback().');
  2885. }
  2886. }
  2887. /**
  2888. * Helper method for transaction unit tests. This "inner layer" transaction
  2889. * is either used alone or nested inside of the "outer layer" transaction.
  2890. *
  2891. * @param $suffix
  2892. * Suffix to add to field values to differentiate tests.
  2893. * @param $rollback
  2894. * Whether or not to try rolling back the transaction when we're done.
  2895. * @param $ddl_statement
  2896. * Whether to execute a DDL statement during the transaction.
  2897. */
  2898. protected function transactionInnerLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
  2899. $connection = Database::getConnection();
  2900. $depth = $connection->transactionDepth();
  2901. // Start a transaction. If we're being called from ->transactionOuterLayer,
  2902. // then we're already in a transaction. Normally, that would make starting
  2903. // a transaction here dangerous, but the database API handles this problem
  2904. // for us by tracking the nesting and avoiding the danger.
  2905. $txn = db_transaction();
  2906. $depth2 = $connection->transactionDepth();
  2907. $this->assertTrue($depth < $depth2, 'Transaction depth is has increased with new transaction.');
  2908. // Insert a single row into the testing table.
  2909. db_insert('test')
  2910. ->fields(array(
  2911. 'name' => 'Daniel' . $suffix,
  2912. 'age' => '19',
  2913. ))
  2914. ->execute();
  2915. $this->assertTrue($connection->inTransaction(), 'In transaction inside nested transaction.');
  2916. if ($ddl_statement) {
  2917. $table = array(
  2918. 'fields' => array(
  2919. 'id' => array(
  2920. 'type' => 'serial',
  2921. 'unsigned' => TRUE,
  2922. 'not null' => TRUE,
  2923. ),
  2924. ),
  2925. 'primary key' => array('id'),
  2926. );
  2927. db_create_table('database_test_1', $table);
  2928. $this->assertTrue($connection->inTransaction(), 'In transaction inside nested transaction.');
  2929. }
  2930. if ($rollback) {
  2931. // Roll back the transaction, if requested.
  2932. // This rollback should propagate to the last savepoint.
  2933. $txn->rollback();
  2934. $this->assertTrue(($connection->transactionDepth() == $depth), 'Transaction has rolled back to the last savepoint after calling rollback().');
  2935. }
  2936. }
  2937. /**
  2938. * Test transaction rollback on a database that supports transactions.
  2939. *
  2940. * If the active connection does not support transactions, this test does nothing.
  2941. */
  2942. function testTransactionRollBackSupported() {
  2943. // This test won't work right if transactions are not supported.
  2944. if (!Database::getConnection()->supportsTransactions()) {
  2945. return;
  2946. }
  2947. try {
  2948. // Create two nested transactions. Roll back from the inner one.
  2949. $this->transactionOuterLayer('B', TRUE);
  2950. // Neither of the rows we inserted in the two transaction layers
  2951. // should be present in the tables post-rollback.
  2952. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidB'))->fetchField();
  2953. $this->assertNotIdentical($saved_age, '24', 'Cannot retrieve DavidB row after commit.');
  2954. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielB'))->fetchField();
  2955. $this->assertNotIdentical($saved_age, '19', 'Cannot retrieve DanielB row after commit.');
  2956. }
  2957. catch (Exception $e) {
  2958. $this->fail($e->getMessage());
  2959. }
  2960. }
  2961. /**
  2962. * Test transaction rollback on a database that does not support transactions.
  2963. *
  2964. * If the active driver supports transactions, this test does nothing.
  2965. */
  2966. function testTransactionRollBackNotSupported() {
  2967. // This test won't work right if transactions are supported.
  2968. if (Database::getConnection()->supportsTransactions()) {
  2969. return;
  2970. }
  2971. try {
  2972. // Create two nested transactions. Attempt to roll back from the inner one.
  2973. $this->transactionOuterLayer('B', TRUE);
  2974. // Because our current database claims to not support transactions,
  2975. // the inserted rows should be present despite the attempt to roll back.
  2976. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidB'))->fetchField();
  2977. $this->assertIdentical($saved_age, '24', 'DavidB not rolled back, since transactions are not supported.');
  2978. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielB'))->fetchField();
  2979. $this->assertIdentical($saved_age, '19', 'DanielB not rolled back, since transactions are not supported.');
  2980. }
  2981. catch (Exception $e) {
  2982. $this->fail($e->getMessage());
  2983. }
  2984. }
  2985. /**
  2986. * Test committed transaction.
  2987. *
  2988. * The behavior of this test should be identical for connections that support
  2989. * transactions and those that do not.
  2990. */
  2991. function testCommittedTransaction() {
  2992. try {
  2993. // Create two nested transactions. The changes should be committed.
  2994. $this->transactionOuterLayer('A');
  2995. // Because we committed, both of the inserted rows should be present.
  2996. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidA'))->fetchField();
  2997. $this->assertIdentical($saved_age, '24', 'Can retrieve DavidA row after commit.');
  2998. $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielA'))->fetchField();
  2999. $this->assertIdentical($saved_age, '19', 'Can retrieve DanielA row after commit.');
  3000. }
  3001. catch (Exception $e) {
  3002. $this->fail($e->getMessage());
  3003. }
  3004. }
  3005. /**
  3006. * Test the compatibility of transactions with DDL statements.
  3007. */
  3008. function testTransactionWithDdlStatement() {
  3009. // First, test that a commit works normally, even with DDL statements.
  3010. $transaction = db_transaction();
  3011. $this->insertRow('row');
  3012. $this->executeDDLStatement();
  3013. unset($transaction);
  3014. $this->assertRowPresent('row');
  3015. // Even in different order.
  3016. $this->cleanUp();
  3017. $transaction = db_transaction();
  3018. $this->executeDDLStatement();
  3019. $this->insertRow('row');
  3020. unset($transaction);
  3021. $this->assertRowPresent('row');
  3022. // Even with stacking.
  3023. $this->cleanUp();
  3024. $transaction = db_transaction();
  3025. $transaction2 = db_transaction();
  3026. $this->executeDDLStatement();
  3027. unset($transaction2);
  3028. $transaction3 = db_transaction();
  3029. $this->insertRow('row');
  3030. unset($transaction3);
  3031. unset($transaction);
  3032. $this->assertRowPresent('row');
  3033. // A transaction after a DDL statement should still work the same.
  3034. $this->cleanUp();
  3035. $transaction = db_transaction();
  3036. $transaction2 = db_transaction();
  3037. $this->executeDDLStatement();
  3038. unset($transaction2);
  3039. $transaction3 = db_transaction();
  3040. $this->insertRow('row');
  3041. $transaction3->rollback();
  3042. unset($transaction3);
  3043. unset($transaction);
  3044. $this->assertRowAbsent('row');
  3045. // The behavior of a rollback depends on the type of database server.
  3046. if (Database::getConnection()->supportsTransactionalDDL()) {
  3047. // For database servers that support transactional DDL, a rollback
  3048. // of a transaction including DDL statements should be possible.
  3049. $this->cleanUp();
  3050. $transaction = db_transaction();
  3051. $this->insertRow('row');
  3052. $this->executeDDLStatement();
  3053. $transaction->rollback();
  3054. unset($transaction);
  3055. $this->assertRowAbsent('row');
  3056. // Including with stacking.
  3057. $this->cleanUp();
  3058. $transaction = db_transaction();
  3059. $transaction2 = db_transaction();
  3060. $this->executeDDLStatement();
  3061. unset($transaction2);
  3062. $transaction3 = db_transaction();
  3063. $this->insertRow('row');
  3064. unset($transaction3);
  3065. $transaction->rollback();
  3066. unset($transaction);
  3067. $this->assertRowAbsent('row');
  3068. }
  3069. else {
  3070. // For database servers that do not support transactional DDL,
  3071. // the DDL statement should commit the transaction stack.
  3072. $this->cleanUp();
  3073. $transaction = db_transaction();
  3074. $this->insertRow('row');
  3075. $this->executeDDLStatement();
  3076. // Rollback the outer transaction.
  3077. try {
  3078. $transaction->rollback();
  3079. unset($transaction);
  3080. // @TODO: an exception should be triggered here, but is not, because
  3081. // "ROLLBACK" fails silently in MySQL if there is no transaction active.
  3082. // $this->fail(t('Rolling back a transaction containing DDL should fail.'));
  3083. }
  3084. catch (DatabaseTransactionNoActiveException $e) {
  3085. $this->pass('Rolling back a transaction containing DDL should fail.');
  3086. }
  3087. $this->assertRowPresent('row');
  3088. }
  3089. }
  3090. /**
  3091. * Insert a single row into the testing table.
  3092. */
  3093. protected function insertRow($name) {
  3094. db_insert('test')
  3095. ->fields(array(
  3096. 'name' => $name,
  3097. ))
  3098. ->execute();
  3099. }
  3100. /**
  3101. * Execute a DDL statement.
  3102. */
  3103. protected function executeDDLStatement() {
  3104. static $count = 0;
  3105. $table = array(
  3106. 'fields' => array(
  3107. 'id' => array(
  3108. 'type' => 'serial',
  3109. 'unsigned' => TRUE,
  3110. 'not null' => TRUE,
  3111. ),
  3112. ),
  3113. 'primary key' => array('id'),
  3114. );
  3115. db_create_table('database_test_' . ++$count, $table);
  3116. }
  3117. /**
  3118. * Start over for a new test.
  3119. */
  3120. protected function cleanUp() {
  3121. db_truncate('test')
  3122. ->execute();
  3123. }
  3124. /**
  3125. * Assert that a given row is present in the test table.
  3126. *
  3127. * @param $name
  3128. * The name of the row.
  3129. * @param $message
  3130. * The message to log for the assertion.
  3131. */
  3132. function assertRowPresent($name, $message = NULL) {
  3133. if (!isset($message)) {
  3134. $message = format_string('Row %name is present.', array('%name' => $name));
  3135. }
  3136. $present = (boolean) db_query('SELECT 1 FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
  3137. return $this->assertTrue($present, $message);
  3138. }
  3139. /**
  3140. * Assert that a given row is absent from the test table.
  3141. *
  3142. * @param $name
  3143. * The name of the row.
  3144. * @param $message
  3145. * The message to log for the assertion.
  3146. */
  3147. function assertRowAbsent($name, $message = NULL) {
  3148. if (!isset($message)) {
  3149. $message = format_string('Row %name is absent.', array('%name' => $name));
  3150. }
  3151. $present = (boolean) db_query('SELECT 1 FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
  3152. return $this->assertFalse($present, $message);
  3153. }
  3154. /**
  3155. * Test transaction stacking and commit / rollback.
  3156. */
  3157. function testTransactionStacking() {
  3158. // This test won't work right if transactions are not supported.
  3159. if (!Database::getConnection()->supportsTransactions()) {
  3160. return;
  3161. }
  3162. $database = Database::getConnection();
  3163. // Standard case: pop the inner transaction before the outer transaction.
  3164. $transaction = db_transaction();
  3165. $this->insertRow('outer');
  3166. $transaction2 = db_transaction();
  3167. $this->insertRow('inner');
  3168. // Pop the inner transaction.
  3169. unset($transaction2);
  3170. $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the inner transaction');
  3171. // Pop the outer transaction.
  3172. unset($transaction);
  3173. $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the outer transaction');
  3174. $this->assertRowPresent('outer');
  3175. $this->assertRowPresent('inner');
  3176. // Pop the transaction in a different order they have been pushed.
  3177. $this->cleanUp();
  3178. $transaction = db_transaction();
  3179. $this->insertRow('outer');
  3180. $transaction2 = db_transaction();
  3181. $this->insertRow('inner');
  3182. // Pop the outer transaction, nothing should happen.
  3183. unset($transaction);
  3184. $this->insertRow('inner-after-outer-commit');
  3185. $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
  3186. // Pop the inner transaction, the whole transaction should commit.
  3187. unset($transaction2);
  3188. $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
  3189. $this->assertRowPresent('outer');
  3190. $this->assertRowPresent('inner');
  3191. $this->assertRowPresent('inner-after-outer-commit');
  3192. // Rollback the inner transaction.
  3193. $this->cleanUp();
  3194. $transaction = db_transaction();
  3195. $this->insertRow('outer');
  3196. $transaction2 = db_transaction();
  3197. $this->insertRow('inner');
  3198. // Now rollback the inner transaction.
  3199. $transaction2->rollback();
  3200. unset($transaction2);
  3201. $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
  3202. // Pop the outer transaction, it should commit.
  3203. $this->insertRow('outer-after-inner-rollback');
  3204. unset($transaction);
  3205. $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
  3206. $this->assertRowPresent('outer');
  3207. $this->assertRowAbsent('inner');
  3208. $this->assertRowPresent('outer-after-inner-rollback');
  3209. // Rollback the inner transaction after committing the outer one.
  3210. $this->cleanUp();
  3211. $transaction = db_transaction();
  3212. $this->insertRow('outer');
  3213. $transaction2 = db_transaction();
  3214. $this->insertRow('inner');
  3215. // Pop the outer transaction, nothing should happen.
  3216. unset($transaction);
  3217. $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
  3218. // Now rollback the inner transaction, it should rollback.
  3219. $transaction2->rollback();
  3220. unset($transaction2);
  3221. $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
  3222. $this->assertRowPresent('outer');
  3223. $this->assertRowAbsent('inner');
  3224. // Rollback the outer transaction while the inner transaction is active.
  3225. // In that case, an exception will be triggered because we cannot
  3226. // ensure that the final result will have any meaning.
  3227. $this->cleanUp();
  3228. $transaction = db_transaction();
  3229. $this->insertRow('outer');
  3230. $transaction2 = db_transaction();
  3231. $this->insertRow('inner');
  3232. $transaction3 = db_transaction();
  3233. $this->insertRow('inner2');
  3234. // Rollback the outer transaction.
  3235. try {
  3236. $transaction->rollback();
  3237. unset($transaction);
  3238. $this->fail('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
  3239. }
  3240. catch (DatabaseTransactionOutOfOrderException $e) {
  3241. $this->pass('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
  3242. }
  3243. $this->assertFalse($database->inTransaction(), 'No more in a transaction after rolling back the outer transaction');
  3244. // Try to commit one inner transaction.
  3245. unset($transaction3);
  3246. $this->pass('Trying to commit an inner transaction resulted in an exception.');
  3247. // Try to rollback one inner transaction.
  3248. try {
  3249. $transaction->rollback();
  3250. unset($transaction2);
  3251. $this->fail('Trying to commit an inner transaction resulted in an exception.');
  3252. }
  3253. catch (DatabaseTransactionNoActiveException $e) {
  3254. $this->pass('Trying to commit an inner transaction resulted in an exception.');
  3255. }
  3256. $this->assertRowAbsent('outer');
  3257. $this->assertRowAbsent('inner');
  3258. $this->assertRowAbsent('inner2');
  3259. }
  3260. }
  3261. /**
  3262. * Check the sequences API.
  3263. */
  3264. class DatabaseNextIdCase extends DrupalWebTestCase {
  3265. public static function getInfo() {
  3266. return array(
  3267. 'name' => 'Sequences API',
  3268. 'description' => 'Test the secondary sequences API.',
  3269. 'group' => 'Database',
  3270. );
  3271. }
  3272. /**
  3273. * Test that the sequences API work.
  3274. */
  3275. function testDbNextId() {
  3276. $first = db_next_id();
  3277. $second = db_next_id();
  3278. // We can test for exact increase in here because we know there is no
  3279. // other process operating on these tables -- normally we could only
  3280. // expect $second > $first.
  3281. $this->assertEqual($first + 1, $second, 'The second call from a sequence provides a number increased by one.');
  3282. $result = db_next_id(1000);
  3283. $this->assertEqual($result, 1001, 'Sequence provides a larger number than the existing ID.');
  3284. }
  3285. }
  3286. /**
  3287. * Tests the empty pseudo-statement class.
  3288. */
  3289. class DatabaseEmptyStatementTestCase extends DrupalWebTestCase {
  3290. public static function getInfo() {
  3291. return array(
  3292. 'name' => 'Empty statement',
  3293. 'description' => 'Test the empty pseudo-statement class.',
  3294. 'group' => 'Database',
  3295. );
  3296. }
  3297. /**
  3298. * Test that the empty result set behaves as empty.
  3299. */
  3300. function testEmpty() {
  3301. $result = new DatabaseStatementEmpty();
  3302. $this->assertTrue($result instanceof DatabaseStatementInterface, 'Class implements expected interface');
  3303. $this->assertNull($result->fetchObject(), 'Null result returned.');
  3304. }
  3305. /**
  3306. * Test that the empty result set iterates safely.
  3307. */
  3308. function testEmptyIteration() {
  3309. $result = new DatabaseStatementEmpty();
  3310. foreach ($result as $record) {
  3311. $this->fail('Iterating empty result set should not iterate.');
  3312. return;
  3313. }
  3314. $this->pass('Iterating empty result set skipped iteration.');
  3315. }
  3316. /**
  3317. * Test that the empty result set mass-fetches in an expected way.
  3318. */
  3319. function testEmptyFetchAll() {
  3320. $result = new DatabaseStatementEmpty();
  3321. $this->assertEqual($result->fetchAll(), array(), 'Empty array returned from empty result set.');
  3322. }
  3323. }
  3324. /**
  3325. * Tests management of database connections.
  3326. */
  3327. class ConnectionUnitTest extends DrupalUnitTestCase {
  3328. protected $key;
  3329. protected $target;
  3330. protected $monitor;
  3331. protected $originalCount;
  3332. public static function getInfo() {
  3333. return array(
  3334. 'name' => 'Connection unit tests',
  3335. 'description' => 'Tests management of database connections.',
  3336. 'group' => 'Database',
  3337. );
  3338. }
  3339. function setUp() {
  3340. parent::setUp();
  3341. $this->key = 'default';
  3342. $this->originalTarget = 'default';
  3343. $this->target = 'DatabaseConnectionUnitTest';
  3344. // Determine whether the database driver is MySQL. If it is not, the test
  3345. // methods will not be executed.
  3346. // @todo Make this test driver-agnostic, or find a proper way to skip it.
  3347. // @see http://drupal.org/node/1273478
  3348. $connection_info = Database::getConnectionInfo('default');
  3349. $this->skipTest = (bool) $connection_info['default']['driver'] != 'mysql';
  3350. if ($this->skipTest) {
  3351. // Insert an assertion to prevent Simpletest from interpreting the test
  3352. // as failure.
  3353. $this->pass('This test is only compatible with MySQL.');
  3354. }
  3355. // Create an additional connection to monitor the connections being opened
  3356. // and closed in this test.
  3357. // @see TestBase::changeDatabasePrefix()
  3358. $connection_info = Database::getConnectionInfo('default');
  3359. Database::addConnectionInfo('default', 'monitor', $connection_info['default']);
  3360. global $databases;
  3361. $databases['default']['monitor'] = $connection_info['default'];
  3362. $this->monitor = Database::getConnection('monitor');
  3363. }
  3364. /**
  3365. * Adds a new database connection info to Database.
  3366. */
  3367. protected function addConnection() {
  3368. // Add a new target to the connection, by cloning the current connection.
  3369. $connection_info = Database::getConnectionInfo($this->key);
  3370. Database::addConnectionInfo($this->key, $this->target, $connection_info[$this->originalTarget]);
  3371. // Verify that the new target exists.
  3372. $info = Database::getConnectionInfo($this->key);
  3373. // Note: Custom assertion message to not expose database credentials.
  3374. $this->assertIdentical($info[$this->target], $connection_info[$this->key], 'New connection info found.');
  3375. }
  3376. /**
  3377. * Returns the connection ID of the current test connection.
  3378. *
  3379. * @return integer
  3380. */
  3381. protected function getConnectionID() {
  3382. return (int) Database::getConnection($this->target, $this->key)->query('SELECT CONNECTION_ID()')->fetchField();
  3383. }
  3384. /**
  3385. * Asserts that a connection ID exists.
  3386. *
  3387. * @param integer $id
  3388. * The connection ID to verify.
  3389. */
  3390. protected function assertConnection($id) {
  3391. $list = $this->monitor->query('SHOW PROCESSLIST')->fetchAllKeyed(0, 0);
  3392. return $this->assertTrue(isset($list[$id]), format_string('Connection ID @id found.', array('@id' => $id)));
  3393. }
  3394. /**
  3395. * Asserts that a connection ID does not exist.
  3396. *
  3397. * @param integer $id
  3398. * The connection ID to verify.
  3399. */
  3400. protected function assertNoConnection($id) {
  3401. $list = $this->monitor->query('SHOW PROCESSLIST')->fetchAllKeyed(0, 0);
  3402. return $this->assertFalse(isset($list[$id]), format_string('Connection ID @id not found.', array('@id' => $id)));
  3403. }
  3404. /**
  3405. * Tests Database::closeConnection() without query.
  3406. *
  3407. * @todo getConnectionID() executes a query.
  3408. */
  3409. function testOpenClose() {
  3410. if ($this->skipTest) {
  3411. return;
  3412. }
  3413. // Add and open a new connection.
  3414. $this->addConnection();
  3415. $id = $this->getConnectionID();
  3416. Database::getConnection($this->target, $this->key);
  3417. // Verify that there is a new connection.
  3418. $this->assertConnection($id);
  3419. // Close the connection.
  3420. Database::closeConnection($this->target, $this->key);
  3421. // Wait 20ms to give the database engine sufficient time to react.
  3422. usleep(20000);
  3423. // Verify that we are back to the original connection count.
  3424. $this->assertNoConnection($id);
  3425. }
  3426. /**
  3427. * Tests Database::closeConnection() with a query.
  3428. */
  3429. function testOpenQueryClose() {
  3430. if ($this->skipTest) {
  3431. return;
  3432. }
  3433. // Add and open a new connection.
  3434. $this->addConnection();
  3435. $id = $this->getConnectionID();
  3436. Database::getConnection($this->target, $this->key);
  3437. // Verify that there is a new connection.
  3438. $this->assertConnection($id);
  3439. // Execute a query.
  3440. Database::getConnection($this->target, $this->key)->query('SHOW TABLES');
  3441. // Close the connection.
  3442. Database::closeConnection($this->target, $this->key);
  3443. // Wait 20ms to give the database engine sufficient time to react.
  3444. usleep(20000);
  3445. // Verify that we are back to the original connection count.
  3446. $this->assertNoConnection($id);
  3447. }
  3448. /**
  3449. * Tests Database::closeConnection() with a query and custom prefetch method.
  3450. */
  3451. function testOpenQueryPrefetchClose() {
  3452. if ($this->skipTest) {
  3453. return;
  3454. }
  3455. // Add and open a new connection.
  3456. $this->addConnection();
  3457. $id = $this->getConnectionID();
  3458. Database::getConnection($this->target, $this->key);
  3459. // Verify that there is a new connection.
  3460. $this->assertConnection($id);
  3461. // Execute a query.
  3462. Database::getConnection($this->target, $this->key)->query('SHOW TABLES')->fetchCol();
  3463. // Close the connection.
  3464. Database::closeConnection($this->target, $this->key);
  3465. // Wait 20ms to give the database engine sufficient time to react.
  3466. usleep(20000);
  3467. // Verify that we are back to the original connection count.
  3468. $this->assertNoConnection($id);
  3469. }
  3470. /**
  3471. * Tests Database::closeConnection() with a select query.
  3472. */
  3473. function testOpenSelectQueryClose() {
  3474. if ($this->skipTest) {
  3475. return;
  3476. }
  3477. // Add and open a new connection.
  3478. $this->addConnection();
  3479. $id = $this->getConnectionID();
  3480. Database::getConnection($this->target, $this->key);
  3481. // Verify that there is a new connection.
  3482. $this->assertConnection($id);
  3483. // Create a table.
  3484. $name = 'foo';
  3485. Database::getConnection($this->target, $this->key)->schema()->createTable($name, array(
  3486. 'fields' => array(
  3487. 'name' => array(
  3488. 'type' => 'varchar',
  3489. 'length' => 255,
  3490. ),
  3491. ),
  3492. ));
  3493. // Execute a query.
  3494. Database::getConnection($this->target, $this->key)->select('foo', 'f')
  3495. ->fields('f', array('name'))
  3496. ->execute()
  3497. ->fetchAll();
  3498. // Drop the table.
  3499. Database::getConnection($this->target, $this->key)->schema()->dropTable($name);
  3500. // Close the connection.
  3501. Database::closeConnection($this->target, $this->key);
  3502. // Wait 20ms to give the database engine sufficient time to react.
  3503. usleep(20000);
  3504. // Verify that we are back to the original connection count.
  3505. $this->assertNoConnection($id);
  3506. }
  3507. }

Classes

Namesort descending Description
ConnectionUnitTest Tests management of database connections.
DatabaseAlterTestCase Select alter tests.
DatabaseBasicSyntaxTestCase Test how the current database driver interprets the SQL syntax.
DatabaseCaseSensitivityTestCase Test case sensitivity handling.
DatabaseConnectionTestCase Test connection management.
DatabaseDeleteTruncateTestCase Delete/Truncate tests.
DatabaseEmptyStatementTestCase Tests the empty pseudo-statement class.
DatabaseFetch2TestCase Test fetch actions, part 2.
DatabaseFetchTestCase Test fetch actions, part 1.
DatabaseInsertDefaultsTestCase Insert tests for "database default" values.
DatabaseInsertLOBTestCase Insert tests using LOB fields, which are weird on some databases.
DatabaseInsertTestCase Test the insert builder.
DatabaseInvalidDataTestCase Test invalid data handling.
DatabaseLoggingTestCase Query logging tests.
DatabaseMergeTestCase Test the MERGE query builder.
DatabaseNextIdCase Check the sequences API.
DatabaseQueryTestCase Drupal-specific SQL syntax tests.
DatabaseRangeQueryTestCase Range query tests.
DatabaseRegressionTestCase Regression tests.
DatabaseSelectCloneTest Test cloning Select queries.
DatabaseSelectComplexTestCase Test more complex select statements.
DatabaseSelectComplexTestCase2 Test more complex select statements, part 2.
DatabaseSelectOrderedTestCase Test select with order by clauses.
DatabaseSelectPagerDefaultTestCase
DatabaseSelectSubqueryTestCase Test case for subselects in a dynamic SELECT query.
DatabaseSelectTableSortDefaultTestCase
DatabaseSelectTestCase Test the SELECT builder.
DatabaseSerializeQueryTestCase Query serialization tests.
DatabaseTaggingTestCase Select tagging tests.
DatabaseTemporaryQueryTestCase Temporary query tests.
DatabaseTestCase Base test class for databases.
DatabaseTransactionTestCase Test transaction support, particularly nesting.
DatabaseUpdateComplexTestCase Tests for more complex update statements.
DatabaseUpdateLOBTestCase Test update queries involving LOB values.
DatabaseUpdateTestCase Update builder tests.
FakeRecord Dummy class for fetching into a class.