CRM/Logging/Schema.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2018                                |
   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-2018
  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    *  - engine_config, a string appended to the engine type.
  63    *    For INNODB  space can be saved with 'ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4'
  64    *  - indexes (default is none and they cannot be added unless engine is innodb. If they are added and
  65    *    engine is not set to innodb an exception will be thrown since quiet acquiescence is easier to miss).
  66    *  - exceptions (by default those stored in $this->exceptions are included). These are
  67    *    excluded from the triggers.
  68    *
  69    * @var array
  70    */
  71   private $logTableSpec = array();
  72
  73   /**
  74    * Setting Callback - Validate.
  75    *
  76    * @param mixed $value
  77    * @param array $fieldSpec
  78    *
  79    * @return bool
  80    * @throws API_Exception
  81    */
  82   public static function checkLoggingSupport(&$value, $fieldSpec) {
  83     $domain = new CRM_Core_DAO_Domain();
  84     $domain->find(TRUE);
  85     if (!(CRM_Core_DAO::checkTriggerViewPermission(FALSE)) && $value) {
  86       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.");
  87     }
  88     return TRUE;
  89   }
  90
  91   /**
  92    * Setting Callback - On Change.
  93    *
  94    * Respond to changes in the "logging" setting. Set up or destroy
  95    * triggers, etal.
  96    *
  97    * @param array $oldValue
  98    *   List of component names.
  99    * @param array $newValue
 100    *   List of component names.
 101    * @param array $metadata
 102    *   Specification of the setting (per *.settings.php).
 103    */
 104   public static function onToggle($oldValue, $newValue, $metadata) {
 105     if ($oldValue == $newValue) {
 106       return;
 107     }
 108
 109     $logging = new CRM_Logging_Schema();
 110     if ($newValue) {
 111       $logging->enableLogging();
 112     }
 113     else {
 114       $logging->disableLogging();
 115     }
 116   }
 117
 118   /**
 119    * Populate $this->tables and $this->logs with current db state.
 120    */
 121   public function __construct() {
 122     $dao = new CRM_Contact_DAO_Contact();
 123     $civiDBName = $dao->_database;
 124
 125     $dao = CRM_Core_DAO::executeQuery("
 126 SELECT TABLE_NAME
 127 FROM   INFORMATION_SCHEMA.TABLES
 128 WHERE  TABLE_SCHEMA = '{$civiDBName}'
 129 AND    TABLE_TYPE = 'BASE TABLE'
 130 AND    TABLE_NAME LIKE 'civicrm_%'
 131 ");
 132     while ($dao->fetch()) {
 133       $this->tables[] = $dao->TABLE_NAME;
 134     }
 135
 136     // do not log temp import, cache, menu and log tables
 137     $this->tables = preg_grep('/^civicrm_import_job_/', $this->tables, PREG_GREP_INVERT);
 138     $this->tables = preg_grep('/_cache$/', $this->tables, PREG_GREP_INVERT);
 139     $this->tables = preg_grep('/_log/', $this->tables, PREG_GREP_INVERT);
 140     $this->tables = preg_grep('/^civicrm_queue_/', $this->tables, PREG_GREP_INVERT);
 141     //CRM-14672
 142     $this->tables = preg_grep('/^civicrm_menu/', $this->tables, PREG_GREP_INVERT);
 143     $this->tables = preg_grep('/_temp_/', $this->tables, PREG_GREP_INVERT);
 144     // CRM-18178
 145     $this->tables = preg_grep('/_bak$/', $this->tables, PREG_GREP_INVERT);
 146     $this->tables = preg_grep('/_backup$/', $this->tables, PREG_GREP_INVERT);
 147
 148     // do not log civicrm_mailing_event* tables, CRM-12300
 149     $this->tables = preg_grep('/^civicrm_mailing_event_/', $this->tables, PREG_GREP_INVERT);
 150
 151     // do not log civicrm_mailing_recipients table, CRM-16193
 152     $this->tables = array_diff($this->tables, array('civicrm_mailing_recipients'));
 153     $this->logTableSpec = array_fill_keys($this->tables, array());
 154     foreach ($this->exceptions as $tableName => $fields) {
 155       $this->logTableSpec[$tableName]['exceptions'] = $fields;
 156     }
 157     CRM_Utils_Hook::alterLogTables($this->logTableSpec);
 158     $this->tables = array_keys($this->logTableSpec);
 159     $nonStandardTableNameString = $this->getNonStandardTableNameFilterString();
 160
 161     if (defined('CIVICRM_LOGGING_DSN')) {
 162       $dsn = DB::parseDSN(CIVICRM_LOGGING_DSN);
 163       $this->useDBPrefix = (CIVICRM_LOGGING_DSN != CIVICRM_DSN);
 164     }
 165     else {
 166       $dsn = DB::parseDSN(CIVICRM_DSN);
 167       $this->useDBPrefix = FALSE;
 168     }
 169     $this->db = $dsn['database'];
 170
 171     $dao = CRM_Core_DAO::executeQuery("
 172 SELECT TABLE_NAME
 173 FROM   INFORMATION_SCHEMA.TABLES
 174 WHERE  TABLE_SCHEMA = '{$this->db}'
 175 AND    TABLE_TYPE = 'BASE TABLE'
 176 AND    (TABLE_NAME LIKE 'log_civicrm_%' $nonStandardTableNameString )
 177 ");
 178     while ($dao->fetch()) {
 179       $log = $dao->TABLE_NAME;
 180       $this->logs[substr($log, 4)] = $log;
 181     }
 182   }
 183
 184   /**
 185    * Return logging custom data tables.
 186    */
 187   public function customDataLogTables() {
 188     return preg_grep('/^log_civicrm_value_/', $this->logs);
 189   }
 190
 191   /**
 192    * Return custom data tables for specified entity / extends.
 193    *
 194    * @param string $extends
 195    *
 196    * @return array
 197    */
 198   public function entityCustomDataLogTables($extends) {
 199     $customGroupTables = array();
 200     $customGroupDAO = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity($extends);
 201     $customGroupDAO->find();
 202     while ($customGroupDAO->fetch()) {
 203       // logging is disabled for the table (e.g by hook) then $this->logs[$customGroupDAO->table_name]
 204       // will be empty.
 205       if (!empty($this->logs[$customGroupDAO->table_name])) {
 206         $customGroupTables[$customGroupDAO->table_name] = $this->logs[$customGroupDAO->table_name];
 207       }
 208     }
 209     return $customGroupTables;
 210   }
 211
 212   /**
 213    * Disable logging by dropping the triggers (but keep the log tables intact).
 214    */
 215   public function disableLogging() {
 216     $config = CRM_Core_Config::singleton();
 217     $config->logging = FALSE;
 218
 219     $this->dropTriggers();
 220
 221     // invoke the meta trigger creation call
 222     CRM_Core_DAO::triggerRebuild();
 223
 224     $this->deleteReports();
 225   }
 226
 227   /**
 228    * Drop triggers for all logged tables.
 229    *
 230    * @param string $tableName
 231    */
 232   public function dropTriggers($tableName = NULL) {
 233     /** @var \Civi\Core\SqlTriggers $sqlTriggers */
 234     $sqlTriggers = Civi::service('sql_triggers');
 235     $dao = new CRM_Core_DAO();
 236
 237     if ($tableName) {
 238       $tableNames = array($tableName);
 239     }
 240     else {
 241       $tableNames = $this->tables;
 242     }
 243
 244     foreach ($tableNames as $table) {
 245       $validName = CRM_Core_DAO::shortenSQLName($table, 48, TRUE);
 246
 247       // before triggers
 248       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
 249       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
 250       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
 251
 252       // after triggers
 253       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
 254       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
 255       $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
 256     }
 257
 258     // now lets also be safe and drop all triggers that start with
 259     // civicrm_ if we are dropping all triggers
 260     // we need to do this to capture all the leftover triggers since
 261     // we did the shortening trigger name for CRM-11794
 262     if ($tableName === NULL) {
 263       $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
 264
 265       while ($triggers->fetch()) {
 266         $sqlTriggers->enqueueQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
 267       }
 268     }
 269   }
 270
 271   /**
 272    * Enable site-wide logging.
 273    */
 274   public function enableLogging() {
 275     $this->fixSchemaDifferences(TRUE);
 276     $this->addReports();
 277   }
 278
 279   /**
 280    * Sync log tables and rebuild triggers.
 281    *
 282    * @param bool $enableLogging : Ensure logging is enabled
 283    */
 284   public function fixSchemaDifferences($enableLogging = FALSE) {
 285     $config = CRM_Core_Config::singleton();
 286     if ($enableLogging) {
 287       $config->logging = TRUE;
 288     }
 289     if ($config->logging) {
 290       $this->fixSchemaDifferencesForALL();
 291     }
 292     // invoke the meta trigger creation call
 293     CRM_Core_DAO::triggerRebuild(NULL, TRUE);
 294   }
 295
 296   /**
 297    * Update log tables structure.
 298    *
 299    * This function updates log tables to have the log_conn_id type of varchar
 300    * and also implements any engine change to INNODB defined by the hooks.
 301    *
 302    * Note changing engine & adding hook-defined indexes, but not changing back
 303    * to ARCHIVE if engine has not been deliberately set (by hook) and not dropping
 304    * indexes. Sysadmin will need to manually intervene to revert to defaults.
 305    */
 306   public function updateLogTableSchema() {
 307     $updateLogConn = FALSE;
 308     foreach ($this->logs as $mainTable => $logTable) {
 309       $alterSql = array();
 310       $tableSpec = $this->logTableSpec[$mainTable];
 311       if (isset($tableSpec['engine']) && strtoupper($tableSpec['engine']) != $this->getEngineForLogTable($logTable)) {
 312         $alterSql[] = "ENGINE=" . $tableSpec['engine'] . " " . CRM_Utils_Array::value('engine_config', $tableSpec);
 313         if (!empty($tableSpec['indexes'])) {
 314           $indexes = $this->getIndexesForTable($logTable);
 315           foreach ($tableSpec['indexes'] as $indexName => $indexSpec) {
 316             if (!in_array($indexName, $indexes)) {
 317               if (is_array($indexSpec)) {
 318                 $indexSpec = implode(" , ", $indexSpec);
 319               }
 320               $alterSql[] = "ADD INDEX {$indexName}($indexSpec)";
 321             }
 322           }
 323         }
 324       }
 325       $columns = $this->columnSpecsOf($logTable);
 326       if (empty($columns['log_conn_id'])) {
 327         throw new Exception($logTable . print_r($columns, TRUE));
 328       }
 329       if ($columns['log_conn_id']['DATA_TYPE'] != 'varchar' || $columns['log_conn_id']['LENGTH'] != 17) {
 330         $alterSql[] = "MODIFY log_conn_id VARCHAR(17)";
 331         $updateLogConn = TRUE;
 332       }
 333       if (!empty($alterSql)) {
 334         CRM_Core_DAO::executeQuery("ALTER TABLE {$this->db}.{$logTable} " . implode(', ', $alterSql));
 335       }
 336     }
 337     if ($updateLogConn) {
 338       civicrm_api3('Setting', 'create', array('logging_uniqueid_date' => date('Y-m-d H:i:s')));
 339     }
 340   }
 341
 342   /**
 343    * Get the engine for the given table.
 344    *
 345    * @param string $table
 346    *
 347    * @return string
 348    */
 349   public function getEngineForLogTable($table) {
 350     return strtoupper(CRM_Core_DAO::singleValueQuery("
 351       SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = %1
 352       AND table_schema = %2
 353     ", array(1 => array($table, 'String'), 2 => array($this->db, 'String'))));
 354   }
 355
 356   /**
 357    * Get all the indexes in the table.
 358    *
 359    * @param string $table
 360    *
 361    * @return array
 362    */
 363   public function getIndexesForTable($table) {
 364     return CRM_Core_DAO::executeQuery("
 365       SELECT constraint_name
 366       FROM information_schema.key_column_usage
 367       WHERE table_schema = %2 AND table_name = %1",
 368       array(1 => array($table, 'String'), 2 => array($this->db, 'String'))
 369     )->fetchAll();
 370   }
 371
 372   /**
 373    * Add missing (potentially specified) log table columns for the given table.
 374    *
 375    * @param string $table
 376    *   name of the relevant table.
 377    * @param array $cols
 378    *   Mixed array of columns to add or null (to check for the missing columns).
 379    * @param bool $rebuildTrigger
 380    *   should we rebuild the triggers.
 381    *
 382    * @return bool
 383    */
 384   public function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
 385     if (empty($table)) {
 386       return FALSE;
 387     }
 388     if (empty($this->logs[$table])) {
 389       $this->createLogTableFor($table);
 390       return TRUE;
 391     }
 392
 393     if (empty($cols)) {
 394       $cols = $this->columnsWithDiffSpecs($table, "log_$table");
 395     }
 396
 397     // use the relevant lines from CREATE TABLE to add colums to the log table
 398     $create = $this->_getCreateQuery($table);
 399     foreach ((array('ADD', 'MODIFY')) as $alterType) {
 400       if (!empty($cols[$alterType])) {
 401         foreach ($cols[$alterType] as $col) {
 402           $line = $this->_getColumnQuery($col, $create);
 403           CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}", array(), TRUE, NULL, FALSE, FALSE);
 404         }
 405       }
 406     }
 407
 408     // for any obsolete columns (not null) we just make the column nullable.
 409     if (!empty($cols['OBSOLETE'])) {
 410       $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
 411       foreach ($cols['OBSOLETE'] as $col) {
 412         $line = $this->_getColumnQuery($col, $create);
 413         // This is just going to make a not null column to nullable
 414         CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}", array(), TRUE, NULL, FALSE, FALSE);
 415       }
 416     }
 417
 418     if ($rebuildTrigger) {
 419       // invoke the meta trigger creation call
 420       CRM_Core_DAO::triggerRebuild($table);
 421     }
 422     return TRUE;
 423   }
 424
 425   /**
 426    * Get query table.
 427    *
 428    * @param string $table
 429    *
 430    * @return array
 431    */
 432   private function _getCreateQuery($table) {
 433     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}", array(), TRUE, NULL, FALSE, FALSE);
 434     $dao->fetch();
 435     $create = explode("\n", $dao->Create_Table);
 436     return $create;
 437   }
 438
 439   /**
 440    * Get column query.
 441    *
 442    * @param string $col
 443    * @param bool $createQuery
 444    *
 445    * @return array|mixed|string
 446    */
 447   private function _getColumnQuery($col, $createQuery) {
 448     $line = preg_grep("/^  `$col` /", $createQuery);
 449     $line = rtrim(array_pop($line), ',');
 450     // CRM-11179
 451     $line = self::fixTimeStampAndNotNullSQL($line);
 452     return $line;
 453   }
 454
 455   /**
 456    * Fix schema differences.
 457    *
 458    * @param bool $rebuildTrigger
 459    */
 460   public function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
 461     $diffs = array();
 462     foreach ($this->tables as $table) {
 463       if (empty($this->logs[$table])) {
 464         $this->createLogTableFor($table);
 465       }
 466       else {
 467         $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
 468       }
 469     }
 470
 471     foreach ($diffs as $table => $cols) {
 472       $this->fixSchemaDifferencesFor($table, $cols, FALSE);
 473     }
 474     if ($rebuildTrigger) {
 475       // invoke the meta trigger creation call
 476       CRM_Core_DAO::triggerRebuild(NULL, TRUE);
 477     }
 478   }
 479
 480   /**
 481    * Fix timestamp.
 482    *
 483    * Log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 484    * so there's no need for a default timestamp and therefore we remove such default timestamps
 485    * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 486    *
 487    * @param string $query
 488    *
 489    * @return mixed
 490    */
 491   public static function fixTimeStampAndNotNullSQL($query) {
 492     $query = str_ireplace("TIMESTAMP() NOT NULL", "TIMESTAMP NULL", $query);
 493     $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
 494     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()", '', $query);
 495     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
 496     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP()", '', $query);
 497     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
 498     $query = str_ireplace("NOT NULL", '', $query);
 499     return $query;
 500   }
 501
 502   /**
 503    * Add reports.
 504    */
 505   private function addReports() {
 506     $titles = array(
 507       'logging/contact/detail' => ts('Logging Details'),
 508       'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
 509       'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
 510       'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
 511     );
 512     // enable logging templates
 513     CRM_Core_DAO::executeQuery("
 514             UPDATE civicrm_option_value
 515             SET is_active = 1
 516             WHERE value IN ('" . implode("', '", $this->reports) . "')
 517         ");
 518
 519     // add report instances
 520     $domain_id = CRM_Core_Config::domainID();
 521     foreach ($this->reports as $report) {
 522       $dao = new CRM_Report_DAO_ReportInstance();
 523       $dao->domain_id = $domain_id;
 524       $dao->report_id = $report;
 525       $dao->title = $titles[$report];
 526       $dao->permission = 'administer CiviCRM';
 527       if ($report == 'logging/contact/summary') {
 528         $dao->is_reserved = 1;
 529       }
 530       $dao->insert();
 531     }
 532   }
 533
 534   /**
 535    * Get an array of column names of the given table.
 536    *
 537    * @param string $table
 538    * @param bool $force
 539    *
 540    * @return array
 541    */
 542   private function columnsOf($table, $force = FALSE) {
 543     if ($force || !isset(\Civi::$statics[__CLASS__]['columnsOf'][$table])) {
 544       $from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
 545       CRM_Core_TemporaryErrorScope::ignoreException();
 546       $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 547       if (is_a($dao, 'DB_Error')) {
 548         return array();
 549       }
 550       \Civi::$statics[__CLASS__]['columnsOf'][$table] = array();
 551       while ($dao->fetch()) {
 552         \Civi::$statics[__CLASS__]['columnsOf'][$table][] = CRM_Utils_type::escape($dao->Field, 'MysqlColumnNameOrAlias');
 553       }
 554     }
 555     return \Civi::$statics[__CLASS__]['columnsOf'][$table];
 556   }
 557
 558   /**
 559    * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
 560    *
 561    * @param string $table
 562    *
 563    * @return array
 564    */
 565   private function columnSpecsOf($table) {
 566     static $civiDB = NULL;
 567     if (empty(\Civi::$statics[__CLASS__]['columnSpecs'])) {
 568       \Civi::$statics[__CLASS__]['columnSpecs'] = array();
 569     }
 570     if (empty(\Civi::$statics[__CLASS__]['columnSpecs']) || !isset(\Civi::$statics[__CLASS__]['columnSpecs'][$table])) {
 571       if (!$civiDB) {
 572         $dao = new CRM_Contact_DAO_Contact();
 573         $civiDB = $dao->_database;
 574       }
 575       CRM_Core_TemporaryErrorScope::ignoreException();
 576       // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
 577       // than firing query for every given table.
 578       $query = "
 579 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, EXTRA
 580 FROM   INFORMATION_SCHEMA.COLUMNS
 581 WHERE  table_schema IN ('{$this->db}', '{$civiDB}')";
 582       $dao = CRM_Core_DAO::executeQuery($query);
 583       if (is_a($dao, 'DB_Error')) {
 584         return array();
 585       }
 586       while ($dao->fetch()) {
 587         if (!array_key_exists($dao->TABLE_NAME, \Civi::$statics[__CLASS__]['columnSpecs'])) {
 588           \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME] = array();
 589         }
 590         \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME] = array(
 591           'COLUMN_NAME' => $dao->COLUMN_NAME,
 592           'DATA_TYPE' => $dao->DATA_TYPE,
 593           'IS_NULLABLE' => $dao->IS_NULLABLE,
 594           'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT,
 595           'EXTRA' => $dao->EXTRA,
 596         );
 597         if (($first = strpos($dao->COLUMN_TYPE, '(')) != 0) {
 598           \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME]['LENGTH'] = substr(
 599             $dao->COLUMN_TYPE, $first, strpos($dao->COLUMN_TYPE, ')')
 600           );
 601         }
 602       }
 603     }
 604     return \Civi::$statics[__CLASS__]['columnSpecs'][$table];
 605   }
 606
 607   /**
 608    * Get columns that have changed.
 609    *
 610    * @param string $civiTable
 611    * @param string $logTable
 612    *
 613    * @return array
 614    */
 615   public function columnsWithDiffSpecs($civiTable, $logTable) {
 616     $civiTableSpecs = $this->columnSpecsOf($civiTable);
 617     $logTableSpecs = $this->columnSpecsOf($logTable);
 618
 619     $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
 620     // columns to be added
 621     $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
 622     // columns to be modified
 623     // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
 624     // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
 625     foreach ($civiTableSpecs as $col => $colSpecs) {
 626       if (!isset($logTableSpecs[$col]) || !is_array($logTableSpecs[$col])) {
 627         $logTableSpecs[$col] = array();
 628       }
 629       $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
 630       if (!empty($specDiff) && $col != 'id' && !in_array($col, $diff['ADD'])) {
 631         if (empty($colSpecs['EXTRA']) || (!empty($colSpecs['EXTRA']) && $colSpecs['EXTRA'] !== 'auto_increment')) {
 632           // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
 633           if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array::value('DATA_TYPE', $logTableSpecs[$col])
 634           // We won't alter the log if the length is decreased in case some of the existing data won't fit.
 635           || CRM_Utils_Array::value('LENGTH', $civiTableSpecs[$col]) > CRM_Utils_Array::value('LENGTH', $logTableSpecs[$col])
 636           ) {
 637             // if data-type is different, surely consider the column
 638             $diff['MODIFY'][] = $col;
 639           }
 640           elseif ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array::value('IS_NULLABLE', $logTableSpecs[$col]) &&
 641             $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 642           ) {
 643             // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
 644             $diff['MODIFY'][] = $col;
 645           }
 646           elseif ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
 647             !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')
 648           ) {
 649             // if default property is different, and its not about a timestamp column, consider it
 650             $diff['MODIFY'][] = $col;
 651           }
 652         }
 653       }
 654     }
 655
 656     // columns to made obsolete by turning into not-null
 657     $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
 658     foreach ($oldCols as $col) {
 659       if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
 660         $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 661       ) {
 662         // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
 663         $diff['OBSOLETE'][] = $col;
 664       }
 665     }
 666
 667     return $diff;
 668   }
 669
 670   /**
 671    * Getter for logTableSpec.
 672    *
 673    * @return array
 674    */
 675   public function getLogTableSpec() {
 676     return $this->logTableSpec;
 677   }
 678
 679   /**
 680    * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
 681    *
 682    * @param string $table
 683    */
 684   private function createLogTableFor($table) {
 685     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 686     $dao->fetch();
 687     $query = $dao->Create_Table;
 688
 689     // rewrite the queries into CREATE TABLE queries for log tables:
 690     $cols = <<<COLS
 691             ,
 692             log_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 693             log_conn_id VARCHAR(17),
 694             log_user_id INTEGER,
 695             log_action  ENUM('Initialization', 'Insert', 'Update', 'Delete')
 696 COLS;
 697
 698     if (!empty($this->logTableSpec[$table]['indexes'])) {
 699       foreach ($this->logTableSpec[$table]['indexes'] as $indexName => $indexSpec) {
 700         if (is_array($indexSpec)) {
 701           $indexSpec = implode(" , ", $indexSpec);
 702         }
 703         $cols .= ", INDEX {$indexName}($indexSpec)";
 704       }
 705     }
 706
 707     // - prepend the name with log_
 708     // - drop AUTO_INCREMENT columns
 709     // - drop non-column rows of the query (keys, constraints, etc.)
 710     // - set the ENGINE to the specified engine (default is archive or if archive is disabled or nor installed INNODB)
 711     // - add log-specific columns (at the end of the table)
 712     $mysqlEngines = [];
 713     $engines = CRM_Core_DAO::executeQuery("SHOW ENGINES");
 714     while ($engines->fetch()) {
 715       if ($engines->Support == 'YES' || $engines->Support == 'DEFAULT') {
 716         $mysqlEngines[] = $engines->Engine;
 717       }
 718     }
 719     $logEngine = in_array('ARCHIVE', $mysqlEngines) ? 'ARCHIVE' : 'INNODB';
 720     $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
 721     $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
 722     $query = preg_replace("/^  [^`].*$/m", '', $query);
 723     $engine = strtoupper(CRM_Utils_Array::value('engine', $this->logTableSpec[$table], $logEngine));
 724     $engine .= " " . CRM_Utils_Array::value('engine_config', $this->logTableSpec[$table]);
 725     $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=' . $engine . ' ', $query);
 726
 727     // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 728     // so there's no need for a default timestamp and therefore we remove such default timestamps
 729     // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 730     $query = self::fixTimeStampAndNotNullSQL($query);
 731     $query = preg_replace("/(,*\n*\) )ENGINE/m", "$cols\n) ENGINE", $query);
 732
 733     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 734
 735     $columns = implode(', ', $this->columnsOf($table));
 736     CRM_Core_DAO::executeQuery("INSERT INTO `{$this->db}`.log_$table ($columns, log_conn_id, log_user_id, log_action) SELECT $columns, @uniqueID, @civicrm_user_id, 'Initialization' FROM {$table}", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 737
 738     $this->tables[] = $table;
 739     if (empty($this->logs)) {
 740       civicrm_api3('Setting', 'create', array('logging_uniqueid_date' => date('Y-m-d H:i:s')));
 741       civicrm_api3('Setting', 'create', array('logging_all_tables_uniquid' => 1));
 742     }
 743     $this->logs[$table] = "log_$table";
 744   }
 745
 746   /**
 747    * Delete reports.
 748    */
 749   private function deleteReports() {
 750     // disable logging templates
 751     CRM_Core_DAO::executeQuery("
 752             UPDATE civicrm_option_value
 753             SET is_active = 0
 754             WHERE value IN ('" . implode("', '", $this->reports) . "')
 755         ");
 756
 757     // delete report instances
 758     $domain_id = CRM_Core_Config::domainID();
 759     foreach ($this->reports as $report) {
 760       $dao = new CRM_Report_DAO_ReportInstance();
 761       $dao->domain_id = $domain_id;
 762       $dao->report_id = $report;
 763       $dao->delete();
 764     }
 765   }
 766
 767   /**
 768    * Predicate whether logging is enabled.
 769    */
 770   public function isEnabled() {
 771     if (\Civi::settings()->get('logging')) {
 772       return ($this->tablesExist() && (\Civi::settings()->get('logging_no_trigger_permission') || $this->triggersExist()));
 773     }
 774     return FALSE;
 775   }
 776
 777   /**
 778    * Predicate whether any log tables exist.
 779    */
 780   private function tablesExist() {
 781     return !empty($this->logs);
 782   }
 783
 784   /**
 785    * Drop all log tables.
 786    *
 787    * This does not currently have a usage outside the tests.
 788    */
 789   public function dropAllLogTables() {
 790     if ($this->tablesExist()) {
 791       foreach ($this->logs as $log_table) {
 792         CRM_Core_DAO::executeQuery("DROP TABLE $log_table");
 793       }
 794     }
 795   }
 796
 797   /**
 798    * Get an sql clause to find the names of any log tables that do not match the normal pattern.
 799    *
 800    * Most tables are civicrm_xxx with the log table being log_civicrm_xxx
 801    * However, they don't have to match this pattern (e.g when defined by hook) so find the
 802    * anomalies and return a filter string to include them.
 803    *
 804    * @return string
 805    */
 806   public function getNonStandardTableNameFilterString() {
 807     $nonStandardTableNames = preg_grep('/^civicrm_/', $this->tables, PREG_GREP_INVERT);
 808     if (empty($nonStandardTableNames)) {
 809       return '';
 810     }
 811     $nonStandardTableLogs = array();
 812     foreach ($nonStandardTableNames as $nonStandardTableName) {
 813       $nonStandardTableLogs[] = "'log_{$nonStandardTableName}'";
 814     }
 815     return " OR TABLE_NAME IN (" . implode(',', $nonStandardTableLogs) . ")";
 816   }
 817
 818   /**
 819    * Predicate whether the logging triggers are in place.
 820    */
 821   private function triggersExist() {
 822     // FIXME: probably should be a bit more thorough…
 823     // note that the LIKE parameter is TABLE NAME
 824     return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
 825   }
 826
 827   /**
 828    * Get trigger info.
 829    *
 830    * @param array $info
 831    * @param null $tableName
 832    * @param bool $force
 833    */
 834   public function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
 835     if (!CRM_Core_Config::singleton()->logging) {
 836       return;
 837     }
 838
 839     $insert = array('INSERT');
 840     $update = array('UPDATE');
 841     $delete = array('DELETE');
 842
 843     if ($tableName) {
 844       $tableNames = array($tableName);
 845     }
 846     else {
 847       $tableNames = $this->tables;
 848     }
 849
 850     // logging is enabled, so now lets create the trigger info tables
 851     foreach ($tableNames as $table) {
 852       $columns = $this->columnsOf($table, $force);
 853
 854       // only do the change if any data has changed
 855       $cond = array();
 856       foreach ($columns as $column) {
 857         $tableExceptions = array_key_exists('exceptions', $this->logTableSpec[$table]) ? $this->logTableSpec[$table]['exceptions'] : array();
 858         // ignore modified_date changes
 859         if ($column != 'modified_date' && !in_array($column, $tableExceptions)) {
 860           $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
 861         }
 862       }
 863       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
 864       $updateSQL = "IF ( (" . implode(' OR ', $cond) . ") AND ( $suppressLoggingCond ) ) THEN ";
 865
 866       if ($this->useDBPrefix) {
 867         $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
 868       }
 869       else {
 870         $sqlStmt = "INSERT INTO log_{tableName} (";
 871       }
 872       foreach ($columns as $column) {
 873         $sqlStmt .= "$column, ";
 874       }
 875       $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
 876
 877       $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
 878       $updateSQL .= $sqlStmt;
 879
 880       $sqlStmt = '';
 881       foreach ($columns as $column) {
 882         $sqlStmt .= "NEW.$column, ";
 883         $deleteSQL .= "OLD.$column, ";
 884       }
 885       if (civicrm_api3('Setting', 'getvalue', array('name' => 'logging_uniqueid_date'))) {
 886         // Note that when connecting directly via mysql @uniqueID may not be set so a fallback is
 887         // 'c_' to identify a non-CRM connection + timestamp to the hour + connection_id
 888         // If the connection_id is longer than 6 chars it will be truncated.
 889         // We tried setting the @uniqueID in the trigger but it was unreliable.
 890         // An external interaction could split over 2 connections & it seems worth blocking the revert on
 891         // these reports & adding extra permissioning to the api for this.
 892         $connectionSQLString = "COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17))";
 893       }
 894       else {
 895         // The log tables have not yet been converted to have varchar(17) fields for log_conn_id.
 896         // Continue to use the less reliable connection_id for al tables for now.
 897         $connectionSQLString = "CONNECTION_ID()";
 898       }
 899       $sqlStmt .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
 900       $deleteSQL .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
 901
 902       $insertSQL .= $sqlStmt;
 903       $updateSQL .= $sqlStmt;
 904
 905       $info[] = array(
 906         'table' => array($table),
 907         'when' => 'AFTER',
 908         'event' => $insert,
 909         'sql' => $insertSQL,
 910       );
 911
 912       $info[] = array(
 913         'table' => array($table),
 914         'when' => 'AFTER',
 915         'event' => $update,
 916         'sql' => $updateSQL,
 917       );
 918
 919       $info[] = array(
 920         'table' => array($table),
 921         'when' => 'AFTER',
 922         'event' => $delete,
 923         'sql' => $deleteSQL,
 924       );
 925     }
 926   }
 927
 928   /**
 929    * Disable logging temporarily.
 930    *
 931    * This allow logging to be temporarily disabled for certain cases
 932    * where we want to do a mass cleanup but do not want to bother with
 933    * an audit trail.
 934    */
 935   public static function disableLoggingForThisConnection() {
 936     if (CRM_Core_Config::singleton()->logging) {
 937       CRM_Core_DAO::executeQuery('SET @civicrm_disable_logging = 1');
 938     }
 939   }
 940
 941   /**
 942    * Get all the log tables that reference civicrm_contact.
 943    *
 944    * Note that it might make sense to wrap this in a getLogTablesForEntity
 945    * but this is the only entity currently available...
 946    */
 947   public function getLogTablesForContact() {
 948     $tables = array_keys(CRM_Dedupe_Merger::cidRefs());
 949     return array_intersect($tables, $this->tables);
 950   }
 951
 952   /**
 953    * Retrieve missing log tables.
 954    *
 955    * @return array
 956    */
 957   public function getMissingLogTables() {
 958     if ($this->tablesExist()) {
 959       return array_diff($this->tables, array_keys($this->logs));
 960     }
 961     return array();
 962   }
 963
 964 }