Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Beatnik (493)

  (email not shown publicly)

A 29 year old belgian who likes Mountain Dew, Girl Scout Cookies, Tim Hortons French Vanilla Flavoured Cappucinno, Belgian beer, Belgian chocolate, Belgian women, Magners Cider, chocolate chipped cookies and Perl. Likes snowboarding, snorkling, sailing and silence. Bach can really cheer him up! He still misses his dog.

Project Daddy of Spine [], a mod_perl based CMS.

In his superhero time (8.30 AM to 5.30 PM), he works on world peace.

Journal of Beatnik (493)

Wednesday February 20, 2008
05:35 PM

Database challenge: upgrading data

[ #35709 ]
Here's a tricky database challenge: Suppose you have a database filled with data. It's pretty much data in it's purest form. Some tables have unique key fields, some don't (but combined unique keys can be made). You provided the original schema and data dump, no changes are made to the schema, just to the data. Suppose you want to update some of the data in the database, leaving other data untouched (for instance, data that the user has modified), what's a good approach? Criteria: Low dependencies (obviously, you don't want to parse SQL.. although pulling the original and the new data from the database is possible). Optionally, what if you didn't have one older version of data but multiple? Suppose you want to allow certain updates to be made conditionally (allow a user to change a record if really really really needed).

Approach I took so far: generate checksum of each record and, together with unique identifier per record per table, match the old with the new data. Use a marker to distinguish critical (upgradeable) to non-critical (non-upgradeable / user-modified data). Provide user interaction to upgrade / generate customized data dumps.

Any thoughts?
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • Set permissions and/or add database triggers?

    Or maybe I still don't quite get the problem...

    • Let me elaborate... Suppose you develop some application that uses a database as a backend. You store pretty much everything in your database, including user-modifiable data (the stuff you expect your user to change because that's the purpose of the application) but also the data that's important to the application itself. Let's assume you have some help files stored in the database. There is some chance that the user translated them, did some extra HTML cleaning, etc. For the upgrade, we shouldn't really o
      • Oh, I get it now. I've seen one app deal with it by keeping the user-defined data (help messages and prompts and things like that) separate from the default system data. If the user updated anything, it was copied and then flagged as user defined. The app preferred to fetch the user defined data first, then the non-user defined data. That way you could update the default app data without overwriting the user defined data.