X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=01817db2dfc637b8979c5b321f09cbe56978d4d3;hp=9aa8e8e07d9c020aeaa6082586d42f1b360827e7;hb=9cecf073cae1e0be03024902596747825b6e2bb2;hpb=c7f8e014fff93ed7552e3e9a97145679ab886f8f

diff --git a/sql/base/base.sql b/sql/base/base.sql
index 9aa8e8e..01817db 100644
--- a/sql/base/base.sql
+++ b/sql/base/base.sql
@@ -23,7 +23,7 @@
 -- SELECT migration_tools.build('foo');
 -- SELECT * FROM foo.fields_requiring_mapping;
 -- \d foo.actor_usr
--- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS foo.asset_copy;
+-- create some incoming ILS specific staging tables, like CREATE foo.legacy_items ( l_barcode TEXT, .. ) INHERITS (foo.asset_copy);
 -- Do some mapping, like UPDATE foo.legacy_items SET barcode = TRIM(BOTH ' ' FROM l_barcode);
 -- Then, to move into production, do: select migration_tools.insert_base_into_production('foo')
 
@@ -117,7 +117,7 @@ CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$
         END;
         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' );
         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' );
-        PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment'' );' );
+        PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,actor.usr_setting,action.circulation,action.hold_request,action.hold_notification,action.hold_request_note,action.hold_transit_copy,action.transit_copy,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.provider_contact,acq.provider_contact_address,acq.fund,acq.fund_allocation,acq.fund_tag,acq.fund_tag_map,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note,config.circ_matrix_matchpoint,config.circ_matrix_limit_set_map,config.hold_matrix_matchpoint'' );' );
         PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' );
         PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' );
         PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' );
@@ -219,7 +219,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$
         PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' );
         PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' );
