+-- 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:
--
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
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
$$ 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.
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');
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;
+
+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;
+
projects / migration-tools.git / blobdiff