database_test.test in Drupal 7

File

modules/simpletest/tests/database_test.test

<?php

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

}

/**
 * Base test class for databases.
 *
 * Because all database tests share the same test data, we can centralize that
 * here.
 */
class DatabaseTestCase extends DrupalWebTestCase {
  protected $profile = 'testing';
  function setUp() {
    parent::setUp('database_test');
    $schema['test'] = drupal_get_schema('test');
    $schema['test_people'] = drupal_get_schema('test_people');
    $schema['test_people_copy'] = drupal_get_schema('test_people_copy');
    $schema['test_one_blob'] = drupal_get_schema('test_one_blob');
    $schema['test_two_blobs'] = drupal_get_schema('test_two_blobs');
    $schema['test_task'] = drupal_get_schema('test_task');
    $this
      ->installTables($schema);
    $this
      ->addSampleData();
  }

  /**
   * Set up several tables needed by a certain test.
   *
   * @param $schema
   *   An array of table definitions to install.
   */
  function installTables($schema) {

    // This ends up being a test for table drop and create, too, which is nice.
    foreach ($schema as $name => $data) {
      if (db_table_exists($name)) {
        db_drop_table($name);
      }
      db_create_table($name, $data);
    }
    foreach ($schema as $name => $data) {
      $this
        ->assertTrue(db_table_exists($name), format_string('Table @name created successfully.', array(
        '@name' => $name,
      )));
    }
  }

  /**
   * Set up tables for NULL handling.
   */
  function ensureSampleDataNull() {
    $schema['test_null'] = drupal_get_schema('test_null');
    $this
      ->installTables($schema);
    db_insert('test_null')
      ->fields(array(
      'name',
      'age',
    ))
      ->values(array(
      'name' => 'Kermit',
      'age' => 25,
    ))
      ->values(array(
      'name' => 'Fozzie',
      'age' => NULL,
    ))
      ->values(array(
      'name' => 'Gonzo',
      'age' => 27,
    ))
      ->execute();
  }

  /**
   * Setup our sample data.
   *
   * These are added using db_query(), since we're not trying to test the
   * INSERT operations here, just populate.
   */
  function addSampleData() {

    // We need the IDs, so we can't use a multi-insert here.
    $john = db_insert('test')
      ->fields(array(
      'name' => 'John',
      'age' => 25,
      'job' => 'Singer',
    ))
      ->execute();
    $george = db_insert('test')
      ->fields(array(
      'name' => 'George',
      'age' => 27,
      'job' => 'Singer',
    ))
      ->execute();
    $ringo = db_insert('test')
      ->fields(array(
      'name' => 'Ringo',
      'age' => 28,
      'job' => 'Drummer',
    ))
      ->execute();
    $paul = db_insert('test')
      ->fields(array(
      'name' => 'Paul',
      'age' => 26,
      'job' => 'Songwriter',
    ))
      ->execute();
    db_insert('test_people')
      ->fields(array(
      'name' => 'Meredith',
      'age' => 30,
      'job' => 'Speaker',
    ))
      ->execute();
    db_insert('test_task')
      ->fields(array(
      'pid',
      'task',
      'priority',
    ))
      ->values(array(
      'pid' => $john,
      'task' => 'eat',
      'priority' => 3,
    ))
      ->values(array(
      'pid' => $john,
      'task' => 'sleep',
      'priority' => 4,
    ))
      ->values(array(
      'pid' => $john,
      'task' => 'code',
      'priority' => 1,
    ))
      ->values(array(
      'pid' => $george,
      'task' => 'sing',
      'priority' => 2,
    ))
      ->values(array(
      'pid' => $george,
      'task' => 'sleep',
      'priority' => 2,
    ))
      ->values(array(
      'pid' => $paul,
      'task' => 'found new band',
      'priority' => 1,
    ))
      ->values(array(
      'pid' => $paul,
      'task' => 'perform at superbowl',
      'priority' => 3,
    ))
      ->execute();
    db_insert('virtual')
      ->fields(array(
      'function' => 'Function value 1',
    ))
      ->execute();
  }

}

/**
 * Test connection management.
 */
class DatabaseConnectionTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Connection tests',
      'description' => 'Tests of the core database system.',
      'group' => 'Database',
    );
  }

  /**
   * Test that connections return appropriate connection objects.
   */
  function testConnectionRouting() {

    // Clone the master credentials to a slave connection.
    // Note this will result in two independent connection objects that happen
    // to point to the same place.
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('default', 'slave', $connection_info['default']);
    $db1 = Database::getConnection('default', 'default');
    $db2 = Database::getConnection('slave', 'default');
    $this
      ->assertNotNull($db1, 'default connection is a real connection object.');
    $this
      ->assertNotNull($db2, 'slave connection is a real connection object.');
    $this
      ->assertNotIdentical($db1, $db2, 'Each target refers to a different connection.');

    // Try to open those targets another time, that should return the same objects.
    $db1b = Database::getConnection('default', 'default');
    $db2b = Database::getConnection('slave', 'default');
    $this
      ->assertIdentical($db1, $db1b, 'A second call to getConnection() returns the same object.');
    $this
      ->assertIdentical($db2, $db2b, 'A second call to getConnection() returns the same object.');

    // Try to open an unknown target.
    $unknown_target = $this
      ->randomName();
    $db3 = Database::getConnection($unknown_target, 'default');
    $this
      ->assertNotNull($db3, 'Opening an unknown target returns a real connection object.');
    $this
      ->assertIdentical($db1, $db3, 'An unknown target opens the default connection.');

    // Try to open that unknown target another time, that should return the same object.
    $db3b = Database::getConnection($unknown_target, 'default');
    $this
      ->assertIdentical($db3, $db3b, 'A second call to getConnection() returns the same object.');
  }

  /**
   * Test that connections return appropriate connection objects.
   */
  function testConnectionRoutingOverride() {

    // Clone the master credentials to a slave connection.
    // Note this will result in two independent connection objects that happen
    // to point to the same place.
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('default', 'slave', $connection_info['default']);
    Database::ignoreTarget('default', 'slave');
    $db1 = Database::getConnection('default', 'default');
    $db2 = Database::getConnection('slave', 'default');
    $this
      ->assertIdentical($db1, $db2, 'Both targets refer to the same connection.');
  }

  /**
   * Tests the closing of a database connection.
   */
  function testConnectionClosing() {

    // Open the default target so we have an object to compare.
    $db1 = Database::getConnection('default', 'default');

    // Try to close the default connection, then open a new one.
    Database::closeConnection('default', 'default');
    $db2 = Database::getConnection('default', 'default');

    // Opening a connection after closing it should yield an object different than the original.
    $this
      ->assertNotIdentical($db1, $db2, 'Opening the default connection after it is closed returns a new object.');
  }

  /**
   * Tests the connection options of the active database.
   */
  function testConnectionOptions() {
    $connection_info = Database::getConnectionInfo('default');

    // Be sure we're connected to the default database.
    $db = Database::getConnection('default', 'default');
    $connectionOptions = $db
      ->getConnectionOptions();

    // In the MySQL driver, the port can be different, so check individual
    // options.
    $this
      ->assertEqual($connection_info['default']['driver'], $connectionOptions['driver'], 'The default connection info driver matches the current connection options driver.');
    $this
      ->assertEqual($connection_info['default']['database'], $connectionOptions['database'], 'The default connection info database matches the current connection options database.');

    // Set up identical slave and confirm connection options are identical.
    Database::addConnectionInfo('default', 'slave', $connection_info['default']);
    $db2 = Database::getConnection('slave', 'default');
    $connectionOptions2 = $db2
      ->getConnectionOptions();

    // Get a fresh copy of the default connection options.
    $connectionOptions = $db
      ->getConnectionOptions();
    $this
      ->assertIdentical($connectionOptions, $connectionOptions2, 'The default and slave connection options are identical.');

    // Set up a new connection with different connection info.
    $test = $connection_info['default'];
    $test['database'] .= 'test';
    Database::addConnectionInfo('test', 'default', $test);
    $connection_info = Database::getConnectionInfo('test');

    // Get a fresh copy of the default connection options.
    $connectionOptions = $db
      ->getConnectionOptions();
    $this
      ->assertNotEqual($connection_info['default']['database'], $connectionOptions['database'], 'The test connection info database does not match the current connection options database.');
  }

}

/**
 * Test cloning Select queries.
 */
class DatabaseSelectCloneTest extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests, cloning',
      'description' => 'Test cloning Select queries.',
      'group' => 'Database',
    );
  }

  /**
   * Test that subqueries as value within conditions are cloned properly.
   */
  function testSelectConditionSubQueryCloning() {
    $subquery = db_select('test', 't');
    $subquery
      ->addField('t', 'id', 'id');
    $subquery
      ->condition('age', 28, '<');
    $query = db_select('test', 't');
    $query
      ->addField('t', 'name', 'name');
    $query
      ->condition('id', $subquery, 'IN');
    $clone = clone $query;

    // Cloned query should not be altered by the following modification
    // happening on original query.
    $subquery
      ->condition('age', 25, '>');
    $clone_result = $clone
      ->countQuery()
      ->execute()
      ->fetchField();
    $query_result = $query
      ->countQuery()
      ->execute()
      ->fetchField();

    // Make sure the cloned query has not been modified
    $this
      ->assertEqual(3, $clone_result, 'The cloned query returns the expected number of rows');
    $this
      ->assertEqual(2, $query_result, 'The query returns the expected number of rows');
  }

}

/**
 * Test fetch actions, part 1.
 *
 * We get timeout errors if we try to run too many tests at once.
 */
class DatabaseFetchTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Fetch tests',
      'description' => 'Test the Database system\'s various fetch capabilities.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that we can fetch a record properly in default object mode.
   */
  function testQueryFetchDefault() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(
      ':age' => 25,
    ));
    $this
      ->assertTrue($result instanceof DatabaseStatementInterface, 'Result set is a Drupal statement object.');
    foreach ($result as $record) {
      $records[] = $record;
      $this
        ->assertTrue(is_object($record), 'Record is an object.');
      $this
        ->assertIdentical($record->name, 'John', '25 year old is John.');
    }
    $this
      ->assertIdentical(count($records), 1, 'There is only one record.');
  }

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

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

  /**
   * Confirm that we can fetch a record into a new instance of a custom class.
   *
   * @see FakeRecord
   */
  function testQueryFetchClass() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(
      ':age' => 25,
    ), array(
      'fetch' => 'FakeRecord',
    ));
    foreach ($result as $record) {
      $records[] = $record;
      if ($this
        ->assertTrue($record instanceof FakeRecord, 'Record is an object of class FakeRecord.')) {
        $this
          ->assertIdentical($record->name, 'John', '25 year old is John.');
      }
    }
    $this
      ->assertIdentical(count($records), 1, 'There is only one record.');
  }

}

/**
 * Test fetch actions, part 2.
 *
 * We get timeout errors if we try to run too many tests at once.
 */
class DatabaseFetch2TestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Fetch tests, part 2',
      'description' => 'Test the Database system\'s various fetch capabilities.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp();
  }

  // Confirm that we can fetch a record into an indexed array explicitly.
  function testQueryFetchNum() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(
      ':age' => 25,
    ), array(
      'fetch' => PDO::FETCH_NUM,
    ));
    foreach ($result as $record) {
      $records[] = $record;
      if ($this
        ->assertTrue(is_array($record), 'Record is an array.')) {
        $this
          ->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
      }
    }
    $this
      ->assertIdentical(count($records), 1, 'There is only one record');
  }

  /**
   * Confirm that we can fetch a record into a doubly-keyed array explicitly.
   */
  function testQueryFetchBoth() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age = :age', array(
      ':age' => 25,
    ), array(
      'fetch' => PDO::FETCH_BOTH,
    ));
    foreach ($result as $record) {
      $records[] = $record;
      if ($this
        ->assertTrue(is_array($record), 'Record is an array.')) {
        $this
          ->assertIdentical($record[0], 'John', 'Record can be accessed numerically.');
        $this
          ->assertIdentical($record['name'], 'John', 'Record can be accessed associatively.');
      }
    }
    $this
      ->assertIdentical(count($records), 1, 'There is only one record.');
  }

  /**
   * Confirm that we can fetch an entire column of a result set at once.
   */
  function testQueryFetchCol() {
    $records = array();
    $result = db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ));
    $column = $result
      ->fetchCol();
    $this
      ->assertIdentical(count($column), 3, 'fetchCol() returns the right number of records.');
    $result = db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ));
    $i = 0;
    foreach ($result as $record) {
      $this
        ->assertIdentical($record->name, $column[$i++], 'Column matches direct accesss.');
    }
  }

}

/**
 * Test the insert builder.
 */
class DatabaseInsertTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Insert tests',
      'description' => 'Test the Insert query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Test the very basic insert functionality.
   */
  function testSimpleInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $query = db_insert('test');
    $query
      ->fields(array(
      'name' => 'Yoko',
      'age' => '29',
    ));
    $query
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Yoko',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '29', 'Can retrieve after inserting.');
  }

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

    // We should be able to specify values in any order if named.
    $query
      ->values(array(
      'age' => '31',
      'name' => 'Curly',
    ));

    // We should be able to say "use the field order".
    // This is not the recommended mechanism for most cases, but it should work.
    $query
      ->values(array(
      'Moe',
      '32',
    ));
    $query
      ->execute();
    $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before + 3, $num_records_after, 'Record inserts correctly.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Larry',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Curly',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Moe',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  }

  /**
   * Test that an insert object can be reused with new data after it executes.
   */
  function testRepeatedInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $query = db_insert('test');
    $query
      ->fields(array(
      'name' => 'Larry',
      'age' => '30',
    ));
    $query
      ->execute();

    // This should run the insert, but leave the fields intact.
    // We should be able to specify values in any order if named.
    $query
      ->values(array(
      'age' => '31',
      'name' => 'Curly',
    ));
    $query
      ->execute();

    // We should be able to say "use the field order".
    $query
      ->values(array(
      'Moe',
      '32',
    ));
    $query
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertIdentical((int) $num_records_before + 3, (int) $num_records_after, 'Record inserts correctly.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Larry',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Curly',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Moe',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  }

  /**
   * Test that we can specify fields without values and specify values later.
   */
  function testInsertFieldOnlyDefinintion() {

    // This is useful for importers, when we want to create a query and define
    // its fields once, then loop over a multi-insert execution.
    db_insert('test')
      ->fields(array(
      'name',
      'age',
    ))
      ->values(array(
      'Larry',
      '30',
    ))
      ->values(array(
      'Curly',
      '31',
    ))
      ->values(array(
      'Moe',
      '32',
    ))
      ->execute();
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Larry',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Curly',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Moe',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
  }

  /**
   * Test that inserts return the proper auto-increment ID.
   */
  function testInsertLastInsertID() {
    $id = db_insert('test')
      ->fields(array(
      'name' => 'Larry',
      'age' => '30',
    ))
      ->execute();
    $this
      ->assertIdentical($id, '5', 'Auto-increment ID returned successfully.');
  }

  /**
   * Test that the INSERT INTO ... SELECT (fields) ... syntax works.
   */
  function testInsertSelectFields() {
    $query = db_select('test_people', 'tp');

    // The query builder will always append expressions after fields.
    // Add the expression first to test that the insert fields are correctly
    // re-ordered.
    $query
      ->addExpression('tp.age', 'age');
    $query
      ->fields('tp', array(
      'name',
      'job',
    ))
      ->condition('tp.name', 'Meredith');

    // The resulting query should be equivalent to:
    // INSERT INTO test (age, name, job)
    // SELECT tp.age AS age, tp.name AS name, tp.job AS job
    // FROM test_people tp
    // WHERE tp.name = 'Meredith'
    db_insert('test')
      ->from($query)
      ->execute();
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Meredith',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  }

  /**
   * Tests that the INSERT INTO ... SELECT * ... syntax works.
   */
  function testInsertSelectAll() {
    $query = db_select('test_people', 'tp')
      ->fields('tp')
      ->condition('tp.name', 'Meredith');

    // The resulting query should be equivalent to:
    // INSERT INTO test_people_copy
    // SELECT *
    // FROM test_people tp
    // WHERE tp.name = 'Meredith'
    db_insert('test_people_copy')
      ->from($query)
      ->execute();
    $saved_age = db_query('SELECT age FROM {test_people_copy} WHERE name = :name', array(
      ':name' => 'Meredith',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
  }

}

/**
 * Insert tests using LOB fields, which are weird on some databases.
 */
class DatabaseInsertLOBTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Insert tests, LOB fields',
      'description' => 'Test the Insert query builder with LOB fields.',
      'group' => 'Database',
    );
  }

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

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

}

