From 646dcac7a31396e3a40b38748b02ff3f94554512 Mon Sep 17 00:00:00 2001 From: scottmk Date: Fri, 24 Sep 2010 00:56:21 +0000 Subject: [PATCH] Apply some fixes that were earlier applied to the base installation script 090.schema.action.sql, but not in an upgrade script. Also: correct a typo (INTEVAL -> INTERVAL). Hence this upgrade needs to go to v2.0 as well as to trunk. M Open-ILS/src/sql/Pg/090.schema.action.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@17944 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 4 +- .../0418.function.action.fix-purge-circ.sql | 99 +++++++++++++++++++ 3 files changed, 102 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 65bcaba392..d6e3e80e57 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0417'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0418'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 49db69621a..76f7c0ba51 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -778,7 +778,7 @@ BEGIN SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; EXIT WHEN circ_chain_tail.xact_finish IS NULL; - -- Now get the user setings, if any, to block purging if the user wants to keep more circs + -- Now get the user settings, if any, to block purging if the user wants to keep more circs usr_keep_age.value := NULL; SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; @@ -794,7 +794,7 @@ BEGIN ELSIF usr_keep_start.value IS NOT NULL THEN keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); ELSE - keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTEVAL ); + keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL ); END IF; EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age; diff --git a/Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql b/Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql new file mode 100644 index 0000000000..6cf5374ee5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0418.function.action.fix-purge-circ.sql @@ -0,0 +1,99 @@ +-- Apply in an update script some fixes that were previously applied only +-- to the base installation script 090.schema.action.sql. + +-- Also fix a typo: INTEVAL -> INTERVAL + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0418'); -- Scott McKellar + +CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$ +DECLARE + usr_keep_age actor.usr_setting%ROWTYPE; + usr_keep_start actor.usr_setting%ROWTYPE; + org_keep_age INTERVAL; + org_keep_count INT; + + keep_age INTERVAL; + + target_acp RECORD; + circ_chain_head action.circulation%ROWTYPE; + circ_chain_tail action.circulation%ROWTYPE; + + purge_position INT; + count_purged INT; +BEGIN + + count_purged := 0; + + SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; + + SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; + IF org_keep_count IS NULL THEN + RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever + END IF; + + -- First, find copies with more than keep_count non-renewal circs + FOR target_acp IN + SELECT target_copy, + COUNT(*) AS total_real_circs + FROM action.circulation + WHERE parent_circ IS NULL + AND xact_finish IS NOT NULL + GROUP BY target_copy + HAVING COUNT(*) > org_keep_count + LOOP + purge_position := 0; + -- And, for those, select circs that are finished and older than keep_age + FOR circ_chain_head IN + SELECT * + FROM action.circulation + WHERE target_copy = target_acp.target_copy + AND parent_circ IS NULL + ORDER BY xact_start + LOOP + + -- Stop once we've purged enough circs to hit org_keep_count + EXIT WHEN target_acp.total_real_circs - purge_position <= org_keep_count; + + SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; + EXIT WHEN circ_chain_tail.xact_finish IS NULL; + + -- Now get the user settings, if any, to block purging if the user wants to keep more circs + usr_keep_age.value := NULL; + SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; + + usr_keep_start.value := NULL; + SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; + + IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN + IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; + END IF; + ELSIF usr_keep_start.value IS NOT NULL THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := COALESCE( org_keep_age::INTERVAL, '2000 years'::INTERVAL ); + END IF; + + EXIT WHEN AGE(NOW(), circ_chain_tail.xact_finish) < keep_age; + + -- We've passed the purging tests, purge the circ chain starting at the end + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + WHILE circ_chain_tail.parent_circ IS NOT NULL LOOP + SELECT * INTO circ_chain_tail FROM action.circulation WHERE id = circ_chain_tail.parent_circ; + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + END LOOP; + + count_purged := count_purged + 1; + purge_position := purge_position + 1; + + END LOOP; + END LOOP; +END; +$func$ LANGUAGE PLPGSQL; + + +COMMIT; -- 2.43.2