X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=0629596dc599d702fdc6b8a114ace60c198fb4d5;hp=b05b4fd5245361b212b52ba02f3987f28194577b;hb=7eb372db1e9b60cf6f25eec7e1233f6f51559be8;hpb=2020fa0c3638c5ddb08bce54e3a0ffffb42bb82f diff --git a/sql/base/base.sql b/sql/base/base.sql index b05b4fd..0629596 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -2751,6 +2751,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) || ' 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; FOREACH o IN ARRAY org_list LOOP @@ -2758,6 +2759,7 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) || ' 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 o; END LOOP; @@ -2860,3 +2862,300 @@ CREATE OR REPLACE FUNCTION migration_tools.handle_not_migrate (TEXT,TEXT) RETURN END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; + +-- convenience function for handling desired_profile + +CREATE OR REPLACE FUNCTION migration_tools.handle_profile (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_profile'' + )' INTO proceed USING table_schema, table_name; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column desired_profile'; + END IF; + + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_profile'; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_profile INTEGER'; + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a' + || ' SET x_profile = id FROM permission.grp_tree b' + || ' WHERE BTRIM(UPPER(a.desired_profile)) = BTRIM(UPPER(b.name))'; + + EXECUTE 'SELECT migration_tools.assert( + NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_profile <> '''' AND x_profile IS NULL), + ''Cannot find a desired profile'', + ''Found all desired profiles'' + );'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +-- convenience function for handling desired actor stat cats + +CREATE OR REPLACE FUNCTION migration_tools.vivicate_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_name ALIAS FOR $2; + field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc. + org_shortname ALIAS FOR $4; + proceed BOOLEAN; + org INTEGER; + org_list INTEGER[]; + sc TEXT; + sce TEXT; + BEGIN + + SELECT 'desired_sc' || field_suffix INTO sc; + SELECT 'desired_sce' || field_suffix INTO sce; + + 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_name, sc; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column %', sc; + 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_name, sce; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column %', sce; + END IF; + + SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname; + IF org IS NULL THEN + RAISE EXCEPTION 'Cannot find org by shortname'; + END IF; + SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org ); + + -- caller responsible for their own truncates though we try to prevent duplicates + EXECUTE 'INSERT INTO actor_stat_cat (owner, name) + SELECT DISTINCT + $1 + ,BTRIM('||sc||') + FROM + ' || quote_ident(table_name) || ' + WHERE + NULLIF(BTRIM('||sc||'),'''') IS NOT NULL + AND NOT EXISTS ( + SELECT id + FROM actor.stat_cat + WHERE owner = ANY ($2) + AND name = BTRIM('||sc||') + ) + AND NOT EXISTS ( + SELECT id + FROM actor_stat_cat + WHERE owner = ANY ($2) + AND name = BTRIM('||sc||') + ) + ORDER BY 2;' + USING org, org_list; + + EXECUTE 'INSERT INTO actor_stat_cat_entry (stat_cat, owner, value) + SELECT DISTINCT + COALESCE( + (SELECT id + FROM actor.stat_cat + WHERE owner = ANY ($2) + AND BTRIM('||sc||') = BTRIM(name)) + ,(SELECT id + FROM actor_stat_cat + WHERE owner = ANY ($2) + AND BTRIM('||sc||') = BTRIM(name)) + ) + ,$1 + ,BTRIM('||sce||') + FROM + ' || quote_ident(table_name) || ' + WHERE + NULLIF(BTRIM('||sc||'),'''') IS NOT NULL + AND NULLIF(BTRIM('||sce||'),'''') IS NOT NULL + AND NOT EXISTS ( + SELECT id + FROM actor.stat_cat_entry + WHERE stat_cat = ( + SELECT id + FROM actor.stat_cat + WHERE owner = ANY ($2) + AND BTRIM('||sc||') = BTRIM(name) + ) AND value = BTRIM('||sce||') + ) + AND NOT EXISTS ( + SELECT id + FROM actor_stat_cat_entry + WHERE stat_cat = ( + SELECT id + FROM actor_stat_cat + WHERE owner = ANY ($2) + AND BTRIM('||sc||') = BTRIM(name) + ) AND value = BTRIM('||sce||') + ) + ORDER BY 1,3;' + USING org, org_list; + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.handle_actor_sc_and_sce (TEXT,TEXT,TEXT,TEXT) RETURNS VOID AS $$ + DECLARE + table_schema ALIAS FOR $1; + table_name ALIAS FOR $2; + field_suffix ALIAS FOR $3; -- for distinguishing between desired_sce1, desired_sce2, etc. + org_shortname ALIAS FOR $4; + proceed BOOLEAN; + org INTEGER; + org_list INTEGER[]; + o INTEGER; + sc TEXT; + sce TEXT; + BEGIN + SELECT 'desired_sc' || field_suffix INTO sc; + SELECT 'desired_sce' || field_suffix INTO sce; + 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_name, sc; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column %', sc; + 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_name, sce; + IF NOT proceed THEN + RAISE EXCEPTION 'Missing column %', sce; + END IF; + + SELECT id INTO org FROM actor.org_unit WHERE shortname = org_shortname; + IF org IS NULL THEN + RAISE EXCEPTION 'Cannot find org by shortname'; + END IF; + + SELECT INTO org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( org ); + + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_sc' || field_suffix; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_sc' || field_suffix || ' INTEGER'; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' DROP COLUMN IF EXISTS x_sce' || field_suffix; + EXECUTE 'ALTER TABLE ' + || quote_ident(table_name) + || ' ADD COLUMN x_sce' || field_suffix || ' INTEGER'; + + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' + SET + x_sc' || field_suffix || ' = id + FROM + (SELECT id, name, owner FROM actor_stat_cat + UNION SELECT id, name, owner FROM actor.stat_cat) u + WHERE + BTRIM(UPPER(u.name)) = BTRIM(UPPER(' || sc || ')) + AND u.owner = ANY ($1);' + USING org_list; + + EXECUTE 'UPDATE ' || quote_ident(table_name) || ' + SET + x_sce' || field_suffix || ' = id + FROM + (SELECT id, stat_cat, owner, value FROM actor_stat_cat_entry + UNION SELECT id, stat_cat, owner, value FROM actor.stat_cat_entry) u + WHERE + u.stat_cat = x_sc' || field_suffix || ' + AND BTRIM(UPPER(u.value)) = BTRIM(UPPER(' || sce || ')) + AND u.owner = ANY ($1);' + USING org_list; + + EXECUTE 'SELECT migration_tools.assert( + NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sc' || field_suffix || ' <> '''' AND x_sc' || field_suffix || ' IS NULL), + ''Cannot find a desired stat cat'', + ''Found all desired stat cats'' + );'; + + EXECUTE 'SELECT migration_tools.assert( + NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_sce' || field_suffix || ' <> '''' AND x_sce' || field_suffix || ' IS NULL), + ''Cannot find a desired stat cat entry'', + ''Found all desired stat cat entries'' + );'; + + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +-- convenience functions for adding shelving locations +DROP FUNCTION IF EXISTS migration_tools.find_shelf(INT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.find_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$ +DECLARE + return_id INT; + d INT; + cur_id INT; +BEGIN + SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id); + WHILE d >= 0 + LOOP + SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d); + SELECT INTO return_id id FROM asset.copy_location WHERE owning_lib = cur_id AND name ILIKE shelf_name; + IF return_id IS NOT NULL THEN + RETURN return_id; + END IF; + d := d - 1; + END LOOP; + + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +-- may remove later but testing using this with new migration scripts and not loading acls until go live + +DROP FUNCTION IF EXISTS migration_tools.find_mig_shelf(INT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.find_mig_shelf(org_id INT, shelf_name TEXT) RETURNS INTEGER AS $$ +DECLARE + return_id INT; + d INT; + cur_id INT; +BEGIN + SELECT INTO d MAX(distance) FROM actor.org_unit_ancestors_distance(org_id); + WHILE d >= 0 + LOOP + SELECT INTO cur_id id FROM actor.org_unit_ancestor_at_depth(org_id,d); + + SELECT INTO return_id id FROM + (SELECT * FROM asset.copy_location UNION ALL SELECT * FROM asset_copy_location) x + WHERE owning_lib = cur_id AND name ILIKE shelf_name; + IF return_id IS NOT NULL THEN + RETURN return_id; + END IF; + d := d - 1; + END LOOP; + + RETURN NULL; +END +$$ LANGUAGE plpgsql;