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.
Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
Read Where should I post X? if you're not absolutely sure you're posting in the right place.
Please read these before you post! —
Posts may use any of the Perl Monks Approved HTML tags:
- a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
| |
For: |
|
Use: |
| & | | & |
| < | | < |
| > | | > |
| [ | | [ |
| ] | | ] |
Link using PerlMonks shortcuts! What shortcuts can I use for linking?
See Writeup Formatting Tips and other pages linked from there for more info.