Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

Re: DBI -- Synchronize table diffs?

by VSarkiss (Monsignor)
on Nov 05, 2002 at 16:53 UTC ( [id://210509]=note: print w/replies, xml ) Need Help??

in reply to DBI -- Synchronize table diffs?

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.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://210509]
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (3)
As of 2024-07-12 21:13 GMT
Find Nodes?
    Voting Booth?

    No recent polls found

    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.