drupal_routes/profiles/acquia/modules/sqlsrv/tests/sqlsrv.select.test

<?php

/**
 * @file
 * Support tests for SQL Server.
 */

class SqlServerSelectTest extends DatabaseTestCase {
  /**
   * {inheritdoc}
   */
  public static function getInfo() {
    return array(
      'name' => 'Select tests',
      'description' => 'Extended tests for SELECT handling.',
      'group' => 'Database (SQL Server)',
    );
  }

  /**
   * Test group by expansion.
   */
  public function testGroupByExpansion() {
    // By ANSI SQL, GROUP BY columns cannot use aliases. Test that the
    // driver expands the aliases properly.
    $query = db_select('test_task', 't');
    $count_field = $query->addExpression('COUNT(task)', 'num');
    $task_field = $query->addExpression('CONCAT(:prefix, t.task)', 'task', array(':prefix' => '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(
      'Task: eat' => 1,
      'Task: sleep' => 2,
      'Task: code' => 1,
      'Task: found new band' => 1,
      'Task: 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.'));
    
    // The driver is also verifying that any expressions/columns used in GROUP BY/ORDER BY
    // are added to the select list if they are not because SQL Server will complain
    // about those fields missing in aggregates.
    $query = db_select('test_task', 't')->distinct();
    $query->addField('t', 'task');
    $query->orderBy('t.pid');
    
    try {
      $result = $query->execute();
    } 
    catch (\Exception $e) {
      $this->assert('fail', "Driver failed to deal with missing fields in aggregate: {$e->getMessage()}");
    }
    
    $query = db_select('test_task', 't');
    $query->addField('t', 'task');
    $query->orderBy('t.pid');
    $query->groupBy('t.pid');
    
    try {
      $result = $query->execute();
    } 
    catch (\Exception $e) {
      $this->assert('fail', "Driver failed to deal with missing fields in aggregate: {$e->getMessage()}");
    }
  }
  
  /**
   * Test cross join.
   */
  public function testCrossJoin() {
    // SelectQuery in SQL Server driver
    // is expanding expressions into a cross
    // join statement. This allows the use
    // of these expressions in the Aggregate
    // or Where part of the query.
    $query = db_select('test_task', 't');
    // Cast the task to an accent insensitive collation in an expression.
    $query->addExpression('(t.task collate Latin1_General_CS_AI)', 'ci_task');
    // Add condition over that expression.
    $query->where('ci_task = :param', array(':param' => 'slëep'));

    $result = $query->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this->assertEqual($num_records, 2, t('Returned the correct number of total rows.'));
    
    // There is a special case, if the query is an aggregate
    // and an expression is used, this expression must be part of the aggregate.
    $query = db_select('test_task', 't');
    // Cast the task to an accent insensitive collation in an expression.
    $query->addExpression('(t.task collate Latin1_General_CS_AI)', 'ci_task');
    // Add condition over that expression.
    $query->where('ci_task = :param', array(':param' => 'slëep'));
    // Add condition over that expression.
    $query->groupBy('t.task');
    
    $result = $query->execute();
    $num_records = 0;
    foreach ($result as $record) {
      $num_records++;
    }
    $this->assertEqual($num_records, 1, t('Returned the correct number of total rows.'));
  }

