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    */
  75   public static function onToggleFts($oldValue, $newValue): void {
  76     if (empty($oldValue) && empty($newValue)) {
  77       return;
  78     }
  79
  80     $indexer = CRM_Core_InnoDBIndexer::singleton();
  81     $indexer->setActive($newValue);
  82     $indexer->fixSchemaDifferences();
  83   }
  84
  85   /**
  86    * Indices.
  87    *
  88    * (string $table => array $indices)
  89    *
  90    * ex: $indices['civicrm_contact'][0] = array('first_name', 'last_name');
  91    *
  92    * @var array
  93    */
  94   protected $indices;
  95
  96   /**
  97    * @var bool
  98    */
  99   protected $isActive;
 100
 101   /**
 102    * Class constructor.
 103    *
 104    * @param bool $isActive
 105    * @param array $indices
 106    */
 107   public function __construct($isActive, $indices) {
 108     $this->isActive = $isActive;
 109     $this->indices = $this->normalizeIndices($indices);
 110   }
 111
 112   /**
 113    * Fix schema differences.
 114    *
 115    * Limitation: This won't pick up stale indices on tables which are not
 116    * declared in $this->indices. That's not much of an issue for now b/c
 117    * we have a static list of tables.
 118    */
 119   public function fixSchemaDifferences() {
 120     foreach ($this->indices as $tableName => $ign) {
 121       $todoSqls = $this->reconcileIndexSqls($tableName);
 122       foreach ($todoSqls as $todoSql) {
 123         CRM_Core_DAO::executeQuery($todoSql);
 124       }
 125     }
 126   }
 127
 128   /**
 129    * Determine if an index is expected to exist.
 130    *
 131    * @param string $table
 132    * @param array $fields
 133    *   List of field names that must be in the index.
 134    * @return bool
 135    */
 136   public function hasDeclaredIndex($table, $fields) {
 137     if (!$this->isActive) {
 138       return FALSE;
 139     }
 140
 141     if (isset($this->indices[$table])) {
 142       foreach ($this->indices[$table] as $idxFields) {
 143         // TODO determine if $idxFields must be exact match or merely a subset
 144         // if (sort($fields) == sort($idxFields)) {
 145         if (array_diff($fields, $idxFields) == []) {
 146           return TRUE;
 147         }
 148       }
 149     }
 150
 151     return FALSE;
 152   }
 153
 154   /**
 155    * Get a list of FTS index names that are currently defined in the database.
 156    *
 157    * @param string $table
 158    * @return array
 159    *   (string $indexName => string $indexName)
 160    */
 161   public function findActualFtsIndexNames($table) {
 162     $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
 163     // Note: In MYSQL 8 the Tables have been renamed from INNODB_SYS_TABLES and INNODB_SYS_INDEXES to INNODB_TABLES and INNODB_INDEXES
 164     $innodbTable = 'innodb_sys_tables';
 165     $innodbIndex = "innodb_sys_indexes";
 166     if (version_compare($mysqlVersion, '8.0', '>=')
 167       // As of 10.4 mariadb is NOT adopting the mysql 8 table names
 168       // - this means it's likely it never will.
 169       && stripos($mysqlVersion, 'mariadb') === FALSE) {
 170       $innodbTable = 'innodb_tables';
 171       $innodbIndex = 'innodb_indexes';
 172     }
 173     $sql = "
 174       SELECT i.name as `index_name`
 175       FROM information_schema.$innodbTable t
 176       JOIN information_schema.$innodbIndex i USING (table_id)
 177       WHERE t.name = concat(database(),'/$table')
 178       AND i.name like '" . self::IDX_PREFIX . "%'
 179       ";
 180     $dao = CRM_Core_DAO::executeQuery($sql);
 181     $indexNames = [];
 182     while ($dao->fetch()) {
 183       $indexNames[$dao->index_name] = $dao->index_name;
 184     }
 185     return $indexNames;
 186   }
 187
 188   /**
 189    * Generate a "CREATE INDEX" statement for each desired
 190    * FTS index.
 191    *
 192    * @param $table
 193    *
 194    * @return array
 195    *   (string $indexName => string $sql)
 196    */
 197   public function buildIndexSql($table): array {
 198     // array (string $idxName => string $sql)
 199     $sqls = [];
 200     if ($this->isActive && isset($this->indices[$table])) {
 201       foreach ($this->indices[$table] as $fields) {
 202         $name = self::IDX_PREFIX . md5($table . '::' . implode(',', $fields));
 203         $sqls[$name] = sprintf("CREATE FULLTEXT INDEX %s ON %s (%s)", $name, $table, implode(',', $fields));
 204       }
 205     }
 206     return $sqls;
 207   }
 208
 209   /**
 210    * Generate a "DROP INDEX" statement for each existing FTS index.
 211    *
 212    * @param string $table
 213    *
 214    * @return array
 215    *   (string $idxName => string $sql)
 216    */
 217   public function dropIndexSql($table) {
 218     $sqls = [];
 219     $names = $this->findActualFtsIndexNames($table);
 220     foreach ($names as $name) {
 221       $sqls[$name] = sprintf("DROP INDEX %s ON %s", $name, $table);
 222     }
 223     return $sqls;
 224   }
 225
 226   /**
 227    * Construct a set of SQL statements which will create (or preserve)
 228    * required indices and destroy unneeded indices.
 229    *
 230    * @param string $table
 231    *
 232    * @return array
 233    */
 234   public function reconcileIndexSqls($table) {
 235     $buildIndexSqls = $this->buildIndexSql($table);
 236     $dropIndexSqls = $this->dropIndexSql($table);
 237
 238     $allIndexNames = array_unique(array_merge(
 239       array_keys($dropIndexSqls),
 240       array_keys($buildIndexSqls)
 241     ));
 242
 243     $todoSqls = [];
 244     foreach ($allIndexNames as $indexName) {
 245       if (isset($buildIndexSqls[$indexName]) && isset($dropIndexSqls[$indexName])) {
 246         // already exists
 247       }
 248       elseif (isset($buildIndexSqls[$indexName])) {
 249         $todoSqls[] = $buildIndexSqls[$indexName];
 250       }
 251       else {
 252         $todoSqls[] = $dropIndexSqls[$indexName];
 253       }
 254     }
 255     return $todoSqls;
 256   }
 257
 258   /**
 259    * Put the indices into a normalized format.
 260    *
 261    * @param $indices
 262    * @return array
 263    */
 264   public function normalizeIndices($indices) {
 265     $result = [];
 266     foreach ($indices as $table => $indicesByTable) {
 267       foreach ($indicesByTable as $k => $fields) {
 268         sort($fields);
 269         $result[$table][] = $fields;
 270       }
 271     }
 272     return $result;
 273   }
 274
 275   /**
 276    * Setter for isActive.
 277    *
 278    * @param bool $isActive
 279    */
 280   public function setActive($isActive) {
 281     $this->isActive = $isActive;
 282   }
 283
 284   /**
 285    * Getter for isActive.
 286    *
 287    * @return bool
 288    */
 289   public function getActive() {
 290     return $this->isActive;
 291   }
 292
 293 }