suffix := 'Jr.';
temp := REGEXP_REPLACE( temp, E'JR\.?\\s*', '', 'i' );
END IF;
+ IF temp ilike '%JR,%' THEN
+ suffix := 'Jr.';
+ temp := REGEXP_REPLACE( temp, E'JR,\\s*', ',', 'i' );
+ END IF;
IF temp ilike '%SR%' THEN
suffix := 'Sr.';
temp := REGEXP_REPLACE( temp, E'SR\.?\\s*', '', 'i' );
END IF;
+ IF temp ilike '%SR,%' THEN
+ suffix := 'Sr.';
+ temp := REGEXP_REPLACE( temp, E'SR,\\s*', ',', 'i' );
+ END IF;
IF temp ~ E'\\sII$' THEN
suffix := 'II';
temp := REGEXP_REPLACE( temp, E'II$', '', 'i' );
suffix := 'III';
temp := REGEXP_REPLACE( temp, E'III$', '', 'i' );
END IF;
+ IF temp ~ E'\\sIV$' THEN
+ suffix := 'IV';
+ temp := REGEXP_REPLACE( temp, E'IV$', '', 'i' );
+ END IF;
family_name := BTRIM( REGEXP_REPLACE(temp,E'^([^,]*)\\s*,.*$',E'\\1') );
first_given_name := BTRIM( CASE WHEN temp ~ ',' THEN REGEXP_REPLACE(temp,E'^[^,]*\\s*,\\s*([^,\\s]*)\\s*.*$',E'\\1') ELSE 'N/A' END );
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;
+ fail_value ALIAS FOR $2;
+ output NUMERIC(8,2);
+ BEGIN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ EXCEPTION
+ WHEN OTHERS THEN
+ FOR output IN
+ EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(8,2) AS a;'
+ LOOP
+ RETURN output;
+ END LOOP;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+-- add_codabar_checkdigit
+-- $barcode source barcode
+--
+-- If the source string is 13 or 14 characters long and contains only digits, adds or replaces the 14
+-- character with a checkdigit computed according to the usual algorithm for library barcodes
+-- using the Codabar symbology - see <http://www.makebarcode.com/specs/codabar.html>. If the
+-- input string does not meet those requirements, it is returned unchanged.
+--
+CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS TEXT AS $$
+ my $barcode = shift;
+
+ return $barcode if $barcode !~ /^\d{13,14}$/;
+ $barcode = substr($barcode, 0, 13); # ignore 14th digit
+ my @digits = split //, $barcode;
+ my $total = 0;
+ $total += $digits[$_] foreach (1, 3, 5, 7, 9, 11);
+ $total += (2 * $digits[$_] >= 10) ? (2 * $digits[$_] - 9) : (2 * $digits[$_]) foreach (0, 2, 4, 6, 8, 10, 12);
+ my $remainder = $total % 10;
+ my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder;
+ return $barcode . $checkdigit;
+$$ LANGUAGE PLPERL STRICT STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$
+ DECLARE
+ phone TEXT := $1;
+ areacode TEXT := $2;
+ temp TEXT := '';
+ output TEXT := '';
+ n_digits INTEGER := 0;
+ BEGIN
+ temp := phone;
+ temp := REGEXP_REPLACE(temp, '^1*[^0-9]*(?=[0-9])', '');
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2-\\3');
+ n_digits := LENGTH(REGEXP_REPLACE(REGEXP_REPLACE(temp, '(.*)?[a-zA-Z].*', E'\\1') , '[^0-9]', '', 'g'));
+ IF n_digits = 7 AND areacode <> '' THEN
+ temp := REGEXP_REPLACE(temp, '[^0-9]*([0-9]{3})[^0-9]*([0-9]{4})', E'\\1-\\2');
+ output := (areacode || '-' || temp);
+ ELSE
+ output := temp;
+ END IF;
+ RETURN output;
+ END;
+
+$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_leader (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $leader = $marc->leader();
+ substr($leader, $pos, 1) = $value;
+ $marc->leader($leader);
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ };
+ return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.set_008 (TEXT, INT, TEXT) RETURNS TEXT AS $$
+ my ($marcxml, $pos, $value) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $f008 = $marc->field('008');
+
+ if ($f008) {
+ my $field = $f008->data();
+ substr($field, $pos, 1) = $value;
+ $f008->update($field);
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ }
+ };
+ return $xml;
+$$ LANGUAGE PLPERLU STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_staff_profile (INT) RETURNS BOOLEAN AS $$
+ DECLARE
+ profile ALIAS FOR $1;
+ BEGIN
+ RETURN CASE WHEN 'Staff' IN (select (permission.grp_ancestors(profile)).name) THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.is_blank (TEXT) RETURNS BOOLEAN AS $$
+ BEGIN
+ RETURN CASE WHEN $1 = '' THEN TRUE ELSE FALSE END;
+ END;
+$$ LANGUAGE PLPGSQL STRICT STABLE;
+
+
+CREATE OR REPLACE FUNCTION migration_tools.insert_tags (TEXT, TEXT) RETURNS TEXT AS $$
+
+ my ($marcxml, $tags) = @_;
+
+ use MARC::Record;
+ use MARC::File::XML;
+
+ my $xml = $marcxml;
+
+ eval {
+ my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
+ my $to_insert = MARC::Record->new_from_xml("<record>$tags</record>", 'UTF-8');
+
+ my @incumbents = ();
+
+ foreach my $field ( $marc->fields() ) {
+ push @incumbents, $field->as_formatted();
+ }
+
+ foreach $field ( $to_insert->fields() ) {
+ if (!grep {$_ eq $field->as_formatted()} @incumbents) {
+ $marc->insert_fields_ordered( ($field) );
+ }
+ }
+
+ $xml = $marc->as_xml_record;
+ $xml =~ s/^<\?.+?\?>$//mo;
+ $xml =~ s/\n//sgo;
+ $xml =~ s/>\s+</></sgo;
+ };
+
+ return $xml;
+
+$$ LANGUAGE PLPERLU STABLE;
+
+CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix( 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_recuring_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,
+ 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
+ WHERE id = this_duration_rule;
+ SELECT INTO rrf * FROM config.rule_recuring_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 = rmf.amount,
+ renewal_remaining = rcd.max_renewals
+ FROM
+ config.rule_circ_duration rcd,
+ config.rule_recuring_fine rrf,
+ config.rule_max_fine rmf
+ WHERE
+ rcd.id = ' || this_duration_rule || ' AND
+ rrf.id = ' || this_fine_rule || ' AND
+ rmf.id = ' || this_max_fine_rule || ' 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.
+-- 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.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;
projects / migration-tools.git / blobdiff