3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
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 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
);
81 // do not log civicrm_mailing_event* tables, CRM-12300
82 $this->tables
= preg_grep('/^civicrm_mailing_event_/', $this->tables
, PREG_GREP_INVERT
);
84 if (defined('CIVICRM_LOGGING_DSN')) {
85 $dsn = DB
::parseDSN(CIVICRM_LOGGING_DSN
);
86 $this->useDBPrefix
= (CIVICRM_LOGGING_DSN
!= CIVICRM_DSN
);
89 $dsn = DB
::parseDSN(CIVICRM_DSN
);
90 $this->useDBPrefix
= FALSE;
92 $this->db
= $dsn['database'];
94 $dao = CRM_Core_DAO
::executeQuery("
96 FROM INFORMATION_SCHEMA.TABLES
97 WHERE TABLE_SCHEMA = '{$this->db}'
98 AND TABLE_TYPE = 'BASE TABLE'
99 AND TABLE_NAME LIKE 'log_civicrm_%'
101 while ($dao->fetch()) {
102 $log = $dao->TABLE_NAME
;
103 $this->logs
[substr($log, 4)] = $log;
108 * Return logging custom data tables.
110 function customDataLogTables() {
111 return preg_grep('/^log_civicrm_value_/', $this->logs
);
115 * Disable logging by dropping the triggers (but keep the log tables intact).
117 function disableLogging() {
118 $config = CRM_Core_Config
::singleton();
119 $config->logging
= FALSE;
121 $this->dropTriggers();
123 // invoke the meta trigger creation call
124 CRM_Core_DAO
::triggerRebuild();
126 $this->deleteReports();
130 * Drop triggers for all logged tables.
132 function dropTriggers($tableName = NULL) {
133 $dao = new CRM_Core_DAO
;
136 $tableNames = array($tableName);
139 $tableNames = $this->tables
;
142 foreach ($tableNames as $table) {
144 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_insert");
145 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_update");
146 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_delete");
149 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_insert");
150 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_update");
151 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_delete");
156 * Enable sitewide logging.
160 function enableLogging() {
161 $this->fixSchemaDifferences(TRUE);
166 * Sync log tables and rebuild triggers.
168 * @param bool $enableLogging: Ensure logging is enabled
172 function fixSchemaDifferences($enableLogging = FALSE) {
173 $config = CRM_Core_Config
::singleton();
174 if ($enableLogging) {
175 $config->logging
= TRUE;
177 if ($config->logging
) {
178 foreach ($this->schemaDifferences() as $table => $cols) {
179 $this->fixSchemaDifferencesFor($table, $cols, FALSE);
182 // invoke the meta trigger creation call
183 CRM_Core_DAO
::triggerRebuild();
187 * Add missing (potentially specified) log table columns for the given table.
189 * @param $table string name of the relevant table
190 * @param $cols mixed array of columns to add or null (to check for the missing columns)
191 * @param $rebuildTrigger boolean should we rebuild the triggers
195 function fixSchemaDifferencesFor($table, $cols = NULL, $rebuildTrigger = TRUE) {
196 if (empty($this->logs
[$table])) {
197 $this->createLogTableFor($table);
201 if (is_null($cols)) {
202 $cols = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
208 // use the relevant lines from CREATE TABLE to add colums to the log table
209 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE $table");
211 $create = explode("\n", $dao->Create_Table
);
212 foreach ($cols as $col) {
213 $line = preg_grep("/^ `$col` /", $create);
214 $line = substr(array_pop($line), 0, -1);
216 $line = self
::fixTimeStampAndNotNullSQL($line);
218 CRM_Core_DAO
::executeQuery("ALTER TABLE `{$this->db}`.log_$table ADD $line");
221 if ($rebuildTrigger) {
222 // invoke the meta trigger creation call
223 CRM_Core_DAO
::triggerRebuild($table);
227 function fixTimeStampAndNotNullSQL($query) {
228 $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
229 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
230 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
231 $query = str_ireplace("NOT NULL", '', $query);
236 * Find missing log table columns by comparing columns of the relevant tables.
237 * Returns table-name-keyed array of arrays of missing columns, e.g. array('civicrm_value_foo_1' => array('bar_1', 'baz_2'))
239 function schemaDifferences() {
241 foreach ($this->tables
as $table) {
242 $diffs[$table] = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
244 return array_filter($diffs);
247 private function addReports() {
249 'logging/contact/detail' => ts('Logging Details'),
250 'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
251 'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
252 'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
254 // enable logging templates
255 CRM_Core_DAO
::executeQuery("
256 UPDATE civicrm_option_value
258 WHERE value IN ('" . implode("', '", $this->reports
) . "')
261 // add report instances
262 $domain_id = CRM_Core_Config
::domainID();
263 foreach ($this->reports
as $report) {
264 $dao = new CRM_Report_DAO_ReportInstance
;
265 $dao->domain_id
= $domain_id;
266 $dao->report_id
= $report;
267 $dao->title
= $titles[$report];
268 $dao->permission
= 'administer CiviCRM';
269 if ($report == 'logging/contact/summary')
270 $dao->is_reserved
= 1;
276 * Get an array of column names of the given table.
278 private function columnsOf($table) {
279 static $columnsOf = array();
281 $from = (substr($table, 0, 4) == 'log_') ?
"`{$this->db}`.$table" : $table;
283 if (!isset($columnsOf[$table])) {
284 CRM_Core_Error
::ignoreException();
285 $dao = CRM_Core_DAO
::executeQuery("SHOW COLUMNS FROM $from");
286 CRM_Core_Error
::setCallback();
287 if (is_a($dao, 'DB_Error')) {
290 $columnsOf[$table] = array();
291 while ($dao->fetch()) {
292 $columnsOf[$table][] = $dao->Field
;
296 return $columnsOf[$table];
300 * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
302 private function createLogTableFor($table) {
303 $dao = CRM_Core_DAO
::executeQuery("SHOW CREATE TABLE $table");
305 $query = $dao->Create_Table
;
307 // rewrite the queries into CREATE TABLE queries for log tables:
309 log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
312 log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
315 // - prepend the name with log_
316 // - drop AUTO_INCREMENT columns
317 // - drop non-column rows of the query (keys, constraints, etc.)
318 // - set the ENGINE to ARCHIVE
319 // - add log-specific columns (at the end of the table)
320 $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
321 $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
322 $query = preg_replace("/^ [^`].*$/m", '', $query);
323 $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=ARCHIVE ', $query);
325 // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
326 // so there's no need for a default timestamp and therefore we remove such default timestamps
327 // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
328 $query = self
::fixTimeStampAndNotNullSQL($query);
329 $query = preg_replace("/^\) /m", "$cols\n) ", $query);
331 CRM_Core_DAO
::executeQuery($query);
333 $columns = implode(', ', $this->columnsOf($table));
334 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}");
336 $this->tables
[] = $table;
337 $this->logs
[$table] = "log_$table";
340 private function deleteReports() {
341 // disable logging templates
342 CRM_Core_DAO
::executeQuery("
343 UPDATE civicrm_option_value
345 WHERE value IN ('" . implode("', '", $this->reports
) . "')
348 // delete report instances
349 $domain_id = CRM_Core_Config
::domainID();
350 foreach ($this->reports
as $report) {
351 $dao = new CRM_Report_DAO_ReportInstance
;
352 $dao->domain_id
= $domain_id;
353 $dao->report_id
= $report;
359 * Predicate whether logging is enabled.
361 public function isEnabled() {
362 $config = CRM_Core_Config
::singleton();
364 if ($config->logging
) {
365 return $this->tablesExist() and $this->triggersExist();
371 * Predicate whether any log tables exist.
373 private function tablesExist() {
374 return !empty($this->logs
);
378 * Predicate whether the logging triggers are in place.
380 private function triggersExist() {
381 // FIXME: probably should be a bit more thorough…
382 // note that the LIKE parameter is TABLE NAME
383 return (bool) CRM_Core_DAO
::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
386 function triggerInfo(&$info, $tableName = NULL) {
387 // check if we have logging enabled
388 $config =& CRM_Core_Config
::singleton();
389 if (!$config->logging
) {
393 $insert = array('INSERT');
394 $update = array('UPDATE');
395 $delete = array('DELETE');
398 $tableNames = array($tableName);
401 $tableNames = $this->tables
;
404 // logging is enabled, so now lets create the trigger info tables
405 foreach ($tableNames as $table) {
406 $columns = $this->columnsOf($table);
408 // only do the change if any data has changed
410 foreach ($columns as $column) {
411 // ignore modified_date changes
412 if ($column != 'modified_date' && !in_array($column, CRM_Utils_Array
::value($table, $this->exceptions
, array()))) {
413 $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
416 $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
417 $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
419 if ($this->useDBPrefix
) {
420 $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
423 $sqlStmt = "INSERT INTO log_{tableName} (";
425 foreach ($columns as $column) {
426 $sqlStmt .= "$column, ";
428 $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
430 $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
431 $updateSQL .= $sqlStmt;
434 foreach ($columns as $column) {
435 $sqlStmt .= "NEW.$column, ";
436 $deleteSQL .= "OLD.$column, ";
438 $sqlStmt .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
439 $deleteSQL .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
441 $sqlStmt .= "END IF;";
442 $deleteSQL .= "END IF;";
444 $insertSQL .= $sqlStmt;
445 $updateSQL .= $sqlStmt;
448 'table' => array($table),
455 'table' => array($table),
462 'table' => array($table),
471 * This allow logging to be temporarily disabled for certain cases
472 * where we want to do a mass cleanup but dont want to bother with
478 static function disableLoggingForThisConnection( ) {
479 // do this only if logging is enabled
480 $config = CRM_Core_Config
::singleton( );
481 if ( $config->logging
) {
482 CRM_Core_DAO
::executeQuery( 'SET @civicrm_disable_logging = 1' );