From c89b9ed65a6e9a99dd9be16e967fef5ea72bfb27 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Tue, 5 Jul 2016 14:20:14 -0700 Subject: [PATCH] CRM_Utils_QueryFormatterTest - Check query results (when possible) This test class is designed to be somewhat useful even when the underlying search engine (InnoDB FTS, Solr) is unavailable. However, the SQL LIKE engine is always available, and InnoDB FTS is sometimes available. In these cases, execute a query and see if it produces the expected results. See also: NYSS #9692 --- .../phpunit/CRM/Utils/QueryFormatterTest.php | 186 +++++++++++++----- 1 file changed, 133 insertions(+), 53 deletions(-) diff --git a/tests/phpunit/CRM/Utils/QueryFormatterTest.php b/tests/phpunit/CRM/Utils/QueryFormatterTest.php index b055b44395..fbd9cffd99 100644 --- a/tests/phpunit/CRM/Utils/QueryFormatterTest.php +++ b/tests/phpunit/CRM/Utils/QueryFormatterTest.php @@ -6,58 +6,103 @@ */ class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { + public function createExampleTable() { + CRM_Core_DAO::executeQuery(' + DROP TABLE IF EXISTS civicrm_fts_example + '); + CRM_Core_DAO::executeQuery(' + CREATE TABLE civicrm_fts_example ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + name varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (id) + ) + '); + $idx = new CRM_Core_InnoDBIndexer(self::supportsFts(), array( + 'civicrm_contact' => array( + array('first_name', 'last_name'), + ), + )); + $idx->fixSchemaDifferences(); + $rows = array( + array(1, 'someone@example.com'), + array(2, 'this is someone@example.com!'), + array(3, 'first second'), + array(4, 'zeroth first second'), + array(5, 'zeroth first second third'), + array(6, 'never say never'), + array(7, 'first someone@example.com second'), + array(8, 'first someone'), + array(9, 'firstly someone'), + ); + foreach ($rows as $row) { + CRM_Core_DAO::executeQuery("INSERT INTO civicrm_fts_example (id,name) VALUES (%1, %2)", + array( + 1 => array($row[0], 'Int'), + 2 => array($row[1], 'String'), + )); + } + } + + public static function tearDownAfterClass() { + CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_fts_example'); + parent::tearDownAfterClass(); + } + /** * Generate data for tests to iterate through. * + * Note: These examples are not locked in stone -- but do exercise + * discretion in revising them! + * * @return array */ public function dataProvider() { - // Array(0=>$inputText, 1=>$language, 2=>$options, 3=>$expectedText). + // Array(0=>$inputText, 1=>$language, 2=>$options, 3=>$expectedText, 4=>$matchingIds). $cases = array(); - $cases[] = array('someone@example.com', 'like', 'simple', '%someone@example.com%'); - $cases[] = array('someone@example.com', 'like', 'phrase', '%someone@example.com%'); - $cases[] = array('someone@example.com', 'like', 'wildphrase', '%someone@example.com%'); - $cases[] = array('someone@example.com', 'like', 'wildwords', '%someone@example.com%'); - $cases[] = array('someone@example.com', 'like', 'wildwords-suffix', '%someone@example.com%'); - - $cases[] = array('someone@example.com', 'fts', 'simple', 'someone@example.com'); - $cases[] = array('someone@example.com', 'fts', 'phrase', '"someone@example.com"'); - $cases[] = array('someone@example.com', 'fts', 'wildphrase', '"*someone@example.com*"'); - $cases[] = array('someone@example.com', 'fts', 'wildwords', '*someone* *example*'); // (1) - $cases[] = array('someone@example.com', 'fts', 'wildwords-suffix', 'someone* example*'); // (1) - - $cases[] = array('someone@example.com', 'ftsbool', 'simple', '+someone +example'); // (1) - $cases[] = array('someone@example.com', 'ftsbool', 'phrase', '+"someone@example.com"'); - $cases[] = array('someone@example.com', 'ftsbool', 'wildphrase', '+"*someone@example.com*"'); - $cases[] = array('someone@example.com', 'ftsbool', 'wildwords', '+*someone* +*example*'); // (1) - $cases[] = array('someone@example.com', 'ftsbool', 'wildwords-suffix', '+someone* +example*'); // (1) - - // Note: The examples marked with (1) are suspicious cases where - - $cases[] = array('first second', 'like', 'simple', '%first second%'); - $cases[] = array('first second', 'like', 'phrase', '%first second%'); - $cases[] = array('first second', 'like', 'wildphrase', '%first second%'); - $cases[] = array('first second', 'like', 'wildwords', '%first%second%'); - $cases[] = array('first second', 'like', 'wildwords-suffix', '%first%second%'); - - $cases[] = array('first second', 'fts', 'simple', 'first second'); - $cases[] = array('first second', 'fts', 'phrase', '"first second"'); - $cases[] = array('first second', 'fts', 'wildphrase', '"*first second*"'); - $cases[] = array('first second', 'fts', 'wildwords', '*first* *second*'); - $cases[] = array('first second', 'fts', 'wildwords-suffix', 'first* second*'); - - $cases[] = array('first second', 'ftsbool', 'simple', '+first +second'); - $cases[] = array('first second', 'ftsbool', 'phrase', '+"first second"'); - $cases[] = array('first second', 'ftsbool', 'wildphrase', '+"*first second*"'); - $cases[] = array('first second', 'ftsbool', 'wildwords', '+*first* +*second*'); - $cases[] = array('first second', 'ftsbool', 'wildwords-suffix', '+first* +second*'); - - $cases[] = array('first second', 'solr', 'simple', 'first second'); - $cases[] = array('first second', 'solr', 'phrase', '"first second"'); - $cases[] = array('first second', 'solr', 'wildphrase', '"*first second*"'); - $cases[] = array('first second', 'solr', 'wildwords', '*first* *second*'); - $cases[] = array('first second', 'solr', 'wildwords-suffix', 'first* second*'); + $allEmailRows = array(1, 2, 7); + + $cases[] = array('someone@example.com', 'like', 'simple', '%someone@example.com%', $allEmailRows); + $cases[] = array('someone@example.com', 'like', 'phrase', '%someone@example.com%', $allEmailRows); + $cases[] = array('someone@example.com', 'like', 'wildphrase', '%someone@example.com%', $allEmailRows); + $cases[] = array('someone@example.com', 'like', 'wildwords', '%someone@example.com%', $allEmailRows); + $cases[] = array('someone@example.com', 'like', 'wildwords-suffix', '%someone@example.com%', $allEmailRows); + + $cases[] = array('someone@example.com', 'fts', 'simple', 'someone@example.com', $allEmailRows); + $cases[] = array('someone@example.com', 'fts', 'phrase', '"someone@example.com"', $allEmailRows); + $cases[] = array('someone@example.com', 'fts', 'wildphrase', '"*someone@example.com*"', $allEmailRows); + $cases[] = array('someone@example.com', 'fts', 'wildwords', '*someone* *example*', $allEmailRows); + $cases[] = array('someone@example.com', 'fts', 'wildwords-suffix', 'someone* example*', $allEmailRows); + + $cases[] = array('someone@example.com', 'ftsbool', 'simple', '+someone +example', $allEmailRows); + $cases[] = array('someone@example.com', 'ftsbool', 'phrase', '+"someone@example.com"', $allEmailRows); + $cases[] = array('someone@example.com', 'ftsbool', 'wildphrase', '+"*someone@example.com*"', $allEmailRows); + $cases[] = array('someone@example.com', 'ftsbool', 'wildwords', '+*someone* +*example*', $allEmailRows); + $cases[] = array('someone@example.com', 'ftsbool', 'wildwords-suffix', '+someone* +example*', $allEmailRows); + + $cases[] = array('first second', 'like', 'simple', '%first second%', array(3, 4, 5)); + $cases[] = array('first second', 'like', 'phrase', '%first second%', array(3, 4, 5)); + $cases[] = array('first second', 'like', 'wildphrase', '%first second%', array(3, 4, 5)); + $cases[] = array('first second', 'like', 'wildwords', '%first%second%', array(3, 4, 5, 7)); + $cases[] = array('first second', 'like', 'wildwords-suffix', '%first%second%', array(3, 4, 5, 7)); + + $cases[] = array('first second', 'fts', 'simple', 'first second', array(3, 4, 5)); + $cases[] = array('first second', 'fts', 'phrase', '"first second"', array(3, 4, 5)); + $cases[] = array('first second', 'fts', 'wildphrase', '"*first second*"', array(3, 4, 5)); + $cases[] = array('first second', 'fts', 'wildwords', '*first* *second*', array(3, 4, 5, 7)); + $cases[] = array('first second', 'fts', 'wildwords-suffix', 'first* second*', array(3, 4, 5, 7)); + + $cases[] = array('first second', 'ftsbool', 'simple', '+first +second', array(3, 4, 5)); + $cases[] = array('first second', 'ftsbool', 'phrase', '+"first second"', array(3, 4, 5)); + $cases[] = array('first second', 'ftsbool', 'wildphrase', '+"*first second*"', array(3, 4, 5)); + $cases[] = array('first second', 'ftsbool', 'wildwords', '+*first* +*second*', array(3, 4, 5, 7)); + $cases[] = array('first second', 'ftsbool', 'wildwords-suffix', '+first* +second*', array(3, 4, 5, 7)); + + $cases[] = array('first second', 'solr', 'simple', 'first second', NULL); + $cases[] = array('first second', 'solr', 'phrase', '"first second"', NULL); + $cases[] = array('first second', 'solr', 'wildphrase', '"*first second*"', NULL); + $cases[] = array('first second', 'solr', 'wildwords', '*first* *second*', NULL); + $cases[] = array('first second', 'solr', 'wildwords-suffix', 'first* second*', NULL); // If user supplies wildcards, then ignore mode. foreach (array( @@ -66,14 +111,14 @@ class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { 'wildwords', 'wildwords-suffix', ) as $mode) { - $cases[] = array('first% second', 'like', $mode, 'first% second'); - $cases[] = array('first% second', 'fts', $mode, 'first* second'); - $cases[] = array('first% second', 'ftsbool', $mode, '+first* +second'); - $cases[] = array('first% second', 'solr', $mode, 'first* second'); - $cases[] = array('first second%', 'like', $mode, 'first second%'); - $cases[] = array('first second%', 'fts', $mode, 'first second*'); - $cases[] = array('first second%', 'ftsbool', $mode, '+first +second*'); - $cases[] = array('first second%', 'solr', $mode, 'first second*'); + $cases[] = array('first% second', 'like', $mode, 'first% second', array(3, 7)); + $cases[] = array('first% second', 'fts', $mode, 'first* second', array(3, 7)); + $cases[] = array('first% second', 'ftsbool', $mode, '+first* +second', array(3, 7)); + $cases[] = array('first% second', 'solr', $mode, 'first* second', NULL); + $cases[] = array('first second%', 'like', $mode, 'first second%', array(3)); + $cases[] = array('first second%', 'fts', $mode, 'first second*', array(3)); + $cases[] = array('first second%', 'ftsbool', $mode, '+first +second*', array(3)); + $cases[] = array('first second%', 'solr', $mode, 'first second*', NULL); } return $cases; @@ -89,10 +134,45 @@ class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { * * @dataProvider dataProvider */ - public function testFormat($text, $language, $mode, $expectedText) { + public function testFormat($text, $language, $mode, $expectedText, $expectedRowIds) { $formatter = new CRM_Utils_QueryFormatter($mode); $actualText = $formatter->format($text, $language); $this->assertEquals($expectedText, $actualText); + + if ($expectedRowIds !== NULL) { + if ($language === 'like') { + $this->createExampleTable(); + $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text)); + } + elseif (in_array($language, array('fts', 'ftsbool'))) { + if ($this->supportsFts()) { + $this->createExampleTable(); + $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text)); + } + } + elseif ($language === 'solr') { + // Skip. Don't have solr test harness. + } + else { + $this->fail("Cannot asset expectedRowIds with unrecognized language $language"); + } + } + } + + public static function supportsFts() { + return version_compare(CRM_Core_DAO::singleValueQuery('SELECT VERSION()'), '5.6.0', '>='); + } + + /** + * @param array $expectedRowIds + * @param string $sql + */ + private function assertSqlIds($expectedRowIds, $sql) { + $actualRowIds = CRM_Utils_Array::collect('id', + CRM_Core_DAO::executeQuery($sql)->fetchAll()); + sort($actualRowIds); + sort($expectedRowIds); + $this->assertEquals($expectedRowIds, $actualRowIds); } } -- 2.25.1