From bc4da93590220fb8d5729c6bc8c2a5f9ef467248 Mon Sep 17 00:00:00 2001
From: Galen Charlton <gmc@esilibrary.com>
Date: Thu, 11 Aug 2016 10:41:29 -0400
Subject: [PATCH] LP#1599634: change ID column in view to BIGINT

This patches 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

This patch also renames the schema upgrade script to better
match conventions.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>
---
 Open-ILS/examples/fm_IDL.xml                                   |  2 +-
 Open-ILS/src/sql/Pg/090.schema.action.sql                      | 10 +++++-----
 ...sql => XXXX.schema.all_circulation_combined_types_view.sql} | 10 +++++-----
 3 files changed, 11 insertions(+), 11 deletions(-)
 rename Open-ILS/src/sql/Pg/upgrade/{XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.sql => XXXX.schema.all_circulation_combined_types_view.sql} (87%)

diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml
index 4a3f25d86a..148ab9b368 100644
--- a/Open-ILS/examples/fm_IDL.xml
+++ b/Open-ILS/examples/fm_IDL.xml
@@ -4212,7 +4212,7 @@ SELECT  usr,
 		<fields>
 			<field reporter:label="Circulating Library" name="circ_lib" reporter:datatype="org_unit"/>
 			<field reporter:label="Circulating Staff" name="circ_staff" reporter:datatype="link"/>
-			<field reporter:label="Circ ID" name="id" reporter:datatype="text" />
+			<field reporter:label="Circ ID" name="id" reporter:datatype="int" />
 			<field reporter:label="Checkout Date/Time" name="xact_start" reporter:datatype="timestamp" />
 			<field reporter:label="Create Date/Time" name="create_time" reporter:datatype="timestamp" />
 			<field reporter:label="Circulation Type" name="circ_type" reporter:datatype="text"/>
diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql
index 9e5a63d974..f42dcc10bd 100644
--- a/Open-ILS/src/sql/Pg/090.schema.action.sql
+++ b/Open-ILS/src/sql/Pg/090.schema.action.sql
@@ -1403,7 +1403,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,
@@ -1414,7 +1414,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,
@@ -1425,7 +1425,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,
@@ -1436,7 +1436,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,
@@ -1447,7 +1447,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.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql
similarity index 87%
rename from Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.sql
rename to Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql
index 76061cc486..e76686b9a4 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.LP1599634_Circulation_report_source_to_include_in-house_and_non_cat_circ.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.all_circulation_combined_types_view.sql
@@ -3,7 +3,7 @@ BEGIN;
 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
 
 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,
@@ -14,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,
@@ -25,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,
@@ -36,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,
@@ -47,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,
-- 
2.11.0