If you don't mind changing your database schema and access logic, the easiest way is to extend your database schema:

create table sometext (
  id INTEGER PRIMARY KEY,
  content VARCHAR(4096),
  previous_version INTEGER
)

[download]

Then, selecting the current version of something becomes easy (modulo indices on the previous_version column), updating a version becomes easy and building the diff between two versions is a matter of Algorithm::Diff.

Of course, with MySQL, which has problems with accessing reverse indices, this might not be a good solution, and if you're driving a system like Perlmonks from the database, (read) access to the current version is far more often than access to previous versions. Then you might want to consider either partitioning your table so that the current versions all live in the same partition or splitting your table into two tables, sometext and old_sometext, where the current version always lives in sometext. With that approach you lose the ability to have multiple branches though, as you will only have one active version.

If you want version/patch integrity across tables, you will first need atomic commits across tables and you will also need a global version number/commit number that will allow you to cross-reference a selection of "patches" against the database. Svk does that (for text files), and I think it would be good to steal the ideas from it.

If you want magic rollback/versioning without changing your software, I believe there is nothing like that except cranking up your DBI logging, copying the database during the night, and then replaying the logs as you need to move forward. Moving backwards is most likely not possible unless you write an SQL parser that knows how to undo stuff or write your DB emulator, which saves the steps with undo information and thus allows you to "move backwards" in your DBI log.

If you only want the differences between two versions of the database, your best bet is to dump both versions as text and then to run diff over the text files :-).

In reply to Re: Revision control in relational databases by Corion
in thread Revision control in relational databases by Your Mother

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.