Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/CreateInstance.sql @ 72:8dd29af7c844 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new extension: FastTotalSize
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 04 Jan 2019 14:43:35 +0100
parents d40c5fecd160
children e61587582cef
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
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;