X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=cb096d0c928c7bf2b6857510f04b6714547aa117;hp=656015ef7009b12b018165729806c93efba9bf32;hb=b152406444c1dabacfa2da3b8fc2cd9434fd314f;hpb=0b30967a52114d24dbe09fe07e5df77090a2bf4d diff --git a/sql/base/base.sql b/sql/base/base.sql index 656015e..cb096d0 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -1,3 +1,19 @@ +-- Copyright 2009-2012, Equinox Software, Inc. +-- +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; either version 2 +-- of the License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + -------------------------------------------------------------------------- -- An example of how to use: -- @@ -606,7 +622,7 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen; return "$prefix$new_barcode$suffix"; -$$ LANGUAGE PLPERL STABLE; +$$ LANGUAGE PLPERLU STABLE; CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$ DECLARE @@ -713,7 +729,7 @@ CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS my $remainder = $total % 10; my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder; return $barcode . $checkdigit; -$$ LANGUAGE PLPERL STRICT STABLE; +$$ LANGUAGE PLPERLU STRICT STABLE; CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$ DECLARE @@ -939,6 +955,220 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$ + +-- Usage: +-- +-- First make sure the circ matrix is loaded and the circulations +-- have been staged to the extent possible (but at the very least +-- circ_lib, target_copy, usr, and *_renewal). User profiles and +-- circ modifiers must also be in place. +-- +-- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation'); +-- + +DECLARE + circ_lib INT; + target_copy INT; + usr INT; + is_renewal BOOLEAN; + this_duration_rule INT; + this_fine_rule INT; + this_max_fine_rule INT; + rcd config.rule_circ_duration%ROWTYPE; + rrf config.rule_recurring_fine%ROWTYPE; + rmf config.rule_max_fine%ROWTYPE; + circ INT; + n INT := 0; + n_circs INT; + +BEGIN + + EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs; + + FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP + + -- 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 || ';') + INTO circ_lib, target_copy, usr, is_renewal ; + SELECT + INTO this_duration_rule, + this_fine_rule, + this_max_fine_rule + duration_rule, + recuring_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 + WHERE id = this_duration_rule; + SELECT INTO rrf * FROM config.rule_recurring_fine + WHERE id = this_fine_rule; + 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, + max_fine_rule = rmf.name, + duration = rcd.normal, + recuring_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, + 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 + || ' (' || (100*n/n_circs) || '%) circs updated.'; + END IF; + + END LOOP; + + RETURN; +END; + +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$ + +-- Usage: +-- +-- First make sure the circ matrix is loaded and the circulations +-- have been staged to the extent possible (but at the very least +-- circ_lib, target_copy, usr, and *_renewal). User profiles and +-- circ modifiers must also be in place. +-- +-- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation'); +-- + +DECLARE + circ_lib INT; + target_copy INT; + usr INT; + is_renewal BOOLEAN; + this_duration_rule INT; + this_fine_rule INT; + this_max_fine_rule INT; + rcd config.rule_circ_duration%ROWTYPE; + rrf config.rule_recurring_fine%ROWTYPE; + rmf config.rule_max_fine%ROWTYPE; + circ INT; + n INT := 0; + n_circs INT; + +BEGIN + + EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs; + + FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP + + -- 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 || ';') + INTO circ_lib, target_copy, usr, is_renewal ; + SELECT + INTO this_duration_rule, + this_fine_rule, + this_max_fine_rule + (matchpoint).duration_rule, + (matchpoint).recurring_fine_rule, + (matchpoint).max_fine_rule + FROM action.find_circ_matrix_matchpoint( + circ_lib, + target_copy, + usr, + is_renewal + ); + SELECT INTO rcd * FROM config.rule_circ_duration + WHERE id = this_duration_rule; + SELECT INTO rrf * FROM config.rule_recurring_fine + WHERE id = this_fine_rule; + 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, + recurring_fine_rule = rrf.name, + max_fine_rule = rmf.name, + duration = rcd.normal, + 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, + grace_period = rrf.grace_period + FROM + config.rule_circ_duration rcd, + 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 + || ' (' || (100*n/n_circs) || '%) circs updated.'; + END IF; + + END LOOP; + + RETURN; +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. @@ -1078,7 +1308,7 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX 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 && + if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 && ! ( $field->as_string('9') =~ m/$shortname/ ) ) { $field->add_subfields( '9' => $shortname ); $field->update( ind2 => '0'); @@ -1396,4 +1626,100 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$ + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use MARC::Charset; + +MARC::Charset->assume_unicode(1); + +my $xml = shift; + +eval { my $r = MARC::Record->new_from_xml( $xml ); }; +if ($@) { + return 0; +} else { + return 1; +} + +$func$ LANGUAGE PLPERLU; +COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML'; + +CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$ +BEGIN + EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_hours_of_operation'$$; + EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_org_unit_closed'$$; + EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_org_unit_setting'$$; + EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_copy_location'$$; + EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$; + EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_call_number_prefix'$$; + EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_call_number_suffix'$$; + EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$; + EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_age_hold_protect'$$; + EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$; + EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$; +END; +$FUNC$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$ +BEGIN + EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$; + EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$; + EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$; + EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$; + EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$; + EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$; + EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$; + + -- import any new circ rules + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_circ_duration', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_age_hold_protect', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_max_fine', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_recurring_fine', 'id', 'name'); + +END; +$FUNC$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$ +DECLARE + name TEXT; + loopq TEXT; + existsq TEXT; + ct INTEGER; + cols TEXT[]; + copyst TEXT; +BEGIN + EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename; + EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$; + EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$; + loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol; + existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1'; + SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol; + FOR name IN EXECUTE loopq LOOP + EXECUTE existsq INTO ct USING name; + IF ct = 0 THEN + RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name; + copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || + ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1'; + EXECUTE copyst USING name; + END IF; + END LOOP; +END; +$FUNC$ LANGUAGE PLPGSQL;