From 58e003b341ca83479ae4317c32ded76b5f4989b4 Mon Sep 17 00:00:00 2001 From: Michael Ritter Date: Fri, 8 Mar 2024 15:55:27 -0700 Subject: [PATCH] Add migration script for collectionobject fieldCollectionPlaces (#402) --- .../8.0.0/post-init/01_collectionobject.sql | 33 +++++++++++++++++++ 1 file changed, 33 insertions(+) create mode 100644 src/main/resources/db/postgresql/upgrade/8.0.0/post-init/01_collectionobject.sql diff --git a/src/main/resources/db/postgresql/upgrade/8.0.0/post-init/01_collectionobject.sql b/src/main/resources/db/postgresql/upgrade/8.0.0/post-init/01_collectionobject.sql new file mode 100644 index 000000000..fca8c0bb0 --- /dev/null +++ b/src/main/resources/db/postgresql/upgrade/8.0.0/post-init/01_collectionobject.sql @@ -0,0 +1,33 @@ +-- Upgrade collectionobject. Move fieldCollectionPlace into repeating field (DRYD-1395). +DO $$ +DECLARE + trow record; + maxpos int; +BEGIN + -- For new install, if collectionobjects_common.fieldcollectionplace does not exist, there is nothing to migrate. + IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='collectionobjects_common' AND column_name='fieldcollectionplace') THEN + FOR trow IN + -- Get record in collectionobjects_common that does not have an existing/matching record in + -- collectionobjects_common_fieldcollectionplaces: + SELECT co.id, co.fieldcollectionplace + FROM public.collectionobjects_common co + LEFT OUTER JOIN public.collectionobjects_common_fieldcollectionplaces co_fcp ON + (co.id = co_fcp.id AND co.fieldcollectionplace = co_fcp.item) + WHERE co.fieldcollectionplace IS NOT NULL AND co.fieldcollectionplace != '' AND co_fcp.item IS NULL + + LOOP + -- Get max pos value for the collectionobject record's field collection place field: + SELECT coalesce(max(pos), -1) INTO maxpos + FROM public.collectionobjects_common_fieldcollectionplaces + WHERE id = trow.id; + + -- Migrate collectionobjects_common fieldcollectionplace data to + -- collectionobjects_common_fieldcollectionplaces table: + INSERT INTO public.collectionobjects_common_fieldcollectionplaces(id, pos, item) + VALUES (trow.id, maxpos + 1, trow.fieldcollectionplace); + END LOOP; + ELSE + RAISE NOTICE 'No v7.2 collectionobject field collection place data to migrate: collectionobjects_common.fieldcollectionplace does not exist'; + END IF; +END +$$; -- 2.47.3