Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/PrepareIndex.sql @ 448:f2427f94d879 pg-transactions

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