/**
 * Insert tests for "database default" values.
 */
class DatabaseInsertDefaultsTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Insert tests, default fields',
      'description' => 'Test the Insert query builder with default values.',
      'group' => 'Database',
    );
  }

  /**
   * Test that we can run a query that is "default values for everything".
   */
  function testDefaultInsert() {
    $query = db_insert('test')
      ->useDefaults(array(
      'job',
    ));
    $id = $query
      ->execute();
    $schema = drupal_get_schema('test');
    $job = db_query('SELECT job FROM {test} WHERE id = :id', array(
      ':id' => $id,
    ))
      ->fetchField();
    $this
      ->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  }

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

      // This is only executed if no exception has been thrown.
      $this
        ->fail('Expected exception NoFieldsException has not been thrown.');
    } catch (NoFieldsException $e) {
      $this
        ->pass('Expected exception NoFieldsException has been thrown.');
    }
    $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before, $num_records_after, 'Do nothing as no fields are specified.');
  }

  /**
   * Test that we can insert fields with values and defaults in the same query.
   */
  function testDefaultInsertWithFields() {
    $query = db_insert('test')
      ->fields(array(
      'name' => 'Bob',
    ))
      ->useDefaults(array(
      'job',
    ));
    $id = $query
      ->execute();
    $schema = drupal_get_schema('test');
    $job = db_query('SELECT job FROM {test} WHERE id = :id', array(
      ':id' => $id,
    ))
      ->fetchField();
    $this
      ->assertEqual($job, $schema['fields']['job']['default'], 'Default field value is set.');
  }

}

/**
 * Update builder tests.
 */
class DatabaseUpdateTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Update tests',
      'description' => 'Test the Update query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that we can update a single record successfully.
   */
  function testSimpleUpdate() {
    $num_updated = db_update('test')
      ->fields(array(
      'name' => 'Tiffany',
    ))
      ->condition('id', 1)
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $saved_name = db_query('SELECT name FROM {test} WHERE id = :id', array(
      ':id' => 1,
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_name, 'Tiffany', 'Updated name successfully.');
  }

  /**
   * Confirm updating to NULL.
   */
  function testSimpleNullUpdate() {
    $this
      ->ensureSampleDataNull();
    $num_updated = db_update('test_null')
      ->fields(array(
      'age' => NULL,
    ))
      ->condition('name', 'Kermit')
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $saved_age = db_query('SELECT age FROM {test_null} WHERE name = :name', array(
      ':name' => 'Kermit',
    ))
      ->fetchField();
    $this
      ->assertNull($saved_age, 'Updated name successfully.');
  }

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

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

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

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

  /**
   * Test updating with expressions.
   */
  function testExpressionUpdate() {

    // Set age = 1 for a single row for this test to work.
    db_update('test')
      ->condition('id', 1)
      ->fields(array(
      'age' => 1,
    ))
      ->execute();

    // Ensure that expressions are handled properly.  This should set every
    // record's age to a square of itself, which will change only three of the
    // four records in the table since 1*1 = 1. That means only three records
    // are modified, so we should get back 3, not 4, from execute().
    $num_rows = db_update('test')
      ->expression('age', 'age * age')
      ->execute();
    $this
      ->assertIdentical($num_rows, 3, 'Number of affected rows are returned.');
  }

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

}

/**
 * Tests for more complex update statements.
 */
class DatabaseUpdateComplexTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Update tests, Complex',
      'description' => 'Test the Update query builder, complex queries.',
      'group' => 'Database',
    );
  }

  /**
   * Test updates with OR conditionals.
   */
  function testOrConditionUpdate() {
    $update = db_update('test')
      ->fields(array(
      'job' => 'Musician',
    ))
      ->condition(db_or()
      ->condition('name', 'John')
      ->condition('name', 'Paul'));
    $num_updated = $update
      ->execute();
    $this
      ->assertIdentical($num_updated, 2, 'Updated 2 records.');
    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(
      ':job' => 'Musician',
    ))
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

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

  /**
   * Test WHERE NOT IN clauses.
   */
  function testNotInConditionUpdate() {

    // The o is lowercase in the 'NoT IN' operator, to make sure the operators
    // work in mixed case.
    $num_updated = db_update('test')
      ->fields(array(
      'job' => 'Musician',
    ))
      ->condition('name', array(
      'John',
      'Paul',
      'George',
    ), 'NoT IN')
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(
      ':job' => 'Musician',
    ))
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  }

  /**
   * Test BETWEEN conditional clauses.
   */
  function testBetweenConditionUpdate() {
    $num_updated = db_update('test')
      ->fields(array(
      'job' => 'Musician',
    ))
      ->condition('age', array(
      25,
      26,
    ), 'BETWEEN')
      ->execute();
    $this
      ->assertIdentical($num_updated, 2, 'Updated 2 records.');
    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(
      ':job' => 'Musician',
    ))
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '2', 'Updated fields successfully.');
  }

  /**
   * Test LIKE conditionals.
   */
  function testLikeConditionUpdate() {
    $num_updated = db_update('test')
      ->fields(array(
      'job' => 'Musician',
    ))
      ->condition('name', '%ge%', 'LIKE')
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(
      ':job' => 'Musician',
    ))
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '1', 'Updated fields successfully.');
  }

  /**
   * Test update with expression values.
   */
  function testUpdateExpression() {
    $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetchField();
    $GLOBALS['larry_test'] = 1;
    $num_updated = db_update('test')
      ->condition('name', 'Ringo')
      ->fields(array(
      'job' => 'Musician',
    ))
      ->expression('age', 'age + :age', array(
      ':age' => 4,
    ))
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', array(
      ':job' => 'Musician',
    ))
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '1', 'Updated fields successfully.');
    $person = db_query('SELECT * FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Ringo', 'Name set correctly.');
    $this
      ->assertEqual($person->age, $before_age + 4, 'Age set correctly.');
    $this
      ->assertEqual($person->job, 'Musician', 'Job set correctly.');
    $GLOBALS['larry_test'] = 0;
  }

  /**
   * Test update with only expression values.
   */
  function testUpdateOnlyExpression() {
    $before_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetchField();
    $num_updated = db_update('test')
      ->condition('name', 'Ringo')
      ->expression('age', 'age + :age', array(
      ':age' => 4,
    ))
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record.');
    $after_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetchField();
    $this
      ->assertEqual($before_age + 4, $after_age, 'Age updated correctly');
  }

}

/**
 * Test update queries involving LOB values.
 */
class DatabaseUpdateLOBTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Update tests, LOB',
      'description' => 'Test the Update query builder with LOB fields.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that we can update a blob column.
   */
  function testUpdateOneBlob() {
    $data = "This is\0a test.";
    $this
      ->assertTrue(strlen($data) === 15, 'Test data contains a NULL.');
    $id = db_insert('test_one_blob')
      ->fields(array(
      'blob1' => $data,
    ))
      ->execute();
    $data .= $data;
    db_update('test_one_blob')
      ->condition('id', $id)
      ->fields(array(
      'blob1' => $data,
    ))
      ->execute();
    $r = db_query('SELECT * FROM {test_one_blob} WHERE id = :id', array(
      ':id' => $id,
    ))
      ->fetchAssoc();
    $this
      ->assertTrue($r['blob1'] === $data, format_string('Can update a blob: id @id, @data.', array(
      '@id' => $id,
      '@data' => serialize($r),
    )));
  }

  /**
   * Confirm that we can update two blob columns in the same table.
   */
  function testUpdateMultipleBlob() {
    $id = db_insert('test_two_blobs')
      ->fields(array(
      'blob1' => 'This is',
      'blob2' => 'a test',
    ))
      ->execute();
    db_update('test_two_blobs')
      ->condition('id', $id)
      ->fields(array(
      'blob1' => 'and so',
      'blob2' => 'is this',
    ))
      ->execute();
    $r = db_query('SELECT * FROM {test_two_blobs} WHERE id = :id', array(
      ':id' => $id,
    ))
      ->fetchAssoc();
    $this
      ->assertTrue($r['blob1'] === 'and so' && $r['blob2'] === 'is this', 'Can update multiple blobs per row.');
  }

}

/**
 * Delete/Truncate tests.
 *
 * The DELETE tests are not as extensive, as all of the interesting code for
 * DELETE queries is in the conditional which is identical to the UPDATE and
 * SELECT conditional handling.
 *
 * The TRUNCATE tests are not extensive either, because the behavior of
 * TRUNCATE queries is not consistent across database engines. We only test
 * that a TRUNCATE query actually deletes all rows from the target table.
 */
class DatabaseDeleteTruncateTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Delete/Truncate tests',
      'description' => 'Test the Delete and Truncate query builders.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that we can use a subselect in a delete successfully.
   */
  function testSubselectDelete() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_task}')
      ->fetchField();
    $pid_to_delete = db_query("SELECT * FROM {test_task} WHERE task = 'sleep'")
      ->fetchField();
    $subquery = db_select('test', 't')
      ->fields('t', array(
      'id',
    ))
      ->condition('t.id', array(
      $pid_to_delete,
    ), 'IN');
    $delete = db_delete('test_task')
      ->condition('task', 'sleep')
      ->condition('pid', $subquery, 'IN');
    $num_deleted = $delete
      ->execute();
    $this
      ->assertEqual($num_deleted, 1, "Deleted 1 record.");
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_task}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  }

  /**
   * Confirm that we can delete a single record successfully.
   */
  function testSimpleDelete() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $num_deleted = db_delete('test')
      ->condition('id', 1)
      ->execute();
    $this
      ->assertIdentical($num_deleted, 1, 'Deleted 1 record.');
    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
  }

  /**
   * Confirm that we can truncate a whole table successfully.
   */
  function testTruncate() {
    $num_records_before = db_query("SELECT COUNT(*) FROM {test}")
      ->fetchField();
    db_truncate('test')
      ->execute();
    $num_records_after = db_query("SELECT COUNT(*) FROM {test}")
      ->fetchField();
    $this
      ->assertEqual(0, $num_records_after, 'Truncate really deletes everything.');
  }

}

/**
 * Test the MERGE query builder.
 */
class DatabaseMergeTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Merge tests',
      'description' => 'Test the Merge query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that we can merge-insert a record successfully.
   */
  function testMergeInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $result = db_merge('test_people')
      ->key(array(
      'job' => 'Presenter',
    ))
      ->fields(array(
      'age' => 31,
      'name' => 'Tiffany',
    ))
      ->execute();
    $this
      ->assertEqual($result, MergeQuery::STATUS_INSERT, 'Insert status returned.');
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Presenter',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this
      ->assertEqual($person->age, 31, 'Age set correctly.');
    $this
      ->assertEqual($person->job, 'Presenter', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully.
   */
  function testMergeUpdate() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $result = db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->fields(array(
      'age' => 31,
      'name' => 'Tiffany',
    ))
      ->execute();
    $this
      ->assertEqual($result, MergeQuery::STATUS_UPDATE, 'Update status returned.');
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this
      ->assertEqual($person->age, 31, 'Age set correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully, with different insert and update.
   */
  function testMergeUpdateExcept() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->insertFields(array(
      'age' => 31,
    ))
      ->updateFields(array(
      'name' => 'Tiffany',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this
      ->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update a record successfully, with alternate replacement.
   */
  function testMergeUpdateExplicit() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->insertFields(array(
      'age' => 31,
      'name' => 'Tiffany',
    ))
      ->updateFields(array(
      'name' => 'Joe',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Joe', 'Name set correctly.');
    $this
      ->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

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

    // This is a very contrived example, as I have no idea why you'd want to
    // change age this way, but that's beside the point.
    // Note that we are also double-setting age here, once as a literal and
    // once as an expression. This test will only pass if the expression wins,
    // which is what is supposed to happen.
    db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->fields(array(
      'name' => 'Tiffany',
    ))
      ->insertFields(array(
      'age' => 31,
    ))
      ->expression('age', 'age + :age', array(
      ':age' => 4,
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge updated properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Tiffany', 'Name set correctly.');
    $this
      ->assertEqual($person->age, $age_before + 4, 'Age updated correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job set correctly.');
  }

  /**
   * Test that we can merge-insert without any update fields.
   */
  function testMergeInsertWithoutUpdate() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    db_merge('test_people')
      ->key(array(
      'job' => 'Presenter',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Presenter',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, '', 'Name set correctly.');
    $this
      ->assertEqual($person->age, 0, 'Age set correctly.');
    $this
      ->assertEqual($person->job, 'Presenter', 'Job set correctly.');
  }

  /**
   * Confirm that we can merge-update without any update fields.
   */
  function testMergeUpdateWithoutUpdate() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
    $this
      ->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
    db_merge('test_people')
      ->key(array(
      'job' => 'Speaker',
    ))
      ->insertFields(array(
      'age' => 31,
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test_people}')
      ->fetchField();
    $this
      ->assertEqual($num_records_before, $num_records_after, 'Merge skipped properly.');
    $person = db_query('SELECT * FROM {test_people} WHERE job = :job', array(
      ':job' => 'Speaker',
    ))
      ->fetch();
    $this
      ->assertEqual($person->name, 'Meredith', 'Name skipped correctly.');
    $this
      ->assertEqual($person->age, 30, 'Age skipped correctly.');
    $this
      ->assertEqual($person->job, 'Speaker', 'Job skipped correctly.');
  }

  /**
   * Test that an invalid merge query throws an exception like it is supposed to.
   */
  function testInvalidMerge() {
    try {

      // This query should die because there is no key field specified.
      db_merge('test_people')
        ->fields(array(
        'age' => 31,
        'name' => 'Tiffany',
      ))
        ->execute();
    } catch (InvalidMergeQueryException $e) {
      $this
        ->pass('InvalidMergeQueryException thrown for invalid query.');
      return;
    }
    $this
      ->fail('No InvalidMergeQueryException thrown');
  }

}

/**
 * Test the SELECT builder.
 */
class DatabaseSelectTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests',
      'description' => 'Test the Select query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Test rudimentary SELECT statements.
   */
  function testSimpleSelect() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test rudimentary SELECT statement with a COMMENT.
   */
  function testSimpleComment() {
    $query = db_select('test')
      ->comment('Testing query comments');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $query = (string) $query;
    $expected = "/* Testing query comments */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this
      ->assertEqual($query, $expected, 'The flattened query contains the comment string.');
  }

  /**
   * Test query COMMENT system against vulnerabilities.
   */
  function testVulnerableComment() {
    $query = db_select('test')
      ->comment('Testing query comments */ SELECT nid FROM {node}; --');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $query = (string) $query;
    $expected = "/* Testing query comments  * / SELECT nid FROM {node}; -- */ SELECT test.name AS name, test.age AS age\nFROM \n{test} test";
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this
      ->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');
    $connection = Database::getConnection();
    foreach ($this
      ->makeCommentsProvider() as $test_set) {
      list($expected, $comments) = $test_set;
      $this
        ->assertEqual($expected, $connection
        ->makeComment($comments));
    }
  }

  /**
   * Provides expected and input values for testVulnerableComment().
   */
  function makeCommentsProvider() {
    return array(
      array(
        '/*  */ ',
        array(
          '',
        ),
      ),
      // Try and close the comment early.
      array(
        '/* Exploit  * / DROP TABLE node; -- */ ',
        array(
          'Exploit */ DROP TABLE node; --',
        ),
      ),
      // Variations on comment closing.
      array(
        '/* Exploit  * / * / DROP TABLE node; -- */ ',
        array(
          'Exploit */*/ DROP TABLE node; --',
        ),
      ),
      array(
        '/* Exploit  *  * // DROP TABLE node; -- */ ',
        array(
          'Exploit **// DROP TABLE node; --',
        ),
      ),
      // Try closing the comment in the second string which is appended.
      array(
        '/* Exploit  * / DROP TABLE node; --; Another try  * / DROP TABLE node; -- */ ',
        array(
          'Exploit */ DROP TABLE node; --',
          'Another try */ DROP TABLE node; --',
        ),
      ),
    );
  }

  /**
   * Test basic conditionals on SELECT statements.
   */
  function testSimpleSelectConditional() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->condition('age', 27);
    $result = $query
      ->execute();

    // Check that the aliases are being created the way we want.
    $this
      ->assertEqual($name_field, 'name', 'Name field alias is correct.');
    $this
      ->assertEqual($age_field, 'age', 'Age field alias is correct.');

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Fetched name is correct.');
    $this
      ->assertEqual($record->{$age_field}, 27, 'Fetched age is correct.');
  }

  /**
   * Test SELECT statements with expressions.
   */
  function testSimpleSelectExpression() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addExpression("age*2", 'double_age');
    $query
      ->condition('age', 27);
    $result = $query
      ->execute();

    // Check that the aliases are being created the way we want.
    $this
      ->assertEqual($name_field, 'name', 'Name field alias is correct.');
    $this
      ->assertEqual($age_field, 'double_age', 'Age field alias is correct.');

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Fetched name is correct.');
    $this
      ->assertEqual($record->{$age_field}, 27 * 2, 'Fetched age expression is correct.');
  }

  /**
   * Test SELECT statements with multiple expressions.
   */
  function testSimpleSelectExpressionMultiple() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_double_field = $query
      ->addExpression("age*2");
    $age_triple_field = $query
      ->addExpression("age*3");
    $query
      ->condition('age', 27);
    $result = $query
      ->execute();

    // Check that the aliases are being created the way we want.
    $this
      ->assertEqual($age_double_field, 'expression', 'Double age field alias is correct.');
    $this
      ->assertEqual($age_triple_field, 'expression_2', 'Triple age field alias is correct.');

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Fetched name is correct.');
    $this
      ->assertEqual($record->{$age_double_field}, 27 * 2, 'Fetched double age expression is correct.');
    $this
      ->assertEqual($record->{$age_triple_field}, 27 * 3, 'Fetched triple age expression is correct.');
  }

  /**
   * Test adding multiple fields to a select statement at the same time.
   */
  function testSimpleSelectMultipleFields() {
    $record = db_select('test')
      ->fields('test', array(
      'id',
      'name',
      'age',
      'job',
    ))
      ->condition('age', 27)
      ->execute()
      ->fetchObject();

    // Check that all fields we asked for are present.
    $this
      ->assertNotNull($record->id, 'ID field is present.');
    $this
      ->assertNotNull($record->name, 'Name field is present.');
    $this
      ->assertNotNull($record->age, 'Age field is present.');
    $this
      ->assertNotNull($record->job, 'Job field is present.');

    // Ensure that we got the right record.
    // Check that all fields we asked for are present.
    $this
      ->assertEqual($record->id, 2, 'ID field has the correct value.');
    $this
      ->assertEqual($record->name, 'George', 'Name field has the correct value.');
    $this
      ->assertEqual($record->age, 27, 'Age field has the correct value.');
    $this
      ->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
  }

  /**
   * Test adding all fields from a given table to a select statement.
   */
  function testSimpleSelectAllFields() {
    $record = db_select('test')
      ->fields('test')
      ->condition('age', 27)
      ->execute()
      ->fetchObject();

    // Check that all fields we asked for are present.
    $this
      ->assertNotNull($record->id, 'ID field is present.');
    $this
      ->assertNotNull($record->name, 'Name field is present.');
    $this
      ->assertNotNull($record->age, 'Age field is present.');
    $this
      ->assertNotNull($record->job, 'Job field is present.');

    // Ensure that we got the right record.
    // Check that all fields we asked for are present.
    $this
      ->assertEqual($record->id, 2, 'ID field has the correct value.');
    $this
      ->assertEqual($record->name, 'George', 'Name field has the correct value.');
    $this
      ->assertEqual($record->age, 27, 'Age field has the correct value.');
    $this
      ->assertEqual($record->job, 'Singer', 'Job field has the correct value.');
  }

  /**
   * Test that we can find a record with a NULL value.
   */
  function testNullCondition() {
    $this
      ->ensureSampleDataNull();
    $names = db_select('test_null', 'tn')
      ->fields('tn', array(
      'name',
    ))
      ->isNull('age')
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
    $this
      ->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
  }

  /**
   * Test that we can find a record without a NULL value.
   */
  function testNotNullCondition() {
    $this
      ->ensureSampleDataNull();
    $names = db_select('test_null', 'tn')
      ->fields('tn', array(
      'name',
    ))
      ->isNotNull('tn.age')
      ->orderBy('name')
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($names), 2, 'Correct number of records found withNOT NULL age.');
    $this
      ->assertEqual($names[0], 'Gonzo', 'Correct record returned for NOT NULL age.');
    $this
      ->assertEqual($names[1], 'Kermit', 'Correct record returned for NOT NULL age.');
  }

  /**
   * Test that we can UNION multiple Select queries together. This is
   * semantically equal to UNION DISTINCT, so we don't explicity test that.
   */
  function testUnion() {
    $query_1 = db_select('test', 't')
      ->fields('t', array(
      'name',
    ))
      ->condition('age', array(
      27,
      28,
    ), 'IN');
    $query_2 = db_select('test', 't')
      ->fields('t', array(
      'name',
    ))
      ->condition('age', 28);
    $query_1
      ->union($query_2);
    $names = $query_1
      ->execute()
      ->fetchCol();

    // Ensure we only get 2 records.
    $this
      ->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
    $this
      ->assertEqual($names[0], 'George', 'First query returned correct name.');
    $this
      ->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
  }

  /**
   * Test that we can UNION ALL multiple Select queries together.
   */
  function testUnionAll() {
    $query_1 = db_select('test', 't')
      ->fields('t', array(
      'name',
    ))
      ->condition('age', array(
      27,
      28,
    ), 'IN');
    $query_2 = db_select('test', 't')
      ->fields('t', array(
      'name',
    ))
      ->condition('age', 28);
    $query_1
      ->union($query_2, 'ALL');
    $names = $query_1
      ->execute()
      ->fetchCol();

    // Ensure we get all 3 records.
    $this
      ->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
    $this
      ->assertEqual($names[0], 'George', 'First query returned correct first name.');
    $this
      ->assertEqual($names[1], 'Ringo', 'Second query returned correct second name.');
    $this
      ->assertEqual($names[2], 'Ringo', 'Third query returned correct name.');
  }

  /**
   * Test that random ordering of queries works.
   *
   * We take the approach of testing the Drupal layer only, rather than trying
   * to test that the database's random number generator actually produces
   * random queries (which is very difficult to do without an unacceptable risk
   * of the test failing by accident).
   *
   * Therefore, in this test we simply run the same query twice and assert that
   * the two results are reordered versions of each other (as well as of the
   * same query without the random ordering). It is reasonable to assume that
   * if we run the same select query twice and the results are in a different
   * order each time, the only way this could happen is if we have successfully
   * triggered the database's random ordering functionality.
   */
  function testRandomOrder() {

    // Use 52 items, so the chance that this test fails by accident will be the
    // same as the chance that a deck of cards will come out in the same order
    // after shuffling it (in other words, nearly impossible).
    $number_of_items = 52;
    while (db_query("SELECT MAX(id) FROM {test}")
      ->fetchField() < $number_of_items) {
      db_insert('test')
        ->fields(array(
        'name' => $this
          ->randomName(),
      ))
        ->execute();
    }

    // First select the items in order and make sure we get an ordered list.
    $expected_ids = range(1, $number_of_items);
    $ordered_ids = db_select('test', 't')
      ->fields('t', array(
      'id',
    ))
      ->range(0, $number_of_items)
      ->orderBy('id')
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual($ordered_ids, $expected_ids, 'A query without random ordering returns IDs in the correct order.');

    // Now perform the same query, but instead choose a random ordering. We
    // expect this to contain a differently ordered version of the original
    // result.
    $randomized_ids = db_select('test', 't')
      ->fields('t', array(
      'id',
    ))
      ->range(0, $number_of_items)
      ->orderRandom()
      ->execute()
      ->fetchCol();
    $this
      ->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
    $sorted_ids = $randomized_ids;
    sort($sorted_ids);
    $this
      ->assertEqual($sorted_ids, $ordered_ids, 'After sorting the random list, the result matches the original query.');

    // Now perform the exact same query again, and make sure the order is
    // different.
    $randomized_ids_second_set = db_select('test', 't')
      ->fields('t', array(
      'id',
    ))
      ->range(0, $number_of_items)
      ->orderRandom()
      ->execute()
      ->fetchCol();
    $this
      ->assertNotEqual($randomized_ids_second_set, $randomized_ids, 'Performing the query with random ordering a second time returns IDs in a different order.');
    $sorted_ids_second_set = $randomized_ids_second_set;
    sort($sorted_ids_second_set);
    $this
      ->assertEqual($sorted_ids_second_set, $sorted_ids, 'After sorting the second random list, the result matches the sorted version of the first random list.');
  }

  /**
   * Test that aliases are renamed when duplicates.
   */
  function testSelectDuplicateAlias() {
    $query = db_select('test', 't');
    $alias1 = $query
      ->addField('t', 'name', 'the_alias');
    $alias2 = $query
      ->addField('t', 'age', 'the_alias');
    $this
      ->assertNotIdentical($alias1, $alias2, 'Duplicate aliases are renamed.');
  }

}

/**
 * Test case for subselects in a dynamic SELECT query.
 */
class DatabaseSelectSubqueryTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests, subqueries',
      'description' => 'Test the Select query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Test that we can use a subquery in a FROM clause.
   */
  function testFromSubquerySelect() {

    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery
      ->addField('tt', 'pid', 'pid');
    $subquery
      ->addField('tt', 'task', 'task');
    $subquery
      ->condition('priority', 1);
    for ($i = 0; $i < 2; $i++) {

      // Create another query that joins against the virtual table resulting
      // from the subquery.
      $select = db_select($subquery, 'tt2');
      $select
        ->join('test', 't', 't.id=tt2.pid');
      $select
        ->addField('t', 'name');
      if ($i) {

        // Use a different number of conditions here to confuse the subquery
        // placeholder counter, testing http://drupal.org/node/1112854.
        $select
          ->condition('name', 'John');
      }
      $select
        ->condition('task', 'code');

      // The resulting query should be equivalent to:
      // SELECT t.name
      // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt WHERE priority=1) tt
      //   INNER JOIN test t ON t.id=tt.pid
      // WHERE tt.task = 'code'
      $people = $select
        ->execute()
        ->fetchCol();
      $this
        ->assertEqual(count($people), 1, 'Returned the correct number of rows.');
    }
  }

  /**
   * Test that we can use a subquery in a FROM clause with a limit.
   */
  function testFromSubquerySelectWithLimit() {

    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery
      ->addField('tt', 'pid', 'pid');
    $subquery
      ->addField('tt', 'task', 'task');
    $subquery
      ->orderBy('priority', 'DESC');
    $subquery
      ->range(0, 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select($subquery, 'tt2');
    $select
      ->join('test', 't', 't.id=tt2.pid');
    $select
      ->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt ORDER BY priority DESC LIMIT 1 OFFSET 0) tt
    //   INNER JOIN test t ON t.id=tt.pid
    $people = $select
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($people), 1, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can use a subquery in a WHERE clause.
   */
  function testConditionSubquerySelect() {

    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery
      ->addField('tt', 'pid', 'pid');
    $subquery
      ->condition('tt.priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test_task', 'tt2');
    $select
      ->addField('tt2', 'task');
    $select
      ->condition('tt2.pid', $subquery, 'IN');

    // The resulting query should be equivalent to:
    // SELECT tt2.name
    // FROM test tt2
    // WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1)
    $people = $select
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($people), 5, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can use a subquery in a JOIN clause.
   */
  function testJoinSubquerySelect() {

    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery
      ->addField('tt', 'pid', 'pid');
    $subquery
      ->condition('priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test', 't');
    $select
      ->join($subquery, 'tt', 't.id=tt.pid');
    $select
      ->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    //   INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
    $people = $select
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($people), 2, 'Returned the correct number of rows.');
  }

  /**
   * Test EXISTS subquery conditionals on SELECT statements.
   *
   * We essentially select all rows from the {test} table that have matching
   * rows in the {test_people} table based on the shared name column.
   */
  function testExistsSubquerySelect() {

    // Put George into {test_people}.
    db_insert('test_people')
      ->fields(array(
      'name' => 'George',
      'age' => 27,
      'job' => 'Singer',
    ))
      ->execute();

    // Base query to {test}.
    $query = db_select('test', 't')
      ->fields('t', array(
      'name',
    ));

    // Subquery to {test_people}.
    $subquery = db_select('test_people', 'tp')
      ->fields('tp', array(
      'name',
    ))
      ->where('tp.name = t.name');
    $query
      ->exists($subquery);
    $result = $query
      ->execute();

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->name, 'George', 'Fetched name is correct using EXISTS query.');
  }

  /**
   * Test NOT EXISTS subquery conditionals on SELECT statements.
   *
   * We essentially select all rows from the {test} table that don't have
   * matching rows in the {test_people} table based on the shared name column.
   */
  function testNotExistsSubquerySelect() {

    // Put George into {test_people}.
    db_insert('test_people')
      ->fields(array(
      'name' => 'George',
      'age' => 27,
      'job' => 'Singer',
    ))
      ->execute();

    // Base query to {test}.
    $query = db_select('test', 't')
      ->fields('t', array(
      'name',
    ));

    // Subquery to {test_people}.
    $subquery = db_select('test_people', 'tp')
      ->fields('tp', array(
      'name',
    ))
      ->where('tp.name = t.name');
    $query
      ->notExists($subquery);

    // Ensure that we got the right number of records.
    $people = $query
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual(count($people), 3, 'NOT EXISTS query returned the correct results.');
  }

}

/**
 * Test select with order by clauses.
 */
class DatabaseSelectOrderedTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests, ordered',
      'description' => 'Test the Select query builder.',
      'group' => 'Database',
    );
  }

  /**
   * Test basic order by.
   */
  function testSimpleSelectOrdered() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->orderBy($age_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $last_age = 0;
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->age >= $last_age, 'Results returned in correct order.');
      $last_age = $record->age;
    }
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test multiple order by.
   */
  function testSimpleSelectMultiOrdered() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $job_field = $query
      ->addField('test', 'job');
    $query
      ->orderBy($job_field);
    $query
      ->orderBy($age_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $expected = array(
      array(
        'Ringo',
        28,
        'Drummer',
      ),
      array(
        'John',
        25,
        'Singer',
      ),
      array(
        'George',
        27,
        'Singer',
      ),
      array(
        'Paul',
        26,
        'Songwriter',
      ),
    );
    $results = $result
      ->fetchAll(PDO::FETCH_NUM);
    foreach ($expected as $k => $record) {
      $num_records++;
      foreach ($record as $kk => $col) {
        if ($expected[$k][$kk] != $results[$k][$kk]) {
          $this
            ->assertTrue(FALSE, 'Results returned in correct order.');
        }
      }
    }
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test order by descending.
   */
  function testSimpleSelectOrderedDesc() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->orderBy($age_field, 'DESC');
    $result = $query
      ->execute();
    $num_records = 0;
    $last_age = 100000000;
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->age <= $last_age, 'Results returned in correct order.');
      $last_age = $record->age;
    }
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

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

}

