class DatabaseSelectTestCase in SimpleTest 7

Test the SELECT builder.

Hierarchy

• class \DrupalTestCase
  • class \DrupalWebTestCase
    • class \DatabaseTestCase
      • class \DatabaseSelectTestCase

File

tests/database_test.test, line 1193

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, t('Returned the correct number of rows.'));
  }

  /**
   * 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', t('Name field alias is correct.'));
    $this
      ->assertEqual($age_field, 'age', t('Age field alias is correct.'));

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', t('Fetched name is correct.'));
    $this
      ->assertEqual($record->{$age_field}, 27, t('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', t('Name field alias is correct.'));
    $this
      ->assertEqual($age_field, 'double_age', t('Age field alias is correct.'));

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', t('Fetched name is correct.'));
    $this
      ->assertEqual($record->{$age_field}, 27 * 2, t('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', t('Double age field alias is correct.'));
    $this
      ->assertEqual($age_triple_field, 'expression_2', t('Triple age field alias is correct.'));

    // Ensure that we got the right record.
    $record = $result
      ->fetch();
    $this
      ->assertEqual($record->{$name_field}, 'George', t('Fetched name is correct.'));
    $this
      ->assertEqual($record->{$age_double_field}, 27 * 2, t('Fetched double age expression is correct.'));
    $this
      ->assertEqual($record->{$age_triple_field}, 27 * 3, t('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, t('ID field is present.'));
    $this
      ->assertNotNull($record->name, t('Name field is present.'));
    $this
      ->assertNotNull($record->age, t('Age field is present.'));
    $this
      ->assertNotNull($record->job, t('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, t('ID field has the correct value.'));
    $this
      ->assertEqual($record->name, 'George', t('Name field has the correct value.'));
    $this
      ->assertEqual($record->age, 27, t('Age field has the correct value.'));
    $this
      ->assertEqual($record->job, 'Singer', t('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, t('ID field is present.'));
    $this
      ->assertNotNull($record->name, t('Name field is present.'));
    $this
      ->assertNotNull($record->age, t('Age field is present.'));
    $this
      ->assertNotNull($record->job, t('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, t('ID field has the correct value.'));
    $this
      ->assertEqual($record->name, 'George', t('Name field has the correct value.'));
    $this
      ->assertEqual($record->age, 27, t('Age field has the correct value.'));
    $this
      ->assertEqual($record->job, 'Singer', t('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, t('Correct number of records found with NULL age.'));
    $this
      ->assertEqual($names[0], 'Fozzie', t('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, t('Correct number of records found withNOT NULL age.'));
    $this
      ->assertEqual($names[0], 'Gonzo', t('Correct record returned for NOT NULL age.'));
    $this
      ->assertEqual($names[1], 'Kermit', t('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, t('UNION correctly discarded duplicates.'));
    $this
      ->assertEqual($names[0], 'George', t('First query returned correct name.'));
    $this
      ->assertEqual($names[1], 'Ringo', t('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, t('UNION ALL correctly preserved duplicates.'));
    $this
      ->assertEqual($names[0], 'George', t('First query returned correct first name.'));
    $this
      ->assertEqual($names[1], 'Ringo', t('Second query returned correct second name.'));
    $this
      ->assertEqual($names[2], 'Ringo', t('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, t('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, t('A query with random ordering returns an unordered set of IDs.'));
    $sorted_ids = $randomized_ids;
    sort($sorted_ids);
    $this
      ->assertEqual($sorted_ids, $ordered_ids, t('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, t('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, t('After sorting the second random list, the result matches the sorted version of the first random list.'));
  }

}

Members

Name	Modifiers	Type	Description	Overrides
DatabaseSelectTestCase::getInfo	public static	function
DatabaseSelectTestCase::testNotNullCondition		function	Test that we can find a record without a NULL value.
DatabaseSelectTestCase::testNullCondition		function	Test that we can find a record with a NULL value.
DatabaseSelectTestCase::testRandomOrder		function	Test that random ordering of queries works.
DatabaseSelectTestCase::testSimpleSelect		function	Test rudimentary SELECT statements.
DatabaseSelectTestCase::testSimpleSelectAllFields		function	Test adding all fields from a given table to a select statement.
DatabaseSelectTestCase::testSimpleSelectConditional		function	Test basic conditionals on SELECT statements.
DatabaseSelectTestCase::testSimpleSelectExpression		function	Test SELECT statements with expressions.
DatabaseSelectTestCase::testSimpleSelectExpressionMultiple		function	Test SELECT statements with multiple expressions.
DatabaseSelectTestCase::testSimpleSelectMultipleFields		function	Test adding multiple fields to a select statement at the same time.
DatabaseSelectTestCase::testUnion		function	Test that we can UNION multiple Select queries together. This is semantically equal to UNION DISTINCT, so we don't explicity test that.
DatabaseSelectTestCase::testUnionAll		function	Test that we can UNION ALL multiple Select queries together.
DatabaseTestCase::addSampleData		function	Setup our sample data.
DatabaseTestCase::ensureSampleDataNull		function	Set up tables for NULL handling.
DatabaseTestCase::installTables		function	Set up several tables needed by a certain test.
DatabaseTestCase::setUp		function	Generates a random database prefix, runs the install scripts on the prefixed database and enable the specified modules. After installation many caches are flushed and the internal browser is setup so that the page requests will run on the new prefix.… Overrides DrupalWebTestCase::setUp	4
DrupalTestCase::$assertions	protected	property	Assertions thrown in that test case.
DrupalTestCase::$originalFileDirectory	protected	property	The original file directory, before it was changed for testing purposes.
DrupalTestCase::$originalPrefix	protected	property	The original database prefix, before it was changed for testing purposes.
DrupalTestCase::$results	public	property	Current results of this test case.
DrupalTestCase::$skipClasses	protected	property	This class is skipped when looking for the source of an assertion.
DrupalTestCase::$testId	protected	property	The test run ID.
DrupalTestCase::$timeLimit	protected	property	Time limit for the test.
DrupalTestCase::assert	protected	function	Internal helper: stores the assert.
DrupalTestCase::assertEqual	protected	function	Check to see if two values are equal.
DrupalTestCase::assertFalse	protected	function	Check to see if a value is false (an empty string, 0, NULL, or FALSE).
DrupalTestCase::assertIdentical	protected	function	Check to see if two values are identical.
DrupalTestCase::assertNotEqual	protected	function	Check to see if two values are not equal.
DrupalTestCase::assertNotIdentical	protected	function	Check to see if two values are not identical.
DrupalTestCase::assertNotNull	protected	function	Check to see if a value is not NULL.
DrupalTestCase::assertNull	protected	function	Check to see if a value is NULL.
DrupalTestCase::assertTrue	protected	function	Check to see if a value is not false (not an empty string, 0, NULL, or FALSE).
DrupalTestCase::error	protected	function	Fire an error assertion.	1
DrupalTestCase::errorHandler	public	function	Handle errors.
DrupalTestCase::exceptionHandler	protected	function	Handle exceptions.
DrupalTestCase::fail	protected	function	Fire an assertion that is always negative.
DrupalTestCase::getAssertionCall	protected	function	Cycles through backtrace until the first non-assertion method is found.
DrupalTestCase::insertAssert	public static	function	Store an assertion from outside the testing context.
DrupalTestCase::pass	protected	function	Fire an assertion that is always positive.
DrupalTestCase::randomName	public static	function	Generates a random string containing letters and numbers.
DrupalTestCase::randomString	public static	function	Generates a random string of ASCII characters of codes 32 to 126.
DrupalTestCase::run	public	function	Run all tests in this class.
DrupalWebTestCase::$additionalCurlOptions	protected	property	Additional cURL options.
DrupalWebTestCase::$content	protected	property	The content of the page currently loaded in the internal browser.
DrupalWebTestCase::$cookieFile	protected	property	The current cookie file used by cURL.
DrupalWebTestCase::$curlHandle	protected	property	The handle of the current cURL connection.
DrupalWebTestCase::$elements	protected	property	The parsed version of the page.
DrupalWebTestCase::$headers	protected	property	The headers of the page currently loaded in the internal browser.
DrupalWebTestCase::$httpauth_credentials	protected	property	HTTP authentication credentials (<username>:<password>).
DrupalWebTestCase::$loggedInUser	protected	property	The current user logged in using the internal browser.
DrupalWebTestCase::$originalUser	protected	property	The original user, before it was changed to a clean uid = 1 for testing purposes.
DrupalWebTestCase::$plainTextContent	protected	property	The content of the page currently loaded in the internal browser (plain text version).
DrupalWebTestCase::$session_id	protected	property	The current session ID, if available.
DrupalWebTestCase::$session_name	protected	property	The current session name, if available.
DrupalWebTestCase::$url	protected	property	The URL currently loaded in the internal browser.
DrupalWebTestCase::assertField	protected	function	Assert that a field exists with the given name or id.
DrupalWebTestCase::assertFieldById	protected	function	Assert that a field exists in the current page with the given id and value.
DrupalWebTestCase::assertFieldByName	protected	function	Assert that a field exists in the current page with the given name and value.
DrupalWebTestCase::assertFieldByXPath	protected	function	Assert that a field exists in the current page by the given XPath.
DrupalWebTestCase::assertFieldChecked	protected	function	Assert that a checkbox field in the current page is checked.
DrupalWebTestCase::assertLink	protected	function	Pass if a link with the specified label is found, and optional with the specified index.
DrupalWebTestCase::assertMail	protected	function	Assert that the most recently sent e-mail message has a field with the given value.
DrupalWebTestCase::assertNoField	protected	function	Assert that a field does not exist with the given name or id.
DrupalWebTestCase::assertNoFieldById	protected	function	Assert that a field does not exist with the given id and value.
DrupalWebTestCase::assertNoFieldByName	protected	function	Assert that a field does not exist with the given name and value.
DrupalWebTestCase::assertNoFieldByXPath	protected	function	Assert that a field does not exist in the current page by the given XPath.
DrupalWebTestCase::assertNoFieldChecked	protected	function	Assert that a checkbox field in the current page is not checked.
DrupalWebTestCase::assertNoLink	protected	function	Pass if a link with the specified label is not found.
DrupalWebTestCase::assertNoPattern	protected	function	Will trigger a pass if the perl regex pattern is not present in raw content.
DrupalWebTestCase::assertNoRaw	protected	function	Pass if the raw text is NOT found on the loaded page, fail otherwise. Raw text refers to the raw HTML that the page generated.
DrupalWebTestCase::assertNoText	protected	function	Pass if the text is NOT found on the text version of the page. The text version is the equivalent of what a user would see when viewing through a web browser. In other words the HTML has been filtered out of the contents.
DrupalWebTestCase::assertNoTitle	protected	function	Pass if the page title is not the given string.
DrupalWebTestCase::assertNoUniqueText	protected	function	Pass if the text is found MORE THAN ONCE on the text version of the page.
DrupalWebTestCase::assertPattern	protected	function	Will trigger a pass if the Perl regex pattern is found in the raw content.
DrupalWebTestCase::assertRaw	protected	function	Pass if the raw text IS found on the loaded page, fail otherwise. Raw text refers to the raw HTML that the page generated.
DrupalWebTestCase::assertResponse	protected	function	Assert the page responds with the specified response code.
DrupalWebTestCase::assertText	protected	function	Pass if the text IS found on the text version of the page. The text version is the equivalent of what a user would see when viewing through a web browser. In other words the HTML has been filtered out of the contents.
DrupalWebTestCase::assertTextHelper	protected	function	Helper for assertText and assertNoText.
DrupalWebTestCase::assertTitle	protected	function	Pass if the page title is the given string.
DrupalWebTestCase::assertUniqueText	protected	function	Pass if the text is found ONLY ONCE on the text version of the page.
DrupalWebTestCase::assertUniqueTextHelper	protected	function	Helper for assertUniqueText and assertNoUniqueText.
DrupalWebTestCase::checkForMetaRefresh	protected	function	Check for meta refresh tag and if found call drupalGet() recursively. This function looks for the http-equiv attribute to be set to "Refresh" and is case-sensitive.
DrupalWebTestCase::checkPermissions	protected	function	Check to make sure that the array of permissions are valid.
DrupalWebTestCase::clickLink	protected	function	Follows a link by name.
DrupalWebTestCase::constructFieldXpath	protected	function	Helper function: construct an XPath for the given set of attributes and value.
DrupalWebTestCase::cronRun	protected	function	Runs cron in the Drupal installed by Simpletest.
DrupalWebTestCase::curlClose	protected	function	Close the cURL handler and unset the handler.
DrupalWebTestCase::curlExec	protected	function	Performs a cURL exec with the specified options after calling curlConnect().
DrupalWebTestCase::curlHeaderCallback	protected	function	Reads headers and registers errors received from the tested site.
DrupalWebTestCase::curlInitialize	protected	function	Initializes the cURL connection.
DrupalWebTestCase::drupalCompareFiles	protected	function	Compare two files based on size and file name.
DrupalWebTestCase::drupalCreateContentType	protected	function	Creates a custom content type based on default settings.
DrupalWebTestCase::drupalCreateNode	protected	function	Creates a node based on default settings.
DrupalWebTestCase::drupalCreateRole	protected	function	Internal helper function; Create a role with specified permissions.
DrupalWebTestCase::drupalCreateUser	protected	function	Create a user with a given set of permissions. The permissions correspond to the names given on the privileges page.
DrupalWebTestCase::drupalGet	protected	function	Retrieves a Drupal path or an absolute path.
DrupalWebTestCase::drupalGetContent	protected	function	Gets the current raw HTML of requested page.
DrupalWebTestCase::drupalGetHeader	protected	function	Gets the value of an HTTP response header. If multiple requests were required to retrieve the page, only the headers from the last request will be checked by default. However, if TRUE is passed as the second argument, all requests will be processed…
DrupalWebTestCase::drupalGetHeaders	protected	function	Gets the HTTP response headers of the requested page. Normally we are only interested in the headers returned by the last request. However, if a page is redirected or HTTP authentication is in use, multiple requests will be required to retrieve the…
DrupalWebTestCase::drupalGetMails	protected	function	Gets an array containing all e-mails sent during this test case.
DrupalWebTestCase::drupalGetNodeByTitle		function	Get a node from the database based on its title.
DrupalWebTestCase::drupalGetTestFiles	protected	function	Get a list files that can be used in tests.
DrupalWebTestCase::drupalGetToken	protected	function	Generate a token for the currently logged in user.
DrupalWebTestCase::drupalHead	protected	function	Retrieves only the headers for a Drupal path or an absolute path.
DrupalWebTestCase::drupalLogin	protected	function	Log in a user with the internal browser.
DrupalWebTestCase::drupalLogout	protected	function
DrupalWebTestCase::drupalPost	protected	function	Execute a POST request on a Drupal page. It will be done as usual POST request with SimpleBrowser.
DrupalWebTestCase::drupalSetContent	protected	function	Sets the raw HTML content. This can be useful when a page has been fetched outside of the internal browser and assertions need to be made on the returned page.
DrupalWebTestCase::getAbsoluteUrl	protected	function	Takes a path and returns an absolute path.
DrupalWebTestCase::getAllOptions	protected	function	Get all option elements, including nested options, in a select.
DrupalWebTestCase::getSelectedItem	protected	function	Get the selected value from a select field.
DrupalWebTestCase::getUrl	protected	function	Get the current url from the cURL handler.
DrupalWebTestCase::handleForm	protected	function	Handle form input related to drupalPost(). Ensure that the specified fields exist and attempt to create POST data in the correct manner for the particular field type.
DrupalWebTestCase::parse	protected	function	Parse content returned from curlExec using DOM and SimpleXML.
DrupalWebTestCase::preloadRegistry	protected	function	This method is called by DrupalWebTestCase::setUp, and preloads the registry from the testing site to cut down on the time it takes to setup a clean environment for the current test run.
DrupalWebTestCase::refreshVariables	protected	function	Refresh the in-memory set of variables. Useful after a page request is made that changes a variable in a different thread.
DrupalWebTestCase::tearDown	protected	function	Delete created files and temporary files directory, delete the tables created by setUp(), and reset the database prefix.	3
DrupalWebTestCase::verbose	protected	function	Log verbose message in a text file.
DrupalWebTestCase::xpath	protected	function	Perform an xpath search on the contents of the internal browser. The search is relative to the root element (HTML tag normally) of the page.
DrupalWebTestCase::__construct		function	Constructor for DrupalWebTestCase. Overrides DrupalTestCase::__construct