[civicrm-core.git] / tests / phpunit / CRM / Logging / SchemaTest.php

<?php

/**
 * Class CRM_Logging_SchmeaTest
 * @group headless
 */
class CRM_Logging_SchemaTest extends CiviUnitTestCase {

  protected $databaseVersion;

  public function setUp(): void {
    $this->databaseVersion = CRM_Utils_SQL::getDatabaseVersion();
    parent::setUp();
  }

  /**
   * Clean up after test.
   *
   * @throws \API_Exception
   * @throws \CRM_Core_Exception
   * @throws \CiviCRM_API3_Exception
   */
  public function tearDown(): void {
    $schema = new CRM_Logging_Schema();
    $schema->dropAllLogTables();
    Civi::settings()->set('logging', FALSE);
    $this->databaseVersion = NULL;
    parent::tearDown();
    $this->quickCleanup(['civicrm_contact'], TRUE);
    CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_test_table');
    CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_test_column_info');
    CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_test_length_change');
    CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_test_enum_change');
  }

  /**
   * Data provider for testing query re-writing.
   *
   * @return array
   */
  public function queryExamples(): array {
    $examples = [];
    $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.'"];
    $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.'"];
    return $examples;
  }

  /**
   * Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema
   *
   * @dataProvider queryExamples
   */
  public function testQueryRewrite($query, $expectedQuery) {
    $this->assertEquals($expectedQuery, CRM_Logging_Schema::fixTimeStampAndNotNullSQL($query));
  }

  /**
   * Test log tables are created as InnoDB by default
   */
  public function testLogEngine(): void {
    Civi::settings()->set('logging', TRUE);
    $log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
    while ($log_table->fetch()) {
      $this->assertRegexp('/ENGINE=InnoDB/', $log_table->Create_Table);
    }
  }

  /**
   * Test that the log table engine can be changed via hook to e.g. MyISAM
   */
  public function testHookLogEngine(): void {
    $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']);
    Civi::settings()->set('logging', TRUE);
    $log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
    while ($log_table->fetch()) {
      $this->assertRegexp('/ENGINE=MyISAM/', $log_table->Create_Table);
    }
  }

  /**
   * Tests that choosing to ignore a custom table does not result in e-notices.
   */
  public function testIgnoreCustomTableByHook(): void {
    $group = $this->customGroupCreate();
    Civi::settings()->set('logging', TRUE);
    $this->hookClass->setHook('civicrm_alterLogTables', [$this, 'noCustomTables']);
    $this->customFieldCreate(['custom_group_id' => $group['id']]);
  }

  /**
   * Remove all custom tables from tables to be logged.
   *
   * @param array $logTableSpec
   */
  public function noCustomTables(&$logTableSpec): void {
    foreach (array_keys($logTableSpec) as $index) {
      if (substr($index, 0, 14) === 'civicrm_value_') {
        unset($logTableSpec[$index]);
      }
    }
  }

