|
0
|
1 CREATE TABLE GlobalProperties(
|
|
|
2 name TEXT PRIMARY KEY,
|
|
|
3 value TEXT
|
|
|
4 );
|
|
|
5
|
|
82
|
6 CREATE TABLE Resources(
|
|
|
7 uuid TEXT PRIMARY KEY,
|
|
|
8 resourceType INTEGER
|
|
|
9 );
|
|
|
10
|
|
0
|
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,
|
|
77
|
25 dicomSeries TEXT,
|
|
80
|
26 expectedNumberOfInstances INTEGER
|
|
0
|
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,
|
|
77
|
36 distantAet TEXT,
|
|
80
|
37 indexInSeries INTEGER
|
|
0
|
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);
|
|
77
|
65 CREATE INDEX MainDicomTagsGroupElement ON MainDicomTags(tagGroup, tagElement);
|
|
|
66 CREATE INDEX MainDicomTagsValues ON MainDicomTags(value COLLATE BINARY);
|
|
|
67
|
|
0
|
68 CREATE INDEX ChangesIndex ON Changes(uuid);
|
|
|
69
|
|
|
70 CREATE TRIGGER InstanceRemoved
|
|
|
71 AFTER DELETE ON Instances
|
|
|
72 FOR EACH ROW BEGIN
|
|
82
|
73 DELETE FROM Resources WHERE uuid = old.uuid;
|
|
0
|
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
|
|
82
|
84 DELETE FROM Resources WHERE uuid = old.uuid;
|
|
0
|
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
|
|
82
|
93 DELETE FROM Resources WHERE uuid = old.uuid;
|
|
0
|
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
|
|
82
|
102 DELETE FROM Resources WHERE uuid = old.uuid;
|
|
0
|
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;
|
