Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/CreateInstance.sql @ 71:d40c5fecd160 db-changes
new extension implemented for PostgreSQL: CreateInstance
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Fri, 04 Jan 2019 13:51:52 +0100 |
parents | |
children | 8dd29af7c844 |
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, |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
13 OUT instanceKey BIGINT) AS $$ |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
14 BEGIN |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
15 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
|
16 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
17 IF NOT (instanceKey IS NULL) THEN |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
18 -- This instance already exists, stop here |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
19 isNewInstance := 0; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
20 ELSE |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
21 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
|
22 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
|
23 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
|
24 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
25 IF patientKey IS NULL THEN |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
26 -- Must create a new patient |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
27 ASSERT studyKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
28 ASSERT seriesKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
29 ASSERT instanceKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
30 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
|
31 isNewPatient := 1; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
32 ELSE |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
33 isNewPatient := 0; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
34 END IF; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
35 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
36 ASSERT NOT patientKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
37 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
38 IF studyKey IS NULL THEN |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
39 -- Must create a new study |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
40 ASSERT seriesKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
41 ASSERT instanceKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
42 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
|
43 isNewStudy := 1; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
44 ELSE |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
45 isNewStudy := 0; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
46 END IF; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
47 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
48 ASSERT NOT studyKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
49 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
50 IF seriesKey IS NULL THEN |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
51 -- Must create a new series |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
52 ASSERT instanceKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
53 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
|
54 isNewSeries := 1; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
55 ELSE |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
56 isNewSeries := 0; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
57 END IF; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
58 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
59 ASSERT NOT seriesKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
60 ASSERT instanceKey IS NULL; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
61 |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
62 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
|
63 isNewInstance := 1; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
64 END IF; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
65 END; |
d40c5fecd160
new extension implemented for PostgreSQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff
changeset
|
66 $$ LANGUAGE plpgsql; |