  /**
   * Test that existing log tables with ARCHIVE engine are converted to InnoDB
   *
   * @throws \Exception
   */
  public function testArchiveEngineConversion(): void {
    Civi::settings()->set('logging', TRUE);
    $schema = new CRM_Logging_Schema();
    // change table to ARCHIVE
    CRM_Core_DAO::executeQuery('ALTER TABLE log_civicrm_acl ENGINE ARCHIVE');
    $log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
    while ($log_table->fetch()) {
      $this->assertRegexp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
    }
    // engine should not change by default
    $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
    $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
    while ($log_table->fetch()) {
      $this->assertRegExp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
    }
    // update with forceEngineMigration should convert to InnoDB
    $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => TRUE]);
    $log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
    while ($log_table->fetch()) {
      $this->assertRegExp('/ENGINE=InnoDB/', $log_table->Create_Table);
    }
  }

  /**
   * Alter the engine on the log tables.
   *
   * @param $logTableSpec
   */
  public function alterLogTables(&$logTableSpec) {
    foreach (array_keys($logTableSpec) as $tableName) {
      $logTableSpec[$tableName]['engine'] = 'MyISAM';
    }
  }

  /**
   * Test correct creation of modified date triggers.
   *
   * Specifically we are testing that the contact table modified date and
   * ONLY the contact table modified date is updated when the custom field is updated.
   *
   * (At point of writing this the modification was leaking to the mailing table).
   */
  public function testTriggers() {
    $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest....');
    Civi::service('sql_triggers')->rebuild();
    $log_table = CRM_Core_DAO::executeQuery("SHOW TRIGGERS WHERE `Trigger` LIKE 'civicrm_value_contact_{$customGroup['custom_group_id']}_after_insert%'");

    while ($log_table->fetch()) {
      $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);
      $this->assertStringNotContainsString('civicrm_mailing', $log_table->Statement, 'Contact field should not update mailing table');
      $this->assertEquals(1, substr_count($log_table->Statement, 'SET modified_date'), 'Modified date should only be updated on one table (here it is contact)');
    }
  }

  /**
   * Test that autoincrement keys are handled sensibly in logging table reconciliation.
   */
  public function testAutoIncrementNonIdColumn() {
    CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_table` (
      test_id  int(10) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`test_id`)
    )  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");

    Civi::settings()->set('logging', TRUE);
    $schema = new CRM_Logging_Schema();
    $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
    // Test that just having a non id named column with Auto Increment doesn't create diffs
    $this->assertTrue(empty($diffs['MODIFY']));
    $this->assertTrue(empty($diffs['ADD']));
    $this->assertTrue(empty($diffs['OBSOLETE']));

    // Check we can add a primary key to the log table and it will not be treated as obsolete.
    CRM_Core_DAO::executeQuery('
      ALTER TABLE log_civicrm_test_table ADD COLUMN `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      ADD PRIMARY KEY (`log_id`)
   ');
    Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
    $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
    $this->assertEmpty($diffs['OBSOLETE']);

    CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_table ADD COLUMN test_varchar varchar(255) DEFAULT NULL");
    Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
    // Check that it still picks up new columns added.
    $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
    $this->assertTrue(!empty($diffs['ADD']));
    $this->assertTrue(empty($diffs['MODIFY']));
    $this->assertTrue(empty($diffs['OBSOLETE']));
    unset(Civi::$statics['CRM_Logging_Schema']);
    $schema->fixSchemaDifferences();
    CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(400) DEFAULT NULL');
    // Check that it properly picks up modifications to columns.
    unset(Civi::$statics['CRM_Logging_Schema']);
    $diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
    $this->assertNotEmpty($diffs['MODIFY']);
    $this->assertEmpty($diffs['ADD']);
    $this->assertEmpty($diffs['OBSOLETE']);
    $schema->fixSchemaDifferences();
    CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(300) DEFAULT NULL');
    // Check that when we reduce the size of column that the log table doesn't shrink as well.
    Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
    $diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
    $this->assertTrue(empty($diffs['MODIFY']));
    $this->assertTrue(empty($diffs['ADD']));
    $this->assertTrue(empty($diffs['OBSOLETE']));
  }

  /**
   * Test logging trigger definition
   */
  public function testTriggerInfo() {
    $info = [];
    $schema = new CRM_Logging_Schema();
    Civi::settings()->set('logging', TRUE);
    $schema->triggerInfo($info, 'civicrm_group');
    // should have 3 triggers (insert/update/delete)
    $this->assertCount(3, $info);
    foreach ($info as $trigger) {
      // table for trigger should be civicrm_group
      $this->assertEquals('civicrm_group', $trigger['table'][0]);
      if ($trigger['event'][0] == 'UPDATE') {
        // civicrm_group.cache_date should be an exception, i.e. not logged
        $this->assertStringNotContainsString(
          "IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')",
          $trigger['sql']
        );
      }
    }
  }

  /**
   * @throws \CiviCRM_API3_Exception
   */
  public function testColumnInfo(): void {
    CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_column_info` (
      test_id  int(10) unsigned NOT NULL AUTO_INCREMENT,
      test_varchar varchar(42) NOT NULL,
      test_integer int(8) NULL,
      test_decimal decimal(20,2),
      test_enum enum('A','B','C'),
      test_integer_default int(8) DEFAULT 42,
      test_date date DEFAULT NULL,
      PRIMARY KEY (`test_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
    Civi::settings()->set('logging', TRUE);

    $schema = new CRM_Logging_Schema();
    $schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
    $ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info'];

    $this->assertEquals('test_id', $ci['test_id']['COLUMN_NAME']);
    $this->assertEquals('int', $ci['test_id']['DATA_TYPE']);
    $this->assertEquals('NO', $ci['test_id']['IS_NULLABLE']);
    $this->assertEquals('auto_increment', $ci['test_id']['EXTRA']);
    if (!$this->isMySQL8()) {
      $this->assertEquals('10', $ci['test_id']['LENGTH']);
    }

    $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
    $this->assertEquals('42', $ci['test_varchar']['LENGTH']);

    $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
    if (!$this->isMySQL8()) {
      $this->assertEquals('8', $ci['test_integer']['LENGTH']);
    }
    $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);

    $this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']);
    $this->assertEquals('20,2', $ci['test_decimal']['LENGTH']);

    $this->assertEquals('enum', $ci['test_enum']['DATA_TYPE']);
    $this->assertEquals("'A','B','C'", $ci['test_enum']['ENUM_VALUES']);
    $this->assertArrayNotHasKey('LENGTH', $ci['test_enum']);

    $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);

    $this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
  }

  public function testIndexes() {
    $schema = new CRM_Logging_Schema();
    $indexes = $schema->getIndexesForTable('civicrm_contact');
    $this->assertContains('PRIMARY', $indexes);
    $this->assertContains('UI_external_identifier', $indexes);
    $this->assertContains('FK_civicrm_contact_employer_id', $indexes);
    $this->assertContains('index_sort_name', $indexes);
  }

  public function testLengthChange() {
    CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_length_change` (
      test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
      test_integer int(4) NULL,
      test_decimal decimal(20,2) NULL,
      PRIMARY KEY (`test_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
    Civi::settings()->set('logging', TRUE);
    $schema = new CRM_Logging_Schema();
    CRM_Core_DAO::executeQuery(
      "ALTER TABLE civicrm_test_length_change
      CHANGE COLUMN test_integer test_integer int(6) NULL,
      CHANGE COLUMN test_decimal test_decimal decimal(22,2) NULL"
    );
    $schema->fixSchemaDifferences();
    $ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
    // length should increase
    if (!$this->isMySQL8()) {
      $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
    }
    $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
    CRM_Core_DAO::executeQuery(
      "ALTER TABLE civicrm_test_length_change
      CHANGE COLUMN test_integer test_integer int(4) NULL,
      CHANGE COLUMN test_decimal test_decimal decimal(20,2) NULL"
    );
    $schema->fixSchemaDifferences();
    $ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
    // length should not decrease
    if (!$this->isMySQL8()) {
      $this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
    }
    $this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
  }

  /**
   * Test changing the enum.
   */
  public function testEnumChange(): void {
    CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_enum_change` (
      test_id int(10) unsigned NOT NULL AUTO_INCREMENT,
      test_enum enum('A','B','C') NULL,
      PRIMARY KEY (`test_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
    Civi::settings()->set('logging', TRUE);
    $schema = new CRM_Logging_Schema();
    CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_enum_change CHANGE COLUMN test_enum test_enum enum('A','B','C','D') NULL");
    Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
    $schema->fixSchemaDifferences();
    $ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
    // new enum value should be included
    $this->assertEquals("'A','B','C','D'", $ci['civicrm_test_enum_change']['test_enum']['ENUM_VALUES']);
  }

  /**
   * Test editing a custom field
   *
   * @throws \CRM_Core_Exception
   */
  public function testCustomFieldEdit(): void {
    Civi::settings()->set('logging', TRUE);
    $customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest.php');

    // get the custom group table name
    $params = ['id' => $customGroup['custom_group_id']];
    $custom_group = $this->callAPISuccess('custom_group', 'getsingle', $params);

    // get the field db column name
    $params = ['id' => $customGroup['custom_field_id']];
    $custom_field = $this->callAPISuccess('custom_field', 'getsingle', $params);

    // check it
    $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
    $dao->fetch();
    $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(255)", $dao->Create_Table);

    // Edit the field
    $params = [
      'id' => $customGroup['custom_field_id'],
      'label' => 'Label changed',
      'text_length' => 768,
    ];
    $this->callAPISuccess('custom_field', 'create', $params);

    // update logging schema
    $schema = new CRM_Logging_Schema();
    $schema->fixSchemaDifferences();

    // verify
    $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
    $dao->fetch();
    $this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table);
  }

  /**
   * Test creating a table with SchemaHandler::createTable when logging
   * is enabled.
   */
  public function testCreateTableWithLogging(): void {
    Civi::settings()->set('logging', TRUE);

    CRM_Core_BAO_SchemaHandler::createTable([
      'name' => 'civicrm_test_table',
      'is_multiple' => FALSE,
      'attributes' => 'ENGINE=InnoDB',
      'fields' => [
        [
          'name' => 'id',
          'type' => 'int unsigned',
          'primary' => TRUE,
          'required' => TRUE,
          'attributes' => 'AUTO_INCREMENT',
          'comment' => 'Default MySQL primary key',
        ],
        [
          'name' => 'activity_id',
          'type' => 'int unsigned',
          'required' => TRUE,
          'comment' => 'FK to civicrm_activity',
          'fk_table_name' => 'civicrm_activity',
          'fk_field_name' => 'id',
          'fk_attributes' => 'ON DELETE CASCADE',
        ],
        [
          'name' => 'texty',
          'type' => 'varchar(255)',
          'required' => FALSE,
        ],
      ],
    ]);
    $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_table");
    $dao->fetch();
    // using regex since not sure it's always int(10), so accept int(10), int(11), integer, etc...
    $this->assertRegExp('/`id` int(.*) unsigned NOT NULL AUTO_INCREMENT/', $dao->Create_Table);
    $this->assertRegExp('/`activity_id` int(.*) unsigned NOT NULL/', $dao->Create_Table);
    $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table);
    $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table);
    $this->assertStringContainsString('FOREIGN KEY (`activity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE', $dao->Create_Table);

    // Check log table.
    $dao = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_test_table');
    $dao->fetch();
    $this->assertStringNotContainsString('AUTO_INCREMENT', $dao->Create_Table);
    // This seems debatable whether `id` should lose its NOT NULL status
    $this->assertRegExp('/`id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table);
    $this->assertRegExp('/`activity_id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table);
    $this->assertStringContainsString('`texty` varchar(255)', $dao->Create_Table);
    $this->assertStringContainsString('ENGINE=InnoDB', $dao->Create_Table);
    $this->assertStringNotContainsString('FOREIGN KEY', $dao->Create_Table);
  }

  /**
   * Determine if we are running on MySQL 8 version 8.0.19 or later.
   *
   * @return bool
   */
  protected function isMySQL8(): bool {
    return (bool) (version_compare($this->databaseVersion, '8.0.19', '>=') && stripos($this->databaseVersion, 'mariadb') === FALSE);
  }

}
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
1cc18449 SL	9	protected $databaseVersion;
1cc18449 SL	10
0b49aa04	11	public function setUp(): void {
1cc18449	12	$this->databaseVersion = CRM_Utils_SQL::getDatabaseVersion();
91bd5c14 SL	13	parent::setUp();
	14	}
	15
