3 INSERT INTO config.upgrade_log (version) VALUES ('0573'); -- miker
5 CREATE OR REPLACE FUNCTION search.query_parser_fts (
11 param_locations INT[],
18 ) RETURNS SETOF search.search_result AS $func$
21 current_res search.search_result%ROWTYPE;
22 search_org_list INT[];
30 core_cursor REFCURSOR;
35 deleted_count INT := 0;
36 visible_count INT := 0;
37 excluded_count INT := 0;
41 check_limit := COALESCE( param_check, 1000 );
42 core_limit := COALESCE( param_limit, 25000 );
43 core_offset := COALESCE( param_offset, 0 );
45 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
47 IF param_search_ou > 0 THEN
48 IF param_depth IS NOT NULL THEN
49 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
51 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
53 ELSIF param_search_ou < 0 THEN
54 SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
55 ELSIF param_search_ou = 0 THEN
56 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
59 OPEN core_cursor FOR EXECUTE param_query;
63 FETCH core_cursor INTO core_result;
65 EXIT WHEN total_count >= core_limit;
67 total_count := total_count + 1;
69 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
71 check_count := check_count + 1;
73 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
75 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
76 deleted_count := deleted_count + 1;
81 FROM biblio.record_entry b
82 JOIN config.bib_source s ON (b.source = s.id)
84 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
87 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
88 visible_count := visible_count + 1;
90 current_res.id = core_result.id;
91 current_res.rel = core_result.rel;
95 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
99 current_res.record = core_result.records[1];
101 current_res.record = NULL;
104 RETURN NEXT current_res;
110 FROM asset.call_number cn
111 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
112 JOIN asset.uri uri ON (map.uri = uri.id)
114 AND cn.label = '##URI##'
116 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
117 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
118 AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) )
122 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
123 visible_count := visible_count + 1;
125 current_res.id = core_result.id;
126 current_res.rel = core_result.rel;
130 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
134 current_res.record = core_result.records[1];
136 current_res.record = NULL;
139 RETURN NEXT current_res;
144 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
147 FROM asset.call_number cn
148 JOIN asset.copy cp ON (cp.call_number = cn.id)
151 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
152 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
153 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
157 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
158 excluded_count := excluded_count + 1;
164 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
167 FROM asset.call_number cn
168 JOIN asset.copy cp ON (cp.call_number = cn.id)
171 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
172 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
173 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
177 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
178 excluded_count := excluded_count + 1;
184 IF staff IS NULL OR NOT staff THEN
187 FROM asset.opac_visible_copies
188 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
189 AND record IN ( SELECT * FROM unnest( core_result.records ) )
193 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
194 excluded_count := excluded_count + 1;
201 FROM asset.call_number cn
202 JOIN asset.copy cp ON (cp.call_number = cn.id)
205 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
206 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
212 FROM asset.call_number cn
213 JOIN asset.copy cp ON (cp.call_number = cn.id)
214 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
219 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
220 excluded_count := excluded_count + 1;
228 visible_count := visible_count + 1;
230 current_res.id = core_result.id;
231 current_res.rel = core_result.rel;
235 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
239 current_res.record = core_result.records[1];
241 current_res.record = NULL;
244 RETURN NEXT current_res;
246 IF visible_count % 1000 = 0 THEN
247 -- RAISE NOTICE ' % visible so far ... ', visible_count;
252 current_res.id = NULL;
253 current_res.rel = NULL;
254 current_res.record = NULL;
255 current_res.total = total_count;
256 current_res.checked = check_count;
257 current_res.deleted = deleted_count;
258 current_res.visible = visible_count;
259 current_res.excluded = excluded_count;
263 RETURN NEXT current_res;
266 $func$ LANGUAGE PLPGSQL;
269 -- Evergreen DB patch 0576.fix_maintain_901_quoting.sql
271 -- Fix for bug LP#809540 - fixes crash when inserting or updating
272 -- bib whose tcn_value contains regex metacharacters.
275 -- check whether patch can be applied
276 INSERT INTO config.upgrade_log (version) VALUES ('0576');
278 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
280 use_id_for_tcn BOOLEAN;
282 -- Remove any existing 901 fields before we insert the authoritative one
283 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
285 IF TG_TABLE_SCHEMA = 'biblio' THEN
286 -- Set TCN value to record ID?
287 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
288 WHERE name = 'cat.bib.use_id_for_tcn';
290 IF use_id_for_tcn = 't' THEN
291 NEW.tcn_value := NEW.id;
294 NEW.marc := REGEXP_REPLACE(
296 E'(</(?:[^:]*?:)?record>)',
297 E'<datafield tag="901" ind1=" " ind2=" ">' ||
298 '<subfield code="a">' || REPLACE(evergreen.xml_escape(NEW.tcn_value), E'\\', E'\\\\') || E'</subfield>' ||
299 '<subfield code="b">' || REPLACE(evergreen.xml_escape(NEW.tcn_source), E'\\', E'\\\\') || E'</subfield>' ||
300 '<subfield code="c">' || NEW.id || E'</subfield>' ||
301 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
302 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
303 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
306 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
307 NEW.marc := REGEXP_REPLACE(
309 E'(</(?:[^:]*?:)?record>)',
310 E'<datafield tag="901" ind1=" " ind2=" ">' ||
311 '<subfield code="c">' || NEW.id || E'</subfield>' ||
312 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
315 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
316 NEW.marc := REGEXP_REPLACE(
318 E'(</(?:[^:]*?:)?record>)',
319 E'<datafield tag="901" ind1=" " ind2=" ">' ||
320 '<subfield code="c">' || NEW.id || E'</subfield>' ||
321 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
322 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
323 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
327 NEW.marc := REGEXP_REPLACE(
329 E'(</(?:[^:]*?:)?record>)',
330 E'<datafield tag="901" ind1=" " ind2=" ">' ||
331 '<subfield code="c">' || NEW.id || E'</subfield>' ||
332 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
339 $func$ LANGUAGE PLPGSQL;
342 INSERT INTO config.upgrade_log (version) VALUES ('0580'); -- tsbere via miker
344 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
346 current_aou actor.org_unit%ROWTYPE;
352 seen_ous := ARRAY[NEW.id];
353 IF TG_OP = 'INSERT' OR NEW.parent_ou IS DISTINCT FROM OLD.parent_ou THEN
355 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
356 RETURN NEW; -- No loop. Carry on.
358 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
359 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
362 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
363 seen_ous := seen_ous || current_aou.id;
364 depth_count := depth_count + 1;
365 IF depth_count = 100 THEN
366 RAISE 'OU CHECK TOO DEEP';
374 CREATE TRIGGER actor_org_unit_parent_protect_trigger
375 BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
376 EXECUTE PROCEDURE actor.org_unit_parent_protect ();
379 INSERT INTO config.upgrade_log (version) VALUES ('0581'); -- tsbere via miker
381 INSERT INTO config.global_flag (name, label, enabled)
383 'circ.opac_renewal.use_original_circ_lib',
385 'circ.opac_renewal.use_original_circ_lib',
386 'Circ: Use original circulation library on opac renewal instead of user home library',
394 INSERT INTO config.upgrade_log (version) VALUES ('0582'); -- miker
396 CREATE OR REPLACE VIEW action.all_circulation AS
397 SELECT id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
398 copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
399 circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, due_date,
400 stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
401 max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
402 max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ
403 FROM action.aged_circulation
405 SELECT DISTINCT circ.id,COALESCE(a.post_code,b.post_code) AS usr_post_code, p.home_ou AS usr_home_ou, p.profile AS usr_profile, EXTRACT(YEAR FROM p.dob)::INT AS usr_birth_year,
406 cp.call_number AS copy_call_number, cp.location AS copy_location, cn.owning_lib AS copy_owning_lib, cp.circ_lib AS copy_circ_lib,
407 cn.record AS copy_bib_record, circ.xact_start, circ.xact_finish, circ.target_copy, circ.circ_lib, circ.circ_staff, circ.checkin_staff,
408 circ.checkin_lib, circ.renewal_remaining, circ.due_date, circ.stop_fines_time, circ.checkin_time, circ.create_time, circ.duration,
409 circ.fine_interval, circ.recurring_fine, circ.max_fine, circ.phone_renewal, circ.desk_renewal, circ.opac_renewal, circ.duration_rule,
410 circ.recurring_fine_rule, circ.max_fine_rule, circ.stop_fines, circ.workstation, circ.checkin_workstation, circ.checkin_scan_time,
412 FROM action.circulation circ
413 JOIN asset.copy cp ON (circ.target_copy = cp.id)
414 JOIN asset.call_number cn ON (cp.call_number = cn.id)
415 JOIN actor.usr p ON (circ.usr = p.id)
416 LEFT JOIN actor.usr_address a ON (p.mailing_address = a.id)
417 LEFT JOIN actor.usr_address b ON (p.billing_address = b.id);
420 INSERT INTO config.upgrade_log (version) VALUES ('0587'); -- dbs/berick
422 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
425 use MARC::File::XML (BinaryEncoding => 'UTF-8');
428 use Unicode::Normalize;
430 MARC::Charset->assume_unicode(1);
432 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
433 my $schema = $_TD->{table_schema};
434 my $rec_id = $_TD->{new}{id};
436 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
437 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
438 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
442 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
443 my $ou_cni = 'EVRGRN';
446 if ($schema eq 'serial') {
447 $owner = $_TD->{new}{owning_lib};
449 # are.owner and bre.owner can be null, so fall back to the consortial setting
450 $owner = $_TD->{new}{owner} || 1;
453 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
454 if ($ous_rv->{processed}) {
455 $ou_cni = $ous_rv->{rows}[0]->{value};
456 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
458 # Fall back to the shortname of the OU if there was no OU setting
459 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
460 if ($ous_rv->{processed}) {
461 $ou_cni = $ous_rv->{rows}[0]->{shortname};
465 my ($create, $munge) = (0, 0);
467 my @scns = $record->field('035');
469 foreach my $id_field ('001', '003') {
471 my @controls = $record->field($id_field);
473 if ($id_field eq '001') {
474 $spec_value = $rec_id;
476 $spec_value = $ou_cni;
479 # Create the 001/003 if none exist
480 if (scalar(@controls) == 1) {
481 # Only one field; check to see if we need to munge it
482 unless (grep $_->data() eq $spec_value, @controls) {
486 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
487 foreach my $control (@controls) {
488 $record->delete_field($control);
490 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
495 my $cn = $record->field('001')->data();
496 # Special handling of OCLC numbers, often found in records that lack 003
497 if ($cn =~ /^oc[nm]/) {
498 $cn =~ s/^oc[nm]0*(\d+)/$1/;
499 $record->field('003')->data('OCoLC');
503 # Now, if we need to munge the 001, we will first push the existing 001/003
504 # into the 035; but if the record did not have one (and one only) 001 and 003
505 # to begin with, skip this process
506 if ($munge and not $create) {
508 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
510 # Do not create duplicate 035 fields
511 unless (grep $_->subfield('a') eq $scn, @scns) {
512 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
516 # Set the 001/003 and update the MARC
517 if ($create or $munge) {
518 $record->field('001')->data($rec_id);
519 $record->field('003')->data($ou_cni);
521 my $xml = $record->as_xml_record();
523 $xml =~ s/^<\?xml.+\?\s*>//go;
524 $xml =~ s/>\s+</></go;
525 $xml =~ s/\p{Cc}//go;
527 # Embed a version of OpenILS::Application::AppUtils->entityize()
528 # to avoid having to set PERL5LIB for PostgreSQL as well
530 # If we are going to convert non-ASCII characters to XML entities,
531 # we had better be dealing with a UTF8 string to begin with
532 $xml = decode_utf8($xml);
536 # Convert raw ampersands to entities
537 $xml =~ s/&(?!\S+;)/&/gso;
539 # Convert Unicode characters to entities
540 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
542 $xml =~ s/[\x00-\x1f]//go;
543 $_TD->{new}{marc} = $xml;
549 $func$ LANGUAGE PLPERLU;
551 INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath) VALUES
552 (29, 'identifier', 'scn', oils_i18n_gettext(28, 'System Control Number', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='035']/marc:subfield[@code="a"]$$);
553 INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath) VALUES
554 (30, 'identifier', 'lccn', oils_i18n_gettext(28, 'LC Control Number', 'cmf', 'label'), 'marcxml', $$//marc:datafield[@tag='010']/marc:subfield[@code="a" or @code='z']$$);
556 -- Far from perfect, but much faster than reingesting every record
557 INSERT INTO metabib.identifier_field_entry(source, field, value)
558 SELECT record, 29, value FROM metabib.full_rec WHERE tag = '035' AND subfield = 'a';
559 INSERT INTO metabib.identifier_field_entry(source, field, value)
560 SELECT record, 30, value FROM metabib.full_rec WHERE tag = '010' AND subfield IN ('a', 'z');
563 INSERT INTO config.upgrade_log (version) VALUES ('0588');
565 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
572 parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
573 xml_output := parsed_target; -- if there are no replace rules, just return the input
575 FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
577 xml_output := vandelay.strip_field( parsed_target, curr_field);
579 IF xml_output <> parsed_target AND curr_field ~ E'~' THEN
580 -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
581 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
582 ELSIF curr_field !~ E'~' THEN
583 -- No regexp restriction, add the curr_field
584 xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
587 parsed_target := xml_output; -- in prep for any following loop iterations
593 $_$ LANGUAGE PLPGSQL;
597 INSERT INTO config.upgrade_log (version) VALUES ('0589');
599 DROP TRIGGER IF EXISTS mat_summary_add_tgr ON money.cash_payment;
600 DROP TRIGGER IF EXISTS mat_summary_upd_tgr ON money.cash_payment;
601 DROP TRIGGER IF EXISTS mat_summary_del_tgr ON money.cash_payment;
603 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('cash_payment');
604 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('cash_payment');
605 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.cash_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('cash_payment');
607 DROP TRIGGER IF EXISTS mat_summary_add_tgr ON money.check_payment;
608 DROP TRIGGER IF EXISTS mat_summary_upd_tgr ON money.check_payment;
609 DROP TRIGGER IF EXISTS mat_summary_del_tgr ON money.check_payment;
611 CREATE TRIGGER mat_summary_add_tgr AFTER INSERT ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_add ('check_payment');
612 CREATE TRIGGER mat_summary_upd_tgr AFTER UPDATE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_update ('check_payment');
613 CREATE TRIGGER mat_summary_del_tgr BEFORE DELETE ON money.check_payment FOR EACH ROW EXECUTE PROCEDURE money.materialized_summary_payment_del ('check_payment');
616 --Upgrade script for lp818311.
619 INSERT INTO config.upgrade_log (version) VALUES ('0592');
621 INSERT INTO permission.perm_list ( id, code, description ) VALUES
622 ( 512, 'ACQ_INVOICE_REOPEN', oils_i18n_gettext( 512,
623 'Allows a user to reopen an Acquisitions invoice', 'ppl', 'description' ));
625 INSERT INTO permission.grp_perm_map (grp, perm, depth, grantable)
627 pgt.id, perm.id, aout.depth, TRUE
629 permission.grp_tree pgt,
630 permission.perm_list perm,
631 actor.org_unit_type aout
633 pgt.name = 'Acquisitions Administrator' AND
634 aout.name = 'Consortium' AND
635 perm.code = 'ACQ_INVOICE_REOPEN';