Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Is this necessary?

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

Code Block
languagesql
-- 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'

Usage

  1. Set the tenant ID in the search path, below

  2. Run the script. This should only take a few seconds

...

If you get an error with gen_random_uuid(), run this, then try again:
SET search_path TO public; CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Migration script

Code Block
breakoutModefull-width
languagesql
-- 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;