--#COMMENT ----------------------------------------------------------
--#COMMENT ---                                                    ---
--#COMMENT ---     FILE:    SQL_VRN.HTML                          ---
--#COMMENT ---     AUTHOR:  G.BIRCHALL                            ---
--#COMMENT ---     DATE:    04/SEP/2003                           ---
--#COMMENT ---                                                    ---
--#COMMENT ---     NOTES:   This file contains some very rough    ---
--#COMMENT ---              sample DDL that illustrates how one   ---
--#COMMENT ---              can use a combination of DB2 triggers ---
--#COMMENT ---              and views to do the following:        ---
--#COMMENT ---                                                    ---
--#COMMENT ---               - Record every change to the data    ---
--#COMMENT ---                 in an application (auditing).      ---
--#COMMENT ---               - Show the state of the data, as     ---
--#COMMENT ---                 it was, at any point in the past   ---
--#COMMENT ---                 (historical analysis).             ---
--#COMMENT ---               - Follow the sequence of changes to  ---
--#COMMENT ---                 any item (e.g. customer), or set   ---
--#COMMENT ---                 of items, in the database.         ---
--#COMMENT ---               - Do "what if" analysis by creating  ---
--#COMMENT ---                 virtual copies of the real world   ---
--#COMMENT ---                 and then changing them as desired, ---
--#COMMENT ---                 without affecting the real-world   ---
--#COMMENT ---                 data.                              ---
--#COMMENT ---                                                    ---
--#COMMENT ---              The schema uses tables, which hold    ---
--#COMMENT ---              all states of the data, and views     ---
--#COMMENT ---              (of the tables), which display (to    ---
--#COMMENT ---              the user) the current state of the    ---
--#COMMENT ---              underlying tables.                    ---
--#COMMENT ---                                                    ---
--#COMMENT ---              When the user updates the data in     ---
--#COMMENT ---              a given view, an INSTEAD OF trigger   ---
--#COMMENT ---              is invoked to turn the change into an ---
--#COMMENT ---              INSERT on the underlying table. All   ---
--#COMMENT ---              of this is totally transparent to     ---
--#COMMENT ---              the user.                             ---
--#COMMENT ---                                                    ---
--#COMMENT ---              This general design is very useful    ---
--#COMMENT ---              for those applications that do not    ---
--#COMMENT ---              have too much data (i.e. table sizes  ---
--#COMMENT ---              < ten million rows) and not too much  ---
--#COMMENT ---              activity (< 50,000 changes/day), and  ---
--#COMMENT ---              which have the following needs:       ---
--#COMMENT ---                                                    ---
--#COMMENT ---                - Need to track (and hold) every    ---
--#COMMENT ---                  change that ever happens.         ---
--#COMMENT ---                - Need to create many copies of the ---
--#COMMENT ---                  real world for what-if analysis.  ---
--#COMMENT ---                                                    ---
--#COMMENT ---              This general design may NOT perform   ---
--#COMMENT ---              very efficiently if a single item     ---
--#COMMENT ---              (e.g. customer row) is updated many   ---
--#COMMENT ---              hundreds of times.  DB2 will have to  ---
--#COMMENT ---              search all of the rows, to find the   ---
--#COMMENT ---              one which has NOT been replaced.      ---
--#COMMENT ---                                                    ---
--#COMMENT ---                                                    ---
--#COMMENT ---              VERSIONS                              ---
--#COMMENT ---                                                    ---
--#COMMENT ---              This table design supports multiple   ---
--#COMMENT ---              (up to one billion) versions, where   ---
--#COMMENT ---              a version is a virtual copy of the    ---
--#COMMENT ---              real world.  Version zero is deemed   ---
--#COMMENT ---              to be reality.                        ---
--#COMMENT ---                                                    ---
--#COMMENT ---              To create a new version, one inserts  ---
--#COMMENT ---              a row into the VERSION table - always ---
--#COMMENT ---              with a begin-timestamp that is less   ---
--#COMMENT ---              than or equal to the current time.    ---
--#COMMENT ---              A new version automatically includes  ---
--#COMMENT ---              (points to) all of the existing data  ---
--#COMMENT ---              in version zero (reality) - up to     ---
--#COMMENT ---              the version cutoff time.              ---
--#COMMENT ---                                                    ---
--#COMMENT ---              Changes to the data in a version do   ---
--#COMMENT ---              not impact the data in version zero   ---
--#COMMENT ---              (reality), nor in any other version.  ---
--#COMMENT ---              Likewise, changes in version zero     ---
--#COMMENT ---              that happen after the version cutoff  ---
--#COMMENT ---              time do not show up in the version.   ---
--#COMMENT ---                                                    ---
--#COMMENT ---              As written, a (non-zero) version can  ---
--#COMMENT ---              only be deleted if no data rows were  ---
--#COMMENT ---              entered (i.e. it is a read-only       ---
--#COMMENT ---              version).  This rule can be changed   ---
--#COMMENT ---              (to cascading deletes) if desired.    ---
--#COMMENT ---                                                    ---
--#COMMENT ---                                                    ---
--#COMMENT ---              USER PROFILE                          ---
--#COMMENT ---                                                    ---
--#COMMENT ---              To use the data tables, the user has  ---
--#COMMENT ---              to insert a row into the user profile ---
--#COMMENT ---              table, saying what version they are   ---
--#COMMENT ---              currently using, and then access the  ---
--#COMMENT ---              tables via the provided view.         ---
--#COMMENT ---                                                    ---
--#COMMENT ---                                                    ---
--#COMMENT ---              DBA ISSUES                            ---
--#COMMENT ---                                                    ---
--#COMMENT ---              The GRANT statements listed below     ---
--#COMMENT ---              illustrate the authorities one would, ---
--#COMMENT ---              and would not, grant to users of this ---
--#COMMENT ---              application. Note that the underlying ---
--#COMMENT ---              data tables cannot be changed         ---
--#COMMENT ---              directly in any way.                  ---
--#COMMENT ---                                                    ---
--#COMMENT ---              The complex views used do not support ---
--#COMMENT ---              the use of DB2 referential activity   ---
--#COMMENT ---              between the data tables.  One can use ---
--#COMMENT ---              triggers to enforce R.I. (see example ---
--#COMMENT ---              in PROD_SALES below), but one must be ---
--#COMMENT ---              careful as there should only be one   ---
--#COMMENT ---              INSTEAD OF trigger per view/action.   ---
--#COMMENT ---                                                    ---
--#COMMENT ---              Enforcing uniqueness of the business  ---
--#COMMENT ---              keys can be, and is done below, using ---
--#COMMENT ---              checks in the relevant triggers, but  ---
--#COMMENT ---              it is not very pretty code. Note that ---
--#COMMENT ---              for this general design to work, all  ---
--#COMMENT ---              tables must have a unique business    ---
--#COMMENT ---              key (which can change over time).     ---
--#COMMENT ---                                                    ---
--#COMMENT ---              The sample application used is a very ---
--#COMMENT ---              simple one that has three tables:     ---
--#COMMENT ---                - CUSTOMER:   One row per customer. ---
--#COMMENT ---                - PRODUCT:    One row per product.  ---
--#COMMENT ---                - PROD_SALES: One row per sale of   ---
--#COMMENT ---                  a product to a customer.          ---
--#COMMENT ---              There are two support tables:         ---
--#COMMENT ---                - VERSION:    One row per version.  ---
--#COMMENT ---                - PROFILE:    One row per user.     ---
--#COMMENT ---              Each data table has two views:        ---
--#COMMENT ---                - One with data on the prior row    ---
--#COMMENT ---                  (if any) and current version/user.---
--#COMMENT ---                - One that the users access, with   ---
--#COMMENT ---                  business fields only.             ---
--#COMMENT ---              Each data table has three triggers:   ---
--#COMMENT ---                - INSERT:  Does the insert (plus).  ---
--#COMMENT ---                - UPDATE:  Turns into an insert.    ---
--#COMMENT ---                - DELETE:  Turns into an insert.    ---
--#COMMENT ---                                                    ---
--#COMMENT ----------------------------------------------------------



