<?php
/*
+--------------------------------------------------------------------+
- | CiviCRM version 4.3 |
+ | CiviCRM version 4.4 |
+--------------------------------------------------------------------+
| Copyright CiviCRM LLC (c) 2004-2013 |
+--------------------------------------------------------------------+
'logging/contribute/summary',
);
- //CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
+ //CRM-13028 / NYSS-6933 - table => array (cols) - to be excluded from the update statement
private $exceptions = array(
'civicrm_job' => array('last_run'),
'civicrm_group' => array('cache_date'),
- );
+ );
/**
* Populate $this->tables and $this->logs with current db state.
return preg_grep('/^log_civicrm_value_/', $this->logs);
}
+ /**
+ * Return custom data tables for specified entity / extends.
+ */
+ function entityCustomDataLogTables($extends) {
+ $customGroupTables = array();
+ $customGroupDAO = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity($extends);
+ $customGroupDAO->find();
+ while ($customGroupDAO->fetch()) {
+ $customGroupTables[$customGroupDAO->table_name] = $this->logs[$customGroupDAO->table_name];
+ }
+ return $customGroupTables;
+ }
+
/**
* Disable logging by dropping the triggers (but keep the log tables intact).
*/
}
foreach ($tableNames as $table) {
+ $validName = CRM_Core_DAO::shortenSQLName($table, 48, TRUE);
+
// before triggers
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_insert");
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_update");
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_before_delete");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_insert");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_update");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_before_delete");
// after triggers
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_insert");
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_update");
- $dao->executeQuery("DROP TRIGGER IF EXISTS {$table}_after_delete");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_insert");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_update");
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$validName}_after_delete");
+ }
+
+ // now lets also be safe and drop all triggers that start with
+ // civicrm_ if we are dropping all triggers
+ // we need to do this to capture all the leftover triggers since
+ // we did the shortening trigger name for CRM-11794
+ if ($tableName === NULL) {
+ $triggers = $dao->executeQuery("SHOW TRIGGERS LIKE 'civicrm_%'");
+
+ while ($triggers->fetch()) {
+ // note that drop trigger has a wierd syntax and hence we do not
+ // send the trigger name as a string (i.e. its not quoted
+ $dao->executeQuery("DROP TRIGGER IF EXISTS {$triggers->Trigger}");
+ }
}
}
$config->logging = TRUE;
}
if ($config->logging) {
- foreach ($this->schemaDifferences() as $table => $cols) {
- $this->fixSchemaDifferencesFor($table, $cols, FALSE);
- }
+ $this->fixSchemaDifferencesForALL();
}
// invoke the meta trigger creation call
- CRM_Core_DAO::triggerRebuild();
+ CRM_Core_DAO::triggerRebuild(NULL, TRUE);
}
/**
*
* @return void
*/
- function fixSchemaDifferencesFor($table, $cols = NULL, $rebuildTrigger = TRUE) {
+ function fixSchemaDifferencesFor($table, $cols = array(), $rebuildTrigger = FALSE) {
+ if (empty($table)) {
+ return FALSE;
+ }
if (empty($this->logs[$table])) {
$this->createLogTableFor($table);
- return;
+ return TRUE;
}
- if (is_null($cols)) {
- $cols = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
- }
if (empty($cols)) {
- return;
+ $cols = $this->columnsWithDiffSpecs($table, "log_$table");
}
// use the relevant lines from CREATE TABLE to add colums to the log table
- $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE $table");
+ $create = $this->_getCreateQuery($table);
+ foreach ((array('ADD', 'MODIFY')) as $alterType) {
+ if (!empty($cols[$alterType])) {
+ foreach ($cols[$alterType] as $col) {
+ $line = $this->_getColumnQuery($col, $create);
+ CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table {$alterType} {$line}");
+ }
+ }
+ }
+
+ // for any obsolete columns (not null) we just make the column nullable.
+ if (!empty($cols['OBSOLETE'])) {
+ $create = $this->_getCreateQuery("`{$this->db}`.log_{$table}");
+ foreach ($cols['OBSOLETE'] as $col) {
+ $line = $this->_getColumnQuery($col, $create);
+ // This is just going to make a not null column to nullable
+ CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table MODIFY {$line}");
+ }
+ }
+
+ if ($rebuildTrigger) {
+ // invoke the meta trigger creation call
+ CRM_Core_DAO::triggerRebuild($table);
+ }
+ return TRUE;
+ }
+
+ private function _getCreateQuery($table) {
+ $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE {$table}");
$dao->fetch();
$create = explode("\n", $dao->Create_Table);
- foreach ($cols as $col) {
- $line = preg_grep("/^ `$col` /", $create);
- $line = substr(array_pop($line), 0, -1);
- // CRM-11179
- $line = self::fixTimeStampAndNotNullSQL($line);
+ return $create;
+ }
+
+ private function _getColumnQuery($col, $createQuery) {
+ $line = preg_grep("/^ `$col` /", $createQuery);
+ $line = rtrim(array_pop($line), ',');
+ // CRM-11179
+ $line = $this->fixTimeStampAndNotNullSQL($line);
+ return $line;
+ }
- CRM_Core_DAO::executeQuery("ALTER TABLE `{$this->db}`.log_$table ADD $line");
+ function fixSchemaDifferencesForAll($rebuildTrigger = FALSE) {
+ $diffs = array();
+ foreach ($this->tables as $table) {
+ if (empty($this->logs[$table])) {
+ $this->createLogTableFor($table);
+ }
+ else {
+ $diffs[$table] = $this->columnsWithDiffSpecs($table, "log_$table");
+ }
+ }
+
+ foreach ($diffs as $table => $cols) {
+ $this->fixSchemaDifferencesFor($table, $cols, FALSE);
}
if ($rebuildTrigger) {
}
}
+ /*
+ * log_civicrm_contact.modified_date for example would always be copied from civicrm_contact.modified_date,
+ * so there's no need for a default timestamp and therefore we remove such default timestamps
+ * also eliminate the NOT NULL constraint, since we always copy and schema can change down the road)
+ */
function fixTimeStampAndNotNullSQL($query) {
$query = str_ireplace("TIMESTAMP NOT NULL", "TIMESTAMP NULL", $query);
$query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP", '', $query);
return $query;
}
- /**
- * Find missing log table columns by comparing columns of the relevant tables.
- * Returns table-name-keyed array of arrays of missing columns, e.g. array('civicrm_value_foo_1' => array('bar_1', 'baz_2'))
- */
- function schemaDifferences() {
- $diffs = array();
- foreach ($this->tables as $table) {
- $diffs[$table] = array_diff($this->columnsOf($table), $this->columnsOf("log_$table"));
- }
- return array_filter($diffs);
- }
-
private function addReports() {
$titles = array(
'logging/contact/detail' => ts('Logging Details'),
/**
* Get an array of column names of the given table.
*/
- private function columnsOf($table) {
+ private function columnsOf($table, $force = FALSE) {
static $columnsOf = array();
$from = (substr($table, 0, 4) == 'log_') ? "`{$this->db}`.$table" : $table;
- if (!isset($columnsOf[$table])) {
+ if (!isset($columnsOf[$table]) || $force) {
CRM_Core_Error::ignoreException();
$dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM $from");
CRM_Core_Error::setCallback();
return $columnsOf[$table];
}
+ /**
+ * Get an array of columns and their details like DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT for the given table.
+ */
+ private function columnSpecsOf($table) {
+ static $columnSpecs = array(), $civiDB = NULL;
+
+ if (empty($columnSpecs)) {
+ if (!$civiDB) {
+ $dao = new CRM_Contact_DAO_Contact();
+ $civiDB = $dao->_database;
+ }
+ CRM_Core_Error::ignoreException();
+ // NOTE: W.r.t Performance using one query to find all details and storing in static array is much faster
+ // than firing query for every given table.
+ $query = "
+SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE table_schema IN ('{$this->db}', '{$civiDB}')";
+ $dao = CRM_Core_DAO::executeQuery($query);
+ CRM_Core_Error::setCallback();
+ if (is_a($dao, 'DB_Error')) {
+ return array();
+ }
+ while ($dao->fetch()) {
+ if (!array_key_exists($dao->TABLE_NAME, $columnSpecs)) {
+ $columnSpecs[$dao->TABLE_NAME] = array();
+ }
+ $columnSpecs[$dao->TABLE_NAME][$dao->COLUMN_NAME] =
+ array(
+ 'COLUMN_NAME' => $dao->COLUMN_NAME,
+ 'DATA_TYPE' => $dao->DATA_TYPE,
+ 'IS_NULLABLE' => $dao->IS_NULLABLE,
+ 'COLUMN_DEFAULT' => $dao->COLUMN_DEFAULT
+ );
+ }
+ }
+ return $columnSpecs[$table];
+ }
+
+ function columnsWithDiffSpecs($civiTable, $logTable) {
+ $civiTableSpecs = $this->columnSpecsOf($civiTable);
+ $logTableSpecs = $this->columnSpecsOf($logTable);
+
+ $diff = array('ADD' => array(), 'MODIFY' => array(), 'OBSOLETE' => array());
+
+ // columns to be added
+ $diff['ADD'] = array_diff(array_keys($civiTableSpecs), array_keys($logTableSpecs));
+
+ // columns to be modified
+ // NOTE: we consider only those columns for modifications where there is a spec change, and that the column definition
+ // wasn't deliberately modified by fixTimeStampAndNotNullSQL() method.
+ foreach ($civiTableSpecs as $col => $colSpecs) {
+ if ( !is_array($logTableSpecs[$col]) ) {
+ $logTableSpecs[$col] = array();
+ }
+
+ $specDiff = array_diff($civiTableSpecs[$col], $logTableSpecs[$col]);
+ if (!empty($specDiff) && $col != 'id' && !array_key_exists($col, $diff['ADD'])) {
+ // ignore 'id' column for any spec changes, to avoid any auto-increment mysql errors
+ if ($civiTableSpecs[$col]['DATA_TYPE'] != $logTableSpecs[$col]['DATA_TYPE']) {
+ // if data-type is different, surely consider the column
+ $diff['MODIFY'][] = $col;
+ } else if ($civiTableSpecs[$col]['IS_NULLABLE'] != $logTableSpecs[$col]['IS_NULLABLE'] &&
+ $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
+ // if is-null property is different, and log table's column is NOT-NULL, surely consider the column
+ $diff['MODIFY'][] = $col;
+ } else if ($civiTableSpecs[$col]['COLUMN_DEFAULT'] != $logTableSpecs[$col]['COLUMN_DEFAULT'] &&
+ !strstr($civiTableSpecs[$col]['COLUMN_DEFAULT'], 'TIMESTAMP')) {
+ // if default property is different, and its not about a timestamp column, consider it
+ $diff['MODIFY'][] = $col;
+ }
+ }
+ }
+
+ // columns to made obsolete by turning into not-null
+ $oldCols = array_diff(array_keys($logTableSpecs), array_keys($civiTableSpecs));
+ foreach ($oldCols as $col) {
+ if (!in_array($col, array('log_date', 'log_conn_id', 'log_user_id', 'log_action')) &&
+ $logTableSpecs[$col]['IS_NULLABLE'] == 'NO') {
+ // if its a column present only in log table, not among those used by log tables for special purpose, and not-null
+ $diff['OBSOLETE'][] = $col;
+ }
+ }
+
+ return $diff;
+ }
+
/**
* Create a log table with schema mirroring the given table’s structure and seeding it with the given table’s contents.
*/
return (bool) CRM_Core_DAO::singleValueQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
}
- function triggerInfo(&$info, $tableName = NULL) {
+ function triggerInfo(&$info, $tableName = NULL, $force = FALSE) {
// check if we have logging enabled
$config =& CRM_Core_Config::singleton();
if (!$config->logging) {
// logging is enabled, so now lets create the trigger info tables
foreach ($tableNames as $table) {
- $columns = $this->columnsOf($table);
+ $columns = $this->columnsOf($table, $force);
// only do the change if any data has changed
$cond = array( );