From 04f85e57f91b71c1c498fc201d1b722d173d4bc8 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 26 Sep 2009 16:25:45 +0000 Subject: [PATCH] Fix targetting trac ticket #70 -- populate the xact_type column in transaction summary materialization trigger git-svn-id: svn://svn.open-ils.org/ILS/trunk@14173 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 +- .../src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql | 8 +++---- ...alized_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 c28fb1cc7c..7654ab9e3d 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 ('0024'); -- phasefx +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 83b5859554..1923e69256 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -258,8 +258,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; @@ -284,7 +284,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 595ce5a69e..402ac44856 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -148,7 +148,7 @@ CREATE INDEX circ_checkin_staff_idx ON action.circulation ( checkin_staff ); CREATE UNIQUE INDEX circ_parent_idx ON action.circulation ( parent_circ ) WHERE parent_circ IS NOT 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.43.2