89d7d62e	16	/**
	17	* Clean up after test.
	18	*
4b208509	19	* @throws \API_Exception
89d7d62e	20	* @throws \CRM_Core_Exception
4b208509	21	* @throws \CiviCRM_API3_Exception
89d7d62e	22	*/
dd09ee0c	23	public function tearDown(): void {
16142ce2	24	$schema = new CRM_Logging_Schema();
4b208509 EM	25	$schema->dropAllLogTables();
4b208509 EM	26	Civi::settings()->set('logging', FALSE);
1cc18449	27	$this->databaseVersion = NULL;
89d7d62e	28	parent::tearDown();
89d7d62e	29	$this->quickCleanup(['civicrm_contact'], TRUE);
4b208509 EM	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');
91bd5c14 SL	34	}
91bd5c14 SL	35
89d7d62e	36	/**
	37	* Data provider for testing query re-writing.
	38	*
	39	* @return array
	40	*/
4b208509	41	public function queryExamples(): array {
91bd5c14 SL	42	$examples = [];
	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.'"];
	45	return $examples;
	46	}
	47
	48	/**
	49	* Tests the function fixTimeStampAndNotNullSQL in CRM_Logging_Schema
	50	*
	51	* @dataProvider queryExamples
	52	*/
	53	public function testQueryRewrite($query, $expectedQuery) {
	54	$this->assertEquals($expectedQuery, CRM_Logging_Schema::fixTimeStampAndNotNullSQL($query));
	55	}
	56
