Mercurial > hg > orthanc-databases

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
1 -- This file contains an SQL procedure to downgrade from schema 6.2 to 6.1 (version = 6, revision = 1).
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
2 -- It reinstalls all triggers and temporary tables that have been removed or replaced in 6.2
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
7 -- these constraints were introduced in 6.2
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
11 -- the CreateInstance has been replaced in 6.2, reinstall the 6.1
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
111 -- these tables have been deleted in 6.2:
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
133 -- this trigger has been removed in 6.2
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
134 CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
135 RETURNS TRIGGER AS $$
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
136 BEGIN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
137 IF TG_OP = 'INSERT' THEN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
138 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
139 RETURN new;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
140 ELSIF TG_OP = 'DELETE' THEN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
141 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
142 RETURN old;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
143 END IF;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
144 END;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
145 $$ LANGUAGE plpgsql;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
146
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
147 CREATE TRIGGER CountResourcesTracker
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
148 AFTER INSERT OR DELETE ON Resources
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
149 FOR EACH ROW
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
150 EXECUTE PROCEDURE CountResourcesTrackerFunc();
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
151
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
152 -- this trigger was introduced in 6.2, remove it:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
153 DROP FUNCTION IF EXISTS InsertOrUpdateMetadata;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
154
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
155 -- reinstall old triggers:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
156 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
157 RETURNS TRIGGER AS $body$
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
158 BEGIN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
159 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
160 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
161 RETURN NULL;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
162 END;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
163 $body$ LANGUAGE plpgsql;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
164
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
165 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc()
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
166 RETURNS TRIGGER AS $body$
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
167 BEGIN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
168 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
169 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
170 RETURN NULL;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
171 END;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
172 $body$ LANGUAGE plpgsql;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
173
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
174 DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
175 CREATE TRIGGER AttachedFileIncrementSize
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
176 AFTER INSERT ON AttachedFiles
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
177 FOR EACH ROW
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
178 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
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 AttachedFileDecrementSize ON AttachedFiles;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
181 CREATE TRIGGER AttachedFileDecrementSize
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
182 AFTER DELETE 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 AttachedFileDecrementSizeFunc();
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 -- these functions have been introduced in 6.2:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
187 DROP FUNCTION IF EXISTS UpdateStatistics;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
188 DROP FUNCTION IF EXISTS UpdateSingleStatistic;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
189
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
190 -- this table has been introduced in 6.2:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
191 DROP TABLE IF EXISTS GlobalIntegersChanges;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
192
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
193 -- these functions have been introduced in 6.2:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
194 DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
195 DROP FUNCTION IF EXISTS DeleteResource;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
196
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
197 -- reinstall this old trigger:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
198 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
199 RETURNS TRIGGER AS $body$
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
200 BEGIN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
201 --RAISE NOTICE 'Delete resource %', old.parentId;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
202 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
203
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
204 -- http://stackoverflow.com/a/11299968/881731
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
205 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
206 -- Signal that the deleted resource has a remaining parent
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
207 INSERT INTO RemainingAncestor
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
208 SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
209 ELSE
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
210 -- Delete a parent resource when its unique child is deleted
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
211 DELETE FROM Resources WHERE internalId = old.parentId;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
212 END IF;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
213 RETURN NULL;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
214 END;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
215 $body$ LANGUAGE plpgsql;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
216
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
217 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
218 CREATE TRIGGER ResourceDeleted
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
219 AFTER DELETE ON Resources
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
220 FOR EACH ROW
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
221 EXECUTE PROCEDURE ResourceDeletedFunc();
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 -- reinstall this old trigger:
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
224 CREATE OR REPLACE FUNCTION PatientAddedFunc()
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
225 RETURNS TRIGGER AS $body$
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
226 BEGIN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
227 -- The "0" corresponds to "OrthancPluginResourceType_Patient"
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
228 IF new.resourceType = 0 THEN
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
229 INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
230 END IF;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
231 RETURN NULL;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
232 END;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
233 $body$ LANGUAGE plpgsql;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
234
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
235 DROP TRIGGER IF EXISTS PatientAdded ON Resources;
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
236 CREATE TRIGGER PatientAdded
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
237 AFTER INSERT ON Resources
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
238 FOR EACH ROW
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
239 EXECUTE PROCEDURE PatientAddedFunc();
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
242 -- 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
243 -- modify only the ones that have changed
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
244 DELETE FROM GlobalProperties WHERE property IN (4, 11);
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
245 INSERT INTO GlobalProperties VALUES (4, 1); -- GlobalProperty_DatabasePatchLevel
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
246 INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance