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