3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.5 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
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-2014
35 class CRM_Logging_Schema
{
36 private $logs = array();
37 private $tables = array();
40 private $useDBPrefix = TRUE;
42 private $reports = array(
43 'logging/contact/detail',
44 'logging/contact/summary',
45 'logging/contribute/detail',
46 'logging/contribute/summary',
49 //CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
50 private $exceptions = array(
51 'civicrm_job' => array('last_run'),
52 'civicrm_group' => array('cache_date'),
56 * Populate $this->tables and $this->logs with current db state.
58 function __construct() {
59 $dao = new CRM_Contact_DAO_Contact();
60 $civiDBName = $dao->_database
;
62 $dao = CRM_Core_DAO
::executeQuery("
64 FROM INFORMATION_SCHEMA.TABLES
65 WHERE TABLE_SCHEMA = '{$civiDBName}'
66 AND TABLE_TYPE = 'BASE TABLE'
67 AND TABLE_NAME LIKE 'civicrm_%'
69 while ($dao->fetch()) {
70 $this->tables
[] = $dao->TABLE_NAME
;
73 // do not log temp import, cache, menu and log tables
74 $this->tables
= preg_grep('/^civicrm_import_job_/', $this->tables
, PREG_GREP_INVERT
);
75 $this->tables
= preg_grep('/_cache$/', $this->tables
, PREG_GREP_INVERT
);
76 $this->tables
= preg_grep('/_log/', $this->tables
, PREG_GREP_INVERT
);
77 $this->tables
= preg_grep('/^civicrm_task_action_temp_/', $this->tables
, PREG_GREP_INVERT
);
78 $this->tables
= preg_grep('/^civicrm_export_temp_/', $this->tables
, PREG_GREP_INVERT
);
79 $this->tables
= preg_grep('/^civicrm_queue_/', $this->tables
, PREG_GREP_INVERT
);
80 $this->tables
= preg_grep('/^civicrm_menu/', $this->tables
, PREG_GREP_INVERT
); //CRM-14672
82 // do not log civicrm_mailing_event* tables, CRM-12300
83 $this->tables
= preg_grep('/^civicrm_mailing_event_/', $this->tables
, PREG_GREP_INVERT
);
85 if (defined('CIVICRM_LOGGING_DSN')) {
86 $dsn = DB
::parseDSN(CIVICRM_LOGGING_DSN
);
87 $this->useDBPrefix
= (CIVICRM_LOGGING_DSN
!= CIVICRM_DSN
);
90 $dsn = DB
::parseDSN(CIVICRM_DSN
);
91 $this->useDBPrefix
= FALSE;
93 $this->db
= $dsn['database'];
95 $dao = CRM_Core_DAO
::executeQuery("
97 FROM INFORMATION_SCHEMA.TABLES
98 WHERE TABLE_SCHEMA = '{$this->db}'
99 AND TABLE_TYPE = 'BASE TABLE'
100 AND TABLE_NAME LIKE 'log_civicrm_%'
102 while ($dao->fetch()) {
103 $log = $dao->TABLE_NAME
;
104 $this->logs
[substr($log, 4)] = $log;
109 * Return logging custom data tables.
111 function customDataLogTables() {
112 return preg_grep('/^log_civicrm_value_/', $this->logs
);
116 * Return custom data tables for specified entity / extends.
118 function entityCustomDataLogTables($extends) {
119 $customGroupTables = array();
120 $customGroupDAO = CRM_Core_BAO_CustomGroup
::getAllCustomGroupsByBaseEntity($extends);
121 $customGroupDAO->find();
122 while ($customGroupDAO->fetch()) {
123 $customGroupTables[$customGroupDAO->table_name
] = $this->logs
[$customGroupDAO->table_name
];
125 return $customGroupTables;
129 * Disable logging by dropping the triggers (but keep the log tables intact).
131 function disableLogging() {
132 $config = CRM_Core_Config
::singleton();
133 $config->logging
= FALSE;
135 $this->dropTriggers();
137 // invoke the meta trigger creation call
138 CRM_Core_DAO
::triggerRebuild();
140 $this->deleteReports();
144 * Drop triggers for all logged tables.
146 function dropTriggers($tableName = NULL) {
147 $dao = new CRM_Core_DAO
;
150 $tableNames = array($tableName);
153 $tableNames = $this->tables
;
156 foreach ($tableNames as $table) {
157 $validName = CRM_Core_DAO
::shortenSQLName($table, 48, TRUE);
160 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
161 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
162 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
165 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
166 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
167 $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
170 // now lets also be safe and drop all triggers that start with
171 // civicrm_ if we are dropping all triggers
172 // we need to do this to capture all the leftover triggers since
173 // we did the shortening trigger name for CRM-11794
174 if ($tableName === NULL) {
175 $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
177 while ($triggers->fetch()) {
178 // note that drop trigger has a wierd syntax and hence we do not
179 // send the trigger name as a string (i.e. its not quoted
180 $dao->executeQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
186 * Enable sitewide logging.
190 function enableLogging() {
191 $this->fixSchemaDifferences(TRUE);
196 * Sync log tables and rebuild triggers.
198 * @param bool $enableLogging: Ensure logging is enabled
202 function fixSchemaDifferences($enableLogging = FALSE) {
203 $config = CRM_Core_Config
::singleton();
204 if ($enableLogging) {
205 $config->logging
= TRUE;
207 if ($config->logging
) {
208 $this->fixSchemaDifferencesForALL();
210 // invoke the meta trigger creation call
211 CRM_Core_DAO
::triggerRebuild(NULL, TRUE);
215 * Add missing (potentially specified) log table columns for the given table.
217 * @param $table string name of the relevant table
218 * @param $cols mixed array of columns to add or null (to check for the missing columns)
219 * @param $rebuildTrigger boolean should we rebuild the triggers
223 function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
227 if (empty($this->logs
[$table])) {
228 $this->createLogTableFor($table);
233 $cols = $this->columnsWithDiffSpecs($table, "log_$table");
236 // use the relevant lines from CREATE TABLE to add colums to the log table
237 $create = $this->_getCreateQuery($table);
238 foreach ((array('ADD', 'MODIFY')) as $alterType) {
239 if (!empty($cols[$alterType])) {
240 foreach ($cols[$alterType] as $col) {
241 $line = $this->_getColumnQuery($col, $create);
242 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}");
247 // for any obsolete columns (not null) we just make the column nullable.
248 if (!empty($cols['OBSOLETE'])) {
249 $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
250 foreach ($cols['OBSOLETE'] as $col) {
251 $line = $this->_getColumnQuery($col, $create);
252 // This is just going to make a not null column to nullable
253 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}");
257 if ($rebuildTrigger) {
258 // invoke the meta trigger creation call
259 CRM_Core_DAO
::triggerRebuild($table);
269 private function _getCreateQuery($table) {
270 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE {$table}");
272 $create = explode("\n", $dao->Create_Table
);
278 * @param $createQuery
280 * @return array|mixed|string
282 private function _getColumnQuery($col, $createQuery) {
283 $line = preg_grep("/^ `$col` /", $createQuery);
284 $line = rtrim(array_pop($line), ',');
286 $line = $this->fixTimeStampAndNotNullSQL($line);
291 * @param bool $rebuildTrigger
293 function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
295 foreach ($this->tables
as $table) {
296 if (empty($this->logs
[$table])) {
297 $this->createLogTableFor($table);
300 $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
304 foreach ($diffs as $table => $cols) {
305 $this->fixSchemaDifferencesFor($table, $cols, FALSE);
308 if ($rebuildTrigger) {
309 // invoke the meta trigger creation call
310 CRM_Core_DAO
::triggerRebuild($table);
315 * log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
316 * so there's no need for a default timestamp and therefore we remove such default timestamps
317 * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
324 function fixTimeStampAndNotNullSQL($query) {
325 $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
326 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
327 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
328 $query = str_ireplace("NOT NULL", '', $query);
332 private function addReports() {
334 'logging/contact/detail' => ts('Logging Details'),
335 'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
336 'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
337 'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
339 // enable logging templates
340 CRM_Core_DAO
::executeQuery("
341 UPDATE civicrm_option_value
343 WHERE value IN ('" . implode("', '", $this->reports
) . "')
346 // add report instances
347 $domain_id = CRM_Core_Config
::domainID();
348 foreach ($this->reports
as $report) {
349 $dao = new CRM_Report_DAO_ReportInstance
;
350 $dao->domain_id
= $domain_id;
351 $dao->report_id
= $report;
352 $dao->title
= $titles[$report];
353 $dao->permission
= 'administer CiviCRM';
354 if ($report == 'logging/contact/summary')
355 $dao->is_reserved
= 1;
361 * Get an array of column names of the given table.
363 private function columnsOf($table, $force = FALSE) {
364 static $columnsOf = array();
366 $from = (substr($table, 0, 4) == 'log_') ?
"`{$this->db}`.$table" : $table;
368 if (!isset($columnsOf[$table]) ||
$force) {
369 $errorScope = CRM_Core_TemporaryErrorScope
::ignoreException();
370 $dao = CRM_Core_DAO
::executeQuery("SHOW COLUMNS FROM $from");
371 if (is_a($dao, 'DB_Error')) {
374 $columnsOf[$table] = array();
375 while ($dao->fetch()) {
376 $columnsOf[$table][] = $dao->Field
;
380 return $columnsOf[$table];
384 * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
386 private function columnSpecsOf($table) {
387 static $columnSpecs = array(), $civiDB = NULL;
389 if (empty($columnSpecs)) {
391 $dao = new CRM_Contact_DAO_Contact();
392 $civiDB = $dao->_database
;
394 $errorScope = CRM_Core_TemporaryErrorScope
::ignoreException();
395 // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
396 // than firing query for every given table.
398 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
399 FROM INFORMATION_SCHEMA.COLUMNS
400 WHERE table_schema IN ('{$this->db}', '{$civiDB}')";
401 $dao = CRM_Core_DAO
::executeQuery($query);
402 if (is_a($dao, 'DB_Error')) {
405 while ($dao->fetch()) {
406 if (!array_key_exists($dao->TABLE_NAME
, $columnSpecs)) {
407 $columnSpecs[$dao->TABLE_NAME
] = array();
409 $columnSpecs[$dao->TABLE_NAME
][$dao->COLUMN_NAME
] =
411 'COLUMN_NAME' => $dao->COLUMN_NAME
,
412 'DATA_TYPE' => $dao->DATA_TYPE
,
413 'IS_NULLABLE' => $dao->IS_NULLABLE
,
414 'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT
418 return $columnSpecs[$table];
427 function columnsWithDiffSpecs($civiTable, $logTable) {
428 $civiTableSpecs = $this->columnSpecsOf($civiTable);
429 $logTableSpecs = $this->columnSpecsOf($logTable);
431 $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
433 // columns to be added
434 $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
436 // columns to be modified
437 // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
438 // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
439 foreach ($civiTableSpecs as $col => $colSpecs) {
440 if (!isset($logTableSpecs[$col]) ||
!is_array($logTableSpecs[$col]) ) {
441 $logTableSpecs[$col] = array();
444 $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
445 if (!empty($specDiff) && $col != 'id' && !array_key_exists($col, $diff['ADD'])) {
446 // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
447 if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array
::value('DATA_TYPE', $logTableSpecs[$col])) {
448 // if data-type is different, surely consider the column
449 $diff['MODIFY'][] = $col;
450 } else if ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array
::value('IS_NULLABLE', $logTableSpecs[$col]) &&
451 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
452 // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
453 $diff['MODIFY'][] = $col;
454 } else if ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array
::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
455 !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')) {
456 // if default property is different, and its not about a timestamp column, consider it
457 $diff['MODIFY'][] = $col;
462 // columns to made obsolete by turning into not-null
463 $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
464 foreach ($oldCols as $col) {
465 if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
466 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
467 // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
468 $diff['OBSOLETE'][] = $col;
476 * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
478 private function createLogTableFor($table) {
479 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE $table");
481 $query = $dao->Create_Table
;
483 // rewrite the queries into CREATE TABLE queries for log tables:
485 log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
488 log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
491 // - prepend the name with log_
492 // - drop AUTO_INCREMENT columns
493 // - drop non-column rows of the query (keys, constraints, etc.)
494 // - set the ENGINE to ARCHIVE
495 // - add log-specific columns (at the end of the table)
496 $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
497 $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
498 $query = preg_replace("/^ [^`].*$/m", '', $query);
499 $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=ARCHIVE ', $query);
501 // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
502 // so there's no need for a default timestamp and therefore we remove such default timestamps
503 // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
504 $query = self
::fixTimeStampAndNotNullSQL($query);
505 $query = preg_replace("/^\) /m", "$cols\n) ", $query);
507 CRM_Core_DAO
::executeQuery($query);
509 $columns = implode(', ', $this->columnsOf($table));
510 CRM_Core_DAO
::executeQuery("INSERT INTO `{$this->db}`.log_$table ($columns, log_conn_id, log_user_id, log_action) SELECT $columns, CONNECTION_ID(), @civicrm_user_id, 'Initialization' FROM {$table}");
512 $this->tables
[] = $table;
513 $this->logs
[$table] = "log_$table";
516 private function deleteReports() {
517 // disable logging templates
518 CRM_Core_DAO
::executeQuery("
519 UPDATE civicrm_option_value
521 WHERE value IN ('" . implode("', '", $this->reports
) . "')
524 // delete report instances
525 $domain_id = CRM_Core_Config
::domainID();
526 foreach ($this->reports
as $report) {
527 $dao = new CRM_Report_DAO_ReportInstance
;
528 $dao->domain_id
= $domain_id;
529 $dao->report_id
= $report;
535 * Predicate whether logging is enabled.
537 public function isEnabled() {
538 $config = CRM_Core_Config
::singleton();
540 if ($config->logging
) {
541 return $this->tablesExist() and $this->triggersExist();
547 * Predicate whether any log tables exist.
549 private function tablesExist() {
550 return !empty($this->logs
);
554 * Predicate whether the logging triggers are in place.
556 private function triggersExist() {
557 // FIXME: probably should be a bit more thorough…
558 // note that the LIKE parameter is TABLE NAME
559 return (bool) CRM_Core_DAO
::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
564 * @param null $tableName
567 function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
568 // check if we have logging enabled
569 $config =& CRM_Core_Config
::singleton();
570 if (!$config->logging
) {
574 $insert = array('INSERT');
575 $update = array('UPDATE');
576 $delete = array('DELETE');
579 $tableNames = array($tableName);
582 $tableNames = $this->tables
;
585 // logging is enabled, so now lets create the trigger info tables
586 foreach ($tableNames as $table) {
587 $columns = $this->columnsOf($table, $force);
589 // only do the change if any data has changed
591 foreach ($columns as $column) {
592 // ignore modified_date changes
593 if ($column != 'modified_date' && !in_array($column, CRM_Utils_Array
::value($table, $this->exceptions
, array()))) {
594 $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
597 $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
598 $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
600 if ($this->useDBPrefix
) {
601 $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
604 $sqlStmt = "INSERT INTO log_{tableName} (";
606 foreach ($columns as $column) {
607 $sqlStmt .= "$column, ";
609 $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
611 $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
612 $updateSQL .= $sqlStmt;
615 foreach ($columns as $column) {
616 $sqlStmt .= "NEW.$column, ";
617 $deleteSQL .= "OLD.$column, ";
619 $sqlStmt .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
620 $deleteSQL .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
622 $sqlStmt .= "END IF;";
623 $deleteSQL .= "END IF;";
625 $insertSQL .= $sqlStmt;
626 $updateSQL .= $sqlStmt;
629 'table' => array($table),
636 'table' => array($table),
643 'table' => array($table),
652 * This allow logging to be temporarily disabled for certain cases
653 * where we want to do a mass cleanup but dont want to bother with
659 static function disableLoggingForThisConnection( ) {
660 // do this only if logging is enabled
661 $config = CRM_Core_Config
::singleton( );
662 if ( $config->logging
) {
663 CRM_Core_DAO
::executeQuery( 'SET @civicrm_disable_logging = 1' );