-        PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' );
+        PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' );
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
@@ -244,7 +244,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build_specific_base_staging_table (TE
     BEGIN
         base_staging_table = REPLACE( production_table, '.', '_' );
         --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
-        PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
+        PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
         PERFORM migration_tools.exec( $1, '
             INSERT INTO ' || migration_schema || '.fields_requiring_mapping
                 SELECT table_schema, table_name, column_name, data_type
@@ -352,6 +352,163 @@ CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_firs
     END;
 $$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
+    DECLARE
+        full_name TEXT := $1;
+        temp TEXT;
+        family_name TEXT := '';
+        first_given_name TEXT := '';
+        second_given_name TEXT := '';
+        suffix TEXT := '';
+        prefix TEXT := '';
+    BEGIN
+        temp := full_name;
+        -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
+        IF temp ilike '%MR.%' THEN
+            prefix := 'Mr.';
+            temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%MRS.%' THEN
+            prefix := 'Mrs.';
+            temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%MS.%' THEN
+            prefix := 'Ms.';
+            temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%DR.%' THEN
+            prefix := 'Dr.';
+            temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%JR.%' THEN
+            suffix := 'Jr.';
+            temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%JR,%' THEN
+            suffix := 'Jr.';
+            temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
+        END IF;
+        IF temp ilike '%SR.%' THEN
+            suffix := 'Sr.';
+            temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' );
+        END IF;
+        IF temp ilike '%SR,%' THEN
+            suffix := 'Sr.';
+            temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
+        END IF;
+        IF temp like '%III%' THEN
+            suffix := 'III';
+            temp := REGEXP_REPLACE( temp, E'III', '' );
+        END IF;
+        IF temp like '%II%' THEN
+            suffix := 'II';
+            temp := REGEXP_REPLACE( temp, E'II', '' );
+        END IF;
+        IF temp like '%IV%' THEN
+            suffix := 'IV';
+            temp := REGEXP_REPLACE( temp, E'IV', '' );
+        END IF;
+
+        temp := REGEXP_REPLACE( temp, '\(\)', '');
+        family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') );
+        family_name := REGEXP_REPLACE( family_name, ',', '' );
+        first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END;
+        first_given_name := REGEXP_REPLACE( first_given_name, ',', '' );
+        second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END;
+        second_given_name := REGEXP_REPLACE( second_given_name, ',', '' );
+
+        RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
+    END;
+$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$
+    DECLARE
+        full_name TEXT := $1;
+        temp TEXT;
+        family_name TEXT := '';
+        first_given_name TEXT := '';
+        second_given_name TEXT := '';
+        suffix TEXT := '';
+        prefix TEXT := '';
+    BEGIN
+        temp := BTRIM(full_name);
+        -- Use values, not structure, for prefix/suffix, unless we come up with a better idea
+        --IF temp ~ '^\S{2,}\.' THEN
+        --    prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1');
+        --    temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1'));
+        --END IF;
+        --IF temp ~ '\S{2,}\.$' THEN
+        --    suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1');
+        --    temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1');
+        --END IF;
+        IF temp ilike '%MR.%' THEN
+            prefix := 'Mr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%MRS.%' THEN
+            prefix := 'Mrs.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%MS.%' THEN
+            prefix := 'Ms.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%DR.%' THEN
+            prefix := 'Dr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%JR.%' THEN
+            suffix := 'Jr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%JR,%' THEN
+            suffix := 'Jr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ));
+        END IF;
+        IF temp ilike '%SR.%' THEN
+            suffix := 'Sr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ));
+        END IF;
+        IF temp ilike '%SR,%' THEN
+            suffix := 'Sr.';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ));
+        END IF;
+        IF temp like '%III%' THEN
+            suffix := 'III';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' ));
+        END IF;
+        IF temp like '%II%' THEN
+            suffix := 'II';
+            temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' ));
+        END IF;
+
+        IF temp ~ ',' THEN
+            family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1'));
+            temp := BTRIM(REPLACE( temp, family_name, '' ));
+            family_name := REPLACE( family_name, ',', '' );
+            IF temp ~ ' ' THEN
+                first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') );
+                second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') );
+            ELSE
+                first_given_name := temp;
+                second_given_name := '';
+            END IF;
+        ELSE
+            IF temp ~ '^\S+\s+\S+\s+\S+$' THEN
+                first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') );
+                second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') );
+                family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') );
+            ELSE
+                first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') );
+                second_given_name := temp;
+                family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') );
+            END IF;
+        END IF;
+
+        RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
+    END;
+$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
     DECLARE
         city_state_zip TEXT := $1;
@@ -365,9 +522,9 @@ CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT)
             state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2');
             city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1');
         ELSE
-            IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*' THEN
-                state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*.*$', E'\\1' );
-                city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*.*)$', E'\\1\\2' );
+            IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
+                state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
+                city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
             ELSE
                 IF city_state_zip ~ E'^\\S+$'  THEN
                     city := city_state_zip;
@@ -382,6 +539,74 @@ CREATE OR REPLACE FUNCTION migration_tools.address_parse_out_citystatezip (TEXT)
     END;
 $$ LANGUAGE PLPGSQL STRICT VOLATILE;
 
+-- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016
+CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$
+    DECLARE
+        fullstring TEXT := $1;
+        address1 TEXT := '';
+        address2 TEXT := '';
+        scratch1 TEXT := '';
+        scratch2 TEXT := '';
+        city TEXT := '';
+        state TEXT := '';
+        zip TEXT := '';
+    BEGIN
+        zip := CASE
+            WHEN fullstring ~ E'\\d\\d\\d\\d\\d'
+            THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' )
+            ELSE ''
+        END;
+        fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2');
+
+        IF fullstring ~ ',' THEN
+            state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2');
+            scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1');
+        ELSE
+            IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN
+                state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' );
+                scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' );
+            ELSE
+                IF fullstring ~ E'^\\S+$'  THEN
+                    scratch1 := fullstring;
+                    state := 'N/A';
+                ELSE
+                    state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2');
+                    scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1');
+                END IF;
+            END IF;
+        END IF;
+
+        IF scratch1 ~ '[\$]' THEN
+            scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1');
+            city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2');
+        ELSE
+            IF scratch1 ~ '\s' THEN
+                scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1');
+                city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2');
+            ELSE
+                scratch2 := 'N/A';
+                city := scratch1;
+            END IF;
+        END IF;
+
+        IF scratch2 ~ '^\d' THEN
+            address1 := scratch2;
+            address2 := '';
+        ELSE
+            address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1');
+            address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2');
+        END IF;
+
+        RETURN ARRAY[
+             TRIM(BOTH ' ' FROM address1)
+            ,TRIM(BOTH ' ' FROM address2)
+            ,TRIM(BOTH ' ' FROM city)
+            ,TRIM(BOTH ' ' FROM state)
+            ,TRIM(BOTH ' ' FROM zip)
+        ];
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
 CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
     DECLARE
         n TEXT := o;
