3 INSERT INTO config.upgrade_log (version) VALUES ('0482');
5 -- Drop old (non-functional) constraints
7 ALTER TABLE config.circ_matrix_matchpoint
8 DROP CONSTRAINT ep_once_per_grp_loc_mod_marc;
10 ALTER TABLE config.hold_matrix_matchpoint
11 DROP CONSTRAINT hous_once_per_grp_loc_mod_marc;
13 -- Clean up tables before making normalized index
15 CREATE OR REPLACE FUNCTION action.cleanup_matrix_matchpoints() RETURNS void AS $func$
21 SELECT org_unit, grp, circ_modifier, marc_type, marc_form, marc_vr_format, copy_circ_lib, copy_owning_lib, user_home_ou, ref_flag, juvenile_flag, is_renewal, usr_age_lower_bound, usr_age_upper_bound, COUNT(id) as rowcount, MIN(id) as firstrow
22 FROM config.circ_matrix_matchpoint
24 GROUP BY org_unit, grp, circ_modifier, marc_type, marc_form, marc_vr_format, copy_circ_lib, copy_owning_lib, user_home_ou, ref_flag, juvenile_flag, is_renewal, usr_age_lower_bound, usr_age_upper_bound
25 HAVING COUNT(id) > 1 LOOP
27 UPDATE config.circ_matrix_matchpoint SET active=false
28 WHERE id > temp_row.firstrow
29 AND org_unit = temp_row.org_unit
30 AND grp = temp_row.grp
31 AND circ_modifier IS NOT DISTINCT FROM temp_row.circ_modifier
32 AND marc_type IS NOT DISTINCT FROM temp_row.marc_type
33 AND marc_form IS NOT DISTINCT FROM temp_row.marc_form
34 AND marc_vr_format IS NOT DISTINCT FROM temp_row.marc_vr_format
35 AND copy_circ_lib IS NOT DISTINCT FROM temp_row.copy_circ_lib
36 AND copy_owning_lib IS NOT DISTINCT FROM temp_row.copy_owning_lib
37 AND user_home_ou IS NOT DISTINCT FROM temp_row.user_home_ou
38 AND ref_flag IS NOT DISTINCT FROM temp_row.ref_flag
39 AND juvenile_flag IS NOT DISTINCT FROM temp_row.juvenile_flag
40 AND is_renewal IS NOT DISTINCT FROM temp_row.is_renewal
41 AND usr_age_lower_bound IS NOT DISTINCT FROM temp_row.usr_age_lower_bound
42 AND usr_age_upper_bound IS NOT DISTINCT FROM temp_row.usr_age_upper_bound;
47 SELECT user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, usr_grp, requestor_grp, circ_modifier, marc_type, marc_form, marc_vr_format, juvenile_flag, ref_flag, COUNT(id) as rowcount, MIN(id) as firstrow
48 FROM config.hold_matrix_matchpoint
50 GROUP BY user_home_ou, request_ou, pickup_ou, item_owning_ou, item_circ_ou, usr_grp, requestor_grp, circ_modifier, marc_type, marc_form, marc_vr_format, juvenile_flag, ref_flag
51 HAVING COUNT(id) > 1 LOOP
53 UPDATE config.hold_matrix_matchpoint SET active=false
54 WHERE id > temp_row.firstrow
55 AND user_home_ou IS NOT DISTINCT FROM temp_row.user_home_ou
56 AND request_ou IS NOT DISTINCT FROM temp_row.request_ou
57 AND pickup_ou IS NOT DISTINCT FROM temp_row.pickup_ou
58 AND item_owning_ou IS NOT DISTINCT FROM temp_row.item_owning_ou
59 AND item_circ_ou IS NOT DISTINCT FROM temp_row.item_circ_ou
60 AND usr_grp IS NOT DISTINCT FROM temp_row.usr_grp
61 AND requestor_grp IS NOT DISTINCT FROM temp_row.requestor_grp
62 AND circ_modifier IS NOT DISTINCT FROM temp_row.circ_modifier
63 AND marc_type IS NOT DISTINCT FROM temp_row.marc_type
64 AND marc_form IS NOT DISTINCT FROM temp_row.marc_form
65 AND marc_vr_format IS NOT DISTINCT FROM temp_row.marc_vr_format
66 AND juvenile_flag IS NOT DISTINCT FROM temp_row.juvenile_flag
67 AND ref_flag IS NOT DISTINCT FROM temp_row.ref_flag;
70 $func$ LANGUAGE plpgsql;
72 SELECT action.cleanup_matrix_matchpoints();
74 DROP FUNCTION IF EXISTS action.cleanup_matrix_matchpoints();
76 -- Create Normalized indexes
78 CREATE UNIQUE INDEX ccmm_once_per_paramset ON config.circ_matrix_matchpoint (org_unit, grp, COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_vr_format, ''), COALESCE(copy_circ_lib::TEXT, ''), COALESCE(copy_owning_lib::TEXT, ''), COALESCE(user_home_ou::TEXT, ''), COALESCE(ref_flag::TEXT, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(is_renewal::TEXT, ''), COALESCE(usr_age_lower_bound::TEXT, ''), COALESCE(usr_age_upper_bound::TEXT, '')) WHERE active;
80 CREATE UNIQUE INDEX chmm_once_per_paramset ON config.hold_matrix_matchpoint (COALESCE(user_home_ou::TEXT, ''), COALESCE(request_ou::TEXT, ''), COALESCE(pickup_ou::TEXT, ''), COALESCE(item_owning_ou::TEXT, ''), COALESCE(item_circ_ou::TEXT, ''), COALESCE(usr_grp::TEXT, ''), COALESCE(requestor_grp::TEXT, ''), COALESCE(circ_modifier, ''), COALESCE(marc_type, ''), COALESCE(marc_form, ''), COALESCE(marc_vr_format, ''), COALESCE(juvenile_flag::TEXT, ''), COALESCE(ref_flag::TEXT, '')) WHERE active;