+-- 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.
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;');
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');
$$ LANGUAGE PLPERLU STABLE;
-
CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$
DECLARE
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;
- FOR ou IN EXECUTE ('SELECT DISTINCT id FROM actor.org_unit ORDER BY 1;') LOOP
+-- 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 depth FROM actor.org_unit_type WHERE id = ou INTO org_unit_depth;
- SELECT depth FROM actor.org_unit_type WHERE id = ou_parent INTO parent_depth;
-
- IF ou_parent IS NOT NULL AND org_unit_depth - parent_depth <> 1 THEN
- 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;
- 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;
+ 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;
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;
+
+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