328 lines
12 KiB
Perl
Executable File
328 lines
12 KiB
Perl
Executable File
#!/usr/bin/perl -w
|
|
# The contents of this file are subject to the Mozilla Public
|
|
# License Version 1.1 (the "License"); you may not use this file
|
|
# except in compliance with the License. You may obtain a copy of
|
|
# the License at http://www.mozilla.org/MPL/
|
|
#
|
|
# Software distributed under the License is distributed on an "AS
|
|
# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
|
|
# implied. See the License for the specific language governing
|
|
# rights and limitations under the License.
|
|
#
|
|
# The Original Code is the Bugzilla Bug Tracking System.
|
|
#
|
|
# The Initial Developer of the Original Code is Everything Solved.
|
|
# Portions created by Everything Solved are Copyright (C) 2006
|
|
# Everything Solved. All Rights Reserved.
|
|
#
|
|
# Contributor(s): Max Kanat-Alexander <mkanat@bugzilla.org>
|
|
# Vitaliy Filippov <vitalif@mail.ru>
|
|
|
|
use strict;
|
|
use lib qw(. lib);
|
|
use Bugzilla;
|
|
use Bugzilla::Constants;
|
|
use Bugzilla::DB;
|
|
use Bugzilla::Install::Util qw(indicate_progress);
|
|
use Bugzilla::Util;
|
|
|
|
my $db = {
|
|
source => {
|
|
type => '',
|
|
name => 'bugs',
|
|
user => 'bugs',
|
|
password => '',
|
|
host => 'localhost',
|
|
},
|
|
target => {
|
|
type => '',
|
|
name => 'bugs',
|
|
user => 'bugs',
|
|
password => '',
|
|
host => 'localhost',
|
|
},
|
|
};
|
|
|
|
# Read parameters
|
|
while (@ARGV)
|
|
{
|
|
my $a = shift @ARGV;
|
|
if ($a =~ /--(\w+)-(\w+)/)
|
|
{
|
|
$db->{lc $1}->{lc $2} = shift @ARGV;
|
|
}
|
|
}
|
|
|
|
if (!$db->{source}->{type} || !$db->{target}->{type})
|
|
{
|
|
print STDERR "Bugzilla database copy script
|
|
|
|
USAGE: perl contrib/bzdbcopy.pl --source-type mysql --source-host localhost \\
|
|
--source-name bugs --source-user bugs --source-password bugs \\
|
|
--target-type pg --target-host localhost \\
|
|
--target-name bugs --target-user bugs --target-password bugs
|
|
|
|
Here, --source-* are the type, host, database name, user and password
|
|
for the source database, and --target-* are the same for the target one.
|
|
";
|
|
exit;
|
|
}
|
|
|
|
print "Connecting to the '" . $db->{source}->{name} . "' source database on " . $db->{source}->{type} . "...\n";
|
|
my $source_db = Bugzilla::DB::_connect(
|
|
$db->{source}->{type}, $db->{source}->{host}, $db->{source}->{name},
|
|
undef, undef, $db->{source}->{user}, $db->{source}->{password}
|
|
);
|
|
# Don't read entire tables into memory.
|
|
if (lc($db->{source}->{type}) eq 'mysql')
|
|
{
|
|
$source_db->{mysql_use_result} = 1;
|
|
# MySQL cannot have two queries running at the same time. Ensure the schema
|
|
# is loaded from the database so bz_column_info will not execute a query
|
|
$source_db->_bz_real_schema;
|
|
}
|
|
|
|
print "Connecting to the '" . $db->{target}->{name} . "' target database on " . $db->{target}->{type} . "...\n";
|
|
my $target_db = Bugzilla::DB::_connect(
|
|
$db->{target}->{type}, $db->{target}->{host}, $db->{target}->{name},
|
|
undef, undef, $db->{target}->{user}, $db->{target}->{password}
|
|
);
|
|
my $ident_char = $target_db->get_info(29); # SQL_IDENTIFIER_QUOTE_CHAR
|
|
|
|
my @src_tables = $source_db->_bz_real_schema->get_table_list;
|
|
|
|
# We don't want to touch the schema storage and the full-text index
|
|
@src_tables = grep { $_ ne 'bz_schema' && $_ ne 'bugs_fulltext' } @src_tables;
|
|
|
|
foreach my $table (@src_tables)
|
|
{
|
|
my $st = $source_db->bz_table_info($table);
|
|
my $tt = $target_db->bz_table_info($table);
|
|
if (!$tt)
|
|
{
|
|
# Create tables that do not exist in the target DB (usually custom field tables)
|
|
$target_db->_bz_schema->{schema}->{$table} = $st;
|
|
$target_db->_bz_schema->{abstract_schema}->{$table} = $st;
|
|
$target_db->bz_add_table($table);
|
|
}
|
|
else
|
|
{
|
|
# Create fields that do not exist in the target DB (usually custom fields in 'bugs')
|
|
my %tf = @{$tt->{FIELDS}};
|
|
my %sf = @{$st->{FIELDS}};
|
|
for (keys %sf)
|
|
{
|
|
if (!$tf{$_})
|
|
{
|
|
push @{$target_db->_bz_schema->{schema}->{$table}->{FIELDS}}, $_, $sf{$_};
|
|
push @{$target_db->_bz_schema->{abstract_schema}->{$table}->{FIELDS}}, $_, $sf{$_};
|
|
$target_db->bz_add_column($table, $_);
|
|
}
|
|
}
|
|
%tf = @{$tt->{INDEXES} || []};
|
|
%sf = @{$st->{INDEXES} || []};
|
|
for (keys %sf)
|
|
{
|
|
my $n = $_;
|
|
$n = 'PRIMARY' if lc($n) eq $table.'_primary_idx';
|
|
if (!$tf{$n})
|
|
{
|
|
$target_db->bz_add_index($table, $n, $sf{$_});
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
# Intersect source and target table sets
|
|
my %st = map { $_ => 1 } @src_tables;
|
|
my @table_list = grep { $st{$_} } $target_db->_bz_real_schema->get_table_list;
|
|
|
|
# Instead of figuring out some fancy algorithm to insert data in the right
|
|
# order and not break FK integrity, we just drop them all.
|
|
$target_db->bz_drop_foreign_keys();
|
|
# We start a transaction on the target DB, which helps when we're doing
|
|
# so many inserts.
|
|
$target_db->bz_start_transaction();
|
|
foreach my $table (@table_list)
|
|
{
|
|
my @serial_cols;
|
|
print "Reading data from the source '$table' table on " . $db->{source}->{type} . "...\n";
|
|
my @table_columns = $target_db->bz_table_columns_real($table);
|
|
# The column names could be quoted using the quote identifier char
|
|
# Remove these chars as different databases use different quote chars
|
|
@table_columns = map { s/^\Q$ident_char\E?(.*?)\Q$ident_char\E?$/$1/; $_ } @table_columns;
|
|
|
|
my ($total) = $source_db->selectrow_array("SELECT COUNT(*) FROM $table");
|
|
my $select_query = "SELECT " . join(',', @table_columns) . " FROM $table";
|
|
my $select_sth = $source_db->prepare($select_query);
|
|
$select_sth->execute();
|
|
|
|
my $insert_query = "INSERT INTO $table ( " . join(',', @table_columns) . " ) VALUES (";
|
|
$insert_query .= '?,' foreach @table_columns;
|
|
# Remove the last comma.
|
|
chop($insert_query);
|
|
$insert_query .= ")";
|
|
my $insert_sth = $target_db->prepare($insert_query);
|
|
|
|
print "Clearing out the target '$table' table on " . $db->{target}->{type} . "...\n";
|
|
$target_db->do("DELETE FROM $table");
|
|
|
|
# Oracle doesn't like us manually inserting into tables that have
|
|
# auto-increment PKs set, because of the way we made auto-increment
|
|
# fields work.
|
|
if ($target_db->isa('Bugzilla::DB::Oracle'))
|
|
{
|
|
foreach my $column (@table_columns)
|
|
{
|
|
my $col_info = $source_db->bz_column_info($table, $column);
|
|
if ($col_info && $col_info->{TYPE} =~ /SERIAL/i)
|
|
{
|
|
print "Dropping the sequence + trigger on $table.$column...\n";
|
|
$target_db->do("DROP TRIGGER ${table}_${column}_TR");
|
|
$target_db->do("DROP SEQUENCE ${table}_${column}_SEQ");
|
|
}
|
|
}
|
|
}
|
|
|
|
print "Writing data to the target '$table' table on " . $db->{target}->{type} . "...\n";
|
|
my $count = 0;
|
|
while (my $row = $select_sth->fetchrow_arrayref)
|
|
{
|
|
# Each column needs to be bound separately, because
|
|
# many columns need to be dealt with specially.
|
|
my $colnum = 0;
|
|
foreach my $column (@table_columns)
|
|
{
|
|
# bind_param args start at 1, but arrays start at 0.
|
|
my $param_num = $colnum + 1;
|
|
my $already_bound;
|
|
|
|
# Certain types of columns need special handling.
|
|
my $col_info = $source_db->bz_column_info($table, $column);
|
|
if ($col_info && $col_info->{TYPE} eq 'LONGBLOB')
|
|
{
|
|
$insert_sth->bind_param($param_num, $row->[$colnum], $target_db->BLOB_TYPE);
|
|
$already_bound = 1;
|
|
}
|
|
elsif ($col_info && $col_info->{TYPE} =~ /decimal/)
|
|
{
|
|
# In MySQL, decimal cols can be too long.
|
|
my $col_type = $col_info->{TYPE};
|
|
$col_type =~ /decimal\((\d+),(\d+)\)/;
|
|
my ($precision, $decimals) = ($1, $2);
|
|
# If it's longer than precision + decimal point
|
|
if (length $row->[$colnum] > $precision + 1)
|
|
{
|
|
# Truncate it to the highest allowed value.
|
|
my $orig_value = $row->[$colnum];
|
|
$row->[$colnum] = '';
|
|
my $non_decimal = $precision - $decimals;
|
|
$row->[$colnum] .= '9' while ($non_decimal--);
|
|
$row->[$colnum] .= '.';
|
|
$row->[$colnum] .= '9' while ($decimals--);
|
|
print "Truncated value $orig_value to " . $row->[$colnum] . " for $table.$column.\n";
|
|
}
|
|
}
|
|
elsif ($col_info && $col_info->{TYPE} =~ /DATETIME/i)
|
|
{
|
|
my $date = $row->[$colnum];
|
|
# MySQL can have strange invalid values for Datetimes.
|
|
if ($date && $date eq '0000-00-00 00:00:00')
|
|
{
|
|
$row->[$colnum] = '1901-01-01 00:00:00';
|
|
}
|
|
}
|
|
|
|
$insert_sth->bind_param($param_num, $row->[$colnum]) unless $already_bound;
|
|
$colnum++;
|
|
}
|
|
|
|
$insert_sth->execute();
|
|
$count++;
|
|
indicate_progress({ current => $count, total => $total, every => 100 });
|
|
}
|
|
|
|
# For some DBs, we have to do clever things with auto-increment fields.
|
|
foreach my $column (@table_columns)
|
|
{
|
|
next if $target_db->isa('Bugzilla::DB::Mysql');
|
|
my $col_info = $source_db->bz_column_info($table, $column);
|
|
if ($col_info && $col_info->{TYPE} =~ /SERIAL/i)
|
|
{
|
|
my ($max_val) = $target_db->selectrow_array("SELECT MAX($column) FROM $table");
|
|
# Set the sequence to the current max value + 1.
|
|
$max_val = 0 if !defined $max_val;
|
|
$max_val++;
|
|
print "\nSetting the next value for $table.$column to $max_val.";
|
|
if ($target_db->isa('Bugzilla::DB::Pg'))
|
|
{
|
|
# PostgreSQL doesn't like it when you insert values into
|
|
# a serial field; it doesn't increment the counter automatically.
|
|
$target_db->bz_set_next_serial_value($table, $column);
|
|
}
|
|
elsif ($target_db->isa('Bugzilla::DB::Oracle'))
|
|
{
|
|
# Oracle increments the counter on every insert, and *always*
|
|
# sets the field, even if you gave it a value. So if there
|
|
# were already rows in the target DB (like the default rows
|
|
# created by checksetup), you'll get crazy values in your
|
|
# id columns. So we just dropped the sequences above and
|
|
# we re-create them here, starting with the right number.
|
|
my @sql = $target_db->_bz_real_schema->_get_create_seq_ddl($table, $column, $max_val);
|
|
$target_db->do($_) foreach @sql;
|
|
}
|
|
}
|
|
}
|
|
|
|
print "\n\n";
|
|
}
|
|
|
|
print "Committing changes to the target database...\n";
|
|
$target_db->bz_commit_transaction();
|
|
$target_db->bz_setup_foreign_keys();
|
|
|
|
print "All done! Make sure to run checksetup.pl on the new DB.\n";
|
|
$source_db->disconnect;
|
|
$target_db->disconnect;
|
|
|
|
1;
|
|
|
|
__END__
|
|
|
|
=head1 NAME
|
|
|
|
bzdbcopy.pl - Copies data from one Bugzilla database to another.
|
|
|
|
=head1 USAGE
|
|
|
|
=over
|
|
|
|
=item 1. Setup the 'localconfig' for the target database
|
|
|
|
=item 2. Run ./checksetup.pl inside your Bugzilla installation directory
|
|
on the target database to populate it with empty tables
|
|
|
|
=item 3. Run contrib/bzdbcopy.pl
|
|
|
|
cd <bugzilla_installation_dir>
|
|
perl contrib/bzdbcopy.pl --source-type mysql --source-host localhost \
|
|
--source-name bugs --source-user bugs --source-password bugs \
|
|
--target-type pg --target-host localhost \
|
|
--target-name bugs --target-user bugs --target-password bugs
|
|
|
|
Here, --source-* are the type, host, database name, user and password
|
|
for the source database, and --target-* are the same for the target one.
|
|
|
|
=back
|
|
|
|
=head1 DESCRIPTION
|
|
|
|
The intended use of this script is to copy data from an installation
|
|
running on one DB platform to an installation running on another
|
|
DB platform.
|
|
|
|
Note: Both schemas must already exist and both must be created/updated
|
|
by the B<SAME> version of checksetup.pl. B<No custom fields> must exist
|
|
in the target database. This script will B<DESTROY ALL CURRENT DATA> in
|
|
the target database.
|