Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/CreateInstance.sql @ 428:4d0bacbd0fba pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
rewrote CreateInstance to make it compatible with READ COMMITED transactions
author Alain Mazy <am@osimis.io>
date Thu, 30 Nov 2023 14:46:38 +0100
parents b559af8fe6e0
children 23c7af6f671a
comparison
equal deleted inserted replaced
427:3cdea26ece73 428:4d0bacbd0fba
1 CREATE FUNCTION CreateInstance( 1 CREATE OR REPLACE FUNCTION CreateInstance(
2 IN patient TEXT, 2 IN patient TEXT,
3 IN study TEXT, 3 IN study TEXT,
4 IN series TEXT, 4 IN series TEXT,
5 IN instance TEXT, 5 IN instance TEXT,
6 OUT isNewPatient BIGINT, 6 OUT isNewPatient BIGINT,
15 DECLARE 15 DECLARE
16 patientSeq BIGINT; 16 patientSeq BIGINT;
17 countRecycling BIGINT; 17 countRecycling BIGINT;
18 18
19 BEGIN 19 BEGIN
20 SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3; 20 isNewPatient := 1;
21 isNewStudy := 1;
22 isNewSeries := 1;
23 isNewInstance := 1;
21 24
22 IF NOT (instanceKey IS NULL) THEN 25 BEGIN
23 -- This instance already exists, stop here 26 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL);
24 isNewInstance := 0; 27 exception
25 ELSE 28 when unique_violation then
26 SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0; 29 isNewPatient := 0;
27 SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1; 30 end;
28 SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2; 31 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0;
29 32
30 IF patientKey IS NULL THEN 33 BEGIN
31 -- Must create a new patient 34 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey);
32 IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN 35 exception
33 RAISE EXCEPTION 'Broken invariant'; 36 when unique_violation then
34 END IF; 37 isNewStudy := 0;
38 end;
39 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1;
35 40
36 INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey; 41 BEGIN
37 isNewPatient := 1; 42 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey);
38 ELSE 43 exception
39 isNewPatient := 0; 44 when unique_violation then
40 END IF; 45 isNewSeries := 0;
41 46 end;
42 IF (patientKey IS NULL) THEN 47 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2;
43 RAISE EXCEPTION 'Broken invariant';
44 END IF;
45 48
46 IF studyKey IS NULL THEN 49 BEGIN
47 -- Must create a new study 50 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey);
48 IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN 51 exception
49 RAISE EXCEPTION 'Broken invariant'; 52 when unique_violation then
50 END IF; 53 isNewInstance := 0;
54 end;
55 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3;
51 56
52 INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey; 57 IF isNewInstance > 0 THEN
53 isNewStudy := 1;
54 ELSE
55 isNewStudy := 0;
56 END IF;
57
58 IF (studyKey IS NULL) THEN
59 RAISE EXCEPTION 'Broken invariant';
60 END IF;
61
62 IF seriesKey IS NULL THEN
63 -- Must create a new series
64 IF NOT (instanceKey IS NULL) THEN
65 RAISE EXCEPTION 'Broken invariant';
66 END IF;
67
68 INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey;
69 isNewSeries := 1;
70 ELSE
71 isNewSeries := 0;
72 END IF;
73
74 IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN
75 RAISE EXCEPTION 'Broken invariant';
76 END IF;
77
78 INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey;
79 isNewInstance := 1;
80
81 -- Move the patient to the end of the recycling order 58 -- Move the patient to the end of the recycling order
82 SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq; 59 SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq;
83 60
84 IF NOT (patientSeq IS NULL) THEN 61 IF NOT (patientSeq IS NULL) THEN
85 -- The patient is not protected 62 -- The patient is not protected