Poppy list migration script

Background

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 these fields:

item_level_call_number_typeid
item_effective_call_number_typeid
item_effective_library_id
item_effective_location_id
item_material_type_id
item_permanent_location_id
item_temporary_location_id
item_material_type_id
loan_checkin_servicepoint_id
loan_checkout_servicepoint_id
loan_policy_id
user_patron_group_id

These fields have corresponding name fields, which are what users actually see. In Quesnelia, FQM (the system used to provide data for Lists) was updated to handle these fields differently, so that queries use the form e.g., loan_policy_name == 7ed9ce32-7cf8-4d14-9223-d2ac17356529 instead of the old loan_policy_id == 7ed9ce32-7cf8-4d14-9223-d2ac17356529 form. The old form still works in the back-end, but the UI now expects the newer form. The Lists app stores queries as part of its list definitions, so old lists with queries that use those fields can no longer be updated through the Lists UI. This migration script addresses the problem my replacing the “ID” field names with the “name” field names.

Should I run the script?

Consider running the migration script if the following are all true:

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)

You can use this query to determine if any lists in a tenant are impacted (make sure to set the tenant ID first!).

-- Replace <tenant ID> with the relevant tenant ID here!
SET search_path TO '<tenant ID>_mod_lists', public;

SELECT *
FROM list_details
WHERE fql_query ~* '\m(item_level_call_number_typeid|item_effective_call_number_typeid|item_effective_library_id|item_effective_location_id|item_material_type_id|item_permanent_location_id|item_temporary_location_id|item_material_type_id|loan_checkin_servicepoint_id|loan_checkout_servicepoint_id|loan_policy_id|user_patron_group_id)\M'

This query returns the list of lists that were created in Poppy that are broken in future releases; you should save. If it returns any results, then you should consider running the migration script below. If only a few lists are affected, consider asking the owning users to recreate the lists manually. If you decide to use this script to migrate lists,

Benefits

No action is needed by users
Fast
Simple
Uses list versioning for added safety

Risks

Requires direct DB access and modifying data directly in the DB, which is always at least a little risky
The migration script fixes the problem with simple string substitution in JSON, without regard to the syntax of the JSON or semantics of the lists being modified. As a result, it’s possible (albeit very unlikely) that things like list names and descriptions could change (e.g., a list has something like loan_policy_id in its name would end up with loan_policy_name in its name instead)
The new form of lists is not backward-compatible with Poppy, so after the migration, you can’t go back easily.

Migration script

Usage

Set the tenant ID in the search path, below
Run the script. This should only take a few seconds

For any affected lists, these are the expected results:

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.

Optionally, you can leave off the COMMIT statement at the end, verify that only the impacted lists are affected, and then either COMMIT (if satisfied) or ROLLBACK (if not) when done.

The script

-- 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;

What if something goes wrong?

If you get an error related to gen_random_uuid() not being a valid function, then run this, and try again:
SET search_path TO public; CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Other issues:

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 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.