BEGIN
base_staging_table = REPLACE( production_table, '.', '_' );
--RAISE INFO 'In migration_tools.build_specific_base_staging_table(%,%) -> %', migration_schema, production_table, base_staging_table;
- PERFORM migration_tools.exec( $1, 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || base_staging_table || ' ( LIKE ' || production_table || ' INCLUDING DEFAULTS EXCLUDING CONSTRAINTS );' );
+ 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
column_list TEXT := '';
column_count INTEGER := 0;
BEGIN
- create_sql := 'CREATE UNLOGGED TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
+ create_sql := 'CREATE TABLE ' || migration_schema || '.' || parent_table || '_legacy ( ';
FOR columns IN
SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
$$ LANGUAGE PLPERLU STABLE;
DROP TABLE IF EXISTS migration_tools.usps_suffixes;
-CREATE UNLOGGED TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
+CREATE TABLE migration_tools.usps_suffixes ( suffix_from TEXT, suffix_to TEXT );
INSERT INTO migration_tools.usps_suffixes VALUES
('ALLEE','ALY'),
('ALLEY','ALY'),
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) = @_;
return \@texts;
$$ LANGUAGE PLPERLU STABLE;
+CREATE OR REPLACE FUNCTION migration_tools.get_marc_tags_filtered (TEXT, TEXT, TEXT, TEXT, TEXT) RETURNS TEXT[] AS $$
+ my ($marcxml, $tag, $subfield, $delimiter, $match) = @_;
+
+ 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) {
+ if ($field->as_string() =~ qr/$match/) {
+ push @texts, $field->as_string($subfield,$delimiter);
+ }
+ }
+ return \@texts;
+$$ LANGUAGE PLPERLU STABLE;
+
CREATE OR REPLACE FUNCTION migration_tools.find_hold_matrix_matchpoint (INTEGER) RETURNS INTEGER AS $$
SELECT action.find_hold_matrix_matchpoint(
(SELECT pickup_lib FROM action.hold_request WHERE id = $1),
-- convenience functions for handling copy_location maps
-
CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
+ SELECT migration_tools._handle_shelf($1,$2,$3,$4,TRUE);
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION migration_tools._handle_shelf (TEXT,TEXT,TEXT,INTEGER,BOOLEAN) RETURNS VOID AS $$
DECLARE
table_schema ALIAS FOR $1;
table_name ALIAS FOR $2;
org_shortname ALIAS FOR $3;
org_range ALIAS FOR $4;
+ make_assertion ALIAS FOR $5;
proceed BOOLEAN;
org INTEGER;
-- if x_org is on the mapping table, it'll take precedence over the passed org_shortname param
x_org INTEGER;
org_list INTEGER[];
o INTEGER;
+ row_count NUMERIC;
BEGIN
EXECUTE 'SELECT EXISTS (
SELECT 1
|| ' ADD COLUMN x_shelf INTEGER';
IF x_org_found THEN
+ RAISE INFO 'Found x_org column';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
|| ' SET x_shelf = b.id FROM asset_copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
|| ' AND x_shelf IS NULL'
|| ' AND NOT b.deleted';
ELSE
+ RAISE INFO 'Did not find x_org column';
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
|| ' SET x_shelf = b.id FROM asset_copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
END IF;
FOREACH o IN ARRAY org_list LOOP
+ RAISE INFO 'Considering org %', o;
EXECUTE 'UPDATE ' || quote_ident(table_name) || ' a'
|| ' SET x_shelf = b.id FROM asset.copy_location b'
|| ' WHERE BTRIM(UPPER(a.desired_shelf)) = BTRIM(UPPER(b.name))'
|| ' AND b.owning_lib = $1 AND x_shelf IS NULL'
|| ' AND NOT b.deleted'
USING o;
+ GET DIAGNOSTICS row_count = ROW_COUNT;
+ RAISE INFO 'Updated % rows', row_count;
END LOOP;
- EXECUTE 'SELECT migration_tools.assert(
- NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
- ''Cannot find a desired location'',
- ''Found all desired locations''
- );';
+ IF make_assertion THEN
+ EXECUTE 'SELECT migration_tools.assert(
+ NOT EXISTS (SELECT 1 FROM ' || quote_ident(table_name) || ' WHERE desired_shelf <> '''' AND x_shelf IS NULL),
+ ''Cannot find a desired location'',
+ ''Found all desired locations''
+ );';
+ END IF;
END;
$$ LANGUAGE PLPGSQL STRICT VOLATILE;
$function$;
+DROP FUNCTION IF EXISTS migration_tools.munge_sf9_qualifying_match(TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.munge_sf9_qualifying_match(marc_xml TEXT, qualifying_match TEXT, new_9_to_set TEXT, force 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 $qualifying_match = shift;
+my $new_9_to_set = shift;
+my $force = 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 @uris = $marc_xml->field('856');
+return $marc_xml->as_xml_record() unless @uris;
+
+foreach my $field (@uris) {
+ if ($field->as_string() =~ qr/$qualifying_match/) {
+ my $ind1 = $field->indicator('1');
+ if (!defined $ind1) { next; }
+ if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'false') { next; }
+ if ($ind1 ne '1' && $ind1 ne '4' && $force eq 'true') { $field->set_indicator(1,'4'); }
+ my $ind2 = $field->indicator('2');
+ if (!defined $ind2) { next; }
+ if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'false') { next; }
+ if ($ind2 ne '0' && $ind2 ne '1' && $force eq 'true') { $field->set_indicator(2,'0'); }
+ $field->add_subfields( '9' => $new_9_to_set );
+ }
+}
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match(TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match (marc_xml TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators 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 $substring_old_value = shift;
+my $new_value = shift;
+my $fix_indicators = 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 @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) {
+ if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
+ $field->set_indicator(1,'4');
+ }
+ }
+ my $ind2 = $field->indicator('2');
+ if (defined $ind2) {
+ if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
+ $field->set_indicator(2,'0');
+ }
+ }
+ if ($field->as_string('9') =~ qr/$substring_old_value/) {
+ $field->delete_subfield('9');
+ $field->add_subfields( '9' => $new_value );
+ }
+ $marc_xml->delete_field($field); # -- we're going to dedup and add them back
+}
+
+my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
+$marc_xml->insert_fields_ordered( values( %hash ) );
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
+DROP FUNCTION IF EXISTS migration_tools.owner_change_sf9_substring_match2(TEXT,TEXT,TEXT,TEXT,TEXT);
+CREATE OR REPLACE FUNCTION migration_tools.owner_change_sf9_substring_match2 (marc_xml TEXT, qualifying_match TEXT, substring_old_value TEXT, new_value TEXT, fix_indicators 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 $qualifying_match = shift;
+my $substring_old_value = shift;
+my $new_value = shift;
+my $fix_indicators = 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 @unqualified_uris = $marc_xml->field('856');
+my @uris = ();
+foreach my $field (@unqualified_uris) {
+ if ($field->as_string() =~ qr/$qualifying_match/) {
+ push @uris, $field;
+ }
+}
+return $marc_xml->as_xml_record() unless @uris;
+
+foreach my $field (@uris) {
+ my $ind1 = $field->indicator('1');
+ if (defined $ind1) {
+ if ($ind1 ne '1' && $ind1 ne '4' && $fix_indicators eq 'true') {
+ $field->set_indicator(1,'4');
+ }
+ }
+ my $ind2 = $field->indicator('2');
+ if (defined $ind2) {
+ if ($ind2 ne '0' && $ind2 ne '1' && $fix_indicators eq 'true') {
+ $field->set_indicator(2,'0');
+ }
+ }
+ if ($field->as_string('9') =~ qr/$substring_old_value/) {
+ $field->delete_subfield('9');
+ $field->add_subfields( '9' => $new_value );
+ }
+ $marc_xml->delete_field($field); # -- we're going to dedup and add them back
+}
+
+my %hash = (map { ($_->as_usmarc => $_) } @uris); # -- courtesy of an old Mike Rylander post :-)
+$marc_xml->insert_fields_ordered( values( %hash ) );
+
+return $marc_xml->as_xml_record();
+
+$function$;
+
-- 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)
RETURN TRUE;
END
$function$;
+
+
+-- convenience function for handling item barcode collisions in asset_copy_legacy
+
+CREATE OR REPLACE FUNCTION migration_tools.handle_asset_barcode_collisions(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 || '_incumbent_collision_' || id || '_' || 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;
+
+-- 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_collisions(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 || '_incumbent_barcode_collision_' || id || '_' || 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 || '_incumbent_usrname_collision_' || id || '_' || 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;
+
+-- 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;
+ result_matchpoint INTEGER;
+ -- ( success BOOL, matchpoint config.circ_matrix_matchpoint, buildrows INT[] )
+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).id,
+ 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;
+
+ RAISE EXCEPTION 'rollback the item and user tables';
+
+EXCEPTION WHEN OTHERS THEN
+
+ RAISE INFO 'inside exception block: %, %', SQLSTATE, SQLERRM;
+ RETURN safe_to_delete;
+
+END;
+$func$ LANGUAGE plpgsql;
+