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
 291     // normalize user-inputted wildcards
 292     $text = str_replace('%', '*', $text);
 293
 294     if (empty($text)) {
 295       $result = '*';
 296     }
 297     elseif (strpos($text, '+') !== FALSE || strpos($text, '-') !== FALSE) {
 298       // if user supplies their own include/exclude operators, use text as is (with trailing wildcard)
 299       $result = $this->mapWords($text, 'word*');
 300     }
 301     elseif (strpos($text, '*') !== FALSE) {
 302       // if user supplies their own wildcards, then don't do any sophisticated changes
 303       $result = $this->mapWords($text, '+word');
 304     }
 305     elseif (preg_match('/^(["\']).*\1$/m', $text)) {
 306       // if surrounded by quotes, use term as is
 307       $result = $text;
 308     }
 309     else {
 310       switch ($mode) {
 311         case self::MODE_NONE:
 312           $result = $this->mapWords($text, '+word');
 313           break;
 314
 315         case self::MODE_PHRASE:
 316           $result = '+"' . $text . '"';
 317           break;
 318
 319         case self::MODE_WILDPHRASE:
 320           $result = '+"*' . $text . '*"';
 321           break;
 322
 323         case self::MODE_WILDWORDS:
 324           $result = $this->mapWords($text, '+*word*');
 325           break;
 326
 327         case self::MODE_WILDWORDS_SUFFIX:
 328           $result = $this->mapWords($text, '+word*');
 329           break;
 330
 331         default:
 332           $result = NULL;
 333       }
 334     }
 335
 336     return $this->dedupeWildcards($result, '%');
 337   }
 338
 339   /**
 340    * Format like.
 341    *
 342    * @param $text
 343    * @param $mode
 344    *
 345    * @return mixed
 346    */
 347   protected function _formatLike($text, $mode) {
 348     $result = NULL;
 349
 350     if (empty($text)) {
 351       $result = '%';
 352     }
 353     elseif (strpos($text, '%') !== FALSE) {
 354       // if user supplies their own wildcards, then don't do any sophisticated changes
 355       $result = $text;
 356     }
 357     else {
 358       switch ($mode) {
 359         case self::MODE_NONE:
 360         case self::MODE_PHRASE:
 361         case self::MODE_WILDPHRASE:
 362           $result = "%" . $text . "%";
 363           break;
 364
 365         case self::MODE_WILDWORDS:
 366         case self::MODE_WILDWORDS_SUFFIX:
 367           $result = "%" . preg_replace('/[ \r\n]+/', '%', $text) . '%';
 368           break;
 369
 370         default:
 371           $result = NULL;
 372       }
 373     }
 374
 375     return $this->dedupeWildcards($result, '%');
 376   }
 377
 378   /**
 379    * @param string $text
 380    *   User-supplied query string.
 381    * @param string $template
 382    *   A prototypical description of each word, eg "word%" or "word*" or "*word*".
 383    * @return string
 384    */
 385   protected function mapWords($text, $template) {
 386     $result = array();
 387     foreach ($this->parseWords($text) as $word) {
 388       $result[] = str_replace('word', $word, $template);
 389     }
 390     return implode(' ', $result);
 391   }
 392
 393   /**
 394    * @param $text
 395    * @return array
 396    */
 397   protected function parseWords($text) {
 398     //NYSS 9692 special handling for emails
 399     if (preg_match('/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/', $text)) {
 400       $parts = explode('@', $text);
 401       $parts[1] = stristr($parts[1], '.', TRUE);
 402       $text = implode(' ', $parts);
 403     }
 404
 405     //NYSS also replace other occurrences of @
 406     return explode(' ', preg_replace('/[ \r\n\t\@]+/', ' ', trim($text)));
 407   }
 408
 409   /**
 410    * @param $text
 411    * @param $wildcard
 412    * @return mixed
 413    */
 414   protected function dedupeWildcards($text, $wildcard) {
 415     if ($text === NULL) {
 416       return NULL;
 417     }
 418
 419     // don't use preg_replace because $wildcard might be special char
 420     while (strpos($text, "{$wildcard}{$wildcard}") !== FALSE) {
 421       $text = str_replace("{$wildcard}{$wildcard}", "{$wildcard}", $text);
 422     }
 423     return $text;
 424   }
 425
 426   /**
 427    * Get modes.
 428    *
 429    * @return array
 430    */
 431   public static function getModes() {
 432     return array(
 433       self::MODE_NONE,
 434       self::MODE_PHRASE,
 435       self::MODE_WILDPHRASE,
 436       self::MODE_WILDWORDS,
 437       self::MODE_WILDWORDS_SUFFIX,
 438     );
 439   }
 440
 441   /**
 442    * Get languages.
 443    *
 444    * @return array
 445    */
 446   public static function getLanguages() {
 447     return array(
 448       self::LANG_SOLR,
 449       self::LANG_SQL_FTS,
 450       self::LANG_SQL_FTSBOOL,
 451       self::LANG_SQL_LIKE,
 452     );
 453   }
 454
 455   /**
 456    * @param $text
 457    *
 458    * Ex: drush eval 'civicrm_initialize(); CRM_Utils_QueryFormatter::dumpExampleTable("firstword secondword");'
 459    */
 460   public static function dumpExampleTable($text) {
 461     $width = strlen($text) + 8;
 462     $buf = '';
 463
 464     $buf .= sprintf("%-{$width}s", 'mode');
 465     foreach (self::getLanguages() as $lang) {
 466       $buf .= sprintf("%-{$width}s", $lang);
 467     }
 468     $buf .= "\n";
 469
 470     foreach (self::getModes() as $mode) {
 471       $formatter = new CRM_Utils_QueryFormatter($mode);
 472       $buf .= sprintf("%-{$width}s", $mode);
 473       foreach (self::getLanguages() as $lang) {
 474         $buf .= sprintf("%-{$width}s", $formatter->format($text, $lang));
 475       }
 476       $buf .= "\n";
 477     }
 478
 479     echo $buf;
 480   }
 481
 482 }