Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql @ 452:6780dd8b0a66 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
fix latency measure for ODBC
author Alain Mazy <am@osimis.io>
date Thu, 18 Jan 2024 10:04:43 +0100
parents 392ac133a491
children
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
451
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
133 -- these triggers have been introduced in 6.2, remove them
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
134 DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
135 DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
136 DROP FUNCTION IF EXISTS IncrementResourcesTrackerFunc;
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
137 DROP FUNCTION IF EXISTS DecrementResourcesTrackerFunc;
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
138
392ac133a491 fix downgrade proc
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
139 -- this trigger has been removed in 6.2, 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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
158 -- this trigger was introduced in 6.2, remove it:
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
192 -- these functions have been introduced in 6.2:
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
196 -- this table has been introduced in 6.2:
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
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents:
diff changeset
199 -- these functions have been introduced in 6.2:
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