END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_first_middle_last_comma_suffix (TEXT) RETURNS TEXT[] AS $$
+ DECLARE
+ full_name TEXT := $1;
+ before_comma TEXT;
+ family_name TEXT := '';
+ first_given_name TEXT := '';
+ second_given_name TEXT := '';
+ suffix TEXT := '';
+ prefix TEXT := '';
+ BEGIN
+ before_comma := BTRIM( REGEXP_REPLACE(full_name,E'^(.+),.+$',E'\\1') );
+ suffix := CASE WHEN full_name ~ ',' THEN BTRIM( REGEXP_REPLACE(full_name,E'^.+,(.+)$',E'\\1') ) ELSE '' END;
+
+ IF suffix = before_comma THEN
+ suffix := '';
+ END IF;
+
+ family_name := BTRIM( REGEXP_REPLACE(before_comma,E'^.+\\s(.+)$',E'\\1') );
+ first_given_name := BTRIM( REGEXP_REPLACE(before_comma,E'^(.+?)\\s.+$',E'\\1') );
+ second_given_name := BTRIM( CASE WHEN before_comma ~ '^.+\s.+\s.+$' THEN REGEXP_REPLACE(before_comma,E'^.+\\s(.+)\\s.+$',E'\\1') ELSE '' END );
+
+ RETURN ARRAY[ family_name, prefix, first_given_name, second_given_name, suffix ];
+ END;
+$$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.name_parse_out_last_comma_prefix_first_middle_suffix (TEXT) RETURNS TEXT[] AS $$
DECLARE
full_name TEXT := $1;
END;
$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;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+-- convenience function for handling desired_not_migrate
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_barred_or_blocked (TEXT,TEXT) RETURNS VOID AS $$
+ DECLARE
+ table_schema ALIAS FOR $1;
+ table_name ALIAS FOR $2;
+ proceed BOOLEAN;
+ BEGIN
+ EXECUTE 'SELECT EXISTS (
+ SELECT 1
+ FROM information_schema.columns
+ WHERE table_schema = $1
+ AND table_name = $2
+ and column_name = ''desired_barred_or_blocked''
+ )' INTO proceed USING table_schema, table_name;
+ IF NOT proceed THEN
+ RAISE EXCEPTION 'Missing column desired_barred_or_blocked';
+ END IF;
+
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' DROP COLUMN IF EXISTS x_barred';
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' ADD COLUMN x_barred BOOLEAN';
+
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' DROP COLUMN IF EXISTS x_blocked';
+ EXECUTE 'ALTER TABLE '
+ || quote_ident(table_name)
+ || ' ADD COLUMN x_blocked BOOLEAN';
+
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_barred = CASE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN TRUE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
+ || ' END';
+
+ EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
+ || ' SET x_blocked = CASE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Blocked'' THEN TRUE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Barred'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = ''Neither'' THEN FALSE'
+ || ' WHEN BTRIM(desired_barred_or_blocked) = '''' THEN FALSE'
+ || ' END';
+
+ EXECUTE 'SELECT migration_tools.assert(
+ NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE x_barred IS NULL or x_blocked IS NULL),
+ ''Not all desired_barred_or_blocked values understood'',
+ ''All desired_barred_or_blocked values understood''
+ );';
+
+ END;
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
-- convenience function for handling desired_profile
CREATE OR REPLACE FUNCTION migration_tools.handle_profile (TEXT,TEXT) RETURNS VOID AS $$
|| ' ADD COLUMN x_profile INTEGER';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
- || ' SET x_profile = id FROM permission.grp_tree b'
+ || ' SET x_profile = b.id FROM permission.grp_tree b'
|| ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))';
EXECUTE 'SELECT migration_tools.assert(
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
AND NOT EXISTS (
SELECT id
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
ORDER BY 1,3;'
USING org, org_list;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE BTRIM(a.' || quote_ident(column_a)
|| ') = BTRIM(b.' || quote_ident(column_b) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = id FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.id FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE a.' || quote_ident(column_a)
|| ' = b.' || quote_ident(column_b);
END IF;
IF btrim_desired THEN
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE BTRIM(a.' || quote_ident(column_a)
|| ') = BTRIM(b.' || quote_ident(column_b) || ')';
ELSE
EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b'
- || ' SET ' || quote_ident(column_x) || ' = ' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
+ || ' SET ' || quote_ident(column_x) || ' = a.' || quote_ident(column_w) || ' FROM ' || quote_ident(table_a) || ' a'
|| ' WHERE a.' || quote_ident(column_a)
|| ' = b.' || quote_ident(column_b);
END IF;
END 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;
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)
|| ' 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 $$
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
AND NOT EXISTS (
SELECT id
WHERE owner = ANY ($2)
AND BTRIM('||sc||') = BTRIM(name)
) AND value = BTRIM('||sce||')
+ AND owner = ANY ($2)
)
ORDER BY 1,3;'
USING org, org_list;
projects / migration-tools.git / blobdiff