Greetings ye Monks.

I want to put a database into version control. We have a devel and production web server with lots of stuff in a database. Small updates often go directly to the production server and larger updates go first to the devel server and later to the production server. Often the small changes only go to the production server and some testing data remains on the devel server, and the two servers generally diverge. If I could put the two databases into version control, I could merge them in a rational fashion from time to time.

The problem is, how do you diff and merge a database? This is tricky because normal text diff is based on changes in lines of text, which might work on a text dump of a database if you didn't also have to worry about ID collisions. An ID collision as I'm talking about it is where someone adds some data to one datbase and that data gets an automatically assigned ID key. Unless the next available ID is always the same on both databases, different bits of data will have different IDs, which breaks everything.

I think the answer would involve a diff-like utility that examines the differences between two databases and produces SQL that could make the appropriate changes to another database in a way that won't cause ID collisions. This brings me to the perl part of this question.

Does anyone have any recommendations for how to go about comparing two databases like this? I assume the heavy lifting can be done by existing perl modules. Which would people recommend? Any other ideas?

Thanks!
--Pileofrogs

In reply to SQL Diff by pileofrogs

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.