@@ -624,36 +849,71 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER,
     return "$prefix$new_barcode$suffix";
 $$ LANGUAGE PLPERLU STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
+-- remove previous version of this function
+DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT);
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
         datatype ALIAS FOR $2;
-        fail_value ALIAS FOR $3;
-        output RECORD;
+    BEGIN
+        EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;';
+        RETURN attempt_value;
+    EXCEPTION
+        WHEN OTHERS THEN RETURN NULL;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output DATE;
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
+            EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;'
         LOOP
             RETURN output;
         END LOOP;
     EXCEPTION
         WHEN OTHERS THEN
             FOR output IN
-                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' AS a;'
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
             LOOP
                 RETURN output;
             END LOOP;
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$
+CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output TIMESTAMPTZ;
+    BEGIN
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
         fail_value ALIAS FOR $2;
         output DATE;
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::date AS a;'
+            EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
         LOOP
             RETURN output;
         END LOOP;
@@ -667,35 +927,77 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$
+CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
         fail_value ALIAS FOR $2;
-        output TIMESTAMPTZ;
+        output TIMESTAMP;
+    BEGIN
+            output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
+            RETURN output;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(8,2);
     BEGIN
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;'
+            EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;'
         LOOP
             RETURN output;
         END LOOP;
     EXCEPTION
         WHEN OTHERS THEN
             FOR output IN
-                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;'
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
             LOOP
                 RETURN output;
             END LOOP;
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(6,2);
+    BEGIN
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
     DECLARE
         attempt_value ALIAS FOR $1;
         fail_value ALIAS FOR $2;
         output NUMERIC(8,2);
     BEGIN
+        IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN
+            RAISE EXCEPTION 'too many digits';
+        END IF;
         FOR output IN
-            EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
+            EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;'
         LOOP
             RETURN output;
         END LOOP;
@@ -709,6 +1011,30 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUM
     END;
 $$ LANGUAGE PLPGSQL STRICT STABLE;
 
+CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$
+    DECLARE
+        attempt_value ALIAS FOR $1;
+        fail_value ALIAS FOR $2;
+        output NUMERIC(6,2);
+    BEGIN
+        IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN
+            RAISE EXCEPTION 'too many digits';
+        END IF;
+        FOR output IN
+            EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;'
+        LOOP
+            RETURN output;
+        END LOOP;
+    EXCEPTION
+        WHEN OTHERS THEN
+            FOR output IN
+                EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;'
+            LOOP
+                RETURN output;
+            END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
 -- add_codabar_checkdigit
 --   $barcode      source barcode
 --
@@ -731,6 +1057,31 @@ CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS
     return $barcode . $checkdigit; 
 $$ LANGUAGE PLPERLU STRICT STABLE;
 
