From 5f3add3fbcb3cc8f014472bc231f22eb9652977e Mon Sep 17 00:00:00 2001 From: scottmk Date: Thu, 29 Oct 2009 13:25:00 +0000 Subject: [PATCH] Remove the percent column from acq.fund_allocation. Make the amount column NOT NULL. Remove references to the percent column from the views acq.fund_allocation_total and acq.funding_source_allocation_total. M Open-ILS/src/sql/Pg/200.schema.acq.sql M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0061.schema.acqfa_no_percent.sql git-svn-id: svn://svn.open-ils.org/ILS/trunk@14670 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/200.schema.acq.sql | 45 +++++----------------- .../Pg/upgrade/0061.schema.acqfa_no_percent.sql | 37 ++++++++++++++++++ 3 files changed, 47 insertions(+), 37 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0061.schema.acqfa_no_percent.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 78e0577e7f..4d5e424711 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 ('0060'); -- atz +INSERT INTO config.upgrade_log (version) VALUES ('0061'); -- Scott McKellar 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 4192e76de2..5915a08050 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -130,8 +130,7 @@ CREATE TABLE acq.fund_allocation ( id SERIAL PRIMARY KEY, funding_source INT NOT NULL REFERENCES acq.funding_source (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, fund INT NOT NULL REFERENCES acq.fund (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - amount NUMERIC, - percent NUMERIC CHECK (percent IS NULL OR percent BETWEEN 0.0 AND 100.0), + amount NUMERIC NOT NULL, allocator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, note TEXT, create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), @@ -693,21 +692,9 @@ CREATE OR REPLACE VIEW acq.funding_source_credit_total AS CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS SELECT funding_source, - SUM(amount)::NUMERIC(100,2) AS amount - FROM ( - SELECT funding_source, - SUM(a.amount)::NUMERIC(100,2) AS amount - FROM acq.fund_allocation a - WHERE a.percent IS NULL - GROUP BY 1 - UNION ALL - SELECT funding_source, - SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * (a.percent/100.0) )::NUMERIC(100,2) AS amount - FROM acq.fund_allocation a - WHERE a.amount IS NULL - GROUP BY 1 - ) x - GROUP BY 1; + SUM(a.amount)::NUMERIC(100,2) AS amount + FROM acq.fund_allocation a + GROUP BY 1; CREATE OR REPLACE VIEW acq.funding_source_balance AS SELECT COALESCE(c.funding_source, a.funding_source) AS funding_source, @@ -718,25 +705,11 @@ CREATE OR REPLACE VIEW acq.funding_source_balance AS CREATE OR REPLACE VIEW acq.fund_allocation_total AS SELECT fund, - SUM(amount)::NUMERIC(100,2) AS amount - FROM ( - SELECT fund, - SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount - FROM acq.fund_allocation a - JOIN acq.fund f ON (a.fund = f.id) - JOIN acq.funding_source s ON (a.funding_source = s.id) - WHERE a.percent IS NULL - GROUP BY 1 - UNION ALL - SELECT fund, - SUM( (SELECT SUM(amount) FROM acq.funding_source_credit c WHERE c.funding_source = a.funding_source) * acq.exchange_ratio(s.currency_type, f.currency_type) * (a.percent/100.0) )::NUMERIC(100,2) AS amount - FROM acq.fund_allocation a - JOIN acq.fund f ON (a.fund = f.id) - JOIN acq.funding_source s ON (a.funding_source = s.id) - WHERE a.amount IS NULL - GROUP BY 1 - ) x - GROUP BY 1; + SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount + FROM acq.fund_allocation a + JOIN acq.fund f ON (a.fund = f.id) + JOIN acq.funding_source s ON (a.funding_source = s.id) + GROUP BY 1; CREATE OR REPLACE VIEW acq.fund_debit_total AS SELECT id AS fund, diff --git a/Open-ILS/src/sql/Pg/upgrade/0061.schema.acqfa_no_percent.sql b/Open-ILS/src/sql/Pg/upgrade/0061.schema.acqfa_no_percent.sql new file mode 100644 index 0000000000..eb3b6ae9ba --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0061.schema.acqfa_no_percent.sql @@ -0,0 +1,37 @@ +BEGIN; + +-- Script to eliminate acq.fund_allocation.percent, which has been moved to the +-- acq.fund_allocation_percent table. + +INSERT INTO config.upgrade_log (version) VALUES ('0061'); -- Scott McKellar + +-- If the following step fails, it's probably because there are still some non-null percent values in +-- acq.fund_allocation. They should have all been converted to amounts, and then set to null, by a +-- previous upgrade script, 0049.schema.acq_funding_allocation_percent.sql. If there are any non-null +-- values, then either that script didn't run, or it didn't work, or some non-null values slipped in +-- afterwards. + +-- To convert any remaining percents to amounts: create, run, and then drop the temporary stored +-- procedure acq.fund_alloc_percent_val as defined in 0049.schema.acq_funding_allocation_percent.sql. + +ALTER TABLE acq.fund_allocation +ALTER COLUMN amount SET NOT NULL; + +CREATE OR REPLACE VIEW acq.fund_allocation_total AS + SELECT fund, + SUM(a.amount * acq.exchange_ratio(s.currency_type, f.currency_type))::NUMERIC(100,2) AS amount + FROM acq.fund_allocation a + JOIN acq.fund f ON (a.fund = f.id) + JOIN acq.funding_source s ON (a.funding_source = s.id) + GROUP BY 1; + +CREATE OR REPLACE VIEW acq.funding_source_allocation_total AS + SELECT funding_source, + SUM(a.amount)::NUMERIC(100,2) AS amount + FROM acq.fund_allocation a + GROUP BY 1; + +ALTER TABLE acq.fund_allocation +DROP COLUMN percent; + +COMMIT; \ No newline at end of file -- 2.11.0