Popp
/* Poppy list migration There was a breaking change between P and Q, where certain fields in queries need to be fixed in order to make the lists editable This script updates lists to account for this change. It is only needed if lists were created in Poppy that use any of the "ID" fields listed below in the "mappings" CTE Usage 1. Set the tenant ID in the search path, below 2. Run the script. This should only take a few seconds - Optionally, leave off the "COMMIT" statement at the end, to verify that the only impacted records are ones using one or more field from the "mappings" CTE below - For any affected lists: - in the list_details table: - the query in the fql_query column should use the "name" field instead of the "ID" field - the updated_date column should be set to the current time when you ran the script - the version column should be incremented from its previous value - in the list_versions table: - there should be an old record with the old version of the list, with the old values for the fql_query, updated_date, and version columns - there should be a new record for the updated list, with an updated fql_query, updated_date, and version columns - No other records should be affected. - If anything looks wrong, execute a "ROLLBACK" statement to undo all of the changes from the script - If it all looks good, execute the "COMMIT" statement to commit all of the changes to the DB If you get an error with gen_random_uuid(), run this: SET search_path TO public; CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; */ -- Replace <tenant ID> with the relevant tenant ID here! SET search_path TO '<tenant ID>_mod_lists', public; BEGIN TRANSACTION; WITH RECURSIVE mappings(id, name) AS (VALUES ('item_level_call_number_typeid', 'item_level_call_number_type_name'), ('item_effective_call_number_typeid', 'item_effective_call_number_type_name'), ('item_effective_library_id', 'item_effective_library_name'), ('item_effective_location_id', 'item_effective_location_name'), ('item_material_type_id', 'item_material_type'), ('item_permanent_location_id', 'item_permanent_location_name'), ('item_temporary_location_id', 'item_temporary_location_name'), ('item_material_type_id', 'item_material_type'), ('loan_checkin_servicepoint_id', 'loan_checkin_servicepoint_name'), ('loan_checkout_servicepoint_id', 'loan_checkout_servicepoint_name'), ('loan_policy_id', 'loan_policy_name'), ('user_patron_group_id', 'user_patron_group')), -- Create a recursive CTE to apply all mappings update_cte AS (SELECT id, fql_query AS original_fql_query, fql_query AS updated_fql_query, 0 AS iteration FROM list_details WHERE is_deleted = false UNION ALL SELECT uc.id, uc.original_fql_query, regexp_replace(uc.updated_fql_query, '\m' || m.id || '\M', m.name, 'g'), uc.iteration + 1 FROM update_cte uc JOIN mappings m ON uc.updated_fql_query LIKE '%' || m.id || '%' WHERE uc.iteration < (SELECT COUNT(*) FROM mappings)), -- Create another CTE to hold only the actual updated queries final_update AS (SELECT DISTINCT ON (id) id, updated_fql_query FROM update_cte WHERE original_fql_query != updated_fql_query ORDER BY id, iteration DESC) -- Insert updated rows into list_versions INSERT INTO list_versions SELECT gen_random_uuid() AS id, ld.name, ld.description, fu.updated_fql_query AS fql_query, ld.user_friendly_query, ld.fields, ld.updated_by, ld.updated_by_username, CURRENT_TIMESTAMP AS updated_date, ld.is_active, ld.is_private, ld.version + 1, ld.id AS list_id FROM list_details ld JOIN final_update fu ON ld.id = fu.id; -- Copy the updated data from list_versions into list_details UPDATE list_details ld SET fql_query = lv.fql_query, version = lv.version, updated_date = lv.updated_date FROM list_versions lv WHERE ld.id = lv.list_id AND ld.fql_query != lv.fql_query AND lv.version = ld.version + 1; -- ROLLBACK; COMMIT;