From 8e8b830be499dc3989c13e669f976ba63617b0f9 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 15 Jan 2014 17:22:27 -0500 Subject: [PATCH] Teach the upgrade script to convert old data Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/upgrade/XXXX.schema.MVF.sql | 60 ++++++++++++++++++------- 1 file changed, 43 insertions(+), 17 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.MVF.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.MVF.sql index 0eee625736..7756f8d0e5 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.MVF.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.MVF.sql @@ -70,23 +70,6 @@ CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr); CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value); -DROP TABLE metabib.record_attr; - -CREATE TYPE metbib.record_attr_type AS ( - id BIGINT, - attrs HSTORE -); - --- Back-compat view ... we're moving to an INTARRAY world -CREATE VIEW metabib.record_attr AS - SELECT v.source AS id, - hstore( ARRAY_AGG( ARRAY[ COALESCE(c.ctype,u.attr), COALESCE(c.value,u.value) ] ) ) - FROM metabib.record_attr_vector_list v - LEFT JOIN metabib.uncontrolled_record_attr_value u ON ( u.id = ANY( v.vlist ) ) - LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) - WHERE c.id IS NOT NULL OR u.id IS NOT NULL - GROUP BY 1; - CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$ DECLARE rtype TEXT; @@ -379,5 +362,48 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +CREATE TEMP UNLOGGED TABLE attr_set ON COMMIT DROP AS SELECT DISTINCT source, (each(attrs)).key,(each(attrs)).value FROM metabib.record_attr; + +-- Grab sort values for the new sorting mechanism +INSERT INTO metabib.record_sorter (source,attr,value) + SELECT a.source, a.key, a.value + FROM attr_set a + JOIN config.record_attr_defintion d ON (d.name = a.key AND d.sorter); + +-- Rewrite uncontrolled SVF record attrs as the seeds of an intarray vector +INSERT INTO metabib.uncontrolled_record_attr_value (attr,value) + SELECT DISTINCT a.key, a.value + FROM attr_set a + JOIN config.record_attr_defintion d ON (d.name = a.key AND d.filter); + LEFT JOIN config.coded_value_map m ON (m.ctype = a.key) + WHERE m.id IS NULL; + +-- Now construct the record-specific vector from the SVF data +INSERT INTO metabib.record_attr_vector_list (source,vlist) + SELECT a.source, ARRAY_AGG(COALESCE(u.id, c.id)) + FROM metabib.record_attr a + JOIN attr_set USING (source) + LEFT JOIN metabib.uncontrolled_record_attr_value u ON (u.attr = attr_set.key) + LEFT JOIN config.coded_value_map c ON (c.ctype = attr_set.key) + WHERE COALESCE(u.id,c.id) IS NOT NULL + GROUP BY 1; + +DROP TABLE metabib.record_attr; + +CREATE TYPE metbib.record_attr_type AS ( + id BIGINT, + attrs HSTORE +); + +-- Back-compat view ... we're moving to an INTARRAY world +CREATE VIEW metabib.record_attr AS + SELECT v.source AS id, + hstore( ARRAY_AGG( ARRAY[ COALESCE(c.ctype,u.attr), COALESCE(c.value,u.value) ] ) ) + FROM metabib.record_attr_vector_list v + LEFT JOIN metabib.uncontrolled_record_attr_value u ON ( u.id = ANY( v.vlist ) ) + LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + WHERE c.id IS NOT NULL OR u.id IS NOT NULL + GROUP BY 1; + COMMIT; -- 2.11.0