X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;fp=sql%2Fbase%2Fbase.sql;h=ae722f0dee301560b9a5442cfa09700e005a1572;hp=56cab3db486de04728051ce9aaef42e5f8090304;hb=9328c60b81355e653950445fbe7288e42a5d45d0;hpb=8c0be645ca11beb5124c93618480f281dfaee6be diff --git a/sql/base/base.sql b/sql/base/base.sql index 56cab3d..ae722f0 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -356,3254 +356,6 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_into_production (TEXT,TEXT) RE 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; - temp TEXT; - family_name TEXT := ''; - first_given_name TEXT := ''; - second_given_name TEXT := ''; - suffix TEXT := ''; - prefix TEXT := ''; - BEGIN - temp := full_name; - -- Use values, not structure, for prefix/suffix, unless we come up with a better idea - IF temp ilike '%MR.%' THEN - prefix := 'Mr.'; - temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%MRS.%' THEN - prefix := 'Mrs.'; - temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%MS.%' THEN - prefix := 'Ms.'; - temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%DR.%' THEN - prefix := 'Dr.'; - temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%JR%' THEN - suffix := 'Jr.'; - temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' ); - END IF; - IF temp ilike '%JR,%' THEN - suffix := 'Jr.'; - temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ); - END IF; - IF temp ilike '%SR%' THEN - suffix := 'Sr.'; - temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' ); - END IF; - IF temp ilike '%SR,%' THEN - suffix := 'Sr.'; - temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ); - END IF; - IF temp ~ E'\\sII$' THEN - suffix := 'II'; - temp := REGEXP_REPLACE( temp, E'II$', '', 'i' ); - END IF; - IF temp ~ E'\\sIII$' THEN - suffix := 'III'; - temp := REGEXP_REPLACE( temp, E'III$', '', 'i' ); - END IF; - IF temp ~ E'\\sIV$' THEN - suffix := 'IV'; - temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' ); - END IF; - - family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') ); - first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END ); - second_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*[^,\\s]*\\s*(.*)$',E'\\1') ELSE '' END ); - - RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ]; - END; -$$ LANGUAGE PLPGSQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$ - DECLARE - full_name TEXT := $1; - temp TEXT; - family_name TEXT := ''; - first_given_name TEXT := ''; - second_given_name TEXT := ''; - suffix TEXT := ''; - prefix TEXT := ''; - BEGIN - temp := full_name; - -- Use values, not structure, for prefix/suffix, unless we come up with a better idea - IF temp ilike '%MR.%' THEN - prefix := 'Mr.'; - temp := REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%MRS.%' THEN - prefix := 'Mrs.'; - temp := REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%MS.%' THEN - prefix := 'Ms.'; - temp := REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%DR.%' THEN - prefix := 'Dr.'; - temp := REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%JR.%' THEN - suffix := 'Jr.'; - temp := REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%JR,%' THEN - suffix := 'Jr.'; - temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' ); - END IF; - IF temp ilike '%SR.%' THEN - suffix := 'Sr.'; - temp := REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' ); - END IF; - IF temp ilike '%SR,%' THEN - suffix := 'Sr.'; - temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' ); - END IF; - IF temp like '%III%' THEN - suffix := 'III'; - temp := REGEXP_REPLACE( temp, E'III', '' ); - END IF; - IF temp like '%II%' THEN - suffix := 'II'; - temp := REGEXP_REPLACE( temp, E'II', '' ); - END IF; - IF temp like '%IV%' THEN - suffix := 'IV'; - temp := REGEXP_REPLACE( temp, E'IV', '' ); - END IF; - - temp := REGEXP_REPLACE( temp, '\(\)', ''); - family_name := BTRIM( REGEXP_REPLACE(temp,E'^(\\S+).*$',E'\\1') ); - family_name := REGEXP_REPLACE( family_name, ',', '' ); - first_given_name := CASE WHEN temp ~ E'^\\S+$' THEN 'N/A' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+(\\S+).*$',E'\\1') ) END; - first_given_name := REGEXP_REPLACE( first_given_name, ',', '' ); - second_given_name := CASE WHEN temp ~ E'^\\S+$' THEN '' ELSE BTRIM( REGEXP_REPLACE(temp,E'^\\S+\\s+\\S+\\s*(.*)$',E'\\1') ) END; - second_given_name := REGEXP_REPLACE( second_given_name, ',', '' ); - - RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ]; - END; -$$ LANGUAGE PLPGSQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_fuller_last_first_middle_and_random_affix (TEXT) RETURNS TEXT[] AS $$ - DECLARE - full_name TEXT := $1; - temp TEXT; - family_name TEXT := ''; - first_given_name TEXT := ''; - second_given_name TEXT := ''; - suffix TEXT := ''; - prefix TEXT := ''; - BEGIN - temp := BTRIM(full_name); - -- Use values, not structure, for prefix/suffix, unless we come up with a better idea - --IF temp ~ '^\S{2,}\.' THEN - -- prefix := REGEXP_REPLACE(temp, '^(\S{2,}\.).*$','\1'); - -- temp := BTRIM(REGEXP_REPLACE(temp, '^\S{2,}\.(.*)$','\1')); - --END IF; - --IF temp ~ '\S{2,}\.$' THEN - -- suffix := REGEXP_REPLACE(temp, '^.*(\S{2,}\.)$','\1'); - -- temp := REGEXP_REPLACE(temp, '^(.*)\S{2,}\.$','\1'); - --END IF; - IF temp ilike '%MR.%' THEN - prefix := 'Mr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'MR\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%MRS.%' THEN - prefix := 'Mrs.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'MRS\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%MS.%' THEN - prefix := 'Ms.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'MS\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%DR.%' THEN - prefix := 'Dr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'DR\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%JR.%' THEN - suffix := 'Jr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'JR\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%JR,%' THEN - suffix := 'Jr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' )); - END IF; - IF temp ilike '%SR.%' THEN - suffix := 'Sr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'SR\.\\s*', '', 'i' )); - END IF; - IF temp ilike '%SR,%' THEN - suffix := 'Sr.'; - temp := BTRIM(REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' )); - END IF; - IF temp like '%III%' THEN - suffix := 'III'; - temp := BTRIM(REGEXP_REPLACE( temp, E'III', '' )); - END IF; - IF temp like '%II%' THEN - suffix := 'II'; - temp := BTRIM(REGEXP_REPLACE( temp, E'II', '' )); - END IF; - - IF temp ~ ',' THEN - family_name = BTRIM(REGEXP_REPLACE(temp,'^(.*?,).*$','\1')); - temp := BTRIM(REPLACE( temp, family_name, '' )); - family_name := REPLACE( family_name, ',', '' ); - IF temp ~ ' ' THEN - first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\1') ); - second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(.+)\s(.+)$','\2') ); - ELSE - first_given_name := temp; - second_given_name := ''; - END IF; - ELSE - IF temp ~ '^\S+\s+\S+\s+\S+$' THEN - first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\1') ); - second_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\2') ); - family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)\s*(\S+)$','\3') ); - ELSE - first_given_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\1') ); - second_given_name := temp; - family_name := BTRIM( REGEXP_REPLACE(temp,'^(\S+)\s*(\S+)$','\2') ); - END IF; - END IF; - - RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ]; - END; -$$ LANGUAGE PLPGSQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION migration_tools.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; - city TEXT := ''; - state TEXT := ''; - zip TEXT := ''; - BEGIN - zip := CASE WHEN city_state_zip ~ E'\\d\\d\\d\\d\\d' THEN REGEXP_REPLACE( city_state_zip, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) ELSE '' END; - city_state_zip := REGEXP_REPLACE( city_state_zip, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2'); - IF city_state_zip ~ ',' THEN - state := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\2'); - city := REGEXP_REPLACE( city_state_zip, E'^(.*),(.*)$', E'\\1'); - ELSE - IF city_state_zip ~ E'\\s+[A-Z][A-Z]\\s*$' THEN - state := REGEXP_REPLACE( city_state_zip, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' ); - city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' ); - ELSE - IF city_state_zip ~ E'^\\S+$' THEN - city := city_state_zip; - state := 'N/A'; - ELSE - state := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2'); - city := REGEXP_REPLACE( city_state_zip, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1'); - END IF; - END IF; - END IF; - RETURN ARRAY[ TRIM(BOTH ' ' FROM city), TRIM(BOTH ' ' FROM state), TRIM(BOTH ' ' FROM zip) ]; - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - --- try to parse data like this: 100 MAIN ST$COVINGTON, GA 30016 -CREATE OR REPLACE FUNCTION migration_tools.parse_out_address (TEXT) RETURNS TEXT[] AS $$ - DECLARE - fullstring TEXT := $1; - address1 TEXT := ''; - address2 TEXT := ''; - scratch1 TEXT := ''; - scratch2 TEXT := ''; - city TEXT := ''; - state TEXT := ''; - zip TEXT := ''; - BEGIN - zip := CASE - WHEN fullstring ~ E'\\d\\d\\d\\d\\d' - THEN REGEXP_REPLACE( fullstring, E'^.*(\\d\\d\\d\\d\\d-?\\d*).*$', E'\\1' ) - ELSE '' - END; - fullstring := REGEXP_REPLACE( fullstring, E'^(.*)\\d\\d\\d\\d\\d-?\\d*(.*)$', E'\\1\\2'); - - IF fullstring ~ ',' THEN - state := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\2'); - scratch1 := REGEXP_REPLACE( fullstring, E'^(.*),(.*)$', E'\\1'); - ELSE - IF fullstring ~ E'\\s+[A-Z][A-Z]\\s*$' THEN - state := REGEXP_REPLACE( fullstring, E'^.*,?\\s+([A-Z][A-Z])\\s*$', E'\\1' ); - scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s+[A-Z][A-Z](\\s*)$', E'\\1\\2' ); - ELSE - IF fullstring ~ E'^\\S+$' THEN - scratch1 := fullstring; - state := 'N/A'; - ELSE - state := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\2'); - scratch1 := REGEXP_REPLACE( fullstring, E'^(.*?),?\\s*(\\S+)\\s*$', E'\\1'); - END IF; - END IF; - END IF; - - IF scratch1 ~ '[\$]' THEN - scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\1'); - city := REGEXP_REPLACE( scratch1, E'^(.+)[\$](.+?)$', E'\\2'); - ELSE - IF scratch1 ~ '\s' THEN - scratch2 := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\1'); - city := REGEXP_REPLACE( scratch1, E'^(.+)\\s+(.+?)$', E'\\2'); - ELSE - scratch2 := 'N/A'; - city := scratch1; - END IF; - END IF; - - IF scratch2 ~ '^\d' THEN - address1 := scratch2; - address2 := ''; - ELSE - address1 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\1'); - address2 := REGEXP_REPLACE( scratch2, E'^(.+?)(\\d.+)$', E'\\2'); - END IF; - - RETURN ARRAY[ - TRIM(BOTH ' ' FROM address1) - ,TRIM(BOTH ' ' FROM address2) - ,TRIM(BOTH ' ' FROM city) - ,TRIM(BOTH ' ' FROM state) - ,TRIM(BOTH ' ' FROM zip) - ]; - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - -CREATE OR REPLACE FUNCTION migration_tools.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; - BEGIN - IF o ~ E'^\\d+$' AND o !~ E'^0' AND length(o) < 19 THEN -- for reference, the max value for a bigint is 9223372036854775807. May also want to consider the case where folks want to add prefixes to non-numeric barcodes - IF o::BIGINT < t THEN - n = o::BIGINT + t; - END IF; - END IF; - - RETURN n; - END; -$$ LANGUAGE PLPGSQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION migration_tools.base_profile_map (TEXT) RETURNS TEXT AS $$ - DECLARE - migration_schema ALIAS FOR $1; - output TEXT; - BEGIN - FOR output IN - EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_profile_map'';' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.base_item_dynamic_field_map (TEXT) RETURNS TEXT AS $$ - DECLARE - migration_schema ALIAS FOR $1; - output TEXT; - BEGIN - FOR output IN - EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_item_dynamic_field_map'';' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.base_copy_location_map (TEXT) RETURNS TEXT AS $$ - DECLARE - migration_schema ALIAS FOR $1; - output TEXT; - BEGIN - FOR output IN - EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_copy_location_map'';' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.base_circ_field_map (TEXT) RETURNS TEXT AS $$ - DECLARE - migration_schema ALIAS FOR $1; - output TEXT; - BEGIN - FOR output IN - EXECUTE 'SELECT ''' || migration_schema || '.'' || value FROM ' || migration_schema || '.config WHERE key = ''base_circ_field_map'';' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.map_base_patron_profile (TEXT,TEXT,INTEGER) RETURNS VOID AS $$ - DECLARE - migration_schema ALIAS FOR $1; - profile_map TEXT; - patron_table ALIAS FOR $2; - default_patron_profile ALIAS FOR $3; - sql TEXT; - sql_update TEXT; - sql_where1 TEXT := ''; - sql_where2 TEXT := ''; - sql_where3 TEXT := ''; - output RECORD; - BEGIN - SELECT migration_tools.base_profile_map(migration_schema) INTO STRICT profile_map; - FOR output IN - EXECUTE 'SELECT * FROM ' || profile_map || E' ORDER BY id;' - LOOP - sql_update := 'UPDATE ' || patron_table || ' AS u SET profile = perm_grp_id FROM ' || profile_map || ' AS m WHERE '; - sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1); - sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2); - sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3); - sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';'; - --RAISE INFO 'sql = %', sql; - PERFORM migration_tools.exec( $1, sql ); - END LOOP; - PERFORM migration_tools.exec( $1, 'UPDATE ' || patron_table || ' AS u SET profile = ' || quote_literal(default_patron_profile) || ' WHERE profile IS NULL;' ); - BEGIN - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_patron_mapping_profile'', now() );' ); - EXCEPTION - WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_patron_mapping_profile'';' ); - END; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_dynamic (TEXT,TEXT) RETURNS VOID AS $$ - DECLARE - migration_schema ALIAS FOR $1; - field_map TEXT; - item_table ALIAS FOR $2; - sql TEXT; - sql_update TEXT; - sql_where1 TEXT := ''; - sql_where2 TEXT := ''; - sql_where3 TEXT := ''; - output RECORD; - BEGIN - SELECT migration_tools.base_item_dynamic_field_map(migration_schema) INTO STRICT field_map; - FOR output IN - EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;' - LOOP - sql_update := 'UPDATE ' || item_table || ' AS i SET ' || output.evergreen_field || E' = ' || quote_literal(output.evergreen_value) || '::' || output.evergreen_datatype || E' FROM ' || field_map || ' AS m WHERE '; - sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1); - sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2); - sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3); - sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';'; - --RAISE INFO 'sql = %', sql; - PERFORM migration_tools.exec( $1, sql ); - END LOOP; - BEGIN - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_dynamic'', now() );' ); - EXCEPTION - WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_dynamic'';' ); - END; - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - -CREATE OR REPLACE FUNCTION migration_tools.map_base_item_table_locations (TEXT,TEXT) RETURNS VOID AS $$ - DECLARE - migration_schema ALIAS FOR $1; - base_copy_location_map TEXT; - item_table ALIAS FOR $2; - sql TEXT; - sql_update TEXT; - sql_where1 TEXT := ''; - sql_where2 TEXT := ''; - sql_where3 TEXT := ''; - output RECORD; - BEGIN - SELECT migration_tools.base_copy_location_map(migration_schema) INTO STRICT base_copy_location_map; - FOR output IN - EXECUTE 'SELECT * FROM ' || base_copy_location_map || E' ORDER BY id;' - LOOP - sql_update := 'UPDATE ' || item_table || ' AS i SET location = m.location FROM ' || base_copy_location_map || ' AS m WHERE '; - sql_where1 := NULLIF(output.legacy_field1,'') || ' = ' || quote_literal( output.legacy_value1 ) || ' AND legacy_field1 = ' || quote_literal(output.legacy_field1) || ' AND legacy_value1 = ' || quote_literal(output.legacy_value1); - sql_where2 := NULLIF(output.legacy_field2,'') || ' = ' || quote_literal( output.legacy_value2 ) || ' AND legacy_field2 = ' || quote_literal(output.legacy_field2) || ' AND legacy_value2 = ' || quote_literal(output.legacy_value2); - sql_where3 := NULLIF(output.legacy_field3,'') || ' = ' || quote_literal( output.legacy_value3 ) || ' AND legacy_field3 = ' || quote_literal(output.legacy_field3) || ' AND legacy_value3 = ' || quote_literal(output.legacy_value3); - sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || ';'; - --RAISE INFO 'sql = %', sql; - PERFORM migration_tools.exec( $1, sql ); - END LOOP; - BEGIN - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_item_mapping_locations'', now() );' ); - EXCEPTION - WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_item_mapping_locations'';' ); - END; - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - --- circulate loan period max renewals max out fine amount fine interval max fine item field 1 item value 1 item field 2 item value 2 patron field 1 patron value 1 patron field 2 patron value 2 -CREATE OR REPLACE FUNCTION migration_tools.map_base_circ_table_dynamic (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ - DECLARE - migration_schema ALIAS FOR $1; - field_map TEXT; - circ_table ALIAS FOR $2; - item_table ALIAS FOR $3; - patron_table ALIAS FOR $4; - sql TEXT; - sql_update TEXT; - sql_where1 TEXT := ''; - sql_where2 TEXT := ''; - sql_where3 TEXT := ''; - sql_where4 TEXT := ''; - output RECORD; - BEGIN - SELECT migration_tools.base_circ_field_map(migration_schema) INTO STRICT field_map; - FOR output IN - EXECUTE 'SELECT * FROM ' || field_map || E' ORDER BY id;' - LOOP - sql_update := 'UPDATE ' || circ_table || ' AS c SET duration = ' || quote_literal(output.loan_period) || '::INTERVAL, renewal_remaining = ' || quote_literal(output.max_renewals) || '::INTEGER, recuring_fine = ' || quote_literal(output.fine_amount) || '::NUMERIC(6,2), fine_interval = ' || quote_literal(output.fine_interval) || '::INTERVAL, max_fine = ' || quote_literal(output.max_fine) || '::NUMERIC(6,2) FROM ' || field_map || ' AS m, ' || item_table || ' AS i, ' || patron_table || ' AS u WHERE c.usr = u.id AND c.target_copy = i.id AND '; - sql_where1 := NULLIF(output.item_field1,'') || ' = ' || quote_literal( output.item_value1 ) || ' AND item_field1 = ' || quote_literal(output.item_field1) || ' AND item_value1 = ' || quote_literal(output.item_value1); - sql_where2 := NULLIF(output.item_field2,'') || ' = ' || quote_literal( output.item_value2 ) || ' AND item_field2 = ' || quote_literal(output.item_field2) || ' AND item_value2 = ' || quote_literal(output.item_value2); - sql_where3 := NULLIF(output.patron_field1,'') || ' = ' || quote_literal( output.patron_value1 ) || ' AND patron_field1 = ' || quote_literal(output.patron_field1) || ' AND patron_value1 = ' || quote_literal(output.patron_value1); - sql_where4 := NULLIF(output.patron_field2,'') || ' = ' || quote_literal( output.patron_value2 ) || ' AND patron_field2 = ' || quote_literal(output.patron_field2) || ' AND patron_value2 = ' || quote_literal(output.patron_value2); - sql := sql_update || COALESCE(sql_where1,'') || CASE WHEN sql_where1 <> '' AND sql_where2<> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where2,'') || CASE WHEN sql_where2 <> '' AND sql_where3 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where3,'') || CASE WHEN sql_where3 <> '' AND sql_where4 <> '' THEN ' AND ' ELSE '' END || COALESCE(sql_where4,'') || ';'; - --RAISE INFO 'sql = %', sql; - PERFORM migration_tools.exec( $1, sql ); - END LOOP; - BEGIN - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''last_base_circ_field_mapping'', now() );' ); - EXCEPTION - WHEN OTHERS THEN PERFORM migration_tools.exec( $1, 'UPDATE ' || migration_schema || '.config SET value = now() WHERE key = ''last_base_circ_field_mapping'';' ); - END; - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - --- expand_barcode --- $barcode source barcode --- $prefix prefix to add to barcode, NULL = add no prefix --- $maxlen maximum length of barcode; default to 14 if left NULL --- $pad padding string to apply to left of source barcode before adding --- prefix and suffix; set to NULL or '' if no padding is desired --- $suffix suffix to add to barcode, NULL = add no suffix --- --- Returns a new string consisting of prefix concatenated with padded barcode and suffix. --- If new barcode would be longer than $maxlen, the original barcode is returned instead. --- -CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, TEXT, TEXT) RETURNS TEXT AS $$ - my ($barcode, $prefix, $maxlen, $pad, $suffix) = @_; - - # default case - return unless defined $barcode; - - $prefix = '' unless defined $prefix; - $maxlen ||= 14; - $pad = '0' unless defined $pad; - $suffix = '' unless defined $suffix; - - # bail out if adding prefix and suffix would bring new barcode over max length - return $barcode if (length($prefix) + length($barcode) + length($suffix)) > $maxlen; - - my $new_barcode = $barcode; - if ($pad ne '') { - my $pad_length = $maxlen - length($prefix) - length($suffix); - if (length($barcode) < $pad_length) { - # assuming we always want padding on the left - # also assuming that it is possible to have the pad string be longer than 1 character - $new_barcode = substr($pad x ($pad_length - length($barcode)), 0, $pad_length - length($barcode)) . $new_barcode; - } - } - - # bail out if adding prefix and suffix would bring new barcode over max length - return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen; - - return "$prefix$new_barcode$suffix"; -$$ LANGUAGE PLPERLU STABLE; - --- remove previous version of this function -DROP FUNCTION IF EXISTS migration_tools.attempt_cast(TEXT, TEXT, TEXT); - -CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT, TEXT) RETURNS TEXT AS $$ - DECLARE - attempt_value ALIAS FOR $1; - datatype ALIAS FOR $2; - BEGIN - EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::' || datatype || ' AS a;'; - RETURN attempt_value; - EXCEPTION - WHEN OTHERS THEN RETURN NULL; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output DATE; - BEGIN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(REGEXP_REPLACE(attempt_value,'^(\d\d)(\d\d)(\d\d)$','\1-\2-\3')) || '::date AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output TIMESTAMPTZ; - BEGIN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output DATE; - BEGIN - FOR output IN - EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output TIMESTAMP; - BEGIN - output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP; - RETURN output; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.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/(.........)./${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; - fail_value ALIAS FOR $2; - output NUMERIC(8,2); - BEGIN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output NUMERIC(6,2); - BEGIN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output NUMERIC(8,2); - BEGIN - IF length(regexp_replace(attempt_value,'^0+','')) > 10 THEN - RAISE EXCEPTION 'too many digits'; - END IF; - FOR output IN - EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),10,'0'),2))::numeric(8,2)) || '::NUMERIC(8,2) AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.attempt_money_from_pennies6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$ - DECLARE - attempt_value ALIAS FOR $1; - fail_value ALIAS FOR $2; - output NUMERIC(6,2); - BEGIN - IF length(regexp_replace(attempt_value,'^0+','')) > 8 THEN - RAISE EXCEPTION 'too many digits'; - END IF; - FOR output IN - EXECUTE 'SELECT ' || quote_literal((left(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),-2) || '.' || right(lpad(regexp_replace(attempt_value,'^0+',''),8,'0'),2))::numeric(6,2)) || '::NUMERIC(6,2) AS a;' - LOOP - RETURN output; - END LOOP; - EXCEPTION - WHEN OTHERS THEN - FOR output IN - EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;' - LOOP - RETURN output; - END LOOP; - END; -$$ LANGUAGE PLPGSQL STRICT STABLE; - --- add_codabar_checkdigit --- $barcode source barcode --- --- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14 --- character with a checkdigit computed according to the usual algorithm for library barcodes --- using the Codabar symbology - see . If the --- input string does not meet those requirements, it is returned unchanged. --- -CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$ - my $barcode = shift; - - return $barcode if $barcode !~ /^\d{13,14}$/; - $barcode = substr($barcode, 0, 13); # ignore 14th digit - my @digits = split //, $barcode; - my $total = 0; - $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11); - $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12); - my $remainder = $total % 10; - my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder; - return $barcode . $checkdigit; -$$ LANGUAGE PLPERLU STRICT STABLE; - --- add_code39mod43_checkdigit --- $barcode source barcode --- --- If the source string is 13 or 14 characters long and contains only valid --- Code 39 mod 43 characters, adds or replaces the 14th --- character with a checkdigit computed according to the usual algorithm for library barcodes --- using the Code 39 mod 43 symbology - see . If the --- input string does not meet those requirements, it is returned unchanged. --- -CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$ - my $barcode = shift; - - return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/; - $barcode = substr($barcode, 0, 13); # ignore 14th character - - my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%'; - my %nums = map { $valid_chars[$_] => $_ } (0..42); - - my $total = 0; - $total += $nums{$_} foreach split(//, $barcode); - my $remainder = $total % 43; - my $checkdigit = $valid_chars[$remainder]; - return $barcode . $checkdigit; -$$ LANGUAGE PLPERLU STRICT STABLE; - --- 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; - areacode TEXT := $2; - temp TEXT := ''; - output TEXT := ''; - n_digits INTEGER := 0; - BEGIN - temp := phone; - temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', ''); - temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3'); - n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g')); - IF n_digits = 7 AND areacode <> '' THEN - temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2'); - output := (areacode || '-' || temp); - ELSE - output := temp; - END IF; - RETURN output; - END; - -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - -CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$ - my ($marcxml, $pos, $value) = @_; - - use MARC::Record; - use MARC::File::XML; - - my $xml = $marcxml; - eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - my $leader = $marc->leader(); - substr($leader, $pos, 1) = $value; - $marc->leader($leader); - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); - my $f008 = $marc->field('008'); - - if ($f008) { - my $field = $f008->data(); - substr($field, $pos, 1) = $value; - $f008->update($field); - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); - my $to_insert = MARC::Record->new_from_xml("$tags", 'UTF-8'); - - my @incumbents = (); - - foreach my $field ( $marc->fields() ) { - push @incumbents, $field->as_formatted(); - } - - foreach $field ( $to_insert->fields() ) { - if (!grep {$_ eq $field->as_formatted()} @incumbents) { - $marc->insert_fields_ordered( ($field) ); - } - } - - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+ 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: --- --- First make sure the circ matrix is loaded and the circulations --- have been staged to the extent possible (but at the very least --- circ_lib, target_copy, usr, and *_renewal). User profiles and --- circ modifiers must also be in place. --- --- SELECT migration_tools.apply_circ_matrix_to_specific_circ('m_nwrl.action_circulation', 18391960); --- - -DECLARE - circ_lib INT; - target_copy INT; - usr INT; - is_renewal BOOLEAN; - this_duration_rule INT; - this_fine_rule INT; - this_max_fine_rule INT; - rcd config.rule_circ_duration%ROWTYPE; - rrf config.rule_recurring_fine%ROWTYPE; - rmf config.rule_max_fine%ROWTYPE; - n INT := 0; - n_circs INT := 1; - -BEGIN - - --EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs; - - --FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP - - -- Fetch the correct rules for this circulation - EXECUTE (' - SELECT - circ_lib, - target_copy, - usr, - CASE - WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE - ELSE FALSE - END - FROM ' || tablename || ' WHERE id = ' || circ || ';') - INTO circ_lib, target_copy, usr, is_renewal ; - SELECT - INTO this_duration_rule, - this_fine_rule, - this_max_fine_rule - (matchpoint).duration_rule, - (matchpoint).recurring_fine_rule, - (matchpoint).max_fine_rule - FROM action.find_circ_matrix_matchpoint( - circ_lib, - target_copy, - usr, - is_renewal - ); - SELECT INTO rcd * FROM config.rule_circ_duration - WHERE id = this_duration_rule; - SELECT INTO rrf * FROM config.rule_recurring_fine - WHERE id = this_fine_rule; - SELECT INTO rmf * FROM config.rule_max_fine - WHERE id = this_max_fine_rule; - - -- Apply the rules to this circulation - EXECUTE ('UPDATE ' || tablename || ' c - SET - duration_rule = rcd.name, - recurring_fine_rule = rrf.name, - max_fine_rule = rmf.name, - duration = rcd.normal, - recurring_fine = rrf.normal, - max_fine = - CASE rmf.is_percent - WHEN TRUE THEN (rmf.amount / 100.0) * migration_tools.get_copy_price(ac.id) - ELSE rmf.amount - END, - renewal_remaining = rcd.max_renewals, - grace_period = rrf.grace_period - FROM - config.rule_circ_duration rcd, - config.rule_recurring_fine rrf, - config.rule_max_fine rmf, - asset.copy ac - WHERE - rcd.id = ' || this_duration_rule || ' AND - rrf.id = ' || this_fine_rule || ' AND - rmf.id = ' || this_max_fine_rule || ' AND - ac.id = c.target_copy AND - c.id = ' || circ || ';'); - - -- Keep track of where we are in the process - n := n + 1; - IF (n % 100 = 0) THEN - RAISE INFO '%', n || ' of ' || n_circs - || ' (' || (100*n/n_circs) || '%) circs updated.'; - END IF; - - --END LOOP; - - RETURN; -END; - -$$ LANGUAGE plpgsql; - - - - -CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$ - --- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat. --- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo'); - --- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats. --- TODO: Add a similar tool for actor stat cats, which behave differently. - -DECLARE - c TEXT := schemaname || '.asset_copy_legacy'; - sc TEXT := schemaname || '.asset_stat_cat'; - sce TEXT := schemaname || '.asset_stat_cat_entry'; - scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map'; - stat_cat INT; - stat_cat_entry INT; - -BEGIN - - FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP - - EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry; - - EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry) - SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN - (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');'); - - END LOOP; - - RETURN; -END; - -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$ - --- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this. --- This will assign standing penalties as needed. - -DECLARE - org_unit INT; - usr INT; - -BEGIN - - FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP - - FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP - - EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');'); - - END LOOP; - - END LOOP; - - RETURN; - -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$ - -BEGIN - INSERT INTO metabib.metarecord (fingerprint, master_record) - SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id - FROM biblio.record_entry b - WHERE NOT b.deleted - AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL) - AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint ) - ORDER BY b.fingerprint, b.quality DESC; - INSERT INTO metabib.metarecord_source_map (metarecord, source) - SELECT m.id, r.id - FROM biblio.record_entry r - JOIN metabib.metarecord m USING (fingerprint) - WHERE NOT r.deleted; -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$ - -BEGIN - INSERT INTO metabib.metarecord (fingerprint, master_record) - SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id - FROM biblio.record_entry b - WHERE NOT b.deleted - AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL) - AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint ) - ORDER BY b.fingerprint, b.quality DESC; - INSERT INTO metabib.metarecord_source_map (metarecord, source) - SELECT m.id, r.id - FROM biblio.record_entry r - JOIN metabib.metarecord m USING (fingerprint) - WHERE NOT r.deleted - AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL); -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$ - --- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned. --- Then SELECT migration_tools.create_cards('m_foo'); - -DECLARE - u TEXT := schemaname || '.actor_usr_legacy'; - c TEXT := schemaname || '.actor_card'; - -BEGIN - - EXECUTE ('DELETE FROM ' || c || ';'); - EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';'); - EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;'); - - RETURN; - -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.insert_856_9_conditional (TEXT, TEXT) RETURNS TEXT AS $$ - - ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...]; - - my ($marcxml, $shortname) = @_; - - use MARC::Record; - use MARC::File::XML; - - my $xml = $marcxml; - - eval { - my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); - - foreach my $field ( $marc->field('856') ) { - if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 && - ! ( $field->as_string('9') =~ m/$shortname/ ) ) { - $field->add_subfields( '9' => $shortname ); - $field->update( ind2 => '0'); - } - } - - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+new_from_xml($marcxml, 'UTF-8'); - - foreach my $field ( $marc->field('856') ) { - if ( ! $field->as_string('9') ) { - $field->add_subfields( '9' => $shortname ); - } - } - - $xml = $marc->as_xml_record; - $xml =~ s/^<\?.+?\?>$//mo; - $xml =~ s/\n//sgo; - $xml =~ s/>\s+) { - chomp; - my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/); - $zipdata{$zip} = [$city, $state, $county]; - } - - if (defined $zipdata{$input}) { - my ($city, $state, $county) = @{$zipdata{$input}}; - return [$city, $state, $county]; - } elsif (defined $zipdata{substr $input, 0, 5}) { - my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}}; - return [$city, $state, $county]; - } else { - return ['ZIP not found', 'ZIP not found', 'ZIP not found']; - } - -$$ LANGUAGE PLPERLU STABLE; - -CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$ - -DECLARE - ou INT; - org_unit_depth INT; - ou_parent INT; - parent_depth INT; - errors_found BOOLEAN; - ou_shortname TEXT; - parent_shortname TEXT; - ou_type_name TEXT; - parent_type TEXT; - type_id INT; - type_depth INT; - type_parent INT; - type_parent_depth INT; - proper_parent TEXT; - -BEGIN - - errors_found := FALSE; - --- Checking actor.org_unit_type - - FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP - - SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth; - SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent; - - IF type_parent IS NOT NULL THEN - - SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth; - - IF type_depth - type_parent_depth <> 1 THEN - SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name; - SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type; - RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.', - ou_type_name, type_depth, parent_type, type_parent_depth; - errors_found := TRUE; - - END IF; - - END IF; - - END LOOP; - --- Checking actor.org_unit - - FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP - - SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent; - SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth; - SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth; - SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname; - SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname; - SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name; - SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type; - - IF ou_parent IS NOT NULL THEN - - IF (org_unit_depth - parent_depth <> 1) OR ( - (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type) - ) THEN - RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', - ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth; - errors_found := TRUE; - END IF; - - END IF; - - END LOOP; - - IF NOT errors_found THEN - RAISE INFO 'No errors found.'; - END IF; - - RETURN; - -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$ - -BEGIN - - DELETE FROM asset.opac_visible_copies; - - INSERT INTO asset.opac_visible_copies (id, circ_lib, record) - SELECT DISTINCT - cp.id, cp.circ_lib, cn.record - FROM - asset.copy cp - JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN actor.org_unit a ON (cp.circ_lib = a.id) - JOIN asset.copy_location cl ON (cp.location = cl.id) - JOIN config.copy_status cs ON (cp.status = cs.id) - JOIN biblio.record_entry b ON (cn.record = b.id) - WHERE - NOT cp.deleted AND - NOT cn.deleted AND - NOT b.deleted AND - cs.opac_visible AND - cl.opac_visible AND - cp.opac_visible AND - a.opac_visible AND - cp.id NOT IN (SELECT id FROM asset.opac_visible_copies); - -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$ - -DECLARE - old_volume BIGINT; - new_volume BIGINT; - bib BIGINT; - old_owning_lib INTEGER; - old_label TEXT; - remainder BIGINT; - -BEGIN - - -- Gather information - SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id; - SELECT record INTO bib FROM asset.call_number WHERE id = old_volume; - SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume; - - -- Bail out if the new_owning_lib is not the ID of an org_unit - IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN - RAISE WARNING - '% is not a valid actor.org_unit ID; no change made.', - new_owning_lib; - RETURN; - END IF; - - -- Bail out discreetly if the owning_lib is already correct - IF new_owning_lib = old_owning_lib THEN - RETURN; - END IF; - - -- Check whether we already have a destination volume available - SELECT id INTO new_volume FROM asset.call_number - WHERE - record = bib AND - owning_lib = new_owning_lib AND - label = old_label AND - NOT deleted; - - -- Create destination volume if needed - IF NOT FOUND THEN - INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) - VALUES (1, 1, bib, new_owning_lib, old_label); - SELECT id INTO new_volume FROM asset.call_number - WHERE - record = bib AND - owning_lib = new_owning_lib AND - label = old_label AND - NOT deleted; - END IF; - - -- Move copy to destination - UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id; - - -- Delete source volume if it is now empty - SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted; - IF NOT FOUND THEN - DELETE FROM asset.call_number WHERE id = old_volume; - END IF; - -END; - -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$ - --- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER). - -DECLARE - new_owning_lib INTEGER; - -BEGIN - - -- Parse the new_owner as an org unit ID or shortname - IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN - SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner; - PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib); - ELSIF new_owner ~ E'^[0-9]+$' THEN - IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN - RAISE INFO - '%', - E'You don\'t need to put the actor.org_unit ID in quotes; ' - || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.'; - new_owning_lib := new_owner::INTEGER; - PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib); - END IF; - ELSE - RAISE WARNING - '% is not a valid actor.org_unit shortname or ID; no change made.', - new_owning_lib; - RETURN; - END IF; - -END; - -$$ LANGUAGE plpgsql; - -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 ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/actor_hours_of_operation'$$; - EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/actor_org_unit_closed'$$; - EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/actor_org_unit_setting'$$; - EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/asset_copy_location'$$; - EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$; - EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/asset_call_number_prefix'$$; - EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ || - ARRAY_TO_STRING(orgs, ',') || $$)$$ || - $$) TO '$$ || dir || $$/asset_call_number_suffix'$$; - EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$; - EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_rule_age_hold_protect'$$; - EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$; - EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$; - EXECUTE $$COPY permission.grp_tree TO '$$ || dir || $$/permission_grp_tree'$$; -END; -$FUNC$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$ -BEGIN - EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$; - EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$; - EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$; - EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$; - EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$; - EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$; - EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$; - - -- import any new circ rules - PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_circ_duration', 'id', 'name'); - PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_age_hold_protect', 'id', 'name'); - PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_max_fine', 'id', 'name'); - PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'config', 'rule_recurring_fine', 'id', 'name'); - - -- and permission groups - PERFORM migration_tools.simple_import_new_rows_by_value(dir, 'permission', 'grp_tree', 'id', 'name'); - -END; -$FUNC$ LANGUAGE PLPGSQL; - - -CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$ -DECLARE - name TEXT; - loopq TEXT; - existsq TEXT; - ct INTEGER; - cols TEXT[]; - copyst TEXT; -BEGIN - EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename; - EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$; - EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$; - loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol; - existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1'; - SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol; - FOR name IN EXECUTE loopq LOOP - EXECUTE existsq INTO ct USING name; - IF ct = 0 THEN - RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name; - copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || - ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1'; - EXECUTE copyst USING name; - END IF; - END LOOP; -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 $$ - DECLARE - target_event_def ALIAS FOR $1; - orgs ALIAS FOR $2; - BEGIN - DROP TABLE IF EXISTS new_atevdefs; - CREATE TEMP TABLE new_atevdefs (atevdef INTEGER); - FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP - INSERT INTO action_trigger.event_definition ( - active - ,owner - ,name - ,hook - ,validator - ,reactor - ,cleanup_success - ,cleanup_failure - ,delay - ,max_delay - ,usr_field - ,opt_in_setting - ,delay_field - ,group_field - ,template - ,granularity - ,repeat_delay - ) SELECT - 'f' - ,orgs[i] - ,name || ' (clone of '||target_event_def||')' - ,hook - ,validator - ,reactor - ,cleanup_success - ,cleanup_failure - ,delay - ,max_delay - ,usr_field - ,opt_in_setting - ,delay_field - ,group_field - ,template - ,granularity - ,repeat_delay - FROM - action_trigger.event_definition - WHERE - id = target_event_def - ; - RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq'); - INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq'); - INSERT INTO action_trigger.environment ( - event_def - ,path - ,collector - ,label - ) SELECT - currval('action_trigger.event_definition_id_seq') - ,path - ,collector - ,label - FROM - action_trigger.environment - WHERE - event_def = target_event_def - ; - INSERT INTO action_trigger.event_params ( - event_def - ,param - ,value - ) SELECT - currval('action_trigger.event_definition_id_seq') - ,param - ,value - FROM - action_trigger.event_params - WHERE - event_def = target_event_def - ; - END LOOP; - RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs); - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - --- example: SELECT * FROM migration_tools.duplicate_template_but_change_delay(5,'{3,4}','00:30:00'::INTERVAL); -CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay (INTEGER, INTEGER[], INTERVAL) RETURNS VOID AS $$ - DECLARE - target_event_def ALIAS FOR $1; - orgs ALIAS FOR $2; - new_interval ALIAS FOR $3; - BEGIN - DROP TABLE IF EXISTS new_atevdefs; - CREATE TEMP TABLE new_atevdefs (atevdef INTEGER); - FOR i IN array_lower(orgs,1) .. array_upper(orgs,1) LOOP - INSERT INTO action_trigger.event_definition ( - active - ,owner - ,name - ,hook - ,validator - ,reactor - ,cleanup_success - ,cleanup_failure - ,delay - ,max_delay - ,usr_field - ,opt_in_setting - ,delay_field - ,group_field - ,template - ,granularity - ,repeat_delay - ) SELECT - 'f' - ,orgs[i] - ,name || ' (clone of '||target_event_def||')' - ,hook - ,validator - ,reactor - ,cleanup_success - ,cleanup_failure - ,new_interval - ,max_delay - ,usr_field - ,opt_in_setting - ,delay_field - ,group_field - ,template - ,granularity - ,repeat_delay - FROM - action_trigger.event_definition - WHERE - id = target_event_def - ; - RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq'); - INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq'); - INSERT INTO action_trigger.environment ( - event_def - ,path - ,collector - ,label - ) SELECT - currval('action_trigger.event_definition_id_seq') - ,path - ,collector - ,label - FROM - action_trigger.environment - WHERE - event_def = target_event_def - ; - INSERT INTO action_trigger.event_params ( - event_def - ,param - ,value - ) SELECT - currval('action_trigger.event_definition_id_seq') - ,param - ,value - FROM - action_trigger.event_params - WHERE - event_def = target_event_def - ; - END LOOP; - RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs); - END; -$$ LANGUAGE PLPGSQL STRICT VOLATILE; - --- 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.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( - (SELECT pickup_lib FROM action.hold_request WHERE id = $1), - (SELECT request_lib FROM action.hold_request WHERE id = $1), - (SELECT current_copy FROM action.hold_request WHERE id = $1), - (SELECT usr FROM action.hold_request WHERE id = $1), - (SELECT requestor FROM action.hold_request WHERE id = $1) - ); -$$ LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint2 (INTEGER) RETURNS SETOF action.matrix_test_result AS $$ - SELECT action.hold_request_permit_test( - (SELECT pickup_lib FROM action.hold_request WHERE id = $1), - (SELECT request_lib FROM action.hold_request WHERE id = $1), - (SELECT current_copy FROM action.hold_request WHERE id = $1), - (SELECT usr FROM action.hold_request WHERE id = $1), - (SELECT requestor FROM action.hold_request WHERE id = $1) - ); -$$ LANGUAGE SQL; - -CREATE OR REPLACE FUNCTION migration_tools.find_circ_matrix_matchpoint (INTEGER) RETURNS SETOF action.found_circ_matrix_matchpoint AS $$ - SELECT action.find_circ_matrix_matchpoint( - (SELECT circ_lib FROM action.circulation WHERE id = $1), - (SELECT target_copy FROM action.circulation WHERE id = $1), - (SELECT usr FROM action.circulation WHERE id = $1), - (SELECT COALESCE( - NULLIF(phone_renewal,false), - NULLIF(desk_renewal,false), - NULLIF(opac_renewal,false), - false - ) FROM action.circulation WHERE id = $1 - ) - ); -$$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION migration_tools.assert (BOOLEAN) RETURNS VOID AS $$ DECLARE