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