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    *
 159    * @return array
 160    *   (string $indexName => string $indexName)
 161    */
 162   public function findActualFtsIndexNames(string $table): array {
 163     $dao = CRM_Core_DAO::executeQuery("
 164   SELECT index_name as index_name
 165   FROM information_Schema.STATISTICS
 166   WHERE table_schema = '" . CRM_Core_DAO::getDatabaseName() . "'
 167     AND table_name = '$table'
 168     AND index_type = 'FULLTEXT'
 169   GROUP BY index_name
 170     ");
 171
 172     $indexNames = [];
 173     while ($dao->fetch()) {
 174       $indexNames[$dao->index_name] = $dao->index_name;
 175     }
 176     return $indexNames;
 177   }
 178
 179   /**
 180    * Generate a "CREATE INDEX" statement for each desired
 181    * FTS index.
 182    *
 183    * @param $table
 184    *
 185    * @return array
 186    *   (string $indexName => string $sql)
 187    */
 188   public function buildIndexSql($table): array {
 189     // array (string $idxName => string $sql)
 190     $sqls = [];
 191     if ($this->isActive && isset($this->indices[$table])) {
 192       foreach ($this->indices[$table] as $fields) {
 193         $name = self::IDX_PREFIX . md5($table . '::' . implode(',', $fields));
 194         $sqls[$name] = sprintf('CREATE FULLTEXT INDEX %s ON %s (%s)', $name, $table, implode(',', $fields));
 195       }
 196     }
 197     return $sqls;
 198   }
 199
 200   /**
 201    * Generate a "DROP INDEX" statement for each existing FTS index.
 202    *
 203    * @param string $table
 204    *
 205    * @return array
 206    *   (string $idxName => string $sql)
 207    */
 208   public function dropIndexSql($table) {
 209     $sqls = [];
 210     $names = $this->findActualFtsIndexNames($table);
 211     foreach ($names as $name) {
 212       $sqls[$name] = sprintf("DROP INDEX %s ON %s", $name, $table);
 213     }
 214     return $sqls;
 215   }
 216
 217   /**
 218    * Construct a set of SQL statements which will create (or preserve)
 219    * required indices and destroy unneeded indices.
 220    *
 221    * @param string $table
 222    *
 223    * @return array
 224    */
 225   public function reconcileIndexSqls($table) {
 226     $buildIndexSqls = $this->buildIndexSql($table);
 227     $dropIndexSqls = $this->dropIndexSql($table);
 228
 229     $allIndexNames = array_unique(array_merge(
 230       array_keys($dropIndexSqls),
 231       array_keys($buildIndexSqls)
 232     ));
 233
 234     $todoSqls = [];
 235     foreach ($allIndexNames as $indexName) {
 236       if (isset($buildIndexSqls[$indexName]) && isset($dropIndexSqls[$indexName])) {
 237         // already exists
 238       }
 239       elseif (isset($buildIndexSqls[$indexName])) {
 240         $todoSqls[] = $buildIndexSqls[$indexName];
 241       }
 242       else {
 243         $todoSqls[] = $dropIndexSqls[$indexName];
 244       }
 245     }
 246     return $todoSqls;
 247   }
 248
 249   /**
 250    * Put the indices into a normalized format.
 251    *
 252    * @param $indices
 253    * @return array
 254    */
 255   public function normalizeIndices($indices) {
 256     $result = [];
 257     foreach ($indices as $table => $indicesByTable) {
 258       foreach ($indicesByTable as $k => $fields) {
 259         sort($fields);
 260         $result[$table][] = $fields;
 261       }
 262     }
 263     return $result;
 264   }
 265
 266   /**
 267    * Setter for isActive.
 268    *
 269    * @param bool $isActive
 270    */
 271   public function setActive($isActive) {
 272     $this->isActive = $isActive;
 273   }
 274
 275   /**
 276    * Getter for isActive.
 277    *
 278    * @return bool
 279    */
 280   public function getActive() {
 281     return $this->isActive;
 282   }
 283
 284 }