X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=59b67388ad4bf2610517246393ed968ba8d66d02;hp=5fc34779d536cfc5e0ce1a341cfbb89b91f74e45;hb=97d3c06d4fe2a6050868121a55f14ff4d5298226;hpb=2469fcbcdb9082997b5dad935f87040295116e61 diff --git a/sql/base/base.sql b/sql/base/base.sql index 5fc3477..59b6738 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -2821,6 +2821,45 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_circmod (TEXT,TEXT) RETURNS VO END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +-- convenience functions for handling item status maps + +CREATE OR REPLACE FUNCTION migration_tools.handle_status (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_status'' + )' INTO proceed USING table_schema, table_name; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column desired_status'; + END IF; + + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_status'; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_status INTEGER'; + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' + || ' SET x_status = id FROM config.copy_status b' + || ' WHERE BTRIM(UPPER(a.desired_status)) = BTRIM(UPPER(b.name))'; + + EXECUTE 'SELECT migration_tools.assert( + NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_status <> '''' AND x_status IS NULL), + ''Cannot find a desired copy status'', + ''Found all desired copy statuses'' + );'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + -- convenience function for handling desired_not_migrate CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURNS VOID AS $$ @@ -3357,3 +3396,118 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_user_barcode (TEXT,TEXT,TEXT,T END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; + +-- convenience function for linking two tables +-- e.g. select migration_tools.handle_link(:'migschema','asset_copy','barcode','test_foo','l_barcode','x_acp_id',false); +CREATE OR REPLACE FUNCTION migration_tools.handle_link (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) 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_x ALIAS FOR $6; + btrim_desired 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 'ALTER TABLE ' + || quote_ident(table_b) + || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x); + EXECUTE 'ALTER TABLE ' + || quote_ident(table_b) + || ' ADD COLUMN ' || quote_ident(column_x) || ' BIGINT'; + + IF btrim_desired THEN + EXECUTE 'UPDATE ' || quote_ident(table_b) || ' b' + || ' SET ' || quote_ident(column_x) || ' = 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' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b); + END IF; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +-- convenience function for linking two tables, but copying column w into column x instead of "id" +-- e.g. select migration_tools.handle_link2(:'migschema','asset_copy','barcode','test_foo','l_barcode','id','x_acp_id',false); +CREATE OR REPLACE FUNCTION migration_tools.handle_link2 (TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,TEXT,BOOLEAN) 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; + btrim_desired ALIAS FOR $8; + 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 'ALTER TABLE ' + || quote_ident(table_b) + || ' DROP COLUMN IF EXISTS ' || quote_ident(column_x); + EXECUTE 'ALTER TABLE ' + || quote_ident(table_b) + || ' ADD COLUMN ' || quote_ident(column_x) || ' TEXT'; + + 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' + || ' 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' + || ' WHERE a.' || quote_ident(column_a) + || ' = b.' || quote_ident(column_b); + END IF; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE;