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
  41   private $reports = array(
  42     'logging/contact/detail',
  43     'logging/contact/summary',
  44     'logging/contribute/detail',
  45     'logging/contribute/summary',
  46   );
  47
  48   /**
  49    * Populate $this->tables and $this->logs with current db state.
  50    */
  51   function __construct() {
  52     $dao = new CRM_Contact_DAO_Contact();
  53     $civiDBName = $dao->_database;
  54
  55     $dao = CRM_Core_DAO::executeQuery("
  56 SELECT TABLE_NAME
  57 FROM   INFORMATION_SCHEMA.TABLES
  58 WHERE  TABLE_SCHEMA = '{$civiDBName}'
  59 AND    TABLE_TYPE = 'BASE TABLE'
  60 AND    TABLE_NAME LIKE 'civicrm_%'
  61 ");
  62     while ($dao->fetch()) {
  63       $this->tables[] = $dao->TABLE_NAME;
  64     }
  65
  66     // do not log temp import, cache and log tables
  67     $this->tables = preg_grep('/^civicrm_import_job_/', $this->tables, PREG_GREP_INVERT);
  68     $this->tables = preg_grep('/_cache$/', $this->tables, PREG_GREP_INVERT);
  69     $this->tables = preg_grep('/_log/', $this->tables, PREG_GREP_INVERT);
  70     $this->tables = preg_grep('/^civicrm_task_action_temp_/', $this->tables, PREG_GREP_INVERT);
  71     $this->tables = preg_grep('/^civicrm_export_temp_/', $this->tables, PREG_GREP_INVERT);
  72     $this->tables = preg_grep('/^civicrm_queue_/', $this->tables, PREG_GREP_INVERT);
  73
  74     // do not log civicrm_mailing_event* tables, CRM-12300
  75     $this->tables = preg_grep('/^civicrm_mailing_event_/', $this->tables, PREG_GREP_INVERT);
  76
  77     $dsn = defined('CIVICRM_LOGGING_DSN') ? DB::parseDSN(CIVICRM_LOGGING_DSN) : DB::parseDSN(CIVICRM_DSN);
  78     $this->db = $dsn['database'];
  79
  80     $dao = CRM_Core_DAO::executeQuery("
  81 SELECT TABLE_NAME
  82 FROM   INFORMATION_SCHEMA.TABLES
  83 WHERE  TABLE_SCHEMA = '{$this->db}'
  84 AND    TABLE_TYPE = 'BASE TABLE'
  85 AND    TABLE_NAME LIKE 'log_civicrm_%'
  86 ");
  87     while ($dao->fetch()) {
  88       $log = $dao->TABLE_NAME;
  89       $this->logs[substr($log, 4)] = $log;
  90     }
  91   }
  92
  93   /**
  94    * Return logging custom data tables.
  95    */
  96   function customDataLogTables() {
  97     return preg_grep('/^log_civicrm_value_/', $this->logs);
  98   }
  99
 100   /**
 101    * Disable logging by dropping the triggers (but keep the log tables intact).
 102    */
 103   function disableLogging() {
 104     $config = CRM_Core_Config::singleton();
 105     $config->logging = FALSE;
 106
 107     $this->dropTriggers();
 108
 109     // invoke the meta trigger creation call
 110     CRM_Core_DAO::triggerRebuild();
 111
 112     $this->deleteReports();
 113   }
 114
 115   /**
 116    * Drop triggers for all logged tables.
 117    */
 118   function dropTriggers($tableName = NULL) {
 119     $dao = new CRM_Core_DAO;
 120
 121     if ($tableName) {
 122       $tableNames = array($tableName);
 123     }
 124     else {
 125       $tableNames = $this->tables;
 126     }
 127
 128     foreach ($tableNames as $table) {
 129       // before triggers
 130       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_insert");
 131       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_update");
 132       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_delete");
 133
 134      // after triggers
 135       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_insert");
 136       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_update");
 137       $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_delete");
 138     }
 139   }
 140
 141   /**
 142    * Enable sitewide logging.
 143    *
 144    * @return void
 145    */
 146   function enableLogging() {
 147     $this->fixSchemaDifferences(TRUE);
 148     $this->addReports();
 149   }
 150
 151   /**
 152    * Sync log tables and rebuild triggers.
 153    *
 154    * @param bool $enableLogging: Ensure logging is enabled
 155    *
 156    * @return void
 157    */
 158   function fixSchemaDifferences($enableLogging = FALSE) {
 159     $config = CRM_Core_Config::singleton();
 160     if ($enableLogging) {
 161       $config->logging = TRUE;
 162     }
 163     if ($config->logging) {
 164       foreach ($this->schemaDifferences() as $table => $cols) {
 165         $this->fixSchemaDifferencesFor($table, $cols, FALSE);
 166       }
 167     }
 168     // invoke the meta trigger creation call
 169     CRM_Core_DAO::triggerRebuild();
 170   }
 171
 172   /**
 173    * Add missing (potentially specified) log table columns for the given table.
 174    *
 175    * @param $table string  name of the relevant table
 176    * @param $cols mixed    array of columns to add or null (to check for the missing columns)
 177    * @param $rebuildTrigger boolean should we rebuild the triggers
 178    *
 179    * @return void
 180    */
 181   function fixSchemaDifferencesFor($table, $cols = NULL, $rebuildTrigger = TRUE) {
 182     if (empty($this->logs[$table])) {
 183       $this->createLogTableFor($table);
 184       return;
 185     }
 186
 187     if (is_null($cols)) {
 188       $cols = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
 189     }
 190     if (empty($cols)) {
 191       return;
 192     }
 193
 194     // use the relevant lines from CREATE TABLE to add colums to the log table
 195     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table");
 196     $dao->fetch();
 197     $create = explode("\n", $dao->Create_Table);
 198     foreach ($cols as $col) {
 199       $line = preg_grep("/^  `$col` /", $create);
 200       $line = substr(array_pop($line), 0, -1);
 201       // CRM-11179
 202       $line = self::fixTimeStampAndNotNullSQL($line);
 203
 204       CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table ADD $line");
 205     }
 206
 207     if ($rebuildTrigger) {
 208       // invoke the meta trigger creation call
 209       CRM_Core_DAO::triggerRebuild($table);
 210     }
 211   }
 212
 213   function fixTimeStampAndNotNullSQL($query) {
 214     $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
 215     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
 216     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
 217     $query = str_ireplace("NOT NULL", '', $query);
 218     return $query;
 219   }
 220
 221   /**
 222    * Find missing log table columns by comparing columns of the relevant tables.
 223    * Returns table-name-keyed array of arrays of missing columns, e.g. array('civicrm_value_foo_1' => array('bar_1', 'baz_2'))
 224    */
 225   function schemaDifferences() {
 226     $diffs = array();
 227     foreach ($this->tables as $table) {
 228       $diffs[$table] = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
 229     }
 230     return array_filter($diffs);
 231   }
 232
 233   private function addReports() {
 234     $titles = array(
 235       'logging/contact/detail' => ts('Logging Details'),
 236       'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
 237       'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
 238       'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
 239     );
 240     // enable logging templates
 241     CRM_Core_DAO::executeQuery("
 242             UPDATE civicrm_option_value
 243             SET is_active = 1
 244             WHERE value IN ('" . implode("', '", $this->reports) . "')
 245         ");
 246
 247     // add report instances
 248     $domain_id = CRM_Core_Config::domainID();
 249     foreach ($this->reports as $report) {
 250       $dao             = new CRM_Report_DAO_Instance;
 251       $dao->domain_id  = $domain_id;
 252       $dao->report_id  = $report;
 253       $dao->title      = $titles[$report];
 254       $dao->permission = 'administer CiviCRM';
 255       if ($report == 'logging/contact/summary')
 256         $dao->is_reserved = 1;
 257       $dao->insert();
 258     }
 259   }
 260
 261   /**
 262    * Get an array of column names of the given table.
 263    */
 264   private function columnsOf($table) {
 265     static $columnsOf = array();
 266
 267     $from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
 268
 269     if (!isset($columnsOf[$table])) {
 270       CRM_Core_Error::ignoreException();
 271       $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from");
 272       CRM_Core_Error::setCallback();
 273       if (is_a($dao, 'DB_Error')) {
 274         return array();
 275       }
 276       $columnsOf[$table] = array();
 277       while ($dao->fetch()) {
 278         $columnsOf[$table][] = $dao->Field;
 279       }
 280     }
 281
 282     return $columnsOf[$table];
 283   }
 284
 285   /**
 286    * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
 287    */
 288   private function createLogTableFor($table) {
 289     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table");
 290     $dao->fetch();
 291     $query = $dao->Create_Table;
 292
 293     // rewrite the queries into CREATE TABLE queries for log tables:
 294     $cols = <<<COLS
 295             log_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 296             log_conn_id INTEGER,
 297             log_user_id INTEGER,
 298             log_action  ENUM('Initialization', 'Insert', 'Update', 'Delete')
 299 COLS;
 300
 301     // - prepend the name with log_
 302     // - drop AUTO_INCREMENT columns
 303     // - drop non-column rows of the query (keys, constraints, etc.)
 304     // - set the ENGINE to ARCHIVE
 305     // - add log-specific columns (at the end of the table)
 306     $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
 307     $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
 308     $query = preg_replace("/^  [^`].*$/m", '', $query);
 309     $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=ARCHIVE ', $query);
 310
 311     // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 312     // so there's no need for a default timestamp and therefore we remove such default timestamps
 313     // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 314     $query = self::fixTimeStampAndNotNullSQL($query);
 315     $query = preg_replace("/^\) /m", "$cols\n) ", $query);
 316
 317     CRM_Core_DAO::executeQuery($query);
 318
 319     $columns = implode(', ', $this->columnsOf($table));
 320     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}");
 321
 322     $this->tables[] = $table;
 323     $this->logs[$table] = "log_$table";
 324   }
 325
 326   private function deleteReports() {
 327     // disable logging templates
 328     CRM_Core_DAO::executeQuery("
 329             UPDATE civicrm_option_value
 330             SET is_active = 0
 331             WHERE value IN ('" . implode("', '", $this->reports) . "')
 332         ");
 333
 334     // delete report instances
 335     $domain_id = CRM_Core_Config::domainID();
 336     foreach ($this->reports as $report) {
 337       $dao            = new CRM_Report_DAO_Instance;
 338       $dao->domain_id = $domain_id;
 339       $dao->report_id = $report;
 340       $dao->delete();
 341     }
 342   }
 343
 344   /**
 345    * Predicate whether logging is enabled.
 346    */
 347   public function isEnabled() {
 348     $config = CRM_Core_Config::singleton();
 349
 350     if ($config->logging) {
 351       return $this->tablesExist() and $this->triggersExist();
 352     }
 353     return FALSE;
 354   }
 355
 356   /**
 357    * Predicate whether any log tables exist.
 358    */
 359   private function tablesExist() {
 360     return !empty($this->logs);
 361   }
 362
 363   /**
 364    * Predicate whether the logging triggers are in place.
 365    */
 366   private function triggersExist() {
 367     // FIXME: probably should be a bit more thorough…
 368     // note that the LIKE parameter is TABLE NAME
 369     return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
 370   }
 371
 372   function triggerInfo(&$info, $tableName = NULL) {
 373     // check if we have logging enabled
 374     $config =& CRM_Core_Config::singleton();
 375     if (!$config->logging) {
 376       return;
 377     }
 378
 379     $insert = array('INSERT');
 380     $update = array('UPDATE');
 381     $delete = array('DELETE');
 382
 383     if ($tableName) {
 384       $tableNames = array($tableName);
 385     }
 386     else {
 387       $tableNames = $this->tables;
 388     }
 389
 390     // logging is enabled, so now lets create the trigger info tables
 391     foreach ($tableNames as $table) {
 392       $columns = $this->columnsOf($table);
 393
 394       // only do the change if any data has changed
 395       $cond = array( );
 396       foreach ($columns as $column) {
 397         // ignore modified_date changes
 398         if ($column != 'modified_date') {
 399           $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
 400         }
 401       }
 402       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
 403       $updateSQL = "IF ( (" . implode( ' OR ', $cond ) . ") AND ( $suppressLoggingCond ) ) THEN ";
 404
 405       $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
 406       foreach ($columns as $column) {
 407         $sqlStmt .= "$column, ";
 408       }
 409       $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
 410
 411       $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
 412       $updateSQL .= $sqlStmt;
 413
 414       $sqlStmt = '';
 415       foreach ($columns as $column) {
 416         $sqlStmt   .= "NEW.$column, ";
 417         $deleteSQL .= "OLD.$column, ";
 418       }
 419       $sqlStmt   .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
 420       $deleteSQL .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
 421
 422       $sqlStmt   .= "END IF;";
 423       $deleteSQL .= "END IF;";
 424
 425       $insertSQL .= $sqlStmt;
 426       $updateSQL .= $sqlStmt;
 427
 428       $info[] = array(
 429         'table' => array($table),
 430         'when' => 'AFTER',
 431         'event' => $insert,
 432         'sql' => $insertSQL,
 433       );
 434
 435       $info[] = array(
 436         'table' => array($table),
 437         'when' => 'AFTER',
 438         'event' => $update,
 439         'sql' => $updateSQL,
 440       );
 441
 442       $info[] = array(
 443         'table' => array($table),
 444         'when' => 'AFTER',
 445         'event' => $delete,
 446         'sql' => $deleteSQL,
 447       );
 448     }
 449   }
 450
 451   /**
 452    * This allow logging to be temporarily disabled for certain cases
 453    * where we want to do a mass cleanup but dont want to bother with
 454    * an audit trail
 455    *
 456    * @static
 457    * @public
 458    */
 459   static function disableLoggingForThisConnection( ) {
 460     // do this only if logging is enabled
 461     $config = CRM_Core_Config::singleton( );
 462     if ( $config->logging ) {
 463       CRM_Core_DAO::executeQuery( 'SET @civicrm_disable_logging = 1' );
 464     }
 465   }
 466
 467 }
 468