3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2019
33 class CRM_Logging_Schema
{
36 * Default storage engine for log tables
40 const ENGINE
= 'InnoDB';
46 private $useDBPrefix = TRUE;
49 'logging/contact/detail',
50 'logging/contact/summary',
51 'logging/contribute/detail',
52 'logging/contribute/summary',
56 * Columns that should never be subject to logging.
58 * CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
62 private $exceptions = [
63 'civicrm_job' => ['last_run'],
64 'civicrm_group' => ['cache_date', 'refresh_date'],
68 * Specifications of all log table including
69 * - engine (default is InnoDB, if not set.)
70 * - engine_config, a string appended to the engine type.
71 * For INNODB space can be saved with 'ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4'
72 * - indexes (default is none and they cannot be added unless engine is innodb. If they are added and
73 * engine is not set to innodb an exception will be thrown since quiet acquiescence is easier to miss).
74 * - exceptions (by default those stored in $this->exceptions are included). These are
75 * excluded from the triggers.
79 private $logTableSpec = [];
82 * Setting Callback - Validate.
85 * @param array $fieldSpec
88 * @throws API_Exception
90 public static function checkLoggingSupport(&$value, $fieldSpec) {
91 $domain = new CRM_Core_DAO_Domain();
93 if (!(CRM_Core_DAO
::checkTriggerViewPermission(FALSE)) && $value) {
94 throw new API_Exception("In order to use this functionality, the installation's database user must have privileges to create triggers (in MySQL 5.0 – and in MySQL 5.1 if binary logging is enabled – this means the SUPER privilege). This install either does not seem to have the required privilege enabled.");
100 * Setting Callback - On Change.
102 * Respond to changes in the "logging" setting. Set up or destroy
105 * @param array $oldValue
106 * List of component names.
107 * @param array $newValue
108 * List of component names.
109 * @param array $metadata
110 * Specification of the setting (per *.settings.php).
112 public static function onToggle($oldValue, $newValue, $metadata) {
113 if ($oldValue == $newValue) {
117 $logging = new CRM_Logging_Schema();
119 $logging->enableLogging();
122 $logging->disableLogging();
127 * Populate $this->tables and $this->logs with current db state.
129 public function __construct() {
130 $dao = new CRM_Contact_DAO_Contact();
131 $civiDBName = $dao->_database
;
133 $dao = CRM_Core_DAO
::executeQuery("
135 FROM INFORMATION_SCHEMA.TABLES
136 WHERE TABLE_SCHEMA = '{$civiDBName}'
137 AND TABLE_TYPE = 'BASE TABLE'
138 AND TABLE_NAME LIKE 'civicrm_%'
140 while ($dao->fetch()) {
141 $this->tables
[] = $dao->TABLE_NAME
;
144 // do not log temp import, cache, menu and log tables
145 $this->tables
= preg_grep('/^civicrm_import_job_/', $this->tables
, PREG_GREP_INVERT
);
146 $this->tables
= preg_grep('/_cache$/', $this->tables
, PREG_GREP_INVERT
);
147 $this->tables
= preg_grep('/_log/', $this->tables
, PREG_GREP_INVERT
);
148 $this->tables
= preg_grep('/^civicrm_queue_/', $this->tables
, PREG_GREP_INVERT
);
150 $this->tables
= preg_grep('/^civicrm_menu/', $this->tables
, PREG_GREP_INVERT
);
151 $this->tables
= preg_grep('/_temp_/', $this->tables
, PREG_GREP_INVERT
);
153 $this->tables
= preg_grep('/_bak$/', $this->tables
, PREG_GREP_INVERT
);
154 $this->tables
= preg_grep('/_backup$/', $this->tables
, PREG_GREP_INVERT
);
156 $this->tables
= preg_grep('/^civicrm_tmp_/', $this->tables
, PREG_GREP_INVERT
);
158 // do not log civicrm_mailing_event* tables, CRM-12300
159 $this->tables
= preg_grep('/^civicrm_mailing_event_/', $this->tables
, PREG_GREP_INVERT
);
161 // do not log civicrm_mailing_recipients table, CRM-16193
162 $this->tables
= array_diff($this->tables
, ['civicrm_mailing_recipients']);
163 $this->logTableSpec
= array_fill_keys($this->tables
, []);
164 foreach ($this->exceptions
as $tableName => $fields) {
165 $this->logTableSpec
[$tableName]['exceptions'] = $fields;
167 CRM_Utils_Hook
::alterLogTables($this->logTableSpec
);
168 $this->tables
= array_keys($this->logTableSpec
);
169 $nonStandardTableNameString = $this->getNonStandardTableNameFilterString();
171 if (defined('CIVICRM_LOGGING_DSN')) {
172 $dsn = DB
::parseDSN(CIVICRM_LOGGING_DSN
);
173 $this->useDBPrefix
= (CIVICRM_LOGGING_DSN
!= CIVICRM_DSN
);
176 $dsn = DB
::parseDSN(CIVICRM_DSN
);
177 $this->useDBPrefix
= FALSE;
179 $this->db
= $dsn['database'];
181 $dao = CRM_Core_DAO
::executeQuery("
183 FROM INFORMATION_SCHEMA.TABLES
184 WHERE TABLE_SCHEMA = '{$this->db}'
185 AND TABLE_TYPE = 'BASE TABLE'
186 AND (TABLE_NAME LIKE 'log_civicrm_%' $nonStandardTableNameString )
188 while ($dao->fetch()) {
189 $log = $dao->TABLE_NAME
;
190 $this->logs
[substr($log, 4)] = $log;
195 * Return logging custom data tables.
197 public function customDataLogTables() {
198 return preg_grep('/^log_civicrm_value_/', $this->logs
);
202 * Return custom data tables for specified entity / extends.
204 * @param string $extends
208 public function entityCustomDataLogTables($extends) {
209 $customGroupTables = [];
210 $customGroupDAO = CRM_Core_BAO_CustomGroup
::getAllCustomGroupsByBaseEntity($extends);
211 $customGroupDAO->find();
212 while ($customGroupDAO->fetch()) {
213 // logging is disabled for the table (e.g by hook) then $this->logs[$customGroupDAO->table_name]
215 if (!empty($this->logs
[$customGroupDAO->table_name
])) {
216 $customGroupTables[$customGroupDAO->table_name
] = $this->logs
[$customGroupDAO->table_name
];
219 return $customGroupTables;
223 * Disable logging by dropping the triggers (but keep the log tables intact).
225 public function disableLogging() {
226 $config = CRM_Core_Config
::singleton();
227 $config->logging
= FALSE;
229 $this->dropTriggers();
231 // invoke the meta trigger creation call
232 CRM_Core_DAO
::triggerRebuild();
234 $this->deleteReports();
238 * Drop triggers for all logged tables.
240 * @param string $tableName
242 public function dropTriggers($tableName = NULL) {
243 /** @var \Civi\Core\SqlTriggers $sqlTriggers */
244 $sqlTriggers = Civi
::service('sql_triggers');
245 $dao = new CRM_Core_DAO();
248 $tableNames = [$tableName];
251 $tableNames = $this->tables
;
254 foreach ($tableNames as $table) {
255 $validName = CRM_Core_DAO
::shortenSQLName($table, 48, TRUE);
258 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
259 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
260 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
263 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
264 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
265 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
268 // now lets also be safe and drop all triggers that start with
269 // civicrm_ if we are dropping all triggers
270 // we need to do this to capture all the leftover triggers since
271 // we did the shortening trigger name for CRM-11794
272 if ($tableName === NULL) {
273 $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
275 while ($triggers->fetch()) {
276 $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
282 * Enable site-wide logging.
284 public function enableLogging() {
285 $this->fixSchemaDifferences(TRUE);
290 * Sync log tables and rebuild triggers.
292 * @param bool $enableLogging : Ensure logging is enabled
294 public function fixSchemaDifferences($enableLogging = FALSE) {
295 $config = CRM_Core_Config
::singleton();
296 if ($enableLogging) {
297 $config->logging
= TRUE;
299 if ($config->logging
) {
300 $this->fixSchemaDifferencesForALL();
302 // invoke the meta trigger creation call
303 CRM_Core_DAO
::triggerRebuild(NULL, TRUE);
307 * Update log tables structure.
309 * This function updates log tables to have the log_conn_id type of varchar
310 * and also implements the engine change defined by the hook (i.e. INNODB).
312 * Note changing engine & adding hook-defined indexes, but not changing back
313 * to INNODB if engine has not been deliberately set (by hook) and not
314 * dropping indexes. Sysadmin will need to manually intervene to revert to
317 * @param array $params
318 * 'updateChangedEngineConfig' - update if the engine config changes?
319 * 'forceEngineMigration' - force engine upgrade from ARCHIVE to InnoDB?
321 * @return int $updateTablesCount
322 * @throws \CiviCRM_API3_Exception
324 public function updateLogTableSchema($params) {
325 $updateLogConn = FALSE;
326 $updatedTablesCount = 0;
327 foreach ($this->logs
as $mainTable => $logTable) {
329 $tableSpec = $this->logTableSpec
[$mainTable];
330 $currentEngine = strtoupper($this->getEngineForLogTable($logTable));
331 if (!isset($tableSpec['engine']) && $currentEngine == 'ARCHIVE' && $params['forceEngineMigration']) {
332 // table uses ARCHIVE engine (the previous default) and no one set an
333 // alternative engine via hook_civicrm_alterLogTables => force change to
335 $tableSpec['engine'] = self
::ENGINE
;
337 $engineChanged = isset($tableSpec['engine']) && (strtoupper($tableSpec['engine']) != $currentEngine);
338 $engineConfigChanged = isset($tableSpec['engine_config']) && (strtoupper($tableSpec['engine_config']) != $this->getEngineConfigForLogTable($logTable));
339 if ($engineChanged ||
($engineConfigChanged && $params['updateChangedEngineConfig'])) {
340 $alterSql[] = "ENGINE=" . $tableSpec['engine'] . " " . CRM_Utils_Array
::value('engine_config', $tableSpec);
342 if (!empty($tableSpec['indexes'])) {
343 $indexes = $this->getIndexesForTable($logTable);
344 foreach ($tableSpec['indexes'] as $indexName => $indexSpec) {
345 if (!in_array($indexName, $indexes)) {
346 if (is_array($indexSpec)) {
347 $indexSpec = implode(" , ", $indexSpec);
349 $alterSql[] = "ADD INDEX {$indexName}($indexSpec)";
353 $columns = $this->columnSpecsOf($logTable);
354 if (empty($columns['log_conn_id'])) {
355 throw new Exception($logTable . print_r($columns, TRUE));
357 if ($columns['log_conn_id']['DATA_TYPE'] != 'varchar' ||
$columns['log_conn_id']['LENGTH'] != 17) {
358 $alterSql[] = "MODIFY log_conn_id VARCHAR(17)";
359 $updateLogConn = TRUE;
361 if (!empty($alterSql)) {
362 CRM_Core_DAO
::executeQuery("ALTER TABLE {$this->db}.{$logTable} " . implode(', ', $alterSql), [], TRUE, NULL, FALSE, FALSE);
363 $updatedTablesCount++
;
366 if ($updateLogConn) {
367 civicrm_api3('Setting', 'create', ['logging_uniqueid_date' => date('Y-m-d H:i:s')]);
369 return $updatedTablesCount;
373 * Get the engine for the given table.
375 * @param string $table
379 public function getEngineForLogTable($table) {
380 return strtoupper(CRM_Core_DAO
::singleValueQuery("
381 SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = %1
382 AND table_schema = %2
383 ", [1 => [$table, 'String'], 2 => [$this->db
, 'String']]));
387 * Get the engine config for the given table.
389 * @param string $table
393 public function getEngineConfigForLogTable($table) {
394 return strtoupper(CRM_Core_DAO
::singleValueQuery("
395 SELECT CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME = %1
396 AND table_schema = %2
397 ", [1 => [$table, 'String'], 2 => [$this->db
, 'String']]));
401 * Get all the indexes in the table.
403 * @param string $table
407 public function getIndexesForTable($table) {
409 $result = CRM_Core_DAO
::executeQuery("
410 SELECT constraint_name AS index_name
411 FROM information_schema.key_column_usage
412 WHERE table_schema = %2 AND table_name = %1
414 SELECT index_name AS index_name
415 FROM information_schema.statistics
416 WHERE table_schema = %2 AND table_name = %1
418 [1 => [$table, 'String'], 2 => [$this->db
, 'String']]
420 while ($result->fetch()) {
421 $indexes[] = $result->index_name
;
427 * Add missing (potentially specified) log table columns for the given table.
429 * @param string $table
430 * name of the relevant table.
432 * Mixed array of columns to add or null (to check for the missing columns).
436 public function fixSchemaDifferencesFor($table, $cols = []) {
440 if (empty($this->logs
[$table])) {
441 $this->createLogTableFor($table);
446 $cols = $this->columnsWithDiffSpecs($table, "log_$table");
449 // If a column that already exists on logging table is being added, we
450 // should treat it as a modification.
451 $this->resetSchemaCacheForTable("log_$table");
452 $logTableSchema = $this->columnSpecsOf("log_$table");
453 foreach ($cols['ADD'] as $colKey => $col) {
454 if (array_key_exists($col, $logTableSchema)) {
455 $cols['MODIFY'][] = $col;
456 unset($cols['ADD'][$colKey]);
460 // use the relevant lines from CREATE TABLE to add colums to the log table
461 $create = $this->_getCreateQuery($table);
462 foreach ((['ADD', 'MODIFY']) as $alterType) {
463 if (!empty($cols[$alterType])) {
464 foreach ($cols[$alterType] as $col) {
465 $line = $this->_getColumnQuery($col, $create);
466 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}", [], TRUE, NULL, FALSE, FALSE);
471 // for any obsolete columns (not null) we just make the column nullable.
472 if (!empty($cols['OBSOLETE'])) {
473 $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
474 foreach ($cols['OBSOLETE'] as $col) {
475 $line = $this->_getColumnQuery($col, $create);
476 // This is just going to make a not null column to nullable
477 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}", [], TRUE, NULL, FALSE, FALSE);
481 $this->resetSchemaCacheForTable("log_$table");
487 * Resets schema cache for the given table.
489 * @param string $table
492 private function resetSchemaCacheForTable($table) {
493 unset(\Civi
::$statics[__CLASS__
]['columnSpecs'][$table]);
499 * @param string $table
503 private function _getCreateQuery($table) {
504 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE {$table}", [], TRUE, NULL, FALSE, FALSE);
506 $create = explode("\n", $dao->Create_Table
);
514 * @param bool $createQuery
516 * @return array|mixed|string
518 private function _getColumnQuery($col, $createQuery) {
519 $line = preg_grep("/^ `$col` /", $createQuery);
520 $line = rtrim(array_pop($line), ',');
522 $line = self
::fixTimeStampAndNotNullSQL($line);
527 * Fix schema differences.
529 * @param bool $rebuildTrigger
531 public function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
533 $this->resetTableColumnsCache();
535 foreach ($this->tables
as $table) {
536 if (empty($this->logs
[$table])) {
537 $this->createLogTableFor($table);
540 $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
544 foreach ($diffs as $table => $cols) {
545 $this->fixSchemaDifferencesFor($table, $cols);
547 if ($rebuildTrigger) {
548 // invoke the meta trigger creation call
549 CRM_Core_DAO
::triggerRebuild(NULL, TRUE);
554 * Resets columnSpecs.
556 * Resets columnSpecs static array in Civi's $statics to make sure we use the
557 * real state of the schema to perform sync operations between core and
560 private function resetTableColumnsCache() {
561 unset(\Civi
::$statics[__CLASS__
]['columnSpecs']);
567 * Log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
568 * so there's no need for a default timestamp and therefore we remove such default timestamps
569 * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
571 * @param string $query
575 public static function fixTimeStampAndNotNullSQL($query) {
576 $query = str_ireplace("TIMESTAMP() NOT NULL", "TIMESTAMP NULL", $query);
577 $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
578 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()", '', $query);
579 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
580 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP()", '', $query);
581 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
582 $query = str_ireplace("NOT NULL", '', $query);
589 private function addReports() {
591 'logging/contact/detail' => ts('Logging Details'),
592 'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
593 'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
594 'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
596 // enable logging templates
597 CRM_Core_DAO
::executeQuery("
598 UPDATE civicrm_option_value
600 WHERE value IN ('" . implode("', '", $this->reports
) . "')
603 // add report instances
604 $domain_id = CRM_Core_Config
::domainID();
605 foreach ($this->reports
as $report) {
606 $dao = new CRM_Report_DAO_ReportInstance();
607 $dao->domain_id
= $domain_id;
608 $dao->report_id
= $report;
609 $dao->title
= $titles[$report];
610 $dao->permission
= 'administer CiviCRM';
611 if ($report == 'logging/contact/summary') {
612 $dao->is_reserved
= 1;
619 * Get an array of column names of the given table.
621 * @param string $table
626 private function columnsOf($table, $force = FALSE) {
627 if ($force ||
!isset(\Civi
::$statics[__CLASS__
]['columnsOf'][$table])) {
628 $from = (substr($table, 0, 4) == 'log_') ?
"`{$this->db}`.$table" : $table;
629 CRM_Core_TemporaryErrorScope
::ignoreException();
630 $dao = CRM_Core_DAO
::executeQuery("SHOW COLUMNS FROM $from", [], TRUE, NULL, FALSE, FALSE);
631 if (is_a($dao, 'DB_Error')) {
634 \Civi
::$statics[__CLASS__
]['columnsOf'][$table] = [];
635 while ($dao->fetch()) {
636 \Civi
::$statics[__CLASS__
]['columnsOf'][$table][] = CRM_Utils_Type
::escape($dao->Field
, 'MysqlColumnNameOrAlias');
639 return \Civi
::$statics[__CLASS__
]['columnsOf'][$table];
643 * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
645 * @param string $table
649 private function columnSpecsOf($table) {
650 static $civiDB = NULL;
651 if (empty(\Civi
::$statics[__CLASS__
]['columnSpecs'])) {
652 \Civi
::$statics[__CLASS__
]['columnSpecs'] = [];
654 if (empty(\Civi
::$statics[__CLASS__
]['columnSpecs']) ||
!isset(\Civi
::$statics[__CLASS__
]['columnSpecs'][$table])) {
656 $dao = new CRM_Contact_DAO_Contact();
657 $civiDB = $dao->_database
;
659 CRM_Core_TemporaryErrorScope
::ignoreException();
660 // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
661 // than firing query for every given table.
663 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, EXTRA
664 FROM INFORMATION_SCHEMA.COLUMNS
665 WHERE table_schema IN ('{$this->db}', '{$civiDB}')";
666 $dao = CRM_Core_DAO
::executeQuery($query);
667 if (is_a($dao, 'DB_Error')) {
670 while ($dao->fetch()) {
671 if (!array_key_exists($dao->TABLE_NAME
, \Civi
::$statics[__CLASS__
]['columnSpecs'])) {
672 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
] = [];
674 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
] = [
675 'COLUMN_NAME' => $dao->COLUMN_NAME
,
676 'DATA_TYPE' => $dao->DATA_TYPE
,
677 'IS_NULLABLE' => $dao->IS_NULLABLE
,
678 'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT
,
679 'EXTRA' => $dao->EXTRA
,
681 if (($first = strpos($dao->COLUMN_TYPE
, '(')) != 0) {
682 // this extracts the value between parentheses after the column type.
683 // it could be the column length, i.e. "int(8)", "decimal(20,2)")
684 // or the permitted values of an enum (e.g. "enum('A','B')")
686 $dao->COLUMN_TYPE
, $first +
1, strpos($dao->COLUMN_TYPE
, ')') - $first - 1
688 if (strpos($parValue, "'") === FALSE) {
689 // no quote in value means column length
690 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
]['LENGTH'] = $parValue;
693 // single quote means enum permitted values
694 \Civi
::$statics[__CLASS__
]['columnSpecs'][$dao->TABLE_NAME
][$dao->COLUMN_NAME
]['ENUM_VALUES'] = $parValue;
699 return \Civi
::$statics[__CLASS__
]['columnSpecs'][$table];
703 * Get columns that have changed.
705 * @param string $civiTable
706 * @param string $logTable
710 public function columnsWithDiffSpecs($civiTable, $logTable) {
711 $civiTableSpecs = $this->columnSpecsOf($civiTable);
712 $logTableSpecs = $this->columnSpecsOf($logTable);
714 $diff = ['ADD' => [], 'MODIFY' => [], 'OBSOLETE' => []];
715 // columns to be added
716 $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
717 // columns to be modified
718 // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
719 // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
720 foreach ($civiTableSpecs as $col => $colSpecs) {
721 if (!isset($logTableSpecs[$col]) ||
!is_array($logTableSpecs[$col])) {
722 $logTableSpecs[$col] = [];
724 $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
725 if (!empty($specDiff) && $col != 'id' && !in_array($col, $diff['ADD'])) {
726 if (empty($colSpecs['EXTRA']) ||
(!empty($colSpecs['EXTRA']) && $colSpecs['EXTRA'] !== 'auto_increment')) {
727 // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
728 if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array
::value('DATA_TYPE', $logTableSpecs[$col])
729 // We won't alter the log if the length is decreased in case some of the existing data won't fit.
730 || CRM_Utils_Array
::value('LENGTH', $civiTableSpecs[$col]) > CRM_Utils_Array
::value('LENGTH', $logTableSpecs[$col])
732 // if data-type is different, surely consider the column
733 $diff['MODIFY'][] = $col;
735 elseif ($civiTableSpecs[$col]['DATA_TYPE'] == 'enum' &&
736 CRM_Utils_Array
::value('ENUM_VALUES', $civiTableSpecs[$col]) != CRM_Utils_Array
::value('ENUM_VALUES', $logTableSpecs[$col])
738 // column is enum and the permitted values have changed
739 $diff['MODIFY'][] = $col;
741 elseif ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array
::value('IS_NULLABLE', $logTableSpecs[$col]) &&
742 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
744 // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
745 $diff['MODIFY'][] = $col;
747 elseif ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array
::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
748 !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')
750 // if default property is different, and its not about a timestamp column, consider it
751 $diff['MODIFY'][] = $col;
757 // columns to made obsolete by turning into not-null
758 $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
759 foreach ($oldCols as $col) {
760 if (!in_array($col, ['log_date', 'log_conn_id', 'log_user_id', 'log_action']) &&
761 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
763 // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
764 $diff['OBSOLETE'][] = $col;
772 * Getter for logTableSpec.
776 public function getLogTableSpec() {
777 return $this->logTableSpec
;
781 * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
783 * @param string $table
785 private function createLogTableFor($table) {
786 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE $table", [], TRUE, NULL, FALSE, FALSE);
788 $query = $dao->Create_Table
;
790 // rewrite the queries into CREATE TABLE queries for log tables:
793 log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
794 log_conn_id VARCHAR(17),
796 log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
799 if (!empty($this->logTableSpec
[$table]['indexes'])) {
800 foreach ($this->logTableSpec
[$table]['indexes'] as $indexName => $indexSpec) {
801 if (is_array($indexSpec)) {
802 $indexSpec = implode(" , ", $indexSpec);
804 $cols .= ", INDEX {$indexName}($indexSpec)";
808 // - prepend the name with log_
809 // - drop AUTO_INCREMENT columns
810 // - drop non-column rows of the query (keys, constraints, etc.)
811 // - set the ENGINE to the specified engine (default is INNODB)
812 // - add log-specific columns (at the end of the table)
814 $engines = CRM_Core_DAO
::executeQuery("SHOW ENGINES");
815 while ($engines->fetch()) {
816 if ($engines->Support
== 'YES' ||
$engines->Support
== 'DEFAULT') {
817 $mysqlEngines[] = $engines->Engine
;
820 $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
821 $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
822 $query = preg_replace("/^ [^`].*$/m", '', $query);
823 $engine = strtoupper(CRM_Utils_Array
::value('engine', $this->logTableSpec
[$table], self
::ENGINE
));
824 $engine .= " " . CRM_Utils_Array
::value('engine_config', $this->logTableSpec
[$table]);
825 $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=' . $engine . ' ', $query);
827 // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
828 // so there's no need for a default timestamp and therefore we remove such default timestamps
829 // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
830 $query = self
::fixTimeStampAndNotNullSQL($query);
831 $query = preg_replace("/(,*\n*\) )ENGINE/m", "$cols\n) ENGINE", $query);
833 CRM_Core_DAO
::executeQuery($query, [], TRUE, NULL, FALSE, FALSE);
835 $columns = implode(', ', $this->columnsOf($table));
836 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);
838 $this->tables
[] = $table;
839 if (empty($this->logs
)) {
840 civicrm_api3('Setting', 'create', ['logging_uniqueid_date' => date('Y-m-d H:i:s')]);
841 civicrm_api3('Setting', 'create', ['logging_all_tables_uniquid' => 1]);
843 $this->logs
[$table] = "log_$table";
849 private function deleteReports() {
850 // disable logging templates
851 CRM_Core_DAO
::executeQuery("
852 UPDATE civicrm_option_value
854 WHERE value IN ('" . implode("', '", $this->reports
) . "')
857 // delete report instances
858 $domain_id = CRM_Core_Config
::domainID();
859 foreach ($this->reports
as $report) {
860 $dao = new CRM_Report_DAO_ReportInstance();
861 $dao->domain_id
= $domain_id;
862 $dao->report_id
= $report;
868 * Predicate whether logging is enabled.
870 public function isEnabled() {
871 if (\Civi
::settings()->get('logging')) {
872 return ($this->tablesExist() && (\Civi
::settings()->get('logging_no_trigger_permission') ||
$this->triggersExist()));
878 * Predicate whether any log tables exist.
880 private function tablesExist() {
881 return !empty($this->logs
);
885 * Drop all log tables.
887 * This does not currently have a usage outside the tests.
889 public function dropAllLogTables() {
890 if ($this->tablesExist()) {
891 foreach ($this->logs
as $log_table) {
892 CRM_Core_DAO
::executeQuery("DROP TABLE $log_table");
898 * Get an sql clause to find the names of any log tables that do not match the normal pattern.
900 * Most tables are civicrm_xxx with the log table being log_civicrm_xxx
901 * However, they don't have to match this pattern (e.g when defined by hook) so find the
902 * anomalies and return a filter string to include them.
906 public function getNonStandardTableNameFilterString() {
907 $nonStandardTableNames = preg_grep('/^civicrm_/', $this->tables
, PREG_GREP_INVERT
);
908 if (empty($nonStandardTableNames)) {
911 $nonStandardTableLogs = [];
912 foreach ($nonStandardTableNames as $nonStandardTableName) {
913 $nonStandardTableLogs[] = "'log_{$nonStandardTableName}'";
915 return " OR TABLE_NAME IN (" . implode(',', $nonStandardTableLogs) . ")";
919 * Predicate whether the logging triggers are in place.
921 private function triggersExist() {
922 // FIXME: probably should be a bit more thorough…
923 // note that the LIKE parameter is TABLE NAME
924 return (bool) CRM_Core_DAO
::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
931 * @param null $tableName
934 public function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
935 if (!CRM_Core_Config
::singleton()->logging
) {
939 $insert = ['INSERT'];
940 $update = ['UPDATE'];
941 $delete = ['DELETE'];
944 $tableNames = [$tableName];
947 $tableNames = $this->tables
;
950 // logging is enabled, so now lets create the trigger info tables
951 foreach ($tableNames as $table) {
952 if (!isset($this->logTableSpec
[$table])) {
953 // Per testIgnoreCustomTableByHook this would be unset if a hook had
954 // intervened to prevent logging / triggers on this table.
955 // This could go to the extent of blocking the updates to 'modified_date'
956 // which makes sense, in particular, for calculated fields.
959 $columns = $this->columnsOf($table, $force);
961 // only do the change if any data has changed
963 foreach ($columns as $column) {
964 $tableExceptions = array_key_exists('exceptions', $this->logTableSpec
[$table]) ?
$this->logTableSpec
[$table]['exceptions'] : [];
965 // ignore modified_date changes
966 $tableExceptions[] = 'modified_date';
967 // exceptions may be provided with or without backticks
968 $excludeColumn = in_array($column, $tableExceptions) ||
969 in_array(str_replace('`', '', $column), $tableExceptions);
970 if (!$excludeColumn) {
971 $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
974 $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
975 $updateSQL = "IF ( (" . implode(' OR ', $cond) . ") AND ( $suppressLoggingCond ) ) THEN ";
977 if ($this->useDBPrefix
) {
978 $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
981 $sqlStmt = "INSERT INTO log_{tableName} (";
983 foreach ($columns as $column) {
984 $sqlStmt .= "$column, ";
986 $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
988 $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
989 $updateSQL .= $sqlStmt;
992 foreach ($columns as $column) {
993 $sqlStmt .= "NEW.$column, ";
994 $deleteSQL .= "OLD.$column, ";
996 if (civicrm_api3('Setting', 'getvalue', ['name' => 'logging_uniqueid_date'])) {
997 // Note that when connecting directly via mysql @uniqueID may not be set so a fallback is
998 // 'c_' to identify a non-CRM connection + timestamp to the hour + connection_id
999 // If the connection_id is longer than 6 chars it will be truncated.
1000 // We tried setting the @uniqueID in the trigger but it was unreliable.
1001 // An external interaction could split over 2 connections & it seems worth blocking the revert on
1002 // these reports & adding extra permissioning to the api for this.
1003 $connectionSQLString = "COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17))";
1006 // The log tables have not yet been converted to have varchar(17) fields for log_conn_id.
1007 // Continue to use the less reliable connection_id for al tables for now.
1008 $connectionSQLString = "CONNECTION_ID()";
1010 $sqlStmt .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
1011 $deleteSQL .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
1013 $insertSQL .= $sqlStmt;
1014 $updateSQL .= $sqlStmt;
1017 'table' => [$table],
1020 'sql' => $insertSQL,
1024 'table' => [$table],
1027 'sql' => $updateSQL,
1031 'table' => [$table],
1034 'sql' => $deleteSQL,
1040 * Disable logging temporarily.
1042 * This allow logging to be temporarily disabled for certain cases
1043 * where we want to do a mass cleanup but do not want to bother with
1046 public static function disableLoggingForThisConnection() {
1047 if (CRM_Core_Config
::singleton()->logging
) {
1048 CRM_Core_DAO
::executeQuery('SET @civicrm_disable_logging = 1');
1053 * Get all the log tables that reference civicrm_contact.
1055 * Note that it might make sense to wrap this in a getLogTablesForEntity
1056 * but this is the only entity currently available...
1058 public function getLogTablesForContact() {
1059 $tables = array_keys(CRM_Core_DAO
::getReferencesToContactTable());
1060 return array_intersect($tables, $this->tables
);
1064 * Retrieve missing log tables.
1068 public function getMissingLogTables() {
1069 if ($this->tablesExist()) {
1070 return array_diff($this->tables
, array_keys($this->logs
));