3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
13 * The InnoDB indexer is responsible for creating and destroying
14 * full-text indices on InnoDB classes.
16 class CRM_Core_InnoDBIndexer
{
17 const IDX_PREFIX
= "civicrm_fts_";
20 * @var CRM_Core_InnoDBIndexer
22 private static $singleton = NULL;
26 * @return CRM_Core_InnoDBIndexer
28 public static function singleton($fresh = FALSE) {
29 if ($fresh || self
::$singleton === NULL) {
31 'civicrm_address' => [
32 ['street_address', 'city', 'postal_code'],
34 'civicrm_activity' => [
35 ['subject', 'details'],
37 'civicrm_contact' => [
38 ['sort_name', 'nick_name', 'display_name'],
40 'civicrm_contribution' => [
41 ['source', 'amount_level', 'trxn_Id', 'invoice_id'],
46 'civicrm_membership' => [
52 'civicrm_participant' => [
53 ['source', 'fee_level'],
62 $active = Civi
::settings()->get('enable_innodb_fts');
63 self
::$singleton = new self($active, $indices);
65 return self
::$singleton;
70 * Respond to changes in the "enable_innodb_fts" setting
72 * @param bool $oldValue
73 * @param bool $newValue
74 * @param array $metadata
75 * Specification of the setting (per *.settings.php).
77 public static function onToggleFts($oldValue, $newValue, $metadata) {
78 if (empty($oldValue) && empty($newValue)) {
82 $indexer = CRM_Core_InnoDBIndexer
::singleton();
83 $indexer->setActive($newValue);
84 $indexer->fixSchemaDifferences();
90 * (string $table => array $indices)
92 * ex: $indices['civicrm_contact'][0] = array('first_name', 'last_name');
106 * @param bool $isActive
107 * @param array $indices
109 public function __construct($isActive, $indices) {
110 $this->isActive
= $isActive;
111 $this->indices
= $this->normalizeIndices($indices);
115 * Fix schema differences.
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.
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);
131 * Determine if an index is expected to exist.
133 * @param string $table
134 * @param array $fields
135 * List of field names that must be in the index.
138 public function hasDeclaredIndex($table, $fields) {
139 if (!$this->isActive
) {
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) == []) {
157 * Get a list of FTS index names that are currently defined in the database.
159 * @param string $table
161 * (string $indexName => string $indexName)
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+.
171 // Note: this only works in MySQL 5.6, but this whole system is intended to only work in MySQL 5.6
172 // Note: In MYSQL 8 the Tables have been renamed from INNODB_SYS_TABLES and INNODB_SYS_INDEXES to INNODB_TABLES and INNODB_INDEXES
173 $innodbTable = "innodb_sys_tables";
174 $innodbIndex = "innodb_sys_indexes";
175 if (version_compare($mysqlVersion, '8.0', '>=')) {
176 $innodbTable = "innodb_tables";
177 $innodbIndex = "innodb_indexes";
180 SELECT i.name as `index_name`
181 FROM information_schema.$innodbTable t
182 JOIN information_schema.$innodbIndex i USING (table_id)
183 WHERE t.name = concat(database(),'/$table')
184 AND i.name like '" . self
::IDX_PREFIX
. "%'
186 $dao = CRM_Core_DAO
::executeQuery($sql);
188 while ($dao->fetch()) {
189 $indexNames[$dao->index_name
] = $dao->index_name
;
195 * Generate a "CREATE INDEX" statement for each desired
201 * (string $indexName => string $sql)
203 public function buildIndexSql($table) {
204 // array (string $idxName => string $sql)
206 if ($this->isActive
&& isset($this->indices
[$table])) {
207 foreach ($this->indices
[$table] as $fields) {
208 $name = self
::IDX_PREFIX
. md5($table . '::' . implode(',', $fields));
209 $sqls[$name] = sprintf("CREATE FULLTEXT INDEX %s ON %s (%s)", $name, $table, implode(',', $fields));
216 * Generate a "DROP INDEX" statement for each existing FTS index.
218 * @param string $table
221 * (string $idxName => string $sql)
223 public function dropIndexSql($table) {
225 $names = $this->findActualFtsIndexNames($table);
226 foreach ($names as $name) {
227 $sqls[$name] = sprintf("DROP INDEX %s ON %s", $name, $table);
233 * Construct a set of SQL statements which will create (or preserve)
234 * required indices and destroy unneeded indices.
236 * @param string $table
240 public function reconcileIndexSqls($table) {
241 $buildIndexSqls = $this->buildIndexSql($table);
242 $dropIndexSqls = $this->dropIndexSql($table);
244 $allIndexNames = array_unique(array_merge(
245 array_keys($dropIndexSqls),
246 array_keys($buildIndexSqls)
250 foreach ($allIndexNames as $indexName) {
251 if (isset($buildIndexSqls[$indexName]) && isset($dropIndexSqls[$indexName])) {
254 elseif (isset($buildIndexSqls[$indexName])) {
255 $todoSqls[] = $buildIndexSqls[$indexName];
258 $todoSqls[] = $dropIndexSqls[$indexName];
265 * Put the indices into a normalized format.
270 public function normalizeIndices($indices) {
272 foreach ($indices as $table => $indicesByTable) {
273 foreach ($indicesByTable as $k => $fields) {
275 $result[$table][] = $fields;
282 * Setter for isActive.
284 * @param bool $isActive
286 public function setActive($isActive) {
287 $this->isActive
= $isActive;
291 * Getter for isActive.
295 public function getActive() {
296 return $this->isActive
;