  /**
   * Test the 2100 parameter limit per query.
   */
  public function testParameterLimit() {
    $values = array();
    for ($x = 0; $x < 2200; $x ++) {
      $values[] = uniqid($x, TRUE);
    }
    $query = db_select('test_task', 't');
    $query->addExpression('COUNT(task)', 'num');
    $query->where('t.task IN (:data)', array(':data' => $values));
    $result = NULL;
    // If > 2100 we can get SQL Exception! The driver must handle that.
    try {
      $result = $query->execute()->fetchField();
    }
    catch (\Exception $err) {
    }

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

  /**
   * Although per official documentation you cannot send
   * duplicate placeholders in same query, this works in mySQL
   * and is present in some queries, even in core, wich have not
   * gotten enough attention.
   */
  public function testDuplicatePlaceholders() {
    $query = db_select('test_task', 't');
    $query->addExpression('COUNT(task)', 'num');
    $query->where('t.task IN (:data0, :data0)', array(':data0' => 'sleep'));
    $result = NULL;
    try {
      $result = $query->execute()->fetchField();
    } 
    catch (\Exception $err) { 
    }

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

  /**
   * Test for weird key names
   * in array arguments.
   */
  public function testBadKeysInArrayArguments() {
    $params[':nids'] = array(
        'uid1' => -9,
        'What a bad placeholder name, why should we care?' => -6,
        );
    $result = NULL;
    try {
      // The regular expandArguments implementation will fail to
      // properly expand the associative array with weird keys, OH, and actually
      // you can perform some SQL Injection through the array keys.
      $result = db_query('SELECT COUNT(*) FROM USERS WHERE USERS.UID IN (:nids)', $params)->fetchField();
    } 
    catch (\Exception $err) {
      // Regular drupal will fail with
      // SQLSTATE[IMSSP]: An error occurred substituting the named parameters.
      // https://www.drupal.org/node/2146839
    }

    // User ID's are negative, so this should return 0 matches.
    $this->assertEqual($result, 0, 'Returned the correct number of total rows.');
  }
  
  /**
   * Test the temporary table functionality.
   */
  public function testTemporaryTables() {
    
    $query = db_select('test_task', 't');
    $query->fields('t');
  
    $table = db_query_temporary((string) $query);
    
    // First assert that the table exists
    $this->assertTRUE(db_table_exists($table), 'The temporary table exists.');
    
    $query2 = db_select($table, 't');
    $query2->fields('t');
    
    // Now make sure that both tables are exactly the same.
    $data1 = $query->execute()->fetchAllAssoc('tid');
    $data2 = $query2->execute()->fetchAllAssoc('tid');

    // User ID's are negative, so this should return 0 matches.
    $this->assertEqual(count($data1), count($data2), 'Temporary table has the same number of rows.');
    // $this->assertEqual(count($data1[0]), count($data2[0]), 'Temporary table has the same number of columns.');
    
    // Drop the table.
    db_drop_table($table);
    
    // The table should not exist now.
    $this->assertFALSE(db_table_exists($table), 'The temporary table does not exists.');
  }
  
  /**
   * Test LIKE statement wildcards are properly escaped.
   */
  public function testEscapeLike() {
    // Test expected escaped characters
    $string = 't[e%s]t_\\';
    $expected = 't[[]e[%]s[]]t[_]\\';
    $actual = db_like($string);
    $this->assertEqual($actual, $expected, 'Properly escaped LIKE statement wildcards.');

    db_insert('test_task')
      ->fields(array(
        'task' => 'T\\est',
      ))
      ->execute();

    $query = db_select('test_task', 't');
    $query->fields('t');
    $query->condition('t.task', db_like('T\\est'), 'LIKE');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));

    db_insert('test_task')
      ->fields(array(
        'task' => 'T\'est',
      ))
      ->execute();

    $query = db_select('test_task', 't');
    $query->fields('t');
    $query->condition('t.task', db_like('T\'est'), 'LIKE');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 1, t('db_select returned the correct number of total rows.'));

    // db_select: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query->condition('t.task', '[s]leep', 'LIKE');
    $query->fields('t');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query->condition('t.task', '[s]leep', 'LIKE');
    $query->fields('t');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select: Test escaped wildcard.
    $query = db_select('test_task', 't');
    $query->condition('t.task', db_like('[s]leep'), 'LIKE');
    $query->fields('t');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 0, t('db_select returned the correct number of total rows.'));

    // db_select->where: Test unescaped wildcard.
    $query = db_select('test_task', 't');
    $query->where('t.task LIKE :task', array(':task' => '[s]leep'));
    $query->fields('t');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 2, t('db_select returned the correct number of total rows.'));

    // db_select->where: Test escaped wildcard.
    $query = db_select('test_task', 't');
    $query->where('t.task LIKE :task', array(':task' => db_like('[s]leep')));
    $query->fields('t');
    $result = $query->execute()->fetchAll();
    $this->assertEqual(count($result), 0, t('db_select returned the correct number of total rows.'));

    // db_query: Test unescaped wildcard.
    $query = db_query('SELECT COUNT(*) FROM {test_task} WHERE task LIKE :task',
      array(':task' => '[s]leep'));
    $result = $query->fetchField();
    $this->assertEqual($result, 2, t('db_query returned the correct number of total rows.'));

    // db_query: Test escaped wildcard.
    $query = db_query('SELECT COUNT(*) FROM {test_task} WHERE task LIKE :task',
      array(':task' => db_like('[s]leep')));
    $result = $query->fetchField();
    $this->assertEqual($result, 0, t('db_query returned the correct number of total rows.'));
  }
}