--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE VERSION TABLE.                   ---
--#COMMENT ---                                            ---
--#COMMENT ---    This table has one row per version.     ---
--#COMMENT ---    The design shown below is a minimalist  ---
--#COMMENT ---    edition.  One might also want to track  ---
--#COMMENT ---    who made a version, and when.           ---
--#COMMENT ---                                            ---
--#COMMENT ---    Version zero (reality) must have a      ---
--#COMMENT ---    begin timestamp of high-values. All     ---
--#COMMENT ---    versions must have a begin timestamp    ---
--#COMMENT ---    that is <= the current timestamp. A     ---
--#COMMENT ---    trigger checks all this.                ---
--#COMMENT ---                                            ---
--#COMMENT ---    Updates of the begin-version timestamp  ---
--#COMMENT ---    field must NOT be allowed, as it could  ---
--#COMMENT ---    lead to corrupt data.  To prevent this, ---
--#COMMENT ---    a sample trigger has been provided.     ---
--#COMMENT ---                                            ---
--#COMMENT ---    Deletes are, in theory, allowed if the  ---
--#COMMENT ---    version did NOT change any rows in the  ---
--#COMMENT ---    data tables.  This would allow a given  ---
--#COMMENT ---    version number to be reused.  To this   ---
--#COMMENT ---    end, this table uses R.I. to check all  ---
--#COMMENT ---    of the related data tables. But such a  ---
--#COMMENT ---    check is going to be very expensive (if ---
--#COMMENT ---    there are many large data tables) so it ---
--#COMMENT ---    might be better to just ban deletes, or ---
--#COMMENT ---    to do them offline at scheduled times.  ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TABLE version
(vrsn         INTEGER       NOT NULL
,vrsn_bgn_ts  TIMESTAMP     NOT NULL
,CONSTRAINT version1 CHECK(vrsn >= 0)
,CONSTRAINT version2 CHECK(vrsn  < 1000000000)
,PRIMARY KEY(vrsn));
COMMIT;

CREATE TRIGGER version_ins
NO CASCADE BEFORE
INSERT ON version
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
  SET nnn.vrsn_bgn_ts =
  CASE
     WHEN (nnn.vrsn         = 0
      AND  nnn.vrsn_bgn_ts <> '9999-12-31-24.00.00')
       OR (nnn.vrsn        <> 0
      AND  nnn.vrsn_bgn_ts  > CURRENT TIMESTAMP)
     THEN RAISE_ERROR('70001','ERROR: Vrsn-TS > current-TS')
     ELSE nnn.vrsn_bgn_ts
  END;
COMMIT;

CREATE TRIGGER version_upd
NO CASCADE BEFORE
UPDATE ON version
FOR EACH ROW
MODE DB2SQL
   SIGNAL SQLSTATE '71000' 
   SET MESSAGE_TEXT = 'ERROR: Cannot update version row';
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE PROFILE TABLE.                   ---
--#COMMENT ---                                            ---
--#COMMENT ---    This table has one row per user (DB2    ---
--#COMMENT ---    USER special register) who uses (reads  ---
--#COMMENT ---    or changes) the data tables.            ---
--#COMMENT ---                                            ---
--#COMMENT ---    The VRSN field indicates which version  ---
--#COMMENT ---    the user is currently using.  To change ---
--#COMMENT ---    versions, update this field accordingly.---
--#COMMENT ---                                            ---
--#COMMENT ---    The VRSN_BGN_TS field has (a copy of)   ---
--#COMMENT ---    the begin-timestamp for the user's      ---
--#COMMENT ---    current version.  It is maintained      ---
--#COMMENT ---    automatically using triggers.           ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TABLE profile
(user_id      VARCHAR(10)   NOT NULL
,vrsn         INTEGER       NOT NULL
,vrsn_bgn_ts  TIMESTAMP     NOT NULL
,CONSTRAINT profile1 FOREIGN KEY(vrsn)
                     REFERENCES version(vrsn)
                     ON DELETE RESTRICT
,PRIMARY KEY(user_id));
COMMIT;

CREATE TRIGGER profile_ins
NO CASCADE BEFORE
INSERT ON profile
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
  SET nnn.vrsn_bgn_ts = (SELECT vvv.vrsn_bgn_ts
                         FROM   version vvv
                         WHERE  vvv.vrsn = nnn.vrsn);
COMMIT;

