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