From 3196b7a5c587e61bf1a9cbd457055517eeb3e7ea Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Wed, 11 Jun 2014 19:19:44 -0700 Subject: [PATCH] CRM-14811 - FullText - Use boolean mode with InnoDB FTS --- .../Custom/FullText/AbstractPartialQuery.php | 4 +- CRM/Utils/QueryFormatter.php | 52 ++++++++++++++++++- .../phpunit/CRM/Utils/QueryFormatterTest.php | 20 ++++--- 3 files changed, 66 insertions(+), 10 deletions(-) diff --git a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php index ff182d1c30..a3bd486477 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php +++ b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php @@ -252,14 +252,14 @@ GROUP BY {$tableValues['id']} $clauses = array(); if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) { $formattedQuery = CRM_Utils_QueryFormatter::singleton() - ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTS); + ->format($queryText, CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL); $prefixedFieldNames = array(); foreach ($fullTextFields as $fieldName) { $prefixedFieldNames[] = "$tableAlias.$fieldName"; } - $clauses[] = sprintf("MATCH (%s) AGAINST ('%s')", + $clauses[] = sprintf("MATCH (%s) AGAINST ('%s' IN BOOLEAN MODE)", implode(',', $prefixedFieldNames), $strtolower(CRM_Core_DAO::escapeString($formattedQuery)) ); diff --git a/CRM/Utils/QueryFormatter.php b/CRM/Utils/QueryFormatter.php index 7da213dbb3..baa8f36f3e 100644 --- a/CRM/Utils/QueryFormatter.php +++ b/CRM/Utils/QueryFormatter.php @@ -38,6 +38,7 @@ class CRM_Utils_QueryFormatter { const LANG_SQL_LIKE = 'like'; const LANG_SQL_FTS = 'fts'; + const LANG_SQL_FTSBOOL = 'ftsbool'; const LANG_SOLR = 'solr'; /** @@ -122,6 +123,9 @@ class CRM_Utils_QueryFormatter { case self::LANG_SQL_FTS: $text = $this->_formatFts($text, $this->mode); break; + case self::LANG_SQL_FTSBOOL: + $text = $this->_formatFtsBool($text, $this->mode); + break; case self::LANG_SQL_LIKE: $text = $this->_formatLike($text, $this->mode); break; @@ -143,11 +147,11 @@ class CRM_Utils_QueryFormatter { $text = str_replace('%', '*', $text); if (empty($text)) { - $result = '%'; + $result = '*'; } elseif (strpos($text, '*') !== FALSE) { // if user supplies their own wildcards, then don't do any sophisticated changes - return $text; + $result = $text; } else { switch ($mode) { @@ -179,6 +183,49 @@ class CRM_Utils_QueryFormatter { return $this->dedupeWildcards($result, '%'); } + protected function _formatFtsBool($text, $mode) { + $result = NULL; + + // normalize user-inputted wildcards + $text = str_replace('%', '*', $text); + + if (empty($text)) { + $result = '*'; + } + elseif (strpos($text, '*') !== FALSE) { + // if user supplies their own wildcards, then don't do any sophisticated changes + $result = $this->mapWords($text, '+word'); + } + else { + switch ($mode) { + case self::MODE_NONE: + $result = $this->mapWords($text, '+word'); + break; + + case self::MODE_PHRASE: + $result = '+"' . $text . '"'; + break; + + case self::MODE_WILDPHRASE: + $result = '+"*' . $text . '*"'; + break; + + case self::MODE_WILDWORDS: + $result = $this->mapWords($text, '+*word*'); + break; + + case self::MODE_WILDWORDS_SUFFIX: + $result = $this->mapWords($text, '+word*'); + break; + + default: + $result = NULL; + } + } + + return $this->dedupeWildcards($result, '%'); + } + protected function _formatLike($text, $mode) { $result = NULL; @@ -262,6 +309,7 @@ class CRM_Utils_QueryFormatter { return array( self::LANG_SOLR, self::LANG_SQL_FTS, + self::LANG_SQL_FTSBOOL, self::LANG_SQL_LIKE, ); } diff --git a/tests/phpunit/CRM/Utils/QueryFormatterTest.php b/tests/phpunit/CRM/Utils/QueryFormatterTest.php index ffe79a53e7..1786dbb4b6 100644 --- a/tests/phpunit/CRM/Utils/QueryFormatterTest.php +++ b/tests/phpunit/CRM/Utils/QueryFormatterTest.php @@ -22,6 +22,12 @@ class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTS, CRM_Utils_QueryFormatter::MODE_WILDWORDS, '*first* *second*'); $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTS, CRM_Utils_QueryFormatter::MODE_WILDWORDS_SUFFIX, 'first* second*'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, CRM_Utils_QueryFormatter::MODE_NONE, '+first +second'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, CRM_Utils_QueryFormatter::MODE_PHRASE, '+"first second"'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, CRM_Utils_QueryFormatter::MODE_WILDPHRASE, '+"*first second*"'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, CRM_Utils_QueryFormatter::MODE_WILDWORDS, '+*first* +*second*'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, CRM_Utils_QueryFormatter::MODE_WILDWORDS_SUFFIX, '+first* +second*'); + $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SOLR, CRM_Utils_QueryFormatter::MODE_NONE, 'first second'); $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SOLR, CRM_Utils_QueryFormatter::MODE_PHRASE, '"first second"'); $cases[] = array('first second', CRM_Utils_QueryFormatter::LANG_SOLR, CRM_Utils_QueryFormatter::MODE_WILDPHRASE, '"*first second*"'); @@ -30,12 +36,14 @@ class CRM_Utils_QueryFormatterTest extends CiviUnitTestCase { // if user supplies wildcards, then ignore mode foreach (array(CRM_Utils_QueryFormatter::MODE_NONE, CRM_Utils_QueryFormatter::MODE_WILDPHRASE, CRM_Utils_QueryFormatter::MODE_WILDWORDS, CRM_Utils_QueryFormatter::MODE_WILDWORDS_SUFFIX) as $mode) { - $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SQL_LIKE, $mode, 'first% second'); - $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SQL_FTS, $mode, 'first* second'); - $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SOLR, $mode, 'first* second'); - $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SQL_LIKE, $mode, 'first second%'); - $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SQL_FTS, $mode, 'first second*'); - $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SOLR, $mode, 'first second*'); + $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SQL_LIKE, $mode, 'first% second'); + $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SQL_FTS, $mode, 'first* second'); + $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, $mode, '+first* +second'); + $cases[] = array('first% second', CRM_Utils_QueryFormatter::LANG_SOLR, $mode, 'first* second'); + $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SQL_LIKE, $mode, 'first second%'); + $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SQL_FTS, $mode, 'first second*'); + $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SQL_FTSBOOL, $mode, '+first +second*'); + $cases[] = array('first second%', CRM_Utils_QueryFormatter::LANG_SOLR, $mode, 'first second*'); } return $cases; -- 2.25.1