From ee0dc31b044dcb0feada9eb4e4ca4bc7efedda19 Mon Sep 17 00:00:00 2001 From: demeritcowboy Date: Mon, 28 Mar 2022 09:02:39 -0400 Subject: [PATCH] upmerge relevant files from 22940 --- CRM/Upgrade/Incremental/MessageTemplates.php | 2 +- .../Incremental/php/FiveFortyEight.php | 47 +++ .../Incremental/php/TimezoneRevertTrait.php | 306 ++++++++++++++++++ 3 files changed, 354 insertions(+), 1 deletion(-) create mode 100644 CRM/Upgrade/Incremental/php/TimezoneRevertTrait.php diff --git a/CRM/Upgrade/Incremental/MessageTemplates.php b/CRM/Upgrade/Incremental/MessageTemplates.php index a46359bb54..bf9c443a39 100644 --- a/CRM/Upgrade/Incremental/MessageTemplates.php +++ b/CRM/Upgrade/Incremental/MessageTemplates.php @@ -323,7 +323,7 @@ class CRM_Upgrade_Incremental_MessageTemplates { ], ], [ - 'version' => '5.48.beta1', + 'version' => '5.48.beta2', 'upgrade_descriptor' => ts('Revert time zone for Event dates'), 'templates' => [ ['name' => 'event_online_receipt', 'type' => 'html'], diff --git a/CRM/Upgrade/Incremental/php/FiveFortyEight.php b/CRM/Upgrade/Incremental/php/FiveFortyEight.php index 18f112597f..e92d9331b1 100644 --- a/CRM/Upgrade/Incremental/php/FiveFortyEight.php +++ b/CRM/Upgrade/Incremental/php/FiveFortyEight.php @@ -21,6 +21,42 @@ */ class CRM_Upgrade_Incremental_php_FiveFortyEight extends CRM_Upgrade_Incremental_Base { + use CRM_Upgrade_Incremental_php_TimezoneRevertTrait; + + /** + * Compute any messages which should be displayed beforeupgrade. + * + * Note: This function is called iteratively for each incremental upgrade step. + * There must be a concrete step (eg 'X.Y.Z.mysql.tpl' or 'upgrade_X_Y_Z()'). + * + * @param string $preUpgradeMessage + * @param string $rev + * a version number, e.g. '4.4.alpha1', '4.4.beta3', '4.4.0'. + * @param null $currentVer + */ + public function setPreUpgradeMessage(&$preUpgradeMessage, $rev, $currentVer = NULL): void { + if ($rev === '5.48.beta2') { + $preUpgradeMessage .= $this->createEventTzPreUpgradeMessage(); + } + } + + /** + * Compute any messages which should be displayed after upgrade. + * + * Note: This function is called iteratively for each incremental upgrade step. + * There must be a concrete step (eg 'X.Y.Z.mysql.tpl' or 'upgrade_X_Y_Z()'). + * + * @param string $postUpgradeMessage + * alterable. + * @param string $rev + * an intermediate version; note that setPostUpgradeMessage is called repeatedly with different $revs. + */ + public function setPostUpgradeMessage(&$postUpgradeMessage, $rev): void { + if ($rev === '5.48.beta2') { + $postUpgradeMessage .= $this->createEventTzPostUpgradeMessage(); + } + } + /** * Upgrade step; adds tasks including 'runSql'. * @@ -48,6 +84,17 @@ class CRM_Upgrade_Incremental_php_FiveFortyEight extends CRM_Upgrade_Incremental ); } + /** + * Upgrade step; adds tasks including 'runSql'. + * + * @param string $rev + * The version number matching this function name + */ + public function upgrade_5_48_beta2($rev): void { + // $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev); + $this->addEventTzTasks(); + } + /** * The `is_autorun` column was introduced in 5.47, but we didn't finish adding the * additional changes to use, so there shouldn't be any real usage. But just to be diff --git a/CRM/Upgrade/Incremental/php/TimezoneRevertTrait.php b/CRM/Upgrade/Incremental/php/TimezoneRevertTrait.php new file mode 100644 index 0000000000..f27d734fbc --- /dev/null +++ b/CRM/Upgrade/Incremental/php/TimezoneRevertTrait.php @@ -0,0 +1,306 @@ +getTimezoneStats(); + return '
' . ts('CiviEvent Timezone Rollback') . '
'; + } + return ''; + } + + public function createEventTzPostUpgradeMessage(): string { + // Note that setPostUpgradeMessage is called at the start of the step, + // before its queued tasks run, so we are examining the database + // before updating the fields. + if (self::areThereAnyCiviEvents() && self::areEventsUsingTimestamp()) { + return '
' . ts('CiviEvent Timezones') . '
'; + } + return ''; + } + + public function addEventTzTasks(): void { + if (self::areEventsUsingTimestamp()) { + $actions = getenv('CIVICRM_TZ_REVERT') + ? explode(',', getenv('CIVICRM_TZ_REVERT')) + : ['backup', 'revert', 'adapt']; + if (in_array('backup', $actions)) { + $this->addTask('Add temporary backup start_date to civicrm_event', 'addColumn', 'civicrm_event', 'start_date_ts_bak', "timestamp NULL DEFAULT NULL COMMENT 'For troubleshooting upgrades post 5.47. Can drop this column if no issues.'"); + $this->addTask('Add temporary backup end_date to civicrm_event', 'addColumn', 'civicrm_event', 'end_date_ts_bak', "timestamp NULL DEFAULT NULL COMMENT 'For troubleshooting upgrades post 5.47. Can drop this column if no issues.'"); + $this->addTask('Add temporary backup registration_start_date to civicrm_event', 'addColumn', 'civicrm_event', 'registration_start_date_ts_bak', "timestamp NULL DEFAULT NULL COMMENT 'For troubleshooting upgrades post 5.47. Can drop this column if no issues.'"); + $this->addTask('Add temporary backup registration_end_date to civicrm_event', 'addColumn', 'civicrm_event', 'registration_end_date_ts_bak', "timestamp NULL DEFAULT NULL COMMENT 'For troubleshooting upgrades post 5.47. Can drop this column if no issues.'"); + $this->addTask('Backup CiviEvent times', 'fillBackupEventDates'); + } + if (in_array('revert', $actions)) { + $this->addTask('Revert CiviEvent times', 'revertEventDates'); + } + if (in_array('adapt', $actions)) { + $this->addTask('Adapt CiviEvent times', 'convertModifiedEvents'); + } + } + } + + /** + * dev/core#2122 - keep a copy of converted dates + * In theory we could skip this step if logging is enabled, but (a) people + * might turn off logging before running upgrades, and (b) there may not be a + * complete record anyway. People can drop the new column if they don't need + * it. + * @param \CRM_Queue_TaskContext $ctx + * @return bool + */ + public static function fillBackupEventDates(CRM_Queue_TaskContext $ctx): bool { + // We only run if the field is timestamp, so don't need to check about that. + CRM_Core_DAO::executeQuery('UPDATE civicrm_event SET start_date_ts_bak = start_date, end_date_ts_bak = end_date, registration_start_date_ts_bak = registration_start_date, registration_end_date_ts_bak = registration_end_date'); + CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_event CHANGE COLUMN event_tz event_tz_bak text NULL DEFAULT NULL COMMENT 'For troubleshooting upgrades post 5.47. Can drop this column if no issues.'"); + return TRUE; + } + + /** + * This is the straight-up opposite of the conversion done in `5.47.alpha1`. + * It flips the `TIMESTAMP`s back to `DATETIME`s. This should be a clean/straight + * revert - provided that the records have not changed. + * + * But some records may have changed. `convertModifiedEvents()` will address those. + * + * @param \CRM_Queue_TaskContext $ctx + * @return bool + */ + public static function revertEventDates(CRM_Queue_TaskContext $ctx = NULL): bool { + // We only run if the field is timestamp, so don't need to check about that. + + // The original 5.47.alpha1 upgrade was executed with SQL helpers in CRM_Utils_File, which use + // a separate DSN/session. We need to use the same interface so that the `@@time_zone` is consistent + // with the prior update. + + $sql = "ALTER TABLE `civicrm_event` + MODIFY COLUMN `start_date` datetime DEFAULT NULL COMMENT 'Date and time that event starts.', + MODIFY COLUMN `end_date` datetime DEFAULT NULL COMMENT 'Date and time that event ends. May be NULL if no defined end date/time', + MODIFY COLUMN `registration_start_date` datetime DEFAULT NULL COMMENT 'Date and time that online registration starts.', + MODIFY COLUMN `registration_end_date` datetime DEFAULT NULL COMMENT 'Date and time that online registration ends.';"; + + $upgrade = new CRM_Upgrade_Form(); + $upgrade->source($sql, TRUE); + + return TRUE; + } + + /** + * If a user edited an `Event` in the UI while running 5.47.alpha1 - 5.47.2, then `revertEventDates` + * won't be good enough. In particular: + * + * - It's likely to have activated the DST bug (based on the current-user's TZ). + * - The user could have filled-in/corrected the values of `event_tz` and/or each `TIMESTAMP` column. + * + * The algorithm reads backup values (eg `start_date_ts_bak`) and rewrites live values (eg `start_date`) + * + * It uses a heuristic approach to cleaning DST error ("skew"). This requires a _representative_ + * timezone ("skewTz"). It should not be necessary to know the exact TZ of every edit -- as long as all TZs + * have similar DST rules. For example: + * + * - Most locales in US+CA have the same DST rules. (`America/Los_Angeles` and `America/Chicago` are equally representative.) + * - Most locales in Europe have the same DST rules. (`Europe/Helsinki` and `Europe/Berlin` are equally representative.) + * - Most locales in Australia have the same DST rules. + * + * By default, this will borrow the current user (sysadmin)'s timezone as the representative skewTz. + * This can be overridden with env-var `CIVICRM_TZ_SKEW`. + * + * @param \CRM_Queue_TaskContext $ctx + * @return bool + */ + public static function convertModifiedEvents(CRM_Queue_TaskContext $ctx = NULL): bool { + $skewTz = self::pickSkewTz(); + $mysqlTz = '+0:00'; + $restoreMysqlTz = static::swapTz($mysqlTz); + + $columns = [ + 'start_date' => 'start_date_ts_bak', + 'end_date' => 'end_date_ts_bak', + 'registration_start_date' => 'registration_start_date_ts_bak', + 'registration_end_date' => 'registration_end_date_ts_bak', + ]; + + [$lowLogId, $highLogId] = self::findLogRange( + '5.47.alpha1', + (static::class === CRM_Upgrade_Incremental_php_FiveFortySeven::class) ? '5.47.3' : '5.48.beta2' + ); + + $eventModTimes = CRM_Utils_SQL_Select::from('civicrm_log') + ->select('entity_id, max(modified_date) as modified_date') + ->where('entity_table = "civicrm_event"') + ->where('id >= #lowLogId AND id <= #highLogId', ['lowLogId' => $lowLogId, 'highLogId' => $highLogId]) + ->groupBy('entity_table, entity_id') + ->execute() + ->fetchMap('entity_id', 'modified_date'); + if (empty($eventModTimes)) { + return TRUE; + } + + $events = CRM_Utils_SQL_Select::from('civicrm_event') + ->select(['id', 'event_tz_bak']) + ->select(array_values($columns)) + ->where('id in (#IDS)', ['IDS' => array_keys($eventModTimes)]) + ->execute() + ->fetchAll(); + + foreach ($events as $event) { + $updates = []; + foreach ($columns as $outColumn => $inColumn) { + if (!empty($event[$inColumn])) { + $dstError = ($skewTz === 'IGNORE') ? 0 : static::findDstError($eventModTimes[$event['id']], $event[$inColumn], $skewTz); + // $event["{$inColumn}_err"] = $dstError; + $newValue = static::addSeconds(static::convertTz($event[$inColumn], $mysqlTz, $event['event_tz_bak']), $dstError); + $updates[] = $outColumn . ' = "' . CRM_Core_DAO::escapeString($newValue) . '"'; + } + else { + $updates[] = $outColumn . ' = NULL'; + } + } + $sql = sprintf('UPDATE civicrm_event SET %s WHERE id = %d', implode(',', $updates), (int) $event['id']); + // printf("\n[UTC, skewTz=%s]\n%s\n%s\n", $skewTz, json_encode($event), $sql); + CRM_Core_DAO::executeQuery($sql); + } + + return TRUE; + } + + public static function findDstError(string $modificationTime, string $targetValue, string $timeZone): int { + $tzObj = new DateTimeZone($timeZone); + $objA = new DateTime($modificationTime, $tzObj); + $objB = new DateTime($targetValue, $tzObj); + return $objA->getOffset() - $objB->getOffset(); + } + + public static function addSeconds(string $dateTime, int $skew): string { + if (!$skew) { + return $dateTime; + } + return date('Y-m-d H:i:s', strtotime($dateTime) + $skew); + } + + public static function convertTz(string $dateTimeExpr, string $srcTz, string $destTz) { + $datetime = new DateTime($dateTimeExpr, new DateTimeZone($srcTz)); + $datetime->setTimezone(new DateTimeZone($destTz)); + return $datetime->format('Y-m-d H:i:s'); + } + + protected static function swapTz($newTz): CRM_Utils_AutoClean { + $startTz = CRM_Core_DAO::singleValueQuery('SELECT @@time_zone'); + CRM_Core_DAO::executeQuery('SET time_zone = %1', [1 => ['+0:00', 'String']]); + return CRM_Utils_AutoClean::with(function() use ($startTz) { + CRM_Core_DAO::executeQuery('SET time_zone = %1', [1 => [$startTz, 'String']]); + }); + } + + /** + * Choose a representative timezone for identifying DST errors. + * + * Preference will be given to ENV['CIVICRM_TZ_SKEW'] or the current user's TZ. + * + * An explict value of `IGNORE` will opt-out of skew correction. + * + * @return string + */ + protected static function pickSkewTz(): string { + $skewTz = getenv('CIVICRM_TZ_SKEW'); + if (!$skewTz) { + $skewTz = CRM_Core_Config::singleton()->userSystem->getTimeZoneString(); + } + if (!$skewTz) { + return 'IGNORE'; + } + return $skewTz; + } + + /** + * Find the slice of `civicrm_log` which occurred between version $X and version $Y. + * + * @param string $lowVersion + * @param string $highVersion + * @return array + */ + protected static function findLogRange(string $lowVersion, string $highVersion): array { + $lowLog = CRM_Core_DAO::executeQuery('SELECT id FROM civicrm_log WHERE entity_table = "civicrm_domain" AND data LIKE %1 ORDER BY id LIMIT 1', [ + 1 => ['upgrade%' . $lowVersion . '.upgrade', 'String'], + ]); + $lowLogId = $lowLog->fetch() ? $lowLog->id : 1; + + $highLog = CRM_Core_DAO::executeQuery('SELECT id FROM civicrm_log WHERE entity_table = "civicrm_domain" AND data LIKE %1 ORDER BY id LIMIT 1', [ + 1 => ['upgrade%' . $highVersion . '.upgrade', 'String'], + ]); + $highLogId = $highLog->fetch() ? $highLog->id : CRM_Core_DAO::singleValueQuery('SELECT MAX(id) FROM civicrm_log'); + return array($lowLogId, $highLogId); + } + + /** + * Check if civicrm_event start_date is a timestamp. + * @return bool + */ + private static function areEventsUsingTimestamp(): bool { + $dao = CRM_Core_DAO::executeQuery("SHOW COLUMNS FROM civicrm_event LIKE 'start_date'"); + if ($dao->fetch()) { + return (strtolower($dao->Type) === 'timestamp'); + } + return FALSE; + } + + /** + * Are there any events in the system? + * @return bool + */ + private static function areThereAnyCiviEvents(): bool { + return (bool) CRM_Core_DAO::singleValueQuery('SELECT COUNT(id) FROM civicrm_event'); + } + + private function getTimezoneStats(): array { + $dao = CRM_Core_DAO::executeQuery('SELECT event_tz, COUNT(*) AS `count` FROM civicrm_event GROUP BY event_tz ORDER BY COUNT(event_tz) DESC'); + $r = []; + while ($dao->fetch()) { + $r[] = ['name' => $dao->event_tz ?: ts('Empty'), 'count' => $dao->count]; + } + return $r; + } + +} -- 2.25.1