Mercurial > hg > orthanc

comparison PalanthirServer/PrepareDatabase.sql @ 45:33d67e1ab173

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