twerq has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I'm looking for a CPAN module or anything inside the DBI scope that I can employ to synchronize database layouts. Something that can basically make diffs between two databases, and generate CREATE and ALTER statements for MySQL.

I've got a CGI::Application that I'm using to share a common codebase for many instances of a site. Each 'instance' of the CGI::Application has it's own copy of the database to work with...

...using CGI::Application allows me to make all my changes and bug-fixes in one place, which is great. But what happens when I want to change the length of a column in the DB? What about adding a column? It seems to me I'm going to have to go around and make this same change to every instance of the database, which seems rather tedious and unnecessary.

If the CGI::Application could compare it's DB layout to the Main DB and make small updates, changes, etc, things would be much smoother.

Does anything do this?
Is anybody else doing this? Any suggestions?


Replies are listed 'Best First'.
Re: DBI -- Synchronize table diffs?
by gmax (Abbot) on Nov 05, 2002 at 17:00 UTC
    Keeping in mind the risks that VSarkiss has already explained, there are two possibilities.
    Short (cheating) answer, without using Perl :-)
    $ mysqldump --add-drop-table -h host1 db1 | mysql -h host2 db2
    If you want to add one column without erasing the data, you can modify your master database and then do like this:
    # copies the target data without the structure. Creates a # complete SQL statement including all the field names. $ mysqldump -t -c -h host1 db2 > db2.sql # copies the structure from db1 to db2, without data $ mysqldump -d -h host1 db1 | mysql -h host2 db2 # reinserts the data $ mysql -h host2 db2 < db2.sql
    Notice that this code does not work if you want to remove a column. If you just add or modify a column, it should work fine.

    The (long) Perl answer is more flexible, exploiting some DBI features to check the differences between two databases and eventually fix them.
    This node shows how to do a basic comparison between two databases.
    For something more structure oriented, the following script can achieve most of what you want. It can determine if one table is in one database and missing from the other one, and warn you if there are differences.
    Since you want to have a copy of the same database in many locations, I assume that you you want all the "satellites" to be like the central one. If this is the case, it should be easy to modify the check_diff sub to drop the offending table and re-create it.
    To create an "ALTER TABLE" statement, you should loop inside the table structure. An example of how to do it is in the code of DBSChema::Normalizer.
    If your goal is to have the same table structure in both databases, you can just run this script twice:
    $ ./dbdiff host1 db1 host2 db2 $ ./dbdiff host2 db2 host1 db1
    This way, tables that did not exist in host 1 will be created in host 2 and vice versa.
    Notice that the script does not modify your databases, but only creates the necessary statements that you should execute.
    It is not a complete solution, but it should be a good start.
    #!/usr/bin/perl -w use strict; use DBI; sub help { print STDERR "syntax dbidiff host1 db1 host2 db2\n"; print STDERR "reverse the order of hosts and DBs for", "a complete synch\n"; exit; } my $host1 = shift || help(); my $db1 = shift || help(); my $host2 = shift || help(); my $db2 = shift || help(); #change connection parameters to suit your needs. #username and password are taken from the configuration #file in the user's home dir my $dbh1 = DBI->connect("DBI:mysql:$db1;host=$host1;" ."mysql_read_default_file=$ENV{HOME}/.my.cnf", undef,undef,{RaiseError => 1}); my $dbh2 = DBI->connect("DBI:mysql:$db2;host=$host2;" ."mysql_read_default_file=$ENV{HOME}/.my.cnf", undef,undef,{RaiseError => 1}); # reads the table list from both databases my %tables1 = map {$_,1} $dbh1->tables(); my %tables2 = map {$_, 1} $dbh2->tables(); sub get_table_struct { my $host_num = shift; my $dbname = shift; my $tablename = shift; my $dbh = $host_num == 1 ? $dbh1 : $dbh2; my $sth = $dbh->prepare(qq{SHOW CREATE TABLE $dbname.$tablenam +e}); $sth->execute(); my ($dummy, $table_creation) = $sth->fetchrow_array(); $sth->finish(); return $table_creation; } sub copy_table { my $tablename = shift; print "# New table to create on $host2 - $db2\n"; print "#\n# CREATING TABLE $tablename\n#\n\n"; print get_table_struct(1, $db1,$tablename), "\n"; } sub check_diff { my $tablename = shift; my $tc1 = get_table_struct(1, $db1, $tablename); my $tc2 = get_table_struct(2, $db2, $tablename); if ($tc1 ne $tc2) { print "# `$tablename` is different\n"; #uncomment the next two lines to fix the differences "brute f +orce" #print "DROP TABLE $tablename;\n"; #print "$tc1\n" } } # # The main loop. Here the two databases get compared. # for (keys %tables1) { if(exists $tables2{$_}) { check_diff($_); } else { copy_table ($_); } } $dbh1->disconnect(); $dbh2->disconnect();
    update Jan 22, 2003 I found an utility on CPAN, MySQL::Diff , which does exactly what is requested.
    _ _ _ _ (_|| | |(_|>< _|
Re: DBI -- Synchronize table diffs?
by VSarkiss (Monsignor) on Nov 05, 2002 at 16:53 UTC

    I rolled my own for this a while ago because I couldn't find anything. I did it on client time so I can't post it, but I've considered re-writing it from scratch on my own and uploading to CPAN. But that's another story.

    I did get some invaluable help from the Algorithm::Diff module. There are facilities in newer DBI versions for retrieving schemas and types but they didn't seem to do what I wanted. (I wrote mine against Sybase, and I know how to mine its catalogs.)

    As to actual design tips: it's trickier than it looks at first. Begin by deciding what you mean by "different". Certainly new tables or new columns qualify, but what about column order? Do you want to consider indexes and index characteristics? What about other object types, like views or trigger? Finally, what do you want the code to do once it finds a difference: just report it, or actually generate some SQL to make the change?

    The general approach I used is like this:

    • Generate a list of tables in each of the two databases, and find the differences (with Algorithm::Diff). Report (or drop/create) tables that are in only one database.
    • For tables in common, retrieve lists of columns of each one (including types and nullity if you want), and diff those. If you don't care about column order, you can sort the column names. Report any differences, or generate alter table statements if you want (I always reviewed those manually....)
    • From the common table list, retrieve indexes and/or triggers, etc., and diff those. Remember that even if you don't care about column order in tables, you do care about the order in indexes.
    Similar considerations for views, procedures, triggers, etc.

    Anyways, I hope this helps a little.