X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=656015ef7009b12b018165729806c93efba9bf32;hp=3119af29417ec55e09207e469f36e45ad8e45679;hb=0b30967a52114d24dbe09fe07e5df77090a2bf4d;hpb=d39ef60a6d19e0643b896eb26674d381ea4c5c2f diff --git a/sql/base/base.sql b/sql/base/base.sql index 3119af2..656015e 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -200,6 +200,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_barcode_key ON ' || migration_schema || '.actor_card ( barcode );' ); PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_patron_usrname_key ON ' || migration_schema || '.actor_usr ( usrname );' ); PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_barcode_key ON ' || migration_schema || '.asset_copy ( barcode );' ); + PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' ); PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' ); PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' ); PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' ); @@ -650,6 +651,27 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_date (TEXT,TEXT) RETURNS DATE END; $$ LANGUAGE PLPGSQL STRICT STABLE; +CREATE OR REPLACE FUNCTION migration_tools.attempt_timestamptz (TEXT,TEXT) RETURNS TIMESTAMPTZ AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output TIMESTAMPTZ; + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::TIMESTAMPTZ AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMPTZ AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$ DECLARE attempt_value ALIAS FOR $1; @@ -833,7 +855,7 @@ DECLARE this_fine_rule INT; this_max_fine_rule INT; rcd config.rule_circ_duration%ROWTYPE; - rrf config.rule_recuring_fine%ROWTYPE; + rrf config.rule_recurring_fine%ROWTYPE; rmf config.rule_max_fine%ROWTYPE; circ INT; n INT := 0; @@ -847,60 +869,66 @@ BEGIN -- Fetch the correct rules for this circulation EXECUTE (' - SELECT - circ_lib, - target_copy, - usr, - CASE - WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE - ELSE FALSE - END - FROM ' || tablename || ' WHERE id = ' || circ || ';') + SELECT + circ_lib, + target_copy, + usr, + CASE + WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE + ELSE FALSE + END + FROM ' || tablename || ' WHERE id = ' || circ || ';') INTO circ_lib, target_copy, usr, is_renewal ; - SELECT + SELECT INTO this_duration_rule, this_fine_rule, - this_max_fine_rule - duration_rule, - recurring_fine_rule, - max_fine_rule - FROM action.find_circ_matrix_matchpoint( - circ_lib, - target_copy, - usr, - is_renewal + this_max_fine_rule + duration_rule, + recurring_fine_rule, + max_fine_rule + FROM action.find_circ_matrix_matchpoint( + circ_lib, + target_copy, + usr, + is_renewal ); - SELECT INTO rcd * FROM config.rule_circ_duration + SELECT INTO rcd * FROM config.rule_circ_duration WHERE id = this_duration_rule; - SELECT INTO rrf * FROM config.rule_recuring_fine + SELECT INTO rrf * FROM config.rule_recurring_fine WHERE id = this_fine_rule; - SELECT INTO rmf * FROM config.rule_max_fine + SELECT INTO rmf * FROM config.rule_max_fine WHERE id = this_max_fine_rule; -- Apply the rules to this circulation EXECUTE ('UPDATE ' || tablename || ' c SET duration_rule = rcd.name, - recuring_fine_rule = rrf.name, + recurring_fine_rule = rrf.name, max_fine_rule = rmf.name, duration = rcd.normal, - recuring_fine = rrf.normal, - max_fine = rmf.amount, + recurring_fine = rrf.normal, + max_fine = + CASE rmf.is_percent + WHEN TRUE THEN (rmf.amount / 100.0) * ac.price + ELSE rmf.amount + END, renewal_remaining = rcd.max_renewals FROM config.rule_circ_duration rcd, - config.rule_recuring_fine rrf, - config.rule_max_fine rmf + config.rule_recurring_fine rrf, + config.rule_max_fine rmf, + asset.copy ac WHERE rcd.id = ' || this_duration_rule || ' AND rrf.id = ' || this_fine_rule || ' AND rmf.id = ' || this_max_fine_rule || ' AND + ac.id = c.target_copy AND c.id = ' || circ || ';'); -- Keep track of where we are in the process n := n + 1; - IF (n % 100 = 0) THEN - RAISE INFO '%', n || ' of ' || n_circs + IF (n % 100 = 0) THEN + RAISE INFO '%', n || ' of ' || n_circs || ' (' || (100*n/n_circs) || '%) circs updated.'; END IF; @@ -910,3 +938,462 @@ BEGIN END; $$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$ + +-- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat. +-- Then SELECT migration_tools.stage_not_applicable_asset_stat_cats('m_foo'); + +-- TODO: Make a variant that will go directly to production tables -- which would be useful for retrofixing the absence of N/A cats. +-- TODO: Add a similar tool for actor stat cats, which behave differently. + +DECLARE + c TEXT := schemaname || '.asset_copy_legacy'; + sc TEXT := schemaname || '.asset_stat_cat'; + sce TEXT := schemaname || '.asset_stat_cat_entry'; + scecm TEXT := schemaname || '.asset_stat_cat_entry_copy_map'; + stat_cat INT; + stat_cat_entry INT; + +BEGIN + + FOR stat_cat IN EXECUTE ('SELECT id FROM ' || sc) LOOP + + EXECUTE ('SELECT id FROM ' || sce || ' WHERE stat_cat = ' || stat_cat || E' AND value = \'Not Applicable\';') INTO stat_cat_entry; + + EXECUTE ('INSERT INTO ' || scecm || ' (owning_copy, stat_cat, stat_cat_entry) + SELECT c.id, ' || stat_cat || ', ' || stat_cat_entry || ' FROM ' || c || ' c WHERE c.id NOT IN + (SELECT owning_copy FROM ' || scecm || ' WHERE stat_cat = ' || stat_cat || ');'); + + END LOOP; + + RETURN; +END; + +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.assign_standing_penalties ( ) RETURNS VOID AS $$ + +-- USAGE: Once circulation data has been loaded, and group penalty thresholds have been set up, run this. +-- This will assign standing penalties as needed. + +DECLARE + org_unit INT; + usr INT; + +BEGIN + + FOR org_unit IN EXECUTE ('SELECT DISTINCT org_unit FROM permission.grp_penalty_threshold;') LOOP + + FOR usr IN EXECUTE ('SELECT id FROM actor.usr WHERE NOT deleted;') LOOP + + EXECUTE('SELECT actor.calculate_system_penalties(' || usr || ', ' || org_unit || ');'); + + END LOOP; + + END LOOP; + + RETURN; + +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_pristine_database () RETURNS VOID AS $$ + +BEGIN + INSERT INTO metabib.metarecord (fingerprint, master_record) + SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id + FROM biblio.record_entry b + WHERE NOT b.deleted + AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL) + AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint ) + ORDER BY b.fingerprint, b.quality DESC; + INSERT INTO metabib.metarecord_source_map (metarecord, source) + SELECT m.id, r.id + FROM biblio.record_entry r + JOIN metabib.metarecord m USING (fingerprint) + WHERE NOT r.deleted; +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.insert_metarecords_for_incumbent_database () RETURNS VOID AS $$ + +BEGIN + INSERT INTO metabib.metarecord (fingerprint, master_record) + SELECT DISTINCT ON (b.fingerprint) b.fingerprint, b.id + FROM biblio.record_entry b + WHERE NOT b.deleted + AND b.id IN (SELECT r.id FROM biblio.record_entry r LEFT JOIN metabib.metarecord_source_map k ON (k.source = r.id) WHERE k.id IS NULL AND r.fingerprint IS NOT NULL) + AND NOT EXISTS ( SELECT 1 FROM metabib.metarecord WHERE fingerprint = b.fingerprint ) + ORDER BY b.fingerprint, b.quality DESC; + INSERT INTO metabib.metarecord_source_map (metarecord, source) + SELECT m.id, r.id + FROM biblio.record_entry r + JOIN metabib.metarecord m USING (fingerprint) + WHERE NOT r.deleted + AND r.id IN (SELECT b.id FROM biblio.record_entry b LEFT JOIN metabib.metarecord_source_map k ON (k.source = b.id) WHERE k.id IS NULL); +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.create_cards( schemaname TEXT ) RETURNS VOID AS $$ + +-- USAGE: Make sure the patrons are staged in schemaname.actor_usr_legacy and have 'usrname' assigned. +-- Then SELECT migration_tools.create_cards('m_foo'); + +DECLARE + u TEXT := schemaname || '.actor_usr_legacy'; + c TEXT := schemaname || '.actor_card'; + +BEGIN + + 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;'); + + RETURN; + +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEXT AS $$ + + ## USAGE: UPDATE biblio.record_entry SET marc = migration_tools.insert_856_9(marc, 'ABC') WHERE [...]; + + my ($marcxml, $shortname) = @_; + + use MARC::Record; + use MARC::File::XML; + + my $xml = $marcxml; + + eval { + my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); + + foreach my $field ( $marc->field('856') ) { + if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 && + ! ( $field->as_string('9') =~ m/$shortname/ ) ) { + $field->add_subfields( '9' => $shortname ); + $field->update( ind2 => '0'); + } + } + + $xml = $marc->as_xml_record; + $xml =~ s/^<\?.+?\?>$//mo; + $xml =~ s/\n//sgo; + $xml =~ s/>\s+) { + chomp; + my ($junk, $state, $city, $zip, $foo, $bar, $county, $baz, $morejunk) = split(/\|/); + $zipdata{$zip} = [$city, $state, $county]; + } + + if (defined $zipdata{$input}) { + my ($city, $state, $county) = @{$zipdata{$input}}; + return [$city, $state, $county]; + } elsif (defined $zipdata{substr $input, 0, 5}) { + my ($city, $state, $county) = @{$zipdata{substr $input, 0, 5}}; + return [$city, $state, $county]; + } else { + return ['ZIP not found', 'ZIP not found', 'ZIP not found']; + } + +$$ 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; + +