Newer
Older
<?php
/**
* @file
* Definition of Drupal\system\Tests\Database\SelectTest.
*/
namespace Drupal\system\Tests\Database;
use Drupal\Core\Database\InvalidQueryException;
/**
* Tests the Select query builder.
*
* @group Database
*/
class SelectTest extends DatabaseTestBase {
/**
Jennifer Hodgdon
committed
* Tests 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++;
}
Jennifer Hodgdon
committed
$this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
}
/**
Jennifer Hodgdon
committed
* Tests rudimentary SELECT statement with a COMMENT.
*/
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";
Jennifer Hodgdon
committed
$this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
$this->assertEqual($query, $expected, 'The flattened query contains the comment string.');
}
/**
Jennifer Hodgdon
committed
* Tests query COMMENT system against vulnerabilities.
*/
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";
Jennifer Hodgdon
committed
$this->assertEqual($num_records, 4, 'Returned the correct number of rows.');
$this->assertEqual($query, $expected, 'The flattened query contains the sanitised comment string.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$this->assertEqual($name_field, 'name', 'Name field alias is correct.');
$this->assertEqual($age_field, 'age', 'Age field alias is correct.');
// Ensure that we got the right record.
$record = $result->fetch();
Jennifer Hodgdon
committed
$this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
$this->assertEqual($record->$age_field, 27, 'Fetched age is correct.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$this->assertEqual($name_field, 'name', 'Name field alias is correct.');
$this->assertEqual($age_field, 'double_age', 'Age field alias is correct.');
// Ensure that we got the right record.
$record = $result->fetch();
Jennifer Hodgdon
committed
$this->assertEqual($record->$name_field, 'George', 'Fetched name is correct.');
$this->assertEqual($record->$age_field, 27*2, 'Fetched age expression is correct.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$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.');
// Ensure that we got the right record.
$record = $result->fetch();
Jennifer Hodgdon
committed
$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.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$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.');
// Ensure that we got the right record.
// Check that all fields we asked for are present.
Jennifer Hodgdon
committed
$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.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$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.');
// Ensure that we got the right record.
// Check that all fields we asked for are present.
Jennifer Hodgdon
committed
$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.');
}
/**
Jennifer Hodgdon
committed
* Tests that a comparison with NULL is always FALSE.
*/
function testNullCondition() {
$this->ensureSampleDataNull();
$names = db_select('test_null', 'tn')
->fields('tn', array('name'))
->condition('age', NULL)
->execute()->fetchCol();
Jennifer Hodgdon
committed
$this->assertEqual(count($names), 0, 'No records found when comparing to NULL.');
}
/**
Jennifer Hodgdon
committed
* Tests that we can find a record with a NULL value.
*/
function testIsNullCondition() {
$this->ensureSampleDataNull();
$names = db_select('test_null', 'tn')
->fields('tn', array('name'))
->isNull('age')
->execute()->fetchCol();
Jennifer Hodgdon
committed
$this->assertEqual(count($names), 1, 'Correct number of records found with NULL age.');
$this->assertEqual($names[0], 'Fozzie', 'Correct record returned for NULL age.');
}
/**
Jennifer Hodgdon
committed
* Tests that we can find a record without a NULL value.
*/
function testIsNotNullCondition() {
$this->ensureSampleDataNull();
$names = db_select('test_null', 'tn')
->fields('tn', array('name'))
->isNotNull('tn.age')
->orderBy('name')
->execute()->fetchCol();
Jennifer Hodgdon
committed
$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.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$this->assertEqual(count($names), 2, 'UNION correctly discarded duplicates.');
Jennifer Hodgdon
committed
$this->assertEqual($names[0], 'George', 'First query returned correct name.');
$this->assertEqual($names[1], 'Ringo', 'Second query returned correct name.');
}
/**
Jennifer Hodgdon
committed
* Tests 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.
Jennifer Hodgdon
committed
$this->assertEqual(count($names), 3, 'UNION ALL correctly preserved duplicates.');
Jennifer Hodgdon
committed
$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.');
}
/**
* 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.");
}
/**
Jennifer Hodgdon
committed
* Tests that random ordering of queries works.
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
*
* 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();
Jennifer Hodgdon
committed
$this->assertEqual($ordered_ids, $expected_ids, '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();
Jennifer Hodgdon
committed
$this->assertNotEqual($randomized_ids, $ordered_ids, 'A query with random ordering returns an unordered set of IDs.');
$sorted_ids = $randomized_ids;
sort($sorted_ids);
Jennifer Hodgdon
committed
$this->assertEqual($sorted_ids, $ordered_ids, '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();
Jennifer Hodgdon
committed
$this->assertNotEqual($randomized_ids_second_set, $randomized_ids, '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);
Jennifer Hodgdon
committed
$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.');
}
catch
committed
365
366
367
368
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
/**
* 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.');
}
}
/**
Jennifer Hodgdon
committed
* Tests that aliases are renamed when they are duplicates.
*/
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.');
}
/**
* Tests that an invalid merge query throws an exception.
*/
function testInvalidSelectCount() {
try {
// This query will fail because the table does not exist.
Jennifer Hodgdon
committed
// Normally it would throw an exception but we are suppressing
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
// 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.');
}
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
/**
* 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());
}
}