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