204aa6fb PF	57	/**
	58	* Test log tables are created as InnoDB by default
	59	*/
4b208509 EM	60	public function testLogEngine(): void {
	61	Civi::settings()->set('logging', TRUE);
	62	$log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
204aa6fb PF	63	while ($log_table->fetch()) {
	64	$this->assertRegexp('/ENGINE=InnoDB/', $log_table->Create_Table);
	65	}
	66	}
	67
	68	/**
	69	* Test that the log table engine can be changed via hook to e.g. MyISAM
	70	*/
4b208509	71	public function testHookLogEngine(): void {
9099cab3	72	$this->hookClass->setHook('civicrm_alterLogTables', [$this, 'alterLogTables']);
4b208509 EM	73	Civi::settings()->set('logging', TRUE);
4b208509 EM	74	$log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
204aa6fb PF	75	while ($log_table->fetch()) {
	76	$this->assertRegexp('/ENGINE=MyISAM/', $log_table->Create_Table);
	77	}
	78	}
	79
89d7d62e	80	/**
	81	* Tests that choosing to ignore a custom table does not result in e-notices.
	82	*/
4b208509	83	public function testIgnoreCustomTableByHook(): void {
89d7d62e	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	*/
4b208509	95	public function noCustomTables(&$logTableSpec): void {
89d7d62e	96	foreach (array_keys($logTableSpec) as $index) {
	97	if (substr($index, 0, 14) === 'civicrm_value_') {
	98	unset($logTableSpec[$index]);
	99	}
	100	}
	101	}
	102
204aa6fb PF	103	/**
	104	* Test that existing log tables with ARCHIVE engine are converted to InnoDB
	105	*
	106	* @throws \Exception
	107	*/
4b208509 EM	108	public function testArchiveEngineConversion(): void {
4b208509 EM	109	Civi::settings()->set('logging', TRUE);
204aa6fb	110	$schema = new CRM_Logging_Schema();
204aa6fb	111	// change table to ARCHIVE
4b208509 EM	112	CRM_Core_DAO::executeQuery('ALTER TABLE log_civicrm_acl ENGINE ARCHIVE');
4b208509 EM	113	$log_table = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_acl');
a599e68c SL	114	while ($log_table->fetch()) {
	115	$this->assertRegexp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
	116	}
204aa6fb	117	// engine should not change by default
e26df891	118	$schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
204aa6fb PF	119	$log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
204aa6fb PF	120	while ($log_table->fetch()) {
89d7d62e	121	$this->assertRegExp('/ENGINE=ARCHIVE/', $log_table->Create_Table);
204aa6fb PF	122	}
204aa6fb PF	123	// update with forceEngineMigration should convert to InnoDB
e26df891	124	$schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => TRUE]);
204aa6fb PF	125	$log_table = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_acl");
204aa6fb PF	126	while ($log_table->fetch()) {
89d7d62e	127	$this->assertRegExp('/ENGINE=InnoDB/', $log_table->Create_Table);
204aa6fb PF	128	}
	129	}
	130
