Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/PrepareIndex.sql @ 0:7cea966b6829
initial commit
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Wed, 04 Jul 2018 08:16:29 +0200 |
parents | |
children | cd9521e04249 a7f0f27fe33c |
comparison
equal
deleted
inserted
replaced
-1:000000000000 | 0:7cea966b6829 |
---|---|
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(); |