CRM/Logging/Schema.php

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