X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=a333a1b3e4bd31b8bd2bae4956e9d88a7ba57dd3;hp=32152924b6e1e29773819a505dc634ea4f490a32;hb=46bd95616923cd2de0b1ed6b7a0fd8ffd0b103e2;hpb=179a09f80c5a518a3ddcd64fa7d837ed8e94fef7 diff --git a/sql/base/base.sql b/sql/base/base.sql index 3215292..a333a1b 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -1098,6 +1098,47 @@ CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RET return $barcode . $checkdigit; $$ LANGUAGE PLPERLU STRICT STABLE; +-- add_mod16_checkdigit +-- $barcode source barcode +-- +-- https://www.activebarcode.com/codes/checkdigit/modulo16.html + +CREATE OR REPLACE FUNCTION migration_tools.add_mod16_checkdigit (TEXT) RETURNS TEXT AS $$ + my $barcode = shift; + + my @digits = split //, $barcode; + my $total = 0; + foreach $digit (@digits) { + if ($digit =~ /[0-9]/) { $total += $digit; + } elsif ($digit eq '-') { $total += 10; + } elsif ($digit eq '$') { $total += 11; + } elsif ($digit eq ':') { $total += 12; + } elsif ($digit eq '/') { $total += 13; + } elsif ($digit eq '.') { $total += 14; + } elsif ($digit eq '+') { $total += 15; + } elsif ($digit eq 'A') { $total += 16; + } elsif ($digit eq 'B') { $total += 17; + } elsif ($digit eq 'C') { $total += 18; + } elsif ($digit eq 'D') { $total += 19; + } else { die "invalid digit <$digit>"; + } + } + my $remainder = $total % 16; + my $difference = 16 - $remainder; + my $checkdigit; + if ($difference < 10) { $checkdigit = $difference; + } elsif ($difference == 10) { $checkdigit = '-'; + } elsif ($difference == 11) { $checkdigit = '$'; + } elsif ($difference == 12) { $checkdigit = ':'; + } elsif ($difference == 13) { $checkdigit = '/'; + } elsif ($difference == 14) { $checkdigit = '.'; + } elsif ($difference == 15) { $checkdigit = '+'; + } else { die "error calculating checkdigit"; + } + + return $barcode . $checkdigit; +$$ LANGUAGE PLPERLU STRICT STABLE; + CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$ DECLARE phone TEXT := $1; @@ -2570,6 +2611,107 @@ CREATE OR REPLACE FUNCTION migration_tools.duplicate_template_but_change_delay ( END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; +-- example: SELECT * FROM migration_tools.duplicate_templates(3,'{5,6}'); +CREATE OR REPLACE FUNCTION migration_tools.duplicate_templates (INTEGER, INTEGER[]) RETURNS VOID AS $$ + DECLARE + org ALIAS FOR $1; + target_event_defs ALIAS FOR $2; + BEGIN + DROP TABLE IF EXISTS new_atevdefs; + CREATE TEMP TABLE new_atevdefs (atevdef INTEGER); + FOR i IN array_lower(target_event_defs,1) .. array_upper(target_event_defs,1) LOOP + INSERT INTO action_trigger.event_definition ( + active + ,owner + ,name + ,hook + ,validator + ,reactor + ,cleanup_success + ,cleanup_failure + ,delay + ,max_delay + ,usr_field + ,opt_in_setting + ,delay_field + ,group_field + ,template + ,granularity + ,repeat_delay + ) SELECT + 'f' + ,org + ,name || ' (clone of '||target_event_defs[i]||')' + ,hook + ,validator + ,reactor + ,cleanup_success + ,cleanup_failure + ,delay + ,max_delay + ,usr_field + ,opt_in_setting + ,delay_field + ,group_field + ,template + ,granularity + ,repeat_delay + FROM + action_trigger.event_definition + WHERE + id = target_event_defs[i] + ; + RAISE INFO 'created atevdef with id = %', currval('action_trigger.event_definition_id_seq'); + INSERT INTO new_atevdefs SELECT currval('action_trigger.event_definition_id_seq'); + INSERT INTO action_trigger.environment ( + event_def + ,path + ,collector + ,label + ) SELECT + currval('action_trigger.event_definition_id_seq') + ,path + ,collector + ,label + FROM + action_trigger.environment + WHERE + event_def = target_event_defs[i] + ; + INSERT INTO action_trigger.event_params ( + event_def + ,param + ,value + ) SELECT + currval('action_trigger.event_definition_id_seq') + ,param + ,value + FROM + action_trigger.event_params + WHERE + event_def = target_event_defs[i] + ; + END LOOP; + RAISE INFO '-- UPDATE action_trigger.event_definition SET active = TRUE WHERE id in (%);', (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs); + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.reset_event (BIGINT) RETURNS VOID AS $$ + UPDATE + action_trigger.event + SET + start_time = NULL + ,update_time = NULL + ,complete_time = NULL + ,update_process = NULL + ,state = 'pending' + ,template_output = NULL + ,error_output = NULL + ,async_output = NULL + WHERE + id = $1; +$$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION migration_tools.get_marc_leader (TEXT) RETURNS TEXT AS $$ my ($marcxml) = @_; @@ -3256,6 +3398,101 @@ BEGIN END $$ LANGUAGE plpgsql; +-- yet another subfield 9 function, this one only adds the $9 if the ind1 = 1 or 4 and ind2 = 0 or 1 +DROP FUNCTION IF EXISTS migration_tools.strict_add_sf9(TEXT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.strict_add_sf9(marc TEXT, new_9 TEXT) + RETURNS TEXT + LANGUAGE plperlu +AS $function$ +use strict; +use warnings; + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'utf8'); + +binmode(STDERR, ':bytes'); +binmode(STDOUT, ':utf8'); +binmode(STDERR, ':utf8'); + +my $marc_xml = shift; +my $new_9_to_set = shift; + +$marc_xml =~ s/(.........)./${1}a/; + +eval { + $marc_xml = MARC::Record->new_from_xml($marc_xml); +}; +if ($@) { + #elog("could not parse $bibid: $@\n"); + import MARC::File::XML (BinaryEncoding => 'utf8'); + return $marc_xml; +} + +my @uris = $marc_xml->field('856'); +return $marc_xml->as_xml_record() unless @uris; + +foreach my $field (@uris) { + my $ind1 = $field->indicator('1'); + if (!defined $ind1) { next; } + if ($ind1 ne '1' && $ind1 ne '4') { next; } + my $ind2 = $field->indicator('2'); + if (!defined $ind2) { next; } + if ($ind2 ne '0' && $ind2 ne '1') { next; } + $field->add_subfields( '9' => $new_9_to_set ); +} + +return $marc_xml->as_xml_record(); + +$function$; + +-- yet another subfield 9 function, this one only adds the $9 and forces +-- ind1 = 4 if not already ind1 = 1 or 4 and ind2 = 0 if not already ind2 = 0 or 1 +DROP FUNCTION IF EXISTS migration_tools.force_add_sf9(TEXT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.force_add_sf9(marc TEXT, new_9 TEXT) + RETURNS TEXT + LANGUAGE plperlu +AS $function$ +use strict; +use warnings; + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'utf8'); + +binmode(STDERR, ':bytes'); +binmode(STDOUT, ':utf8'); +binmode(STDERR, ':utf8'); + +my $marc_xml = shift; +my $new_9_to_set = shift; + +$marc_xml =~ s/(.........)./${1}a/; + +eval { + $marc_xml = MARC::Record->new_from_xml($marc_xml); +}; +if ($@) { + #elog("could not parse $bibid: $@\n"); + import MARC::File::XML (BinaryEncoding => 'utf8'); + return $marc_xml; +} + +my @uris = $marc_xml->field('856'); +return $marc_xml->as_xml_record() unless @uris; + +foreach my $field (@uris) { + my $ind1 = $field->indicator('1'); + if (!defined $ind1) { next; } + if ($ind1 ne '1' && $ind1 ne '4') { $field->set_indicator(1,'4'); } + my $ind2 = $field->indicator('2'); + if (!defined $ind2) { next; } + if ($ind2 ne '0' && $ind2 ne '1') { $field->set_indicator(2,'0'); } + $field->add_subfields( '9' => $new_9_to_set ); +} + +return $marc_xml->as_xml_record(); + +$function$; + -- alternate adding subfield 9 function in that it adds them to existing tags where the 856$u matches a correct value only DROP FUNCTION IF EXISTS migration_tools.add_sf9(TEXT,TEXT,TEXT); CREATE OR REPLACE FUNCTION migration_tools.add_sf9(marc TEXT, partial_u TEXT, new_9 TEXT) @@ -3296,7 +3533,7 @@ foreach my $field (@uris) { if (!defined $ind2) { next; } if ($ind2 ne '0') { next; } if (!defined $sfu) { next; } - if ($sfu =~ m/$matching_u_text/) { + if ($sfu =~ m/$matching_u_text/ or $matching_u_text eq 'pineapple') { $field->add_subfields( '9' => $new_9_to_set ); last; } @@ -3318,7 +3555,7 @@ BEGIN EXECUTE 'SELECT marc FROM ' || bib_table || ' WHERE id = ' || bib_id INTO source_xml; - SELECT add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml; + SELECT migration_tools.add_sf9(source_xml, target_u_text, sf9_text) INTO new_xml; r = FALSE; new_xml = '$_$' || new_xml || '$_$'; @@ -3333,6 +3570,45 @@ BEGIN END; $BODY$ LANGUAGE plpgsql; +-- strip marc tag +DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT) + RETURNS TEXT + LANGUAGE plperlu +AS $function$ +use strict; +use warnings; + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'utf8'); + +binmode(STDERR, ':bytes'); +binmode(STDOUT, ':utf8'); +binmode(STDERR, ':utf8'); + +my $marc_xml = shift; +my $tag = shift; + +$marc_xml =~ s/(.........)./${1}a/; + +eval { + $marc_xml = MARC::Record->new_from_xml($marc_xml); +}; +if ($@) { + #elog("could not parse $bibid: $@\n"); + import MARC::File::XML (BinaryEncoding => 'utf8'); + return $marc_xml; +} + +my @fields = $marc_xml->field($tag); +return $marc_xml->as_xml_record() unless @fields; + +$marc_xml->delete_fields(@fields); + +return $marc_xml->as_xml_record(); + +$function$; + -- convenience function for linking to the item staging table CREATE OR REPLACE FUNCTION migration_tools.handle_item_barcode (TEXT,TEXT,TEXT,TEXT,BOOLEAN) RETURNS VOID AS $$ @@ -3799,3 +4075,24 @@ BEGIN END $function$; +DROP FUNCTION IF EXISTS migration_tools.null_empty_lcolumns(TEXT,TEXT); +CREATE OR REPLACE FUNCTION migration_tools.null_empty_lcolumns(s_name TEXT, t_name TEXT) RETURNS BOOLEAN + LANGUAGE plpgsql +AS $function$ +DECLARE + c_name TEXT; +BEGIN + + FOR c_name IN SELECT column_name FROM information_schema.columns WHERE + table_name = t_name + AND table_schema = s_name + AND (data_type='text' OR data_type='character varying') + AND column_name like 'l_%' + LOOP + EXECUTE FORMAT('UPDATE ' || s_name || '.' || t_name || ' SET ' || c_name || ' = NULL WHERE ' || c_name || ' = '''' '); + END LOOP; + + RETURN TRUE; +END +$function$; +