END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.country_code (TEXT) RETURNS TEXT AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ output TEXT;
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT value FROM ' || migration_schema || '.config WHERE key = ''country_code'';'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
CREATE OR REPLACE FUNCTION migration_tools.log (TEXT,TEXT,INTEGER) RETURNS VOID AS $$
DECLARE
migration_schema ALIAS FOR $1;
sql ALIAS FOR $2;
nrows INTEGER;
BEGIN
+ EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
+ --RAISE INFO '%', sql;
+ EXECUTE sql;
+ GET DIAGNOSTICS nrows = ROW_COUNT;
+ PERFORM migration_tools.log(migration_schema,sql,nrows);
+ EXCEPTION
+ WHEN OTHERS THEN
+ RAISE EXCEPTION '!!!!!!!!!!! state = %, msg = %, sql = %', SQLSTATE, SQLERRM, sql;
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.debug_exec (TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ sql ALIAS FOR $2;
+ nrows INTEGER;
+ BEGIN
+ EXECUTE 'UPDATE ' || migration_schema || '.sql_current SET sql = ' || quote_literal(sql) || ';';
+ RAISE INFO 'debug_exec sql = %', sql;
EXECUTE sql;
GET DIAGNOSTICS nrows = ROW_COUNT;
PERFORM migration_tools.log(migration_schema,sql,nrows);
migration_schema ALIAS FOR $1;
sql TEXT;
BEGIN
+ EXECUTE 'DROP TABLE IF EXISTS ' || migration_schema || '.sql_current;';
+ EXECUTE 'CREATE TABLE ' || migration_schema || '.sql_current ( sql TEXT);';
+ EXECUTE 'INSERT INTO ' || migration_schema || '.sql_current ( sql ) VALUES ( '''' );';
BEGIN
SELECT 'CREATE TABLE ' || migration_schema || '.sql_log ( time TIMESTAMP NOT NULL DEFAULT NOW(), row_count INTEGER, sql TEXT );' INTO STRICT sql;
EXECUTE sql;
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);' );
+ PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.base_profile_map;' );
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || E'.base_profile_map (
+ id SERIAL,
+ perm_grp_id INTEGER,
+ transcribed_perm_group TEXT,
+ legacy_field1 TEXT,
+ legacy_value1 TEXT,
+ legacy_field2 TEXT,
+ legacy_value2 TEXT,
+ legacy_field3 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
--RAISE INFO 'In migration_tools.build(%)', migration_schema;
SELECT migration_tools.production_tables(migration_schema) INTO STRICT production_tables;
PERFORM migration_tools.build_base_staging_tables(migration_schema,production_tables);
+ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' );
+ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' );
+ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' );
+ 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 );' );
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (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 ~ E'\\sII$' THEN
+ suffix := 'II';
+ temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
+ END IF;
+ IF temp ~ E'\\sIII$' THEN
+ 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 );
+ second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END );
+
+ 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;
DECLARE
n TEXT := o;
BEGIN
- IF o ~ E'^\\d+$' AND o !~ E'^0' THEN
+ IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes
IF o::BIGINT < t THEN
n = o::BIGINT + t;
END IF;
END;
$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+CREATE OR REPLACE FUNCTION migration_tools.base_profile_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_profile_map'';'
+ LOOP
+ RETURN output;
+ END LOOP;
+ 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;
+ profile_map TEXT;
+ patron_table ALIAS FOR $2;
+ default_patron_profile ALIAS FOR $3;
+ sql TEXT;
+ sql_update TEXT;
+ sql_where1 TEXT := '';
+ sql_where2 TEXT := '';
+ sql_where3 TEXT := '';
+ output RECORD;
+ BEGIN
+ SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map;
+ FOR output IN
+ EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;'
+ LOOP
+ sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_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;
+ PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' );
+ BEGIN
+ PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' );
+ EXCEPTION
+ WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' );
+ END;
+ 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_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_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 AND areacode <> '' 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;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $leader = $marc->leader();
+ substr($leader, $pos, 1) = $value;
+ $marc->leader($leader);
+ $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.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $f008 = $marc->field('008');
+
+ if ($f008) {
+ my $field = $f008->data();
+ substr($field, $pos, 1) = $value;
+ $f008->update($field);
+ $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.is_staff_profile (INT) RETURNS BOOLEAN AS $$
+ DECLARE
+ profile ALIAS FOR $1;
+ BEGIN
+ RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
+ BEGIN
+ RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
+
+ my ($marcxml, $tags) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
+
+ my @incumbents = ();
+
+ foreach my $field ( $marc->fields() ) {
+ push @incumbents, $field->as_formatted();
+ }
+
+ foreach $field ( $to_insert->fields() ) {
+ if (!grep {$_ eq $field->as_formatted()} @incumbents) {
+ $marc->insert_fields_ordered( ($field) );
+ }
+ }
+
+ $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.apply_circ_matrix( tablename TEXT ) 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('m_pioneer.action_circulation');
+--
+
+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;
+ circ INT;
+ n INT := 0;
+ n_circs INT;
+
+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
+ duration_rule,
+ recurring_fine_rule,
+ 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
+ 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;
+
+CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat.
+-- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo');
+
+-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats.
+-- TODO: Add a similar tool for actor stat cats, which behave differently.
+
+DECLARE
+ c TEXT := schemaname || '.asset_copy_legacy';
+ sc TEXT := schemaname || '.asset_stat_cat';
+ sce TEXT := schemaname || '.asset_stat_cat_entry';
+ scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map';
+ stat_cat INT;
+ stat_cat_entry INT;
+
+BEGIN
+
+ FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP
+
+ EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry;
+
+ EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry)
+ SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN
+ (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');');
+
+ END LOOP;
+
+ RETURN;
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$
+
+-- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this.
+-- This will assign standing penalties as needed.
+
+DECLARE
+ org_unit INT;
+ usr INT;
+
+BEGIN
+
+ FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP
+
+ FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP
+
+ EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');');
+
+ END LOOP;
+
+ END LOOP;
+
+ RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$
+
+BEGIN
+ INSERT INTO metabib.metarecord (fingerprint, master_record)
+ SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+ FROM biblio.record_entry b
+ WHERE NOT b.deleted
+ AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
+ AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+ ORDER BY b.fingerprint, b.quality DESC;
+ INSERT INTO metabib.metarecord_source_map (metarecord, source)
+ SELECT m.id, r.id
+ FROM biblio.record_entry r
+ JOIN metabib.metarecord m USING (fingerprint)
+ WHERE NOT r.deleted;
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$
+
+BEGIN
+ INSERT INTO metabib.metarecord (fingerprint, master_record)
+ SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id
+ FROM biblio.record_entry b
+ WHERE NOT b.deleted
+ AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL)
+ AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint )
+ ORDER BY b.fingerprint, b.quality DESC;
+ INSERT INTO metabib.metarecord_source_map (metarecord, source)
+ SELECT m.id, r.id
+ FROM biblio.record_entry r
+ JOIN metabib.metarecord m USING (fingerprint)
+ WHERE NOT r.deleted
+ AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL);
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$
+
+-- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned.
+-- Then SELECT migration_tools.create_cards('m_foo');
+
+DECLARE
+ u TEXT := schemaname || '.actor_usr_legacy';
+ c TEXT := schemaname || '.actor_card';
+
+BEGIN
+
+ EXECUTE ('TRUNCATE ' || c || ';');
+ EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';');
+ EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;');
+
+ RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+
+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 ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 &&
+ ! ( $field->as_string('9') =~ m/$shortname/ ) ) {
+ $field->add_subfields( '9' => $shortname );
+ $field->update( ind2 => '0');
+ }
+ }
+
+ $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) RETURNS VOID AS $$
+
+DECLARE
+ old_volume BIGINT;
+ new_volume BIGINT;
+ bib BIGINT;
+ owner INTEGER;
+ old_label TEXT;
+ remainder BIGINT;
+
+BEGIN
+
+ -- Bail out if asked to change the label to ##URI##
+ IF new_label = '##URI##' THEN
+ RETURN;
+ END IF;
+
+ -- Gather information
+ SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id;
+ SELECT record INTO bib FROM asset.call_number WHERE id = old_volume;
+ SELECT owning_lib, label INTO owner, old_label FROM asset.call_number WHERE id = old_volume;
+
+ -- Bail out if the label already is ##URI##
+ IF old_label = '##URI##' THEN
+ RETURN;
+ END IF;
+
+ -- Bail out if the call number label is already correct
+ IF new_volume = old_volume THEN
+ RETURN;
+ END IF;
+
+ -- Check whether we already have a destination volume available
+ SELECT id INTO new_volume FROM asset.call_number
+ WHERE
+ record = bib AND
+ owning_lib = owner AND
+ label = new_label AND
+ NOT deleted;
+
+ -- 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);
+ SELECT id INTO new_volume FROM asset.call_number
+ WHERE
+ record = bib AND
+ owning_lib = owner AND
+ label = new_label AND
+ NOT deleted;
+ END IF;
+
+ -- Move copy to destination
+ UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id;
+
+ -- Delete source volume if it is now empty
+ SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted;
+ IF NOT FOUND THEN
+ DELETE FROM asset.call_number WHERE id = old_volume;
+ END IF;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETURNS TEXT[] AS $$
+
+ my $input = $_[0];
+ my %zipdata;
+
+ open (FH, '<', '/openils/var/data/zips.txt') or return ('No File Found', 'No File Found', 'No File Found');
+
+ while (<FH>) {
+ chomp;
+ my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/);
+ $zipdata{$zip} = [$city, $state, $county];
+ }
+
+ if (defined $zipdata{$input}) {
+ my ($city, $state, $county) = @{$zipdata{$input}};
+ return [$city, $state, $county];
+ } elsif (defined $zipdata{substr $input, 0, 5}) {
+ my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}};
+ return [$city, $state, $county];
+ } else {
+ return ['ZIP not found', 'ZIP not found', 'ZIP not found'];
+ }
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
+
+DECLARE
+ ou INT;
+ org_unit_depth INT;
+ ou_parent INT;
+ parent_depth INT;
+ errors_found BOOLEAN;
+ ou_shortname TEXT;
+ parent_shortname TEXT;
+ ou_type_name TEXT;
+ parent_type TEXT;
+ type_id INT;
+ type_depth INT;
+ type_parent INT;
+ type_parent_depth INT;
+ proper_parent TEXT;
+
+BEGIN
+
+ errors_found := FALSE;
+
+-- Checking actor.org_unit_type
+
+ FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP
+
+ SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth;
+ SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent;
+
+ IF type_parent IS NOT NULL THEN
+
+ SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth;
+
+ IF type_depth - type_parent_depth <> 1 THEN
+ SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name;
+ SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type;
+ RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.',
+ ou_type_name, type_depth, parent_type, type_parent_depth;
+ errors_found := TRUE;
+
+ END IF;
+
+ END IF;
+
+ END LOOP;
+
+-- Checking actor.org_unit
+
+ FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP
+
+ SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent;
+ SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth;
+ SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth;
+ SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname;
+ SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name;
+ SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type;
+
+ IF ou_parent IS NOT NULL THEN
+
+ IF (org_unit_depth - parent_depth <> 1) OR (
+ (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type)
+ ) THEN
+ RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).',
+ ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth;
+ errors_found := TRUE;
+ END IF;
+
+ END IF;
+
+ END LOOP;
+
+ IF NOT errors_found THEN
+ RAISE INFO 'No errors found.';
+ END IF;
+
+ RETURN;
+
+END;
+
+$$ LANGUAGE plpgsql;