CRM/Core/InnoDBIndexer.php

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