CREATE TRIGGER profile_upd
NO CASCADE BEFORE
UPDATE ON profile
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
  SET nnn.vrsn_bgn_ts = (SELECT vvv.vrsn_bgn_ts
                         FROM   version vvv
                         WHERE  vvv.vrsn = nnn.vrsn);
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE DATA (+HISTORY) TABLES.          ---
--#COMMENT ---                                            ---
--#COMMENT ---    The following tables hold the business  ---
--#COMMENT ---    data, and also a record of all changes  ---
--#COMMENT ---    to that data.                           ---
--#COMMENT ---                                            ---
--#COMMENT ---    Every table must have a unique business ---
--#COMMENT ---    key (e.g. customer-number), which can   ---
--#COMMENT ---    change over time, and also be deleted   ---
--#COMMENT ---    and then re-inserted, but which must    ---
--#COMMENT ---    always be unique (within a version).    ---
--#COMMENT ---                                            ---
--#COMMENT ---    Each table has (added to it) some extra ---
--#COMMENT ---    fields, that record changes:            ---
--#COMMENT ---    - CUR_TS:    Current timestamp (time    ---
--#COMMENT ---                 row was inserted).         ---
--#COMMENT ---    - CUR_VRSN:  Current version that the   ---
--#COMMENT ---                 user is using.             ---
--#COMMENT ---    - CUR_ACTN:  Type of DB2 action that    ---
--#COMMENT ---                 created the row: insert,   ---
--#COMMENT ---                 update, or delete.         ---
--#COMMENT ---    - CUR_USER:  Who changed the row (for   ---
--#COMMENT ---                 auditing purposes only).   ---
--#COMMENT ---    The next three fields are NULL if the   ---
--#COMMENT ---    row represents an insert, but point to  ---
--#COMMENT ---    the previous row for updates & deletes. ---
--#COMMENT ---    - PRV_KEY:   The key (e.g. customer     ---
--#COMMENT ---                 number) of the previous    ---
--#COMMENT ---                 row.                       ---
--#COMMENT ---    - PRV_TS:    Previous row timestamp.    ---
--#COMMENT ---    - PRV_VRSN:  Previous row version.      ---
--#COMMENT ---                                            ---
--#COMMENT ---    Observe that the tables do NOT have a   ---
--#COMMENT ---    begin and end timestamp.  Instead, each ---
--#COMMENT ---    row optionally points back to the one   ---
--#COMMENT ---    that it replaces.  This design enables  ---
--#COMMENT ---    to have a one-to-many relationship (and ---
--#COMMENT ---    thus multiple versions) between any one ---
--#COMMENT ---    row, and what happens to it at some     ---
--#COMMENT ---    future point in time.                   ---
--#COMMENT ---                                            ---
--#COMMENT ---    A secondary index (on previous-key, ts, ---
--#COMMENT ---    and version) has been added to make the ---
--#COMMENT ---    following views more efficient.         ---
--#COMMENT ---                                            ---
--#COMMENT ---    Do NOT allow direct updates to these    ---
--#COMMENT ---    tables.  Inserts, updates, and deletes  ---
--#COMMENT ---    must come through the views provided    ---
--#COMMENT ---    below, so the INSTEAD OF triggers can   ---
--#COMMENT ---    act on them.                            ---
--#COMMENT ---                                            ---
--#COMMENT ---    One can remove the foreign-key checks   ---
--#COMMENT ---    shown below if you ban all deletes on   ---
--#COMMENT ---    the version table.                      ---
--#COMMENT ---                                            ---
--#COMMENT ---    The cur_actn constraints are just to    ---
--#COMMENT ---    make sure that my triggers to do not    ---
--#COMMENT ---    have a boo-boo. Remove if you wish.     ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TABLE customer_his
(cust#        INTEGER       NOT NULL
,cust_name    CHAR(10)      NOT NULL
,cust_mgr     CHAR(10)
,cur_ts       TIMESTAMP     NOT NULL
,cur_vrsn     INTEGER       NOT NULL
,cur_actn     CHAR(1)       NOT NULL
,cur_user     VARCHAR(10)   NOT NULL
,prv_cust#    INTEGER
,prv_ts       TIMESTAMP
,prv_vrsn     INTEGER
,CONSTRAINT customer1 FOREIGN KEY(cur_vrsn)
                      REFERENCES version(vrsn)
                      ON DELETE RESTRICT
,CONSTRAINT customer2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(cust#,cur_vrsn,cur_ts));

CREATE INDEX customer_x2 ON customer_his
(prv_cust#
,prv_ts
,prv_vrsn);

CREATE TABLE product_his
(prod#        SMALLINT      NOT NULL
,prod_name    CHAR(10)      NOT NULL
,prod_cost    DECIMAL(7,2)  NOT NULL
,intro_dt     DATE          NOT NULL
,cur_ts       TIMESTAMP     NOT NULL
,cur_vrsn     INTEGER       NOT NULL
,cur_actn     CHAR(1)       NOT NULL
,cur_user     VARCHAR(10)   NOT NULL
,prv_prod#    SMALLINT
,prv_ts       TIMESTAMP
,prv_vrsn     INTEGER
,CONSTRAINT product1 FOREIGN KEY(cur_vrsn)
                     REFERENCES version(vrsn)
                     ON DELETE RESTRICT
,CONSTRAINT product2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(prod#,cur_vrsn,cur_ts));

CREATE INDEX product_x2 ON product_his
(prv_prod#
,prv_ts
,prv_vrsn);

CREATE TABLE prod_sales_his
(prod#        SMALLINT      NOT NULL
,cust#        INTEGER       NOT NULL
,sale_ts      TIMESTAMP     NOT NULL
,qty_sold     SMALLINT      NOT NULL
,cur_ts       TIMESTAMP     NOT NULL
,cur_vrsn     INTEGER       NOT NULL
,cur_actn     CHAR(1)       NOT NULL
,cur_user     VARCHAR(10)   NOT NULL
,prv_prod#    SMALLINT
,prv_cust#    INTEGER
,prv_sale_ts  TIMESTAMP
,prv_ts       TIMESTAMP
,prv_vrsn     INTEGER
,CONSTRAINT prod_sales1 FOREIGN KEY(cur_vrsn)
                        REFERENCES version(vrsn)
                        ON DELETE RESTRICT
,CONSTRAINT prod_sales2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(cust#,prod#,sale_ts,cur_vrsn,cur_ts));

CREATE INDEX prod_sales_x2 ON prod_sales_his
(prv_prod#
,prv_cust#
,prv_sale_ts
,prv_ts
,prv_vrsn);
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE DATA VIEWS (LONG FORM).          ---
--#COMMENT ---                                            ---
--#COMMENT ---    There is one view below for every data  ---
--#COMMENT ---    table specified above.  Each view shows ---
--#COMMENT ---    all of the rows in the table that, for  ---
--#COMMENT ---    the user's current version (and version ---
--#COMMENT ---    zero, where applicable) does NOT have a ---
--#COMMENT ---    row that points back to them.           ---
--#COMMENT ---                                            ---
--#COMMENT ---    All inserts, updates, and deletes are,  ---
--#COMMENT ---    directly or indirectly, done on these   ---
--#COMMENT ---    views, where the INSTEAD OF triggers    ---
--#COMMENT ---    then turn them into inserts.            ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE VIEW customer_vw AS
SELECT  *
FROM    customer_his hhh
       ,profile      ppp
WHERE   ppp.user_id    =  USER
  AND   hhh.cur_actn  <> 'D'
  AND ((ppp.vrsn       =  0
  AND   hhh.cur_vrsn   =  0)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  0
  AND   hhh.cur_ts     <  ppp.vrsn_bgn_ts)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  ppp.vrsn))
  AND   NOT EXISTS
       (SELECT *
        FROM    customer_his nnn
        WHERE   nnn.prv_cust#  =  hhh.cust#
          AND   nnn.prv_ts     =  hhh.cur_ts
          AND   nnn.prv_vrsn   =  hhh.cur_vrsn
          AND ((ppp.vrsn       =  0
          AND   nnn.cur_vrsn   =  0)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  0
          AND   nnn.cur_ts     <  ppp.vrsn_bgn_ts)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  ppp.vrsn)));
COMMIT;

CREATE VIEW product_vw AS
SELECT  *
FROM    product_his hhh
       ,profile     ppp
WHERE   ppp.user_id    =  USER
  AND   hhh.cur_actn  <> 'D'
  AND ((ppp.vrsn       =  0
  AND   hhh.cur_vrsn   =  0)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  0
  AND   hhh.cur_ts     <  ppp.vrsn_bgn_ts)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  ppp.vrsn))
  AND   NOT EXISTS
       (SELECT *
        FROM   product_his nnn
        WHERE  nnn.prv_prod#   =  hhh.prod#
          AND  nnn.prv_ts      =  hhh.cur_ts
          AND  nnn.prv_vrsn    =  hhh.cur_vrsn
          AND ((ppp.vrsn       =  0
          AND   nnn.cur_vrsn   =  0)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  0
          AND   nnn.cur_ts     <  ppp.vrsn_bgn_ts)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  ppp.vrsn)));
