4 * Class CRM_Logging_SchmeaTest
7 class CRM_Logging_SchemaTest
extends CiviUnitTestCase
{
9 protected $databaseVersion;
11 public function setUp(): void
{
12 $this->databaseVersion
= CRM_Utils_SQL
::getDatabaseVersion();
17 * Clean up after test.
19 * @throws \CRM_Core_Exception
21 public function tearDown(): void
{
22 $schema = new CRM_Logging_Schema();
23 $schema->disableLogging();
24 $this->databaseVersion
= NULL;
26 $this->quickCleanup(['civicrm_contact'], TRUE);
27 $schema->dropAllLogTables();
28 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS civicrm_test_table");
29 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS civicrm_test_column_info");
30 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS civicrm_test_length_change");
31 CRM_Core_DAO
::executeQuery("DROP TABLE IF EXISTS civicrm_test_enum_change");
35 * Data provider for testing query re-writing.
39 public function queryExamples() {
41 $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.'"];
42 $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.'"];
47 * Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema
49 * @dataProvider queryExamples
51 public function testQueryRewrite($query, $expectedQuery) {
52 $this->assertEquals($expectedQuery, CRM_Logging_Schema
::fixTimeStampAndNotNullSQL($query));
56 * Test log tables are created as InnoDB by default
58 public function testLogEngine() {
59 $schema = new CRM_Logging_Schema();
60 $schema->enableLogging();
61 $log_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
62 while ($log_table->fetch()) {
63 $this->assertRegexp('/ENGINE=InnoDB/', $log_table->Create_Table
);
68 * Test that the log table engine can be changed via hook to e.g. MyISAM
70 public function testHookLogEngine() {
71 $this->hookClass
->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']);
72 $schema = new CRM_Logging_Schema();
73 $schema->enableLogging();
74 $log_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
75 while ($log_table->fetch()) {
76 $this->assertRegexp('/ENGINE=MyISAM/', $log_table->Create_Table
);
81 * Tests that choosing to ignore a custom table does not result in e-notices.
83 public function testIgnoreCustomTableByHook() {
84 $group = $this->customGroupCreate();
85 Civi
::settings()->set('logging', TRUE);
86 $this->hookClass
->setHook('civicrm_alterLogTables', [$this, 'noCustomTables']);
87 $this->customFieldCreate(['custom_group_id' => $group['id']]);
91 * Remove all custom tables from tables to be logged.
93 * @param array $logTableSpec
95 public function noCustomTables(&$logTableSpec) {
96 foreach (array_keys($logTableSpec) as $index) {
97 if (substr($index, 0, 14) === 'civicrm_value_') {
98 unset($logTableSpec[$index]);
104 * Test that existing log tables with ARCHIVE engine are converted to InnoDB
108 public function testArchiveEngineConversion() {
109 $schema = new CRM_Logging_Schema();
110 $schema->enableLogging();
111 // change table to ARCHIVE
112 CRM_Core_DAO
::executeQuery("ALTER TABLE log_civicrm_acl ENGINE ARCHIVE");
113 $log_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
114 while ($log_table->fetch()) {
115 $this->assertRegexp('/ENGINE=ARCHIVE/', $log_table->Create_Table
);
117 // engine should not change by default
118 $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
119 $log_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
120 while ($log_table->fetch()) {
121 $this->assertRegExp('/ENGINE=ARCHIVE/', $log_table->Create_Table
);
123 // update with forceEngineMigration should convert to InnoDB
124 $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => TRUE]);
125 $log_table = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
126 while ($log_table->fetch()) {
127 $this->assertRegExp('/ENGINE=InnoDB/', $log_table->Create_Table
);
132 * Alter the engine on the log tables.
134 * @param $logTableSpec
136 public function alterLogTables(&$logTableSpec) {
137 foreach (array_keys($logTableSpec) as $tableName) {
138 $logTableSpec[$tableName]['engine'] = 'MyISAM';
143 * Test correct creation of modified date triggers.
145 * Specifically we are testing that the contact table modified date and
146 * ONLY the contact table modified date is updated when the custom field is updated.
148 * (At point of writing this the modification was leaking to the mailing table).
150 public function testTriggers() {
151 $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest....');
152 Civi
::service('sql_triggers')->rebuild();
153 $log_table = CRM_Core_DAO
::executeQuery("SHOW TRIGGERS WHERE `Trigger` LIKE 'civicrm_value_contact_{$customGroup['custom_group_id']}_after_insert%'");
155 while ($log_table->fetch()) {
156 $this->assertStringContainsString('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
);
157 $this->assertStringNotContainsString('civicrm_mailing', $log_table->Statement
, 'Contact field should not update mailing table');
158 $this->assertEquals(1, substr_count($log_table->Statement
, 'SET modified_date'), 'Modified date should only be updated on one table (here it is contact)');
163 * Test that autoincrement keys are handled sensibly in logging table reconciliation.
165 public function testAutoIncrementNonIdColumn() {
166 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_table` (
167 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
168 PRIMARY KEY (`test_id`)
169 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
170 $schema = new CRM_Logging_Schema();
171 $schema->enableLogging();
172 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
173 // Test that just having a non id named column with Auto Increment doesn't create diffs
174 $this->assertTrue(empty($diffs['MODIFY']));
175 $this->assertTrue(empty($diffs['ADD']));
176 $this->assertTrue(empty($diffs['OBSOLETE']));
178 // Check we can add a primary key to the log table and it will not be treated as obsolete.
179 CRM_Core_DAO
::executeQuery("
180 ALTER TABLE log_civicrm_test_table ADD COLUMN `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
181 ADD PRIMARY KEY (`log_id`)
183 \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
184 $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
185 $this->assertTrue(empty($diffs['OBSOLETE']));
187 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_table ADD COLUMN test_varchar varchar(255) DEFAULT NULL");
188 \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
189 // Check that it still picks up new columns added.
190 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
191 $this->assertTrue(!empty($diffs['ADD']));
192 $this->assertTrue(empty($diffs['MODIFY']));
193 $this->assertTrue(empty($diffs['OBSOLETE']));
194 $schema->fixSchemaDifferences();
195 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(400) DEFAULT NULL");
196 // Check that it properly picks up modifications to columns.
197 \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
198 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
199 $this->assertTrue(!empty($diffs['MODIFY']));
200 $this->assertTrue(empty($diffs['ADD']));
201 $this->assertTrue(empty($diffs['OBSOLETE']));
202 $schema->fixSchemaDifferences();
203 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(300) DEFAULT NULL");
204 // Check that when we reduce the size of column that the log table doesn't shrink as well.
205 \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
206 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
207 $this->assertTrue(empty($diffs['MODIFY']));
208 $this->assertTrue(empty($diffs['ADD']));
209 $this->assertTrue(empty($diffs['OBSOLETE']));
213 * Test logging trigger definition
215 public function testTriggerInfo() {
217 $schema = new CRM_Logging_Schema();
218 $schema->enableLogging();
219 $schema->triggerInfo($info, 'civicrm_group');
220 // should have 3 triggers (insert/update/delete)
221 $this->assertCount(3, $info);
222 foreach ($info as $trigger) {
223 // table for trigger should be civicrm_group
224 $this->assertEquals('civicrm_group', $trigger['table'][0]);
225 if ($trigger['event'][0] == 'UPDATE') {
226 // civicrm_group.cache_date should be an exception, i.e. not logged
227 $this->assertStringNotContainsString(
228 "IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')",
235 public function testColumnInfo() {
236 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_column_info` (
237 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
238 test_varchar varchar(42) NOT NULL,
239 test_integer int(8) NULL,
240 test_decimal decimal(20,2),
241 test_enum enum('A','B','C'),
242 test_integer_default int(8) DEFAULT 42,
243 test_date date DEFAULT NULL,
244 PRIMARY KEY (`test_id`)
245 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
246 $schema = new CRM_Logging_Schema();
247 $schema->enableLogging();
248 $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
249 $ci = \Civi
::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info'];
251 $this->assertEquals('test_id', $ci['test_id']['COLUMN_NAME']);
252 $this->assertEquals('int', $ci['test_id']['DATA_TYPE']);
253 $this->assertEquals('NO', $ci['test_id']['IS_NULLABLE']);
254 $this->assertEquals('auto_increment', $ci['test_id']['EXTRA']);
255 if (!$this->isMySQL8()) {
256 $this->assertEquals('10', $ci['test_id']['LENGTH']);
259 $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
260 $this->assertEquals('42', $ci['test_varchar']['LENGTH']);
262 $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
263 if (!$this->isMySQL8()) {
264 $this->assertEquals('8', $ci['test_integer']['LENGTH']);
266 $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);
268 $this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']);
269 $this->assertEquals('20,2', $ci['test_decimal']['LENGTH']);
271 $this->assertEquals('enum', $ci['test_enum']['DATA_TYPE']);
272 $this->assertEquals("'A','B','C'", $ci['test_enum']['ENUM_VALUES']);
273 $this->assertArrayNotHasKey('LENGTH', $ci['test_enum']);
275 $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);
277 $this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
280 public function testIndexes() {
281 $schema = new CRM_Logging_Schema();
282 $indexes = $schema->getIndexesForTable('civicrm_contact');
283 $this->assertContains('PRIMARY', $indexes);
284 $this->assertContains('UI_external_identifier', $indexes);
285 $this->assertContains('FK_civicrm_contact_employer_id', $indexes);
286 $this->assertContains('index_sort_name', $indexes);
289 public function testLengthChange() {
290 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_length_change` (
291 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
292 test_integer int(4) NULL,
293 test_decimal decimal(20,2) NULL,
294 PRIMARY KEY (`test_id`)
295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
296 $schema = new CRM_Logging_Schema();
297 $schema->enableLogging();
298 CRM_Core_DAO
::executeQuery(
299 "ALTER TABLE civicrm_test_length_change
300 CHANGE COLUMN test_integer test_integer int(6) NULL,
301 CHANGE COLUMN test_decimal test_decimal decimal(22,2) NULL"
303 $schema->fixSchemaDifferences();
304 $ci = \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
305 // length should increase
306 if (!$this->isMySQL8()) {
307 $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
309 $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
310 CRM_Core_DAO
::executeQuery(
311 "ALTER TABLE civicrm_test_length_change
312 CHANGE COLUMN test_integer test_integer int(4) NULL,
313 CHANGE COLUMN test_decimal test_decimal decimal(20,2) NULL"
315 $schema->fixSchemaDifferences();
316 $ci = \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
317 // length should not decrease
318 if (!$this->isMySQL8()) {
319 $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
321 $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
324 public function testEnumChange() {
325 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_enum_change` (
326 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
327 test_enum enum('A','B','C') NULL,
328 PRIMARY KEY (`test_id`)
329 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
330 $schema = new CRM_Logging_Schema();
331 $schema->enableLogging();
332 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_enum_change CHANGE COLUMN test_enum test_enum enum('A','B','C','D') NULL");
333 \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
334 $schema->fixSchemaDifferences();
335 $ci = \Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
336 // new enum value should be included
337 $this->assertEquals("'A','B','C','D'", $ci['civicrm_test_enum_change']['test_enum']['ENUM_VALUES']);
341 * Test editing a custom field
343 public function testCustomFieldEdit() {
344 $schema = new CRM_Logging_Schema();
345 $schema->enableLogging();
346 $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest.php');
348 // get the custom group table name
349 $params = ['id' => $customGroup['custom_group_id']];
350 $custom_group = $this->callAPISuccess('custom_group', 'getsingle', $params);
352 // get the field db column name
353 $params = ['id' => $customGroup['custom_field_id']];
354 $custom_field = $this->callAPISuccess('custom_field', 'getsingle', $params);
357 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
359 $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(255)", $dao->Create_Table
);
363 'id' => $customGroup['custom_field_id'],
364 'label' => 'Label changed',
365 'text_length' => 768,
367 $this->callAPISuccess('custom_field', 'create', $params);
369 // update logging schema
370 $schema->fixSchemaDifferences();
373 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
375 $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table
);
379 * Test creating a table with SchemaHandler::createTable when logging
382 public function testCreateTableWithLogging() {
383 $schema = new CRM_Logging_Schema();
384 $schema->enableLogging();
386 CRM_Core_BAO_SchemaHandler
::createTable([
387 'name' => 'civicrm_test_table',
388 'is_multiple' => FALSE,
389 'attributes' => 'ENGINE=InnoDB',
393 'type' => 'int unsigned',
396 'attributes' => 'AUTO_INCREMENT',
397 'comment' => 'Default MySQL primary key',
400 'name' => 'activity_id',
401 'type' => 'int unsigned',
403 'comment' => 'FK to civicrm_activity',
404 'fk_table_name' => 'civicrm_activity',
405 'fk_field_name' => 'id',
406 'fk_attributes' => 'ON DELETE CASCADE',
410 'type' => 'varchar(255)',
415 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_table");
417 // using regex since not sure it's always int(10), so accept int(10), int(11), integer, etc...
418 $this->assertRegExp('/`id` int(.*) unsigned NOT NULL AUTO_INCREMENT/', $dao->Create_Table
);
419 $this->assertRegExp('/`activity_id` int(.*) unsigned NOT NULL/', $dao->Create_Table
);
420 $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table
);
421 $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table
);
422 $this->assertStringContainsString('FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE', $dao->Create_Table
);
425 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE log_civicrm_test_table");
427 $this->assertStringNotContainsString('AUTO_INCREMENT', $dao->Create_Table
);
428 // This seems debatable whether `id` should lose its NOT NULL status
429 $this->assertRegExp('/`id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table
);
430 $this->assertRegExp('/`activity_id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table
);
431 $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table
);
432 $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table
);
433 $this->assertStringNotContainsString('FOREIGN KEY', $dao->Create_Table
);
437 * Determine if we are running on MySQL 8 version 8.0.19 or later.
441 protected function isMySQL8() {
442 return (bool) (version_compare($this->databaseVersion
, '8.0.19', '>=') && stripos($this->databaseVersion
, 'mariadb') === FALSE);