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?
Set permissions and/or add database triggers?
Or maybe I still don't quite get the problem...
Re: Database challenge: upgrading data
Beatnik on 2008-02-21T07:45:17
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 overwrite the user-data but we would like to overwrite the application data. Adding a checksum seemed like a good idea because that way we can be sure something has changed and we can ask the user for confirmation. Also consider that the user has current data already in the database.. using the normal schemas. Adding extra triggers is not really going to help in upgrading now (it might for the next upgrade ofcourse, ignoring the fact that those triggers are often RDBMS specific or even version specific and that we run this app on a multi-RDBMS platform).
Does that make any sense?Re: Database challenge: upgrading data
runrig on 2008-02-21T18:05:24
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.
