summaryrefslogtreecommitdiffstats
path: root/scripts/convert_datetime.pl
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/convert_datetime.pl')
-rwxr-xr-xscripts/convert_datetime.pl125
1 files changed, 66 insertions, 59 deletions
diff --git a/scripts/convert_datetime.pl b/scripts/convert_datetime.pl
index 279eb84b8..8118ab674 100755
--- a/scripts/convert_datetime.pl
+++ b/scripts/convert_datetime.pl
@@ -12,8 +12,6 @@ use warnings;
use lib qw(. lib local/lib/perl5);
-
-
use Bugzilla;
use Bugzilla::Constants;
use Bugzilla::DB;
@@ -27,35 +25,40 @@ 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
+ 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");
+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')");
+ || 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";
+ || 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
+ "SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ?
- AND DATA_TYPE='datetime'",
- undef, Bugzilla->localconfig->{db_name});
+ AND DATA_TYPE='datetime'", undef, Bugzilla->localconfig->{db_name}
+);
my $total = scalar @$rows;
if (!$total) {
- print "No DATETIME columns found.\n";
- exit;
+ print "No DATETIME columns found.\n";
+ exit;
}
print STDERR <<EOF;
@@ -68,62 +71,66 @@ 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 ($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;
+ my ($table, $column) = @$row;
- if (my $column_info = $bugs_dbh->bz_column_info($table, $column)) {
- say "Converting $table.$column to TIMESTAMP...";
+ 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);
- }
+ # 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");
+ # 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);
+ # 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')");
+ # 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');
+ # 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.");
+ # 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";
+ print "Errors:\n" . join("\n", @errors) . "\n";
}
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-05-03 12:16:39 +0200