|
0
|
1 CREATE TABLE GlobalProperties(
|
|
|
2 property INTEGER PRIMARY KEY,
|
|
|
3 value TEXT
|
|
|
4 );
|
|
|
5
|
|
|
6 CREATE TABLE Resources(
|
|
|
7 internalId BIGSERIAL NOT NULL PRIMARY KEY,
|
|
|
8 resourceType INTEGER NOT NULL,
|
|
|
9 publicId VARCHAR(64) NOT NULL,
|
|
|
10 parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
|
11 );
|
|
|
12
|
|
|
13 CREATE TABLE MainDicomTags(
|
|
|
14 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
|
15 tagGroup INTEGER,
|
|
|
16 tagElement INTEGER,
|
|
|
17 value TEXT,
|
|
|
18 PRIMARY KEY(id, tagGroup, tagElement)
|
|
|
19 );
|
|
|
20
|
|
|
21 CREATE TABLE DicomIdentifiers(
|
|
|
22 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
|
23 tagGroup INTEGER,
|
|
|
24 tagElement INTEGER,
|
|
|
25 value TEXT,
|
|
|
26 PRIMARY KEY(id, tagGroup, tagElement)
|
|
|
27 );
|
|
|
28
|
|
|
29 CREATE TABLE Metadata(
|
|
|
30 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
|
31 type INTEGER NOT NULL,
|
|
|
32 value TEXT,
|
|
|
33 PRIMARY KEY(id, type)
|
|
|
34 );
|
|
|
35
|
|
|
36 CREATE TABLE AttachedFiles(
|
|
|
37 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
|
38 fileType INTEGER,
|
|
|
39 uuid VARCHAR(64) NOT NULL,
|
|
|
40 compressedSize BIGINT,
|
|
|
41 uncompressedSize BIGINT,
|
|
|
42 compressionType INTEGER,
|
|
|
43 uncompressedHash VARCHAR(40),
|
|
|
44 compressedHash VARCHAR(40),
|
|
|
45 PRIMARY KEY(id, fileType)
|
|
|
46 );
|
|
|
47
|
|
|
48 CREATE TABLE Changes(
|
|
|
49 seq BIGSERIAL NOT NULL PRIMARY KEY,
|
|
|
50 changeType INTEGER,
|
|
|
51 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
|
|
|
52 resourceType INTEGER,
|
|
|
53 date VARCHAR(64)
|
|
|
54 );
|
|
|
55
|
|
|
56 CREATE TABLE ExportedResources(
|
|
|
57 seq BIGSERIAL NOT NULL PRIMARY KEY,
|
|
|
58 resourceType INTEGER,
|
|
|
59 publicId VARCHAR(64),
|
|
|
60 remoteModality TEXT,
|
|
|
61 patientId VARCHAR(64),
|
|
|
62 studyInstanceUid TEXT,
|
|
|
63 seriesInstanceUid TEXT,
|
|
|
64 sopInstanceUid TEXT,
|
|
|
65 date VARCHAR(64)
|
|
|
66 );
|
|
|
67
|
|
|
68 CREATE TABLE PatientRecyclingOrder(
|
|
|
69 seq BIGSERIAL NOT NULL PRIMARY KEY,
|
|
|
70 patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE
|
|
|
71 );
|
|
|
72
|
|
|
73 CREATE INDEX ChildrenIndex ON Resources(parentId);
|
|
|
74 CREATE INDEX PublicIndex ON Resources(publicId);
|
|
|
75 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
|
|
|
76 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
|
|
|
77
|
|
|
78 CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id);
|
|
|
79 CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
|
|
|
80 CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
|
|
|
81 CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
|
|
|
82
|
|
|
83 CREATE INDEX ChangesIndex ON Changes(internalId);
|
|
|
84
|
|
|
85
|
|
|
86 -- New tables wrt. Orthanc core
|
|
|
87 CREATE TABLE DeletedFiles(
|
|
|
88 uuid VARCHAR(64) NOT NULL, -- 0
|
|
|
89 fileType INTEGER, -- 1
|
|
|
90 compressedSize BIGINT, -- 2
|
|
|
91 uncompressedSize BIGINT, -- 3
|
|
|
92 compressionType INTEGER, -- 4
|
|
|
93 uncompressedHash VARCHAR(40), -- 5
|
|
|
94 compressedHash VARCHAR(40) -- 6
|
|
|
95 );
|
|
|
96
|
|
|
97 CREATE TABLE RemainingAncestor(
|
|
|
98 resourceType INTEGER NOT NULL,
|
|
|
99 publicId VARCHAR(64) NOT NULL
|
|
|
100 );
|
|
|
101
|
|
|
102 CREATE TABLE DeletedResources(
|
|
|
103 resourceType INTEGER NOT NULL,
|
|
|
104 publicId VARCHAR(64) NOT NULL
|
|
|
105 );
|
|
|
106 -- End of differences
|
|
|
107
|
|
|
108
|
|
|
109 CREATE FUNCTION AttachedFileDeletedFunc()
|
|
|
110 RETURNS TRIGGER AS $body$
|
|
|
111 BEGIN
|
|
|
112 INSERT INTO DeletedFiles VALUES
|
|
|
113 (old.uuid, old.filetype, old.compressedSize,
|
|
|
114 old.uncompressedSize, old.compressionType,
|
|
|
115 old.uncompressedHash, old.compressedHash);
|
|
|
116 RETURN NULL;
|
|
|
117 END;
|
|
|
118 $body$ LANGUAGE plpgsql;
|
|
|
119
|
|
|
120 CREATE TRIGGER AttachedFileDeleted
|
|
|
121 AFTER DELETE ON AttachedFiles
|
|
|
122 FOR EACH ROW
|
|
|
123 EXECUTE PROCEDURE AttachedFileDeletedFunc();
|
|
|
124
|
|
|
125
|
|
|
126 -- The following trigger combines 2 triggers from SQLite:
|
|
|
127 -- ResourceDeleted + ResourceDeletedParentCleaning
|
|
|
128 CREATE FUNCTION ResourceDeletedFunc()
|
|
|
129 RETURNS TRIGGER AS $body$
|
|
|
130 BEGIN
|
|
|
131 --RAISE NOTICE 'Delete resource %', old.parentId;
|
|
|
132 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
|
|
|
133
|
|
|
134 -- http://stackoverflow.com/a/11299968/881731
|
|
|
135 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN
|
|
|
136 -- Signal that the deleted resource has a remaining parent
|
|
|
137 INSERT INTO RemainingAncestor
|
|
|
138 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
|
|
|
139 ELSE
|
|
|
140 -- Delete a parent resource when its unique child is deleted
|
|
|
141 DELETE FROM Resources WHERE internalId = old.parentId;
|
|
|
142 END IF;
|
|
|
143 RETURN NULL;
|
|
|
144 END;
|
|
|
145 $body$ LANGUAGE plpgsql;
|
|
|
146
|
|
|
147 CREATE TRIGGER ResourceDeleted
|
|
|
148 AFTER DELETE ON Resources
|
|
|
149 FOR EACH ROW
|
|
|
150 EXECUTE PROCEDURE ResourceDeletedFunc();
|
|
|
151
|
|
|
152
|
|
|
153
|
|
|
154 CREATE FUNCTION PatientAddedFunc()
|
|
|
155 RETURNS TRIGGER AS $body$
|
|
|
156 BEGIN
|
|
|
157 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
|
|
|
158 IF new.resourceType = 0 THEN
|
|
|
159 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
|
|
|
160 END IF;
|
|
|
161 RETURN NULL;
|
|
|
162 END;
|
|
|
163 $body$ LANGUAGE plpgsql;
|
|
|
164
|
|
|
165 CREATE TRIGGER PatientAdded
|
|
|
166 AFTER INSERT ON Resources
|
|
|
167 FOR EACH ROW
|
|
|
168 EXECUTE PROCEDURE PatientAddedFunc();
|
