Mercurial > hg > orthanc-databases
annotate MySQL/Plugins/CreateInstance.sql @ 350:592b1db14fda OrthancPostgreSQL-3.1
closing OrthancPostgreSQL-3.1
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Thu, 12 Aug 2021 16:09:43 +0200 |
parents | 441a472bfd93 |
children | 740d9829f52e |
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; |