#!/usr/bin/sh -x

echo DB2 version, evaluated on

date

echo Populate, since all subsequent modifications will execute on one day

db2 "DROP TABLE WDS_TT"

db2 "CREATE TABLE WDS_TT(RA_Hour INT, RA_Min INT, RA_Sec INT, Dec_Degree INT, Dec_Minute INT, Discoverer CHAR(7), Mag_First DECIMAL(5,2), Trans_Start TIMESTAMP, Trans_Stop TIMESTAMP)"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (05, 57, 40, 00, 02, 'BU 1190', 6.5, TIMESTAMP('1988-11-08-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 00, 75, 30, 'A  1248', 12.0, TIMESTAMP('1989-03-12-00.00.00'), TIMESTAMP('1992-11-15-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (04, 13, 20, 50, 32, 'CHR  15', 15.5, TIMESTAMP('1990-02-09-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (01, 23, 70, -09, 55, 'HJ 3433', 10.5, TIMESTAMP('1991-03-25-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 09, 75, 30, 'A  1248', 12.0, TIMESTAMP('1992-11-15-00.00.00'), TIMESTAMP('1994-05-18-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (02, 33, 10, -09, 25, 'LDS3402', 10.6, TIMESTAMP('1993-12-19-00.00.00'), TIMESTAMP('1996-07-09-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 09, 75, 30, 'A  1248', 10.5, TIMESTAMP('1994-05-18-00.00.00'), TIMESTAMP('1995-07-23-00.00.00'))"

db2 "INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (00, 00, 08, 75, 30, 'A  1248', 10.5, TIMESTAMP('1995-07-23-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'))"

db2 "SELECT * FROM WDS_TT"

db2 "DROP TABLE WDS"

db2 "CREATE TABLE WDS(RA_Hour INT, RA_Min INT, RA_Sec INT, Dec_Degree INT, Dec_Minute INT, Discoverer CHAR(7), Mag_First DECIMAL(5,2))"

db2 "INSERT INTO WDS(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Stop = TIMESTAMP('9999-12-31-00.00.00')"

db2 "SELECT * FROM WDS"

echo trigger defs

db2 "DROP TRIGGER INSERT_WDS"

db2 "DROP TRIGGER DELETE_WDS"

db2 "DROP TRIGGER UPDATE_WDS"

db2 "CREATE TRIGGER INSERT_WDS AFTER INSERT ON WDS REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute, N.Discoverer, N.Mag_First, CURRENT TIMESTAMP, TIMESTAMP('9999-12-31-00.00.00'))"

db2 "CREATE TRIGGER DELETE_WDS AFTER DELETE ON WDS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL UPDATE WDS_TT SET Trans_Stop = CURRENT TIMESTAMP WHERE WDS_TT.Discoverer = O.Discoverer AND WDS_TT.Trans_Stop = TIMESTAMP('9999-12-31-00.00.00')"

db2 "CREATE TRIGGER UPDATE_WDS AFTER UPDATE ON WDS REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC UPDATE WDS_TT SET Trans_Stop = CURRENT TIMESTAMP WHERE WDS_TT.Discoverer = O.Discoverer AND WDS_TT.Trans_Stop = TIMESTAMP('9999-12-31-00.00.00'); INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First, Trans_Start, Trans_Stop) VALUES (N.RA_Hour, N.RA_Min, N.RA_Sec, N.Dec_Degree, N.Dec_Minute, N.Discoverer, N.Mag_First, CURRENT TIMESTAMP, TIMESTAMP('9999-12-31-00.00.00')); END"

echo "some updates, to test triggers (not in article)"

db2 "INSERT INTO WDS (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) VALUES (00, 02, 00, 60, 57, 'ES 1933', 2.4)"

db2 "INSERT INTO WDS (RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First) VALUES (00, 08, 00, 31, 23, 'STT 256', 7.9)"

db2 "DELETE FROM WDS WHERE Discoverer = 'ES 1933'"

db2 "UPDATE WDS SET Dec_Minute = 33 WHERE Discoverer = 'STT 256'"

db2 "SELECT * FROM WDS"

db2 "SELECT * FROM WDS_TT"

echo get the current state from the audit log

db2 "SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Stop = TIMESTAMP('9999-12-31-00.00.00')"

echo define a prior state as a view

db2 "CREATE VIEW WDS_April_1 AS SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute, Discoverer, Mag_First FROM WDS_TT WHERE Trans_Start <= TIMESTAMP('1994-04-01-00.00.00') AND TIMESTAMP('1994-04-01-00.00.00') < Trans_Stop"

db2 "SELECT * FROM WDS_April_1"

echo "Which stars were of magnitude 11 or brighter, as currently known?"

db2 "SELECT Discoverer FROM WDS WHERE Mag_First <= 11.0"

echo Asking the same question, as best known on April 1, 1994

db2 "SELECT Discoverer FROM WDS_April_1 WHERE Mag_First <= 11.0"

echo "When was it recorded that A1248 had a magnitude other than 10.5?"

db2 "SELECT Mag_First, Trans_Start, Trans_Stop FROM WDS_TT WHERE Discoverer = 'A  1248' AND Mag_First <> 10.5"

echo "When was it recorded that a star had a magnitude equal to that of A1248?"

db2 "SELECT W2.Discoverer, (CASE WHEN W1.Trans_Start > W2.Trans_Start THEN W1.Trans_Start ELSE W2.Trans_Start END), (CASE WHEN W1.Trans_Stop < W2.Trans_Stop THEN W1.Trans_Stop ELSE W2.Trans_Stop END) FROM WDS_TT W1, WDS_TT W2 WHERE W1.Discoverer = 'A  1248' AND W2.Discoverer <> W1.Discoverer AND W1.Mag_First = W2.Mag_First AND (CASE WHEN W1.Trans_Start > W2.Trans_Start THEN W1.Trans_Start ELSE W2.Trans_Start END) < (CASE WHEN W1.Trans_Stop < W2.Trans_Stop THEN W1.Trans_Stop ELSE W2.Trans_Stop END)"

echo "When was the RA_Sec position of a double star corrected?"

db2 "SELECT W1.Discoverer, W1.RA_Sec AS Old_Value, W2.RA_Sec AS New_Value, W1.Trans_Stop AS When_Changed FROM WDS_TT AS W1, WDS_TT AS W2 WHERE W1.Discoverer = W2.Discoverer AND W1.Trans_Stop = W2.Trans_Start AND W1.RA_Sec <> W2.RA_Sec"

db2 "DROP TABLE WDS_TT"

db2 "DROP TRIGGER INSERT_WDS"

db2 "DROP TRIGGER DELETE_WDS"

db2 "DROP TRIGGER UPDATE_WDS"

echo bitemporal tables

db2 "DROP TABLE WDS_B"

db2 "CREATE TABLE WDS_B(Discoverer CHAR(7), Mag_First DECIMAL(5,2), Trans_Start TIMESTAMP, Trans_Stop TIMESTAMP, Valid_From DATE, Valid_To DATE)"

db2 "INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A  1248', 12.0, TIMESTAMP('1989-03-12-00.00.00'), TIMESTAMP('1995-11-15-00.00.00'), DATE('1922-05-14'), DATE('9999-12-31'))"

db2 "INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A  1248', 12.0, TIMESTAMP('1995-11-15-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'), DATE('1922-05-14'), DATE('1994-10-16'))"

db2 "INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop, Valid_From, Valid_To) VALUES ('A  1248', 10.5, TIMESTAMP('1995-11-15-00.00.00'), TIMESTAMP('9999-12-31-00.00.00'), DATE('1994-10-16'), DATE('9999-12-31'))"

db2 "SELECT * FROM WDS_B"

echo "What was the history recorded as of January 1, 1994?"

db2 "CREATE VIEW WDS_VT_AS_OF_Jan_1 AS SELECT Discoverer, Mag_First, Valid_From, Valid_To FROM WDS_B WHERE Trans_Start <= TIMESTAMP('1994-01-01-00.00.00') AND TIMESTAMP('1994-01-01-00.00.00') < Trans_Stop"

db2 "SELECT * FROM WDS_VT_AS_OF_Jan_1"

echo "List the corrections made on plates taken in the 1920's."

db2 "SELECT B1.Discoverer, B1.Trans_Stop AS When_Changed, (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) AS Valid_From, (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END) AS Valid_To FROM WDS_B AS B1, WDS_B AS B2 WHERE B1.Discoverer = B2.Discoverer AND B1.Trans_Stop = B2.Trans_Start AND (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) < DATE('1929-12-31') AND DATE('1920-01-01') < (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END) AND (CASE WHEN B1.Valid_From > B2.Valid_From THEN B1.Valid_From ELSE B2.Valid_From END) < (CASE WHEN B1.Valid_To < B2.Valid_To THEN B1.Valid_To ELSE B2.Valid_To END)"

db2 "DROP TABLE WDS_B"

db2 "DROP TABLE WDS"