+-- add_code39mod43_checkdigit
+--   $barcode      source barcode
+--
+-- If the source string is 13 or 14 characters long and contains only valid
+-- Code 39 mod 43 characters, adds or replaces the 14th
+-- character with a checkdigit computed according to the usual algorithm for library barcodes
+-- using the Code 39 mod 43 symbology - see <http://en.wikipedia.org/wiki/Code_39#Code_39_mod_43>.  If the
+-- input string does not meet those requirements, it is returned unchanged.
+--
+CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$
+    my $barcode = shift;
+
+    return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/;
+    $barcode = substr($barcode, 0, 13); # ignore 14th character
+
+    my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%';
+    my %nums = map { $valid_chars[$_] => $_ } (0..42);
+
+    my $total = 0;
+    $total += $nums{$_} foreach split(//, $barcode);
+    my $remainder = $total % 43;
+    my $checkdigit = $valid_chars[$remainder];
+    return $barcode . $checkdigit;
+$$ LANGUAGE PLPERLU STRICT STABLE;
+
 CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
   DECLARE
     phone TEXT := $1;
@@ -902,7 +1253,7 @@ BEGIN
       duration_rule,
       recurring_fine_rule,
       max_fine_rule
-      FROM action.find_circ_matrix_matchpoint(
+      FROM action.item_user_circ_test(
         circ_lib,
         target_copy,
         usr,
@@ -1166,6 +1517,111 @@ END;
 
 $$ LANGUAGE plpgsql;
 
+CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
+
+-- Usage:
+--
+--   First make sure the circ matrix is loaded and the circulations
+--   have been staged to the extent possible (but at the very least
+--   circ_lib, target_copy, usr, and *_renewal).  User profiles and
+--   circ modifiers must also be in place.
+--
+--   SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960);
+--
+
+DECLARE
+  circ_lib             INT;
+  target_copy          INT;
+  usr                  INT;
+  is_renewal           BOOLEAN;
+  this_duration_rule   INT;
+  this_fine_rule       INT;
+  this_max_fine_rule   INT;
+  rcd                  config.rule_circ_duration%ROWTYPE;
+  rrf                  config.rule_recurring_fine%ROWTYPE;
+  rmf                  config.rule_max_fine%ROWTYPE;
+  n                    INT := 0;
+  n_circs              INT := 1;
+  
+BEGIN
+
+  --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs;
+
+  --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP
+
+    -- Fetch the correct rules for this circulation
+    EXECUTE ('
+      SELECT
+        circ_lib,
+        target_copy,
+        usr,
+        CASE
+          WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE
+          ELSE FALSE
+        END
+      FROM ' || tablename || ' WHERE id = ' || circ || ';')
+      INTO circ_lib, target_copy, usr, is_renewal ;
+    SELECT
+      INTO this_duration_rule,
+           this_fine_rule,
+           this_max_fine_rule
+      (matchpoint).duration_rule,
+      (matchpoint).recurring_fine_rule,
+      (matchpoint).max_fine_rule
+      FROM action.find_circ_matrix_matchpoint(
+        circ_lib,
+        target_copy,
+        usr,
+        is_renewal
+        );
+    SELECT INTO rcd * FROM config.rule_circ_duration
+      WHERE id = this_duration_rule;
+    SELECT INTO rrf * FROM config.rule_recurring_fine
+      WHERE id = this_fine_rule;
+    SELECT INTO rmf * FROM config.rule_max_fine
+      WHERE id = this_max_fine_rule;
+
+    -- Apply the rules to this circulation
+    EXECUTE ('UPDATE ' || tablename || ' c
+    SET
+      duration_rule = rcd.name,
+      recurring_fine_rule = rrf.name,
+      max_fine_rule = rmf.name,
+      duration = rcd.normal,
+      recurring_fine = rrf.normal,
+      max_fine =
+        CASE rmf.is_percent
+          WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+          ELSE rmf.amount
+        END,
+      renewal_remaining = rcd.max_renewals,
+      grace_period = rrf.grace_period
+    FROM
+      config.rule_circ_duration rcd,
+      config.rule_recurring_fine rrf,
+      config.rule_max_fine rmf,
+                        asset.copy ac
+    WHERE
+      rcd.id = ' || this_duration_rule || ' AND
+      rrf.id = ' || this_fine_rule || ' AND
+      rmf.id = ' || this_max_fine_rule || ' AND
+                        ac.id = c.target_copy AND
+      c.id = ' || circ || ';');
+
+    -- Keep track of where we are in the process
+    n := n + 1;
+    IF (n % 100 = 0) THEN
+      RAISE INFO '%', n || ' of ' || n_circs
+        || ' (' || (100*n/n_circs) || '%) circs updated.';
+    END IF;
+
+  --END LOOP;
+
+  RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
 
 
 
@@ -1293,7 +1749,7 @@ END;
 $$ LANGUAGE plpgsql;
 
 
-CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
+CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$
 
   ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
 
@@ -1325,7 +1781,38 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX
 
 $$ LANGUAGE PLPERLU STABLE;
 
-CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT) RETURNS VOID AS $$
+CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$
+
+  ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...];
+
+  my ($marcxml, $shortname) = @_;
+
+  use MARC::Record;
+  use MARC::File::XML;
+
+  my $xml = $marcxml;
+
+  eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+
+    foreach my $field ( $marc->field('856') ) {
+      if ( ! $field->as_string('9') ) {
+        $field->add_subfields( '9' => $shortname );
+      }
+    }
+
+    $xml = $marc->as_xml_record;
+    $xml =~ s/^<\?.+?\?>$//mo;
+    $xml =~ s/\n//sgo;
+    $xml =~ s/>\s+</></sgo;
+  };
+
+  return $xml;
+
+$$ LANGUAGE PLPERLU STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.change_call_number(copy_id BIGINT, new_label TEXT, cn_class BIGINT) RETURNS VOID AS $$
 
 DECLARE
   old_volume   BIGINT;
@@ -1367,8 +1854,8 @@ BEGIN
 
   -- Create destination volume if needed
   IF NOT FOUND THEN
-    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) 
-      VALUES (1, 1, bib, owner, new_label);
+    INSERT INTO asset.call_number (creator, editor, record, owning_lib, label, label_class) 
+      VALUES (1, 1, bib, owner, new_label, cn_class);
     SELECT id INTO new_volume FROM asset.call_number
       WHERE 
         record = bib AND
