Trouble ticket is here: https://www.sqlite.org/src/tktview/34cd55d68e
On 5/19/15, Roman Fleysher <roman.fleysher at einstein.yu.edu> wrote:
> -- 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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
drh at sqlite.org
