CRM/Utils/Check/Component/Timestamps.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Utils_Check_Component_Timestamps extends CRM_Utils_Check_Component {
  18
  19   const DOCTOR_WHEN = 'https://github.com/civicrm/org.civicrm.doctorwhen';
  20
  21   /**
  22    * Check that various columns are TIMESTAMP and not DATETIME. (CRM-9683, etal)
  23    *
  24    * @return CRM_Utils_Check_Message[]
  25    */
  26   public function checkSchema() {
  27     $problems = [];
  28     foreach (self::getConvertedTimestamps() as $target) {
  29       if (self::isFieldType($target['table'], $target['column'], 'datetime')) {
  30         $phrases = [];
  31         $phrases[] = sprintf('<em>%s.%s</em>', $target['table'], $target['column']);
  32
  33         if ($target['changed']) {
  34           $phrases[] = sprintf('(New sites default to TIMESTAMP in v%s+)', $target['changed']);
  35         }
  36         else {
  37           $phrases[] = '(Experimental suggestion)';
  38         }
  39
  40         if (isset($target['jira'])) {
  41           $phrases[] = sprintf(' [<a href="https://issues.civicrm.org/jira/browse/%s" target="_blank">%s</a>]', $target['jira'], $target['jira']);
  42         }
  43
  44         $problems[] = implode(' ', $phrases);
  45       }
  46     }
  47
  48     $messages = [];
  49     if ($problems) {
  50       $messages[] = new CRM_Utils_Check_Message(
  51         __FUNCTION__ . md5(implode(',', $problems)),
  52         '<p>' .
  53         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".') .
  54         '</p>' .
  55         '<ul><li>' .
  56         implode('</li><li>', $problems) .
  57         '</li></ul>' .
  58         '<p>' .
  59         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>.') .
  60         '</p>' .
  61         '<p>' .
  62         ts('For further discussion, please visit %1', [
  63           1 => sprintf('<a href="%s" target="_blank">%s</a>', self::DOCTOR_WHEN, self::DOCTOR_WHEN),
  64         ]) .
  65         '</p>',
  66         ts('Timestamps and Timezones'),
  67         \Psr\Log\LogLevel::NOTICE,
  68         'fa-clock-o'
  69       );
  70     }
  71     return $messages;
  72   }
  73
  74   /**
  75    * @param string $table
  76    *   Ex: 'civicrm_log'.
  77    * @param string $column
  78    *   Ex: 'modified_date'.
  79    * @param string $expectType
  80    *   Ex: 'datetime' or 'timestamp'.
  81    * @return bool
  82    */
  83   public static function isFieldType($table, $column, $expectType) {
  84     $result = FALSE;
  85     $dao = CRM_Core_DAO::executeQuery('DESC ' . $table);
  86     while ($dao->fetch()) {
  87       if ($dao->Field === $column && strtolower($dao->Type) === $expectType) {
  88         $result = TRUE;
  89       }
  90     }
  91     return $result;
  92   }
  93
  94   public static function getConvertedTimestamps() {
  95     return [
  96       ['table' => 'civicrm_cache', 'column' => 'created_date', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When was the cache item created'],
  97       ['table' => 'civicrm_cache', 'column' => 'expired_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'When should the cache item expire'],
  98       ['table' => 'civicrm_job', 'column' => 'last_run', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'When was this cron entry last run'],
  99       ['table' => 'civicrm_mailing_event_bounce', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this bounce event occurred.'],
 100       ['table' => 'civicrm_mailing_event_confirm', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this confirmation event occurred.'],
 101       ['table' => 'civicrm_mailing_event_delivered', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this delivery event occurred.'],
 102       ['table' => 'civicrm_mailing_event_forward', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this forward event occurred.'],
 103       ['table' => 'civicrm_mailing_event_opened', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this open event occurred.'],
 104       ['table' => 'civicrm_mailing_event_reply', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this reply event occurred.'],
 105       ['table' => 'civicrm_mailing_event_subscribe', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this subscription event occurred.'],
 106       ['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.'],
 107       ['table' => 'civicrm_mailing_event_unsubscribe', 'column' => 'time_stamp', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When this delivery event occurred.'],
 108       ['table' => 'civicrm_mailing', 'column' => 'created_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was created.'],
 109       ['table' => 'civicrm_mailing', 'column' => 'scheduled_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was scheduled.'],
 110       ['table' => 'civicrm_mailing', 'column' => 'approval_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'Date and time this mailing was approved.'],
 111       ['table' => 'civicrm_mailing_abtest', 'column' => 'created_date', 'changed' => '4.7.20', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-9683', 'comment' => 'When was this item created'],
 112       ['table' => 'civicrm_mailing_job', 'column' => 'scheduled_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was scheduled.'],
 113       ['table' => 'civicrm_mailing_job', 'column' => 'start_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was started.'],
 114       ['table' => 'civicrm_mailing_job', 'column' => 'end_date', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job ended.'],
 115       ['table' => 'civicrm_mailing_spool', 'column' => 'added_at', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was added.'],
 116       ['table' => 'civicrm_mailing_spool', 'column' => 'removed_at', 'changed' => '4.7.20', 'jira' => 'CRM-9683', 'comment' => 'date on which this job was removed.'],
 117       ['table' => 'civicrm_subscription_history', 'column' => 'date', 'changed' => '4.7.27', 'default' => 'CURRENT_TIMESTAMP', 'jira' => 'CRM-21157', 'comment' => 'Date of the (un)subscription'],
 118     ];
 119   }
 120
 121 }