Mercurial > hg > orthanc-databases
annotate MySQL/Plugins/CreateInstance.sql @ 272:c7dc70a0a477
upgrade to Orthanc SDK 1.9.2
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 22 Apr 2021 14:09:46 +0200 |
parents | 740d9829f52e |
children |
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; |