Commit | Line | Data |
---|---|---|
91bd5c14 SL |
1 | <?php |
2 | ||
3 | /** | |
4 | * Class CRM_Logging_SchmeaTest | |
5 | * @group headless | |
6 | */ | |
7 | class CRM_Logging_SchemaTest extends CiviUnitTestCase { | |
8 | ||
9 | public function setUp() { | |
10 | parent::setUp(); | |
11 | } | |
12 | ||
89d7d62e | 13 | /** |
14 | * Clean up after test. | |
15 | * | |
16 | * @throws \CRM_Core_Exception | |
17 | */ | |
91bd5c14 | 18 | public function tearDown() { |
16142ce2 SL |
19 | $schema = new CRM_Logging_Schema(); |
20 | $schema->disableLogging(); | |
89d7d62e | 21 | parent::tearDown(); |
22 | $this->quickCleanup(['civicrm_contact'], TRUE); | |
16142ce2 SL |
23 | $schema->dropAllLogTables(); |
24 | CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_table"); | |
9c8a3025 | 25 | CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_column_info"); |
f34f504a PF |
26 | CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_length_change"); |
27 | CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_enum_change"); | |
91bd5c14 SL |
28 | } |
29 | ||
89d7d62e | 30 | /** |
31 | * Data provider for testing query re-writing. | |
32 | * | |
33 | * @return array | |
34 | */ | |
91bd5c14 SL |
35 | public function queryExamples() { |
36 | $examples = []; | |
37 | $examples[] = ["`modified_date` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.'", "`modified_date` timestamp NULL COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.'"]; | |
38 | $examples[] = ["`modified_date` timestamp NULL DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.'", "`modified_date` timestamp NULL COMMENT 'When the mailing (or closely related entity) was created or modified or deleted.'"]; | |
39 | return $examples; | |
40 | } | |
41 | ||
42 | /** | |
43 | * Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema | |
44 | * | |
45 | * @dataProvider queryExamples | |
46 | */ | |
47 | public function testQueryRewrite($query, $expectedQuery) { | |
48 | $this->assertEquals($expectedQuery, CRM_Logging_Schema::fixTimeStampAndNotNullSQL($query)); | |
49 | } | |
50 | ||
204aa6fb PF |
51 | /** |
52 | * Test log tables are created as InnoDB by default | |
53 | */ | |
a599e68c SL |
54 | public function testLogEngine() { |
55 | $schema = new CRM_Logging_Schema(); | |
56 | $schema->enableLogging(); | |
57 | $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl"); | |
204aa6fb PF |
58 | while ($log_table->fetch()) { |
59 | $this->assertRegexp('/ENGINE=InnoDB/', $log_table->Create_Table); | |
60 | } | |
61 | } | |
62 | ||
63 | /** | |
64 | * Test that the log table engine can be changed via hook to e.g. MyISAM | |
65 | */ | |
66 | public function testHookLogEngine() { | |
9099cab3 | 67 | $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']); |
204aa6fb PF |
68 | $schema = new CRM_Logging_Schema(); |
69 | $schema->enableLogging(); | |
70 | $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl"); | |
71 | while ($log_table->fetch()) { | |
72 | $this->assertRegexp('/ENGINE=MyISAM/', $log_table->Create_Table); | |
73 | } | |
74 | } | |
75 | ||
89d7d62e | 76 | /** |
77 | * Tests that choosing to ignore a custom table does not result in e-notices. | |
78 | */ | |
79 | public function testIgnoreCustomTableByHook() { | |
80 | $group = $this->customGroupCreate(); | |
81 | Civi::settings()->set('logging', TRUE); | |
82 | $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'noCustomTables']); | |
83 | $this->customFieldCreate(['custom_group_id' => $group['id']]); | |
84 | } | |
85 | ||
86 | /** | |
87 | * Remove all custom tables from tables to be logged. | |
88 | * | |
89 | * @param array $logTableSpec | |
90 | */ | |
91 | public function noCustomTables(&$logTableSpec) { | |
92 | foreach (array_keys($logTableSpec) as $index) { | |
93 | if (substr($index, 0, 14) === 'civicrm_value_') { | |
94 | unset($logTableSpec[$index]); | |
95 | } | |
96 | } | |
97 | } | |
98 | ||
204aa6fb PF |
99 | /** |
100 | * Test that existing log tables with ARCHIVE engine are converted to InnoDB | |
101 | * | |
102 | * @throws \Exception | |
103 | */ | |
104 | public function testArchiveEngineConversion() { | |
105 | $schema = new CRM_Logging_Schema(); | |
106 | $schema->enableLogging(); | |
107 | // change table to ARCHIVE | |
108 | CRM_Core_DAO::executeQuery("ALTER TABLE log_civicrm_acl ENGINE ARCHIVE"); | |
109 | $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl"); | |
a599e68c SL |
110 | while ($log_table->fetch()) { |
111 | $this->assertRegexp('/ENGINE=ARCHIVE/', $log_table->Create_Table); | |
112 | } | |
204aa6fb | 113 | // engine should not change by default |
e26df891 | 114 | $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]); |
204aa6fb PF |
115 | $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl"); |
116 | while ($log_table->fetch()) { | |
89d7d62e | 117 | $this->assertRegExp('/ENGINE=ARCHIVE/', $log_table->Create_Table); |
204aa6fb PF |
118 | } |
119 | // update with forceEngineMigration should convert to InnoDB | |
e26df891 | 120 | $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => TRUE]); |
204aa6fb PF |
121 | $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl"); |
122 | while ($log_table->fetch()) { | |
89d7d62e | 123 | $this->assertRegExp('/ENGINE=InnoDB/', $log_table->Create_Table); |
204aa6fb PF |
124 | } |
125 | } | |
126 | ||
89d7d62e | 127 | /** |
128 | * Alter the engine on the log tables. | |
129 | * | |
130 | * @param $logTableSpec | |
131 | */ | |
204aa6fb PF |
132 | public function alterLogTables(&$logTableSpec) { |
133 | foreach (array_keys($logTableSpec) as $tableName) { | |
134 | $logTableSpec[$tableName]['engine'] = 'MyISAM'; | |
135 | } | |
16142ce2 SL |
136 | } |
137 | ||
6bbc383d | 138 | /** |
139 | * Test correct creation of modified date triggers. | |
140 | * | |
141 | * Specifically we are testing that the contact table modified date and | |
142 | * ONLY the contact table modified date is updated when the custom field is updated. | |
143 | * | |
144 | * (At point of writing this the modification was leaking to the mailing table). | |
145 | */ | |
146 | public function testTriggers() { | |
147 | $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest....'); | |
148 | Civi::service('sql_triggers')->rebuild(); | |
149 | $log_table = CRM_Core_DAO::executeQuery("SHOW TRIGGERS WHERE `Trigger` LIKE 'civicrm_value_contact_{$customGroup['custom_group_id']}_after_insert%'"); | |
150 | ||
151 | while ($log_table->fetch()) { | |
152 | $this->assertContains('UPDATE civicrm_contact SET modified_date = CURRENT_TIMESTAMP WHERE id = NEW.entity_id;', $log_table->Statement, "Contact modification update should be in the trigger :\n" . $log_table->Statement); | |
153 | $this->assertNotContains('civicrm_mailing', $log_table->Statement, 'Contact field should not update mailing table'); | |
154 | $this->assertEquals(1, substr_count($log_table->Statement, 'SET modified_date'), 'Modified date should only be updated on one table (here it is contact)'); | |
155 | } | |
156 | } | |
157 | ||
16142ce2 SL |
158 | public function testAutoIncrementNonIdColumn() { |
159 | CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_table` ( | |
160 | test_id int(10) unsigned NOT NULL AUTO_INCREMENT, | |
161 | PRIMARY KEY (`test_id`) | |
162 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"); | |
163 | $schema = new CRM_Logging_Schema(); | |
164 | $schema->enableLogging(); | |
165 | $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table"); | |
166 | // Test that just havving a non id nanmed column with Auto Increment doesn't create diffs | |
167 | $this->assertTrue(empty($diffs['MODIFY'])); | |
168 | $this->assertTrue(empty($diffs['ADD'])); | |
169 | $this->assertTrue(empty($diffs['OBSOLETE'])); | |
170 | CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_table ADD COLUMN test_varchar varchar(255) DEFAULT NULL"); | |
9099cab3 | 171 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; |
16142ce2 SL |
172 | // Check that it still picks up new columns added. |
173 | $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table"); | |
174 | $this->assertTrue(!empty($diffs['ADD'])); | |
175 | $this->assertTrue(empty($diffs['MODIFY'])); | |
176 | $this->assertTrue(empty($diffs['OBSOLETE'])); | |
177 | $schema->fixSchemaDifferences(); | |
178 | CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(400) DEFAULT NULL"); | |
179 | // Check that it properly picks up modifications to columns. | |
9099cab3 | 180 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; |
16142ce2 SL |
181 | $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table"); |
182 | $this->assertTrue(!empty($diffs['MODIFY'])); | |
183 | $this->assertTrue(empty($diffs['ADD'])); | |
184 | $this->assertTrue(empty($diffs['OBSOLETE'])); | |
185 | $schema->fixSchemaDifferences(); | |
186 | CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(300) DEFAULT NULL"); | |
187 | // Check that when we reduce the size of column that the log table doesn't shrink as well. | |
9099cab3 | 188 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; |
16142ce2 SL |
189 | $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table"); |
190 | $this->assertTrue(empty($diffs['MODIFY'])); | |
191 | $this->assertTrue(empty($diffs['ADD'])); | |
192 | $this->assertTrue(empty($diffs['OBSOLETE'])); | |
a599e68c SL |
193 | } |
194 | ||
9c3c84d1 PF |
195 | /** |
196 | * Test logging trigger definition | |
197 | */ | |
198 | public function testTriggerInfo() { | |
199 | $info = []; | |
200 | $schema = new CRM_Logging_Schema(); | |
201 | $schema->enableLogging(); | |
202 | $schema->triggerInfo($info, 'civicrm_group'); | |
203 | // should have 3 triggers (insert/update/delete) | |
204 | $this->assertCount(3, $info); | |
205 | foreach ($info as $trigger) { | |
206 | // table for trigger should be civicrm_group | |
207 | $this->assertEquals('civicrm_group', $trigger['table'][0]); | |
208 | if ($trigger['event'][0] == 'UPDATE') { | |
209 | // civicrm_group.cache_date should be an exception, i.e. not logged | |
210 | $this->assertNotContains( | |
211 | "IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')", | |
212 | $trigger['sql'] | |
213 | ); | |
214 | } | |
215 | } | |
216 | } | |
217 | ||
9c8a3025 PF |
218 | public function testColumnInfo() { |
219 | CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_column_info` ( | |
220 | test_id int(10) unsigned NOT NULL AUTO_INCREMENT, | |
221 | test_varchar varchar(42) NOT NULL, | |
222 | test_integer int(8) NULL, | |
f34f504a PF |
223 | test_decimal decimal(20,2), |
224 | test_enum enum('A','B','C'), | |
9c8a3025 PF |
225 | test_integer_default int(8) DEFAULT 42, |
226 | test_date date DEFAULT NULL, | |
227 | PRIMARY KEY (`test_id`) | |
228 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"); | |
229 | $schema = new CRM_Logging_Schema(); | |
230 | $schema->enableLogging(); | |
e26df891 | 231 | $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]); |
9c8a3025 PF |
232 | $ci = \Civi::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info']; |
233 | ||
234 | $this->assertEquals('test_id', $ci['test_id']['COLUMN_NAME']); | |
235 | $this->assertEquals('int', $ci['test_id']['DATA_TYPE']); | |
236 | $this->assertEquals('NO', $ci['test_id']['IS_NULLABLE']); | |
237 | $this->assertEquals('auto_increment', $ci['test_id']['EXTRA']); | |
238 | $this->assertEquals('10', $ci['test_id']['LENGTH']); | |
239 | ||
240 | $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']); | |
241 | $this->assertEquals('42', $ci['test_varchar']['LENGTH']); | |
242 | ||
243 | $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']); | |
244 | $this->assertEquals('8', $ci['test_integer']['LENGTH']); | |
245 | $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']); | |
246 | ||
f34f504a PF |
247 | $this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']); |
248 | $this->assertEquals('20,2', $ci['test_decimal']['LENGTH']); | |
249 | ||
250 | $this->assertEquals('enum', $ci['test_enum']['DATA_TYPE']); | |
251 | $this->assertEquals("'A','B','C'", $ci['test_enum']['ENUM_VALUES']); | |
252 | $this->assertArrayNotHasKey('LENGTH', $ci['test_enum']); | |
253 | ||
9c8a3025 PF |
254 | $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']); |
255 | ||
256 | $this->assertEquals('date', $ci['test_date']['DATA_TYPE']); | |
257 | } | |
258 | ||
259 | public function testIndexes() { | |
260 | $schema = new CRM_Logging_Schema(); | |
261 | $indexes = $schema->getIndexesForTable('civicrm_contact'); | |
262 | $this->assertContains('PRIMARY', $indexes); | |
263 | $this->assertContains('UI_external_identifier', $indexes); | |
264 | $this->assertContains('FK_civicrm_contact_employer_id', $indexes); | |
265 | $this->assertContains('index_sort_name', $indexes); | |
266 | } | |
267 | ||
f34f504a PF |
268 | public function testLengthChange() { |
269 | CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_length_change` ( | |
270 | test_id int(10) unsigned NOT NULL AUTO_INCREMENT, | |
271 | test_integer int(4) NULL, | |
272 | test_decimal decimal(20,2) NULL, | |
273 | PRIMARY KEY (`test_id`) | |
274 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"); | |
275 | $schema = new CRM_Logging_Schema(); | |
276 | $schema->enableLogging(); | |
277 | CRM_Core_DAO::executeQuery( | |
278 | "ALTER TABLE civicrm_test_length_change | |
279 | CHANGE COLUMN test_integer test_integer int(6) NULL, | |
280 | CHANGE COLUMN test_decimal test_decimal decimal(22,2) NULL" | |
281 | ); | |
282 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
283 | $schema->fixSchemaDifferences(); | |
284 | // need to do it twice so the columnSpecs static is refreshed | |
285 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
286 | $schema->fixSchemaDifferences(); | |
287 | $ci = \Civi::$statics['CRM_Logging_Schema']['columnSpecs']; | |
288 | // length should increase | |
289 | $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']); | |
290 | $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']); | |
291 | CRM_Core_DAO::executeQuery( | |
292 | "ALTER TABLE civicrm_test_length_change | |
293 | CHANGE COLUMN test_integer test_integer int(4) NULL, | |
294 | CHANGE COLUMN test_decimal test_decimal decimal(20,2) NULL" | |
295 | ); | |
296 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
297 | $schema->fixSchemaDifferences(); | |
298 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
299 | $schema->fixSchemaDifferences(); | |
300 | $ci = \Civi::$statics['CRM_Logging_Schema']['columnSpecs']; | |
301 | // length should not decrease | |
302 | $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']); | |
303 | $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']); | |
304 | } | |
305 | ||
306 | public function testEnumChange() { | |
307 | CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_enum_change` ( | |
308 | test_id int(10) unsigned NOT NULL AUTO_INCREMENT, | |
309 | test_enum enum('A','B','C') NULL, | |
310 | PRIMARY KEY (`test_id`) | |
311 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"); | |
312 | $schema = new CRM_Logging_Schema(); | |
313 | $schema->enableLogging(); | |
314 | CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_enum_change CHANGE COLUMN test_enum test_enum enum('A','B','C','D') NULL"); | |
315 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
316 | $schema->fixSchemaDifferences(); | |
317 | // need to do it twice so the columnSpecs static is refreshed | |
318 | \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = []; | |
319 | $schema->fixSchemaDifferences(); | |
320 | $ci = \Civi::$statics['CRM_Logging_Schema']['columnSpecs']; | |
321 | // new enum value should be included | |
322 | $this->assertEquals("'A','B','C','D'", $ci['civicrm_test_enum_change']['test_enum']['ENUM_VALUES']); | |
323 | } | |
324 | ||
91bd5c14 | 325 | } |