From a2f85d61ade81c43ae7c9b7a3f198e4aa7233fd2 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Thu, 30 May 2019 16:24:25 -0400 Subject: [PATCH 1/1] migration_tools.is_circ_rule_safe_to_delete --- sql/base/base.sql | 152 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 152 insertions(+), 0 deletions(-) diff --git a/sql/base/base.sql b/sql/base/base.sql index 46d221a..e9c993f 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -5457,3 +5457,155 @@ BEGIN RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count; END $function$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$ +-- WARNING: Use at your own risk +-- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age +DECLARE + item_object asset.copy%ROWTYPE; + user_object actor.usr%ROWTYPE; + test_rule_object config.circ_matrix_matchpoint%ROWTYPE; + result_rule_object config.circ_matrix_matchpoint%ROWTYPE; + safe_to_delete BOOLEAN := FALSE; + m action.found_circ_matrix_matchpoint; + result_matchpoint INTEGER; + -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] ) +BEGIN + SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint; + RAISE INFO 'testing rule: %', test_rule_object; + + INSERT INTO actor.usr ( + profile, + usrname, + passwd, + ident_type, + first_given_name, + family_name, + home_ou, + juvenile + ) SELECT + COALESCE(test_rule_object.grp, 2), + 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text, + MD5(NOW()::TEXT), + 1, + 'Ima', + 'Test', + COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit), + COALESCE(test_rule_object.juvenile_flag, FALSE) + ; + + SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq'); + + INSERT INTO asset.call_number ( + creator, + editor, + record, + owning_lib, + label, + label_class + ) SELECT + 1, + 1, + -1, + COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit), + 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text, + 1 + ; + + INSERT INTO asset.copy ( + barcode, + circ_lib, + creator, + call_number, + editor, + location, + loan_duration, + fine_level, + ref, + circ_modifier + ) SELECT + 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text, + COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit), + 1, + currval('asset.call_number_id_seq'), + 1, + COALESCE(test_rule_object.copy_location,1), + 2, + 2, + COALESCE(test_rule_object.ref_flag,FALSE), + test_rule_object.circ_modifier + ; + + SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq'); + + SELECT INTO m * FROM action.find_circ_matrix_matchpoint( + test_rule_object.org_unit, + item_object, + user_object, + COALESCE(test_rule_object.is_renewal,FALSE) + ); + RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)', + test_rule_object.org_unit, + item_object.id, + user_object.id, + COALESCE(test_rule_object.is_renewal,FALSE), + m.success, + (m.matchpoint).id, + m.buildrows + ; + + FOR result_matchpoint IN SELECT UNNEST(m.buildrows) + LOOP + SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint; + RAISE INFO 'considering rule: %', result_rule_object; + IF result_rule_object.id = test_rule_object.id THEN + RAISE INFO 'found self'; + CONTINUE; + END IF; + IF (result_rule_object.circulate = test_rule_object.circulate + AND result_rule_object.duration_rule = test_rule_object.duration_rule + AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule + AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule + AND ( + (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL) + OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date) + OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL) + ) + AND ( + (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL) + OR (result_rule_object.renewals = test_rule_object.renewals) + OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL) + ) + AND ( + (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL) + OR (result_rule_object.grace_period = test_rule_object.grace_period) + OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL) + ) + AND NOT EXISTS ( + SELECT limit_set, fallthrough + FROM config.circ_matrix_limit_set_map + WHERE active and matchpoint = test_rule_object.id + EXCEPT + SELECT limit_set, fallthrough + FROM config.circ_matrix_limit_set_map + WHERE active and matchpoint = result_rule_object.id + ) + ) THEN + RAISE INFO 'rule has same outcome'; + safe_to_delete := TRUE; + ELSE + RAISE INFO 'rule has different outcome, bail now'; + RAISE EXCEPTION 'rollback the item and user tables'; + END IF; + END LOOP; + + RAISE EXCEPTION 'rollback the item and user tables'; + +EXCEPTION WHEN OTHERS THEN + + RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM; + RETURN safe_to_delete; + +END; +$func$ LANGUAGE plpgsql; + -- 1.7.2.5