Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql @ 467:ff84104f7842 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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