Mercurial > hg > orthanc-databases

annotate MySQL/Plugins/CreateInstance.sql @ 110:441a472bfd93

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new extension implemented for MySQL: CreateInstance
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 23 Jan 2019 16:28:43 +0100
parents
children 740d9829f52e
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
110
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE PROCEDURE CreateInstance(
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 IN patient TEXT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 IN study TEXT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4 IN series TEXT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5 IN instance TEXT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 OUT isNewPatient BOOLEAN,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7 OUT isNewStudy BOOLEAN,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 OUT isNewSeries BOOLEAN,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 OUT isNewInstance BOOLEAN,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10 OUT patientKey BIGINT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11 OUT studyKey BIGINT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12 OUT seriesKey BIGINT,
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 OUT instanceKey BIGINT)
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 BEGIN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 DECLARE recyclingSeq BIGINT@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 SELECT internalId INTO instanceKey FROM Resources WHERE publicId = instance AND resourceType = 3@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 IF NOT instanceKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 -- This instance already exists, stop here
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21 SELECT 0 INTO isNewInstance@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 ELSE
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 SELECT internalId INTO patientKey FROM Resources WHERE publicId = patient AND resourceType = 0@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 SELECT internalId INTO studyKey FROM Resources WHERE publicId = study AND resourceType = 1@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 SELECT internalId INTO seriesKey FROM Resources WHERE publicId = series AND resourceType = 2@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 IF patientKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 -- Must create a new patient
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 1'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL)@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 SELECT LAST_INSERT_ID() INTO patientKey@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 SELECT 1 INTO isNewPatient@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 ELSE
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37 SELECT 0 INTO isNewPatient@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40 IF patientKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 2'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
42 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
43
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
44 IF studyKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
45 -- Must create a new study
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
46 IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
47 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 3'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
48 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
49
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
50 INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey)@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
51 SELECT LAST_INSERT_ID() INTO studyKey@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
52 SELECT 1 INTO isNewStudy@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
53 ELSE
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
54 SELECT 0 INTO isNewStudy@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
55 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
56
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
57 IF studyKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
58 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 4'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
59 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
60
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
61 IF seriesKey IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
62 -- Must create a new series
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
63 IF NOT (instanceKey IS NULL) THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
64 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 5'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
65 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
66
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
67 INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey)@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
68 SELECT LAST_INSERT_ID() INTO seriesKey@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
69 SELECT 1 INTO isNewSeries@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
70 ELSE
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
71 SELECT 0 INTO isNewSeries@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
72 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
73
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
74 IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
75 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Broken invariant 6'@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
76 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
77
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
78 INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey)@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
79 SELECT LAST_INSERT_ID() INTO instanceKey@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
80 SELECT 1 INTO isNewInstance@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
81
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
82 -- Move the patient to the end of the recycling order
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
83 IF NOT isNewPatient THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
84 SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO recyclingSeq@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
85
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
86 IF NOT recyclingSeq IS NULL THEN
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
87 -- The patient is not protected
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
88 DELETE FROM PatientRecyclingOrder WHERE seq = recyclingSeq@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
89 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, patientKey)@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
90 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
91 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
92 END IF@
441a472bfd93 new extension implemented for MySQL: CreateInstance
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
93 END;