From 5f26da6220906be586d43158449d76c19559323f Mon Sep 17 00:00:00 2001 From: blake Date: Thu, 10 Nov 2016 17:06:39 -0600 Subject: [PATCH] LP1599634: follow-ups Squashed patch containing follows-ups written by Chris Sharp and Galen Charlton: * When running the upgrade script, the DROP VIEW statement resulted in an error since the view did not already exist. You could add IF EXISTS, but CREATE OR REPLACE VIEW is sufficient. * Changes the ID column to just pass through the ID from the source tables, promoted to BIGINT in some cases. This has the effect of: - removing a redundancy, as circ_type already specifies the source table - making it easier to join this view against the circulation tables when writing SQL queries * Renames the schema upgrade script to better match conventions. * Move release notes entry so that it will get picked up Signed-off-by: Chris Sharp Signed-off-by: Galen Charlton Signed-off-by: blake Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 10 +++++----- ...X.schema.all_circulation_combined_types_view.sql} | 12 +++++------- ..._source_to_include_in-house_and_non_cat_circ.adoc | 0 4 files changed, 11 insertions(+), 13 deletions(-) rename Open-ILS/src/sql/Pg/upgrade/{XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ => XXXX.schema.all_circulation_combined_types_view.sql} (85%) rename docs/{ => RELEASE_NOTES_NEXT/Reports}/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc (100%) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 50b0dc4ff5..8f358581be 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -4223,7 +4223,7 @@ SELECT usr, - + diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 3744e1562e..bed82b70cb 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -1503,7 +1503,7 @@ CREATE TRIGGER maintain_usr_circ_history_tgr FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history(); CREATE OR REPLACE VIEW action.all_circulation_combined_types AS - SELECT 'regularcirc'::text || acirc.id AS id, + SELECT acirc.id AS id, acirc.xact_start, acirc.circ_lib, acirc.circ_staff, @@ -1514,7 +1514,7 @@ CREATE OR REPLACE VIEW action.all_circulation_combined_types AS asset.copy ac_acirc WHERE acirc.target_copy = ac_acirc.id UNION ALL - SELECT 'noncatcirc'::text || ancc.id AS id, + SELECT ancc.id::BIGINT AS id, ancc.circ_time AS xact_start, ancc.circ_lib, ancc.staff AS circ_staff, @@ -1525,7 +1525,7 @@ UNION ALL config.non_cataloged_type cnct_ancc WHERE ancc.item_type = cnct_ancc.id UNION ALL - SELECT 'inhouseuse'::text || aihu.id AS id, + SELECT aihu.id::BIGINT AS id, aihu.use_time AS xact_start, aihu.org_unit AS circ_lib, aihu.staff AS circ_staff, @@ -1536,7 +1536,7 @@ UNION ALL asset.copy ac_aihu WHERE aihu.item = ac_aihu.id UNION ALL - SELECT 'noncatinhouseuse'::text || ancihu.id AS id, + SELECT ancihu.id::BIGINT AS id, ancihu.use_time AS xact_start, ancihu.org_unit AS circ_lib, ancihu.staff AS circ_staff, @@ -1547,7 +1547,7 @@ UNION ALL config.non_cataloged_type cnct_ancihu WHERE ancihu.item_type = cnct_ancihu.id UNION ALL - SELECT 'agedcirc'::text || aacirc.id::text AS id, + SELECT aacirc.id AS id, aacirc.xact_start, aacirc.circ_lib, aacirc.circ_staff, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql similarity index 85% rename from Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ rename to Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql index 8042c32740..e76686b9a4 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql @@ -2,10 +2,8 @@ BEGIN; SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); -DROP VIEW action.all_circulation_combined_types; - CREATE OR REPLACE VIEW action.all_circulation_combined_types AS - SELECT 'regularcirc'::text || acirc.id AS id, + SELECT acirc.id AS id, acirc.xact_start, acirc.circ_lib, acirc.circ_staff, @@ -16,7 +14,7 @@ CREATE OR REPLACE VIEW action.all_circulation_combined_types AS asset.copy ac_acirc WHERE acirc.target_copy = ac_acirc.id UNION ALL - SELECT 'noncatcirc'::text || ancc.id AS id, + SELECT ancc.id::BIGINT AS id, ancc.circ_time AS xact_start, ancc.circ_lib, ancc.staff AS circ_staff, @@ -27,7 +25,7 @@ UNION ALL config.non_cataloged_type cnct_ancc WHERE ancc.item_type = cnct_ancc.id UNION ALL - SELECT 'inhouseuse'::text || aihu.id AS id, + SELECT aihu.id::BIGINT AS id, aihu.use_time AS xact_start, aihu.org_unit AS circ_lib, aihu.staff AS circ_staff, @@ -38,7 +36,7 @@ UNION ALL asset.copy ac_aihu WHERE aihu.item = ac_aihu.id UNION ALL - SELECT 'noncatinhouseuse'::text || ancihu.id AS id, + SELECT ancihu.id::BIGINT AS id, ancihu.use_time AS xact_start, ancihu.org_unit AS circ_lib, ancihu.staff AS circ_staff, @@ -49,7 +47,7 @@ UNION ALL config.non_cataloged_type cnct_ancihu WHERE ancihu.item_type = cnct_ancihu.id UNION ALL - SELECT 'agedcirc'::text || aacirc.id::text AS id, + SELECT aacirc.id AS id, aacirc.xact_start, aacirc.circ_lib, aacirc.circ_staff, diff --git a/docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc b/docs/RELEASE_NOTES_NEXT/Reports/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc similarity index 100% rename from docs/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc rename to docs/RELEASE_NOTES_NEXT/Reports/lp1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.adoc -- 2.43.2