END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
+-- creates other child table so you can have more than one child table in a schema from a base table
+CREATE OR REPLACE FUNCTION build_variant_staging_table(text, text, text)
+ RETURNS void
+ LANGUAGE plpgsql
+ STRICT
+AS $function$
+ DECLARE
+ migration_schema ALIAS FOR $1;
+ production_table ALIAS FOR $2;
+ base_staging_table ALIAS FOR $3;
+ columns RECORD;
+ BEGIN
+ --RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
+ PERFORM migration_tools.exec( $1, 'CREATE TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
+ PERFORM migration_tools.exec( $1, '
+ INSERT INTO ' || migration_schema || '.fields_requiring_mapping
+ SELECT table_schema, table_name, column_name, data_type
+ FROM information_schema.columns
+ WHERE table_schema = ''' || migration_schema || ''' AND table_name = ''' || base_staging_table || ''' AND is_nullable = ''NO'' AND column_default IS NULL;
+ ' );
+ FOR columns IN
+ SELECT table_schema, table_name, column_name, data_type
+ FROM information_schema.columns
+ WHERE table_schema = migration_schema AND table_name = base_staging_table AND is_nullable = 'NO' AND column_default IS NULL
+ LOOP
+ PERFORM migration_tools.exec( $1, 'ALTER TABLE ' || columns.table_schema || '.' || columns.table_name || ' ALTER COLUMN ' || columns.column_name || ' DROP NOT NULL;' );
+ END LOOP;
+ END;
+$function$
+
CREATE OR REPLACE FUNCTION migration_tools.create_linked_legacy_table_from (TEXT,TEXT,TEXT) RETURNS VOID AS $$
DECLARE
migration_schema ALIAS FOR $1;
END
$func$ LANGUAGE plpgsql;
+-- add koha holding tag to marc
+DROP FUNCTION IF EXISTS migration_tools.generate_koha_holding_tag(TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT, TEXT);
+
+CREATE OR REPLACE FUNCTION migration_tools.generate_koha_holding_tag(marc TEXT, tag TEXT, ind1 TEXT, ind2 TEXT, barcode TEXT, dateaccessioned TEXT, booksellerid TEXT, homebranch TEXT, price TEXT, replacementprice TEXT, replacementpricedate TEXT, datelastborrowed TEXT, datelastseen TEXT, stack TEXT, notforloan TEXT, damaged TEXT, itemlost TEXT, wthdrawn TEXT, itemcallnumber TEXT, issues TEXT, renewals TEXT, reserves TEXT, restricted TEXT, internalnotes TEXT, itemnotes TEXT, holdingbranch TEXT, location TEXT, onloan TEXT, cn_source TEXT, cn_sort TEXT, ccode TEXT, materials TEXT, uri TEXT, itype TEXT, enumchron TEXT, copynumber TEXT, stocknumber 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, $tag , $ind1 , $ind2 , $barcode , $dateaccessioned , $booksellerid , $homebranch , $price , $replacementprice , $replacementpricedate , $datelastborrowed , $datelastseen , $stack , $notforloan , $damaged , $itemlost , $wthdrawn , $itemcallnumber , $issues , $renewals , $reserves , $restricted , $internalnotes , $itemnotes , $holdingbranch , $location , $onloan , $cn_source , $cn_sort , $ccode , $materials , $uri , $itype , $enumchron , $copynumber , $stocknumber ) = @_;
+
+$marc_xml =~ s/(<leader>.........)./${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 $new_field = new MARC::Field(
+ $tag, $ind1, $ind2,
+ 'a' => $homebranch,
+ 'b' => $holdingbranch,
+ 'c' => $location,
+ 'p' => $barcode,
+ 'y' => $itype
+);
+
+if ($dateaccessioned) { $new_field->add_subfields('d' => $dateaccessioned); }
+if ($booksellerid) { $new_field->add_subfields('e' => $booksellerid); }
+if ($price) { $new_field->add_subfields('g' => $price); }
+if ($replacementprice) { $new_field->add_subfields('v' => $replacementprice); }
+if ($replacementpricedate) { $new_field->add_subfields('w' => $replacementpricedate); }
+if ($datelastborrowed) { $new_field->add_subfields('s' => $datelastborrowed); }
+if ($datelastseen) { $new_field->add_subfields('r' => $datelastseen); }
+if ($stack) { $new_field->add_subfields('j' => $stack); }
+if ($notforloan) { $new_field->add_subfields('7' => $notforloan); }
+if ($damaged) { $new_field->add_subfields('4' => $damaged); }
+if ($itemlost) { $new_field->add_subfields('1' => $itemlost); }
+if ($wthdrawn) { $new_field->add_subfields('0' => $wthdrawn); }
+if ($itemcallnumber) { $new_field->add_subfields('o' => $itemcallnumber); }
+if ($issues) { $new_field->add_subfields('l' => $issues); }
+if ($renewals) { $new_field->add_subfields('m' => $renewals); }
+if ($reserves) { $new_field->add_subfields('n' => $reserves); }
+if ($restricted) { $new_field->add_subfields('5' => $restricted); }
+if ($internalnotes) { $new_field->add_subfields('x' => $internalnotes); }
+if ($itemnotes) { $new_field->add_subfields('z' => $itemnotes); }
+if ($onloan) { $new_field->add_subfields('q' => $onloan); }
+if ($cn_source) { $new_field->add_subfields('2' => $cn_source); }
+if ($cn_sort) { $new_field->add_subfields('6' => $cn_sort); }
+if ($ccode) { $new_field->add_subfields('8' => $ccode); }
+if ($materials) { $new_field->add_subfields('3' => $materials); }
+if ($uri) { $new_field->add_subfields('u' => $uri); }
+if ($enumchron) { $new_field->add_subfields('h' => $enumchron); }
+if ($copynumber) { $new_field->add_subfields('t' => $copynumber); }
+if ($stocknumber) { $new_field->add_subfields('i' => $stocknumber); }
+
+$marc_xml->insert_grouped_field( $new_field );
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
CREATE OR REPLACE FUNCTION migration_tools.attempt_money (TEXT,TEXT) RETURNS NUMERIC(8,2) AS $$
DECLARE
attempt_value ALIAS FOR $1;
my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8');
$field = $marc->field($tag);
};
- return $field->as_string($subfield,$delimiter);
+ return $field->as_string($subfield,$delimiter) if $field;
+ return;
$$ LANGUAGE PLPERLU STABLE;
CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags (TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
$function$;
+-- removes tags from record based on tag, subfield and evidence
+-- example: strip_tag(marc, '500', 'a', 'gift') will remove 500s with 'gift' as a part of the $a
+DROP FUNCTION IF EXISTS migration_tools.strip_tag(TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.strip_tag(marc TEXT, tag TEXT, subfield TEXT, evidence 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;
+my $subfield = shift;
+my $evidence = shift;
+
+$marc_xml =~ s/(<leader>.........)./${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;
+
+my @fields_to_delete;
+
+foreach my $f (@fields) {
+ my $sf = lc($f->as_string($subfield));
+ if ($sf =~ m/$evidence/) { push @fields_to_delete, $f; }
+}
+
+$marc_xml->delete_fields(@fields_to_delete);
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
+
-- consolidate marc tag
DROP FUNCTION IF EXISTS migration_tools.consolidate_tag(TEXT,TEXT);
CREATE OR REPLACE FUNCTION migration_tools.consolidate_tag(marc TEXT, tag TEXT)
result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
safe_to_delete BOOLEAN := FALSE;
m action.found_circ_matrix_matchpoint;
- result_matchpoint INTEGER;
+ n action.found_circ_matrix_matchpoint;
-- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
+ result_matchpoint INTEGER;
BEGIN
SELECT INTO test_rule_object * FROM config.circ_matrix_matchpoint WHERE id = test_matchpoint;
RAISE INFO 'testing rule: %', test_rule_object;
user_object,
COALESCE(test_rule_object.is_renewal,FALSE)
);
- RAISE INFO 'action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
+ RAISE INFO ' action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
test_rule_object.org_unit,
item_object.id,
user_object.id,
COALESCE(test_rule_object.is_renewal,FALSE),
m.success,
- (m.matchpoint).id,
+ m.matchpoint,
m.buildrows
;
- FOR result_matchpoint IN SELECT UNNEST(m.buildrows)
- LOOP
- SELECT INTO result_rule_object * FROM config.circ_matrix_matchpoint WHERE id = result_matchpoint;
- RAISE INFO 'considering rule: %', result_rule_object;
- IF result_rule_object.id = test_rule_object.id THEN
- RAISE INFO 'found self';
- CONTINUE;
- END IF;
- IF (result_rule_object.circulate = test_rule_object.circulate
- AND result_rule_object.duration_rule = test_rule_object.duration_rule
- AND result_rule_object.recurring_fine_rule = test_rule_object.recurring_fine_rule
- AND result_rule_object.max_fine_rule = test_rule_object.max_fine_rule
- AND (
- (result_rule_object.hard_due_date IS NULL AND test_rule_object.hard_due_date IS NULL)
- OR (result_rule_object.hard_due_date = test_rule_object.hard_due_date)
- OR (result_rule_object.hard_due_date IS NOT NULL AND test_rule_object.hard_due_date IS NULL)
- )
- AND (
- (result_rule_object.renewals IS NULL AND test_rule_object.renewals IS NULL)
- OR (result_rule_object.renewals = test_rule_object.renewals)
- OR (result_rule_object.renewals IS NOT NULL AND test_rule_object.renewals IS NULL)
- )
- AND (
- (result_rule_object.grace_period IS NULL AND test_rule_object.grace_period IS NULL)
- OR (result_rule_object.grace_period = test_rule_object.grace_period)
- OR (result_rule_object.grace_period IS NOT NULL AND test_rule_object.grace_period IS NULL)
- )
- AND NOT EXISTS (
- SELECT limit_set, fallthrough
- FROM config.circ_matrix_limit_set_map
- WHERE active and matchpoint = test_rule_object.id
- EXCEPT
- SELECT limit_set, fallthrough
- FROM config.circ_matrix_limit_set_map
- WHERE active and matchpoint = result_rule_object.id
- )
- ) THEN
- RAISE INFO 'rule has same outcome';
- safe_to_delete := TRUE;
- ELSE
- RAISE INFO 'rule has different outcome, bail now';
- RAISE EXCEPTION 'rollback the item and user tables';
- END IF;
- END LOOP;
+ -- disable the rule being tested to see if the outcome changes
+ UPDATE config.circ_matrix_matchpoint SET active = FALSE WHERE id = (m.matchpoint).id;
+
+ SELECT INTO n * FROM action.find_circ_matrix_matchpoint(
+ test_rule_object.org_unit,
+ item_object,
+ user_object,
+ COALESCE(test_rule_object.is_renewal,FALSE)
+ );
+ RAISE INFO 'VS action.find_circ_matrix_matchpoint(%,%,%,%) = (%,%,%)',
+ test_rule_object.org_unit,
+ item_object.id,
+ user_object.id,
+ COALESCE(test_rule_object.is_renewal,FALSE),
+ n.success,
+ n.matchpoint,
+ n.buildrows
+ ;
+
+ -- FIXME: We could dig deeper and see if the referenced config.rule_*
+ -- entries are effectively equivalent, but for now, let's assume no
+ -- duplicate rules at that level
+ IF (
+ (m.matchpoint).circulate = (n.matchpoint).circulate
+ AND (m.matchpoint).duration_rule = (n.matchpoint).duration_rule
+ AND (m.matchpoint).recurring_fine_rule = (n.matchpoint).recurring_fine_rule
+ AND (m.matchpoint).max_fine_rule = (n.matchpoint).max_fine_rule
+ AND (
+ (m.matchpoint).hard_due_date = (n.matchpoint).hard_due_date
+ OR (
+ (m.matchpoint).hard_due_date IS NULL
+ AND (n.matchpoint).hard_due_date IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).renewals = (n.matchpoint).renewals
+ OR (
+ (m.matchpoint).renewals IS NULL
+ AND (n.matchpoint).renewals IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).grace_period = (n.matchpoint).grace_period
+ OR (
+ (m.matchpoint).grace_period IS NULL
+ AND (n.matchpoint).grace_period IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).total_copy_hold_ratio = (n.matchpoint).total_copy_hold_ratio
+ OR (
+ (m.matchpoint).total_copy_hold_ratio IS NULL
+ AND (n.matchpoint).total_copy_hold_ratio IS NULL
+ )
+ )
+ AND (
+ (m.matchpoint).available_copy_hold_ratio = (n.matchpoint).available_copy_hold_ratio
+ OR (
+ (m.matchpoint).available_copy_hold_ratio IS NULL
+ AND (n.matchpoint).available_copy_hold_ratio IS NULL
+ )
+ )
+ AND NOT EXISTS (
+ SELECT limit_set, fallthrough
+ FROM config.circ_matrix_limit_set_map
+ WHERE active and matchpoint = (m.matchpoint).id
+ EXCEPT
+ SELECT limit_set, fallthrough
+ FROM config.circ_matrix_limit_set_map
+ WHERE active and matchpoint = (n.matchpoint).id
+ )
+
+ ) THEN
+ RAISE INFO 'rule has same outcome';
+ safe_to_delete := TRUE;
+ ELSE
+ RAISE INFO 'rule has different outcome';
+ safe_to_delete := FALSE;
+ END IF;
- RAISE EXCEPTION 'rollback the item and user tables';
+ RAISE EXCEPTION 'rollback the temporary changes';
EXCEPTION WHEN OTHERS THEN
projects / migration-tools.git / blobdiff