-- Dear SQLiters,
-- Here is schema first, table is below,
-- followed by offending statement.
-- you can copy and paste the entire body
-- my comments are SQL compatible
-----------------------------------------
-- STEP 1 --
-- create gender and handedness tables to fix possible values
-- then create subject table
-----------------------------------------
CREATE TABLE gender(
gender TEXT PRIMARY KEY NOT NULL,
description TEXT
);
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male
gender');
INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female
gender');
CREATE TABLE handedness(
handedness TEXT PRIMARY KEY NOT NULL,
description TEXT
);
-- force dob yyyy-mm-dd or NULL
CREATE TABLE subject(
subjectID INT PRIMARY KEY NOT NULL,
dob TEXT CHECK (dob IS date(dob)),
gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
race TEXT,
handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
);
-- STEP 2 --
CREATE TABLE MRIMetric(
metricName TEXT PRIMARY KEY NOT NULL,
description TEXT,
units TEXT
);
-- STEP 3 --
-- examType.examType = name of the table where
-- to find exam specifics
CREATE TABLE ExamType(
examType TEXT PRIMARY KEY NOT NULL,
description TEXT
);
INSERT OR IGNORE INTO ExamType (examType, description) VALUES ('MRIExam', 'any
MRI exam');
INSERT OR IGNORE INTO ExamType (examType, description) VALUES
('demographicExam', 'view on subject table');
INSERT OR IGNORE INTO ExamType (examType) VALUES ('educationExam');
-- assume date yyyy-mm-dd
CREATE TABLE Exam(
examID TEXT PRIMARY KEY NOT NULL,
subjectID INTEGER NOT NULL REFERENCES subject(subjectID) ON UPDATE CASCADE
ON DELETE CASCADE,
examType TEXT NOT NULL REFERENCES examType(examType) ON UPDATE CASCADE,
comments TEXT,
date TEXT CHECK (date IS date(date)),
age INTEGER
);
-----------------------------------------
-- create triggers to compute age on various updates and inserts
-----------------------------------------
-- automatically compute age at the time of exam when exam is inserted
CREATE TRIGGER ageCalculatorOnExamInsert AFTER INSERT ON Exam FOR EACH ROW BEGIN
UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
+ (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
WHERE Exam.examID = NEW.examID;
END;
-- automatically compute age at the time of exam when exam is updated
CREATE TRIGGER ageCalculatorOnExamUpdate AFTER UPDATE ON Exam FOR EACH ROW BEGIN
UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
strftime('%Y', NEW.date) - strftime('%Y', subject.dob) -1
+ (strftime('%m-%d', NEW.date) >= strftime('%m-%d', subject.dob)),
NEW.age)
FROM subject
WHERE subject.subjectID = NEW.subjectID
) -- this ends select statement that joins exam and subject tables
WHERE Exam.examID = NEW.examID;
END;
-- automatically compute age at the time of exam when subject.DOB is updated
CREATE TRIGGER ageCalculatorOnSubjectUpdate AFTER UPDATE ON subject FOR EACH
ROW BEGIN
UPDATE OR IGNORE Exam SET age = (
SELECT coalesce(
strftime('%Y', e.date) - strftime('%Y', NEW.dob) -1
+ (strftime('%m-%d', e.date) >= strftime('%m-%d', NEW.dob)),
e.age)
FROM Exam e
WHERE Exam.examID = e.examID
) -- this ends select statement that joins exam and subject tables
WHERE Exam.subjectID = NEW.subjectID;
END;
-- STEP 4 --
CREATE TABLE MRIExam(
examID TEXT NOT NULL REFERENCES Exam(examID) ON UPDATE CASCADE ON
DELETE CASCADE,
ResearchName TEXT,
MRN INTEGER,
pipeline TEXT,
DICOMFolder TEXT,
PRIMARY KEY (examID)
);
CREATE TABLE MRIExamAccession(
examID TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON
DELETE CASCADE,
accession INTEGER,
UNIQUE (examID, accession)
);
CREATE INDEX MRIExamAccessionIndex ON MRIExamAccession(examID);
-- STEP 5 --
CREATE TABLE badMRIMetric(
examID TEXT NOT NULL REFERENCES MRIExam(examID) ON UPDATE CASCADE ON
DELETE CASCADE,
metricName TEXT NOT NULL REFERENCES MRIMetric(metricName) ON UPDATE CASCADE,
reason TEXT,
PRIMARY KEY (examID, metricName)
);
-----------------------------------------
-- STEP 6 --
-- create wave table
-----------------------------------------
CREATE TABLE aboutWave(
waveID TEXT PRIMARY KEY NOT NULL,
description TEXT
);
INSERT OR IGNORE INTO aboutWave (waveID, description) VALUES ('time1', 'first
scan');
-- force uniqueness of subject+examType+wave.
-- can not have several instances of one exam type within a wave
-- given examID can be assigned to several waves
CREATE TABLE wave(
subjectID INT NOT NULL REFERENCES subject(subjectID) ON UPDATE CASCADE ON
DELETE CASCADE,
examType TEXT NOT NULL REFERENCES ExamType(examType) ON UPDATE CASCADE,
waveID TEXT NOT NULL REFERENCES aboutWave(waveID) ON UPDATE CASCADE,
examID TEXT NOT NULL REFERENCES Exam(examID) ON UPDATE CASCADE ON
DELETE CASCADE,
PRIMARY KEY (subjectID, examType, waveID)
);
CREATE INDEX waveIndex ON wave(examID);
-----------------------------------------
-- STEP 7 --
-- create view on subject table as demographicExam
-- use subjectID as examID
-----------------------------------------
CREATE VIEW IF NOT EXISTS demographicExam AS
SELECT subjectID AS examID,
dob,
gender,
race,
handedness
FROM subject;
-- automatically add demographic exam to wave and Exam tables when subject is
inserted
-- use subjectID as examID
CREATE TRIGGER demographicInsert AFTER INSERT ON subject FOR EACH ROW BEGIN
INSERT INTO Exam (subjectID, examID, examType) VALUES (NEW.subjectID,
NEW.subjectID, 'demographicExam');
INSERT INTO wave (subjectID, examType, waveID, examID) VALUES (NEW.subjectID,
'demographicExam', 'time1', NEW.subjectID);
END;
-- STEP 8 --
CREATE TABLE educationExam(
examID TEXT NOT NULL REFERENCES Exam(examID) ON UPDATE CASCADE ON DELETE
CASCADE,
education INTEGER,
PRIMARY KEY (examID)
);
-----------------------------------------
---------- end of schema
-----------------------------------------
CREATE TABLE demographics(
"subjectID" TEXT,
"DOB" TEXT,
"Age" TEXT,
"Education" TEXT,
"Gender" TEXT
);
INSERT INTO "demographics" VALUES('0001','1992-05-04','23','16','Female');
INSERT INTO "demographics" VALUES('0002','1992-07-02','22','17','Female');
INSERT INTO "demographics" VALUES('0003','1986-01-02','29','13','Female');
INSERT INTO "demographics" VALUES('0004','1989-04-05','26','12','Female');
INSERT INTO "demographics" VALUES('0005','1989-10-15','25','18','Male');
INSERT INTO "demographics" VALUES('0006','1989-08-14','25','19','Female');
INSERT INTO "demographics" VALUES('0007','1990-04-02','25','14','Female');
INSERT INTO "demographics" VALUES('0008','1982-03-17','33','13','Male');
INSERT INTO "demographics" VALUES('0009','1966-03-03','49','16','Male');
INSERT INTO "demographics" VALUES('0010','1988-09-14','26','13','Female');
INSERT INTO "demographics" VALUES('0011','1987-11-22','27','14','Female');
INSERT INTO "demographics" VALUES('0012','1969-02-12','46','20','Male');
INSERT INTO "demographics" VALUES('0013','1988-05-03','27','19','Male');
INSERT INTO "demographics" VALUES('0014','1966-08-24','48','16','Female');
INSERT INTO "demographics" VALUES('0015','1970-04-20','45','17','Female');
INSERT INTO "demographics" VALUES('0016','1991-08-03','23','17','Male');
INSERT INTO "demographics" VALUES('0017','1989-12-15','25','18','Male');
INSERT INTO "demographics" VALUES('0018','1991-12-18','23','16','Male');
INSERT INTO "demographics" VALUES('0019','1989-04-24','26','20','Male');
INSERT INTO "demographics" VALUES('0020','1965-10-15','49','20','Male');
INSERT INTO "demographics" VALUES('0021','1980-01-03','35','15','Female');
INSERT INTO "demographics" VALUES('0022','1979-01-05','36','11','Male');
INSERT INTO "demographics" VALUES('0023','1990-10-28','24','16','Male');
INSERT INTO "demographics" VALUES('0024','1983-10-13','31','14','Male');
INSERT INTO "demographics" VALUES('0025','1991-07-10','23','14','Male');
INSERT INTO "demographics" VALUES('0026','1991-04-28','24','18','Male');
INSERT INTO "demographics" VALUES('0027','1988-07-05','26','18.5','Male');
INSERT INTO "demographics" VALUES('0028','1987-04-22','28','21','Male');
INSERT INTO "demographics" VALUES('0029','1988-08-04','26','18','Male');
INSERT INTO "demographics" VALUES('0030','1967-12-13','47','16','Male');
INSERT INTO "demographics" VALUES('0031','1983-11-05','31','16','Male');
-- Offending statement:
PRAGMA foreign_keys=ON;
INSERT OR IGNORE INTO subject(subjectID, dob, gender)
SELECT subjectID, dob, gender FROM demographics;
PRAGMA integrity_check;
-- Roman
