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,action.circulation,action.hold_request,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.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,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 ( ''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);' );
legacy_value3 TEXT
);' );
PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_profile_map'', ''base_profile_map'' );' );
+ PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_item_dynamic_field_map;' );
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_item_dynamic_field_map (
+ id SERIAL,
+ evergreen_field TEXT,
+ evergreen_value TEXT,
+ evergreen_datatype TEXT,
+ legacy_field1 TEXT,
+ legacy_value1 TEXT,
+ legacy_field2 TEXT,
+ legacy_value2 TEXT,
+ legacy_field3 TEXT,
+ legacy_value3 TEXT
+ );' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf1_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field1,legacy_value1);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf2_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field2,legacy_value2);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_item_dynamic_lf3_idx ON ' || migration_schema || '.base_item_dynamic_field_map (legacy_field3,legacy_value3);' );
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_item_dynamic_field_map'', ''base_item_dynamic_field_map'' );' );
+ PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_copy_location_map;' );
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_copy_location_map (
+ id SERIAL,
+ location INTEGER,
+ holdable BOOLEAN NOT NULL DEFAULT TRUE,
+ hold_verify BOOLEAN NOT NULL DEFAULT FALSE,
+ opac_visible BOOLEAN NOT NULL DEFAULT TRUE,
+ circulate BOOLEAN NOT NULL DEFAULT TRUE,
+ transcribed_location TEXT,
+ legacy_field1 TEXT,
+ legacy_value1 TEXT,
+ legacy_field2 TEXT,
+ legacy_value2 TEXT,
+ legacy_field3 TEXT,
+ legacy_value3 TEXT
+ );' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf1_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field1,legacy_value1);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf2_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field2,legacy_value2);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_lf3_idx ON ' || migration_schema || '.base_copy_location_map (legacy_field3,legacy_value3);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_base_copy_location_loc_idx ON ' || migration_schema || '.base_copy_location_map (transcribed_location);' );
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_copy_location_map'', ''base_copy_location_map'' );' );
+ PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_circ_field_map;' );
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_circ_field_map (
+ id SERIAL,
+ circulate BOOLEAN,
+ loan_period TEXT,
+ max_renewals TEXT,
+ max_out TEXT,
+ fine_amount TEXT,
+ fine_interval TEXT,
+ max_fine TEXT,
+ item_field1 TEXT,
+ item_value1 TEXT,
+ item_field2 TEXT,
+ item_value2 TEXT,
+ patron_field1 TEXT,
+ patron_value1 TEXT,
+ patron_field2 TEXT,
+ patron_value2 TEXT
+ );' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf1_idx ON ' || migration_schema || '.base_circ_field_map (item_field1,item_value1);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf2_idx ON ' || migration_schema || '.base_circ_field_map (item_field2,item_value2);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf3_idx ON ' || migration_schema || '.base_circ_field_map (patron_field1,patron_value1);' );
+ PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_circ_dynamic_lf4_idx ON ' || migration_schema || '.base_circ_field_map (patron_field2,patron_value2);' );
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''base_circ_field_map'', ''base_circ_field_map'' );' );
+
BEGIN
PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_init'', now() );' );
EXCEPTION
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 ~ E'\\sII$' THEN
suffix := 'II';
temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
suffix := 'III';
temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
END IF;
+ IF temp ~ E'\\sIV$' THEN
+ suffix := 'IV';
+ temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
+ END IF;
family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ output TEXT;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ output TEXT;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ output TEXT;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
DECLARE
migration_schema ALIAS FOR $1;
END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ field_map TEXT;
+ item_table ALIAS FOR $2;
+ sql TEXT;
+ sql_update TEXT;
+ sql_where1 TEXT := '';
+ sql_where2 TEXT := '';
+ sql_where3 TEXT := '';
+ output RECORD;
+ BEGIN
+ SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map;
+ FOR output IN
+ EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
+ LOOP
+ sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE ';
+ sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
+ sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
+ sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
+ sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
+ --RAISE INFO 'sql = %', sql;
+ PERFORM migration_tools.exec( $1, sql );
+ END LOOP;
+ BEGIN
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' );
+ EXCEPTION
+ WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' );
+ END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ base_copy_location_map TEXT;
+ item_table ALIAS FOR $2;
+ sql TEXT;
+ sql_update TEXT;
+ sql_where1 TEXT := '';
+ sql_where2 TEXT := '';
+ sql_where3 TEXT := '';
+ output RECORD;
+ BEGIN
+ SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map;
+ FOR output IN
+ EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;'
+ LOOP
+ sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE ';
+ sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1);
+ sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2);
+ sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3);
+ sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';';
+ --RAISE INFO 'sql = %', sql;
+ PERFORM migration_tools.exec( $1, sql );
+ END LOOP;
+ BEGIN
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' );
+ EXCEPTION
+ WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' );
+ END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2
+CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ field_map TEXT;
+ circ_table ALIAS FOR $2;
+ item_table ALIAS FOR $3;
+ patron_table ALIAS FOR $4;
+ sql TEXT;
+ sql_update TEXT;
+ sql_where1 TEXT := '';
+ sql_where2 TEXT := '';
+ sql_where3 TEXT := '';
+ sql_where4 TEXT := '';
+ output RECORD;
+ BEGIN
+ SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map;
+ FOR output IN
+ EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;'
+ LOOP
+ sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND ';
+ sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1);
+ sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2);
+ sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1);
+ sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2);
+ sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';';
+ --RAISE INFO 'sql = %', sql;
+ PERFORM migration_tools.exec( $1, sql );
+ END LOOP;
+ BEGIN
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' );
+ EXCEPTION
+ WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' );
+ END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- expand_barcode
+-- $barcode source barcode
+-- $prefix prefix to add to barcode, NULL = add no prefix
+-- $maxlen maximum length of barcode; default to 14 if left NULL
+-- $pad padding string to apply to left of source barcode before adding
+-- prefix and suffix; set to NULL or '' if no padding is desired
+-- $suffix suffix to add to barcode, NULL = add no suffix
+--
+-- Returns a new string consisting of prefix concatenated with padded barcode and suffix.
+-- If new barcode would be longer than $maxlen, the original barcode is returned instead.
+--
+CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$
+ my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_;
+
+ # default case
+ return unless defined $barcode;
+
+ $prefix = '' unless defined $prefix;
+ $maxlen ||= 14;
+ $pad = '0' unless defined $pad;
+ $suffix = '' unless defined $suffix;
+
+ # bail out if adding prefix and suffix would bring new barcode over max length
+ return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen;
+
+ my $new_barcode = $barcode;
+ if ($pad ne '') {
+ my $pad_length = $maxlen - length($prefix) - length($suffix);
+ if (length($barcode) < $pad_length) {
+ # assuming we always want padding on the left
+ # also assuming that it is possible to have the pad string be longer than 1 character
+ $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode;
+ }
+ }
+
+ # bail out if adding prefix and suffix would bring new barcode over max length
+ return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen;
+
+ return "$prefix$new_barcode$suffix";
+$$ LANGUAGE PLPERL STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$
+ DECLARE
+ attempt_value ALIAS FOR $1;
+ datatype ALIAS FOR $2;
+ fail_value ALIAS FOR $3;
+ output RECORD;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(fail_value) || '::' || datatype || ' 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 $$
+ 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;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ 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_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) || '::NUMERIC(8,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+-- add_codabar_checkdigit
+-- $barcode source barcode
+--
+-- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
+-- character with a checkdigit computed according to the usual algorithm for library barcodes
+-- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
+-- input string does not meet those requirements, it is returned unchanged.
+--
+CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
+ my $barcode = shift;
+
+ return $barcode if $barcode !~ /^\d{13,14}$/;
+ $barcode = substr($barcode, 0, 13); # ignore 14th digit
+ my @digits = split //, $barcode;
+ my $total = 0;
+ $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
+ $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
+ my $remainder = $total % 10;
+ my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
+ return $barcode . $checkdigit;
+$$ LANGUAGE PLPERL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
+ DECLARE
+ phone TEXT := $1;
+ areacode TEXT := $2;
+ temp TEXT := '';
+ output TEXT := '';
+ n_digits INTEGER := 0;
+ BEGIN
+ temp := phone;
+ temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+ n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+ IF n_digits = 7 THEN
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+ output := (areacode || '-' || temp);
+ ELSE
+ output := temp;
+ END IF;
+ RETURN output;
+ END;
+
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;