Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql @ 437:d979f25e60cf pg-transactions
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 |
comparison
equal
deleted
inserted
replaced
| 436:f16faa1fdc46 | 437:d979f25e60cf |
|---|---|
| 1 -- This file contains part of the changes required to upgrade from revision 1 to revision 2 (v 6.0) | |
| 2 -- It actually contains only the changes that: | |
| 3 -- can not be executed with an idempotent statement in SQL | |
| 4 -- or would polute the PrepareIndexV2.sql | |
| 5 -- This file is executed only if the current schema is in revision 1 and it is executed before PrepareIndexV2.sql | |
| 6 -- that is idempotent. | |
| 7 | |
| 8 -- add unique constraints if they donot exists | |
| 9 DO $body$ | |
| 10 BEGIN | |
| 11 | |
| 12 IF NOT EXISTS ( | |
| 13 SELECT 1 | |
| 14 FROM information_schema.table_constraints | |
| 15 WHERE table_schema = 'public' | |
| 16 AND table_name = 'resources' | |
| 17 AND constraint_name = 'uniquepublicid') | |
| 18 THEN | |
| 19 ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId); | |
| 20 RAISE NOTICE 'UniquePublicId constraint added to Resources.'; | |
| 21 END IF; | |
| 22 | |
| 23 IF NOT EXISTS ( | |
| 24 SELECT 1 | |
| 25 FROM information_schema.table_constraints | |
| 26 WHERE table_schema = 'public' | |
| 27 AND table_name = 'patientrecyclingorder' | |
| 28 AND constraint_name = 'uniquepatientid') | |
| 29 THEN | |
| 30 ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId); | |
| 31 RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.'; | |
| 32 END IF; | |
| 33 | |
| 34 END $body$ LANGUAGE plpgsql; | |
| 35 | |
| 36 | |
| 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 ! | |
| 38 -- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean | |
| 39 DROP TABLE IF EXISTS DeletedFiles; | |
| 40 DROP TABLE IF EXISTS RemainingAncestor; | |
| 41 DROP TABLE IF EXISTS DeletedResources; | |
| 42 | |
| 43 -- These triggers disappears and are not replaced in V2 | |
| 44 DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources; | |
| 45 |