|
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,
|
|
77
|
20 dicomSeries TEXT,
|
|
|
21 numberOfInstances INTEGER
|
|
0
|
22 );
|
|
|
23
|
|
|
24 CREATE TABLE Instances(
|
|
|
25 uuid TEXT PRIMARY KEY,
|
|
|
26 parentSeries TEXT REFERENCES Series(uuid) ON DELETE CASCADE,
|
|
|
27 dicomInstance TEXT,
|
|
|
28 fileUuid TEXT,
|
|
|
29 fileSize INTEGER,
|
|
|
30 jsonUuid TEXT,
|
|
77
|
31 distantAet TEXT,
|
|
|
32 instanceIndex INTEGER
|
|
0
|
33 );
|
|
|
34
|
|
|
35 CREATE TABLE MainDicomTags(
|
|
|
36 uuid TEXT,
|
|
|
37 tagGroup INTEGER,
|
|
|
38 tagElement INTEGER,
|
|
|
39 value TEXT,
|
|
|
40 PRIMARY KEY(uuid, tagGroup, tagElement)
|
|
|
41 );
|
|
|
42
|
|
|
43 CREATE TABLE Changes(
|
|
|
44 seq INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
|
45 basePath TEXT,
|
|
|
46 uuid TEXT
|
|
|
47 );
|
|
|
48
|
|
|
49
|
|
|
50 CREATE INDEX PatientToStudies ON Studies(parentPatient);
|
|
|
51 CREATE INDEX StudyToSeries ON Series(parentStudy);
|
|
|
52 CREATE INDEX SeriesToInstances ON Instances(parentSeries);
|
|
|
53
|
|
|
54 CREATE INDEX DicomPatientIndex ON Patients(dicomPatientId);
|
|
|
55 CREATE INDEX DicomStudyIndex ON Studies(dicomStudy);
|
|
|
56 CREATE INDEX DicomSeriesIndex ON Series(dicomSeries);
|
|
|
57 CREATE INDEX DicomInstanceIndex ON Instances(dicomInstance);
|
|
|
58
|
|
|
59 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(uuid);
|
|
77
|
60 CREATE INDEX MainDicomTagsGroupElement ON MainDicomTags(tagGroup, tagElement);
|
|
|
61 CREATE INDEX MainDicomTagsValues ON MainDicomTags(value COLLATE BINARY);
|
|
|
62
|
|
0
|
63 CREATE INDEX ChangesIndex ON Changes(uuid);
|
|
|
64
|
|
|
65 CREATE TRIGGER InstanceRemoved
|
|
|
66 AFTER DELETE ON Instances
|
|
|
67 FOR EACH ROW BEGIN
|
|
|
68 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
|
|
|
69 DELETE FROM Changes WHERE uuid = old.uuid;
|
|
|
70 SELECT DeleteFromFileStorage(old.fileUuid);
|
|
|
71 SELECT DeleteFromFileStorage(old.jsonUuid);
|
|
|
72 SELECT SignalDeletedLevel(3, old.parentSeries);
|
|
|
73 END;
|
|
|
74
|
|
|
75 CREATE TRIGGER SeriesRemoved
|
|
|
76 AFTER DELETE ON Series
|
|
|
77 FOR EACH ROW BEGIN
|
|
|
78 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
|
|
|
79 DELETE FROM Changes WHERE uuid = old.uuid;
|
|
|
80 SELECT SignalDeletedLevel(2, old.parentStudy);
|
|
|
81 END;
|
|
|
82
|
|
|
83 CREATE TRIGGER StudyRemoved
|
|
|
84 AFTER DELETE ON Studies
|
|
|
85 FOR EACH ROW BEGIN
|
|
|
86 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
|
|
|
87 DELETE FROM Changes WHERE uuid = old.uuid;
|
|
|
88 SELECT SignalDeletedLevel(1, old.parentPatient);
|
|
|
89 END;
|
|
|
90
|
|
|
91 CREATE TRIGGER PatientRemoved
|
|
|
92 AFTER DELETE ON Patients
|
|
|
93 FOR EACH ROW BEGIN
|
|
|
94 DELETE FROM MainDicomTags WHERE uuid = old.uuid;
|
|
|
95 DELETE FROM Changes WHERE uuid = old.uuid;
|
|
|
96 SELECT SignalDeletedLevel(0, "");
|
|
|
97 END;
|
|
|
98
|
|
|
99
|
|
|
100
|
|
|
101
|
|
|
102 CREATE TRIGGER InstanceRemovedUpwardCleaning
|
|
|
103 AFTER DELETE ON Instances
|
|
|
104 FOR EACH ROW
|
|
|
105 WHEN (SELECT COUNT(*) FROM Instances WHERE parentSeries = old.parentSeries) = 0
|
|
|
106 BEGIN
|
|
|
107 SELECT DeleteFromFileStorage("deleting parent series"); -- TODO REMOVE THIS
|
|
|
108 DELETE FROM Series WHERE uuid = old.parentSeries;
|
|
|
109 END;
|
|
|
110
|
|
|
111 CREATE TRIGGER SeriesRemovedUpwardCleaning
|
|
|
112 AFTER DELETE ON Series
|
|
|
113 FOR EACH ROW
|
|
|
114 WHEN (SELECT COUNT(*) FROM Series WHERE parentStudy = old.parentStudy) = 0
|
|
|
115 BEGIN
|
|
|
116 SELECT DeleteFromFileStorage("deleting parent study"); -- TODO REMOVE THIS
|
|
|
117 DELETE FROM Studies WHERE uuid = old.parentStudy;
|
|
|
118 END;
|
|
|
119
|
|
|
120 CREATE TRIGGER StudyRemovedUpwardCleaning
|
|
|
121 AFTER DELETE ON Studies
|
|
|
122 FOR EACH ROW
|
|
|
123 WHEN (SELECT COUNT(*) FROM Studies WHERE parentPatient = old.parentPatient) = 0
|
|
|
124 BEGIN
|
|
|
125 SELECT DeleteFromFileStorage("deleting parent patient"); -- TODO REMOVE THIS
|
|
|
126 DELETE FROM Patients WHERE uuid = old.parentPatient;
|
|
|
127 END;
|
