On Sat, Jun 11, 2005 at 11:31:02AM -0500, Peter Fein wrote:
> Peter Fein wrote:
> > As an uninformed, off-the-wall idea, could one compare snapshots of the
> > system tables to generate these diffs? I know next-to-nothing about
> > these, but it seems like they'd contain the info you'd need.
>
> Here's another nutty idea: Could one create a (carefully designed) audit
> table on the system tables themselves? You'd need to exclude irrelevant
> stuff (stats or whatever) & I'd have no idea about performance impact.
>
> Dumping & transforming the audit would basically give you a script that
> runs through all the actions done to a schema. I'm not 100% how to use
> this for an update script though. I'm not sure a straight diff of
> actions will work - it seems like the results may be order dependent in
> some cases and you might need to infer undos. Anyway, it's interesting...
If triggers were supported on system tables, then yes, you could easily
create an audit record. And yes, such a record should make it easy to
generate a patch file.
Personally, I've always worked in such a way that development databases
tend to be re-created frequently, from a set of definition files. When
it's time to create patch code, I'll diff the different versions of the
files and generate a patch file based on that. Of course it'd be really
handy if I could load up two databases (one with the old schema, one
with the new) and have some tool compare the two and generate a diff and
a patch file. Of course it might not catch everything (such as droping a
column and then re-adding it with the same definition), but it would
work fine in 99.99% of the cases.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"