- <?php
- * @file
- * Database tests.
- */
-
- * Dummy class for fetching into a class.
- *
- * PDO supports using a new instance of an arbitrary class for records
- * rather than just a stdClass or array. This class is for testing that
- * functionality. (See testQueryFetchClass() below)
- */
- class FakeRecord { }
-
- * Base test class for databases.
- *
- * Because all database tests share the same test data, we can centralize that
- * here.
- */
- class DatabaseTestCase extends BackdropWebTestCase {
- protected $profile = 'testing';
-
- function setUp() {
- $modules = func_get_args();
- if (isset($modules[0]) && is_array($modules[0])) {
- $modules = $modules[0];
- }
- $modules[] = 'database_test';
- parent::setUp($modules);
-
- $schema['test'] = backdrop_get_schema('test');
- $schema['test_people'] = backdrop_get_schema('test_people');
- $schema['test_people_copy'] = backdrop_get_schema('test_people_copy');
- $schema['test_one_blob'] = backdrop_get_schema('test_one_blob');
- $schema['test_two_blobs'] = backdrop_get_schema('test_two_blobs');
- $schema['test_task'] = backdrop_get_schema('test_task');
-
- $this->installTables($schema);
-
- $this->addSampleData();
- }
-
-
- * Set up several tables needed by a certain test.
- *
- * @param $schema
- * An array of table definitions to install.
- */
- function installTables($schema) {
-
- foreach ($schema as $name => $data) {
- if (db_table_exists($name)) {
- db_drop_table($name);
- }
- db_create_table($name, $data);
- }
-
- foreach ($schema as $name => $data) {
- $this->assertTrue(db_table_exists($name), format_string('Table @name created successfully.', array('@name' => $name)));
- }
- }
-
-
- * Set up tables for NULL handling.
- */
- function ensureSampleDataNull() {
- $schema['test_null'] = backdrop_get_schema('test_null');
- $this->installTables($schema);
-
- db_insert('test_null')
- ->fields(array('name', 'age'))
- ->values(array(
- 'name' => 'Kermit',
- 'age' => 25,
- ))
- ->values(array(
- 'name' => 'Fozzie',
- 'age' => NULL,
- ))
- ->values(array(
- 'name' => 'Gonzo',
- 'age' => 27,
- ))
- ->execute();
- }
-
-
- * Setup our sample data.
- *
- * These are added using db_query(), since we're not trying to test the
- * INSERT operations here, just populate.
- */
- function addSampleData() {
-
- $john = db_insert('test')
- ->fields(array(
- 'name' => 'John',
- 'age' => 25,
- 'job' => 'Singer',
- ))
- ->execute();
-
- $george = db_insert('test')
- ->fields(array(
- 'name' => 'George',
- 'age' => 27,
- 'job' => 'Singer',
- ))
- ->execute();
-
- $ringo = db_insert('test')
- ->fields(array(
- 'name' => 'Ringo',
- 'age' => 28,
- 'job' => 'Drummer',
- ))
- ->execute();
-
- $paul = db_insert('test')
- ->fields(array(
- 'name' => 'Paul',
- 'age' => 26,
- 'job' => 'Songwriter',
- ))
- ->execute();
-
- db_insert('test_people')
- ->fields(array(
- 'name' => 'Meredith',
- 'age' => 30,
- 'job' => 'Speaker',
- ))
- ->execute();
-
- db_insert('test_task')
- ->fields(array('pid', 'task', 'priority'))
- ->values(array(
- 'pid' => $john,
- 'task' => 'eat',
- 'priority' => 3,
- ))
- ->values(array(
- 'pid' => $john,
- 'task' => 'sleep',
- 'priority' => 4,
- ))
- ->values(array(
- 'pid' => $john,
- 'task' => 'code',
- 'priority' => 1,
- ))
- ->values(array(
- 'pid' => $george,
- 'task' => 'sing',
- 'priority' => 2,
- ))
- ->values(array(
- 'pid' => $george,
- 'task' => 'sleep',
- 'priority' => 2,
- ))
- ->values(array(
- 'pid' => $paul,
- 'task' => 'found new band',
- 'priority' => 1,
- ))
- ->values(array(
- 'pid' => $paul,
- 'task' => 'perform at superbowl',
- 'priority' => 3,
- ))
- ->execute();
-
- db_insert('virtual')
- ->fields(array(
- 'function' => 'Function value 1',
- ))
- ->execute();
- }
- }
-
- * Test connection management.
- */
- class DatabaseConnectionTestCase extends DatabaseTestCase {
-
- * Test that connections return appropriate connection objects.
- */
- function testConnectionRouting() {
-
-
-
- $connection_info = Database::getConnectionInfo('default');
- Database::addConnectionInfo('default', 'slave', $connection_info['default']);
-
- $db1 = Database::getConnection('default', 'default');
- $db2 = Database::getConnection('slave', 'default');
-
- $this->assertNotNull($db1, 'default connection is a real connection object.');
- $this->assertNotNull($db2, 'slave connection is a real connection object.');
- $this->assertNotIdentical($db1, $db2, 'Each target refers to a different connection.');
-
-
- $db1b = Database::getConnection('default', 'default');
- $db2b = Database::getConnection('slave', 'default');
- $this->assertIdentical($db1, $db1b, 'A second call to getConnection() returns the same object.');
- $this->assertIdentical($db2, $db2b, 'A second call to getConnection() returns the same object.');
-
-
- $unknown_target = $this->randomName();
- $db3 = Database::getConnection($unknown_target, 'default');
- $this->assertNotNull($db3, 'Opening an unknown target returns a real connection object.');
- $this->assertIdentical($db1, $db3, 'An unknown target opens the default connection.');
-
-
- $db3b = Database::getConnection($unknown_target, 'default');
- $this->assertIdentical($db3, $db3b, 'A second call to getConnection() returns the same object.');
- }
-
-
- * Test that connections return appropriate connection objects.
- */
- function testConnectionRoutingOverride() {
-
-
-
- $connection_info = Database::getConnectionInfo('default');
- Database::addConnectionInfo('default', 'slave', $connection_info['default']);
-
- Database::ignoreTarget('default', 'slave');
-
- $db1 = Database::getConnection('default', 'default');
- $db2 = Database::getConnection('slave', 'default');
-
- $this->assertIdentical($db1, $db2, 'Both targets refer to the same connection.');
- }
-
-
- * Tests the closing of a database connection.
- */
- function testConnectionClosing() {
-
- $db1 = Database::getConnection('default', 'default');
-
-
- Database::closeConnection('default', 'default');
- $db2 = Database::getConnection('default', 'default');
-
-
- $this->assertNotIdentical($db1, $db2, 'Opening the default connection after it is closed returns a new object.');
- }
-
-
- * Tests the connection options of the active database.
- */
- function testConnectionOptions() {
- $connection_info = Database::getConnectionInfo('default');
-
-
- $db = Database::getConnection('default', 'default');
- $connectionOptions = $db->getConnectionOptions();
-
-
-
- $this->assertEqual($connection_info['default']['driver'], $connectionOptions['driver'], 'The default connection info driver matches the current connection options driver.');
- $this->assertEqual($connection_info['default']['database'], $connectionOptions['database'], 'The default connection info database matches the current connection options database.');
-
-
- Database::addConnectionInfo('default', 'slave', $connection_info['default']);
- $db2 = Database::getConnection('slave', 'default');
- $connectionOptions2 = $db2->getConnectionOptions();
-
-
- $connectionOptions = $db->getConnectionOptions();
- $this->assertIdentical($connectionOptions, $connectionOptions2, 'The default and slave connection options are identical.');
-
-
- $test = $connection_info['default'];
- $test['database'] .= 'test';
- Database::addConnectionInfo('test', 'default', $test);
- $connection_info = Database::getConnectionInfo('test');
-
-
- $connectionOptions = $db->getConnectionOptions();
- $this->assertNotEqual($connection_info['default']['database'], $connectionOptions['database'], 'The test connection info database does not match the current connection options database.');
- }
-
- function testConnectionUrlParsing() {
- $parsed = db_parse_url('mysql://myUser:myPass@hostname.tld/myDatabase');
- $expected = array(
- 'scheme' => 'mysql',
- 'user' => 'myUser',
- 'pass' => 'myPass',
- 'host' => 'hostname.tld',
- 'path' => '/myDatabase',
- );
- $this->assertEqual($parsed, $expected, 'The database URL was parsed correctly.');
-
- $parsed = db_parse_url('mysql:/myUser:myPass@hostname.tld/myDatabase');
- $expected = FALSE;
- $this->assertEqual($parsed, $expected, 'A malformed URL returned FALSE.');
-
- $parsed = db_parse_url('null://myUser:myPass@hostname.tld/myDatabase');
- $expected = FALSE;
- $this->assertEqual($parsed, $expected, 'The database URL for a non-existent driver returned FALSE.');
- }
- }
-
- * Test cloning Select queries.
- */
- class DatabaseSelectCloneTest extends DatabaseTestCase {
-
- * Test that subqueries as value within conditions are cloned properly.
- */
- function testSelectConditionSubQueryCloning() {
- $subquery = db_select('test', 't');
- $subquery->addField('t', 'id', 'id');
- $subquery->condition('age', 28, '<');
-
- $query = db_select('test', 't');
- $query->addField('t', 'name', 'name');
- $query->condition('id', $subquery, 'IN');
-
- $clone = clone $query;
-
-
- $subquery->condition('age', 25, '>');
-
- $clone_result = $clone->countQuery()->execute()->fetchField();
- $query_result = $query->countQuery()->execute()->fetchField();
-
-
- $this->assertEqual(3, $clone_result, 'The cloned query returns the expected number of rows');
- $this->assertEqual(2, $query_result, 'The query returns the expected number of rows');
- }
- }
-
- * Test fetch actions, part 1.
- *
- * We get timeout errors if we try to run too many tests at once.
- */
- class DatabaseFetchTestCase extends DatabaseTestCase {
-
- * Confirm that we can fetch a record properly in default object mode.
- */
- function testQueryFetchDefault() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25));
- $this->assertTrue($result instanceof DatabaseStatementInterface, 'Result set is a Backdrop statement object.');
- foreach ($result as $record) {
- $records[] = $record;
- $this->assertTrue(is_object($record), 'Record is an object.');
- $this->assertIdentical($record->name, 'John', '25 year old is John.');
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record.');
- }
-
-
- * Confirm that we can fetch a record to an object explicitly.
- */
- function testQueryFetchObject() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_OBJ));
- foreach ($result as $record) {
- $records[] = $record;
- $this->assertTrue(is_object($record), 'Record is an object.');
- $this->assertIdentical($record->name, 'John', '25 year old is John.');
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record.');
- }
-
-
- * Confirm that we can fetch a record to an array associative explicitly.
- */
- function testQueryFetchArray() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_ASSOC));
- foreach ($result as $record) {
- $records[] = $record;
- if ($this->assertTrue(is_array($record), 'Record is an array.')) {
- $this->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
- }
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record.');
- }
-
-
- * Confirm that we can fetch a record into a new instance of a custom class.
- *
- * @see FakeRecord
- */
- function testQueryFetchClass() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => 'FakeRecord'));
- foreach ($result as $record) {
- $records[] = $record;
- if ($this->assertTrue($record instanceof FakeRecord, 'Record is an object of class FakeRecord.')) {
- $this->assertIdentical($record->name, 'John', '25 year old is John.');
- }
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record.');
- }
- }
-
- * Test fetch actions, part 2.
- *
- * We get timeout errors if we try to run too many tests at once.
- */
- class DatabaseFetch2TestCase extends DatabaseTestCase {
-
- function testQueryFetchNum() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_NUM));
- foreach ($result as $record) {
- $records[] = $record;
- if ($this->assertTrue(is_array($record), 'Record is an array.')) {
- $this->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
- }
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record');
- }
-
-
- * Confirm that we can fetch a record into a doubly-keyed array explicitly.
- */
- function testQueryFetchBoth() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_BOTH));
- foreach ($result as $record) {
- $records[] = $record;
- if ($this->assertTrue(is_array($record), 'Record is an array.')) {
- $this->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
- $this->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
- }
- }
-
- $this->assertIdentical(count($records), 1, 'There is only one record.');
- }
-
-
- * Confirm that we can fetch an entire column of a result set at once.
- */
- function testQueryFetchCol() {
- $records = array();
- $result = db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25));
- $column = $result->fetchCol();
- $this->assertIdentical(count($column), 3, 'fetchCol() returns the right number of records.');
-
- $result = db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25));
- $i = 0;
- foreach ($result as $record) {
- $this->assertIdentical($record->name, $column[$i++], 'Column matches direct accesss.');
- }
- }
- }
-
- * Test the insert builder.
- */
- class DatabaseInsertTestCase extends DatabaseTestCase {
-
- * Test the very basic insert functionality.
- */
- function testSimpleInsert() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $query = db_insert('test');
- $query->fields(array(
- 'name' => 'Yoko',
- 'age' => '29',
- ));
- $query->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Yoko'))->fetchField();
- $this->assertIdentical($saved_age, '29', 'Can retrieve after inserting.');
- }
-
-
- * Test that we can insert multiple records in one query object.
- */
- function testMultiInsert() {
- $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $query = db_insert('test');
- $query->fields(array(
- 'name' => 'Larry',
- 'age' => '30',
- ));
-
-
- $query->values(array(
- 'age' => '31',
- 'name' => 'Curly',
- ));
-
-
-
- $query->values(array('Moe', '32'));
- $query->execute();
-
- $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertIdentical($num_records_before + 3, $num_records_after, 'Record inserts correctly.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
- $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
- $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
- $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
- }
-
-
- * Test that an insert object can be reused with new data after it executes.
- */
- function testRepeatedInsert() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $query = db_insert('test');
-
- $query->fields(array(
- 'name' => 'Larry',
- 'age' => '30',
- ));
- $query->execute();
-
-
- $query->values(array(
- 'age' => '31',
- 'name' => 'Curly',
- ));
- $query->execute();
-
-
- $query->values(array('Moe', '32'));
- $query->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertIdentical((int) $num_records_before + 3, (int) $num_records_after, 'Record inserts correctly.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
- $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
- $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
- $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
- }
-
-
- * Test that we can specify fields without values and specify values later.
- */
- function testInsertFieldOnlyDefinintion() {
-
-
- db_insert('test')
- ->fields(array('name', 'age'))
- ->values(array('Larry', '30'))
- ->values(array('Curly', '31'))
- ->values(array('Moe', '32'))
- ->execute();
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
- $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
- $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
- $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
- }
-
-
- * Test that inserts return the proper auto-increment ID.
- */
- function testInsertLastInsertID() {
- $id = db_insert('test')
- ->fields(array(
- 'name' => 'Larry',
- 'age' => '30',
- ))
- ->execute();
-
- $this->assertIdentical($id, '5', 'Auto-increment ID returned successfully.');
- }
-
-
- * Test that the INSERT INTO ... SELECT (fields) ... syntax works.
- */
- function testInsertSelectFields() {
- $query = db_select('test_people', 'tp');
-
-
-
- $query->addExpression('tp.age', 'age');
- $query
- ->fields('tp', array('name','job'))
- ->condition('tp.name', 'Meredith');
-
-
-
-
-
-
- db_insert('test')
- ->from($query)
- ->execute();
-
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Meredith'))->fetchField();
- $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
- }
-
-
- * Tests that the INSERT INTO ... SELECT * ... syntax works.
- */
- function testInsertSelectAll() {
- $query = db_select('test_people', 'tp')
- ->fields('tp')
- ->condition('tp.name', 'Meredith');
-
-
-
-
-
-
- db_insert('test_people_copy')
- ->from($query)
- ->execute();
-
- $saved_age = db_query('SELECT age FROM {test_people_copy} WHERE name = :name', array(':name' => 'Meredith'))->fetchField();
- $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
- }
- }
-
- * Insert tests using LOB fields, which are weird on some databases.
- */
- class DatabaseInsertLOBTestCase extends DatabaseTestCase {
-
- * Test that we can insert a single blob field successfully.
- */
- function testInsertOneBlob() {
- $data = "This is\000a test.";
- $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
- $id = db_insert('test_one_blob')
- ->fields(array('blob1' => $data))
- ->execute();
- $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
- $this->assertTrue($r['blob1'] === $data, format_string('Can insert a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
- }
-
-
- * Test that we can insert multiple blob fields in the same query.
- */
- function testInsertMultipleBlob() {
- $id = db_insert('test_two_blobs')
- ->fields(array(
- 'blob1' => 'This is',
- 'blob2' => 'a test',
- ))
- ->execute();
- $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
- $this->assertTrue($r['blob1'] === 'This is' && $r['blob2'] === 'a test', 'Can insert multiple blobs per row.');
- }
- }
-
- * Insert tests for "database default" values.
- */
- class DatabaseInsertDefaultsTestCase extends DatabaseTestCase {
-
- * Test that we can run a query that is "default values for everything".
- */
- function testDefaultInsert() {
- $query = db_insert('test')->useDefaults(array('job'));
- $id = $query->execute();
-
- $schema = backdrop_get_schema('test');
-
- $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
- $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
- }
-
-
- * Test that no action will be preformed if no fields are specified.
- */
- function testDefaultEmptyInsert() {
- $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- try {
- $result = db_insert('test')->execute();
-
- $this->fail('Expected exception NoFieldsException has not been thrown.');
- } catch (NoFieldsException $e) {
- $this->pass('Expected exception NoFieldsException has been thrown.');
- }
-
- $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertIdentical($num_records_before, $num_records_after, 'Do nothing as no fields are specified.');
- }
-
-
- * Test that we can insert fields with values and defaults in the same query.
- */
- function testDefaultInsertWithFields() {
- $query = db_insert('test')
- ->fields(array('name' => 'Bob'))
- ->useDefaults(array('job'));
- $id = $query->execute();
-
- $schema = backdrop_get_schema('test');
-
- $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
- $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
- }
- }
-
- * Update builder tests.
- */
- class DatabaseUpdateTestCase extends DatabaseTestCase {
-
- * Confirm that we can update a single record successfully.
- */
- function testSimpleUpdate() {
- $num_updated = db_update('test')
- ->fields(array('name' => 'Tiffany'))
- ->condition('id', 1)
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $saved_name = db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 1))->fetchField();
- $this->assertIdentical($saved_name, 'Tiffany', 'Updated name successfully.');
- }
-
-
- * Confirm updating to NULL.
- */
- function testSimpleNullUpdate() {
- $this->ensureSampleDataNull();
- $num_updated = db_update('test_null')
- ->fields(array('age' => NULL))
- ->condition('name', 'Kermit')
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $saved_age = db_query('SELECT age FROM {test_null} WHERE name = :name', array(':name' => 'Kermit'))->fetchField();
- $this->assertNull($saved_age, 'Updated name successfully.');
- }
-
-
- * Confirm that we can update a multiple records successfully.
- */
- function testMultiUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('job', 'Singer')
- ->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Confirm that we can update a multiple records with a non-equality condition.
- */
- function testMultiGTUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('age', 26, '>')
- ->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Confirm that we can update a multiple records with a where call.
- */
- function testWhereUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->where('age > :age', array(':age' => 26))
- ->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Confirm that we can stack condition and where calls.
- */
- function testWhereAndConditionUpdate() {
- $update = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->where('age > :age', array(':age' => 26))
- ->condition('name', 'Ringo');
- $num_updated = $update->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
- }
-
-
- * Test updating with expressions.
- */
- function testExpressionUpdate() {
-
-
- $num_rows = db_update('test')
- ->expression('age', 'age * age')
- ->execute();
- $this->assertIdentical($num_rows, 4, 'Updated 4 records.');
- }
-
-
- * Tests return value on update.
- */
- function testUpdateAffectedRows() {
-
-
-
-
-
-
- $num_rows = db_update('test_task')
- ->condition('priority', 1, '<>')
- ->fields(array('task' => 'sleep'))
- ->execute();
- $this->assertIdentical($num_rows, 5, 'Correctly returned 5 affected rows.');
- }
-
-
- * Confirm that we can update the primary key of a record successfully.
- */
- function testPrimaryKeyUpdate() {
- $num_updated = db_update('test')
- ->fields(array('id' => 42, 'name' => 'John'))
- ->condition('id', 1)
- ->execute();
- $this->assertIdentical($num_updated, 1, t('Updated 1 record.'));
-
- $saved_name= db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 42))->fetchField();
- $this->assertIdentical($saved_name, 'John', t('Updated primary key successfully.'));
- }
- }
-
- * Tests for more complex update statements.
- */
- class DatabaseUpdateComplexTestCase extends DatabaseTestCase {
-
- * Test updates with OR conditionals.
- */
- function testOrConditionUpdate() {
- $update = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition(db_or()
- ->condition('name', 'John')
- ->condition('name', 'Paul')
- );
- $num_updated = $update->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Test WHERE IN clauses.
- */
- function testInConditionUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('name', array('John', 'Paul'), 'IN')
- ->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Test WHERE NOT IN clauses.
- */
- function testNotInConditionUpdate() {
-
-
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('name', array('John', 'Paul', 'George'), 'NoT IN')
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
- }
-
-
- * Test BETWEEN conditional clauses.
- */
- function testBetweenConditionUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('age', array(25, 26), 'BETWEEN')
- ->execute();
- $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
- }
-
-
- * Test LIKE conditionals.
- */
- function testLikeConditionUpdate() {
- $num_updated = db_update('test')
- ->fields(array('job' => 'Musician'))
- ->condition('name', '%ge%', 'LIKE')
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
- }
-
-
- * Test update with expression values.
- */
- function testUpdateExpression() {
- $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
- $GLOBALS['larry_test'] = 1;
- $num_updated = db_update('test')
- ->condition('name', 'Ringo')
- ->fields(array('job' => 'Musician'))
- ->expression('age', 'age + :age', array(':age' => 4))
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
- $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
-
- $person = db_query('SELECT * FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetch();
- $this->assertEqual($person->name, 'Ringo', 'Name set correctly.');
- $this->assertEqual($person->age, $before_age + 4, 'Age set correctly.');
- $this->assertEqual($person->job, 'Musician', 'Job set correctly.');
- $GLOBALS['larry_test'] = 0;
- }
-
-
- * Test update with only expression values.
- */
- function testUpdateOnlyExpression() {
- $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
- $num_updated = db_update('test')
- ->condition('name', 'Ringo')
- ->expression('age', 'age + :age', array(':age' => 4))
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
-
- $after_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchField();
- $this->assertEqual($before_age + 4, $after_age, 'Age updated correctly');
- }
- }
-
- * Test update queries involving LOB values.
- */
- class DatabaseUpdateLOBTestCase extends DatabaseTestCase {
-
- * Confirm that we can update a blob column.
- */
- function testUpdateOneBlob() {
- $data = "This is\000a test.";
- $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
- $id = db_insert('test_one_blob')
- ->fields(array('blob1' => $data))
- ->execute();
-
- $data .= $data;
- db_update('test_one_blob')
- ->condition('id', $id)
- ->fields(array('blob1' => $data))
- ->execute();
-
- $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
- $this->assertTrue($r['blob1'] === $data, format_string('Can update a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
- }
-
-
- * Confirm that we can update two blob columns in the same table.
- */
- function testUpdateMultipleBlob() {
- $id = db_insert('test_two_blobs')
- ->fields(array(
- 'blob1' => 'This is',
- 'blob2' => 'a test',
- ))
- ->execute();
-
- db_update('test_two_blobs')
- ->condition('id', $id)
- ->fields(array('blob1' => 'and so', 'blob2' => 'is this'))
- ->execute();
-
- $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
- $this->assertTrue($r['blob1'] === 'and so' && $r['blob2'] === 'is this', 'Can update multiple blobs per row.');
- }
- }
-
- * Delete/Truncate tests.
- *
- * The DELETE tests are not as extensive, as all of the interesting code for
- * DELETE queries is in the conditional which is identical to the UPDATE and
- * SELECT conditional handling.
- *
- * The TRUNCATE tests are not extensive either, because the behavior of
- * TRUNCATE queries is not consistent across database engines. We only test
- * that a TRUNCATE query actually deletes all rows from the target table.
- */
- class DatabaseDeleteTruncateTestCase extends DatabaseTestCase {
-
- * Confirm that we can use a subselect in a delete successfully.
- */
- function testSubselectDelete() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
- $pid_to_delete = db_query("SELECT * FROM {test_task} WHERE task = 'sleep'")->fetchField();
-
- $subquery = db_select('test', 't')
- ->fields('t', array('id'))
- ->condition('t.id', array($pid_to_delete), 'IN');
- $delete = db_delete('test_task')
- ->condition('task', 'sleep')
- ->condition('pid', $subquery, 'IN');
-
- $num_deleted = $delete->execute();
- $this->assertEqual($num_deleted, 1, "Deleted 1 record.");
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
- }
-
-
- * Confirm that we can delete a single record successfully.
- */
- function testSimpleDelete() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $num_deleted = db_delete('test')
- ->condition('id', 1)
- ->execute();
- $this->assertIdentical($num_deleted, 1, 'Deleted 1 record.');
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
- }
-
-
- * Confirm that we can truncate a whole table successfully.
- */
- function testTruncate() {
- $num_records_before = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
-
- db_truncate('test')->execute();
-
- $num_records_after = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
- $this->assertEqual(0, $num_records_after, 'Truncate really deletes everything.');
- }
- }
-
- * Test the MERGE query builder.
- */
- class DatabaseMergeTestCase extends DatabaseTestCase {
-
- * Confirm that we can merge-insert a record successfully.
- */
- function testMergeInsert() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $max_id_before = db_query('SELECT MAX(id) FROM {test}')->fetchField();
-
- $merge = db_merge('test')
- ->key(array('job' => 'Presenter'))
- ->fields(array(
- 'age' => 31,
- 'name' => 'Tiffany',
- ));
- $result = $merge->execute();
-
- $this->assertEqual($result, MergeQuery::STATUS_INSERT, 'Insert status returned.');
- $this->assertEqual($merge->getResult(), $max_id_before + 1, 'Incremented ID returned.');
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
-
- $person = db_query('SELECT * FROM {test} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
- $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
- $this->assertEqual($person->age, 31, 'Age set correctly.');
- $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
- }
-
-
- * Confirm that we can merge-update a record successfully.
- */
- function testMergeUpdate() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $num_records_matched = db_query('SELECT COUNT(*) FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetchField();
-
- $merge = db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->fields(array(
- 'age' => 31,
- 'name' => 'Tiffany',
- ));
- $result = $merge->execute();
-
- $this->assertEqual($result, MergeQuery::STATUS_UPDATE, 'Update status returned.');
- $this->assertEqual($merge->getResult(), $num_records_matched, 'Number of updated rows matches.');
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
- $this->assertEqual($person->age, 31, 'Age set correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
- }
-
-
- * Confirm that we can merge-update a record successfully, with different insert and update.
- */
- function testMergeUpdateExcept() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
-
- db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->insertFields(array('age' => 31))
- ->updateFields(array('name' => 'Tiffany'))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
- $this->assertEqual($person->age, 30, 'Age skipped correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
- }
-
-
- * Confirm that we can merge-update a record successfully, with alternate replacement.
- */
- function testMergeUpdateExplicit() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
-
- db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->insertFields(array(
- 'age' => 31,
- 'name' => 'Tiffany',
- ))
- ->updateFields(array(
- 'name' => 'Joe',
- ))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Joe', 'Name set correctly.');
- $this->assertEqual($person->age, 30, 'Age skipped correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
- }
-
-
- * Confirm that we can merge-update a record successfully, with expressions.
- */
- function testMergeUpdateExpression() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
-
- $age_before = db_query('SELECT age FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetchField();
-
-
-
-
-
-
- db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->fields(array('name' => 'Tiffany'))
- ->insertFields(array('age' => 31))
- ->expression('age', 'age + :age', array(':age' => 4))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
- $this->assertEqual($person->age, $age_before + 4, 'Age updated correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
- }
-
-
- * Test that we can merge-insert without any update fields.
- */
- function testMergeInsertWithoutUpdate() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
-
- db_merge('test_people')
- ->key(array('job' => 'Presenter'))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
- $this->assertEqual($person->name, '', 'Name set correctly.');
- $this->assertEqual($person->age, 0, 'Age set correctly.');
- $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
- }
-
-
- * Confirm that we can merge-update without any update fields.
- */
- function testMergeUpdateWithoutUpdate() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
-
- db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
- $this->assertEqual($person->age, 30, 'Age skipped correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
-
- db_merge('test_people')
- ->key(array('job' => 'Speaker'))
- ->insertFields(array('age' => 31))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
- $this->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
-
- $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
- $this->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
- $this->assertEqual($person->age, 30, 'Age skipped correctly.');
- $this->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
- }
-
-
- * Test that an invalid merge query throws an exception like it is supposed to.
- */
- function testInvalidMerge() {
- try {
-
- db_merge('test_people')
- ->fields(array(
- 'age' => 31,
- 'name' => 'Tiffany',
- ))
- ->execute();
- }
- catch (InvalidMergeQueryException $e) {
- $this->pass('InvalidMergeQueryException thrown for invalid query.');
- return;
- }
- $this->fail('No InvalidMergeQueryException thrown');
- }
- }
-
- * Test the SELECT builder.
- */
- class DatabaseSelectTestCase extends DatabaseTestCase {
-
- * Test rudimentary SELECT statements.
- */
- function testSimpleSelect() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- }
-
-
- * Test rudimentary SELECT statement with a COMMENT.
- */
- function testSimpleComment() {
- $query = db_select('test');
- $query->comment('Testing query comments');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $query = (string)$query;
- $expected = "/* Testing query comments */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- $this->assertEqual($query, $expected, 'The flattened query contains the comment string.');
- }
-
-
- * Test query COMMENT system against vulnerabilities.
- */
- function testVulnerableComment() {
- $query = db_select('test');
- $query->comment('Testing query comments */ SELECT nid FROM {node}; --');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $query = (string)$query;
- $expected = "/* Testing query comments * / SELECT nid FROM {node}; -- */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- $this->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');
-
- $connection = Database::getConnection();
- foreach ($this->makeCommentsProvider() as $test_set) {
- list($expected, $comments) = $test_set;
- $this->assertEqual($expected, $connection->makeComment($comments));
- }
- }
-
-
- * Provides expected and input values for testVulnerableComment().
- */
- function makeCommentsProvider() {
- return array(
- array(
- '/* */ ',
- array(''),
- ),
-
- array(
- '/* Exploit * / DROP TABLE node; -- */ ',
- array('Exploit */ DROP TABLE node; --'),
- ),
-
- array(
- '/* Exploit * / * / DROP TABLE node; -- */ ',
- array('Exploit */*/ DROP TABLE node; --'),
- ),
- array(
- '/* Exploit * * // DROP TABLE node; -- */ ',
- array('Exploit **// DROP TABLE node; --'),
- ),
-
- array(
- '/* Exploit * / DROP TABLE node; --; Another try * / DROP TABLE node; -- */ ',
- array('Exploit */ DROP TABLE node; --', 'Another try */ DROP TABLE node; --'),
- ),
- );
- }
-
-
- * Test basic conditionals on SELECT statements.
- */
- function testSimpleSelectConditional() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->condition('age', 27);
- $result = $query->execute();
-
-
- $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
- $this->assertEqual($age_field, 'age', 'Age field alias is correct.');
-
-
- $record = $result->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
- $this->assertEqual($record->$age_field, 27, 'Fetched age is correct.');
- }
-
-
- * Test SELECT statements with expressions.
- */
- function testSimpleSelectExpression() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addExpression("age*2", 'double_age');
- $query->condition('age', 27);
- $result = $query->execute();
-
-
- $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
- $this->assertEqual($age_field, 'double_age', 'Age field alias is correct.');
-
-
- $record = $result->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
- $this->assertEqual($record->$age_field, 27*2, 'Fetched age expression is correct.');
- }
-
-
- * Test SELECT statements with multiple expressions.
- */
- function testSimpleSelectExpressionMultiple() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_double_field = $query->addExpression("age*2");
- $age_triple_field = $query->addExpression("age*3");
- $query->condition('age', 27);
- $result = $query->execute();
-
-
- $this->assertEqual($age_double_field, 'expression', 'Double age field alias is correct.');
- $this->assertEqual($age_triple_field, 'expression_2', 'Triple age field alias is correct.');
-
-
- $record = $result->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
- $this->assertEqual($record->$age_double_field, 27*2, 'Fetched double age expression is correct.');
- $this->assertEqual($record->$age_triple_field, 27*3, 'Fetched triple age expression is correct.');
- }
-
-
- * Test adding multiple fields to a select statement at the same time.
- */
- function testSimpleSelectMultipleFields() {
- $record = db_select('test')
- ->fields('test', array('id', 'name', 'age', 'job'))
- ->condition('age', 27)
- ->execute()->fetchObject();
-
-
- $this->assertNotNull($record->id, 'ID field is present.');
- $this->assertNotNull($record->name, 'Name field is present.');
- $this->assertNotNull($record->age, 'Age field is present.');
- $this->assertNotNull($record->job, 'Job field is present.');
-
-
-
- $this->assertEqual($record->id, 2, 'ID field has the correct value.');
- $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
- $this->assertEqual($record->age, 27, 'Age field has the correct value.');
- $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
- }
-
-
- * Test adding all fields from a given table to a select statement.
- */
- function testSimpleSelectAllFields() {
- $record = db_select('test')
- ->fields('test')
- ->condition('age', 27)
- ->execute()->fetchObject();
-
-
- $this->assertNotNull($record->id, 'ID field is present.');
- $this->assertNotNull($record->name, 'Name field is present.');
- $this->assertNotNull($record->age, 'Age field is present.');
- $this->assertNotNull($record->job, 'Job field is present.');
-
-
-
- $this->assertEqual($record->id, 2, 'ID field has the correct value.');
- $this->assertEqual($record->name, 'George', 'Name field has the correct value.');
- $this->assertEqual($record->age, 27, 'Age field has the correct value.');
- $this->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
- }
-
-
- * Test that a comparison with NULL is always FALSE.
- */
- function testNullCondition() {
- $this->ensureSampleDataNull();
-
- $names = db_select('test_null', 'tn')
- ->fields('tn', array('name'))
- ->condition('age', NULL)
- ->execute()->fetchCol();
-
- $this->assertEqual(count($names), 0, t('No records found when comparing to NULL.'));
- }
-
-
- * Test that we can find a record with a NULL value.
- */
- function testIsNullCondition() {
- $this->ensureSampleDataNull();
-
- $names = db_select('test_null', 'tn')
- ->fields('tn', array('name'))
- ->isNull('age')
- ->execute()->fetchCol();
-
- $this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
- $this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
- }
-
-
- * Test that we can find a record without a NULL value.
- */
- function testIsNotNullCondition() {
- $this->ensureSampleDataNull();
-
- $names = db_select('test_null', 'tn')
- ->fields('tn', array('name'))
- ->isNotNull('tn.age')
- ->orderBy('name')
- ->execute()->fetchCol();
-
- $this->assertEqual(count($names), 2, 'Correct number of records found withNOT NULL age.');
- $this->assertEqual($names[0], 'Gonzo', 'Correct record returned for NOT NULL age.');
- $this->assertEqual($names[1], 'Kermit', 'Correct record returned for NOT NULL age.');
- }
-
-
- * Test that we can UNION multiple Select queries together. This is
- * semantically equal to UNION DISTINCT, so we don't explicity test that.
- */
- function testUnion() {
- $query_1 = db_select('test', 't')
- ->fields('t', array('name'))
- ->condition('age', array(27, 28), 'IN');
-
- $query_2 = db_select('test', 't')
- ->fields('t', array('name'))
- ->condition('age', 28);
-
- $query_1->union($query_2);
-
- $names = $query_1->execute()->fetchCol();
-
-
- $this->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
-
- $this->assertEqual($names[0], 'George', 'First query returned correct name.');
- $this->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
- }
-
-
- * Test that we can UNION ALL multiple Select queries together.
- */
- function testUnionAll() {
- $query_1 = db_select('test', 't')
- ->fields('t', array('name'))
- ->condition('age', array(27, 28), 'IN');
-
- $query_2 = db_select('test', 't')
- ->fields('t', array('name'))
- ->condition('age', 28);
-
- $query_1->union($query_2, 'ALL');
-
- $names = $query_1->execute()->fetchCol();
-
-
- $this->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
-
- $this->assertEqual($names[0], 'George', 'First query returned correct first name.');
- $this->assertEqual($names[1], 'Ringo', 'Second query returned correct second name.');
- $this->assertEqual($names[2], 'Ringo', 'Third query returned correct name.');
- }
-
-
- * Test that random ordering of queries works.
- *
- * We take the approach of testing the Backdrop layer only, rather than trying
- * to test that the database's random number generator actually produces
- * random queries (which is very difficult to do without an unacceptable risk
- * of the test failing by accident).
- *
- * Therefore, in this test we run the same query twice and assert that the two
- * results are reordered versions of each other (as well as of the same query
- * without the random ordering). It is reasonable to assume that if we run the
- * same select query twice and the results are in a different order each time,
- * the only way this could happen is if we have successfully triggered the
- * database's random ordering functionality.
- */
- function testRandomOrder() {
-
-
-
- $number_of_items = 52;
- while (db_query("SELECT MAX(id) FROM {test}")->fetchField() < $number_of_items) {
- db_insert('test')->fields(array('name' => $this->randomName()))->execute();
- }
-
-
- $expected_ids = range(1, $number_of_items);
- $ordered_ids = db_select('test', 't')
- ->fields('t', array('id'))
- ->range(0, $number_of_items)
- ->orderBy('id')
- ->execute()
- ->fetchCol();
- $this->assertEqual($ordered_ids, $expected_ids, 'A query without random ordering returns IDs in the correct order.');
-
-
-
-
- $randomized_ids = db_select('test', 't')
- ->fields('t', array('id'))
- ->range(0, $number_of_items)
- ->orderRandom()
- ->execute()
- ->fetchCol();
- $this->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
- $sorted_ids = $randomized_ids;
- sort($sorted_ids);
- $this->assertEqual($sorted_ids, $ordered_ids, 'After sorting the random list, the result matches the original query.');
-
-
-
- $randomized_ids_second_set = db_select('test', 't')
- ->fields('t', array('id'))
- ->range(0, $number_of_items)
- ->orderRandom()
- ->execute()
- ->fetchCol();
- $this->assertNotEqual($randomized_ids_second_set, $randomized_ids, 'Performing the query with random ordering a second time returns IDs in a different order.');
- $sorted_ids_second_set = $randomized_ids_second_set;
- sort($sorted_ids_second_set);
- $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.');
- }
-
-
- * Test that aliases are renamed when duplicates.
- */
- function testSelectDuplicateAlias() {
- $query = db_select('test', 't');
- $alias1 = $query->addField('t', 'name', 'the_alias');
- $alias2 = $query->addField('t', 'age', 'the_alias');
- $this->assertNotIdentical($alias1, $alias2, 'Duplicate aliases are renamed.');
- }
- }
-
- * Test case for subselects in a dynamic SELECT query.
- */
- class DatabaseSelectSubqueryTestCase extends DatabaseTestCase {
-
- * Test that we can use a subquery in a FROM clause.
- */
- function testFromSubquerySelect() {
-
- $subquery = db_select('test_task', 'tt');
- $subquery->addField('tt', 'pid', 'pid');
- $subquery->addField('tt', 'task', 'task');
- $subquery->condition('priority', 1);
-
- for ($i = 0; $i < 2; $i++) {
-
-
- $select = db_select($subquery, 'tt2');
- $select->join('test', 't', 't.id=tt2.pid');
- $select->addField('t', 'name');
- if ($i) {
-
-
- $select->condition('name', 'John');
- }
- $select->condition('task', 'code');
-
-
-
-
-
-
- $people = $select->execute()->fetchCol();
-
- $this->assertEqual(count($people), 1, 'Returned the correct number of rows.');
- }
- }
-
-
- * Test that we can use a subquery in a FROM clause with a limit.
- */
- function testFromSubquerySelectWithLimit() {
-
- $subquery = db_select('test_task', 'tt');
- $subquery->addField('tt', 'pid', 'pid');
- $subquery->addField('tt', 'task', 'task');
- $subquery->orderBy('priority', 'DESC');
- $subquery->range(0, 1);
-
-
-
- $select = db_select($subquery, 'tt2');
- $select->join('test', 't', 't.id=tt2.pid');
- $select->addField('t', 'name');
-
-
-
-
-
- $people = $select->execute()->fetchCol();
-
- $this->assertEqual(count($people), 1, 'Returned the correct number of rows.');
- }
-
-
- * Test that we can use a subquery in a WHERE clause.
- */
- function testConditionSubquerySelect() {
-
- $subquery = db_select('test_task', 'tt');
- $subquery->addField('tt', 'pid', 'pid');
- $subquery->condition('tt.priority', 1);
-
-
-
- $select = db_select('test_task', 'tt2');
- $select->addField('tt2', 'task');
- $select->condition('tt2.pid', $subquery, 'IN');
-
-
-
-
-
- $people = $select->execute()->fetchCol();
- $this->assertEqual(count($people), 5, 'Returned the correct number of rows.');
-
-
- $subquery2 = db_select('test_task', 'tt');
- $subquery2->addField('tt', 'priority');
- $subquery2->condition('tt.task', 'eat');
-
-
- $select2 = db_select('test_task', 'tt2');
- $select2->addField('tt2', 'task');
- $select2->condition('tt2.priority', $subquery2, '<');
-
- $tasks = $select2->execute()->fetchCol();
- $this->assertEqual(count($tasks), 4, 'Returned the correct number of rows.');
- }
-
-
- * Test that we can use a subquery in a JOIN clause.
- */
- function testJoinSubquerySelect() {
-
- $subquery = db_select('test_task', 'tt');
- $subquery->addField('tt', 'pid', 'pid');
- $subquery->condition('priority', 1);
-
-
-
- $select = db_select('test', 't');
- $select->join($subquery, 'tt', 't.id=tt.pid');
- $select->addField('t', 'name');
-
-
-
-
-
- $people = $select->execute()->fetchCol();
-
- $this->assertEqual(count($people), 2, 'Returned the correct number of rows.');
- }
-
-
- * Test EXISTS subquery conditionals on SELECT statements.
- *
- * We essentially select all rows from the {test} table that have matching
- * rows in the {test_people} table based on the shared name column.
- */
- function testExistsSubquerySelect() {
-
- db_insert('test_people')
- ->fields(array(
- 'name' => 'George',
- 'age' => 27,
- 'job' => 'Singer',
- ))
- ->execute();
-
- $query = db_select('test', 't')
- ->fields('t', array('name'));
-
- $subquery = db_select('test_people', 'tp')
- ->fields('tp', array('name'))
- ->where('tp.name = t.name');
- $query->exists($subquery);
- $result = $query->execute();
-
-
- $record = $result->fetch();
- $this->assertEqual($record->name, 'George', 'Fetched name is correct using EXISTS query.');
- }
-
-
- * Test NOT EXISTS subquery conditionals on SELECT statements.
- *
- * We essentially select all rows from the {test} table that don't have
- * matching rows in the {test_people} table based on the shared name column.
- */
- function testNotExistsSubquerySelect() {
-
- db_insert('test_people')
- ->fields(array(
- 'name' => 'George',
- 'age' => 27,
- 'job' => 'Singer',
- ))
- ->execute();
-
-
- $query = db_select('test', 't')
- ->fields('t', array('name'));
-
- $subquery = db_select('test_people', 'tp')
- ->fields('tp', array('name'))
- ->where('tp.name = t.name');
- $query->notExists($subquery);
-
-
- $people = $query->execute()->fetchCol();
- $this->assertEqual(count($people), 3, 'NOT EXISTS query returned the correct results.');
- }
- }
-
- * Test select with order by clauses.
- */
- class DatabaseSelectOrderedTestCase extends DatabaseTestCase {
-
- * Test basic order by.
- */
- function testSimpleSelectOrdered() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->orderBy($age_field);
- $result = $query->execute();
-
- $num_records = 0;
- $last_age = 0;
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue($record->age >= $last_age, 'Results returned in correct order.');
- $last_age = $record->age;
- }
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- }
-
-
- * Test multiple order by.
- */
- function testSimpleSelectMultiOrdered() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $job_field = $query->addField('test', 'job');
- $query->orderBy($job_field);
- $query->orderBy($age_field);
- $result = $query->execute();
-
- $num_records = 0;
- $expected = array(
- array('Ringo', 28, 'Drummer'),
- array('John', 25, 'Singer'),
- array('George', 27, 'Singer'),
- array('Paul', 26, 'Songwriter'),
- );
- $results = $result->fetchAll(PDO::FETCH_NUM);
- foreach ($expected as $k => $record) {
- $num_records++;
- foreach ($record as $kk => $col) {
- if ($expected[$k][$kk] != $results[$k][$kk]) {
- $this->assertTrue(FALSE, 'Results returned in correct order.');
- }
- }
- }
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- }
-
-
- * Test order by descending.
- */
- function testSimpleSelectOrderedDesc() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->orderBy($age_field, 'DESC');
- $result = $query->execute();
-
- $num_records = 0;
- $last_age = 100000000;
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue($record->age <= $last_age, 'Results returned in correct order.');
- $last_age = $record->age;
- }
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- }
-
-
- * Tests that the sort direction is sanitized properly.
- */
- function testOrderByEscaping() {
- $query = db_select('test')->orderBy('name', 'invalid direction');
- $order_bys = $query->getOrderBy();
- $this->assertEqual($order_bys['name'], 'ASC', 'Invalid order by direction is converted to ASC.');
- }
- }
-
- * Test more complex select statements.
- */
- class DatabaseSelectComplexTestCase extends DatabaseTestCase {
-
- * Test simple JOIN statements.
- */
- function testDefaultJoin() {
- $query = db_select('test_task', 't');
- $people_alias = $query->join('test', 'p', 't.pid = p.id');
- $name_field = $query->addField($people_alias, 'name', 'name');
- $task_field = $query->addField('t', 'task', 'task');
- $priority_field = $query->addField('t', 'priority', 'priority');
-
- $query->orderBy($priority_field);
- $result = $query->execute();
-
- $num_records = 0;
- $last_priority = 0;
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue($record->$priority_field >= $last_priority, 'Results returned in correct order.');
- $this->assertNotEqual($record->$name_field, 'Ringo', 'Taskless person not selected.');
- $last_priority = $record->$priority_field;
- }
-
- $this->assertEqual($num_records, 7, 'Returned the correct number of rows.');
- }
-
-
- * Test LEFT OUTER joins.
- */
- function testLeftOuterJoin() {
- $query = db_select('test', 'p');
- $people_alias = $query->leftJoin('test_task', 't', 't.pid = p.id');
- $name_field = $query->addField('p', 'name', 'name');
- $task_field = $query->addField($people_alias, 'task', 'task');
- $priority_field = $query->addField($people_alias, 'priority', 'priority');
-
- $query->orderBy($name_field);
- $result = $query->execute();
-
- $num_records = 0;
- $last_name = 0;
-
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue(strcmp($record->$name_field, $last_name) >= 0, 'Results returned in correct order.');
- $last_priority = $record->$name_field;
- }
-
- $this->assertEqual($num_records, 8, 'Returned the correct number of rows.');
- }
-
-
- * Test GROUP BY clauses.
- */
- function testGroupBy() {
- $query = db_select('test_task', 't');
- $count_field = $query->addExpression('COUNT(task)', 'num');
- $task_field = $query->addField('t', 'task');
- $query->orderBy($count_field);
- $query->groupBy($task_field);
- $result = $query->execute();
-
- $num_records = 0;
- $last_count = 0;
- $records = array();
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
- $last_count = $record->$count_field;
- $records[$record->$task_field] = $record->$count_field;
- }
-
- $correct_results = array(
- 'eat' => 1,
- 'sleep' => 2,
- 'code' => 1,
- 'found new band' => 1,
- 'perform at superbowl' => 1,
- );
-
- foreach ($correct_results as $task => $count) {
- $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
- }
-
- $this->assertEqual($num_records, 6, 'Returned the correct number of total rows.');
- }
-
-
- * Test GROUP BY and HAVING clauses together.
- */
- function testGroupByAndHaving() {
- $query = db_select('test_task', 't');
- $count_field = $query->addExpression('COUNT(task)', 'num');
- $task_field = $query->addField('t', 'task');
- $query->orderBy($count_field);
- $query->groupBy($task_field);
- $query->having('COUNT(task) >= 2');
- $result = $query->execute();
-
- $num_records = 0;
- $last_count = 0;
- $records = array();
- foreach ($result as $record) {
- $num_records++;
- $this->assertTrue($record->$count_field >= 2, 'Record has the minimum count.');
- $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
- $last_count = $record->$count_field;
- $records[$record->$task_field] = $record->$count_field;
- }
-
- $correct_results = array(
- 'sleep' => 2,
- );
-
- foreach ($correct_results as $task => $count) {
- $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
- }
-
- $this->assertEqual($num_records, 1, 'Returned the correct number of total rows.');
- }
-
-
- * Test range queries. The SQL clause varies with the database.
- */
- function testRange() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->range(0, 2);
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
- }
-
-
- * Test distinct queries.
- */
- function testDistinct() {
- $query = db_select('test_task');
- $task_field = $query->addField('test_task', 'task');
- $query->distinct();
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $this->assertEqual($num_records, 6, 'Returned the correct number of rows.');
- }
-
-
- * Test that we can generate a count query from a built query.
- */
- function testCountQuery() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->orderBy('name');
-
- $count = $query->countQuery()->execute()->fetchField();
-
- $this->assertEqual($count, 4, 'Counted the correct number of records.');
-
-
-
- $record = $query->execute()->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Correct data retrieved.');
- $this->assertEqual($record->$age_field, 27, 'Correct data retrieved.');
- }
-
- function testHavingCountQuery() {
- $query = db_select('test')
- ->extend('PagerDefault')
- ->groupBy('age')
- ->having('age + 1 > 0');
- $query->addField('test', 'age');
- $query->addExpression('age + 1');
- $count = count($query->execute()->fetchCol());
- $this->assertEqual($count, 4, 'Counted the correct number of records.');
- }
-
-
- * Test that countQuery properly removes 'all_fields' statements and
- * ordering clauses.
- */
- function testCountQueryRemovals() {
- $query = db_select('test');
- $query->fields('test');
- $query->orderBy('name');
- $count = $query->countQuery();
-
-
- $tables = $query->getTables();
- $this->assertEqual($tables['test']['all_fields'], 1, 'Query correctly sets \'all_fields\' statement.');
- $tables = $count->getTables();
- $this->assertFalse(isset($tables['test']['all_fields']), 'Count query correctly unsets \'all_fields\' statement.');
-
-
- $orderby = $query->getOrderBy();
- $this->assertEqual($orderby['name'], 'ASC', 'Query correctly sets ordering clause.');
- $orderby = $count->getOrderBy();
- $this->assertFalse(isset($orderby['name']), 'Count query correctly unsets ordering caluse.');
-
-
- $count = $count->execute()->fetchField();
-
- $this->assertEqual($count, 4, 'Counted the correct number of records.');
- }
-
-
-
- * Test that countQuery properly removes fields and expressions.
- */
- function testCountQueryFieldRemovals() {
-
-
-
-
-
- $query = db_select('test');
- $query->fields('test', array('fail'));
- $this->assertEqual(4, $query->countQuery()->execute()->fetchField(), 'Count Query removed fields');
-
- $query = db_select('test');
- $query->addExpression('fail');
- $this->assertEqual(4, $query->countQuery()->execute()->fetchField(), 'Count Query removed expressions');
- }
-
-
- * Test that we can generate a count query from a query with distinct.
- */
- function testCountQueryDistinct() {
- $query = db_select('test_task');
- $task_field = $query->addField('test_task', 'task');
- $query->distinct();
-
- $count = $query->countQuery()->execute()->fetchField();
-
- $this->assertEqual($count, 6, 'Counted the correct number of records.');
- }
-
-
- * Test that we can generate a count query from a query with GROUP BY.
- */
- function testCountQueryGroupBy() {
- $query = db_select('test_task');
- $pid_field = $query->addField('test_task', 'pid');
- $query->groupBy('pid');
-
- $count = $query->countQuery()->execute()->fetchField();
-
- $this->assertEqual($count, 3, 'Counted the correct number of records.');
-
-
-
- $query = db_select('test_task');
- $pid_field = $query->addField('test_task', 'pid', 'pid_alias');
- $query->addExpression('COUNT(test_task.task)', 'count');
- $query->groupBy('pid_alias');
- $query->orderBy('pid_alias', 'asc');
-
- $count = $query->countQuery()->execute()->fetchField();
-
- $this->assertEqual($count, 3, 'Counted the correct number of records.');
- }
-
-
- * Confirm that we can properly nest conditional clauses.
- */
- function testNestedConditions() {
-
-
-
-
- $query = db_select('test');
- $query->addField('test', 'job');
- $query->condition('name', 'Paul');
- $query->condition(db_or()->condition('age', 26)->condition('age', 27));
-
- $job = $query->execute()->fetchField();
- $this->assertEqual($job, 'Songwriter', 'Correct data retrieved.');
- }
-
-
- * Confirm we can join on a single table twice with a dynamic alias.
- */
- function testJoinTwice() {
- $query = db_select('test')->fields('test');
- $alias = $query->join('test', 'test', 'test.job = %alias.job');
- $query->addField($alias, 'name', 'othername');
- $query->addField($alias, 'job', 'otherjob');
- $query->where("$alias.name <> test.name");
- $crowded_job = $query->execute()->fetch();
- $this->assertEqual($crowded_job->job, $crowded_job->otherjob, 'Correctly joined same table twice.');
- $this->assertNotEqual($crowded_job->name, $crowded_job->othername, 'Correctly joined same table twice.');
- }
-
- }
-
- * Test more complex select statements, part 2.
- */
- class DatabaseSelectComplexTestCase2 extends DatabaseTestCase {
- function setUp() {
- parent::setUp(array('node_access_test'));
- }
-
-
- * Test that we can join on a query.
- */
- function testJoinSubquery() {
- $acct = $this->backdropCreateUser();
- $this->backdropLogin($acct);
-
- $query = db_select('test_task', 'tt', array('target' => 'slave'));
- $query->addExpression('tt.pid + 1', 'abc');
- $query->condition('priority', 1, '>');
- $query->condition('priority', 100, '<');
-
- $subquery = db_select('test', 'tp');
- $subquery->join('test_one_blob', 'tpb', 'tp.id = tpb.id');
- $subquery->join('node', 'n', 'tp.id = n.nid');
- $subquery->addTag('node_access');
- $subquery->addMetaData('account', $acct);
- $subquery->addField('tp', 'id');
- $subquery->condition('age', 5, '>');
- $subquery->condition('age', 500, '<');
-
- $query->leftJoin($subquery, 'sq', 'tt.pid = sq.id');
- $query->join('test_one_blob', 'tb3', 'tt.pid = tb3.id');
-
-
-
- $query->preExecute();
- $query->getArguments();
- $str = (string) $query;
-
-
- $pos = strpos($str, 'db_condition_placeholder_0', 0);
- $pos2 = strpos($str, 'db_condition_placeholder_0', $pos + 1);
- $this->assertFalse($pos2, 'Condition placeholder is not repeated.');
- }
- }
-
- class DatabaseSelectPagerDefaultTestCase extends DatabaseTestCase {
-
- * Confirm that a pager query returns the correct results.
- *
- * Note that we have to make an HTTP request to a test page handler
- * because the pager depends on GET parameters.
- */
- function testEvenPagerQuery() {
-
-
-
-
- $limit = 2;
- $count = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $correct_number = $limit;
- $num_pages = floor($count / $limit);
-
-
- if (!($num_pages * $limit < $count)) {
- $num_pages--;
- }
-
- for ($page = 0; $page <= $num_pages; ++$page) {
- $this->backdropGet('database_test/pager_query_even/' . $limit, array('query' => array('page' => $page)));
- $data = json_decode($this->backdropGetContent());
-
- if ($page == $num_pages) {
- $correct_number = $count - ($limit * $page);
- }
-
- $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
- }
- }
-
-
- * Confirm that a pager query returns the correct results.
- *
- * Note that we have to make an HTTP request to a test page handler
- * because the pager depends on GET parameters.
- */
- function testOddPagerQuery() {
-
-
-
-
- $limit = 2;
- $count = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
-
- $correct_number = $limit;
- $num_pages = floor($count / $limit);
-
-
- if (!($num_pages * $limit < $count)) {
- $num_pages--;
- }
-
- for ($page = 0; $page <= $num_pages; ++$page) {
- $this->backdropGet('database_test/pager_query_odd/' . $limit, array('query' => array('page' => $page)));
- $data = json_decode($this->backdropGetContent());
-
- if ($page == $num_pages) {
- $correct_number = $count - ($limit * $page);
- }
-
- $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
- }
- }
-
-
- * Confirm that a pager query with inner pager query returns valid results.
- *
- * This is a regression test for #467984.
- */
- function testInnerPagerQuery() {
- $query = db_select('test', 't')->extend('PagerDefault');
- $query
- ->fields('t', array('age'))
- ->orderBy('age')
- ->limit(5);
-
- $outer_query = db_select($query);
- $outer_query->addField('subquery', 'age');
-
- $ages = $outer_query
- ->execute()
- ->fetchCol();
- $this->assertEqual($ages, array(25, 26, 27, 28), 'Inner pager query returned the correct ages.');
- }
-
-
- * Confirm that a paging query with a having expression returns valid results.
- *
- * This is a regression test for #467984.
- */
- function testHavingPagerQuery() {
- $query = db_select('test', 't')->extend('PagerDefault');
- $query
- ->fields('t', array('name'))
- ->orderBy('name')
- ->groupBy('name')
- ->having('MAX(age) > :count', array(':count' => 26))
- ->limit(5);
-
- $ages = $query
- ->execute()
- ->fetchCol();
- $this->assertEqual($ages, array('George', 'Ringo'), 'Pager query with having expression returned the correct ages.');
- }
-
-
- * Confirm that every pager gets a valid non-overlaping element ID.
- */
- function testElementNumbers() {
- $_GET['page'] = '3, 2, 1, 0';
-
- $name = db_select('test', 't')->extend('PagerDefault')
- ->element(2)
- ->fields('t', array('name'))
- ->orderBy('age')
- ->limit(1)
- ->execute()
- ->fetchField();
- $this->assertEqual($name, 'Paul', 'Pager query #1 with a specified element ID returned the correct results.');
-
-
-
- $name = db_select('test', 't')->extend('PagerDefault')
- ->element(1)
- ->fields('t', array('name'))
- ->orderBy('age')
- ->limit(1)
- ->execute()
- ->fetchField();
- $this->assertEqual($name, 'George', 'Pager query #2 with a specified element ID returned the correct results.');
-
- $name = db_select('test', 't')->extend('PagerDefault')
- ->fields('t', array('name'))
- ->orderBy('age')
- ->limit(1)
- ->execute()
- ->fetchField();
- $this->assertEqual($name, 'John', 'Pager query #3 with a generated element ID returned the correct results.');
-
- unset($_GET['page']);
- }
- }
-
-
- class DatabaseSelectTableSortDefaultTestCase extends DatabaseTestCase {
-
- * Confirm that a tablesort query returns the correct results.
- *
- * Note that we have to make an HTTP request to a test page handler
- * because the pager depends on GET parameters.
- */
- function testTableSortQuery() {
- $sorts = array(
- array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
- array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
- array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
- array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
-
-
- );
-
- foreach ($sorts as $sort) {
- $this->backdropGet('database_test/tablesort/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
- $data = json_decode($this->backdropGetContent());
-
- $first = array_shift($data->tasks);
- $last = array_pop($data->tasks);
-
- $this->assertEqual($first->task, $sort['first'], 'Items appear in the correct order.');
- $this->assertEqual($last->task, $sort['last'], 'Items appear in the correct order.');
- }
- }
-
-
- * Confirm that if a tablesort's orderByHeader is called before another orderBy, that the header happens first.
- *
- */
- function testTableSortQueryFirst() {
- $sorts = array(
- array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
- array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
- array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
- array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
-
-
- );
-
- foreach ($sorts as $sort) {
- $this->backdropGet('database_test/tablesort_first/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
- $data = json_decode($this->backdropGetContent());
-
- $first = array_shift($data->tasks);
- $last = array_pop($data->tasks);
-
- $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'])));
- $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'])));
- }
- }
-
-
- * Confirm that if a sort is not set in a tableselect form there is no error thrown when using the default.
- */
- function testTableSortDefaultSort() {
- $this->backdropGet('database_test/tablesort_default_sort');
-
-
- }
- }
-
- * Select tagging tests.
- *
- * Tags are a way to flag queries for alter hooks so they know
- * what type of query it is, such as "node_access".
- */
- class DatabaseTaggingTestCase extends DatabaseTestCase {
-
- * Confirm that a query has a "tag" added to it.
- */
- function testHasTag() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
-
- $this->assertTrue($query->hasTag('test'), 'hasTag() returned true.');
- $this->assertFalse($query->hasTag('other'), 'hasTag() returned false.');
- }
-
-
- * Test query tagging "has all of these tags" functionality.
- */
- function testHasAllTags() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
- $query->addTag('other');
-
- $this->assertTrue($query->hasAllTags('test', 'other'), 'hasAllTags() returned true.');
- $this->assertFalse($query->hasAllTags('test', 'stuff'), 'hasAllTags() returned false.');
- }
-
-
- * Test query tagging "has at least one of these tags" functionality.
- */
- function testHasAnyTag() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
-
- $this->assertTrue($query->hasAnyTag('test', 'other'), 'hasAnyTag() returned true.');
- $this->assertFalse($query->hasAnyTag('other', 'stuff'), 'hasAnyTag() returned false.');
- }
-
-
- * Confirm that an extended query has a "tag" added to it.
- */
- function testExtenderHasTag() {
- $query = db_select('test')
- ->extend('SelectQueryExtender');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
-
- $this->assertTrue($query->hasTag('test'), 'hasTag() returned true.');
- $this->assertFalse($query->hasTag('other'), 'hasTag() returned false.');
- }
-
-
- * Test extended query tagging "has all of these tags" functionality.
- */
- function testExtenderHasAllTags() {
- $query = db_select('test')
- ->extend('SelectQueryExtender');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
- $query->addTag('other');
-
- $this->assertTrue($query->hasAllTags('test', 'other'), 'hasAllTags() returned true.');
- $this->assertFalse($query->hasAllTags('test', 'stuff'), 'hasAllTags() returned false.');
- }
-
-
- * Test extended query tagging "has at least one of these tags" functionality.
- */
- function testExtenderHasAnyTag() {
- $query = db_select('test')
- ->extend('SelectQueryExtender');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $query->addTag('test');
-
- $this->assertTrue($query->hasAnyTag('test', 'other'), 'hasAnyTag() returned true.');
- $this->assertFalse($query->hasAnyTag('other', 'stuff'), 'hasAnyTag() returned false.');
- }
-
-
- * Test that we can attach meta data to a query object.
- *
- * This is how we pass additional context to alter hooks.
- */
- function testMetaData() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
-
- $data = array(
- 'a' => 'A',
- 'b' => 'B',
- );
-
- $query->addMetaData('test', $data);
-
- $return = $query->getMetaData('test');
- $this->assertEqual($data, $return, 'Corect metadata returned.');
-
- $return = $query->getMetaData('nothere');
- $this->assertNull($return, 'Non-existent key returned NULL.');
- }
- }
-
- * Select alter tests.
- *
- * @see database_test_query_alter()
- */
- class DatabaseAlterTestCase extends DatabaseTestCase {
-
- * Test that we can do basic alters.
- */
- function testSimpleAlter() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
- $query->addTag('database_test_alter_add_range');
-
- $result = $query->execute();
-
- $num_records = 0;
- foreach ($result as $record) {
- $num_records++;
- }
-
- $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
- }
-
-
- * Test that we can alter the joins on a query.
- */
- function testAlterWithJoin() {
- $query = db_select('test_task');
- $tid_field = $query->addField('test_task', 'tid');
- $task_field = $query->addField('test_task', 'task');
- $query->orderBy($task_field);
- $query->addTag('database_test_alter_add_join');
-
- $result = $query->execute();
-
- $records = $result->fetchAll();
-
- $this->assertEqual(count($records), 2, 'Returned the correct number of rows.');
-
- $this->assertEqual($records[0]->name, 'George', 'Correct data retrieved.');
- $this->assertEqual($records[0]->$tid_field, 4, 'Correct data retrieved.');
- $this->assertEqual($records[0]->$task_field, 'sing', 'Correct data retrieved.');
- $this->assertEqual($records[1]->name, 'George', 'Correct data retrieved.');
- $this->assertEqual($records[1]->$tid_field, 5, 'Correct data retrieved.');
- $this->assertEqual($records[1]->$task_field, 'sleep', 'Correct data retrieved.');
- }
-
-
- * Test that we can alter a query's conditionals.
- */
- function testAlterChangeConditional() {
- $query = db_select('test_task');
- $tid_field = $query->addField('test_task', 'tid');
- $pid_field = $query->addField('test_task', 'pid');
- $task_field = $query->addField('test_task', 'task');
- $people_alias = $query->join('test', 'people', "test_task.pid = people.id");
- $name_field = $query->addField($people_alias, 'name', 'name');
- $query->condition('test_task.tid', '1');
- $query->orderBy($tid_field);
- $query->addTag('database_test_alter_change_conditional');
-
- $result = $query->execute();
-
- $records = $result->fetchAll();
-
- $this->assertEqual(count($records), 1, 'Returned the correct number of rows.');
- $this->assertEqual($records[0]->$name_field, 'John', 'Correct data retrieved.');
- $this->assertEqual($records[0]->$tid_field, 2, 'Correct data retrieved.');
- $this->assertEqual($records[0]->$pid_field, 1, 'Correct data retrieved.');
- $this->assertEqual($records[0]->$task_field, 'sleep', 'Correct data retrieved.');
- }
-
-
- * Test that we can alter the fields of a query.
- */
- function testAlterChangeFields() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addField('test', 'age', 'age');
- $query->orderBy('name');
- $query->addTag('database_test_alter_change_fields');
-
- $record = $query->execute()->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Correct data retrieved.');
- $this->assertFalse(isset($record->$age_field), 'Age field not found, as intended.');
- }
-
-
- * Test that we can alter expressions in the query.
- */
- function testAlterExpression() {
- $query = db_select('test');
- $name_field = $query->addField('test', 'name');
- $age_field = $query->addExpression("age*2", 'double_age');
- $query->condition('age', 27);
- $query->addTag('database_test_alter_change_expressions');
- $result = $query->execute();
-
-
- $record = $result->fetch();
-
- $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
- $this->assertEqual($record->$age_field, 27*3, 'Fetched age expression is correct.');
- }
-
-
- * Test that we can remove a range() value from a query. This also tests hook_query_TAG_alter().
- */
- function testAlterRemoveRange() {
- $query = db_select('test');
- $query->addField('test', 'name');
- $query->addField('test', 'age', 'age');
- $query->range(0, 2);
- $query->addTag('database_test_alter_remove_range');
-
- $num_records = count($query->execute()->fetchAll());
-
- $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
- }
-
-
- * Test that we can do basic alters on subqueries.
- */
- function testSimpleAlterSubquery() {
-
- $subquery = db_select('test', 'p');
- $subquery->addField('p', 'name');
- $subquery->addField('p', 'id');
-
- $subquery->condition('age', 27);
- $subquery->addExpression("age*2", 'double_age');
-
- $subquery->addTag('database_test_alter_change_expressions');
-
-
- $query = db_select('test_task', 'tt');
- $query->join($subquery, 'pq', 'pq.id = tt.pid');
- $age_field = $query->addField('pq', 'double_age');
- $name_field = $query->addField('pq', 'name');
-
- $record = $query->execute()->fetch();
- $this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
- $this->assertEqual($record->$age_field, 27*3, 'Fetched age expression is correct.');
- }
- }
-
- * Regression tests.
- */
- class DatabaseRegressionTestCase extends DatabaseTestCase {
- function setUp() {
- parent::setUp(array('node'));
- }
-
-
- * Regression test for #310447.
- *
- * Tries to insert non-ascii UTF-8 data in a database column and checks
- * if its stored properly.
- */
- function testRegression_310447() {
-
- $name = str_repeat("é", 255);
- db_insert('test')
- ->fields(array(
- 'name' => $name,
- 'age' => 20,
- 'job' => 'Dancer',
- ))->execute();
-
- $from_database = db_query('SELECT name FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
- $this->assertIdentical($name, $from_database, "The database handles UTF-8 characters cleanly.");
- }
-
-
- * Test the db_table_exists() function.
- */
- function testDBTableExists() {
- $this->assertIdentical(TRUE, db_table_exists('node'), 'Returns true for existent table.');
- $this->assertIdentical(FALSE, db_table_exists('nosuchtable'), 'Returns false for nonexistent table.');
- }
-
-
- * Test the db_field_exists() function.
- */
- function testDBFieldExists() {
- $this->assertIdentical(TRUE, db_field_exists('node', 'nid'), 'Returns true for existent column.');
- $this->assertIdentical(FALSE, db_field_exists('node', 'nosuchcolumn'), 'Returns false for nonexistent column.');
- }
-
-
- * Test the db_index_exists() function.
- */
- function testDBIndexExists() {
- $this->assertIdentical(TRUE, db_index_exists('node', 'node_created'), 'Returns true for existent index.');
- $this->assertIdentical(FALSE, db_index_exists('node', 'nosuchindex'), 'Returns false for nonexistent index.');
- }
- }
-
- * Query logging tests.
- */
- class DatabaseLoggingTestCase extends DatabaseTestCase {
-
- * Test that we can log the existence of a query.
- */
- function testEnableLogging() {
- $log = Database::startLog('testing');
-
- db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
- db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchCol();
-
-
- call_user_func_array('db_query', array('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo')))->fetchCol();
-
- $queries = Database::getLog('testing', 'default');
-
- $this->assertEqual(count($queries), 3, 'Correct number of queries recorded.');
-
- foreach ($queries as $query) {
- $this->assertEqual($query['caller']['function'], __FUNCTION__, 'Correct function in query log.');
- }
- }
-
-
- * Test that we can run two logs in parallel.
- */
- function testEnableMultiLogging() {
- Database::startLog('testing1');
-
- db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
-
- Database::startLog('testing2');
-
- db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'))->fetchCol();
-
- $queries1 = Database::getLog('testing1');
- $queries2 = Database::getLog('testing2');
-
- $this->assertEqual(count($queries1), 2, 'Correct number of queries recorded for log 1.');
- $this->assertEqual(count($queries2), 1, 'Correct number of queries recorded for log 2.');
- }
-
-
- * Test that we can log queries against multiple targets on the same connection.
- */
- function testEnableTargetLogging() {
-
-
- $connection_info = Database::getConnectionInfo('default');
- Database::addConnectionInfo('default', 'slave', $connection_info['default']);
-
- Database::startLog('testing1');
-
- db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
-
- db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'slave'));
-
- $queries1 = Database::getLog('testing1');
-
- $this->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
- $this->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
- $this->assertEqual($queries1[1]['target'], 'slave', 'Second query used slave target.');
- }
-
-
- * Test that logs to separate targets collapse to the same connection properly.
- *
- * This test is identical to the one above, except that it doesn't create
- * a fake target so the query should fall back to running on the default
- * target.
- */
- function testEnableTargetLoggingNoTarget() {
- Database::startLog('testing1');
-
- db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
-
-
-
-
-
-
- db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'fake'))->fetchCol();
-
- $queries1 = Database::getLog('testing1');
-
- $this->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
- $this->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
- $this->assertEqual($queries1[1]['target'], 'default', 'Second query used default target as fallback.');
- }
-
-
- * Test that we can log queries separately on different connections.
- */
- function testEnableMultiConnectionLogging() {
-
-
- $connection_info = Database::getConnectionInfo('default');
- Database::addConnectionInfo('test2', 'default', $connection_info['default']);
-
- Database::startLog('testing1');
- Database::startLog('testing1', 'test2');
-
- db_query('SELECT name FROM {test} WHERE age > :age', array(':age' => 25))->fetchCol();
-
- $old_key = db_set_active('test2');
-
- db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Ringo'), array('target' => 'slave'))->fetchCol();
-
- db_set_active($old_key);
-
- $queries1 = Database::getLog('testing1');
- $queries2 = Database::getLog('testing1', 'test2');
-
- $this->assertEqual(count($queries1), 1, 'Correct number of queries recorded for first connection.');
- $this->assertEqual(count($queries2), 1, 'Correct number of queries recorded for second connection.');
- }
- }
-
- * Query serialization tests.
- */
- class DatabaseSerializeQueryTestCase extends DatabaseTestCase {
-
- * Confirm that a query can be serialized and unserialized.
- */
- function testSerializeQuery() {
- $query = db_select('test');
- $query->addField('test', 'age');
- $query->condition('name', 'Ringo');
-
-
- $query = unserialize(serialize($query));
- $results = $query->execute()->fetchCol();
- $this->assertEqual($results[0], 28, 'Query properly executed after unserialization.');
- }
- }
-
- * Range query tests.
- */
- class DatabaseRangeQueryTestCase extends BackdropWebTestCase {
- function setUp() {
- parent::setUp('database_test');
- }
-
-
- * Confirm that range query work and return correct result.
- */
- function testRangeQuery() {
-
- $range_rows = db_query_range("SELECT name FROM {system} ORDER BY name", 2, 3)->fetchAll();
- $this->assertEqual(count($range_rows), 3, 'Range query work and return correct number of rows.');
-
-
- $raw_rows = db_query('SELECT name FROM {system} ORDER BY name')->fetchAll();
- $raw_rows = array_slice($raw_rows, 2, 3);
- $this->assertEqual($range_rows, $raw_rows, 'Range query work and return target data.');
- }
- }
-
- * Temporary query tests.
- */
- class DatabaseTemporaryQueryTestCase extends BackdropWebTestCase {
- function setUp() {
- parent::setUp('database_test');
- }
-
-
- * Return the number of rows of a table.
- */
- function countTableRows($table_name) {
- return db_select($table_name)->countQuery()->execute()->fetchField();
- }
-
-
- * Confirm that temporary tables work and are limited to one request.
- */
- function testTemporaryQuery() {
- $this->backdropGet('database_test/db_query_temporary');
- $data = json_decode($this->backdropGetContent());
- if ($data) {
- $this->assertEqual($this->countTableRows("system"), $data->row_count, 'The temporary table contains the correct amount of rows.');
- $this->assertFalse(db_table_exists($data->table_name), 'The temporary table is, indeed, temporary.');
- }
- else {
- $this->fail("The creation of the temporary table failed.");
- }
-
-
- $table_name_system = db_query_temporary('SELECT status FROM {system}', array());
- $table_name_users = db_query_temporary('SELECT uid FROM {users}', array());
-
- $this->assertEqual($this->countTableRows($table_name_system), $this->countTableRows("system"), 'A temporary table was created successfully in this request.');
- $this->assertEqual($this->countTableRows($table_name_users), $this->countTableRows("users"), 'A second temporary table was created successfully in this request.');
-
-
-
- $sql = "
- -- Let's select some rows into a temporary table
- SELECT name FROM {test}
- ";
- $table_name_test = db_query_temporary($sql, array());
- $this->assertEqual($this->countTableRows($table_name_test), $this->countTableRows('test'), 'Leading white space and comments do not interfere with temporary table creation.');
- }
- }
-
- * Test how the current database driver interprets the SQL syntax.
- *
- * In order to ensure consistent SQL handling throughout Backdrop
- * across multiple kinds of database systems, we test that the
- * database system interprets SQL syntax in an expected fashion.
- */
- class DatabaseBasicSyntaxTestCase extends DatabaseTestCase {
-
- * Test for string concatenation.
- */
- function testConcatLiterals() {
- $result = db_query('SELECT CONCAT(:a1, CONCAT(:a2, CONCAT(:a3, CONCAT(:a4, :a5))))', array(
- ':a1' => 'This',
- ':a2' => ' ',
- ':a3' => 'is',
- ':a4' => ' a ',
- ':a5' => 'test.',
- ));
- $this->assertIdentical($result->fetchField(), 'This is a test.', 'Basic CONCAT works.');
- }
-
-
- * Test for string concatenation with field values.
- */
- function testConcatFields() {
- $result = db_query('SELECT CONCAT(:a1, CONCAT(name, CONCAT(:a2, CONCAT(age, :a3)))) FROM {test} WHERE age = :age', array(
- ':a1' => 'The age of ',
- ':a2' => ' is ',
- ':a3' => '.',
- ':age' => 25,
- ));
- $this->assertIdentical($result->fetchField(), 'The age of John is 25.', 'Field CONCAT works.');
- }
-
-
- * Tests string concatenation with separator.
- */
- function testConcatWsLiterals() {
- $result = db_query("SELECT CONCAT_WS(', ', :a1, NULL, :a2, :a3, :a4)", array(
- ':a1' => 'Hello',
- ':a2' => NULL,
- ':a3' => '',
- ':a4' => 'world.',
- ));
- $this->assertIdentical($result->fetchField(), 'Hello, , world.');
- }
-
-
- * Tests string concatenation with separator, with field values.
- */
- function testConcatWsFields() {
- $result = db_query("SELECT CONCAT_WS('-', :a1, name, :a2, age) FROM {test} WHERE age = :age", array(
- ':a1' => 'name',
- ':a2' => 'age',
- ':age' => 25,
- ));
- $this->assertIdentical($result->fetchField(), 'name-John-age-25');
- }
-
-
- * Test escaping of LIKE wildcards.
- */
- function testLikeEscape() {
- db_insert('test')
- ->fields(array(
- 'name' => 'Ring_',
- ))
- ->execute();
-
-
- $num_matches = db_select('test', 't')
- ->condition('name', 'Ring_', 'LIKE')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertIdentical($num_matches, '2', 'Found 2 records.');
-
- $num_matches = db_select('test', 't')
- ->condition('name', db_like('Ring_'), 'LIKE')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertIdentical($num_matches, '1', 'Found 1 record.');
- }
-
-
- * Test LIKE query containing a backslash.
- */
- function testLikeBackslash() {
- db_insert('test')
- ->fields(array('name'))
- ->values(array(
- 'name' => 'abcde\f',
- ))
- ->values(array(
- 'name' => 'abc%\_',
- ))
- ->execute();
-
-
-
- $num_matches = db_select('test', 't')
- ->condition('name', 'abc%\\\\_', 'LIKE')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertIdentical($num_matches, '2', 'Found 2 records.');
-
- $num_matches = db_select('test', 't')
- ->condition('name', db_like('abc%\_'), 'LIKE')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertIdentical($num_matches, '1', 'Found 1 record.');
- }
- }
-
- * Test case sensitivity handling.
- */
- class DatabaseCaseSensitivityTestCase extends DatabaseTestCase {
-
- * Test BINARY collation in MySQL.
- */
- function testCaseSensitiveInsert() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
-
- $john = db_insert('test')
- ->fields(array(
- 'name' => 'john',
- 'age' => 2,
- 'job' => 'Baby',
- ))
- ->execute();
-
- $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
- $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'john'))->fetchField();
- $this->assertIdentical($saved_age, '2', 'Can retrieve after inserting.');
- }
- }
-
- * Test invalid data handling.
- */
- class DatabaseInvalidDataTestCase extends DatabaseTestCase {
-
- * Traditional SQL database systems abort inserts when invalid data is encountered.
- */
- function testInsertDuplicateData() {
-
- try {
- db_insert('test')
- ->fields(array('name', 'age', 'job'))
- ->values(array(
- 'name' => 'Elvis',
- 'age' => 63,
- 'job' => 'Singer',
- ))->values(array(
- 'name' => 'John',
- 'age' => 17,
- 'job' => 'Consultant',
- ))
- ->values(array(
- 'name' => 'Frank',
- 'age' => 75,
- 'job' => 'Singer',
- ))
- ->execute();
- $this->fail('Insert succeedded when it should not have.');
- }
- catch (Exception $e) {
-
- $name = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 63))->fetchField();
-
- if ($name == 'Elvis') {
- if (!Database::getConnection()->supportsTransactions()) {
-
-
-
-
- $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");
- }
- else {
- $this->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
- }
- }
- else {
- $this->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
- }
-
-
- $record = db_select('test')
- ->fields('test', array('name', 'age'))
- ->condition('age', array(17, 75), 'IN')
- ->execute()->fetchObject();
-
- $this->assertFalse($record, 'The rest of the insert aborted as expected.');
- }
- }
-
- }
-
- * Backdrop-specific SQL syntax tests.
- */
- class DatabaseQueryTestCase extends DatabaseTestCase {
-
- * Test that we can specify an array of values in the query by passing in an array.
- */
- function testArraySubstitution() {
- $names = db_query('SELECT name FROM {test} WHERE age IN (:ages) ORDER BY age', array(':ages' => array(25, 26, 27)))->fetchAll();
-
- $this->assertEqual(count($names), 3, 'Correct number of names returned');
- }
-
-
- * Test SQL injection via database query array arguments.
- */
- public function testArrayArgumentsSQLInjection() {
-
- $condition = array(
- "1 ;INSERT INTO {test} SET name = 'test12345678'; -- " => '',
- '1' => '',
- );
- try {
- db_query("SELECT * FROM {test} WHERE name = :name", array(':name' => $condition))->fetchObject();
- $this->fail('SQL injection attempt via array arguments should result in a PDOException.');
- }
- catch (PDOException $e) {
- $this->pass('SQL injection attempt via array arguments should result in a PDOException.');
- }
-
-
-
- $result = db_select('test')
- ->condition('name', 'test12345678')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertFalse($result, 'SQL injection attempt did not result in a row being inserted in the database table.');
- }
- }
-
- * Test transaction support, particularly nesting.
- *
- * We test nesting by having two transaction layers, an outer and inner. The
- * outer layer encapsulates the inner layer. Our transaction nesting abstraction
- * should allow the outer layer function to call any function it wants,
- * especially the inner layer that starts its own transaction, and be
- * confident that, when the function it calls returns, its own transaction
- * is still "alive."
- *
- * Call structure:
- * transactionOuterLayer()
- * Start transaction
- * transactionInnerLayer()
- * Start transaction (does nothing in database)
- * [Maybe decide to roll back]
- * Do more stuff
- * Should still be in transaction A
- *
- */
- class DatabaseTransactionTestCase extends DatabaseTestCase {
-
- * Helper method for transaction unit test. This "outer layer" transaction
- * starts and then encapsulates the "inner layer" transaction. This nesting
- * is used to evaluate whether the the database transaction API properly
- * supports nesting. By "properly supports," we mean the outer transaction
- * continues to exist regardless of what functions are called and whether
- * those functions start their own transactions.
- *
- * In contrast, a typical database would commit the outer transaction, start
- * a new transaction for the inner layer, commit the inner layer transaction,
- * and then be confused when the outer layer transaction tries to commit its
- * transaction (which was already committed when the inner transaction
- * started).
- *
- * @param $suffix
- * Suffix to add to field values to differentiate tests.
- * @param $rollback
- * Whether or not to try rolling back the transaction when we're done.
- * @param $ddl_statement
- * Whether to execute a DDL statement during the inner transaction.
- */
- protected function transactionOuterLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
- $connection = Database::getConnection();
- $depth = $connection->transactionDepth();
- $txn = db_transaction();
-
-
- db_insert('test')
- ->fields(array(
- 'name' => 'David' . $suffix,
- 'age' => '24',
- ))
- ->execute();
-
- $this->assertTrue($connection->inTransaction(), 'In transaction before calling nested transaction.');
-
-
-
- $this->transactionInnerLayer($suffix, $rollback, $ddl_statement);
-
- $this->assertTrue($connection->inTransaction(), 'In transaction after calling nested transaction.');
-
- if ($rollback) {
-
-
- $txn->rollback();
- $this->assertTrue(($connection->transactionDepth() == $depth), 'Transaction has rolled back to the last savepoint after calling rollback().');
- }
- }
-
-
- * Helper method for transaction unit tests. This "inner layer" transaction
- * is either used alone or nested inside of the "outer layer" transaction.
- *
- * @param $suffix
- * Suffix to add to field values to differentiate tests.
- * @param $rollback
- * Whether or not to try rolling back the transaction when we're done.
- * @param $ddl_statement
- * Whether to execute a DDL statement during the transaction.
- */
- protected function transactionInnerLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
- $connection = Database::getConnection();
-
- $depth = $connection->transactionDepth();
-
-
-
-
- $txn = db_transaction();
-
- $depth2 = $connection->transactionDepth();
- $this->assertTrue($depth < $depth2, 'Transaction depth is has increased with new transaction.');
-
-
- db_insert('test')
- ->fields(array(
- 'name' => 'Daniel' . $suffix,
- 'age' => '19',
- ))
- ->execute();
-
- $this->assertTrue($connection->inTransaction(), 'In transaction inside nested transaction.');
-
- if ($ddl_statement) {
- $table = array(
- 'fields' => array(
- 'id' => array(
- 'type' => 'serial',
- 'unsigned' => TRUE,
- 'not null' => TRUE,
- ),
- ),
- 'primary key' => array('id'),
- );
- db_create_table('database_test_1', $table);
-
- $this->assertTrue($connection->inTransaction(), 'In transaction inside nested transaction.');
- }
-
- if ($rollback) {
-
-
- $txn->rollback();
- $this->assertTrue(($connection->transactionDepth() == $depth), 'Transaction has rolled back to the last savepoint after calling rollback().');
- }
- }
-
-
- * Test transaction rollback on a database that supports transactions.
- *
- * If the active connection does not support transactions, this test does nothing.
- */
- function testTransactionRollBackSupported() {
-
- if (!Database::getConnection()->supportsTransactions()) {
- return;
- }
- try {
-
- $this->transactionOuterLayer('B', TRUE);
-
-
-
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidB'))->fetchField();
- $this->assertNotIdentical($saved_age, '24', 'Cannot retrieve DavidB row after commit.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielB'))->fetchField();
- $this->assertNotIdentical($saved_age, '19', 'Cannot retrieve DanielB row after commit.');
- }
- catch (Exception $e) {
- $this->fail($e->getMessage());
- }
- }
-
-
- * Test transaction rollback on a database that does not support transactions.
- *
- * If the active driver supports transactions, this test does nothing.
- */
- function testTransactionRollBackNotSupported() {
-
- if (Database::getConnection()->supportsTransactions()) {
- return;
- }
- try {
-
- $this->transactionOuterLayer('B', TRUE);
-
-
-
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidB'))->fetchField();
- $this->assertIdentical($saved_age, '24', 'DavidB not rolled back, since transactions are not supported.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielB'))->fetchField();
- $this->assertIdentical($saved_age, '19', 'DanielB not rolled back, since transactions are not supported.');
- }
- catch (Exception $e) {
- $this->fail($e->getMessage());
- }
- }
-
-
- * Test committed transaction.
- *
- * The behavior of this test should be identical for connections that support
- * transactions and those that do not.
- */
- function testCommittedTransaction() {
- try {
-
- $this->transactionOuterLayer('A');
-
-
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DavidA'))->fetchField();
- $this->assertIdentical($saved_age, '24', 'Can retrieve DavidA row after commit.');
- $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'DanielA'))->fetchField();
- $this->assertIdentical($saved_age, '19', 'Can retrieve DanielA row after commit.');
- }
- catch (Exception $e) {
- $this->fail($e->getMessage());
- }
- }
-
-
- * Test the compatibility of transactions with DDL statements.
- */
- function testTransactionWithDdlStatement() {
-
- $transaction = db_transaction();
- $this->insertRow('row');
- $this->executeDDLStatement();
- unset($transaction);
- $this->assertRowPresent('row');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->executeDDLStatement();
- $this->insertRow('row');
- unset($transaction);
- $this->assertRowPresent('row');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $transaction2 = db_transaction();
- $this->executeDDLStatement();
- unset($transaction2);
- $transaction3 = db_transaction();
- $this->insertRow('row');
- unset($transaction3);
- unset($transaction);
- $this->assertRowPresent('row');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $transaction2 = db_transaction();
- $this->executeDDLStatement();
- unset($transaction2);
- $transaction3 = db_transaction();
- $this->insertRow('row');
- $transaction3->rollback();
- unset($transaction3);
- unset($transaction);
- $this->assertRowAbsent('row');
-
-
- if (Database::getConnection()->supportsTransactionalDDL()) {
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('row');
- $this->executeDDLStatement();
- $transaction->rollback();
- unset($transaction);
- $this->assertRowAbsent('row');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $transaction2 = db_transaction();
- $this->executeDDLStatement();
- unset($transaction2);
- $transaction3 = db_transaction();
- $this->insertRow('row');
- unset($transaction3);
- $transaction->rollback();
- unset($transaction);
- $this->assertRowAbsent('row');
- }
- else {
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('row');
- $this->executeDDLStatement();
-
- try {
- $transaction->rollback();
- unset($transaction);
-
-
-
- }
- catch (DatabaseTransactionNoActiveException $e) {
- $this->pass('Rolling back a transaction containing DDL should fail.');
- }
- $this->assertRowPresent('row');
- }
- }
-
-
- * Insert a single row into the testing table.
- */
- protected function insertRow($name) {
- db_insert('test')
- ->fields(array(
- 'name' => $name,
- ))
- ->execute();
- }
-
-
- * Execute a DDL statement.
- */
- protected function executeDDLStatement() {
- static $count = 0;
- $table = array(
- 'fields' => array(
- 'id' => array(
- 'type' => 'serial',
- 'unsigned' => TRUE,
- 'not null' => TRUE,
- ),
- ),
- 'primary key' => array('id'),
- );
- db_create_table('database_test_' . ++$count, $table);
- }
-
-
- * Start over for a new test.
- */
- protected function cleanUp() {
- db_truncate('test')
- ->execute();
- }
-
-
- * Assert that a given row is present in the test table.
- *
- * @param $name
- * The name of the row.
- * @param $message
- * The message to log for the assertion.
- */
- function assertRowPresent($name, $message = NULL) {
- if (!isset($message)) {
- $message = format_string('Row %name is present.', array('%name' => $name));
- }
- $present = (boolean) db_query('SELECT 1 FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
- return $this->assertTrue($present, $message);
- }
-
-
- * Assert that a given row is absent from the test table.
- *
- * @param $name
- * The name of the row.
- * @param $message
- * The message to log for the assertion.
- */
- function assertRowAbsent($name, $message = NULL) {
- if (!isset($message)) {
- $message = format_string('Row %name is absent.', array('%name' => $name));
- }
- $present = (boolean) db_query('SELECT 1 FROM {test} WHERE name = :name', array(':name' => $name))->fetchField();
- return $this->assertFalse($present, $message);
- }
-
-
- * Test transaction stacking and commit / rollback.
- */
- function testTransactionStacking() {
-
- if (!Database::getConnection()->supportsTransactions()) {
- return;
- }
-
- $database = Database::getConnection();
-
-
- $transaction = db_transaction();
- $this->insertRow('outer');
- $transaction2 = db_transaction();
- $this->insertRow('inner');
-
- unset($transaction2);
- $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the inner transaction');
-
- unset($transaction);
- $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the outer transaction');
- $this->assertRowPresent('outer');
- $this->assertRowPresent('inner');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('outer');
- $transaction2 = db_transaction();
- $this->insertRow('inner');
-
- unset($transaction);
- $this->insertRow('inner-after-outer-commit');
- $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
-
- unset($transaction2);
- $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
- $this->assertRowPresent('outer');
- $this->assertRowPresent('inner');
- $this->assertRowPresent('inner-after-outer-commit');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('outer');
- $transaction2 = db_transaction();
- $this->insertRow('inner');
-
- $transaction2->rollback();
- unset($transaction2);
- $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
-
- $this->insertRow('outer-after-inner-rollback');
- unset($transaction);
- $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
- $this->assertRowPresent('outer');
- $this->assertRowAbsent('inner');
- $this->assertRowPresent('outer-after-inner-rollback');
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('outer');
- $transaction2 = db_transaction();
- $this->insertRow('inner');
-
- unset($transaction);
- $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction');
-
- $transaction2->rollback();
- unset($transaction2);
- $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction');
- $this->assertRowPresent('outer');
- $this->assertRowAbsent('inner');
-
-
-
-
- $this->cleanUp();
- $transaction = db_transaction();
- $this->insertRow('outer');
- $transaction2 = db_transaction();
- $this->insertRow('inner');
- $transaction3 = db_transaction();
- $this->insertRow('inner2');
-
- try {
- $transaction->rollback();
- unset($transaction);
- $this->fail('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
- }
- catch (DatabaseTransactionOutOfOrderException $e) {
- $this->pass('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
- }
- $this->assertFalse($database->inTransaction(), 'No more in a transaction after rolling back the outer transaction');
-
- unset($transaction3);
- $this->pass('Trying to commit an inner transaction resulted in an exception.');
-
- try {
- $transaction->rollback();
- unset($transaction2);
- $this->fail('Trying to commit an inner transaction resulted in an exception.');
- }
- catch (DatabaseTransactionNoActiveException $e) {
- $this->pass('Trying to commit an inner transaction resulted in an exception.');
- }
- $this->assertRowAbsent('outer');
- $this->assertRowAbsent('inner');
- $this->assertRowAbsent('inner2');
- }
- }
-
- * Test proposed new data types for the schema API.
- */
- class DatabaseExtraTypesTestCase extends BackdropWebTestCase {
-
-
- * Test the date data type.
- */
- function testDateField() {
- try {
- $date_table = array(
- 'fields' => array(
- 'date_field' => array(
- 'description' => t('Test Date field'),
- 'type' => 'date',
- 'not null' => FALSE,
- ),
- ),
- );
- db_create_table('date_table', $date_table);
- $this->assertTrue(db_table_exists('date_table'), t('Created table with date field'));
-
- db_insert('date_table')->fields(array('date_field'))
- ->values(array('date_field' => '2001-01-01'))
- ->values(array('date_field' => '1856-12-31'))
- ->values(array('date_field' => '2100-06-30'))
- ->execute();
-
- $num_records = (int) db_query('SELECT COUNT(*) FROM {date_table}')->fetchField();
- $this->assertEqual($num_records, 3, t('Inserted 3 records, and counted 3 records'));
- $res = db_query('SELECT date_field from {date_table} ORDER BY date_field');
-
- $date = $res->fetch()->date_field;
- $this->assertEqual($date, '1856-12-31', t('Date retrieved in order @date', array('@date' => $date)));
- $date = $res->fetch()->date_field;
- $this->assertEqual($date, '2001-01-01', t('Date retrieved in order @date', array('@date' => $date)));
- $date = $res->fetch()->date_field;
- $this->assertEqual($date, '2100-06-30', t('Date retrieved in order @date', array('@date' => $date)));
-
-
- db_drop_table('date_table');
- $this->assertFalse(db_table_exists('date_table'), t('Dropped table with date field'));
-
- } catch (Exception $e) {
- $this->fail($e->getMessage());
- }
-
- }
-
-
- * Test the time data type.
- */
- function testTimeField() {
- try {
- $time_table = array(
- 'fields' => array(
- 'time_field' => array(
- 'description' => t('Test Time field'),
- 'type' => 'time',
- 'not null' => FALSE,
- ),
- ),
- );
- db_create_table('time_table', $time_table);
- $this->assertTrue(db_table_exists('time_table'), t('Created table with time field'));
-
- db_insert('time_table')->fields(array('time_field'))
- ->values(array('time_field' => '12:59:00'))
- ->values(array('time_field' => '00:01:00'))
- ->values(array('time_field' => '23:17:00'))
- ->execute();
-
- $num_records = (int) db_query('SELECT COUNT(*) FROM {time_table}')->fetchField();
- $this->assertEqual($num_records, 3, t('Inserted 3 records, and counted 3 records'));
- $res = db_query('SELECT time_field from {time_table} ORDER BY time_field');
-
- $time = $res->fetch()->time_field;
- $this->assertEqual($time, '00:01:00', t('Time retrieved in order @time', array('@time' => $time)));
- $time = $res->fetch()->time_field;
- $this->assertEqual($time, '12:59:00', t('Time retrieved in order @time', array('@time' => $time)));
- $time = $res->fetch()->time_field;
- $this->assertEqual($time, '23:17:00', t('Time retrieved in order @time', array('@time' => $time)));
-
- db_drop_table('time_table');
- $this->assertFalse(db_table_exists('time_table'), t('Dropped table with time field'));
- } catch (Exception $e) {
- $this->fail($e->getMessage());
- }
-
- }
-
- }
-
- * Check the sequences API.
- */
- class DatabaseNextIdCase extends BackdropWebTestCase {
-
- * Test that the sequences API work.
- */
- function testDbNextId() {
- $first = db_next_id();
- $second = db_next_id();
-
-
-
- $this->assertEqual($first + 1, $second, 'The second call from a sequence provides a number increased by one.');
- $result = db_next_id(1000);
- $this->assertEqual($result, 1001, 'Sequence provides a larger number than the existing ID.');
- }
- }
-
- * Tests the empty pseudo-statement class.
- */
- class DatabaseEmptyStatementTestCase extends BackdropWebTestCase {
-
- * Test that the empty result set behaves as empty.
- */
- function testEmpty() {
- $result = new DatabaseStatementEmpty();
-
- $this->assertTrue($result instanceof DatabaseStatementInterface, 'Class implements expected interface');
- $this->assertNull($result->fetchObject(), 'Null result returned.');
- }
-
-
- * Test that the empty result set iterates safely.
- */
- function testEmptyIteration() {
- $result = new DatabaseStatementEmpty();
-
- foreach ($result as $record) {
- $this->fail('Iterating empty result set should not iterate.');
- return;
- }
-
- $this->pass('Iterating empty result set skipped iteration.');
- }
-
-
- * Test that the empty result set mass-fetches in an expected way.
- */
- function testEmptyFetchAll() {
- $result = new DatabaseStatementEmpty();
-
- $this->assertEqual($result->fetchAll(), array(), 'Empty array returned from empty result set.');
- }
- }
-
- * Tests management of database connections.
- */
- class ConnectionUnitTest extends BackdropUnitTestCase {
-
- protected $key;
- protected $target;
-
- protected $monitor;
- protected $originalCount;
-
- function setUp() {
- parent::setUp();
-
- $this->key = 'default';
- $this->originalTarget = 'default';
- $this->target = 'DatabaseConnectionUnitTest';
-
-
-
-
-
- $connection_info = Database::getConnectionInfo('default');
- $this->skipTest = ($connection_info['default']['driver'] != 'mysql');
- if ($this->skipTest) {
-
-
- $this->pass('This test is only compatible with MySQL.');
- }
-
-
-
-
- $connection_info = Database::getConnectionInfo('default');
- Database::addConnectionInfo('default', 'monitor', $connection_info['default']);
- global $databases;
- $databases['default']['monitor'] = $connection_info['default'];
- $this->monitor = Database::getConnection('monitor');
- }
-
-
- * Adds a new database connection info to Database.
- */
- protected function addConnection() {
-
- $connection_info = Database::getConnectionInfo($this->key);
- Database::addConnectionInfo($this->key, $this->target, $connection_info[$this->originalTarget]);
-
-
- $info = Database::getConnectionInfo($this->key);
-
- $this->assertIdentical($info[$this->target], $connection_info[$this->key], 'New connection info found.');
- }
-
-
- * Returns the connection ID of the current test connection.
- *
- * @return integer
- */
- protected function getConnectionID() {
- return (int) Database::getConnection($this->target, $this->key)->query('SELECT CONNECTION_ID()')->fetchField();
- }
-
-
- * Asserts that a connection ID exists.
- *
- * @param integer $id
- * The connection ID to verify.
- */
- protected function assertConnection($id) {
- $list = $this->monitor->query('SHOW PROCESSLIST')->fetchAllKeyed(0, 0);
- return $this->assertTrue(isset($list[$id]), format_string('Connection ID @id found.', array('@id' => $id)));
- }
-
-
- * Asserts that a connection ID does not exist.
- *
- * @param integer $id
- * The connection ID to verify.
- */
- protected function assertNoConnection($id) {
- $list = $this->monitor->query('SHOW PROCESSLIST')->fetchAllKeyed(0, 0);
- return $this->assertFalse(isset($list[$id]), format_string('Connection ID @id not found.', array('@id' => $id)));
- }
-
-
- * Tests Database::closeConnection() without query.
- *
- * @todo getConnectionID() executes a query.
- */
- function testOpenClose() {
- if ($this->skipTest) {
- return;
- }
-
- $this->addConnection();
- $id = $this->getConnectionID();
- Database::getConnection($this->target, $this->key);
-
-
- $this->assertConnection($id);
-
-
- Database::closeConnection($this->target, $this->key);
-
- usleep(20000);
-
-
- $this->assertNoConnection($id);
- }
-
-
- * Tests Database::closeConnection() with a query.
- */
- function testOpenQueryClose() {
- if ($this->skipTest) {
- return;
- }
-
- $this->addConnection();
- $id = $this->getConnectionID();
- Database::getConnection($this->target, $this->key);
-
-
- $this->assertConnection($id);
-
-
- Database::getConnection($this->target, $this->key)->query('SHOW TABLES');
-
-
- Database::closeConnection($this->target, $this->key);
-
- usleep(20000);
-
-
- $this->assertNoConnection($id);
- }
-
-
- * Tests Database::closeConnection() with a query and custom prefetch method.
- */
- function testOpenQueryPrefetchClose() {
- if ($this->skipTest) {
- return;
- }
-
- $this->addConnection();
- $id = $this->getConnectionID();
- Database::getConnection($this->target, $this->key);
-
-
- $this->assertConnection($id);
-
-
- Database::getConnection($this->target, $this->key)->query('SHOW TABLES')->fetchCol();
-
-
- Database::closeConnection($this->target, $this->key);
-
- usleep(20000);
-
-
- $this->assertNoConnection($id);
- }
-
-
- * Tests Database::closeConnection() with a select query.
- */
- function testOpenSelectQueryClose() {
- if ($this->skipTest) {
- return;
- }
-
- $this->addConnection();
- $id = $this->getConnectionID();
- Database::getConnection($this->target, $this->key);
-
-
- $this->assertConnection($id);
-
-
- $name = 'foo';
- Database::getConnection($this->target, $this->key)->schema()->createTable($name, array(
- 'fields' => array(
- 'name' => array(
- 'type' => 'varchar',
- 'length' => 255,
- ),
- ),
- ));
-
-
- Database::getConnection($this->target, $this->key)->select('foo', 'f')
- ->fields('f', array('name'))
- ->execute()
- ->fetchAll();
-
-
- Database::getConnection($this->target, $this->key)->schema()->dropTable($name);
-
-
- Database::closeConnection($this->target, $this->key);
-
- usleep(20000);
-
-
- $this->assertNoConnection($id);
- }
- }
-
-
- * Test reserved keyword handling (introduced for MySQL 8+)
- */
- class DatabaseReservedKeywordTestCase extends DatabaseTestCase {
- public static function getInfo() {
- return array(
- 'name' => 'Reserved Keywords',
- 'description' => 'Test handling of reserved keywords.',
- 'group' => 'Database',
- );
- }
-
- function setUp() {
- parent::setUp('database_test');
- }
-
- public function testTableNameQuoting() {
-
- $record = db_query('SELECT * FROM {system} LIMIT 1')->fetchObject();
- $this->assertTrue(isset($record->filename), 'Successfully queried the {system} table.');
-
- $connection = Database::getConnection()->getConnectionOptions();
- if ($connection['driver'] === 'sqlite') {
-
-
-
-
- $this->assert(TRUE, 'Skipping schema.{system} test for SQLite.');
- }
- else {
- $database = $connection['database'];
-
- db_query('SELECT * FROM ' . $database . '.{system} LIMIT 1')->fetchObject();
- $this->assertTrue(isset($record->filename), 'Successfully queried the schema.{system} table.');
- }
- }
-
- public function testSelectReservedWordTableCount() {
- $rows = db_select('virtual')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertEqual($rows, 1, 'Successful count query on a table with a reserved name.');
- }
-
- public function testSelectReservedWordTableSpecificField() {
- $record = db_select('virtual')
- ->fields('virtual', array('function'))
- ->execute()
- ->fetchAssoc();
- $this->assertEqual($record['function'], 'Function value 1', 'Successfully read a field from a table with a name and column which are reserved words.');
- }
-
- public function testSelectReservedWordTableAllFields() {
- $record = db_select('virtual')
- ->fields('virtual')
- ->execute()
- ->fetchAssoc();
- $this->assertEqual($record['function'], 'Function value 1', 'Successful all_fields query from a table with a name and column which are reserved words.');
- }
-
- public function testSelectReservedWordAliasCount() {
- $rows = db_select('test', 'character')
- ->countQuery()
- ->execute()
- ->fetchField();
- $this->assertEqual($rows, 4, 'Successful count query using an alias which is a reserved word.');
- }
-
- public function testSelectReservedWordAliasSpecificFields() {
- $record = db_select('test', 'high_priority')
- ->fields('high_priority', array('name'))
- ->condition('age', 27)
- ->execute()->fetchAssoc();
- $this->assertEqual($record['name'], 'George', 'Successful query using an alias which is a reserved word.');
- }
-
- public function testSelectReservedWordAliasAllFields() {
- $record = db_select('test', 'high_priority')
- ->fields('high_priority')
- ->condition('age', 27)
- ->execute()->fetchAssoc();
- $this->assertEqual($record['name'], 'George', 'Successful all_fields query using an alias which is a reserved word.');
- }
-
- public function testInsertReservedWordTable() {
- $num_records_before = db_query('SELECT COUNT(*) FROM {virtual}')->fetchField();
- db_insert('virtual')
- ->fields(array(
- 'function' => 'Inserted function',
- ))
- ->execute();
- $num_records_after = db_query('SELECT COUNT(*) FROM {virtual}')->fetchField();
- $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Successful insert into a table with a name and column which are reserved words.');
- }
-
- public function testDeleteReservedWordTable() {
- $delete = db_delete('virtual')
- ->condition('function', 'Function value 1');
- $num_deleted = $delete->execute();
- $this->assertEqual($num_deleted, 1, "Deleted 1 record from a table with a name and column which are reserved words..");
- }
-
- function testTruncateReservedWordTable() {
- db_truncate('virtual')->execute();
- $num_records_after = db_query("SELECT COUNT(*) FROM {virtual}")->fetchField();
- $this->assertEqual(0, $num_records_after, 'Truncated a table with a reserved name.');
- }
-
- function testUpdateReservedWordTable() {
- $num_updated = db_update('virtual')
- ->fields(array('function' => 'Updated function'))
- ->execute();
- $this->assertIdentical($num_updated, 1, 'Updated 1 record in a table with a name and column which are reserved words.');
- }
-
- function testMergeReservedWordTable() {
- $key = db_query('SELECT id FROM {virtual} LIMIT 1')->fetchField();
- $num_records_before = db_query('SELECT COUNT(*) FROM {virtual}')->fetchField();
- db_merge('virtual')
- ->key(array('id' => $key))
- ->fields(array('function' => 'Merged function'))
- ->execute();
- $num_records_after = db_query('SELECT COUNT(*) FROM {virtual}')->fetchField();
- $this->assertIdentical($num_records_before, $num_records_after, 'Successful merge query on a table with a name and column which are reserved words.');
- }
-
- }