rename marc.sql to 01-marc.sql

[migration-tools.git] / sql / base / fixed_fields.sql

diff --git a/sql/base/fixed_fields.sql b/sql/base/fixed_fields.sql
index aab4c81..7ab6748 100644 (file)
--- 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$