Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/CreateInstance.sql @ 90:e61587582cef db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
moved extension TagMostRecentPatient into stored procedure CreateInstance
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 16 Jan 2019 18:14:28 +0100
parents 8dd29af7c844
children b559af8fe6e0
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
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32 ASSERT studyKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 ASSERT seriesKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 ASSERT instanceKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 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
36 isNewPatient := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 isNewPatient := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 ASSERT NOT patientKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
42
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
43 IF studyKey IS NULL THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
44 -- Must create a new study
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 ASSERT seriesKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46 ASSERT instanceKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 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
48 isNewStudy := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 isNewStudy := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53 ASSERT NOT studyKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 IF seriesKey IS NULL THEN
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56 -- Must create a new series
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 ASSERT instanceKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 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
59 isNewSeries := 1;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
60 ELSE
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
61 isNewSeries := 0;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
64 ASSERT NOT seriesKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
65 ASSERT instanceKey IS NULL;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
66
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
67 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
68 isNewInstance := 1;
90
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
69
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
70 -- 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
71 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
72
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
73 IF NOT (patientSeq IS NULL) THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
74 -- The patient is not protected
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
75 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
76 IF countRecycling = 2 THEN
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
77 -- 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
78 DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
79 INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey);
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
80 END IF;
e61587582cef moved extension TagMostRecentPatient into stored procedure CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 72
diff changeset
81 END IF;
71
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
82 END IF;
d40c5fecd160 new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
83 END;
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
84
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents: 71
diff changeset
85 $body$ LANGUAGE plpgsql;