annotate OrthancServer/PrepareDatabase.sql @ 66:5e195fdc22a3 orthanc-renaming

close renaming branch for palanthir -> orthanc
author Sebastien Jodogne <s.jodogne@gmail.com>
date Sun, 16 Sep 2012 09:51:19 +0200
parents 4bc019d2f969
children b8dfde8d64e8
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE TABLE GlobalProperties(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 name TEXT PRIMARY KEY,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 value TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 CREATE TABLE Patients(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 uuid TEXT PRIMARY KEY,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 dicomPatientId TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 CREATE TABLE Studies(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 uuid TEXT PRIMARY KEY,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 parentPatient TEXT REFERENCES Patients(uuid) ON DELETE CASCADE,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 dicomStudy TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 CREATE TABLE Series(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 uuid TEXT PRIMARY KEY,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 parentStudy TEXT REFERENCES Studies(uuid) ON DELETE CASCADE,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 dicomSeries TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 CREATE TABLE Instances(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 uuid TEXT PRIMARY KEY,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 parentSeries TEXT REFERENCES Series(uuid) ON DELETE CASCADE,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 dicomInstance TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 fileUuid TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 fileSize INTEGER,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 jsonUuid TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 distantAet TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 CREATE TABLE MainDicomTags(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 uuid TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 tagGroup INTEGER,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 tagElement INTEGER,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 value TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 PRIMARY KEY(uuid, tagGroup, tagElement)
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 CREATE TABLE Changes(
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
42 seq INTEGER PRIMARY KEY AUTOINCREMENT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
43 basePath TEXT,
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
44 uuid TEXT
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 );
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
48 CREATE INDEX PatientToStudies ON Studies(parentPatient);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49 CREATE INDEX StudyToSeries ON Series(parentStudy);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 CREATE INDEX SeriesToInstances ON Instances(parentSeries);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52 CREATE INDEX DicomPatientIndex ON Patients(dicomPatientId);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53 CREATE INDEX DicomStudyIndex ON Studies(dicomStudy);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 CREATE INDEX DicomSeriesIndex ON Series(dicomSeries);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 CREATE INDEX DicomInstanceIndex ON Instances(dicomInstance);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(uuid);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 CREATE INDEX ChangesIndex ON Changes(uuid);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
59
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
60 CREATE TRIGGER InstanceRemoved
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
61 AFTER DELETE ON Instances
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62 FOR EACH ROW BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
64 DELETE FROM Changes WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
65 SELECT DeleteFromFileStorage(old.fileUuid);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
66 SELECT DeleteFromFileStorage(old.jsonUuid);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
67 SELECT SignalDeletedLevel(3, old.parentSeries);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
68 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
69
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 CREATE TRIGGER SeriesRemoved
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 AFTER DELETE ON Series
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
72 FOR EACH ROW BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
73 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
74 DELETE FROM Changes WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
75 SELECT SignalDeletedLevel(2, old.parentStudy);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
76 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
77
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
78 CREATE TRIGGER StudyRemoved
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
79 AFTER DELETE ON Studies
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
80 FOR EACH ROW BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
81 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
82 DELETE FROM Changes WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
83 SELECT SignalDeletedLevel(1, old.parentPatient);
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
84 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
85
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
86 CREATE TRIGGER PatientRemoved
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
87 AFTER DELETE ON Patients
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
88 FOR EACH ROW BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
89 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
90 DELETE FROM Changes WHERE uuid = old.uuid;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
91 SELECT SignalDeletedLevel(0, "");
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
92 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
93
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
94
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
95
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
96
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
97 CREATE TRIGGER InstanceRemovedUpwardCleaning
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
98 AFTER DELETE ON Instances
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
99 FOR EACH ROW
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
100 WHEN (SELECT COUNT(*) FROM Instances WHERE parentSeries = old.parentSeries) = 0
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
101 BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
102 SELECT DeleteFromFileStorage("deleting parent series"); -- TODO REMOVE THIS
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
103 DELETE FROM Series WHERE uuid = old.parentSeries;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
104 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
105
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
106 CREATE TRIGGER SeriesRemovedUpwardCleaning
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
107 AFTER DELETE ON Series
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
108 FOR EACH ROW
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
109 WHEN (SELECT COUNT(*) FROM Series WHERE parentStudy = old.parentStudy) = 0
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
110 BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
111 SELECT DeleteFromFileStorage("deleting parent study"); -- TODO REMOVE THIS
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
112 DELETE FROM Studies WHERE uuid = old.parentStudy;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
113 END;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
114
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
115 CREATE TRIGGER StudyRemovedUpwardCleaning
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
116 AFTER DELETE ON Studies
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
117 FOR EACH ROW
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
118 WHEN (SELECT COUNT(*) FROM Studies WHERE parentPatient = old.parentPatient) = 0
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
119 BEGIN
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
120 SELECT DeleteFromFileStorage("deleting parent patient"); -- TODO REMOVE THIS
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
121 DELETE FROM Patients WHERE uuid = old.parentPatient;
3959d33612cc initial commit
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
122 END;