Mercurial > hg > orthanc

comparison OrthancServer/PrepareDatabase-v1.sql @ 202:1650557bd81a

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