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;
$$ LANGUAGE PLPGSQL STRICT STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
+BEGIN
+-- Expects the following table/columns:
+
+-- export_biblio_tsv:
+-- l_bibid | 1
+-- l_create_dt | 2007-03-07 09:03:09
+-- l_last_change_dt | 2015-01-23 11:18:54
+-- l_last_change_userid | 2
+-- l_material_cd | 10
+-- l_collection_cd | 13
+-- l_call_nmbr1 | Canada
+-- l_call_nmbr2 | ON
+-- l_call_nmbr3 | Ottawa 18
+-- l_title | Art and the courts : France ad England
+-- l_title_remainder | from 1259-1328
+-- l_responsibility_stmt |
+-- l_author | National Gallery of Canada
+-- l_topic1 |
+-- l_topic2 |
+-- l_topic3 |
+-- l_topic4 |
+-- l_topic5 |
+-- l_opac_flg | Y
+-- l_flag_attention | 0
+
+-- export_biblio_field_tsv:
+-- l_bibid | 1
+-- l_fieldid | 1
+-- l_tag | 720
+-- l_ind1_cd | N
+-- l_ind2_cd | N
+-- l_subfield_cd | a
+-- l_field_data | Brieger, Peter Henry
+
+-- Map export_biblio_tsv as follows:
+-- l_call_nmbr? -> 099a
+-- l_author -> 100a
+-- l_title -> 245a
+-- l_title_remainder -> 245b
+-- l_responsibility_stmt -> 245c
+-- l_topic? -> 650a
+-- l_bibid -> 001
+
+RETURN
+ migration_tools.consolidate_tag( migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data), '245' )
+FROM (
+ select
+ array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
+ array_agg(l_ind1_cd) as "ind1",
+ array_agg(l_ind2_cd) as "ind2",
+ array_agg(l_field_data) as "data"
+ from (
+ select
+ l_tag,
+ l_subfield_cd,
+ l_ind1_cd,
+ l_ind2_cd,
+ l_field_data
+ from export_biblio_field_tsv
+ where l_bibid = x_bibid
+ union
+ select
+ '099' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ concat_ws(' ',
+ nullif(btrim(l_call_nmbr1),''),
+ nullif(btrim(l_call_nmbr2),''),
+ nullif(btrim(l_call_nmbr3),'')
+ ) as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid
+ union
+ select
+ '100' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_author as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
+ union
+ select
+ '245' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_title as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
+ union
+ select
+ '245' as "l_tag",
+ 'b' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_title_remainder as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic1 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic2 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic3 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic4 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
+ union
+ select
+ '650' as "l_tag",
+ 'a' as "l_subfield_cd",
+ ' ' as "l_ind1_cd",
+ ' ' as "l_ind2_cd",
+ l_topic5 as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
+ union
+ select
+ '001' as "l_tag",
+ '' as "l_subfield_cd",
+ '' as "l_ind1_cd",
+ '' as "l_ind2_cd",
+ l_bibid as "l_field_data"
+ from export_biblio_tsv
+ where l_bibid = x_bibid
+ ) x
+) y;
+
+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;
If the value for a given field is NULL or the empty string, it is not inserted.
$$;
+CREATE OR REPLACE FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) RETURNS TEXT AS $func$
+
+use strict;
+use warnings;
+
+use MARC::Record;
+use MARC::File::XML (BinaryEncoding => 'UTF-8');
+use Text::CSV;
+
+my $in_tags = shift;
+my $in_ind1 = shift;
+my $in_ind2 = shift;
+my $in_values = shift;
+
+# hack-and-slash parsing of array-passed-as-string;
+# this can go away once everybody is running Postgres 9.1+
+my $csv = Text::CSV->new({binary => 1});
+$in_tags =~ s/^{//;
+$in_tags =~ s/}$//;
+my $status = $csv->parse($in_tags);
+my $tags = [ $csv->fields() ];
+$in_ind1 =~ s/^{//;
+$in_ind1 =~ s/}$//;
+$status = $csv->parse($in_ind1);
+my $ind1s = [ $csv->fields() ];
+$in_ind2 =~ s/^{//;
+$in_ind2 =~ s/}$//;
+$status = $csv->parse($in_ind2);
+my $ind2s = [ $csv->fields() ];
+$in_values =~ s/^{//;
+$in_values =~ s/}$//;
+$status = $csv->parse($in_values);
+my $values = [ $csv->fields() ];
+
+my $marc = MARC::Record->new();
+
+$marc->leader('00000nam a22000007 4500');
+$marc->append_fields(MARC::Field->new('008', '000000s 000 eng d'));
+
+foreach my $i (0..$#$tags) {
+ my ($tag, $sf);
+ if ($tags->[$i] =~ /^(\d{3})([0-9a-z])$/) {
+ $tag = $1;
+ $sf = $2;
+ $marc->append_fields(MARC::Field->new($tag, $ind1s->[$i], $ind2s->[$i], $sf => $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
+ } elsif ($tags->[$i] =~ /^(\d{3})$/) {
+ $tag = $1;
+ $marc->append_fields(MARC::Field->new($tag, $values->[$i])) if $values->[$i] !~ /^\s*$/ and $values->[$i] ne 'NULL';
+ }
+}
+
+my $xml = $marc->as_xml_record;
+$xml =~ s/^<\?.+?\?>$//mo;
+$xml =~ s/\n//sgo;
+$xml =~ s/>\s+</></sgo;
+
+return $xml;
+
+$func$ LANGUAGE PLPERLU;
+COMMENT ON FUNCTION migration_tools.make_stub_bib (text[], text[], text[], text[]) IS $$Simple function to create a stub MARCXML bib from a set of columns.
+The first argument is an array of tag/subfield specifiers, e.g., ARRAY['001', '245a', '500a'].
+The second argument is an array of text containing the values to plug into indicator 1 for each field.
+The third argument is an array of text containing the values to plug into indicator 2 for each field.
+The fourth argument is an array of text containing the values to plug into each field.
+If the value for a given field is NULL or the empty string, it is not inserted.
+$$;
+
CREATE OR REPLACE FUNCTION migration_tools.set_indicator (TEXT, TEXT, INTEGER, CHAR(1)) RETURNS TEXT AS $func$
my ($marcxml, $tag, $pos, $value) = @_;
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)
+ 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/(<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 @combined_subfield_refs = ();
+my @combined_subfields = ();
+foreach my $field (@fields) {
+ my @subfield_refs = $field->subfields();
+ push @combined_subfield_refs, @subfield_refs;
+}
+
+my @sorted_subfield_refs = reverse sort { $a->[0] <=> $b->[0] } @combined_subfield_refs;
+
+while ( my $tuple = pop( @sorted_subfield_refs ) ) {
+ my ($code,$data) = @$tuple;
+ unshift( @combined_subfields, $code, $data );
+}
+
+$marc_xml->delete_fields(@fields);
+
+my $new_field = new MARC::Field(
+ $tag,
+ $fields[0]->indicator(1),
+ $fields[0]->indicator(2),
+ @combined_subfields
+);
+
+$marc_xml->insert_grouped_field( $new_field );
+
+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 $$
RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
END
$function$ LANGUAGE plpgsql;
+
+-- alternate version: convenience function for handling item barcode collisions in asset_copy_legacy
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT barcode FROM asset_copy_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy SET barcode = migration_schema || '_internal_collision_' || id || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal collisions', internal_collision_count;
+ FOR x_barcode IN SELECT a.barcode FROM asset.copy a, asset_copy_legacy b WHERE x_migrate AND a.deleted IS FALSE AND a.barcode = b.barcode
+ LOOP
+ FOR x_id IN SELECT id FROM asset_copy_legacy WHERE barcode = x_barcode
+ LOOP
+ UPDATE asset_copy_legacy SET barcode = migration_schema || '_' || barcode WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_collision_count := incumbent_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent collisions', incumbent_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+-- alternate version: convenience function for handling patron barcode/usrname collisions in actor_usr_legacy
+-- this should be ran prior to populating actor_card
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_actor_barcode_collisions2(migration_schema TEXT) RETURNS VOID AS $function$
+DECLARE
+ x_barcode TEXT;
+ x_id BIGINT;
+ row_count NUMERIC;
+ internal_collision_count NUMERIC := 0;
+ incumbent_barcode_collision_count NUMERIC := 0;
+ incumbent_usrname_collision_count NUMERIC := 0;
+BEGIN
+ FOR x_barcode IN SELECT usrname FROM actor_usr_legacy WHERE x_migrate GROUP BY 1 HAVING COUNT(*) > 1
+ LOOP
+ FOR x_id IN SELECT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_internal_collision_' || id || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ internal_collision_count := internal_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% internal usrname/barcode collisions', internal_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.barcode FROM actor.card a, actor_usr_legacy b WHERE x_migrate AND a.barcode = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_barcode_collision_count := incumbent_barcode_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent barcode collisions', incumbent_barcode_collision_count;
+
+ FOR x_barcode IN
+ SELECT a.usrname FROM actor.usr a, actor_usr_legacy b WHERE x_migrate AND a.usrname = b.usrname
+ LOOP
+ FOR x_id IN SELECT DISTINCT id FROM actor_usr_legacy WHERE x_migrate AND usrname = x_barcode
+ LOOP
+ UPDATE actor_usr_legacy SET usrname = migration_schema || '_' || usrname WHERE id = x_id;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ incumbent_usrname_collision_count := incumbent_usrname_collision_count + row_count;
+ END LOOP;
+ END LOOP;
+ RAISE INFO '% incumbent usrname collisions (post barcode collision munging)', incumbent_usrname_collision_count;
+END
+$function$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION migration_tools.is_circ_rule_safe_to_delete( test_matchpoint INTEGER ) RETURNS BOOLEAN AS $func$
+-- WARNING: Use at your own risk
+-- FIXME: not considering marc_type, marc_form, marc_bib_level, marc_vr_format, usr_age_lower_bound, usr_age_upper_bound, item_age
+DECLARE
+ item_object asset.copy%ROWTYPE;
+ user_object actor.usr%ROWTYPE;
+ test_rule_object config.circ_matrix_matchpoint%ROWTYPE;
+ result_rule_object config.circ_matrix_matchpoint%ROWTYPE;
+ safe_to_delete BOOLEAN := FALSE;
+ m action.found_circ_matrix_matchpoint;
+ 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;
+
+ INSERT INTO actor.usr (
+ profile,
+ usrname,
+ passwd,
+ ident_type,
+ first_given_name,
+ family_name,
+ home_ou,
+ juvenile
+ ) SELECT
+ COALESCE(test_rule_object.grp, 2),
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ MD5(NOW()::TEXT),
+ 1,
+ 'Ima',
+ 'Test',
+ COALESCE(test_rule_object.user_home_ou, test_rule_object.org_unit),
+ COALESCE(test_rule_object.juvenile_flag, FALSE)
+ ;
+
+ SELECT INTO user_object * FROM actor.usr WHERE id = currval('actor.usr_id_seq');
+
+ INSERT INTO asset.call_number (
+ creator,
+ editor,
+ record,
+ owning_lib,
+ label,
+ label_class
+ ) SELECT
+ 1,
+ 1,
+ -1,
+ COALESCE(test_rule_object.copy_owning_lib,test_rule_object.org_unit),
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ 1
+ ;
+
+ INSERT INTO asset.copy (
+ barcode,
+ circ_lib,
+ creator,
+ call_number,
+ editor,
+ location,
+ loan_duration,
+ fine_level,
+ ref,
+ circ_modifier
+ ) SELECT
+ 'is_circ_rule_safe_to_delete_' || test_matchpoint || '_' || NOW()::text,
+ COALESCE(test_rule_object.copy_circ_lib,test_rule_object.org_unit),
+ 1,
+ currval('asset.call_number_id_seq'),
+ 1,
+ COALESCE(test_rule_object.copy_location,1),
+ 2,
+ 2,
+ COALESCE(test_rule_object.ref_flag,FALSE),
+ test_rule_object.circ_modifier
+ ;
+
+ SELECT INTO item_object * FROM asset.copy WHERE id = currval('asset.copy_id_seq');
+
+ SELECT INTO m * FROM action.find_circ_matrix_matchpoint(
+ test_rule_object.org_unit,
+ item_object,
+ user_object,
+ COALESCE(test_rule_object.is_renewal,FALSE)
+ );
+ 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,
+ m.buildrows
+ ;
+
+ -- 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 temporary changes';
+
+EXCEPTION WHEN OTHERS THEN
+
+ RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
+ RETURN safe_to_delete;
+
+END;
+$func$ LANGUAGE plpgsql;
+
projects / migration-tools.git / blobdiff