X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=9914aefb87c866b72bf82959456b49059f8e933f;hp=f302b44d41d33e882e8369ad8665f6120550cdfe;hb=97209b5f6e56350a5d4311db2e4fd948fda95e13;hpb=0dda77d4c46b9df85d2abf16c64c78770e17c583 diff --git a/sql/base/base.sql b/sql/base/base.sql index f302b44..9914aef 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -287,6 +287,31 @@ 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; @@ -623,6 +648,564 @@ CREATE OR REPLACE FUNCTION migration_tools.parse_out_address2 (TEXT) RETURNS TEX ]; $$ LANGUAGE PLPERLU STABLE; +DROP TABLE IF EXISTS migration_tools.usps_suffixes; +CREATE UNLOGGED 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; @@ -2324,6 +2907,24 @@ BEGIN 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; @@ -2927,6 +3528,10 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) 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 @@ -2938,9 +3543,17 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) 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'; @@ -2955,12 +3568,33 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) || 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 = 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 = 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 = 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 = 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' @@ -3152,6 +3786,64 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURN END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +-- convenience function for handling desired_not_migrate + +CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_name ALIAS FOR $2; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = ''desired_barred_or_blocked'' + )' INTO proceed USING table_schema, table_name; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column desired_barred_or_blocked'; + END IF; + + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_barred'; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_barred BOOLEAN'; + + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_blocked'; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_blocked BOOLEAN'; + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' + || ' SET x_barred = CASE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE' + || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE' + || ' END'; + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' + || ' SET x_blocked = CASE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE' + || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE' + || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE' + || ' END'; + + EXECUTE 'SELECT migration_tools.assert( + NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL), + ''Not all desired_barred_or_blocked values understood'', + ''All desired_barred_or_blocked values understood'' + );'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + -- convenience function for handling desired_profile CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$ @@ -3179,7 +3871,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VO || ' 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( @@ -3288,6 +3980,7 @@ CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT, WHERE owner = ANY ($2) AND BTRIM('||sc||') = BTRIM(name) ) AND value = BTRIM('||sce||') + AND owner = ANY ($2) ) AND NOT EXISTS ( SELECT id @@ -3298,6 +3991,7 @@ CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT, 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; @@ -3826,12 +4520,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a' || ' WHERE BTRIM(a.' || quote_ident(column_a) || ') = BTRIM(b.' || quote_ident(column_b) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); END IF; @@ -3884,12 +4578,12 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEX IF btrim_desired THEN EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE BTRIM(a.' || quote_ident(column_a) || ') = BTRIM(b.' || quote_ident(column_b) || ')'; ELSE EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); END IF; @@ -3933,12 +4627,223 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_link3 (TEXT,TEXT,TEXT,TEXT,TEX END IF; EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' - || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' || ' WHERE a.' || quote_ident(column_a) || ' = b.' || quote_ident(column_b); END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null_or_empty_string (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND a.' || quote_ident(column_w) || ' IS NOT NULL'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_true (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND a.' || quote_ident(column_w) || ' IS NOT TRUE'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_skip_false (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND a.' || quote_ident(column_w) || ' IS NOT FALSE'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_link3_concat_skip_null (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_a ALIAS FOR $2; + column_a ALIAS FOR $3; + table_b ALIAS FOR $4; + column_b ALIAS FOR $5; + column_w ALIAS FOR $6; + column_x ALIAS FOR $7; + proceed BOOLEAN; + BEGIN + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_a, column_a; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_a, column_a; + END IF; + + EXECUTE 'SELECT EXISTS ( + SELECT 1 + FROM information_schema.columns + WHERE table_schema = $1 + AND table_name = $2 + and column_name = $3 + )' INTO proceed USING table_schema, table_b, column_b; + IF NOT proceed THEN + RAISE EXCEPTION '%.% missing column %', table_schema, table_b, column_b; + END IF; + + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = CONCAT_WS('' ; '',b.' || quote_ident(column_x) || ',a.' || quote_ident(column_w) || ') FROM ' || quote_ident(table_a) || ' a' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b) + || ' AND NULLIF(a.' || quote_ident(column_w) || ','''') IS NOT NULL'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + -- convenience function for handling desired asset stat cats CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ @@ -4036,6 +4941,7 @@ CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT, WHERE owner = ANY ($2) AND BTRIM('||sc||') = BTRIM(name) ) AND value = BTRIM('||sce||') + AND owner = ANY ($2) ) AND NOT EXISTS ( SELECT id @@ -4046,6 +4952,7 @@ CREATE OR REPLACE FUNCTION migration_tools.vivicate_asset_sc_and_sce (TEXT,TEXT, 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;