3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2020 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2020
33 class CRM_Utils_Check_Component_Timestamps
extends CRM_Utils_Check_Component
{
35 const DOCTOR_WHEN
= 'https://github.com/civicrm/org.civicrm.doctorwhen';
38 * Check that various columns are TIMESTAMP and not DATETIME. (CRM-9683, etal)
42 public function checkSchema() {
44 foreach (self
::getConvertedTimestamps() as $target) {
45 if (self
::isFieldType($target['table'], $target['column'], 'datetime')) {
47 $phrases[] = sprintf('<em>%s.%s</em>', $target['table'], $target['column']);
49 if ($target['changed']) {
50 $phrases[] = sprintf('(New sites default to TIMESTAMP in v%s+)', $target['changed']);
53 $phrases[] = '(Experimental suggestion)';
56 if (isset($target['jira'])) {
57 $phrases[] = sprintf(' [<a href="https://issues.civicrm.org/jira/browse/%s" target="_blank">%s</a>]', $target['jira'], $target['jira']);
60 $problems[] = implode(' ', $phrases);
66 $messages[] = new CRM_Utils_Check_Message(
67 __FUNCTION__
. md5(implode(',', $problems)),
69 ts('This MySQL database stores certain fields with data-type "DATETIME". To improve timezone support, you <em>may</em> want to change these from "DATETIME" to "TIMESTAMP".') .
72 implode('</li><li>', $problems) .
75 ts('Changing should improve data-quality for organizations working in multiple timezones. However, if you do change, then you may need to re-test any customizations or processes that reference these fields. Changing is <em>suggested</em> but not <em>required</em>.') .
78 ts('For further discussion, please visit %1', [
79 1 => sprintf('<a href="%s" target="_blank">%s</a>', self
::DOCTOR_WHEN
, self
::DOCTOR_WHEN
),
82 ts('Timestamps and Timezones'),
83 \Psr\Log\LogLevel
::NOTICE
,
91 * @param string $table
93 * @param string $column
94 * Ex: 'modified_date'.
95 * @param string $expectType
96 * Ex: 'datetime' or 'timestamp'.
99 public static function isFieldType($table, $column, $expectType) {
101 $dao = CRM_Core_DAO
::executeQuery('DESC ' . $table);
102 while ($dao->fetch()) {
103 if ($dao->Field
=== $column && strtolower($dao->Type
) === $expectType) {
110 public static function getConvertedTimestamps() {
112 ['table' => 'civicrm_cache', 'column' => 'created_date', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When was the cache item created'],
113 ['table' => 'civicrm_cache', 'column' => 'expired_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'When should the cache item expire'],
114 ['table' => 'civicrm_job', 'column' => 'last_run', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'When was this cron entry last run'],
115 ['table' => 'civicrm_mailing_event_bounce', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this bounce event occurred.'],
116 ['table' => 'civicrm_mailing_event_confirm', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this confirmation event occurred.'],
117 ['table' => 'civicrm_mailing_event_delivered', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this delivery event occurred.'],
118 ['table' => 'civicrm_mailing_event_forward', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this forward event occurred.'],
119 ['table' => 'civicrm_mailing_event_opened', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this open event occurred.'],
120 ['table' => 'civicrm_mailing_event_reply', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this reply event occurred.'],
121 ['table' => 'civicrm_mailing_event_subscribe', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this subscription event occurred.'],
122 ['table' => 'civicrm_mailing_event_trackable_url_open', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this trackable URL open occurred.'],
123 ['table' => 'civicrm_mailing_event_unsubscribe', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this delivery event occurred.'],
124 ['table' => 'civicrm_mailing', 'column' => 'created_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was created.'],
125 ['table' => 'civicrm_mailing', 'column' => 'scheduled_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was scheduled.'],
126 ['table' => 'civicrm_mailing', 'column' => 'approval_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was approved.'],
127 ['table' => 'civicrm_mailing_abtest', 'column' => 'created_date', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When was this item created'],
128 ['table' => 'civicrm_mailing_job', 'column' => 'scheduled_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was scheduled.'],
129 ['table' => 'civicrm_mailing_job', 'column' => 'start_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was started.'],
130 ['table' => 'civicrm_mailing_job', 'column' => 'end_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job ended.'],
131 ['table' => 'civicrm_mailing_spool', 'column' => 'added_at', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was added.'],
132 ['table' => 'civicrm_mailing_spool', 'column' => 'removed_at', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was removed.'],
133 ['table' => 'civicrm_subscription_history', 'column' => 'date', 'changed' => '4.7.27', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-21157', 'comment' => 'Date of the (un)subscription'],