DD 6.0.2 - ability to reference "foreign tables"?

This is more of my continuing saga to try and get DD to work for a situation which I've be placed in which seems ripe with problems right now.

This is hopefully a simple example that will explain the problem:

column-pair ("direction", "direction")

column-pair("color_id", "color_id")

column-pair ("room_id", "room_id")

column-pair("color_id", "color_id")

The DCT has two source locations for color information - both of which are supposed to be deployed to the COLOR table but I cannot defiine two "groups" named "COLOR" to represent each of the potential sources for this deployment.
Okay - Suppose we decide that the COLOR table is actually being maintained separately (from a paint manufacting source) and we no longer have to deploy the actual "color_id" information from the DCR to the DB - we still have a problem in that we want to make the cross-reference associations between each room and its associated color as well as each side of the exterior of the house and its associated color (we could go real crazy and allow each wall of a room to be painted a different color too) - but we still have the problem of how to define the 2nd primary key for each of the "*_XREF" tables.

As near as I can tell - in order to define a foreign key - I need to specify both the table from which it comes and the source xpath from the DCR which is the actual source of the information. Well - if the table is now being mained separate from the DCR to solve the problem of trying to push content from two different sources into a single DB - we no longer have a handle on the "parent-table" to use for our reference, and even if we did, we still have the problem of definining which column of information was being used to feed that particular table.

If we could stipulate that the table (or "parent-group") was itself "foreign" then each XREF table could define the source information to use for the foreign-primary-key that comes from that foreign-table in order to make the correct association.

Looking at the DTD and the examples - it doesn't seem possible for me to define a foreign key as coming from an external or foreign table. Is it?

If not, I may end up having to scrap the entire use of DD here and spend a bunch of time writing my own code to parse the DCRs and use Perl DBI to push data where we want it. For various reasons I'd like to avoid this switch of approach - but if DD cannot do what we need, I need to know it ASAP so as to avoid any additional loss of time on this project.

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Find more posts tagged with

Comments

iwovGraduate

This is a shot in the dark -

Instead of specifying color_id as a foreign key in EXTERIOR_COLOR_XREF and ROOM_COLOR_XREF tables, just define it to be a regular value from DCR. Would that work? That way you avoid the idiosyncrasy of DD group definitions and foriegn-keys.

That leaves you without any relation between COLOR and ..._XREF table. What if you add the FK constraint directly from the DB after the table is created? DD won't be aware of the FK constraint. If we assume that the COLOR table is maintained separately with appropriate ids, would DD need to be aware of the FK constraint after the table is created ?

Again, not sure if this will work, and unfortunately I don't have the setup to try it right now. - its a shot in the dark.

Adam Stoller

Samir - you may have just shone the light at the end of the table!

If we can push the COLOR table completely outside the equation - and assume that the color_id information in our DCRs are previously-recorded-ids in that external table, then all we need to do is deploy the color information from the exterior container to the EXTERIOR_COLOR_XREF table and from the interior/room container to the ROOM_COLOR_XREF table - and not declare the color_id as foreign since it won't really be (there will be a direct source to the XREF table and a presumed association with a value that already exists in the "foreign table" which DD need know nothing about...

This sounds promising - and might even let me get to the actual DD config files by tomorrow morning so that by Wednesday I might even be able to see about deployments in general (of course I still need to get that value-from-callout working ...)

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

Migrateduser

I didn't get time to really digest your email. But it sounds like the basic dilemma is that you have two colors that need to get into the same table during the deployment of the same DCR. Since these colors are within different structures in the DCR, you have a problem.

(If this isn't what you are trying to do, let me know. If it is, read on)

In general, for DD to insert multiple rows in one DCR deployment, you need to specify the data as a replicant. In your case, you can't really do that since the values are coming from different places... ie you don't have two colors for interior. But one for interior and one for exterior.

What I am thinking (and I haven't really thought it through, I have to admit) is that you can "fake" a replicant by using the exernal tuple prepreprocessor (TPP). The TPP unlike the external data source, allows you to modify the tuple... you can do almost whatever you want with it. So your TPP finds the color id's and then you make your own replicant... the replicant doesn't even have to really be related to your DCR. It could be /a/color/0= blue /a/color/1=red. Then in your value-from-field, say value-from-field=/a/color[0-5] ... something like that. DD will treat it like a replicant. Just add those new tuple key-values to the tuple that the TPP returns back to DD.

Mariam

Adam Stoller

The sample provided was, unfortunately flawed - in that our real case we do have replicants of "colors" in two different containers (say one wall painted white-with-purple-polkadots, another wall painted purple-with-white-polkadots, etc.)

I think Samir saw through the mist that was my mind and found the [potential] solution. I'm working on trying to finish up the dbschema.cfg files today so I can try to get to the ddclient.xml file(s) tomorrow, so I can hopefully do at least a mid-point-to-end-point deployment by wednesday to try and verify this stuff.

Remaining on my list of things for today is getting that darn java thing to compile.

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com