Commit | Line | Data |
---|---|---|
d1930d05 TO |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
fee14197 | 4 | | CiviCRM version 5 | |
d1930d05 | 5 | +--------------------------------------------------------------------+ |
f299f7db | 6 | | Copyright CiviCRM LLC (c) 2004-2020 | |
d1930d05 TO |
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 | |
f299f7db | 31 | * @copyright CiviCRM LLC (c) 2004-2020 |
d1930d05 TO |
32 | */ |
33 | class CRM_Utils_Check_Component_Timestamps extends CRM_Utils_Check_Component { | |
34 | ||
35 | const DOCTOR_WHEN = 'https://github.com/civicrm/org.civicrm.doctorwhen'; | |
36 | ||
37 | /** | |
38 | * Check that various columns are TIMESTAMP and not DATETIME. (CRM-9683, etal) | |
39 | * | |
40 | * @return array | |
41 | */ | |
42 | public function checkSchema() { | |
be2fb01f | 43 | $problems = []; |
d1930d05 TO |
44 | foreach (self::getConvertedTimestamps() as $target) { |
45 | if (self::isFieldType($target['table'], $target['column'], 'datetime')) { | |
be2fb01f | 46 | $phrases = []; |
874ee386 TO |
47 | $phrases[] = sprintf('<em>%s.%s</em>', $target['table'], $target['column']); |
48 | ||
d1930d05 | 49 | if ($target['changed']) { |
874ee386 | 50 | $phrases[] = sprintf('(New sites default to TIMESTAMP in v%s+)', $target['changed']); |
d1930d05 TO |
51 | } |
52 | else { | |
874ee386 TO |
53 | $phrases[] = '(Experimental suggestion)'; |
54 | } | |
55 | ||
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']); | |
d1930d05 | 58 | } |
874ee386 TO |
59 | |
60 | $problems[] = implode(' ', $phrases); | |
d1930d05 TO |
61 | } |
62 | } | |
63 | ||
be2fb01f | 64 | $messages = []; |
d1930d05 TO |
65 | if ($problems) { |
66 | $messages[] = new CRM_Utils_Check_Message( | |
67 | __FUNCTION__ . md5(implode(',', $problems)), | |
68 | '<p>' . | |
874ee386 | 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".') . |
d1930d05 TO |
70 | '</p>' . |
71 | '<ul><li>' . | |
72 | implode('</li><li>', $problems) . | |
73 | '</li></ul>' . | |
74 | '<p>' . | |
874ee386 TO |
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>.') . |
76 | '</p>' . | |
77 | '<p>' . | |
be2fb01f | 78 | ts('For further discussion, please visit %1', [ |
d1930d05 | 79 | 1 => sprintf('<a href="%s" target="_blank">%s</a>', self::DOCTOR_WHEN, self::DOCTOR_WHEN), |
be2fb01f | 80 | ]) . |
d1930d05 | 81 | '</p>', |
874ee386 | 82 | ts('Timestamps and Timezones'), |
d1930d05 TO |
83 | \Psr\Log\LogLevel::NOTICE, |
84 | 'fa-clock-o' | |
85 | ); | |
86 | } | |
87 | return $messages; | |
88 | } | |
89 | ||
90 | /** | |
91 | * @param string $table | |
92 | * Ex: 'civicrm_log'. | |
93 | * @param string $column | |
94 | * Ex: 'modified_date'. | |
95 | * @param string $expectType | |
96 | * Ex: 'datetime' or 'timestamp'. | |
97 | * @return bool | |
98 | */ | |
99 | public static function isFieldType($table, $column, $expectType) { | |
100 | $result = FALSE; | |
101 | $dao = CRM_Core_DAO::executeQuery('DESC ' . $table); | |
102 | while ($dao->fetch()) { | |
103 | if ($dao->Field === $column && strtolower($dao->Type) === $expectType) { | |
104 | $result = TRUE; | |
105 | } | |
106 | } | |
d1930d05 TO |
107 | return $result; |
108 | } | |
109 | ||
110 | public static function getConvertedTimestamps() { | |
be2fb01f CW |
111 | return [ |
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'], | |
134 | ]; | |
d1930d05 TO |
135 | } |
136 | ||
137 | } |