From facc8f45bb9c0dc85ddcf23633daac95829e573b Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Mon, 12 Oct 2009 16:24:41 +0000
Subject: [PATCH] forward porting 14364: do not use NEW in an ON DELETE
 trigger, use OLD

git-svn-id: svn://svn.open-ils.org/ILS/trunk@14367 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.schema.config.sql          | 35 +++++++++++++++++++++-
 Open-ILS/src/sql/Pg/080.schema.money.sql           |  2 +-
 ...aterialized_billing_summmary_delete_trigger.sql | 32 ++++++++++++++++++++
 3 files changed, 67 insertions(+), 2 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0031.schema.materialized_billing_summmary_delete_trigger.sql

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 78899b8334..d4c5b05653 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 ('0030'); -- dbs
+INSERT INTO config.upgrade_log (version) VALUES ('0031'); -- miker
 
 CREATE TABLE config.bib_source (
 	id		SERIAL	PRIMARY KEY,
@@ -562,5 +562,38 @@ CREATE TABLE config.org_unit_setting_type (
       ( datatype <> 'link' AND fm_class IS NULL ) )
 );
 
+
+-- Some handy functions, based on existing ones, to provide optional ingest normalization
+
+CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
+        SELECT SUBSTRING($1,$2);
+$func$ LANGUAGE SQL STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
+        SELECT SUBSTRING($1,1,$2);
+$func$ LANGUAGE SQL STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
+        SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
+$func$ LANGUAGE SQL STRICT IMMUTABLE;
+
+-- And ... a table in which to register them
+
+CREATE TABLE config.index_normalizer (
+        id              SERIAL  PRIMARY KEY,
+        name            TEXT    UNIQUE NOT NULL,
+        func            TEXT    NOT NULL,
+        param_count     INT     NOT NULL DEFAULT 0
+);
+
+CREATE TABLE config.metabib_field_index_norm_map (
+        id      SERIAL  PRIMARY KEY,
+        field   INT     NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+        norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+        params  TEXT,
+        pos     INT     NOT NULL DEFAULT 0
+);
+
+
 COMMIT;
 
diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql
index ace19a90da..16955f95e4 100644
--- a/Open-ILS/src/sql/Pg/080.schema.money.sql
+++ b/Open-ILS/src/sql/Pg/080.schema.money.sql
@@ -374,7 +374,7 @@ BEGIN
 		  SET	last_billing_ts = prev_billing.billing_ts,
 			last_billing_note = prev_billing.note,
 			last_billing_type = prev_billing.billing_type
-		  WHERE	id = NEW.xact;
+		  WHERE	id = OLD.xact;
 	END IF;
 
 	IF NOT OLD.voided THEN
diff --git a/Open-ILS/src/sql/Pg/upgrade/0031.schema.materialized_billing_summmary_delete_trigger.sql b/Open-ILS/src/sql/Pg/upgrade/0031.schema.materialized_billing_summmary_delete_trigger.sql
new file mode 100644
index 0000000000..bdf6ded0a4
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0031.schema.materialized_billing_summmary_delete_trigger.sql
@@ -0,0 +1,32 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0031'); -- miker
+
+CREATE OR REPLACE FUNCTION money.materialized_summary_billing_del () RETURNS TRIGGER AS $$
+DECLARE
+        prev_billing    money.billing%ROWTYPE;
+        old_billing     money.billing%ROWTYPE;
+BEGIN
+        SELECT * INTO prev_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1 OFFSET 1;
+        SELECT * INTO old_billing FROM money.billing WHERE xact = OLD.xact AND NOT voided ORDER BY billing_ts DESC LIMIT 1;
+
+        IF OLD.id = old_billing.id THEN
+                UPDATE  money.materialized_billable_xact_summary
+                  SET   last_billing_ts = prev_billing.billing_ts,
+                        last_billing_note = prev_billing.note,
+                        last_billing_type = prev_billing.billing_type
+                  WHERE id = OLD.xact;
+        END IF;
+
+        IF NOT OLD.voided THEN
+                UPDATE  money.materialized_billable_xact_summary
+                  SET   total_owed = total_owed - OLD.amount,
+                        balance_owed = balance_owed + OLD.amount
+                  WHERE id = OLD.xact;
+        END IF;
+
+        RETURN OLD;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMIT;
-- 
2.11.0