S
Sara
Hi All,
Just want to know if there is any straightforward solution in Perl to
compare two MySQL database structures.
I was able to get all the table names and the column names in the
database but want to know if this is the only way of doing it. I hope
there is a simpler way to directly convert the database structure
(without the data in it) to nested data structure, without looping
through each table.
I actually wrote a small program to change the mysqldump output to a
HoH so that it can be compared with another HoH using is_deeply method
in Test::More module.
# code segment
# subroutine for creating datastructure out of mysqldump output
sub get_struc($) {
my $file = shift; # the mysqldump file
my $string;
open my $fh, $file or die "Can't open file '$file': $!\n";
{ local $/; $string = <$fh>; close $fh }
my $re; # magic regex from perlre for matching braces
$re = qr{ \( (?
?> [^\(\)]+ ) | (??{ $re }))* \) }x;
my @matches = $string =~ /(CREATE TABLE `[^`]+` $re)/g;
my %tables;
foreach (@matches) {
my ($tbl,$cols);
$tbl = $1, $cols = $2 if /CREATE TABLE `([^`]+)`
\(\n(.+)\n\)/s;
if ($cols =~ s/\s*PRIMARY KEY\s*\((.*?)\)\s*//) {
$tables{$tbl}{'primary_keys'} = $1;
}
foreach my $c (split /\n/, $cols) {
my ($col,$type);
$c =~ s/^\s+|\,\s*$//g;
$col = $1, $type = $2 if $c =~ /`([^`]+)` (.+)/;
$tables{$tbl}{$col} = $type;
}
}
return \%tables;
}
If there is any way to get the output of mysqldump from within Perl
even that would be fine.
I searched through documentation of DBI, DBD::mysql and DBI::FAQ but
wasn't able to find anything specific to this problem.
Any pointers would be of great use.
Thanks,
Sara
Just want to know if there is any straightforward solution in Perl to
compare two MySQL database structures.
I was able to get all the table names and the column names in the
database but want to know if this is the only way of doing it. I hope
there is a simpler way to directly convert the database structure
(without the data in it) to nested data structure, without looping
through each table.
I actually wrote a small program to change the mysqldump output to a
HoH so that it can be compared with another HoH using is_deeply method
in Test::More module.
# code segment
# subroutine for creating datastructure out of mysqldump output
sub get_struc($) {
my $file = shift; # the mysqldump file
my $string;
open my $fh, $file or die "Can't open file '$file': $!\n";
{ local $/; $string = <$fh>; close $fh }
my $re; # magic regex from perlre for matching braces
$re = qr{ \( (?
?> [^\(\)]+ ) | (??{ $re }))* \) }x;my @matches = $string =~ /(CREATE TABLE `[^`]+` $re)/g;
my %tables;
foreach (@matches) {
my ($tbl,$cols);
$tbl = $1, $cols = $2 if /CREATE TABLE `([^`]+)`
\(\n(.+)\n\)/s;
if ($cols =~ s/\s*PRIMARY KEY\s*\((.*?)\)\s*//) {
$tables{$tbl}{'primary_keys'} = $1;
}
foreach my $c (split /\n/, $cols) {
my ($col,$type);
$c =~ s/^\s+|\,\s*$//g;
$col = $1, $type = $2 if $c =~ /`([^`]+)` (.+)/;
$tables{$tbl}{$col} = $type;
}
}
return \%tables;
}
If there is any way to get the output of mysqldump from within Perl
even that would be fine.
I searched through documentation of DBI, DBD::mysql and DBI::FAQ but
wasn't able to find anything specific to this problem.
Any pointers would be of great use.
Thanks,
Sara