/**
 * Test more complex select statements.
 */
class DatabaseSelectComplexTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests, complex',
      'description' => 'Test the Select query builder with more complex queries.',
      'group' => 'Database',
    );
  }

  /**
   * Test simple JOIN statements.
   */
  function testDefaultJoin() {
    $query = db_select('test_task', 't');
    $people_alias = $query
      ->join('test', 'p', 't.pid = p.id');
    $name_field = $query
      ->addField($people_alias, 'name', 'name');
    $task_field = $query
      ->addField('t', 'task', 'task');
    $priority_field = $query
      ->addField('t', 'priority', 'priority');
    $query
      ->orderBy($priority_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $last_priority = 0;
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->{$priority_field} >= $last_priority, 'Results returned in correct order.');
      $this
        ->assertNotEqual($record->{$name_field}, 'Ringo', 'Taskless person not selected.');
      $last_priority = $record->{$priority_field};
    }
    $this
      ->assertEqual($num_records, 7, 'Returned the correct number of rows.');
  }

  /**
   * Test LEFT OUTER joins.
   */
  function testLeftOuterJoin() {
    $query = db_select('test', 'p');
    $people_alias = $query
      ->leftJoin('test_task', 't', 't.pid = p.id');
    $name_field = $query
      ->addField('p', 'name', 'name');
    $task_field = $query
      ->addField($people_alias, 'task', 'task');
    $priority_field = $query
      ->addField($people_alias, 'priority', 'priority');
    $query
      ->orderBy($name_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $last_name = 0;
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue(strcmp($record->{$name_field}, $last_name) >= 0, 'Results returned in correct order.');
      $last_priority = $record->{$name_field};
    }
    $this
      ->assertEqual($num_records, 8, 'Returned the correct number of rows.');
  }

  /**
   * Test GROUP BY clauses.
   */
  function testGroupBy() {
    $query = db_select('test_task', 't');
    $count_field = $query
      ->addExpression('COUNT(task)', 'num');
    $task_field = $query
      ->addField('t', 'task');
    $query
      ->orderBy($count_field);
    $query
      ->groupBy($task_field);
    $result = $query
      ->execute();
    $num_records = 0;
    $last_count = 0;
    $records = array();
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->{$count_field} >= $last_count, 'Results returned in correct order.');
      $last_count = $record->{$count_field};
      $records[$record->{$task_field}] = $record->{$count_field};
    }
    $correct_results = array(
      'eat' => 1,
      'sleep' => 2,
      'code' => 1,
      'found new band' => 1,
      'perform at superbowl' => 1,
    );
    foreach ($correct_results as $task => $count) {
      $this
        ->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array(
        '@task' => $task,
      )));
    }
    $this
      ->assertEqual($num_records, 6, 'Returned the correct number of total rows.');
  }

  /**
   * Test GROUP BY and HAVING clauses together.
   */
  function testGroupByAndHaving() {
    $query = db_select('test_task', 't');
    $count_field = $query
      ->addExpression('COUNT(task)', 'num');
    $task_field = $query
      ->addField('t', 'task');
    $query
      ->orderBy($count_field);
    $query
      ->groupBy($task_field);
    $query
      ->having('COUNT(task) >= 2');
    $result = $query
      ->execute();
    $num_records = 0;
    $last_count = 0;
    $records = array();
    foreach ($result as $record) {
      $num_records++;
      $this
        ->assertTrue($record->{$count_field} >= 2, 'Record has the minimum count.');
      $this
        ->assertTrue($record->{$count_field} >= $last_count, 'Results returned in correct order.');
      $last_count = $record->{$count_field};
      $records[$record->{$task_field}] = $record->{$count_field};
    }
    $correct_results = array(
      'sleep' => 2,
    );
    foreach ($correct_results as $task => $count) {
      $this
        ->assertEqual($records[$task], $count, format_string("Correct number of '@task' records found.", array(
        '@task' => $task,
      )));
    }
    $this
      ->assertEqual($num_records, 1, 'Returned the correct number of total rows.');
  }

  /**
   * Test range queries. The SQL clause varies with the database.
   */
  function testRange() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->range(0, 2);
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this
      ->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  }

  /**
   * Test distinct queries.
   */
  function testDistinct() {
    $query = db_select('test_task');
    $task_field = $query
      ->addField('test_task', 'task');
    $query
      ->distinct();
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this
      ->assertEqual($num_records, 6, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can generate a count query from a built query.
   */
  function testCountQuery() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->orderBy('name');
    $count = $query
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($count, 4, 'Counted the correct number of records.');

    // Now make sure we didn't break the original query!  We should still have
    // all of the fields we asked for.
    $record = $query
      ->execute()
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Correct data retrieved.');
    $this
      ->assertEqual($record->{$age_field}, 27, 'Correct data retrieved.');
  }
  function testHavingCountQuery() {
    $query = db_select('test')
      ->extend('PagerDefault')
      ->groupBy('age')
      ->having('age + 1 > 0');
    $query
      ->addField('test', 'age');
    $query
      ->addExpression('age + 1');
    $count = count($query
      ->execute()
      ->fetchCol());
    $this
      ->assertEqual($count, 4, 'Counted the correct number of records.');
  }

  /**
   * Test that countQuery properly removes 'all_fields' statements and
   * ordering clauses.
   */
  function testCountQueryRemovals() {
    $query = db_select('test');
    $query
      ->fields('test');
    $query
      ->orderBy('name');
    $count = $query
      ->countQuery();

    // Check that the 'all_fields' statement is handled properly.
    $tables = $query
      ->getTables();
    $this
      ->assertEqual($tables['test']['all_fields'], 1, 'Query correctly sets \'all_fields\' statement.');
    $tables = $count
      ->getTables();
    $this
      ->assertFalse(isset($tables['test']['all_fields']), 'Count query correctly unsets \'all_fields\' statement.');

    // Check that the ordering clause is handled properly.
    $orderby = $query
      ->getOrderBy();
    $this
      ->assertEqual($orderby['name'], 'ASC', 'Query correctly sets ordering clause.');
    $orderby = $count
      ->getOrderBy();
    $this
      ->assertFalse(isset($orderby['name']), 'Count query correctly unsets ordering caluse.');

    // Make sure that the count query works.
    $count = $count
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($count, 4, 'Counted the correct number of records.');
  }

  /**
   * Test that countQuery properly removes fields and expressions.
   */
  function testCountQueryFieldRemovals() {

    // countQuery should remove all fields and expressions, so this can be
    // tested by adding a non-existent field and expression: if it ends
    // up in the query, an error will be thrown. If not, it will return the
    // number of records, which in this case happens to be 4 (there are four
    // records in the {test} table).
    $query = db_select('test');
    $query
      ->fields('test', array(
      'fail',
    ));
    $this
      ->assertEqual(4, $query
      ->countQuery()
      ->execute()
      ->fetchField(), 'Count Query removed fields');
    $query = db_select('test');
    $query
      ->addExpression('fail');
    $this
      ->assertEqual(4, $query
      ->countQuery()
      ->execute()
      ->fetchField(), 'Count Query removed expressions');
  }

  /**
   * Test that we can generate a count query from a query with distinct.
   */
  function testCountQueryDistinct() {
    $query = db_select('test_task');
    $task_field = $query
      ->addField('test_task', 'task');
    $query
      ->distinct();
    $count = $query
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($count, 6, 'Counted the correct number of records.');
  }

  /**
   * Test that we can generate a count query from a query with GROUP BY.
   */
  function testCountQueryGroupBy() {
    $query = db_select('test_task');
    $pid_field = $query
      ->addField('test_task', 'pid');
    $query
      ->groupBy('pid');
    $count = $query
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($count, 3, 'Counted the correct number of records.');

    // Use a column alias as, without one, the query can succeed for the wrong
    // reason.
    $query = db_select('test_task');
    $pid_field = $query
      ->addField('test_task', 'pid', 'pid_alias');
    $query
      ->addExpression('COUNT(test_task.task)', 'count');
    $query
      ->groupBy('pid_alias');
    $query
      ->orderBy('pid_alias', 'asc');
    $count = $query
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($count, 3, 'Counted the correct number of records.');
  }

  /**
   * Confirm that we can properly nest conditional clauses.
   */
  function testNestedConditions() {

    // This query should translate to:
    // "SELECT job FROM {test} WHERE name = 'Paul' AND (age = 26 OR age = 27)"
    // That should find only one record. Yes it's a non-optimal way of writing
    // that query but that's not the point!
    $query = db_select('test');
    $query
      ->addField('test', 'job');
    $query
      ->condition('name', 'Paul');
    $query
      ->condition(db_or()
      ->condition('age', 26)
      ->condition('age', 27));
    $job = $query
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($job, 'Songwriter', 'Correct data retrieved.');
  }

  /**
   * Confirm we can join on a single table twice with a dynamic alias.
   */
  function testJoinTwice() {
    $query = db_select('test')
      ->fields('test');
    $alias = $query
      ->join('test', 'test', 'test.job = %alias.job');
    $query
      ->addField($alias, 'name', 'othername');
    $query
      ->addField($alias, 'job', 'otherjob');
    $query
      ->where("{$alias}.name <> test.name");
    $crowded_job = $query
      ->execute()
      ->fetch();
    $this
      ->assertEqual($crowded_job->job, $crowded_job->otherjob, 'Correctly joined same table twice.');
    $this
      ->assertNotEqual($crowded_job->name, $crowded_job->othername, 'Correctly joined same table twice.');
  }

}

/**
 * Test more complex select statements, part 2.
 */
