BasicSyntaxTest.php 4.14 KB
Newer Older
1 2
<?php

3
namespace Drupal\KernelTests\Core\Database;
4 5

/**
6
 * Tests SQL syntax interpretation.
7 8 9 10
 *
 * 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.
11 12
 *
 * @group Database
13 14
 */
class BasicSyntaxTest extends DatabaseTestBase {
15

16
  /**
17
   * Tests string concatenation.
18
   */
19
  public function testConcatLiterals() {
20
    $result = db_query('SELECT CONCAT(:a1, CONCAT(:a2, CONCAT(:a3, CONCAT(:a4, :a5))))', [
21 22 23 24 25
      ':a1' => 'This',
      ':a2' => ' ',
      ':a3' => 'is',
      ':a4' => ' a ',
      ':a5' => 'test.',
26
    ]);
27
    $this->assertIdentical($result->fetchField(), 'This is a test.', 'Basic CONCAT works.');
28 29 30
  }

  /**
31
   * Tests string concatenation with field values.
32 33 34 35 36
   *
   * We use 'job' and 'age' fields from the {test} table. Using the 'name' field
   * for concatenation causes issues with custom or contrib database drivers,
   * since its type 'varchar_ascii' may lead to using field-level collations not
   * compatible with the other fields.
37
   */
38
  public function testConcatFields() {
39 40 41 42 43 44 45 46 47
    $result = $this->connection->query(
      'SELECT CONCAT(:a1, CONCAT(job, CONCAT(:a2, CONCAT(age, :a3)))) FROM {test} WHERE age = :age', [
        ':a1' => 'The age of ',
        ':a2' => ' is ',
        ':a3' => '.',
        ':age' => 25,
      ]
    );
    $this->assertSame('The age of Singer is 25.', $result->fetchField(), 'Field CONCAT works.');
48 49
  }

50 51 52
  /**
   * Tests string concatenation with separator.
   */
53
  public function testConcatWsLiterals() {
54
    $result = db_query("SELECT CONCAT_WS(', ', :a1, NULL, :a2, :a3, :a4)", [
55 56 57 58
      ':a1' => 'Hello',
      ':a2' => NULL,
      ':a3' => '',
      ':a4' => 'world.',
59
    ]);
60 61 62 63 64 65
    $this->assertIdentical($result->fetchField(), 'Hello, , world.');
  }

  /**
   * Tests string concatenation with separator, with field values.
   */
66
  public function testConcatWsFields() {
67
    $result = db_query("SELECT CONCAT_WS('-', :a1, name, :a2, age) FROM {test} WHERE age = :age", [
68 69 70
      ':a1' => 'name',
      ':a2' => 'age',
      ':age' => 25,
71
    ]);
72 73 74
    $this->assertIdentical($result->fetchField(), 'name-John-age-25');
  }

75
  /**
76
   * Tests escaping of LIKE wildcards.
77
   */
78
  public function testLikeEscape() {
79
    db_insert('test')
80
      ->fields([
81
        'name' => 'Ring_',
82
      ])
83 84 85 86 87 88 89 90
      ->execute();

    // Match both "Ringo" and "Ring_".
    $num_matches = db_select('test', 't')
      ->condition('name', 'Ring_', 'LIKE')
      ->countQuery()
      ->execute()
      ->fetchField();
91
    $this->assertIdentical($num_matches, '2', 'Found 2 records.');
92 93 94 95 96 97
    // 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();
98
    $this->assertIdentical($num_matches, '1', 'Found 1 record.');
99 100 101
  }

  /**
102
   * Tests a LIKE query containing a backslash.
103
   */
104
  public function testLikeBackslash() {
105
    db_insert('test')
106 107
      ->fields(['name'])
      ->values([
108
        'name' => 'abcde\f',
109 110
      ])
      ->values([
111
        'name' => 'abc%\_',
112
      ])
113 114 115 116 117 118 119 120 121
      ->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();
122
    $this->assertIdentical($num_matches, '2', 'Found 2 records.');
123 124 125 126 127 128
    // 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();
129
    $this->assertIdentical($num_matches, '1', 'Found 1 record.');
130
  }
131 132 133 134 135 136 137 138 139 140 141 142 143

  /**
   * Tests \Drupal\Core\Database\Connection::getFullQualifiedTableName().
   */
  public function testGetFullQualifiedTableName() {
    $database = \Drupal::database();
    $num_matches = $database->select($database->getFullQualifiedTableName('test'), 't')
      ->countQuery()
      ->execute()
      ->fetchField();
    $this->assertIdentical($num_matches, '4', 'Found 4 records.');
  }

144
}