CRM/Logging/Schema.php

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