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 \API_Exception
20 * @throws \CRM_Core_Exception
21 * @throws \CiviCRM_API3_Exception
23 public function tearDown(): void
{
24 $schema = new CRM_Logging_Schema();
25 $schema->dropAllLogTables();
26 Civi
::settings()->set('logging', FALSE);
27 $this->databaseVersion
= NULL;
29 $this->quickCleanup(['civicrm_contact'], TRUE);
30 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS civicrm_test_table');
31 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS civicrm_test_column_info');
32 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS civicrm_test_length_change');
33 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS civicrm_test_enum_change');
37 * Data provider for testing query re-writing.
41 public function queryExamples(): array {
43 $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.'"];
44 $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.'"];
49 * Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema
51 * @dataProvider queryExamples
53 public function testQueryRewrite($query, $expectedQuery) {
54 $this->assertEquals($expectedQuery, CRM_Logging_Schema
::fixTimeStampAndNotNullSQL($query));
58 * Test log tables are created as InnoDB by default
60 public function testLogEngine(): void
{
61 Civi
::settings()->set('logging', TRUE);
62 $log_table = CRM_Core_DAO
::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
63 while ($log_table->fetch()) {
64 $this->assertRegexp('/ENGINE=InnoDB/', $log_table->Create_Table
);
69 * Test that the log table engine can be changed via hook to e.g. MyISAM
71 public function testHookLogEngine(): void
{
72 $this->hookClass
->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']);
73 Civi
::settings()->set('logging', TRUE);
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(): void
{
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): void
{
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(): void
{
109 Civi
::settings()->set('logging', TRUE);
110 $schema = new CRM_Logging_Schema();
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");
171 Civi
::settings()->set('logging', TRUE);
172 $schema = new CRM_Logging_Schema();
173 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
174 // Test that just having a non id named column with Auto Increment doesn't create diffs
175 $this->assertTrue(empty($diffs['MODIFY']));
176 $this->assertTrue(empty($diffs['ADD']));
177 $this->assertTrue(empty($diffs['OBSOLETE']));
179 // Check we can add a primary key to the log table and it will not be treated as obsolete.
180 CRM_Core_DAO
::executeQuery('
181 ALTER TABLE log_civicrm_test_table ADD COLUMN `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
182 ADD PRIMARY KEY (`log_id`)
184 Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
185 $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
186 $this->assertEmpty($diffs['OBSOLETE']);
188 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_table ADD COLUMN test_varchar varchar(255) DEFAULT NULL");
189 Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
190 // Check that it still picks up new columns added.
191 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
192 $this->assertTrue(!empty($diffs['ADD']));
193 $this->assertTrue(empty($diffs['MODIFY']));
194 $this->assertTrue(empty($diffs['OBSOLETE']));
195 unset(Civi
::$statics['CRM_Logging_Schema']);
196 $schema->fixSchemaDifferences();
197 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(400) DEFAULT NULL');
198 // Check that it properly picks up modifications to columns.
199 unset(Civi
::$statics['CRM_Logging_Schema']);
200 $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
201 $this->assertNotEmpty($diffs['MODIFY']);
202 $this->assertEmpty($diffs['ADD']);
203 $this->assertEmpty($diffs['OBSOLETE']);
204 $schema->fixSchemaDifferences();
205 CRM_Core_DAO
::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(300) DEFAULT NULL');
206 // Check that when we reduce the size of column that the log table doesn't shrink as well.
207 Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
208 $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
209 $this->assertTrue(empty($diffs['MODIFY']));
210 $this->assertTrue(empty($diffs['ADD']));
211 $this->assertTrue(empty($diffs['OBSOLETE']));
215 * Test logging trigger definition
217 public function testTriggerInfo() {
219 $schema = new CRM_Logging_Schema();
220 Civi
::settings()->set('logging', TRUE);
221 $schema->triggerInfo($info, 'civicrm_group');
222 // should have 3 triggers (insert/update/delete)
223 $this->assertCount(3, $info);
224 foreach ($info as $trigger) {
225 // table for trigger should be civicrm_group
226 $this->assertEquals('civicrm_group', $trigger['table'][0]);
227 if ($trigger['event'][0] == 'UPDATE') {
228 // civicrm_group.cache_date should be an exception, i.e. not logged
229 $this->assertStringNotContainsString(
230 "IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')",
238 * @throws \CiviCRM_API3_Exception
240 public function testColumnInfo(): void
{
241 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_column_info` (
242 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
243 test_varchar varchar(42) NOT NULL,
244 test_integer int(8) NULL,
245 test_decimal decimal(20,2),
246 test_enum enum('A','B','C'),
247 test_integer_default int(8) DEFAULT 42,
248 test_date date DEFAULT NULL,
249 PRIMARY KEY (`test_id`)
250 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
251 Civi
::settings()->set('logging', TRUE);
253 $schema = new CRM_Logging_Schema();
254 $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
255 $ci = Civi
::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info'];
257 $this->assertEquals('test_id', $ci['test_id']['COLUMN_NAME']);
258 $this->assertEquals('int', $ci['test_id']['DATA_TYPE']);
259 $this->assertEquals('NO', $ci['test_id']['IS_NULLABLE']);
260 $this->assertEquals('auto_increment', $ci['test_id']['EXTRA']);
261 if (!$this->isMySQL8()) {
262 $this->assertEquals('10', $ci['test_id']['LENGTH']);
265 $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
266 $this->assertEquals('42', $ci['test_varchar']['LENGTH']);
268 $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
269 if (!$this->isMySQL8()) {
270 $this->assertEquals('8', $ci['test_integer']['LENGTH']);
272 $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);
274 $this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']);
275 $this->assertEquals('20,2', $ci['test_decimal']['LENGTH']);
277 $this->assertEquals('enum', $ci['test_enum']['DATA_TYPE']);
278 $this->assertEquals("'A','B','C'", $ci['test_enum']['ENUM_VALUES']);
279 $this->assertArrayNotHasKey('LENGTH', $ci['test_enum']);
281 $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);
283 $this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
286 public function testIndexes() {
287 $schema = new CRM_Logging_Schema();
288 $indexes = $schema->getIndexesForTable('civicrm_contact');
289 $this->assertContains('PRIMARY', $indexes);
290 $this->assertContains('UI_external_identifier', $indexes);
291 $this->assertContains('FK_civicrm_contact_employer_id', $indexes);
292 $this->assertContains('index_sort_name', $indexes);
295 public function testLengthChange() {
296 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_length_change` (
297 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
298 test_integer int(4) NULL,
299 test_decimal decimal(20,2) NULL,
300 PRIMARY KEY (`test_id`)
301 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
302 Civi
::settings()->set('logging', TRUE);
303 $schema = new CRM_Logging_Schema();
304 CRM_Core_DAO
::executeQuery(
305 "ALTER TABLE civicrm_test_length_change
306 CHANGE COLUMN test_integer test_integer int(6) NULL,
307 CHANGE COLUMN test_decimal test_decimal decimal(22,2) NULL"
309 $schema->fixSchemaDifferences();
310 $ci = Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
311 // length should increase
312 if (!$this->isMySQL8()) {
313 $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
315 $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
316 CRM_Core_DAO
::executeQuery(
317 "ALTER TABLE civicrm_test_length_change
318 CHANGE COLUMN test_integer test_integer int(4) NULL,
319 CHANGE COLUMN test_decimal test_decimal decimal(20,2) NULL"
321 $schema->fixSchemaDifferences();
322 $ci = Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
323 // length should not decrease
324 if (!$this->isMySQL8()) {
325 $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
327 $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
331 * Test changing the enum.
333 public function testEnumChange(): void
{
334 CRM_Core_DAO
::executeQuery("CREATE TABLE `civicrm_test_enum_change` (
335 test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
336 test_enum enum('A','B','C') NULL,
337 PRIMARY KEY (`test_id`)
338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
339 Civi
::settings()->set('logging', TRUE);
340 $schema = new CRM_Logging_Schema();
341 CRM_Core_DAO
::executeQuery("ALTER TABLE civicrm_test_enum_change CHANGE COLUMN test_enum test_enum enum('A','B','C','D') NULL");
342 Civi
::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
343 $schema->fixSchemaDifferences();
344 $ci = Civi
::$statics['CRM_Logging_Schema']['columnSpecs'];
345 // new enum value should be included
346 $this->assertEquals("'A','B','C','D'", $ci['civicrm_test_enum_change']['test_enum']['ENUM_VALUES']);
350 * Test editing a custom field
352 * @throws \CRM_Core_Exception
354 public function testCustomFieldEdit(): void
{
355 Civi
::settings()->set('logging', TRUE);
356 $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest.php');
358 // get the custom group table name
359 $params = ['id' => $customGroup['custom_group_id']];
360 $custom_group = $this->callAPISuccess('custom_group', 'getsingle', $params);
362 // get the field db column name
363 $params = ['id' => $customGroup['custom_field_id']];
364 $custom_field = $this->callAPISuccess('custom_field', 'getsingle', $params);
367 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
369 $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(255)", $dao->Create_Table
);
373 'id' => $customGroup['custom_field_id'],
374 'label' => 'Label changed',
375 'text_length' => 768,
377 $this->callAPISuccess('custom_field', 'create', $params);
379 // update logging schema
380 $schema = new CRM_Logging_Schema();
381 $schema->fixSchemaDifferences();
384 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
386 $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table
);
390 * Test creating a table with SchemaHandler::createTable when logging
393 public function testCreateTableWithLogging(): void
{
394 Civi
::settings()->set('logging', TRUE);
396 CRM_Core_BAO_SchemaHandler
::createTable([
397 'name' => 'civicrm_test_table',
398 'is_multiple' => FALSE,
399 'attributes' => 'ENGINE=InnoDB',
403 'type' => 'int unsigned',
406 'attributes' => 'AUTO_INCREMENT',
407 'comment' => 'Default MySQL primary key',
410 'name' => 'activity_id',
411 'type' => 'int unsigned',
413 'comment' => 'FK to civicrm_activity',
414 'fk_table_name' => 'civicrm_activity',
415 'fk_field_name' => 'id',
416 'fk_attributes' => 'ON DELETE CASCADE',
420 'type' => 'varchar(255)',
425 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE civicrm_test_table");
427 // using regex since not sure it's always int(10), so accept int(10), int(11), integer, etc...
428 $this->assertRegExp('/`id` int(.*) unsigned NOT NULL AUTO_INCREMENT/', $dao->Create_Table
);
429 $this->assertRegExp('/`activity_id` int(.*) unsigned NOT NULL/', $dao->Create_Table
);
430 $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table
);
431 $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table
);
432 $this->assertStringContainsString('FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE', $dao->Create_Table
);
435 $dao = CRM_Core_DAO
::executeQuery('SHOW CREATE TABLE log_civicrm_test_table');
437 $this->assertStringNotContainsString('AUTO_INCREMENT', $dao->Create_Table
);
438 // This seems debatable whether `id` should lose its NOT NULL status
439 $this->assertRegExp('/`id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table
);
440 $this->assertRegExp('/`activity_id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table
);
441 $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table
);
442 $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table
);
443 $this->assertStringNotContainsString('FOREIGN KEY', $dao->Create_Table
);
447 * Determine if we are running on MySQL 8 version 8.0.19 or later.
451 protected function isMySQL8(): bool {
452 return (bool) (version_compare($this->databaseVersion
, '8.0.19', '>=') && stripos($this->databaseVersion
, 'mariadb') === FALSE);