class DatabaseSelectComplexTestCase2 extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Select tests, complex 2',
      'description' => 'Test the Select query builder with even more complex queries.',
      'group' => 'Database',
    );
  }
  function setUp() {
    DrupalWebTestCase::setUp('database_test', 'node_access_test');
    $schema['test'] = drupal_get_schema('test');
    $schema['test_people'] = drupal_get_schema('test_people');
    $schema['test_one_blob'] = drupal_get_schema('test_one_blob');
    $schema['test_two_blobs'] = drupal_get_schema('test_two_blobs');
    $schema['test_task'] = drupal_get_schema('test_task');
    $this
      ->installTables($schema);
    $this
      ->addSampleData();
  }

  /**
   * Test that we can join on a query.
   */
  function testJoinSubquery() {
    $acct = $this
      ->drupalCreateUser(array(
      'access content',
    ));
    $this
      ->drupalLogin($acct);
    $query = db_select('test_task', 'tt', array(
      'target' => 'slave',
    ));
    $query
      ->addExpression('tt.pid + 1', 'abc');
    $query
      ->condition('priority', 1, '>');
    $query
      ->condition('priority', 100, '<');
    $subquery = db_select('test', 'tp');
    $subquery
      ->join('test_one_blob', 'tpb', 'tp.id = tpb.id');
    $subquery
      ->join('node', 'n', 'tp.id = n.nid');
    $subquery
      ->addTag('node_access');
    $subquery
      ->addMetaData('account', $acct);
    $subquery
      ->addField('tp', 'id');
    $subquery
      ->condition('age', 5, '>');
    $subquery
      ->condition('age', 500, '<');
    $query
      ->leftJoin($subquery, 'sq', 'tt.pid = sq.id');
    $query
      ->join('test_one_blob', 'tb3', 'tt.pid = tb3.id');

    // Construct the query string.
    // This is the same sequence that SelectQuery::execute() goes through.
    $query
      ->preExecute();
    $query
      ->getArguments();
    $str = (string) $query;

    // Verify that the string only has one copy of condition placeholder 0.
    $pos = strpos($str, 'db_condition_placeholder_0', 0);
    $pos2 = strpos($str, 'db_condition_placeholder_0', $pos + 1);
    $this
      ->assertFalse($pos2, 'Condition placeholder is not repeated.');
  }

}
class DatabaseSelectPagerDefaultTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Pager query tests',
      'description' => 'Test the pager query extender.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that a pager query returns the correct results.
   *
   * Note that we have to make an HTTP request to a test page handler
   * because the pager depends on GET parameters.
   */
  function testEvenPagerQuery() {

    // To keep the test from being too brittle, we determine up front
    // what the page count should be dynamically, and pass the control
    // information forward to the actual query on the other side of the
    // HTTP request.
    $limit = 2;
    $count = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $correct_number = $limit;
    $num_pages = floor($count / $limit);

    // If there is no remainder from rounding, subtract 1 since we index from 0.
    if (!($num_pages * $limit < $count)) {
      $num_pages--;
    }
    for ($page = 0; $page <= $num_pages; ++$page) {
      $this
        ->drupalGet('database_test/pager_query_even/' . $limit, array(
        'query' => array(
          'page' => $page,
        ),
      ));
      $data = json_decode($this
        ->drupalGetContent());
      if ($page == $num_pages) {
        $correct_number = $count - $limit * $page;
      }
      $this
        ->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array(
        '@number' => $correct_number,
      )));
    }
  }

  /**
   * Confirm that a pager query returns the correct results.
   *
   * Note that we have to make an HTTP request to a test page handler
   * because the pager depends on GET parameters.
   */
  function testOddPagerQuery() {

    // To keep the test from being too brittle, we determine up front
    // what the page count should be dynamically, and pass the control
    // information forward to the actual query on the other side of the
    // HTTP request.
    $limit = 2;
    $count = db_query('SELECT COUNT(*) FROM {test_task}')
      ->fetchField();
    $correct_number = $limit;
    $num_pages = floor($count / $limit);

    // If there is no remainder from rounding, subtract 1 since we index from 0.
    if (!($num_pages * $limit < $count)) {
      $num_pages--;
    }
    for ($page = 0; $page <= $num_pages; ++$page) {
      $this
        ->drupalGet('database_test/pager_query_odd/' . $limit, array(
        'query' => array(
          'page' => $page,
        ),
      ));
      $data = json_decode($this
        ->drupalGetContent());
      if ($page == $num_pages) {
        $correct_number = $count - $limit * $page;
      }
      $this
        ->assertEqual(count($data->names), $correct_number, format_string('Correct number of records returned by pager: @number', array(
        '@number' => $correct_number,
      )));
    }
  }

  /**
   * Confirm that a pager query with inner pager query returns valid results.
   *
   * This is a regression test for #467984.
   */
  function testInnerPagerQuery() {
    $query = db_select('test', 't')
      ->extend('PagerDefault');
    $query
      ->fields('t', array(
      'age',
    ))
      ->orderBy('age')
      ->limit(5);
    $outer_query = db_select($query);
    $outer_query
      ->addField('subquery', 'age');
    $ages = $outer_query
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual($ages, array(
      25,
      26,
      27,
      28,
    ), 'Inner pager query returned the correct ages.');
  }

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

  /**
   * Confirm that every pager gets a valid non-overlaping element ID.
   */
  function testElementNumbers() {
    $_GET['page'] = '3, 2, 1, 0';
    $name = db_select('test', 't')
      ->extend('PagerDefault')
      ->element(2)
      ->fields('t', array(
      'name',
    ))
      ->orderBy('age')
      ->limit(1)
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($name, 'Paul', 'Pager query #1 with a specified element ID returned the correct results.');

    // Setting an element smaller than the previous one
    // should not overwrite the pager $maxElement with a smaller value.
    $name = db_select('test', 't')
      ->extend('PagerDefault')
      ->element(1)
      ->fields('t', array(
      'name',
    ))
      ->orderBy('age')
      ->limit(1)
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($name, 'George', 'Pager query #2 with a specified element ID returned the correct results.');
    $name = db_select('test', 't')
      ->extend('PagerDefault')
      ->fields('t', array(
      'name',
    ))
      ->orderBy('age')
      ->limit(1)
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($name, 'John', 'Pager query #3 with a generated element ID returned the correct results.');
    unset($_GET['page']);
  }

}
class DatabaseSelectTableSortDefaultTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Tablesort query tests',
      'description' => 'Test the tablesort query extender.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that a tablesort query returns the correct results.
   *
   * Note that we have to make an HTTP request to a test page handler
   * because the pager depends on GET parameters.
   */
  function testTableSortQuery() {
    $sorts = array(
      array(
        'field' => t('Task ID'),
        'sort' => 'desc',
        'first' => 'perform at superbowl',
        'last' => 'eat',
      ),
      array(
        'field' => t('Task ID'),
        'sort' => 'asc',
        'first' => 'eat',
        'last' => 'perform at superbowl',
      ),
      array(
        'field' => t('Task'),
        'sort' => 'asc',
        'first' => 'code',
        'last' => 'sleep',
      ),
      array(
        'field' => t('Task'),
        'sort' => 'desc',
        'first' => 'sleep',
        'last' => 'code',
      ),
    );
    foreach ($sorts as $sort) {
      $this
        ->drupalGet('database_test/tablesort/', array(
        'query' => array(
          'order' => $sort['field'],
          'sort' => $sort['sort'],
        ),
      ));
      $data = json_decode($this
        ->drupalGetContent());
      $first = array_shift($data->tasks);
      $last = array_pop($data->tasks);
      $this
        ->assertEqual($first->task, $sort['first'], 'Items appear in the correct order.');
      $this
        ->assertEqual($last->task, $sort['last'], 'Items appear in the correct order.');
    }
  }

  /**
   * Confirm that if a tablesort's orderByHeader is called before another orderBy, that the header happens first.
   *
   */
  function testTableSortQueryFirst() {
    $sorts = array(
      array(
        'field' => t('Task ID'),
        'sort' => 'desc',
        'first' => 'perform at superbowl',
        'last' => 'eat',
      ),
      array(
        'field' => t('Task ID'),
        'sort' => 'asc',
        'first' => 'eat',
        'last' => 'perform at superbowl',
      ),
      array(
        'field' => t('Task'),
        'sort' => 'asc',
        'first' => 'code',
        'last' => 'sleep',
      ),
      array(
        'field' => t('Task'),
        'sort' => 'desc',
        'first' => 'sleep',
        'last' => 'code',
      ),
    );
    foreach ($sorts as $sort) {
      $this
        ->drupalGet('database_test/tablesort_first/', array(
        'query' => array(
          'order' => $sort['field'],
          'sort' => $sort['sort'],
        ),
      ));
      $data = json_decode($this
        ->drupalGetContent());
      $first = array_shift($data->tasks);
      $last = array_pop($data->tasks);
      $this
        ->assertEqual($first->task, $sort['first'], format_string('Items appear in the correct order sorting by @field @sort.', array(
        '@field' => $sort['field'],
        '@sort' => $sort['sort'],
      )));
      $this
        ->assertEqual($last->task, $sort['last'], format_string('Items appear in the correct order sorting by @field @sort.', array(
        '@field' => $sort['field'],
        '@sort' => $sort['sort'],
      )));
    }
  }

  /**
   * Confirm that if a sort is not set in a tableselect form there is no error thrown when using the default.
   */
  function testTableSortDefaultSort() {
    $this
      ->drupalGet('database_test/tablesort_default_sort');

    // Any PHP errors or notices thrown would trigger a simpletest exception, so
    // no additional assertions are needed.
  }

}

/**
 * Select tagging tests.
 *
 * Tags are a way to flag queries for alter hooks so they know
 * what type of query it is, such as "node_access".
 */
class DatabaseTaggingTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Query tagging tests',
      'description' => 'Test the tagging capabilities of the Select builder.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that a query has a "tag" added to it.
   */
  function testHasTag() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $this
      ->assertTrue($query
      ->hasTag('test'), 'hasTag() returned true.');
    $this
      ->assertFalse($query
      ->hasTag('other'), 'hasTag() returned false.');
  }

  /**
   * Test query tagging "has all of these tags" functionality.
   */
  function testHasAllTags() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $query
      ->addTag('other');
    $this
      ->assertTrue($query
      ->hasAllTags('test', 'other'), 'hasAllTags() returned true.');
    $this
      ->assertFalse($query
      ->hasAllTags('test', 'stuff'), 'hasAllTags() returned false.');
  }

  /**
   * Test query tagging "has at least one of these tags" functionality.
   */
  function testHasAnyTag() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $this
      ->assertTrue($query
      ->hasAnyTag('test', 'other'), 'hasAnyTag() returned true.');
    $this
      ->assertFalse($query
      ->hasAnyTag('other', 'stuff'), 'hasAnyTag() returned false.');
  }

  /**
   * Confirm that an extended query has a "tag" added to it.
   */
  function testExtenderHasTag() {
    $query = db_select('test')
      ->extend('SelectQueryExtender');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $this
      ->assertTrue($query
      ->hasTag('test'), 'hasTag() returned true.');
    $this
      ->assertFalse($query
      ->hasTag('other'), 'hasTag() returned false.');
  }

  /**
   * Test extended query tagging "has all of these tags" functionality.
   */
  function testExtenderHasAllTags() {
    $query = db_select('test')
      ->extend('SelectQueryExtender');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $query
      ->addTag('other');
    $this
      ->assertTrue($query
      ->hasAllTags('test', 'other'), 'hasAllTags() returned true.');
    $this
      ->assertFalse($query
      ->hasAllTags('test', 'stuff'), 'hasAllTags() returned false.');
  }

  /**
   * Test extended query tagging "has at least one of these tags" functionality.
   */
  function testExtenderHasAnyTag() {
    $query = db_select('test')
      ->extend('SelectQueryExtender');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('test');
    $this
      ->assertTrue($query
      ->hasAnyTag('test', 'other'), 'hasAnyTag() returned true.');
    $this
      ->assertFalse($query
      ->hasAnyTag('other', 'stuff'), 'hasAnyTag() returned false.');
  }

  /**
   * Test that we can attach meta data to a query object.
   *
   * This is how we pass additional context to alter hooks.
   */
  function testMetaData() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $data = array(
      'a' => 'A',
      'b' => 'B',
    );
    $query
      ->addMetaData('test', $data);
    $return = $query
      ->getMetaData('test');
    $this
      ->assertEqual($data, $return, 'Corect metadata returned.');
    $return = $query
      ->getMetaData('nothere');
    $this
      ->assertNull($return, 'Non-existent key returned NULL.');
  }

}

/**
 * Select alter tests.
 *
 * @see database_test_query_alter()
 */
class DatabaseAlterTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Query altering tests',
      'description' => 'Test the hook_query_alter capabilities of the Select builder.',
      'group' => 'Database',
    );
  }

  /**
   * Test that we can do basic alters.
   */
  function testSimpleAlter() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->addTag('database_test_alter_add_range');
    $result = $query
      ->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this
      ->assertEqual($num_records, 2, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can alter the joins on a query.
   */
  function testAlterWithJoin() {
    $query = db_select('test_task');
    $tid_field = $query
      ->addField('test_task', 'tid');
    $task_field = $query
      ->addField('test_task', 'task');
    $query
      ->orderBy($task_field);
    $query
      ->addTag('database_test_alter_add_join');
    $result = $query
      ->execute();
    $records = $result
      ->fetchAll();
    $this
      ->assertEqual(count($records), 2, 'Returned the correct number of rows.');
    $this
      ->assertEqual($records[0]->name, 'George', 'Correct data retrieved.');
    $this
      ->assertEqual($records[0]->{$tid_field}, 4, 'Correct data retrieved.');
    $this
      ->assertEqual($records[0]->{$task_field}, 'sing', 'Correct data retrieved.');
    $this
      ->assertEqual($records[1]->name, 'George', 'Correct data retrieved.');
    $this
      ->assertEqual($records[1]->{$tid_field}, 5, 'Correct data retrieved.');
    $this
      ->assertEqual($records[1]->{$task_field}, 'sleep', 'Correct data retrieved.');
  }

  /**
   * Test that we can alter a query's conditionals.
   */
  function testAlterChangeConditional() {
    $query = db_select('test_task');
    $tid_field = $query
      ->addField('test_task', 'tid');
    $pid_field = $query
      ->addField('test_task', 'pid');
    $task_field = $query
      ->addField('test_task', 'task');
    $people_alias = $query
      ->join('test', 'people', "test_task.pid = people.id");
    $name_field = $query
      ->addField($people_alias, 'name', 'name');
    $query
      ->condition('test_task.tid', '1');
    $query
      ->orderBy($tid_field);
    $query
      ->addTag('database_test_alter_change_conditional');
    $result = $query
      ->execute();
    $records = $result
      ->fetchAll();
    $this
      ->assertEqual(count($records), 1, 'Returned the correct number of rows.');
    $this
      ->assertEqual($records[0]->{$name_field}, 'John', 'Correct data retrieved.');
    $this
      ->assertEqual($records[0]->{$tid_field}, 2, 'Correct data retrieved.');
    $this
      ->assertEqual($records[0]->{$pid_field}, 1, 'Correct data retrieved.');
    $this
      ->assertEqual($records[0]->{$task_field}, 'sleep', 'Correct data retrieved.');
  }

  /**
   * Test that we can alter the fields of a query.
   */
  function testAlterChangeFields() {
    $query = db_select('test');
    $name_field = $query
      ->addField('test', 'name');
    $age_field = $query
      ->addField('test', 'age', 'age');
    $query
      ->orderBy('name');
    $query
      ->addTag('database_test_alter_change_fields');
    $record = $query
      ->execute()
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Correct data retrieved.');
    $this
      ->assertFalse(isset($record->{$age_field}), 'Age field not found, as intended.');
  }

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

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Fetched name is correct.');
    $this
      ->assertEqual($record->{$age_field}, 27 * 3, 'Fetched age expression is correct.');
  }

  /**
   * Test that we can remove a range() value from a query. This also tests hook_query_TAG_alter().
   */
  function testAlterRemoveRange() {
    $query = db_select('test');
    $query
      ->addField('test', 'name');
    $query
      ->addField('test', 'age', 'age');
    $query
      ->range(0, 2);
    $query
      ->addTag('database_test_alter_remove_range');
    $num_records = count($query
      ->execute()
      ->fetchAll());
    $this
      ->assertEqual($num_records, 4, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can do basic alters on subqueries.
   */
  function testSimpleAlterSubquery() {

    // Create a sub-query with an alter tag.
    $subquery = db_select('test', 'p');
    $subquery
      ->addField('p', 'name');
    $subquery
      ->addField('p', 'id');

    // Pick out George.
    $subquery
      ->condition('age', 27);
    $subquery
      ->addExpression("age*2", 'double_age');

    // This query alter should change it to age * 3.
    $subquery
      ->addTag('database_test_alter_change_expressions');

    // Create a main query and join to sub-query.
    $query = db_select('test_task', 'tt');
    $query
      ->join($subquery, 'pq', 'pq.id = tt.pid');
    $age_field = $query
      ->addField('pq', 'double_age');
    $name_field = $query
      ->addField('pq', 'name');
    $record = $query
      ->execute()
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', 'Fetched name is correct.');
    $this
      ->assertEqual($record->{$age_field}, 27 * 3, 'Fetched age expression is correct.');
  }

}

/**
 * Regression tests.
 */
class DatabaseRegressionTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Regression tests',
      'description' => 'Regression tests cases for the database layer.',
      'group' => 'Database',
    );
  }

  /**
   * Regression test for #310447.
   *
   * Tries to insert non-ascii UTF-8 data in a database column and checks
   * if its stored properly.
   */
  function testRegression_310447() {

    // That's a 255 character UTF-8 string.
    $name = str_repeat("é", 255);
    db_insert('test')
      ->fields(array(
      'name' => $name,
      'age' => 20,
      'job' => 'Dancer',
    ))
      ->execute();
    $from_database = db_query('SELECT name FROM {test} WHERE name = :name', array(
      ':name' => $name,
    ))
      ->fetchField();
    $this
      ->assertIdentical($name, $from_database, "The database handles UTF-8 characters cleanly.");
  }

  /**
   * Test the db_table_exists() function.
   */
  function testDBTableExists() {
    $this
      ->assertIdentical(TRUE, db_table_exists('node'), 'Returns true for existent table.');
    $this
      ->assertIdentical(FALSE, db_table_exists('nosuchtable'), 'Returns false for nonexistent table.');
  }

  /**
   * Test the db_field_exists() function.
   */
  function testDBFieldExists() {
    $this
      ->assertIdentical(TRUE, db_field_exists('node', 'nid'), 'Returns true for existent column.');
    $this
      ->assertIdentical(FALSE, db_field_exists('node', 'nosuchcolumn'), 'Returns false for nonexistent column.');
  }

  /**
   * Test the db_index_exists() function.
   */
  function testDBIndexExists() {
    $this
      ->assertIdentical(TRUE, db_index_exists('node', 'node_created'), 'Returns true for existent index.');
    $this
      ->assertIdentical(FALSE, db_index_exists('node', 'nosuchindex'), 'Returns false for nonexistent index.');
  }

}

