Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql @ 448:f2427f94d879 pg-transactions
added downgrade script + renames version
author | Alain Mazy <am@osimis.io> |
---|---|
date | Wed, 17 Jan 2024 16:22:08 +0100 |
parents | PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql@d979f25e60cf |
children | 0ac99983abfa |
comparison
equal
deleted
inserted
replaced
447:5881e4af5799 | 448:f2427f94d879 |
---|---|
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 |