You are here

Home » API reference » SimpleTest 7 » database_test.test

class DatabaseSelectComplexTestCase in SimpleTest 7

Test more complex select statements.

Hierarchy

Expanded class hierarchy of DatabaseSelectComplexTestCase

File

tests/database_test.test, line 1650

View source 
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, t('Results returned in correct order.'));
      $this
        ->assertNotEqual($record->{$name_field}, 'Ringo', t('Taskless person not selected.'));
      $last_priority = $record->{$priority_field};
    }
    $this
      ->assertEqual($num_records, 7, t('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, t('Results returned in correct order.'));
      $last_priority = $record->{$name_field};
    }
    $this
      ->assertEqual($num_records, 8, t('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, t('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, t("Correct number of '@task' records found.", array(
        '@task' => $task,
      )));
    }
    $this
      ->assertEqual($num_records, 6, t('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
      ->havingCondition('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, t('Record has the minimum count.'));
      $this
        ->assertTrue($record->{$count_field} >= $last_count, t('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, t("Correct number of '@task' records found.", array(
        '@task' => $task,
      )));
    }
    $this
      ->assertEqual($num_records, 1, t('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, t('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, t('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, t('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', t('Correct data retrieved.'));
    $this
      ->assertEqual($record->{$age_field}, 27, t('Correct data retrieved.'));
  }

  /**
   * 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', t('Correct data retrieved.'));
  }

}

Members

Namesort descending Modifiers Type Description Overrides
DatabaseSelectComplexTestCase::getInfo public static function
DatabaseSelectComplexTestCase::testCountQuery function Test that we can generate a count query from a built query.
DatabaseSelectComplexTestCase::testDefaultJoin function Test simple JOIN statements.
DatabaseSelectComplexTestCase::testDistinct function Test distinct queries.
DatabaseSelectComplexTestCase::testGroupBy function Test GROUP BY clauses.
DatabaseSelectComplexTestCase::testGroupByAndHaving function Test GROUP BY and HAVING clauses together.
DatabaseSelectComplexTestCase::testLeftOuterJoin function Test LEFT OUTER joins.
DatabaseSelectComplexTestCase::testNestedConditions function Confirm that we can properly nest conditional clauses.
DatabaseSelectComplexTestCase::testRange function Test range queries. The SQL clause varies with the database.
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