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;
+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;
];
$$ 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;
$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;
+
CREATE OR REPLACE FUNCTION migration_tools.merge_marc_fields( TEXT, TEXT, TEXT[] ) RETURNS TEXT AS $func$
use strict;
return \@texts;
$$ LANGUAGE PLPERLU STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
+ my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
+
+ 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) {
+ if ($field->as_string() =~ qr/$match/) {
+ push @texts, $field->as_string($subfield,$delimiter);
+ }
+ }
+ return \@texts;
+$$ LANGUAGE PLPERLU STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
SELECT action.find_hold_matrix_matchpoint(
(SELECT pickup_lib FROM action.hold_request WHERE id = $1),
org_range ALIAS FOR $4;
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;
BEGIN
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
+ 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
+ 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
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'
|| ' 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(
|| ' 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;
--- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1
-DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT);
-CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT)
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9(INTEGER,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.merge_group(bib_id INTEGER,new_sf9 TEXT,force TEXT DEFAULT 'false')
+ RETURNS BOOLEAN AS
+$BODY$
+DECLARE
+ marc_xml TEXT;
+ new_marc TEXT;
+BEGIN
+ SELECT marc FROM biblio.record_entry WHERE id = bib_id INTO marc_xml;
+
+ SELECT munge_sf9(marc_xml,new_sf9,force) INTO new_marc;
+ UPDATE biblio.record_entry SET marc = new_marc WHERE id = bib_id;
+
+ RETURN true;
+END;
+$BODY$ LANGUAGE plpgsql;
+
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9(TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.munge_sf9(marc_xml TEXT, new_9_to_set TEXT, force TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $function$
my $marc_xml = shift;
my $new_9_to_set = shift;
+my $force = shift;
$marc_xml =~ s/(<leader>.........)./${1}a/;
foreach my $field (@uris) {
my $ind1 = $field->indicator('1');
if (!defined $ind1) { next; }
- if ($ind1 ne '1' && $ind1 ne '4') { next; }
+ if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
+ if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
my $ind2 = $field->indicator('2');
if (!defined $ind2) { next; }
- if ($ind2 ne '0' && $ind2 ne '1') { next; }
+ if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
+ if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
$field->add_subfields( '9' => $new_9_to_set );
}
$function$;
--- yet another subfield 9 function, this one only adds the $9 and forces
--- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1
-DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT);
-CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT)
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $function$
binmode(STDERR, ':utf8');
my $marc_xml = shift;
-my $new_9_to_set = shift;
+my $substring_old_value = shift;
+my $new_value = shift;
+my $fix_indicators = shift;
$marc_xml =~ s/(<leader>.........)./${1}a/;
foreach my $field (@uris) {
my $ind1 = $field->indicator('1');
- if (!defined $ind1) { next; }
- if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); }
+ if (defined $ind1) {
+ if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
+ $field->set_indicator(1,'4');
+ }
+ }
my $ind2 = $field->indicator('2');
- if (!defined $ind2) { next; }
- if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); }
- $field->add_subfields( '9' => $new_9_to_set );
+ if (defined $ind2) {
+ if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
+ $field->set_indicator(2,'0');
+ }
+ }
+ if ($field->as_string('9') =~ qr/$substring_old_value/) {
+ $field->delete_subfield('9');
+ $field->add_subfields( '9' => $new_value );
+ }
+ $marc_xml->delete_field($field); # -- we're going to dedup and add them back
}
+my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
+$marc_xml->insert_fields_ordered( values( %hash ) );
+
return $marc_xml->as_xml_record();
$function$;
--- 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)
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $function$
binmode(STDERR, ':utf8');
my $marc_xml = shift;
-my $matching_u_text = shift;
-my $new_9_to_set = shift;
+my $qualifying_match = shift;
+my $substring_old_value = shift;
+my $new_value = shift;
+my $fix_indicators = shift;
$marc_xml =~ s/(<leader>.........)./${1}a/;
if ($@) {
#elog("could not parse $bibid: $@\n");
import MARC::File::XML (BinaryEncoding => 'utf8');
- return;
+ return $marc_xml;
}
-my @uris = $marc_xml->field('856');
-return unless @uris;
+my @unqualified_uris = $marc_xml->field('856');
+my @uris = ();
+foreach my $field (@unqualified_uris) {
+ if ($field->as_string() =~ qr/$qualifying_match/) {
+ push @uris, $field;
+ }
+}
+return $marc_xml->as_xml_record() unless @uris;
foreach my $field (@uris) {
- my $sfu = $field->subfield('u');
+ my $ind1 = $field->indicator('1');
+ if (defined $ind1) {
+ if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
+ $field->set_indicator(1,'4');
+ }
+ }
my $ind2 = $field->indicator('2');
- if (!defined $ind2) { next; }
- if ($ind2 ne '0') { next; }
- if (!defined $sfu) { next; }
- if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') {
- $field->add_subfields( '9' => $new_9_to_set );
- last;
+ if (defined $ind2) {
+ if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
+ $field->set_indicator(2,'0');
+ }
}
+ if ($field->as_string('9') =~ qr/$substring_old_value/) {
+ $field->delete_subfield('9');
+ $field->add_subfields( '9' => $new_value );
+ }
+ $marc_xml->delete_field($field); # -- we're going to dedup and add them back
}
+my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
+$marc_xml->insert_fields_ordered( values( %hash ) );
+
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 migration_tools.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;
-
-- strip marc tag
DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT);
CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT)
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;
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;
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.deleted IS FALSE 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;
projects / migration-tools.git / blobdiff