Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/CreateInstance.sql @ 351:2d9631b3e8c0 OrthancPostgreSQL-3.0
closing OrthancPostgreSQL-3.0
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 12 Aug 2021 16:09:46 +0200 |
parents | e61587582cef |
children | b559af8fe6e0 |
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; |