Create a gist now

Instantly share code, notes, and snippets.

Poor man's schema comparison Given two database definitions, it displays the difference in their schemas
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my @dsn;
$dsn[0] = shift or help('no dns1');
$dsn[1] = shift or help('no dns1');
my @dbh;
my @schemas;
for my $i (0 .. 1) {
my ($username, $password, $host, $port, $db) ;
if ($dsn[$i] =~ m[
mysql://
(\S+) # username
:
(\S+) # password
\@
(\S+) # hostname
:
(\d+) # port number
/
(\w+) # database
]x ) {
($username, $password, $host, $port, $db) = ($1, $2, $3 , $4, $5);
}
else {
die "malformed identifier ($dsn[$i])\n";
}
my $dh = DBI->connect(sprintf( 'DBI:mysql:%s;host=%s;port=%s',
$db, $host, $port),
$username, $password, {RaiseError => 1})
or die q{can't connect\n};
print "ok $dsn[$i]\n";
$dbh[$i] = $dh;
$schemas[$i] = "$host:$port/$db";
}
for my $i ( 0 .. 1 ) {
open my $FH, '>', "/tmp/tmp_schema$i.sql"
or die "can't create schema$i.sql\n";
print $FH "#\n# schema: ", $schemas[$i], "\n#\n\n";
my $tables = $dbh[$i]->selectcol_arrayref(qq{SHOW TABLES});
unless (@$tables) {
die "no tables in dbh # $i\n";
}
for my $table (@$tables) {
my $result = $dbh[$i]->selectall_arrayref(
qq{SHOW CREATE TABLE `$table`});
unless ($result) {
die "can't get DDL for $table\n";
}
#use Data::Dumper;
#print Dumper($result,"\n"); exit;
my $create = $result->[0][1];
next unless $create =~ /^CREATE\s+TABLE/;
print $FH "#\n# $table\n#\n\n";
print $FH "$create;\n";
}
close $FH;
}
exec "vimdiff -o /tmp/tmp_schema0.sql /tmp/tmp_schema1.sql";
sub help {
my ($msg) = @_;
if ($msg) {
print "*** $msg\n";
}
print "syntax: get_schema source target\n",
"where source and target are \n",
"mysql://username:password\@hostname:port/database\n";
exit(1);
}
@aweher
aweher commented Jul 16, 2013

At lines 7 and 8:

$dsn[0] = shift or help('no dns1');
$dsn[1] = shift or help('no dns1');

The help message has a typo: "dns1"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment