tests/phpunit/CRM/Logging/SchemaTest.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Logging_SchmeaTest
   5  * @group headless
   6  */
   7 class CRM_Logging_SchemaTest extends CiviUnitTestCase {
   8
   9   protected $databaseVersion;
  10
  11   public function setUp() {
  12     $this->databaseVersion = CRM_Utils_SQL::getDatabaseVersion();
  13     parent::setUp();
  14   }
  15
  16   /**
  17    * Clean up after test.
  18    *
  19    * @throws \CRM_Core_Exception
  20    */
  21   public function tearDown(): void {
  22     $schema = new CRM_Logging_Schema();
  23     $schema->disableLogging();
  24     $this->databaseVersion = NULL;
  25     parent::tearDown();
  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");
  32   }
  33
  34   /**
  35    * Data provider for testing query re-writing.
  36    *
  37    * @return array
  38    */
  39   public function queryExamples() {
  40     $examples = [];
  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.'"];
  43     return $examples;
  44   }
  45
  46   /**
  47    * Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema
  48    *
  49    * @dataProvider queryExamples
  50    */
  51   public function testQueryRewrite($query, $expectedQuery) {
  52     $this->assertEquals($expectedQuery, CRM_Logging_Schema::fixTimeStampAndNotNullSQL($query));
  53   }
  54
  55   /**
  56    * Test log tables are created as InnoDB by default
  57    */
  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);
  64     }
  65   }
  66
  67   /**
  68    * Test that the log table engine can be changed via hook to e.g. MyISAM
  69    */
  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);
  77     }
  78   }
  79
  80   /**
  81    * Tests that choosing to ignore a custom table does not result in e-notices.
  82    */
  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']]);
  88   }
  89
  90   /**
  91    * Remove all custom tables from tables to be logged.
  92    *
  93    * @param array $logTableSpec
  94    */
  95   public function noCustomTables(&$logTableSpec) {
  96     foreach (array_keys($logTableSpec) as $index) {
  97       if (substr($index, 0, 14) === 'civicrm_value_') {
  98         unset($logTableSpec[$index]);
  99       }
 100     }
 101   }
 102
 103   /**
 104    * Test that existing log tables with ARCHIVE engine are converted to InnoDB
 105    *
 106    * @throws \Exception
 107    */
 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);
 116     }
 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);
 122     }
 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);
 128     }
 129   }
 130
 131   /**
 132    * Alter the engine on the log tables.
 133    *
 134    * @param $logTableSpec
 135    */
 136   public function alterLogTables(&$logTableSpec) {
 137     foreach (array_keys($logTableSpec) as $tableName) {
 138       $logTableSpec[$tableName]['engine'] = 'MyISAM';
 139     }
 140   }
 141
 142   /**
 143    * Test correct creation of modified date triggers.
 144    *
 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.
 147    *
 148    * (At point of writing this the modification was leaking to the mailing table).
 149    */
 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%'");
 154
 155     while ($log_table->fetch()) {
 156       $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);
 157       $this->assertNotContains('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)');
 159     }
 160   }
 161
 162   /**
 163    * Test that autoincrement keys are handled sensibly in logging table reconciliation.
 164    */
 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']));
 177
 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`)
 182    ");
 183     \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
 184     $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
 185     $this->assertTrue(empty($diffs['OBSOLETE']));
 186
 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']));
 210   }
 211
 212   /**
 213    * Test logging trigger definition
 214    */
 215   public function testTriggerInfo() {
 216     $info = [];
 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->assertNotContains(
 228           "IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')",
 229           $trigger['sql']
 230         );
 231       }
 232     }
 233   }
 234
 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'];
 250
 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']);
 257     }
 258
 259     $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
 260     $this->assertEquals('42', $ci['test_varchar']['LENGTH']);
 261
 262     $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
 263     if (!$this->isMySQL8()) {
 264       $this->assertEquals('8', $ci['test_integer']['LENGTH']);
 265     }
 266     $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);
 267
 268     $this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']);
 269     $this->assertEquals('20,2', $ci['test_decimal']['LENGTH']);
 270
 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']);
 274
 275     $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);
 276
 277     $this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
 278   }
 279
 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);
 287   }
 288
 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"
 302     );
 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']);
 308     }
 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"
 314     );
 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']);
 320     }
 321     $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
 322   }
 323
 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']);
 338   }
 339
 340   /**
 341    * Test editing a custom field
 342    */
 343   public function testCustomFieldEdit() {
 344     $schema = new CRM_Logging_Schema();
 345     $schema->enableLogging();
 346     $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest.php');
 347
 348     // get the custom group table name
 349     $params = ['id' => $customGroup['custom_group_id']];
 350     $custom_group = $this->callAPISuccess('custom_group', 'getsingle', $params);
 351
 352     // get the field db column name
 353     $params = ['id' => $customGroup['custom_field_id']];
 354     $custom_field = $this->callAPISuccess('custom_field', 'getsingle', $params);
 355
 356     // check it
 357     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
 358     $dao->fetch();
 359     $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(255)", $dao->Create_Table);
 360
 361     // Edit the field
 362     $params = [
 363       'id' => $customGroup['custom_field_id'],
 364       'label' => 'Label changed',
 365       'text_length' => 768,
 366     ];
 367     $this->callAPISuccess('custom_field', 'create', $params);
 368
 369     // update logging schema
 370     $schema->fixSchemaDifferences();
 371
 372     // verify
 373     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
 374     $dao->fetch();
 375     $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table);
 376   }
 377
 378   /**
 379    * Test creating a table with SchemaHandler::createTable when logging
 380    * is enabled.
 381    */
 382   public function testCreateTableWithLogging() {
 383     $schema = new CRM_Logging_Schema();
 384     $schema->enableLogging();
 385
 386     CRM_Core_BAO_SchemaHandler::createTable([
 387       'name' => 'civicrm_test_table',
 388       'is_multiple' => FALSE,
 389       'attributes' => 'ENGINE=InnoDB',
 390       'fields' => [
 391         [
 392           'name' => 'id',
 393           'type' => 'int unsigned',
 394           'primary' => TRUE,
 395           'required' => TRUE,
 396           'attributes' => 'AUTO_INCREMENT',
 397           'comment' => 'Default MySQL primary key',
 398         ],
 399         [
 400           'name' => 'activity_id',
 401           'type' => 'int unsigned',
 402           'required' => TRUE,
 403           'comment' => 'FK to civicrm_activity',
 404           'fk_table_name' => 'civicrm_activity',
 405           'fk_field_name' => 'id',
 406           'fk_attributes' => 'ON DELETE CASCADE',
 407         ],
 408         [
 409           'name' => 'texty',
 410           'type' => 'varchar(255)',
 411           'required' => FALSE,
 412         ],
 413       ],
 414     ]);
 415     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_table");
 416     $dao->fetch();
 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);
 423
 424     // Check log table.
 425     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_test_table");
 426     $dao->fetch();
 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);
 434   }
 435
 436   /**
 437    * Determine if we are running on MySQL 8 version 8.0.19 or later.
 438    *
 439    * @return bool
 440    */
 441   protected function isMySQL8() {
 442     return (bool) (version_compare($this->databaseVersion, '8.0.19', '>=') && stripos($this->databaseVersion, 'mariadb') === FALSE);
 443   }
 444
 445 }