From 49186f94f51fc53ab6fc8be0193515c3e5c9ff51 Mon Sep 17 00:00:00 2001 From: Aidan Saunders Date: Fri, 14 Oct 2016 22:20:14 +0100 Subject: [PATCH] CRM-20312 add api wrapper for updating indexes --- CRM/Core/BAO/SchemaHandler.php | 106 ++++++++++++++++-- CRM/Utils/Check/Component/Schema.php | 59 ++++++++++ api/v3/System.php | 11 ++ .../CRM/Core/BAO/SchemaHandlerTest.php | 88 +++++++++++++++ 4 files changed, 257 insertions(+), 7 deletions(-) create mode 100644 CRM/Utils/Check/Component/Schema.php diff --git a/CRM/Core/BAO/SchemaHandler.php b/CRM/Core/BAO/SchemaHandler.php index 83c34d5852..fbf194411a 100644 --- a/CRM/Core/BAO/SchemaHandler.php +++ b/CRM/Core/BAO/SchemaHandler.php @@ -357,14 +357,14 @@ ALTER TABLE {$tableName} } /** - * Delete a CiviCRM-table + * Delete a CiviCRM-table. * * @param string $tableName * Name of the table to be created. */ public static function dropTable($tableName) { $sql = "DROP TABLE $tableName"; - $dao = CRM_Core_DAO::executeQuery($sql); + CRM_Core_DAO::executeQuery($sql); } /** @@ -406,7 +406,7 @@ ADD INDEX `FK_{$tableName}_entity_id` ( `entity_id` )"; DROP INDEX `FK_{$tableName}_entity_id` , ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )"; } - $dao = CRM_Core_DAO::executeQuery($sql); + CRM_Core_DAO::executeQuery($sql); } /** @@ -506,6 +506,32 @@ ADD UNIQUE INDEX `unique_entity_id` ( `entity_id` )"; } } + /** + * Get indexes for tables + * @param array $tables + * array of table names to find indexes for + * + * @return array('tableName' => array('index1', 'index2')) + */ + public static function getIndexes($tables) { + $indexes = array(); + foreach ($tables as $table) { + $query = "SHOW INDEX FROM $table"; + $dao = CRM_Core_DAO::executeQuery($query); + + $tableIndexes = array(); + while ($dao->fetch()) { + $tableIndexes[$dao->Key_name]['name'] = $dao->Key_name; + $tableIndexes[$dao->Key_name]['field'][] = $dao->Column_name . + ($dao->Sub_part ? '(' . $dao->Sub_part . ')' : ''); + $tableIndexes[$dao->Key_name]['unique'] = ($dao->Non_unique == 0 ? 1 : 0); + } + $indexes[$table] = $tableIndexes; + $dao->free(); + } + return $indexes; + } + /** * Drop an index if one by that name exists. * @@ -581,7 +607,7 @@ MODIFY {$columnName} varchar( $length ) * @param string $tableName * @param array $indexName * - * @return \CRM_Core_DAO|object + * @return bool */ public static function checkIfIndexExists($tableName, $indexName) { $result = CRM_Core_DAO::executeQuery( @@ -595,12 +621,12 @@ MODIFY {$columnName} varchar( $length ) } /** - * Check if the table has a specified column + * Check if the table has a specified column. * * @param string $tableName * @param string $columnName * - * @return \CRM_Core_DAO|object + * @return bool */ public static function checkIfFieldExists($tableName, $columnName) { $result = CRM_Core_DAO::executeQuery( @@ -614,10 +640,12 @@ MODIFY {$columnName} varchar( $length ) } /** - * Remove a foreign key from a table if it exists + * Remove a foreign key from a table if it exists. * * @param $table_name * @param $constraint_name + * + * @return bool */ public static function safeRemoveFK($table_name, $constraint_name) { @@ -658,4 +686,68 @@ MODIFY {$columnName} varchar( $length ) } } + /** + * Compare the indices specified in the XML files with those in the DB. + * + * @return array + * index specifications + */ + public static function getMissingIndices() { + $requiredSigs = $existingSigs = array(); + // Get the indices defined (originally) in the xml files + $requiredIndices = CRM_Core_DAO_AllCoreTables::indices(); + foreach ($requiredIndices as $table => $indices) { + $reqSigs[] = CRM_Utils_Array::collect('sig', $indices); + } + CRM_Utils_Array::flatten($reqSigs, $requiredSigs); + + // Get the indices in the database + $existingIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array_keys($requiredIndices)); + foreach ($existingIndices as $table => $indices) { + CRM_Core_BAO_SchemaHandler::addIndexSignature($table, $indices); + $extSigs[] = CRM_Utils_Array::collect('sig', $indices); + } + CRM_Utils_Array::flatten($extSigs, $existingSigs); + + // Compare + $missingSigs = array_diff($requiredSigs, $existingSigs); + // Get missing indices + $missingIndices = array(); + foreach ($missingSigs as $sig) { + $sigParts = explode('::', $sig); + foreach ($requiredIndices[$sigParts[0]] as $index) { + if ($index['sig'] == $sig) { + $missingIndices[$sigParts[0]][] = $index; + continue; + } + } + } + return $missingIndices; + } + + /** + * Create missing indices. + * + * @param array $missingIndices as returned by getMissingIndices() + */ + public static function createMissingIndices($missingIndices) { + $queries = array(); + foreach ($missingIndices as $table => $indexList) { + foreach ($indexList as $index) { + $queries[] = "CREATE " . + (array_key_exists('unique', $index) && $index['unique'] ? 'UNIQUE ' : '') . + "INDEX {$index['name']} ON {$table} (" . + implode(", ", $index['field']) . + ")"; + } + } + + /* FIXME potential problem if index name already exists, so check before creating */ + $dao = new CRM_Core_DAO(); + foreach ($queries as $query) { + $dao->query($query, FALSE); + } + $dao->free(); + } + } diff --git a/CRM/Utils/Check/Component/Schema.php b/CRM/Utils/Check/Component/Schema.php new file mode 100644 index 0000000000..75c20a6b5d --- /dev/null +++ b/CRM/Utils/Check/Component/Schema.php @@ -0,0 +1,59 @@ + 'https://civicrm.org/blog/totten/psa-please-verify-php-extension-mysqli', + // 2 => 'mysqli', + // ) + ), + ts('Performance warning: Rebuild indices'), + \Psr\Log\LogLevel::WARNING, + 'fa-server' + ); + } + return $messages; + } + +} diff --git a/api/v3/System.php b/api/v3/System.php index 086310357d..b711032972 100644 --- a/api/v3/System.php +++ b/api/v3/System.php @@ -399,4 +399,15 @@ function _civicrm_api3_system_get_whitelist($whitelistFile) { function civicrm_api3_system_updatelogtables() { $schema = new CRM_Logging_Schema(); $schema->updateLogTableSchema(); + return civicrm_api3_create_success(1); +} + +/** + * Update indexes. + * + * This adds any indexes that exist in the schema but not the database. + */ +function civicrm_api3_system_updateindexes() { + CRM_Core_BAO_SchemaHandler::createMissingIndices(CRM_Core_BAO_SchemaHandler::getMissingIndices()); + return civicrm_api3_create_success(1); } diff --git a/tests/phpunit/CRM/Core/BAO/SchemaHandlerTest.php b/tests/phpunit/CRM/Core/BAO/SchemaHandlerTest.php index 8d3f10ed79..07c59814d7 100644 --- a/tests/phpunit/CRM/Core/BAO/SchemaHandlerTest.php +++ b/tests/phpunit/CRM/Core/BAO/SchemaHandlerTest.php @@ -56,6 +56,14 @@ class CRM_Core_BAO_SchemaHandlerTest extends CiviUnitTestCase { $this->assertEquals(1, $count); } + /** + * Test CRM_Core_BAO_SchemaHandler::getIndexes() function + */ + public function testGetIndexes() { + $indexes = CRM_Core_BAO_SchemaHandler::getIndexes(array('civicrm_contact')); + $this->assertTrue(array_key_exists('index_contact_type', $indexes['civicrm_contact'])); + } + /** * Test creating an index. * @@ -172,4 +180,84 @@ class CRM_Core_BAO_SchemaHandlerTest extends CiviUnitTestCase { } } + /** + * Check there are no missing indices + */ + public function testGetMissingIndices() { + $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); + $this->assertTrue(empty($missingIndices)); + } + + /** + * Test that missing indices are correctly created + */ + public function testCreateMissingIndices() { + $indices = array( + 'test_table' => array( + 'test_index1' => array( + 'name' => 'test_index1', + 'field' => array( + 'title', + ), + 'unique' => FALSE, + ), + 'test_index2' => array( + 'name' => 'test_index2', + 'field' => array( + 'title', + ), + 'unique' => TRUE, + ), + 'test_index3' => array( + 'name' => 'test_index3', + 'field' => array( + 'title(3)', + 'name', + ), + 'unique' => FALSE, + ), + ), + ); + CRM_Core_DAO::executeQuery('DROP table if exists `test_table`'); + CRM_Core_DAO::executeQuery('CREATE table `test_table` (`title` varchar(255), `name` varchar(255))'); + CRM_Core_BAO_SchemaHandler::createMissingIndices($indices); + $actualIndices = CRM_Core_BAO_SchemaHandler::getIndexes(array('test_table')); + $this->assertEquals($actualIndices, $indices); + } + + /** + * Check there are no missing indices + */ + public function testReconcileMissingIndices() { + CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name'); + $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); + $this->assertEquals(array('civicrm_contact' => array(array( + 'name' => 'index_sort_name', + 'field' => array('sort_name'), + 'localizable' => FALSE, + 'sig' => 'civicrm_contact::0::sort_name', + ))), $missingIndices); + $this->callAPISuccess('System', 'updateindexes', array()); + $missingIndices = CRM_Core_BAO_SchemaHandler::getMissingIndices(); + $this->assertTrue(empty($missingIndices)); + } + + /** + * Test index signatures are added correctly + */ + public function testAddIndexSignatures() { + $indices = array( + 'one' => array( + 'field' => array('id', 'name(3)'), + 'unique' => TRUE, + ), + 'two' => array( + 'field' => array('title'), + ), + ); + CRM_Core_BAO_SchemaHandler::addIndexSignature('my_table', $indices); + $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)'); + $this->assertEquals($indices['two']['sig'], 'my_table::0::title'); + } + } -- 2.25.1