| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Class CRM_Utils_QueryFormatterTest |
| 5 | * @group headless |
| 6 | */ |
| 7 | class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { |
| 8 | |
| 9 | public function createExampleTable() { |
| 10 | CRM_Core_DAO::executeQuery(' |
| 11 | DROP TABLE IF EXISTS civicrm_fts_example |
| 12 | '); |
| 13 | CRM_Core_DAO::executeQuery(' |
| 14 | CREATE TABLE civicrm_fts_example ( |
| 15 | id int(10) unsigned NOT NULL AUTO_INCREMENT, |
| 16 | name varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL, |
| 17 | PRIMARY KEY (id) |
| 18 | ) |
| 19 | '); |
| 20 | $idx = new CRM_Core_InnoDBIndexer(self::supportsFts(), [ |
| 21 | 'civicrm_contact' => [ |
| 22 | ['first_name', 'last_name'], |
| 23 | ], |
| 24 | ]); |
| 25 | $idx->fixSchemaDifferences(); |
| 26 | $rows = [ |
| 27 | [1, 'someone@example.com'], |
| 28 | [2, 'this is someone@example.com!'], |
| 29 | [3, 'first second'], |
| 30 | [4, 'zeroth first second'], |
| 31 | [5, 'zeroth first second third'], |
| 32 | [6, 'never say never'], |
| 33 | [7, 'first someone@example.com second'], |
| 34 | [8, 'first someone'], |
| 35 | [9, 'firstly someone'], |
| 36 | ]; |
| 37 | foreach ($rows as $row) { |
| 38 | CRM_Core_DAO::executeQuery("INSERT INTO civicrm_fts_example (id,name) VALUES (%1, %2)", |
| 39 | [ |
| 40 | 1 => [$row[0], 'Int'], |
| 41 | 2 => [$row[1], 'String'], |
| 42 | ]); |
| 43 | } |
| 44 | } |
| 45 | |
| 46 | public static function tearDownAfterClass() { |
| 47 | CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_fts_example'); |
| 48 | parent::tearDownAfterClass(); |
| 49 | } |
| 50 | |
| 51 | /** |
| 52 | * Generate data for tests to iterate through. |
| 53 | * |
| 54 | * Note: These examples are not locked in stone -- but do exercise |
| 55 | * discretion in revising them! |
| 56 | * |
| 57 | * @return array |
| 58 | */ |
| 59 | public function dataProvider() { |
| 60 | // Array(0=>$inputText, 1=>$language, 2=>$options, 3=>$expectedText, 4=>$matchingIds). |
| 61 | $cases = []; |
| 62 | |
| 63 | $allEmailRows = [1, 2, 7]; |
| 64 | |
| 65 | $cases[] = ['someone@example.com', 'like', 'simple', '%someone@example.com%', $allEmailRows]; |
| 66 | $cases[] = ['someone@example.com', 'like', 'phrase', '%someone@example.com%', $allEmailRows]; |
| 67 | $cases[] = ['someone@example.com', 'like', 'wildphrase', '%someone@example.com%', $allEmailRows]; |
| 68 | $cases[] = ['someone@example.com', 'like', 'wildwords', '%someone@example.com%', $allEmailRows]; |
| 69 | $cases[] = ['someone@example.com', 'like', 'wildwords-suffix', '%someone@example.com%', $allEmailRows]; |
| 70 | |
| 71 | $cases[] = ['someone@example.com', 'fts', 'simple', 'someone@example.com', $allEmailRows]; |
| 72 | $cases[] = ['someone@example.com', 'fts', 'phrase', '"someone@example.com"', $allEmailRows]; |
| 73 | $cases[] = ['someone@example.com', 'fts', 'wildphrase', '"*someone@example.com*"', $allEmailRows]; |
| 74 | $cases[] = ['someone@example.com', 'fts', 'wildwords', '*someone* *example*', $allEmailRows]; |
| 75 | $cases[] = ['someone@example.com', 'fts', 'wildwords-suffix', 'someone* example*', $allEmailRows]; |
| 76 | |
| 77 | $cases[] = ['someone@example.com', 'ftsbool', 'simple', '+"someone" +"example"', $allEmailRows]; |
| 78 | $cases[] = ['someone@example.com', 'ftsbool', 'phrase', '+"someone@example.com"', $allEmailRows]; |
| 79 | $cases[] = ['someone@example.com', 'ftsbool', 'wildphrase', '+"*someone@example.com*"', $allEmailRows]; |
| 80 | $cases[] = ['someone@example.com', 'ftsbool', 'wildwords', '+*someone* +*example*', $allEmailRows]; |
| 81 | $cases[] = ['someone@example.com', 'ftsbool', 'wildwords-suffix', '+someone* +example*', $allEmailRows]; |
| 82 | |
| 83 | $cases[] = ['first second', 'like', 'simple', '%first second%', [3, 4, 5]]; |
| 84 | $cases[] = ['first second', 'like', 'phrase', '%first second%', [3, 4, 5]]; |
| 85 | $cases[] = ['first second', 'like', 'wildphrase', '%first second%', [3, 4, 5]]; |
| 86 | $cases[] = ['first second', 'like', 'wildwords', '%first%second%', [3, 4, 5, 7]]; |
| 87 | $cases[] = ['first second', 'like', 'wildwords-suffix', '%first%second%', [3, 4, 5, 7]]; |
| 88 | |
| 89 | $cases[] = ['first second', 'fts', 'simple', 'first second', [3, 4, 5]]; |
| 90 | $cases[] = ['first second', 'fts', 'phrase', '"first second"', [3, 4, 5]]; |
| 91 | $cases[] = ['first second', 'fts', 'wildphrase', '"*first second*"', [3, 4, 5]]; |
| 92 | $cases[] = ['first second', 'fts', 'wildwords', '*first* *second*', [3, 4, 5, 7]]; |
| 93 | $cases[] = ['first second', 'fts', 'wildwords-suffix', 'first* second*', [3, 4, 5, 7]]; |
| 94 | |
| 95 | $cases[] = ['first second', 'ftsbool', 'simple', '+"first" +"second"', [3, 4, 5]]; |
| 96 | $cases[] = ['first second', 'ftsbool', 'phrase', '+"first second"', [3, 4, 5]]; |
| 97 | $cases[] = ['first second', 'ftsbool', 'wildphrase', '+"*first second*"', [3, 4, 5]]; |
| 98 | $cases[] = ['first second', 'ftsbool', 'wildwords', '+*first* +*second*', [3, 4, 5, 7]]; |
| 99 | $cases[] = ['first second', 'ftsbool', 'wildwords-suffix', '+first* +second*', [3, 4, 5, 7]]; |
| 100 | |
| 101 | $cases[] = ['first second', 'solr', 'simple', 'first second', NULL]; |
| 102 | $cases[] = ['first second', 'solr', 'phrase', '"first second"', NULL]; |
| 103 | $cases[] = ['first second', 'solr', 'wildphrase', '"*first second*"', NULL]; |
| 104 | $cases[] = ['first second', 'solr', 'wildwords', '*first* *second*', NULL]; |
| 105 | $cases[] = ['first second', 'solr', 'wildwords-suffix', 'first* second*', NULL]; |
| 106 | |
| 107 | $cases[] = ['someone@', 'ftsbool', 'simple', '+"someone"', $allEmailRows]; |
| 108 | $cases[] = ['@example.com', 'ftsbool', 'simple', '+"example.com"', $allEmailRows]; |
| 109 | |
| 110 | // If user supplies wildcards, then ignore mode. |
| 111 | foreach ([ |
| 112 | 'simple', |
| 113 | 'wildphrase', |
| 114 | 'wildwords', |
| 115 | 'wildwords-suffix', |
| 116 | ] as $mode) { |
| 117 | $cases[] = ['first% second', 'like', $mode, 'first% second', [3, 7]]; |
| 118 | $cases[] = ['first% second', 'fts', $mode, 'first* second', [3, 7]]; |
| 119 | $cases[] = ['first% second', 'ftsbool', $mode, '+first* +second', [3, 7]]; |
| 120 | $cases[] = ['first% second', 'solr', $mode, 'first* second', NULL]; |
| 121 | $cases[] = ['first second%', 'like', $mode, 'first second%', [3]]; |
| 122 | $cases[] = ['first second%', 'fts', $mode, 'first second*', [3]]; |
| 123 | $cases[] = ['first second%', 'ftsbool', $mode, '+first +second*', [3]]; |
| 124 | $cases[] = ['first second%', 'solr', $mode, 'first second*', NULL]; |
| 125 | } |
| 126 | |
| 127 | return $cases; |
| 128 | } |
| 129 | |
| 130 | /** |
| 131 | * Test format. |
| 132 | * |
| 133 | * @param string $text |
| 134 | * @param string $language |
| 135 | * @param string $mode |
| 136 | * @param string $expectedText |
| 137 | * @param array|NULL $expectedRowIds |
| 138 | * |
| 139 | * @dataProvider dataProvider |
| 140 | */ |
| 141 | public function testFormat($text, $language, $mode, $expectedText, $expectedRowIds) { |
| 142 | $formatter = new CRM_Utils_QueryFormatter($mode); |
| 143 | $actualText = $formatter->format($text, $language); |
| 144 | $this->assertEquals($expectedText, $actualText); |
| 145 | |
| 146 | if ($expectedRowIds !== NULL) { |
| 147 | if ($language === 'like') { |
| 148 | $this->createExampleTable(); |
| 149 | $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text)); |
| 150 | } |
| 151 | elseif (in_array($language, ['fts', 'ftsbool'])) { |
| 152 | if ($this->supportsFts()) { |
| 153 | $this->createExampleTable(); |
| 154 | $this->assertSqlIds($expectedRowIds, "SELECT id FROM civicrm_fts_example WHERE " . $formatter->formatSql('civicrm_fts_example', 'name', $text)); |
| 155 | } |
| 156 | } |
| 157 | elseif ($language === 'solr') { |
| 158 | // Skip. Don't have solr test harness. |
| 159 | } |
| 160 | else { |
| 161 | $this->fail("Cannot asset expectedRowIds with unrecognized language $language"); |
| 162 | } |
| 163 | } |
| 164 | } |
| 165 | |
| 166 | public static function supportsFts() { |
| 167 | return version_compare(CRM_Core_DAO::singleValueQuery('SELECT VERSION()'), '5.6.0', '>='); |
| 168 | } |
| 169 | |
| 170 | /** |
| 171 | * @param array $expectedRowIds |
| 172 | * @param string $sql |
| 173 | */ |
| 174 | private function assertSqlIds($expectedRowIds, $sql) { |
| 175 | $actualRowIds = CRM_Utils_Array::collect('id', |
| 176 | CRM_Core_DAO::executeQuery($sql)->fetchAll()); |
| 177 | sort($actualRowIds); |
| 178 | sort($expectedRowIds); |
| 179 | $this->assertEquals($expectedRowIds, $actualRowIds); |
| 180 | } |
| 181 | |
| 182 | } |