Differences

This shows you the differences between two versions of the page.

--- importing:holdings:import_via_staging_table [2008/12/01 17:04] – Slightly better layout dbs
+++ importing:holdings:import_via_staging_table [2022/02/10 13:34] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
+======Importing bibliographic holdings via a staging table======
+In the following example, you would:
+  - Import your bibliographic records into the system [[http://open-ils.org/dokuwiki/doku.php?id=evergreen-admin:importing:bibrecords|as usual]]
+  - Create the ''staging_items'' table as defined below: <code sql>
+-- Given a staging table with the following structure ...
+CREATE TABLE staging_items (
+        callnum text, -- call number label
+        bibkey  int,  -- biblio.record_entry_id
+        createdate      date,
+        location        text,
+        barcode         text,
+        item_type       text,
+        owning_lib      text  -- actor.org_unit.shortname
+);
+</code>
+  - Insert your holdings into the ''staging_items'' table
+  - Then follow the steps starting at ''... import using the following ...'' below, adjusted to map according to your legacy item types, etc
+A complete sample of scripts, instructions, and sample data for importing bib records and holdings can be found on the Evergreen [[http://open-ils.org/downloads.php|downloads page]].
+<code sql>
+-- ... import using the following ...
+-- Wrap this all in a transaction so that if we run
+-- into an error, we do not get duplicate values
+BEGIN;
+-- First, we build shelving location
+INSERT INTO asset.copy_location (name, owning_lib)
+        SELECT  DISTINCT l.location, ou.id
+          FROM  staging_items l JOIN actor.org_unit ou
+                        ON (l.owning_lib = ou.shortname);
+-- Needed for Evergreen 1.4 and beyond - will fail with Evergreen 1.2
+-- Create circulation modifiers for in-db circulation
+-- This is very, very crude but satisfies the FK constraints
+INSERT INTO config.circ_modifier (code, name, description, sip2_media_type, magnetic_media)
+        SELECT  DISTINCT item_type as code,
+          item_type AS name,
+          LOWER(item_type) AS description,
+          '001' AS sip2_media_type,
+          FALSE AS magnetic_media
+          FROM  staging_items
+          WHERE item_type NOT IN (SELECT code FROM config.circ_modifier);
+-- Import call numbers for bibrecord->library mappings
+INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
+        SELECT  DISTINCT 1, 1, l.bibkey , l.callnum, ou.id
+          FROM  staging_items l
+                JOIN biblio.record_entry b ON (l.bibkey = b.id)
+                JOIN actor.org_unit ou ON (l.owning_lib = ou.shortname);
+-- Import base copy data
+INSERT INTO asset.copy (
+        circ_lib, creator, editor, create_date, barcode,
+        status, location, loan_duration,
+        fine_level, circ_modifier, deposit, ref, call_number)
+        SELECT  DISTINCT ou.id AS circ_lib,
+AS creator,
+AS editor,
+                l.createdate AS create_date,
+                l.barcode AS barcode,
+AS status,
+                cl.id AS location,
+AS loan_duration,
+AS fine_level,
+                CASE
+                        WHEN l.item_type IN ('REFERENCE', 'DEPOSIT_BK') THEN 'BOOK'
+                        ELSE l.item_type
+                END AS circ_modifier,
+                CASE
+                        WHEN l.item_type = 'DEPOSIT_BK' THEN TRUE
+                        ELSE FALSE
+                END AS deposit,
+                CASE
+                        WHEN l.item_type = 'REFERENCE' THEN TRUE
+                        ELSE FALSE
+                END AS ref,
+                cn.id AS call_number
+          FROM  staging_items l
+                JOIN actor.org_unit ou
+                        ON (l.owning_lib = ou.shortname)
+                JOIN asset.copy_location cl
+                        ON (ou.id = cl.owning_lib AND l.location = cl.name)
+                JOIN asset.call_number cn
+                        ON (ou.id = cn.owning_lib AND l.bibkey = cn.record AND l.callnum = cn.label);
+-- Commit the transaction if everything went well
+COMMIT;
+</code>