...
The FOLIO instance was running Poppy at one point and was updated to Quesnelia or later (ideally, the script is run on Quesnelia before upgrading further. If run on Ramsons or later, this will be handled by the built-in list migration, which is triggered automatically upon upgrading the module)
When running Poppy, the Lists app was used
Lists were created in Poppy that used any of the above ID fields
Recreating those lists manually via the UI is too much of a burden for users (e.g., there are a lot of affected lists or the lists use large/complex queries)
...
Expand | ||
---|---|---|
| ||
Optionally, you can leave off the "COMMIT" statement at the end, to verify that only the impacted lists are affected. For any affected lists, these are the expected results:
Optionally, you can leave off the |
The script
Code Block | ||||
---|---|---|---|---|
| ||||
-- 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; |
...
The migration script uses the Lists app’s versioning system to update the lists without losing any data. As a result, if a list got broken in some way, the old version of all updated lists are available in the list_versions
table (select fql_query from list_versions where list_id = '<list ‘<list ID>' and version = '<version from list_details - 1>
), to put back into the list_details
table. If the script fails before the UPDATE
statement at the end, then the old version of the list will still be used; you’ll need to delete the new version added to list_versions
before you try again.
If you need any assistance, feel free to reach out to the Corsair team (#corsair on Slack) or submit a ticket to the MODLISTS Jira project.