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