A "Behind-the-scenes" Look at Developing
Change Enhancements for DPS (Data Propagation System)
The following documents are representative of the design methodology generally employed for DPS: The write-up and review of a "conceptual design document", followed by write-up and review of a "technical design document" (based on the principals outlined in the conceptual design document).
Conceptual Design Document for DPS Support
of RDBMS Foreign Key Constraints
Here are two premises and a conclusion:
PREMISE 1:
For two tables, Parent_table and Child_table, related by a foreign key constraint, there are four possible transaction scenarios that will be rejected due to a violation of the foreign key constraint:
(a) when an INSERT is attempted to a Child_table *before* a corresponding INSERT has been made to the Parent_table(b) when a DELETE is attempted to a Parent_table row *before* a corresponding DELETE to all related Child_table rows has been completed (unless Cascade-Delete is in effect)(c) when an UPDATE is attempted to a Parent_table that will change the value of that table's Referenced Unique Index *before* a corresponding DELETE to all related Child_table rows has been completed
(d) when an UPDATE is attempted to a Child_table that will change the value of that table's Foreign Key Index to a value that does not correspond to an existing value on the Referenced Unique Index of the Parent_table
PREMISE 2:
These are the *only* transaction scenarios in which a transaction would be rejected due to violation of the foreign key constraint between these two tables (i.e., Premise 1 is a comprehensive list of all possible transaction scenarios that could ever produce a rejection due to foreign key constraint violation.)CONCLUSION:
If a transaction-producing system (such as DPS) is made to *never* generate any of the transaction scenarios listed in Premise 1, its transactions will *never* be rejected due to the foreign key constraint.Okay, if the premises and conclusion above are true, then how can we "make good" on the conclusion? Answer: by constructing a system that guarantees that the bogus transaction scenarios (those listed in Premise 1) cannot occur.Working within the constraint that DPS operates sequentially and can only produce a set of transactions based upon a single ADABAS transaction, how can DPS be made to *never* generate any of the transaction scenarios listed in Premise 1?If for a given single ADABAS transaction:
(a) DPS always writes out INSERTs to a Parent_table *before* it writes out INSERTs to a Child_table; and(b) DPS always writes out DELETEs to a Child_table *before* it writes out DELETEs to a Parent_table; and(c) DPS never outputs an UPDATE that would alter the Referenced Unique Index value on a Parent_table row or the Foreign Key Index value on a Child_table row (instead of an UPDATE, it should issue a DELETE and INSERT); and
(d) DPS always writes out all DELETEs before all INSERTS (to avoid resultant violation of any Primary Key constraint),
then DPS will *never* generate foreign-key-violating transactions out of a single ADABAS transaction, and it will avoid generating any resultant primary-key-violating transactions.Now that we've already done the logical "heavy lifting", the path to completion is much simpler. With the knowledge that tRelational produces FTTEs in such an order that a parent table will *always* precede its child table(s), then, within the context of a single ADABAS transaction:
(a) if DPS produces *all* INSERT statements in FTTE order; and(b) if DPS produces *all* DELETE statements in reverse-FTTE order; and(c) if DPS never produces an UPDATE that would alter a parent table's Referenced Unique Index value or a child table's Foreign Key Index value ; and
(d) if DPS produces *all* DELETE statements *before* all INSERT statements (to avoid resultant violation of any Primary Key constraint),
then it assures that no INSERT, DELETE, or UPDATE will be rejected due to violation of a foreign key constraint, nor will it be rejected due to a resultant violation of a primary key constraint.That's it. If any of the premises above (or any stated or unstated underlying assumptions) can be shown to be false, then this whole logical construct falls apart, and we're back to the drawing board!If all these premises are true, then all we need to do is to:
(1) produce DELETE statements in reverse-FTTE order; and(2) create a new DDPARML construct for each table, "FKEY" through "FKEY-END", in which ALL columns in a table that participate in ANY foreign key relationship will be listed (and then adhere to the logic outlined in the footnote below); and
(3) produce all DELETE statements before all INSERT statements.
This will complete DPS's basic compliance with foreign key constraints.
To accomplish this, DPS must be made "aware" of which column(s) in any table participate in a Foreign Key relationship. Then, if the value of any of these columns is changed as a result of an ADABAS Update transaction, DPS will issue a DELETE followed by an INSERT (i.e., it will issue exactly the same set of transactions that is issued when a designated PKEY column has its value changed).
Technical Design Document for DPS Support
of RDBMS Foreign Key Constraints
PROBLEM:
DPS does not support parent/child relationships because it was never designed to do so. By requiring the customer to enable "Cascade-Delete" the RDMBS is kept intact when a column is updated that is involved in a parent/child relationship. Keep in mind that not all RDBMS’s support "Cascade-Delete". In order to remove that requirement and fully support parent/child relationships, code must be added to DPS, and tRelational must provide an indication when a field is part of a parent/child relationship.
PROPOSED SOLUTION:
As stated in the "DPS Foreign Key Compliance" document by Dan V. dated 1/14/00, tRelational could be made to provide an FKEY-START and an FKEY-END describing those fields participating in a parent/child relationship.
As the FTTE structure is now in relative order (from the fix for the SQL-out-of-sequence problem) there would be a backward chain pointer in each TBLCB, so the chain could be followed backwards. This would allow the child tables to be first to have DELETES done if there is an UPDATE to a column that participates in a relationship with a parent table.
When all child tables have been processed for DELETES, the chain of TBLCB’s can be processed forward and the INSERTS and UPDATES can safely be done.
This requires that STEP 8 in LTRPUPDT be made into a two pass process.
SPECIFICATIONS:
- A separate module similar to LTRPKEY (LTRFKEY) will have to be added to handle the FKEY definitions in DDPARML. This can be fashioned after LTRPKEY. DPSTRANS would be changed to load and call LTRFKEY. Dsects need to be added for FKEY and FCOL again mirroring PKEY and PCOL.
- Change SETFPMD csect in LTRPUPDT to set FTTESIGN to FTTEFKEY if appropriate.
- Change LTRPUPAA csect to add CSQLFKEY to CSQL to indicate column is part of foreign key.
- Add CSQLFKEY to CSQL dsect.
- In LTRPUPDT after label TBLOK01, where TBLCB’s are added in relative order, insert code to include a backward chain pointer in each TBLCB and save two pointers (LASTATBL, LASTBTBL) in UPDTWORK to the last TBLCB added in the AI and BI chain. This allows quickly finding the start of the reverse pass.
- Add a flag to UPDTWORK to indicate the DELETE pass is being done.
- In the DELETE pass, in places in LTRPUPDT where checks are made for changes to PKEY, also check for changes to FKEY and take the same action as though it were a PKEY. Add an indicator in the ROWCB defining the action taken, so that row will not be DELETE’d twice if there are both PKEY and FKEY changes.
- Branch around all calls for INSERT and UPDATE while processing the DELETE pass.
- When done with both AI and BI structures, turn off the DELETE pass flag, get the pointer to AI and BI structures and start the forward processing. This time branch around calls to DELETE and LTRCOLMB, so we process only UPDATE’s and INSERT’s. Allow calls to user-exits.
- Handle all counting appropriately.
