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.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,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
+ 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.copy_alert,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,asset.copy_tag,asset.copy_tag_copy_map,config.copy_tag_type,serial.item,serial.item_note,serial.record_entry,biblio.record_entry'' );' );
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);' );
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 UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
+ PERFORM migration_tools.exec( $1, 'CREATE 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
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+-- creates other child table so you can have more than one child table in a schema from a base table
+CREATE OR REPLACE FUNCTION build_variant_staging_table(text, text, text)
+ RETURNS void
+ LANGUAGE plpgsql
+ STRICT
+AS $function$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ production_table ALIAS FOR $2;
+ base_staging_table ALIAS FOR $3;
+ columns RECORD;
+ BEGIN
+ --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, '
+ INSERT INTO ' || migration_schema || '.fields_requiring_mapping
+ SELECT table_schema, table_name, column_name, data_type
+ FROM information_schema.columns
+ WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
+ ' );
+ FOR columns IN
+ SELECT table_schema, table_name, column_name, data_type
+ FROM information_schema.columns
+ WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
+ LOOP
+ PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
+ END LOOP;
+ END;
+$function$
+
+CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ parent_table ALIAS FOR $2;
+ source_table ALIAS FOR $3;
+ columns RECORD;
+ create_sql TEXT;
+ insert_sql TEXT;
+ column_list TEXT := '';
+ column_count INTEGER := 0;
+ BEGIN
+ create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
+ FOR columns IN
+ SELECT table_schema, table_name, column_name, data_type
+ FROM information_schema.columns
+ WHERE table_schema = migration_schema AND table_name = source_table
+ LOOP
+ column_count := column_count + 1;
+ if column_count > 1 then
+ create_sql := create_sql || ', ';
+ column_list := column_list || ', ';
+ end if;
+ create_sql := create_sql || columns.column_name || ' ';
+ if columns.data_type = 'ARRAY' then
+ create_sql := create_sql || 'TEXT[]';
+ else
+ create_sql := create_sql || columns.data_type;
+ end if;
+ column_list := column_list || columns.column_name;
+ END LOOP;
+ create_sql := create_sql || ' ) INHERITS ( ' || migration_schema || '.' || parent_table || ' );';
+ --RAISE INFO 'create_sql = %', create_sql;
+ EXECUTE create_sql;
+ insert_sql := 'INSERT INTO ' || migration_schema || '.' || parent_table || '_legacy (' || column_list || ') SELECT ' || column_list || ' FROM ' || migration_schema || '.' || source_table || ';';
+ --RAISE INFO 'insert_sql = %', insert_sql;
+ EXECUTE insert_sql;
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
CREATE OR REPLACE FUNCTION migration_tools.insert_base_into_production (TEXT) RETURNS VOID AS $$
DECLARE
migration_schema ALIAS FOR $1;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
+ DECLARE
+ full_name TEXT := $1;
+ before_comma TEXT;
+ family_name TEXT := '';
+ first_given_name TEXT := '';
+ second_given_name TEXT := '';
+ suffix TEXT := '';
+ prefix TEXT := '';
+ BEGIN
+ before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
+ suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
+
+ IF suffix = before_comma THEN
+ suffix := '';
+ END IF;
+
+ family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
+ first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
+ second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\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.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
DECLARE
full_name TEXT := $1;
END;
$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix2 (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;
+
+ family_name := BTRIM(REPLACE(REPLACE(family_name,',',''),'"',''));
+ first_given_name := BTRIM(REPLACE(REPLACE(first_given_name,',',''),'"',''));
+ second_given_name := BTRIM(REPLACE(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.address_parse_out_citystatezip (TEXT) RETURNS TEXT[] AS $$
DECLARE
city_state_zip TEXT := $1;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEXT AS $$
+ my ($address) = @_;
+
+ use Geo::StreetAddress::US;
+
+ my $a = Geo::StreetAddress::US->parse_location($address);
+
+ return [
+ "$a->{number} $a->{prefix} $a->{street} $a->{type} $a->{suffix}"
+ ,"$a->{sec_unit_type} $a->{sec_unit_num}"
+ ,$a->{city}
+ ,$a->{state}
+ ,$a->{zip}
+ ];
+$$ LANGUAGE PLPERLU STABLE;
+
+DROP TABLE IF EXISTS migration_tools.usps_suffixes;
+CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
+INSERT INTO migration_tools.usps_suffixes VALUES
+ ('ALLEE','ALY'),
+ ('ALLEY','ALY'),
+ ('ALLY','ALY'),
+ ('ALY','ALY'),
+ ('ANEX','ANX'),
+ ('ANNEX','ANX'),
+ ('ANNX','ANX'),
+ ('ANX','ANX'),
+ ('ARCADE','ARC'),
+ ('ARC','ARC'),
+ ('AV','AVE'),
+ ('AVE','AVE'),
+ ('AVEN','AVE'),
+ ('AVENU','AVE'),
+ ('AVENUE','AVE'),
+ ('AVN','AVE'),
+ ('AVNUE','AVE'),
+ ('BAYOO','BYU'),
+ ('BAYOU','BYU'),
+ ('BCH','BCH'),
+ ('BEACH','BCH'),
+ ('BEND','BND'),
+ ('BLF','BLF'),
+ ('BLUF','BLF'),
+ ('BLUFF','BLF'),
+ ('BLUFFS','BLFS'),
+ ('BLVD','BLVD'),
+ ('BND','BND'),
+ ('BOT','BTM'),
+ ('BOTTM','BTM'),
+ ('BOTTOM','BTM'),
+ ('BOUL','BLVD'),
+ ('BOULEVARD','BLVD'),
+ ('BOULV','BLVD'),
+ ('BRANCH','BR'),
+ ('BR','BR'),
+ ('BRDGE','BRG'),
+ ('BRG','BRG'),
+ ('BRIDGE','BRG'),
+ ('BRK','BRK'),
+ ('BRNCH','BR'),
+ ('BROOK','BRK'),
+ ('BROOKS','BRKS'),
+ ('BTM','BTM'),
+ ('BURG','BG'),
+ ('BURGS','BGS'),
+ ('BYPA','BYP'),
+ ('BYPAS','BYP'),
+ ('BYPASS','BYP'),
+ ('BYP','BYP'),
+ ('BYPS','BYP'),
+ ('CAMP','CP'),
+ ('CANYN','CYN'),
+ ('CANYON','CYN'),
+ ('CAPE','CPE'),
+ ('CAUSEWAY','CSWY'),
+ ('CAUSWAY','CSWY'),
+ ('CEN','CTR'),
+ ('CENT','CTR'),
+ ('CENTER','CTR'),
+ ('CENTERS','CTRS'),
+ ('CENTR','CTR'),
+ ('CENTRE','CTR'),
+ ('CIRC','CIR'),
+ ('CIR','CIR'),
+ ('CIRCL','CIR'),
+ ('CIRCLE','CIR'),
+ ('CIRCLES','CIRS'),
+ ('CK','CRK'),
+ ('CLB','CLB'),
+ ('CLF','CLF'),
+ ('CLFS','CLFS'),
+ ('CLIFF','CLF'),
+ ('CLIFFS','CLFS'),
+ ('CLUB','CLB'),
+ ('CMP','CP'),
+ ('CNTER','CTR'),
+ ('CNTR','CTR'),
+ ('CNYN','CYN'),
+ ('COMMON','CMN'),
+ ('COR','COR'),
+ ('CORNER','COR'),
+ ('CORNERS','CORS'),
+ ('CORS','CORS'),
+ ('COURSE','CRSE'),
+ ('COURT','CT'),
+ ('COURTS','CTS'),
+ ('COVE','CV'),
+ ('COVES','CVS'),
+ ('CP','CP'),
+ ('CPE','CPE'),
+ ('CRCL','CIR'),
+ ('CRCLE','CIR'),
+ ('CR','CRK'),
+ ('CRECENT','CRES'),
+ ('CREEK','CRK'),
+ ('CRESCENT','CRES'),
+ ('CRES','CRES'),
+ ('CRESENT','CRES'),
+ ('CREST','CRST'),
+ ('CRK','CRK'),
+ ('CROSSING','XING'),
+ ('CROSSROAD','XRD'),
+ ('CRSCNT','CRES'),
+ ('CRSE','CRSE'),
+ ('CRSENT','CRES'),
+ ('CRSNT','CRES'),
+ ('CRSSING','XING'),
+ ('CRSSNG','XING'),
+ ('CRT','CT'),
+ ('CSWY','CSWY'),
+ ('CT','CT'),
+ ('CTR','CTR'),
+ ('CTS','CTS'),
+ ('CURVE','CURV'),
+ ('CV','CV'),
+ ('CYN','CYN'),
+ ('DALE','DL'),
+ ('DAM','DM'),
+ ('DIV','DV'),
+ ('DIVIDE','DV'),
+ ('DL','DL'),
+ ('DM','DM'),
+ ('DR','DR'),
+ ('DRIV','DR'),
+ ('DRIVE','DR'),
+ ('DRIVES','DRS'),
+ ('DRV','DR'),
+ ('DVD','DV'),
+ ('DV','DV'),
+ ('ESTATE','EST'),
+ ('ESTATES','ESTS'),
+ ('EST','EST'),
+ ('ESTS','ESTS'),
+ ('EXP','EXPY'),
+ ('EXPRESS','EXPY'),
+ ('EXPRESSWAY','EXPY'),
+ ('EXPR','EXPY'),
+ ('EXPW','EXPY'),
+ ('EXPY','EXPY'),
+ ('EXTENSION','EXT'),
+ ('EXTENSIONS','EXTS'),
+ ('EXT','EXT'),
+ ('EXTN','EXT'),
+ ('EXTNSN','EXT'),
+ ('EXTS','EXTS'),
+ ('FALL','FALL'),
+ ('FALLS','FLS'),
+ ('FERRY','FRY'),
+ ('FIELD','FLD'),
+ ('FIELDS','FLDS'),
+ ('FLAT','FLT'),
+ ('FLATS','FLTS'),
+ ('FLD','FLD'),
+ ('FLDS','FLDS'),
+ ('FLS','FLS'),
+ ('FLT','FLT'),
+ ('FLTS','FLTS'),
+ ('FORD','FRD'),
+ ('FORDS','FRDS'),
+ ('FOREST','FRST'),
+ ('FORESTS','FRST'),
+ ('FORGE','FRG'),
+ ('FORGES','FRGS'),
+ ('FORG','FRG'),
+ ('FORK','FRK'),
+ ('FORKS','FRKS'),
+ ('FORT','FT'),
+ ('FRD','FRD'),
+ ('FREEWAY','FWY'),
+ ('FREEWY','FWY'),
+ ('FRG','FRG'),
+ ('FRK','FRK'),
+ ('FRKS','FRKS'),
+ ('FRRY','FRY'),
+ ('FRST','FRST'),
+ ('FRT','FT'),
+ ('FRWAY','FWY'),
+ ('FRWY','FWY'),
+ ('FRY','FRY'),
+ ('FT','FT'),
+ ('FWY','FWY'),
+ ('GARDEN','GDN'),
+ ('GARDENS','GDNS'),
+ ('GARDN','GDN'),
+ ('GATEWAY','GTWY'),
+ ('GATEWY','GTWY'),
+ ('GATWAY','GTWY'),
+ ('GDN','GDN'),
+ ('GDNS','GDNS'),
+ ('GLEN','GLN'),
+ ('GLENS','GLNS'),
+ ('GLN','GLN'),
+ ('GRDEN','GDN'),
+ ('GRDN','GDN'),
+ ('GRDNS','GDNS'),
+ ('GREEN','GRN'),
+ ('GREENS','GRNS'),
+ ('GRN','GRN'),
+ ('GROVE','GRV'),
+ ('GROVES','GRVS'),
+ ('GROV','GRV'),
+ ('GRV','GRV'),
+ ('GTWAY','GTWY'),
+ ('GTWY','GTWY'),
+ ('HARB','HBR'),
+ ('HARBOR','HBR'),
+ ('HARBORS','HBRS'),
+ ('HARBR','HBR'),
+ ('HAVEN','HVN'),
+ ('HAVN','HVN'),
+ ('HBR','HBR'),
+ ('HEIGHT','HTS'),
+ ('HEIGHTS','HTS'),
+ ('HGTS','HTS'),
+ ('HIGHWAY','HWY'),
+ ('HIGHWY','HWY'),
+ ('HILL','HL'),
+ ('HILLS','HLS'),
+ ('HIWAY','HWY'),
+ ('HIWY','HWY'),
+ ('HL','HL'),
+ ('HLLW','HOLW'),
+ ('HLS','HLS'),
+ ('HOLLOW','HOLW'),
+ ('HOLLOWS','HOLW'),
+ ('HOLW','HOLW'),
+ ('HOLWS','HOLW'),
+ ('HRBOR','HBR'),
+ ('HT','HTS'),
+ ('HTS','HTS'),
+ ('HVN','HVN'),
+ ('HWAY','HWY'),
+ ('HWY','HWY'),
+ ('INLET','INLT'),
+ ('INLT','INLT'),
+ ('IS','IS'),
+ ('ISLAND','IS'),
+ ('ISLANDS','ISS'),
+ ('ISLANDS','SLNDS'),
+ ('ISLANDS','SS'),
+ ('ISLE','ISLE'),
+ ('ISLES','ISLE'),
+ ('ISLND','IS'),
+ ('I','SLNDS'),
+ ('ISS','ISS'),
+ ('JCTION','JCT'),
+ ('JCT','JCT'),
+ ('JCTN','JCT'),
+ ('JCTNS','JCTS'),
+ ('JCTS','JCTS'),
+ ('JUNCTION','JCT'),
+ ('JUNCTIONS','JCTS'),
+ ('JUNCTN','JCT'),
+ ('JUNCTON','JCT'),
+ ('KEY','KY'),
+ ('KEYS','KYS'),
+ ('KNL','KNL'),
+ ('KNLS','KNLS'),
+ ('KNOL','KNL'),
+ ('KNOLL','KNL'),
+ ('KNOLLS','KNLS'),
+ ('KY','KY'),
+ ('KYS','KYS'),
+ ('LAKE','LK'),
+ ('LAKES','LKS'),
+ ('LA','LN'),
+ ('LANDING','LNDG'),
+ ('LAND','LAND'),
+ ('LANE','LN'),
+ ('LANES','LN'),
+ ('LCK','LCK'),
+ ('LCKS','LCKS'),
+ ('LDGE','LDG'),
+ ('LDG','LDG'),
+ ('LF','LF'),
+ ('LGT','LGT'),
+ ('LIGHT','LGT'),
+ ('LIGHTS','LGTS'),
+ ('LK','LK'),
+ ('LKS','LKS'),
+ ('LNDG','LNDG'),
+ ('LNDNG','LNDG'),
+ ('LN','LN'),
+ ('LOAF','LF'),
+ ('LOCK','LCK'),
+ ('LOCKS','LCKS'),
+ ('LODGE','LDG'),
+ ('LODG','LDG'),
+ ('LOOP','LOOP'),
+ ('LOOPS','LOOP'),
+ ('MALL','MALL'),
+ ('MANOR','MNR'),
+ ('MANORS','MNRS'),
+ ('MDW','MDW'),
+ ('MDWS','MDWS'),
+ ('MEADOW','MDW'),
+ ('MEADOWS','MDWS'),
+ ('MEDOWS','MDWS'),
+ ('MEWS','MEWS'),
+ ('MILL','ML'),
+ ('MILLS','MLS'),
+ ('MISSION','MSN'),
+ ('MISSN','MSN'),
+ ('ML','ML'),
+ ('MLS','MLS'),
+ ('MNR','MNR'),
+ ('MNRS','MNRS'),
+ ('MNTAIN','MTN'),
+ ('MNT','MT'),
+ ('MNTN','MTN'),
+ ('MNTNS','MTNS'),
+ ('MOTORWAY','MTWY'),
+ ('MOUNTAIN','MTN'),
+ ('MOUNTAINS','MTNS'),
+ ('MOUNTIN','MTN'),
+ ('MOUNT','MT'),
+ ('MSN','MSN'),
+ ('MSSN','MSN'),
+ ('MTIN','MTN'),
+ ('MT','MT'),
+ ('MTN','MTN'),
+ ('NCK','NCK'),
+ ('NECK','NCK'),
+ ('ORCHARD','ORCH'),
+ ('ORCH','ORCH'),
+ ('ORCHRD','ORCH'),
+ ('OVAL','OVAL'),
+ ('OVERPASS','OPAS'),
+ ('OVL','OVAL'),
+ ('PARK','PARK'),
+ ('PARKS','PARK'),
+ ('PARKWAY','PKWY'),
+ ('PARKWAYS','PKWY'),
+ ('PARKWY','PKWY'),
+ ('PASSAGE','PSGE'),
+ ('PASS','PASS'),
+ ('PATH','PATH'),
+ ('PATHS','PATH'),
+ ('PIKE','PIKE'),
+ ('PIKES','PIKE'),
+ ('PINE','PNE'),
+ ('PINES','PNES'),
+ ('PK','PARK'),
+ ('PKWAY','PKWY'),
+ ('PKWY','PKWY'),
+ ('PKWYS','PKWY'),
+ ('PKY','PKWY'),
+ ('PLACE','PL'),
+ ('PLAINES','PLNS'),
+ ('PLAIN','PLN'),
+ ('PLAINS','PLNS'),
+ ('PLAZA','PLZ'),
+ ('PLN','PLN'),
+ ('PLNS','PLNS'),
+ ('PL','PL'),
+ ('PLZA','PLZ'),
+ ('PLZ','PLZ'),
+ ('PNES','PNES'),
+ ('POINT','PT'),
+ ('POINTS','PTS'),
+ ('PORT','PRT'),
+ ('PORTS','PRTS'),
+ ('PRAIRIE','PR'),
+ ('PRARIE','PR'),
+ ('PRK','PARK'),
+ ('PR','PR'),
+ ('PRR','PR'),
+ ('PRT','PRT'),
+ ('PRTS','PRTS'),
+ ('PT','PT'),
+ ('PTS','PTS'),
+ ('RADIAL','RADL'),
+ ('RADIEL','RADL'),
+ ('RADL','RADL'),
+ ('RAD','RADL'),
+ ('RAMP','RAMP'),
+ ('RANCHES','RNCH'),
+ ('RANCH','RNCH'),
+ ('RAPID','RPD'),
+ ('RAPIDS','RPDS'),
+ ('RDGE','RDG'),
+ ('RDG','RDG'),
+ ('RDGS','RDGS'),
+ ('RD','RD'),
+ ('RDS','RDS'),
+ ('REST','RST'),
+ ('RIDGE','RDG'),
+ ('RIDGES','RDGS'),
+ ('RIVER','RIV'),
+ ('RIV','RIV'),
+ ('RIVR','RIV'),
+ ('RNCH','RNCH'),
+ ('RNCHS','RNCH'),
+ ('ROAD','RD'),
+ ('ROADS','RDS'),
+ ('ROUTE','RTE'),
+ ('ROW','ROW'),
+ ('RPD','RPD'),
+ ('RPDS','RPDS'),
+ ('RST','RST'),
+ ('RUE','RUE'),
+ ('RUN','RUN'),
+ ('RVR','RIV'),
+ ('SHL','SHL'),
+ ('SHLS','SHLS'),
+ ('SHOAL','SHL'),
+ ('SHOALS','SHLS'),
+ ('SHOAR','SHR'),
+ ('SHOARS','SHRS'),
+ ('SHORE','SHR'),
+ ('SHORES','SHRS'),
+ ('SHR','SHR'),
+ ('SHRS','SHRS'),
+ ('SKYWAY','SKWY'),
+ ('SMT','SMT'),
+ ('SPG','SPG'),
+ ('SPGS','SPGS'),
+ ('SPNG','SPG'),
+ ('SPNGS','SPGS'),
+ ('SPRING','SPG'),
+ ('SPRINGS','SPGS'),
+ ('SPRNG','SPG'),
+ ('SPRNGS','SPGS'),
+ ('SPUR','SPUR'),
+ ('SPURS','SPUR'),
+ ('SQRE','SQ'),
+ ('SQR','SQ'),
+ ('SQRS','SQS'),
+ ('SQ','SQ'),
+ ('SQUARE','SQ'),
+ ('SQUARES','SQS'),
+ ('SQU','SQ'),
+ ('STA','STA'),
+ ('STATION','STA'),
+ ('STATN','STA'),
+ ('STN','STA'),
+ ('STRA','STRA'),
+ ('STRAVEN','STRA'),
+ ('STRAVENUE','STRA'),
+ ('STRAVE','STRA'),
+ ('STRAVN','STRA'),
+ ('STRAV','STRA'),
+ ('STREAM','STRM'),
+ ('STREETS','STS'),
+ ('STREET','ST'),
+ ('STREME','STRM'),
+ ('STRM','STRM'),
+ ('STR','ST'),
+ ('STRT','ST'),
+ ('STRVN','STRA'),
+ ('STRVNUE','STRA'),
+ ('ST','ST'),
+ ('SUMIT','SMT'),
+ ('SUMITT','SMT'),
+ ('SUMMIT','SMT'),
+ ('TERRACE','TER'),
+ ('TERR','TER'),
+ ('TER','TER'),
+ ('THROUGHWAY','TRWY'),
+ ('TPKE','TPKE'),
+ ('TPK','TPKE'),
+ ('TRACES','TRCE'),
+ ('TRACE','TRCE'),
+ ('TRACKS','TRAK'),
+ ('TRACK','TRAK'),
+ ('TRAFFICWAY','TRFY'),
+ ('TRAILS','TRL'),
+ ('TRAIL','TRL'),
+ ('TRAK','TRAK'),
+ ('TRCE','TRCE'),
+ ('TRFY','TRFY'),
+ ('TRKS','TRAK'),
+ ('TRK','TRAK'),
+ ('TRLS','TRL'),
+ ('TRL','TRL'),
+ ('TRNPK','TPKE'),
+ ('TRPK','TPKE'),
+ ('TR','TRL'),
+ ('TUNEL','TUNL'),
+ ('TUNLS','TUNL'),
+ ('TUNL','TUNL'),
+ ('TUNNELS','TUNL'),
+ ('TUNNEL','TUNL'),
+ ('TUNNL','TUNL'),
+ ('TURNPIKE','TPKE'),
+ ('TURNPK','TPKE'),
+ ('UNDERPASS','UPAS'),
+ ('UNIONS','UNS'),
+ ('UNION','UN'),
+ ('UN','UN'),
+ ('VALLEYS','VLYS'),
+ ('VALLEY','VLY'),
+ ('VALLY','VLY'),
+ ('VDCT','IA'),
+ ('VIADCT','VIA'),
+ ('VIADUCT','IA'),
+ ('VIADUCT','VIA'),
+ ('VIA','VIA'),
+ ('VIEWS','VWS'),
+ ('VIEW','VW'),
+ ('VILLAGES','VLGS'),
+ ('VILLAGE','VLG'),
+ ('VILLAG','VLG'),
+ ('VILLE','VL'),
+ ('VILLG','VLG'),
+ ('VILLIAGE','VLG'),
+ ('VILL','VLG'),
+ ('VISTA','VIS'),
+ ('VIST','VIS'),
+ ('VIS','VIS'),
+ ('VLGS','VLGS'),
+ ('VLG','VLG'),
+ ('VLLY','VLY'),
+ ('VL','VL'),
+ ('VLYS','VLYS'),
+ ('VLY','VLY'),
+ ('VSTA','VIS'),
+ ('VST','VIS'),
+ ('VWS','VWS'),
+ ('VW','VW'),
+ ('WALKS','WALK'),
+ ('WALK','WALK'),
+ ('WALL','WALL'),
+ ('WAYS','WAYS'),
+ ('WAY','WAY'),
+ ('WELLS','WLS'),
+ ('WELL','WL'),
+ ('WLS','WLS'),
+ ('WY','WAY'),
+ ('XING','XING');
+
+-- this function should get a smaller range of inputs and benefit more from STABLE, hopefully speeding things up
+CREATE OR REPLACE FUNCTION migration_tools._normalize_address_suffix (TEXT) RETURNS TEXT AS $$
+ DECLARE
+ suffix TEXT := $1;
+ _r RECORD;
+ BEGIN
+ --RAISE INFO 'suffix = %', suffix;
+ FOR _r IN (SELECT * FROM migration_tools.usps_suffixes) LOOP
+ suffix := REGEXP_REPLACE( suffix, _r.suffix_from, _r.suffix_to, 'i');
+ END LOOP;
+ RETURN suffix;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.normalize_address_suffix (TEXT) RETURNS TEXT AS $$
+ BEGIN
+ RETURN CASE
+ WHEN $1 ~ '\s\S+$' THEN REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\1' ) || migration_tools._normalize_address_suffix( REGEXP_REPLACE( $1, '^(.*\s)(\S+)$', '\2' ) )
+ ELSE $1
+ END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.rebarcode (o TEXT, t BIGINT) RETURNS TEXT AS $$
DECLARE
n TEXT := o;
END;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
+BEGIN
+-- Expects the following table/columns:
+
+-- export_biblio_tsv:
+-- l_bibid | 1
+-- l_create_dt | 2007-03-07 09:03:09
+-- l_last_change_dt | 2015-01-23 11:18:54
+-- l_last_change_userid | 2
+-- l_material_cd | 10
+-- l_collection_cd | 13
+-- l_call_nmbr1 | Canada
+-- l_call_nmbr2 | ON
+-- l_call_nmbr3 | Ottawa 18
+-- l_title | Art and the courts : France ad England
+-- l_title_remainder | from 1259-1328
+-- l_responsibility_stmt |
+-- l_author | National Gallery of Canada
+-- l_topic1 |
+-- l_topic2 |
+-- l_topic3 |
+-- l_topic4 |
+-- l_topic5 |
+-- l_opac_flg | Y
+-- l_flag_attention | 0
+
+-- export_biblio_field_tsv:
+-- l_bibid | 1
+-- l_fieldid | 1
+-- l_tag | 720
+-- l_ind1_cd | N
+-- l_ind2_cd | N
+-- l_subfield_cd | a
+-- l_field_data | Brieger, Peter Henry
+
+-- Map export_biblio_tsv as follows:
+-- l_call_nmbr? -> 099a
+-- l_author -> 100a
+-- l_title -> 245a
+-- l_title_remainder -> 245b
+-- l_responsibility_stmt -> 245c
+-- l_topic? -> 650a
+-- l_bibid -> 001
+
+RETURN
+ migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
+FROM (
+ select
+ array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
+ array_agg(l_ind1_cd) as "ind1",
+ array_agg(l_ind2_cd) as "ind2",
+ array_agg(l_field_data) as "data"
+ from (
+ select
+ l_tag,
+ l_subfield_cd,
+ l_ind1_cd,
+ l_ind2_cd,
+ l_field_data
+ from export_biblio_field_tsv
+ where l_bibid = x_bibid
+ union
+ select
+ '099' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ concat_ws(' ',
+ nullif(btrim(l_call_nmbr1),''),
+ nullif(btrim(l_call_nmbr2),''),
+ nullif(btrim(l_call_nmbr3),'')
+ ) as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid
+ union
+ select
+ '100' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_author as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
+ union
+ select
+ '245' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_title as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
+ union
+ select
+ '245' as "l_tag",
+ 'b' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_title_remainder as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic1 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic2 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic3 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic4 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic5 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
+ union
+ select
+ '001' as "l_tag",
+ '' as "l_subfield_cd",
+ '' as "l_ind1_cd",
+ '' as "l_ind2_cd",
+ l_bibid as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid
+ ) x
+) y;
+
+END
+$func$ LANGUAGE plpgsql;
+
+-- add koha holding tag to marc
+DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
+
+CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber TEXT)
+ RETURNS TEXT
+ LANGUAGE plperlu
+AS $function$
+use strict;
+use warnings;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'utf8');
+
+binmode(STDERR, ':bytes');
+binmode(STDOUT, ':utf8');
+binmode(STDERR, ':utf8');
+
+my ($marc_xml, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
+
+$marc_xml =~ s/(<leader>.........)./${1}a/;
+
+eval {
+ $marc_xml = MARC::Record->new_from_xml($marc_xml);
+};
+if ($@) {
+ #elog("could not parse $bibid: $@\n");
+ import MARC::File::XML (BinaryEncoding => 'utf8');
+ return $marc_xml;
+}
+
+my $new_field = new MARC::Field(
+ $tag, $ind1, $ind2,
+ 'a' => $homebranch,
+ 'b' => $holdingbranch,
+ 'c' => $location,
+ 'p' => $barcode,
+ 'y' => $itype
+);
+
+if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
+if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
+if ($price) { $new_field->add_subfields('g' => $price); }
+if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
+if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
+if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
+if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
+if ($stack) { $new_field->add_subfields('j' => $stack); }
+if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
+if ($damaged) { $new_field->add_subfields('4' => $damaged); }
+if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
+if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
+if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
+if ($issues) { $new_field->add_subfields('l' => $issues); }
+if ($renewals) { $new_field->add_subfields('m' => $renewals); }
+if ($reserves) { $new_field->add_subfields('n' => $reserves); }
+if ($restricted) { $new_field->add_subfields('5' => $restricted); }
+if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
+if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
+if ($onloan) { $new_field->add_subfields('q' => $onloan); }
+if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
+if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
+if ($ccode) { $new_field->add_subfields('8' => $ccode); }
+if ($materials) { $new_field->add_subfields('3' => $materials); }
+if ($uri) { $new_field->add_subfields('u' => $uri); }
+if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
+if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
+if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
+
+$marc_xml->insert_grouped_field( $new_field );
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
DECLARE
attempt_value ALIAS FOR $1;
return $barcode . $checkdigit;
$$ LANGUAGE PLPERLU STRICT STABLE;
+-- add_mod16_checkdigit
+-- $barcode source barcode
+--
+-- https://www.activebarcode.com/codes/checkdigit/modulo16.html
+
+CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$
+ my $barcode = shift;
+
+ my @digits = split //, $barcode;
+ my $total = 0;
+ foreach $digit (@digits) {
+ if ($digit =~ /[0-9]/) { $total += $digit;
+ } elsif ($digit eq '-') { $total += 10;
+ } elsif ($digit eq '$') { $total += 11;
+ } elsif ($digit eq ':') { $total += 12;
+ } elsif ($digit eq '/') { $total += 13;
+ } elsif ($digit eq '.') { $total += 14;
+ } elsif ($digit eq '+') { $total += 15;
+ } elsif ($digit eq 'A') { $total += 16;
+ } elsif ($digit eq 'B') { $total += 17;
+ } elsif ($digit eq 'C') { $total += 18;
+ } elsif ($digit eq 'D') { $total += 19;
+ } else { die "invalid digit <$digit>";
+ }
+ }
+ my $remainder = $total % 16;
+ my $difference = 16 - $remainder;
+ my $checkdigit;
+ if ($difference < 10) { $checkdigit = $difference;
+ } elsif ($difference == 10) { $checkdigit = '-';
+ } elsif ($difference == 11) { $checkdigit = '$';
+ } elsif ($difference == 12) { $checkdigit = ':';
+ } elsif ($difference == 13) { $checkdigit = '/';
+ } elsif ($difference == 14) { $checkdigit = '.';
+ } elsif ($difference == 15) { $checkdigit = '+';
+ } else { die "error calculating checkdigit";
+ }
+
+ return $barcode . $checkdigit;
+$$ LANGUAGE PLPERLU STRICT STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
DECLARE
phone TEXT := $1;
$$ LANGUAGE plpgsql;
+-- TODO: make another version of the procedure below that can work with specified copy staging tables
+-- The following should track the logic of OpenILS::Application::AppUtils::get_copy_price
+CREATE OR REPLACE FUNCTION migration_tools.get_copy_price( item BIGINT ) RETURNS NUMERIC AS $$
+DECLARE
+ context_lib INT;
+ charge_lost_on_zero BOOLEAN;
+ min_price NUMERIC;
+ max_price NUMERIC;
+ default_price NUMERIC;
+ working_price NUMERIC;
+
+BEGIN
+
+ SELECT INTO context_lib CASE WHEN call_number = -1 THEN circ_lib ELSE owning_lib END
+ FROM asset.copy ac, asset.call_number acn WHERE ac.call_number = acn.id AND ac.id = item;
+
+ SELECT INTO charge_lost_on_zero value
+ FROM actor.org_unit_ancestor_setting('circ.charge_lost_on_zero',context_lib);
+
+ SELECT INTO min_price value
+ FROM actor.org_unit_ancestor_setting('circ.min_item_price',context_lib);
+
+ SELECT INTO max_price value
+ FROM actor.org_unit_ancestor_setting('circ.max_item_price',context_lib);
+
+ SELECT INTO default_price value
+ FROM actor.org_unit_ancestor_setting('cat.default_item_price',context_lib);
+
+ SELECT INTO working_price price FROM asset.copy WHERE id = item;
+
+ IF (working_price IS NULL OR (working_price = 0 AND charge_lost_on_zero)) THEN
+ working_price := default_price;
+ END IF;
+
+ IF (max_price IS NOT NULL AND working_price > max_price) THEN
+ working_price := max_price;
+ END IF;
+
+ IF (min_price IS NOT NULL AND working_price < min_price) THEN
+ IF (working_price <> 0 OR charge_lost_on_zero IS NULL OR charge_lost_on_zero) THEN
+ working_price := min_price;
+ END IF;
+ END IF;
+
+ RETURN working_price;
+
+END;
+
+$$ LANGUAGE plpgsql;
+
CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) RETURNS VOID AS $$
-- Usage:
recurring_fine = rrf.normal,
max_fine =
CASE rmf.is_percent
- WHEN TRUE THEN (rmf.amount / 100.0) * ac.price
+ WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id)
ELSE rmf.amount
END,
renewal_remaining = rcd.max_renewals,
$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$
-
-use MARC::Record;
-use MARC::File::XML (BinaryEncoding => 'UTF-8');
-use MARC::Charset;
-
-MARC::Charset->assume_unicode(1);
-
-my $xml = shift;
-
-eval {
- my $r = MARC::Record->new_from_xml( $xml );
- my $output_xml = $r->as_xml_record();
-};
-if ($@) {
- return 0;
-} else {
- return 1;
-}
-
-$func$ LANGUAGE PLPERLU;
-COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML';
-
CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$
BEGIN
EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ ||
END;
$FUNC$ LANGUAGE PLPGSQL;
-CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
-
-use strict;
-use warnings;
-
-use MARC::Record;
-use MARC::File::XML (BinaryEncoding => 'UTF-8');
-use MARC::Charset;
-
-MARC::Charset->assume_unicode(1);
-
-my $target_xml = shift;
-my $source_xml = shift;
-my $tags = shift;
-
-my $target;
-my $source;
-
-eval { $target = MARC::Record->new_from_xml( $target_xml ); };
-if ($@) {
- return;
-}
-eval { $source = MARC::Record->new_from_xml( $source_xml ); };
-if ($@) {
- return;
-}
-
-my $source_id = $source->subfield('901', 'c');
-$source_id = $source->subfield('903', 'a') unless $source_id;
-my $target_id = $target->subfield('901', 'c');
-$target_id = $target->subfield('903', 'a') unless $target_id;
-
-my %existing_fields;
-foreach my $tag (@$tags) {
- my %existing_fields = map { $_->as_formatted() => 1 } $target->field($tag);
- my @to_add = grep { not exists $existing_fields{$_->as_formatted()} } $source->field($tag);
- $target->insert_fields_ordered(map { $_->clone() } @to_add);
- if (@to_add) {
- elog(NOTICE, "Merged $tag tag(s) from $source_id to $target_id");
- }
-}
-
-my $xml = $target->as_xml_record;
-$xml =~ s/^<\?.+?\?>$//mo;
-$xml =~ s/\n//sgo;
-$xml =~ s/>\s+</></sgo;
-
-return $xml;
-
-$func$ LANGUAGE PLPERLU;
-COMMENT ON FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) IS 'Given two MARCXML strings and an array of tags, returns MARCXML representing the merge of the specified fields from the second MARCXML record into the first.';
-
-CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[]) RETURNS TEXT AS $func$
-
-use strict;
-use warnings;
-
-use MARC::Record;
-use MARC::File::XML (BinaryEncoding => 'UTF-8');
-use Text::CSV;
-
-my $in_tags = shift;
-my $in_values = shift;
-
-# hack-and-slash parsing of array-passed-as-string;
-# this can go away once everybody is running Postgres 9.1+
-my $csv = Text::CSV->new({binary => 1});
-$in_tags =~ s/^{//;
-$in_tags =~ s/}$//;
-my $status = $csv->parse($in_tags);
-my $tags = [ $csv->fields() ];
-$in_values =~ s/^{//;
-$in_values =~ s/}$//;
-$status = $csv->parse($in_values);
-my $values = [ $csv->fields() ];
-
-my $marc = MARC::Record->new();
-
-$marc->leader('00000nam a22000007 4500');
-$marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
-
-foreach my $i (0..$#$tags) {
- my ($tag, $sf);
- if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
- $tag = $1;
- $sf = $2;
- $marc->append_fields(MARC::Field->new($tag, ' ', ' ', $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
- } elsif ($tags->[$i] =~ /^(\d{3})$/) {
- $tag = $1;
- $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
- }
-}
-
-my $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.make_stub_bib (text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
-The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
-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;
+CREATE OR REPLACE FUNCTION migration_tools.split_rows_on_column_with_delimiter(schemaname TEXT, tablename TEXT, matchcol TEXT, delimiter TEXT) RETURNS VOID AS $FUNC$
+DECLARE
+ id BIGINT;
+ loopq TEXT;
+ cols TEXT[];
+ splitst TEXT;
+BEGIN
+ loopq := 'SELECT id FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' ~ $1 ORDER BY id';
+ SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> 'id' AND column_name <> matchcol;
+ FOR id IN EXECUTE loopq USING delimiter LOOP
+ RAISE NOTICE 'splitting row from %.% with id = %', schemaname, tablename, id;
+ splitst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ', ' || matchcol || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || ', s.token ' ||
+ ' FROM ' || schemaname || '.' || tablename || ' t, UNNEST(STRING_TO_ARRAY(t.' || matchcol || ', $2)) s(token) WHERE id = $1';
+ EXECUTE splitst USING id, delimiter;
+ END LOOP;
+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 $$
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
-CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$
- my ($marcxml) = @_;
-
- 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->leader();
- };
- return $field;
-$$ LANGUAGE PLPERLU STABLE;
-
-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;
+-- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}');
+CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$
+ DECLARE
+ org ALIAS FOR $1;
+ target_event_defs ALIAS FOR $2;
+ BEGIN
+ DROP TABLE IF EXISTS new_atevdefs;
+ CREATE TEMP TABLE new_atevdefs (atevdef INTEGER);
+ FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,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'
+ ,org
+ ,name || ' (clone of '||target_event_defs[i]||')'
+ ,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_defs[i]
+ ;
+ 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_defs[i]
+ ;
+ 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_defs[i]
+ ;
+ END LOOP;
+ RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs);
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
-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.reset_event (BIGINT) RETURNS VOID AS $$
+ UPDATE
+ action_trigger.event
+ SET
+ start_time = NULL
+ ,update_time = NULL
+ ,complete_time = NULL
+ ,update_process = NULL
+ ,state = 'pending'
+ ,template_output = NULL
+ ,error_output = NULL
+ ,async_output = NULL
+ WHERE
+ id = $1;
+$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
SELECT action.find_hold_matrix_matchpoint(
-- convenience functions for handling copy_location maps
-
CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
+ SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
DECLARE
table_schema ALIAS FOR $1;
table_name ALIAS FOR $2;
org_shortname ALIAS FOR $3;
org_range ALIAS FOR $4;
+ make_assertion ALIAS FOR $5;
proceed BOOLEAN;
org INTEGER;
+ -- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
+ -- though we'll still use the passed org for the full path traversal when needed
+ x_org_found BOOLEAN;
+ x_org INTEGER;
org_list INTEGER[];
o INTEGER;
+ row_count NUMERIC;
BEGIN
EXECUTE 'SELECT EXISTS (
SELECT 1
and column_name = ''desired_shelf''
)' INTO proceed USING table_schema, table_name;
IF NOT proceed THEN
- RAISE EXCEPTION 'Missing column desired_shelf';
+ RAISE EXCEPTION 'Missing column desired_shelf';
END IF;
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = ''x_org''
+ )' INTO x_org_found USING table_schema, table_name;
+
SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
IF org IS NULL THEN
RAISE EXCEPTION 'Cannot find org by shortname';
|| quote_ident(table_name)
|| ' ADD COLUMN x_shelf INTEGER';
- EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset_copy_location b'
- || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
- || ' AND b.owning_lib = $1'
- || ' AND NOT b.deleted'
- USING org;
+ IF x_org_found THEN
+ RAISE INFO 'Found x_org column';
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_shelf = b.id FROM asset_copy_location b'
+ || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
+ || ' AND b.owning_lib = x_org'
+ || ' AND NOT b.deleted';
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_shelf = b.id FROM asset.copy_location b'
+ || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
+ || ' AND b.owning_lib = x_org'
+ || ' AND x_shelf IS NULL'
+ || ' AND NOT b.deleted';
+ ELSE
+ RAISE INFO 'Did not find x_org column';
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_shelf = b.id FROM asset_copy_location b'
+ || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
+ || ' AND b.owning_lib = $1'
+ || ' AND NOT b.deleted'
+ USING org;
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_shelf = b.id FROM asset_copy_location b'
+ || ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
+ || ' AND b.owning_lib = $1'
+ || ' AND x_shelf IS NULL'
+ || ' AND NOT b.deleted'
+ USING org;
+ END IF;
FOREACH o IN ARRAY org_list LOOP
+ RAISE INFO 'Considering org %', o;
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_shelf = id FROM asset.copy_location b'
+ || ' SET x_shelf = b.id FROM asset.copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
|| ' AND b.owning_lib = $1 AND x_shelf IS NULL'
|| ' AND NOT b.deleted'
USING o;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ RAISE INFO 'Updated % rows', row_count;
END LOOP;
- EXECUTE 'SELECT migration_tools.assert(
- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
- ''Cannot find a desired location'',
- ''Found all desired locations''
- );';
+ IF make_assertion THEN
+ EXECUTE 'SELECT migration_tools.assert(
+ NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
+ ''Cannot find a desired location'',
+ ''Found all desired locations''
+ );';
+ END IF;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
|| ' ADD COLUMN x_org INTEGER';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_org = id FROM actor.org_unit b'
+ || ' SET x_org = b.id FROM actor.org_unit b'
|| ' WHERE BTRIM(a.desired_org) = BTRIM(b.shortname)';
EXECUTE 'SELECT migration_tools.assert(
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
|| ' SET x_migrate = CASE'
|| ' WHEN BTRIM(desired_not_migrate) = ''TRUE'' THEN FALSE'
+ || ' WHEN BTRIM(desired_not_migrate) = ''DNM'' THEN FALSE'
+ || ' WHEN BTRIM(desired_not_migrate) = ''Do Not Migrate'' THEN FALSE'
|| ' WHEN BTRIM(desired_not_migrate) = ''FALSE'' THEN TRUE'
+ || ' WHEN BTRIM(desired_not_migrate) = ''Migrate'' THEN TRUE'
|| ' WHEN BTRIM(desired_not_migrate) = '''' THEN TRUE'
|| ' END';
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+-- convenience function for handling desired_not_migrate
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_name ALIAS FOR $2;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = ''desired_barred_or_blocked''
+ )' INTO proceed USING table_schema, table_name;
+ IF NOT proceed THEN
+ RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
+ END IF;
+
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' DROP COLUMN IF EXISTS x_barred';
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' ADD COLUMN x_barred BOOLEAN';
+
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' DROP COLUMN IF EXISTS x_blocked';
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' ADD COLUMN x_blocked BOOLEAN';
+
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_barred = CASE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
+ || ' END';
+
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_blocked = CASE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
+ || ' END';
+
+ EXECUTE 'SELECT migration_tools.assert(
+ NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
+ ''Not all desired_barred_or_blocked values understood'',
+ ''All desired_barred_or_blocked values understood''
+ );';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
-- convenience function for handling desired_profile
CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
|| ' ADD COLUMN x_profile INTEGER';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_profile = id FROM permission.grp_tree b'
+ || ' SET x_profile = b.id FROM permission.grp_tree b'
|| ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
EXECUTE 'SELECT migration_tools.assert(
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
AND NOT EXISTS (
SELECT id
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
ORDER BY 1,3;'
USING org, org_list;
END
$$ LANGUAGE plpgsql;
--- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only
-DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT);
-CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT)
- RETURNS TEXT
- LANGUAGE plperlu
-AS $function$
-use strict;
-use warnings;
-
-use MARC::Record;
-use MARC::File::XML (BinaryEncoding => 'utf8');
-
-binmode(STDERR, ':bytes');
-binmode(STDOUT, ':utf8');
-binmode(STDERR, ':utf8');
-
-my $marc_xml = shift;
-my $matching_u_text = shift;
-my $new_9_to_set = shift;
-
-$marc_xml =~ s/(<leader>.........)./${1}a/;
-
-eval {
- $marc_xml = MARC::Record->new_from_xml($marc_xml);
-};
-if ($@) {
- #elog("could not parse $bibid: $@\n");
- import MARC::File::XML (BinaryEncoding => 'utf8');
- return;
-}
-
-my @uris = $marc_xml->field('856');
-return unless @uris;
-
-foreach my $field (@uris) {
- my $sfu = $field->subfield('u');
- my $ind2 = $field->indicator('2');
- if (!defined $ind2) { next; }
- if ($ind2 ne '0') { next; }
- if (!defined $sfu) { next; }
- if ($sfu =~ m/$matching_u_text/) {
- $field->add_subfields( '9' => $new_9_to_set );
- last;
- }
-}
-
-return $marc_xml->as_xml_record();
-
-$function$;
-
-DROP FUNCTION IF EXISTS migration_tools.add_sf9(BIGINT, TEXT, TEXT, REGCLASS);
-CREATE OR REPLACE FUNCTION migration_tools.add_sf9(bib_id BIGINT, target_u_text TEXT, sf9_text TEXT, bib_table REGCLASS)
- RETURNS BOOLEAN AS
-$BODY$
-DECLARE
- source_xml TEXT;
- new_xml TEXT;
- r BOOLEAN;
-BEGIN
-
- EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml;
-
- SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml;
-
- r = FALSE;
- new_xml = '$_$' || new_xml || '$_$';
-
- IF new_xml != source_xml THEN
- EXECUTE 'UPDATE ' || bib_table || ' SET marc = ' || new_xml || ' WHERE id = ' || bib_id;
- r = TRUE;
- END IF;
-
- RETURN r;
-
-END;
-$BODY$ LANGUAGE plpgsql;
+function$;
-- convenience function for linking to the item staging table
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_item = id FROM asset_copy_legacy b'
+ || ' SET x_item = b.id FROM asset_copy_legacy b'
|| ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
|| ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_item = id FROM asset_copy_legacy b'
+ || ' SET x_item = b.id FROM asset_copy_legacy b'
|| ' WHERE a.' || quote_ident(foreign_column_name)
|| ' = b.' || quote_ident(main_column_name);
END IF;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_user = id FROM actor_usr_legacy b'
+ || ' SET x_user = b.id FROM actor_usr_legacy b'
|| ' WHERE BTRIM(a.' || quote_ident(foreign_column_name)
|| ') = BTRIM(b.' || quote_ident(main_column_name) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_user = id FROM actor_usr_legacy b'
+ || ' SET x_user = b.id FROM actor_usr_legacy b'
|| ' WHERE a.' || quote_ident(foreign_column_name)
|| ' = b.' || quote_ident(main_column_name);
END IF;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE BTRIM(a.' || quote_ident(column_a)
|| ') = BTRIM(b.' || quote_ident(column_b) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE a.' || quote_ident(column_a)
|| ' = b.' || quote_ident(column_b);
END IF;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE BTRIM(a.' || quote_ident(column_a)
|| ') = BTRIM(b.' || quote_ident(column_b) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE a.' || quote_ident(column_a)
|| ' = b.' || quote_ident(column_b);
END IF;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
--- convenience function for handling desired asset stat cats
-
-CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+-- convenience function for linking two tables, but copying column w into column x instead of "id". Unlike handle_link2, this one won't drop the target column, and it also doesn't have a final boolean argument for btrim
+-- e.g. select migration_tools.handle_link3(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id');
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
DECLARE
table_schema ALIAS FOR $1;
- table_name ALIAS FOR $2;
- field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
- org_shortname ALIAS FOR $4;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
proceed BOOLEAN;
- org INTEGER;
- org_list INTEGER[];
- sc TEXT;
- sce TEXT;
BEGIN
-
- SELECT 'desired_sc' || field_suffix INTO sc;
- SELECT 'desired_sce' || field_suffix INTO sce;
-
EXECUTE 'SELECT EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_schema = $1
AND table_name = $2
and column_name = $3
- )' INTO proceed USING table_schema, table_name, sc;
+ )' INTO proceed USING table_schema, table_a, column_a;
IF NOT proceed THEN
- RAISE EXCEPTION 'Missing column %', sc;
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
END IF;
+
EXECUTE 'SELECT EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_schema = $1
AND table_name = $2
and column_name = $3
- )' INTO proceed USING table_schema, table_name, sce;
+ )' INTO proceed USING table_schema, table_b, column_b;
IF NOT proceed THEN
- RAISE EXCEPTION 'Missing column %', sce;
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
END IF;
- SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
- IF org IS NULL THEN
- RAISE EXCEPTION 'Cannot find org by shortname';
- END IF;
- SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b);
- -- caller responsible for their own truncates though we try to prevent duplicates
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_a, column_a;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
+ END IF;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_b, column_b;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
+ END IF;
+
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b)
+ || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_a, column_a;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
+ END IF;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_b, column_b;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
+ END IF;
+
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b)
+ || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_a, column_a;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
+ END IF;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_b, column_b;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
+ END IF;
+
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b)
+ || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_a, column_a;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
+ END IF;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_b, column_b;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
+ END IF;
+
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b)
+ || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_a ALIAS FOR $2;
+ column_a ALIAS FOR $3;
+ table_b ALIAS FOR $4;
+ column_b ALIAS FOR $5;
+ column_w ALIAS FOR $6;
+ column_x ALIAS FOR $7;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_a, column_a;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a;
+ END IF;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_b, column_b;
+ IF NOT proceed THEN
+ RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b;
+ END IF;
+
+ EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
+ || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a'
+ || ' WHERE a.' || quote_ident(column_a)
+ || ' = b.' || quote_ident(column_b)
+ || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+-- convenience function for handling desired asset stat cats
+
+CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_name ALIAS FOR $2;
+ field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc.
+ org_shortname ALIAS FOR $4;
+ proceed BOOLEAN;
+ org INTEGER;
+ org_list INTEGER[];
+ sc TEXT;
+ sce TEXT;
+ BEGIN
+
+ SELECT 'desired_sc' || field_suffix INTO sc;
+ SELECT 'desired_sce' || field_suffix INTO sce;
+
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_name, sc;
+ IF NOT proceed THEN
+ RAISE EXCEPTION 'Missing column %', sc;
+ END IF;
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = $3
+ )' INTO proceed USING table_schema, table_name, sce;
+ IF NOT proceed THEN
+ RAISE EXCEPTION 'Missing column %', sce;
+ END IF;
+
+ SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname;
+ IF org IS NULL THEN
+ RAISE EXCEPTION 'Cannot find org by shortname';
+ END IF;
+ SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org );
+
+ -- caller responsible for their own truncates though we try to prevent duplicates
EXECUTE 'INSERT INTO asset_stat_cat (owner, name)
SELECT DISTINCT
$1
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
AND NOT EXISTS (
SELECT id
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
ORDER BY 1,3;'
USING org, org_list;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+DROP FUNCTION IF EXISTS migration_tools.btrim_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.btrim_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ AND column_name like 'l_%'
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.btrim_columns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.btrim_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = BTRIM(' || c_name || ')');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ AND column_name like 'l_%'
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.null_empty_columns(TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.null_empty_columns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN
+ LANGUAGE plpgsql
+AS $function$
+DECLARE
+ c_name TEXT;
+BEGIN
+
+ FOR c_name IN SELECT column_name FROM information_schema.columns WHERE
+ table_name = t_name
+ AND table_schema = s_name
+ AND (data_type='text' OR data_type='character varying')
+ LOOP
+ EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' ');
+ END LOOP;
+
+ RETURN TRUE;
+END
+$function$;
+
+
+-- convenience function for handling item barcode collisions in asset_copy_legacy
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal collisions', internal_collision_count;
+ FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy_legacy SET barcode = migration_schema || '_incumbent_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_collision_count := incumbent_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent collisions', incumbent_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+-- convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
+-- this should be ran prior to populating actor_card
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_barcode_collision_count NUMERIC := 0;
+ incumbent_usrname_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_barcode_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_incumbent_usrname_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+-- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal collisions', internal_collision_count;
+ FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_collision_count := incumbent_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent collisions', incumbent_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+-- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
+-- this should be ran prior to populating actor_card
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_barcode_collision_count NUMERIC := 0;
+ incumbent_usrname_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
+-- WARNING: Use at your own risk
+-- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
+DECLARE
+ item_object asset.copy%ROWTYPE;
+ user_object actor.usr%ROWTYPE;
+ test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
+ result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
+ safe_to_delete BOOLEAN := FALSE;
+ m action.found_circ_matrix_matchpoint;
+ n action.found_circ_matrix_matchpoint;
+ -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
+ result_matchpoint INTEGER;
+BEGIN
+ SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
+ RAISE INFO 'testing rule: %', test_rule_object;
+
+ INSERT INTO actor.usr (
+ profile,
+ usrname,
+ passwd,
+ ident_type,
+ first_given_name,
+ family_name,
+ home_ou,
+ juvenile
+ ) SELECT
+ COALESCE(test_rule_object.grp, 2),
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ MD5(NOW()::TEXT),
+ 1,
+ 'Ima',
+ 'Test',
+ COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
+ COALESCE(test_rule_object.juvenile_flag, FALSE)
+ ;
+
+ SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
+
+ INSERT INTO asset.call_number (
+ creator,
+ editor,
+ record,
+ owning_lib,
+ label,
+ label_class
+ ) SELECT
+ 1,
+ 1,
+ -1,
+ COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ 1
+ ;
+
+ INSERT INTO asset.copy (
+ barcode,
+ circ_lib,
+ creator,
+ call_number,
+ editor,
+ location,
+ loan_duration,
+ fine_level,
+ ref,
+ circ_modifier
+ ) SELECT
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
+ 1,
+ currval('asset.call_number_id_seq'),
+ 1,
+ COALESCE(test_rule_object.copy_location,1),
+ 2,
+ 2,
+ COALESCE(test_rule_object.ref_flag,FALSE),
+ test_rule_object.circ_modifier
+ ;
+
+ SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
+
+ SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
+ test_rule_object.org_unit,
+ item_object,
+ user_object,
+ COALESCE(test_rule_object.is_renewal,FALSE)
+ );
+ RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
+ test_rule_object.org_unit,
+ item_object.id,
+ user_object.id,
+ COALESCE(test_rule_object.is_renewal,FALSE),
+ m.success,
+ m.matchpoint,
+ m.buildrows
+ ;
+
+ -- disable the rule being tested to see if the outcome changes
+ UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
+
+ SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
+ test_rule_object.org_unit,
+ item_object,
+ user_object,
+ COALESCE(test_rule_object.is_renewal,FALSE)
+ );
+ RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
+ test_rule_object.org_unit,
+ item_object.id,
+ user_object.id,
+ COALESCE(test_rule_object.is_renewal,FALSE),
+ n.success,
+ n.matchpoint,
+ n.buildrows
+ ;
+
+ -- FIXME: We could dig deeper and see if the referenced config.rule_*
+ -- entries are effectively equivalent, but for now, let's assume no
+ -- duplicate rules at that level
+ IF (
+ (m.matchpoint).circulate = (n.matchpoint).circulate
+ AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
+ AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
+ AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
+ AND (
+ (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
+ OR (
+ (m.matchpoint).hard_due_date IS NULL
+ AND (n.matchpoint).hard_due_date IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).renewals = (n.matchpoint).renewals
+ OR (
+ (m.matchpoint).renewals IS NULL
+ AND (n.matchpoint).renewals IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).grace_period = (n.matchpoint).grace_period
+ OR (
+ (m.matchpoint).grace_period IS NULL
+ AND (n.matchpoint).grace_period IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
+ OR (
+ (m.matchpoint).total_copy_hold_ratio IS NULL
+ AND (n.matchpoint).total_copy_hold_ratio IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
+ OR (
+ (m.matchpoint).available_copy_hold_ratio IS NULL
+ AND (n.matchpoint).available_copy_hold_ratio IS NULL
+ )
+ )
+ AND NOT EXISTS (
+ SELECT limit_set, fallthrough
+ FROM config.circ_matrix_limit_set_map
+ WHERE active and matchpoint = (m.matchpoint).id
+ EXCEPT
+ SELECT limit_set, fallthrough
+ FROM config.circ_matrix_limit_set_map
+ WHERE active and matchpoint = (n.matchpoint).id
+ )
+
+ ) THEN
+ RAISE INFO 'rule has same outcome';
+ safe_to_delete := TRUE;
+ ELSE
+ RAISE INFO 'rule has different outcome';
+ safe_to_delete := FALSE;
+ END IF;
+
+ RAISE EXCEPTION 'rollback the temporary changes';
+
+EXCEPTION WHEN OTHERS THEN
+
+ RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
+ RETURN safe_to_delete;
+
+END;
+$func$ LANGUAGE plpgsql;
+
projects / migration-tools.git / blobdiff