2 # Builds a dump of the database structure suitable for use by upgrade_mysql.pl
8 my $dist = "/home/tony/dev/bse/base/bse/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 for my $drop (@drop_tables) {
24 $dbh->do("drop table $drop")
25 or die "Could not drop old table: ", $dbh->errstr;
28 system "mysql -u$un -p$pw $db <$dist"
29 and die "Error loading database";
31 $tl = $dbh->prepare("show tables")
32 or die "prepare show tables ",$dbh->errstr;
34 or die "execute show tables ",$tl->errstr;
36 while (my $row = $tl->fetchrow_arrayref) {
37 push(@tables, $row->[0]);
41 my @expected = qw(field type null key default extra);
42 my @want = qw(field type null default extra);
43 for my $table (@tables) {
44 print "Table $table\n";
45 my $ti = $dbh->prepare("describe $table")
46 or die "prepare describe $table: ",$dbh->errstr;
48 or die "execute describe $table: ",$dbh->errstr;
49 my @names = @{$ti->{NAME_lc}};
51 @names{@names} = 0..$#names;
52 for my $name (@expected) {
54 or die "Didn't find expected field $name in describe table $table";
56 while (my $row = $ti->fetchrow_arrayref) {
57 for my $name (@want) {
58 defined $row->[$names{$name}] or $row->[$names{$name}] = "NULL";
60 print "Column ",join(";",@$row[@names{@want}]),
64 my $ii = $dbh->prepare("show index from $table")
65 or die "prepare show index from $table: ",$dbh->errstr;
67 or die "execute show index from $table: ",$dbh->errstr;
70 while (my $row = $ii->fetchrow_hashref("NAME_lc")) {
71 push(@{$indices{$row->{key_name}}},
72 [ $row->{column_name}, $row->{seq_in_index} ]);
73 $unique{$row->{key_name}} = 0 + !$row->{non_unique};
76 #print Dumper(\%indices);
77 for my $index (sort keys %indices) {
78 my @sorted = sort { $a->[1] <=> $b->[1] } @{$indices{$index}};
79 print "Index $index;$unique{$index};[",
80 join(";", map $_->[0], @sorted),