CRM/Core/InnoDBIndexer.php

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