X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Ffixed_fields.sql;h=7ab674823567e665c508041b8fa21c25e1a02373;hp=aab4c81b6983923af4436c63b5d2dd8248972dd6;hb=f2c33d194c8fc303a7a9addda3930f04fe14c4df;hpb=6b09502678529e60056145d6d5d8955a0850b73a diff --git a/sql/base/fixed_fields.sql b/sql/base/fixed_fields.sql index aab4c81..7ab6748 100644 --- a/sql/base/fixed_fields.sql +++ b/sql/base/fixed_fields.sql @@ -1,4 +1,37 @@ -CREATE OR REPLACE FUNCTION reingest_staged_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS INTEGER[] AS $func$ +DROP TABLE IF EXISTS migration_tools.search_format_map; +CREATE TABLE migration_tools.search_format_map (code TEXT, itype CHAR(1), iform CHAR(1), phy CHAR(1), phyv CHAR(1), phyp SMALLINT, + biblevel CHAR(1), iform_exclude CHAR(1)[], srform_exclude CHAR(1)[] ); +INSERT INTO search_format_map (code, itype, iform, phy, phyv, phyp, biblevel, iform_exclude, srform_exclude) VALUES + -- itype iform phy phyv phyp bib itemform exclude sr format exclude + ('blu-ray', 'g', NULL, 'v', 's', 4, NULL, NULL, NULL) + ,('book', 'a', NULL, NULL, NULL, NULL, 'a', '{a,b,c,f,o,q,r,s}', NULL) + ,('braille', 'a', 'f', NULL, NULL, NULL, NULL, NULL, NULL) + ,('casaudiobook', 'i', NULL, 's', 'l', 3, NULL, NULL, NULL) + ,('casmusic', 'j', NULL, 's', 'l', 3, NULL, NULL, NULL) + ,('cdaudiobook', 'i', NULL, 's', 'f', 3, NULL, NULL, NULL) + ,('cdmusic', 'j', NULL, 's', 'f', 3, NULL, NULL, NULL) + ,('dvd', 'g', NULL, 'v', 'v', 4, NULL, NULL, NULL) + ,('eaudio', 'i', 'o', NULL, NULL, NULL, NULL, NULL, NULL) + ,('ebook', 'a', 's', NULL, NULL, NULL, 'a' , NULL, NULL) + ,('electronic', 's', 'o', NULL, NULL, NULL, NULL, NULL, NULL) + ,('equip', 'r', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('evideo', 'g', 'o', NULL, NULL, NULL, NULL, NULL, NULL) + ,('kit', 'o', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('lpbook', 'a', 'd', NULL, NULL, NULL, 'a' , NULL, NULL) + ,('map', 'e', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('microform', 'a', 'b', NULL, NULL, NULL, NULL, NULL, NULL) + ,('music', 'j', NULL, NULL, NULL, NULL, NULL, NULL, '{a,b,c,d,e,f}') + ,('phonomusic', 'j', NULL, 's', 'a', 3, NULL, NULL, NULL) + ,('phonospoken', 'i', NULL, 's', 'a', 3, NULL, NULL, NULL) + ,('picture', 'k', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('preloadedaudio', 'i', 'q', NULL, NULL, NULL, NULL, NULL, '{a,b,c,d,e,f,s}') + ,('score', 'c', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('serial', NULL, NULL, NULL, NULL, NULL, 's' , NULL, NULL) + ,('software', 'm', NULL, NULL, NULL, NULL, NULL, NULL, NULL) + ,('vhs', 'g', NULL, 'v', 'b', 4, NULL, NULL, NULL) +; + +CREATE OR REPLACE FUNCTION migration_tools.reingest_staged_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS INTEGER[] AS $func$ DECLARE transformed_xml TEXT; rmarc TEXT := prmarc; @@ -210,7 +243,7 @@ BEGIN $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION view_staged_vlist (rid BIGINT) RETURNS TABLE (r_ctype text, r_code text, r_value text) AS $func$ +CREATE OR REPLACE FUNCTION migration_tools.view_staged_vlist (rid BIGINT) RETURNS TABLE (r_ctype text, r_code text, r_value text) AS $func$ DECLARE search TEXT[]; icon TEXT[]; @@ -222,7 +255,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION stage_vlist (rid BIGINT) RETURNS VOID AS $func$ +CREATE OR REPLACE FUNCTION migration_tools.stage_vlist (rid BIGINT) RETURNS VOID AS $func$ DECLARE search TEXT[]; vlist INTEGER[]; @@ -236,7 +269,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION show_staged_vlist (rid BIGINT) RETURNS TEXT[] AS $func$ +CREATE OR REPLACE FUNCTION migration_tools.show_staged_vlist (rid BIGINT) RETURNS TEXT[] AS $func$ DECLARE search TEXT[]; vlist INTEGER[]; @@ -251,7 +284,7 @@ END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION postfix_vlist (rid BIGINT) RETURNS VOID AS $func$ +CREATE OR REPLACE FUNCTION migration_tools.postfix_vlist (rid BIGINT) RETURNS VOID AS $func$ DECLARE search TEXT[]; vlist INTEGER[]; @@ -266,7 +299,7 @@ END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION set_exp_sfs (rid BIGINT) RETURNS VOID AS $func$ +CREATE OR REPLACE FUNCTION migration_tools.set_exp_sfs (rid BIGINT) RETURNS VOID AS $func$ DECLARE cms TEXT[]; y TEXT; @@ -292,7 +325,7 @@ CREATE AGGREGATE anyarray_agg(anyarray) ( COMMENT ON AGGREGATE anyarray_agg(anyarray) IS 'Concatenates arrays into a single array when aggregating.'; -DROP FUNCTION IF EXISTS anyarray_agg_statefunc(anyarray, anyarray); +DROP FUNCTION IF EXISTS migration_tools.anyarray_agg_statefunc(anyarray, anyarray); CREATE FUNCTION anyarray_agg_statefunc(state anyarray, value anyarray) RETURNS anyarray AS $BODY$ @@ -302,8 +335,8 @@ $BODY$ COMMENT ON FUNCTION anyarray_agg_statefunc(anyarray, anyarray) IS 'Used internally by aggregate anyarray_agg(anyarray).'; -DROP FUNCTION IF EXISTS anyarray_sort(anyarray); -CREATE OR REPLACE FUNCTION anyarray_sort(with_array anyarray) +DROP FUNCTION IF EXISTS migration_tools.anyarray_sort(anyarray); +CREATE OR REPLACE FUNCTION migration_tools.anyarray_sort(with_array anyarray) RETURNS anyarray AS $BODY$ DECLARE @@ -318,8 +351,8 @@ $BODY$ END; $BODY$ LANGUAGE plpgsql; -DROP FUNCTION IF EXISTS anyarray_uniq(anyarray); -CREATE OR REPLACE FUNCTION anyarray_uniq(with_array anyarray) +DROP FUNCTION IF EXISTS migration_tools.anyarray_uniq(anyarray); +CREATE OR REPLACE FUNCTION migration_tools.anyarray_uniq(with_array anyarray) RETURNS anyarray AS $BODY$ DECLARE @@ -355,8 +388,8 @@ $BODY$ END; $BODY$ LANGUAGE plpgsql; -DROP FUNCTION IF EXISTS modify_staged_fixed_fields (BIGINT,TEXT); -CREATE OR REPLACE FUNCTION modify_staged_fixed_fields (bib_id BIGINT, xcode TEXT) +DROP FUNCTION IF EXISTS migration_tools.modify_staged_fixed_fields (BIGINT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.modify_staged_fixed_fields (bib_id BIGINT, xcode TEXT) RETURNS BOOLEAN LANGUAGE plpgsql AS $function$ @@ -391,8 +424,8 @@ BEGIN END; $function$; -DROP FUNCTION IF EXISTS modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT); -CREATE OR REPLACE FUNCTION modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT) +DROP FUNCTION IF EXISTS migration_tools.modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT); +CREATE OR REPLACE FUNCTION migration_tools.modify_fixed_fields (TEXT, TEXT, CHAR(1), CHAR(1), CHAR(1), CHAR(1), SMALLINT, CHAR(1), TEXT, TEXT) RETURNS TEXT LANGUAGE plperlu AS $function$