From 715b27c892248c4f3c326bbcbca57e2bed796ce7 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 14 Aug 2017 11:48:46 -0400 Subject: [PATCH] JBAS-1554 Circ history migration check script Signed-off-by: Bill Erickson --- .../schema/tools/circ-history-migrate-check.sql | 60 ++++++++++++++++++++++ 1 file changed, 60 insertions(+) create mode 100644 KCLS/sql/schema/tools/circ-history-migrate-check.sql diff --git a/KCLS/sql/schema/tools/circ-history-migrate-check.sql b/KCLS/sql/schema/tools/circ-history-migrate-check.sql new file mode 100644 index 0000000000..50965ca5d2 --- /dev/null +++ b/KCLS/sql/schema/tools/circ-history-migrate-check.sql @@ -0,0 +1,60 @@ +/** + * Scan the database and look for circ history rows that need migrating. + * + * In existing code, history is available for the whole day on the day + * where history is enabled. In the new code, history is only available + * to circs created after history is enabled (not necessarily the whole + * day). To be sure all circs from existing history are available in the + * new form once migrated, use the script to find affected patrons and + * then manually migrate each via: + * + * SET STATEMENT_TIMEOUT = 0; + * SELECT action.migrate_usr_circ_history(1, )" + * + * is reported by this script. + */ + +SET STATEMENT_TIMEOUT = 0; +DO $$ +DECLARE + cur_usr INTEGER; + cur_circ action.circulation%ROWTYPE; + usr_count INTEGER DEFAULT 0; + migrated_count INTEGER DEFAULT 0; + pending_count INTEGER DEFAULT 0; +BEGIN + FOR cur_usr IN + WITH history_users AS ( + SELECT DISTINCT(au.id) AS id + FROM actor.usr au + JOIN actor.usr_setting aus ON (aus.usr = au.id) + WHERE + NOT au.deleted + AND aus.name ~ '^history.circ.retention_' + ) SELECT users.id FROM history_users users ORDER BY users.id + LOOP + usr_count := usr_count + 1; + FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP + PERFORM TRUE FROM action.usr_circ_history + WHERE source_circ = cur_circ.id; + + IF FOUND THEN + migrated_count := migrated_count + 1; + ELSE + RAISE NOTICE 'User % [offset=%] has pending circs', cur_usr, usr_count; + pending_count := pending_count + 1; + END IF; + END LOOP; + + IF (usr_count % 10000) = 0 THEN + RAISE NOTICE 'Processed % patrons', usr_count; + RAISE NOTICE 'Pending circs: %', pending_count; + RAISE NOTICE 'Migrated circs: %', migrated_count; + END IF; + + END LOOP; + + RAISE NOTICE 'Pending circs: %', pending_count; + RAISE NOTICE 'Migrated circs: %', migrated_count; +END $$; + -- 2.11.0