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 * Class CRM_Core_BAO_SchemaHandlerTest.
15 * These tests create and drop indexes on the civicrm_uf_join table. The indexes
16 * being added and dropped we assume will never exist.
19 class CRM_Core_BAO_SchemaHandlerTest
extends CiviUnitTestCase
{
22 * Test creating an index.
24 * We want to be sure it creates an index and exits gracefully if the index
27 public function testCreateIndex() {
28 $tables = ['civicrm_uf_join' => ['weight']];
29 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
30 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
31 $dao = CRM_Core_DAO
::executeQuery("SHOW INDEX FROM civicrm_uf_join");
34 while ($dao->fetch()) {
35 if ($dao->Column_name
== 'weight') {
37 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $dao->Key_name
);
40 $this->assertEquals(1, $count);
44 * Test CRM_Core_BAO_SchemaHandler::getIndexes() function
46 public function testGetIndexes() {
47 $indexes = CRM_Core_BAO_SchemaHandler
::getIndexes(['civicrm_contact']);
48 $this->assertTrue(array_key_exists('index_contact_type', $indexes['civicrm_contact']));
52 * Test creating an index.
54 * We want to be sure it creates an index and exits gracefully if the index
57 public function testCombinedIndex() {
58 $tables = ['civicrm_uf_join' => ['weight']];
59 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
61 $tables = ['civicrm_uf_join' => [['weight', 'module']]];
62 CRM_Core_BAO_SchemaHandler
::createIndexes($tables);
63 $dao = CRM_Core_DAO
::executeQuery("SHOW INDEX FROM civicrm_uf_join");
68 while ($dao->fetch()) {
69 if ($dao->Column_name
== 'weight') {
71 $indexes[$dao->Key_name
] = $dao->Key_name
;
73 if ($dao->Column_name
== 'module') {
75 $this->assertArrayHasKey($dao->Key_name
, $indexes);
79 foreach (array_keys($indexes) as $index) {
80 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_uf_join DROP INDEX " . $index);
82 $this->assertEquals(2, $weightCount);
86 * Test the drop index if exists function for a non-existent index.
88 public function testCheckIndexNotExists() {
89 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::checkIfIndexExists('civicrm_contact', 'magic_button'));
93 * Test the drop index if exists function for a non-existent index.
95 public function testCheckIndexExists() {
96 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::checkIfIndexExists('civicrm_contact', 'index_hash'));
100 * Test the drop index if exists function for a non-existent index.
102 public function testDropIndexNoneExists() {
103 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_contact', 'magic_button');
107 * Test the drop index if exists function.
109 public function testDropIndexExists() {
110 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_contact', 'index_hash');
111 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::checkIfIndexExists('civicrm_contact', 'index_hash'));
113 // Recreate it to clean up after the test.
114 CRM_Core_BAO_SchemaHandler
::createIndexes(['civicrm_contact' => ['hash']]);
120 public function columnTests() {
122 $columns[] = ['civicrm_contribution', 'total_amount'];
123 $columns[] = ['civicrm_contact', 'first_name'];
124 $columns[] = ['civicrm_contact', 'xxxx'];
132 * @dataProvider columnTests
134 public function testCheckIfColumnExists($tableName, $columnName) {
135 if ($columnName == 'xxxx') {
136 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($tableName, $columnName));
139 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::checkIfFieldExists($tableName, $columnName));
146 public function foreignKeyTests() {
148 $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_email_id'];
149 $keys[] = ['civicrm_mailing_recipients', 'FK_civicrm_mailing_recipients_id'];
154 * Test to see if we can drop foreign key
156 * @dataProvider foreignKeyTests
158 public function testSafeDropForeignKey($tableName, $key) {
159 if ($key == 'FK_civicrm_mailing_recipients_id') {
160 $this->assertFalse(CRM_Core_BAO_SchemaHandler
::safeRemoveFK('civicrm_mailing_recipients', $key));
163 $this->assertTrue(CRM_Core_BAO_SchemaHandler
::safeRemoveFK('civicrm_mailing_recipients', $key));
168 * Check there are no missing indices
170 public function testGetMissingIndices() {
171 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
172 $this->assertEmpty($missingIndices);
176 * Test that missing indices are correctly created
178 public function testCreateMissingIndices() {
182 'name' => 'test_index1',
189 'name' => 'test_index2',
196 'name' => 'test_index3',
205 CRM_Core_DAO
::executeQuery('DROP table if exists `test_table`');
206 CRM_Core_DAO
::executeQuery('CREATE table `test_table` (`title` varchar(255), `name` varchar(255))');
207 CRM_Core_BAO_SchemaHandler
::createMissingIndices($indices);
208 $actualIndices = CRM_Core_BAO_SchemaHandler
::getIndexes(['test_table']);
209 $this->assertEquals($actualIndices, $indices);
213 * Check there are no missing indices
215 * @throws \CRM_Core_Exception
217 public function testReconcileMissingIndices() {
218 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name');
219 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
220 // Check the api also retrieves them.
221 $missingIndicesAPI = $this->callAPISuccess('System', 'getmissingindices', [])['values'];
222 $this->assertEquals($missingIndices, $missingIndicesAPI);
223 $this->assertEquals([
224 'civicrm_contact' => [
226 'name' => 'index_sort_name',
227 'field' => ['sort_name'],
228 'localizable' => FALSE,
229 'sig' => 'civicrm_contact::0::sort_name',
233 $this->callAPISuccess('System', 'updateindexes', []);
234 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
235 $this->assertEmpty($missingIndices);
239 * Check for partial indices
241 public function testPartialIndices() {
243 'index_all' => 'civicrm_prevnext_cache',
244 'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
246 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_prevnext_cache', 'index_all');
247 //Missing Column `is_selected`.
248 CRM_Core_DAO
::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)');
249 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
250 $this->assertNotEmpty($missingIndices);
252 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id');
253 //Test incorrect Ordering(correct order defined is entity_id and then entity_table, tag_id).
254 CRM_Core_DAO
::executeQuery('CREATE INDEX UI_entity_id_entity_table_tag_id ON civicrm_entity_tag (entity_table, entity_id, tag_id)');
255 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices(TRUE);
256 $this->assertNotEmpty($missingIndices);
257 $this->assertEquals(array_values($tables), array_keys($missingIndices));
259 //Check if both indices are deleted.
260 $indices = CRM_Core_BAO_SchemaHandler
::getIndexes($tables);
261 foreach ($tables as $index => $tableName) {
262 $this->assertFalse(in_array($index, array_keys($indices[$tableName])));
264 //Drop false index and create again.
265 CRM_Core_BAO_SchemaHandler
::createMissingIndices($missingIndices);
266 //Both vars should be empty now.
267 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
268 $this->assertEmpty($missingIndices);
272 * Test index signatures are added correctly
274 public function testAddIndexSignatures() {
277 'field' => ['id', 'name(3)'],
281 'field' => ['title'],
284 CRM_Core_BAO_SchemaHandler
::addIndexSignature('my_table', $indices);
285 $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)');
286 $this->assertEquals($indices['two']['sig'], 'my_table::0::title');
290 * Test that columns are dropped
292 public function testDropColumn() {
293 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS `civicrm_test_drop_column`');
294 CRM_Core_DAO
::executeQuery('CREATE TABLE `civicrm_test_drop_column` (`id` int(10), `col1` varchar(255), `col2` varchar(255))');
296 // test with logging enabled to ensure log triggers don't break anything
297 $schema = new CRM_Logging_Schema();
298 $schema->enableLogging();
301 'table_name' => 'civicrm_test_drop_column',
302 'operation' => 'delete',
304 'type' => 'varchar(255)',
306 'searchable' => FALSE,
310 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
312 $create_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
313 while ($create_table->fetch()) {
314 $this->assertNotContains('col1', $create_table->Create_Table
);
315 $this->assertContains('col2', $create_table->Create_Table
);
319 $alterParams['name'] = 'col2';
320 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
322 $create_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
323 while ($create_table->fetch()) {
324 $this->assertNotContains('col2', $create_table->Create_Table
);
329 * Tests the function that generates sql to modify fields.
331 public function testBuildFieldChangeSql() {
333 'table_name' => 'big_table',
334 'operation' => 'add',
338 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
339 $this->assertEquals("ALTER TABLE big_table
340 ADD COLUMN `big_bob` text", trim($sql));
342 $params['operation'] = 'modify';
343 $params['comment'] = 'super big';
344 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
345 $this->assertEquals("ALTER TABLE big_table
346 MODIFY `big_bob` text COMMENT 'super big'", trim($sql));
348 $params['operation'] = 'delete';
349 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
350 $this->assertEquals('ALTER TABLE big_table DROP COLUMN `big_bob`', trim($sql));