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