Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql @ 456:0ac99983abfa pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
missing drop function
author Alain Mazy <am@osimis.io>
date Tue, 23 Jan 2024 16:50:55 +0100
parents f2427f94d879
children 9da7c688e7dc
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: 437
diff changeset
1 -- This file contains part of the changes required to upgrade from 6.1 to 6.2 (DB version 6 and revision 2)
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
2 -- It actually contains only the changes that:
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
3 -- can not be executed with an idempotent statement in SQL
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
4 -- or would polute the PrepareIndex.sql
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
5 -- This file is executed only if the current schema is in revision 1 and it is executed
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
6 -- before PrepareIndex.sql that is idempotent.
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
7
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
8
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
9 -- add unique constraints if they do not exists
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
10 DO $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
11 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
12
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
13 IF NOT EXISTS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
14 SELECT 1
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
15 FROM information_schema.table_constraints
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
16 WHERE table_schema = 'public'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
17 AND table_name = 'resources'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
18 AND constraint_name = 'uniquepublicid')
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
19 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
20 ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
21 RAISE NOTICE 'UniquePublicId constraint added to Resources.';
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
22 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
23
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
24 IF NOT EXISTS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
25 SELECT 1
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
26 FROM information_schema.table_constraints
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
27 WHERE table_schema = 'public'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
28 AND table_name = 'patientrecyclingorder'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
29 AND constraint_name = 'uniquepatientid')
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
30 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
31 ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId);
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
32 RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.';
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
33 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
34
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
35 END $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
36
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
37
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
38 -- In V6.2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions !
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
39 -- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
40 DROP TABLE IF EXISTS DeletedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
41 DROP TABLE IF EXISTS RemainingAncestor;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
42 DROP TABLE IF EXISTS DeletedResources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
43
448
f2427f94d879 added downgrade script + renames version
Alain Mazy <am@osimis.io>
parents: 437
diff changeset
44 -- These triggers disappears and are not replaced in V6.2
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
45 DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
46
456
0ac99983abfa missing drop function
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
47 -- The signature has changed so we must delete the function before replacing it.
0ac99983abfa missing drop function
Alain Mazy <am@osimis.io>
parents: 448
diff changeset
48 DROP FUNCTION CreateInstance;