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->assertTrue(empty($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 public function testReconcileMissingIndices() {
216 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_contact DROP INDEX index_sort_name');
217 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
218 $this->assertEquals([
219 'civicrm_contact' => [
221 'name' => 'index_sort_name',
222 'field' => ['sort_name'],
223 'localizable' => FALSE,
224 'sig' => 'civicrm_contact::0::sort_name',
228 $this->callAPISuccess('System', 'updateindexes', []);
229 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
230 $this->assertTrue(empty($missingIndices));
234 * Check for partial indices
236 public function testPartialIndices() {
238 'index_all' => 'civicrm_prevnext_cache',
239 'UI_entity_id_entity_table_tag_id' => 'civicrm_entity_tag',
241 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_prevnext_cache', 'index_all');
242 //Missing Column `is_selected`.
243 CRM_Core_DAO
::executeQuery('CREATE INDEX index_all ON civicrm_prevnext_cache (cachekey, entity_id1, entity_id2, entity_table)');
244 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
245 $this->assertNotEmpty($missingIndices);
247 CRM_Core_BAO_SchemaHandler
::dropIndexIfExists('civicrm_entity_tag', 'UI_entity_id_entity_table_tag_id');
248 //Test incorrect Ordering(correct order defined is entity_id and then entity_table, tag_id).
249 CRM_Core_DAO
::executeQuery('CREATE INDEX UI_entity_id_entity_table_tag_id ON civicrm_entity_tag (entity_table, entity_id, tag_id)');
250 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices(TRUE);
251 $this->assertNotEmpty($missingIndices);
252 $this->assertEquals(array_values($tables), array_keys($missingIndices));
254 //Check if both indices are deleted.
255 $indices = CRM_Core_BAO_SchemaHandler
::getIndexes($tables);
256 foreach ($tables as $index => $tableName) {
257 $this->assertFalse(in_array($index, array_keys($indices[$tableName])));
259 //Drop false index and create again.
260 CRM_Core_BAO_SchemaHandler
::createMissingIndices($missingIndices);
261 //Both vars should be empty now.
262 $missingIndices = CRM_Core_BAO_SchemaHandler
::getMissingIndices();
263 $this->assertEmpty($missingIndices);
267 * Test index signatures are added correctly
269 public function testAddIndexSignatures() {
272 'field' => ['id', 'name(3)'],
276 'field' => ['title'],
279 CRM_Core_BAO_SchemaHandler
::addIndexSignature('my_table', $indices);
280 $this->assertEquals($indices['one']['sig'], 'my_table::1::id::name(3)');
281 $this->assertEquals($indices['two']['sig'], 'my_table::0::title');
285 * Test that columns are dropped
287 public function testDropColumn() {
288 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS `civicrm_test_drop_column`');
289 CRM_Core_DAO
::executeQuery('CREATE TABLE `civicrm_test_drop_column` (`id` int(10), `col1` varchar(255), `col2` varchar(255))');
291 // test with logging enabled to ensure log triggers don't break anything
292 $schema = new CRM_Logging_Schema();
293 $schema->enableLogging();
296 'table_name' => 'civicrm_test_drop_column',
297 'operation' => 'delete',
299 'type' => 'varchar(255)',
301 'searchable' => FALSE,
305 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
307 $create_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
308 while ($create_table->fetch()) {
309 $this->assertNotContains('col1', $create_table->Create_Table
);
310 $this->assertContains('col2', $create_table->Create_Table
);
314 $alterParams['name'] = 'col2';
315 CRM_Core_DAO
::executeQuery(CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($alterParams, FALSE));
317 $create_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_drop_column");
318 while ($create_table->fetch()) {
319 $this->assertNotContains('col2', $create_table->Create_Table
);
324 * Tests the function that generates sql to modify fields.
326 public function testBuildFieldChangeSql() {
328 'table_name' => 'big_table',
329 'operation' => 'add',
333 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
334 $this->assertEquals("ALTER TABLE big_table
335 ADD COLUMN `big_bob` text", trim($sql));
337 $params['operation'] = 'modify';
338 $params['comment'] = 'super big';
339 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
340 $this->assertEquals("ALTER TABLE big_table
341 MODIFY `big_bob` text COMMENT 'super big'", trim($sql));
343 $params['operation'] = 'delete';
344 $sql = CRM_Core_BAO_SchemaHandler
::buildFieldChangeSql($params, FALSE);
345 $this->assertEquals('ALTER TABLE big_table DROP COLUMN `big_bob`', trim($sql));