/**
 * Query logging tests.
 */
class DatabaseLoggingTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Query logging',
      'description' => 'Test the query logging facility.',
      'group' => 'Database',
    );
  }

  /**
   * Test that we can log the existence of a query.
   */
  function testEnableLogging() {
    $log = Database::startLog('testing');
    db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ))
      ->fetchCol();
    db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetchCol();

    // Trigger a call that does not have file in the backtrace.
    call_user_func_array('db_query', array(
      'SELECT age FROM {test} WHERE name = :name',
      array(
        ':name' => 'Ringo',
      ),
    ))
      ->fetchCol();
    $queries = Database::getLog('testing', 'default');
    $this
      ->assertEqual(count($queries), 3, 'Correct number of queries recorded.');
    foreach ($queries as $query) {
      $this
        ->assertEqual($query['caller']['function'], __FUNCTION__, 'Correct function in query log.');
    }
  }

  /**
   * Test that we can run two logs in parallel.
   */
  function testEnableMultiLogging() {
    Database::startLog('testing1');
    db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ))
      ->fetchCol();
    Database::startLog('testing2');
    db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ))
      ->fetchCol();
    $queries1 = Database::getLog('testing1');
    $queries2 = Database::getLog('testing2');
    $this
      ->assertEqual(count($queries1), 2, 'Correct number of queries recorded for log 1.');
    $this
      ->assertEqual(count($queries2), 1, 'Correct number of queries recorded for log 2.');
  }

  /**
   * Test that we can log queries against multiple targets on the same connection.
   */
  function testEnableTargetLogging() {

    // Clone the master credentials to a slave connection and to another fake
    // connection.
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('default', 'slave', $connection_info['default']);
    Database::startLog('testing1');
    db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ))
      ->fetchCol();
    db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ), array(
      'target' => 'slave',
    ));

    //->fetchCol();
    $queries1 = Database::getLog('testing1');
    $this
      ->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
    $this
      ->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
    $this
      ->assertEqual($queries1[1]['target'], 'slave', 'Second query used slave target.');
  }

  /**
   * Test that logs to separate targets collapse to the same connection properly.
   *
   * This test is identical to the one above, except that it doesn't create
   * a fake target so the query should fall back to running on the default
   * target.
   */
  function testEnableTargetLoggingNoTarget() {
    Database::startLog('testing1');
    db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ))
      ->fetchCol();

    // We use "fake" here as a target because any non-existent target will do.
    // However, because all of the tests in this class share a single page
    // request there is likely to be a target of "slave" from one of the other
    // unit tests, so we use a target here that we know with absolute certainty
    // does not exist.
    db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ), array(
      'target' => 'fake',
    ))
      ->fetchCol();
    $queries1 = Database::getLog('testing1');
    $this
      ->assertEqual(count($queries1), 2, 'Recorded queries from all targets.');
    $this
      ->assertEqual($queries1[0]['target'], 'default', 'First query used default target.');
    $this
      ->assertEqual($queries1[1]['target'], 'default', 'Second query used default target as fallback.');
  }

  /**
   * Test that we can log queries separately on different connections.
   */
  function testEnableMultiConnectionLogging() {

    // Clone the master credentials to a fake connection.
    // That both connections point to the same physical database is irrelevant.
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('test2', 'default', $connection_info['default']);
    Database::startLog('testing1');
    Database::startLog('testing1', 'test2');
    db_query('SELECT name FROM {test} WHERE age > :age', array(
      ':age' => 25,
    ))
      ->fetchCol();
    $old_key = db_set_active('test2');
    db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'Ringo',
    ), array(
      'target' => 'slave',
    ))
      ->fetchCol();
    db_set_active($old_key);
    $queries1 = Database::getLog('testing1');
    $queries2 = Database::getLog('testing1', 'test2');
    $this
      ->assertEqual(count($queries1), 1, 'Correct number of queries recorded for first connection.');
    $this
      ->assertEqual(count($queries2), 1, 'Correct number of queries recorded for second connection.');
  }

}

/**
 * Query serialization tests.
 */
class DatabaseSerializeQueryTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Serialize query',
      'description' => 'Test serializing and unserializing a query.',
      'group' => 'Database',
    );
  }

  /**
   * Confirm that a query can be serialized and unserialized.
   */
  function testSerializeQuery() {
    $query = db_select('test');
    $query
      ->addField('test', 'age');
    $query
      ->condition('name', 'Ringo');

    // If this doesn't work, it will throw an exception, so no need for an
    // assertion.
    $query = unserialize(serialize($query));
    $results = $query
      ->execute()
      ->fetchCol();
    $this
      ->assertEqual($results[0], 28, 'Query properly executed after unserialization.');
  }

}

/**
 * Range query tests.
 */
class DatabaseRangeQueryTestCase extends DrupalWebTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Range query test',
      'description' => 'Test the Range query functionality.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }

  /**
   * Confirm that range query work and return correct result.
   */
  function testRangeQuery() {

    // Test if return correct number of rows.
    $range_rows = db_query_range("SELECT name FROM {system} ORDER BY name", 2, 3)
      ->fetchAll();
    $this
      ->assertEqual(count($range_rows), 3, 'Range query work and return correct number of rows.');

    // Test if return target data.
    $raw_rows = db_query('SELECT name FROM {system} ORDER BY name')
      ->fetchAll();
    $raw_rows = array_slice($raw_rows, 2, 3);
    $this
      ->assertEqual($range_rows, $raw_rows, 'Range query work and return target data.');
  }

}

/**
 * Temporary query tests.
 */
class DatabaseTemporaryQueryTestCase extends DrupalWebTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Temporary query test',
      'description' => 'Test the temporary query functionality.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }

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

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

    // Now try to run two db_query_temporary() in the same request.
    $table_name_system = db_query_temporary('SELECT status FROM {system}', array());
    $table_name_users = db_query_temporary('SELECT uid FROM {users}', array());
    $this
      ->assertEqual($this
      ->countTableRows($table_name_system), $this
      ->countTableRows("system"), 'A temporary table was created successfully in this request.');
    $this
      ->assertEqual($this
      ->countTableRows($table_name_users), $this
      ->countTableRows("users"), 'A second temporary table was created successfully in this request.');

    // Check that leading whitespace and comments do not cause problems
    // in the modified query.
    $sql = "\n      -- Let's select some rows into a temporary table\n      SELECT name FROM {test}\n    ";
    $table_name_test = db_query_temporary($sql, array());
    $this
      ->assertEqual($this
      ->countTableRows($table_name_test), $this
      ->countTableRows('test'), 'Leading white space and comments do not interfere with temporary table creation.');
  }

}

/**
 * Test how the current database driver interprets the SQL syntax.
 *
 * In order to ensure consistent SQL handling throughout Drupal
 * across multiple kinds of database systems, we test that the
 * database system interprets SQL syntax in an expected fashion.
 */
class DatabaseBasicSyntaxTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Basic SQL syntax tests',
      'description' => 'Test SQL syntax interpretation.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }

  /**
   * Test for string concatenation.
   */
  function testBasicConcat() {
    $result = db_query('SELECT CONCAT(:a1, CONCAT(:a2, CONCAT(:a3, CONCAT(:a4, :a5))))', array(
      ':a1' => 'This',
      ':a2' => ' ',
      ':a3' => 'is',
      ':a4' => ' a ',
      ':a5' => 'test.',
    ));
    $this
      ->assertIdentical($result
      ->fetchField(), 'This is a test.', 'Basic CONCAT works.');
  }

  /**
   * Test for string concatenation with field values.
   */
  function testFieldConcat() {
    $result = db_query('SELECT CONCAT(:a1, CONCAT(name, CONCAT(:a2, CONCAT(age, :a3)))) FROM {test} WHERE age = :age', array(
      ':a1' => 'The age of ',
      ':a2' => ' is ',
      ':a3' => '.',
      ':age' => 25,
    ));
    $this
      ->assertIdentical($result
      ->fetchField(), 'The age of John is 25.', 'Field CONCAT works.');
  }

  /**
   * Test escaping of LIKE wildcards.
   */
  function testLikeEscape() {
    db_insert('test')
      ->fields(array(
      'name' => 'Ring_',
    ))
      ->execute();

    // Match both "Ringo" and "Ring_".
    $num_matches = db_select('test', 't')
      ->condition('name', 'Ring_', 'LIKE')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '2', 'Found 2 records.');

    // Match only "Ring_" using a LIKE expression with no wildcards.
    $num_matches = db_select('test', 't')
      ->condition('name', db_like('Ring_'), 'LIKE')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '1', 'Found 1 record.');
  }

  /**
   * Test LIKE query containing a backslash.
   */
  function testLikeBackslash() {
    db_insert('test')
      ->fields(array(
      'name',
    ))
      ->values(array(
      'name' => 'abcde\\f',
    ))
      ->values(array(
      'name' => 'abc%\\_',
    ))
      ->execute();

    // Match both rows using a LIKE expression with two wildcards and a verbatim
    // backslash.
    $num_matches = db_select('test', 't')
      ->condition('name', 'abc%\\\\_', 'LIKE')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '2', 'Found 2 records.');

    // Match only the former using a LIKE expression with no wildcards.
    $num_matches = db_select('test', 't')
      ->condition('name', db_like('abc%\\_'), 'LIKE')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertIdentical($num_matches, '1', 'Found 1 record.');
  }

}

/**
 * Test case sensitivity handling.
 */
class DatabaseCaseSensitivityTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Case sensitivity',
      'description' => 'Test handling case sensitive collation.',
      'group' => 'Database',
    );
  }

  /**
   * Test BINARY collation in MySQL.
   */
  function testCaseSensitiveInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $john = db_insert('test')
      ->fields(array(
      'name' => 'john',
      // <- A record already exists with name 'John'.
      'age' => 2,
      'job' => 'Baby',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
      ':name' => 'john',
    ))
      ->fetchField();
    $this
      ->assertIdentical($saved_age, '2', 'Can retrieve after inserting.');
  }

}

/**
 * Test invalid data handling.
 */
class DatabaseInvalidDataTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Invalid data',
      'description' => 'Test handling of some invalid data.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }

  /**
   * Traditional SQL database systems abort inserts when invalid data is encountered.
   */
  function testInsertDuplicateData() {

    // Try to insert multiple records where at least one has bad data.
    try {
      db_insert('test')
        ->fields(array(
        'name',
        'age',
        'job',
      ))
        ->values(array(
        'name' => 'Elvis',
        'age' => 63,
        'job' => 'Singer',
      ))
        ->values(array(
        'name' => 'John',
        // <-- Duplicate value on unique field.
        'age' => 17,
        'job' => 'Consultant',
      ))
        ->values(array(
        'name' => 'Frank',
        'age' => 75,
        'job' => 'Singer',
      ))
        ->execute();
      $this
        ->fail('Insert succeedded when it should not have.');
    } catch (Exception $e) {

      // Check if the first record was inserted.
      $name = db_query('SELECT name FROM {test} WHERE age = :age', array(
        ':age' => 63,
      ))
        ->fetchField();
      if ($name == 'Elvis') {
        if (!Database::getConnection()
          ->supportsTransactions()) {

          // This is an expected fail.
          // Database engines that don't support transactions can leave partial
          // inserts in place when an error occurs. This is the case for MySQL
          // when running on a MyISAM table.
          $this
            ->pass("The whole transaction has not been rolled-back when a duplicate key insert occurs, this is expected because the database doesn't support transactions");
        }
        else {
          $this
            ->fail('The whole transaction is rolled back when a duplicate key insert occurs.');
        }
      }
      else {
        $this
          ->pass('The whole transaction is rolled back when a duplicate key insert occurs.');
      }

      // Ensure the other values were not inserted.
      $record = db_select('test')
        ->fields('test', array(
        'name',
        'age',
      ))
        ->condition('age', array(
        17,
        75,
      ), 'IN')
        ->execute()
        ->fetchObject();
      $this
        ->assertFalse($record, 'The rest of the insert aborted as expected.');
    }
  }

}

