Mercurial > hg > orthanc
comparison OrthancServer/PrepareDatabase.sql @ 57:4bc019d2f969 orthanc-renaming
renaming
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Sun, 16 Sep 2012 09:22:48 +0200 |
parents | PalanthirServer/PrepareDatabase.sql@33d67e1ab173 |
children | b8dfde8d64e8 |
comparison
equal
deleted
inserted
replaced
56:088c4f23e2c8 | 57:4bc019d2f969 |
---|---|
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; |