Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 569:f18e46d7dbf8 attach-custom-data

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