89d7d62e	131	/**
	132	* Alter the engine on the log tables.
	133	*
	134	* @param $logTableSpec
	135	*/
204aa6fb PF	136	public function alterLogTables(&$logTableSpec) {
	137	foreach (array_keys($logTableSpec) as $tableName) {
	138	$logTableSpec[$tableName]['engine'] = 'MyISAM';
	139	}
16142ce2 SL	140	}
16142ce2 SL	141
6bbc383d	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()) {
275686a3 SL	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);
275686a3 SL	157	$this->assertStringNotContainsString('civicrm_mailing', $log_table->Statement, 'Contact field should not update mailing table');
6bbc383d	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
235592e0	162	/**
	163	* Test that autoincrement keys are handled sensibly in logging table reconciliation.
	164	*/
16142ce2 SL	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");
4b208509 EM	170
4b208509 EM	171	Civi::settings()->set('logging', TRUE);
16142ce2	172	$schema = new CRM_Logging_Schema();
16142ce2	173	$diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
235592e0	174	// Test that just having a non id named column with Auto Increment doesn't create diffs
16142ce2 SL	175	$this->assertTrue(empty($diffs['MODIFY']));
	176	$this->assertTrue(empty($diffs['ADD']));
	177	$this->assertTrue(empty($diffs['OBSOLETE']));
