-- Oracle

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

DROP TABLE WDS_TT;

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 DATE,
   Trans_Stop DATE
);

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,
   TO_DATE('1988-11-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1989-03-12 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1992-11-15 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1990-02-09 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1991-03-25 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1992-11-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1994-05-18 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1993-12-19 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1996-07-09 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1994-05-18 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1995-07-23 00:00:00','YYYY-MM-DD HH24:MI:SS'));

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,
   TO_DATE('1995-07-23 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));

SELECT * FROM WDS_TT;

DROP TABLE WDS;

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)
);

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 = TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS');

SELECT * FROM WDS;

-- trigger defs

CREATE OR REPLACE TRIGGER INSERT_WDS AFTER INSERT ON WDS FOR EACH ROW
BEGIN
   INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
      Discoverer, Mag_First, Trans_Start, Trans_Stop)
   VALUES (:new.RA_Hour, :new.RA_Min, :new.RA_Sec,
      :new.Dec_Degree, :new.Dec_Minute,
      :new.Discoverer, :new.Mag_First,
      SYSDATE, TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));
END;
/

CREATE TRIGGER DELETE_WDS AFTER DELETE ON WDS FOR EACH ROW
BEGIN
   UPDATE WDS_TT SET Trans_Stop = SYSDATE
   WHERE WDS_TT.Discoverer = :old.Discoverer
      AND WDS_TT.Trans_Stop = TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS');
END;

/

CREATE TRIGGER UPDATE_WDS AFTER UPDATE ON WDS FOR EACH ROW
BEGIN
   UPDATE WDS_TT SET Trans_Stop = SYSDATE
   WHERE WDS_TT.Discoverer = :old.Discoverer
      AND WDS_TT.Trans_Stop = TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS');
   INSERT INTO WDS_TT(RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
      Discoverer, Mag_First, Trans_Start, Trans_Stop)
   VALUES (:new.RA_Hour, :new.RA_Min, :new.RA_Sec,
      :new.Dec_Degree, :new.Dec_Minute,
      :new.Discoverer, :new.Mag_First,
      SYSDATE, TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));
END;

/

-- some updates, to test triggers (not in article)

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);

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);

DELETE FROM WDS WHERE Discoverer = 'ES 1933';

UPDATE WDS SET Dec_Minute = 33 WHERE Discoverer = 'STT 256';

SELECT * FROM WDS;

SELECT * FROM WDS_TT;

-- get the current state from the audit log

SELECT RA_Hour, RA_Min, RA_Sec, Dec_Degree, Dec_Minute,
   Discoverer, Mag_First
FROM WDS_TT
WHERE Trans_Stop = TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS');

-- define a prior state as a view

CREATE OR REPLACE 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 <= TO_DATE('1994-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
   AND TO_DATE('1994-04-01 00:00:00','YYYY-MM-DD HH24:MI:SS') < Trans_Stop;

SELECT * FROM WDS_April_1;

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

SELECT Discoverer FROM WDS WHERE Mag_First <= 11.0;

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

SELECT Discoverer FROM WDS_April_1 WHERE Mag_First <= 11.0;

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

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

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

SELECT W2.Discoverer,
         GREATEST(W1.Trans_Start, W2.Trans_Start) AS Trans_Start,
         LEAST(W1.Trans_Stop, W2.Trans_Stop) AS Trans_Stop
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 GREATEST(W1.Trans_Start, W2.Trans_Start) <
         LEAST(W1.Trans_Stop, W2.Trans_Stop);

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

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

DROP TABLE WDS_TT;

DROP TRIGGER INSERT_WDS;

DROP TRIGGER DELETE_WDS;

DROP TRIGGER UPDATE_WDS;

-- bitemporal tables

DROP TABLE WDS_B;

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

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 12.0,
   TO_DATE('1989-03-12 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1995-11-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1922-05-14', 'YYYY-MM-DD'), TO_DATE('9999-12-31', 'YYYY-MM-DD'));

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 12.0,
   TO_DATE('1995-11-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1922-05-14', 'YYYY-MM-DD'), TO_DATE('1994-10-16', 'YYYY-MM-DD'));

INSERT INTO WDS_B(Discoverer, Mag_First, Trans_Start, Trans_Stop,
   Valid_From, Valid_To)
VALUES ('A  1248', 10.5,
   TO_DATE('1995-11-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('9999-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'),
   TO_DATE('1994-10-16', 'YYYY-MM-DD'), TO_DATE('9999-12-31', 'YYYY-MM-DD'));

SELECT * FROM WDS_B;

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

CREATE OR REPLACE VIEW WDS_VT_AS_OF_Jan_1 AS
SELECT Discoverer, Mag_First, Valid_From, Valid_To
FROM WDS_B
WHERE Trans_Start <= TO_DATE('1994-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')
   AND TO_DATE('1994-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS') < Trans_Stop;

SELECT * FROM WDS_VT_AS_OF_Jan_1;

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

SELECT B1.Discoverer, B1.Trans_Stop AS When_Changed,
  GREATEST(B1.Valid_From, B2.Valid_From) AS Valid_From,
  LEAST(B1.Valid_To, B2.Valid_To) AS Valid_To
FROM WDS_B B1, WDS_B B2
WHERE B1.Discoverer = B2.Discoverer
   AND B1.Trans_Stop = B2.Trans_Start
   AND GREATEST(B1.Valid_From, B2.Valid_From) < TO_DATE('1929-12-31','YYYY-MM-DD')
   AND TO_DATE('1920-01-01','YYYY-MM-DD') < LEAST(B1.Valid_To, B2.Valid_To)
   AND GREATEST(B1.Valid_From, B2.Valid_From) <  LEAST(B1.Valid_To, B2.Valid_To);

DROP TABLE WDS_B;

DROP TABLE WDS;