Bug 232193 - bmo's systems (webheads, database, etc) should use UTC natively for o/s timezone and date storage

5 files changed, 159 insertions, 83 deletions

diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index bf8854c9c..8a545bd87 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -566,80 +566,6 @@ sub bz_setup_database {
         $self->_bz_store_real_schema;
     }
 
-    # The old timestamp fields need to be adjusted here instead of in
-    # checksetup. Otherwise the UPDATE statements inside of bz_add_column
-    # will cause accidental timestamp updates.
-    # The code that does this was moved here from checksetup.
-
-    # 2002-08-14 - bbaetz@student.usyd.edu.au - bug 153578
-    # attachments creation time needs to be a datetime, not a timestamp
-    my $attach_creation = 
-        $self->bz_column_info("attachments", "creation_ts");
-    if ($attach_creation && $attach_creation->{TYPE} =~ /^TIMESTAMP/i) {
-        print "Fixing creation time on attachments...\n";
-
-        my $sth = $self->prepare("SELECT COUNT(attach_id) FROM attachments");
-        $sth->execute();
-        my ($attach_count) = $sth->fetchrow_array();
-
-        if ($attach_count > 1000) {
-            print "This may take a while...\n";
-        }
-        my $i = 0;
-
-        # This isn't just as simple as changing the field type, because
-        # the creation_ts was previously updated when an attachment was made
-        # obsolete from the attachment creation screen. So we have to go
-        # and recreate these times from the comments..
-        $sth = $self->prepare("SELECT bug_id, attach_id, submitter_id " .
-                               "FROM attachments");
-        $sth->execute();
-
-        # Restrict this as much as possible in order to avoid false 
-        # positives, and keep the db search time down
-        my $sth2 = $self->prepare("SELECT bug_when FROM longdescs 
-                                    WHERE bug_id=? AND who=? 
-                                          AND thetext LIKE ?
-                                 ORDER BY bug_when " . $self->sql_limit(1));
-        while (my ($bug_id, $attach_id, $submitter_id) 
-                  = $sth->fetchrow_array()) 
-        {
-            $sth2->execute($bug_id, $submitter_id, 
-                "Created an attachment (id=$attach_id)%");
-            my ($when) = $sth2->fetchrow_array();
-            if ($when) {
-                $self->do("UPDATE attachments " .
-                             "SET creation_ts='$when' " .
-                           "WHERE attach_id=$attach_id");
-            } else {
-                print "Warning - could not determine correct creation"
-                      . " time for attachment $attach_id on bug $bug_id\n";
-            }
-            ++$i;
-            print "Converted $i of $attach_count attachments\n" if !($i % 1000);
-        }
-        print "Done - converted $i attachments\n";
-
-        $self->bz_alter_column("attachments", "creation_ts", 
-                               {TYPE => 'DATETIME', NOTNULL => 1});
-    }
-
-    # 2004-08-29 - Tomas.Kopal@altap.cz, bug 257303
-    # Change logincookies.lastused type from timestamp to datetime
-    my $login_lastused = $self->bz_column_info("logincookies", "lastused");
-    if ($login_lastused && $login_lastused->{TYPE} =~ /^TIMESTAMP/i) {
-        $self->bz_alter_column('logincookies', 'lastused', 
-                               { TYPE => 'DATETIME',  NOTNULL => 1});
-    }
-
-    # 2005-01-17 - Tomas.Kopal@altap.cz, bug 257315
-    # Change bugs.delta_ts type from timestamp to datetime 
-    my $bugs_deltats = $self->bz_column_info("bugs", "delta_ts");
-    if ($bugs_deltats && $bugs_deltats->{TYPE} =~ /^TIMESTAMP/i) {
-        $self->bz_alter_column('bugs', 'delta_ts', 
-                               {TYPE => 'DATETIME', NOTNULL => 1});
-    }
-
     # 2005-09-24 - bugreport@peshkin.net, bug 307602
     # Make sure that default 4G table limit is overridden
     my $attach_data_create = $self->selectrow_array(
diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm
index b0a40586f..bcc663e83 100644
--- a/Bugzilla/DB/Schema/Mysql.pm
+++ b/Bugzilla/DB/Schema/Mysql.pm
@@ -119,7 +119,7 @@ sub _initialize {
 
         LONGBLOB =>     'longblob',
 
-        DATETIME =>     'datetime',
+        DATETIME =>     'timestamp',
         DATE     =>     'date',
     };
 
@@ -396,4 +396,25 @@ sub get_rename_column_ddl {
     return ("ALTER TABLE $table CHANGE COLUMN $old_name $new_name $def");
 }
 
+sub get_type_ddl {
+    my $self = shift;
+    my $type_ddl = $self->SUPER::get_type_ddl(@_);
+
+    # TIMESTAMPS as of 5.6.6 still default to
+    # 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'
+    # unless explicitly setup in the table definition. This will change in future releases
+    # and can be disabled by using 'explicit_defaults_for_timestamp = 1' in my.cnf.
+    # So instead, we explicitly setup TIMESTAMP types to not be automatic.
+    if ($type_ddl =~ /^timestamp/i) {
+        if ($type_ddl !~ /NOT NULL/) {
+            $type_ddl .= ' NULL DEFAULT NULL';
+        }
+        if ($type_ddl =~ /NOT NULL/ && $type_ddl !~ /DEFAULT/) {
+            $type_ddl .= ' DEFAULT CURRENT_TIMESTAMP';
+        }
+    }
+
+    return $type_ddl;
+}
+
 1;
diff --git a/extensions/BMO/lib/Reports/ReleaseTracking.pm b/extensions/BMO/lib/Reports/ReleaseTracking.pm
index 0d9a51583..8894a5169 100644
--- a/extensions/BMO/lib/Reports/ReleaseTracking.pm
+++ b/extensions/BMO/lib/Reports/ReleaseTracking.pm
@@ -367,9 +367,9 @@ sub report {
             push @where, "(a.fieldid = ?)";
             push @params, $q->{field_id};
 
-            push @where, "(a.bug_when >= ?)";
+            push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') >= ?)";
             push @params, $q->{start_date} . ' 00:00:00';
-            push @where, "(a.bug_when <= ?)";
+            push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') <= ?)";
             push @params, $q->{end_date} . ' 23:59:59';
 
             push @where, "(a.added LIKE ?)";
diff --git a/extensions/BMO/lib/Reports/UserActivity.pm b/extensions/BMO/lib/Reports/UserActivity.pm
index 04810c2ec..0a8e79e1f 100644
--- a/extensions/BMO/lib/Reports/UserActivity.pm
+++ b/extensions/BMO/lib/Reports/UserActivity.pm
@@ -126,7 +126,8 @@ sub report {
         INNER JOIN profiles
                 ON profiles.userid = bugs_activity.who
              WHERE profiles.login_name IN ($who_bits)
-                   AND bugs_activity.bug_when >= ? AND bugs_activity.bug_when <= ?
+                   AND CONVERT_TZ(bugs_activity.bug_when, 'UTC', 'America/Los_Angeles') >= ?
+                   AND CONVERT_TZ(bugs_activity.bug_when, 'UTC', 'America/Los_Angeles') <= ?
                    $activity_where
 
         UNION ALL
@@ -147,8 +148,8 @@ sub report {
         INNER JOIN profiles
                 ON profiles.userid = longdescs_tags_activity.who
              WHERE profiles.login_name IN ($who_bits)
-                   AND longdescs_tags_activity.bug_when >= ?
-                   AND longdescs_tags_activity.bug_when <= ?
+                   AND CONVERT_TZ(longdescs_tags_activity.bug_when, 'UTC', 'America/Los_Angeles') >= ?
+                   AND CONVERT_TZ(longdescs_tags_activity.bug_when, 'UTC', 'America/Los_Angeles') <= ?
                   $tags_activity_where
 
         UNION ALL
@@ -167,7 +168,8 @@ sub report {
         INNER JOIN profiles
                 ON profiles.userid = bugs.reporter
              WHERE profiles.login_name IN ($who_bits)
-                   AND bugs.creation_ts >= ? AND bugs.creation_ts <= ?
+                   AND CONVERT_TZ(bugs.creation_ts, 'UTC', 'America/Los_Angeles') >= ?
+                   AND CONVERT_TZ(bugs.creation_ts, 'UTC', 'America/Los_Angeles') <= ?
 
         UNION ALL
 
@@ -185,7 +187,8 @@ sub report {
         INNER JOIN profiles
                 ON profiles.userid = longdescs.who
              WHERE profiles.login_name IN ($who_bits)
-                   AND longdescs.bug_when >= ? AND longdescs.bug_when <= ?
+                   AND CONVERT_TZ(longdescs.bug_when, 'UTC', 'America/Los_Angeles') >= ?
+                   AND CONVERT_TZ(longdescs.bug_when, 'UTC', 'America/Los_Angeles') <= ?
                    $comment_filter
 
         UNION ALL
@@ -204,7 +207,8 @@ sub report {
         INNER JOIN profiles
                 ON profiles.userid = attachments.submitter_id
              WHERE profiles.login_name IN ($who_bits)
-                   AND attachments.creation_ts >= ? AND attachments.creation_ts <= ?
+                   AND CONVERT_TZ(attachments.creation_ts, 'UTC', 'America/Los_Angeles') >= ?
+                   AND CONVERT_TZ(attachments.creation_ts, 'UTC', 'America/Los_Angeles') <= ?
                    $attachments_where
 
           ORDER BY $order ";
diff --git a/scripts/convert_datetime.pl b/scripts/convert_datetime.pl
new file mode 100644
index 000000000..b87e3561a
--- /dev/null
+++ b/scripts/convert_datetime.pl
@@ -0,0 +1,125 @@
+#!/usr/bin/perl
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+
+use 5.10.1;
+use strict;
+use warnings;
+
+use Bugzilla;
+use Bugzilla::Constants;
+use Bugzilla::DB;
+
+Bugzilla->usage_mode(USAGE_MODE_CMDLINE);
+
+my $bugs_dbh    = Bugzilla->dbh;
+my $localconfig = Bugzilla->localconfig;
+
+my $root_mysql_pw = shift;
+defined $root_mysql_pw || die "MySQL root password required.\n";
+
+my $mysql_dbh = Bugzilla::DB::_connect({
+    db_driver => $localconfig->{db_driver},
+    db_host   => $localconfig->{db_host},
+    db_name   => 'mysql',
+    db_user   => 'root',
+    db_pass   => $root_mysql_pw
+});
+
+# Check that the mysql timezones are populated and up to date
+my $mysql_tz_install
+  = "Please populate using instuctions at http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html#time-zone-installation and re-run this script.";
+my $mysql_tz_count = $mysql_dbh->selectrow_array("SELECT COUNT(*) FROM mysql.time_zone_name");
+$mysql_tz_count
+  || die "The timezone table mysql.time_zone_name has not been populated.\n$mysql_tz_install\n";
+my $mysql_tz_date1 = $mysql_dbh->selectrow_array("SELECT CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central')");
+my $mysql_tz_date2 = $mysql_dbh->selectrow_array("SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central')");
+($mysql_tz_date1 eq $mysql_tz_date2)
+  || die "The timezone table mysql.time_zone_name needs to be updated.\n$mysql_tz_install\n";
+
+my $rows = $mysql_dbh->selectall_arrayref(
+    "SELECT TABLE_NAME, COLUMN_NAME
+       FROM INFORMATION_SCHEMA.COLUMNS
+      WHERE TABLE_SCHEMA = ?
+            AND DATA_TYPE='datetime'",
+    undef, Bugzilla->localconfig->{db_name});
+my $total = scalar @$rows;
+
+if (!$total) {
+    print "No DATETIME columns found.\n";
+    exit;
+}
+
+print STDERR <<EOF;
+About to convert $total DATETIME columns to TIMESTAMP columns and migrate their values from PST to UTC.
+
+Press <Ctrl-C> to stop or <Enter> to continue...
+EOF
+getc();
+
+# Store any indexes we may need to drop/add later
+my %indexes;
+foreach my $row (@$rows) {
+    my ($table, $column) = @$row;
+    next if exists $indexes{$table} && exists $indexes{$table}{$column};
+    my $table_info = $bugs_dbh->bz_table_info($table);
+    next if !exists $table_info->{INDEXES};
+    my $table_indexes = $table_info->{INDEXES};
+    for (my $i = 0; $i < @$table_indexes; $i++) {
+        my $name       = $table_indexes->[$i];
+        my $definition = $table_indexes->[$i+1];
+        if ((ref $definition eq 'HASH' && grep($column eq $_, @{ $definition->{FIELDS} }))
+                || (ref $definition eq 'ARRAY' && grep($column eq $_, @$definition)))
+        {
+            $indexes{$table} ||= {};
+            $indexes{$table}->{$column} = { name => $name, definition => $definition };
+            last;
+        }
+    }
+}
+
+my @errors;
+foreach my $row (@$rows) {
+    my ($table, $column) = @$row;
+
+    if (my $column_info = $bugs_dbh->bz_column_info($table, $column)) {
+        say "Converting $table.$column to TIMESTAMP...";
+
+        # Drop any indexes first
+        if (exists $indexes{$table} && exists $indexes{$table}->{$column}) {
+            my $index_name = $indexes{$table}->{$column}->{name};
+            $bugs_dbh->bz_drop_index($table, $index_name);
+        }
+
+        # Rename current column to PST
+        $bugs_dbh->bz_rename_column($table, $column, $column . "_pst");
+
+        # Create the new UTC column
+        $column_info->{TYPE} = 'TIMESTAMP';
+        $column_info->{DEFAULT} = 'CURRENT_TIMESTAMP' if $column_info->{NOTNULL} && !$column_info->{DEFAULT};
+        $bugs_dbh->bz_add_column($table, $column, $column_info);
+
+        # Migrate the PST value to UTC
+        $bugs_dbh->do("UPDATE $table SET $column = CONVERT_TZ(" . $column . '_pst' . ", 'America/Los_Angeles', 'UTC')");
+
+        # Drop the old PST column
+        $bugs_dbh->bz_drop_column($table, $column . '_pst');
+
+        # And finally recreate the index if one existed for this column
+        if (exists $indexes{$table} && exists $indexes{$table}->{$column}) {
+            my $index_info = $indexes{$table}->{$column};
+            $bugs_dbh->bz_add_index($table, $index_info->{name}, $index_info->{definition});
+        }
+    }
+    else {
+        push(@errors, "$table.$column does not exist in bz_schema and will need to fixed manually.");
+    }
+}
+
+if (@errors) {
+    print "Errors:\n" . join("\n", @errors) . "\n";
+}