CRM/Core/InnoDBIndexer.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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  * The InnoDB indexer is responsible for creating and destroying
  30  * full-text indices on InnoDB classes.
  31  */
  32 class CRM_Core_InnoDBIndexer {
  33   const IDX_PREFIX = "civicrm_fts_";
  34
  35   /**
  36    * @var CRM_Core_InnoDBIndexer
  37    */
  38   private static $singleton = NULL;
  39
  40   /**
  41    * @param bool $fresh
  42    * @return CRM_Core_InnoDBIndexer
  43    */
  44   public static function singleton($fresh = FALSE) {
  45     if ($fresh || self::$singleton === NULL) {
  46       $indices = array(
  47         'civicrm_address' => array(
  48           array('street_address', 'city', 'postal_code'),
  49         ),
  50         'civicrm_activity' => array(
  51           array('subject', 'details'),
  52         ),
  53         'civicrm_contact' => array(
  54           array('sort_name', 'nick_name', 'display_name'),
  55         ),
  56         'civicrm_contribution' => array(
  57           array('source', 'amount_level', 'trxn_Id', 'invoice_id'),
  58         ),
  59         'civicrm_email' => array(
  60           array('email'),
  61         ),
  62         'civicrm_membership' => array(
  63           array('source'),
  64         ),
  65         'civicrm_note' => array(
  66           array('subject', 'note'),
  67         ),
  68         'civicrm_participant' => array(
  69           array('source', 'fee_level'),
  70         ),
  71         'civicrm_phone' => array(
  72           array('phone'),
  73         ),
  74         'civicrm_tag' => array(
  75           array('name'),
  76         ),
  77       );
  78       $active = CRM_Core_BAO_Setting::getItem(CRM_Core_BAO_Setting::SEARCH_PREFERENCES_NAME, 'enable_innodb_fts', NULL, FALSE);
  79       self::$singleton = new self($active, $indices);
  80     }
  81     return self::$singleton;
  82   }
  83
  84   /**
  85    * (Setting Callback)
  86    * Respond to changes in the "enable_innodb_fts" setting
  87    *
  88    * @param bool $oldValue
  89    * @param bool $newValue
  90    * @param array $metadata
  91    *   Specification of the setting (per *.settings.php).
  92    */
  93   public static function onToggleFts($oldValue, $newValue, $metadata) {
  94     $indexer = CRM_Core_InnoDBIndexer::singleton();
  95     $indexer->setActive($newValue);
  96     $indexer->fixSchemaDifferences();
  97   }
  98
  99   /**
 100    * @var array (string $table => array $indices)
 101    *
 102    * ex: $indices['civicrm_contact'][0] = array('first_name', 'last_name');
 103    */
 104   protected $indices;
 105
 106   /**
 107    * @var bool
 108    */
 109   protected $isActive;
 110
 111   /**
 112    * Class constructor.
 113    *
 114    * @param $isActive
 115    * @param $indices
 116    */
 117   public function __construct($isActive, $indices) {
 118     $this->isActive = $isActive;
 119     $this->indices = $this->normalizeIndices($indices);
 120   }
 121
 122   /**
 123    * Fix schema differences.
 124    *
 125    * Limitation: This won't pick up stale indices on tables which are not
 126    * declared in $this->indices. That's not much of an issue for now b/c
 127    * we have a static list of tables.
 128    */
 129   public function fixSchemaDifferences() {
 130     foreach ($this->indices as $tableName => $ign) {
 131       $todoSqls = $this->reconcileIndexSqls($tableName);
 132       foreach ($todoSqls as $todoSql) {
 133         CRM_Core_DAO::executeQuery($todoSql);
 134       }
 135     }
 136   }
 137
 138   /**
 139    * Determine if an index is expected to exist.
 140    *
 141    * @param string $table
 142    * @param array $fields
 143    *   List of field names that must be in the index.
 144    * @return bool
 145    */
 146   public function hasDeclaredIndex($table, $fields) {
 147     if (!$this->isActive) {
 148       return FALSE;
 149     }
 150
 151     if (isset($this->indices[$table])) {
 152       foreach ($this->indices[$table] as $idxFields) {
 153         // TODO determine if $idxFields must be exact match or merely a subset
 154         // if (sort($fields) == sort($idxFields)) {
 155         if (array_diff($fields, $idxFields) == array()) {
 156           return TRUE;
 157         }
 158       }
 159     }
 160
 161     return FALSE;
 162   }
 163
 164   /**
 165    * Get a list of FTS index names that are currently defined in the database.
 166    *
 167    * @param string $table
 168    * @return array
 169    *   (string $indexName => string $indexName)
 170    */
 171   public function findActualFtsIndexNames($table) {
 172     $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
 173     if (version_compare($mysqlVersion, '5.6', '<')) {
 174       // If we're not on 5.6+, then there cannot be any InnoDB FTS indices!
 175       // Also: information_schema.innodb_sys_indexes is only available on 5.6+.
 176       return array();
 177     }
 178
 179     // Note: this only works in MySQL 5.6,  but this whole system is intended to only work in MySQL 5.6
 180     $sql = "
 181       SELECT i.name as index_name
 182       FROM information_schema.innodb_sys_tables t
 183       JOIN information_schema.innodb_sys_indexes i USING (table_id)
 184       WHERE t.name = concat(database(),'/$table')
 185       AND i.name like '" . self::IDX_PREFIX . "%'
 186       ";
 187     $dao = CRM_Core_DAO::executeQuery($sql);
 188     $indexNames = array();
 189     while ($dao->fetch()) {
 190       $indexNames[$dao->index_name] = $dao->index_name;
 191     }
 192     return $indexNames;
 193   }
 194
 195   /**
 196    * Generate a "CREATE INDEX" statement for each desired
 197    * FTS index.
 198    *
 199    * @param $table
 200    *
 201    * @return array
 202    *   (string $indexName => string $sql)
 203    */
 204   public function buildIndexSql($table) {
 205     $sqls = array(); // array (string $idxName => string $sql)
 206     if ($this->isActive && isset($this->indices[$table])) {
 207       foreach ($this->indices[$table] as $fields) {
 208         $name = self::IDX_PREFIX . md5($table . '::' . implode(',', $fields));
 209         $sqls[$name] = sprintf("CREATE FULLTEXT INDEX %s ON %s (%s)", $name, $table, implode(',', $fields));
 210       }
 211     }
 212     return $sqls;
 213   }
 214
 215   /**
 216    * Generate a "DROP INDEX" statement for each existing FTS index.
 217    *
 218    * @param string $table
 219    *
 220    * @return array
 221    *   (string $idxName => string $sql)
 222    */
 223   public function dropIndexSql($table) {
 224     $sqls = array();
 225     $names = $this->findActualFtsIndexNames($table);
 226     foreach ($names as $name) {
 227       $sqls[$name] = sprintf("DROP INDEX %s ON %s", $name, $table);
 228     }
 229     return $sqls;
 230   }
 231
 232   /**
 233    * Construct a set of SQL statements which will create (or preserve)
 234    * required indices and destroy unneeded indices.
 235    *
 236    * @param string $table
 237    *
 238    * @return array
 239    */
 240   public function reconcileIndexSqls($table) {
 241     $buildIndexSqls = $this->buildIndexSql($table);
 242     $dropIndexSqls = $this->dropIndexSql($table);
 243
 244     $allIndexNames = array_unique(array_merge(
 245       array_keys($dropIndexSqls),
 246       array_keys($buildIndexSqls)
 247     ));
 248
 249     $todoSqls = array();
 250     foreach ($allIndexNames as $indexName) {
 251       if (isset($buildIndexSqls[$indexName]) && isset($dropIndexSqls[$indexName])) {
 252         // already exists
 253       }
 254       elseif (isset($buildIndexSqls[$indexName])) {
 255         $todoSqls[] = $buildIndexSqls[$indexName];
 256       }
 257       else {
 258         $todoSqls[] = $dropIndexSqls[$indexName];
 259       }
 260     }
 261     return $todoSqls;
 262   }
 263
 264   /**
 265    * Put the indices into a normalized format.
 266    *
 267    * @param $indices
 268    * @return array
 269    */
 270   public function normalizeIndices($indices) {
 271     $result = array();
 272     foreach ($indices as $table => $indicesByTable) {
 273       foreach ($indicesByTable as $k => $fields) {
 274         sort($fields);
 275         $result[$table][] = $fields;
 276       }
 277     }
 278     return $result;
 279   }
 280
 281   /**
 282    * Setter for isActive.
 283    *
 284    * @param bool $isActive
 285    */
 286   public function setActive($isActive) {
 287     $this->isActive = $isActive;
 288   }
 289
 290   /**
 291    * Getter for isActive.
 292    *
 293    * @return bool
 294    */
 295   public function getActive() {
 296     return $this->isActive;
 297   }
 298
 299 }