|
0
|
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;
|
