From 9b4693f07a26eed41dfdd98cbc8a59a1a6070962 Mon Sep 17 00:00:00 2001
From: Bill Erickson <berick@esilibrary.com>
Date: Thu, 22 Sep 2011 11:10:09 -0400
Subject: [PATCH] Stamped upgrade script for ACQ fund view repairs
At the heart of this change is the need to force acq.fund_debit_total to
return exactly 1 row per fund, instead of 1 per fund + encumbrance
value. However, such a change required rearranging a number of
dependent views.
Also added acq.fund_spent_balance to the set of views that needs
dropping and re-building.
Minor SQL format change to match surrounding code in schema file
See also https://bugs.launchpad.net/evergreen/+bug/800477
Signed-off-by: Bill Erickson <berick@esilibrary.com>
---
Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +-
Open-ILS/src/sql/Pg/200.schema.acq.sql | 32 ++++++-------
.../upgrade/0628.schema.acq_fund_view_repairs.sql | 52 ++++++++++++++++++++++
.../src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 | 34 --------------
4 files changed, 69 insertions(+), 51 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0628.schema.acq_fund_view_repairs.sql
delete mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index e9ef912027..67fe0e897b 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -86,7 +86,7 @@ CREATE TRIGGER no_overlapping_deps
BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
-INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0626', :eg_version); -- senator
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0628', :eg_version); -- jamesrf/berick
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index 9276248fdf..8765c4a659 100644
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -2460,33 +2460,33 @@ CREATE OR REPLACE VIEW acq.fund_allocation_total AS
GROUP BY 1;
CREATE OR REPLACE VIEW acq.fund_debit_total AS
- SELECT fund.id AS fund,
- SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ SELECT fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
GROUP BY fund.id;
CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
- SELECT fund.id AS fund,
- SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ SELECT
+ fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
- WHERE fund_debit.encumbrance
- GROUP BY fund.id;
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ WHERE fund_debit.encumbrance GROUP BY fund.id;
CREATE OR REPLACE VIEW acq.fund_spent_total AS
- SELECT fund.id AS fund,
- SUM(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ SELECT fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
- WHERE NOT fund_debit.encumbrance
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ WHERE NOT fund_debit.encumbrance
GROUP BY fund.id;
CREATE OR REPLACE VIEW acq.fund_combined_balance AS
- SELECT c.fund,
- c.amount - COALESCE(d.amount,0.0) AS amount
- FROM acq.fund_allocation_total c
- LEFT JOIN acq.fund_debit_total d USING (fund);
+ SELECT c.fund,
+ c.amount - COALESCE(d.amount, 0.0) AS amount
+ FROM acq.fund_allocation_total c
+ LEFT JOIN acq.fund_debit_total d USING (fund);
CREATE OR REPLACE VIEW acq.fund_spent_balance AS
SELECT c.fund,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0628.schema.acq_fund_view_repairs.sql b/Open-ILS/src/sql/Pg/upgrade/0628.schema.acq_fund_view_repairs.sql
new file mode 100644
index 0000000000..63204248ab
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0628.schema.acq_fund_view_repairs.sql
@@ -0,0 +1,52 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('0628', :eg_version);
+
+-- acq.fund_combined_balance and acq.fund_spent_balance are unchanged,
+-- however we need to drop them to recreate the other views.
+-- we need to drop all our views because we change the number of columns
+-- for example, debit_total does not need an encumberance column when we
+-- have a sepearate total for that.
+
+DROP VIEW acq.fund_spent_balance;
+DROP VIEW acq.fund_combined_balance;
+DROP VIEW acq.fund_encumbrance_total;
+DROP VIEW acq.fund_spent_total;
+DROP VIEW acq.fund_debit_total;
+
+CREATE OR REPLACE VIEW acq.fund_debit_total AS
+ SELECT fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ FROM acq.fund fund
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ GROUP BY fund.id;
+
+CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
+ SELECT
+ fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ FROM acq.fund fund
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ WHERE fund_debit.encumbrance GROUP BY fund.id;
+
+CREATE OR REPLACE VIEW acq.fund_spent_total AS
+ SELECT fund.id AS fund,
+ sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
+ FROM acq.fund fund
+ LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
+ WHERE NOT fund_debit.encumbrance
+ GROUP BY fund.id;
+
+CREATE OR REPLACE VIEW acq.fund_combined_balance AS
+ SELECT c.fund,
+ c.amount - COALESCE(d.amount, 0.0) AS amount
+ FROM acq.fund_allocation_total c
+ LEFT JOIN acq.fund_debit_total d USING (fund);
+
+CREATE OR REPLACE VIEW acq.fund_spent_balance AS
+ SELECT c.fund,
+ c.amount - COALESCE(d.amount,0.0) AS amount
+ FROM acq.fund_allocation_total c
+ LEFT JOIN acq.fund_spent_total d USING (fund);
+
+COMMIT;
diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477 b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477
deleted file mode 100644
index dc21cd0373..0000000000
--- a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.acq_lp800477
+++ /dev/null
@@ -1,34 +0,0 @@
-BEGIN;
-
-INSERT INTO config.upgrade_log (version, install_date) VALUES ('XXXXXXX',now());
-
--- acq.fund_combined_balance is unchanged however we need to drop it to recreate the other views
--- we need to drop all our views because we change the number of columns
--- for example, debit_total does not need an encumberance column when we have a sepearate total for that
-
-DROP VIEW acq.fund_combined_balance;
-DROP VIEW acq.fund_encumbrance_total;
-DROP VIEW acq.fund_spent_total;
-DROP VIEW acq.fund_debit_total;
-
-CREATE OR REPLACE VIEW acq.fund_debit_total AS
- SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount
- FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund
- GROUP BY fund.id;
-
-CREATE OR REPLACE VIEW acq.fund_encumbrance_total AS
-SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE fund_debit.encumbrance GROUP BY fund.id;
-
-CREATE OR REPLACE VIEW acq.fund_spent_total AS
-SELECT fund.id AS fund, sum(COALESCE(fund_debit.amount, 0::numeric)) AS amount FROM acq.fund fund
- LEFT JOIN acq.fund_debit fund_debit ON fund.id = fund_debit.fund WHERE NOT fund_debit.encumbrance GROUP BY fund.id;
-
-CREATE OR REPLACE VIEW acq.fund_combined_balance AS
- SELECT c.fund, c.amount - COALESCE(d.amount, 0.0) AS amount
- FROM acq.fund_allocation_total c
- LEFT JOIN acq.fund_debit_total d USING (fund);
-
-
-COMMIT;
--
2.11.0