Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql @ 583:ae7375d38607 find-refactoring tip
MySQL: fix ordering
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Mon, 21 Oct 2024 18:19:51 +0200 |
parents | 11c6bcc9d1f2 |
children |
rev | line source |
---|---|
470 | 1 -- This file contains an SQL procedure to downgrade from schema Rev2 to Rev1 (version = 6, revision = 1). |
2 -- It reinstalls all triggers and temporary tables that have been removed or replaced in Rev2 | |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
3 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
4 -- note: we don't not remove the unique constraints that have been added - they should not |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
5 -- create any issues. |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
6 |
470 | 7 -- these constraints were introduced in Rev2 |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
8 ALTER TABLE Resources DROP CONSTRAINT UniquePublicId; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
9 ALTER TABLE PatientRecyclingOrder DROP CONSTRAINT UniquePatientId; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
10 |
470 | 11 -- the CreateInstance has been replaced in Rev2, reinstall the Rev1 |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
12 DROP FUNCTION CreateInstance; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
13 CREATE FUNCTION CreateInstance( |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
14 IN patient TEXT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
15 IN study TEXT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
16 IN series TEXT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
17 IN instance TEXT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
18 OUT isNewPatient BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
19 OUT isNewStudy BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
20 OUT isNewSeries BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
21 OUT isNewInstance BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
22 OUT patientKey BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
23 OUT studyKey BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
24 OUT seriesKey BIGINT, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
25 OUT instanceKey BIGINT) AS $body$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
26 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
27 DECLARE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
28 patientSeq BIGINT; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
29 countRecycling BIGINT; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
30 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
31 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
32 SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
33 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
34 IF NOT (instanceKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
35 -- This instance already exists, stop here |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
36 isNewInstance := 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
37 ELSE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
38 SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
39 SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
40 SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
41 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
42 IF patientKey IS NULL THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
43 -- Must create a new patient |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
44 IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
45 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
46 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
47 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
48 INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
49 isNewPatient := 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
50 ELSE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
51 isNewPatient := 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
52 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
53 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
54 IF (patientKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
55 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
56 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
57 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
58 IF studyKey IS NULL THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
59 -- Must create a new study |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
60 IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
61 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
62 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
63 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
64 INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
65 isNewStudy := 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
66 ELSE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
67 isNewStudy := 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
68 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
69 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
70 IF (studyKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
71 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
72 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
73 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
74 IF seriesKey IS NULL THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
75 -- Must create a new series |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
76 IF NOT (instanceKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
77 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
78 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
79 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
80 INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
81 isNewSeries := 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
82 ELSE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
83 isNewSeries := 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
84 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
85 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
86 IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
87 RAISE EXCEPTION 'Broken invariant'; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
88 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
89 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
90 INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
91 isNewInstance := 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
92 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
93 -- Move the patient to the end of the recycling order |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
94 SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
95 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
96 IF NOT (patientSeq IS NULL) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
97 -- The patient is not protected |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
98 SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
99 IF countRecycling = 2 THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
100 -- The patient was not at the end of the recycling order |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
101 DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
102 INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
103 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
104 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
105 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
106 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
107 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
108 $body$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
109 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
110 |
470 | 111 -- these tables have been deleted in Rev2: |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
112 CREATE TABLE DeletedFiles( |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
113 uuid VARCHAR(64) NOT NULL, -- 0 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
114 fileType INTEGER, -- 1 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
115 compressedSize BIGINT, -- 2 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
116 uncompressedSize BIGINT, -- 3 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
117 compressionType INTEGER, -- 4 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
118 uncompressedHash VARCHAR(40), -- 5 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
119 compressedHash VARCHAR(40) -- 6 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
120 ); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
121 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
122 CREATE TABLE RemainingAncestor( |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
123 resourceType INTEGER NOT NULL, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
124 publicId VARCHAR(64) NOT NULL |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
125 ); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
126 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
127 CREATE TABLE DeletedResources( |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
128 resourceType INTEGER NOT NULL, |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
129 publicId VARCHAR(64) NOT NULL |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
130 ); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
131 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
132 |
470 | 133 -- these triggers have been introduced in Rev2, remove them |
451 | 134 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; |
135 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; | |
136 DROP FUNCTION IF EXISTS IncrementResourcesTrackerFunc; | |
137 DROP FUNCTION IF EXISTS DecrementResourcesTrackerFunc; | |
138 | |
470 | 139 -- this trigger has been removed in Rev2, reinstall it |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
140 CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc() |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
141 RETURNS TRIGGER AS $$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
142 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
143 IF TG_OP = 'INSERT' THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
144 UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
145 RETURN new; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
146 ELSIF TG_OP = 'DELETE' THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
147 UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
148 RETURN old; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
149 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
150 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
151 $$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
152 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
153 CREATE TRIGGER CountResourcesTracker |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
154 AFTER INSERT OR DELETE ON Resources |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
155 FOR EACH ROW |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
156 EXECUTE PROCEDURE CountResourcesTrackerFunc(); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
157 |
470 | 158 -- this trigger was introduced in Rev2, remove it: |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
159 DROP FUNCTION IF EXISTS InsertOrUpdateMetadata; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
160 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
161 -- reinstall old triggers: |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
162 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
163 RETURNS TRIGGER AS $body$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
164 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
165 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
166 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
167 RETURN NULL; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
168 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
169 $body$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
170 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
171 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
172 RETURNS TRIGGER AS $body$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
173 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
174 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
175 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
176 RETURN NULL; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
177 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
178 $body$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
179 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
180 DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
181 CREATE TRIGGER AttachedFileIncrementSize |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
182 AFTER INSERT ON AttachedFiles |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
183 FOR EACH ROW |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
184 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
185 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
186 DROP TRIGGER AttachedFileDecrementSize ON AttachedFiles; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
187 CREATE TRIGGER AttachedFileDecrementSize |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
188 AFTER DELETE ON AttachedFiles |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
189 FOR EACH ROW |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
190 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
191 |
470 | 192 -- these functions have been introduced in Rev2: |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
193 DROP FUNCTION IF EXISTS UpdateStatistics; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
194 DROP FUNCTION IF EXISTS UpdateSingleStatistic; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
195 |
470 | 196 -- this table has been introduced in Rev2: |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
197 DROP TABLE IF EXISTS GlobalIntegersChanges; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
198 |
470 | 199 -- these functions have been introduced in Rev2: |
448
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
200 DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
201 DROP FUNCTION IF EXISTS DeleteResource; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
202 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
203 -- reinstall this old trigger: |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
204 CREATE OR REPLACE FUNCTION ResourceDeletedFunc() |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
205 RETURNS TRIGGER AS $body$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
206 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
207 --RAISE NOTICE 'Delete resource %', old.parentId; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
208 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
209 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
210 -- http://stackoverflow.com/a/11299968/881731 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
211 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
212 -- Signal that the deleted resource has a remaining parent |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
213 INSERT INTO RemainingAncestor |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
214 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
215 ELSE |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
216 -- Delete a parent resource when its unique child is deleted |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
217 DELETE FROM Resources WHERE internalId = old.parentId; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
218 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
219 RETURN NULL; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
220 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
221 $body$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
222 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
223 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
224 CREATE TRIGGER ResourceDeleted |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
225 AFTER DELETE ON Resources |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
226 FOR EACH ROW |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
227 EXECUTE PROCEDURE ResourceDeletedFunc(); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
228 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
229 -- reinstall this old trigger: |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
230 CREATE OR REPLACE FUNCTION PatientAddedFunc() |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
231 RETURNS TRIGGER AS $body$ |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
232 BEGIN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
233 -- The "0" corresponds to "OrthancPluginResourceType_Patient" |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
234 IF new.resourceType = 0 THEN |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
235 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
236 END IF; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
237 RETURN NULL; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
238 END; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
239 $body$ LANGUAGE plpgsql; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
240 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
241 DROP TRIGGER IF EXISTS PatientAdded ON Resources; |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
242 CREATE TRIGGER PatientAdded |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
243 AFTER INSERT ON Resources |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
244 FOR EACH ROW |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
245 EXECUTE PROCEDURE PatientAddedFunc(); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
246 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
247 |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
248 -- set the global properties that actually documents the DB version, revision and some of the capabilities |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
249 -- modify only the ones that have changed |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
250 DELETE FROM GlobalProperties WHERE property IN (4, 11); |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
251 INSERT INTO GlobalProperties VALUES (4, 1); -- GlobalProperty_DatabasePatchLevel |
f2427f94d879
added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
252 INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance |