diff -r 07239de796bb -r e756a8c72c3d cms/drupal/modules/simpletest/tests/database_test.test --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/cms/drupal/modules/simpletest/tests/database_test.test Fri Sep 08 12:04:06 2017 +0200 @@ -0,0 +1,4242 @@ +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) { + // This ends up being a test for table drop and create, too, which is nice. + 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'] = drupal_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() { + // We need the IDs, so we can't use a multi-insert here. + $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(); + } +} + +/** + * Test connection management. + */ +class DatabaseConnectionTestCase extends DatabaseTestCase { + + public static function getInfo() { + return array( + 'name' => 'Connection tests', + 'description' => 'Tests of the core database system.', + 'group' => 'Database', + ); + } + + /** + * Test that connections return appropriate connection objects. + */ + function testConnectionRouting() { + // Clone the master credentials to a slave connection. + // Note this will result in two independent connection objects that happen + // to point to the same place. + $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.'); + + // Try to open those targets another time, that should return the same objects. + $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.'); + + // Try to open an unknown target. + $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.'); + + // Try to open that unknown target another time, that should return the same object. + $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() { + // Clone the master credentials to a slave connection. + // Note this will result in two independent connection objects that happen + // to point to the same place. + $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() { + // Open the default target so we have an object to compare. + $db1 = Database::getConnection('default', 'default'); + + // Try to close the default connection, then open a new one. + Database::closeConnection('default', 'default'); + $db2 = Database::getConnection('default', 'default'); + + // Opening a connection after closing it should yield an object different than the original. + $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'); + + // Be sure we're connected to the default database. + $db = Database::getConnection('default', 'default'); + $connectionOptions = $db->getConnectionOptions(); + + // In the MySQL driver, the port can be different, so check individual + // options. + $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.'); + + // Set up identical slave and confirm connection options are identical. + Database::addConnectionInfo('default', 'slave', $connection_info['default']); + $db2 = Database::getConnection('slave', 'default'); + $connectionOptions2 = $db2->getConnectionOptions(); + + // Get a fresh copy of the default connection options. + $connectionOptions = $db->getConnectionOptions(); + $this->assertIdentical($connectionOptions, $connectionOptions2, 'The default and slave connection options are identical.'); + + // Set up a new connection with different connection info. + $test = $connection_info['default']; + $test['database'] .= 'test'; + Database::addConnectionInfo('test', 'default', $test); + $connection_info = Database::getConnectionInfo('test'); + + // Get a fresh copy of the default connection options. + $connectionOptions = $db->getConnectionOptions(); + $this->assertNotEqual($connection_info['default']['database'], $connectionOptions['database'], 'The test connection info database does not match the current connection options database.'); + } +} + +/** + * Test cloning Select queries. + */ +class DatabaseSelectCloneTest extends DatabaseTestCase { + + public static function getInfo() { + return array( + 'name' => 'Select tests, cloning', + 'description' => 'Test cloning Select queries.', + 'group' => 'Database', + ); + } + + /** + * 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; + // Cloned query should not be altered by the following modification + // happening on original query. + $subquery->condition('age', 25, '>'); + + $clone_result = $clone->countQuery()->execute()->fetchField(); + $query_result = $query->countQuery()->execute()->fetchField(); + + // Make sure the cloned query has not been modified + $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 { + + public static function getInfo() { + return array( + 'name' => 'Fetch tests', + 'description' => 'Test the Database system\'s various fetch capabilities.', + 'group' => 'Database', + ); + } + + /** + * 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 Drupal 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 { + + public static function getInfo() { + return array( + 'name' => 'Fetch tests, part 2', + 'description' => 'Test the Database system\'s various fetch capabilities.', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp(); + } + + // Confirm that we can fetch a record into an indexed array explicitly. + 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 { + + public static function getInfo() { + return array( + 'name' => 'Insert tests', + 'description' => 'Test the Insert query builder.', + 'group' => 'Database', + ); + } + + /** + * 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', + )); + + // We should be able to specify values in any order if named. + $query->values(array( + 'age' => '31', + 'name' => 'Curly', + )); + + // We should be able to say "use the field order". + // This is not the recommended mechanism for most cases, but it should work. + $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(); // This should run the insert, but leave the fields intact. + + // We should be able to specify values in any order if named. + $query->values(array( + 'age' => '31', + 'name' => 'Curly', + )); + $query->execute(); + + // We should be able to say "use the field order". + $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() { + // This is useful for importers, when we want to create a query and define + // its fields once, then loop over a multi-insert execution. + 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'); + // The query builder will always append expressions after fields. + // Add the expression first to test that the insert fields are correctly + // re-ordered. + $query->addExpression('tp.age', 'age'); + $query + ->fields('tp', array('name','job')) + ->condition('tp.name', 'Meredith'); + + // The resulting query should be equivalent to: + // INSERT INTO test (age, name, job) + // SELECT tp.age AS age, tp.name AS name, tp.job AS job + // FROM test_people tp + // WHERE 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'); + + // The resulting query should be equivalent to: + // INSERT INTO test_people_copy + // SELECT * + // FROM test_people tp + // WHERE 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 { + + public static function getInfo() { + return array( + 'name' => 'Insert tests, LOB fields', + 'description' => 'Test the Insert query builder with LOB fields.', + 'group' => 'Database', + ); + } + + /** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Insert tests, default fields', + 'description' => 'Test the Insert query builder with default values.', + 'group' => 'Database', + ); + } + + /** + * 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 = drupal_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 is only executed if no exception has been thrown. + $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 = drupal_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 { + + public static function getInfo() { + return array( + 'name' => 'Update tests', + 'description' => 'Test the Update query builder.', + 'group' => 'Database', + ); + } + + /** + * 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() { + // Set age = 1 for a single row for this test to work. + db_update('test') + ->condition('id', 1) + ->fields(array('age' => 1)) + ->execute(); + + // Ensure that expressions are handled properly. This should set every + // record's age to a square of itself, which will change only three of the + // four records in the table since 1*1 = 1. That means only three records + // are modified, so we should get back 3, not 4, from execute(). + $num_rows = db_update('test') + ->expression('age', 'age * age') + ->execute(); + $this->assertIdentical($num_rows, 3, 'Number of affected rows are returned.'); + } + + /** + * 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, 'Updated 1 record.'); + + $saved_name= db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 42))->fetchField(); + $this->assertIdentical($saved_name, 'John', 'Updated primary key successfully.'); + } +} + +/** + * Tests for more complex update statements. + */ +class DatabaseUpdateComplexTestCase extends DatabaseTestCase { + + public static function getInfo() { + return array( + 'name' => 'Update tests, Complex', + 'description' => 'Test the Update query builder, complex queries.', + 'group' => 'Database', + ); + } + + /** + * 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() { + // The o is lowercase in the 'NoT IN' operator, to make sure the operators + // work in mixed case. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Update tests, LOB', + 'description' => 'Test the Update query builder with LOB fields.', + 'group' => 'Database', + ); + } + + /** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Delete/Truncate tests', + 'description' => 'Test the Delete and Truncate query builders.', + 'group' => 'Database', + ); + } + + /** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Merge tests', + 'description' => 'Test the Merge query builder.', + 'group' => 'Database', + ); + } + + /** + * Confirm that we can merge-insert a record successfully. + */ + function testMergeInsert() { + $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField(); + + $result = db_merge('test_people') + ->key(array('job' => 'Presenter')) + ->fields(array( + 'age' => 31, + 'name' => 'Tiffany', + )) + ->execute(); + + $this->assertEqual($result, MergeQuery::STATUS_INSERT, 'Insert status returned.'); + + $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, '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(); + + $result = db_merge('test_people') + ->key(array('job' => 'Speaker')) + ->fields(array( + 'age' => 31, + 'name' => 'Tiffany', + )) + ->execute(); + + $this->assertEqual($result, MergeQuery::STATUS_UPDATE, 'Update status returned.'); + + $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(); + + // This is a very contrived example, as I have no idea why you'd want to + // change age this way, but that's beside the point. + // Note that we are also double-setting age here, once as a literal and + // once as an expression. This test will only pass if the expression wins, + // which is what is supposed to happen. + 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 { + // This query should die because there is no key field specified. + 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 { + + public static function getInfo() { + return array( + 'name' => 'Select tests', + 'description' => 'Test the Select query builder.', + 'group' => 'Database', + ); + } + + /** + * 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')->comment('Testing query comments'); + $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++; + } + + $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')->comment('Testing query comments */ SELECT nid FROM {node}; --'); + $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++; + } + + $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(''), + ), + // Try and close the comment early. + array( + '/* Exploit * / DROP TABLE node; -- */ ', + array('Exploit */ DROP TABLE node; --'), + ), + // Variations on comment closing. + array( + '/* Exploit * / * / DROP TABLE node; -- */ ', + array('Exploit */*/ DROP TABLE node; --'), + ), + array( + '/* Exploit * * // DROP TABLE node; -- */ ', + array('Exploit **// DROP TABLE node; --'), + ), + // Try closing the comment in the second string which is appended. + 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(); + + // Check that the aliases are being created the way we want. + $this->assertEqual($name_field, 'name', 'Name field alias is correct.'); + $this->assertEqual($age_field, 'age', 'Age field alias is correct.'); + + // Ensure that we got the right record. + $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(); + + // Check that the aliases are being created the way we want. + $this->assertEqual($name_field, 'name', 'Name field alias is correct.'); + $this->assertEqual($age_field, 'double_age', 'Age field alias is correct.'); + + // Ensure that we got the right record. + $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(); + + // Check that the aliases are being created the way we want. + $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.'); + + // Ensure that we got the right record. + $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(); + + // Check that all fields we asked for are present. + $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.'); + + // Ensure that we got the right record. + // Check that all fields we asked for are 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(); + + // Check that all fields we asked for are present. + $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.'); + + // Ensure that we got the right record. + // Check that all fields we asked for are 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 we can find a record with a NULL value. + */ + function testNullCondition() { + $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 testNotNullCondition() { + $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(); + + // Ensure we only get 2 records. + $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(); + + // Ensure we get all 3 records. + $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 Drupal 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 simply 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() { + // Use 52 items, so the chance that this test fails by accident will be the + // same as the chance that a deck of cards will come out in the same order + // after shuffling it (in other words, nearly impossible). + $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(); + } + + // First select the items in order and make sure we get an ordered list. + $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.'); + + // Now perform the same query, but instead choose a random ordering. We + // expect this to contain a differently ordered version of the original + // result. + $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.'); + + // Now perform the exact same query again, and make sure the order is + // different. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Select tests, subqueries', + 'description' => 'Test the Select query builder.', + 'group' => 'Database', + ); + } + + /** + * Test that we can use a subquery in a FROM clause. + */ + function testFromSubquerySelect() { + // Create a subquery, which is just a normal query object. + $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++) { + // Create another query that joins against the virtual table resulting + // from the subquery. + $select = db_select($subquery, 'tt2'); + $select->join('test', 't', 't.id=tt2.pid'); + $select->addField('t', 'name'); + if ($i) { + // Use a different number of conditions here to confuse the subquery + // placeholder counter, testing http://drupal.org/node/1112854. + $select->condition('name', 'John'); + } + $select->condition('task', 'code'); + + // The resulting query should be equivalent to: + // SELECT t.name + // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt WHERE priority=1) tt + // INNER JOIN test t ON t.id=tt.pid + // WHERE tt.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() { + // Create a subquery, which is just a normal query object. + $subquery = db_select('test_task', 'tt'); + $subquery->addField('tt', 'pid', 'pid'); + $subquery->addField('tt', 'task', 'task'); + $subquery->orderBy('priority', 'DESC'); + $subquery->range(0, 1); + + // Create another query that joins against the virtual table resulting + // from the subquery. + $select = db_select($subquery, 'tt2'); + $select->join('test', 't', 't.id=tt2.pid'); + $select->addField('t', 'name'); + + // The resulting query should be equivalent to: + // SELECT t.name + // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt ORDER BY priority DESC LIMIT 1 OFFSET 0) tt + // INNER JOIN test t ON t.id=tt.pid + $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() { + // Create a subquery, which is just a normal query object. + $subquery = db_select('test_task', 'tt'); + $subquery->addField('tt', 'pid', 'pid'); + $subquery->condition('tt.priority', 1); + + // Create another query that joins against the virtual table resulting + // from the subquery. + $select = db_select('test_task', 'tt2'); + $select->addField('tt2', 'task'); + $select->condition('tt2.pid', $subquery, 'IN'); + + // The resulting query should be equivalent to: + // SELECT tt2.name + // FROM test tt2 + // WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1) + $people = $select->execute()->fetchCol(); + $this->assertEqual(count($people), 5, 'Returned the correct number of rows.'); + } + + /** + * Test that we can use a subquery in a JOIN clause. + */ + function testJoinSubquerySelect() { + // Create a subquery, which is just a normal query object. + $subquery = db_select('test_task', 'tt'); + $subquery->addField('tt', 'pid', 'pid'); + $subquery->condition('priority', 1); + + // Create another query that joins against the virtual table resulting + // from the subquery. + $select = db_select('test', 't'); + $select->join($subquery, 'tt', 't.id=tt.pid'); + $select->addField('t', 'name'); + + // The resulting query should be equivalent to: + // SELECT t.name + // FROM test t + // INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid + $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() { + // Put George into {test_people}. + db_insert('test_people') + ->fields(array( + 'name' => 'George', + 'age' => 27, + 'job' => 'Singer', + )) + ->execute(); + // Base query to {test}. + $query = db_select('test', 't') + ->fields('t', array('name')); + // Subquery to {test_people}. + $subquery = db_select('test_people', 'tp') + ->fields('tp', array('name')) + ->where('tp.name = t.name'); + $query->exists($subquery); + $result = $query->execute(); + + // Ensure that we got the right record. + $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() { + // Put George into {test_people}. + db_insert('test_people') + ->fields(array( + 'name' => 'George', + 'age' => 27, + 'job' => 'Singer', + )) + ->execute(); + + // Base query to {test}. + $query = db_select('test', 't') + ->fields('t', array('name')); + // Subquery to {test_people}. + $subquery = db_select('test_people', 'tp') + ->fields('tp', array('name')) + ->where('tp.name = t.name'); + $query->notExists($subquery); + + // Ensure that we got the right number of records. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Select tests, ordered', + 'description' => 'Test the Select query builder.', + 'group' => 'Database', + ); + } + + /** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Select tests, complex', + 'description' => 'Test the Select query builder with more complex queries.', + 'group' => 'Database', + ); + } + + /** + * 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.'); + + // Now make sure we didn't break the original query! We should still have + // all of the fields we asked for. + $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(); + + // Check that the 'all_fields' statement is handled properly. + $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.'); + + // Check that the ordering clause is handled properly. + $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.'); + + // Make sure that the count query works. + $count = $count->execute()->fetchField(); + + $this->assertEqual($count, 4, 'Counted the correct number of records.'); + } + + + /** + * Test that countQuery properly removes fields and expressions. + */ + function testCountQueryFieldRemovals() { + // countQuery should remove all fields and expressions, so this can be + // tested by adding a non-existent field and expression: if it ends + // up in the query, an error will be thrown. If not, it will return the + // number of records, which in this case happens to be 4 (there are four + // records in the {test} table). + $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.'); + + // Use a column alias as, without one, the query can succeed for the wrong + // reason. + $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() { + // This query should translate to: + // "SELECT job FROM {test} WHERE name = 'Paul' AND (age = 26 OR age = 27)" + // That should find only one record. Yes it's a non-optimal way of writing + // that query but that's not the point! + $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 { + + public static function getInfo() { + return array( + 'name' => 'Select tests, complex 2', + 'description' => 'Test the Select query builder with even more complex queries.', + 'group' => 'Database', + ); + } + + function setUp() { + DrupalWebTestCase::setUp('database_test', 'node_access_test'); + + $schema['test'] = drupal_get_schema('test'); + $schema['test_people'] = drupal_get_schema('test_people'); + $schema['test_one_blob'] = drupal_get_schema('test_one_blob'); + $schema['test_two_blobs'] = drupal_get_schema('test_two_blobs'); + $schema['test_task'] = drupal_get_schema('test_task'); + + $this->installTables($schema); + + $this->addSampleData(); + } + + /** + * Test that we can join on a query. + */ + function testJoinSubquery() { + $acct = $this->drupalCreateUser(array('access content')); + $this->drupalLogin($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'); + + // Construct the query string. + // This is the same sequence that SelectQuery::execute() goes through. + $query->preExecute(); + $query->getArguments(); + $str = (string) $query; + + // Verify that the string only has one copy of condition placeholder 0. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Pager query tests', + 'description' => 'Test the pager query extender.', + 'group' => 'Database', + ); + } + + /** + * 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() { + // To keep the test from being too brittle, we determine up front + // what the page count should be dynamically, and pass the control + // information forward to the actual query on the other side of the + // HTTP request. + $limit = 2; + $count = db_query('SELECT COUNT(*) FROM {test}')->fetchField(); + + $correct_number = $limit; + $num_pages = floor($count / $limit); + + // If there is no remainder from rounding, subtract 1 since we index from 0. + if (!($num_pages * $limit < $count)) { + $num_pages--; + } + + for ($page = 0; $page <= $num_pages; ++$page) { + $this->drupalGet('database_test/pager_query_even/' . $limit, array('query' => array('page' => $page))); + $data = json_decode($this->drupalGetContent()); + + 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() { + // To keep the test from being too brittle, we determine up front + // what the page count should be dynamically, and pass the control + // information forward to the actual query on the other side of the + // HTTP request. + $limit = 2; + $count = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField(); + + $correct_number = $limit; + $num_pages = floor($count / $limit); + + // If there is no remainder from rounding, subtract 1 since we index from 0. + if (!($num_pages * $limit < $count)) { + $num_pages--; + } + + for ($page = 0; $page <= $num_pages; ++$page) { + $this->drupalGet('database_test/pager_query_odd/' . $limit, array('query' => array('page' => $page))); + $data = json_decode($this->drupalGetContent()); + + 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.'); + + // Setting an element smaller than the previous one + // should not overwrite the pager $maxElement with a smaller value. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Tablesort query tests', + 'description' => 'Test the tablesort query extender.', + 'group' => 'Database', + ); + } + + /** + * 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'), + // more elements here + + ); + + foreach ($sorts as $sort) { + $this->drupalGet('database_test/tablesort/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort']))); + $data = json_decode($this->drupalGetContent()); + + $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'), + // more elements here + + ); + + foreach ($sorts as $sort) { + $this->drupalGet('database_test/tablesort_first/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort']))); + $data = json_decode($this->drupalGetContent()); + + $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->drupalGet('database_test/tablesort_default_sort'); + // Any PHP errors or notices thrown would trigger a simpletest exception, so + // no additional assertions are needed. + } +} + +/** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Query tagging tests', + 'description' => 'Test the tagging capabilities of the Select builder.', + 'group' => 'Database', + ); + } + + /** + * 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 { + + public static function getInfo() { + return array( + 'name' => 'Query altering tests', + 'description' => 'Test the hook_query_alter capabilities of the Select builder.', + 'group' => 'Database', + ); + } + + /** + * 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(); + + // Ensure that we got the right record. + $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() { + // Create a sub-query with an alter tag. + $subquery = db_select('test', 'p'); + $subquery->addField('p', 'name'); + $subquery->addField('p', 'id'); + // Pick out George. + $subquery->condition('age', 27); + $subquery->addExpression("age*2", 'double_age'); + // This query alter should change it to age * 3. + $subquery->addTag('database_test_alter_change_expressions'); + + // Create a main query and join to sub-query. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Regression tests', + 'description' => 'Regression tests cases for the database layer.', + 'group' => 'Database', + ); + } + + /** + * 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() { + // That's a 255 character UTF-8 string. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Query logging', + 'description' => 'Test the query logging facility.', + 'group' => 'Database', + ); + } + + /** + * 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(); + + // Trigger a call that does not have file in the backtrace. + 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() { + // Clone the master credentials to a slave connection and to another fake + // connection. + $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'));//->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'], '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(); + + // We use "fake" here as a target because any non-existent target will do. + // However, because all of the tests in this class share a single page + // request there is likely to be a target of "slave" from one of the other + // unit tests, so we use a target here that we know with absolute certainty + // does not exist. + 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() { + // Clone the master credentials to a fake connection. + // That both connections point to the same physical database is irrelevant. + $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 { + public static function getInfo() { + return array( + 'name' => 'Serialize query', + 'description' => 'Test serializing and unserializing a query.', + 'group' => 'Database', + ); + } + + /** + * Confirm that a query can be serialized and unserialized. + */ + function testSerializeQuery() { + $query = db_select('test'); + $query->addField('test', 'age'); + $query->condition('name', 'Ringo'); + // If this doesn't work, it will throw an exception, so no need for an + // assertion. + $query = unserialize(serialize($query)); + $results = $query->execute()->fetchCol(); + $this->assertEqual($results[0], 28, 'Query properly executed after unserialization.'); + } +} + +/** + * Range query tests. + */ +class DatabaseRangeQueryTestCase extends DrupalWebTestCase { + public static function getInfo() { + return array( + 'name' => 'Range query test', + 'description' => 'Test the Range query functionality.', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp('database_test'); + } + + /** + * Confirm that range query work and return correct result. + */ + function testRangeQuery() { + // Test if return correct number of rows. + $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.'); + + // Test if return target data. + $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 DrupalWebTestCase { + public static function getInfo() { + return array( + 'name' => 'Temporary query test', + 'description' => 'Test the temporary query functionality.', + 'group' => 'Database', + ); + } + + 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->drupalGet('database_test/db_query_temporary'); + $data = json_decode($this->drupalGetContent()); + 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."); + } + + // Now try to run two db_query_temporary() in the same request. + $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.'); + + // Check that leading whitespace and comments do not cause problems + // in the modified query. + $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 Drupal + * across multiple kinds of database systems, we test that the + * database system interprets SQL syntax in an expected fashion. + */ +class DatabaseBasicSyntaxTestCase extends DatabaseTestCase { + public static function getInfo() { + return array( + 'name' => 'Basic SQL syntax tests', + 'description' => 'Test SQL syntax interpretation.', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp('database_test'); + } + + /** + * Test for string concatenation. + */ + function testBasicConcat() { + $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 testFieldConcat() { + $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.'); + } + + /** + * Test escaping of LIKE wildcards. + */ + function testLikeEscape() { + db_insert('test') + ->fields(array( + 'name' => 'Ring_', + )) + ->execute(); + + // Match both "Ringo" and "Ring_". + $num_matches = db_select('test', 't') + ->condition('name', 'Ring_', 'LIKE') + ->countQuery() + ->execute() + ->fetchField(); + $this->assertIdentical($num_matches, '2', 'Found 2 records.'); + // Match only "Ring_" using a LIKE expression with no wildcards. + $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(); + + // Match both rows using a LIKE expression with two wildcards and a verbatim + // backslash. + $num_matches = db_select('test', 't') + ->condition('name', 'abc%\\\\_', 'LIKE') + ->countQuery() + ->execute() + ->fetchField(); + $this->assertIdentical($num_matches, '2', 'Found 2 records.'); + // Match only the former using a LIKE expression with no wildcards. + $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 { + public static function getInfo() { + return array( + 'name' => 'Case sensitivity', + 'description' => 'Test handling case sensitive collation.', + 'group' => 'Database', + ); + } + + /** + * 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', // <- A record already exists with 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 { + public static function getInfo() { + return array( + 'name' => 'Invalid data', + 'description' => 'Test handling of some invalid data.', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp('database_test'); + } + + /** + * Traditional SQL database systems abort inserts when invalid data is encountered. + */ + function testInsertDuplicateData() { + // Try to insert multiple records where at least one has bad data. + try { + db_insert('test') + ->fields(array('name', 'age', 'job')) + ->values(array( + 'name' => 'Elvis', + 'age' => 63, + 'job' => 'Singer', + ))->values(array( + 'name' => 'John', // <-- Duplicate value on unique field. + '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) { + // Check if the first record was inserted. + $name = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 63))->fetchField(); + + if ($name == 'Elvis') { + if (!Database::getConnection()->supportsTransactions()) { + // This is an expected fail. + // Database engines that don't support transactions can leave partial + // inserts in place when an error occurs. This is the case for MySQL + // when running on a MyISAM table. + $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.'); + } + + // Ensure the other values were not inserted. + $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.'); + } + } + +} + +/** + * Drupal-specific SQL syntax tests. + */ +class DatabaseQueryTestCase extends DatabaseTestCase { + public static function getInfo() { + return array( + 'name' => 'Custom query syntax tests', + 'description' => 'Test Drupal\'s extended prepared statement syntax..', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp('database_test'); + } + + /** + * Test that we can specify an array of values in the query by simply 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() { + // Attempt SQL injection and verify that it does not work. + $condition = array( + "1 ;INSERT INTO {test} (name) VALUES ('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.'); + } + + // Test that the insert query that was used in the SQL injection attempt did + // not result in a row being inserted in the database. + $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 { + + public static function getInfo() { + return array( + 'name' => 'Transaction tests', + 'description' => 'Test the transaction abstraction system.', + 'group' => 'Database', + ); + } + + /** + * 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 + * 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(); + + // Insert a single row into the testing table. + db_insert('test') + ->fields(array( + 'name' => 'David' . $suffix, + 'age' => '24', + )) + ->execute(); + + $this->assertTrue($connection->inTransaction(), 'In transaction before calling nested transaction.'); + + // We're already in a transaction, but we call ->transactionInnerLayer + // to nest another transaction inside the current one. + $this->transactionInnerLayer($suffix, $rollback, $ddl_statement); + + $this->assertTrue($connection->inTransaction(), 'In transaction after calling nested transaction.'); + + if ($rollback) { + // Roll back the transaction, if requested. + // This rollback should propagate to the last savepoint. + $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(); + // Start a transaction. If we're being called from ->transactionOuterLayer, + // then we're already in a transaction. Normally, that would make starting + // a transaction here dangerous, but the database API handles this problem + // for us by tracking the nesting and avoiding the danger. + $txn = db_transaction(); + + $depth2 = $connection->transactionDepth(); + $this->assertTrue($depth < $depth2, 'Transaction depth is has increased with new transaction.'); + + // Insert a single row into the testing table. + 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) { + // Roll back the transaction, if requested. + // This rollback should propagate to the last savepoint. + $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() { + // This test won't work right if transactions are not supported. + if (!Database::getConnection()->supportsTransactions()) { + return; + } + try { + // Create two nested transactions. Roll back from the inner one. + $this->transactionOuterLayer('B', TRUE); + + // Neither of the rows we inserted in the two transaction layers + // should be present in the tables post-rollback. + $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() { + // This test won't work right if transactions are supported. + if (Database::getConnection()->supportsTransactions()) { + return; + } + try { + // Create two nested transactions. Attempt to roll back from the inner one. + $this->transactionOuterLayer('B', TRUE); + + // Because our current database claims to not support transactions, + // the inserted rows should be present despite the attempt to roll back. + $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 { + // Create two nested transactions. The changes should be committed. + $this->transactionOuterLayer('A'); + + // Because we committed, both of the inserted rows should be present. + $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() { + // First, test that a commit works normally, even with DDL statements. + $transaction = db_transaction(); + $this->insertRow('row'); + $this->executeDDLStatement(); + unset($transaction); + $this->assertRowPresent('row'); + + // Even in different order. + $this->cleanUp(); + $transaction = db_transaction(); + $this->executeDDLStatement(); + $this->insertRow('row'); + unset($transaction); + $this->assertRowPresent('row'); + + // Even with stacking. + $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'); + + // A transaction after a DDL statement should still work the same. + $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'); + + // The behavior of a rollback depends on the type of database server. + if (Database::getConnection()->supportsTransactionalDDL()) { + // For database servers that support transactional DDL, a rollback + // of a transaction including DDL statements should be possible. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('row'); + $this->executeDDLStatement(); + $transaction->rollback(); + unset($transaction); + $this->assertRowAbsent('row'); + + // Including with stacking. + $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 { + // For database servers that do not support transactional DDL, + // the DDL statement should commit the transaction stack. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('row'); + $this->executeDDLStatement(); + // Rollback the outer transaction. + try { + $transaction->rollback(); + unset($transaction); + // @TODO: an exception should be triggered here, but is not, because + // "ROLLBACK" fails silently in MySQL if there is no transaction active. + // $this->fail(t('Rolling back a transaction containing DDL should fail.')); + } + 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() { + // This test won't work right if transactions are not supported. + if (!Database::getConnection()->supportsTransactions()) { + return; + } + + $database = Database::getConnection(); + + // Standard case: pop the inner transaction before the outer transaction. + $transaction = db_transaction(); + $this->insertRow('outer'); + $transaction2 = db_transaction(); + $this->insertRow('inner'); + // Pop the inner transaction. + unset($transaction2); + $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the inner transaction'); + // Pop the outer transaction. + unset($transaction); + $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the outer transaction'); + $this->assertRowPresent('outer'); + $this->assertRowPresent('inner'); + + // Pop the transaction in a different order they have been pushed. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('outer'); + $transaction2 = db_transaction(); + $this->insertRow('inner'); + // Pop the outer transaction, nothing should happen. + unset($transaction); + $this->insertRow('inner-after-outer-commit'); + $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction'); + // Pop the inner transaction, the whole transaction should commit. + 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'); + + // Rollback the inner transaction. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('outer'); + $transaction2 = db_transaction(); + $this->insertRow('inner'); + // Now rollback the inner transaction. + $transaction2->rollback(); + unset($transaction2); + $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction'); + // Pop the outer transaction, it should commit. + $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'); + + // Rollback the inner transaction after committing the outer one. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('outer'); + $transaction2 = db_transaction(); + $this->insertRow('inner'); + // Pop the outer transaction, nothing should happen. + unset($transaction); + $this->assertTrue($database->inTransaction(), 'Still in a transaction after popping the outer transaction'); + // Now rollback the inner transaction, it should rollback. + $transaction2->rollback(); + unset($transaction2); + $this->assertFalse($database->inTransaction(), 'Transaction closed after popping the inner transaction'); + $this->assertRowPresent('outer'); + $this->assertRowAbsent('inner'); + + // Rollback the outer transaction while the inner transaction is active. + // In that case, an exception will be triggered because we cannot + // ensure that the final result will have any meaning. + $this->cleanUp(); + $transaction = db_transaction(); + $this->insertRow('outer'); + $transaction2 = db_transaction(); + $this->insertRow('inner'); + $transaction3 = db_transaction(); + $this->insertRow('inner2'); + // Rollback the outer transaction. + 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'); + // Try to commit one inner transaction. + unset($transaction3); + $this->pass('Trying to commit an inner transaction resulted in an exception.'); + // Try to rollback one inner transaction. + 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'); + } +} + + +/** + * Check the sequences API. + */ +class DatabaseNextIdCase extends DrupalWebTestCase { + public static function getInfo() { + return array( + 'name' => 'Sequences API', + 'description' => 'Test the secondary sequences API.', + 'group' => 'Database', + ); + } + + /** + * Test that the sequences API work. + */ + function testDbNextId() { + $first = db_next_id(); + $second = db_next_id(); + // We can test for exact increase in here because we know there is no + // other process operating on these tables -- normally we could only + // expect $second > $first. + $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 DrupalWebTestCase { + public static function getInfo() { + return array( + 'name' => 'Empty statement', + 'description' => 'Test the empty pseudo-statement class.', + 'group' => 'Database', + ); + } + + /** + * 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 DrupalUnitTestCase { + + protected $key; + protected $target; + + protected $monitor; + protected $originalCount; + + public static function getInfo() { + return array( + 'name' => 'Connection unit tests', + 'description' => 'Tests management of database connections.', + 'group' => 'Database', + ); + } + + function setUp() { + parent::setUp(); + + $this->key = 'default'; + $this->originalTarget = 'default'; + $this->target = 'DatabaseConnectionUnitTest'; + + // Determine whether the database driver is MySQL. If it is not, the test + // methods will not be executed. + // @todo Make this test driver-agnostic, or find a proper way to skip it. + // @see http://drupal.org/node/1273478 + $connection_info = Database::getConnectionInfo('default'); + $this->skipTest = (bool) $connection_info['default']['driver'] != 'mysql'; + if ($this->skipTest) { + // Insert an assertion to prevent Simpletest from interpreting the test + // as failure. + $this->pass('This test is only compatible with MySQL.'); + } + + // Create an additional connection to monitor the connections being opened + // and closed in this test. + // @see TestBase::changeDatabasePrefix() + $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() { + // Add a new target to the connection, by cloning the current connection. + $connection_info = Database::getConnectionInfo($this->key); + Database::addConnectionInfo($this->key, $this->target, $connection_info[$this->originalTarget]); + + // Verify that the new target exists. + $info = Database::getConnectionInfo($this->key); + // Note: Custom assertion message to not expose database credentials. + $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; + } + // Add and open a new connection. + $this->addConnection(); + $id = $this->getConnectionID(); + Database::getConnection($this->target, $this->key); + + // Verify that there is a new connection. + $this->assertConnection($id); + + // Close the connection. + Database::closeConnection($this->target, $this->key); + // Wait 20ms to give the database engine sufficient time to react. + usleep(20000); + + // Verify that we are back to the original connection count. + $this->assertNoConnection($id); + } + + /** + * Tests Database::closeConnection() with a query. + */ + function testOpenQueryClose() { + if ($this->skipTest) { + return; + } + // Add and open a new connection. + $this->addConnection(); + $id = $this->getConnectionID(); + Database::getConnection($this->target, $this->key); + + // Verify that there is a new connection. + $this->assertConnection($id); + + // Execute a query. + Database::getConnection($this->target, $this->key)->query('SHOW TABLES'); + + // Close the connection. + Database::closeConnection($this->target, $this->key); + // Wait 20ms to give the database engine sufficient time to react. + usleep(20000); + + // Verify that we are back to the original connection count. + $this->assertNoConnection($id); + } + + /** + * Tests Database::closeConnection() with a query and custom prefetch method. + */ + function testOpenQueryPrefetchClose() { + if ($this->skipTest) { + return; + } + // Add and open a new connection. + $this->addConnection(); + $id = $this->getConnectionID(); + Database::getConnection($this->target, $this->key); + + // Verify that there is a new connection. + $this->assertConnection($id); + + // Execute a query. + Database::getConnection($this->target, $this->key)->query('SHOW TABLES')->fetchCol(); + + // Close the connection. + Database::closeConnection($this->target, $this->key); + // Wait 20ms to give the database engine sufficient time to react. + usleep(20000); + + // Verify that we are back to the original connection count. + $this->assertNoConnection($id); + } + + /** + * Tests Database::closeConnection() with a select query. + */ + function testOpenSelectQueryClose() { + if ($this->skipTest) { + return; + } + // Add and open a new connection. + $this->addConnection(); + $id = $this->getConnectionID(); + Database::getConnection($this->target, $this->key); + + // Verify that there is a new connection. + $this->assertConnection($id); + + // Create a table. + $name = 'foo'; + Database::getConnection($this->target, $this->key)->schema()->createTable($name, array( + 'fields' => array( + 'name' => array( + 'type' => 'varchar', + 'length' => 255, + ), + ), + )); + + // Execute a query. + Database::getConnection($this->target, $this->key)->select('foo', 'f') + ->fields('f', array('name')) + ->execute() + ->fetchAll(); + + // Drop the table. + Database::getConnection($this->target, $this->key)->schema()->dropTable($name); + + // Close the connection. + Database::closeConnection($this->target, $this->key); + // Wait 20ms to give the database engine sufficient time to react. + usleep(20000); + + // Verify that we are back to the original connection count. + $this->assertNoConnection($id); + } + +}