310 lines
8.3 KiB
Perl
310 lines
8.3 KiB
Perl
#!/usr/bin/perl
|
|
# Alternative Bugzilla database copy script
|
|
# Correctly handles custom fields and does no foreign key removal
|
|
|
|
# See help below $USAGE = ... or run this with --help
|
|
# Lines marked with "#!" comment in the end modify the DB
|
|
|
|
use strict;
|
|
|
|
my ($from, $from_user, $from_password);
|
|
my @ignore_tables;
|
|
|
|
# Runs before any 'uses'
|
|
BEGIN {
|
|
|
|
my $USAGE = <<EOF;
|
|
Bugzilla database copy script
|
|
|
|
USAGE: perl $0 --from DSN -u USER -p PASSWORD [--ignore TABLE]
|
|
DSN help: see perldoc DBI, perldoc DBD::mysql, perldoc DBD::Pg
|
|
|
|
Use --ignore TABLE only for some garbage tables.
|
|
Run this from contrib/ subdirectory of your Bugzilla installation.
|
|
Apply to a virgin Bugzilla database created by checksetup.pl of same Bugzilla version
|
|
Then run checksetup.pl again to populate bugs_fulltext.
|
|
|
|
Does the following:
|
|
1) Redefine existing fields with IDs taken from old database
|
|
2) Create custom fields with IDs taken from old database using Bugzilla classes
|
|
3) Copy table data in the correct order, ignoring garbage columns
|
|
4) Copy AUTOINCREMENT (MySQL) or SEQUENCE (PostgreSQL) values
|
|
EOF
|
|
|
|
# Parse command-line arguments
|
|
while ($_ = shift @ARGV)
|
|
{
|
|
if ($_ eq '--from')
|
|
{
|
|
$from = shift @ARGV;
|
|
}
|
|
elsif ($_ eq '--from-user' || $_ eq '-u')
|
|
{
|
|
$from_user = shift @ARGV;
|
|
}
|
|
elsif ($_ eq '--from-password' || $_ eq '-p')
|
|
{
|
|
$from_password = shift @ARGV;
|
|
}
|
|
elsif ($_ eq '--help' || $_ eq '-h')
|
|
{
|
|
print $USAGE;
|
|
exit;
|
|
}
|
|
elsif ($_ eq '--ignore')
|
|
{
|
|
push @ignore_tables, shift @ARGV;
|
|
}
|
|
}
|
|
|
|
unless ($from && $from_user)
|
|
{
|
|
print $USAGE;
|
|
exit;
|
|
}
|
|
$from_password ||= '';
|
|
|
|
}
|
|
|
|
use lib qw(..);
|
|
use DBI;
|
|
use Bugzilla;
|
|
use Bugzilla::Constants;
|
|
use Bugzilla::Field;
|
|
use Bugzilla::Extension;
|
|
|
|
# Pre-load all extensions
|
|
$Bugzilla::extension_packages = Bugzilla::Extension->load_all();
|
|
|
|
my %seen_tables = (
|
|
# Copied manually:
|
|
'fielddefs' => 1,
|
|
# Ignored:
|
|
'series_data' => 1,
|
|
'bz_schema' => 1,
|
|
'attach_data' => 1,
|
|
'bugs_fulltext' => 1,
|
|
'logincookies' => 1,
|
|
'tokens' => 1,
|
|
'globalauth' => 1,
|
|
'series1' => 1,
|
|
'bug_user_map' => 1,
|
|
'old_profiles' => 1,
|
|
'qwe' => 1,
|
|
);
|
|
|
|
# Ignore more tables
|
|
$seen_tables{$_} = 1 for @ignore_tables;
|
|
|
|
my %field_exclude = (
|
|
'fieldvaluecontrol' => 'field_id',
|
|
'bugs_activity' => 'fieldid',
|
|
'profiles_activity' => 'fieldid',
|
|
);
|
|
|
|
my $from = DBI->connect('DBI:mysql:database=bugs3new', 'bugzilla3', 'bugzilla3') || die "Can't connect to source DB";
|
|
my $to = Bugzilla->dbh || die "Can't connect to destination DB";
|
|
|
|
# Get table dependencies and autoincrement columns
|
|
my $table_depends = {};
|
|
my $autoincrement = {};
|
|
my ($bz_schema) = $from->selectrow_array('SELECT schema_data FROM bz_schema');
|
|
$bz_schema =~ s/^\$VAR1 = //;
|
|
$bz_schema = eval $bz_schema;
|
|
die $@ if $@;
|
|
|
|
for my $t (keys %$bz_schema)
|
|
{
|
|
for (my $i = 0; $i < @{$bz_schema->{$t}->{FIELDS}}; $i += 2)
|
|
{
|
|
my $f = $bz_schema->{$t}->{FIELDS}->[$i+1];
|
|
if ($f->{REFERENCES})
|
|
{
|
|
$table_depends->{$t}->{$f->{REFERENCES}->{TABLE}} = 1;
|
|
}
|
|
elsif ($f->{TYPE} =~ /serial/i)
|
|
{
|
|
$autoincrement->{$t} = $bz_schema->{$t}->{FIELDS}->[$i];
|
|
}
|
|
}
|
|
}
|
|
|
|
# All tables except ones for custom fields
|
|
my @copy_tables = getseq([ sort keys %$bz_schema ], \%seen_tables, $table_depends);
|
|
|
|
my $fielddefs = $from->selectall_hashref('SELECT * FROM fielddefs', 'name');
|
|
my $fieldsnew = $to->selectall_hashref('SELECT * FROM fielddefs WHERE name IN (\''.join('\',\'', keys %$fielddefs).'\')', 'name');
|
|
|
|
for (values %$fielddefs)
|
|
{
|
|
if ($_->{custom})
|
|
{
|
|
if ($_->{type} == FIELD_TYPE_SINGLE_SELECT ||
|
|
$_->{type} == FIELD_TYPE_MULTI_SELECT)
|
|
{
|
|
push @copy_tables, $_->{name};
|
|
}
|
|
if ($_->{type} == FIELD_TYPE_MULTI_SELECT)
|
|
{
|
|
push @copy_tables, 'bug_'.$_->{name};
|
|
}
|
|
}
|
|
}
|
|
|
|
my (@skip_fields, $maxkey);
|
|
|
|
print "Redefine existing fields\n";
|
|
$to->do('DELETE FROM profiles_activity'); #!
|
|
$to->do('DELETE FROM fielddefs WHERE name IN (\''.join('\',\'', keys %$fieldsnew).'\')'); #!
|
|
insertall_hashref($to, 'fielddefs', [ map { $fielddefs->{$_} } keys %$fieldsnew ]); #!
|
|
|
|
delete $fielddefs->{$_} for keys %$fieldsnew;
|
|
|
|
for (keys %$fielddefs)
|
|
{
|
|
if ($fielddefs->{$_}->{obsolete})
|
|
{
|
|
print "Skip field $_ (y/n)? ";
|
|
if (<STDIN> =~ /^\s*y/is)
|
|
{
|
|
push @skip_fields, $_;
|
|
next;
|
|
}
|
|
}
|
|
if (/^cf_/)
|
|
{
|
|
print "Creating field $_\n";
|
|
my $field = Bugzilla::Field->create($fielddefs->{$_}); #!
|
|
}
|
|
else
|
|
{
|
|
insertall_hashref($to, 'fielddefs', [ $fielddefs->{$_} ]); #!
|
|
}
|
|
}
|
|
|
|
# Alter fielddefs autoincrement value manually
|
|
($maxkey) = $to->selectrow_array('SELECT MAX(id) FROM fielddefs');
|
|
alter_sequence($to, 'fielddefs', 'id', $maxkey);
|
|
|
|
$to->{RaiseError} = 0;
|
|
$to->{PrintError} = 1;
|
|
for my $table (@copy_tables)
|
|
{
|
|
print "Selecting $table\n";
|
|
my $data = $from->selectall_arrayref(
|
|
'SELECT * FROM '.$from->quote_identifier($table).
|
|
' WHERE '.($field_exclude{$table} ? $field_exclude{$table}.' NOT IN ('.join(',', map { $fielddefs->{$_}->{id} } @skip_fields).')' : '1'),
|
|
{Slice=>{}}
|
|
);
|
|
print "Erasing $table\n";
|
|
$to->do('DELETE FROM '.$to->quote_identifier($table)); #!
|
|
@$data || next;
|
|
my %from_keys = %{$data->[0]};
|
|
my @to_cols = @{ $to->selectcol_arrayref(
|
|
"SELECT column_name FROM information_schema.columns".
|
|
" WHERE table_catalog=current_database() and table_schema=current_schema() and table_name=?",
|
|
undef, $table
|
|
) };
|
|
delete $from_keys{$_} for @to_cols;
|
|
for my $bad_key (keys %from_keys)
|
|
{
|
|
print "Removing column $bad_key\n";
|
|
delete $_->{$bad_key} for @$data;
|
|
}
|
|
$maxkey = 0;
|
|
if (my $ai = $autoincrement->{$table})
|
|
{
|
|
for (@$data)
|
|
{
|
|
$maxkey = $_->{$ai} if $_->{$ai} > $maxkey;
|
|
}
|
|
}
|
|
my @buf;
|
|
my $n = 0;
|
|
my $total = @$data;
|
|
while (@$data)
|
|
{
|
|
@buf = splice @$data, 0, 1024;
|
|
insertall_hashref($to, $table, \@buf); #!
|
|
$n += @buf;
|
|
print "\rInserting $table: $n/$total...";
|
|
}
|
|
print "\n";
|
|
# Initialize auto-increment values
|
|
if (my $ai = $autoincrement->{$table})
|
|
{
|
|
alter_sequence($to, $table, $ai, $maxkey); #!
|
|
}
|
|
}
|
|
|
|
sub alter_sequence
|
|
{
|
|
my ($dbh, $table, $field, $maxkey) = @_;
|
|
$maxkey = int($maxkey)+1;
|
|
if ($dbh->isa('Bugzilla::DB::Mysql'))
|
|
{
|
|
$dbh->do("ALTER TABLE `$table` AUTOINCREMENT=$maxkey");
|
|
}
|
|
elsif ($dbh->isa('Bugzilla::DB::Pg'))
|
|
{
|
|
$dbh->do("ALTER SEQUENCE ${table}_${field}_seq RESTART WITH $maxkey");
|
|
}
|
|
elsif ($dbh->isa('Bugzilla::DB::Oracle'))
|
|
{
|
|
my @sql = $dbh->_bz_real_schema->_get_create_seq_ddl($table, $field, $maxkey);
|
|
$dbh->do($_) foreach @sql;
|
|
}
|
|
}
|
|
|
|
# Insert an array of hashes into a database table
|
|
sub insertall_hashref
|
|
{
|
|
my ($dbh, $table, $rows) = @_;
|
|
return 0 unless
|
|
$dbh && $table &&
|
|
$rows && ref($rows) eq 'ARRAY' && @$rows;
|
|
my @f = keys %{$rows->[0]};
|
|
my $k = join "|", $table, @f, scalar @$rows;
|
|
my $sth;
|
|
if (!($sth = $dbh->{__insertall_hashref_cache}->{$k}))
|
|
{
|
|
my $q = substr($dbh->quote_identifier('a'), 0, 1);
|
|
$sth = $dbh->prepare(
|
|
"INSERT INTO $q$table$q ($q".join("$q,$q", @f)."$q) VALUES ".
|
|
join(',', ('('.(join(',', ('?') x @f)).')') x scalar @$rows)
|
|
);
|
|
if ($dbh->isa('Bugzilla::DB::Pg') || $dbh->isa('Bugzilla::DB::Oracle'))
|
|
{
|
|
for my $i (0 .. $#f)
|
|
{
|
|
if ($dbh->bz_column_info($table, $f[$i])->{TYPE} eq 'LONGBLOB')
|
|
{
|
|
for my $j (0 .. $#$rows)
|
|
{
|
|
$sth->bind_param(1 + $i + $j*@f, '', $dbh->BLOB_TYPE);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
$dbh->{__insertall_hashref_cache}->{$k} = $sth;
|
|
}
|
|
return $sth->execute(map { @$_{@f} } @$rows);
|
|
}
|
|
|
|
# Get node sequence from a dependency graph
|
|
sub getseq
|
|
{
|
|
my ($seq, $seen, $dep) = @_;
|
|
my @r;
|
|
for (@$seq)
|
|
{
|
|
if (!$seen->{$_})
|
|
{
|
|
$seen->{$_} = 1;
|
|
push @r, getseq([ sort keys %{$dep->{$_} || {}} ], $seen, $dep);
|
|
push @r, $_;
|
|
}
|
|
}
|
|
return @r;
|
|
}
|