Hello all.

I want to mirror a database table from location A to location B. I only require location B to hold a subset of the information in A - that I'd define simply by a query. The mirror is one way, as location B is read-only. I don't need the mirror to be "live", so I plan to run a DBI-based perl script every hour or two that updates location B. So far, so simple. :-)

What I'm looking for is an efficient and easy way to do this. My first thought was to copy the whole table from A to a temporary table in B, and do a rename to make this the current table. However, this seems a tad inefficient (the table has many rows which don't change often, and I'd be doing many INSERTs), and this does leave table B non-existant for a brief period, which isn't ideal with people constantly querying it.

Alternatively, I could query both databases, and do a bunch of comparisons to see which rows would need adding, which deleting, which updating - and then perform the relevant INSERT/DELETE/UPDATE queries. This approach sounds more promising, but so generic that I wondered if there were any existing modules to perform mirroring in this fashion - or, indeed, if there's a better approach. I've tried CPAN searching for "dbi mirror", but haven't really found anything that looks applicable. Maybe I'm being thick, however!

Does anyone have any pointers for me?

Many thanks,
Neil.

P.S. My source database is quite old and doesn't support any flashy replication functionality.

In reply to Searching for Module to Mirror Database Table by SmugX

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.