SelectTest.php 17.4 KB
Newer Older
1
2
3
4
5
6
7
8
<?php

/**
 * @file
 * Definition of Drupal\system\Tests\Database\SelectTest.
 */

namespace Drupal\system\Tests\Database;
9
use Drupal\Core\Database\InvalidQueryException;
10
11

/**
12
 * Tests the SELECT builder.
13
14
15
16
17
18
19
20
21
22
23
24
 */
class SelectTest extends DatabaseTestBase {

  public static function getInfo() {
    return array(
      'name' => 'Select tests',
      'description' => 'Test the Select query builder.',
      'group' => 'Database',
    );
  }

  /**
25
   * Tests rudimentary SELECT statements.
26
27
28
29
30
31
32
33
34
35
36
37
   */
  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++;
    }

38
    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
39
40
41
  }

  /**
42
   * Tests rudimentary SELECT statement with a COMMENT.
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
   */
  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";

58
59
    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this->assertEqual($query, $expected, 'The flattened query contains the comment string.');
60
61
62
  }

  /**
63
   * Tests query COMMENT system against vulnerabilities.
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
   */
  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";

79
80
    $this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
    $this->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');
81
82
83
  }

  /**
84
   * Tests basic conditionals on SELECT statements.
85
86
87
88
89
90
91
92
93
   */
  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.
94
95
    $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
    $this->assertEqual($age_field, 'age', 'Age field alias is correct.');
96
97
98

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

  /**
104
   * Tests SELECT statements with expressions.
105
106
107
108
109
110
111
112
113
   */
  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.
114
115
    $this->assertEqual($name_field, 'name', 'Name field alias is correct.');
    $this->assertEqual($age_field, 'double_age', 'Age field alias is correct.');
116
117
118

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

  /**
124
   * Tests SELECT statements with multiple expressions.
125
126
127
128
129
130
131
132
133
134
   */
  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.
135
136
    $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.');
137
138
139

    // Ensure that we got the right record.
    $record = $result->fetch();
140
141
142
    $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.');
143
144
145
  }

  /**
146
   * Tests adding multiple fields to a SELECT statement at the same time.
147
148
149
150
151
152
153
154
   */
  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.
155
156
157
158
    $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.');
159
160
161

    // Ensure that we got the right record.
    // Check that all fields we asked for are present.
162
163
164
165
    $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.');
166
167
168
  }

  /**
169
   * Tests adding all fields from a given table to a SELECT statement.
170
171
172
173
174
175
176
177
   */
  function testSimpleSelectAllFields() {
    $record = db_select('test')
      ->fields('test')
      ->condition('age', 27)
      ->execute()->fetchObject();

    // Check that all fields we asked for are present.
178
179
180
181
    $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.');
182
183
184

    // Ensure that we got the right record.
    // Check that all fields we asked for are present.
185
186
187
188
    $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.');
189
190
191
  }

  /**
192
   * Tests that a comparison with NULL is always FALSE.
193
194
195
196
197
198
199
200
201
   */
  function testNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->condition('age', NULL)
      ->execute()->fetchCol();

202
    $this->assertEqual(count($names), 0, 'No records found when comparing to NULL.');
203
204
205
  }

  /**
206
   * Tests that we can find a record with a NULL value.
207
208
209
210
211
212
213
214
215
   */
  function testIsNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->isNull('age')
      ->execute()->fetchCol();

216
217
    $this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
    $this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
218
219
220
  }

  /**
221
   * Tests that we can find a record without a NULL value.
222
223
224
225
226
227
228
229
230
231
   */
  function testIsNotNullCondition() {
    $this->ensureSampleDataNull();

    $names = db_select('test_null', 'tn')
      ->fields('tn', array('name'))
      ->isNotNull('tn.age')
      ->orderBy('name')
      ->execute()->fetchCol();

232
233
234
    $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.');
235
236
237
  }

  /**
238
239
240
241
   * Tests that we can UNION multiple Select queries together.
   *
   * This is semantically equal to UNION DISTINCT, so we don't explicity test
   * that.
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
   */
  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.
257
    $this->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
258

259
260
    $this->assertEqual($names[0], 'George', 'First query returned correct name.');
    $this->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
261
262
263
  }

  /**
264
   * Tests that we can UNION ALL multiple SELECT queries together.
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
   */
  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.
280
    $this->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
281

282
283
284
    $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.');
285
286
  }

287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
  /**
   * Tests that we can get a count query for a UNION Select query.
   */
  function testUnionCount() {
    $query_1 = db_select('test', 't')
      ->fields('t', array('name', 'age'))
      ->condition('age', array(27, 28), 'IN');

    $query_2 = db_select('test', 't')
      ->fields('t', array('name', 'age'))
      ->condition('age', 28);

    $query_1->union($query_2, 'ALL');
    $names = $query_1->execute()->fetchCol();

    $query_3 = $query_1->countQuery();
    $count = $query_3->execute()->fetchField();

    // Ensure the counts match.
    $this->assertEqual(count($names), $count, "The count query's result matched the number of rows in the UNION query.");
  }

