X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=656015ef7009b12b018165729806c93efba9bf32;hp=d9c9e12c6217e72a4e0fdba4dd26f2c097e562a2;hb=0b30967a52114d24dbe09fe07e5df77090a2bf4d;hpb=5ae48c036a39dd252c80d8a1dfc602f933f5068d diff --git a/sql/base/base.sql b/sql/base/base.sql index d9c9e12..656015e 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -1052,7 +1052,7 @@ DECLARE BEGIN - EXECUTE ('TRUNCATE ' || c || ';'); + 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;'); @@ -1185,3 +1185,215 @@ CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETUR $$ 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; + +