database_test.test

<?php
/**
 * @file
 * Database tests.
 */

/**
 * Dummy class for fetching into a class.
 *
 * PDO supports using a new instance of an arbitrary class for records
 * rather than just a stdClass or array. This class is for testing that
 * functionality. (See testQueryFetchClass() below)
 */
class FakeRecord {

  /**
   * @var string
   */
  public $name;

}

/**
 * Dummy class that contains a construct method.
 */
class FakeRecordWithConstructor {

  /**
   * @var string
   */
  public $name;

  /**
   * Constructor for FakeRecordWithConstructor.
   */
  public function __construct() {}
}

/**
 * Base test class for databases.
 *
 * Because all database tests share the same test data, we can centralize that
 * here.
 */
class DatabaseTestCase extends BackdropWebTestCase {
  protected $profile = 'testing';

  function setUp() {
    $modules = func_get_args();
    if (isset($modules[0]) && is_array($modules[0])) {
      $modules = $modules[0];
    }
    $modules[] = 'database_test';
    parent::setUp($modules);

    $schema['test'] = backdrop_get_schema('test');
    $schema['test_people'] = backdrop_get_schema('test_people');
    $schema['test_people_copy'] = backdrop_get_schema('test_people_copy');
    $schema['test_one_blob'] = backdrop_get_schema('test_one_blob');
    $schema['test_two_blobs'] = backdrop_get_schema('test_two_blobs');
    $schema['test_task'] = backdrop_get_schema('test_task');

    $this->installTables($schema);

    $this->addSampleData();
  }

  /**
   * Set up several tables needed by a certain test.
   *
   * @param $schema
   *   An array of table definitions to install.
   */
  function installTables($schema) {
    // 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'] = backdrop_get_schema('test_null');
    $this->installTables($schema);

    db_insert('test_null')
    ->fields(array('name', 'age'))
    ->values(array(
      'name' => 'Kermit',
      'age' => 25,
    ))
    ->values(array(
      'name' => 'Fozzie',
      'age' => NULL,
    ))
    ->values(array(
      'name' => 'Gonzo',
      'age' => 27,
    ))
    ->execute();
  }

  /**
   * Setup our sample data.
   *
   * These are added using db_query(), since we're not trying to test the
   * INSERT operations here, just populate.
   */
  function addSampleData() {
    // 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 {
  /**
   * Test that connections return appropriate connection objects.
   */
  function testConnectionRouting() {
    // Clone the primary credentials to a replica 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', 'replica', $connection_info['default']);

    $db1 = Database::getConnection('default', 'default');
    $db2 = Database::getConnection('replica', 'default');

    $this->assertNotNull($db1, 'default connection is a real connection object.');
    $this->assertNotNull($db2, 'replica 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('replica', '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 primary credentials to a replica 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', 'replica', $connection_info['default']);

    Database::ignoreTarget('default', 'replica');

    $db1 = Database::getConnection('default', 'default');
    $db2 = Database::getConnection('replica', '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 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 replica and confirm connection options are identical.
    Database::addConnectionInfo('default', 'replica', $connection_info['default']);
    $db2 = Database::getConnection('replica', 'default');
    $connectionOptions2 = $db2->getConnectionOptions();

    // Get a fresh copy of the default connection options.
    $connectionOptions = $db->getConnectionOptions();
    $this->assertIdentical($connectionOptions, $connectionOptions2, 'The default and replica 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 {
  /**
   * 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 have a different unique identifier.
    $this->assertNotEqual($query->uniqueIdentifier(), $clone->uniqueIdentifier());

    // 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');
  }

  /**
   * Tests that nested SELECT queries are cloned properly.
   */
  public function testNestedQueryCloning() {
    $sub_query = db_select('test', 't');
    $sub_query->addField('t', 'id', 'id');
    $sub_query->condition('age', 28, '<');

    $query = db_select($sub_query, 't');

    $clone = clone $query;

    // Cloned query should have a different unique identifier.
    $this->assertNotEqual($query->uniqueIdentifier(), $clone->uniqueIdentifier());

    // Cloned query should not be altered by the following modification
    // happening on original query.
    $sub_query->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 {
  /**
   * Confirm that we can fetch a record properly in default object mode.
   */
  function testQueryFetchDefault() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25));
    $this->assertTrue($result instanceof DatabaseStatementInterface, 'Result set is a Backdrop statement object.');
    foreach ($result as $record) {
      $records[] = $record;
      $this->assertTrue(is_object($record), 'Record is an object.');
      $this->assertIdentical($record->name, 'John', '25 year old is John.');
    }

    $this->assertIdentical(count($records), 1, 'There is only one record.');
  }

  /**
   * Confirm that we can fetch a record to an object explicitly.
   */
  function testQueryFetchObject() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_OBJ));
    foreach ($result as $record) {
      $records[] = $record;
      $this->assertTrue(is_object($record), 'Record is an object.');
      $this->assertIdentical($record->name, 'John', '25 year old is John.');
    }

    $this->assertIdentical(count($records), 1, 'There is only one record.');
  }

  /**
   * Confirm that we can fetch a record to an array associative explicitly.
   */
  function testQueryFetchArray() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25), array('fetch' => PDO::FETCH_ASSOC));
    foreach ($result as $record) {
      $records[] = $record;
      if ($this->assertTrue(is_array($record), 'Record is an array.')) {
        $this->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
      }
    }

    $this->assertIdentical(count($records), 1, 'There is only one record.');
  }

  /**
   * Test passing custom constructor_args for a FETCH_CLASS.
   *
   * @see FakeRecordWithConstructor
   */
  public function testQueryFetchClassCustomConstructorArgs() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(':age' => 25));
    // @see EntityPlusController::query (in the contrib entity_plus module).
    $result->setFetchMode(PDO::FETCH_CLASS, 'FakeRecordWithConstructor', array('foo'));
    foreach ($result as $record) {
      $records[] = $record;
      if ($this->assertTrue($record instanceof FakeRecordWithConstructor, 'Record is an object of class FakeRecordWithConstructor.')) {
        $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 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 {
  // 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 access.');
    }
  }
}

/**
 * Test the insert builder.
 */
