/********************************************************************** * * PURPOSE: * ============ * Suggested queries to validate a Temporal RI on a child table. * * BACKGROUND: * ============ * Multiple variants of RI can be defined with temporal tables. * * When a parent table is temporal, there can be multiple rows in the * parent that contain a given parent key value with non-overlapping * temporal column value. Presence of a temporal column causes rows * to be tracked and hence when a non-parent key column is modified * in the parent, system tracks history. This occurs in TransactionTime * dimension. The same occurs for ValidTime dimension when modifications * are made using CURRENT or SEQUENCED VALIDTIME qualifiers. * * Note this example: * * create multiset table tpar ( * c1 int, * c2 int, * pk int not null sequenced validtime unique * vt period(timestamp with time zone) as validtime not null, * tt period(timestamp with time zone) not null as transactiontime * ) * primary index(c1) * * Assume row in parent is * (1, 1, 1, (t1-until_changed),(t1-until_closed)) * on t3, assume c2 got incremented by 1. * * Rows in the parent table then are : * (1, 1, 1, (t1-until_changed),(t1-t3)) * (1, 1, 1, (t1-t3),(t3-until_closed)) * (1, 2, 1, (t3-until_changed),(t3-until_closed)) * * As noted, the pk value 1 is valid from t1-until_changed but this value * is split in the two open rows above. Hence when validating whether a * given child row's time value is present in the parent's VT or TT value, * the rows must be normalized before comparing. * * The queries below use the TD_NORMALIZE_MEET table function. * * DESCRIPTION: * ============ * * For each of the variant of RI, a separate query (or set of queries) * is provided to validate the RI constraint. If query returns rows, then * it implies that that the child table does not satisfy the constraints. * Please follw the instructions below to clean up the child table * FK refers to the columns (excluding the temporal columns or the * TRC column if the child table does not support ValidTime) on which the * RI constraint is defined. * * 1) If FK is not present in the parent, then either update * the child to point to correct FK column value or delete * the row in the child * 2) If FK is present in the parent, but the child's * date/time column (date/timestamp or VT or TT column) * does not satisfy the corresponding temporal RI * property in the parent's VT or TT, then fix the * portions of the child column to satisfy the RI * constraint or delete the child row * 3) If parent and child table's VT column's data types are * different(ex Period(date), period(timestamp) etc.), * Then use the cast function in the queries * approprately. * For Ex: In the below queries the contains condition * will be wriiten by using cast function as follows. * (cast(tblf.vtp as period(date)) contains t2chld.vt) * * * * * * GLOSSARY * ========= * NT - Nontemporal * VT - Validtime * TT - Transactiontime * BiT - BiTemporal * TRC - Temporal Relationship Constraints * CRI - Current Referential Integrity * SRI - Sequenced Referential Integrity * NRI - Nonsequenced Referential Integrity * * **********************************************************************/ /**********************************************************************/ /************ NT child -- NT parent ***********************************/ /**********************************************************************/ /* RI definition. foreign key(fk) references with no check option tpar(pk) */ SELECT fk FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL; /**********************************************************************/ /************ NT child -- VT parent (TRC)******************************/ /* The parent's VT must contain the child dt to unitl_changed *********/ /* when pk-fk match */ /**********************************************************************/ /* RI definition. foreign key(fk,dt) references with no check option tpar(pk,vt) */ drop table nm_par; CREATE MULTISET TABLE nm_par ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( pk INTEGER, vtp PERIOD(DATE) as VALIDTIME) PRIMARY INDEX ( pk ); INSERT INTO nm_par (pk,vtp) WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar) SELECT * FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d ) AS TBLF(pk,vtp); SELECT * FROM ( CURRENT VALIDTIME SELECT DISTINCT fk,dt FROM nm_par LEFT OUTER JOIN tchld ON fk=pk WHERE NOT( vtp CONTAINS period(dt, UNTIL_CHANGED)) AND vtp IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME SELECT fk,tchld.dt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ NT child -- TT parent ***********************************/ /******* child row must exist in the open rows of parent **************/ /**********************************************************************/ /* RI definition. foreign key(fk) references with no check option tpar(pk) */ CURRENT TRANSACTIONTIME SELECT fk FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL ; /**********************************************************************/ /************ NT child -- BiT parent (TRC) ****************************/ /* The parent's VT must contain the child dt to unitl_changed */ /* when pk-fk match */ /**********************************************************************/ /* RI definition. foreign key(fk,dt) references with no check option tpar(pk,vt) */ drop table nm_par; CREATE MULTISET TABLE nm_par ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( pk INTEGER, vtp PERIOD(DATE) as VALIDTIME) PRIMARY INDEX ( pk ); INSERT INTO nm_par (pk,vtp) WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar) SELECT * FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d ) AS TBLF(pk,vtp); SELECT * FROM ( CURRENT VALIDTIME SELECT DISTINCT fk,dt FROM nm_par LEFT OUTER JOIN tchld ON fk=pk WHERE NOT( vtp CONTAINS period(dt, UNTIL_CHANGED)) AND vtp IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,tchld.dt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ VT child -- NT parent ***********************************/ /************ NRI in VT dimension *************************************/ /**********************************************************************/ /* RI definition. nonsequenced validtime foreign key(fk) references with no check option tpar(pk) */ NONSEQUENCED VALIDTIME SELECT fk,vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL ; /**********************************************************************/ /************ VT child -- VT parent ***********************************/ /**********************************************************************/ /* CRI in VT dimension */ /* RI definition. current validtime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar WHERE END( tpar.vt ) >= temporal_date ) SELECT x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) FROM ( NONSEQUENCED VALIDTIME SELECT fk ( title 'Foreign Key Column'),vt ( title ' ValidTime Column' ) FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS period( CASE WHEN begin (tchld.vt) > temporal_date THEN begin (tchld.vt) ELSE temporal_date END , END (tchld.vt))) AND END( tchld.vt ) >= temporal_date AND END( tblf.vtp ) >= temporal_date AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar WHERE END ( tpar.vt ) >= temporal_date ) AND fk IS NOT NULL AND END( tchld.vt ) >= temporal_date)DT)dt(x,y); /* SRI in VT dimension */ /* RI definition. sequenced validtime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar ) SELECT x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) FROM ( NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS tchld.vt) AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ VT child -- BiT parent **********************************/ /**********************************************************************/ /* CRI in VT dimension */ /* RI definition. current validtime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar WHERE END( tpar.vt ) >= temporal_date ) SELECT x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) FROM ( NONSEQUENCED VALIDTIME SELECT fk, tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS period( CASE WHEN begin (tchld.vt) > temporal_date THEN begin (tchld.vt) ELSE temporal_date END , END (tchld.vt))) AND END( tchld.vt ) >= temporal_date AND END( tblf.vtp ) >= temporal_date AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar WHERE END ( tpar.vt ) >= temporal_date ) AND fk IS NOT NULL AND END( tchld.vt ) >= temporal_date)DT)dt(x,y); /* SRI in VT dimension */ /* RI definition. sequenced validtime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar ) SELECT x ( title 'Foreign Key Column'),y ( title ' ValidTime Column' ) FROM ( NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS tchld.vt) AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ TT child -- NT parent ***********************************/ /************ NRI in TT dimension *************************************/ /**********************************************************************/ /* RI definition. nonsequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ NONSEQUENCED TRANSACTIONTIME SELECT fk ( title 'Foreign Key Column'),tt ( title 'TransactionTime Column' ) FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL; /**********************************************************************/ /************ TT child -- VT parent (TRC) *****************************/ /**** NONSEQUENCED TRANSACTIONTIME RI with TRC on parent't VT *********/ /* The parent's VT must contain the child dt to until_changed */ /* when pk-fk match */ /**********************************************************************/ /* RI definition. nonsequenced transactiontime foreign key(fk,dt) references with no check option tpar(pk,vt) */ drop table nm_par; CREATE MULTISET TABLE nm_par ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( pk INTEGER, vtp PERIOD(DATE) as VALIDTIME) PRIMARY INDEX ( pk ); INSERT INTO nm_par (pk,vtp) WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar) SELECT * FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d ) AS TBLF(pk,vtp); SELECT * FROM ( CURRENT VALIDTIME SELECT DISTINCT fk,dt FROM nm_par LEFT OUTER JOIN tchld ON fk=pk WHERE NOT( vtp CONTAINS period(dt, UNTIL_CHANGED)) AND vtp IS NOT NULL UNION SELECT * FROM (NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.dt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ TT child -- TT parent ***********************************/ /**********************************************************************/ /* CRI in TT dimension */ /* RI definition. current transactiontime foreign key(fk) references with no check option tpar(pk) */ /* CURRENT rows in child should have equivalent PK in parent's open rows for each matching fk-pk rows. */ CURRENT TRANSACTIONTIME SELECT fk ( title 'Foreign Key Column'),tt ( title 'TransactionTime Column' ) FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL; /* SRI in TT dimension */ /* RI definition. sequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ /* child row's TT must be contained in the normalized TT of the parent for each of the matching fk-pk rows. */ WITH SUBTBL(x,d ) AS (NONSEQUENCED TRANSACTIONTIME SELECT pk,tt FROM tpar ) SELECT * FROM ( NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.tt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, ttp1 PERIOD(timestamp with time zone)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,ttp) , tchld WHERE pk=fk AND NOT (tblf.ttp CONTAINS tchld.tt) AND tchld.tt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ TT child -- BiT parent(TRC) *****************************/ /**********************************************************************/ /* CURRENT TRANSACTIONTIME RI AND TRC on parent */ /* RI definition. current transactiontime foreign key(fk,dt) references with no check option tpar(pk,vt) */ drop table nm_par; CREATE MULTISET TABLE nm_par ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( pk INTEGER, vtp PERIOD(DATE) as VALIDTIME) PRIMARY INDEX ( pk ); INSERT INTO nm_par (pk,vtp) WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar) SELECT * FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d ) AS TBLF(pk,vtp); SELECT * FROM ( CURRENT VALIDTIME SELECT DISTINCT fk,dt FROM nm_par LEFT OUTER JOIN tchld ON fk=pk WHERE NOT( vtp CONTAINS period(dt, UNTIL_CHANGED)) AND vtp IS NOT NULL UNION SELECT * FROM(NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,tchld.dt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL)DT)dt(x,y); /* SEQUENCED TRANSACTIONTIME RI AND TRC on parent */ /* RI definition. sequenced transactiontime foreign key(fk,dt) references with no check option tpar(pk,vt) */ /* child row's TT must be contained in the normalized TT of the parent for each of the matching fk-pk rows */ /* normalize the VT on open rows in TT AND PK column - with this result, normalize in TT diemntion on matching PK AND VT */ drop table tmptbl; CREATE MULTISET TABLE tmptbl AS ( WITH SUBTBL(x,d,t) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt, tt FROM tpar ) SELECT * FROM ( TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t), SUBTBL.d ) RETURNS (pk1 INTEGER, ttp1 PERIOD(timestamp with time zone), vtp1 period(date)) HASH BY x, t LOCAL ORDER BY x,t,d)AS TBLF(pk,tt, vt)) ) with data ; drop table tmptbl1; CREATE MULTISET TABLE tmptbl1 AS ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT pk, vt, tt FROM tpar WHERE END(tt) is not until_closed UNION SELECT pk, vt, tt FROM tmptbl ) with data ; WITH SUBTBL(x,v, d) AS ( SEL * FROM tmptbl1 ) SELECT * FROM ( NONSEQUENCED TRANSACTIONTIME SELECT fk, tchld.dt, tchld.tt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 period(date), ttp1 PERIOD(timestamp with time zone)) HASH BY x, v LOCAL ORDER BY x,v,d)AS TBLF(pk,vtp, ttp) , tchld WHERE pk=fk AND ( (NOT (tblf.ttp CONTAINS tchld.tt) ) OR (NOT ( tblf.vtp CONTAINS period(tchld.dt, UNTIL_CHANGED) ) ) ) UNION SELECT * FROM (NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.dt, tchld.tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt order by 1, 2 ; /**********************************************************************/ /************ BiT child -- NT parent **********************************/ /**********************************************************************/ /* NRI in both the dimensions */ /* RI definition. nonsequenced validtime and nonsequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,vt,tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL ; /**********************************************************************/ /************ BiT child -- VT parent **********************************/ /**********************************************************************/ /* CRI in VT dimension NRI in TT dimension */ /* RI definition. current validtime and nonsequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar WHERE END( tpar.vt ) >= temporal_date ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk, tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS period( CASE WHEN begin (tchld.vt) > temporal_date THEN begin (tchld.vt) ELSE temporal_date END , END (tchld.vt))) AND END( tchld.vt ) >= temporal_date AND END( tblf.vtp ) >= temporal_date AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar WHERE END ( tpar.vt ) >= temporal_date ) AND fk IS NOT NULL AND END( tchld.vt ) >= temporal_date)DT)dt(x,y); /* SRI in VT dimension NRI in TT dimension */ /* RI definition. sequenced validtime and nonsequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME SELECT pk,vt FROM tpar ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS tchld.vt) AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y) order by 1, 2 ; /**********************************************************************/ /************ BiT child -- TT parent **********************************/ /**********************************************************************/ /* NRI in VT dimension CRI in TT dimension */ /* RI definition. nonsequenced validtime and current transactiontime foreign key(fk) references with no check option tpar(pk) */ /* CURRENT rows in child should have equivalent PK in parent's open rows for each matching fk-pk rows */ NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,vt,tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar) AND fk IS NOT NULL; /* NRI in VT dimension SRI in TT dimension */ /* RI definition. nonsequenced validtime and sequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ /* child row's TT must be contained in the normalized TT of the parent for each of the matching fk-pk rows */ WITH SUBTBL(x,d ) AS (NONSEQUENCED TRANSACTIONTIME SELECT pk,tt FROM tpar ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT DISTINCT fk,tchld.tt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, ttp1 PERIOD(timestamp with time zone)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,ttp) , tchld WHERE pk=fk AND NOT (tblf.ttp CONTAINS tchld.tt) AND tchld.tt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y); /**********************************************************************/ /************ BiT child -- BiT parent *********************************/ /**********************************************************************/ /* CRI in VT dimension, CRI TT dimension */ /* RI definition. current validtime and current transactiontime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar WHERE END( tpar.vt ) >= temporal_date ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk, tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS period( CASE WHEN begin (tchld.vt) > temporal_date THEN begin (tchld.vt) ELSE temporal_date END , END (tchld.vt))) AND END( tchld.vt ) >= temporal_date AND END( tblf.vtp ) >= temporal_date AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar WHERE END ( tpar.vt ) >= temporal_date ) AND fk IS NOT NULL AND END( tchld.vt ) >= temporal_date)DT)dt(x,y); /* SRI in VT dimension, CRI TT dimension */ /* RI definition. sequenced validtime and current transactiontime foreign key(fk) references with no check option tpar(pk) */ WITH SUBTBL(x,d ) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt FROM tpar ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,tchld.vt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 PERIOD(DATE)) HASH BY x LOCAL ORDER BY x,d) AS TBLF(pk,vtp) , tchld WHERE pk=fk AND NOT (tblf.vtp CONTAINS tchld.vt) AND tchld.vt IS NOT NULL UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT fk,tchld.vt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt(x,y); /******************************************* * CRI in VT dimension, SRI TT dimension ********************************************/ /* RI definition. current validtime and sequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ drop table tmptbl; CREATE MULTISET TABLE tmptbl AS ( WITH SUBTBL(x,d,t) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt, tt FROM tpar ) SELECT * FROM ( TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t), SUBTBL.d ) RETURNS (pk1 INTEGER, ttp1 PERIOD(timestamp with time zone), vtp1 period(date)) HASH BY x, t LOCAL ORDER BY x,t,d)AS TBLF(pk,tt, vt)) ) with data ; drop table tmptbl1; CREATE MULTISET TABLE tmptbl1 AS ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT pk, vt, tt FROM tpar WHERE END(tt) is not until_closed UNION SELECT pk, vt, tt FROM tmptbl ) with data ; WITH SUBTBL(x,v, d) AS ( SEL * FROM tmptbl1 ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT DISTINCT fk, tchld.vt, tchld.tt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 period(date), ttp1 PERIOD(timestamp with time zone)) HASH BY x, v LOCAL ORDER BY x,v,d)AS TBLF(pk,vtp, ttp) , tchld WHERE pk=fk AND ( (NOT (tblf.ttp CONTAINS tchld.tt) ) OR (NOT ( tblf.vtp CONTAINS PERIOD( CASE WHEN begin (tchld.vt) > temporal_date THEN begin (tchld.vt) ELSE temporal_date END , END (tchld.vt)) ) ) ) AND END(tchld.vt) >= TEMPORAL_DATE UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.vt, tchld.tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt order by 1, 2 ; /******************************************* * SRI in VT dimension, SRI TT dimension ********************************************/ /* RI definition. sequenced validtime and sequenced transactiontime foreign key(fk) references with no check option tpar(pk) */ drop table tmptbl; CREATE MULTISET TABLE tmptbl AS ( WITH SUBTBL(x,d,t) AS (NONSEQUENCED VALIDTIME AND CURRENT TRANSACTIONTIME SELECT pk,vt, tt FROM tpar ) SELECT * FROM ( TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.t), SUBTBL.d ) RETURNS (pk1 INTEGER, ttp1 PERIOD(timestamp with time zone), vtp1 period(date)) HASH BY x, t LOCAL ORDER BY x,t,d)AS TBLF(pk,tt, vt)) ) with data ; drop table tmptbl1; CREATE MULTISET TABLE tmptbl1 AS ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT pk, vt, tt FROM tpar WHERE END(tt) is not until_closed UNION SELECT pk, vt, tt FROM tmptbl ) with data ; WITH SUBTBL(x,v, d) AS ( SEL * FROM tmptbl1 ) SELECT * FROM ( NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT DISTINCT fk, tchld.vt, tchld.tt FROM TABLE ( TD_SYSFNLIB.TD_NORMALIZE_MEET( NEW VARIANT_TYPE(SUBTBL.x, SUBTBL.v), SUBTBL.d ) RETURNS (pk1 INTEGER, vtp1 period(date), ttp1 PERIOD(timestamp with time zone)) HASH BY x, v LOCAL ORDER BY x,v,d)AS TBLF(pk,vtp, ttp) , tchld WHERE pk=fk AND ( (NOT (tblf.ttp CONTAINS tchld.tt) ) OR (NOT ( tblf.vtp CONTAINS tchld.vt ) ) ) UNION SELECT * FROM (NONSEQUENCED VALIDTIME AND NONSEQUENCED TRANSACTIONTIME SELECT fk,tchld.vt, tchld.tt FROM tchld WHERE fk NOT IN ( SELECT pk FROM tpar ) AND fk IS NOT NULL)DT)dt order by 1, 2 ;