-- convenience functions for handling copy_location maps
-
CREATE OR REPLACE FUNCTION migration_tools.handle_shelf (TEXT,TEXT,TEXT,INTEGER) RETURNS VOID AS $$
+ PEFORM 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