309
  /**
310
   * Tests that random ordering of queries works.
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
   *
   * 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();
341
    $this->assertEqual($ordered_ids, $expected_ids, 'A query without random ordering returns IDs in the correct order.');
342
343
344
345
346
347
348
349
350
351

    // 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();
352
    $this->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
353
354
    $sorted_ids = $randomized_ids;
    sort($sorted_ids);
355
    $this->assertEqual($sorted_ids, $ordered_ids, 'After sorting the random list, the result matches the original query.');
356
357
358
359
360
361
362
363
364

    // 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();
365
    $this->assertNotEqual($randomized_ids_second_set, $randomized_ids, 'Performing the query with random ordering a second time returns IDs in a different order.');
366
367
    $sorted_ids_second_set = $randomized_ids_second_set;
    sort($sorted_ids_second_set);
368
    $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.');
369
370
  }

371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
  /**
   * Tests that filter by a regular expression works as expected.
   */
  public function testRegexCondition() {

    $test_groups[] = array(
      'regex' => 'hn$',
      'expected' => array(
        'John',
      ),
    );
    $test_groups[] = array(
      'regex' => '^Pau',
      'expected' => array(
        'Paul',
      ),
    );
    $test_groups[] = array(
      'regex' => 'Ringo|George',
      'expected' => array(
        'Ringo', 'George',
      ),
    );


    $database = $this->container->get('database');
    foreach ($test_groups as $test_group) {
      $query = $database->select('test', 't');
      $query->addField('t', 'name');
      $query->condition('t.name', $test_group['regex'], 'REGEXP');
      $result = $query->execute()->fetchCol();

      $this->assertEqual(count($result), count($test_group['expected']), 'Returns the expected number of rows.');
      $this->assertEqual(sort($result), sort($test_group['expected']), 'Returns the expected rows.');
    }

    // Ensure that filter by "#" still works due to the quoting.
    $database->insert('test')
      ->fields(array(
        'name' => 'Pete',
        'age' => 26,
        'job' => '#Drummer',
      ))
      ->execute();

    $test_groups = array();
    $test_groups[] = array(
      'regex' => '#Drummer',
      'expected' => array(
        'Pete',
      ),
    );
    $test_groups[] = array(
      'regex' => '#Singer',
      'expected' => array(
      ),
    );

    foreach ($test_groups as $test_group) {
      $query = $database->select('test', 't');
      $query->addField('t', 'name');
      $query->condition('t.job', $test_group['regex'], 'REGEXP');
      $result = $query->execute()->fetchCol();

      $this->assertEqual(count($result), count($test_group['expected']), 'Returns the expected number of rows.');
      $this->assertEqual(sort($result), sort($test_group['expected']), 'Returns the expected rows.');
    }
  }

440
  /**
441
   * Tests that aliases are renamed when they are duplicates.
442
443
444
445
446
447
448
   */
  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.');
  }
449
450
451
452
453
454
455

  /**
   * Tests that an invalid merge query throws an exception.
   */
  function testInvalidSelectCount() {
    try {
      // This query will fail because the table does not exist.
456
      // Normally it would throw an exception but we are suppressing
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
      // it with the throw_exception option.
      $options['throw_exception'] = FALSE;
      db_select('some_table_that_doesnt_exist', 't', $options)
        ->fields('t')
        ->countQuery()
        ->execute();

      $this->pass('$options[\'throw_exception\'] is FALSE, no Exception thrown.');
    }
    catch (\Exception $e) {
      $this->fail('$options[\'throw_exception\'] is FALSE, but Exception thrown for invalid query.');
      return;
    }

    try {
      // This query will fail because the table does not exist.
      db_select('some_table_that_doesnt_exist', 't')
        ->fields('t')
        ->countQuery()
        ->execute();
    }
    catch (\Exception $e) {
      $this->pass('Exception thrown for invalid query.');
      return;
    }
    $this->fail('No Exception thrown.');
  }

485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
  /**
   * Tests thrown exception for IN query conditions with an empty array.
   */
  function testEmptyInCondition() {
    try {
      db_select('test', 't')
        ->fields('t')
        ->condition('age', array(), 'IN')
        ->execute();

      $this->fail('Expected exception not thrown');
    }
    catch (InvalidQueryException $e) {
      $this->assertEqual("Query condition 'age IN ()' cannot be empty.", $e->getMessage());
    }

    try {
      db_select('test', 't')
        ->fields('t')
        ->condition('age', array(), 'NOT IN')
        ->execute();

      $this->fail('Expected exception not thrown');
    }
    catch (InvalidQueryException $e) {
      $this->assertEqual("Query condition 'age NOT IN ()' cannot be empty.", $e->getMessage());
    }
  }

514
}