class DatabaseInsertTestCase extends DatabaseTestCase {
  /**
   * Test the very basic insert functionality.
   */
  function testSimpleInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $query = db_insert('test');
    $query->fields(array(
      'name' => 'Yoko',
      'age' => '29',
    ));
    $query->execute();

    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
    $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Yoko'))->fetchField();
    $this->assertIdentical($saved_age, '29', 'Can retrieve after inserting.');
  }

  /**
   * Test that we can insert multiple records in one query object.
   */
  function testMultiInsert() {
    $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $query = db_insert('test');
    $query->fields(array(
      'name' => 'Larry',
      'age' => '30',
    ));

    // 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 testInsertFieldOnlyDefinition() {
    // 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 {
  /**
   * Test that we can insert a single blob field successfully.
   */
  function testInsertOneBlob() {
    $data = "This is\000a test.";
    $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
    $id = db_insert('test_one_blob')
      ->fields(array('blob1' => $data))
      ->execute();
    $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
    $this->assertTrue($r['blob1'] === $data, format_string('Can insert a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
  }

  /**
   * Test that we can insert multiple blob fields in the same query.
   */
  function testInsertMultipleBlob() {
    $id = db_insert('test_two_blobs')
      ->fields(array(
        'blob1' => 'This is',
        'blob2' => 'a test',
      ))
      ->execute();
    $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
    $this->assertTrue($r['blob1'] === 'This is' && $r['blob2'] === 'a test', 'Can insert multiple blobs per row.');
  }
}

/**
 * Insert tests for "database default" values.
 */
class DatabaseInsertDefaultsTestCase extends DatabaseTestCase {
  /**
   * Test that we can run a query that is "default values for everything".
   */
  function testDefaultInsert() {
    $query = db_insert('test')->useDefaults(array('job'));
    $id = $query->execute();

    $schema = backdrop_get_schema('test');

    $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
    $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  }

  /**
   * Test that no action will be preformed if no fields are specified.
   */
  function testDefaultEmptyInsert() {
    $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    try {
      $result = db_insert('test')->execute();
      // This 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 = backdrop_get_schema('test');

    $job = db_query('SELECT job FROM {test} WHERE id = :id', array(':id' => $id))->fetchField();
    $this->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  }
}

/**
 * Update builder tests.
 */
class DatabaseUpdateTestCase extends DatabaseTestCase {
  /**
   * Confirm that we can update a single record successfully.
   */
  function testSimpleUpdate() {
    $num_updated = db_update('test')
      ->fields(array('name' => 'Tiffany'))
      ->condition('id', 1)
      ->execute();
    $this->assertIdentical($num_updated, 1, 'Updated 1 record.');

    $saved_name = db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 1))->fetchField();
    $this->assertIdentical($saved_name, 'Tiffany', 'Updated name successfully.');
  }

  /**
   * Confirm updating to NULL.
   */
  function testSimpleNullUpdate() {
    $this->ensureSampleDataNull();
    $num_updated = db_update('test_null')
      ->fields(array('age' => NULL))
      ->condition('name', 'Kermit')
      ->execute();
    $this->assertIdentical($num_updated, 1, 'Updated 1 record.');

    $saved_age = db_query('SELECT age FROM {test_null} WHERE name = :name', array(':name' => 'Kermit'))->fetchField();
    $this->assertNull($saved_age, 'Updated name successfully.');
  }

  /**
   * Confirm that we can update a multiple records successfully.
   */
  function testMultiUpdate() {
    $num_updated = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->condition('job', 'Singer')
      ->execute();
    $this->assertIdentical($num_updated, 2, 'Updated 2 records.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Confirm that we can update a multiple records with a non-equality condition.
   */
  function testMultiGTUpdate() {
    $num_updated = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->condition('age', 26, '>')
      ->execute();
    $this->assertIdentical($num_updated, 2, 'Updated 2 records.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Confirm that we can update a multiple records with a where call.
   */
  function testWhereUpdate() {
    $num_updated = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->where('age > :age', array(':age' => 26))
      ->execute();
    $this->assertIdentical($num_updated, 2, 'Updated 2 records.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Confirm that we can stack condition and where calls.
   */
  function testWhereAndConditionUpdate() {
    $update = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->where('age > :age', array(':age' => 26))
      ->condition('name', 'Ringo');
    $num_updated = $update->execute();
    $this->assertIdentical($num_updated, 1, 'Updated 1 record.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  }

  /**
   * Test updating with expressions.
   */
  function testExpressionUpdate() {
    // Ensure that expressions are handled properly. This should set every
    // record's age to a square of itself
    $num_rows = db_update('test')
      ->expression('age', 'age * age')
      ->execute();
    $this->assertIdentical($num_rows, 4, 'Updated 4 records.');
  }

  /**
   * Tests return value on update.
   */
  function testUpdateAffectedRows() {
    // At 5am in the morning, all band members but those with a priority 1 task
    // are sleeping. So we set their tasks to 'sleep'. 5 records match the
    // condition and therefore are affected by the query, even though two of
    // them actually don't have to be changed because their value was already
    // 'sleep'. Still, execute() should return 5 affected rows, not only 3,
    // because that's cross-db expected behaviour.
    $num_rows = db_update('test_task')
      ->condition('priority', 1, '<>')
      ->fields(array('task' => 'sleep'))
      ->execute();
    $this->assertIdentical($num_rows, 5, 'Correctly returned 5 affected rows.');
  }

  /**
   * Confirm that we can update the primary key of a record successfully.
   */
  function testPrimaryKeyUpdate() {
    $num_updated = db_update('test')
      ->fields(array('id' => 42, 'name' => 'John'))
      ->condition('id', 1)
      ->execute();
    $this->assertIdentical($num_updated, 1, t('Updated 1 record.'));

    $saved_name= db_query('SELECT name FROM {test} WHERE id = :id', array(':id' => 42))->fetchField();
    $this->assertIdentical($saved_name, 'John', t('Updated primary key successfully.'));
  }
}

/**
 * Tests for more complex update statements.
 */
class DatabaseUpdateComplexTestCase extends DatabaseTestCase {
  /**
   * Test updates with OR conditionals.
   */
  function testOrConditionUpdate() {
    $update = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->condition(db_or()
        ->condition('name', 'John')
        ->condition('name', 'Paul')
      );
    $num_updated = $update->execute();
    $this->assertIdentical($num_updated, 2, 'Updated 2 records.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Test WHERE IN clauses.
   */
  function testInConditionUpdate() {
    $num_updated = db_update('test')
      ->fields(array('job' => 'Musician'))
      ->condition('name', array('John', 'Paul'), 'IN')
      ->execute();
    $this->assertIdentical($num_updated, 2, 'Updated 2 records.');

    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(':job' => 'Musician'))->fetchField();
    $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Test WHERE NOT IN clauses.
   */
  function testNotInConditionUpdate() {
    // 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 {
  /**
   * Confirm that we can update a blob column.
   */
  function testUpdateOneBlob() {
    $data = "This is\000a test.";
    $this->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
    $id = db_insert('test_one_blob')
      ->fields(array('blob1' => $data))
      ->execute();

    $data .= $data;
    db_update('test_one_blob')
      ->condition('id', $id)
      ->fields(array('blob1' => $data))
      ->execute();

    $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(':id' => $id))->fetchAssoc();
    $this->assertTrue($r['blob1'] === $data, format_string('Can update a blob: id @id, @data.', array('@id' => $id, '@data' => serialize($r))));
  }

  /**
   * Confirm that we can update two blob columns in the same table.
   */
  function testUpdateMultipleBlob() {
    $id = db_insert('test_two_blobs')
      ->fields(array(
        'blob1' => 'This is',
        'blob2' => 'a test',
      ))
      ->execute();

    db_update('test_two_blobs')
      ->condition('id', $id)
      ->fields(array('blob1' => 'and so', 'blob2' => 'is this'))
      ->execute();

    $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(':id' => $id))->fetchAssoc();
    $this->assertTrue($r['blob1'] === 'and so' && $r['blob2'] === 'is this', 'Can update multiple blobs per row.');
  }
}

/**
 * Delete/Truncate tests.
 *
 * The DELETE tests are not as extensive, as all of the interesting code for
 * DELETE queries is in the conditional which is identical to the UPDATE and
 * SELECT conditional handling.
 *
 * The TRUNCATE tests are not extensive either, because the behavior of
 * TRUNCATE queries is not consistent across database engines. We only test
 * that a TRUNCATE query actually deletes all rows from the target table.
 */
class DatabaseDeleteTruncateTestCase extends DatabaseTestCase {
  /**
   * Confirm that we can use a subselect in a delete successfully.
   */
  function testSubselectDelete() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
    $pid_to_delete = db_query("SELECT * FROM {test_task} WHERE task = 'sleep'")->fetchField();

    $subquery = db_select('test', 't')
      ->fields('t', array('id'))
      ->condition('t.id', array($pid_to_delete), 'IN');
    $delete = db_delete('test_task')
      ->condition('task', 'sleep')
      ->condition('pid', $subquery, 'IN');

    $num_deleted = $delete->execute();
    $this->assertEqual($num_deleted, 1, "Deleted 1 record.");

    $num_records_after = db_query('SELECT COUNT(*) FROM {test_task}')->fetchField();
    $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  }

  /**
   * Confirm that we can delete a single record successfully.
   */
  function testSimpleDelete() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $num_deleted = db_delete('test')
      ->condition('id', 1)
      ->execute();
    $this->assertIdentical($num_deleted, 1, 'Deleted 1 record.');

    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
    $this->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  }

  /**
   * Confirm that we can truncate a whole table successfully.
   */
  function testTruncate() {
    $num_records_before = db_query("SELECT COUNT(*) FROM {test}")->fetchField();

    db_truncate('test')->execute();

    $num_records_after = db_query("SELECT COUNT(*) FROM {test}")->fetchField();
    $this->assertEqual(0, $num_records_after, 'Truncate really deletes everything.');
  }
}

/**
 * Test the MERGE query builder.
 */
class DatabaseMergeTestCase extends DatabaseTestCase {
  /**
   * Confirm that we can merge-insert a record successfully.
   */
  function testMergeInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
    $max_id_before = db_query('SELECT MAX(id) FROM {test}')->fetchField();

    $merge = db_merge('test')
      ->key(array('job' => 'Presenter'))
      ->fields(array(
        'age' => 31,
        'name' => 'Tiffany',
      ));
    $result = $merge->execute();

    $this->assertEqual($result, MergeQuery::STATUS_INSERT, 'Insert status returned.');
    $this->assertEqual($merge->getResult(), $max_id_before + 1, 'Incremented ID returned.');

    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
    $this->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');

    $person = db_query('SELECT * FROM {test} WHERE job = :job', array(':job' => 'Presenter'))->fetch();
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 31, 'Age set correctly.');
    $this->assertEqual($person->job, 'Presenter', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully.
   */
  function testMergeUpdate() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
    $num_records_matched = db_query('SELECT COUNT(*) FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetchField();

    $merge = db_merge('test_people')
      ->key(array('job' => 'Speaker'))
      ->fields(array(
        'age' => 31,
        'name' => 'Tiffany',
      ));
    $result = $merge->execute();

    $this->assertEqual($result, MergeQuery::STATUS_UPDATE, 'Update status returned.');
    $this->assertEqual($merge->getResult(), $num_records_matched, 'Number of updated rows matches.');
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');

    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 31, 'Age set correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully, with different insert and update.
   */
  function testMergeUpdateExcept() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();

    db_merge('test_people')
      ->key(array('job' => 'Speaker'))
      ->insertFields(array('age' => 31))
      ->updateFields(array('name' => 'Tiffany'))
      ->execute();

    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');

    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
    $this->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully, with alternate replacement.
   */
  function testMergeUpdateExplicit() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();

    db_merge('test_people')
      ->key(array('job' => 'Speaker'))
      ->insertFields(array(
        'age' => 31,
        'name' => 'Tiffany',
      ))
      ->updateFields(array(
        'name' => 'Joe',
      ))
      ->execute();

    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();
    $this->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');

    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetch();
    $this->assertEqual($person->name, 'Joe', 'Name set correctly.');
    $this->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully, with expressions.
   */
  function testMergeUpdateExpression() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')->fetchField();

    $age_before = db_query('SELECT age FROM {test_people} WHERE job = :job', array(':job' => 'Speaker'))->fetchField();

    // 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 {
  /**
   * Test rudimentary SELECT statements.
   */
  function testSimpleSelect() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test rudimentary SELECT statement with a COMMENT.
   */
  function testSimpleComment() {
    $query = db_select('test');
    $query->comment('Testing query comments');
    $query->addField('test', 'name');
    $query->addField('test', 'age', 'age');
    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $query = (string)$query;
    $expected = "/* Testing query comments */ SELECT `test`.`name` AS `name`, `test`.`age` AS `age`\nFROM \n{test} `test`";

    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this->assertEqual($query, $expected, 'The flattened query contains the comment string.');
  }

  /**
   * Test query COMMENT system against vulnerabilities.
   */
  function testVulnerableComment() {
    $query = db_select('test');
    $query->comment('Testing query comments */ SELECT nid FROM {node}; --');
    $query->addField('test', 'name');
    $query->addField('test', 'age', 'age');
    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $query = (string)$query;
    $expected = "/* Testing query comments  * / SELECT nid FROM {node}; -- */ SELECT `test`.`name` AS `name`, `test`.`age` AS `age`\nFROM \n{test} `test`";

    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');

    $connection = Database::getConnection();
    foreach ($this->makeCommentsProvider() as $test_set) {
      list($expected, $comments) = $test_set;
      $this->assertEqual($expected, $connection->makeComment($comments));
    }
  }

  /**
   * Provides expected and input values for testVulnerableComment().
   */
  function makeCommentsProvider() {
    return array(
      array(
        '/*  */ ',
        array(''),
      ),
      // 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 a comparison with NULL is always FALSE.
   */
  function testNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->condition('age', NULL)
      ->execute()->fetchCol();

    $this->assertEqual(count($names), 0, t('No records found when comparing to NULL.'));
  }

  /**
   * Test that we can find a record with a NULL value.
   */
  function testIsNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->isNull('age')
      ->execute()->fetchCol();

    $this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
    $this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
  }

  /**
   * Test that we can find a record without a NULL value.
   */
  function testIsNotNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->isNotNull('tn.age')
      ->orderBy('name')
      ->execute()->fetchCol();

    $this->assertEqual(count($names), 2, 'Correct number of records found withNOT NULL age.');
    $this->assertEqual($names[0], 'Gonzo', 'Correct record returned for NOT NULL age.');
    $this->assertEqual($names[1], 'Kermit', 'Correct record returned for NOT NULL age.');
  }

  /**
   * Test that we can UNION multiple Select queries together. This is
   * semantically equal to UNION DISTINCT, so we don't explicitly 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 Backdrop layer only, rather than trying
   * to test that the database's random number generator actually produces
   * random queries (which is very difficult to do without an unacceptable risk
   * of the test failing by accident).
   *
   * Therefore, in this test we run the same query twice and assert that the two
   * results are reordered versions of each other (as well as of the same query
   * without the random ordering). It is reasonable to assume that if we run the
   * same select query twice and the results are in a different order each time,
   * the only way this could happen is if we have successfully triggered the
   * database's random ordering functionality.
   */
  function testRandomOrder() {
    // 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 sub-selects in a dynamic SELECT query.
 */
class DatabaseSelectSubqueryTestCase extends DatabaseTestCase {
  /**
   * 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.');

    // Create a subquery that only returns one value
    $subquery2 = db_select('test_task', 'tt');
    $subquery2->addField('tt', 'priority');
    $subquery2->condition('tt.task', 'eat'); // Gives 3

    // Create another query that tests against the value returned by the subquery
    $select2 = db_select('test_task', 'tt2');
    $select2->addField('tt2', 'task');
    $select2->condition('tt2.priority', $subquery2, '<');

    $tasks = $select2->execute()->fetchCol();
    $this->assertEqual(count($tasks), 4, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can use a subquery in a JOIN clause.
   */
  function testJoinSubquerySelect() {
    // 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 {
  /**
   * Test basic order by.
   */
  function testSimpleSelectOrdered() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $query->orderBy($age_field);
    $result = $query->execute();

    $num_records = 0;
    $last_age = 0;
    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue($record->age >= $last_age, 'Results returned in correct order.');
      $last_age = $record->age;
    }

    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test multiple order by.
   */
  function testSimpleSelectMultiOrdered() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $job_field = $query->addField('test', 'job');
    $query->orderBy($job_field);
    $query->orderBy($age_field);
    $result = $query->execute();

    $num_records = 0;
    $expected = array(
      array('Ringo', 28, 'Drummer'),
      array('John', 25, 'Singer'),
      array('George', 27, 'Singer'),
      array('Paul', 26, 'Songwriter'),
    );
    $results = $result->fetchAll(PDO::FETCH_NUM);
    foreach ($expected as $k => $record) {
      $num_records++;
      foreach ($record as $kk => $col) {
        if ($expected[$k][$kk] != $results[$k][$kk]) {
          $this->assertTrue(FALSE, 'Results returned in correct order.');
        }
      }
    }
    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test order by descending.
   */
  function testSimpleSelectOrderedDesc() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $query->orderBy($age_field, 'DESC');
    $result = $query->execute();

    $num_records = 0;
    $last_age = 100000000;
    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue($record->age <= $last_age, 'Results returned in correct order.');
      $last_age = $record->age;
    }

    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Tests that the sort direction is sanitized properly.
   */
  function testOrderByEscaping() {
    $query = db_select('test')->orderBy('name', 'invalid direction');
    $order_bys = $query->getOrderBy();
    $this->assertEqual($order_bys['name'], 'ASC', 'Invalid order by direction is converted to ASC.');
  }
}

/**
 * Test more complex select statements.
 */
class DatabaseSelectComplexTestCase extends DatabaseTestCase {
  /**
   * Test simple JOIN statements.
   */
  function testDefaultJoin() {
    $query = db_select('test_task', 't');
    $people_alias = $query->join('test', 'p', 't.pid = p.id');
    $name_field = $query->addField($people_alias, 'name', 'name');
    $task_field = $query->addField('t', 'task', 'task');
    $priority_field = $query->addField('t', 'priority', 'priority');

    $query->orderBy($priority_field);
    $result = $query->execute();

    $num_records = 0;
    $last_priority = 0;
    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue($record->$priority_field >= $last_priority, 'Results returned in correct order.');
      $this->assertNotEqual($record->$name_field, 'Ringo', 'Taskless person not selected.');
      $last_priority = $record->$priority_field;
    }

    $this->assertEqual($num_records, 7, 'Returned the correct number of rows.');
  }

  /**
   * Test LEFT OUTER joins.
   */
  function testLeftOuterJoin() {
    $query = db_select('test', 'p');
    $people_alias = $query->leftJoin('test_task', 't', 't.pid = p.id');
    $name_field = $query->addField('p', 'name', 'name');
    $task_field = $query->addField($people_alias, 'task', 'task');
    $priority_field = $query->addField($people_alias, 'priority', 'priority');

    $query->orderBy($name_field);
    $result = $query->execute();

    $num_records = 0;
    $last_name = 0;

    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue(strcmp($record->$name_field, $last_name) >= 0, 'Results returned in correct order.');
      $last_priority = $record->$name_field;
    }

    $this->assertEqual($num_records, 8, 'Returned the correct number of rows.');
  }

  /**
   * Test GROUP BY clauses.
   */
  function testGroupBy() {
    $query = db_select('test_task', 't');
    $count_field = $query->addExpression('COUNT(task)', 'num');
    $task_field = $query->addField('t', 'task');
    $query->orderBy($count_field);
    $query->groupBy($task_field);
    $result = $query->execute();

    $num_records = 0;
    $last_count = 0;
    $records = array();
    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
      $last_count = $record->$count_field;
      $records[$record->$task_field] = $record->$count_field;
    }

    $correct_results = array(
      'eat' => 1,
      'sleep' => 2,
      'code' => 1,
      'found new band' => 1,
      'perform at superbowl' => 1,
    );

    foreach ($correct_results as $task => $count) {
      $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
    }

    $this->assertEqual($num_records, 6, 'Returned the correct number of total rows.');
  }

  /**
   * Test GROUP BY and HAVING clauses together.
   */
  function testGroupByAndHaving() {
    $query = db_select('test_task', 't');
    $count_field = $query->addExpression('COUNT(task)', 'num');
    $task_field = $query->addField('t', 'task');
    $query->orderBy($count_field);
    $query->groupBy($task_field);
    $query->having('COUNT(task) >= 2');
    $result = $query->execute();

    $num_records = 0;
    $last_count = 0;
    $records = array();
    foreach ($result as $record) {
      $num_records++;
      $this->assertTrue($record->$count_field >= 2, 'Record has the minimum count.');
      $this->assertTrue($record->$count_field >= $last_count, 'Results returned in correct order.');
      $last_count = $record->$count_field;
      $records[$record->$task_field] = $record->$count_field;
    }

    $correct_results = array(
      'sleep' => 2,
    );

    foreach ($correct_results as $task => $count) {
      $this->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array('@task' => $task)));
    }

    $this->assertEqual($num_records, 1, 'Returned the correct number of total rows.');
  }

  /**
   * Test range queries. The SQL clause varies with the database.
   */
  function testRange() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $query->range(0, 2);
    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  }

  /**
   * Test distinct queries.
   */
  function testDistinct() {
    $query = db_select('test_task');
    $task_field = $query->addField('test_task', 'task');
    $query->distinct();
    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $this->assertEqual($num_records, 6, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can generate a count query from a built query.
   */
  function testCountQuery() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $query->orderBy('name');

    $count = $query->countQuery()->execute()->fetchField();

    $this->assertEqual($count, 4, 'Counted the correct number of records.');

    // 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 clause.');

    // 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 that join conditions can use Condition objects.
   */
  public function testJoinConditionObject() {
    // Same test as testDefaultJoin, but with a Condition object.
    $query = db_select('test_task', 't');
    $join_cond = db_and()->where('t.pid = p.id');
    $people_alias = $query->join('test', 'p', $join_cond);
    $name_field = $query->addField($people_alias, 'name', 'name');
    $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 a condition object that creates placeholders.
    $t1_name = 'John';
    $t2_name = 'George';
    $join_cond = db_and()
      ->condition('t1.name', $t1_name)
      ->condition('t2.name', $t2_name);
    $query = db_select('test', 't1');
    $query->innerJoin('test', 't2', $join_cond);
    $query->addField('t1', 'name', 't1_name');
    $query->addField('t2', 'name', 't2_name');

    $num_records = $query->countQuery()->execute()->fetchField();
    $this->assertEqual($num_records, 1, 'Query expected to return 1 row. Actual: ' . $num_records);
    if ($num_records == 1) {
      $record = $query->execute()->fetchObject();
      $this->assertEqual($record->t1_name, $t1_name, 'Query expected to retrieve name ' . $t1_name . ' from table t1. Actual: ' . $record->t1_name);
      $this->assertEqual($record->t2_name, $t2_name, 'Query expected to retrieve name ' . $t2_name . ' from table t2. Actual: ' . $record->t2_name);
    }
  }
}

/**
 * Test more complex select statements, part 2.
 */
class DatabaseSelectComplexTestCase2 extends DatabaseTestCase {
  function setUp() {
    parent::setUp(array('node_access_test'));
  }

  /**
   * Test that we can join on a query.
   */
  function testJoinSubquery() {
    $acct = $this->backdropCreateUser();
    $this->backdropLogin($acct);

    $query = db_select('test_task', 'tt', array('target' => 'replica'));
    $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 {
  /**
   * 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->backdropGet('database_test/pager_query_even/' . $limit, array('query' => array('page' => $page)));
      $data = json_decode($this->backdropGetContent());

      if ($page == $num_pages) {
        $correct_number = $count - ($limit * $page);
      }

      $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
    }
  }

  /**
   * Confirm that a pager query returns the correct results.
   *
   * Note that we have to make an HTTP request to a test page handler
   * because the pager depends on GET parameters.
   */
  function testOddPagerQuery() {
    // 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->backdropGet('database_test/pager_query_odd/' . $limit, array('query' => array('page' => $page)));
      $data = json_decode($this->backdropGetContent());

      if ($page == $num_pages) {
        $correct_number = $count - ($limit * $page);
      }

      $this->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array('@number' => $correct_number)));
    }
  }

  /**
   * Confirm that a pager query with inner pager query returns valid results.
   *
   * This is a regression test for #467984.
   */
  function testInnerPagerQuery() {
    $query = db_select('test', 't')->extend('PagerDefault');
    $query
      ->fields('t', array('age'))
      ->orderBy('age')
      ->limit(5);

    $outer_query = db_select($query);
    $outer_query->addField('subquery', 'age');

    $ages = $outer_query
      ->execute()
      ->fetchCol();
    $this->assertEqual($ages, array(25, 26, 27, 28), 'Inner pager query returned the correct ages.');
  }

  /**
   * Confirm that a paging query with a having expression returns valid results.
   *
   * This is a regression test for #467984.
   */
  function testHavingPagerQuery() {
    $query = db_select('test', 't')->extend('PagerDefault');
    $query
      ->fields('t', array('name'))
      ->orderBy('name')
      ->groupBy('name')
      ->having('MAX(age) > :count', array(':count' => 26))
      ->limit(5);

    $ages = $query
      ->execute()
      ->fetchCol();
    $this->assertEqual($ages, array('George', 'Ringo'), 'Pager query with having expression returned the correct ages.');
  }

  /**
   * Confirm that every pager gets a valid non-overlapping element ID.
   */
  function testElementNumbers() {
    $_GET['page'] = '3, 2, 1, 0';

    $query = db_select('test', 't')->extend('PagerDefault');
    $query->element(2)
      ->fields('t', array('name'))
      ->orderBy('age')
      ->limit(1);
      $this->assertEqual(2, $query->getElement());
      $name = $query->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.
    $query = db_select('test', 't')->extend('PagerDefault');
    $query->element(1)
      ->fields('t', array('name'))
      ->orderBy('age')
      ->limit(1);
      $this->assertEqual(1, $query->getElement());
      $name = $query->execute()->fetchField();
    $this->assertEqual($name, 'George', 'Pager query #2 with a specified element ID returned the correct results.');

    $query = db_select('test', 't')->extend('PagerDefault');
    $query->fields('t', array('name'))
      ->orderBy('age')
      ->limit(1);
    $this->assertEqual(3, $query->getElement());
    $name = $query->execute()->fetchField();
    $this->assertEqual($name, 'John', 'Pager query #3 with a generated element ID returned the correct results.');

    unset($_GET['page']);
  }
}


class DatabaseSelectTableSortDefaultTestCase extends DatabaseTestCase {
  /**
   * Confirm that a tablesort query returns the correct results.
   *
   * Note that we have to make an HTTP request to a test page handler
   * because the pager depends on GET parameters.
   */
  function testTableSortQuery() {
    $sorts = array(
      array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
      array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
      array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
      array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
      // more elements here

    );

    foreach ($sorts as $sort) {
      $this->backdropGet('database_test/tablesort/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
      $data = json_decode($this->backdropGetContent());

      $first = array_shift($data->tasks);
      $last = array_pop($data->tasks);

      $this->assertEqual($first->task, $sort['first'], 'Items appear in the correct order.');
      $this->assertEqual($last->task, $sort['last'], 'Items appear in the correct order.');
    }
  }

  /**
   * Confirm that if a tablesort's orderByHeader is called before another orderBy, that the header happens first.
   *
   */
  function testTableSortQueryFirst() {
    $sorts = array(
      array('field' => t('Task ID'), 'sort' => 'desc', 'first' => 'perform at superbowl', 'last' => 'eat'),
      array('field' => t('Task ID'), 'sort' => 'asc', 'first' => 'eat', 'last' => 'perform at superbowl'),
      array('field' => t('Task'), 'sort' => 'asc', 'first' => 'code', 'last' => 'sleep'),
      array('field' => t('Task'), 'sort' => 'desc', 'first' => 'sleep', 'last' => 'code'),
      // more elements here

    );

    foreach ($sorts as $sort) {
      $this->backdropGet('database_test/tablesort_first/', array('query' => array('order' => $sort['field'], 'sort' => $sort['sort'])));
      $data = json_decode($this->backdropGetContent());

      $first = array_shift($data->tasks);
      $last = array_pop($data->tasks);

      $this->assertEqual($first->task, $sort['first'], format_string('Items appear in the correct order sorting by @field @sort.', array('@field' => $sort['field'], '@sort' => $sort['sort'])));
      $this->assertEqual($last->task, $sort['last'], format_string('Items appear in the correct order sorting by @field @sort.', array('@field' => $sort['field'], '@sort' => $sort['sort'])));
    }
  }

  /**
   * Confirm that if a sort is not set in a tableselect form there is no error thrown when using the default.
   */
  function testTableSortDefaultSort() {
    $this->backdropGet('database_test/tablesort_default_sort');
    // 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 {
  /**
   * 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, 'Correct metadata returned.');

    $return = $query->getMetaData('nothere');
    $this->assertNull($return, 'Non-existent key returned NULL.');
  }
}

/**
 * Select alter tests.
 *
 * @see database_test_query_alter()
 */
class DatabaseAlterTestCase extends DatabaseTestCase {
  /**
   * Test that we can do basic alters.
   */
  function testSimpleAlter() {
    $query = db_select('test');
    $query->addField('test', 'name');
    $query->addField('test', 'age', 'age');
    $query->addTag('database_test_alter_add_range');

    $result = $query->execute();

    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }

    $this->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can alter the joins on a query.
   */
  function testAlterWithJoin() {
    $query = db_select('test_task');
    $tid_field = $query->addField('test_task', 'tid');
    $task_field = $query->addField('test_task', 'task');
    $query->orderBy($task_field);
    $query->addTag('database_test_alter_add_join');

    $result = $query->execute();

    $records = $result->fetchAll();

    $this->assertEqual(count($records), 2, 'Returned the correct number of rows.');

    $this->assertEqual($records[0]->name, 'George', 'Correct data retrieved.');
    $this->assertEqual($records[0]->$tid_field, 4, 'Correct data retrieved.');
    $this->assertEqual($records[0]->$task_field, 'sing', 'Correct data retrieved.');
    $this->assertEqual($records[1]->name, 'George', 'Correct data retrieved.');
    $this->assertEqual($records[1]->$tid_field, 5, 'Correct data retrieved.');
    $this->assertEqual($records[1]->$task_field, 'sleep', 'Correct data retrieved.');
  }

  /**
   * Test that we can alter a query's conditionals.
   */
  function testAlterChangeConditional() {
    $query = db_select('test_task');
    $tid_field = $query->addField('test_task', 'tid');
    $pid_field = $query->addField('test_task', 'pid');
    $task_field = $query->addField('test_task', 'task');
    $people_alias = $query->join('test', 'people', "test_task.pid = people.id");
    $name_field = $query->addField($people_alias, 'name', 'name');
    $query->condition('test_task.tid', '1');
    $query->orderBy($tid_field);
    $query->addTag('database_test_alter_change_conditional');

    $result = $query->execute();

    $records = $result->fetchAll();

    $this->assertEqual(count($records), 1, 'Returned the correct number of rows.');
    $this->assertEqual($records[0]->$name_field, 'John', 'Correct data retrieved.');
    $this->assertEqual($records[0]->$tid_field, 2, 'Correct data retrieved.');
    $this->assertEqual($records[0]->$pid_field, 1, 'Correct data retrieved.');
    $this->assertEqual($records[0]->$task_field, 'sleep', 'Correct data retrieved.');
  }

  /**
   * Test that we can alter the fields of a query.
   */
  function testAlterChangeFields() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addField('test', 'age', 'age');
    $query->orderBy('name');
    $query->addTag('database_test_alter_change_fields');

    $record = $query->execute()->fetch();
    $this->assertEqual($record->$name_field, 'George', 'Correct data retrieved.');
    $this->assertFalse(isset($record->$age_field), 'Age field not found, as intended.');
  }

  /**
   * Test that we can alter expressions in the query.
   */
  function testAlterExpression() {
    $query = db_select('test');
    $name_field = $query->addField('test', 'name');
    $age_field = $query->addExpression("age*2", 'double_age');
    $query->condition('age', 27);
    $query->addTag('database_test_alter_change_expressions');
    $result = $query->execute();

    // 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 {
  function setUp() {
    parent::setUp(array('node'));
  }

  /**
   * Regression test for #310447.
   *
   * Tries to insert non-ascii UTF-8 data in a database column and checks
   * if its stored properly.
   */
  function testRegression_310447() {
    // 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 {
  /**
   * 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 primary credentials to a replica connection and to another fake
    // connection.
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('default', 'replica', $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' => 'replica'));//->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'], 'replica', 'Second query used replica 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 "replica" 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 primary 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' => 'replica'))->fetchCol();

    db_set_active($old_key);

    $queries1 = Database::getLog('testing1');
    $queries2 = Database::getLog('testing1', 'test2');

    $this->assertEqual(count($queries1), 1, 'Correct number of queries recorded for first connection.');
    $this->assertEqual(count($queries2), 1, 'Correct number of queries recorded for second connection.');
  }
}

/**
 * Query serialization tests.
 */
class DatabaseSerializeQueryTestCase extends DatabaseTestCase {
  /**
   * Confirm that a query can be serialized and unserialized.
   */
  function testSerializeQuery() {
    $query = db_select('test');
    $query->addField('test', 'age');
    $query->condition('name', 'Ringo');
    // 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 BackdropWebTestCase {
  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 BackdropWebTestCase {
  function setUp() {
    parent::setUp('database_test');
  }

  /**
   * Return the number of rows of a table.
   */
  function countTableRows($table_name) {
    return db_select($table_name)->countQuery()->execute()->fetchField();
  }

  /**
   * Confirm that temporary tables work and are limited to one request.
   */
  function testTemporaryQuery() {
    $this->backdropGet('database_test/db_query_temporary');
    $data = json_decode($this->backdropGetContent());
    if ($data) {
      $this->assertEqual($this->countTableRows("system"), $data->row_count, 'The temporary table contains the correct amount of rows.');
      $this->assertFalse(db_table_exists($data->table_name), 'The temporary table is, indeed, temporary.');
    }
    else {
      $this->fail("The creation of the temporary table failed.");
    }

    // 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 Backdrop
 * across multiple kinds of database systems, we test that the
 * database system interprets SQL syntax in an expected fashion.
 */
class DatabaseBasicSyntaxTestCase extends DatabaseTestCase {
  /**
   * Test for string concatenation.
   */
  function 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 {
  /**
   * 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 {
  /**
   * 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 succeeded 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.');
    }
  }

}

/**
 * Backdrop-specific SQL syntax tests.
 */
class DatabaseQueryTestCase extends DatabaseTestCase {
  /**
   * Test that we can specify an array of values in the query by passing in an array.
   */
  function testArraySubstitution() {
    $names = db_query('SELECT name FROM {test} WHERE age IN (:ages) ORDER BY age', array(':ages' => array(25, 26, 27)))->fetchAll();

    $this->assertEqual(count($names), 3, 'Correct number of names returned');
  }

  /**
   * Test SQL injection via database query array arguments.
   */
  public function testArrayArgumentsSQLInjection() {
    // Attempt SQL injection and verify that it does not work.
    $condition = array(
      "1 ;INSERT INTO {test} SET name = 'test12345678'; -- " => '',
      '1' => '',
    );
    try {
      db_query("SELECT * FROM {test} WHERE name = :name", array(':name' => $condition))->fetchObject();
      $this->fail('SQL injection attempt via array arguments should result in a PDOException.');
    }
    catch (PDOException $e) {
      $this->pass('SQL injection attempt via array arguments should result in a PDOException.');
    }

    // 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 {
  /**
   * Helper method for transaction unit test. This "outer layer" transaction
   * starts and then encapsulates the "inner layer" transaction. This nesting
   * is used to evaluate whether the the database transaction API properly
   * supports nesting. By "properly supports," we mean the outer transaction
   * continues to exist regardless of what functions are called and whether
   * those functions start their own transactions.
   *
   * In contrast, a typical database would commit the outer transaction, start
   * a new transaction for the inner layer, commit the inner layer transaction,
   * and then be confused when the outer layer transaction tries to commit its
   * transaction (which was already committed when the inner transaction
   * started).
   *
   * @param $suffix
   *   Suffix to add to field values to differentiate tests.
   * @param $rollback
   *   Whether or not to try rolling back the transaction when we're done.
   * @param $ddl_statement
   *   Whether to execute a DDL statement during the inner transaction.
   */
  protected function transactionOuterLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
    $connection = Database::getConnection();
    $depth = $connection->transactionDepth();
    $txn = db_transaction();

    // 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();

      set_error_handler(array($this, 'rollBackWithoutTransactionErrorHandler'));
      try {
        // Rollback the outer transaction.
        $transaction->rollback();
        // @see \DatabaseConnection_mysql::rollback()
        if (PHP_VERSION_ID >= 80000) {
          $this->fail('Rolling back a transaction containing DDL should produce a warning.');
        }
      }
      catch (Exception $e) {
        $this->assertEqual('Rollback attempted when there is no active transaction.', $e->getMessage());
      }
      restore_error_handler();
      unset($transaction);
      $this->assertRowPresent('row');
    }
  }

  /**
   * Special handling of "rollback without transaction" errors.
   */
  public function rollBackWithoutTransactionErrorHandler($error_level, $message, $filename, $line) {
    // Throw an exception if this is a "rollback without transaction" error.
    if (strpos($message, 'Rollback attempted when there is no active transaction.') !== FALSE ) {
      throw new Exception('Rollback attempted when there is no active transaction.');
    }
    _backdrop_error_handler($error_level, $message, $filename, $line);
  }

  /**
   * 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');
  }
}

/**
 * Test proposed new data types for the schema API.
 */
class DatabaseExtraTypesTestCase extends BackdropWebTestCase {

  /**
   * Test the date data type.
   */
   function testDateField() {
     try {
       $date_table = array(
         'fields' => array(
           'date_field' => array(
             'description' => t('Test Date field'),
             'type' => 'date',
             'not null' => FALSE,
           ),
         ),
       );
       db_create_table('date_table', $date_table);
       $this->assertTrue(db_table_exists('date_table'), t('Created table with date field'));

       db_insert('date_table')->fields(array('date_field'))
         ->values(array('date_field' => '2001-01-01'))
         ->values(array('date_field' => '1856-12-31'))
         ->values(array('date_field' => '2100-06-30'))
         ->execute();

       $num_records = (int) db_query('SELECT COUNT(*) FROM {date_table}')->fetchField();
       $this->assertEqual($num_records, 3, t('Inserted 3 records, and counted 3 records'));
       $res = db_query('SELECT date_field from {date_table} ORDER BY date_field');

       $date = $res->fetch()->date_field;
       $this->assertEqual($date, '1856-12-31', t('Date retrieved in order @date', array('@date' => $date)));
       $date = $res->fetch()->date_field;
       $this->assertEqual($date, '2001-01-01', t('Date retrieved in order @date', array('@date' => $date)));
       $date = $res->fetch()->date_field;
       $this->assertEqual($date, '2100-06-30', t('Date retrieved in order @date', array('@date' => $date)));


       db_drop_table('date_table');
       $this->assertFalse(db_table_exists('date_table'), t('Dropped table with date field'));

     } catch (Exception $e) {
       $this->fail($e->getMessage());
     }

   }

  /**
   * Test the time data type.
   */
   function testTimeField() {
     try {
       $time_table = array(
         'fields' => array(
           'time_field' => array(
             'description' => t('Test Time field'),
             'type' => 'time',
             'not null' => FALSE,
           ),
         ),
       );
       db_create_table('time_table', $time_table);
       $this->assertTrue(db_table_exists('time_table'), t('Created table with time field'));

       db_insert('time_table')->fields(array('time_field'))
         ->values(array('time_field' => '12:59:00'))
         ->values(array('time_field' => '00:01:00'))
         ->values(array('time_field' => '23:17:00'))
         ->execute();

       $num_records = (int) db_query('SELECT COUNT(*) FROM {time_table}')->fetchField();
       $this->assertEqual($num_records, 3, t('Inserted 3 records, and counted 3 records'));
       $res = db_query('SELECT time_field from {time_table} ORDER BY time_field');

       $time = $res->fetch()->time_field;
       $this->assertEqual($time, '00:01:00', t('Time retrieved in order @time', array('@time' => $time)));
       $time = $res->fetch()->time_field;
       $this->assertEqual($time, '12:59:00', t('Time retrieved in order @time', array('@time' => $time)));
       $time = $res->fetch()->time_field;
       $this->assertEqual($time, '23:17:00', t('Time retrieved in order @time', array('@time' => $time)));

       db_drop_table('time_table');
       $this->assertFalse(db_table_exists('time_table'), t('Dropped table with time field'));
     } catch (Exception $e) {
       $this->fail($e->getMessage());
     }

  }

}

/**
 * Check the sequences API.
 */
class DatabaseNextIdCase extends BackdropWebTestCase {
  /**
   * Test that the sequences API work.
   */
  function testDbNextId() {
    $first = db_next_id();
    $second = db_next_id();
    // 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 BackdropWebTestCase {
  /**
   * Test that the empty result set behaves as empty.
   */
  function testEmpty() {
    $result = new DatabaseStatementEmpty();

    $this->assertTrue($result instanceof DatabaseStatementInterface, 'Class implements expected interface');
    $this->assertNull($result->fetchObject(), 'Null result returned.');
  }

  /**
   * Test that the empty result set iterates safely.
   */
  function testEmptyIteration() {
    $result = new DatabaseStatementEmpty();

    foreach ($result as $record) {
      $this->fail('Iterating empty result set should not iterate.');
      return;
    }

    $this->pass('Iterating empty result set skipped iteration.');
  }

  /**
   * Test that the empty result set mass-fetches in an expected way.
   */
  function testEmptyFetchAll() {
    $result = new DatabaseStatementEmpty();

    $this->assertEqual($result->fetchAll(), array(), 'Empty array returned from empty result set.');
  }
}

/**
 * Tests management of database connections.
 */
class ConnectionUnitTest extends BackdropUnitTestCase {

  protected $key;
  protected $target;

  protected $monitor;
  protected $originalCount;

  /**
   * @var string
   */
  protected $originalTarget;

  /**
   * @var bool
   */
  protected $skipTest;

  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 = ($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);
  }

}

/**
 * Tests database statements against deserialization attacks.
 *
 * PHPGGC (PHP Generic Gadget Chain) is a tool for creating serialized attack
 * strings.
 */
class DatabaseStatementPrefetchGadgetChainTestCase extends BackdropWebTestCase {
  /**
   * Tests unserialization within the theme registry.
   */
  public function testThemeRegistryGadgetChain() {
    if (version_compare(PHP_VERSION, '7.4', '<')) {
      $this->assert('pass', 'Test skipped for older PHP versions.');
      return;
    }
    $payload = 'O:13:"ThemeRegistry":1:{s:13:"keysToPersist";O:25:"DatabaseStatementPrefetch":3:{s:10:"currentRow";a:0:{}s:10:"fetchStyle";i:8;s:12:"fetchOptions";a:2:{s:5:"class";s:10:"FakeRecord";s:16:"constructor_args";a:1:{i:0;a:2:{i:0;s:3:"foo";i:1;s:3:"bar";}}}}}';
    try {
      // Do not assign the return value of unserialize as we want the objects
      // to be destructed immediately.
      unserialize($payload);
    } catch (Exception $e) {
      $this->assertEqual(get_class($e), 'UnexpectedValueException', get_class($e) . ' thrown when unserializing payload.');
    }
  }

  /**
   * Tests unserialization within the UpdateQuery class.
   */
  public function testUpdateQueryGadgetChain() {
    if (version_compare(PHP_VERSION, '7.4', '<')) {
      $this->assert('pass', 'Test skipped for older PHP versions.');
      return;
    }
    $payload = 'O:11:"UpdateQuery":2:{s:10:"connection";O:24:"DatabaseConnection_mysql":0:{}s:6:"fields";O:25:"DatabaseStatementPrefetch":3:{s:10:"currentRow";a:0:{}s:10:"fetchStyle";i:8;s:12:"fetchOptions";a:2:{s:5:"class";s:10:"FakeRecord";s:16:"constructor_args";a:2:{i:0;s:3:"foo";i:1;s:3:"bar";}}}}';
    try {
      // In this case we can assign the return value; casting it to a string
      // invokes the relevant __toString magic method.
      $result = (string) unserialize($payload);
    } catch (Exception $e) {
      $this->assertEqual(get_class($e), 'UnexpectedValueException', get_class($e) . ' thrown when unserializing payload.');
    }
  }
}