Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql @ 467:ff84104f7842 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
renamed v6.2 to REV2 + removed 'default' TransactionMode + renamed 'READ COMMITTED' into 'ReadCommitted'
author Alain Mazy <am@osimis.io>
date Mon, 05 Feb 2024 09:43:14 +0100
parents PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql@9da7c688e7dc
children 11c6bcc9d1f2
comparison
equal deleted inserted replaced
466:daaa35ddba54 467:ff84104f7842
1 -- This file contains part of the changes required to upgrade from 6.1 to 6.2 (DB version 6 and revision 2)
2 -- It actually contains only the changes that:
3 -- can not be executed with an idempotent statement in SQL
4 -- or would polute the PrepareIndex.sql
5 -- This file is executed only if the current schema is in revision 1 and it is executed
6 -- before PrepareIndex.sql that is idempotent.
7
8
9 -- add unique constraints if they do not exists
10 DO $body$
11 BEGIN
12
13 IF NOT EXISTS (
14 SELECT 1
15 FROM information_schema.table_constraints
16 WHERE table_schema = 'public'
17 AND table_name = 'resources'
18 AND constraint_name = 'uniquepublicid')
19 THEN
20 ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId);
21 RAISE NOTICE 'UniquePublicId constraint added to Resources.';
22 END IF;
23
24 IF NOT EXISTS (
25 SELECT 1
26 FROM information_schema.table_constraints
27 WHERE table_schema = 'public'
28 AND table_name = 'patientrecyclingorder'
29 AND constraint_name = 'uniquepatientid')
30 THEN
31 ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId);
32 RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.';
33 END IF;
34
35 END $body$ LANGUAGE plpgsql;
36
37
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 !
39 -- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean
40 DROP TABLE IF EXISTS DeletedFiles;
41 DROP TABLE IF EXISTS RemainingAncestor;
42 DROP TABLE IF EXISTS DeletedResources;
43
44 -- These triggers disappears and are not replaced in V6.2
45 DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
46
47 -- The signature has changed so we must delete the function before replacing it.
48 DROP FUNCTION IF EXISTS CreateInstance;