Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql @ 437:d979f25e60cf pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Re-organized DB creation/upgrade into standalone files
author Alain Mazy <am@osimis.io>
date Mon, 18 Dec 2023 18:50:01 +0100
parents
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
437
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
1 -- This file contains part of the changes required to upgrade from revision 1 to revision 2 (v 6.0)
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
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
4 -- or would polute the PrepareIndexV2.sql
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
5 -- This file is executed only if the current schema is in revision 1 and it is executed before PrepareIndexV2.sql
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
6 -- that is idempotent.
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
7
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
8 -- add unique constraints if they donot exists
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
9 DO $body$
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
10 BEGIN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
11
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
12 IF NOT EXISTS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
13 SELECT 1
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
14 FROM information_schema.table_constraints
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
15 WHERE table_schema = 'public'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
16 AND table_name = 'resources'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
17 AND constraint_name = 'uniquepublicid')
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
18 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
19 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
20 RAISE NOTICE 'UniquePublicId constraint added to Resources.';
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
21 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
22
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
23 IF NOT EXISTS (
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
24 SELECT 1
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
25 FROM information_schema.table_constraints
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
26 WHERE table_schema = 'public'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
27 AND table_name = 'patientrecyclingorder'
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
28 AND constraint_name = 'uniquepatientid')
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
29 THEN
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
30 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
31 RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.';
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
32 END IF;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
33
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
34 END $body$ LANGUAGE plpgsql;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
35
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 -- In V2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions !
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
38 -- 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
39 DROP TABLE IF EXISTS DeletedFiles;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
40 DROP TABLE IF EXISTS RemainingAncestor;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
41 DROP TABLE IF EXISTS DeletedResources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
42
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
43 -- These triggers disappears and are not replaced in V2
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
44 DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
d979f25e60cf Re-organized DB creation/upgrade into standalone files
Alain Mazy <am@osimis.io>
parents:
diff changeset
45