1 --Upgrade Script for 2.5.3 to 2.6.0
2 \set eg_version '''2.6.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.0', :eg_version);
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0851', :eg_version);
9 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
12 use MARC::File::XML (BinaryEncoding => 'UTF-8');
15 use Unicode::Normalize;
17 MARC::Charset->assume_unicode(1);
19 my $schema = $_TD->{table_schema};
20 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
22 my @old901s = $marc->field('901');
23 $marc->delete_fields(@old901s);
25 if ($schema eq 'biblio') {
26 my $tcn_value = $_TD->{new}{tcn_value};
28 # Set TCN value to record ID?
29 my $id_as_tcn = spi_exec_query("
31 FROM config.global_flag
32 WHERE name = 'cat.bib.use_id_for_tcn'
34 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
35 $tcn_value = $_TD->{new}{id};
36 $_TD->{new}{tcn_value} = $tcn_value;
39 my $new_901 = MARC::Field->new("901", " ", " ",
41 "b" => $_TD->{new}{tcn_source},
42 "c" => $_TD->{new}{id},
46 if ($_TD->{new}{owner}) {
47 $new_901->add_subfields("o" => $_TD->{new}{owner});
50 if ($_TD->{new}{share_depth}) {
51 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
54 $marc->append_fields($new_901);
55 } elsif ($schema eq 'authority') {
56 my $new_901 = MARC::Field->new("901", " ", " ",
57 "c" => $_TD->{new}{id},
60 $marc->append_fields($new_901);
61 } elsif ($schema eq 'serial') {
62 my $new_901 = MARC::Field->new("901", " ", " ",
63 "c" => $_TD->{new}{id},
65 "o" => $_TD->{new}{owning_lib},
68 if ($_TD->{new}{record}) {
69 $new_901->add_subfields("r" => $_TD->{new}{record});
72 $marc->append_fields($new_901);
74 my $new_901 = MARC::Field->new("901", " ", " ",
75 "c" => $_TD->{new}{id},
78 $marc->append_fields($new_901);
81 my $xml = $marc->as_xml_record();
83 $xml =~ s/^<\?xml.+\?\s*>//go;
84 $xml =~ s/>\s+</></go;
87 # Embed a version of OpenILS::Application::AppUtils->entityize()
88 # to avoid having to set PERL5LIB for PostgreSQL as well
92 # Convert raw ampersands to entities
93 $xml =~ s/&(?!\S+;)/&/gso;
95 # Convert Unicode characters to entities
96 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
98 $xml =~ s/[\x00-\x1f]//go;
99 $_TD->{new}{marc} = $xml;
102 $func$ LANGUAGE PLPERLU;
104 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
107 use MARC::File::XML (BinaryEncoding => 'UTF-8');
110 use Unicode::Normalize;
112 MARC::Charset->assume_unicode(1);
114 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
115 my $schema = $_TD->{table_schema};
116 my $rec_id = $_TD->{new}{id};
118 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
119 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
120 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
124 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
125 my $ou_cni = 'EVRGRN';
128 if ($schema eq 'serial') {
129 $owner = $_TD->{new}{owning_lib};
131 # are.owner and bre.owner can be null, so fall back to the consortial setting
132 $owner = $_TD->{new}{owner} || 1;
135 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
136 if ($ous_rv->{processed}) {
137 $ou_cni = $ous_rv->{rows}[0]->{value};
138 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
140 # Fall back to the shortname of the OU if there was no OU setting
141 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
142 if ($ous_rv->{processed}) {
143 $ou_cni = $ous_rv->{rows}[0]->{shortname};
147 my ($create, $munge) = (0, 0);
149 my @scns = $record->field('035');
151 foreach my $id_field ('001', '003') {
153 my @controls = $record->field($id_field);
155 if ($id_field eq '001') {
156 $spec_value = $rec_id;
158 $spec_value = $ou_cni;
161 # Create the 001/003 if none exist
162 if (scalar(@controls) == 1) {
163 # Only one field; check to see if we need to munge it
164 unless (grep $_->data() eq $spec_value, @controls) {
168 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
169 foreach my $control (@controls) {
170 $record->delete_field($control);
172 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
177 my $cn = $record->field('001')->data();
178 # Special handling of OCLC numbers, often found in records that lack 003
179 if ($cn =~ /^o(c[nm]|n)\d/) {
180 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
181 $record->field('003')->data('OCoLC');
185 # Now, if we need to munge the 001, we will first push the existing 001/003
186 # into the 035; but if the record did not have one (and one only) 001 and 003
187 # to begin with, skip this process
188 if ($munge and not $create) {
190 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
192 # Do not create duplicate 035 fields
193 unless (grep $_->subfield('a') eq $scn, @scns) {
194 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
198 # Set the 001/003 and update the MARC
199 if ($create or $munge) {
200 $record->field('001')->data($rec_id);
201 $record->field('003')->data($ou_cni);
203 my $xml = $record->as_xml_record();
205 $xml =~ s/^<\?xml.+\?\s*>//go;
206 $xml =~ s/>\s+</></go;
207 $xml =~ s/\p{Cc}//go;
209 # Embed a version of OpenILS::Application::AppUtils->entityize()
210 # to avoid having to set PERL5LIB for PostgreSQL as well
214 # Convert raw ampersands to entities
215 $xml =~ s/&(?!\S+;)/&/gso;
217 # Convert Unicode characters to entities
218 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
220 $xml =~ s/[\x00-\x1f]//go;
221 $_TD->{new}{marc} = $xml;
227 $func$ LANGUAGE PLPERLU;
229 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
232 use Unicode::Normalize;
238 # Apply NACO normalization to input string; based on
239 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
241 # Note that unlike a strict reading of the NACO normalization rules,
242 # output is returned as lowercase instead of uppercase for compatibility
243 # with previous versions of the Evergreen naco_normalize routine.
245 # Convert to upper-case first; even though final output will be lowercase, doing this will
246 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
247 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
250 # remove non-filing strings
251 $str =~ s/\x{0098}.*?\x{009C}//g;
255 # additional substitutions - 3.6.
256 $str =~ s/\x{00C6}/AE/g;
257 $str =~ s/\x{00DE}/TH/g;
258 $str =~ s/\x{0152}/OE/g;
259 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}]['/DDOLl/d;
261 # transformations based on Unicode category codes
262 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
264 if ($sf && $sf =~ /^a/o) {
265 my $commapos = index($str, ',');
266 if ($commapos > -1) {
267 if ($commapos != length($str) - 1) {
268 $str =~ s/,/\x07/; # preserve first comma
273 # since we've stripped out the control characters, we can now
274 # use a few as placeholders temporarily
275 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
276 $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;
277 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
280 $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/;
282 # intentionally skipping step 8 of the NACO algorithm; if the string
283 # gets normalized away, that's fine.
285 # leading and trailing spaces
291 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
293 -- Currently, the only difference from naco_normalize is that search_normalize
294 -- turns apostrophes into spaces, while naco_normalize collapses them.
295 CREATE OR REPLACE FUNCTION public.search_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
298 use Unicode::Normalize;
304 # Apply NACO normalization to input string; based on
305 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
307 # Note that unlike a strict reading of the NACO normalization rules,
308 # output is returned as lowercase instead of uppercase for compatibility
309 # with previous versions of the Evergreen naco_normalize routine.
311 # Convert to upper-case first; even though final output will be lowercase, doing this will
312 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
313 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
316 # remove non-filing strings
317 $str =~ s/\x{0098}.*?\x{009C}//g;
321 # additional substitutions - 3.6.
322 $str =~ s/\x{00C6}/AE/g;
323 $str =~ s/\x{00DE}/TH/g;
324 $str =~ s/\x{0152}/OE/g;
325 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}][/DDOLl/d;
327 # transformations based on Unicode category codes
328 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
330 if ($sf && $sf =~ /^a/o) {
331 my $commapos = index($str, ',');
332 if ($commapos > -1) {
333 if ($commapos != length($str) - 1) {
334 $str =~ s/,/\x07/; # preserve first comma
339 # since we've stripped out the control characters, we can now
340 # use a few as placeholders temporarily
341 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
342 $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;
343 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
346 $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/;
348 # intentionally skipping step 8 of the NACO algorithm; if the string
349 # gets normalized away, that's fine.
351 # leading and trailing spaces
357 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
359 -- Evergreen DB patch XXXX.data.prefer_external_url_OUS.sql
361 -- FIXME: insert description of change, if needed
364 -- check whether patch can be applied
365 SELECT evergreen.upgrade_deps_block_check('0853', :eg_version);
367 INSERT into config.org_unit_setting_type
368 ( name, grp, label, description, datatype, fm_class ) VALUES
369 ( 'lib.prefer_external_url', 'lib',
370 'Use external "library information URL" in copy table, if available',
371 'If set to true, the library name in the copy details section will link to the URL associated with the "Library information URL" library setting rather than the library information page generated by Evergreen.',
376 SELECT evergreen.upgrade_deps_block_check('0854', :eg_version);
378 INSERT INTO permission.perm_list ( id, code, description ) VALUES (
380 'UPDATE_ORG_UNIT_SETTING.circ.min_item_price',
383 'UPDATE_ORG_UNIT_SETTING.circ.min_item_price',
389 'UPDATE_ORG_UNIT_SETTING.circ.max_item_price',
392 'UPDATE_ORG_UNIT_SETTING.circ.max_item_price',
398 INSERT into config.org_unit_setting_type
399 ( name, grp, label, description, datatype, fm_class )
401 'circ.min_item_price',
404 'circ.min_item_price',
405 'Minimum Item Price',
408 'circ.min_item_price',
409 'When charging for lost items, charge this amount as a minimum.',
410 'coust', 'description'),
414 'circ.max_item_price',
417 'circ.max_item_price',
418 'Maximum Item Price',
421 'circ.max_item_price',
422 'When charging for lost items, limit the charge to this as a maximum.',
423 'coust', 'description'),
428 -- Compiled list of all changed functions and views where we went from:
429 -- array_accum() to array_agg()
430 -- array_to_string(array_agg()) to string_agg()
433 SELECT evergreen.upgrade_deps_block_check('0855', :eg_version);
435 -- from 000.functions.general.sql
438 -- from 002.functions.config.sql
440 CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
441 SELECT regexp_replace(string_agg(output,' '),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT);
445 -- from 011.schema.authority.sql
447 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
448 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
451 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
452 SELECT ARRAY_AGG(y) from (
453 SELECT unnest(ARRAY_CAT(
455 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
457 FROM authority.browse_axis_authority_field_map a
461 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
462 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
465 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
466 SELECT ARRAY_AGG(y) from (
467 SELECT unnest(ARRAY_CAT(
468 ARRAY[a.authority_field],
469 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
471 FROM authority.control_set_bib_field a
475 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
476 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
479 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
480 SELECT ARRAY_AGG(y) from (
481 SELECT unnest(ARRAY_CAT(
483 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
485 FROM authority.control_set_authority_field a
490 -- from 012.schema.vandelay.sql
492 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
494 transformed_xml TEXT;
497 xfrm config.xml_transform%ROWTYPE;
499 new_attrs HSTORE := ''::HSTORE;
500 attr_def config.record_attr_definition%ROWTYPE;
503 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
505 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
506 SELECT STRING_AGG(x.value, COALESCE(attr_def.joiner,' ')) INTO attr_value
507 FROM vandelay.flatten_marc(xml) AS x
508 WHERE x.tag LIKE attr_def.tag
510 WHEN attr_def.sf_list IS NOT NULL
511 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
518 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
519 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
521 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
523 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
525 -- See if we can skip the XSLT ... it's expensive
526 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
527 -- Can't skip the transform
528 IF xfrm.xslt <> '---' THEN
529 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
531 transformed_xml := xml;
534 prev_xfrm := xfrm.name;
537 IF xfrm.name IS NULL THEN
538 -- just grab the marcxml (empty) transform
539 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
540 prev_xfrm := xfrm.name;
543 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
545 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
546 SELECT m.value::TEXT INTO attr_value
547 FROM vandelay.marc21_physical_characteristics(xml) v
548 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
549 WHERE v.subfield = attr_def.phys_char_sf
550 LIMIT 1; -- Just in case ...
554 -- apply index normalizers to attr_value
556 SELECT n.func AS func,
557 n.param_count AS param_count,
559 FROM config.index_normalizer n
560 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
561 WHERE attr = attr_def.name
563 EXECUTE 'SELECT ' || normalizer.func || '(' ||
564 quote_nullable( attr_value ) ||
566 WHEN normalizer.param_count > 0
567 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
574 -- Add the new value to the hstore
575 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
581 $_$ LANGUAGE PLPGSQL;
583 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
584 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_AGG(name) FROM config.record_attr_definition));
587 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
588 match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER
589 ) RETURNS SETOF vandelay.match_set_test_result AS $$
600 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
601 svf_rstore := vandelay.extract_rec_attrs(record_xml);
603 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
604 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
606 -- generate the where clause and return that directly (into wq), and as
607 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
608 wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore);
610 query_ := 'SELECT DISTINCT(record), ';
612 -- qrows table is for the quality bits we add to the SELECT clause
614 'COALESCE(n' || q::TEXT || '.quality, 0)', ' + '
615 ) INTO coal FROM _vandelay_tmp_qrows;
617 -- our query string so far is the SELECT clause and the inital FROM.
618 -- no JOINs yet nor the WHERE clause
619 query_ := query_ || coal || ' AS quality ' || E'\n';
621 -- jrows table is for the joins we must make (and the real text conditions)
622 SELECT STRING_AGG(j, E'\n') INTO joins
623 FROM _vandelay_tmp_jrows;
625 -- add those joins and the where clause to our query.
626 query_ := query_ || joins || E'\n';
628 -- join the record bucket
629 IF bucket_id IS NOT NULL THEN
630 query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' ||
631 'brebi ON (brebi.target_biblio_record_entry = record ' ||
632 'AND brebi.bucket = ' || bucket_id || E')\n';
635 query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted';
637 -- this will return rows of record,quality
638 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
642 DROP TABLE _vandelay_tmp_qrows;
643 DROP TABLE _vandelay_tmp_jrows;
648 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
650 ) RETURNS HSTORE AS $func$
654 ARRAY_AGG(tag || (COALESCE(subfield, ''))),
658 SELECT tag, subfield, ARRAY_AGG(value)::TEXT AS value
661 CASE WHEN tag = '020' THEN -- caseless -- isbn
662 LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
663 WHEN tag = '022' THEN -- caseless -- issn
664 LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
665 WHEN tag = '024' THEN -- caseless -- upc (other)
670 FROM vandelay.flatten_marc(record_xml)) x
671 GROUP BY tag, subfield ORDER BY tag, subfield
675 $func$ LANGUAGE PLPGSQL;
677 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
678 node vandelay.match_set_point,
686 child vandelay.match_set_point;
688 SELECT ARRAY_AGG(id) INTO children FROM vandelay.match_set_point
689 WHERE parent = node.id;
691 IF ARRAY_LENGTH(children, 1) > 0 THEN
692 this_op := vandelay._get_expr_render_one(node);
695 WHILE children[i] IS NOT NULL LOOP
696 SELECT * INTO child FROM vandelay.match_set_point
697 WHERE id = children[i];
699 q := q || ' ' || this_op || ' ';
702 q := q || vandelay.get_expr_from_match_set_point(child, tags_rstore);
706 ELSIF node.bool_op IS NULL THEN
707 PERFORM vandelay._get_expr_push_qrow(node);
708 PERFORM vandelay._get_expr_push_jrow(node, tags_rstore);
709 RETURN vandelay._get_expr_render_one(node);
717 -- from 030.schema.metabib.sql
719 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
726 uri_owner_list TEXT[];
734 -- Clear any URI mappings and call numbers for this bib.
735 -- This leads to acn / auricnm inflation, but also enables
736 -- old acn/auricnm's to go away and for bibs to be deleted.
737 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
738 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
739 DELETE FROM asset.call_number WHERE id = uri_cn_id;
742 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
743 IF ARRAY_UPPER(uris,1) > 0 THEN
744 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
745 -- First we pull info out of the 856
748 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
749 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
750 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
752 IF uri_label IS NULL THEN
753 uri_label := uri_href;
755 CONTINUE WHEN uri_href IS NULL;
757 -- Get the distinct list of libraries wanting to use
759 DISTINCT REGEXP_REPLACE(
761 $re$^.*?\((\w+)\).*$$re$,
764 ) INTO uri_owner_list
767 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
772 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
774 -- look for a matching uri
775 IF uri_use IS NULL THEN
776 SELECT id INTO uri_id
778 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
780 IF NOT FOUND THEN -- create one
781 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
782 SELECT id INTO uri_id
784 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
787 SELECT id INTO uri_id
789 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
791 IF NOT FOUND THEN -- create one
792 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
793 SELECT id INTO uri_id
795 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
799 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
800 uri_owner := uri_owner_list[j];
802 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
803 CONTINUE WHEN NOT FOUND;
805 -- we need a call number to link through
806 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
808 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
809 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
810 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted;
813 -- now, link them if they're not already
814 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
816 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
828 $func$ LANGUAGE PLPGSQL;
830 -- from 100.circ_matrix.sql
832 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
834 user_object actor.usr%ROWTYPE;
835 new_sp_row actor.usr_standing_penalty%ROWTYPE;
836 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
837 collections_fines permission.grp_penalty_threshold%ROWTYPE;
838 max_fines permission.grp_penalty_threshold%ROWTYPE;
839 max_overdue permission.grp_penalty_threshold%ROWTYPE;
840 max_items_out permission.grp_penalty_threshold%ROWTYPE;
841 max_lost permission.grp_penalty_threshold%ROWTYPE;
842 max_longoverdue permission.grp_penalty_threshold%ROWTYPE;
847 items_longoverdue INT;
848 context_org_list INT[];
849 current_fines NUMERIC(8,2) := 0.0;
850 tmp_fines NUMERIC(8,2);
853 tmp_org actor.org_unit%ROWTYPE;
854 tmp_penalty config.standing_penalty%ROWTYPE;
857 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
860 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
862 -- Fail if the user has a high fine balance
864 tmp_grp := user_object.profile;
866 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
868 IF max_fines.threshold IS NULL THEN
869 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
874 IF tmp_grp IS NULL THEN
879 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
883 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
887 IF max_fines.threshold IS NOT NULL THEN
891 FROM actor.usr_standing_penalty
892 WHERE usr = match_user
893 AND org_unit = max_fines.org_unit
894 AND (stop_date IS NULL or stop_date > NOW())
895 AND standing_penalty = 1;
897 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
899 SELECT SUM(f.balance_owed) INTO current_fines
900 FROM money.materialized_billable_xact_summary f
903 FROM booking.reservation r
904 WHERE r.usr = match_user
905 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
906 AND xact_finish IS NULL
910 WHERE g.usr = match_user
911 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
912 AND xact_finish IS NULL
915 FROM action.circulation circ
916 WHERE circ.usr = match_user
917 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
918 AND xact_finish IS NULL ) l USING (id);
920 IF current_fines >= max_fines.threshold THEN
921 new_sp_row.usr := match_user;
922 new_sp_row.org_unit := max_fines.org_unit;
923 new_sp_row.standing_penalty := 1;
924 RETURN NEXT new_sp_row;
928 -- Start over for max overdue
929 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
931 -- Fail if the user has too many overdue items
933 tmp_grp := user_object.profile;
936 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
938 IF max_overdue.threshold IS NULL THEN
939 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
944 IF tmp_grp IS NULL THEN
949 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
953 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
957 IF max_overdue.threshold IS NOT NULL THEN
961 FROM actor.usr_standing_penalty
962 WHERE usr = match_user
963 AND org_unit = max_overdue.org_unit
964 AND (stop_date IS NULL or stop_date > NOW())
965 AND standing_penalty = 2;
967 SELECT INTO items_overdue COUNT(*)
968 FROM action.circulation circ
969 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
970 WHERE circ.usr = match_user
971 AND circ.checkin_time IS NULL
972 AND circ.due_date < NOW()
973 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
975 IF items_overdue >= max_overdue.threshold::INT THEN
976 new_sp_row.usr := match_user;
977 new_sp_row.org_unit := max_overdue.org_unit;
978 new_sp_row.standing_penalty := 2;
979 RETURN NEXT new_sp_row;
983 -- Start over for max out
984 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
986 -- Fail if the user has too many checked out items
988 tmp_grp := user_object.profile;
990 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
992 IF max_items_out.threshold IS NULL THEN
993 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
998 IF tmp_grp IS NULL THEN
1003 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1007 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1012 -- Fail if the user has too many items checked out
1013 IF max_items_out.threshold IS NOT NULL THEN
1017 FROM actor.usr_standing_penalty
1018 WHERE usr = match_user
1019 AND org_unit = max_items_out.org_unit
1020 AND (stop_date IS NULL or stop_date > NOW())
1021 AND standing_penalty = 3;
1023 SELECT INTO items_out COUNT(*)
1024 FROM action.circulation circ
1025 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
1026 WHERE circ.usr = match_user
1027 AND circ.checkin_time IS NULL
1028 AND (circ.stop_fines IN (
1029 SELECT 'MAXFINES'::TEXT
1031 SELECT 'LONGOVERDUE'::TEXT
1037 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
1042 SELECT 'CLAIMSRETURNED'::TEXT
1046 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
1050 ) OR circ.stop_fines IS NULL)
1051 AND xact_finish IS NULL;
1053 IF items_out >= max_items_out.threshold::INT THEN
1054 new_sp_row.usr := match_user;
1055 new_sp_row.org_unit := max_items_out.org_unit;
1056 new_sp_row.standing_penalty := 3;
1057 RETURN NEXT new_sp_row;
1061 -- Start over for max lost
1062 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1064 -- Fail if the user has too many lost items
1066 tmp_grp := user_object.profile;
1069 SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
1071 IF max_lost.threshold IS NULL THEN
1072 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1077 IF tmp_grp IS NULL THEN
1082 IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1086 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1090 IF max_lost.threshold IS NOT NULL THEN
1094 FROM actor.usr_standing_penalty
1095 WHERE usr = match_user
1096 AND org_unit = max_lost.org_unit
1097 AND (stop_date IS NULL or stop_date > NOW())
1098 AND standing_penalty = 5;
1100 SELECT INTO items_lost COUNT(*)
1101 FROM action.circulation circ
1102 JOIN actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
1103 WHERE circ.usr = match_user
1104 AND circ.checkin_time IS NULL
1105 AND (circ.stop_fines = 'LOST')
1106 AND xact_finish IS NULL;
1108 IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
1109 new_sp_row.usr := match_user;
1110 new_sp_row.org_unit := max_lost.org_unit;
1111 new_sp_row.standing_penalty := 5;
1112 RETURN NEXT new_sp_row;
1116 -- Start over for max longoverdue
1117 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1119 -- Fail if the user has too many longoverdue items
1121 tmp_grp := user_object.profile;
1124 SELECT * INTO max_longoverdue
1125 FROM permission.grp_penalty_threshold
1126 WHERE grp = tmp_grp AND
1128 org_unit = tmp_org.id;
1130 IF max_longoverdue.threshold IS NULL THEN
1131 SELECT parent INTO tmp_grp
1132 FROM permission.grp_tree WHERE id = tmp_grp;
1137 IF tmp_grp IS NULL THEN
1142 IF max_longoverdue.threshold IS NOT NULL
1143 OR tmp_org.parent_ou IS NULL THEN
1147 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1151 IF max_longoverdue.threshold IS NOT NULL THEN
1155 FROM actor.usr_standing_penalty
1156 WHERE usr = match_user
1157 AND org_unit = max_longoverdue.org_unit
1158 AND (stop_date IS NULL or stop_date > NOW())
1159 AND standing_penalty = 35;
1161 SELECT INTO items_longoverdue COUNT(*)
1162 FROM action.circulation circ
1163 JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp
1164 ON (circ.circ_lib = fp.id)
1165 WHERE circ.usr = match_user
1166 AND circ.checkin_time IS NULL
1167 AND (circ.stop_fines = 'LONGOVERDUE')
1168 AND xact_finish IS NULL;
1170 IF items_longoverdue >= max_longoverdue.threshold::INT
1171 AND 0 < max_longoverdue.threshold::INT THEN
1172 new_sp_row.usr := match_user;
1173 new_sp_row.org_unit := max_longoverdue.org_unit;
1174 new_sp_row.standing_penalty := 35;
1175 RETURN NEXT new_sp_row;
1180 -- Start over for collections warning
1181 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1183 -- Fail if the user has a collections-level fine balance
1185 tmp_grp := user_object.profile;
1187 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
1189 IF max_fines.threshold IS NULL THEN
1190 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1195 IF tmp_grp IS NULL THEN
1200 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1204 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1208 IF max_fines.threshold IS NOT NULL THEN
1212 FROM actor.usr_standing_penalty
1213 WHERE usr = match_user
1214 AND org_unit = max_fines.org_unit
1215 AND (stop_date IS NULL or stop_date > NOW())
1216 AND standing_penalty = 4;
1218 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
1220 SELECT SUM(f.balance_owed) INTO current_fines
1221 FROM money.materialized_billable_xact_summary f
1224 FROM booking.reservation r
1225 WHERE r.usr = match_user
1226 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
1227 AND r.xact_finish IS NULL
1230 FROM money.grocery g
1231 WHERE g.usr = match_user
1232 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
1233 AND g.xact_finish IS NULL
1236 FROM action.circulation circ
1237 WHERE circ.usr = match_user
1238 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
1239 AND circ.xact_finish IS NULL ) l USING (id);
1241 IF current_fines >= max_fines.threshold THEN
1242 new_sp_row.usr := match_user;
1243 new_sp_row.org_unit := max_fines.org_unit;
1244 new_sp_row.standing_penalty := 4;
1245 RETURN NEXT new_sp_row;
1249 -- Start over for in collections
1250 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1252 -- Remove the in-collections penalty if the user has paid down enough
1253 -- This penalty is different, because this code is not responsible for creating
1254 -- new in-collections penalties, only for removing them
1256 tmp_grp := user_object.profile;
1258 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
1260 IF max_fines.threshold IS NULL THEN
1261 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1266 IF tmp_grp IS NULL THEN
1271 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1275 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1279 IF max_fines.threshold IS NOT NULL THEN
1281 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
1283 -- first, see if the user had paid down to the threshold
1284 SELECT SUM(f.balance_owed) INTO current_fines
1285 FROM money.materialized_billable_xact_summary f
1288 FROM booking.reservation r
1289 WHERE r.usr = match_user
1290 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
1291 AND r.xact_finish IS NULL
1294 FROM money.grocery g
1295 WHERE g.usr = match_user
1296 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
1297 AND g.xact_finish IS NULL
1300 FROM action.circulation circ
1301 WHERE circ.usr = match_user
1302 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
1303 AND circ.xact_finish IS NULL ) l USING (id);
1305 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
1306 -- patron has paid down enough
1308 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
1310 IF tmp_penalty.org_depth IS NOT NULL THEN
1312 -- since this code is not responsible for applying the penalty, it can't
1313 -- guarantee the current context org will match the org at which the penalty
1314 --- was applied. search up the org tree until we hit the configured penalty depth
1315 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1316 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1318 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
1322 FROM actor.usr_standing_penalty
1323 WHERE usr = match_user
1324 AND org_unit = tmp_org.id
1325 AND (stop_date IS NULL or stop_date > NOW())
1326 AND standing_penalty = 30;
1328 IF tmp_org.parent_ou IS NULL THEN
1332 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1333 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1338 -- no penalty depth is defined, look for exact matches
1342 FROM actor.usr_standing_penalty
1343 WHERE usr = match_user
1344 AND org_unit = max_fines.org_unit
1345 AND (stop_date IS NULL or stop_date > NOW())
1346 AND standing_penalty = 30;
1355 $func$ LANGUAGE plpgsql;
1358 -- from 110.hold_matrix.sql
1360 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
1363 user_object actor.usr%ROWTYPE;
1364 age_protect_object config.rule_age_hold_protect%ROWTYPE;
1365 standing_penalty config.standing_penalty%ROWTYPE;
1366 transit_range_ou_type actor.org_unit_type%ROWTYPE;
1367 transit_source actor.org_unit%ROWTYPE;
1368 item_object asset.copy%ROWTYPE;
1369 item_cn_object asset.call_number%ROWTYPE;
1370 item_status_object config.copy_status%ROWTYPE;
1371 item_location_object asset.copy_location%ROWTYPE;
1372 ou_skip actor.org_unit_setting%ROWTYPE;
1373 result action.matrix_test_result;
1374 hold_test config.hold_matrix_matchpoint%ROWTYPE;
1375 use_active_date TEXT;
1376 age_protect_date TIMESTAMP WITH TIME ZONE;
1378 hold_transit_prox INT;
1379 frozen_hold_count INT;
1380 context_org_list INT[];
1384 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1385 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( pickup_ou );
1387 result.success := TRUE;
1389 -- The HOLD penalty block only applies to new holds.
1390 -- The CAPTURE penalty block applies to existing holds.
1391 hold_penalty := 'HOLD';
1392 IF retargetting THEN
1393 hold_penalty := 'CAPTURE';
1396 -- Fail if we couldn't find a user
1397 IF user_object.id IS NULL THEN
1398 result.fail_part := 'no_user';
1399 result.success := FALSE;
1405 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1407 -- Fail if we couldn't find a copy
1408 IF item_object.id IS NULL THEN
1409 result.fail_part := 'no_item';
1410 result.success := FALSE;
1416 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
1417 result.matchpoint := matchpoint_id;
1419 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
1421 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
1422 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
1423 result.fail_part := 'circ.holds.target_skip_me';
1424 result.success := FALSE;
1430 -- Fail if user is barred
1431 IF user_object.barred IS TRUE THEN
1432 result.fail_part := 'actor.usr.barred';
1433 result.success := FALSE;
1439 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1440 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
1441 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
1443 -- Fail if we couldn't find any matchpoint (requires a default)
1444 IF matchpoint_id IS NULL THEN
1445 result.fail_part := 'no_matchpoint';
1446 result.success := FALSE;
1452 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
1454 IF hold_test.holdable IS FALSE THEN
1455 result.fail_part := 'config.hold_matrix_test.holdable';
1456 result.success := FALSE;
1461 IF item_object.holdable IS FALSE THEN
1462 result.fail_part := 'item.holdable';
1463 result.success := FALSE;
1468 IF item_status_object.holdable IS FALSE THEN
1469 result.fail_part := 'status.holdable';
1470 result.success := FALSE;
1475 IF item_location_object.holdable IS FALSE THEN
1476 result.fail_part := 'location.holdable';
1477 result.success := FALSE;
1482 IF hold_test.transit_range IS NOT NULL THEN
1483 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
1484 IF hold_test.distance_is_from_owner THEN
1485 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
1487 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
1490 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
1493 result.fail_part := 'transit_range';
1494 result.success := FALSE;
1500 FOR standing_penalty IN
1501 SELECT DISTINCT csp.*
1502 FROM actor.usr_standing_penalty usp
1503 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1504 WHERE usr = match_user
1505 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1506 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1507 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
1509 result.fail_part := standing_penalty.name;
1510 result.success := FALSE;
1515 IF hold_test.stop_blocked_user IS TRUE THEN
1516 FOR standing_penalty IN
1517 SELECT DISTINCT csp.*
1518 FROM actor.usr_standing_penalty usp
1519 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1520 WHERE usr = match_user
1521 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1522 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1523 AND csp.block_list LIKE '%CIRC%' LOOP
1525 result.fail_part := standing_penalty.name;
1526 result.success := FALSE;
1532 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
1533 SELECT INTO hold_count COUNT(*)
1534 FROM action.hold_request
1535 WHERE usr = match_user
1536 AND fulfillment_time IS NULL
1537 AND cancel_time IS NULL
1538 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
1540 IF hold_count >= hold_test.max_holds THEN
1541 result.fail_part := 'config.hold_matrix_test.max_holds';
1542 result.success := FALSE;
1548 IF item_object.age_protect IS NOT NULL THEN
1549 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
1550 IF hold_test.distance_is_from_owner THEN
1551 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
1553 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
1555 IF use_active_date = 'true' THEN
1556 age_protect_date := COALESCE(item_object.active_date, NOW());
1558 age_protect_date := item_object.create_date;
1560 IF age_protect_date + age_protect_object.age > NOW() THEN
1561 IF hold_test.distance_is_from_owner THEN
1562 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1563 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
1565 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
1568 IF hold_transit_prox > age_protect_object.prox THEN
1569 result.fail_part := 'config.rule_age_hold_protect.prox';
1570 result.success := FALSE;
1583 $func$ LANGUAGE plpgsql;
1586 -- from 300.schema.staged_search.sql
1589 -- from 990.schema.unapi.sql
1591 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT)
1592 RETURNS anyarray AS $$
1593 SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
1594 $$ LANGUAGE SQL STABLE;
1597 -- from 999.functions.global.sql
1599 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
1601 moved_objects INT := 0;
1602 source_cn asset.call_number%ROWTYPE;
1603 target_cn asset.call_number%ROWTYPE;
1604 metarec metabib.metarecord%ROWTYPE;
1605 hold action.hold_request%ROWTYPE;
1606 ser_rec serial.record_entry%ROWTYPE;
1607 ser_sub serial.subscription%ROWTYPE;
1608 acq_lineitem acq.lineitem%ROWTYPE;
1609 acq_request acq.user_request%ROWTYPE;
1610 booking booking.resource_type%ROWTYPE;
1611 source_part biblio.monograph_part%ROWTYPE;
1612 target_part biblio.monograph_part%ROWTYPE;
1613 multi_home biblio.peer_bib_copy_map%ROWTYPE;
1617 uri_text TEXT := '';
1620 -- move any 856 entries on records that have at least one MARC-mapped URI entry
1621 SELECT INTO uri_count COUNT(*)
1622 FROM asset.uri_call_number_map m
1623 JOIN asset.call_number cn ON (m.call_number = cn.id)
1624 WHERE cn.record = source_record;
1626 IF uri_count > 0 THEN
1628 -- This returns more nodes than you might expect:
1629 -- 7 instead of 1 for an 856 with $u $y $9
1630 SELECT COUNT(*) INTO counter
1631 FROM oils_xpath_table(
1634 'biblio.record_entry',
1636 'id=' || source_record
1637 ) as t(i int,c text);
1639 FOR i IN 1 .. counter LOOP
1640 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
1642 ' ind1="' || FIRST(ind1) || '"' ||
1643 ' ind2="' || FIRST(ind2) || '">' ||
1645 '<subfield code="' || subfield || '">' ||
1648 regexp_replace(data,'&','&','g'),
1652 ) || '</subfield>', ''
1653 ) || '</datafield>' INTO uri_datafield
1654 FROM oils_xpath_table(
1657 'biblio.record_entry',
1658 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
1659 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
1660 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
1661 '//*[@tag="856"][position()=' || i || ']/*[@code]',
1662 'id=' || source_record
1663 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
1665 -- As most of the results will be NULL, protect against NULLifying
1666 -- the valid content that we do generate
1667 uri_text := uri_text || COALESCE(uri_datafield, '');
1670 IF uri_text <> '' THEN
1671 UPDATE biblio.record_entry
1672 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
1673 WHERE id = target_record;
1678 -- Find and move metarecords to the target record
1679 SELECT INTO metarec *
1680 FROM metabib.metarecord
1681 WHERE master_record = source_record;
1684 UPDATE metabib.metarecord
1685 SET master_record = target_record,
1687 WHERE id = metarec.id;
1689 moved_objects := moved_objects + 1;
1692 -- Find call numbers attached to the source ...
1693 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
1695 SELECT INTO target_cn *
1696 FROM asset.call_number
1697 WHERE label = source_cn.label
1698 AND owning_lib = source_cn.owning_lib
1699 AND record = target_record
1702 -- ... and if there's a conflicting one on the target ...
1705 -- ... move the copies to that, and ...
1707 SET call_number = target_cn.id
1708 WHERE call_number = source_cn.id;
1710 -- ... move V holds to the move-target call number
1711 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
1713 UPDATE action.hold_request
1714 SET target = target_cn.id
1717 moved_objects := moved_objects + 1;
1722 -- ... just move the call number to the target record
1723 UPDATE asset.call_number
1724 SET record = target_record
1725 WHERE id = source_cn.id;
1728 moved_objects := moved_objects + 1;
1731 -- Find T holds targeting the source record ...
1732 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
1734 -- ... and move them to the target record
1735 UPDATE action.hold_request
1736 SET target = target_record
1739 moved_objects := moved_objects + 1;
1742 -- Find serial records targeting the source record ...
1743 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
1744 -- ... and move them to the target record
1745 UPDATE serial.record_entry
1746 SET record = target_record
1747 WHERE id = ser_rec.id;
1749 moved_objects := moved_objects + 1;
1752 -- Find serial subscriptions targeting the source record ...
1753 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
1754 -- ... and move them to the target record
1755 UPDATE serial.subscription
1756 SET record_entry = target_record
1757 WHERE id = ser_sub.id;
1759 moved_objects := moved_objects + 1;
1762 -- Find booking resource types targeting the source record ...
1763 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
1764 -- ... and move them to the target record
1765 UPDATE booking.resource_type
1766 SET record = target_record
1767 WHERE id = booking.id;
1769 moved_objects := moved_objects + 1;
1772 -- Find acq lineitems targeting the source record ...
1773 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
1774 -- ... and move them to the target record
1776 SET eg_bib_id = target_record
1777 WHERE id = acq_lineitem.id;
1779 moved_objects := moved_objects + 1;
1782 -- Find acq user purchase requests targeting the source record ...
1783 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
1784 -- ... and move them to the target record
1785 UPDATE acq.user_request
1786 SET eg_bib = target_record
1787 WHERE id = acq_request.id;
1789 moved_objects := moved_objects + 1;
1792 -- Find parts attached to the source ...
1793 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
1795 SELECT INTO target_part *
1796 FROM biblio.monograph_part
1797 WHERE label = source_part.label
1798 AND record = target_record;
1800 -- ... and if there's a conflicting one on the target ...
1803 -- ... move the copy-part maps to that, and ...
1804 UPDATE asset.copy_part_map
1805 SET part = target_part.id
1806 WHERE part = source_part.id;
1808 -- ... move P holds to the move-target part
1809 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
1811 UPDATE action.hold_request
1812 SET target = target_part.id
1815 moved_objects := moved_objects + 1;
1820 -- ... just move the part to the target record
1821 UPDATE biblio.monograph_part
1822 SET record = target_record
1823 WHERE id = source_part.id;
1826 moved_objects := moved_objects + 1;
1829 -- Find multi_home items attached to the source ...
1830 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
1831 -- ... and move them to the target record
1832 UPDATE biblio.peer_bib_copy_map
1833 SET peer_record = target_record
1834 WHERE id = multi_home.id;
1836 moved_objects := moved_objects + 1;
1839 -- And delete mappings where the item's home bib was merged with the peer bib
1840 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
1841 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
1842 FROM asset.copy WHERE id = target_copy
1845 -- Finally, "delete" the source record
1846 DELETE FROM biblio.record_entry WHERE id = source_record;
1848 -- That's all, folks!
1849 RETURN moved_objects;
1851 $func$ LANGUAGE plpgsql;
1853 -- from reporter-schema.sql
1855 CREATE OR REPLACE VIEW reporter.simple_record AS
1862 title.value AS title,
1863 uniform_title.value AS uniform_title,
1864 author.value AS author,
1865 publisher.value AS publisher,
1866 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
1867 series_title.value AS series_title,
1868 series_statement.value AS series_statement,
1869 summary.value AS summary,
1870 ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
1871 ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
1872 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
1873 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
1874 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
1875 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
1876 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
1877 ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
1878 FROM biblio.record_entry r
1879 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
1880 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
1881 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1882 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
1883 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
1884 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
1885 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1886 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1887 LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
1888 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
1889 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
1890 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
1892 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
1898 FIRST(title.value) AS title,
1899 FIRST(author.value) AS author,
1900 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
1901 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
1902 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
1904 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
1906 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
1908 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
1910 FROM biblio.record_entry r
1911 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1912 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
1913 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND (publisher.tag = '260' OR (publisher.tag = '264' AND publisher.ind2 = '1')) AND publisher.subfield = 'b')
1914 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND (pubdate.tag = '260' OR (pubdate.tag = '264' AND pubdate.ind2 = '1')) AND pubdate.subfield = 'c')
1915 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1916 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1921 SELECT evergreen.upgrade_deps_block_check('0856', :eg_version);
1923 CREATE OR REPLACE FUNCTION metabib.staged_browse(
1927 context_locations INT[],
1929 browse_superpage_size INT,
1930 count_up_from_zero BOOL, -- if false, count down from -1
1933 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1941 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
1942 results_skipped INT := 0;
1943 row_counter INT := 0;
1948 all_records BIGINT[];
1949 all_brecords BIGINT[];
1950 all_arecords BIGINT[];
1951 superpage_of_records BIGINT[];
1954 IF count_up_from_zero THEN
1960 OPEN curs FOR EXECUTE query;
1963 FETCH curs INTO rec;
1965 IF result_row.pivot_point IS NOT NULL THEN
1966 RETURN NEXT result_row;
1972 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1973 SELECT INTO all_arecords, result_row.sees, afields
1974 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1975 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1976 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1978 FROM metabib.browse_entry_simple_heading_map mbeshm
1979 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1980 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1981 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1982 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1983 ash.atag = map.authority_field
1984 AND map.metabib_field = ANY(fields)
1986 WHERE mbeshm.entry = rec.id;
1989 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
1990 SELECT INTO all_brecords, result_row.authorities, bfields
1991 ARRAY_AGG(DISTINCT source),
1992 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
1993 ARRAY_AGG(DISTINCT def)
1994 FROM metabib.browse_entry_def_map
1995 WHERE entry = rec.id
1996 AND def = ANY(fields);
1998 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2000 result_row.sources := 0;
2001 result_row.asources := 0;
2003 -- Bib-linked vis checking
2004 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2006 full_end := ARRAY_LENGTH(all_brecords, 1);
2007 superpage_size := COALESCE(browse_superpage_size, full_end);
2009 slice_end := superpage_size;
2011 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
2012 superpage_of_records := all_brecords[slice_start:slice_end];
2014 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2015 '1::INT AS rel FROM (SELECT UNNEST(' ||
2016 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2018 -- We use search.query_parser_fts() for visibility testing.
2019 -- We're calling it once per browse-superpage worth of records
2020 -- out of the set of records related to a given mbe, until we've
2021 -- either exhausted that set of records or found at least 1
2024 SELECT INTO result_row.sources visible
2025 FROM search.query_parser_fts(
2026 context_org, NULL, qpfts_query, NULL,
2027 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2029 WHERE qpfts.rel IS NULL;
2031 slice_start := slice_start + superpage_size;
2032 slice_end := slice_end + superpage_size;
2035 -- Accurate? Well, probably.
2036 result_row.accurate := browse_superpage_size IS NULL OR
2037 browse_superpage_size >= full_end;
2041 -- Authority-linked vis checking
2042 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2044 full_end := ARRAY_LENGTH(all_arecords, 1);
2045 superpage_size := COALESCE(browse_superpage_size, full_end);
2047 slice_end := superpage_size;
2049 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
2050 superpage_of_records := all_arecords[slice_start:slice_end];
2052 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2053 '1::INT AS rel FROM (SELECT UNNEST(' ||
2054 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2056 -- We use search.query_parser_fts() for visibility testing.
2057 -- We're calling it once per browse-superpage worth of records
2058 -- out of the set of records related to a given mbe, via
2059 -- authority until we've either exhausted that set of records
2060 -- or found at least 1 visible record.
2062 SELECT INTO result_row.asources visible
2063 FROM search.query_parser_fts(
2064 context_org, NULL, qpfts_query, NULL,
2065 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2067 WHERE qpfts.rel IS NULL;
2069 slice_start := slice_start + superpage_size;
2070 slice_end := slice_end + superpage_size;
2074 -- Accurate? Well, probably.
2075 result_row.aaccurate := browse_superpage_size IS NULL OR
2076 browse_superpage_size >= full_end;
2080 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2082 -- The function that calls this function needs row_number in order
2083 -- to correctly order results from two different runs of this
2085 result_row.row_number := row_number;
2087 -- Now, if row_counter is still less than limit, return a row. If
2088 -- not, but it is less than next_pivot_pos, continue on without
2089 -- returning actual result rows until we find
2090 -- that next pivot, and return it.
2092 IF row_counter < result_limit THEN
2093 result_row.browse_entry := rec.id;
2094 result_row.value := rec.value;
2096 RETURN NEXT result_row;
2098 result_row.browse_entry := NULL;
2099 result_row.authorities := NULL;
2100 result_row.fields := NULL;
2101 result_row.value := NULL;
2102 result_row.sources := NULL;
2103 result_row.sees := NULL;
2104 result_row.accurate := NULL;
2105 result_row.aaccurate := NULL;
2106 result_row.pivot_point := rec.id;
2108 IF row_counter >= next_pivot_pos THEN
2109 RETURN NEXT result_row;
2114 IF count_up_from_zero THEN
2115 row_number := row_number + 1;
2117 row_number := row_number - 1;
2120 -- row_counter is different from row_number.
2121 -- It simply counts up from zero so that we know when
2122 -- we've reached our limit.
2123 row_counter := row_counter + 1;
2127 $p$ LANGUAGE PLPGSQL;
2131 * Copyright (C) 2014 Equinox Software, Inc.
2132 * Mike Rylander <miker@esilibrary.com>
2134 * This program is free software; you can redistribute it and/or
2135 * modify it under the terms of the GNU General Public License
2136 * as published by the Free Software Foundation; either version 2
2137 * of the License, or (at your option) any later version.
2139 * This program is distributed in the hope that it will be useful,
2140 * but WITHOUT ANY WARRANTY; without even the implied warranty of
2141 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
2142 * GNU General Public License for more details.
2148 SELECT evergreen.upgrade_deps_block_check('0857', :eg_version);
2150 INSERT INTO config.global_flag (name, enabled, label)
2152 'opac.located_uri.act_as_copy',
2155 'opac.located_uri.act_as_copy',
2156 'When enabled, Located URIs will provide visiblity behavior identical to copies.',
2162 CREATE OR REPLACE FUNCTION search.query_parser_fts (
2164 param_search_ou INT,
2167 param_statuses INT[],
2168 param_locations INT[],
2174 deleted_search BOOL,
2175 param_pref_ou INT DEFAULT NULL
2176 ) RETURNS SETOF search.search_result AS $func$
2179 current_res search.search_result%ROWTYPE;
2180 search_org_list INT[];
2181 luri_org_list INT[];
2190 core_cursor REFCURSOR;
2191 core_rel_query TEXT;
2193 total_count INT := 0;
2194 check_count INT := 0;
2195 deleted_count INT := 0;
2196 visible_count INT := 0;
2197 excluded_count INT := 0;
2202 check_limit := COALESCE( param_check, 1000 );
2203 core_limit := COALESCE( param_limit, 25000 );
2204 core_offset := COALESCE( param_offset, 0 );
2206 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
2208 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
2210 IF param_search_ou > 0 THEN
2211 IF param_depth IS NOT NULL THEN
2212 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
2214 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
2217 IF luri_as_copy THEN
2218 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
2220 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
2223 ELSIF param_search_ou < 0 THEN
2224 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
2226 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
2228 IF luri_as_copy THEN
2229 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
2231 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
2234 luri_org_list := luri_org_list || tmp_int_list;
2237 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
2239 ELSIF param_search_ou = 0 THEN
2240 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
2243 IF param_pref_ou IS NOT NULL THEN
2244 IF luri_as_copy THEN
2245 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
2247 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
2250 luri_org_list := luri_org_list || tmp_int_list;
2253 OPEN core_cursor FOR EXECUTE param_query;
2257 FETCH core_cursor INTO core_result;
2258 EXIT WHEN NOT FOUND;
2259 EXIT WHEN total_count >= core_limit;
2261 total_count := total_count + 1;
2263 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
2265 check_count := check_count + 1;
2267 IF NOT deleted_search THEN
2269 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
2271 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
2272 deleted_count := deleted_count + 1;
2277 FROM biblio.record_entry b
2278 JOIN config.bib_source s ON (b.source = s.id)
2279 WHERE s.transcendant
2280 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
2283 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
2284 visible_count := visible_count + 1;
2286 current_res.id = core_result.id;
2287 current_res.rel = core_result.rel;
2291 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2295 current_res.record = core_result.records[1];
2297 current_res.record = NULL;
2300 RETURN NEXT current_res;
2306 FROM asset.call_number cn
2307 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
2308 JOIN asset.uri uri ON (map.uri = uri.id)
2309 WHERE NOT cn.deleted
2310 AND cn.label = '##URI##'
2312 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
2313 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2314 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
2318 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
2319 visible_count := visible_count + 1;
2321 current_res.id = core_result.id;
2322 current_res.rel = core_result.rel;
2326 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2330 current_res.record = core_result.records[1];
2332 current_res.record = NULL;
2335 RETURN NEXT current_res;
2340 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
2343 FROM asset.call_number cn
2344 JOIN asset.copy cp ON (cp.call_number = cn.id)
2345 WHERE NOT cn.deleted
2347 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
2348 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2349 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2354 FROM biblio.peer_bib_copy_map pr
2355 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2356 WHERE NOT cp.deleted
2357 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
2358 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2359 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2363 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
2364 excluded_count := excluded_count + 1;
2371 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
2374 FROM asset.call_number cn
2375 JOIN asset.copy cp ON (cp.call_number = cn.id)
2376 WHERE NOT cn.deleted
2378 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
2379 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2380 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2385 FROM biblio.peer_bib_copy_map pr
2386 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2387 WHERE NOT cp.deleted
2388 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
2389 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2390 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2394 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
2395 excluded_count := excluded_count + 1;
2402 IF staff IS NULL OR NOT staff THEN
2405 FROM asset.opac_visible_copies
2406 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2407 AND record IN ( SELECT * FROM unnest( core_result.records ) )
2412 FROM biblio.peer_bib_copy_map pr
2413 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
2414 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2415 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2420 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
2421 excluded_count := excluded_count + 1;
2429 FROM asset.call_number cn
2430 JOIN asset.copy cp ON (cp.call_number = cn.id)
2431 WHERE NOT cn.deleted
2433 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2434 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2440 FROM biblio.peer_bib_copy_map pr
2441 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2442 WHERE NOT cp.deleted
2443 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2444 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2450 FROM asset.call_number cn
2451 JOIN asset.copy cp ON (cp.call_number = cn.id)
2452 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2457 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
2458 excluded_count := excluded_count + 1;
2469 visible_count := visible_count + 1;
2471 current_res.id = core_result.id;
2472 current_res.rel = core_result.rel;
2476 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2480 current_res.record = core_result.records[1];
2482 current_res.record = NULL;
2485 RETURN NEXT current_res;
2487 IF visible_count % 1000 = 0 THEN
2488 -- RAISE NOTICE ' % visible so far ... ', visible_count;
2493 current_res.id = NULL;
2494 current_res.rel = NULL;
2495 current_res.record = NULL;
2496 current_res.total = total_count;
2497 current_res.checked = check_count;
2498 current_res.deleted = deleted_count;
2499 current_res.visible = visible_count;
2500 current_res.excluded = excluded_count;
2504 RETURN NEXT current_res;
2507 $func$ LANGUAGE PLPGSQL;
2509 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2513 depth INT DEFAULT NULL,
2514 includes TEXT[] DEFAULT NULL::TEXT[],
2515 slimit HSTORE DEFAULT NULL,
2516 soffset HSTORE DEFAULT NULL,
2517 include_xmlns BOOL DEFAULT TRUE,
2518 pref_lib INT DEFAULT NULL
2524 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2525 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2526 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2530 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2533 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2535 FROM asset.opac_ou_record_copy_count($2, $1)
2539 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2541 FROM asset.staff_ou_record_copy_count($2, $1)
2545 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2547 FROM asset.opac_ou_record_copy_count($9, $1)
2552 WHEN ('bmp' = ANY ($5)) THEN
2554 name monograph_parts,
2555 (SELECT XMLAGG(bmp) FROM (
2556 SELECT unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
2557 FROM biblio.monograph_part
2565 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2567 SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
2568 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
2571 SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey
2572 FROM evergreen.located_uris($1, $2, $9) AS uris
2575 CASE WHEN ('ssub' = ANY ($5)) THEN
2578 (SELECT XMLAGG(ssub) FROM (
2579 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2580 FROM serial.subscription
2581 WHERE record_entry = $1
2585 CASE WHEN ('acp' = ANY ($5)) THEN
2587 name foreign_copies,
2588 (SELECT XMLAGG(acp) FROM (
2589 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2590 FROM biblio.peer_bib_copy_map p
2591 JOIN asset.copy c ON (p.target_copy = c.id)
2592 WHERE NOT c.deleted AND p.peer_record = $1
2593 LIMIT ($6 -> 'acp')::INT
2594 OFFSET ($7 -> 'acp')::INT
2599 $F$ LANGUAGE SQL STABLE;
2603 SELECT evergreen.upgrade_deps_block_check('0858', :eg_version);
2605 -- Fix faulty seed data. Otherwise for ptype 'f' we have subfield 'e'
2606 -- overlapping subfield 'd'
2607 UPDATE config.marc21_physical_characteristic_subfield_map
2609 WHERE ptype_key = 'f' AND subfield = 'e';
2611 -- Evergreen DB patch 0859.data.staff-initials-settings.sql
2613 -- More granular configuration settings for requiring use of staff initials
2616 -- check whether patch can be applied
2617 SELECT evergreen.upgrade_deps_block_check('0859', :eg_version);
2619 -- add new granular settings for requiring use of staff initials
2620 INSERT INTO config.org_unit_setting_type
2621 (name, grp, label, description, datatype)
2623 'ui.staff.require_initials.patron_standing_penalty',
2626 'ui.staff.require_initials.patron_standing_penalty',
2627 'Require staff initials for entry/edit of patron standing penalties and messages.',
2632 'ui.staff.require_initials.patron_standing_penalty',
2633 'Appends staff initials and edit date into patron standing penalties and messages.',
2639 'ui.staff.require_initials.patron_info_notes',
2642 'ui.staff.require_initials.patron_info_notes',
2643 'Require staff initials for entry/edit of patron notes.',
2648 'ui.staff.require_initials.patron_info_notes',
2649 'Appends staff initials and edit date into patron note content.',
2655 'ui.staff.require_initials.copy_notes',
2658 'ui.staff.require_initials.copy_notes',
2659 'Require staff initials for entry/edit of copy notes.',
2664 'ui.staff.require_initials.copy_notes',
2665 'Appends staff initials and edit date into copy note content..',
2672 -- Update any existing setting so that the original set value is now passed to
2673 -- one of the newer settings.
2675 UPDATE actor.org_unit_setting
2676 SET name = 'ui.staff.require_initials.patron_standing_penalty'
2677 WHERE name = 'ui.staff.require_initials';
2679 -- Add similar values for new settings as old ones to preserve existing configured
2682 INSERT INTO actor.org_unit_setting (org_unit, name, value)
2683 SELECT org_unit, 'ui.staff.require_initials.patron_info_notes', value
2684 FROM actor.org_unit_setting
2685 WHERE name = 'ui.staff.require_initials.patron_standing_penalty';
2687 INSERT INTO actor.org_unit_setting (org_unit, name, value)
2688 SELECT org_unit, 'ui.staff.require_initials.copy_notes', value
2689 FROM actor.org_unit_setting
2690 WHERE name = 'ui.staff.require_initials.patron_standing_penalty';
2692 -- Update setting logs so that the original setting name's history is now transferred
2693 -- over to one of the newer settings.
2695 UPDATE config.org_unit_setting_type_log
2696 SET field_name = 'ui.staff.require_initials.patron_standing_penalty'
2697 WHERE field_name = 'ui.staff.require_initials';
2699 -- Remove the old setting entirely
2701 DELETE FROM config.org_unit_setting_type WHERE name = 'ui.staff.require_initials';
2705 SELECT evergreen.upgrade_deps_block_check('0860', :eg_version);
2707 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
2713 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
2715 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
2719 $$ LANGUAGE PLPGSQL;
2722 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
2723 SELECT DISTINCT l.version
2724 FROM config.upgrade_log l
2725 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
2726 WHERE d.db_patch = $1
2729 -- List applied db patches that are superseded by (and block the application of) my_db_patch
2730 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
2731 SELECT DISTINCT l.version
2732 FROM config.upgrade_log l
2733 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
2734 WHERE d.db_patch = $1
2738 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
2743 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
2744 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
2745 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
2747 Upgrade script % can not be applied:
2748 applied deprecated scripts %
2749 applied superseded scripts %
2753 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
2754 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
2755 evergreen.upgrade_list_applied_deprecated(my_db_patch),
2756 evergreen.upgrade_list_applied_superseded(my_db_patch);
2759 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
2762 $$ LANGUAGE PLPGSQL;
2766 SELECT evergreen.upgrade_deps_block_check('0861', :eg_version);
2768 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
2769 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
2773 SELECT evergreen.upgrade_deps_block_check('0863', :eg_version);
2776 -- cheat sheet for enabling Stripe payments:
2777 -- 'credit.payments.allow' must be true, and among other things it drives the
2778 -- opac to render a payment form at all
2779 -- NEW 'credit.processor.stripe.enabled' must be true (kind of redundant but
2780 -- my fault for setting the precedent with c.p.{authorizenet|paypal|payflowpro}.enabled)
2781 -- 'credit.default.processor' must be 'Stripe'
2782 -- NEW 'credit.processor.stripe.pubkey' must be set
2783 -- NEW 'credit.processor.stripe.secretkey' must be set
2785 INSERT into config.org_unit_setting_type
2786 ( name, grp, label, description, datatype, fm_class ) VALUES
2788 ( 'credit.processor.stripe.enabled', 'credit',
2789 oils_i18n_gettext('credit.processor.stripe.enabled',
2790 'Enable Stripe payments',
2792 oils_i18n_gettext('credit.processor.stripe.enabled',
2793 'Enable Stripe payments',
2794 'coust', 'description'),
2797 ,( 'credit.processor.stripe.pubkey', 'credit',
2798 oils_i18n_gettext('credit.processor.stripe.pubkey',
2799 'Stripe publishable key',
2801 oils_i18n_gettext('credit.processor.stripe.pubkey',
2802 'Stripe publishable key',
2803 'coust', 'description'),
2806 ,( 'credit.processor.stripe.secretkey', 'credit',
2807 oils_i18n_gettext('credit.processor.stripe.secretkey',
2808 'Stripe secret key',
2810 oils_i18n_gettext('credit.processor.stripe.secretkey',
2811 'Stripe secret key',
2812 'coust', 'description'),
2816 UPDATE config.org_unit_setting_type
2817 SET description = 'This might be "AuthorizeNet", "PayPal", "PayflowPro", or "Stripe".'
2818 WHERE name = 'credit.processor.default' AND description = 'This might be "AuthorizeNet", "PayPal", etc.'; -- don't clobber local edits or i18n
2821 SELECT evergreen.upgrade_deps_block_check('0864', :eg_version);
2823 CREATE EXTENSION intarray;
2825 -- while we have this opportunity, and before we start collecting
2826 -- CCVM IDs (below) carve out a nice space for stock ccvm values
2827 UPDATE config.coded_value_map SET id = id + 10000 WHERE id > 556;
2828 SELECT SETVAL('config.coded_value_map_id_seq'::TEXT,
2829 (SELECT GREATEST(max(id), 10000) FROM config.coded_value_map));
2831 ALTER TABLE config.record_attr_definition ADD COLUMN multi BOOL NOT NULL DEFAULT TRUE, ADD COLUMN composite BOOL NOT NULL DEFAULT FALSE;
2833 UPDATE config.record_attr_definition
2835 WHERE name IN ('bib_level','control_type','pubdate','cat_form','enc_level','item_type','titlesort','authorsort');
2837 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
2841 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
2842 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
2843 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
2844 retval biblio.marc21_physical_characteristics%ROWTYPE;
2847 FOR _007 IN SELECT oils_xpath_string('//*', value) FROM UNNEST(oils_xpath('//*[@tag="007"]', marc)) x(value) LOOP
2848 IF _007 IS NOT NULL AND _007 <> '' THEN
2849 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
2851 IF ptype.ptype_key IS NOT NULL THEN
2852 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
2853 SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length );
2855 IF pval.id IS NOT NULL THEN
2858 retval.ptype := ptype.ptype_key;
2859 retval.subfield := psf.id;
2860 retval.value := pval.id;
2871 $func$ LANGUAGE PLPGSQL;
2873 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$
2879 collection TEXT[] := '{}'::TEXT[];
2881 rtype := (vandelay.marc21_record_type( marc )).code;
2882 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
2883 IF ff_pos.tag = 'ldr' THEN
2884 val := oils_xpath_string('//*[local-name()="leader"]', marc);
2885 IF val IS NOT NULL THEN
2886 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
2887 collection := collection || val;
2890 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
2891 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
2892 collection := collection || val;
2895 val := REPEAT( ff_pos.default_val, ff_pos.length );
2896 collection := collection || val;
2901 $func$ LANGUAGE PLPGSQL;
2903 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
2904 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
2905 $func$ LANGUAGE SQL;
2907 -- DECREMENTING serial starts at -1
2908 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
2910 CREATE TABLE metabib.uncontrolled_record_attr_value (
2911 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
2912 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
2915 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
2917 CREATE TABLE metabib.record_attr_vector_list (
2918 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
2919 vlist INT[] NOT NULL -- stores id from ccvm AND murav
2921 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
2923 CREATE TABLE metabib.record_sorter (
2924 id BIGSERIAL PRIMARY KEY,
2925 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
2926 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
2929 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
2930 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
2931 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
2933 CREATE TEMP TABLE attr_set ON COMMIT DROP AS SELECT DISTINCT id AS source, (each(attrs)).key,(each(attrs)).value FROM metabib.record_attr;
2934 DELETE FROM attr_set WHERE BTRIM(value) = '';
2936 -- Grab sort values for the new sorting mechanism
2937 INSERT INTO metabib.record_sorter (source,attr,value)
2938 SELECT a.source, a.key, a.value
2940 JOIN config.record_attr_definition d ON (d.name = a.key AND d.sorter AND a.value IS NOT NULL);
2942 -- Rewrite uncontrolled SVF record attrs as the seeds of an intarray vector
2943 INSERT INTO metabib.uncontrolled_record_attr_value (attr,value)
2944 SELECT DISTINCT a.key, a.value
2946 JOIN config.record_attr_definition d ON (d.name = a.key AND d.filter AND a.value IS NOT NULL)
2947 LEFT JOIN config.coded_value_map m ON (m.ctype = a.key)
2950 -- Now construct the record-specific vector from the SVF data
2951 INSERT INTO metabib.record_attr_vector_list (source,vlist)
2952 SELECT a.id, ARRAY_AGG(COALESCE(u.id, c.id))
2953 FROM metabib.record_attr a
2954 JOIN attr_set ON (a.id = attr_set.source)
2955 LEFT JOIN metabib.uncontrolled_record_attr_value u ON (u.attr = attr_set.key AND u.value = attr_set.value)
2956 LEFT JOIN config.coded_value_map c ON (c.ctype = attr_set.key AND c.code = attr_set.value)
2957 WHERE COALESCE(u.id,c.id) IS NOT NULL
2960 DROP VIEW IF EXISTS reporter.classic_current_circ;
2961 DROP VIEW metabib.rec_descriptor;
2962 DROP TABLE metabib.record_attr;
2964 CREATE TYPE metabib.record_attr_type AS (
2969 CREATE TABLE config.composite_attr_entry_definition(
2970 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
2971 definition TEXT NOT NULL -- JSON
2974 CREATE OR REPLACE VIEW metabib.record_attr_id_map AS
2975 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
2977 SELECT c.id, c.ctype AS attr, c.code AS value
2978 FROM config.coded_value_map c
2979 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
2981 CREATE VIEW metabib.composite_attr_id_map AS
2982 SELECT c.id, c.ctype AS attr, c.code AS value
2983 FROM config.coded_value_map c
2984 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
2986 CREATE OR REPLACE VIEW metabib.full_attr_id_map AS
2987 SELECT id, attr, value FROM metabib.record_attr_id_map
2989 SELECT id, attr, value FROM metabib.composite_attr_id_map;
2992 -- Back-compat view ... we're moving to an INTARRAY world
2993 CREATE VIEW metabib.record_attr_flat AS
2994 SELECT v.source AS id,
2997 FROM metabib.full_attr_id_map m
2998 JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) );
3000 CREATE VIEW metabib.record_attr AS
3001 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1;
3003 CREATE VIEW metabib.rec_descriptor AS
3006 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
3007 FROM metabib.record_attr;
3009 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
3010 $_SHARED{metabib_compile_composite_attr_cache} = {}
3011 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
3012 return exists $_SHARED{metabib_compile_composite_attr_cache};
3013 $f$ LANGUAGE PLPERLU;
3015 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
3016 delete $_SHARED{metabib_compile_composite_attr_cache};
3017 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
3018 $f$ LANGUAGE PLPERLU;
3020 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
3021 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
3024 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
3026 PERFORM metabib.compile_composite_attr_cache_invalidate();
3029 $f$ LANGUAGE PLPGSQL;
3031 CREATE TRIGGER ccraed_cache_inval_tgr AFTER INSERT OR UPDATE OR DELETE ON config.composite_attr_entry_definition FOR EACH STATEMENT EXECUTE PROCEDURE metabib.composite_attr_def_cache_inval_tgr();
3033 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
3038 my $def = decode_json($json);
3040 die("Composite attribute definition not supplied") unless $def;
3042 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
3044 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
3045 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
3052 if (ref $d eq 'HASH') { # node or AND
3053 if (exists $d->{_attr}) { # it is a node
3054 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
3055 my $id = spi_exec_prepared(
3056 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
3058 spi_freeplan($plan);
3060 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
3061 return '!' . recurse($$d{_not});
3062 } else { # an AND list
3063 @list = map { recurse($$d{$_}) } sort keys %$d;
3065 } elsif (ref $d eq 'ARRAY') {
3067 @list = map { recurse($_) } @$d;
3070 @list = grep { defined && $_ ne '' } @list;
3072 return '(' . join($j,@list) . ')' if @list;
3076 my $val = recurse($def) || undef;
3077 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
3080 $func$ IMMUTABLE LANGUAGE plperlu;
3082 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
3083 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
3084 $func$ STRICT IMMUTABLE LANGUAGE SQL;
3087 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
3091 temp_vector TEXT := '';
3097 NEW.index_vector = ''::tsvector;
3099 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
3101 SELECT n.func AS func,
3102 n.param_count AS param_count,
3104 FROM config.index_normalizer n
3105 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
3106 WHERE field = NEW.field AND m.pos < 0
3108 EXECUTE 'SELECT ' || normalizer.func || '(' ||
3109 quote_literal( value ) ||
3111 WHEN normalizer.param_count > 0
3112 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
3122 SELECT n.func AS func,
3123 n.param_count AS param_count,
3125 FROM config.index_normalizer n
3126 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
3127 WHERE field = NEW.field AND m.pos >= 0
3129 EXECUTE 'SELECT ' || normalizer.func || '(' ||
3130 quote_literal( value ) ||
3132 WHEN normalizer.param_count > 0
3133 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
3141 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
3143 value := ARRAY_TO_STRING(
3144 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
3146 value := public.search_normalize(value);
3147 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
3149 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
3152 SELECT DISTINCT m.ts_config, m.index_weight
3153 FROM config.metabib_class_ts_map m
3154 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
3155 LEFT JOIN config.coded_value_map ccvm ON (
3156 ccvm.ctype IN ('item_lang', 'language') AND
3157 ccvm.code = m.index_lang AND
3158 r.vlist @> intset(ccvm.id)
3160 WHERE m.field_class = TG_ARGV[0]
3162 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
3163 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
3165 SELECT DISTINCT m.ts_config, m.index_weight
3166 FROM config.metabib_field_ts_map m
3167 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
3168 LEFT JOIN config.coded_value_map ccvm ON (
3169 ccvm.ctype IN ('item_lang', 'language') AND
3170 ccvm.code = m.index_lang AND
3171 r.vlist @> intset(ccvm.id)
3173 WHERE m.metabib_field = NEW.field
3175 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
3176 ORDER BY index_weight ASC
3180 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
3181 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
3185 cur_weight = ts_rec.index_weight;
3186 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
3189 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
3191 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
3196 $$ LANGUAGE PLPGSQL;
3198 -- add new sr_format attribute definition
3200 INSERT INTO config.record_attr_definition (name, label, phys_char_sf)
3203 oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'),
3207 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
3208 (557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')),
3209 (558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')),
3210 (559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')),
3211 (560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')),
3212 (561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')),
3213 (562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')),
3214 (563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')),
3215 (586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')),
3216 (587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value'));
3218 INSERT INTO config.coded_value_map
3219 (id, ctype, code, value, search_label) VALUES
3220 (564, 'icon_format', 'book',
3221 oils_i18n_gettext(564, 'Book', 'ccvm', 'value'),
3222 oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')),
3223 (565, 'icon_format', 'braille',
3224 oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'),
3225 oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')),
3226 (566, 'icon_format', 'software',
3227 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'),
3228 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')),
3229 (567, 'icon_format', 'dvd',
3230 oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'),
3231 oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')),
3232 (568, 'icon_format', 'ebook',
3233 oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'),
3234 oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')),
3235 (569, 'icon_format', 'eaudio',
3236 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'),
3237 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')),
3238 (570, 'icon_format', 'kit',
3239 oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'),
3240 oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')),
3241 (571, 'icon_format', 'map',
3242 oils_i18n_gettext(571, 'Map', 'ccvm', 'value'),
3243 oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')),
3244 (572, 'icon_format', 'microform',
3245 oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'),
3246 oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')),
3247 (573, 'icon_format', 'score',
3248 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'),
3249 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')),
3250 (574, 'icon_format', 'picture',
3251 oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'),
3252 oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')),
3253 (575, 'icon_format', 'equip',
3254 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'),
3255 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')),
3256 (576, 'icon_format', 'serial',
3257 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'),
3258 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')),
3259 (577, 'icon_format', 'vhs',
3260 oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'),
3261 oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')),
3262 (578, 'icon_format', 'evideo',
3263 oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'),
3264 oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')),
3265 (579, 'icon_format', 'cdaudiobook',
3266 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'),
3267 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')),
3268 (580, 'icon_format', 'cdmusic',
3269 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'),
3270 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')),
3271 (581, 'icon_format', 'casaudiobook',
3272 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'),
3273 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')),
3274 (582, 'icon_format', 'casmusic',
3275 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'),
3276 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')),
3277 (583, 'icon_format', 'phonospoken',
3278 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'),
3279 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')),
3280 (584, 'icon_format', 'phonomusic',
3281 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'),
3282 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')),
3283 (585, 'icon_format', 'lpbook',
3284 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'),
3285 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label'))
3288 -- add the new icon format attribute definition
3290 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3294 'OPAC Format Icons Attribute',
3302 INSERT INTO config.record_attr_definition
3303 (name, label, multi, filter, composite) VALUES (
3307 'OPAC Format Icons',
3314 -- icon format composite definitions
3316 INSERT INTO config.composite_attr_entry_definition
3317 (coded_value, definition) VALUES
3319 (564, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":{"_not":[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"},{"_attr":"item_form","_val":"d"},{"_attr":"item_form","_val":"f"},{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"r"},{"_attr":"item_form","_val":"s"}]},"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'),
3322 (565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'),
3325 (566, '{"_attr":"item_type","_val":"m"}'),
3328 (567, '{"_attr":"vr_format","_val":"v"}'),
3331 (568, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}],"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'),
3334 (569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'),
3337 (570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'),
3340 (571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'),
3343 (572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'),
3346 (573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'),
3349 (574, '{"_attr":"item_type","_val":"k"}'),
3352 (575, '{"_attr":"item_type","_val":"r"}'),
3355 (576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'),
3358 (577, '{"_attr":"vr_format","_val":"b"}'),
3361 (578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'),
3364 (579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'),
3367 (580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'),
3370 (581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'),
3373 (582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'),
3376 (583, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"sr_format","_val":"a"},{"_attr":"sr_format","_val":"b"},{"_attr":"sr_format","_val":"c"},{"_attr":"sr_format","_val":"d"},{"_attr":"sr_format","_val":"e"}]}'),
3379 (584, '{"0":{"_attr":"item_type","_val":"j"},"1":[{"_attr":"sr_format","_val":"a"},{"_attr":"sr_format","_val":"b"},{"_attr":"sr_format","_val":"c"},{"_attr":"sr_format","_val":"d"},{"_attr":"sr_format","_val":"e"}]}'),
3382 (585, '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":{"_attr":"item_form","_val":"d"},"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}');
3387 CREATE OR REPLACE FUNCTION unapi.mra (
3393 depth INT DEFAULT NULL,
3394 slimit HSTORE DEFAULT NULL,
3395 soffset HSTORE DEFAULT NULL,
3396 include_xmlns BOOL DEFAULT TRUE
3397 ) RETURNS XML AS $F$
3401 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
3402 'tag:open-ils.org:U2@mra/' || $1 AS id,
3403 'tag:open-ils.org:U2@bre/' || $1 AS record
3405 (SELECT XMLAGG(foo.y)
3411 cvm.value AS "coded-value",
3420 FROM metabib.record_attr_flat mra
3421 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
3422 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
3427 $F$ LANGUAGE SQL STABLE;
3430 SELECT evergreen.upgrade_deps_block_check('0865', :eg_version);
3432 -- First, explode the field into constituent parts
3433 WITH format_parts_array AS (
3435 STRING_TO_ARRAY(a.holdable_formats, '-') AS parts
3436 FROM action.hold_request a
3437 WHERE a.hold_type = 'M'
3438 AND a.fulfillment_time IS NULL
3439 ), format_parts_wide AS (
3441 regexp_split_to_array(parts[1], '') AS item_type,
3442 regexp_split_to_array(parts[2], '') AS item_form,
3443 parts[3] AS item_lang
3444 FROM format_parts_array
3445 ), converted_formats_flat AS (
3447 CASE WHEN ARRAY_LENGTH(item_type,1) > 0
3448 THEN '"0":[{"_attr":"item_type","_val":"' || ARRAY_TO_STRING(item_type,'"},{"_attr":"item_type","_val":"') || '"}]'
3451 CASE WHEN ARRAY_LENGTH(item_form,1) > 0
3452 THEN '"1":[{"_attr":"item_form","_val":"' || ARRAY_TO_STRING(item_form,'"},{"_attr":"item_form","_val":"') || '"}]'
3455 CASE WHEN item_lang <> ''
3456 THEN '"2":[{"_attr":"item_lang","_val":"' || item_lang ||'"}]'
3459 FROM format_parts_wide
3460 ) UPDATE action.hold_request SET holdable_formats = '{' ||
3461 converted_formats_flat.item_type || ',' ||
3462 converted_formats_flat.item_form || ',' ||
3463 converted_formats_flat.item_lang || '}'
3464 FROM converted_formats_flat WHERE converted_formats_flat.id = action.hold_request.id;
3468 SELECT evergreen.upgrade_deps_block_check('0866', :eg_version);
3470 DROP FUNCTION asset.record_has_holdable_copy (BIGINT);
3471 CREATE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
3476 JOIN asset.call_number acn ON acp.call_number = acn.id
3477 JOIN asset.copy_location acpl ON acp.location = acpl.id
3478 JOIN config.copy_status ccs ON acp.status = ccs.id
3481 AND acp.holdable = true
3482 AND acpl.holdable = true
3483 AND ccs.holdable = true
3484 AND acp.deleted = false
3485 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
3492 $f$ LANGUAGE PLPGSQL;
3494 DROP FUNCTION asset.metarecord_has_holdable_copy (BIGINT);
3495 CREATE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
3500 JOIN asset.call_number acn ON acp.call_number = acn.id
3501 JOIN asset.copy_location acpl ON acp.location = acpl.id
3502 JOIN config.copy_status ccs ON acp.status = ccs.id
3503 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
3505 mmsm.metarecord = rid
3506 AND acp.holdable = true
3507 AND acpl.holdable = true
3508 AND ccs.holdable = true
3509 AND acp.deleted = false
3510 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
3517 $f$ LANGUAGE PLPGSQL;
3519 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
3524 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
3526 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
3531 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3535 actor.org_unit_descendants(ans.id) d
3536 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
3537 JOIN asset.copy cp ON (cp.id = av.copy_id)
3538 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
3542 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3549 $f$ LANGUAGE PLPGSQL;
3551 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
3556 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
3558 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
3563 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3567 actor.org_unit_descendants(ans.id) d
3568 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
3569 JOIN asset.copy cp ON (cp.id = av.copy_id)
3570 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
3574 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3581 $f$ LANGUAGE PLPGSQL;
3583 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
3588 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
3590 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
3595 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3599 actor.org_unit_descendants(ans.id) d
3600 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
3601 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
3602 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
3606 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3613 $f$ LANGUAGE PLPGSQL;
3615 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
3620 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
3622 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
3627 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3631 actor.org_unit_descendants(ans.id) d
3632 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
3633 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
3634 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
3638 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3645 $f$ LANGUAGE PLPGSQL;
3647 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
3653 depth INT DEFAULT NULL,
3654 slimit HSTORE DEFAULT NULL,
3655 soffset HSTORE DEFAULT NULL,
3656 include_xmlns BOOL DEFAULT TRUE,
3657 pref_lib INT DEFAULT NULL
3658 ) RETURNS XML AS $F$
3662 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
3663 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
3665 (SELECT XMLAGG(foo.y)
3667 SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id))
3668 COALESCE(cvm.id,uvm.id),
3673 cvm.value AS "coded-value",
3682 FROM metabib.record_attr_flat mra
3683 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
3684 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
3685 LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value)
3687 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id
3688 FROM actor.org_unit WHERE shortname = $5 LIMIT 1)
3690 FROM metabib.metarecord_source_map, aou
3691 WHERE metarecord = $1 AND (
3693 SELECT 1 FROM asset.opac_visible_copies
3694 WHERE record = source AND circ_lib IN (
3695 SELECT id FROM actor.org_unit_descendants(aou.id, $6))
3698 OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1)
3705 $F$ LANGUAGE SQL STABLE;
3707 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
3710 depth INT DEFAULT NULL,
3711 slimit HSTORE DEFAULT NULL,
3712 soffset HSTORE DEFAULT NULL,
3713 pref_lib INT DEFAULT NULL,
3714 includes TEXT[] DEFAULT NULL::TEXT[]
3715 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
3716 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
3717 SELECT acn.id, aou.name, acn.label_sortkey,
3718 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
3720 FROM asset.call_number acn
3721 JOIN asset.copy acp ON (acn.id = acp.call_number)
3722 JOIN actor.org_unit_descendants( $2, COALESCE(
3725 FROM actor.org_unit_type aout
3726 INNER JOIN actor.org_unit ou ON ou_type = aout.id
3729 ) AS aou ON (acp.circ_lib = aou.id)
3730 WHERE acn.record = ANY ($1)
3731 AND acn.deleted IS FALSE
3732 AND acp.deleted IS FALSE
3733 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
3736 FROM asset.opac_visible_copies
3737 WHERE copy_id = acp.id AND record = acn.record
3739 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
3741 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
3744 GROUP BY ua.id, ua.name, ua.label_sortkey
3745 ORDER BY rank, ua.name, ua.label_sortkey
3746 LIMIT ($4 -> 'acn')::INT
3747 OFFSET ($5 -> 'acn')::INT;
3749 LANGUAGE SQL STABLE;
3751 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
3752 ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] )
3753 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
3754 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
3757 CREATE OR REPLACE FUNCTION evergreen.located_uris (
3760 pref_lib INT DEFAULT NULL
3761 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
3762 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
3763 SELECT DISTINCT ON (id) * FROM (
3764 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
3765 FROM asset.call_number acn
3766 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
3767 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
3768 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
3769 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
3771 WHERE acn.record = ANY ($1)
3772 AND acn.deleted IS FALSE
3773 AND auri.active IS TRUE
3774 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL)
3776 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
3777 FROM asset.call_number acn
3778 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
3779 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
3780 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
3781 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
3783 WHERE acn.record = ANY ($1)
3784 AND acn.deleted IS FALSE
3785 AND auri.active IS TRUE
3786 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x
3787 ORDER BY id, pref_ou DESC;
3789 LANGUAGE SQL STABLE;
3791 CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
3792 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT)
3793 AS $$ SELECT * FROM evergreen.located_uris(ARRAY[$1],$2,$3) $$ LANGUAGE SQL STABLE;
3796 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
3800 depth INT DEFAULT NULL,
3801 includes TEXT[] DEFAULT NULL::TEXT[],
3802 slimit HSTORE DEFAULT NULL,
3803 soffset HSTORE DEFAULT NULL,
3804 include_xmlns BOOL DEFAULT TRUE,
3805 pref_lib INT DEFAULT NULL
3811 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3812 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
3813 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
3817 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
3820 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3822 FROM asset.opac_ou_metarecord_copy_count($2, $1)
3826 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3828 FROM asset.staff_ou_metarecord_copy_count($2, $1)
3832 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3834 FROM asset.opac_ou_metarecord_copy_count($9, $1)
3838 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
3841 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
3843 SELECT unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
3844 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
3847 SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey
3848 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
3851 CASE WHEN ('ssub' = ANY ($5)) THEN
3854 (SELECT XMLAGG(ssub) FROM (
3855 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
3856 FROM serial.subscription
3857 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
3862 $F$ LANGUAGE SQL STABLE;
3866 SELECT evergreen.upgrade_deps_block_check('0867', :eg_version);
3868 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3869 'opac.metarecord.holds.format_attr',
3871 'opac.metarecord.holds.format_attr',
3872 'OPAC Metarecord Hold Formats Attribute',
3880 -- until we have a custom attribute for the selector,
3881 -- default to the icon_format attribute
3882 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3883 'opac.format_selector.attr',
3885 'opac.format_selector.attr',
3886 'OPAC Format Selector Attribute',
3895 INSERT INTO config.record_attr_definition
3896 (name, label, multi, filter, composite)
3901 'Metarecord Hold Formats',
3908 -- these formats are a subset of the "icon_format" attribute,
3909 -- modified to exclude electronic resources, which are not holdable
3911 -- for i18n purposes, these have to be listed individually
3912 INSERT INTO config.coded_value_map
3913 (id, ctype, code, value, search_label) VALUES
3914 (588, 'mr_hold_format', 'book',
3915 oils_i18n_gettext(588, 'Book', 'ccvm', 'value'),
3916 oils_i18n_gettext(588, 'Book', 'ccvm', 'search_label')),
3917 (589, 'mr_hold_format', 'braille',
3918 oils_i18n_gettext(589, 'Braille', 'ccvm', 'value'),
3919 oils_i18n_gettext(589, 'Braille', 'ccvm', 'search_label')),
3920 (590, 'mr_hold_format', 'software',
3921 oils_i18n_gettext(590, 'Software and video games', 'ccvm', 'value'),
3922 oils_i18n_gettext(590, 'Software and video games', 'ccvm', 'search_label')),
3923 (591, 'mr_hold_format', 'dvd',
3924 oils_i18n_gettext(591, 'DVD', 'ccvm', 'value'),
3925 oils_i18n_gettext(591, 'DVD', 'ccvm', 'search_label')),
3926 (592, 'mr_hold_format', 'kit',
3927 oils_i18n_gettext(592, 'Kit', 'ccvm', 'value'),
3928 oils_i18n_gettext(592, 'Kit', 'ccvm', 'search_label')),
3929 (593, 'mr_hold_format', 'map',
3930 oils_i18n_gettext(593, 'Map', 'ccvm', 'value'),
3931 oils_i18n_gettext(593, 'Map', 'ccvm', 'search_label')),
3932 (594, 'mr_hold_format', 'microform',
3933 oils_i18n_gettext(594, 'Microform', 'ccvm', 'value'),
3934 oils_i18n_gettext(594, 'Microform', 'ccvm', 'search_label')),
3935 (595, 'mr_hold_format', 'score',
3936 oils_i18n_gettext(595, 'Music Score', 'ccvm', 'value'),
3937 oils_i18n_gettext(595, 'Music Score', 'ccvm', 'search_label')),
3938 (596, 'mr_hold_format', 'picture',
3939 oils_i18n_gettext(596, 'Picture', 'ccvm', 'value'),
3940 oils_i18n_gettext(596, 'Picture', 'ccvm', 'search_label')),
3941 (597, 'mr_hold_format', 'equip',
3942 oils_i18n_gettext(597, 'Equipment, games, toys', 'ccvm', 'value'),
3943 oils_i18n_gettext(597, 'Equipment, games, toys', 'ccvm', 'search_label')),
3944 (598, 'mr_hold_format', 'serial',
3945 oils_i18n_gettext(598, 'Serials and magazines', 'ccvm', 'value'),
3946 oils_i18n_gettext(598, 'Serials and magazines', 'ccvm', 'search_label')),
3947 (599, 'mr_hold_format', 'vhs',
3948 oils_i18n_gettext(599, 'VHS', 'ccvm', 'value'),
3949 oils_i18n_gettext(599, 'VHS', 'ccvm', 'search_label')),
3950 (600, 'mr_hold_format', 'cdaudiobook',
3951 oils_i18n_gettext(600, 'CD Audiobook', 'ccvm', 'value'),
3952 oils_i18n_gettext(600, 'CD Audiobook', 'ccvm', 'search_label')),
3953 (601, 'mr_hold_format', 'cdmusic',
3954 oils_i18n_gettext(601, 'CD Music recording', 'ccvm', 'value'),
3955 oils_i18n_gettext(601, 'CD Music recording', 'ccvm', 'search_label')),
3956 (602, 'mr_hold_format', 'casaudiobook',
3957 oils_i18n_gettext(602, 'Cassette audiobook', 'ccvm', 'value'),
3958 oils_i18n_gettext(602, 'Cassette audiobook', 'ccvm', 'search_label')),
3959 (603, 'mr_hold_format', 'casmusic',
3960 oils_i18n_gettext(603, 'Audiocassette music recording', 'ccvm', 'value'),
3961 oils_i18n_gettext(603, 'Audiocassette music recording', 'ccvm', 'search_label')),
3962 (604, 'mr_hold_format', 'phonospoken',
3963 oils_i18n_gettext(604, 'Phonograph spoken recording', 'ccvm', 'value'),
3964 oils_i18n_gettext(604, 'Phonograph spoken recording', 'ccvm', 'search_label')),
3965 (605, 'mr_hold_format', 'phonomusic',
3966 oils_i18n_gettext(605, 'Phonograph music recording', 'ccvm', 'value'),
3967 oils_i18n_gettext(605, 'Phonograph music recording', 'ccvm', 'search_label')),
3968 (606, 'mr_hold_format', 'lpbook',
3969 oils_i18n_gettext(606, 'Large Print Book', 'ccvm', 'value'),
3970 oils_i18n_gettext(606, 'Large Print Book', 'ccvm', 'search_label'))
3973 -- but we can auto-generate the composite definitions
3976 DECLARE format TEXT;
3978 FOR format IN SELECT UNNEST(
3979 '{book,braille,software,dvd,kit,map,microform,score,picture,equip,serial,vhs,cdaudiobook,cdmusic,casaudiobook,casmusic,phonospoken,phonomusic,lpbook}'::text[]) LOOP
3981 INSERT INTO config.composite_attr_entry_definition
3982 (coded_value, definition) VALUES
3984 -- get the ID from the new ccvm above
3985 (SELECT id FROM config.coded_value_map
3986 WHERE code = format AND ctype = 'mr_hold_format'),
3987 -- get the def of the matching ccvm attached to the icon_format attr
3988 (SELECT definition FROM config.composite_attr_entry_definition ccaed
3989 JOIN config.coded_value_map ccvm ON (ccaed.coded_value = ccvm.id)
3990 WHERE ccvm.ctype = 'icon_format' AND ccvm.code = format)
3995 INSERT INTO config.coded_value_map
3996 (id, ctype, code, value, search_label) VALUES
3997 (607, 'icon_format', 'music',
3998 oils_i18n_gettext(607, 'Musical Sound Recording (Unknown Format)', 'ccvm', 'value'),
3999 oils_i18n_gettext(607, 'Musical Sound Recording (Unknown Format)', 'ccvm', 'search_label'));
4001 INSERT INTO config.composite_attr_entry_definition
4002 (coded_value, definition) VALUES
4003 (607, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_not":[{"_attr":"sr_format","_val":"a"},{"_attr":"sr_format","_val":"b"},{"_attr":"sr_format","_val":"c"},{"_attr":"sr_format","_val":"d"},{"_attr":"sr_format","_val":"f"},{"_attr":"sr_format","_val":"e"},{"_attr":"sr_format","_val":"l"}]}}');
4006 INSERT INTO config.coded_value_map
4007 (id, ctype, code, value, search_label) VALUES
4008 (608, 'icon_format', 'blu-ray',
4009 oils_i18n_gettext(608, 'Blu-ray', 'ccvm', 'value'),
4010 oils_i18n_gettext(608, 'Blu-ray', 'ccvm', 'search_label'));
4011 INSERT INTO config.composite_attr_entry_definition
4012 (coded_value, definition) VALUES (608, '{"_attr":"vr_format","_val":"s"}');
4014 -- metarecord hold format for blu-ray
4015 INSERT INTO config.coded_value_map
4016 (id, ctype, code, value, search_label) VALUES
4017 (609, 'mr_hold_format', 'blu-ray',
4018 oils_i18n_gettext(609, 'Blu-ray', 'ccvm', 'value'),
4019 oils_i18n_gettext(609, 'Blu-ray', 'ccvm', 'search_label'));
4020 INSERT INTO config.composite_attr_entry_definition
4021 (coded_value, definition) VALUES (609, '{"_attr":"vr_format","_val":"s"}');
4025 SELECT evergreen.upgrade_deps_block_check('0869', :eg_version);
4027 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
4029 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
4032 $f$ LANGUAGE PLPGSQL;
4034 CREATE TRIGGER hold_copy_proximity_update_tgr BEFORE INSERT OR UPDATE ON action.hold_copy_map FOR EACH ROW EXECUTE PROCEDURE action.hold_copy_calculated_proximity_update ();
4036 -- Now, cause the update we need in a HOT-friendly manner (http://pgsql.tapoueh.org/site/html/misc/hot.html)
4037 UPDATE action.hold_copy_map SET proximity = proximity WHERE proximity IS NULL;
4041 * Copyright (C) 2014 Equinox Software, Inc.
4042 * Mike Rylander <miker@esilibrary.com>
4044 * This program is free software; you can redistribute it and/or
4045 * modify it under the terms of the GNU General Public License
4046 * as published by the Free Software Foundation; either version 2
4047 * of the License, or (at your option) any later version.
4049 * This program is distributed in the hope that it will be useful,
4050 * but WITHOUT ANY WARRANTY; without even the implied warranty of
4051 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
4052 * GNU General Public License for more details.
4058 SELECT evergreen.upgrade_deps_block_check('0870', :eg_version);
4060 CREATE OR REPLACE FUNCTION evergreen.located_uris (
4063 pref_lib INT DEFAULT NULL
4064 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
4065 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
4066 SELECT DISTINCT ON (id) * FROM (
4067 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
4068 FROM asset.call_number acn
4069 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
4070 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
4071 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
4072 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
4074 WHERE acn.record = ANY ($1)
4075 AND acn.deleted IS FALSE
4076 AND auri.active IS TRUE
4077 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL))
4079 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
4080 FROM asset.call_number acn
4081 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
4082 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
4083 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
4084 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
4086 WHERE acn.record = ANY ($1)
4087 AND acn.deleted IS FALSE
4088 AND auri.active IS TRUE
4089 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL)))x
4090 ORDER BY id, pref_ou DESC;
4092 LANGUAGE SQL STABLE;
4097 SELECT evergreen.upgrade_deps_block_check('0871', :eg_version);
4099 INSERT INTO config.record_attr_definition
4100 (name, label, multi, filter, composite) VALUES (
4102 oils_i18n_gettext('search_format', 'Search Formats', 'crad', 'label'),
4106 INSERT INTO config.coded_value_map
4107 (id, ctype, code, value, search_label) VALUES
4108 (610, 'search_format', 'book',
4109 oils_i18n_gettext(610, 'All Books', 'ccvm', 'value'),
4110 oils_i18n_gettext(610, 'All Books', 'ccvm', 'search_label')),
4111 (611, 'search_format', 'braille',
4112 oils_i18n_gettext(611, 'Braille', 'ccvm', 'value'),
4113 oils_i18n_gettext(611, 'Braille', 'ccvm', 'search_label')),
4114 (612, 'search_format', 'software',
4115 oils_i18n_gettext(612, 'Software and video games', 'ccvm', 'value'),
4116 oils_i18n_gettext(612, 'Software and video games', 'ccvm', 'search_label')),
4117 (613, 'search_format', 'dvd',
4118 oils_i18n_gettext(613, 'DVD', 'ccvm', 'value'),
4119 oils_i18n_gettext(613, 'DVD', 'ccvm', 'search_label')),
4120 (614, 'search_format', 'ebook',
4121 oils_i18n_gettext(614, 'E-book', 'ccvm', 'value'),
4122 oils_i18n_gettext(614, 'E-book', 'ccvm', 'search_label')),
4123 (615, 'search_format', 'eaudio',
4124 oils_i18n_gettext(615, 'E-audio', 'ccvm', 'value'),
4125 oils_i18n_gettext(615, 'E-audio', 'ccvm', 'search_label')),
4126 (616, 'search_format', 'kit',
4127 oils_i18n_gettext(616, 'Kit', 'ccvm', 'value'),
4128 oils_i18n_gettext(616, 'Kit', 'ccvm', 'search_label')),
4129 (617, 'search_format', 'map',
4130 oils_i18n_gettext(617, 'Map', 'ccvm', 'value'),
4131 oils_i18n_gettext(617, 'Map', 'ccvm', 'search_label')),
4132 (618, 'search_format', 'microform',
4133 oils_i18n_gettext(618, 'Microform', 'ccvm', 'value'),
4134 oils_i18n_gettext(618, 'Microform', 'ccvm', 'search_label')),
4135 (619, 'search_format', 'score',
4136 oils_i18n_gettext(619, 'Music Score', 'ccvm', 'value'),
4137 oils_i18n_gettext(619, 'Music Score', 'ccvm', 'search_label')),
4138 (620, 'search_format', 'picture',
4139 oils_i18n_gettext(620, 'Picture', 'ccvm', 'value'),
4140 oils_i18n_gettext(620, 'Picture', 'ccvm', 'search_label')),
4141 (621, 'search_format', 'equip',
4142 oils_i18n_gettext(621, 'Equipment, games, toys', 'ccvm', 'value'),
4143 oils_i18n_gettext(621, 'Equipment, games, toys', 'ccvm', 'search_label')),
4144 (622, 'search_format', 'serial',
4145 oils_i18n_gettext(622, 'Serials and magazines', 'ccvm', 'value'),
4146 oils_i18n_gettext(622, 'Serials and magazines', 'ccvm', 'search_label')),
4147 (623, 'search_format', 'vhs',
4148 oils_i18n_gettext(623, 'VHS', 'ccvm', 'value'),
4149 oils_i18n_gettext(623, 'VHS', 'ccvm', 'search_label')),
4150 (624, 'search_format', 'evideo',
4151 oils_i18n_gettext(624, 'E-video', 'ccvm', 'value'),
4152 oils_i18n_gettext(624, 'E-video', 'ccvm', 'search_label')),
4153 (625, 'search_format', 'cdaudiobook',
4154 oils_i18n_gettext(625, 'CD Audiobook', 'ccvm', 'value'),
4155 oils_i18n_gettext(625, 'CD Audiobook', 'ccvm', 'search_label')),
4156 (626, 'search_format', 'cdmusic',
4157 oils_i18n_gettext(626, 'CD Music recording', 'ccvm', 'value'),
4158 oils_i18n_gettext(626, 'CD Music recording', 'ccvm', 'search_label')),
4159 (627, 'search_format', 'casaudiobook',
4160 oils_i18n_gettext(627, 'Cassette audiobook', 'ccvm', 'value'),
4161 oils_i18n_gettext(627, 'Cassette audiobook', 'ccvm', 'search_label')),
4162 (628, 'search_format', 'casmusic',
4163 oils_i18n_gettext(628, 'Audiocassette music recording', 'ccvm', 'value'),
4164 oils_i18n_gettext(628, 'Audiocassette music recording', 'ccvm', 'search_label')),
4165 (629, 'search_format', 'phonospoken',
4166 oils_i18n_gettext(629, 'Phonograph spoken recording', 'ccvm', 'value'),
4167 oils_i18n_gettext(629, 'Phonograph spoken recording', 'ccvm', 'search_label')),
4168 (630, 'search_format', 'phonomusic',
4169 oils_i18n_gettext(630, 'Phonograph music recording', 'ccvm', 'value'),
4170 oils_i18n_gettext(630, 'Phonograph music recording', 'ccvm', 'search_label')),
4171 (631, 'search_format', 'lpbook',
4172 oils_i18n_gettext(631, 'Large Print Book', 'ccvm', 'value'),
4173 oils_i18n_gettext(631, 'Large Print Book', 'ccvm', 'search_label')),
4174 (632, 'search_format', 'music',
4175 oils_i18n_gettext(632, 'All Music', 'ccvm', 'label'),
4176 oils_i18n_gettext(632, 'All Music', 'ccvm', 'search_label')),
4177 (633, 'search_format', 'blu-ray',
4178 oils_i18n_gettext(633, 'Blu-ray', 'ccvm', 'value'),
4179 oils_i18n_gettext(633, 'Blu-ray', 'ccvm', 'search_label'));
4183 -- copy the composite definition from icon_format into
4184 -- search_format for a baseline data set
4186 DECLARE format config.coded_value_map%ROWTYPE;
4188 FOR format IN SELECT *
4189 FROM config.coded_value_map WHERE ctype = 'icon_format'
4191 INSERT INTO config.composite_attr_entry_definition
4192 (coded_value, definition) VALUES
4194 -- get the ID from the new ccvm above
4195 (SELECT id FROM config.coded_value_map
4196 WHERE code = format.code AND ctype = 'search_format'),
4198 -- def of the matching icon_format attr
4199 (SELECT definition FROM config.composite_attr_entry_definition
4200 WHERE coded_value = format.id)
4205 -- modify the 'book' definition so that it includes large print
4206 UPDATE config.composite_attr_entry_definition
4207 SET definition = '{"0":[{"_attr":"item_type","_val":"a"},{"_attr":"item_type","_val":"t"}],"1":{"_not":[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"},{"_attr":"item_form","_val":"f"},{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"r"},{"_attr":"item_form","_val":"s"}]},"2":[{"_attr":"bib_level","_val":"a"},{"_attr":"bib_level","_val":"c"},{"_attr":"bib_level","_val":"d"},{"_attr":"bib_level","_val":"m"}]}'
4208 WHERE coded_value = 610;
4210 -- modify 'music' to include all recorded music, regardless of format
4211 UPDATE config.composite_attr_entry_definition
4212 SET definition = '{"_attr":"item_type","_val":"j"}'
4213 WHERE coded_value = 632;
4215 UPDATE config.global_flag
4216 SET value = 'search_format'
4217 WHERE name = 'opac.format_selector.attr';
4221 SELECT evergreen.upgrade_deps_block_check('0872', :eg_version);
4223 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT, bib_is_deleted BOOL DEFAULT FALSE, retain_deleted BOOL DEFAULT FALSE ) RETURNS BIGINT AS $func$
4225 new_mapping BOOL := TRUE;
4228 tmp_mr metabib.metarecord%ROWTYPE;
4229 deleted_mrs BIGINT[];
4232 -- We need to make sure we're not a deleted master record of an MR
4233 IF bib_is_deleted THEN
4234 FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
4236 IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
4237 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
4240 -- Now, are there any more sources on this MR?
4241 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
4243 IF source_count = 0 AND NOT retain_deleted THEN -- No other records
4244 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
4245 DELETE FROM metabib.metarecord WHERE id = old_mr;
4247 ELSE -- indeed there are. Update it with a null cache and recalcualated master record
4248 UPDATE metabib.metarecord
4250 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4255 ELSE -- insert or update
4257 FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP
4259 -- Find the first fingerprint-matching
4260 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
4261 old_mr := tmp_mr.id;
4262 new_mapping := FALSE;
4264 ELSE -- Our fingerprint changed ... maybe remove the old MR
4265 DELETE FROM metabib.metarecord_source_map WHERE metarecord = old_mr AND source = bib_id; -- remove the old source mapping
4266 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
4267 IF source_count = 0 THEN -- No other records
4268 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
4269 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
4275 -- we found no suitable, preexisting MR based on old source maps
4276 IF old_mr IS NULL THEN
4277 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
4279 IF old_mr IS NULL THEN -- nope, create one and grab its id
4280 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
4281 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
4283 ELSE -- indeed there is. update it with a null cache and recalcualated master record
4284 UPDATE metabib.metarecord
4286 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4290 ELSE -- there was one we already attached to, update its mods cache and master_record
4291 UPDATE metabib.metarecord
4293 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4298 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
4303 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
4304 UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
4310 $func$ LANGUAGE PLPGSQL;
4312 DROP FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT );
4314 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
4319 IF NEW.deleted THEN -- If this bib is deleted
4321 PERFORM * FROM config.internal_flag WHERE
4322 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
4324 tmp_bool := FOUND; -- Just in case this is changed by some other statement
4326 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
4328 IF NOT tmp_bool THEN
4329 -- One needs to keep these around to support searches
4330 -- with the #deleted modifier, so one should turn on the named
4331 -- internal flag for that functionality.
4332 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
4335 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
4336 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
4337 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
4338 RETURN NEW; -- and we're done
4341 IF TG_OP = 'UPDATE' THEN -- re-ingest?
4342 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
4344 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
4349 -- Record authority linking
4350 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
4352 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
4355 -- Flatten and insert the mfr data
4356 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
4358 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
4360 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
4361 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
4363 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
4367 -- Gather and insert the field entry data
4368 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
4370 -- Located URI magic
4371 IF TG_OP = 'INSERT' THEN
4372 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4374 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4377 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4379 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4383 -- (re)map metarecord-bib linking
4384 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
4385 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
4387 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4389 ELSE -- we're doing an update, and we're not deleted, remap
4390 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
4392 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4398 $func$ LANGUAGE PLPGSQL;
4400 CREATE OR REPLACE FUNCTION unapi.mmr (
4406 depth INT DEFAULT NULL,
4407 slimit HSTORE DEFAULT NULL,
4408 soffset HSTORE DEFAULT NULL,
4409 include_xmlns BOOL DEFAULT TRUE,
4410 pref_lib INT DEFAULT NULL
4414 mmrec metabib.metarecord%ROWTYPE;
4415 leadrec biblio.record_entry%ROWTYPE;
4416 subrec biblio.record_entry%ROWTYPE;
4417 layout unapi.bre_output_layout%ROWTYPE;
4418 xfrm config.xml_transform%ROWTYPE;
4420 xml_buf TEXT; -- growing XML document
4421 tmp_xml TEXT; -- single-use XML string
4422 xml_frag TEXT; -- single-use XML fragment
4427 subxml XML; -- subordinate records elements
4432 -- xpath for extracting bre.marc values from subordinate records
4433 -- so they may be appended to the MARC of the master record prior
4434 -- to XSLT processing.
4435 -- subjects, isbn, issn, upc -- anything else?
4437 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
4439 IF org = '-' OR org IS NULL THEN
4440 SELECT shortname INTO org FROM evergreen.org_top();
4443 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
4445 IF ouid IS NULL THEN
4449 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
4454 -- TODO: aggregate holdings from constituent records
4455 IF format = 'holdings_xml' THEN -- the special case
4456 output := unapi.mmr_holdings_xml(
4457 obj_id, ouid, org, depth,
4458 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
4459 slimit, soffset, include_xmlns, pref_lib);
4463 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
4465 IF layout.name IS NULL THEN
4469 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
4471 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
4473 -- Grab distinct MVF for all records if requested
4474 IF ('mra' = ANY (includes)) THEN
4475 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
4480 xml_buf = leadrec.marc;
4483 IF ('holdings_xml' = ANY (includes)) THEN
4484 hxml := unapi.mmr_holdings_xml(
4485 obj_id, ouid, org, depth,
4486 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
4487 slimit, soffset, include_xmlns, pref_lib);
4490 subxml := NULL::XML;
4491 parts := '{}'::TEXT[];
4492 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
4493 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
4494 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
4495 WHERE mmr.id = obj_id AND NOT bre.deleted
4496 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
4497 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
4499 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
4500 -- Append choice data from the the non-lead records to the
4501 -- the lead record document
4503 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
4506 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
4508 -- append data from the subordinate records to the
4509 -- main record document before applying the XSLT
4511 IF subxml IS NOT NULL THEN
4512 xml_buf := REGEXP_REPLACE(xml_buf,
4513 '</record>(.*?)$', subxml || '</record>' || E'\\1');
4516 IF format = 'marcxml' THEN
4517 -- If we're not using the prefixed namespace in
4518 -- this record, then remove all declarations of it
4519 IF xml_buf !~ E'<marc:' THEN
4520 xml_buf := REGEXP_REPLACE(xml_buf,
4521 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
4524 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
4527 -- update top_el to reflect the change in xml_buf, which may
4528 -- now be a different type of document (e.g. record -> mods)
4529 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
4530 layout.holdings_element || ').*$', E'\\1');
4532 IF axml IS NOT NULL THEN
4533 xml_buf := REGEXP_REPLACE(xml_buf,
4534 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
4537 IF hxml IS NOT NULL THEN
4538 xml_buf := REGEXP_REPLACE(xml_buf,
4539 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
4542 IF ('mmr.unapi' = ANY (includes)) THEN
4543 output := REGEXP_REPLACE(
4545 '</' || top_el || '>(.*?)',
4549 'http://www.w3.org/1999/xhtml' AS xmlns,
4550 'unapi-id' AS class,
4551 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
4553 )::TEXT || '</' || top_el || E'>\\1'
4559 -- remove ignorable whitesace
4560 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
4563 $F$ LANGUAGE PLPGSQL STABLE;
4565 -- Forcibly remap deleted master records, retaining the linkage if so configured.
4566 SELECT count(metabib.remap_metarecord_for_bib( bre.id, bre.fingerprint, TRUE, COALESCE(flag.enabled,FALSE)))
4567 FROM metabib.metarecord metar
4568 JOIN biblio.record_entry bre ON bre.id = metar.master_record,
4569 config.internal_flag flag
4570 WHERE bre.deleted = TRUE AND flag.name = 'ingest.metarecord_mapping.preserve_on_delete';
4574 SELECT evergreen.upgrade_deps_block_check('0873', :eg_version);
4576 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer)
4580 requestor_object actor.usr%ROWTYPE;
4581 user_object actor.usr%ROWTYPE;
4582 item_object asset.copy%ROWTYPE;
4583 item_cn_object asset.call_number%ROWTYPE;
4584 my_item_age INTERVAL;
4585 rec_descriptor metabib.rec_descriptor%ROWTYPE;
4586 matchpoint config.hold_matrix_matchpoint%ROWTYPE;
4587 weights config.hold_matrix_weights%ROWTYPE;
4588 denominator NUMERIC(6,2);
4589 v_pickup_ou ALIAS FOR pickup_ou;
4590 v_request_ou ALIAS FOR request_ou;
4592 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
4593 SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
4594 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
4595 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4596 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = item_cn_object.record;
4598 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
4600 -- The item's owner should probably be the one determining if the item is holdable
4601 -- How to decide that is debatable. Decided to default to the circ library (where the item lives)
4602 -- This flag will allow for setting it to the owning library (where the call number "lives")
4603 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled;
4605 -- Grab the closest set circ weight setting.
4607 -- Default to circ library
4608 SELECT INTO weights hw.*
4609 FROM config.weight_assoc wa
4610 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
4611 JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id)
4616 -- Flag is set, use owning library
4617 SELECT INTO weights hw.*
4618 FROM config.weight_assoc wa
4619 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
4620 JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) d ON (wa.org_unit = d.id)
4626 -- No weights? Bad admin! Defaults to handle that anyway.
4627 IF weights.id IS NULL THEN
4628 weights.user_home_ou := 5.0;
4629 weights.request_ou := 5.0;
4630 weights.pickup_ou := 5.0;
4631 weights.item_owning_ou := 5.0;
4632 weights.item_circ_ou := 5.0;
4633 weights.usr_grp := 7.0;
4634 weights.requestor_grp := 8.0;
4635 weights.circ_modifier := 4.0;
4636 weights.marc_type := 3.0;
4637 weights.marc_form := 2.0;
4638 weights.marc_bib_level := 1.0;
4639 weights.marc_vr_format := 1.0;
4640 weights.juvenile_flag := 4.0;
4641 weights.ref_flag := 0.0;
4642 weights.item_age := 0.0;
4645 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
4646 -- If you break your org tree with funky parenting this may be wrong
4647 -- Note: This CTE is duplicated in the find_circ_matrix_matchpoint function, and it may be a good idea to split it off to a function
4648 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
4649 WITH all_distance(distance) AS (
4650 SELECT depth AS distance FROM actor.org_unit_type
4652 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
4654 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
4656 -- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids.
4657 -- This may be better implemented as part of the upgrade script?
4658 -- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set
4659 -- Then remove this flag, of course.
4660 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;
4663 -- Note: This, to me, is REALLY hacky. I put it in anyway.
4664 -- If you can't tell, this is a single call swap on two variables.
4665 SELECT INTO user_object.profile, requestor_object.profile
4666 requestor_object.profile, user_object.profile;
4669 -- Select the winning matchpoint into the matchpoint variable for returning
4670 SELECT INTO matchpoint m.*
4671 FROM config.hold_matrix_matchpoint m
4672 /*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id
4673 LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id
4674 LEFT JOIN actor.org_unit_ancestors_distance( v_pickup_ou ) puoua ON m.pickup_ou = puoua.id
4675 LEFT JOIN actor.org_unit_ancestors_distance( v_request_ou ) rqoua ON m.request_ou = rqoua.id
4676 LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id
4677 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id
4678 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
4680 -- Permission Groups
4681 -- AND (m.requestor_grp IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group?
4682 AND (m.usr_grp IS NULL OR upgad.id IS NOT NULL)
4684 AND (m.pickup_ou IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match)))
4685 AND (m.request_ou IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match)))
4686 AND (m.item_owning_ou IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match)))
4687 AND (m.item_circ_ou IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match)))
4688 AND (m.user_home_ou IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match)))
4689 -- Static User Checks
4690 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
4691 -- Static Item Checks
4692 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
4693 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
4694 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
4695 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
4696 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
4697 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
4698 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
4700 -- Permission Groups
4701 CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END +
4702 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END +
4704 CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END +
4705 CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END +
4706 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END +
4707 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END +
4708 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
4709 -- Static User Checks -- Note: 4^x is equiv to 2^(2*x)
4710 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
4711 -- Static Item Checks
4712 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
4713 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
4714 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
4715 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
4716 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
4717 -- Item age has a slight adjustment to weight based on value.
4718 -- This should ensure that a shorter age limit comes first when all else is equal.
4719 -- NOTE: This assumes that intervals will normally be in days.
4720 CASE WHEN m.item_age IS NOT NULL THEN 4^weights.item_age - 86400/EXTRACT(EPOCH FROM m.item_age) ELSE 0.0 END DESC,
4721 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
4722 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
4725 -- Return just the ID for now
4726 RETURN matchpoint.id;
4728 $func$ LANGUAGE 'plpgsql';
4730 CREATE OR REPLACE FUNCTION action.hold_request_permit_test( pickup_ou INT, request_ou INT, match_item BIGINT, match_user INT, match_requestor INT, retargetting BOOL ) RETURNS SETOF action.matrix_test_result AS $func$
4733 user_object actor.usr%ROWTYPE;
4734 age_protect_object config.rule_age_hold_protect%ROWTYPE;
4735 standing_penalty config.standing_penalty%ROWTYPE;
4736 transit_range_ou_type actor.org_unit_type%ROWTYPE;
4737 transit_source actor.org_unit%ROWTYPE;
4738 item_object asset.copy%ROWTYPE;
4739 item_cn_object asset.call_number%ROWTYPE;
4740 item_status_object config.copy_status%ROWTYPE;
4741 item_location_object asset.copy_location%ROWTYPE;
4742 ou_skip actor.org_unit_setting%ROWTYPE;
4743 result action.matrix_test_result;
4744 hold_test config.hold_matrix_matchpoint%ROWTYPE;
4745 use_active_date TEXT;
4746 age_protect_date TIMESTAMP WITH TIME ZONE;
4748 hold_transit_prox INT;
4749 frozen_hold_count INT;
4750 context_org_list INT[];
4753 v_pickup_ou ALIAS FOR pickup_ou;
4754 v_request_ou ALIAS FOR request_ou;
4756 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
4757 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( v_pickup_ou );
4759 result.success := TRUE;
4761 -- The HOLD penalty block only applies to new holds.
4762 -- The CAPTURE penalty block applies to existing holds.
4763 hold_penalty := 'HOLD';
4764 IF retargetting THEN
4765 hold_penalty := 'CAPTURE';
4768 -- Fail if we couldn't find a user
4769 IF user_object.id IS NULL THEN
4770 result.fail_part := 'no_user';
4771 result.success := FALSE;
4777 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
4779 -- Fail if we couldn't find a copy
4780 IF item_object.id IS NULL THEN
4781 result.fail_part := 'no_item';
4782 result.success := FALSE;
4788 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(v_pickup_ou, v_request_ou, match_item, match_user, match_requestor);
4789 result.matchpoint := matchpoint_id;
4791 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
4793 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
4794 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
4795 result.fail_part := 'circ.holds.target_skip_me';
4796 result.success := FALSE;
4802 -- Fail if user is barred
4803 IF user_object.barred IS TRUE THEN
4804 result.fail_part := 'actor.usr.barred';
4805 result.success := FALSE;
4811 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4812 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
4813 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
4815 -- Fail if we couldn't find any matchpoint (requires a default)
4816 IF matchpoint_id IS NULL THEN
4817 result.fail_part := 'no_matchpoint';
4818 result.success := FALSE;
4824 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
4826 IF hold_test.holdable IS FALSE THEN
4827 result.fail_part := 'config.hold_matrix_test.holdable';
4828 result.success := FALSE;
4833 IF item_object.holdable IS FALSE THEN
4834 result.fail_part := 'item.holdable';
4835 result.success := FALSE;
4840 IF item_status_object.holdable IS FALSE THEN
4841 result.fail_part := 'status.holdable';
4842 result.success := FALSE;
4847 IF item_location_object.holdable IS FALSE THEN
4848 result.fail_part := 'location.holdable';
4849 result.success := FALSE;
4854 IF hold_test.transit_range IS NOT NULL THEN
4855 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
4856 IF hold_test.distance_is_from_owner THEN
4857 SELECT INTO transit_source ou.* FROM actor.org_unit ou JOIN asset.call_number cn ON (cn.owning_lib = ou.id) WHERE cn.id = item_object.call_number;
4859 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
4862 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = v_pickup_ou;
4865 result.fail_part := 'transit_range';
4866 result.success := FALSE;
4872 FOR standing_penalty IN
4873 SELECT DISTINCT csp.*
4874 FROM actor.usr_standing_penalty usp
4875 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
4876 WHERE usr = match_user
4877 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
4878 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
4879 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
4881 result.fail_part := standing_penalty.name;
4882 result.success := FALSE;
4887 IF hold_test.stop_blocked_user IS TRUE THEN
4888 FOR standing_penalty IN
4889 SELECT DISTINCT csp.*
4890 FROM actor.usr_standing_penalty usp
4891 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
4892 WHERE usr = match_user
4893 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
4894 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
4895 AND csp.block_list LIKE '%CIRC%' LOOP
4897 result.fail_part := standing_penalty.name;
4898 result.success := FALSE;
4904 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
4905 SELECT INTO hold_count COUNT(*)
4906 FROM action.hold_request
4907 WHERE usr = match_user
4908 AND fulfillment_time IS NULL
4909 AND cancel_time IS NULL
4910 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
4912 IF hold_count >= hold_test.max_holds THEN
4913 result.fail_part := 'config.hold_matrix_test.max_holds';
4914 result.success := FALSE;
4920 IF item_object.age_protect IS NOT NULL THEN
4921 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
4922 IF hold_test.distance_is_from_owner THEN
4923 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
4925 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
4927 IF use_active_date = 'true' THEN
4928 age_protect_date := COALESCE(item_object.active_date, NOW());
4930 age_protect_date := item_object.create_date;
4932 IF age_protect_date + age_protect_object.age > NOW() THEN
4933 IF hold_test.distance_is_from_owner THEN
4934 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4935 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = v_pickup_ou;
4937 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = v_pickup_ou;
4940 IF hold_transit_prox > age_protect_object.prox THEN
4941 result.fail_part := 'config.rule_age_hold_protect.prox';
4942 result.success := FALSE;
4955 $func$ LANGUAGE plpgsql;
4959 SELECT evergreen.upgrade_deps_block_check('0874', :eg_version);
4961 DROP FUNCTION IF EXISTS evergreen.oils_xpath( TEXT, TEXT, ANYARRAY);
4962 DROP FUNCTION IF EXISTS public.oils_xpath(TEXT, TEXT, ANYARRAY);
4963 DROP FUNCTION IF EXISTS public.oils_xpath(TEXT, TEXT);
4964 DROP FUNCTION IF EXISTS public.oils_xslt_process(TEXT, TEXT);
4966 CREATE OR REPLACE FUNCTION evergreen.xml_famous5_to_text( TEXT ) RETURNS TEXT AS $f$
4971 REPLACE( $1, '<', '<'),
4984 $f$ LANGUAGE SQL IMMUTABLE;
4986 CREATE OR REPLACE FUNCTION evergreen.oils_xpath ( TEXT, TEXT, TEXT[] ) RETURNS TEXT[] AS $f$
4988 CASE WHEN strpos(x,'<') = 1 THEN -- It's an element node
4990 ELSE -- it's text-ish
4991 evergreen.xml_famous5_to_text(x)
4994 FROM UNNEST(XPATH( $1, $2::XML, $3 )::TEXT[]) x;
4995 $f$ LANGUAGE SQL IMMUTABLE;
4997 -- Trust me, it's just simpler to duplicate these...
4998 CREATE OR REPLACE FUNCTION evergreen.oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS $f$
5000 CASE WHEN strpos(x,'<') = 1 THEN -- It's an element node
5002 ELSE -- it's text-ish
5003 evergreen.xml_famous5_to_text(x)
5006 FROM UNNEST(XPATH( $1, $2::XML)::TEXT[]) x;
5007 $f$ LANGUAGE SQL IMMUTABLE;
5009 CREATE OR REPLACE FUNCTION evergreen.oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
5018 # The following approach uses the older XML::LibXML 1.69 / XML::LibXSLT 1.68
5019 # methods of parsing XML documents and stylesheets, in the hopes of broader
5020 # compatibility with distributions
5021 my $parser = $_SHARED{'_xslt_process'}{parsers}{xml} || XML::LibXML->new();
5023 # Cache the XML parser, if we do not already have one
5024 $_SHARED{'_xslt_process'}{parsers}{xml} = $parser
5025 unless ($_SHARED{'_xslt_process'}{parsers}{xml});
5027 my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
5029 # Cache the XSLT processor, if we do not already have one
5030 $_SHARED{'_xslt_process'}{parsers}{xslt} = $xslt_parser
5031 unless ($_SHARED{'_xslt_process'}{parsers}{xslt});
5033 my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
5034 $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
5036 $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
5037 unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
5039 return $stylesheet->output_string(
5040 $stylesheet->transform(
5041 $parser->parse_string($doc)
5045 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
5047 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5049 res authority.simple_heading%ROWTYPE;
5050 acsaf authority.control_set_authority_field%ROWTYPE;
5061 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
5064 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5066 IF cset IS NULL THEN
5067 SELECT control_set INTO cset
5068 FROM authority.control_set_authority_field
5069 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5073 res.record := auth_id;
5075 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5077 res.atag := acsaf.id;
5078 tag_used := acsaf.tag;
5079 nfi_used := acsaf.nfi;
5080 joiner_text := COALESCE(acsaf.joiner, ' ');
5082 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
5084 heading_text := COALESCE(
5085 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
5089 IF nfi_used IS NOT NULL THEN
5091 sort_text := SUBSTRING(
5096 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
5108 sort_text := heading_text;
5111 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5112 res.value := heading_text;
5113 res.sort_value := public.naco_normalize(sort_text);
5114 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5124 $func$ LANGUAGE PLPGSQL IMMUTABLE;
5126 CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$
5133 current_url_pos INT;
5134 current_selector url_verify.url_selector%ROWTYPE;
5138 FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP
5139 current_url_pos := 1;
5141 SELECT (oils_xpath(current_selector.xpath || '/text()', b.marc))[current_url_pos] INTO current_url
5142 FROM biblio.record_entry b
5143 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5144 WHERE c.id = item_id;
5146 EXIT WHEN current_url IS NULL;
5148 SELECT (oils_xpath(current_selector.xpath || '/../@tag', b.marc))[current_url_pos] INTO current_tag
5149 FROM biblio.record_entry b
5150 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5151 WHERE c.id = item_id;
5153 IF current_tag IS NULL THEN
5154 current_tag := last_seen_tag;
5156 last_seen_tag := current_tag;
5159 SELECT (oils_xpath(current_selector.xpath || '/@code', b.marc))[current_url_pos] INTO current_sf
5160 FROM biblio.record_entry b
5161 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5162 WHERE c.id = item_id;
5164 INSERT INTO url_verify.url (session, item, url_selector, tag, subfield, ord, full_url)
5165 VALUES ( session_id, item_id, current_selector.id, current_tag, current_sf, current_ord, current_url);
5167 current_url_pos := current_url_pos + 1;
5168 current_ord := current_ord + 1;
5172 RETURN current_ord - 1;
5174 $$ LANGUAGE PLPGSQL;
5176 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
5178 bib biblio.record_entry%ROWTYPE;
5179 idx config.metabib_field%ROWTYPE;
5180 xfrm config.xml_transform%ROWTYPE;
5182 transformed_xml TEXT;
5184 xml_node_list TEXT[];
5190 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
5191 authority_text TEXT;
5192 authority_link BIGINT;
5193 output_row metabib.field_entry_template%ROWTYPE;
5196 -- Start out with no field-use bools set
5197 output_row.browse_field = FALSE;
5198 output_row.facet_field = FALSE;
5199 output_row.search_field = FALSE;
5202 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
5204 -- Loop over the indexing entries
5205 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
5207 joiner := COALESCE(idx.joiner, default_joiner);
5209 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
5211 -- See if we can skip the XSLT ... it's expensive
5212 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5213 -- Can't skip the transform
5214 IF xfrm.xslt <> '---' THEN
5215 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
5217 transformed_xml := bib.marc;
5220 prev_xfrm := xfrm.name;
5223 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5226 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
5227 CONTINUE WHEN xml_node !~ E'^\\s*<';
5229 -- XXX much of this should be moved into oils_xpath_string...
5230 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
5231 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
5232 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
5233 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
5237 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
5239 IF raw_text IS NOT NULL THEN
5240 raw_text := raw_text || joiner;
5243 raw_text := COALESCE(raw_text,'') || curr_text;
5245 -- autosuggest/metabib.browse_entry
5246 IF idx.browse_field THEN
5248 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
5249 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5251 browse_text := curr_text;
5254 IF idx.browse_sort_xpath IS NOT NULL AND
5255 idx.browse_sort_xpath <> '' THEN
5257 sort_value := oils_xpath_string(
5258 idx.browse_sort_xpath, xml_node, joiner,
5259 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
5262 sort_value := browse_text;
5265 output_row.field_class = idx.field_class;
5266 output_row.field = idx.id;
5267 output_row.source = rid;
5268 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
5269 output_row.sort_value :=
5270 public.naco_normalize(sort_value);
5272 output_row.authority := NULL;
5274 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
5275 authority_text := oils_xpath_string(
5276 idx.authority_xpath, xml_node, joiner,
5278 ARRAY[xfrm.prefix, xfrm.namespace_uri],
5279 ARRAY['xlink','http://www.w3.org/1999/xlink']
5283 IF authority_text ~ '^\d+$' THEN
5284 authority_link := authority_text::BIGINT;
5285 PERFORM * FROM authority.record_entry WHERE id = authority_link;
5287 output_row.authority := authority_link;
5293 output_row.browse_field = TRUE;
5294 -- Returning browse rows with search_field = true for search+browse
5295 -- configs allows us to retain granularity of being able to search
5296 -- browse fields with "starts with" type operators (for example, for
5297 -- titles of songs in music albums)
5298 IF idx.search_field THEN
5299 output_row.search_field = TRUE;
5301 RETURN NEXT output_row;
5302 output_row.browse_field = FALSE;
5303 output_row.search_field = FALSE;
5304 output_row.sort_value := NULL;
5307 -- insert raw node text for faceting
5308 IF idx.facet_field THEN
5310 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
5311 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5313 facet_text := curr_text;
5316 output_row.field_class = idx.field_class;
5317 output_row.field = -1 * idx.id;
5318 output_row.source = rid;
5319 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
5321 output_row.facet_field = TRUE;
5322 RETURN NEXT output_row;
5323 output_row.facet_field = FALSE;
5328 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
5330 -- insert combined node text for searching
5331 IF idx.search_field THEN
5332 output_row.field_class = idx.field_class;
5333 output_row.field = idx.id;
5334 output_row.source = rid;
5335 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
5337 output_row.search_field = TRUE;
5338 RETURN NEXT output_row;
5339 output_row.search_field = FALSE;
5346 $func$ LANGUAGE PLPGSQL;
5348 CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$
5350 transformed_xml TEXT;
5351 rmarc TEXT := prmarc;
5355 xfrm config.xml_transform%ROWTYPE;
5356 attr_vector INT[] := '{}'::INT[];
5357 attr_vector_tmp INT[];
5358 attr_list TEXT[] := pattr_list;
5360 norm_attr_value TEXT[];
5362 attr_def config.record_attr_definition%ROWTYPE;
5363 ccvm_row config.coded_value_map%ROWTYPE;
5366 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
5367 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
5370 IF rmarc IS NULL THEN
5371 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
5374 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
5376 attr_value := '{}'::TEXT[];
5377 norm_attr_value := '{}'::TEXT[];
5378 attr_vector_tmp := '{}'::INT[];
5380 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
5382 -- tag+sf attrs only support SVF
5383 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
5384 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
5385 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
5387 AND tag LIKE attr_def.tag
5389 WHEN attr_def.sf_list IS NOT NULL
5390 THEN POSITION(subfield IN attr_def.sf_list) > 0
5397 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
5398 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
5400 IF NOT attr_def.multi THEN
5401 attr_value := ARRAY[attr_value[1]];
5404 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
5406 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
5408 -- See if we can skip the XSLT ... it's expensive
5409 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5410 -- Can't skip the transform
5411 IF xfrm.xslt <> '---' THEN
5412 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
5414 transformed_xml := rmarc;
5417 prev_xfrm := xfrm.name;
5420 IF xfrm.name IS NULL THEN
5421 -- just grab the marcxml (empty) transform
5422 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
5423 prev_xfrm := xfrm.name;
5426 FOR tmp_xml IN SELECT oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
5427 tmp_val := oils_xpath_string(
5430 COALESCE(attr_def.joiner,' '),
5431 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
5433 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5434 attr_value := attr_value || tmp_val;
5435 EXIT WHEN NOT attr_def.multi;
5439 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
5440 SELECT ARRAY_AGG(m.value) INTO attr_value
5441 FROM vandelay.marc21_physical_characteristics(rmarc) v
5442 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
5443 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
5444 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
5446 IF NOT attr_def.multi THEN
5447 attr_value := ARRAY[attr_value[1]];
5452 -- apply index normalizers to attr_value
5453 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
5455 SELECT n.func AS func,
5456 n.param_count AS param_count,
5458 FROM config.index_normalizer n
5459 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
5460 WHERE attr = attr_def.name
5462 EXECUTE 'SELECT ' || normalizer.func || '(' ||
5463 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
5465 WHEN normalizer.param_count > 0
5466 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
5472 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5473 norm_attr_value := norm_attr_value || tmp_val;
5477 IF attr_def.filter THEN
5478 -- Create unknown uncontrolled values and find the IDs of the values
5479 IF ccvm_row.id IS NULL THEN
5480 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
5481 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5482 BEGIN -- use subtransaction to isolate unique constraint violations
5483 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
5484 EXCEPTION WHEN unique_violation THEN END;
5488 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value );
5490 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
5493 -- Add the new value to the vector
5494 attr_vector := attr_vector || attr_vector_tmp;
5497 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
5498 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
5499 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
5504 /* We may need to rewrite the vlist to contain
5505 the intersection of new values for requested
5506 attrs and old values for ignored attrs. To
5507 do this, we take the old attr vlist and
5508 subtract any values that are valid for the
5509 requested attrs, and then add back the new
5510 set of attr values. */
5512 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
5513 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
5514 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
5515 attr_vector := attr_vector || attr_vector_tmp;
5518 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
5519 -- attributes can depend on earlier ones.
5520 PERFORM metabib.compile_composite_attr_cache_init();
5521 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
5523 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
5525 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
5526 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
5528 IF attr_def.filter THEN
5529 IF attr_vector @@ tmp_val::query_int THEN
5530 attr_vector = attr_vector + intset(ccvm_row.id);
5531 EXIT WHEN NOT attr_def.multi;
5535 IF attr_def.sorter THEN
5536 IF attr_vector @@ tmp_val THEN
5537 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
5538 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
5546 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
5547 IF rdeleted THEN -- initial insert OR revivication
5548 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
5549 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
5551 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
5557 $func$ LANGUAGE PLPGSQL;
5560 ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
5561 ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete;
5562 ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
5563 ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
5564 ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;
5567 SELECT evergreen.upgrade_deps_block_check('0875', :eg_version);
5569 ALTER TABLE authority.record_entry ADD COLUMN heading TEXT, ADD COLUMN simple_heading TEXT;
5571 DROP INDEX IF EXISTS authority.unique_by_heading_and_thesaurus;
5572 DROP INDEX IF EXISTS authority.by_heading_and_thesaurus;
5573 DROP INDEX IF EXISTS authority.by_heading;
5575 -- Update without indexes for HOT update
5576 UPDATE authority.record_entry
5577 SET heading = authority.normalize_heading( marc ),
5578 simple_heading = authority.simple_normalize_heading( marc );
5580 CREATE INDEX by_heading_and_thesaurus ON authority.record_entry (heading) WHERE deleted IS FALSE or deleted = FALSE;
5581 CREATE INDEX by_heading ON authority.record_entry (simple_heading) WHERE deleted IS FALSE or deleted = FALSE;
5584 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
5586 NEW.heading := authority.normalize_heading( NEW.marc );
5587 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
5590 $f$ LANGUAGE PLPGSQL;
5592 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
5594 ALTER FUNCTION authority.normalize_heading(TEXT, BOOL) STABLE STRICT;
5595 ALTER FUNCTION authority.normalize_heading(TEXT) STABLE STRICT;
5596 ALTER FUNCTION authority.simple_normalize_heading(TEXT) STABLE STRICT;
5597 ALTER FUNCTION authority.simple_heading_set(TEXT) STABLE STRICT;
5600 ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
5601 ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete;
5602 ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
5603 ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
5604 ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;
5608 SELECT evergreen.upgrade_deps_block_check('0876', :eg_version);
5610 INSERT INTO permission.perm_list ( code, description ) VALUES
5611 ( 'group_application.user.staff.admin.system_admin', oils_i18n_gettext( '',
5612 'Allow a user to add/remove users to/from the "System Administrator" group', 'ppl', 'description' )),
5613 ( 'group_application.user.staff.cat_admin', oils_i18n_gettext( '',
5614 'Allow a user to add/remove users to/from the "Cataloging Administrator" group', 'ppl', 'description' )),
5615 ( 'group_application.user.staff.circ_admin', oils_i18n_gettext( '',
5616 'Allow a user to add/remove users to/from the "Circulation Administrator" group', 'ppl', 'description' )),
5617 ( 'group_application.user.staff.data_review', oils_i18n_gettext( '',
5618 'Allow a user to add/remove users to/from the "Data Review" group', 'ppl', 'description' )),
5619 ( 'group_application.user.staff.volunteers', oils_i18n_gettext( '',
5620 'Allow a user to add/remove users to/from the "Volunteers" group', 'ppl', 'description' ))
5624 SELECT evergreen.upgrade_deps_block_check('0877', :eg_version);
5626 -- Don't use Series search field as the browse field
5627 UPDATE config.metabib_field SET
5628 browse_field = FALSE,
5629 browse_xpath = NULL,
5630 browse_sort_xpath = NULL,
5631 xpath = $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo[not(@type="nfi")]$$
5634 -- Create a new series browse config
5635 INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, search_field, authority_xpath, browse_field, browse_sort_xpath ) VALUES
5636 (32, 'series', 'browse', oils_i18n_gettext(32, 'Series Title (Browse)', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo[@type="nfi"]$$, FALSE, '//@xlink:href', TRUE, $$*[local-name() != "nonSort"]$$ );
5638 SELECT evergreen.upgrade_deps_block_check('0878', :eg_version);
5640 CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$
5643 ind_data metabib.field_entry_template%ROWTYPE;
5644 mbe_row metabib.browse_entry%ROWTYPE;
5652 SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet;
5653 SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse;
5654 SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search;
5656 PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled;
5658 IF NOT b_skip_search THEN
5659 FOR fclass IN SELECT * FROM config.metabib_class LOOP
5660 -- RAISE NOTICE 'Emptying out %', fclass.name;
5661 EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id;
5664 IF NOT b_skip_facet THEN
5665 DELETE FROM metabib.facet_entry WHERE source = bib_id;
5667 IF NOT b_skip_browse THEN
5668 DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id;
5672 FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP
5674 -- don't store what has been normalized away
5675 CONTINUE WHEN ind_data.value IS NULL;
5677 IF ind_data.field < 0 THEN
5678 ind_data.field = -1 * ind_data.field;
5681 IF ind_data.facet_field AND NOT b_skip_facet THEN
5682 INSERT INTO metabib.facet_entry (field, source, value)
5683 VALUES (ind_data.field, ind_data.source, ind_data.value);
5686 IF ind_data.browse_field AND NOT b_skip_browse THEN
5687 -- A caveat about this SELECT: this should take care of replacing
5688 -- old mbe rows when data changes, but not if normalization (by
5689 -- which I mean specifically the output of
5690 -- evergreen.oils_tsearch2()) changes. It may or may not be
5691 -- expensive to add a comparison of index_vector to index_vector
5692 -- to the WHERE clause below.
5694 CONTINUE WHEN ind_data.sort_value IS NULL;
5696 value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field);
5697 SELECT INTO mbe_row * FROM metabib.browse_entry
5698 WHERE value = value_prepped AND sort_value = ind_data.sort_value;
5701 mbe_id := mbe_row.id;
5703 INSERT INTO metabib.browse_entry
5704 ( value, sort_value ) VALUES
5705 ( value_prepped, ind_data.sort_value );
5707 mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
5710 INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority)
5711 VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority);
5714 IF ind_data.search_field AND NOT b_skip_search THEN
5715 -- Avoid inserting duplicate rows
5716 EXECUTE 'SELECT 1 FROM metabib.' || ind_data.field_class ||
5717 '_field_entry WHERE field = $1 AND source = $2 AND value = $3'
5718 INTO mbe_id USING ind_data.field, ind_data.source, ind_data.value;
5719 -- RAISE NOTICE 'Search for an already matching row returned %', mbe_id;
5720 IF mbe_id IS NULL THEN
5722 INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value)
5724 quote_literal(ind_data.field) || $$, $$ ||
5725 quote_literal(ind_data.source) || $$, $$ ||
5726 quote_literal(ind_data.value) ||
5733 IF NOT b_skip_search THEN
5734 PERFORM metabib.update_combined_index_vectors(bib_id);
5739 $func$ LANGUAGE PLPGSQL;
5743 SELECT evergreen.upgrade_deps_block_check('0879', :eg_version);
5745 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
5746 node vandelay.match_set_point,
5748 ) RETURNS VOID AS $$
5759 -- remember $1 is tags_rstore, and $2 is svf_rstore
5762 SELECT COUNT(*) INTO jrow_count FROM _vandelay_tmp_jrows;
5763 IF jrow_count > 0 THEN
5764 my_using := ' USING (record)';
5765 my_join := 'FULL OUTER JOIN';
5771 IF node.tag IS NOT NULL THEN
5772 caseless := (node.tag IN ('020', '022', '024'));
5774 IF node.subfield IS NOT NULL THEN
5775 tagkey := tagkey || node.subfield;
5793 my_alias := 'n' || node.id::TEXT;
5795 jrow := my_join || ' (SELECT *, ';
5796 IF node.tag IS NOT NULL THEN
5797 jrow := jrow || node.quality ||
5798 ' AS quality FROM metabib.full_rec mfr WHERE mfr.tag = ''' ||
5800 IF node.subfield IS NOT NULL THEN
5801 jrow := jrow || ' AND mfr.subfield = ''' ||
5802 node.subfield || '''';
5804 jrow := jrow || ' AND (';
5805 jrow := jrow || vandelay._node_tag_comparisons(caseless, op, tags_rstore, tagkey);
5806 jrow := jrow || ')) ' || my_alias || my_using || E'\n';
5808 jrow := jrow || 'id AS record, ' || node.quality ||
5809 ' AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = ''' ||
5810 node.svf || ''' AND mraf.value ' || op || ' $2->''' || node.svf || ''') ' ||
5811 my_alias || my_using || E'\n';
5813 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
5815 $$ LANGUAGE PLPGSQL;
5820 -- Not running changes from example.reporter-extension.sql since these are
5821 -- not installed by default, but including a helpful note.
5823 \qecho **** NOTICE ****
5824 \qecho 'There were changes in example.reporter-extension.sql.'
5825 \qecho 'Please run that script again if you use it in your system'
5826 \qecho 'to apply new changes.'
5829 \qecho **** Certain improvements in 2.6, particularly attribute improvements,
5830 \qecho **** require a reingest of all your bib records. In order to allow
5831 \qecho **** this to continue without locking your entire bibliographic data
5832 \qecho **** set, consider generating an SQL script with the following queries,
5833 \qecho **** then running it via psql.
5835 \qecho **** If you have a large number of bibs (100,000+), please consider this
5836 \qecho **** as a starting point only, as you will likely wish to parallelize
5837 \qecho **** this is some fashion.
5839 \qecho **** If you require a more responsive catalog/database while reingesting,
5840 \qecho **** consider adding 'pg_sleep()' calls between each reingest update.
5843 \qecho '\\o /tmp/reingest_2.6_bib_recs.sql'
5844 \qecho 'SELECT ''-- Grab current setting'';'
5845 \qecho 'SELECT ''\\set force_reingest '' || enabled FROM config.internal_flag WHERE name = ''ingest.reingest.force_on_same_marc'';'
5846 \qecho 'SELECT ''update config.internal_flag set enabled = true where name = ''''ingest.reingest.force_on_same_marc'''';'';'
5847 \qecho 'SELECT ''update biblio.record_entry set id = id where id = '' || id || '';'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;'
5848 \qecho 'SELECT ''-- Restore previous setting'';'
5849 \qecho 'SELECT ''update config.internal_flag set enabled = :force_reingest where name = \'\'ingest.reingest.force_on_same_marc\'\';'';'