CRM/Core/InnoDBIndexer.php

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