Mercurial > hg > orthanc-databases
view PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql @ 618:20d7c9471e8e
fix pg unit tests (1)
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Wed, 18 Dec 2024 11:23:35 +0100 |
parents | c2497f8bf5b6 |
children |
line wrap: on
line source
-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2) -- It actually contains only the changes that: -- can not be executed with an idempotent statement in SQL -- or would polute the PrepareIndex.sql -- do facilite an up-time upgrade -- This file is executed only if the current schema is in revision 2 and it is executed -- before PrepareIndex.sql that is idempotent. -- create a new ChildrenIndex2 that is replacing ChildrenIndex. -- We create it in this partial update so it can be created while the system is up ! DO $$ DECLARE pg_version text; BEGIN SELECT version() INTO pg_version; IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN -- PostgreSQL 11 or later EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; ELSE EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; END IF; END $$; DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading -- add the childCount columns in Resources if not yet done DO $body$ BEGIN IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN ALTER TABLE Resources ADD COLUMN childcount INTEGER; ELSE raise notice 'the resources.childcount column already exists'; END IF; END $body$; -- other changes performed in PrepareIndex.sql: -- add ChildCount tables and triggers