Mercurial > hg > orthanc-databases

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
1 CREATE OR REPLACE FUNCTION CreateInstance(
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 IN patient TEXT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 IN study TEXT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 IN series TEXT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5 IN instance TEXT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 OUT isNewPatient BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 OUT isNewStudy BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 OUT isNewSeries BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 OUT isNewInstance BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 OUT patientKey BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 OUT studyKey BIGINT,
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 OUT seriesKey BIGINT,
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
13 OUT instanceKey BIGINT) AS $body$
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
14
90
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
15 DECLARE
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
16 patientSeq BIGINT;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
17 countRecycling BIGINT;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
18
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 BEGIN
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
20 isNewPatient := 1;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
21 isNewStudy := 1;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
22 isNewSeries := 1;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
23 isNewInstance := 1;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
25 BEGIN
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
26 INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL);
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
27 exception
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
28 when unique_violation then
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
29 isNewPatient := 0;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
30 end;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
31 select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
33 BEGIN
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
34 INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey);
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
35 exception
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
36 when unique_violation then
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
37 isNewStudy := 0;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
38 end;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
39 select internalid into studyKey from "resources" where publicId=study and resourcetype = 1;
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
40
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
41 BEGIN
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
42 INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey);
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
43 exception
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
44 when unique_violation then
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
45 isNewSeries := 0;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
46 end;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
47 select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2;
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
48
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
49 BEGIN
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
50 INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey);
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
51 exception
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
52 when unique_violation then
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
53 isNewInstance := 0;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
54 end;
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
55 select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
57 IF isNewInstance > 0 THEN
90
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
58 -- Move the patient to the end of the recycling order
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
59 SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
60
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
61 IF NOT (patientSeq IS NULL) THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
62 -- The patient is not protected
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
63 SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
64 IF countRecycling = 2 THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
65 -- The patient was not at the end of the recycling order
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
66 DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
67 INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey);
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
68 END IF;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
69 END IF;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 END;
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
73 $body$ LANGUAGE plpgsql;