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   public function setUp() {
  10     parent::setUp();
  11   }
  12
  13   /**
  14    * Clean up after test.
  15    *
  16    * @throws \CRM_Core_Exception
  17    */
  18   public function tearDown() {
  19     $schema = new CRM_Logging_Schema();
  20     $schema->disableLogging();
  21     parent::tearDown();
  22     $this->quickCleanup(['civicrm_contact'], TRUE);
  23     $schema->dropAllLogTables();
  24     CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_table");
  25     CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_column_info");
  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");
  28   }
  29
  30   /**
  31    * Data provider for testing query re-writing.
  32    *
  33    * @return array
  34    */
  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
  51   /**
  52    * Test log tables are created as InnoDB by default
  53    */
  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");
  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() {
  67     $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']);
  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
  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
  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");
 110     while ($log_table->fetch()) {
 111       $this->assertRegexp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
 112     }
 113     // engine should not change by default
 114     $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
 115     $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
 116     while ($log_table->fetch()) {
 117       $this->assertRegExp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
 118     }
 119     // update with forceEngineMigration should convert to InnoDB
 120     $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => TRUE]);
 121     $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
 122     while ($log_table->fetch()) {
 123       $this->assertRegExp('/ENGINE=InnoDB/', $log_table->Create_Table);
 124     }
 125   }
 126
 127   /**
 128    * Alter the engine on the log tables.
 129    *
 130    * @param $logTableSpec
 131    */
 132   public function alterLogTables(&$logTableSpec) {
 133     foreach (array_keys($logTableSpec) as $tableName) {
 134       $logTableSpec[$tableName]['engine'] = 'MyISAM';
 135     }
 136   }
 137
 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
 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");
 171     \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
 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.
 180     \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
 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.
 188     \Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
 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']));
 193   }
 194
 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
 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,
 223       test_decimal decimal(20,2),
 224       test_enum enum('A','B','C'),
 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();
 231     $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
 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
 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
 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
 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
 325 }