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