Page Comparison

...

Expand

title	Manual verification for added safety (optional)

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:

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

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

Code Block

breakoutMode	full-width
language	sql

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

Versions Compared

Old Version 6

New Version 7

Key

The script