From 3c04dfffd0cb27a86e8a63b2852b402b68fb04bf Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 27 Oct 2009 20:55:30 +0000 Subject: [PATCH] forward-porting r14636: correct ambiguous select including upgrade script; also, fixing thinko in previous upgrade script git-svn-id: svn://svn.open-ils.org/ILS/trunk@14639 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 2 +- ...rmission-columns-for-org-setting-types.sql | 2 +- ...9.schema.broken-circ-by-circ_mod-query.sql | 145 ++++++++++++++++++ 4 files changed, 148 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0059.schema.broken-circ-by-circ_mod-query.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 74764c1e36..414f6588b8 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0058'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0059'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index 24a5d42b2f..9eb1d5bf24 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -336,7 +336,7 @@ BEGIN FROM action.circulation circ JOIN asset.copy cp ON (cp.id = circ.target_copy) WHERE circ.usr = match_user - AND circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) + AND circ.circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) AND circ.checkin_time IS NULL AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); diff --git a/Open-ILS/src/sql/Pg/upgrade/0058.schema.permission-columns-for-org-setting-types.sql b/Open-ILS/src/sql/Pg/upgrade/0058.schema.permission-columns-for-org-setting-types.sql index 19e5383db9..6e5be10786 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0058.schema.permission-columns-for-org-setting-types.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0058.schema.permission-columns-for-org-setting-types.sql @@ -3,7 +3,7 @@ BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0058'); -- miker ALTER TABLE config.org_unit_setting_type ADD COLUMN view_perm INT REFERENCES permission.perm_list (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE config.org_unit_setting_type ADD COLUMN update_perm INT REFERENCES permission.perm_list (id) ON DELETE SET NULL CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.org_unit_setting_type ADD COLUMN update_perm INT REFERENCES permission.perm_list (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0059.schema.broken-circ-by-circ_mod-query.sql b/Open-ILS/src/sql/Pg/upgrade/0059.schema.broken-circ-by-circ_mod-query.sql new file mode 100644 index 0000000000..fad65830c7 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0059.schema.broken-circ-by-circ_mod-query.sql @@ -0,0 +1,145 @@ + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0059'); -- miker + +CREATE OR REPLACE FUNCTION action.item_user_circ_test( circ_ou INT, match_item BIGINT, match_user INT, renewal BOOL ) RETURNS SETOF action.matrix_test_result AS $func$ +DECLARE + user_object actor.usr%ROWTYPE; + standing_penalty config.standing_penalty%ROWTYPE; + item_object asset.copy%ROWTYPE; + item_status_object config.copy_status%ROWTYPE; + item_location_object asset.copy_location%ROWTYPE; + result action.matrix_test_result; + circ_test config.circ_matrix_matchpoint%ROWTYPE; + out_by_circ_mod config.circ_matrix_circ_mod_test%ROWTYPE; + circ_mod_map config.circ_matrix_circ_mod_test_map%ROWTYPE; + penalty_type TEXT; + tmp_grp INT; + items_out INT; + context_org_list INT[]; + done BOOL := FALSE; +BEGIN + result.success := TRUE; + + -- Fail if the user is BARRED + SELECT INTO user_object * FROM actor.usr WHERE id = match_user; + + -- Fail if we couldn't find a set of tests + IF user_object.id IS NULL THEN + result.fail_part := 'no_user'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + RETURN; + END IF; + + IF user_object.barred IS TRUE THEN + result.fail_part := 'actor.usr.barred'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate + SELECT INTO item_object * FROM asset.copy WHERE id = match_item; + IF item_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item isn't in a circulateable status on a non-renewal + IF NOT renewal AND item_object.status NOT IN ( 0, 7, 8 ) THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + ELSIF renewal AND item_object.status <> 1 THEN + result.fail_part := 'asset.copy.status'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the item can't circulate because of the shelving location + SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location; + IF item_location_object.circulate IS FALSE THEN + result.fail_part := 'asset.copy_location.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + SELECT INTO circ_test * FROM action.find_circ_matrix_matchpoint(circ_ou, match_item, match_user, renewal); + result.matchpoint := circ_test.id; + + SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_test.org_unit ); + + -- Fail if we couldn't find a set of tests + IF result.matchpoint IS NULL THEN + result.fail_part := 'no_matchpoint'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + -- Fail if the test is set to hard non-circulating + IF circ_test.circulate IS FALSE THEN + result.fail_part := 'config.circ_matrix_test.circulate'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + + IF renewal THEN + penalty_type = '%RENEW%'; + ELSE + penalty_type = '%CIRC%'; + END IF; + + FOR standing_penalty IN + SELECT DISTINCT csp.* + FROM actor.usr_standing_penalty usp + JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) + WHERE usr = match_user + AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND (usp.stop_date IS NULL or usp.stop_date > NOW()) + AND csp.block_list LIKE penalty_type LOOP + + result.fail_part := standing_penalty.name; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END LOOP; + + -- Fail if the user has too many items with specific circ_modifiers checked out + FOR out_by_circ_mod IN SELECT * FROM config.circ_matrix_circ_mod_test WHERE matchpoint = circ_test.id LOOP + SELECT INTO items_out COUNT(*) + FROM action.circulation circ + JOIN asset.copy cp ON (cp.id = circ.target_copy) + WHERE circ.usr = match_user + AND circ.circ_lib IN ( SELECT * FROM explode_array(context_org_list) ) + AND circ.checkin_time IS NULL + AND (circ.stop_fines IN ('MAXFINES','LONGOVERDUE') OR circ.stop_fines IS NULL) + AND cp.circ_modifier IN (SELECT circ_mod FROM config.circ_matrix_circ_mod_test_map WHERE circ_mod_test = out_by_circ_mod.id); + IF items_out >= out_by_circ_mod.items_out THEN + result.fail_part := 'config.circ_matrix_circ_mod_test'; + result.success := FALSE; + done := TRUE; + RETURN NEXT result; + END IF; + END LOOP; + + -- If we passed everything, return the successful matchpoint id + IF NOT done THEN + RETURN NEXT result; + END IF; + + RETURN; +END; +$func$ LANGUAGE plpgsql; + +COMMIT; + -- 2.43.2