2 # Builds a dump of the database structure suitable for use by upgrade_mysql.pl
8 my $dist = shift || "schema/bse.sql";
10 my $dbh = DBI->connect("dbi:mysql:$db", $un, $pw)
11 or die "Cannot connect to db: ",DBI->errstr;
13 my $tl = $dbh->prepare("show tables")
14 or die "prepare show tables ",$dbh->errstr;
16 or die "execute show tables ",$tl->errstr;
19 while (my $row = $tl->fetchrow_arrayref) {
20 push(@drop_tables, $row->[0]);
23 my %tables = map { $_ => 1 } @drop_tables;
26 # need this loop to handle references between tables restricting us
28 while ($dropped && keys %tables) {
30 my @tables = keys %tables;
31 for my $drop (@tables) { # not keys %tables, since we modify it
32 if ($dbh->do("drop table $drop")) {
34 delete $tables{$drop};
37 $error = "Could not drop old table: ". $dbh->errstr;
42 print "Could not drop bsebuilder tables:\n ", join("\n ", sort keys %tables), "\n";
46 system "mysql -u$un -p$pw $db <$dist"
47 and die "Error loading database";
49 $tl = $dbh->prepare("show table status")
50 or die "prepare show table status ",$dbh->errstr;
52 or die "execute show table status ",$tl->errstr;
55 while (my $row = $tl->fetchrow_arrayref) {
56 push(@tables, $row->[0]);
57 $engines{$row->[0]} = $row->[1];
61 my @expected = qw(field type null key default extra);
62 my @want = qw(field type null default extra);
63 for my $table (@tables) {
64 print "Table $table\n";
65 print "Engine $engines{$table}\n";
66 my $ti = $dbh->prepare("describe $table")
67 or die "prepare describe $table: ",$dbh->errstr;
69 or die "execute describe $table: ",$dbh->errstr;
70 my @names = @{$ti->{NAME_lc}};
72 @names{@names} = 0..$#names;
73 for my $name (@expected) {
75 or die "Didn't find expected field $name in describe table $table";
77 while (my $row = $ti->fetchrow_arrayref) {
78 for my $name (@want) {
79 defined $row->[$names{$name}] or $row->[$names{$name}] = "NULL";
80 if ($name eq 'type' &&
81 $row->[$names{$name}] =~ /^varchar\((\d+)\) binary$/i) {
82 $row->[$names{$name}] = "varbinary($1)";
85 print "Column ",join(";",@$row[@names{@want}]),
89 my $ii = $dbh->prepare("show index from $table")
90 or die "prepare show index from $table: ",$dbh->errstr;
92 or die "execute show index from $table: ",$dbh->errstr;
95 while (my $row = $ii->fetchrow_hashref("NAME_lc")) {
96 push(@{$indices{$row->{key_name}}},
97 [ $row->{column_name}, $row->{seq_in_index} ]);
98 $unique{$row->{key_name}} = 0 + !$row->{non_unique};
101 #print Dumper(\%indices);
102 for my $index (sort keys %indices) {
103 my @sorted = sort { $a->[1] <=> $b->[1] } @{$indices{$index}};
104 print "Index $index;$unique{$index};[",
105 join(";", map $_->[0], @sorted),