3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
30 * @copyright CiviCRM LLC (c) 2004-2019
34 * Class CRM_Utils_QueryFormatter
36 * This class is a bad idea. It exists for the unholy reason that a single installation
37 * may have up to three query engines (MySQL LIKE, MySQL FTS, Solr) processing the same
38 * query-text. It labors* to take the user's search expression and provide similar search
39 * semantics in different contexts. It is unknown whether this labor will be fruitful
42 class CRM_Utils_QueryFormatter
{
44 * Generate queries using SQL LIKE expressions.
46 const LANG_SQL_LIKE
= 'like';
49 * Generate queries using MySQL FTS expressions.
51 const LANG_SQL_FTS
= 'fts';
54 * Generate queries using MySQL's boolean FTS expressions.
56 const LANG_SQL_FTSBOOL
= 'ftsbool';
59 * Generate queries using Solr expressions.
61 const LANG_SOLR
= 'solr';
64 * Attempt to leave the text as-is.
66 const MODE_NONE
= 'simple';
69 * Attempt to treat the input text as a phrase
71 const MODE_PHRASE
= 'phrase';
74 * Attempt to treat the input text as a phrase with
75 * wildcards on each end.
77 const MODE_WILDPHRASE
= 'wildphrase';
80 * Attempt to treat individual word as if it
81 * had wildcards at the start and end.
83 const MODE_WILDWORDS
= 'wildwords';
86 * Attempt to treat individual word as if it
87 * had a wildcard at the end.
89 const MODE_WILDWORDS_SUFFIX
= 'wildwords-suffix';
92 * @var \CRM_Utils_QueryFormatter|NULL
94 static protected $singleton;
98 * @return CRM_Utils_QueryFormatter
100 public static function singleton($fresh = FALSE) {
101 if ($fresh || self
::$singleton === NULL) {
102 $mode = Civi
::settings()->get('fts_query_mode');
103 self
::$singleton = new CRM_Utils_QueryFormatter($mode);
105 return self
::$singleton;
115 * @param string $mode
118 public function __construct($mode) {
125 public function setMode($mode) {
132 public function getMode() {
137 * @param string $text
138 * @param string $language
139 * Eg LANG_SQL_LIKE, LANG_SQL_FTS, LANG_SOLR.
140 * @throws CRM_Core_Exception
143 public function format($text, $language) {
147 case self
::LANG_SOLR
:
148 case self
::LANG_SQL_FTS
:
149 $text = $this->_formatFts($text, $this->mode
);
152 case self
::LANG_SQL_FTSBOOL
:
153 $text = $this->_formatFtsBool($text, $this->mode
);
156 case self
::LANG_SQL_LIKE
:
157 $text = $this->_formatLike($text, $this->mode
);
164 if ($text === NULL) {
165 throw new CRM_Core_Exception("Unrecognized combination: language=[{$language}] mode=[{$this->mode}]");
172 * Create a SQL WHERE expression for matching against a list of
175 * @param string $table
176 * Eg "civicrm_note" or "civicrm_note mynote".
177 * @param array|string $columns
178 * List of columns to search against.
179 * Eg "first_name" or "activity_details".
180 * @param string $queryText
182 * SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'"
184 public function formatSql($table, $columns, $queryText) {
185 if ($queryText === '*' ||
$queryText === '%' ||
empty($queryText)) {
189 $strtolower = function_exists('mb_strtolower') ?
'mb_strtolower' : 'strtolower';
191 if (strpos($table, ' ') === FALSE) {
192 $tableName = $tableAlias = $table;
195 list ($tableName, $tableAlias) = explode(' ', $table);
197 if (is_scalar($columns)) {
198 $columns = [$columns];
202 if (CRM_Core_InnoDBIndexer
::singleton()
203 ->hasDeclaredIndex($tableName, $columns)
205 $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter
::LANG_SQL_FTSBOOL
);
207 $prefixedFieldNames = [];
208 foreach ($columns as $fieldName) {
209 $prefixedFieldNames[] = "$tableAlias.$fieldName";
212 $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)",
213 implode(',', $prefixedFieldNames),
214 $strtolower(CRM_Core_DAO
::escapeString($formattedQuery))
218 //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array(
220 // 2 => implode(', ', $fullTextFields),
223 $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter
::LANG_SQL_LIKE
);
224 $escapedText = $strtolower(CRM_Core_DAO
::escapeString($formattedQuery));
225 foreach ($columns as $fieldName) {
226 $clauses[] = "$tableAlias.$fieldName LIKE '{$escapedText}'";
229 return implode(' OR ', $clauses);
235 * @param string $text
240 protected function _formatFts($text, $mode) {
243 // normalize user-inputted wildcards
244 $text = str_replace('%', '*', $text);
249 elseif (strpos($text, '*') !== FALSE) {
250 // if user supplies their own wildcards, then don't do any sophisticated changes
255 case self
::MODE_NONE
:
259 case self
::MODE_PHRASE
:
260 $result = '"' . $text . '"';
263 case self
::MODE_WILDPHRASE
:
264 $result = '"*' . $text . '*"';
267 case self
::MODE_WILDWORDS
:
268 $result = $this->mapWords($text, '*word*');
271 case self
::MODE_WILDWORDS_SUFFIX
:
272 $result = $this->mapWords($text, 'word*');
280 return $this->dedupeWildcards($result, '%');
286 * @param string $text
291 protected function _formatFtsBool($text, $mode) {
293 $operators = ['+', '-', '~', '(', ')'];
294 $wildCards = ['@', '%', '*'];
295 $expression = preg_quote(implode('', array_merge($operators, $wildCards)), '/');
297 //Return if searched string ends with an unsupported operator.
298 //Or if the string contains an invalid joint occurrence of operators.
299 foreach ($operators as $val) {
300 if ($text == '@' || CRM_Utils_String
::endsWith($text, $val) ||
preg_match("/[{$expression}]{2,}/", $text)) {
301 $csid = CRM_Core_DAO
::getFieldValue('CRM_Core_DAO_OptionValue', 'CRM_Contact_Form_Search_Custom_FullText', 'value', 'name');
302 $url = CRM_Utils_System
::url("civicrm/contact/search/custom", "csid={$csid}&reset=1");
303 $operators = implode("', '", $operators);
304 CRM_Core_Error
::statusBounce("Full-Text Search does not support the use of a search with two attached operators or string ending with any of these operators ('{$operators}' or a single '@'). Please adjust your search term and try again.", $url, 'Invalid Search String');
308 // normalize user-inputted wildcards
309 $text = str_replace('%', '*', $text);
314 elseif (strpos($text, '+') !== FALSE ||
strpos($text, '-') !== FALSE) {
315 // if user supplies their own include/exclude operators, use text as is (with trailing wildcard)
316 $result = $this->mapWords($text, 'word*');
318 elseif (strpos($text, '*') !== FALSE) {
319 // if user supplies their own wildcards, then don't do any sophisticated changes
320 $result = $this->mapWords($text, '+word');
322 elseif (preg_match('/^(["\']).*\1$/m', $text)) {
323 // if surrounded by quotes, use term as is
328 case self
::MODE_NONE
:
329 $result = $this->mapWords($text, '+word', TRUE);
332 case self
::MODE_PHRASE
:
333 $result = '+"' . $text . '"';
336 case self
::MODE_WILDPHRASE
:
337 $result = '+"*' . $text . '*"';
340 case self
::MODE_WILDWORDS
:
341 $result = $this->mapWords($text, '+*word*');
344 case self
::MODE_WILDWORDS_SUFFIX
:
345 $result = $this->mapWords($text, '+word*');
353 return $this->dedupeWildcards($result, '%');
364 protected function _formatLike($text, $mode) {
370 elseif (strpos($text, '%') !== FALSE) {
371 // if user supplies their own wildcards, then don't do any sophisticated changes
376 case self
::MODE_NONE
:
377 case self
::MODE_PHRASE
:
378 case self
::MODE_WILDPHRASE
:
379 $result = "%" . $text . "%";
382 case self
::MODE_WILDWORDS
:
383 case self
::MODE_WILDWORDS_SUFFIX
:
384 $result = "%" . preg_replace('/[ \r\n]+/', '%', $text) . '%';
392 return $this->dedupeWildcards($result, '%');
396 * @param string $text
397 * User-supplied query string.
398 * @param string $template
399 * A prototypical description of each word, eg "word%" or "word*" or "*word*".
400 * @param bool $quotes
401 * True if each searched keyword need to be surrounded with quotes.
404 protected function mapWords($text, $template, $quotes = FALSE) {
406 foreach ($this->parseWords($text, $quotes) as $word) {
407 $result[] = str_replace('word', $word, $template);
409 return implode(' ', $result);
413 * @param string $text
414 * @param bool $quotes
417 protected function parseWords($text, $quotes) {
418 //NYSS 9692 special handling for emails
419 if (preg_match('/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/', $text)) {
420 $parts = explode('@', $text);
421 $parts[1] = stristr($parts[1], '.', TRUE);
422 $text = implode(' ', $parts);
425 //NYSS also replace other occurrences of @
426 $replacedText = preg_replace('/[ \r\n\t\@]+/', ' ', trim($text));
427 //filter empty values if any
428 $keywords = array_filter(explode(' ', $replacedText));
430 //Ensure each searched keywords are wrapped in double quotes.
432 foreach ($keywords as &$val) {
433 if (!is_numeric($val)) {
446 protected function dedupeWildcards($text, $wildcard) {
447 if ($text === NULL) {
451 // don't use preg_replace because $wildcard might be special char
452 while (strpos($text, "{$wildcard}{$wildcard}") !== FALSE) {
453 $text = str_replace("{$wildcard}{$wildcard}", "{$wildcard}", $text);
463 public static function getModes() {
467 self
::MODE_WILDPHRASE
,
468 self
::MODE_WILDWORDS
,
469 self
::MODE_WILDWORDS_SUFFIX
,
478 public static function getLanguages() {
482 self
::LANG_SQL_FTSBOOL
,
490 * Ex: drush eval 'civicrm_initialize(); CRM_Utils_QueryFormatter::dumpExampleTable("firstword secondword");'
492 public static function dumpExampleTable($text) {
493 $width = strlen($text) +
8;
496 $buf .= sprintf("%-{$width}s", 'mode');
497 foreach (self
::getLanguages() as $lang) {
498 $buf .= sprintf("%-{$width}s", $lang);
502 foreach (self
::getModes() as $mode) {
503 $formatter = new CRM_Utils_QueryFormatter($mode);
504 $buf .= sprintf("%-{$width}s", $mode);
505 foreach (self
::getLanguages() as $lang) {
506 $buf .= sprintf("%-{$width}s", $formatter->format($text, $lang));