CRM/Logging/Schema.php

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