Re: Building the Meta-data Diff Engine

"Laconic2" <laconic2_at_comcast.net> wrote in message news:L9OdnSkAE828ROrcRVn-hg_at_comcast.com...
> What follows in more skunk works. You have been warned!
>
> On two of the contracts I worked, one of the things I was called upon to
> work with was a pair of databases that had diverged, because different DDL
> had been applied to the two.
>
>
> I approached the first one by moderating a series of meetings between the
> two people who controlled the DDL, each on one database. I detected the
> differences pretty much by hand.
>
> By the time I hit the second case, I was ready with a more automatic
> approach. I never would have been able to get the DBDs in the same room
> anyway. They were on opposite sides of the pond. And they spoke two
> different languages, which each of them insisted was called "English".
>
> So I built a meta-database.
>
> The meta-database contains user tables that are the reflection of the
system
> tables in each of the databases. I only selected a subset of the data in
> the system tables. There were some tables and columns I didn't need. I
had
> to add one column to each table in the meta-database, called
DATABASE_NAME.
> This is completely unnecessary metadata in the original databases, due to
> what I'll call the "Tigger principle": ("The most wonderful thing is, I'm
> the only one!") But, without it, we've got the cat food problem all over
> again.
>
> Then I loaded the meta-database from the respective databases with an
> INSERT...SELECT construct. The insert was into the user tables of my
> meta-database, while the select was from the system tables of one of the
> databases. The SELECT also has one extra item, a literal name of the
> databases, like this:
>
> , 'EAST_DB' as DATABASE NAME
>
> This means I needed two separate loader scripts, one for each database,
> that differed by two trivial details, the connect statement, and the
> literal database name. Big deal.
>
> This INSERT...SELECT construct is what make me call this approach "skunk
> works". It violates the "thou shalt not conflate meta-data with data"
> dictum all over the place. I use skunk works whenever I need to.
<snip>

But the good news is that it does not violate my rule that says what we all know that "Metadata are data". Attempting to think and do otherwise gets us into difficulty when we want to have end-user-extensible data structures, for example (although I realize that those are also considered bad form by many DBAs).
smiles. --dawn Received on Sat Oct 23 2004 - 00:56:52 CEST