Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Comparing databases - what is the best way?

by Win (Novice)
on Apr 13, 2006 at 08:01 UTC ( #543021=perlquestion: print w/replies, xml ) Need Help??

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

This node falls below the community's threshold of quality. You may see it by logging in.
  • Comment on Comparing databases - what is the best way?

Replies are listed 'Best First'.
Re: Comparing databases - what is the best way?
by DrHyde (Prior) on Apr 13, 2006 at 09:25 UTC
    Provided that the tables aren't too big, I'd think about using $dbh->selectall_arrayref("...") to slurp each pair of tables into memory, use Data::Dumper to turn them into strings, and something diff-ish to find differences.

    If the tables are too big for that, then you'll need to do a bit more work. For each pair of tables, get a list of the values of the primary key(s), and find any differences - that'll catch added and deleted records. Then you'll also need to compare the individual records for each value of the primary key that is common to both tables

Re: Comparing databases - what is the best way?
by salva (Canon) on Apr 13, 2006 at 11:05 UTC
    If you run a select on every table ordered by the primary key you can compare the rows without loading the full tables in memory.

    For instance, supposing the primary key my_key is of some string type:

    my $sql = <<EOSQL; select my_key, foo1, foo2, ... from bar order by my_key EOSQL my $sth1 = $dbh1->prepare($sql); my $sth2 = $dbh2->prepare($sql); unless ($sth1->execute and $sth2->execute) { die ...; } my $row1 = $sth1->fetchrow_arrayref; my $row2 = $sth1->fetchrow_arrayref; while ($row1 and $row2) { if ($row1->[0] lt $row2->[0]) { print "$row1->[0] only in db 1\n" $row1 = $sth1->fetchrow_arrayref; } elsif ($row1->[0] gt $row2->[0]) { print "$row2->[0] only in db 2\n" $row2 = $sth2->fetchrow_arrayref; } else { # a better equality check for rows could be needed # if the db software is different or if the db # schemas are not identical: if ("@$row1" eq "@$row2") { print "row $row1->[0] differ\n" } $row1 = $sth1->fetchrow_arrayref; $row2 = $sth2->fetchrow_arrayref; } } while ($row1) { print "$row1->[0] only in db 1\n" $row1 = $sth1->fetchrow_arrayref; } while ($row2) { print "$row2->[0] only in db 2\n" $row2 = $sth2->fetchrow_arrayref; }
Re: Comparing databases - what is the best way?
by jplindstrom (Monsignor) on Apr 13, 2006 at 13:34 UTC
    If what you want to do is to compare the table structure, SQL::Translator may work for you. Pretty impressive project. It has a sqlt-diff tool which can be used to create alter scripts between schemas.

    I researched this the other day, looking for a tool to help upgrading schemas, and my impression is that the capabilities seems a bit spotty, and the database support is somewhat uneven. If you use MySQL it may work just fine, if you use Oracle (like I do in the current project) or some other database, it may depend on what features you use in the schema. For example, the Oracle diff seemed to lack support for sequences and partitions, just to name a few things.

    (One other tool I looked at briefly is Embarcadero Change Manager, which looked very competent and feature-complete. It operates on db connections though, not SQL DDL statements which I would prefer.)

    /J

    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Comparing databases - what is the best way?
by roboticus (Chancellor) on Apr 13, 2006 at 12:38 UTC
    Win:

    Your question isn't very clear. Are you trying to find out the difference between the structure or content of the two databases?

    It might be helpful to review How (Not) To Ask A Question

    --roboticus

A reply falls below the community's threshold of quality. You may see it by logging in.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://543021]
Approved by holli
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2022-01-21 10:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    In 2022, my preferred method to securely store passwords is:












    Results (57 votes). Check out past polls.

    Notices?