Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql @ 448:f2427f94d879 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added downgrade script + renames version
author Alain Mazy <am@osimis.io>
date Wed, 17 Jan 2024 16:22:08 +0100
parents
children 392ac133a491
comparison
equal deleted inserted replaced
447:5881e4af5799 448:f2427f94d879
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 -- this trigger has been removed in 6.2
134 CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
135 RETURNS TRIGGER AS $$
136 BEGIN
137 IF TG_OP = 'INSERT' THEN
138 UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
139 RETURN new;
140 ELSIF TG_OP = 'DELETE' THEN
141 UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
142 RETURN old;
143 END IF;
144 END;
145 $$ LANGUAGE plpgsql;
146
147 CREATE TRIGGER CountResourcesTracker
148 AFTER INSERT OR DELETE ON Resources
149 FOR EACH ROW
150 EXECUTE PROCEDURE CountResourcesTrackerFunc();
151
152 -- this trigger was introduced in 6.2, remove it:
153 DROP FUNCTION IF EXISTS InsertOrUpdateMetadata;
154
155 -- reinstall old triggers:
156 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
157 RETURNS TRIGGER AS $body$
158 BEGIN
159 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
160 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
161 RETURN NULL;
162 END;
163 $body$ LANGUAGE plpgsql;
164
165 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
166 RETURNS TRIGGER AS $body$
167 BEGIN
168 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
169 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
170 RETURN NULL;
171 END;
172 $body$ LANGUAGE plpgsql;
173
174 DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles;
175 CREATE TRIGGER AttachedFileIncrementSize
176 AFTER INSERT ON AttachedFiles
177 FOR EACH ROW
178 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
179
180 DROP TRIGGER AttachedFileDecrementSize ON AttachedFiles;
181 CREATE TRIGGER AttachedFileDecrementSize
182 AFTER DELETE ON AttachedFiles
183 FOR EACH ROW
184 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
185
186 -- these functions have been introduced in 6.2:
187 DROP FUNCTION IF EXISTS UpdateStatistics;
188 DROP FUNCTION IF EXISTS UpdateSingleStatistic;
189
190 -- this table has been introduced in 6.2:
191 DROP TABLE IF EXISTS GlobalIntegersChanges;
192
193 -- these functions have been introduced in 6.2:
194 DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable;
195 DROP FUNCTION IF EXISTS DeleteResource;
196
197 -- reinstall this old trigger:
198 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
199 RETURNS TRIGGER AS $body$
200 BEGIN
201 --RAISE NOTICE 'Delete resource %', old.parentId;
202 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
203
204 -- http://stackoverflow.com/a/11299968/881731
205 IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN
206 -- Signal that the deleted resource has a remaining parent
207 INSERT INTO RemainingAncestor
208 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
209 ELSE
210 -- Delete a parent resource when its unique child is deleted
211 DELETE FROM Resources WHERE internalId = old.parentId;
212 END IF;
213 RETURN NULL;
214 END;
215 $body$ LANGUAGE plpgsql;
216
217 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
218 CREATE TRIGGER ResourceDeleted
219 AFTER DELETE ON Resources
220 FOR EACH ROW
221 EXECUTE PROCEDURE ResourceDeletedFunc();
222
223 -- reinstall this old trigger:
224 CREATE OR REPLACE FUNCTION PatientAddedFunc()
225 RETURNS TRIGGER AS $body$
226 BEGIN
227 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
228 IF new.resourceType = 0 THEN
229 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
230 END IF;
231 RETURN NULL;
232 END;
233 $body$ LANGUAGE plpgsql;
234
235 DROP TRIGGER IF EXISTS PatientAdded ON Resources;
236 CREATE TRIGGER PatientAdded
237 AFTER INSERT ON Resources
238 FOR EACH ROW
239 EXECUTE PROCEDURE PatientAddedFunc();
240
241
242 -- set the global properties that actually documents the DB version, revision and some of the capabilities
243 -- modify only the ones that have changed
244 DELETE FROM GlobalProperties WHERE property IN (4, 11);
245 INSERT INTO GlobalProperties VALUES (4, 1); -- GlobalProperty_DatabasePatchLevel
246 INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance