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;
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