Mercurial > hg > orthanc-databases

annotate MySQL/Plugins/CreateInstance.sql @ 144:740d9829f52e

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