/**
 * Drupal-specific SQL syntax tests.
 */
class DatabaseQueryTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Custom query syntax tests',
      'description' => 'Test Drupal\'s extended prepared statement syntax..',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }

  /**
   * Test that we can specify an array of values in the query by simply passing in an array.
   */
  function testArraySubstitution() {
    $names = db_query('SELECT name FROM {test} WHERE age IN (:ages) ORDER BY age', array(
      ':ages' => array(
        25,
        26,
        27,
      ),
    ))
      ->fetchAll();
    $this
      ->assertEqual(count($names), 3, 'Correct number of names returned');
  }

  /**
   * Test SQL injection via database query array arguments.
   */
  public function testArrayArgumentsSQLInjection() {

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

    // Test that the insert query that was used in the SQL injection attempt did
    // not result in a row being inserted in the database.
    $result = db_select('test')
      ->condition('name', 'test12345678')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertFalse($result, 'SQL injection attempt did not result in a row being inserted in the database table.');
  }

}

/**
 * Test transaction support, particularly nesting.
 *
 * We test nesting by having two transaction layers, an outer and inner. The
 * outer layer encapsulates the inner layer. Our transaction nesting abstraction
 * should allow the outer layer function to call any function it wants,
 * especially the inner layer that starts its own transaction, and be
 * confident that, when the function it calls returns, its own transaction
 * is still "alive."
 *
 * Call structure:
 *   transactionOuterLayer()
 *     Start transaction
 *     transactionInnerLayer()
 *       Start transaction (does nothing in database)
 *       [Maybe decide to roll back]
 *     Do more stuff
 *     Should still be in transaction A
 *
 */
class DatabaseTransactionTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Transaction tests',
      'description' => 'Test the transaction abstraction system.',
      'group' => 'Database',
    );
  }

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

    // Insert a single row into the testing table.
    db_insert('test')
      ->fields(array(
      'name' => 'David' . $suffix,
      'age' => '24',
    ))
      ->execute();
    $this
      ->assertTrue($connection
      ->inTransaction(), 'In transaction before calling nested transaction.');

    // We're already in a transaction, but we call ->transactionInnerLayer
    // to nest another transaction inside the current one.
    $this
      ->transactionInnerLayer($suffix, $rollback, $ddl_statement);
    $this
      ->assertTrue($connection
      ->inTransaction(), 'In transaction after calling nested transaction.');
    if ($rollback) {

      // Roll back the transaction, if requested.
      // This rollback should propagate to the last savepoint.
      $txn
        ->rollback();
      $this
        ->assertTrue($connection
        ->transactionDepth() == $depth, 'Transaction has rolled back to the last savepoint after calling rollback().');
    }
  }

  /**
   * Helper method for transaction unit tests. This "inner layer" transaction
   * is either used alone or nested inside of the "outer layer" transaction.
   *
   * @param $suffix
   *   Suffix to add to field values to differentiate tests.
   * @param $rollback
   *   Whether or not to try rolling back the transaction when we're done.
   * @param $ddl_statement
   *   Whether to execute a DDL statement during the transaction.
   */
  protected function transactionInnerLayer($suffix, $rollback = FALSE, $ddl_statement = FALSE) {
    $connection = Database::getConnection();
    $depth = $connection
      ->transactionDepth();

    // Start a transaction. If we're being called from ->transactionOuterLayer,
    // then we're already in a transaction. Normally, that would make starting
    // a transaction here dangerous, but the database API handles this problem
    // for us by tracking the nesting and avoiding the danger.
    $txn = db_transaction();
    $depth2 = $connection
      ->transactionDepth();
    $this
      ->assertTrue($depth < $depth2, 'Transaction depth is has increased with new transaction.');

    // Insert a single row into the testing table.
    db_insert('test')
      ->fields(array(
      'name' => 'Daniel' . $suffix,
      'age' => '19',
    ))
      ->execute();
    $this
      ->assertTrue($connection
      ->inTransaction(), 'In transaction inside nested transaction.');
    if ($ddl_statement) {
      $table = array(
        'fields' => array(
          'id' => array(
            'type' => 'serial',
            'unsigned' => TRUE,
            'not null' => TRUE,
          ),
        ),
        'primary key' => array(
          'id',
        ),
      );
      db_create_table('database_test_1', $table);
      $this
        ->assertTrue($connection
        ->inTransaction(), 'In transaction inside nested transaction.');
    }
    if ($rollback) {

      // Roll back the transaction, if requested.
      // This rollback should propagate to the last savepoint.
      $txn
        ->rollback();
      $this
        ->assertTrue($connection
        ->transactionDepth() == $depth, 'Transaction has rolled back to the last savepoint after calling rollback().');
    }
  }

  /**
   * Test transaction rollback on a database that supports transactions.
   *
   * If the active connection does not support transactions, this test does nothing.
   */
  function testTransactionRollBackSupported() {

    // This test won't work right if transactions are not supported.
    if (!Database::getConnection()
      ->supportsTransactions()) {
      return;
    }
    try {

      // Create two nested transactions. Roll back from the inner one.
      $this
        ->transactionOuterLayer('B', TRUE);

      // Neither of the rows we inserted in the two transaction layers
      // should be present in the tables post-rollback.
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DavidB',
      ))
        ->fetchField();
      $this
        ->assertNotIdentical($saved_age, '24', 'Cannot retrieve DavidB row after commit.');
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DanielB',
      ))
        ->fetchField();
      $this
        ->assertNotIdentical($saved_age, '19', 'Cannot retrieve DanielB row after commit.');
    } catch (Exception $e) {
      $this
        ->fail($e
        ->getMessage());
    }
  }

  /**
   * Test transaction rollback on a database that does not support transactions.
   *
   * If the active driver supports transactions, this test does nothing.
   */
  function testTransactionRollBackNotSupported() {

    // This test won't work right if transactions are supported.
    if (Database::getConnection()
      ->supportsTransactions()) {
      return;
    }
    try {

      // Create two nested transactions. Attempt to roll back from the inner one.
      $this
        ->transactionOuterLayer('B', TRUE);

      // Because our current database claims to not support transactions,
      // the inserted rows should be present despite the attempt to roll back.
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DavidB',
      ))
        ->fetchField();
      $this
        ->assertIdentical($saved_age, '24', 'DavidB not rolled back, since transactions are not supported.');
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DanielB',
      ))
        ->fetchField();
      $this
        ->assertIdentical($saved_age, '19', 'DanielB not rolled back, since transactions are not supported.');
    } catch (Exception $e) {
      $this
        ->fail($e
        ->getMessage());
    }
  }

  /**
   * Test committed transaction.
   *
   * The behavior of this test should be identical for connections that support
   * transactions and those that do not.
   */
  function testCommittedTransaction() {
    try {

      // Create two nested transactions. The changes should be committed.
      $this
        ->transactionOuterLayer('A');

      // Because we committed, both of the inserted rows should be present.
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DavidA',
      ))
        ->fetchField();
      $this
        ->assertIdentical($saved_age, '24', 'Can retrieve DavidA row after commit.');
      $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(
        ':name' => 'DanielA',
      ))
        ->fetchField();
      $this
        ->assertIdentical($saved_age, '19', 'Can retrieve DanielA row after commit.');
    } catch (Exception $e) {
      $this
        ->fail($e
        ->getMessage());
    }
  }

  /**
   * Test the compatibility of transactions with DDL statements.
   */
  function testTransactionWithDdlStatement() {

    // First, test that a commit works normally, even with DDL statements.
    $transaction = db_transaction();
    $this
      ->insertRow('row');
    $this
      ->executeDDLStatement();
    unset($transaction);
    $this
      ->assertRowPresent('row');

    // Even in different order.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $this
      ->executeDDLStatement();
    $this
      ->insertRow('row');
    unset($transaction);
    $this
      ->assertRowPresent('row');

    // Even with stacking.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $transaction2 = db_transaction();
    $this
      ->executeDDLStatement();
    unset($transaction2);
    $transaction3 = db_transaction();
    $this
      ->insertRow('row');
    unset($transaction3);
    unset($transaction);
    $this
      ->assertRowPresent('row');

    // A transaction after a DDL statement should still work the same.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $transaction2 = db_transaction();
    $this
      ->executeDDLStatement();
    unset($transaction2);
    $transaction3 = db_transaction();
    $this
      ->insertRow('row');
    $transaction3
      ->rollback();
    unset($transaction3);
    unset($transaction);
    $this
      ->assertRowAbsent('row');

    // The behavior of a rollback depends on the type of database server.
    if (Database::getConnection()
      ->supportsTransactionalDDL()) {

      // For database servers that support transactional DDL, a rollback
      // of a transaction including DDL statements should be possible.
      $this
        ->cleanUp();
      $transaction = db_transaction();
      $this
        ->insertRow('row');
      $this
        ->executeDDLStatement();
      $transaction
        ->rollback();
      unset($transaction);
      $this
        ->assertRowAbsent('row');

      // Including with stacking.
      $this
        ->cleanUp();
      $transaction = db_transaction();
      $transaction2 = db_transaction();
      $this
        ->executeDDLStatement();
      unset($transaction2);
      $transaction3 = db_transaction();
      $this
        ->insertRow('row');
      unset($transaction3);
      $transaction
        ->rollback();
      unset($transaction);
      $this
        ->assertRowAbsent('row');
    }
    else {

      // For database servers that do not support transactional DDL,
      // the DDL statement should commit the transaction stack.
      $this
        ->cleanUp();
      $transaction = db_transaction();
      $this
        ->insertRow('row');
      $this
        ->executeDDLStatement();
      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.');
    }
    _drupal_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 = (bool) 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 = (bool) db_query('SELECT 1 FROM {test} WHERE name = :name', array(
      ':name' => $name,
    ))
      ->fetchField();
    return $this
      ->assertFalse($present, $message);
  }

  /**
   * Test transaction stacking and commit / rollback.
   */
  function testTransactionStacking() {

    // This test won't work right if transactions are not supported.
    if (!Database::getConnection()
      ->supportsTransactions()) {
      return;
    }
    $database = Database::getConnection();

    // Standard case: pop the inner transaction before the outer transaction.
    $transaction = db_transaction();
    $this
      ->insertRow('outer');
    $transaction2 = db_transaction();
    $this
      ->insertRow('inner');

    // Pop the inner transaction.
    unset($transaction2);
    $this
      ->assertTrue($database
      ->inTransaction(), 'Still in a transaction after popping the inner transaction');

    // Pop the outer transaction.
    unset($transaction);
    $this
      ->assertFalse($database
      ->inTransaction(), 'Transaction closed after popping the outer transaction');
    $this
      ->assertRowPresent('outer');
    $this
      ->assertRowPresent('inner');

    // Pop the transaction in a different order they have been pushed.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $this
      ->insertRow('outer');
    $transaction2 = db_transaction();
    $this
      ->insertRow('inner');

    // Pop the outer transaction, nothing should happen.
    unset($transaction);
    $this
      ->insertRow('inner-after-outer-commit');
    $this
      ->assertTrue($database
      ->inTransaction(), 'Still in a transaction after popping the outer transaction');

    // Pop the inner transaction, the whole transaction should commit.
    unset($transaction2);
    $this
      ->assertFalse($database
      ->inTransaction(), 'Transaction closed after popping the inner transaction');
    $this
      ->assertRowPresent('outer');
    $this
      ->assertRowPresent('inner');
    $this
      ->assertRowPresent('inner-after-outer-commit');

    // Rollback the inner transaction.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $this
      ->insertRow('outer');
    $transaction2 = db_transaction();
    $this
      ->insertRow('inner');

    // Now rollback the inner transaction.
    $transaction2
      ->rollback();
    unset($transaction2);
    $this
      ->assertTrue($database
      ->inTransaction(), 'Still in a transaction after popping the outer transaction');

    // Pop the outer transaction, it should commit.
    $this
      ->insertRow('outer-after-inner-rollback');
    unset($transaction);
    $this
      ->assertFalse($database
      ->inTransaction(), 'Transaction closed after popping the inner transaction');
    $this
      ->assertRowPresent('outer');
    $this
      ->assertRowAbsent('inner');
    $this
      ->assertRowPresent('outer-after-inner-rollback');

    // Rollback the inner transaction after committing the outer one.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $this
      ->insertRow('outer');
    $transaction2 = db_transaction();
    $this
      ->insertRow('inner');

    // Pop the outer transaction, nothing should happen.
    unset($transaction);
    $this
      ->assertTrue($database
      ->inTransaction(), 'Still in a transaction after popping the outer transaction');

    // Now rollback the inner transaction, it should rollback.
    $transaction2
      ->rollback();
    unset($transaction2);
    $this
      ->assertFalse($database
      ->inTransaction(), 'Transaction closed after popping the inner transaction');
    $this
      ->assertRowPresent('outer');
    $this
      ->assertRowAbsent('inner');

    // Rollback the outer transaction while the inner transaction is active.
    // In that case, an exception will be triggered because we cannot
    // ensure that the final result will have any meaning.
    $this
      ->cleanUp();
    $transaction = db_transaction();
    $this
      ->insertRow('outer');
    $transaction2 = db_transaction();
    $this
      ->insertRow('inner');
    $transaction3 = db_transaction();
    $this
      ->insertRow('inner2');

    // Rollback the outer transaction.
    try {
      $transaction
        ->rollback();
      unset($transaction);
      $this
        ->fail('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
    } catch (DatabaseTransactionOutOfOrderException $e) {
      $this
        ->pass('Rolling back the outer transaction while the inner transaction is active resulted in an exception.');
    }
    $this
      ->assertFalse($database
      ->inTransaction(), 'No more in a transaction after rolling back the outer transaction');

    // Try to commit one inner transaction.
    unset($transaction3);
    $this
      ->pass('Trying to commit an inner transaction resulted in an exception.');

    // Try to rollback one inner transaction.
    try {
      $transaction
        ->rollback();
      unset($transaction2);
      $this
        ->fail('Trying to commit an inner transaction resulted in an exception.');
    } catch (DatabaseTransactionNoActiveException $e) {
      $this
        ->pass('Trying to commit an inner transaction resulted in an exception.');
    }
    $this
      ->assertRowAbsent('outer');
    $this
      ->assertRowAbsent('inner');
    $this
      ->assertRowAbsent('inner2');
  }

}

/**
 * Check the sequences API.
 */
