1 --Upgrade Script for 2.5.2 to 2.5.3
2 \set eg_version '''2.5.3'''
4 \qecho *** This ALTER TABLE might fail depending on your DB vintage. ***
5 \qecho *** It should be harmless. ***
6 ALTER TABLE action.aged_hold_request ADD COLUMN behind_desk BOOLEAN;
9 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.5.3', :eg_version);
11 -- this file is a duplicate of 0851, moved up for better backport clarity
13 -- check whether patch can be applied
14 SELECT evergreen.upgrade_deps_block_check('0862', :eg_version);
16 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
19 use MARC::File::XML (BinaryEncoding => 'UTF-8');
22 use Unicode::Normalize;
24 MARC::Charset->assume_unicode(1);
26 my $schema = $_TD->{table_schema};
27 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
29 my @old901s = $marc->field('901');
30 $marc->delete_fields(@old901s);
32 if ($schema eq 'biblio') {
33 my $tcn_value = $_TD->{new}{tcn_value};
35 # Set TCN value to record ID?
36 my $id_as_tcn = spi_exec_query("
38 FROM config.global_flag
39 WHERE name = 'cat.bib.use_id_for_tcn'
41 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
42 $tcn_value = $_TD->{new}{id};
43 $_TD->{new}{tcn_value} = $tcn_value;
46 my $new_901 = MARC::Field->new("901", " ", " ",
48 "b" => $_TD->{new}{tcn_source},
49 "c" => $_TD->{new}{id},
53 if ($_TD->{new}{owner}) {
54 $new_901->add_subfields("o" => $_TD->{new}{owner});
57 if ($_TD->{new}{share_depth}) {
58 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
61 $marc->append_fields($new_901);
62 } elsif ($schema eq 'authority') {
63 my $new_901 = MARC::Field->new("901", " ", " ",
64 "c" => $_TD->{new}{id},
67 $marc->append_fields($new_901);
68 } elsif ($schema eq 'serial') {
69 my $new_901 = MARC::Field->new("901", " ", " ",
70 "c" => $_TD->{new}{id},
72 "o" => $_TD->{new}{owning_lib},
75 if ($_TD->{new}{record}) {
76 $new_901->add_subfields("r" => $_TD->{new}{record});
79 $marc->append_fields($new_901);
81 my $new_901 = MARC::Field->new("901", " ", " ",
82 "c" => $_TD->{new}{id},
85 $marc->append_fields($new_901);
88 my $xml = $marc->as_xml_record();
90 $xml =~ s/^<\?xml.+\?\s*>//go;
91 $xml =~ s/>\s+</></go;
94 # Embed a version of OpenILS::Application::AppUtils->entityize()
95 # to avoid having to set PERL5LIB for PostgreSQL as well
99 # Convert raw ampersands to entities
100 $xml =~ s/&(?!\S+;)/&/gso;
102 # Convert Unicode characters to entities
103 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
105 $xml =~ s/[\x00-\x1f]//go;
106 $_TD->{new}{marc} = $xml;
109 $func$ LANGUAGE PLPERLU;
111 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
114 use MARC::File::XML (BinaryEncoding => 'UTF-8');
117 use Unicode::Normalize;
119 MARC::Charset->assume_unicode(1);
121 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
122 my $schema = $_TD->{table_schema};
123 my $rec_id = $_TD->{new}{id};
125 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
126 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
127 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
131 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
132 my $ou_cni = 'EVRGRN';
135 if ($schema eq 'serial') {
136 $owner = $_TD->{new}{owning_lib};
138 # are.owner and bre.owner can be null, so fall back to the consortial setting
139 $owner = $_TD->{new}{owner} || 1;
142 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
143 if ($ous_rv->{processed}) {
144 $ou_cni = $ous_rv->{rows}[0]->{value};
145 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
147 # Fall back to the shortname of the OU if there was no OU setting
148 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
149 if ($ous_rv->{processed}) {
150 $ou_cni = $ous_rv->{rows}[0]->{shortname};
154 my ($create, $munge) = (0, 0);
156 my @scns = $record->field('035');
158 foreach my $id_field ('001', '003') {
160 my @controls = $record->field($id_field);
162 if ($id_field eq '001') {
163 $spec_value = $rec_id;
165 $spec_value = $ou_cni;
168 # Create the 001/003 if none exist
169 if (scalar(@controls) == 1) {
170 # Only one field; check to see if we need to munge it
171 unless (grep $_->data() eq $spec_value, @controls) {
175 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
176 foreach my $control (@controls) {
177 $record->delete_field($control);
179 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
184 my $cn = $record->field('001')->data();
185 # Special handling of OCLC numbers, often found in records that lack 003
186 if ($cn =~ /^o(c[nm]|n)\d/) {
187 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
188 $record->field('003')->data('OCoLC');
192 # Now, if we need to munge the 001, we will first push the existing 001/003
193 # into the 035; but if the record did not have one (and one only) 001 and 003
194 # to begin with, skip this process
195 if ($munge and not $create) {
197 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
199 # Do not create duplicate 035 fields
200 unless (grep $_->subfield('a') eq $scn, @scns) {
201 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
205 # Set the 001/003 and update the MARC
206 if ($create or $munge) {
207 $record->field('001')->data($rec_id);
208 $record->field('003')->data($ou_cni);
210 my $xml = $record->as_xml_record();
212 $xml =~ s/^<\?xml.+\?\s*>//go;
213 $xml =~ s/>\s+</></go;
214 $xml =~ s/\p{Cc}//go;
216 # Embed a version of OpenILS::Application::AppUtils->entityize()
217 # to avoid having to set PERL5LIB for PostgreSQL as well
221 # Convert raw ampersands to entities
222 $xml =~ s/&(?!\S+;)/&/gso;
224 # Convert Unicode characters to entities
225 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
227 $xml =~ s/[\x00-\x1f]//go;
228 $_TD->{new}{marc} = $xml;
234 $func$ LANGUAGE PLPERLU;
236 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
239 use Unicode::Normalize;
245 # Apply NACO normalization to input string; based on
246 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
248 # Note that unlike a strict reading of the NACO normalization rules,
249 # output is returned as lowercase instead of uppercase for compatibility
250 # with previous versions of the Evergreen naco_normalize routine.
252 # Convert to upper-case first; even though final output will be lowercase, doing this will
253 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
254 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
257 # remove non-filing strings
258 $str =~ s/\x{0098}.*?\x{009C}//g;
262 # additional substitutions - 3.6.
263 $str =~ s/\x{00C6}/AE/g;
264 $str =~ s/\x{00DE}/TH/g;
265 $str =~ s/\x{0152}/OE/g;
266 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}]['/DDOLl/d;
268 # transformations based on Unicode category codes
269 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
271 if ($sf && $sf =~ /^a/o) {
272 my $commapos = index($str, ',');
273 if ($commapos > -1) {
274 if ($commapos != length($str) - 1) {
275 $str =~ s/,/\x07/; # preserve first comma
280 # since we've stripped out the control characters, we can now
281 # use a few as placeholders temporarily
282 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
283 $str =~ s/[\p{Pc}\p{Pd}\p{Pe}\p{Pf}\p{Pi}\p{Po}\p{Ps}\p{Sk}\p{Sm}\p{So}\p{Zl}\p{Zp}\p{Zs}]/ /g;
284 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
287 $str =~ tr/\x{0660}-\x{0669}\x{06F0}-\x{06F9}\x{07C0}-\x{07C9}\x{0966}-\x{096F}\x{09E6}-\x{09EF}\x{0A66}-\x{0A6F}\x{0AE6}-\x{0AEF}\x{0B66}-\x{0B6F}\x{0BE6}-\x{0BEF}\x{0C66}-\x{0C6F}\x{0CE6}-\x{0CEF}\x{0D66}-\x{0D6F}\x{0E50}-\x{0E59}\x{0ED0}-\x{0ED9}\x{0F20}-\x{0F29}\x{1040}-\x{1049}\x{1090}-\x{1099}\x{17E0}-\x{17E9}\x{1810}-\x{1819}\x{1946}-\x{194F}\x{19D0}-\x{19D9}\x{1A80}-\x{1A89}\x{1A90}-\x{1A99}\x{1B50}-\x{1B59}\x{1BB0}-\x{1BB9}\x{1C40}-\x{1C49}\x{1C50}-\x{1C59}\x{A620}-\x{A629}\x{A8D0}-\x{A8D9}\x{A900}-\x{A909}\x{A9D0}-\x{A9D9}\x{AA50}-\x{AA59}\x{ABF0}-\x{ABF9}\x{FF10}-\x{FF19}/0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9/;
289 # intentionally skipping step 8 of the NACO algorithm; if the string
290 # gets normalized away, that's fine.
292 # leading and trailing spaces
298 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
300 -- Currently, the only difference from naco_normalize is that search_normalize
301 -- turns apostrophes into spaces, while naco_normalize collapses them.
302 CREATE OR REPLACE FUNCTION public.search_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
305 use Unicode::Normalize;
311 # Apply NACO normalization to input string; based on
312 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
314 # Note that unlike a strict reading of the NACO normalization rules,
315 # output is returned as lowercase instead of uppercase for compatibility
316 # with previous versions of the Evergreen naco_normalize routine.
318 # Convert to upper-case first; even though final output will be lowercase, doing this will
319 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
320 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
323 # remove non-filing strings
324 $str =~ s/\x{0098}.*?\x{009C}//g;
328 # additional substitutions - 3.6.
329 $str =~ s/\x{00C6}/AE/g;
330 $str =~ s/\x{00DE}/TH/g;
331 $str =~ s/\x{0152}/OE/g;
332 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}][/DDOLl/d;
334 # transformations based on Unicode category codes
335 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
337 if ($sf && $sf =~ /^a/o) {
338 my $commapos = index($str, ',');
339 if ($commapos > -1) {
340 if ($commapos != length($str) - 1) {
341 $str =~ s/,/\x07/; # preserve first comma
346 # since we've stripped out the control characters, we can now
347 # use a few as placeholders temporarily
348 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
349 $str =~ s/[\p{Pc}\p{Pd}\p{Pe}\p{Pf}\p{Pi}\p{Po}\p{Ps}\p{Sk}\p{Sm}\p{So}\p{Zl}\p{Zp}\p{Zs}]/ /g;
350 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
353 $str =~ tr/\x{0660}-\x{0669}\x{06F0}-\x{06F9}\x{07C0}-\x{07C9}\x{0966}-\x{096F}\x{09E6}-\x{09EF}\x{0A66}-\x{0A6F}\x{0AE6}-\x{0AEF}\x{0B66}-\x{0B6F}\x{0BE6}-\x{0BEF}\x{0C66}-\x{0C6F}\x{0CE6}-\x{0CEF}\x{0D66}-\x{0D6F}\x{0E50}-\x{0E59}\x{0ED0}-\x{0ED9}\x{0F20}-\x{0F29}\x{1040}-\x{1049}\x{1090}-\x{1099}\x{17E0}-\x{17E9}\x{1810}-\x{1819}\x{1946}-\x{194F}\x{19D0}-\x{19D9}\x{1A80}-\x{1A89}\x{1A90}-\x{1A99}\x{1B50}-\x{1B59}\x{1BB0}-\x{1BB9}\x{1C40}-\x{1C49}\x{1C50}-\x{1C59}\x{A620}-\x{A629}\x{A8D0}-\x{A8D9}\x{A900}-\x{A909}\x{A9D0}-\x{A9D9}\x{AA50}-\x{AA59}\x{ABF0}-\x{ABF9}\x{FF10}-\x{FF19}/0-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-90-9/;
355 # intentionally skipping step 8 of the NACO algorithm; if the string
356 # gets normalized away, that's fine.
358 # leading and trailing spaces
364 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
366 -- add missing behind_desk column
369 SELECT evergreen.upgrade_deps_block_check('0868', :eg_version);
371 CREATE OR REPLACE VIEW action.all_hold_request AS
373 COALESCE(a.post_code, b.post_code) AS usr_post_code,
374 p.home_ou AS usr_home_ou,
375 p.profile AS usr_profile,
376 EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
377 CAST(ahr.requestor <> ahr.usr AS BOOLEAN) AS staff_placed,
381 ahr.fulfillment_time,
391 ahr.fulfillment_staff,
398 ahr.holdable_formats,
400 WHEN ahr.phone_notify IS NULL THEN FALSE
401 WHEN ahr.phone_notify = '' THEN FALSE
406 WHEN ahr.sms_notify IS NULL THEN FALSE
407 WHEN ahr.sms_notify = '' THEN FALSE
415 ahr.shelf_expire_time,
416 ahr.current_shelf_lib,
418 FROM action.hold_request ahr
419 JOIN actor.usr p ON (ahr.usr = p.id)
420 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
421 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id)
461 FROM action.aged_hold_request;
465 CREATE OR REPLACE FUNCTION action.age_hold_on_delete () RETURNS TRIGGER AS $$
468 -- Archive a copy of the old row to action.aged_hold_request
470 INSERT INTO action.aged_hold_request
546 FROM action.all_hold_request WHERE id = OLD.id;
550 $$ LANGUAGE 'plpgsql';