diff options
Diffstat (limited to 'scripts/convert_datetime.pl')
-rwxr-xr-x | scripts/convert_datetime.pl | 125 |
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"; } |