X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=6a46f221ce3941e35ec61a50552adb7af07bc6b8;hp=751ce72fdca2634b431de6cd1c0d251f3f4eaf4f;hb=023943cdc7069a5aa405e1257e875e9e49acfbcb;hpb=a10d65c8040afbaa527ab930802222663d5be87e diff --git a/sql/base/base.sql b/sql/base/base.sql index 751ce72..6a46f22 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -117,7 +117,7 @@ CREATE OR REPLACE FUNCTION migration_tools.init (TEXT) RETURNS VOID AS $$ END; PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.config;' ); PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.config ( key TEXT UNIQUE, value TEXT);' ); - PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note'' );' ); + PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''production_tables'', ''asset.call_number,asset.call_number_prefix,asset.call_number_suffix,asset.copy_location,asset.copy,asset.stat_cat,asset.stat_cat_entry,asset.stat_cat_entry_copy_map,asset.copy_note,actor.usr,actor.card,actor.usr_address,actor.stat_cat,actor.stat_cat_entry,actor.stat_cat_entry_usr_map,actor.usr_note,actor.usr_standing_penalty,action.circulation,action.hold_request,action.hold_notification,money.grocery,money.billing,money.cash_payment,money.forgive_payment,acq.provider,acq.provider_address,acq.provider_note,acq.fund,acq.fund_allocation,acq.fund_tag,acq.funding_source,acq.funding_source_credit,acq.lineitem,acq.purchase_order,acq.po_item,acq.invoice,acq.invoice_item,acq.invoice_entry,acq.lineitem_detail,acq.fund_debit,acq.fund_transfer,acq.po_note'' );' ); PERFORM migration_tools.exec( $1, 'INSERT INTO ' || migration_schema || '.config (key,value) VALUES ( ''country_code'', ''USA'' );' ); PERFORM migration_tools.exec( $1, 'DROP TABLE IF EXISTS ' || migration_schema || '.fields_requiring_mapping;' ); PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.fields_requiring_mapping( table_schema TEXT, table_name TEXT, column_name TEXT, data_type TEXT);' ); @@ -219,7 +219,7 @@ CREATE OR REPLACE FUNCTION migration_tools.build (TEXT) RETURNS VOID AS $$ PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_copy_id_key ON ' || migration_schema || '.asset_copy ( id );' ); PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_record_idx ON ' || migration_schema || '.asset_call_number ( record );' ); PERFORM migration_tools.exec( $1, 'CREATE INDEX ' || migration_schema || '_callnum_upper_label_id_lib_idx ON ' || migration_schema || '.asset_call_number ( UPPER(label),id,owning_lib );' ); - PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label );' ); + PERFORM migration_tools.exec( $1, 'CREATE UNIQUE INDEX ' || migration_schema || '_callnum_label_once_per_lib ON ' || migration_schema || '.asset_call_number ( record,owning_lib,label,prefix,suffix );' ); END; $$ LANGUAGE PLPGSQL STRICT VOLATILE; @@ -688,7 +688,7 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUM output NUMERIC(8,2); BEGIN FOR output IN - EXECUTE 'SELECT ' || quote_literal(attempt_value) || '::NUMERIC(8,2) AS a;' + EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(8,2) AS a;' LOOP RETURN output; END LOOP; @@ -702,6 +702,27 @@ CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUM END; $$ LANGUAGE PLPGSQL STRICT STABLE; +CREATE OR REPLACE FUNCTION migration_tools.attempt_money6 (TEXT,TEXT) RETURNS NUMERIC(6,2) AS $$ + DECLARE + attempt_value ALIAS FOR $1; + fail_value ALIAS FOR $2; + output NUMERIC(6,2); + BEGIN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(REPLACE(REPLACE(attempt_value,'$',''),',','')) || '::NUMERIC(6,2) AS a;' + LOOP + RETURN output; + END LOOP; + EXCEPTION + WHEN OTHERS THEN + FOR output IN + EXECUTE 'SELECT ' || quote_literal(fail_value) || '::NUMERIC(6,2) AS a;' + LOOP + RETURN output; + END LOOP; + END; +$$ LANGUAGE PLPGSQL STRICT STABLE; + -- add_codabar_checkdigit -- $barcode source barcode -- @@ -724,6 +745,31 @@ CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS return $barcode . $checkdigit; $$ LANGUAGE PLPERLU STRICT STABLE; +-- add_code39mod43_checkdigit +-- $barcode source barcode +-- +-- If the source string is 13 or 14 characters long and contains only valid +-- Code 39 mod 43 characters, adds or replaces the 14th +-- character with a checkdigit computed according to the usual algorithm for library barcodes +-- using the Code 39 mod 43 symbology - see . If the +-- input string does not meet those requirements, it is returned unchanged. +-- +CREATE OR REPLACE FUNCTION migration_tools.add_code39mod43_checkdigit (TEXT) RETURNS TEXT AS $$ + my $barcode = shift; + + return $barcode if $barcode !~ /^[0-9A-Z. $\/+%-]{13,14}$/; + $barcode = substr($barcode, 0, 13); # ignore 14th character + + my @valid_chars = split //, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%'; + my %nums = map { $valid_chars[$_] => $_ } (0..42); + + my $total = 0; + $total += $nums{$_} foreach split(//, $barcode); + my $remainder = $total % 43; + my $checkdigit = $valid_chars[$remainder]; + return $barcode . $checkdigit; +$$ LANGUAGE PLPERLU STRICT STABLE; + CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$ DECLARE phone TEXT := $1; @@ -1159,6 +1205,111 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_to_specific_circ( tablename TEXT, circ BIGINT ) 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_to_specific_circ('m_nwrl.action_circulation', 18391960); +-- + +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; + n INT := 0; + n_circs INT := 1; + +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; + @@ -1888,3 +2039,123 @@ BEGIN END $func$ LANGUAGE PLPGSQL; + +-- example: SELECT * FROM migration_tools.duplicate_template(5,'{3,4}'); +CREATE OR REPLACE FUNCTION migration_tools.duplicate_template (INTEGER, INTEGER[]) RETURNS VOID AS $$ + DECLARE + target_event_def ALIAS FOR $1; + orgs ALIAS FOR $2; + BEGIN + DROP TABLE IF EXISTS new_atevdefs; + CREATE TEMP TABLE new_atevdefs (atevdef INTEGER); + FOR i IN array_lower(orgs,1) .. array_upper(orgs,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' + ,orgs[i] + ,name || ' (clone of '||target_event_def||')' + ,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_def + ; + 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_def + ; + 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_def + ; + END LOOP; + RAISE INFO '-- UPDATE action_trigger.event_definition SET active = CASE WHEN id = % THEN FALSE ELSE TRUE END WHERE id in (%,%);', target_event_def, target_event_def, (SELECT array_to_string(array_agg(atevdef),',') from new_atevdefs); + END; +$$ LANGUAGE PLPGSQL STRICT VOLATILE; + +CREATE OR REPLACE FUNCTION migration_tools.get_marc_tag (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT AS $$ + my ($marcxml, $tag, $subfield, $delimiter) = @_; + + use MARC::Record; + use MARC::File::XML; + use MARC::Field; + + my $field; + eval { + my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); + $field = $marc->field($tag); + }; + return $field->as_string($subfield,$delimiter); +$$ LANGUAGE PLPERLU STABLE; + +CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$ + my ($marcxml, $tag, $subfield, $delimiter) = @_; + + use MARC::Record; + use MARC::File::XML; + use MARC::Field; + + my @fields; + eval { + my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); + @fields = $marc->field($tag); + }; + my @texts; + foreach my $field (@fields) { + push @texts, $field->as_string($subfield,$delimiter); + } + return \@texts; +$$ LANGUAGE PLPERLU STABLE; +