CRM/Utils/QueryFormatter.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28  /**
  29   * @package CRM
  30   * @copyright CiviCRM LLC (c) 2004-2016
  31   */
  32
  33 /**
  34  * Class CRM_Utils_QueryFormatter
  35  *
  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
  40  * or in vain.
  41  */
  42 class CRM_Utils_QueryFormatter {
  43   /**
  44    * Generate queries using SQL LIKE expressions.
  45    */
  46   const LANG_SQL_LIKE = 'like';
  47
  48   /**
  49    * Generate queries using MySQL FTS expressions.
  50    */
  51   const LANG_SQL_FTS = 'fts';
  52
  53   /**
  54    * Generate queries using MySQL's boolean FTS expressions.
  55    */
  56   const LANG_SQL_FTSBOOL = 'ftsbool';
  57
  58   /**
  59    * Generate queries using Solr expressions.
  60    */
  61   const LANG_SOLR = 'solr';
  62
  63   /**
  64    * Attempt to leave the text as-is.
  65    */
  66   const MODE_NONE = 'simple';
  67
  68   /**
  69    * Attempt to treat the input text as a phrase
  70    */
  71   const MODE_PHRASE = 'phrase';
  72
  73   /**
  74    * Attempt to treat the input text as a phrase with
  75    * wildcards on each end.
  76    */
  77   const MODE_WILDPHRASE = 'wildphrase';
  78
  79   /**
  80    * Attempt to treat individual word as if it
  81    * had wildcards at the start and end.
  82    */
  83   const MODE_WILDWORDS = 'wildwords';
  84
  85   /**
  86    * Attempt to treat individual word as if it
  87    * had a wildcard at the end.
  88    */
  89   const MODE_WILDWORDS_SUFFIX = 'wildwords-suffix';
  90
  91   static protected $singleton;
  92
  93   /**
  94    * @param bool $fresh
  95    * @return CRM_Utils_QueryFormatter
  96    */
  97   public static function singleton($fresh = FALSE) {
  98     if ($fresh || self::$singleton === NULL) {
  99       $mode = Civi::settings()->get('fts_query_mode');
 100       self::$singleton = new CRM_Utils_QueryFormatter($mode);
 101     }
 102     return self::$singleton;
 103   }
 104
 105   /**
 106    * @var string
 107    *   eg MODE_NONE
 108    */
 109   protected $mode;
 110
 111   /**
 112    * @param string $mode
 113    *   Eg MODE_NONE.
 114    */
 115   public function __construct($mode) {
 116     $this->mode = $mode;
 117   }
 118
 119   /**
 120    * @param mixed $mode
 121    */
 122   public function setMode($mode) {
 123     $this->mode = $mode;
 124   }
 125
 126   /**
 127    * @return mixed
 128    */
 129   public function getMode() {
 130     return $this->mode;
 131   }
 132
 133   /**
 134    * @param string $text
 135    * @param string $language
 136    *   Eg LANG_SQL_LIKE, LANG_SQL_FTS, LANG_SOLR.
 137    * @throws CRM_Core_Exception
 138    * @return string
 139    */
 140   public function format($text, $language) {
 141     $text = trim($text);
 142
 143     switch ($language) {
 144       case self::LANG_SOLR:
 145       case self::LANG_SQL_FTS:
 146         $text = $this->_formatFts($text, $this->mode);
 147         break;
 148
 149       case self::LANG_SQL_FTSBOOL:
 150         $text = $this->_formatFtsBool($text, $this->mode);
 151         break;
 152
 153       case self::LANG_SQL_LIKE:
 154         $text = $this->_formatLike($text, $this->mode);
 155         break;
 156
 157       default:
 158         $text = NULL;
 159     }
 160
 161     if ($text === NULL) {
 162       throw new CRM_Core_Exception("Unrecognized combination: language=[{$language}] mode=[{$this->mode}]");
 163     }
 164
 165     return $text;
 166   }
 167
 168   /**
 169    * Create a SQL WHERE expression for matching against a list of
 170    * text columns.
 171    *
 172    * @param string $table
 173    *   Eg "civicrm_note" or "civicrm_note mynote".
 174    * @param array|string $columns
 175    *   List of columns to search against.
 176    *   Eg "first_name" or "activity_details".
 177    * @param string $queryText
 178    * @return string
 179    *   SQL, eg "MATCH (col1) AGAINST (queryText)" or "col1 LIKE '%queryText%'"
 180    */
 181   public function formatSql($table, $columns, $queryText) {
 182     if ($queryText === '*' || $queryText === '%' || empty($queryText)) {
 183       return '(1)';
 184     }
 185
 186     $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower';
 187
 188     if (strpos($table, ' ') === FALSE) {
 189       $tableName = $tableAlias = $table;
 190     }
 191     else {
 192       list ($tableName, $tableAlias) = explode(' ', $table);
 193     }
 194     if (is_scalar($columns)) {
 195       $columns = array($columns);
 196     }
 197
 198     $clauses = array();
 199     if (CRM_Core_InnoDBIndexer::singleton()
 200       ->hasDeclaredIndex($tableName, $columns)
 201     ) {
 202       $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL);
 203
 204       $prefixedFieldNames = array();
 205       foreach ($columns as $fieldName) {
 206         $prefixedFieldNames[] = "$tableAlias.$fieldName";
 207       }
 208
 209       $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)",
 210         implode(',', $prefixedFieldNames),
 211         $strtolower(CRM_Core_DAO::escapeString($formattedQuery))
 212       );
 213     }
 214     else {
 215       //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array(
 216       //  1 => $table,
 217       //  2 => implode(', ', $fullTextFields),
 218       //)));
 219
 220       $formattedQuery = $this->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_LIKE);
 221       $escapedText = $strtolower(CRM_Core_DAO::escapeString($formattedQuery));
 222       foreach ($columns as $fieldName) {
 223         $clauses[] = "$tableAlias.$fieldName LIKE '{$escapedText}'";
 224       }
 225     }
 226     return implode(' OR ', $clauses);
 227   }
 228
 229   /**
 230    * Format Fts.
 231    *
 232    * @param string $text
 233    * @param $mode
 234    *
 235    * @return mixed
 236    */
 237   protected function _formatFts($text, $mode) {
 238     $result = NULL;
 239
 240     // normalize user-inputted wildcards
 241     $text = str_replace('%', '*', $text);
 242
 243     if (empty($text)) {
 244       $result = '*';
 245     }
 246     elseif (strpos($text, '*') !== FALSE) {
 247       // if user supplies their own wildcards, then don't do any sophisticated changes
 248       $result = $text;
 249     }
 250     else {
 251       switch ($mode) {
 252         case self::MODE_NONE:
 253           $result = $text;
 254           break;
 255
 256         case self::MODE_PHRASE:
 257           $result = '"' . $text . '"';
 258           break;
 259
 260         case self::MODE_WILDPHRASE:
 261           $result = '"*' . $text . '*"';
 262           break;
 263
 264         case self::MODE_WILDWORDS:
 265           $result = $this->mapWords($text, '*word*');
 266           break;
 267
 268         case self::MODE_WILDWORDS_SUFFIX:
 269           $result = $this->mapWords($text, 'word*');
 270           break;
 271
 272         default:
 273           $result = NULL;
 274       }
 275     }
 276
 277     return $this->dedupeWildcards($result, '%');
 278   }
 279
 280   /**
 281    * Format FTS.
 282    *
 283    * @param string $text
 284    * @param $mode
 285    *
 286    * @return mixed
 287    */
 288   protected function _formatFtsBool($text, $mode) {
 289     $result = NULL;
 290     $operators = array('+', '-', '~', '(', ')');
 291
 292     //Return if searched string ends with an unsupported operator.
 293     foreach ($operators as $val) {
 294       if ($text == '@' || CRM_Utils_String::endsWith($text, $val)) {
 295         $csid = CRM_Core_DAO::getFieldValue('CRM_Core_DAO_OptionValue', 'CRM_Contact_Form_Search_Custom_FullText', 'value', 'name');
 296         $url = CRM_Utils_System::url("civicrm/contact/search/custom", "csid={$csid}&reset=1");
 297         $operators = implode("', '", $operators);
 298         CRM_Core_Error::statusBounce("Full-Text Search does not support the use of a search string ending with any of these operators ('{$operators}' or a single '@'). Please adjust your search term and try again.", $url);
 299       }
 300     }
 301
 302     // normalize user-inputted wildcards
 303     $text = str_replace('%', '*', $text);
 304
 305     if (empty($text)) {
 306       $result = '*';
 307     }
 308     elseif (strpos($text, '+') !== FALSE || strpos($text, '-') !== FALSE) {
 309       // if user supplies their own include/exclude operators, use text as is (with trailing wildcard)
 310       $result = $this->mapWords($text, 'word*');
 311     }
 312     elseif (strpos($text, '*') !== FALSE) {
 313       // if user supplies their own wildcards, then don't do any sophisticated changes
 314       $result = $this->mapWords($text, '+word');
 315     }
 316     elseif (preg_match('/^(["\']).*\1$/m', $text)) {
 317       // if surrounded by quotes, use term as is
 318       $result = $text;
 319     }
 320     else {
 321       switch ($mode) {
 322         case self::MODE_NONE:
 323           $result = $this->mapWords($text, '+word', TRUE);
 324           break;
 325
 326         case self::MODE_PHRASE:
 327           $result = '+"' . $text . '"';
 328           break;
 329
 330         case self::MODE_WILDPHRASE:
 331           $result = '+"*' . $text . '*"';
 332           break;
 333
 334         case self::MODE_WILDWORDS:
 335           $result = $this->mapWords($text, '+*word*');
 336           break;
 337
 338         case self::MODE_WILDWORDS_SUFFIX:
 339           $result = $this->mapWords($text, '+word*');
 340           break;
 341
 342         default:
 343           $result = NULL;
 344       }
 345     }
 346
 347     return $this->dedupeWildcards($result, '%');
 348   }
 349
 350   /**
 351    * Format like.
 352    *
 353    * @param $text
 354    * @param $mode
 355    *
 356    * @return mixed
 357    */
 358   protected function _formatLike($text, $mode) {
 359     $result = NULL;
 360
 361     if (empty($text)) {
 362       $result = '%';
 363     }
 364     elseif (strpos($text, '%') !== FALSE) {
 365       // if user supplies their own wildcards, then don't do any sophisticated changes
 366       $result = $text;
 367     }
 368     else {
 369       switch ($mode) {
 370         case self::MODE_NONE:
 371         case self::MODE_PHRASE:
 372         case self::MODE_WILDPHRASE:
 373           $result = "%" . $text . "%";
 374           break;
 375
 376         case self::MODE_WILDWORDS:
 377         case self::MODE_WILDWORDS_SUFFIX:
 378           $result = "%" . preg_replace('/[ \r\n]+/', '%', $text) . '%';
 379           break;
 380
 381         default:
 382           $result = NULL;
 383       }
 384     }
 385
 386     return $this->dedupeWildcards($result, '%');
 387   }
 388
 389   /**
 390    * @param string $text
 391    *   User-supplied query string.
 392    * @param string $template
 393    *   A prototypical description of each word, eg "word%" or "word*" or "*word*".
 394    * @param bool $quotes
 395    *   True if each searched keyword need to be surrounded with quotes.
 396    * @return string
 397    */
 398   protected function mapWords($text, $template, $quotes = FALSE) {
 399     $result = array();
 400     foreach ($this->parseWords($text, $quotes) as $word) {
 401       $result[] = str_replace('word', $word, $template);
 402     }
 403     return implode(' ', $result);
 404   }
 405
 406   /**
 407    * @param $text
 408    * @bool $quotes
 409    * @return array
 410    */
 411   protected function parseWords($text, $quotes) {
 412     //NYSS 9692 special handling for emails
 413     if (preg_match('/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/', $text)) {
 414       $parts = explode('@', $text);
 415       $parts[1] = stristr($parts[1], '.', TRUE);
 416       $text = implode(' ', $parts);
 417     }
 418
 419     //NYSS also replace other occurrences of @
 420     $replacedText = preg_replace('/[ \r\n\t\@]+/', ' ', trim($text));
 421     //filter empty values if any
 422     $keywords = array_filter(explode(' ', $replacedText));
 423
 424     //Ensure each searched keywords are wrapped in double quotes.
 425     if ($quotes) {
 426       foreach ($keywords as &$val) {
 427         if (!is_numeric($val)) {
 428           $val = "\"{$val}\"";
 429         }
 430       }
 431     }
 432     return $keywords;
 433   }
 434
 435   /**
 436    * @param $text
 437    * @param $wildcard
 438    * @return mixed
 439    */
 440   protected function dedupeWildcards($text, $wildcard) {
 441     if ($text === NULL) {
 442       return NULL;
 443     }
 444
 445     // don't use preg_replace because $wildcard might be special char
 446     while (strpos($text, "{$wildcard}{$wildcard}") !== FALSE) {
 447       $text = str_replace("{$wildcard}{$wildcard}", "{$wildcard}", $text);
 448     }
 449     return $text;
 450   }
 451
 452   /**
 453    * Get modes.
 454    *
 455    * @return array
 456    */
 457   public static function getModes() {
 458     return array(
 459       self::MODE_NONE,
 460       self::MODE_PHRASE,
 461       self::MODE_WILDPHRASE,
 462       self::MODE_WILDWORDS,
 463       self::MODE_WILDWORDS_SUFFIX,
 464     );
 465   }
 466
 467   /**
 468    * Get languages.
 469    *
 470    * @return array
 471    */
 472   public static function getLanguages() {
 473     return array(
 474       self::LANG_SOLR,
 475       self::LANG_SQL_FTS,
 476       self::LANG_SQL_FTSBOOL,
 477       self::LANG_SQL_LIKE,
 478     );
 479   }
 480
 481   /**
 482    * @param $text
 483    *
 484    * Ex: drush eval 'civicrm_initialize(); CRM_Utils_QueryFormatter::dumpExampleTable("firstword secondword");'
 485    */
 486   public static function dumpExampleTable($text) {
 487     $width = strlen($text) + 8;
 488     $buf = '';
 489
 490     $buf .= sprintf("%-{$width}s", 'mode');
 491     foreach (self::getLanguages() as $lang) {
 492       $buf .= sprintf("%-{$width}s", $lang);
 493     }
 494     $buf .= "\n";
 495
 496     foreach (self::getModes() as $mode) {
 497       $formatter = new CRM_Utils_QueryFormatter($mode);
 498       $buf .= sprintf("%-{$width}s", $mode);
 499       foreach (self::getLanguages() as $lang) {
 500         $buf .= sprintf("%-{$width}s", $formatter->format($text, $lang));
 501       }
 502       $buf .= "\n";
 503     }
 504
 505     echo $buf;
 506   }
 507
 508 }