Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/CreateInstance.sql @ 71:d40c5fecd160 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
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,
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;