class DatabaseNextIdCase extends DrupalWebTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Sequences API',
      'description' => 'Test the secondary sequences API.',
      'group' => 'Database',
    );
  }

  /**
   * Test that the sequences API work.
   */
  function testDbNextId() {
    $first = db_next_id();
    $second = db_next_id();

    // We can test for exact increase in here because we know there is no
    // other process operating on these tables -- normally we could only
    // expect $second > $first.
    $this
      ->assertEqual($first + 1, $second, 'The second call from a sequence provides a number increased by one.');
    $result = db_next_id(1000);
    $this
      ->assertEqual($result, 1001, 'Sequence provides a larger number than the existing ID.');
  }

}

/**
 * Tests the empty pseudo-statement class.
 */
class DatabaseEmptyStatementTestCase extends DrupalWebTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Empty statement',
      'description' => 'Test the empty pseudo-statement class.',
      'group' => 'Database',
    );
  }

  /**
   * Test that the empty result set behaves as empty.
   */
  function testEmpty() {
    $result = new DatabaseStatementEmpty();
    $this
      ->assertTrue($result instanceof DatabaseStatementInterface, 'Class implements expected interface');
    $this
      ->assertNull($result
      ->fetchObject(), 'Null result returned.');
  }

  /**
   * Test that the empty result set iterates safely.
   */
  function testEmptyIteration() {
    $result = new DatabaseStatementEmpty();
    foreach ($result as $record) {
      $this
        ->fail('Iterating empty result set should not iterate.');
      return;
    }
    $this
      ->pass('Iterating empty result set skipped iteration.');
  }

  /**
   * Test that the empty result set mass-fetches in an expected way.
   */
  function testEmptyFetchAll() {
    $result = new DatabaseStatementEmpty();
    $this
      ->assertEqual($result
      ->fetchAll(), array(), 'Empty array returned from empty result set.');
  }

}

/**
 * Tests management of database connections.
 */
class ConnectionUnitTest extends DrupalUnitTestCase {
  protected $key;
  protected $target;
  protected $monitor;
  protected $originalCount;
  protected $skipTest;
  public static function getInfo() {
    return array(
      'name' => 'Connection unit tests',
      'description' => 'Tests management of database connections.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp();
    $this->key = 'default';
    $this->originalTarget = 'default';
    $this->target = 'DatabaseConnectionUnitTest';

    // Determine whether the database driver is MySQL. If it is not, the test
    // methods will not be executed.
    // @todo Make this test driver-agnostic, or find a proper way to skip it.
    // @see http://drupal.org/node/1273478
    $connection_info = Database::getConnectionInfo('default');
    $this->skipTest = (bool) ($connection_info['default']['driver'] != 'mysql');
    if ($this->skipTest) {

      // Insert an assertion to prevent Simpletest from interpreting the test
      // as failure.
      $this
        ->pass('This test is only compatible with MySQL.');
    }

    // Create an additional connection to monitor the connections being opened
    // and closed in this test.
    // @see TestBase::changeDatabasePrefix()
    $connection_info = Database::getConnectionInfo('default');
    Database::addConnectionInfo('default', 'monitor', $connection_info['default']);
    global $databases;
    $databases['default']['monitor'] = $connection_info['default'];
    $this->monitor = Database::getConnection('monitor');
  }

  /**
   * Adds a new database connection info to Database.
   */
  protected function addConnection() {

    // Add a new target to the connection, by cloning the current connection.
    $connection_info = Database::getConnectionInfo($this->key);
    Database::addConnectionInfo($this->key, $this->target, $connection_info[$this->originalTarget]);

    // Verify that the new target exists.
    $info = Database::getConnectionInfo($this->key);

    // Note: Custom assertion message to not expose database credentials.
    $this
      ->assertIdentical($info[$this->target], $connection_info[$this->key], 'New connection info found.');
  }

  /**
   * Returns the connection ID of the current test connection.
   *
   * @return integer
   */
  protected function getConnectionID() {
    return (int) Database::getConnection($this->target, $this->key)
      ->query('SELECT CONNECTION_ID()')
      ->fetchField();
  }

  /**
   * Asserts that a connection ID exists.
   *
   * @param integer $id
   *   The connection ID to verify.
   */
  protected function assertConnection($id) {
    $list = $this->monitor
      ->query('SHOW PROCESSLIST')
      ->fetchAllKeyed(0, 0);
    return $this
      ->assertTrue(isset($list[$id]), format_string('Connection ID @id found.', array(
      '@id' => $id,
    )));
  }

  /**
   * Asserts that a connection ID does not exist.
   *
   * @param integer $id
   *   The connection ID to verify.
   */
  protected function assertNoConnection($id) {
    $list = $this->monitor
      ->query('SHOW PROCESSLIST')
      ->fetchAllKeyed(0, 0);
    return $this
      ->assertFalse(isset($list[$id]), format_string('Connection ID @id not found.', array(
      '@id' => $id,
    )));
  }

  /**
   * Tests Database::closeConnection() without query.
   *
   * @todo getConnectionID() executes a query.
   */
  function testOpenClose() {
    if ($this->skipTest) {
      return;
    }

    // Add and open a new connection.
    $this
      ->addConnection();
    $id = $this
      ->getConnectionID();
    Database::getConnection($this->target, $this->key);

    // Verify that there is a new connection.
    $this
      ->assertConnection($id);

    // Close the connection.
    Database::closeConnection($this->target, $this->key);

    // Wait 20ms to give the database engine sufficient time to react.
    usleep(20000);

    // Verify that we are back to the original connection count.
    $this
      ->assertNoConnection($id);
  }

  /**
   * Tests Database::closeConnection() with a query.
   */
  function testOpenQueryClose() {
    if ($this->skipTest) {
      return;
    }

    // Add and open a new connection.
    $this
      ->addConnection();
    $id = $this
      ->getConnectionID();
    Database::getConnection($this->target, $this->key);

    // Verify that there is a new connection.
    $this
      ->assertConnection($id);

    // Execute a query.
    Database::getConnection($this->target, $this->key)
      ->query('SHOW TABLES');

    // Close the connection.
    Database::closeConnection($this->target, $this->key);

    // Wait 20ms to give the database engine sufficient time to react.
    usleep(20000);

    // Verify that we are back to the original connection count.
    $this
      ->assertNoConnection($id);
  }

  /**
   * Tests Database::closeConnection() with a query and custom prefetch method.
   */
  function testOpenQueryPrefetchClose() {
    if ($this->skipTest) {
      return;
    }

    // Add and open a new connection.
    $this
      ->addConnection();
    $id = $this
      ->getConnectionID();
    Database::getConnection($this->target, $this->key);

    // Verify that there is a new connection.
    $this
      ->assertConnection($id);

    // Execute a query.
    Database::getConnection($this->target, $this->key)
      ->query('SHOW TABLES')
      ->fetchCol();

    // Close the connection.
    Database::closeConnection($this->target, $this->key);

    // Wait 20ms to give the database engine sufficient time to react.
    usleep(20000);

    // Verify that we are back to the original connection count.
    $this
      ->assertNoConnection($id);
  }

  /**
   * Tests Database::closeConnection() with a select query.
   */
  function testOpenSelectQueryClose() {
    if ($this->skipTest) {
      return;
    }

    // Add and open a new connection.
    $this
      ->addConnection();
    $id = $this
      ->getConnectionID();
    Database::getConnection($this->target, $this->key);

    // Verify that there is a new connection.
    $this
      ->assertConnection($id);

    // Create a table.
    $name = 'foo';
    Database::getConnection($this->target, $this->key)
      ->schema()
      ->createTable($name, array(
      'fields' => array(
        'name' => array(
          'type' => 'varchar',
          'length' => 255,
        ),
      ),
    ));

    // Execute a query.
    Database::getConnection($this->target, $this->key)
      ->select('foo', 'f')
      ->fields('f', array(
      'name',
    ))
      ->execute()
      ->fetchAll();

    // Drop the table.
    Database::getConnection($this->target, $this->key)
      ->schema()
      ->dropTable($name);

    // Close the connection.
    Database::closeConnection($this->target, $this->key);

    // Wait 20ms to give the database engine sufficient time to react.
    usleep(20000);

    // Verify that we are back to the original connection count.
    $this
      ->assertNoConnection($id);
  }

}

/**
 * Test reserved keyword handling (introduced for MySQL 8+)
*/
class DatabaseReservedKeywordTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Reserved Keywords',
      'description' => 'Test handling of reserved keywords.',
      'group' => 'Database',
    );
  }
  function setUp() {
    parent::setUp('database_test');
  }
  public function testTableNameQuoting() {

    // Test db_query with {table} pattern.
    $record = db_query('SELECT * FROM {system} LIMIT 1')
      ->fetchObject();
    $this
      ->assertTrue(isset($record->filename), 'Successfully queried the {system} table.');
    $connection = Database::getConnection()
      ->getConnectionOptions();
    if ($connection['driver'] === 'sqlite') {

      // In SQLite simpletest's prefixed db tables exist in their own schema
      // (e.g. simpletest124904.system), so we cannot test the schema.{table}
      // syntax here as the table name will have the schema name prepended to it
      // when prefixes are processed.
      $this
        ->assert(TRUE, 'Skipping schema.{system} test for SQLite.');
    }
    else {
      $database = $connection['database'];

      // Test db_query with schema.{table} pattern
      db_query('SELECT * FROM ' . $database . '.{system} LIMIT 1')
        ->fetchObject();
      $this
        ->assertTrue(isset($record->filename), 'Successfully queried the schema.{system} table.');
    }
  }
  public function testSelectReservedWordTableCount() {
    $rows = db_select('virtual')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($rows, 1, 'Successful count query on a table with a reserved name.');
  }
  public function testSelectReservedWordTableSpecificField() {
    $record = db_select('virtual')
      ->fields('virtual', array(
      'function',
    ))
      ->execute()
      ->fetchAssoc();
    $this
      ->assertEqual($record['function'], 'Function value 1', 'Successfully read a field from a table with a name and column which are reserved words.');
  }
  public function testSelectReservedWordTableAllFields() {
    $record = db_select('virtual')
      ->fields('virtual')
      ->execute()
      ->fetchAssoc();
    $this
      ->assertEqual($record['function'], 'Function value 1', 'Successful all_fields query from a table with a name and column which are reserved words.');
  }
  public function testSelectReservedWordAliasCount() {
    $rows = db_select('test', 'character')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this
      ->assertEqual($rows, 4, 'Successful count query using an alias which is a reserved word.');
  }
  public function testSelectReservedWordAliasSpecificFields() {
    $record = db_select('test', 'high_priority')
      ->fields('high_priority', array(
      'name',
    ))
      ->condition('age', 27)
      ->execute()
      ->fetchAssoc();
    $this
      ->assertEqual($record['name'], 'George', 'Successful query using an alias which is a reserved word.');
  }
  public function testSelectReservedWordAliasAllFields() {
    $record = db_select('test', 'high_priority')
      ->fields('high_priority')
      ->condition('age', 27)
      ->execute()
      ->fetchAssoc();
    $this
      ->assertEqual($record['name'], 'George', 'Successful all_fields query using an alias which is a reserved word.');
  }
  public function testInsertReservedWordTable() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {virtual}')
      ->fetchField();
    db_insert('virtual')
      ->fields(array(
      'function' => 'Inserted function',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {virtual}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Successful insert into a table with a name and column which are reserved words.');
  }
  public function testDeleteReservedWordTable() {
    $delete = db_delete('virtual')
      ->condition('function', 'Function value 1');
    $num_deleted = $delete
      ->execute();
    $this
      ->assertEqual($num_deleted, 1, "Deleted 1 record from a table with a name and column which are reserved words..");
  }
  function testTruncateReservedWordTable() {
    db_truncate('virtual')
      ->execute();
    $num_records_after = db_query("SELECT COUNT(*) FROM {virtual}")
      ->fetchField();
    $this
      ->assertEqual(0, $num_records_after, 'Truncated a table with a reserved name.');
  }
  function testUpdateReservedWordTable() {
    $num_updated = db_update('virtual')
      ->fields(array(
      'function' => 'Updated function',
    ))
      ->execute();
    $this
      ->assertIdentical($num_updated, 1, 'Updated 1 record in a table with a name and column which are reserved words.');
  }
  function testMergeReservedWordTable() {
    $key = db_query('SELECT id FROM {virtual} LIMIT 1')
      ->fetchField();
    $num_records_before = db_query('SELECT COUNT(*) FROM {virtual}')
      ->fetchField();
    db_merge('virtual')
      ->key(array(
      'id' => $key,
    ))
      ->fields(array(
      'function' => 'Merged function',
    ))
      ->execute();
    $num_records_after = db_query('SELECT COUNT(*) FROM {virtual}')
      ->fetchField();
    $this
      ->assertIdentical($num_records_before, $num_records_after, 'Successful merge query on a table with a name and column which are reserved words.');
  }

}

/**
 * Test table prefix handling.
*/
class DatabaseTablePrefixTestCase extends DatabaseTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Table prefixes',
      'description' => 'Test handling of table prefixes.',
      'group' => 'Database',
    );
  }
  public function testSchemaDotTablePrefixes() {

    // Get a copy of the default connection options.
    $db = Database::getConnection('default', 'default');
    $connection_options = $db
      ->getConnectionOptions();
    if ($connection_options['driver'] === 'sqlite') {

      // In SQLite simpletest's prefixed db tables exist in their own schema
      // (e.g. simpletest124904.system), so we cannot test the schema.table
      // prefix syntax here.
      $this
        ->assert(TRUE, 'Skipping schema.table prefixed tables test for SQLite.');
      return;
    }
    $db_name = $connection_options['database'];

    // This prefix is usually something like simpletest12345
    $test_prefix = $connection_options['prefix']['default'];

    // Set up a new connection with table prefixes in the form "schema.table"
    $prefixed = $connection_options;
    $prefixed['prefix'] = array(
      'default' => $test_prefix,
      'users' => $db_name . '.' . $test_prefix,
      'role' => $db_name . '.' . $test_prefix,
    );
    Database::addConnectionInfo('default', 'prefixed', $prefixed);

    // Test that the prefixed database connection can query the prefixed tables.
    $num_users_prefixed = Database::getConnection('prefixed', 'default')
      ->query('SELECT COUNT(1) FROM {users}')
      ->fetchField();
    $this
      ->assertTrue((int) $num_users_prefixed > 0, 'Successfully queried the users table using a schema.table prefix');
    $num_users_default = Database::getConnection('default', 'default')
      ->query('SELECT COUNT(1) FROM {users}')
      ->fetchField();
    $this
      ->assertEqual($num_users_default, $num_users_prefixed, 'Verified results of query using a connection with schema.table prefixed tables');
  }

}

Classes

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