From 3cc2edc8e86824d4ba126558afcbfed8b6000505 Mon Sep 17 00:00:00 2001 From: Thomas Berezansky Date: Mon, 8 Dec 2014 10:53:57 -0500 Subject: [PATCH] Fix metabib.record_attr_flat view When using LEFT joins other views error out due to null fields in HSTORE, causing failures in circ amongst other things. Signed-off-by: Thomas Berezansky --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 4 ++-- .../src/sql/Pg/upgrade/XXXX.schema.fix_record_attr_flat.sql | 12 ++++++++++++ 2 files changed, 14 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_record_attr_flat.sql diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index c03868ed36..9769f95969 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -412,13 +412,13 @@ CREATE VIEW metabib.record_attr_flat AS m.attr AS attr, m.value AS value FROM metabib.record_attr_vector_list v - LEFT JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) UNION SELECT v.source AS id, c.ctype AS attr, c.code AS value FROM metabib.record_attr_vector_list v - LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); CREATE VIEW metabib.record_attr AS SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_record_attr_flat.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_record_attr_flat.sql new file mode 100644 index 0000000000..6a6a42e11c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_record_attr_flat.sql @@ -0,0 +1,12 @@ +CREATE OR REPLACE VIEW metabib.record_attr_flat AS + SELECT v.source AS id, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + UNION + SELECT v.source AS id, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); -- 2.11.0