--Execute the third reconstruction algorithm
--Connect to db
CONNECT TO cs560

--Drop the triggers and tables if they exist
DROP TRIGGER Delete_PROJECTIONS
DROP TRIGGER Update_PROJECTIONS
DROP TRIGGER Insert_PROJECTIONS
DROP TABLE PROJECTIONS
DROP TABLE P_AUDIT

--Create the tables
CREATE TABLE PROJECTIONS (PROJECTION_ID INT, PROJECTION_NAME INT, PROJECTION_TYPE INT, SPHEROID_CODE INT, PROJECTION_UOM INT, ZONE_CODE INT) 

CREATE TABLE P_AUDIT (PROJECTION_ID INT NOT NULL, PROJECTION_NAME INT, PROJECTION_TYPE INT, SPHEROID_CODE INT, PROJECTION_UOM INT, ZONE_CODE INT, When_Changed TIMESTAMP NOT NULL, Operation CHAR(1), PRIMARY KEY (PROJECTION_ID, When_Changed))

--Create the triggers
CREATE TRIGGER Delete_PROJECTIONS AFTER DELETE ON PROJECTIONS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO P_Audit VALUES (O.PROJECTION_ID, O.PROJECTION_NAME, O.PROJECTION_TYPE, O.SPHEROID_CODE, O.PROJECTION_UOM, O.ZONE_CODE, CURRENT TIMESTAMP, 'D'); END

CREATE TRIGGER Update_PROJECTIONS AFTER UPDATE ON PROJECTIONS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO P_Audit VALUES (O.PROJECTION_ID, O.PROJECTION_NAME, O.PROJECTION_TYPE, O.SPHEROID_CODE, O.PROJECTION_UOM, O.ZONE_CODE, CURRENT TIMESTAMP, 'U'); END

CREATE TRIGGER Insert_PROJECTIONS AFTER INSERT ON PROJECTIONS REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO P_Audit VALUES (N.PROJECTION_ID, N.PROJECTION_NAME, N.PROJECTION_TYPE, N.SPHEROID_CODE, N.PROJECTION_UOM, N.ZONE_CODE, CURRENT TIMESTAMP, 'I'); END

--Execute the Transactions corresponding to Section 9.4
--Trans 1
INSERT INTO PROJECTIONS VALUES (1, 1, 12, 1, 1, 1)
--Trans 2
INSERT INTO PROJECTIONS VALUES (2, 1, 10, 1, 1, 1)
--Trans 3
INSERT INTO PROJECTIONS VALUES (3, 1, 15, 1, 1, 1)
--Trans 4
INSERT INTO PROJECTIONS VALUES (4, 1, 17, 1, 1, 1)
--Trans 5
INSERT INTO PROJECTIONS VALUES (5, 1, 18, 1, 1, 1)

--Change the dates to reflect Jan 1
UPDATE P_AUDIT SET When_Changed = '1996-01-01-00.00.00.000000'

--For each of the following, 
--change the date in p_audit to reflect date in book
--Trans 6
DELETE FROM PROJECTIONS WHERE PROJECTION_ID = 5
UPDATE P_AUDIT SET When_Changed = '1996-02-03-00.00.00.000000' WHERE PROJECTION_TYPE = 18 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 18)

--Trans 7
UPDATE PROJECTIONS SET PROJECTION_TYPE = 13 WHERE PROJECTION_ID = 2
UPDATE P_AUDIT SET When_Changed = '1996-03-20-00.00.00.000000' WHERE PROJECTION_TYPE = 10 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 10)

--Trans 8
INSERT INTO PROJECTIONS VALUES (5, 1, 19, 1, 1, 1)
UPDATE P_AUDIT SET When_Changed = '1996-04-09-00.00.00.000000' WHERE PROJECTION_TYPE = 19 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 19)

--Trans 9
UPDATE PROJECTIONS SET PROJECTION_TYPE = 11 WHERE PROJECTION_ID = 3
UPDATE P_AUDIT SET When_Changed = '1996-05-28-00.00.00.000000' WHERE PROJECTION_TYPE = 15 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 15)

--Trans 10
UPDATE PROJECTIONS SET PROJECTION_TYPE = 14 WHERE PROJECTION_ID = 2
UPDATE P_AUDIT SET When_Changed = '1996-06-17-00.00.00.000000' WHERE PROJECTION_TYPE = 13 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 13)

--Trans 11
DELETE FROM PROJECTIONS WHERE PROJECTION_ID = 4
UPDATE P_AUDIT SET When_Changed = '1996-07-12-00.00.00.000000' WHERE PROJECTION_TYPE = 17 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 17)

--Trans 12
INSERT INTO PROJECTIONS VALUES (4, 1, 18, 1, 1, 1)
UPDATE P_AUDIT SET When_Changed = '1996-08-30-00.00.00.000000' WHERE PROJECTION_TYPE = 18 AND When_Changed = (SELECT MAX(When_Changed) from P_Audit WHERE PROJECTION_TYPE = 18)


--Projections Table
SELECT PROJECTION_ID, PROJECTION_TYPE FROM PROJECTIONS

--P_Audit Table
SELECT PROJECTION_ID, PROJECTION_TYPE, DATE(When_Changed) AS When_Changed, OPERATION FROM P_AUDIT

--Reconstructed Projections
--Code Fragment 9.10
SELECT PROJECTION_ID, PROJECTION_TYPE FROM PROJECTIONS AS P WHERE '04/01/1996' > (SELECT MAX(DATE(When_Changed)) FROM P_Audit AS A WHERE P.PROJECTION_ID = A.PROJECTION_ID) UNION SELECT A2.PROJECTION_ID, A2.PROJECTION_TYPE FROM P_Audit AS A, P_Audit AS A2 WHERE DATE(A.When_Changed) < DATE(A2.When_Changed) AND A.PROJECTION_ID = A2.PROJECTION_ID AND NOT EXISTS (SELECT * FROM P_Audit AS A3 WHERE A.PROJECTION_ID = A3.PROJECTION_ID AND DATE(A.When_Changed) < DATE(A3.When_Changed) AND DATE(A3.When_Changed) < DATE(A2.When_Changed)) AND DATE(A.When_Changed) < '04/01/1996' AND '04/01/1996' < DATE(A2.When_Changed) AND A.Operation <> 'D'

QUIT

