Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/PrepareIndexV2.sql @ 437:d979f25e60cf pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Re-organized DB creation/upgrade into standalone files
author Alain Mazy <am@osimis.io>
date Mon, 18 Dec 2023 18:50:01 +0100
parents
children f790232b77ef
comparison
equal deleted inserted replaced
436:f16faa1fdc46 437:d979f25e60cf
1 -- This SQL file creates a DB in revision 2 directly (version is being used from v 6.0 of the PostgreSQL plugin)
2 -- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions.
3 -- This script is self contained, it contains everything that needs to be run to create an Orthanc DB.
4 -- Note: it is and must be idempotent.
5
6 CREATE TABLE IF NOT EXISTS GlobalProperties(
7 property INTEGER PRIMARY KEY,
8 value TEXT
9 );
10
11 CREATE TABLE IF NOT EXISTS Resources(
12 internalId BIGSERIAL NOT NULL PRIMARY KEY,
13 resourceType INTEGER NOT NULL,
14 publicId VARCHAR(64) NOT NULL,
15 parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
16 CONSTRAINT UniquePublicId UNIQUE (publicId)
17 );
18
19 CREATE TABLE IF NOT EXISTS MainDicomTags(
20 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
21 tagGroup INTEGER,
22 tagElement INTEGER,
23 value TEXT,
24 PRIMARY KEY(id, tagGroup, tagElement)
25 );
26
27 CREATE TABLE IF NOT EXISTS DicomIdentifiers(
28 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
29 tagGroup INTEGER,
30 tagElement INTEGER,
31 value TEXT,
32 PRIMARY KEY(id, tagGroup, tagElement)
33 );
34
35 CREATE TABLE IF NOT EXISTS Metadata(
36 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
37 type INTEGER NOT NULL,
38 value TEXT,
39 revision INTEGER,
40 PRIMARY KEY(id, type)
41 );
42
43 CREATE TABLE IF NOT EXISTS AttachedFiles(
44 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
45 fileType INTEGER,
46 uuid VARCHAR(64) NOT NULL,
47 compressedSize BIGINT,
48 uncompressedSize BIGINT,
49 compressionType INTEGER,
50 uncompressedHash VARCHAR(40),
51 compressedHash VARCHAR(40),
52 revision INTEGER,
53 PRIMARY KEY(id, fileType)
54 );
55
56 CREATE TABLE IF NOT EXISTS Changes(
57 seq BIGSERIAL NOT NULL PRIMARY KEY,
58 changeType INTEGER,
59 internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
60 resourceType INTEGER,
61 date VARCHAR(64)
62 );
63
64 CREATE TABLE IF NOT EXISTS ExportedResources(
65 seq BIGSERIAL NOT NULL PRIMARY KEY,
66 resourceType INTEGER,
67 publicId VARCHAR(64),
68 remoteModality TEXT,
69 patientId VARCHAR(64),
70 studyInstanceUid TEXT,
71 seriesInstanceUid TEXT,
72 sopInstanceUid TEXT,
73 date VARCHAR(64)
74 );
75
76 CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
77 seq BIGSERIAL NOT NULL PRIMARY KEY,
78 patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
79 CONSTRAINT UniquePatientId UNIQUE (patientId)
80 );
81
82 CREATE TABLE IF NOT EXISTS Labels(
83 id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
84 label TEXT,
85 PRIMARY KEY(id, label)
86 );
87
88 CREATE TABLE IF NOT EXISTS GlobalIntegers(
89 key INTEGER PRIMARY KEY,
90 value BIGINT);
91 -- GlobalIntegers keys:
92 -- 0: CompressedSize
93 -- 1: UncompressedSize
94 -- 2: PatientsCount
95 -- 3: StudiesCount
96 -- 4: SeriesCount
97 -- 5: InstancesCount
98 -- 6: ChangeSeq
99 -- 7: PatientRecyclingOrderSeq
100
101 CREATE TABLE IF NOT EXISTS ServerProperties(
102 server VARCHAR(64) NOT NULL,
103 property INTEGER, value TEXT,
104 PRIMARY KEY(server, property)
105 );
106
107 CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId);
108 CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
109 CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
110 CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
111
112 CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
113 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
114 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
115 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value);
116
117 CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId);
118 CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id);
119 CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label);
120
121 ------------------- Trigram index creation -------------------
122
123
124 -- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested
125 -- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better
126 -- performance for "strict" searches (i.e. searches involving no wildcard).
127 -- https://www.postgresql.org/docs/current/static/pgtrgm.html
128 -- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47
129
130 DO $body$
131 begin
132 IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN
133 CREATE EXTENSION IF NOT EXISTS pg_trgm;
134 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
135 ELSE
136 RAISE NOTICE 'pg_trgm extension is not available on you system';
137 END IF;
138 END $body$;
139
140
141 ------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
142 DROP TRIGGER IF EXISTS PatientAdded ON Resources;
143
144 CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
145 IN patient_id BIGINT,
146 IN is_update BIGINT
147 )
148 RETURNS VOID AS $body$
149 BEGIN
150 DECLARE
151 newSeq BIGINT;
152 BEGIN
153 UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq;
154 IF is_update > 0 THEN
155 -- Note: Protected patient are not listed in this table ! So, they won't be updated
156 UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id;
157 ELSE
158 INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id);
159 -- ON CONFLICT (patientId) DO UPDATE SET seq = newSeq;
160 END IF;
161 END;
162 END;
163 $body$ LANGUAGE plpgsql;
164
165 CREATE OR REPLACE FUNCTION PatientAddedFunc()
166 RETURNS TRIGGER AS $body$
167 BEGIN
168 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
169 IF new.resourceType = 0 THEN
170 PERFORM PatientAddedOrUpdated(new.internalId, 0);
171 -- UPDATE GlobalIntegers WHERE key = 7 SET value = value + 1 RETURNING value
172 -- INSERT INTO PatientRecyclingOrder VALUES ((SELECT value FROM GlobalIntegers WHERE key = 7), new.internalId)
173 -- ON CONFLICT ;
174 END IF;
175 RETURN NULL;
176 END;
177 $body$ LANGUAGE plpgsql;
178
179 CREATE TRIGGER PatientAdded
180 AFTER INSERT ON Resources
181 FOR EACH ROW
182 EXECUTE PROCEDURE PatientAddedFunc();
183
184 -- initial value for PatientRecyclingOrderSeq
185 INSERT INTO GlobalIntegers
186 SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder
187 ON CONFLICT DO NOTHING;
188
189
190 ------------------- ResourceDeleted trigger -------------------
191 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
192
193 -- The following trigger combines 2 triggers from SQLite:
194 -- ResourceDeleted + ResourceDeletedParentCleaning
195 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
196 RETURNS TRIGGER AS $body$
197 BEGIN
198 -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
199 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
200
201 -- If this resource is the latest child, delete the parent
202 DELETE FROM Resources WHERE internalId = old.parentId
203 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
204 RETURN NULL;
205 END;
206 $body$ LANGUAGE plpgsql;
207
208 CREATE TRIGGER ResourceDeleted
209 AFTER DELETE ON Resources
210 FOR EACH ROW
211 EXECUTE PROCEDURE ResourceDeletedFunc();
212
213
214 ------------------- DeleteResource function -------------------
215
216 CREATE OR REPLACE FUNCTION DeleteResource(
217 IN id BIGINT,
218 OUT remaining_ancestor_resource_type INTEGER,
219 OUT remaining_anncestor_public_id TEXT) AS $body$
220
221 DECLARE
222 deleted_row RECORD;
223
224 BEGIN
225
226 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
227
228 -- note: temporary tables are created at session (connection) level -> they are likely to exist
229 -- these tables are used by the triggers
230 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
231 resourceType INTEGER NOT NULL,
232 publicId VARCHAR(64) NOT NULL
233 );
234
235 RESET client_min_messages;
236
237 -- clear the temporary table in case it has been created earlier in the session
238 DELETE FROM DeletedResources;
239
240 -- create/clear the DeletedFiles temporary table
241 PERFORM CreateDeletedFilesTemporaryTable();
242
243 -- delete the resource itself
244 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
245 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
246
247 -- If this resource still has siblings, keep track of the remaining parent
248 -- (a parent that must not be deleted but whose LastUpdate must be updated)
249 SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id
250 FROM Resources
251 WHERE internalId = deleted_row.parentId
252 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
253
254 END;
255
256 $body$ LANGUAGE plpgsql;
257
258 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
259 ) RETURNS VOID AS $body$
260
261 BEGIN
262
263 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
264
265 -- note: temporary tables are created at session (connection) level -> they are likely to exist
266 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
267 uuid VARCHAR(64) NOT NULL,
268 fileType INTEGER,
269 compressedSize BIGINT,
270 uncompressedSize BIGINT,
271 compressionType INTEGER,
272 uncompressedHash VARCHAR(40),
273 compressedHash VARCHAR(40)
274 );
275
276 RESET client_min_messages;
277
278 -- clear the temporary table in case it has been created earlier in the session
279 DELETE FROM DeletedFiles;
280 END;
281
282 $body$ LANGUAGE plpgsql;
283
284
285 DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles;
286
287 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc()
288 RETURNS TRIGGER AS $body$
289 BEGIN
290 INSERT INTO DeletedFiles VALUES
291 (old.uuid, old.filetype, old.compressedSize,
292 old.uncompressedSize, old.compressionType,
293 old.uncompressedHash, old.compressedHash);
294 RETURN NULL;
295 END;
296 $body$ LANGUAGE plpgsql;
297
298 CREATE TRIGGER AttachedFileDeleted
299 AFTER DELETE ON AttachedFiles
300 FOR EACH ROW
301 EXECUTE PROCEDURE AttachedFileDeletedFunc();
302
303
304 ------------------- Fast Statistics -------------------
305
306 -- initialize values if not already theere
307 INSERT INTO GlobalIntegers
308 SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles
309 ON CONFLICT DO NOTHING;
310
311 INSERT INTO GlobalIntegers
312 SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles
313 ON CONFLICT DO NOTHING;
314
315 INSERT INTO GlobalIntegers
316 SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0 -- Count patients
317 ON CONFLICT DO NOTHING;
318
319 INSERT INTO GlobalIntegers
320 SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1 -- Count studies
321 ON CONFLICT DO NOTHING;
322
323 INSERT INTO GlobalIntegers
324 SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2 -- Count series
325 ON CONFLICT DO NOTHING;
326
327 INSERT INTO GlobalIntegers
328 SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3 -- Count instances
329 ON CONFLICT DO NOTHING;
330
331
332 -- this table stores all changes that needs to be performed to the GlobalIntegers table
333 -- This way, each transaction can add row independently in this table without having to lock
334 -- any row (which was the case with previous FastTotalSize).
335 -- These changes will be applied at regular interval by an external thread or when someone
336 -- requests the statistics
337 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
338 key INTEGER,
339 value BIGINT);
340
341 CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
342 IN statistics_key INTEGER,
343 OUT new_value BIGINT
344 ) AS $body$
345 BEGIN
346
347 -- Delete the current changes, sum them and update the GlobalIntegers row.
348 -- New rows can be added in the meantime, they won't be deleted or summed.
349 WITH deleted_rows AS (
350 DELETE FROM GlobalIntegersChanges
351 WHERE GlobalIntegersChanges.key = statistics_key
352 RETURNING value
353 )
354 UPDATE GlobalIntegers
355 SET value = value + (
356 SELECT COALESCE(SUM(value), 0)
357 FROM deleted_rows
358 )
359 WHERE GlobalIntegers.key = statistics_key
360 RETURNING value INTO new_value;
361
362 END;
363 $body$ LANGUAGE plpgsql;
364
365
366 CREATE OR REPLACE FUNCTION UpdateStatistics(
367 OUT patients_cunt BIGINT,
368 OUT studies_count BIGINT,
369 OUT series_count BIGINT,
370 OUT instances_count BIGINT,
371 OUT total_compressed_size BIGINT,
372 OUT total_uncompressed_size BIGINT
373 ) AS $body$
374 BEGIN
375
376 SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
377 SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
378 SELECT UpdateSingleStatistic(2) INTO patients_cunt;
379 SELECT UpdateSingleStatistic(3) INTO studies_count;
380 SELECT UpdateSingleStatistic(4) INTO series_count;
381 SELECT UpdateSingleStatistic(5) INTO instances_count;
382
383 END;
384 $body$ LANGUAGE plpgsql;
385
386
387 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
388 RETURNS TRIGGER AS $$
389 BEGIN
390 INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
391 RETURN NULL;
392 END;
393 $$ LANGUAGE plpgsql;
394
395 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
396 RETURNS TRIGGER AS $$
397 BEGIN
398 INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
399 RETURN NULL;
400 END;
401 $$ LANGUAGE plpgsql;
402
403
404 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
405 RETURNS TRIGGER AS $body$
406 BEGIN
407 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
408 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
409 RETURN NULL;
410 END;
411 $body$ LANGUAGE plpgsql;
412
413 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
414 RETURNS TRIGGER AS $body$
415 BEGIN
416 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
417 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
418 RETURN NULL;
419 END;
420 $body$ LANGUAGE plpgsql;
421
422 DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
423 CREATE TRIGGER AttachedFileIncrementSize
424 AFTER INSERT ON AttachedFiles
425 FOR EACH ROW
426 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
427
428 DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
429 CREATE TRIGGER AttachedFileDecrementSize
430 AFTER DELETE ON AttachedFiles
431 FOR EACH ROW
432 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
433
434 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
435 CREATE TRIGGER IncrementResourcesTracker
436 AFTER INSERT ON Resources
437 FOR EACH ROW
438 EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
439
440 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
441 CREATE TRIGGER DecrementResourcesTracker
442 AFTER DELETE ON Resources
443 FOR EACH ROW
444 EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
445
446
447 ------------------- InsertOrUpdateMetadata function -------------------
448 CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[],
449 metadata_types INTEGER[],
450 metadata_values TEXT[],
451 revisions INTEGER[])
452 RETURNS VOID AS $body$
453 BEGIN
454 FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP
455 -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i];
456 INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) ON CONFLICT DO NOTHING;
457 END LOOP;
458
459 END;
460 $body$ LANGUAGE plpgsql;
461
462
463 ------------------- GetLastChange function -------------------
464 DROP TRIGGER IF EXISTS InsertedChange ON Changes;
465
466 -- insert the value if not already there
467 INSERT INTO GlobalIntegers
468 SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes
469 ON CONFLICT DO NOTHING;
470
471 CREATE OR REPLACE FUNCTION InsertedChangeFunc()
472 RETURNS TRIGGER AS $body$
473 BEGIN
474 UPDATE GlobalIntegers SET value = new.seq WHERE key = 6;
475 RETURN NULL;
476 END;
477 $body$ LANGUAGE plpgsql;
478
479 CREATE TRIGGER InsertedChange
480 AFTER INSERT ON Changes
481 FOR EACH ROW
482 EXECUTE PROCEDURE InsertedChangeFunc();
483
484
485 ------------------- CreateInstance function -------------------
486 CREATE OR REPLACE FUNCTION CreateInstance(
487 IN patient_public_id TEXT,
488 IN study_public_id TEXT,
489 IN series_public_id TEXT,
490 IN instance_public_id TEXT,
491 OUT is_new_patient BIGINT,
492 OUT is_new_study BIGINT,
493 OUT is_new_series BIGINT,
494 OUT is_new_instance BIGINT,
495 OUT patient_internal_id BIGINT,
496 OUT study_internal_id BIGINT,
497 OUT series_internal_id BIGINT,
498 OUT instance_internal_id BIGINT) AS $body$
499
500 DECLARE
501 patientSeq BIGINT;
502 countRecycling BIGINT;
503
504 BEGIN
505 is_new_patient := 1;
506 is_new_study := 1;
507 is_new_series := 1;
508 is_new_instance := 1;
509
510 BEGIN
511 INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL);
512 EXCEPTION
513 WHEN unique_violation THEN
514 is_new_patient := 0;
515 END;
516 SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0;
517
518 BEGIN
519 INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id);
520 EXCEPTION
521 WHEN unique_violation THEN
522 is_new_study := 0;
523 END;
524 SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1;
525
526 BEGIN
527 INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id);
528 EXCEPTION
529 WHEN unique_violation THEN
530 is_new_series := 0;
531 END;
532 SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2;
533
534 BEGIN
535 INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id);
536 EXCEPTION
537 WHEN unique_violation THEN
538 is_new_instance := 0;
539 END;
540 SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3;
541
542 IF is_new_instance > 0 THEN
543 -- Move the patient to the end of the recycling order.
544 PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
545 END IF;
546 END;
547
548 $body$ LANGUAGE plpgsql;