Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/CreateInstance.sql @ 435:326f8304daa1 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new creating temporary tables inside functions
author Alain Mazy <am@osimis.io>
date Thu, 14 Dec 2023 09:25:45 +0100
parents 23c7af6f671a
children
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);
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
27 EXCEPTION
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
28 WHEN unique_violation THEN
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
29 isNewPatient := 0;
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
30 END;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
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);
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
35 EXCEPTION
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
36 WHEN unique_violation THEN
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
37 isNewStudy := 0;
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
38 END;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
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);
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
43 EXCEPTION
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
44 WHEN unique_violation THEN
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
45 isNewSeries := 0;
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
46 END;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
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);
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
51 EXCEPTION
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
52 WHEN unique_violation THEN
428
4d0bacbd0fba rewrote CreateInstance to make it compatible with READ COMMITED transactions
Alain Mazy <am@osimis.io>
parents: 106
diff changeset
53 isNewInstance := 0;
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
diff changeset
54 END;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 428
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;