COMMIT;

CREATE VIEW prod_sales_vw AS
SELECT  *
FROM    prod_sales_his hhh
       ,profile        ppp
WHERE   ppp.user_id    =  USER
  AND   hhh.cur_actn  <> 'D'
  AND ((ppp.vrsn       =  0
  AND   hhh.cur_vrsn   =  0)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  0
  AND   hhh.cur_ts     <  ppp.vrsn_bgn_ts)
   OR  (ppp.vrsn       >  0
  AND   hhh.cur_vrsn   =  ppp.vrsn))
  AND   NOT EXISTS
       (SELECT *
        FROM   prod_sales_his nnn
        WHERE  nnn.prv_prod#   =  hhh.prod#
          AND  nnn.prv_cust#   =  hhh.cust#
          AND  nnn.prv_sale_ts =  hhh.sale_ts
          AND  nnn.prv_ts      =  hhh.cur_ts
          AND  nnn.prv_vrsn    =  hhh.cur_vrsn
          AND ((ppp.vrsn       =  0
          AND   nnn.cur_vrsn   =  0)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  0
          AND   nnn.cur_ts     <  ppp.vrsn_bgn_ts)
           OR  (ppp.vrsn       >  0
          AND   nnn.cur_vrsn   =  ppp.vrsn)));
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE DATA VIEWS (SHORT FORM).         ---
--#COMMENT ---                                            ---
--#COMMENT ---    These views are the ones that the users ---
--#COMMENT ---    would normal refer to (to select and/or ---
--#COMMENT ---    update).  They are the same as the long ---
--#COMMENT ---    form views above, except that only the  ---
--#COMMENT ---    business fields are shown.              ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE VIEW customer AS
SELECT  cust#
       ,cust_name
       ,cust_mgr
FROM    customer_vw;

CREATE VIEW product AS
SELECT  prod#
       ,prod_name
       ,prod_cost
       ,intro_dt
FROM    product_vw;

CREATE VIEW prod_sales AS
SELECT  prod#
       ,cust#
       ,sale_ts
       ,qty_sold
