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 = array();
  35   private $tables = array();
  36
  37   private $db;
  38   private $useDBPrefix = TRUE;
  39
  40   private $reports = array(
  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 = array(
  55     'civicrm_job' => array('last_run'),
  56     'civicrm_group' => array('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 = array();
  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, array('civicrm_mailing_recipients'));
 155     $this->logTableSpec = array_fill_keys($this->tables, array());
 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 = array();
 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 = array($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 any engine change to INNODB defined by the hooks.
 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   public function updateLogTableSchema() {
 309     $updateLogConn = FALSE;
 310     foreach ($this->logs as $mainTable => $logTable) {
 311       $alterSql = array();
 312       $tableSpec = $this->logTableSpec[$mainTable];
 313       if (isset($tableSpec['engine']) && strtoupper($tableSpec['engine']) != $this->getEngineForLogTable($logTable)) {
 314         $alterSql[] = "ENGINE=" . $tableSpec['engine'] . " " . CRM_Utils_Array::value('engine_config', $tableSpec);
 315         if (!empty($tableSpec['indexes'])) {
 316           $indexes = $this->getIndexesForTable($logTable);
 317           foreach ($tableSpec['indexes'] as $indexName => $indexSpec) {
 318             if (!in_array($indexName, $indexes)) {
 319               if (is_array($indexSpec)) {
 320                 $indexSpec = implode(" , ", $indexSpec);
 321               }
 322               $alterSql[] = "ADD INDEX {$indexName}($indexSpec)";
 323             }
 324           }
 325         }
 326       }
 327       $columns = $this->columnSpecsOf($logTable);
 328       if (empty($columns['log_conn_id'])) {
 329         throw new Exception($logTable . print_r($columns, TRUE));
 330       }
 331       if ($columns['log_conn_id']['DATA_TYPE'] != 'varchar' || $columns['log_conn_id']['LENGTH'] != 17) {
 332         $alterSql[] = "MODIFY log_conn_id VARCHAR(17)";
 333         $updateLogConn = TRUE;
 334       }
 335       if (!empty($alterSql)) {
 336         CRM_Core_DAO::executeQuery("ALTER TABLE {$this->db}.{$logTable} " . implode(', ', $alterSql), [], TRUE, NULL, FALSE, FALSE);
 337       }
 338     }
 339     if ($updateLogConn) {
 340       civicrm_api3('Setting', 'create', array('logging_uniqueid_date' => date('Y-m-d H:i:s')));
 341     }
 342   }
 343
 344   /**
 345    * Get the engine for the given table.
 346    *
 347    * @param string $table
 348    *
 349    * @return string
 350    */
 351   public function getEngineForLogTable($table) {
 352     return strtoupper(CRM_Core_DAO::singleValueQuery("
 353       SELECT ENGINE FROM information_schema.tables WHERE TABLE_NAME = %1
 354       AND table_schema = %2
 355     ", array(1 => array($table, 'String'), 2 => array($this->db, 'String'))));
 356   }
 357
 358   /**
 359    * Get all the indexes in the table.
 360    *
 361    * @param string $table
 362    *
 363    * @return array
 364    */
 365   public function getIndexesForTable($table) {
 366     $indexes = [];
 367     $result = CRM_Core_DAO::executeQuery("
 368         SELECT constraint_name AS index_name
 369         FROM information_schema.key_column_usage
 370         WHERE table_schema = %2 AND table_name = %1
 371       UNION
 372         SELECT index_name AS index_name
 373         FROM information_schema.statistics
 374         WHERE table_schema = %2 AND table_name = %1
 375       ",
 376       array(1 => array($table, 'String'), 2 => array($this->db, 'String'))
 377     );
 378     while ($result->fetch()) {
 379       $indexes[] = $result->index_name;
 380     }
 381     return $indexes;
 382   }
 383
 384   /**
 385    * Add missing (potentially specified) log table columns for the given table.
 386    *
 387    * @param string $table
 388    *   name of the relevant table.
 389    * @param array $cols
 390    *   Mixed array of columns to add or null (to check for the missing columns).
 391    * @param bool $rebuildTrigger
 392    *   should we rebuild the triggers.
 393    *
 394    * @return bool
 395    */
 396   public function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
 397     if (empty($table)) {
 398       return FALSE;
 399     }
 400     if (empty($this->logs[$table])) {
 401       $this->createLogTableFor($table);
 402       return TRUE;
 403     }
 404
 405     if (empty($cols)) {
 406       $cols = $this->columnsWithDiffSpecs($table, "log_$table");
 407     }
 408
 409     // use the relevant lines from CREATE TABLE to add colums to the log table
 410     $create = $this->_getCreateQuery($table);
 411     foreach ((array('ADD', 'MODIFY')) as $alterType) {
 412       if (!empty($cols[$alterType])) {
 413         foreach ($cols[$alterType] as $col) {
 414           $line = $this->_getColumnQuery($col, $create);
 415           CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}", array(), TRUE, NULL, FALSE, FALSE);
 416         }
 417       }
 418     }
 419
 420     // for any obsolete columns (not null) we just make the column nullable.
 421     if (!empty($cols['OBSOLETE'])) {
 422       $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
 423       foreach ($cols['OBSOLETE'] as $col) {
 424         $line = $this->_getColumnQuery($col, $create);
 425         // This is just going to make a not null column to nullable
 426         CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}", array(), TRUE, NULL, FALSE, FALSE);
 427       }
 428     }
 429
 430     if ($rebuildTrigger) {
 431       // invoke the meta trigger creation call
 432       CRM_Core_DAO::triggerRebuild($table);
 433     }
 434     return TRUE;
 435   }
 436
 437   /**
 438    * Get query table.
 439    *
 440    * @param string $table
 441    *
 442    * @return array
 443    */
 444   private function _getCreateQuery($table) {
 445     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}", array(), TRUE, NULL, FALSE, FALSE);
 446     $dao->fetch();
 447     $create = explode("\n", $dao->Create_Table);
 448     return $create;
 449   }
 450
 451   /**
 452    * Get column query.
 453    *
 454    * @param string $col
 455    * @param bool $createQuery
 456    *
 457    * @return array|mixed|string
 458    */
 459   private function _getColumnQuery($col, $createQuery) {
 460     $line = preg_grep("/^  `$col` /", $createQuery);
 461     $line = rtrim(array_pop($line), ',');
 462     // CRM-11179
 463     $line = self::fixTimeStampAndNotNullSQL($line);
 464     return $line;
 465   }
 466
 467   /**
 468    * Fix schema differences.
 469    *
 470    * @param bool $rebuildTrigger
 471    */
 472   public function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
 473     $diffs = array();
 474     foreach ($this->tables as $table) {
 475       if (empty($this->logs[$table])) {
 476         $this->createLogTableFor($table);
 477       }
 478       else {
 479         $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
 480       }
 481     }
 482
 483     foreach ($diffs as $table => $cols) {
 484       $this->fixSchemaDifferencesFor($table, $cols, FALSE);
 485     }
 486     if ($rebuildTrigger) {
 487       // invoke the meta trigger creation call
 488       CRM_Core_DAO::triggerRebuild(NULL, TRUE);
 489     }
 490   }
 491
 492   /**
 493    * Fix timestamp.
 494    *
 495    * Log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 496    * so there's no need for a default timestamp and therefore we remove such default timestamps
 497    * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 498    *
 499    * @param string $query
 500    *
 501    * @return mixed
 502    */
 503   public static function fixTimeStampAndNotNullSQL($query) {
 504     $query = str_ireplace("TIMESTAMP() NOT NULL", "TIMESTAMP NULL", $query);
 505     $query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
 506     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP()", '', $query);
 507     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
 508     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP()", '', $query);
 509     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
 510     $query = str_ireplace("NOT NULL", '', $query);
 511     return $query;
 512   }
 513
 514   /**
 515    * Add reports.
 516    */
 517   private function addReports() {
 518     $titles = array(
 519       'logging/contact/detail' => ts('Logging Details'),
 520       'logging/contact/summary' => ts('Contact Logging Report (Summary)'),
 521       'logging/contribute/detail' => ts('Contribution Logging Report (Detail)'),
 522       'logging/contribute/summary' => ts('Contribution Logging Report (Summary)'),
 523     );
 524     // enable logging templates
 525     CRM_Core_DAO::executeQuery("
 526             UPDATE civicrm_option_value
 527             SET is_active = 1
 528             WHERE value IN ('" . implode("', '", $this->reports) . "')
 529         ");
 530
 531     // add report instances
 532     $domain_id = CRM_Core_Config::domainID();
 533     foreach ($this->reports as $report) {
 534       $dao = new CRM_Report_DAO_ReportInstance();
 535       $dao->domain_id = $domain_id;
 536       $dao->report_id = $report;
 537       $dao->title = $titles[$report];
 538       $dao->permission = 'administer CiviCRM';
 539       if ($report == 'logging/contact/summary') {
 540         $dao->is_reserved = 1;
 541       }
 542       $dao->insert();
 543     }
 544   }
 545
 546   /**
 547    * Get an array of column names of the given table.
 548    *
 549    * @param string $table
 550    * @param bool $force
 551    *
 552    * @return array
 553    */
 554   private function columnsOf($table, $force = FALSE) {
 555     if ($force || !isset(\Civi::$statics[__CLASS__]['columnsOf'][$table])) {
 556       $from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
 557       CRM_Core_TemporaryErrorScope::ignoreException();
 558       $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 559       if (is_a($dao, 'DB_Error')) {
 560         return array();
 561       }
 562       \Civi::$statics[__CLASS__]['columnsOf'][$table] = array();
 563       while ($dao->fetch()) {
 564         \Civi::$statics[__CLASS__]['columnsOf'][$table][] = CRM_Utils_type::escape($dao->Field, 'MysqlColumnNameOrAlias');
 565       }
 566     }
 567     return \Civi::$statics[__CLASS__]['columnsOf'][$table];
 568   }
 569
 570   /**
 571    * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
 572    *
 573    * @param string $table
 574    *
 575    * @return array
 576    */
 577   private function columnSpecsOf($table) {
 578     static $civiDB = NULL;
 579     if (empty(\Civi::$statics[__CLASS__]['columnSpecs'])) {
 580       \Civi::$statics[__CLASS__]['columnSpecs'] = array();
 581     }
 582     if (empty(\Civi::$statics[__CLASS__]['columnSpecs']) || !isset(\Civi::$statics[__CLASS__]['columnSpecs'][$table])) {
 583       if (!$civiDB) {
 584         $dao = new CRM_Contact_DAO_Contact();
 585         $civiDB = $dao->_database;
 586       }
 587       CRM_Core_TemporaryErrorScope::ignoreException();
 588       // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
 589       // than firing query for every given table.
 590       $query = "
 591 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_TYPE, EXTRA
 592 FROM   INFORMATION_SCHEMA.COLUMNS
 593 WHERE  table_schema IN ('{$this->db}', '{$civiDB}')";
 594       $dao = CRM_Core_DAO::executeQuery($query);
 595       if (is_a($dao, 'DB_Error')) {
 596         return array();
 597       }
 598       while ($dao->fetch()) {
 599         if (!array_key_exists($dao->TABLE_NAME, \Civi::$statics[__CLASS__]['columnSpecs'])) {
 600           \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME] = array();
 601         }
 602         \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME] = array(
 603           'COLUMN_NAME' => $dao->COLUMN_NAME,
 604           'DATA_TYPE' => $dao->DATA_TYPE,
 605           'IS_NULLABLE' => $dao->IS_NULLABLE,
 606           'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT,
 607           'EXTRA' => $dao->EXTRA,
 608         );
 609         if (($first = strpos($dao->COLUMN_TYPE, '(')) != 0) {
 610           // this extracts the value between parentheses after the column type.
 611           // it could be the column length, i.e. "int(8)", "decimal(20,2)")
 612           // or the permitted values of an enum (e.g. "enum('A','B')")
 613           $parValue = substr(
 614             $dao->COLUMN_TYPE, $first + 1, strpos($dao->COLUMN_TYPE, ')') - $first - 1
 615           );
 616           if (strpos($parValue, "'") === FALSE) {
 617             // no quote in value means column length
 618             \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME]['LENGTH'] = $parValue;
 619           }
 620           else {
 621             // single quote means enum permitted values
 622             \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME]['ENUM_VALUES'] = $parValue;
 623           }
 624         }
 625       }
 626     }
 627     return \Civi::$statics[__CLASS__]['columnSpecs'][$table];
 628   }
 629
 630   /**
 631    * Get columns that have changed.
 632    *
 633    * @param string $civiTable
 634    * @param string $logTable
 635    *
 636    * @return array
 637    */
 638   public function columnsWithDiffSpecs($civiTable, $logTable) {
 639     $civiTableSpecs = $this->columnSpecsOf($civiTable);
 640     $logTableSpecs = $this->columnSpecsOf($logTable);
 641
 642     $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
 643     // columns to be added
 644     $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
 645     // columns to be modified
 646     // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
 647     // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
 648     foreach ($civiTableSpecs as $col => $colSpecs) {
 649       if (!isset($logTableSpecs[$col]) || !is_array($logTableSpecs[$col])) {
 650         $logTableSpecs[$col] = array();
 651       }
 652       $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
 653       if (!empty($specDiff) && $col != 'id' && !in_array($col, $diff['ADD'])) {
 654         if (empty($colSpecs['EXTRA']) || (!empty($colSpecs['EXTRA']) && $colSpecs['EXTRA'] !== 'auto_increment')) {
 655           // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
 656           if ($civiTableSpecs[$col]['DATA_TYPE'] != CRM_Utils_Array::value('DATA_TYPE', $logTableSpecs[$col])
 657           // We won't alter the log if the length is decreased in case some of the existing data won't fit.
 658           || CRM_Utils_Array::value('LENGTH', $civiTableSpecs[$col]) > CRM_Utils_Array::value('LENGTH', $logTableSpecs[$col])
 659           ) {
 660             // if data-type is different, surely consider the column
 661             $diff['MODIFY'][] = $col;
 662           }
 663           elseif ($civiTableSpecs[$col]['DATA_TYPE'] == 'enum' &&
 664             CRM_Utils_Array::value('ENUM_VALUES', $civiTableSpecs[$col]) != CRM_Utils_Array::value('ENUM_VALUES', $logTableSpecs[$col])
 665           ) {
 666             // column is enum and the permitted values have changed
 667             $diff['MODIFY'][] = $col;
 668           }
 669           elseif ($civiTableSpecs[$col]['IS_NULLABLE'] != CRM_Utils_Array::value('IS_NULLABLE', $logTableSpecs[$col]) &&
 670             $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 671           ) {
 672             // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
 673             $diff['MODIFY'][] = $col;
 674           }
 675           elseif ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != CRM_Utils_Array::value('COLUMN_DEFAULT', $logTableSpecs[$col]) &&
 676             !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')
 677           ) {
 678             // if default property is different, and its not about a timestamp column, consider it
 679             $diff['MODIFY'][] = $col;
 680           }
 681         }
 682       }
 683     }
 684
 685     // columns to made obsolete by turning into not-null
 686     $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
 687     foreach ($oldCols as $col) {
 688       if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
 689         $logTableSpecs[$col]['IS_NULLABLE'] == 'NO'
 690       ) {
 691         // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
 692         $diff['OBSOLETE'][] = $col;
 693       }
 694     }
 695
 696     return $diff;
 697   }
 698
 699   /**
 700    * Getter for logTableSpec.
 701    *
 702    * @return array
 703    */
 704   public function getLogTableSpec() {
 705     return $this->logTableSpec;
 706   }
 707
 708   /**
 709    * Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
 710    *
 711    * @param string $table
 712    */
 713   private function createLogTableFor($table) {
 714     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table", CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 715     $dao->fetch();
 716     $query = $dao->Create_Table;
 717
 718     // rewrite the queries into CREATE TABLE queries for log tables:
 719     $cols = <<<COLS
 720             ,
 721             log_date    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 722             log_conn_id VARCHAR(17),
 723             log_user_id INTEGER,
 724             log_action  ENUM('Initialization', 'Insert', 'Update', 'Delete')
 725 COLS;
 726
 727     if (!empty($this->logTableSpec[$table]['indexes'])) {
 728       foreach ($this->logTableSpec[$table]['indexes'] as $indexName => $indexSpec) {
 729         if (is_array($indexSpec)) {
 730           $indexSpec = implode(" , ", $indexSpec);
 731         }
 732         $cols .= ", INDEX {$indexName}($indexSpec)";
 733       }
 734     }
 735
 736     // - prepend the name with log_
 737     // - drop AUTO_INCREMENT columns
 738     // - drop non-column rows of the query (keys, constraints, etc.)
 739     // - set the ENGINE to the specified engine (default is archive or if archive is disabled or nor installed INNODB)
 740     // - add log-specific columns (at the end of the table)
 741     $mysqlEngines = [];
 742     $engines = CRM_Core_DAO::executeQuery("SHOW ENGINES");
 743     while ($engines->fetch()) {
 744       if ($engines->Support == 'YES' || $engines->Support == 'DEFAULT') {
 745         $mysqlEngines[] = $engines->Engine;
 746       }
 747     }
 748     $logEngine = in_array('ARCHIVE', $mysqlEngines) ? 'ARCHIVE' : 'INNODB';
 749     $query = preg_replace("/^CREATE TABLE `$table`/i", "CREATE TABLE `{$this->db}`.log_$table", $query);
 750     $query = preg_replace("/ AUTO_INCREMENT/i", '', $query);
 751     $query = preg_replace("/^  [^`].*$/m", '', $query);
 752     $engine = strtoupper(CRM_Utils_Array::value('engine', $this->logTableSpec[$table], $logEngine));
 753     $engine .= " " . CRM_Utils_Array::value('engine_config', $this->logTableSpec[$table]);
 754     $query = preg_replace("/^\) ENGINE=[^ ]+ /im", ') ENGINE=' . $engine . ' ', $query);
 755
 756     // log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
 757     // so there's no need for a default timestamp and therefore we remove such default timestamps
 758     // also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
 759     $query = self::fixTimeStampAndNotNullSQL($query);
 760     $query = preg_replace("/(,*\n*\) )ENGINE/m", "$cols\n) ENGINE", $query);
 761
 762     CRM_Core_DAO::executeQuery($query, CRM_Core_DAO::$_nullArray, TRUE, NULL, FALSE, FALSE);
 763
 764     $columns = implode(', ', $this->columnsOf($table));
 765     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);
 766
 767     $this->tables[] = $table;
 768     if (empty($this->logs)) {
 769       civicrm_api3('Setting', 'create', array('logging_uniqueid_date' => date('Y-m-d H:i:s')));
 770       civicrm_api3('Setting', 'create', array('logging_all_tables_uniquid' => 1));
 771     }
 772     $this->logs[$table] = "log_$table";
 773   }
 774
 775   /**
 776    * Delete reports.
 777    */
 778   private function deleteReports() {
 779     // disable logging templates
 780     CRM_Core_DAO::executeQuery("
 781             UPDATE civicrm_option_value
 782             SET is_active = 0
 783             WHERE value IN ('" . implode("', '", $this->reports) . "')
 784         ");
 785
 786     // delete report instances
 787     $domain_id = CRM_Core_Config::domainID();
 788     foreach ($this->reports as $report) {
 789       $dao = new CRM_Report_DAO_ReportInstance();
 790       $dao->domain_id = $domain_id;
 791       $dao->report_id = $report;
 792       $dao->delete();
 793     }
 794   }
 795
 796   /**
 797    * Predicate whether logging is enabled.
 798    */
 799   public function isEnabled() {
 800     if (\Civi::settings()->get('logging')) {
 801       return ($this->tablesExist() && (\Civi::settings()->get('logging_no_trigger_permission') || $this->triggersExist()));
 802     }
 803     return FALSE;
 804   }
 805
 806   /**
 807    * Predicate whether any log tables exist.
 808    */
 809   private function tablesExist() {
 810     return !empty($this->logs);
 811   }
 812
 813   /**
 814    * Drop all log tables.
 815    *
 816    * This does not currently have a usage outside the tests.
 817    */
 818   public function dropAllLogTables() {
 819     if ($this->tablesExist()) {
 820       foreach ($this->logs as $log_table) {
 821         CRM_Core_DAO::executeQuery("DROP TABLE $log_table");
 822       }
 823     }
 824   }
 825
 826   /**
 827    * Get an sql clause to find the names of any log tables that do not match the normal pattern.
 828    *
 829    * Most tables are civicrm_xxx with the log table being log_civicrm_xxx
 830    * However, they don't have to match this pattern (e.g when defined by hook) so find the
 831    * anomalies and return a filter string to include them.
 832    *
 833    * @return string
 834    */
 835   public function getNonStandardTableNameFilterString() {
 836     $nonStandardTableNames = preg_grep('/^civicrm_/', $this->tables, PREG_GREP_INVERT);
 837     if (empty($nonStandardTableNames)) {
 838       return '';
 839     }
 840     $nonStandardTableLogs = array();
 841     foreach ($nonStandardTableNames as $nonStandardTableName) {
 842       $nonStandardTableLogs[] = "'log_{$nonStandardTableName}'";
 843     }
 844     return " OR TABLE_NAME IN (" . implode(',', $nonStandardTableLogs) . ")";
 845   }
 846
 847   /**
 848    * Predicate whether the logging triggers are in place.
 849    */
 850   private function triggersExist() {
 851     // FIXME: probably should be a bit more thorough…
 852     // note that the LIKE parameter is TABLE NAME
 853     return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
 854   }
 855
 856   /**
 857    * Get trigger info.
 858    *
 859    * @param array $info
 860    * @param null $tableName
 861    * @param bool $force
 862    */
 863   public function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
 864     if (!CRM_Core_Config::singleton()->logging) {
 865       return;
 866     }
 867
 868     $insert = array('INSERT');
 869     $update = array('UPDATE');
 870     $delete = array('DELETE');
 871
 872     if ($tableName) {
 873       $tableNames = array($tableName);
 874     }
 875     else {
 876       $tableNames = $this->tables;
 877     }
 878
 879     // logging is enabled, so now lets create the trigger info tables
 880     foreach ($tableNames as $table) {
 881       $columns = $this->columnsOf($table, $force);
 882
 883       // only do the change if any data has changed
 884       $cond = array();
 885       foreach ($columns as $column) {
 886         $tableExceptions = array_key_exists('exceptions', $this->logTableSpec[$table]) ? $this->logTableSpec[$table]['exceptions'] : array();
 887         // ignore modified_date changes
 888         $tableExceptions[] = 'modified_date';
 889         // exceptions may be provided with or without backticks
 890         $excludeColumn = in_array($column, $tableExceptions) ||
 891           in_array(str_replace('`', '', $column), $tableExceptions);
 892         if (!$excludeColumn) {
 893           $cond[] = "IFNULL(OLD.$column,'') <> IFNULL(NEW.$column,'')";
 894         }
 895       }
 896       $suppressLoggingCond = "@civicrm_disable_logging IS NULL OR @civicrm_disable_logging = 0";
 897       $updateSQL = "IF ( (" . implode(' OR ', $cond) . ") AND ( $suppressLoggingCond ) ) THEN ";
 898
 899       if ($this->useDBPrefix) {
 900         $sqlStmt = "INSERT INTO `{$this->db}`.log_{tableName} (";
 901       }
 902       else {
 903         $sqlStmt = "INSERT INTO log_{tableName} (";
 904       }
 905       foreach ($columns as $column) {
 906         $sqlStmt .= "$column, ";
 907       }
 908       $sqlStmt .= "log_conn_id, log_user_id, log_action) VALUES (";
 909
 910       $insertSQL = $deleteSQL = "IF ( $suppressLoggingCond ) THEN $sqlStmt ";
 911       $updateSQL .= $sqlStmt;
 912
 913       $sqlStmt = '';
 914       foreach ($columns as $column) {
 915         $sqlStmt .= "NEW.$column, ";
 916         $deleteSQL .= "OLD.$column, ";
 917       }
 918       if (civicrm_api3('Setting', 'getvalue', array('name' => 'logging_uniqueid_date'))) {
 919         // Note that when connecting directly via mysql @uniqueID may not be set so a fallback is
 920         // 'c_' to identify a non-CRM connection + timestamp to the hour + connection_id
 921         // If the connection_id is longer than 6 chars it will be truncated.
 922         // We tried setting the @uniqueID in the trigger but it was unreliable.
 923         // An external interaction could split over 2 connections & it seems worth blocking the revert on
 924         // these reports & adding extra permissioning to the api for this.
 925         $connectionSQLString = "COALESCE(@uniqueID, LEFT(CONCAT('c_', unix_timestamp()/3600, CONNECTION_ID()), 17))";
 926       }
 927       else {
 928         // The log tables have not yet been converted to have varchar(17) fields for log_conn_id.
 929         // Continue to use the less reliable connection_id for al tables for now.
 930         $connectionSQLString = "CONNECTION_ID()";
 931       }
 932       $sqlStmt .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
 933       $deleteSQL .= $connectionSQLString . ", @civicrm_user_id, '{eventName}'); END IF;";
 934
 935       $insertSQL .= $sqlStmt;
 936       $updateSQL .= $sqlStmt;
 937
 938       $info[] = array(
 939         'table' => array($table),
 940         'when' => 'AFTER',
 941         'event' => $insert,
 942         'sql' => $insertSQL,
 943       );
 944
 945       $info[] = array(
 946         'table' => array($table),
 947         'when' => 'AFTER',
 948         'event' => $update,
 949         'sql' => $updateSQL,
 950       );
 951
 952       $info[] = array(
 953         'table' => array($table),
 954         'when' => 'AFTER',
 955         'event' => $delete,
 956         'sql' => $deleteSQL,
 957       );
 958     }
 959   }
 960
 961   /**
 962    * Disable logging temporarily.
 963    *
 964    * This allow logging to be temporarily disabled for certain cases
 965    * where we want to do a mass cleanup but do not want to bother with
 966    * an audit trail.
 967    */
 968   public static function disableLoggingForThisConnection() {
 969     if (CRM_Core_Config::singleton()->logging) {
 970       CRM_Core_DAO::executeQuery('SET @civicrm_disable_logging = 1');
 971     }
 972   }
 973
 974   /**
 975    * Get all the log tables that reference civicrm_contact.
 976    *
 977    * Note that it might make sense to wrap this in a getLogTablesForEntity
 978    * but this is the only entity currently available...
 979    */
 980   public function getLogTablesForContact() {
 981     $tables = array_keys(CRM_Core_DAO::getReferencesToContactTable());
 982     return array_intersect($tables, $this->tables);
 983   }
 984
 985   /**
 986    * Retrieve missing log tables.
 987    *
 988    * @return array
 989    */
 990   public function getMissingLogTables() {
 991     if ($this->tablesExist()) {
 992       return array_diff($this->tables, array_keys($this->logs));
 993     }
 994     return array();
 995   }
 996
 997 }