migration_tools.openbiblio2marc

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

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 7c75408..8ff83f4 100644 (file)
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -1675,6 +1675,167 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT)
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
+BEGIN
+-- Expects the following table/columns:
+
+-- export_biblio_tsv:
+-- l_bibid               | 1
+-- l_create_dt           | 2007-03-07 09:03:09
+-- l_last_change_dt      | 2015-01-23 11:18:54
+-- l_last_change_userid  | 2
+-- l_material_cd         | 10
+-- l_collection_cd       | 13
+-- l_call_nmbr1          | Canada
+-- l_call_nmbr2          | ON
+-- l_call_nmbr3          | Ottawa 18
+-- l_title               | Art and the courts : France ad England
+-- l_title_remainder     | from 1259-1328
+-- l_responsibility_stmt |
+-- l_author              | National Gallery of Canada
+-- l_topic1              |
+-- l_topic2              |
+-- l_topic3              |
+-- l_topic4              |
+-- l_topic5              |
+-- l_opac_flg            | Y
+-- l_flag_attention      | 0
+
+-- export_biblio_field_tsv:
+-- l_bibid       | 1
+-- l_fieldid     | 1
+-- l_tag         | 720
+-- l_ind1_cd     | N
+-- l_ind2_cd     | N
+-- l_subfield_cd | a
+-- l_field_data  | Brieger, Peter Henry
+
+-- Map export_biblio_tsv as follows:
+-- l_call_nmbr?             -> 099a
+-- l_author                 -> 100a
+-- l_title                  -> 245a
+-- l_title_remainder        -> 245b
+-- l_responsibility_stmt    -> 245c
+-- l_topic?                 -> 650a
+-- l_bibid                  -> 999a
+
+RETURN
+    migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data)
+FROM (
+    select
+        array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
+        array_agg(l_ind1_cd) as "ind1",
+        array_agg(l_ind2_cd) as "ind2",
+        array_agg(l_field_data) as "data"
+    from (
+        select
+            l_tag,
+            l_subfield_cd,
+            l_ind1_cd,
+            l_ind2_cd,
+            l_field_data
+        from export_biblio_field_tsv
+        where l_bibid = x_bibid
+    union
+        select
+            '099' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            concat_ws(' ',
+                nullif(btrim(l_call_nmbr1),''),
+                nullif(btrim(l_call_nmbr2),''),
+                nullif(btrim(l_call_nmbr3),'')
+            ) as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid
+    union
+        select
+            '100' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_author as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
+    union
+        select
+            '245' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_title as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
+    union
+        select
+            '245' as "l_tag",
+            'b' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_title_remainder as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
+    union
+        select
+            '650' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_topic1 as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
+    union
+        select
+            '650' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_topic2 as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
+    union
+        select
+            '650' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_topic3 as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
+    union
+        select
+            '650' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_topic4 as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
+    union
+        select
+            '650' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_topic5 as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
+    union
+        select
+            '999' as "l_tag",
+            'a' as "l_subfield_cd",
+            ' ' as "l_ind1_cd",
+            ' ' as "l_ind2_cd",
+            l_bibid as "l_field_data"
+        from export_biblio_tsv
+        where l_bibid = x_bibid
+    ) x
+) y;
+
+END
+$func$ LANGUAGE plpgsql;
+
 CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
     DECLARE
         attempt_value ALIAS FOR $1;