From 88ccd7fe382e37b41ba2b8a45514ac961c131145 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 26 Sep 2009 16:39:14 +0000 Subject: [PATCH] backporting r14173: Fix targetting trac ticket #70 -- populate the xact_type column in transaction summary materialization trigger git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_6@14174 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/080.schema.money.sql | 6 +++--- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +- Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql | 8 ++++---- ...aterialized_xact_summary-populate-xact_type.sql | 24 ++++++++++++++++++++++ 5 files changed, 33 insertions(+), 9 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0025.schema.materialized_xact_summary-populate-xact_type.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 9bc1e846d4..db0fa10ffd 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 ('0022'); +INSERT INTO config.upgrade_log (version) VALUES ('0025'); --miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index 4d83cfd34b..8082c41cf5 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -257,8 +257,8 @@ CREATE INDEX money_mat_summary_xact_start_idx ON money.materialized_billable_xac /* AFTER trigger only! */ CREATE OR REPLACE FUNCTION money.mat_summary_create () RETURNS TRIGGER AS $$ BEGIN - INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed) - VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0); + INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type) + VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]); RETURN NEW; END; $$ LANGUAGE PLPGSQL; @@ -283,7 +283,7 @@ BEGIN END; $$ LANGUAGE PLPGSQL; -CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create (); +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('grocery'); CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update (); CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index c1e2c6c0cd..d2e7796a31 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -126,7 +126,7 @@ CREATE INDEX circ_checkin_time ON "action".circulation (checkin_time) WHERE chec CREATE INDEX circ_circ_lib_idx ON "action".circulation (circ_lib); CREATE INDEX circ_open_date_idx ON "action".circulation (xact_start) WHERE xact_finish IS NULL; -CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create (); +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation'); CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update (); CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); diff --git a/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql index d577e72602..a0c618358b 100644 --- a/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql @@ -828,8 +828,8 @@ CREATE INDEX money_mat_summary_xact_start_idx ON money.materialized_billable_xac /* AFTER trigger only! */ CREATE OR REPLACE FUNCTION money.mat_summary_create () RETURNS TRIGGER AS $$ BEGIN - INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed) - VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0); + INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type) + VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]); RETURN NEW; END; $$ LANGUAGE PLPGSQL; @@ -854,7 +854,7 @@ BEGIN END; $$ LANGUAGE PLPGSQL; -CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create (); +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('grocery'); CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update (); CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); @@ -1096,7 +1096,7 @@ CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.credit_card_payment FOR CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.credit_card_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('credit_card_payment'); -CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create (); +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation'); CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update (); CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete (); diff --git a/Open-ILS/src/sql/Pg/upgrade/0025.schema.materialized_xact_summary-populate-xact_type.sql b/Open-ILS/src/sql/Pg/upgrade/0025.schema.materialized_xact_summary-populate-xact_type.sql new file mode 100644 index 0000000000..7745055c63 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0025.schema.materialized_xact_summary-populate-xact_type.sql @@ -0,0 +1,24 @@ + +-- Populate xact_type column in the materialized version of billable_xact_summary + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0025'); + +CREATE OR REPLACE FUNCTION money.mat_summary_create () RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO money.materialized_billable_xact_summary (id, usr, xact_start, xact_finish, total_paid, total_owed, balance_owed, xact_type) + VALUES ( NEW.id, NEW.usr, NEW.xact_start, NEW.xact_finish, 0.0, 0.0, 0.0, TG_ARGV[0]); + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + + +DROP TRIGGER mat_summary_create_tgr ON action.circulation; +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON action.circulation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('circulation'); + +DROP TRIGGER mat_summary_create_tgr ON money.grocery; +CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON money.grocery FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('grocery'); + +COMMIT; + -- 2.11.0