3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Logging_Schema
{
20 * Default storage engine for log tables
24 const ENGINE
= 'InnoDB';
30 * Name of the database where the logging data is stored.
36 private $useDBPrefix = TRUE;
39 'logging/contact/detail',
40 'logging/contact/summary',
41 'logging/contribute/detail',
42 'logging/contribute/summary',
46 * Columns that should never be subject to logging.
48 * CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
52 private $exceptions = [
53 'civicrm_job' => ['last_run'],
54 'civicrm_group' => ['cache_date', 'refresh_date'],
58 * Specifications of all log table including
59 * - engine (default is InnoDB, if not set.)
60 * - engine_config, a string appended to the engine type.
61 * For INNODB space can be saved with 'ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4'
62 * - indexes (default is none and they cannot be added unless engine is innodb. If they are added and
63 * engine is not set to innodb an exception will be thrown since quiet acquiescence is easier to miss).
64 * - exceptions (by default those stored in $this->exceptions are included). These are
65 * excluded from the triggers.
69 private $logTableSpec = [];
72 * Setting Callback - Validate.
75 * @param array $fieldSpec
78 * @throws API_Exception
80 public static function checkLoggingSupport(&$value, $fieldSpec) {
81 if (!(CRM_Core_DAO
::checkTriggerViewPermission(FALSE)) && $value) {
82 throw new API_Exception(ts("In order to use this functionality, the installation's database user must have privileges to create triggers and views (if binary logging is enabled – this means the SUPER privilege). This install does not have the required privilege(s) enabled."));
84 // dev/core#1812 Disable logging in a multilingual environment.
85 if (CRM_Core_I18n
::isMultilingual() && $value) {
86 throw new API_Exception(ts("Logging is not supported in a multilingual environment!"));
92 * Setting Callback - On Change.
94 * Respond to changes in the "logging" setting. Set up or destroy
97 * @param array $oldValue
98 * List of component names.
99 * @param array $newValue
100 * List of component names.
101 * @param array $metadata
102 * Specification of the setting (per *.settings.php).
104 public static function onToggle($oldValue, $newValue, $metadata) {
105 if ($oldValue == $newValue) {
109 $logging = new CRM_Logging_Schema();
111 $logging->enableLogging();
114 $logging->disableLogging();
119 * Populate $this->tables and $this->logs with current db state.
121 public function __construct() {
122 $civiDBName = $this->getCiviCRMDatabaseName();
124 $dao = CRM_Core_DAO
::executeQuery("
126 FROM INFORMATION_SCHEMA.TABLES
127 WHERE TABLE_SCHEMA = '{$civiDBName}'
128 AND TABLE_TYPE = 'BASE TABLE'
129 AND TABLE_NAME LIKE 'civicrm_%'
131 while ($dao->fetch()) {
132 $this->tables
[] = $dao->TABLE_NAME
;
135 // do not log temp import, cache, menu and log tables
136 $this->tables
= preg_grep('/^civicrm_import_job_/', $this->tables
, PREG_GREP_INVERT
);
137 $this->tables
= preg_grep('/_cache$/', $this->tables
, PREG_GREP_INVERT
);
138 $this->tables
= preg_grep('/_log/', $this->tables
, PREG_GREP_INVERT
);
139 $this->tables
= preg_grep('/^civicrm_queue_/', $this->tables
, PREG_GREP_INVERT
);
141 $this->tables
= preg_grep('/^civicrm_menu/', $this->tables
, PREG_GREP_INVERT
);
142 $this->tables
= preg_grep('/_temp_/', $this->tables
, PREG_GREP_INVERT
);
144 $this->tables
= preg_grep('/_bak$/', $this->tables
, PREG_GREP_INVERT
);
145 $this->tables
= preg_grep('/_backup$/', $this->tables
, PREG_GREP_INVERT
);
147 $this->tables
= preg_grep('/^civicrm_tmp_/', $this->tables
, PREG_GREP_INVERT
);
149 // do not log civicrm_mailing_event* tables, CRM-12300
150 $this->tables
= preg_grep('/^civicrm_mailing_event_/', $this->tables
, PREG_GREP_INVERT
);
152 // dev/core#1762 Don't log subscription_history
153 $this->tables
= preg_grep('/^civicrm_subscription_history/', $this->tables
, PREG_GREP_INVERT
);
155 // do not log civicrm_mailing_recipients table, CRM-16193
156 $this->tables
= array_diff($this->tables
, ['civicrm_mailing_recipients']);
157 $this->logTableSpec
= array_fill_keys($this->tables
, []);
158 foreach ($this->exceptions
as $tableName => $fields) {
159 $this->logTableSpec
[$tableName]['exceptions'] = $fields;
161 CRM_Utils_Hook
::alterLogTables($this->logTableSpec
);
162 $this->tables
= array_keys($this->logTableSpec
);
163 $nonStandardTableNameString = $this->getNonStandardTableNameFilterString();
165 $this->db
= $this->getDatabaseNameFromDSN(defined('CIVICRM_LOGGING_DSN') ? CIVICRM_LOGGING_DSN
: CIVICRM_DSN
);
166 $this->useDBPrefix
= $this->db
!== $civiDBName;
168 $dao = CRM_Core_DAO
::executeQuery("
170 FROM INFORMATION_SCHEMA.TABLES
171 WHERE TABLE_SCHEMA = '{$this->db}'
172 AND TABLE_TYPE = 'BASE TABLE'
173 AND (TABLE_NAME LIKE 'log_civicrm_%' $nonStandardTableNameString )
175 while ($dao->fetch()) {
176 $log = $dao->TABLE_NAME
;
177 $this->logs
[substr($log, 4)] = $log;
182 * Return logging custom data tables.
184 public function customDataLogTables() {
185 return preg_grep('/^log_civicrm_value_/', $this->logs
);
189 * Return custom data tables for specified entity / extends.
191 * @param string $extends
195 public function entityCustomDataLogTables($extends) {
196 $customGroupTables = [];
197 $customGroupDAO = CRM_Core_BAO_CustomGroup
::getAllCustomGroupsByBaseEntity($extends);
198 $customGroupDAO->find();
199 while ($customGroupDAO->fetch()) {
200 // logging is disabled for the table (e.g by hook) then $this->logs[$customGroupDAO->table_name]
202 if (!empty($this->logs
[$customGroupDAO->table_name
])) {
203 $customGroupTables[$customGroupDAO->table_name
] = $this->logs
[$customGroupDAO->table_name
];
206 return $customGroupTables;
210 * Disable logging by dropping the triggers (but keep the log tables intact).
212 public function disableLogging() {
213 $config = CRM_Core_Config
::singleton();
214 $config->logging
= FALSE;
216 $this->dropTriggers();
218 // invoke the meta trigger creation call
219 CRM_Core_DAO
::triggerRebuild();
221 $this->deleteReports();
225 * Drop triggers for all logged tables.
227 * @param string $tableName
229 public function dropTriggers($tableName = NULL) {
230 /** @var \Civi\Core\SqlTriggers $sqlTriggers */
231 $sqlTriggers = Civi
::service('sql_triggers');
232 $dao = new CRM_Core_DAO();
235 $tableNames = [$tableName];
238 $tableNames = $this->tables
;
241 // Sort the table names so the sql output is consistent for those sites
242 // loading it asynchronously (using the setting 'logging_no_trigger_permission')
244 foreach ($tableNames as $table) {
245 $validName = CRM_Core_DAO
::shortenSQLName($table, 48, TRUE);
248 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
249 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
250 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
253 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
254 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
255 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
258 // now lets also be safe and drop all triggers that start with
259 // civicrm_ if we are dropping all triggers
260 // we need to do this to capture all the leftover triggers since
261 // we did the shortening trigger name for CRM-11794
262 if ($tableName === NULL) {
263 $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
265 while ($triggers->fetch()) {
266 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
272 * Enable site-wide logging.
274 public function enableLogging() {
275 $this->fixSchemaDifferences(TRUE);
280 * Sync log tables and rebuild triggers.
282 * @param bool $enableLogging : Ensure logging is enabled
284 public function fixSchemaDifferences($enableLogging = FALSE) {
285 $config = CRM_Core_Config
::singleton();
286 if ($enableLogging) {
287 $config->logging
= TRUE;
289 if ($config->logging
) {
290 $this->fixSchemaDifferencesForAll();
292 // invoke the meta trigger creation call
293 CRM_Core_DAO
::triggerRebuild(NULL, TRUE);
297 * Update log tables structure.
299 * This function updates log tables to have the log_conn_id type of varchar
300 * and also implements the engine change defined by the hook (i.e. INNODB).
302 * Note changing engine & adding hook-defined indexes, but not changing back
303 * to INNODB if engine has not been deliberately set (by hook) and not
304 * dropping indexes. Sysadmin will need to manually intervene to revert to
307 * @param array $params
308 * 'updateChangedEngineConfig' - update if the engine config changes?
309 * 'forceEngineMigration' - force engine upgrade from ARCHIVE to InnoDB?
311 * @return int $updateTablesCount
312 * @throws \CiviCRM_API3_Exception
314 public function updateLogTableSchema($params) {
315 $updateLogConn = FALSE;
316 $updatedTablesCount = 0;
317 foreach ($this->logs
as $mainTable => $logTable) {
319 $tableSpec = $this->logTableSpec
[$mainTable];
320 $currentEngine = strtoupper($this->getEngineForLogTable($logTable));
321 if (!isset($tableSpec['engine']) && $currentEngine == 'ARCHIVE' && $params['forceEngineMigration']) {
322 // table uses ARCHIVE engine (the previous default) and no one set an
323 // alternative engine via hook_civicrm_alterLogTables => force change to
325 $tableSpec['engine'] = self
::ENGINE
;
327 $engineChanged = isset($tableSpec['engine']) && (strtoupper($tableSpec['engine']) != $currentEngine);
328 $engineConfigChanged = isset($tableSpec['engine_config']) && (strtoupper($tableSpec['engine_config']) != $this->getEngineConfigForLogTable($logTable));
329 if ($engineChanged ||
($engineConfigChanged && $params['updateChangedEngineConfig'])) {
330 $alterSql[] = "ENGINE=" . $tableSpec['engine'] . " " . CRM_Utils_Array
::value('engine_config', $tableSpec);
332 if (!empty($tableSpec['indexes'])) {
333 $indexes = $this->getIndexesForTable($logTable);
334 foreach ($tableSpec['indexes'] as $indexName => $indexSpec) {
335 if (!in_array($indexName, $indexes)) {
336 if (is_array($indexSpec)) {
337 $indexSpec = implode(" , ", $indexSpec);
339 $alterSql[] = "ADD INDEX {$indexName}($indexSpec)";
343 $columns = $this->columnSpecsOf($logTable);
344 if (empty($columns['log_conn_id'])) {
345 throw new Exception($logTable . print_r($columns, TRUE));
347 if ($columns['log_conn_id']['DATA_TYPE'] != 'varchar' ||
$columns['log_conn_id']['LENGTH'] != 17) {
348 $alterSql[] = "MODIFY log_conn_id VARCHAR(17)";
349 $updateLogConn = TRUE;
351 if (!empty($alterSql)) {
352 CRM_Core_DAO
::executeQuery("ALTER TABLE {$this->db}.{$logTable} " . implode(', ', $alterSql), [], TRUE, NULL, FALSE, FALSE);
353 $updatedTablesCount++
;
356 if ($updateLogConn) {
357 civicrm_api3('Setting', 'create', ['logging_uniqueid_date' => date('Y-m-d H:i:s')]);
359 return $updatedTablesCount;
363 * Get the engine for the given table.
365 * @param string $table
369 public function getEngineForLogTable($table) {
370 return strtoupper(CRM_Core_DAO
::singleValueQuery("
371 SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = %1
372 AND table_schema = %2
373 ", [1 => [$table, 'String'], 2 => [$this->db
, 'String']]));
377 * Get the engine config for the given table.
379 * @param string $table
383 public function getEngineConfigForLogTable($table) {
384 return strtoupper(CRM_Core_DAO
::singleValueQuery("
385 SELECT CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = %1
386 AND table_schema = %2
387 ", [1 => [$table, 'String'], 2 => [$this->db
, 'String']]));
391 * Get all the indexes in the table.
393 * @param string $table
397 public function getIndexesForTable($table) {
399 $result = CRM_Core_DAO
::executeQuery("
400 SELECT constraint_name AS index_name
401 FROM information_schema.key_column_usage
402 WHERE table_schema = %2 AND table_name = %1
404 SELECT index_name AS index_name
405 FROM information_schema.statistics
406 WHERE table_schema = %2 AND table_name = %1
408 [1 => [$table, 'String'], 2 => [$this->db
, 'String']]
410 while ($result->fetch()) {
411 $indexes[] = $result->index_name
;
417 * Add missing (potentially specified) log table columns for the given table.
419 * @param string $table
420 * name of the relevant table.
422 * Mixed array of columns to add or null (to check for the missing columns).
426 public function fixSchemaDifferencesFor($table, $cols = []) {
430 if (empty($this->logs
[$table])) {
431 $this->createLogTableFor($table);
436 $cols = $this->columnsWithDiffSpecs($table, "log_$table");
439 // If a column that already exists on logging table is being added, we
440 // should treat it as a modification.
441 $this->resetSchemaCacheForTable("log_$table");
442 $logTableSchema = $this->columnSpecsOf("log_$table");
443 if (!empty($cols['ADD'])) {
444 foreach ($cols['ADD'] as $colKey => $col) {
445 if (array_key_exists($col, $logTableSchema)) {
446 $cols['MODIFY'][] = $col;
447 unset($cols['ADD'][$colKey]);
452 // use the relevant lines from CREATE TABLE to add colums to the log table
453 $create = $this->_getCreateQuery($table);
454 foreach ((['ADD', 'MODIFY']) as $alterType) {
455 if (!empty($cols[$alterType])) {
456 foreach ($cols[$alterType] as $col) {
457 $line = $this->_getColumnQuery($col, $create);
458 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}", [], TRUE, NULL, FALSE, FALSE);
463 // for any obsolete columns (not null) we just make the column nullable.
464 if (!empty($cols['OBSOLETE'])) {
465 $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
466 foreach ($cols['OBSOLETE'] as $col) {
467 $line = $this->_getColumnQuery($col, $create);
468 // This is just going to make a not null column to nullable
469 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}", [], TRUE, NULL, FALSE, FALSE);
473 $this->resetSchemaCacheForTable("log_$table");
479 * Resets schema cache for the given table.
481 * @param string $table
484 private function resetSchemaCacheForTable($table) {
485 unset(\Civi
::$statics[__CLASS__
]['columnSpecs'][$table]);
491 * @param string $table
495 private function _getCreateQuery($table) {
496 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE {$table}", [], TRUE, NULL, FALSE, FALSE);
498 $create = explode("\n", $dao->Create_Table
);
506 * @param bool $createQuery
508 * @return array|mixed|string
510 private function _getColumnQuery($col, $createQuery) {
511 $line = preg_grep("/^ `$col` /", $createQuery);
512 $line = rtrim(array_pop($line), ',');
514 $line = self
::fixTimeStampAndNotNullSQL($line);
519 * Fix schema differences.
521 public function fixSchemaDifferencesForAll(): void
{
523 $this->resetTableColumnsCache();
525 foreach ($this->tables
as $table) {
526 if (empty($this->logs
[$table])) {
527 $this->createLogTableFor($table);
530 $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
534 foreach ($diffs as $table => $cols) {
535 $this->fixSchemaDifferencesFor($table, $cols);
540 * Resets columnSpecs.
542 * Resets columnSpecs static array in Civi's $statics to make sure we use the
543 * real state of the schema to perform sync operations between core and
546 private function resetTableColumnsCache() {
547 unset(\Civi
::$statics[__CLASS__
]['columnSpecs']);
553 * Log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
554 * so there's no need for a default timestamp and therefore we remove such default timestamps
555 * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
557 * @param string $query
561 public static function fixTimeStampAndNotNullSQL($query) {
562 $query = str_ireplace("TIMESTAMP() NOT NULL", "TIMESTAMP NULL", $query);
563 $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
564 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()", '', $query);
565 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
566 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP()", '', $query);
567 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
568 $query = str_ireplace("NOT NULL", '', $query);
575 private function addReports() {
577 'logging/contact/detail' => ts('Logging Details'),
578 'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
579 'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
580 'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
582 // enable logging templates
583 CRM_Core_DAO
::executeQuery("
584 UPDATE civicrm_option_value
586 WHERE value IN ('" . implode("', '", $this->reports
) . "')
589 // add report instances
590 $domain_id = CRM_Core_Config
::domainID();
591 foreach ($this->reports
as $report) {
592 $dao = new CRM_Report_DAO_ReportInstance();
593 $dao->domain_id
= $domain_id;
594 $dao->report_id
= $report;
595 $dao->title
= $titles[$report];
596 $dao->permission
= 'administer CiviCRM';
597 if ($report == 'logging/contact/summary') {
598 $dao->is_reserved
= 1;
605 * Get an array of column names of the given table.
607 * @param string $table
612 private function columnsOf($table, $force = FALSE) {
613 if ($force ||
!isset(\Civi
::$statics[__CLASS__
]['columnsOf'][$table])) {
614 $from = (substr($table, 0, 4) == 'log_') ?
"`{$this->db}`.$table" : $table;
615 $dao = CRM_Core_DAO
::executeQuery("SHOW COLUMNS FROM $from", [], TRUE, NULL, FALSE, FALSE);
616 if (is_a($dao, 'DB_Error')) {
619 \Civi
::$statics[__CLASS__
]['columnsOf'][$table] = [];
620 while ($dao->fetch()) {
621 \Civi
::$statics[__CLASS__
]['columnsOf'][$table][] = CRM_Utils_Type
::escape($dao->Field
, 'MysqlColumnNameOrAlias');
624 return \Civi
::$statics[__CLASS__
]['columnsOf'][$table];
628 * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
630 * @param string $table
634 private function columnSpecsOf($table) {
635 static $civiDB = NULL;
636 if (empty(\Civi
::$statics[__CLASS__
]['columnSpecs'])) {
637 \Civi
::$statics[__CLASS__
]['columnSpecs'] = [];
639 if (empty(\Civi
::$statics[__CLASS__
]['columnSpecs']) ||
!isset(\Civi
::$statics[__CLASS__
]['columnSpecs'][$table])) {
641 $dao = new CRM_Contact_DAO_Contact();
642 $civiDB = $dao->_database
;
645 // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
646 // than firing query for every given table.
648 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, EXTRA
649 FROM INFORMATION_SCHEMA.COLUMNS
650 WHERE table_schema IN ('{$this->db}', '{$civiDB}')";
651 $dao = CRM_Core_DAO
::executeQuery($query);
652 if (is_a($dao, 'DB_Error')) {
655 while ($dao->fetch()) {
656 if (!array_key_exists($dao->TABLE_NAME
, \Civi
::$statics[__CLASS__
]['columnSpecs'])) {
657 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
] = [];
659 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
] = [
660 'COLUMN_NAME' => $dao->COLUMN_NAME
,
661 'DATA_TYPE' => $dao->DATA_TYPE
,
662 'IS_NULLABLE' => $dao->IS_NULLABLE
,
663 'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT
,
664 'EXTRA' => $dao->EXTRA
,
666 if (($first = strpos($dao->COLUMN_TYPE
, '(')) != 0) {
667 // this extracts the value between parentheses after the column type.
668 // it could be the column length, i.e. "int(8)", "decimal(20,2)")
669 // or the permitted values of an enum (e.g. "enum('A','B')")
671 $dao->COLUMN_TYPE
, $first +
1, strpos($dao->COLUMN_TYPE
, ')') - $first - 1
673 if (strpos($parValue, "'") === FALSE) {
674 // no quote in value means column length
675 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
]['LENGTH'] = $parValue;
678 // single quote means enum permitted values
679 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
]['ENUM_VALUES'] = $parValue;
684 return \Civi
::$statics[__CLASS__
]['columnSpecs'][$table];
688 * Get columns that have changed.
690 * @param string $civiTable
691 * @param string $logTable
695 public function columnsWithDiffSpecs($civiTable, $logTable) {
696 $civiTableSpecs = $this->columnSpecsOf($civiTable);
697 $logTableSpecs = $this->columnSpecsOf($logTable);
699 $diff = ['ADD' => [], 'MODIFY' => [], 'OBSOLETE' => []];
701 // Columns to be added
702 $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
704 // Columns to be modified
705 // Only pick columns where there is a spec change and the column definition was not deliberately modified by
706 // fixTimeStampAndNotNullSQL() method, also accounting for differences in db version.
707 foreach ($civiTableSpecs as $col => $colSpecs) {
708 if (!isset($logTableSpecs[$col]) ||
!is_array($logTableSpecs[$col])) {
709 $logTableSpecs[$col] = [];
711 $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
712 if (!empty($specDiff) && $col !== 'id' && !in_array($col, $diff['ADD'])) {
713 if (empty($colSpecs['EXTRA']) ||
(!empty($colSpecs['EXTRA']) && $colSpecs['EXTRA'] !== 'auto_increment')) {
714 // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
715 if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array
::value('DATA_TYPE', $logTableSpecs[$col])
716 // We won't alter the log if the length is decreased in case some of the existing data won't fit.
717 || CRM_Utils_Array
::value('LENGTH', $civiTableSpecs[$col]) > CRM_Utils_Array
::value('LENGTH', $logTableSpecs[$col])
719 // if data-type is different, surely consider the column
720 $diff['MODIFY'][] = $col;
722 elseif ($civiTableSpecs[$col]['DATA_TYPE'] === 'enum' &&
723 CRM_Utils_Array
::value('ENUM_VALUES', $civiTableSpecs[$col]) != CRM_Utils_Array
::value('ENUM_VALUES', $logTableSpecs[$col])
725 // column is enum and the permitted values have changed
726 $diff['MODIFY'][] = $col;
728 elseif ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array
::value('IS_NULLABLE', $logTableSpecs[$col]) &&
729 $logTableSpecs[$col]['IS_NULLABLE'] === 'NO'
731 // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
732 $diff['MODIFY'][] = $col;
735 $civiTableSpecs[$col]['COLUMN_DEFAULT'] != ($logTableSpecs[$col]['COLUMN_DEFAULT'] ??
NULL)
736 && !stristr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'timestamp')
737 && !($civiTableSpecs[$col]['COLUMN_DEFAULT'] === NULL && ($logTableSpecs[$col]['COLUMN_DEFAULT'] ??
NULL) === 'NULL')
739 // if default property is different, and its not about a timestamp column, consider it
740 $diff['MODIFY'][] = $col;
746 // columns to made obsolete by turning into not-null
747 $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
748 foreach ($oldCols as $col) {
749 if (!in_array($col, ['log_date', 'log_conn_id', 'log_user_id', 'log_action']) &&
750 $logTableSpecs[$col]['IS_NULLABLE'] === 'NO'
751 // This could be to support replication - https://lab.civicrm.org/dev/core/-/issues/2120
752 && $logTableSpecs[$col]['EXTRA'] !== 'auto_increment'
754 // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
755 $diff['OBSOLETE'][] = $col;
763 * Getter for logTableSpec.
767 public function getLogTableSpec() {
768 return $this->logTableSpec
;
772 * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
774 * @param string $table
776 private function createLogTableFor($table) {
777 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE $table", [], TRUE, NULL, FALSE, FALSE);
779 $query = $dao->Create_Table
;
781 // rewrite the queries into CREATE TABLE queries for log tables:
784 log_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
785 log_conn_id VARCHAR(17),
787 log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
790 if (!empty($this->logTableSpec
[$table]['indexes'])) {
791 foreach ($this->logTableSpec
[$table]['indexes'] as $indexName => $indexSpec) {
792 if (is_array($indexSpec)) {
793 $indexSpec = implode(" , ", $indexSpec);
795 $cols .= ", INDEX {$indexName}($indexSpec)";
799 // - prepend the name with log_
800 // - drop AUTO_INCREMENT columns
801 // - drop non-column rows of the query (keys, constraints, etc.)
802 // - set the ENGINE to the specified engine (default is INNODB)
803 // - add log-specific columns (at the end of the table)
804 $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
805 $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
806 $query = preg_replace("/^ [^`].*$/m", '', $query);
807 $engine = strtoupper(empty($this->logTableSpec
[$table]['engine']) ? self
::ENGINE
: $this->logTableSpec
[$table]['engine']);
808 $engine .= " " . ($this->logTableSpec
[$table]['engine_config'] ??
'');
809 if (strpos($engine, 'ROW_FORMAT') !== FALSE) {
810 $query = preg_replace("/ROW_FORMAT=\w+/m", '', $query);
812 $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=' . $engine . ' ', $query);
814 // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
815 // so there's no need for a default timestamp and therefore we remove such default timestamps
816 // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
817 $query = self
::fixTimeStampAndNotNullSQL($query);
818 $query = preg_replace("/(,*\n*\) )ENGINE/m", "$cols\n) ENGINE", $query);
820 CRM_Core_DAO
::executeQuery($query, [], TRUE, NULL, FALSE, FALSE);
822 $columns = implode(', ', $this->columnsOf($table));
823 CRM_Core_DAO
::executeQuery("INSERT INTO `{$this->db}`.log_$table ($columns, log_conn_id, log_user_id, log_action) SELECT $columns, @uniqueID, @civicrm_user_id, 'Initialization' FROM {$table}", [], TRUE, NULL, FALSE, FALSE);
825 $this->tables
[] = $table;
826 if (empty($this->logs
)) {
827 civicrm_api3('Setting', 'create', ['logging_uniqueid_date' => date('Y-m-d H:i:s')]);
828 civicrm_api3('Setting', 'create', ['logging_all_tables_uniquid' => 1]);
830 $this->logs
[$table] = "log_$table";
836 private function deleteReports() {
837 // disable logging templates
838 CRM_Core_DAO
::executeQuery("
839 UPDATE civicrm_option_value
841 WHERE value IN ('" . implode("', '", $this->reports
) . "')
844 // delete report instances
845 $domain_id = CRM_Core_Config
::domainID();
846 foreach ($this->reports
as $report) {
847 $dao = new CRM_Report_DAO_ReportInstance();
848 $dao->domain_id
= $domain_id;
849 $dao->report_id
= $report;
855 * Predicate whether logging is enabled.
857 public function isEnabled() {
858 if (\Civi
::settings()->get('logging')) {
859 return ($this->tablesExist() && (\Civi
::settings()->get('logging_no_trigger_permission') ||
$this->triggersExist()));
865 * Predicate whether any log tables exist.
867 private function tablesExist() {
868 return !empty($this->logs
);
872 * Drop all log tables.
874 * This does not currently have a usage outside the tests.
876 public function dropAllLogTables() {
877 if ($this->tablesExist()) {
878 foreach ($this->logs
as $log_table) {
879 CRM_Core_DAO
::executeQuery("DROP TABLE $log_table");
885 * Get an sql clause to find the names of any log tables that do not match the normal pattern.
887 * Most tables are civicrm_xxx with the log table being log_civicrm_xxx
888 * However, they don't have to match this pattern (e.g when defined by hook) so find the
889 * anomalies and return a filter string to include them.
893 public function getNonStandardTableNameFilterString() {
894 $nonStandardTableNames = preg_grep('/^civicrm_/', $this->tables
, PREG_GREP_INVERT
);
895 if (empty($nonStandardTableNames)) {
898 $nonStandardTableLogs = [];
899 foreach ($nonStandardTableNames as $nonStandardTableName) {
900 $nonStandardTableLogs[] = "'log_{$nonStandardTableName}'";
902 return " OR TABLE_NAME IN (" . implode(',', $nonStandardTableLogs) . ")";
906 * Predicate whether the logging triggers are in place.
908 private function triggersExist() {
909 // FIXME: probably should be a bit more thorough…
910 // note that the LIKE parameter is TABLE NAME
911 return (bool) CRM_Core_DAO
::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
918 * @param null $tableName
921 public function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
922 if (!CRM_Core_Config
::singleton()->logging
) {
926 $insert = ['INSERT'];
927 $update = ['UPDATE'];
928 $delete = ['DELETE'];
931 $tableNames = [$tableName];
934 $tableNames = $this->tables
;
937 // logging is enabled, so now lets create the trigger info tables
938 foreach ($tableNames as $table) {
939 if (!isset($this->logTableSpec
[$table])) {
940 // Per testIgnoreCustomTableByHook this would be unset if a hook had
941 // intervened to prevent logging / triggers on this table.
942 // This could go to the extent of blocking the updates to 'modified_date'
943 // which makes sense, in particular, for calculated fields.
946 $columns = $this->columnsOf($table, $force);
948 // only do the change if any data has changed
950 foreach ($columns as $column) {
951 $tableExceptions = array_key_exists('exceptions', $this->logTableSpec
[$table]) ?
$this->logTableSpec
[$table]['exceptions'] : [];
952 // ignore modified_date changes
953 $tableExceptions[] = 'modified_date';
954 // exceptions may be provided with or without backticks
955 $excludeColumn = in_array($column, $tableExceptions) ||
956 in_array(str_replace('`', '', $column), $tableExceptions);
957 if (!$excludeColumn) {
958 $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
961 $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
962 $updateSQL = "IF ( (" . implode(' OR ', $cond) . ") AND ( $suppressLoggingCond ) ) THEN ";
964 if ($this->useDBPrefix
) {
965 $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
968 $sqlStmt = "INSERT INTO log_{tableName} (";
970 foreach ($columns as $column) {
971 $sqlStmt .= "$column, ";
973 $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
975 $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
976 $updateSQL .= $sqlStmt;
979 foreach ($columns as $column) {
980 $sqlStmt .= "NEW.$column, ";
981 $deleteSQL .= "OLD.$column, ";
983 if (civicrm_api3('Setting', 'getvalue', ['name' => 'logging_uniqueid_date'])) {
984 // Note that when connecting directly via mysql @uniqueID may not be set so a fallback is
985 // 'c_' to identify a non-CRM connection + timestamp to the hour + connection_id
986 // If the connection_id is longer than 6 chars it will be truncated.
987 // We tried setting the @uniqueID in the trigger but it was unreliable.
988 // An external interaction could split over 2 connections & it seems worth blocking the revert on
989 // these reports & adding extra permissioning to the api for this.
990 $connectionSQLString = "COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17))";
993 // The log tables have not yet been converted to have varchar(17) fields for log_conn_id.
994 // Continue to use the less reliable connection_id for al tables for now.
995 $connectionSQLString = "CONNECTION_ID()";
997 $sqlStmt .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
998 $deleteSQL .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
1000 $insertSQL .= $sqlStmt;
1001 $updateSQL .= $sqlStmt;
1004 'table' => [$table],
1007 'sql' => $insertSQL,
1011 'table' => [$table],
1014 'sql' => $updateSQL,
1018 'table' => [$table],
1021 'sql' => $deleteSQL,
1027 * Disable logging temporarily.
1029 * This allow logging to be temporarily disabled for certain cases
1030 * where we want to do a mass cleanup but do not want to bother with
1033 public static function disableLoggingForThisConnection() {
1034 if (CRM_Core_Config
::singleton()->logging
) {
1035 CRM_Core_DAO
::executeQuery('SET @civicrm_disable_logging = 1');
1040 * Get all the log tables that reference civicrm_contact.
1042 * Note that it might make sense to wrap this in a getLogTablesForEntity
1043 * but this is the only entity currently available...
1045 public function getLogTablesForContact() {
1046 $tables = array_keys(CRM_Core_DAO
::getReferencesToContactTable());
1047 // This additional hardcoding has been moved from getReferencesToContactTable
1048 // to here as it is not needed in the other place where the function is called.
1049 // It may not be needed here either...
1050 $tables[] = 'civicrm_entity_tag';
1051 return array_intersect($tables, $this->tables
);
1055 * Retrieve missing log tables.
1059 public function getMissingLogTables() {
1060 if ($this->tablesExist()) {
1061 return array_diff($this->tables
, array_keys($this->logs
));
1067 * Get the name of the database from the dsn string.
1069 * @param string $dsnString
1073 protected function getDatabaseNameFromDSN($dsnString): string {
1074 $dsn = CRM_Utils_SQL
::autoSwitchDSN($dsnString);
1075 $dsn = DB
::parseDSN($dsn);
1076 return $dsn['database'];
1080 * Get the database name for the CiviCRM connection.
1082 * Note that we want to get it from the database connection,
1083 * not the dsn, because there is at least one extension
1084 * (https://github.com/totten/rpow) that 'meddles' with
1089 protected function getCiviCRMDatabaseName(): string {
1090 return (new CRM_Contact_DAO_Contact())->_database
;