Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/CreateInstance.sql @ 259:93a6efd7d8e5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
todo
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 16 Apr 2021 17:49:29 +0200
parents b559af8fe6e0
children 4d0bacbd0fba
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE FUNCTION CreateInstance(
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
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 IF NOT (instanceKey IS NULL) THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 -- This instance already exists, stop here
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 isNewInstance := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 IF patientKey IS NULL THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 -- Must create a new patient
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
32 IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
33 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
34 END IF;
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
35
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 isNewPatient := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 isNewPatient := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
42 IF (patientKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
43 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
44 END IF;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46 IF studyKey IS NULL THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 -- Must create a new study
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
48 IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
49 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
50 END IF;
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
51
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52 INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53 isNewStudy := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 isNewStudy := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
58 IF (studyKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
59 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
60 END IF;
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
61
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62 IF seriesKey IS NULL THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63 -- Must create a new series
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
64 IF NOT (instanceKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
65 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
66 END IF;
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
67
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
68 INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
69 isNewSeries := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 isNewSeries := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
72 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
73
106
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
74 IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
75 RAISE EXCEPTION 'Broken invariant';
b559af8fe6e0 Remove "ASSERT" in SQL for compatibility with older releases of PostgreSQL
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 90
diff changeset
76 END IF;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
77
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
78 INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
79 isNewInstance := 1;
90
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
80
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
81 -- 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
82 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
83
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
84 IF NOT (patientSeq IS NULL) THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
85 -- The patient is not protected
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
86 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
87 IF countRecycling = 2 THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
88 -- 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
89 DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
90 INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey);
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
91 END IF;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
92 END IF;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
93 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
94 END;
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
95
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
96 $body$ LANGUAGE plpgsql;