Merge pull request #1674 from systopia/CRM-13440
[civicrm-core.git] / CRM / Logging / Schema.php
1 <?php
2 /*
3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
9 | |
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. |
13 | |
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. |
18 | |
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 +--------------------------------------------------------------------+
26 */
27
28 /**
29 *
30 * @package CRM
31 * @copyright CiviCRM LLC (c) 2004-2013
32 * $Id$
33 *
34 */
35 class CRM_Logging_Schema {
36 private $logs = array();
37 private $tables = array();
38
39 private $db;
40 private $useDBPrefix = TRUE;
41
42 private $reports = array(
43 'logging/contact/detail',
44 'logging/contact/summary',
45 'logging/contribute/detail',
46 'logging/contribute/summary',
47 );
48
49 /**
50 * Populate $this->tables and $this->logs with current db state.
51 */
52 function __construct() {
53 $dao = new CRM_Contact_DAO_Contact();
54 $civiDBName = $dao->_database;
55
56 $dao = CRM_Core_DAO::executeQuery("
57 SELECT TABLE_NAME
58 FROM INFORMATION_SCHEMA.TABLES
59 WHERE TABLE_SCHEMA = '{$civiDBName}'
60 AND TABLE_TYPE = 'BASE TABLE'
61 AND TABLE_NAME LIKE 'civicrm_%'
62 ");
63 while ($dao->fetch()) {
64 $this->tables[] = $dao->TABLE_NAME;
65 }
66
67 // do not log temp import, cache and log tables
68 $this->tables = preg_grep('/^civicrm_import_job_/', $this->tables, PREG_GREP_INVERT);
69 $this->tables = preg_grep('/_cache$/', $this->tables, PREG_GREP_INVERT);
70 $this->tables = preg_grep('/_log/', $this->tables, PREG_GREP_INVERT);
71 $this->tables = preg_grep('/^civicrm_task_action_temp_/', $this->tables, PREG_GREP_INVERT);
72 $this->tables = preg_grep('/^civicrm_export_temp_/', $this->tables, PREG_GREP_INVERT);
73 $this->tables = preg_grep('/^civicrm_queue_/', $this->tables, PREG_GREP_INVERT);
74
75 // do not log civicrm_mailing_event* tables, CRM-12300
76 $this->tables = preg_grep('/^civicrm_mailing_event_/', $this->tables, PREG_GREP_INVERT);
77
78 if (defined('CIVICRM_LOGGING_DSN')) {
79 $dsn = DB::parseDSN(CIVICRM_LOGGING_DSN);
80 $this->useDBPrefix = (CIVICRM_LOGGING_DSN != CIVICRM_DSN);
81 }
82 else {
83 $dsn = DB::parseDSN(CIVICRM_DSN);
84 $this->useDBPrefix = FALSE;
85 }
86 $this->db = $dsn['database'];
87
88 $dao = CRM_Core_DAO::executeQuery("
89 SELECT TABLE_NAME
90 FROM INFORMATION_SCHEMA.TABLES
91 WHERE TABLE_SCHEMA = '{$this->db}'
92 AND TABLE_TYPE = 'BASE TABLE'
93 AND TABLE_NAME LIKE 'log_civicrm_%'
94 ");
95 while ($dao->fetch()) {
96 $log = $dao->TABLE_NAME;
97 $this->logs[substr($log, 4)] = $log;
98 }
99 }
100
101 /**
102 * Return logging custom data tables.
103 */
104 function customDataLogTables() {
105 return preg_grep('/^log_civicrm_value_/', $this->logs);
106 }
107
108 /**
109 * Disable logging by dropping the triggers (but keep the log tables intact).
110 */
111 function disableLogging() {
112 $config = CRM_Core_Config::singleton();
113 $config->logging = FALSE;
114
115 $this->dropTriggers();
116
117 // invoke the meta trigger creation call
118 CRM_Core_DAO::triggerRebuild();
119
120 $this->deleteReports();
121 }
122
123 /**
124 * Drop triggers for all logged tables.
125 */
126 function dropTriggers($tableName = NULL) {
127 $dao = new CRM_Core_DAO;
128
129 if ($tableName) {
130 $tableNames = array($tableName);
131 }
132 else {
133 $tableNames = $this->tables;
134 }
135
136 foreach ($tableNames as $table) {
137 // before triggers
138 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_insert");
139 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_update");
140 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_delete");
141
142 // after triggers
143 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_insert");
144 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_update");
145 $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_delete");
146 }
147 }
148
149 /**
150 * Enable sitewide logging.
151 *
152 * @return void
153 */
154 function enableLogging() {
155 $this->fixSchemaDifferences(TRUE);
156 $this->addReports();
157 }
158
159 /**
160 * Sync log tables and rebuild triggers.
161 *
162 * @param bool $enableLogging: Ensure logging is enabled
163 *
164 * @return void
165 */
166 function fixSchemaDifferences($enableLogging = FALSE) {
167 $config = CRM_Core_Config::singleton();
168 if ($enableLogging) {
169 $config->logging = TRUE;
170 }
171 if ($config->logging) {
172 $this->fixSchemaDifferencesForALL();
173 }
174 // invoke the meta trigger creation call
175 CRM_Core_DAO::triggerRebuild();
176 }
177
178 /**
179 * Add missing (potentially specified) log table columns for the given table.
180 *
181 * @param $table string name of the relevant table
182 * @param $cols mixed array of columns to add or null (to check for the missing columns)
183 * @param $rebuildTrigger boolean should we rebuild the triggers
184 *
185 * @return void
186 */
187 function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
188 if (empty($table)) {
189 return FALSE;
190 }
191 if (empty($this->logs[$table])) {
192 $this->createLogTableFor($table);
193 return TRUE;
194 }
195
196 if (empty($cols)) {
197 $cols = $this->columnsWithDiffSpecs($table, "log_$table");
198 }
199
200 // use the relevant lines from CREATE TABLE to add colums to the log table
201 $create = $this->_getCreateQuery($table);
202 foreach ((array('ADD', 'MODIFY')) as $alterType) {
203 if (!empty($cols[$alterType])) {
204 foreach ($cols[$alterType] as $col) {
205 $line = $this->_getColumnQuery($col, $create);
206 CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}");
207 }
208 }
209 }
210
211 // for any obsolete columns (not null) we just make the column nullable.
212 if (!empty($cols['OBSOLETE'])) {
213 $create = $this->_getCreateQuery("log_{$table}");
214 foreach ($cols['OBSOLETE'] as $col) {
215 $line = $this->_getColumnQuery($col, $create);
216 // This is just going to make a not null column to nullable
217 CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}");
218 }
219 }
220
221 if ($rebuildTrigger) {
222 // invoke the meta trigger creation call
223 CRM_Core_DAO::triggerRebuild($table);
224 }
225 return TRUE;
226 }
227
228 private function _getCreateQuery($table) {
229 $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}");
230 $dao->fetch();
231 $create = explode("\n", $dao->Create_Table);
232 return $create;
233 }
234
235 private function _getColumnQuery($col, $createQuery) {
236 $line = preg_grep("/^ `$col` /", $createQuery);
237 $line = rtrim(array_pop($line), ',');
238 // CRM-11179
239 $line = $this->fixTimeStampAndNotNullSQL($line);
240 return $line;
241 }
242
243 function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
244 $diffs = array();
245 foreach ($this->tables as $table) {
246 if (empty($this->logs[$table])) {
247 $this->createLogTableFor($table);
248 } else {
249 $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
250 }
251 }
252
253 foreach ($diffs as $table => $cols) {
254 $this->fixSchemaDifferencesFor($table, $cols, FALSE);
255 }
256
257 if ($rebuildTrigger) {
258 // invoke the meta trigger creation call
259 CRM_Core_DAO::triggerRebuild($table);
260 }
261 }
262
263 /*
264 * log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
265 * so there's no need for a default timestamp and therefore we remove such default timestamps
266 * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
267 */
268 function fixTimeStampAndNotNullSQL($query) {
269 $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
270 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
271 $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
272 $query = str_ireplace("NOT NULL", '', $query);
273 return $query;
274 }
275
276 private function addReports() {
277 $titles = array(
278 'logging/contact/detail' => ts('Logging Details'),
279 'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
280 'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
281 'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
282 );
283 // enable logging templates
284 CRM_Core_DAO::executeQuery("
285 UPDATE civicrm_option_value
286 SET is_active = 1
287 WHERE value IN ('" . implode("', '", $this->reports) . "')
288 ");
289
290 // add report instances
291 $domain_id = CRM_Core_Config::domainID();
292 foreach ($this->reports as $report) {
293 $dao = new CRM_Report_DAO_Instance;
294 $dao->domain_id = $domain_id;
295 $dao->report_id = $report;
296 $dao->title = $titles[$report];
297 $dao->permission = 'administer CiviCRM';
298 if ($report == 'logging/contact/summary')
299 $dao->is_reserved = 1;
300 $dao->insert();
301 }
302 }
303
304 /**
305 * Get an array of column names of the given table.
306 */
307 private function columnsOf($table) {
308 static $columnsOf = array();
309
310 $from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
311
312 if (!isset($columnsOf[$table])) {
313 CRM_Core_Error::ignoreException();
314 $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from");
315 CRM_Core_Error::setCallback();
316 if (is_a($dao, 'DB_Error')) {
317 return array();
318 }
319 $columnsOf[$table] = array();
320 while ($dao->fetch()) {
321 $columnsOf[$table][] = $dao->Field;
322 }
323 }
324
325 return $columnsOf[$table];
326 }
327
328 /**
329 * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
330 */
331 private function columnSpecsOf($table) {
332 static $columnSpecs = array(), $civiDB = NULL;
333
334 if (empty($columnSpecs)) {
335 if (!$civiDB) {
336 $dao = new CRM_Contact_DAO_Contact();
337 $civiDB = $dao->_database;
338 }
339 CRM_Core_Error::ignoreException();
340 // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
341 // than firing query for every given table.
342 $query = "
343 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
344 FROM INFORMATION_SCHEMA.COLUMNS
345 WHERE table_schema IN ('{$this->db}', '{$civiDB}')";
346 $dao = CRM_Core_DAO::executeQuery($query);
347 CRM_Core_Error::setCallback();
348 if (is_a($dao, 'DB_Error')) {
349 return array();
350 }
351 while ($dao->fetch()) {
352 if (!array_key_exists($dao->TABLE_NAME, $columnSpecs)) {
353 $columnSpecs[$dao->TABLE_NAME] = array();
354 }
355 $columnSpecs[$dao->TABLE_NAME][$dao->COLUMN_NAME] =
356 array(
357 'COLUMN_NAME' => $dao->COLUMN_NAME,
358 'DATA_TYPE' => $dao->DATA_TYPE,
359 'IS_NULLABLE' => $dao->IS_NULLABLE,
360 'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT
361 );
362 }
363 }
364 return $columnSpecs[$table];
365 }
366
367 function columnsWithDiffSpecs($civiTable, $logTable) {
368 $civiTableSpecs = $this->columnSpecsOf($civiTable);
369 $logTableSpecs = $this->columnSpecsOf($logTable);
370
371 $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
372
373 // columns to be added
374 $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
375
376 // columns to be modified
377 // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
378 // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
379 foreach ($civiTableSpecs as $col => $colSpecs) {
380 $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
381 if (!empty($specDiff) && $col != 'id' && !array_key_exists($col, $diff['ADD'])) {
382 // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
383 if ($civiTableSpecs[$col]['DATA_TYPE'] != $logTableSpecs[$col]['DATA_TYPE']) {
384 // if data-type is different, surely consider the column
385 $diff['MODIFY'][] = $col;
386 } else if ($civiTableSpecs[$col]['IS_NULLABLE'] != $logTableSpecs[$col]['IS_NULLABLE'] &&
387 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
388 // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
389 $diff['MODIFY'][] = $col;
390 } else if ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != $logTableSpecs[$col]['COLUMN_DEFAULT'] &&
391 !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')) {
392 // if default property is different, and its not about a timestamp column, consider it
393 $diff['MODIFY'][] = $col;
394 }
395 }
396 }
397
398 // columns to made obsolete by turning into not-null
399 $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
400 foreach ($oldCols as $col) {
401 if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
402 $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
403 // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
404 $diff['OBSOLETE'][] = $col;
405 }
406 }
407
408 return $diff;
409 }
410
411 /**
412 * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
413 */
414 private function createLogTableFor($table) {
415 $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table");
416 $dao->fetch();
417 $query = $dao->Create_Table;
418
419 // rewrite the queries into CREATE TABLE queries for log tables:
420 $cols = <<<COLS
421 log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
422 log_conn_id INTEGER,
423 log_user_id INTEGER,
424 log_action ENUM('Initialization', 'Insert', 'Update', 'Delete')
425 COLS;
426
427 // - prepend the name with log_
428 // - drop AUTO_INCREMENT columns
429 // - drop non-column rows of the query (keys, constraints, etc.)
430 // - set the ENGINE to ARCHIVE
431 // - add log-specific columns (at the end of the table)
432 $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
433 $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
434 $query = preg_replace("/^ [^`].*$/m", '', $query);
435 $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=ARCHIVE ', $query);
436
437 // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
438 // so there's no need for a default timestamp and therefore we remove such default timestamps
439 // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
440 $query = self::fixTimeStampAndNotNullSQL($query);
441 $query = preg_replace("/^\) /m", "$cols\n) ", $query);
442
443 CRM_Core_DAO::executeQuery($query);
444
445 $columns = implode(', ', $this->columnsOf($table));
446 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}");
447
448 $this->tables[] = $table;
449 $this->logs[$table] = "log_$table";
450 }
451
452 private function deleteReports() {
453 // disable logging templates
454 CRM_Core_DAO::executeQuery("
455 UPDATE civicrm_option_value
456 SET is_active = 0
457 WHERE value IN ('" . implode("', '", $this->reports) . "')
458 ");
459
460 // delete report instances
461 $domain_id = CRM_Core_Config::domainID();
462 foreach ($this->reports as $report) {
463 $dao = new CRM_Report_DAO_Instance;
464 $dao->domain_id = $domain_id;
465 $dao->report_id = $report;
466 $dao->delete();
467 }
468 }
469
470 /**
471 * Predicate whether logging is enabled.
472 */
473 public function isEnabled() {
474 $config = CRM_Core_Config::singleton();
475
476 if ($config->logging) {
477 return $this->tablesExist() and $this->triggersExist();
478 }
479 return FALSE;
480 }
481
482 /**
483 * Predicate whether any log tables exist.
484 */
485 private function tablesExist() {
486 return !empty($this->logs);
487 }
488
489 /**
490 * Predicate whether the logging triggers are in place.
491 */
492 private function triggersExist() {
493 // FIXME: probably should be a bit more thorough…
494 // note that the LIKE parameter is TABLE NAME
495 return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
496 }
497
498 function triggerInfo(&$info, $tableName = NULL) {
499 // check if we have logging enabled
500 $config =& CRM_Core_Config::singleton();
501 if (!$config->logging) {
502 return;
503 }
504
505 $insert = array('INSERT');
506 $update = array('UPDATE');
507 $delete = array('DELETE');
508
509 if ($tableName) {
510 $tableNames = array($tableName);
511 }
512 else {
513 $tableNames = $this->tables;
514 }
515
516 // logging is enabled, so now lets create the trigger info tables
517 foreach ($tableNames as $table) {
518 $columns = $this->columnsOf($table);
519
520 // only do the change if any data has changed
521 $cond = array( );
522 foreach ($columns as $column) {
523 // ignore modified_date changes
524 if ($column != 'modified_date') {
525 $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
526 }
527 }
528 $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
529 $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
530
531 if ($this->useDBPrefix) {
532 $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
533 }
534 else {
535 $sqlStmt = "INSERT INTO log_{tableName} (";
536 }
537 foreach ($columns as $column) {
538 $sqlStmt .= "$column, ";
539 }
540 $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
541
542 $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
543 $updateSQL .= $sqlStmt;
544
545 $sqlStmt = '';
546 foreach ($columns as $column) {
547 $sqlStmt .= "NEW.$column, ";
548 $deleteSQL .= "OLD.$column, ";
549 }
550 $sqlStmt .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
551 $deleteSQL .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
552
553 $sqlStmt .= "END IF;";
554 $deleteSQL .= "END IF;";
555
556 $insertSQL .= $sqlStmt;
557 $updateSQL .= $sqlStmt;
558
559 $info[] = array(
560 'table' => array($table),
561 'when' => 'AFTER',
562 'event' => $insert,
563 'sql' => $insertSQL,
564 );
565
566 $info[] = array(
567 'table' => array($table),
568 'when' => 'AFTER',
569 'event' => $update,
570 'sql' => $updateSQL,
571 );
572
573 $info[] = array(
574 'table' => array($table),
575 'when' => 'AFTER',
576 'event' => $delete,
577 'sql' => $deleteSQL,
578 );
579 }
580 }
581
582 /**
583 * This allow logging to be temporarily disabled for certain cases
584 * where we want to do a mass cleanup but dont want to bother with
585 * an audit trail
586 *
587 * @static
588 * @public
589 */
590 static function disableLoggingForThisConnection( ) {
591 // do this only if logging is enabled
592 $config = CRM_Core_Config::singleton( );
593 if ( $config->logging ) {
594 CRM_Core_DAO::executeQuery( 'SET @civicrm_disable_logging = 1' );
595 }
596 }
597
598 }
599