Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql @ 583:ae7375d38607 find-refactoring tip
MySQL: fix ordering
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Mon, 21 Oct 2024 18:19:51 +0200 |
parents | 11c6bcc9d1f2 |
children | f18e46d7dbf8 |
rev | line source |
---|---|
470 | 1 -- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1 or 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 |
470 | 38 -- In Rev2, 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 |
470 | 44 -- These triggers disappears and are not replaced in Rev2 |
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 | 47 -- The signature has changed so we must delete the function before replacing it. |
458 | 48 DROP FUNCTION IF EXISTS CreateInstance; |