FROM    prod_sales_vw;
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE INSERT TRIGGERS (DATA TABLES).   ---
--#COMMENT ---                                            ---
--#COMMENT ---    These INSTEAD OF triggers substitute    ---
--#COMMENT ---    the user's insert with another insert   ---
--#COMMENT ---    that also populates the current-data    ---
--#COMMENT ---    fields.                                 ---
--#COMMENT ---                                            ---
--#COMMENT ---    To illustrate how it can be done, the   ---
--#COMMENT ---    triggers also check for non-unique key  ---
--#COMMENT ---    values and, in the PROD_SALES table,    ---
--#COMMENT ---    for foreign-key relationships. In the   ---
--#COMMENT ---    PRODUCT table, the DELETE trigger also  ---
--#COMMENT ---    does cascading deletes (to PROD_SALES). ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_ins
INSTEAD OF
INSERT ON customer_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
   INSERT INTO customer_his VALUES
   (nnn.cust#
   ,nnn.cust_name
   ,nnn.cust_mgr
   ,CURRENT TIMESTAMP
   ,(SELECT vrsn
     FROM   profile
     WHERE  user_id = USER)
   ,CASE
       WHEN 0 < (SELECT COUNT(*)
                 FROM   customer
                 WHERE  cust# = nnn.cust#)
       THEN RAISE_ERROR('71001','ERROR: Duplicate cust#')
       ELSE 'I'
    END
   ,USER
   ,NULL
   ,NULL
   ,NULL);
COMMIT;

CREATE TRIGGER product_ins
INSTEAD OF
INSERT ON product_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
   INSERT INTO product_his VALUES
   (nnn.prod#
   ,nnn.prod_name
   ,nnn.prod_cost
   ,nnn.intro_dt
   ,CURRENT TIMESTAMP
   ,(SELECT vrsn
     FROM   profile
     WHERE  user_id = USER)
   ,CASE
       WHEN 0 < (SELECT COUNT(*)
                 FROM   product
                 WHERE  prod# = nnn.prod#)
       THEN RAISE_ERROR('71002','ERROR: Duplicate prod#')
       ELSE 'I'
    END
   ,USER
   ,NULL
   ,NULL
   ,NULL);
COMMIT;

CREATE TRIGGER prod_sales_ins
INSTEAD OF
INSERT ON prod_sales_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
   INSERT INTO prod_sales_his VALUES
   (nnn.prod#
   ,nnn.cust#
   ,nnn.sale_ts
   ,nnn.qty_sold
   ,CURRENT TIMESTAMP
   ,(SELECT vrsn
     FROM   profile
     WHERE  user_id = USER)
   ,CASE
       WHEN 0 < (SELECT COUNT(*)
                 FROM   prod_sales
                 WHERE  prod#   = nnn.prod#
                   AND  cust#   = nnn.cust#
                   AND  sale_ts = nnn.sale_ts)
       THEN RAISE_ERROR('71003','ERROR: Duplicate prod#/cust#/ts')
       WHEN 0 = (SELECT COUNT(*)
                 FROM   product
                 WHERE  prod# = nnn.prod#)
       THEN RAISE_ERROR('71004','ERROR: No prod# in Product')
       WHEN 0 = (SELECT COUNT(*)
                 FROM   customer
                 WHERE  cust# = nnn.cust#)
       THEN RAISE_ERROR('71005','ERROR: No cust# in Customer')
       ELSE 'I'
    END
   ,USER
   ,NULL
   ,NULL
   ,NULL
   ,NULL
   ,NULL);
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE UPDATE TRIGGERS (DATA TABLES).   ---
--#COMMENT ---                                            ---
--#COMMENT ---    These INSTEAD OF triggers substitute    ---
--#COMMENT ---    the user's update with an insert that   ---
--#COMMENT ---    adds in a row that points to the        ---
--#COMMENT ---    current row.                            ---
--#COMMENT ---                                            ---
--#COMMENT ---    To illustrate how to do it, updates of  ---
--#COMMENT ---    the cust# (business key column) are not ---
--#COMMENT ---    allowed in the CUSTOMER table.          ---
--#COMMENT ---                                            ---
--#COMMENT ---    If the prod# in PRODUCT is updated, the ---
--#COMMENT ---    trigger updates the related rows in the ---
--#COMMENT ---    PROD_SALES table accordingly.           ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_upd
INSTEAD OF
UPDATE ON customer_vw
REFERENCING NEW AS nnn
            OLD AS ooo
FOR EACH ROW
MODE DB2SQL
   INSERT INTO customer_his VALUES
   (nnn.cust#
   ,nnn.cust_name
   ,nnn.cust_mgr
   ,CURRENT TIMESTAMP
   ,ooo.vrsn
   ,CASE
       WHEN nnn.cust# <> ooo.cust#
       THEN RAISE_ERROR('72001','ERROR: Cannot change cust#')
       ELSE 'U'
    END
   ,ooo.user_id
   ,ooo.cust#
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
COMMIT;

--#SET DELIMITER !
CREATE TRIGGER product_upd
INSTEAD OF
UPDATE ON product_vw
REFERENCING NEW AS nnn
            OLD AS ooo
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
   INSERT INTO product_his VALUES
   (nnn.prod#
   ,nnn.prod_name
   ,nnn.prod_cost
   ,nnn.intro_dt
   ,CURRENT TIMESTAMP
   ,ooo.vrsn
   ,CASE
       WHEN nnn.prod# <> ooo.prod#
        AND 0 < (SELECT COUNT(*)
                 FROM   product
                 WHERE  prod# = nnn.prod#)
       THEN RAISE_ERROR('72002','ERROR: Duplicate prod#')
       ELSE 'U'
    END
   ,ooo.user_id
   ,ooo.prod#
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
   INSERT INTO prod_sales_his
   SELECT  nnn.prod#
          ,sss.cust#
          ,sss.sale_ts
          ,sss.qty_sold
          ,CURRENT TIMESTAMP
          ,sss.vrsn
          ,'U'
          ,sss.user_id
          ,sss.prod#
          ,sss.cust#
          ,sss.sale_ts
          ,sss.cur_ts
          ,sss.cur_vrsn
   FROM    prod_sales_vw sss
   WHERE   sss.prod#  = ooo.prod#
     AND   ooo.prod# <> nnn.prod#;
END!
--#SET DELIMITER ;
COMMIT;

CREATE TRIGGER prod_sales_upd
INSTEAD OF
UPDATE ON prod_sales_vw
REFERENCING NEW AS nnn
            OLD AS ooo
FOR EACH ROW
MODE DB2SQL
   INSERT INTO prod_sales_his VALUES
   (nnn.prod#
   ,nnn.cust#
   ,nnn.sale_ts
   ,nnn.qty_sold
   ,CURRENT TIMESTAMP
   ,nnn.vrsn
   ,CASE
       WHEN (nnn.prod#   <> ooo.prod#
         OR  nnn.cust#   <> ooo.cust#
         OR  nnn.sale_ts <> ooo.sale_ts)
        AND 0 < (SELECT COUNT(*)
                 FROM   prod_sales
                 WHERE  prod#   = nnn.prod#
                   AND  cust#   = nnn.cust#
                   AND  sale_ts = nnn.sale_ts)
       THEN RAISE_ERROR('72003','ERROR: Duplicate prod#/cust#/ts')
       WHEN 0 = (SELECT COUNT(*)
                 FROM   product
                 WHERE  prod# = nnn.prod#)
       THEN RAISE_ERROR('72004','ERROR: No prod# in Product')
       WHEN 0 = (SELECT COUNT(*)
                 FROM   customer
                 WHERE  cust# = nnn.cust#)
       THEN RAISE_ERROR('72005','ERROR: No cust# in Customer')
       ELSE 'U'
    END
   ,ooo.user_id
   ,ooo.prod#
   ,ooo.cust#
   ,ooo.sale_ts
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE DELETE TRIGGERS (DATA TABLES).   ---
--#COMMENT ---                                            ---
--#COMMENT ---    These INSTEAD OF triggers substitute    ---
--#COMMENT ---    the user's delete with an insert that   ---
--#COMMENT ---    adds in a row that points to the        ---
--#COMMENT ---    current row.  The new row is given      ---
--#COMMENT ---    an CUR_ACTN value of "D", so it will    ---
--#COMMENT ---    not be visible in the views.            ---
--#COMMENT ---                                            ---
--#COMMENT ---    If a row is deleted in PRODUCT, then    ---
--#COMMENT ---    the trigger also deletes the related    ---
--#COMMENT ---    rows in PROD_SALES.                     ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_del
INSTEAD OF
DELETE ON customer_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
   INSERT INTO customer_his VALUES
   (ooo.cust#
   ,ooo.cust_name
   ,ooo.cust_mgr
   ,CURRENT TIMESTAMP
   ,ooo.vrsn
   ,'D'
   ,ooo.user_id
   ,ooo.cust#
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
COMMIT;

--#SET DELIMITER !
CREATE TRIGGER product_del
INSTEAD OF
DELETE ON product_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
   INSERT INTO product_his VALUES
   (ooo.prod#
   ,ooo.prod_name
   ,ooo.prod_cost
   ,ooo.intro_dt
   ,CURRENT TIMESTAMP
   ,ooo.vrsn
   ,'D'
   ,ooo.user_id
   ,ooo.prod#
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
   INSERT INTO prod_sales_his
   SELECT  sss.prod#
          ,sss.cust#
          ,sss.sale_ts
          ,sss.qty_sold
          ,CURRENT TIMESTAMP
          ,sss.vrsn
          ,'D'
          ,sss.user_id
          ,sss.prod#
          ,sss.cust#
          ,sss.sale_ts
          ,sss.cur_ts
          ,sss.cur_vrsn
   FROM    prod_sales_vw sss
   WHERE   sss.prod# = ooo.prod#;
END! 
--#SET DELIMITER ;
COMMIT;

CREATE TRIGGER prod_sales_del
INSTEAD OF
DELETE ON prod_sales_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
   INSERT INTO prod_sales_his VALUES
   (ooo.prod#
   ,ooo.cust#
   ,ooo.sale_ts
   ,ooo.qty_sold
   ,CURRENT TIMESTAMP
   ,ooo.vrsn
   ,'D'
   ,ooo.user_id
   ,ooo.prod#
   ,ooo.cust#
   ,ooo.sale_ts
   ,ooo.cur_ts
   ,ooo.cur_vrsn);
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    GRANT AUTHORITIES ON TABLES & VIEWS.    ---
--#COMMENT ---                                            ---
--#COMMENT ---    Below is a list of typical access that  ---
--#COMMENT ---    one would allow general users in this   ---
--#COMMENT ---    application.  Note especially that NO   ---
--#COMMENT ---    direct insert/update/delete access is   ---
--#COMMENT ---    is given on the underlying data tables. ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
GRANT SELECT ON version TO PUBLIC;
GRANT INSERT ON version TO PUBLIC;
GRANT DELETE ON version TO PUBLIC;

GRANT SELECT ON profile TO PUBLIC;
GRANT INSERT ON profile TO PUBLIC;
GRANT UPDATE ON profile TO PUBLIC;
GRANT DELETE ON profile TO PUBLIC;

GRANT SELECT ON customer TO PUBLIC;
GRANT INSERT ON customer TO PUBLIC;
GRANT UPDATE ON customer TO PUBLIC;
GRANT DELETE ON customer TO PUBLIC;

GRANT SELECT ON product TO PUBLIC;
GRANT INSERT ON product TO PUBLIC;
GRANT UPDATE ON product TO PUBLIC;
GRANT DELETE ON product TO PUBLIC;

GRANT SELECT ON prod_sales TO PUBLIC;
GRANT INSERT ON prod_sales TO PUBLIC;
GRANT UPDATE ON prod_sales TO PUBLIC;
GRANT DELETE ON prod_sales TO PUBLIC;

GRANT SELECT ON customer_his   TO PUBLIC;
GRANT SELECT ON product_his    TO PUBLIC;
GRANT SELECT ON prod_sales_his TO PUBLIC;
COMMIT;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    CREATE VERSION/PROFILE ROWS.            ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
INSERT INTO version VALUES(0,'9999-12-31-24.00.00');
INSERT INTO profile (user_id,vrsn) VALUES(USER,0);
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    BAD VERSION, FAIL ---
--#COMMENT ----------------------------
INSERT INTO version VALUES(1,CURRENT TIMESTAMP + 1 DAY);
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    SELECT FROM TABLES---
--#COMMENT ----------------------------
SELECT   *
FROM     version
ORDER BY vrsn;

SELECT   *
FROM     profile
ORDER BY user_id;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    RUN SOME TESTS ON CUSTOMER.             ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
INSERT INTO customer VALUES(001,'ABC','MMM');
INSERT INTO customer VALUES(002,'DEF','MMM');
INSERT INTO customer VALUES(003,'ERT','MMM');

UPDATE  customer
SET     cust_name = '222'
WHERE   cust# = 2;
COMMIT;

SELECT   *
FROM     customer_vw
ORDER BY cust#;

UPDATE  customer
SET     cust_name = '333'
WHERE   cust# >= 2;

INSERT INTO customer VALUES(004,'FGH','YYY');

SELECT   *
FROM     customer_vw
ORDER BY cust#;

DELETE
FROM    customer
WHERE   cust# IN (1,3,2);

INSERT INTO customer VALUES(002,'A22','M22');
INSERT INTO customer VALUES(007,'A77','M77');
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    SEE CURRENT DATA  ---
--#COMMENT ----------------------------
SELECT   *
FROM     customer
ORDER BY cust#;

SELECT   *
FROM     customer_vw
ORDER BY cust#;

SELECT   *
FROM     customer_his
ORDER BY cur_ts
        ,cust#;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    SEE PAST DATA     ---
--#COMMENT ----------------------------
INSERT INTO version VALUES
       (1
       ,(SELECT cur_ts + 1 MICROSECOND
         FROM   customer_his
         WHERE  cust# = 4));

UPDATE   profile
SET      vrsn    = 1
WHERE    user_id = USER;

SELECT   *
FROM     version
ORDER BY vrsn;

SELECT   *
FROM     profile
ORDER BY user_id;

SELECT   *
FROM     customer
ORDER BY cust#;

SELECT   *
FROM     customer_vw
ORDER BY cust#;
COMMIT;


--#COMMENT ----------------------------
--#COMMENT ---    CHANGE ROW IN VRSN---
--#COMMENT ----------------------------
UPDATE  customer
SET     cust_name = '771'
WHERE   cust# = 2;

UPDATE  customer
SET     cust_name = '772'
       ,cust_mgr  = 'M72'
WHERE   cust# = 2;

SELECT   *
FROM     customer_vw
ORDER BY cust#;

UPDATE   profile
SET      vrsn    = 0
WHERE    user_id = USER;

SELECT   *
FROM     customer_vw
ORDER BY cust#;

SELECT   *
FROM     customer_his
ORDER BY cur_ts
        ,cust#;
COMMIT;


--#COMMENT ----------------------------
--#COMMENT ---    INSERT DUP. ROW   ---
--#COMMENT ----------------------------
INSERT INTO customer VALUES(002,'DDD','DDD');
COMMIT;

SELECT   *
FROM     customer_vw
ORDER BY cust#;


--#COMMENT ----------------------------
--#COMMENT ---    UPDATE ROW        ---
--#COMMENT ----------------------------
UPDATE customer
SET    cust# = 7
WHERE  cust# = 2;
COMMIT;

SELECT   *
FROM     customer_vw
ORDER BY cust#;


--#COMMENT ----------------------------
--#COMMENT ---    TRY TO DELETE VRSN---
--#COMMENT ---    THAT HAS OWN DATA ---
--#COMMENT ---    (SHOULD FAIL).    ---
--#COMMENT ----------------------------
DELETE
FROM      version
WHERE     vrsn = 1;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    CREATE TEMP VRSN  ---
--#COMMENT ---    TO SEE OLD DATA   ---
--#COMMENT ---    (NO CHANGES).     ---
--#COMMENT ----------------------------
INSERT INTO version VALUES
       (2
       ,(SELECT cur_ts + 1 MICROSECOND
         FROM   customer_his
         WHERE  cust#    =  4
          AND   cur_actn = 'I'));

UPDATE   profile
SET      vrsn    = 2
WHERE    user_id = USER;

SELECT   *
FROM     version
ORDER BY vrsn;

SELECT   *
FROM     profile
ORDER BY user_id;

SELECT   *
FROM     customer
ORDER BY cust#;

SELECT   *
FROM     customer_vw
ORDER BY cust#;

UPDATE   profile
SET      vrsn    = 0
WHERE    user_id = USER;

DELETE
FROM     version
WHERE    vrsn = 2;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    TRACE "2" CHANGES ---
--#COMMENT ---    (IN VRSN 0 ONLY)  ---
--#COMMENT ----------------------------
WITH temp1 (cust#, cur_ts) AS
  (SELECT   cust#
           ,MIN(cur_ts)
   FROM     customer_his
   WHERE    cust#     = 2
     AND    cur_vrsn  = 0
   GROUP BY cust#
   UNION ALL
   SELECT   hhh.cust#
           ,hhh.cur_ts
   FROM     temp1        ttt
           ,customer_his hhh
   WHERE    ttt.cust#    = hhh.prv_cust#
     AND    ttt.cur_ts   = hhh.prv_ts
     AND    hhh.cur_vrsn = 0)
SELECT   hhh.*
FROM     temp1        ttt
        ,customer_his hhh
WHERE    ttt.cust#    = hhh.cust#
  AND    ttt.cur_ts   = hhh.cur_ts
  AND    hhh.cur_vrsn = 0
ORDER BY hhh.cur_ts;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    TRACE "2" CHANGES ---
--#COMMENT ---    (IN CURRENT VRSN) ---
--#COMMENT ----------------------------
UPDATE   profile
SET      vrsn    = 1
WHERE    user_id = USER;

WITH temp1 (cust#, cur_ts, cur_vrsn) AS
  (SELECT   hhh.cust#
           ,MIN(hhh.cur_ts)
           ,hhh.cur_vrsn
   FROM     customer_his hhh
           ,profile      ppp
   WHERE    hhh.cust#      =  2
     AND    ppp.user_id    =  USER
     AND  ((ppp.vrsn       =  0
     AND    hhh.cur_vrsn   =  0)
      OR   (ppp.vrsn       >  0
     AND    hhh.cur_vrsn   =  0
     AND    hhh.cur_ts     <  ppp.vrsn_bgn_ts)
      OR   (ppp.vrsn       >  0
     AND    hhh.cur_vrsn   =  ppp.vrsn))
   GROUP BY hhh.cust#
           ,hhh.cur_vrsn
   UNION ALL
   SELECT   hhh.cust#
           ,hhh.cur_ts
           ,hhh.cur_vrsn
   FROM     temp1        ttt
           ,customer_his hhh
           ,profile      ppp
   WHERE    ttt.cust#      =  hhh.prv_cust#
     AND    ttt.cur_ts     =  hhh.prv_ts
     AND    ttt.cur_vrsn   =  hhh.prv_vrsn
     AND  ((ppp.vrsn       =  0
     AND    ppp.user_id    =  USER
     AND    hhh.cur_vrsn   =  0)
      OR   (ppp.vrsn       >  0
     AND    hhh.cur_vrsn   =  0
     AND    hhh.cur_ts     <  ppp.vrsn_bgn_ts)
      OR   (ppp.vrsn       >  0
     AND    hhh.cur_vrsn   =  ppp.vrsn)))
SELECT   hhh.*
FROM     temp1        ttt
        ,customer_his hhh
WHERE    ttt.cust#    = hhh.cust#
  AND    ttt.cur_ts   = hhh.cur_ts
  AND    ttt.cur_vrsn = hhh.cur_vrsn
ORDER BY hhh.cur_ts;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    RUN SOME VOLUME TESTS ON PRODUCT.       ---
--#COMMENT ---                                            ---
--#COMMENT ---    On my desktop DB2 system, without any   ---
--#COMMENT ---    tuning, I am able to get the following  ---
--#COMMENT ---    performance rates (for 10K rowss):      ---
--#COMMENT ---    - INSERT:  600 rows per second.         ---
--#COMMENT ---    - UPDATE:  700 rows per second.         ---
--#COMMENT ---    - DELETE:  850 rows per second.         ---
--#COMMENT ---                                            ---
--#COMMENT ---    I do not have any secondary (business)  ---
--#COMMENT ---    indexes, and nor have my test rows been ---
--#COMMENT ---    updated many times, so your results may ---
--#COMMENT ---    vary.                                   ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    INSERT 10K ROWS   ---
--#COMMENT ----------------------------
UPDATE   profile
SET      vrsn    = 0
WHERE    user_id = USER;
COMMIT;

INSERT INTO product
WITH temp1 (n) AS
  (VALUES  (1)
   UNION ALL
   SELECT  n + 1
   FROM    temp1
   WHERE   n < 500)
SELECT   n
        ,CHAR(n)
        ,n * 1.23
        ,DATE('2003-10-14') + n DAYS
FROM     temp1;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    COMPARE TO INSERT ---
--#COMMENT ---    (measure time)    ---
--#COMMENT ----------------------------
WITH temp1 (n) AS
  (VALUES  (1)
   UNION ALL
   SELECT  n + 1
   FROM    temp1
   WHERE   n < 500)
SELECT   COUNT(*)
        ,COUNT(DISTINCT n)
FROM     temp1;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    CREATE NEW VRSN   ---
--#COMMENT ----------------------------
INSERT INTO version VALUES(3,CURRENT TIMESTAMP);

UPDATE   profile
SET      vrsn    = 3
WHERE    user_id = USER;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    UPDATE IN VRSN    ---
--#COMMENT ----------------------------
UPDATE   product
SET      prod_name = '3' CONCAT prod_name
WHERE    prod#     = prod# /3 * 3;
COMMIT;

DELETE
FROM     product
WHERE    prod# = prod# /7 * 7;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    UPDATE IN REALITY ---
--#COMMENT ----------------------------
UPDATE   profile
SET      vrsn    = 0
WHERE    user_id = USER;
COMMIT;

UPDATE   product
SET      prod_name = '0' CONCAT prod_name
WHERE    prod#     = prod# /3 * 3;
COMMIT;

DELETE
FROM     product
WHERE    prod# = prod# /9 * 9;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    QUERY DATA        ---
--#COMMENT ----------------------------
UPDATE   profile
SET      vrsn    = 0
WHERE    user_id = USER;
COMMIT;

SELECT   *
FROM     product
WHERE    prod# < 30
ORDER BY prod#;

UPDATE   profile
SET      vrsn    = 3
WHERE    user_id = USER;
COMMIT;

SELECT   *
FROM     product
WHERE    prod# < 30
ORDER BY prod#;


SELECT   *
FROM     product_his
WHERE    prod# < 30
ORDER BY prod#
        ,cur_ts;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    RUN SOME R.I. TESTS USING PROD_SALES.   ---
--#COMMENT ---                                            ---
--#COMMENT ---    The above INSTEAD OF triggers enforce   ---
--#COMMENT ---    the following R.I. rules between the    ---
--#COMMENT ---    PRODUCT and PROD_SALES tables:          ---
--#COMMENT ---    - Prod# in PROD_SALES must already      ---
--#COMMENT ---      exist in the PRODUCT table.           ---
--#COMMENT ---    - Delete of prod# from PRODUCT also     ---
--#COMMENT ---      cascades down to PROD_SALES.          ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------


UPDATE   profile
SET      vrsn    = 3
WHERE    user_id = USER;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    INSERT WILL FAIL  ---
--#COMMENT ----------------------------
INSERT INTO prod_sales VALUES (-55,2,CURRENT TIMESTAMP,123);
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    INSERT WILL WORK  ---
--#COMMENT ----------------------------
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,123);
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,234);
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,345);
COMMIT;

SELECT   *
FROM     prod_sales
ORDER BY prod#
        ,cust#
        ,sale_ts;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    ORDINARY UPDATE   ---
--#COMMENT ----------------------------
UPDATE prod_sales
SET    sale_ts  = sale_ts + 1 SECOND
WHERE  prod#    = 5
  AND  qty_sold = 234;
COMMIT;

SELECT   *
FROM     prod_sales
ORDER BY prod#
        ,cust#
        ,sale_ts;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    ORDINARY DELETE   ---
--#COMMENT ----------------------------
DELETE
FROM   prod_sales
WHERE  prod#    = 5
  AND  qty_sold = 345;
COMMIT;

SELECT   *
FROM     prod_sales
ORDER BY prod#
        ,cust#
        ,sale_ts;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    UPDATE PRODUCT    ---
--#COMMENT ----------------------------
UPDATE product
SET    prod# = prod# * -1
WHERE  prod# = 5;
COMMIT;

SELECT   *
FROM     prod_sales
ORDER BY prod#
        ,cust#
        ,sale_ts;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    CASCADING DELETE  ---
--#COMMENT ----------------------------
DELETE
FROM   product
WHERE  prod# = -5;
COMMIT;


--#COMMENT
--#COMMENT ----------------------------
--#COMMENT ---    QUERY DATA        ---
--#COMMENT ----------------------------
SELECT   *
FROM     prod_sales
ORDER BY prod#
        ,cust#
        ,sale_ts;

SELECT   *
FROM     prod_sales_his
ORDER BY cur_ts
        ,prod#
        ,cust#
        ,sale_ts;

SELECT   *
FROM     product_his
WHERE    prod# IN (5,-5)
ORDER BY prod#
        ,cur_ts;


--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT ---                                            ---
--#COMMENT ---    DROP OBJECTS.                           ---
--#COMMENT ---                                            ---
--#COMMENT --------------------------------------------------
DROP TRIGGER customer_ins;
DROP TRIGGER customer_upd;
DROP TRIGGER customer_del;

DROP TRIGGER product_ins;
DROP TRIGGER product_upd;
DROP TRIGGER product_del;

DROP TRIGGER prod_sales_ins;
DROP TRIGGER prod_sales_upd;
DROP TRIGGER prod_sales_del;
COMMIT;

DROP VIEW customer;
DROP VIEW product;
DROP VIEW prod_sales;

DROP VIEW customer_vw;
DROP VIEW product_vw;
DROP VIEW prod_sales_vw;

DROP TABLE customer_his;
DROP TABLE product_his;
DROP TABLE prod_sales_his;
COMMIT;

DROP TRIGGER profile_ins;
DROP TRIGGER profile_upd;

DROP TABLE profile;
COMMIT;

DROP TRIGGER version_ins;
DROP TRIGGER version_upd;

DROP TABLE version;
COMMIT;