@@ -1636,7 +2123,10 @@ MARC::Charset->assume_unicode(1);
 
 my $xml = shift;
 
-eval { my $r = MARC::Record->new_from_xml( $xml ); };
+eval {
+    my $r = MARC::Record->new_from_xml( $xml );
+    my $output_xml = $r->as_xml_record();
+};
 if ($@) {
     return 0;
 } else {
@@ -1833,3 +2323,376 @@ The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '2
 The second argument is an array of text containing the values to plug into each field.  
 If the value for a given field is NULL or the empty string, it is not inserted.
 $$;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
+
+my ($marcxml, $tag, $pos, $value) = @_;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'UTF-8');
+use MARC::Charset;
+use strict;
+
+MARC::Charset->assume_unicode(1);
+
+elog(ERROR, 'indicator position must be either 1 or 2') unless $pos =~ /^[12]$/;
+elog(ERROR, 'MARC tag must be numeric') unless $tag =~ /^\d{3}$/;
+elog(ERROR, 'MARC tag must not be control field') if $tag =~ /^00/;
+elog(ERROR, 'Value must be exactly one character') unless $value =~ /^.$/;
+
+my $xml = $marcxml;
+eval {
+    my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+
+    foreach my $field ($marc->field($tag)) {
+        $field->update("ind$pos" => $value);
+    }
+    $xml = $marc->as_xml_record;
+    $xml =~ s/^<\?.+?\?>$//mo;
+    $xml =~ s/\n//sgo;
+    $xml =~ s/>\s+</></sgo;
+};
+return $xml;
+
+$func$ LANGUAGE PLPERLU;
+
+COMMENT ON FUNCTION migration_tools.set_indicator(TEXT, TEXT, INTEGER, CHAR(1)) IS $$Set indicator value of a specified MARC field.
+The first argument is a MARCXML string.
+The second argument is a MARC tag.
+The third argument is the indicator position, either 1 or 2.
+The fourth argument is the character to set the indicator value to.
+All occurences of the specified field will be changed.
+The function returns the revised MARCXML string.$$;
+
+CREATE OR REPLACE FUNCTION migration_tools.create_staff_user(
+    username TEXT,
+    password TEXT,
+    org TEXT,
+    perm_group TEXT,
+    first_name TEXT DEFAULT '',
+    last_name TEXT DEFAULT ''
+) RETURNS VOID AS $func$
+BEGIN
+    RAISE NOTICE '%', org ;
+    INSERT INTO actor.usr (usrname, passwd, ident_type, first_given_name, family_name, home_ou, profile)
+    SELECT username, password, 1, first_name, last_name, aou.id, pgt.id
+    FROM   actor.org_unit aou, permission.grp_tree pgt
+    WHERE  aou.shortname = org
+    AND    pgt.name = perm_group;
+END
+$func$
+LANGUAGE PLPGSQL;
+
+-- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}');
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$
+    DECLARE
+        target_event_def ALIAS FOR $1;
+        orgs ALIAS FOR $2;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
+            INSERT INTO action_trigger.event_definition (
+                active
+                ,owner
+                ,name
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            ) SELECT
+                'f'
+                ,orgs[i]
+                ,name || ' (clone of '||target_event_def||')'
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            FROM
+                action_trigger.event_definition
+            WHERE
+                id = target_event_def
+            ;
+            RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
+            INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
+            INSERT INTO action_trigger.environment (
+                event_def
+                ,path
+                ,collector
+                ,label
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,path
+                ,collector
+                ,label
+            FROM
+                action_trigger.environment
+            WHERE
+                event_def = target_event_def
+            ;
+            INSERT INTO action_trigger.event_params (
+                event_def
+                ,param
+                ,value
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,param
+                ,value
+            FROM
+                action_trigger.event_params
+            WHERE
+                event_def = target_event_def
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL);
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$
+    DECLARE
+        target_event_def ALIAS FOR $1;
+        orgs ALIAS FOR $2;
+        new_interval ALIAS FOR $3;
+    BEGIN
+        DROP TABLE IF EXISTS new_atevdefs;
+        CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+        FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP
+            INSERT INTO action_trigger.event_definition (
+                active
+                ,owner
+                ,name
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,delay
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            ) SELECT
+                'f'
+                ,orgs[i]
+                ,name || ' (clone of '||target_event_def||')'
+                ,hook
+                ,validator
+                ,reactor
+                ,cleanup_success
+                ,cleanup_failure
+                ,new_interval
+                ,max_delay
+                ,usr_field
+                ,opt_in_setting
+                ,delay_field
+                ,group_field
+                ,template
+                ,granularity
+                ,repeat_delay
+            FROM
+                action_trigger.event_definition
+            WHERE
+                id = target_event_def
+            ;
+            RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq');
+            INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq');
+            INSERT INTO action_trigger.environment (
+                event_def
+                ,path
+                ,collector
+                ,label
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,path
+                ,collector
+                ,label
+            FROM
+                action_trigger.environment
+            WHERE
+                event_def = target_event_def
+            ;
+            INSERT INTO action_trigger.event_params (
+                event_def
+                ,param
+                ,value
+            ) SELECT
+                currval('action_trigger.event_definition_id_seq')
+                ,param
+                ,value
+            FROM
+                action_trigger.event_params
+            WHERE
+                event_def = target_event_def
+            ;
+        END LOOP;
+        RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$
+    my ($marcxml, $tag, $subfield, $delimiter) = @_;
+
+    use MARC::Record;
+    use MARC::File::XML;
+    use MARC::Field;
+
+    my $field;
+    eval {
+        my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+        $field = $marc->field($tag);
+    };
+    return $field->as_string($subfield,$delimiter);
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
+    my ($marcxml, $tag, $subfield, $delimiter) = @_;
+
+    use MARC::Record;
+    use MARC::File::XML;
+    use MARC::Field;
+
+    my @fields;
+    eval {
+        my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+        @fields = $marc->field($tag);
+    };
+    my @texts;
+    foreach my $field (@fields) {
+        push @texts, $field->as_string($subfield,$delimiter);
+    }
+    return \@texts;
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
+    SELECT action.find_hold_matrix_matchpoint(
+        (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
+        (SELECT request_lib FROM action.hold_request WHERE id = $1),
+        (SELECT current_copy FROM action.hold_request WHERE id = $1),
+        (SELECT usr FROM action.hold_request WHERE id = $1),
+        (SELECT requestor FROM action.hold_request WHERE id = $1)
+    );
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$
+    SELECT action.hold_request_permit_test(
+        (SELECT pickup_lib FROM action.hold_request WHERE id = $1),
+        (SELECT request_lib FROM action.hold_request WHERE id = $1),
+        (SELECT current_copy FROM action.hold_request WHERE id = $1),
+        (SELECT usr FROM action.hold_request WHERE id = $1),
+        (SELECT requestor FROM action.hold_request WHERE id = $1)
+    );
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$
+    SELECT action.find_circ_matrix_matchpoint(
+        (SELECT circ_lib FROM action.circulation WHERE id = $1),
+        (SELECT target_copy FROM action.circulation WHERE id = $1),
+        (SELECT usr FROM action.circulation WHERE id = $1),
+        (SELECT COALESCE(
+                NULLIF(phone_renewal,false),
+                NULLIF(desk_renewal,false),
+                NULLIF(opac_renewal,false),
+                false
+            ) FROM action.circulation WHERE id = $1
+        )
+    );
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$
+    DECLARE
+        test ALIAS FOR $1;
+    BEGIN
+        IF NOT test THEN
+            RAISE EXCEPTION 'assertion';
+        END IF;
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT) RETURNS VOID AS $$
+    DECLARE
+        test ALIAS FOR $1;
+        msg ALIAS FOR $2;
+    BEGIN
+        IF NOT test THEN
+            RAISE EXCEPTION '%', msg;
+        END IF;
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN,TEXT,TEXT) RETURNS TEXT AS $$
+    DECLARE
+        test ALIAS FOR $1;
+        fail_msg ALIAS FOR $2;
+        success_msg ALIAS FOR $3;
+    BEGIN
+        IF NOT test THEN
+            RAISE EXCEPTION '%', fail_msg;
+        END IF;
+        RETURN success_msg;
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- push bib sequence and return starting value for reserved range
+CREATE OR REPLACE FUNCTION migration_tools.push_bib_sequence(INTEGER) RETURNS BIGINT AS $$
+    DECLARE
+        bib_count ALIAS FOR $1;
+        output BIGINT;
+    BEGIN
+        PERFORM setval('biblio.record_entry_id_seq',(SELECT MAX(id) FROM biblio.record_entry) + bib_count + 2000);
+        FOR output IN
+            SELECT CEIL(MAX(id)/1000)*1000+1000 FROM biblio.record_entry WHERE id < (SELECT last_value FROM biblio.record_entry_id_seq)
+        LOOP
+            RETURN output;
+        END LOOP;
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- set a new salted password
+
+CREATE OR REPLACE FUNCTION migration_tools.set_salted_passwd(INTEGER,TEXT) RETURNS BOOLEAN AS $$
+    DECLARE
+        usr_id              ALIAS FOR $1;
+        plain_passwd        ALIAS FOR $2;
+        plain_salt          TEXT;
+        md5_passwd          TEXT;
+    BEGIN
+
+        SELECT actor.create_salt('main') INTO plain_salt;
+
+        SELECT MD5(plain_passwd) INTO md5_passwd;
+        
+        PERFORM actor.set_passwd(usr_id, 'main', MD5(plain_salt || md5_passwd), plain_salt);
+
+        RETURN TRUE;
+
+    END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+
+
+