#csh

# DB2

echo Cleanup

db2 "DROP TABLE NICUStatus"

db2 "DROP TRIGGER seq_duplicates"

db2 "CREATE TABLE NICUStatus (Name CHAR(15) NOT NULL, Status CHAR(8) NOT NULL, from_date DATE,to_date DATE, PRIMARY KEY (Name, Status))"

# DB2 does not have UNIQUE constraints, so we have to use PRIMARY KEY,
# which requires the columns to all be NOT NULL

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'serious', DATE('1997-11-19'), DATE('1997-11-21'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'serious', DATE('1997-11-19'), DATE('1997-11-27'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Natalie Sue', 'serious', DATE('1997-11-19'), DATE('1997-11-25'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kelsey Ann', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Brandon James', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Nathan Roy', 'serious', DATE('1997-11-19'), DATE('1997-11-28'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'critical', DATE('1997-11-19'), DATE('1997-11-20'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'serious', DATE('1997-11-20'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'fair', DATE('1997-11-21'), DATE('1998-01-03'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-11-27'), DATE('1998-01-11'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

# Prevent nonsequenced duplicates in NICUStatus

db2 "DROP TABLE NICUStatus"

db2 "CREATE TABLE NICUStatus (Name CHAR(15) NOT NULL,Status CHAR(8) NOT NULL,from_date DATE NOT NULL,to_date DATE NOT NULL, PRIMARY KEY (Name, Status, from_date, to_date))"

### DB2 does not have UNIQUE constraints, so we have to use PRIMARY KEY,
### which requires the columns to all be NOT NULL

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'serious', DATE('1997-11-19'), DATE('1997-11-21'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'serious', DATE('1997-11-19'), DATE('1997-11-27'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Natalie Sue', 'serious', DATE('1997-11-19'), DATE('1997-11-25'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kelsey Ann', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Brandon James', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Nathan Roy', 'serious', DATE('1997-11-19'), DATE('1997-11-28'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'critical', DATE('1997-11-19'), DATE('1997-11-20'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'serious', DATE('1997-11-20'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'fair', DATE('1997-11-21'), DATE('1998-01-03'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-11-27'), DATE('1998-01-11'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

# Prevent current duplicates in NICUStatus

db2 "DROP TABLE NICUStatus"

echo Check condition cannot contain subqueries

db2 "CREATE TABLE NICUStatus (Name CHAR(15),Status CHAR(8),from_date DATE,to_date DATE,CHECK (NOT EXISTS (SELECT N1.SSN FROM NICUStatus AS N1 WHERE 1 < (SELECT COUNT(Name) FROM NICUStatus AS N2 WHERE N1.Name = N2.Name AND N1.Status = N2.Status AND N1.from_date <= CURRENT_DATE AND CURRENT_DATE < N1.to_date AND N2.from_date <= CURRENT_DATE AND CURRENT_DATE < N2.to_date))))"

# Prevent current duplicates in NICUStatus, assuming no future data

# db2 "DROP TABLE NICUStatus"

db2 "CREATE TABLE NICUStatus (Name CHAR(15) NOT NULL,Status CHAR(8) NOT NULL,from_date DATE,to_date DATE NOT NULL,PRIMARY KEY (Name, Status, to_date))"

### must use PRIMARY KEY

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'serious', DATE('1997-11-19'), DATE('1997-11-21'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'serious', DATE('1997-11-19'), DATE('1997-11-27'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Natalie Sue', 'serious', DATE('1997-11-19'), DATE('1997-11-25'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kelsey Ann', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Brandon James', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Nathan Roy', 'serious', DATE('1997-11-19'), DATE('1997-11-28'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'critical', DATE('1997-11-19'), DATE('1997-11-20'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'serious', DATE('1997-11-20'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'fair', DATE('1997-11-21'), DATE('1998-01-03'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-11-27'), DATE('1998-01-11'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

# Prevent sequenced duplicates in NICUStatus

db2 "DROP TABLE NICUStatus"

echo Check condition cannot contain subqueries

db2 "CREATE TABLE NICUStatus (Name CHAR(15),Status CHAR(8),from_date DATE,to_date DATE,CHECK (NOT EXISTS (SELECT N1.Name FROM NICUStatus AS N1 WHERE 1 < (SELECT COUNT(Name) FROM NICUStatus AS N2 WHERE N1.Name = N2.Name AND N1.Status = N2.Status AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date))))"

# db2 "DROP TABLE NICUStatus"

db2 "CREATE TABLE NICUStatus (Name CHAR(15),Status CHAR(8),from_date DATE,to_date DATE)"

echo Check condition cannot contain subqueries

db2 "ALTER TABLE NICUStatus ADD CHECK (NOT EXISTS (SELECT N1.Name FROM NICUStatus AS N1 WHERE 1 < (SELECT COUNT(Name) FROM NICUStatus AS N2 WHERE N1.Name = N2.Name AND N1.Status = N2.Status AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date)))"

# Prevent sequenced duplicates in NICUStatus in DB2

db2 "DROP TABLE NICUStatus"

db2 "CREATE TABLE NICUStatus (Name CHAR(15), Status CHAR(8), from_date DATE,to_date DATE)"

### we'll use a trigger here

db2 "CREATE TRIGGER seq_duplicates AFTER INSERT ON NICUStatus FOR EACH ROW MODE DB2SQL WHEN (EXISTS (SELECT N1.Name FROM NICUStatus AS N1 WHERE 1 < (SELECT COUNT(Name) FROM NICUStatus AS N2 WHERE N1.Name = N2.Name AND N1.Status = N2.Status AND N1.from_date < N2.to_date AND N2.from_date < N1.to_date))) SIGNAL SQLSTATE '75000' ('Name and Status must be sequenced unique' )"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'serious', DATE('1997-11-19'), DATE('1997-11-21'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'serious', DATE('1997-11-19'), DATE('1997-11-27'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Natalie Sue', 'serious', DATE('1997-11-19'), DATE('1997-11-25'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kelsey Ann', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Brandon James', 'serious', DATE('1997-11-19'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Nathan Roy', 'serious', DATE('1997-11-19'), DATE('1997-11-28'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'critical', DATE('1997-11-19'), DATE('1997-11-20'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Joel Steven', 'serious', DATE('1997-11-20'), DATE('1997-11-26'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Kenneth Robert', 'fair', DATE('1997-11-21'), DATE('1998-01-03'))"

db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-11-27'), DATE('1998-01-11'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"

echo should cause an error
db2 "INSERT INTO NICUStatus (Name, Status, from_date, to_date) VALUES ('Alexis May', 'fair', DATE('1997-12-02'), DATE('9999-12-31'))"