CRM/Logging/Schema.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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-2016
  32  */
  33 class CRM_Logging_Schema {
  34   private $logs = array();
  35   private $tables = array();
  36
  37   private $db;
  38   private $useDBPrefix = TRUE;
  39
  40   private $reports = array(
  41     'logging/contact/detail',
  42     'logging/contact/summary',
  43     'logging/contribute/detail',
  44     'logging/contribute/summary',
  45   );
  46
  47   /**
  48    * Columns that should never be subject to logging.
  49    *
  50    * CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
  51    *
  52    * @var array
  53    */
  54   private $exceptions = array(
  55     'civicrm_job' => array('last_run'),
  56     'civicrm_group' => array('cache_date', 'refresh_date'),
  57   );
  58
  59   /**
  60    * Specifications of all log table including
  61    *  - engine (default is archive, if not set.)
  62    *  - indexes (default is none and they cannot be added unless engine is innodb. If they are added and
  63    *    engine is not set to innodb an exception will be thrown since quiet acquiescence is easier to miss).
  64    *  - exceptions (by default those stored in $this->exceptions are included). These are
  65    *    excluded from the triggers.
  66    *
  67    * @var array
  68    */
  69   private $logTableSpec = array();
  70
  71   /**
  72    * Setting Callback - Validate.
  73    *
  74    * @param mixed $value
  75    * @param array $fieldSpec
  76    *
  77    * @return bool
  78    * @throws API_Exception
  79    */
  80   public static function checkLoggingSupport(&$value, $fieldSpec) {
  81     $domain = new CRM_Core_DAO_Domain();
  82     $domain->find(TRUE);
  83     if (!(CRM_Core_DAO::checkTriggerViewPermission(FALSE)) && $value) {
  84       throw new API_Exception("In order to use this functionality, the installation's database user must have privileges to create triggers (in MySQL 5.0 – and in MySQL 5.1 if binary logging is enabled – this means the SUPER privilege). This install either does not seem to have the required privilege enabled.");
  85     }
  86     return TRUE;
  87   }
  88
  89   /**
  90    * Setting Callback - On Change.
  91    *
  92    * Respond to changes in the "logging" setting. Set up or destroy
  93    * triggers, etal.
  94    *
  95    * @param array $oldValue
  96    *   List of component names.
  97    * @param array $newValue
  98    *   List of component names.
  99    * @param array $metadata
 100    *   Specification of the setting (per *.settings.php).
 101    */
 102   public static function onToggle($oldValue, $newValue, $metadata) {
 103     if ($oldValue == $newValue) {
 104       return;
 105     }
 106
 107     $logging = new CRM_Logging_Schema();
 108     if ($newValue) {
 109       $logging->enableLogging();
 110     }
 111     else {
 112       $logging->disableLogging();
 113     }
 114   }
 115
 116   /**
 117    * Populate $this->tables and $this->logs with current db state.
 118    */
 119   public function __construct() {
 120     $dao = new CRM_Contact_DAO_Contact();
 121     $civiDBName = $dao->_database;
 122
 123     $dao = CRM_Core_DAO::executeQuery("
 124 SELECT TABLE_NAME
 125 FROM   INFORMATION_SCHEMA.TABLES
 126 WHERE  TABLE_SCHEMA = '{$civiDBName}'
 127 AND    TABLE_TYPE = 'BASE TABLE'
 128 AND    TABLE_NAME LIKE 'civicrm_%'
 129 ");
 130     while ($dao->fetch()) {
 131       $this->tables[] = $dao->TABLE_NAME;
 132     }
 133
 134     // do not log temp import, cache, menu and log tables
 135     $this->tables = preg_grep('/^civicrm_import_job_/', $this->tables, PREG_GREP_INVERT);
 136     $this->tables = preg_grep('/_cache$/', $this->tables, PREG_GREP_INVERT);
 137     $this->tables = preg_grep('/_log/', $this->tables, PREG_GREP_INVERT);
 138     $this->tables = preg_grep('/^civicrm_queue_/', $this->tables, PREG_GREP_INVERT);
 139     //CRM-14672
 140     $this->tables = preg_grep('/^civicrm_menu/', $this->tables, PREG_GREP_INVERT);
 141     $this->tables = preg_grep('/_temp_/', $this->tables, PREG_GREP_INVERT);
 142     // CRM-18178
 143     $this->tables = preg_grep('/_bak$/', $this->tables, PREG_GREP_INVERT);
 144     $this->tables = preg_grep('/_backup$/', $this->tables, PREG_GREP_INVERT);
 145
 146     // do not log civicrm_mailing_event* tables, CRM-12300
 147     $this->tables = preg_grep('/^civicrm_mailing_event_/', $this->tables, PREG_GREP_INVERT);
 148
 149     // do not log civicrm_mailing_recipients table, CRM-16193
 150     $this->tables = array_diff($this->tables, array('civicrm_mailing_recipients'));
 151     $this->logTableSpec = array_fill_keys($this->tables, array());
 152     foreach ($this->exceptions as $tableName => $fields) {
 153       $this->logTableSpec[$tableName]['exceptions'] = $fields;
 154     }
 155     CRM_Utils_Hook::alterLogTables($this->logTableSpec);
 156     $this->tables = array_keys($this->logTableSpec);
 157
 158     if (defined('CIVICRM_LOGGING_DSN')) {
 159       $dsn = DB::parseDSN(CIVICRM_LOGGING_DSN);
 160       $this->useDBPrefix = (CIVICRM_LOGGING_DSN != CIVICRM_DSN);
 161     }
 162     else {
 163       $dsn = DB::parseDSN(CIVICRM_DSN);
 164       $this->useDBPrefix = FALSE;
 165     }
 166     $this->db = $dsn['database'];
 167
 168     $dao = CRM_Core_DAO::executeQuery("
 169 SELECT TABLE_NAME
 170 FROM   INFORMATION_SCHEMA.TABLES
 171 WHERE  TABLE_SCHEMA = '{$this->db}'
 172 AND    TABLE_TYPE = 'BASE TABLE'
 173 AND    TABLE_NAME LIKE 'log_civicrm_%'
 174 ");
 175     while ($dao->fetch()) {
 176       $log = $dao->TABLE_NAME;
 177       $this->logs[substr($log, 4)] = $log;
 178     }
 179   }
 180
 181   /**
 182    * Return logging custom data tables.
 183    */
 184   public function customDataLogTables() {
 185     return preg_grep('/^log_civicrm_value_/', $this->logs);
 186   }
 187
 188   /**
 189    * Return custom data tables for specified entity / extends.
 190    *
 191    * @param string $extends
 192    *
 193    * @return array
 194    */
 195   public function entityCustomDataLogTables($extends) {
 196     $customGroupTables = array();
 197     $customGroupDAO = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity($extends);
 198     $customGroupDAO->find();
 199     while ($customGroupDAO->fetch()) {
 200       $customGroupTables[$customGroupDAO->table_name] = $this->logs[$customGroupDAO->table_name];
 201     }
 202     return $customGroupTables;
 203   }
 204
 205   /**
 206    * Disable logging by dropping the triggers (but keep the log tables intact).
 207    */
 208   public function disableLogging() {
 209     $config = CRM_Core_Config::singleton();
 210     $config->logging = FALSE;
 211
 212     $this->dropTriggers();
 213
 214     // invoke the meta trigger creation call
 215     CRM_Core_DAO::triggerRebuild();
 216
 217     $this->deleteReports();
 218   }
 219
 220   /**
 221    * Drop triggers for all logged tables.
 222    *
 223    * @param string $tableName
 224    */
 225   public function dropTriggers($tableName = NULL) {
 226     /** @var \Civi\Core\SqlTriggers $sqlTriggers */
 227     $sqlTriggers = Civi::service('sql_triggers');
 228     $dao = new CRM_Core_DAO();
 229
 230     if ($tableName) {
 231       $tableNames = array($tableName);
 232     }
 233     else {
 234       $tableNames = $this->tables;
 235     }
 236
 237     foreach ($tableNames as $table) {
 238       $validName = CRM_Core_DAO::shortenSQLName($table, 48, TRUE);
 239
 240       // before triggers
 241       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
 242       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
 243       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
 244
 245       // after triggers
 246       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
 247       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
 248       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
 249     }
 250
 251     // now lets also be safe and drop all triggers that start with
 252     // civicrm_ if we are dropping all triggers
 253     // we need to do this to capture all the leftover triggers since
 254     // we did the shortening trigger name for CRM-11794
 255     if ($tableName === NULL) {
 256       $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
 257
 258       while ($triggers->fetch()) {
 259         $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
 260       }
 261     }
 262   }
 263
 264   /**
 265    * Enable site-wide logging.
 266    */
 267   public function enableLogging() {
 268     $this->fixSchemaDifferences(TRUE);
 269     $this->addReports();
 270   }
 271
 272   /**
 273    * Sync log tables and rebuild triggers.
 274    *
 275    * @param bool $enableLogging : Ensure logging is enabled
 276    */
 277   public function fixSchemaDifferences($enableLogging = FALSE) {
 278     $config = CRM_Core_Config::singleton();
 279     if ($enableLogging) {
 280       $config->logging = TRUE;
 281     }
 282     if ($config->logging) {
 283       $this->fixSchemaDifferencesForALL();
 284     }
 285     // invoke the meta trigger creation call
 286     CRM_Core_DAO::triggerRebuild(NULL, TRUE);
 287   }
 288
 289   /**
 290    * Add missing (potentially specified) log table columns for the given table.
 291    *
 292    * @param string $table
 293    *   name of the relevant table.
 294    * @param array $cols
 295    *   Mixed array of columns to add or null (to check for the missing columns).
 296    * @param bool $rebuildTrigger
 297    *   should we rebuild the triggers.
 298    *
 299    * @return bool
 300    */
 301   public function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
 302     if (empty($table)) {
 303       return FALSE;
 304     }
 305     if (empty($this->logs[$table])) {
 306       $this->createLogTableFor($table);
 307       return TRUE;
 308     }
 309
 310     if (empty($cols)) {
 311       $cols = $this->columnsWithDiffSpecs($table, "log_$table");
 312     }
 313
 314     // use the relevant lines from CREATE TABLE to add colums to the log table
 315     $create = $this->_getCreateQuery($table);
 316     foreach ((array('ADD', 'MODIFY')) as $alterType) {
 317       if (!empty($cols[$alterType])) {
 318         foreach ($cols[$alterType] as $col) {
 319           $line = $this->_getColumnQuery($col, $create);
 320           CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}");
 321         }
 322       }
 323     }
 324
 325     // for any obsolete columns (not null) we just make the column nullable.
 326     if (!empty($cols['OBSOLETE'])) {
 327       $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
 328       foreach ($cols['OBSOLETE'] as $col) {
 329         $line = $this->_getColumnQuery($col, $create);
 330         // This is just going to make a not null column to nullable
 331         CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}");
 332       }
 333     }
 334
 335     if ($rebuildTrigger) {
 336       // invoke the meta trigger creation call
 337       CRM_Core_DAO::triggerRebuild($table);
 338     }
 339     return TRUE;
 340   }
 341
 342   /**
 343    * Get query table.
 344    *
 345    * @param string $table
 346    *
 347    * @return array
 348    */
 349   private function _getCreateQuery($table) {
 350     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}");
 351     $dao->fetch();
 352     $create = explode("\n", $dao->Create_Table);
 353     return $create;
 354   }
 355
 356   /**
 357    * Get column query.
 358    *
 359    * @param string $col
 360    * @param bool $createQuery
 361    *
 362    * @return array|mixed|string
 363    */
 364   private function _getColumnQuery($col, $createQuery) {
 365     $line = preg_grep("/^  `$col` /", $createQuery);
 366     $line = rtrim(array_pop($line), ',');
 367     // CRM-11179
 368     $line = $this->fixTimeStampAndNotNullSQL($line);
 369     return $line;
 370   }
 371
 372   /**
 373    * Fix schema differences.
 374    *
 375    * @param bool $rebuildTrigger
 376    */
 377   public function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
 378     $diffs = array();
 379     foreach ($this->tables as $table) {
 380       if (empty($this->logs[$table])) {
 381         $this->createLogTableFor($table);
 382       }
 383       else {
 384         $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
 385       }
 386     }
 387
 388     foreach ($diffs as $table => $cols) {
 389       $this->fixSchemaDifferencesFor($table, $cols, FALSE);
 390     }
 391
 392     if ($rebuildTrigger) {
 393       // invoke the meta trigger creation call
 394       CRM_Core_DAO::triggerRebuild($table);
 395     }
 396   }
 397
 398   /**
 399    * Fix timestamp.
 400    *
 401    * Log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 402    * so there's no need for a default timestamp and therefore we remove such default timestamps
 403    * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 404    *
 405    * @param string $query
 406    *
 407    * @return mixed
 408    */
 409   public function fixTimeStampAndNotNullSQL($query) {
 410     $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
 411     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
 412     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
 413     $query = str_ireplace("NOT NULL", '', $query);
 414     return $query;
 415   }
 416
 417   /**
 418    * Add reports.
 419    */
 420   private function addReports() {
 421     $titles = array(
 422       'logging/contact/detail' => ts('Logging Details'),
 423       'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
 424       'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
 425       'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
 426     );
 427     // enable logging templates
 428     CRM_Core_DAO::executeQuery("
 429             UPDATE civicrm_option_value
 430             SET is_active = 1
 431             WHERE value IN ('" . implode("', '", $this->reports) . "')
 432         ");
 433
 434     // add report instances
 435     $domain_id = CRM_Core_Config::domainID();
 436     foreach ($this->reports as $report) {
 437       $dao = new CRM_Report_DAO_ReportInstance();
 438       $dao->domain_id = $domain_id;
 439       $dao->report_id = $report;
 440       $dao->title = $titles[$report];
 441       $dao->permission = 'administer CiviCRM';
 442       if ($report == 'logging/contact/summary') {
 443         $dao->is_reserved = 1;
 444       }
 445       $dao->insert();
 446     }
 447   }
 448
 449   /**
 450    * Get an array of column names of the given table.
 451    *
 452    * @param string $table
 453    * @param bool $force
 454    *
 455    * @return array
 456    */
 457   private function columnsOf($table, $force = FALSE) {
 458     static $columnsOf = array();
 459
 460     $from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
 461
 462     if (!isset($columnsOf[$table]) || $force) {
 463       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
 464       $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 465       if (is_a($dao, 'DB_Error')) {
 466         return array();
 467       }
 468       $columnsOf[$table] = array();
 469       while ($dao->fetch()) {
 470         $columnsOf[$table][] = $dao->Field;
 471       }
 472     }
 473
 474     return $columnsOf[$table];
 475   }
 476
 477   /**
 478    * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
 479    *
 480    * @param string $table
 481    *
 482    * @return array
 483    */
 484   private function columnSpecsOf($table) {
 485     static $columnSpecs = array(), $civiDB = NULL;
 486
 487     if (empty($columnSpecs)) {
 488       if (!$civiDB) {
 489         $dao = new CRM_Contact_DAO_Contact();
 490         $civiDB = $dao->_database;
 491       }
 492       CRM_Core_TemporaryErrorScope::ignoreException();
 493       // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
 494       // than firing query for every given table.
 495       $query = "
 496 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE
 497 FROM   INFORMATION_SCHEMA.COLUMNS
 498 WHERE  table_schema IN ('{$this->db}', '{$civiDB}')";
 499       $dao = CRM_Core_DAO::executeQuery($query);
 500       if (is_a($dao, 'DB_Error')) {
 501         return array();
 502       }
 503       while ($dao->fetch()) {
 504         if (!array_key_exists($dao->TABLE_NAME, $columnSpecs)) {
 505           $columnSpecs[$dao->TABLE_NAME] = array();
 506         }
 507         $columnSpecs[$dao->TABLE_NAME][$dao->COLUMN_NAME] = array(
 508           'COLUMN_NAME' => $dao->COLUMN_NAME,
 509           'DATA_TYPE' => $dao->DATA_TYPE,
 510           'IS_NULLABLE' => $dao->IS_NULLABLE,
 511           'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT,
 512         );
 513         if (($first = strpos($dao->COLUMN_TYPE, '(')) != 0) {
 514           $columnSpecs[$dao->TABLE_NAME][$dao->COLUMN_NAME]['LENGTH'] = substr($dao->COLUMN_TYPE, $first, strpos($dao->COLUMN_TYPE, ')'));
 515         }
 516       }
 517     }
 518     return $columnSpecs[$table];
 519   }
 520
 521   /**
 522    * Get columns that have changed.
 523    *
 524    * @param string $civiTable
 525    * @param string $logTable
 526    *
 527    * @return array
 528    */
 529   public function columnsWithDiffSpecs($civiTable, $logTable) {
 530     $civiTableSpecs = $this->columnSpecsOf($civiTable);
 531     $logTableSpecs = $this->columnSpecsOf($logTable);
 532
 533     $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
 534
 535     // columns to be added
 536     $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
 537
 538     // columns to be modified
 539     // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
 540     // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
 541     foreach ($civiTableSpecs as $col => $colSpecs) {
 542       if (!isset($logTableSpecs[$col]) || !is_array($logTableSpecs[$col])) {
 543         $logTableSpecs[$col] = array();
 544       }
 545
 546       $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
 547       if (!empty($specDiff) && $col != 'id' && !array_key_exists($col, $diff['ADD'])) {
 548         // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
 549         if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array::value('DATA_TYPE', $logTableSpecs[$col])
 550         // We won't alter the log if the length is decreased in case some of the existing data won't fit.
 551         || CRM_Utils_Array::value('LENGTH', $civiTableSpecs[$col]) > CRM_Utils_Array::value('LENGTH', $logTableSpecs[$col])
 552         ) {
 553           // if data-type is different, surely consider the column
 554           $diff['MODIFY'][] = $col;
 555         }
 556         elseif ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array::value('IS_NULLABLE', $logTableSpecs[$col]) &&
 557           $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 558         ) {
 559           // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
 560           $diff['MODIFY'][] = $col;
 561         }
 562         elseif ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
 563           !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')
 564         ) {
 565           // if default property is different, and its not about a timestamp column, consider it
 566           $diff['MODIFY'][] = $col;
 567         }
 568       }
 569     }
 570
 571     // columns to made obsolete by turning into not-null
 572     $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
 573     foreach ($oldCols as $col) {
 574       if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
 575         $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 576       ) {
 577         // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
 578         $diff['OBSOLETE'][] = $col;
 579       }
 580     }
 581
 582     return $diff;
 583   }
 584
 585   /**
 586    * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
 587    *
 588    * @param string $table
 589    */
 590   private function createLogTableFor($table) {
 591     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 592     $dao->fetch();
 593     $query = $dao->Create_Table;
 594
 595     // rewrite the queries into CREATE TABLE queries for log tables:
 596     $cols = <<<COLS
 597             ,
 598             log_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 599             log_conn_id INTEGER,
 600             log_user_id INTEGER,
 601             log_action  ENUM('Initialization', 'Insert', 'Update', 'Delete')
 602 COLS;
 603
 604     if (!empty($this->logTableSpec[$table]['indexes'])) {
 605       foreach ($this->logTableSpec[$table]['indexes'] as $indexName => $indexSpec) {
 606         if (is_array($indexSpec)) {
 607           $indexSpec = implode(" , ", $indexSpec);
 608         }
 609         $cols .= ", INDEX {$indexName}($indexSpec)";
 610       }
 611     }
 612
 613     // - prepend the name with log_
 614     // - drop AUTO_INCREMENT columns
 615     // - drop non-column rows of the query (keys, constraints, etc.)
 616     // - set the ENGINE to ARCHIVE
 617     // - add log-specific columns (at the end of the table)
 618     $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
 619     $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
 620     $query = preg_replace("/^  [^`].*$/m", '', $query);
 621     $engine = strtoupper(CRM_Utils_Array::value('engine', $this->logTableSpec[$table], 'ARCHIVE'));
 622     $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=' . $engine . ' ', $query);
 623
 624     // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 625     // so there's no need for a default timestamp and therefore we remove such default timestamps
 626     // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 627     $query = self::fixTimeStampAndNotNullSQL($query);
 628     $query = preg_replace("/(,*\n*\) )ENGINE/m", "$cols\n) ENGINE", $query);
 629
 630     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 631
 632     $columns = implode(', ', $this->columnsOf($table));
 633     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}", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 634
 635     $this->tables[] = $table;
 636     $this->logs[$table] = "log_$table";
 637   }
 638
 639   /**
 640    * Delete reports.
 641    */
 642   private function deleteReports() {
 643     // disable logging templates
 644     CRM_Core_DAO::executeQuery("
 645             UPDATE civicrm_option_value
 646             SET is_active = 0
 647             WHERE value IN ('" . implode("', '", $this->reports) . "')
 648         ");
 649
 650     // delete report instances
 651     $domain_id = CRM_Core_Config::domainID();
 652     foreach ($this->reports as $report) {
 653       $dao = new CRM_Report_DAO_ReportInstance();
 654       $dao->domain_id = $domain_id;
 655       $dao->report_id = $report;
 656       $dao->delete();
 657     }
 658   }
 659
 660   /**
 661    * Predicate whether logging is enabled.
 662    */
 663   public function isEnabled() {
 664     $config = CRM_Core_Config::singleton();
 665
 666     if ($config->logging) {
 667       return $this->tablesExist() and $this->triggersExist();
 668     }
 669     return FALSE;
 670   }
 671
 672   /**
 673    * Predicate whether any log tables exist.
 674    */
 675   private function tablesExist() {
 676     return !empty($this->logs);
 677   }
 678
 679   /**
 680    * Predicate whether the logging triggers are in place.
 681    */
 682   private function triggersExist() {
 683     // FIXME: probably should be a bit more thorough…
 684     // note that the LIKE parameter is TABLE NAME
 685     return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
 686   }
 687
 688   /**
 689    * Get trigger info.
 690    *
 691    * @param array $info
 692    * @param null $tableName
 693    * @param bool $force
 694    */
 695   public function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
 696     if (!CRM_Core_Config::singleton()->logging) {
 697       return;
 698     }
 699
 700     $insert = array('INSERT');
 701     $update = array('UPDATE');
 702     $delete = array('DELETE');
 703
 704     if ($tableName) {
 705       $tableNames = array($tableName);
 706     }
 707     else {
 708       $tableNames = $this->tables;
 709     }
 710
 711     // logging is enabled, so now lets create the trigger info tables
 712     foreach ($tableNames as $table) {
 713       $columns = $this->columnsOf($table, $force);
 714
 715       // only do the change if any data has changed
 716       $cond = array();
 717       foreach ($columns as $column) {
 718         $tableExceptions = array_key_exists('exceptions', $this->logTableSpec[$table]) ? $this->logTableSpec[$table]['exceptions'] : array();
 719         // ignore modified_date changes
 720         if ($column != 'modified_date' && !in_array($column, $tableExceptions)) {
 721           $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
 722         }
 723       }
 724       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
 725       $updateSQL = "IF ( (" . implode(' OR ', $cond) . ") AND ( $suppressLoggingCond ) ) THEN ";
 726
 727       if ($this->useDBPrefix) {
 728         $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
 729       }
 730       else {
 731         $sqlStmt = "INSERT INTO log_{tableName} (";
 732       }
 733       foreach ($columns as $column) {
 734         $sqlStmt .= "$column, ";
 735       }
 736       $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
 737
 738       $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
 739       $updateSQL .= $sqlStmt;
 740
 741       $sqlStmt = '';
 742       foreach ($columns as $column) {
 743         $sqlStmt .= "NEW.$column, ";
 744         $deleteSQL .= "OLD.$column, ";
 745       }
 746       $sqlStmt .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
 747       $deleteSQL .= "CONNECTION_ID(), @civicrm_user_id, '{eventName}');";
 748
 749       $sqlStmt .= "END IF;";
 750       $deleteSQL .= "END IF;";
 751
 752       $insertSQL .= $sqlStmt;
 753       $updateSQL .= $sqlStmt;
 754
 755       $info[] = array(
 756         'table' => array($table),
 757         'when' => 'AFTER',
 758         'event' => $insert,
 759         'sql' => $insertSQL,
 760       );
 761
 762       $info[] = array(
 763         'table' => array($table),
 764         'when' => 'AFTER',
 765         'event' => $update,
 766         'sql' => $updateSQL,
 767       );
 768
 769       $info[] = array(
 770         'table' => array($table),
 771         'when' => 'AFTER',
 772         'event' => $delete,
 773         'sql' => $deleteSQL,
 774       );
 775     }
 776   }
 777
 778   /**
 779    * Disable logging temporarily.
 780    *
 781    * This allow logging to be temporarily disabled for certain cases
 782    * where we want to do a mass cleanup but do not want to bother with
 783    * an audit trail.
 784    */
 785   public static function disableLoggingForThisConnection() {
 786     if (CRM_Core_Config::singleton()->logging) {
 787       CRM_Core_DAO::executeQuery('SET @civicrm_disable_logging = 1');
 788     }
 789   }
 790
 791 }