235592e0	178
235592e0	179	// Check we can add a primary key to the log table and it will not be treated as obsolete.
4b208509	180	CRM_Core_DAO::executeQuery('
235592e0	181	ALTER TABLE log_civicrm_test_table ADD COLUMN `log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
235592e0	182	ADD PRIMARY KEY (`log_id`)
4b208509 EM	183	');
4b208509 EM	184	Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
235592e0	185	$diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
4b208509	186	$this->assertEmpty($diffs['OBSOLETE']);
235592e0	187
16142ce2	188	CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_table ADD COLUMN test_varchar varchar(255) DEFAULT NULL");
4b208509	189	Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
16142ce2 SL	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']));
4b208509	195	unset(Civi::$statics['CRM_Logging_Schema']);
16142ce2	196	$schema->fixSchemaDifferences();
4b208509	197	CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(400) DEFAULT NULL');
16142ce2	198	// Check that it properly picks up modifications to columns.
4b208509	199	unset(Civi::$statics['CRM_Logging_Schema']);
16142ce2	200	$diffs = $schema->columnsWithDiffSpecs("civicrm_test_table", "log_civicrm_test_table");
4b208509 EM	201	$this->assertNotEmpty($diffs['MODIFY']);
	202	$this->assertEmpty($diffs['ADD']);
	203	$this->assertEmpty($diffs['OBSOLETE']);
16142ce2	204	$schema->fixSchemaDifferences();
4b208509	205	CRM_Core_DAO::executeQuery('ALTER TABLE civicrm_test_table CHANGE COLUMN test_varchar test_varchar varchar(300) DEFAULT NULL');
16142ce2	206	// Check that when we reduce the size of column that the log table doesn't shrink as well.
4b208509 EM	207	Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
4b208509 EM	208	$diffs = $schema->columnsWithDiffSpecs('civicrm_test_table', "log_civicrm_test_table");
16142ce2 SL	209	$this->assertTrue(empty($diffs['MODIFY']));
	210	$this->assertTrue(empty($diffs['ADD']));
	211	$this->assertTrue(empty($diffs['OBSOLETE']));
a599e68c SL	212	}
a599e68c SL	213
9c3c84d1 PF	214	/**
	215	* Test logging trigger definition
	216	*/
	217	public function testTriggerInfo() {
	218	$info = [];
	219	$schema = new CRM_Logging_Schema();
4b208509	220	Civi::settings()->set('logging', TRUE);
9c3c84d1 PF	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
275686a3	229	$this->assertStringNotContainsString(
9c3c84d1 PF	230	"IFNULL(OLD.`cache_date`,'') <> IFNULL(NEW.`cache_date`,'')",
	231	$trigger['sql']
	232	);
	233	}
	234	}
	235	}
	236
4b208509 EM	237	/**
	238	* @throws \CiviCRM_API3_Exception
	239	*/
	240	public function testColumnInfo(): void {
9c8a3025 PF	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,
f34f504a PF	245	test_decimal decimal(20,2),
f34f504a PF	246	test_enum enum('A','B','C'),
9c8a3025 PF	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");
4b208509 EM	251	Civi::settings()->set('logging', TRUE);
4b208509 EM	252
9c8a3025	253	$schema = new CRM_Logging_Schema();
e26df891	254	$schema->updateLogTableSchema(['updateChangedEngineConfig' => FALSE, 'forceEngineMigration' => FALSE]);
4b208509	255	$ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info'];
9c8a3025 PF	256
	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']);
1cc18449 SL	261	if (!$this->isMySQL8()) {
	262	$this->assertEquals('10', $ci['test_id']['LENGTH']);
	263	}
9c8a3025 PF	264
	265	$this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
	266	$this->assertEquals('42', $ci['test_varchar']['LENGTH']);
	267
	268	$this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
1cc18449 SL	269	if (!$this->isMySQL8()) {
	270	$this->assertEquals('8', $ci['test_integer']['LENGTH']);
	271	}
9c8a3025 PF	272	$this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);
9c8a3025 PF	273
f34f504a PF	274	$this->assertEquals('decimal', $ci['test_decimal']['DATA_TYPE']);
	275	$this->assertEquals('20,2', $ci['test_decimal']['LENGTH']);
	276
	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']);
	280
9c8a3025 PF	281	$this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);
	282
	283	$this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
	284	}
	285
	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);
	293	}
	294
f34f504a PF	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");
4b208509	302	Civi::settings()->set('logging', TRUE);
f34f504a	303	$schema = new CRM_Logging_Schema();
f34f504a PF	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"
	308	);
f34f504a	309	$schema->fixSchemaDifferences();
4b208509	310	$ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
f34f504a	311	// length should increase
1cc18449 SL	312	if (!$this->isMySQL8()) {
	313	$this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
	314	}
f34f504a PF	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"
	320	);
f34f504a	321	$schema->fixSchemaDifferences();
4b208509	322	$ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
f34f504a	323	// length should not decrease
1cc18449 SL	324	if (!$this->isMySQL8()) {
	325	$this->assertEquals(6, $ci['log_civicrm_test_length_change']['test_integer']['LENGTH']);
	326	}
f34f504a PF	327	$this->assertEquals('22,2', $ci['log_civicrm_test_length_change']['test_decimal']['LENGTH']);
	328	}
	329
4b208509 EM	330	/**
	331	* Test changing the enum.
	332	*/
	333	public function testEnumChange(): void {
f34f504a PF	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");
4b208509	339	Civi::settings()->set('logging', TRUE);
f34f504a	340	$schema = new CRM_Logging_Schema();
f34f504a	341	CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_test_enum_change CHANGE COLUMN test_enum test_enum enum('A','B','C','D') NULL");
4b208509	342	Civi::$statics['CRM_Logging_Schema']['columnSpecs'] = [];
f34f504a	343	$schema->fixSchemaDifferences();
4b208509	344	$ci = Civi::$statics['CRM_Logging_Schema']['columnSpecs'];
f34f504a PF	345	// new enum value should be included
	346	$this->assertEquals("'A','B','C','D'", $ci['civicrm_test_enum_change']['test_enum']['ENUM_VALUES']);
	347	}
	348
b3dd4cb0	349	/**
b3dd4cb0	350	* Test editing a custom field
4b208509 EM	351	*
4b208509 EM	352	* @throws \CRM_Core_Exception
b3dd4cb0	353	*/
4b208509 EM	354	public function testCustomFieldEdit(): void {
4b208509 EM	355	Civi::settings()->set('logging', TRUE);
b3dd4cb0	356	$customGroup = $this->entityCustomGroupWithSingleFieldCreate('Contact', 'ContactTest.php');
	357
	358	// get the custom group table name
	359	$params = ['id' => $customGroup['custom_group_id']];
	360	$custom_group = $this->callAPISuccess('custom_group', 'getsingle', $params);
	361
	362	// get the field db column name
	363	$params = ['id' => $customGroup['custom_field_id']];
	364	$custom_field = $this->callAPISuccess('custom_field', 'getsingle', $params);
	365
	366	// check it
	367	$dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
	368	$dao->fetch();
	369	$this->assertStringContainsString("`{$custom_field['column_name']}` varchar(255)", $dao->Create_Table);
	370
	371	// Edit the field
	372	$params = [
	373	'id' => $customGroup['custom_field_id'],
	374	'label' => 'Label changed',
	375	'text_length' => 768,
	376	];
	377	$this->callAPISuccess('custom_field', 'create', $params);
	378
	379	// update logging schema
4b208509	380	$schema = new CRM_Logging_Schema();
b3dd4cb0	381	$schema->fixSchemaDifferences();
	382
	383	// verify
	384	$dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE `log_{$custom_group['table_name']}`");
	385	$dao->fetch();
	386	$this->assertStringContainsString("`{$custom_field['column_name']}` varchar(768)", $dao->Create_Table);
	387	}
	388
c3eff32d	389	/**
	390	* Test creating a table with SchemaHandler::createTable when logging
	391	* is enabled.
	392	*/
4b208509 EM	393	public function testCreateTableWithLogging(): void {
4b208509 EM	394	Civi::settings()->set('logging', TRUE);
c3eff32d	395
	396	CRM_Core_BAO_SchemaHandler::createTable([
	397	'name' => 'civicrm_test_table',
	398	'is_multiple' => FALSE,
	399	'attributes' => 'ENGINE=InnoDB',
	400	'fields' => [
	401	[
	402	'name' => 'id',
	403	'type' => 'int unsigned',
	404	'primary' => TRUE,
	405	'required' => TRUE,
	406	'attributes' => 'AUTO_INCREMENT',
	407	'comment' => 'Default MySQL primary key',
	408	],
	409	[
	410	'name' => 'activity_id',
	411	'type' => 'int unsigned',
	412	'required' => TRUE,
	413	'comment' => 'FK to civicrm_activity',
	414	'fk_table_name' => 'civicrm_activity',
	415	'fk_field_name' => 'id',
	416	'fk_attributes' => 'ON DELETE CASCADE',
	417	],
	418	[
	419	'name' => 'texty',
	420	'type' => 'varchar(255)',
	421	'required' => FALSE,
	422	],
	423	],
	424	]);
	425	$dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE civicrm_test_table");
	426	$dao->fetch();
	427	// using regex since not sure it's always int(10), so accept int(10), int(11), integer, etc...
9ddc0612 SL	428	$this->assertRegExp('/`id` int(.*) unsigned NOT NULL AUTO_INCREMENT/', $dao->Create_Table);
9ddc0612 SL	429	$this->assertRegExp('/`activity_id` int(.*) unsigned NOT NULL/', $dao->Create_Table);
c3eff32d	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);
	433
	434	// Check log table.
4b208509	435	$dao = CRM_Core_DAO::executeQuery('SHOW CREATE TABLE log_civicrm_test_table');
c3eff32d	436	$dao->fetch();
	437	$this->assertStringNotContainsString('AUTO_INCREMENT', $dao->Create_Table);
	438	// This seems debatable whether `id` should lose its NOT NULL status
9ddc0612 SL	439	$this->assertRegExp('/`id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table);
9ddc0612 SL	440	$this->assertRegExp('/`activity_id` int(.*) unsigned DEFAULT NULL/', $dao->Create_Table);
c3eff32d	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);
	444	}
	445
1cc18449 SL	446	/**
	447	* Determine if we are running on MySQL 8 version 8.0.19 or later.
	448	*
	449	* @return bool
	450	*/
4b208509	451	protected function isMySQL8(): bool {
1cc18449 SL	452	return (bool) (version_compare($this->databaseVersion, '8.0.19', '>=') && stripos($this->databaseVersion, 'mariadb') === FALSE);
	453	}
	454
91bd5c14	455	}