From a04af6db744643f0fb31e0d045fe8b80c7c42e1e Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Fri, 6 Jun 2014 19:26:47 -0700 Subject: [PATCH] CRM-14811 - FullText - Use InnoDB FTS when available. --- .../Custom/FullText/AbstractPartialQuery.php | 54 ++++++++++++++++++- .../Form/Search/Custom/FullText/Activity.php | 15 +++--- .../Form/Search/Custom/FullText/Case.php | 6 ++- .../Form/Search/Custom/FullText/Contact.php | 4 +- .../Search/Custom/FullText/Contribution.php | 9 ++-- .../Search/Custom/FullText/Membership.php | 4 +- .../Search/Custom/FullText/Participant.php | 6 ++- CRM/Core/InnoDBIndexer.php | 16 ++++++ 8 files changed, 96 insertions(+), 18 deletions(-) diff --git a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php index df216da965..372c5f5746 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php +++ b/CRM/Contact/Form/Search/Custom/FullText/AbstractPartialQuery.php @@ -141,7 +141,8 @@ $sqlStatement } } else { - $clauses = array(); + $fullTextFields = array(); // array (string $sqlColumnName) + $clauses = array(); // array (string $sqlExpression) foreach ($tableValues['fields'] as $fieldName => $fieldType) { if ($fieldType == 'Int') { @@ -150,10 +151,14 @@ $sqlStatement } } else { - $clauses[] = "$fieldName LIKE {$this->toSqlWildCard($queryText)}"; + $fullTextFields[] = $fieldName; } } + if (!empty($fullTextFields)) { + $clauses[] = $this->matchText($tableName, $fullTextFields, $queryText); + } + if (empty($clauses)) { continue; } @@ -191,6 +196,51 @@ GROUP BY {$tableValues['id']} return CRM_Core_DAO::singleValueQuery($rowCount); } + /** + * Create a SQL expression for matching against a list of + * text columns. + * + * @param string $table eg "civicrm_note" or "civicrm_note mynote" + * @param array|string $fullTextFields list of field names + * @param string $queryText + * @return array + */ + public function matchText($table, $fullTextFields, $queryText) { + if (strpos($table, ' ') === FALSE) { + $tableName = $tableAlias = $table; + } else { + list ($tableName, $tableAlias) = explode(' ', $table); + } + if (is_scalar($fullTextFields)) { + $fullTextFields = array($fullTextFields); + } + + $clauses = array(); + if (CRM_Core_InnoDBIndexer::singleton()->hasDeclaredIndex($tableName, $fullTextFields)) { + $strtolower = function_exists('mb_strtolower') ? 'mb_strtolower' : 'strtolower'; + + $prefixedFieldNames = array(); + foreach ($fullTextFields as $fieldName) { + $prefixedFieldNames[] = "$tableAlias.$fieldName"; + } + + $clauses[] = sprintf("MATCH (%s) AGAINST ('%s')", + implode(',', $prefixedFieldNames), + $strtolower(CRM_Core_DAO::escapeString($queryText)) + ); + } + else { + //CRM_Core_Session::setStatus(ts('Cannot use FTS for %1 (%2)', array( + // 1 => $table, + // 2 => implode(', ', $fullTextFields), + //))); + foreach ($fullTextFields as $fieldName) { + $clauses[] = "$tableAlias.$fieldName LIKE {$this->toSqlWildCard($queryText)}"; + } + } + return implode(' OR ', $clauses); + } + /** * Format text to include wild card characters at beginning and end * diff --git a/CRM/Contact/Form/Search/Custom/FullText/Activity.php b/CRM/Contact/Form/Search/Custom/FullText/Activity.php index 0e2d90ec50..7b981c46ba 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Activity.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Activity.php @@ -56,6 +56,8 @@ class CRM_Contact_Form_Search_Custom_FullText_Activity extends CRM_Contact_Form_ * @return int the total number of matches */ function fillActivityIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " @@ -66,10 +68,11 @@ INNER JOIN civicrm_contact c ON cat.contact_id = c.id LEFT JOIN civicrm_email e ON cat.contact_id = e.contact_id LEFT JOIN civicrm_option_group og ON og.name = 'activity_type' LEFT JOIN civicrm_option_value ov ON ( ov.option_group_id = og.id ) -WHERE ( (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) OR - ( e.email LIKE {$this->toSqlWildCard($queryText)} AND - ca.activity_type_id = ov.value AND - ov.name IN ('Inbound Email', 'Email') ) ) +WHERE ( + ({$this->matchText('civicrm_contact c', array('sort_name', 'display_name', 'nick_name'), $queryText)}) + OR + ({$this->matchText('civicrm_email e', 'email', $queryText)} AND ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email') ) + ) AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) AND (c.is_deleted = 0 OR c.is_deleted IS NULL) "; @@ -81,7 +84,7 @@ INNER JOIN civicrm_tag t ON et.tag_id = t.id INNER JOIN civicrm_activity ca ON et.entity_id = ca.id WHERE et.entity_table = 'civicrm_activity' AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} +AND ({$this->matchText('civicrm_tag t', 'name', $queryText)}) AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) GROUP BY et.entity_id "; @@ -89,7 +92,7 @@ GROUP BY et.entity_id $contactSQL[] = " SELECT distinct ca.id FROM civicrm_activity ca -WHERE (ca.subject LIKE {$this->toSqlWildCard($queryText)} OR ca.details LIKE {$this->toSqlWildCard($queryText)}) +WHERE ({$this->matchText('civicrm_activity ca', array('subject', 'details'), $queryText)}) AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) "; diff --git a/CRM/Contact/Form/Search/Custom/FullText/Case.php b/CRM/Contact/Form/Search/Custom/FullText/Case.php index a99ac6cb66..98fc5bdeca 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Case.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Case.php @@ -57,6 +57,8 @@ class CRM_Contact_Form_Search_Custom_FullText_Case extends CRM_Contact_Form_Sear * @return int the total number of matches */ function fillCaseIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " @@ -64,7 +66,7 @@ SELECT distinct cc.id FROM civicrm_case cc LEFT JOIN civicrm_case_contact ccc ON cc.id = ccc.case_id LEFT JOIN civicrm_contact c ON ccc.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) +WHERE ({$this->matchText('civicrm_contact c', array('sort_name', 'display_name', 'nick_name'), $queryText)}) AND (cc.is_deleted = 0 OR cc.is_deleted IS NULL) "; @@ -85,7 +87,7 @@ FROM civicrm_entity_tag et INNER JOIN civicrm_tag t ON et.tag_id = t.id WHERE et.entity_table = 'civicrm_case' AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} +AND ({$this->matchText('civicrm_tag t', 'name', $queryText)}) GROUP BY et.entity_id "; diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contact.php b/CRM/Contact/Form/Search/Custom/FullText/Contact.php index 6839a596b1..0eb806b026 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Contact.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Contact.php @@ -56,6 +56,8 @@ class CRM_Contact_Form_Search_Custom_FullText_Contact extends CRM_Contact_Form_S * @return int the total number of matches */ function fillContactIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " SELECT et.entity_id @@ -63,7 +65,7 @@ FROM civicrm_entity_tag et INNER JOIN civicrm_tag t ON et.tag_id = t.id WHERE et.entity_table = 'civicrm_contact' AND et.tag_id = t.id -AND t.name LIKE {$this->toSqlWildCard($queryText)} +AND ({$this->matchText('civicrm_tag t', 'name', $queryText)}) GROUP BY et.entity_id "; diff --git a/CRM/Contact/Form/Search/Custom/FullText/Contribution.php b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php index 219ea65a85..b4fad6dd7e 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Contribution.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Contribution.php @@ -60,13 +60,14 @@ class CRM_Contact_Form_Search_Custom_FullText_Contribution extends CRM_Contact_F * @return int the total number of matches */ function fillContributionIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " SELECT distinct cc.id FROM civicrm_contribution cc INNER JOIN civicrm_contact c ON cc.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR - c.display_name LIKE {$this->toSqlWildCard($queryText)}) +WHERE ({$this->matchText('civicrm_contact c', array('sort_name', 'display_name', 'nick_name'), $queryText)}) "; $tables = array( 'civicrm_contribution' => array( @@ -76,8 +77,8 @@ WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR 'amount_level' => NULL, 'trxn_Id' => NULL, 'invoice_id' => NULL, - 'check_number' => (is_numeric($queryText)) ? 'Int' : NULL, - 'total_amount' => (is_numeric($queryText)) ? 'Int' : NULL, + 'check_number' => 'Int', // Odd: This is really a VARCHAR, so why are we searching like an INT? + 'total_amount' => 'Int', ), ), 'sql' => $contactSQL, diff --git a/CRM/Contact/Form/Search/Custom/FullText/Membership.php b/CRM/Contact/Form/Search/Custom/FullText/Membership.php index 1cebe0a93f..2ec54eeb5d 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Membership.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Membership.php @@ -60,12 +60,14 @@ class CRM_Contact_Form_Search_Custom_FullText_Membership extends CRM_Contact_For * @return int the total number of matches */ function fillMembershipIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " SELECT distinct cm.id FROM civicrm_membership cm INNER JOIN civicrm_contact c ON cm.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) +WHERE ({$this->matchText('civicrm_contact c', array('sort_name', 'display_name', 'nick_name'), $queryText)}) "; $tables = array( 'civicrm_membership' => array( diff --git a/CRM/Contact/Form/Search/Custom/FullText/Participant.php b/CRM/Contact/Form/Search/Custom/FullText/Participant.php index b1b623ae8b..5af52f39bb 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Participant.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Participant.php @@ -60,12 +60,14 @@ class CRM_Contact_Form_Search_Custom_FullText_Participant extends CRM_Contact_Fo * @return int the total number of matches */ function fillParticipantIDs($queryText, $entityIDTableName, $limit) { + // Note: For available full-text indices, see CRM_Core_InnoDBIndexer + $contactSQL = array(); $contactSQL[] = " SELECT distinct cp.id FROM civicrm_participant cp INNER JOIN civicrm_contact c ON cp.contact_id = c.id -WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_name LIKE {$this->toSqlWildCard($queryText)}) +WHERE ({$this->matchText('civicrm_contact c', array('sort_name', 'display_name', 'nick_name'), $queryText)}) "; $tables = array( 'civicrm_participant' => array( @@ -73,7 +75,7 @@ WHERE (c.sort_name LIKE {$this->toSqlWildCard($queryText)} OR c.display_nam 'fields' => array( 'source' => NULL, 'fee_level' => NULL, - 'fee_amount' => (is_numeric($queryText)) ? 'Int' : NULL, + 'fee_amount' => 'Int', ), ), 'sql' => $contactSQL, diff --git a/CRM/Core/InnoDBIndexer.php b/CRM/Core/InnoDBIndexer.php index c9bc4c9af7..cce2802e5a 100644 --- a/CRM/Core/InnoDBIndexer.php +++ b/CRM/Core/InnoDBIndexer.php @@ -37,21 +37,37 @@ class CRM_Core_InnoDBIndexer { */ private static $singleton = NULL; + /** + * @param bool $fresh + * @return CRM_Core_InnoDBIndexer + */ public static function singleton($fresh = FALSE) { if ($fresh || self::$singleton === NULL) { $indices = array( 'civicrm_address' => array( array('street_address', 'city', 'postal_code') ), + 'civicrm_activity' => array( + array('subject', 'details'), + ), 'civicrm_contact' => array( array('sort_name', 'nick_name', 'display_name'), ), + 'civicrm_contribution' => array( + array('source', 'amount_level', 'trxn_Id', 'invoice_id'), + ), 'civicrm_email' => array( array('email') ), + 'civicrm_membership' => array( + array('source'), + ), 'civicrm_note' => array( array('subject', 'note'), ), + 'civicrm_participant' => array( + array('source', 'fee_level'), + ), 'civicrm_phone' => array( array('phone'), ), -- 2.25.1