1 --Upgrade Script for 2.5.3 to 2.6.0
2 \set eg_version '''2.6.0'''
5 \qecho **** NOTICE ****
6 \qecho 'We are disabling all triggers for authority.record_entry outside the '
7 \qecho 'transaction. If this upgrade fails, you may want to double-check that '
8 \qecho 'triggers are reactivated, e.g.:'
9 \qecho 'ALTER TABLE authority.record_entry ENABLE TRIGGER ALL;'
11 ALTER TABLE authority.record_entry DISABLE TRIGGER a_marcxml_is_well_formed;
12 ALTER TABLE authority.record_entry DISABLE TRIGGER aaa_auth_ingest_or_delete;
13 ALTER TABLE authority.record_entry DISABLE TRIGGER b_maintain_901;
14 ALTER TABLE authority.record_entry DISABLE TRIGGER c_maintain_control_numbers;
15 ALTER TABLE authority.record_entry DISABLE TRIGGER map_thesaurus_to_control_set;
18 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.6.0', :eg_version);
20 -- check whether patch can be applied
21 SELECT evergreen.upgrade_deps_block_check('0851', :eg_version);
23 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
26 use MARC::File::XML (BinaryEncoding => 'UTF-8');
29 use Unicode::Normalize;
31 MARC::Charset->assume_unicode(1);
33 my $schema = $_TD->{table_schema};
34 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
36 my @old901s = $marc->field('901');
37 $marc->delete_fields(@old901s);
39 if ($schema eq 'biblio') {
40 my $tcn_value = $_TD->{new}{tcn_value};
42 # Set TCN value to record ID?
43 my $id_as_tcn = spi_exec_query("
45 FROM config.global_flag
46 WHERE name = 'cat.bib.use_id_for_tcn'
48 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
49 $tcn_value = $_TD->{new}{id};
50 $_TD->{new}{tcn_value} = $tcn_value;
53 my $new_901 = MARC::Field->new("901", " ", " ",
55 "b" => $_TD->{new}{tcn_source},
56 "c" => $_TD->{new}{id},
60 if ($_TD->{new}{owner}) {
61 $new_901->add_subfields("o" => $_TD->{new}{owner});
64 if ($_TD->{new}{share_depth}) {
65 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
68 $marc->append_fields($new_901);
69 } elsif ($schema eq 'authority') {
70 my $new_901 = MARC::Field->new("901", " ", " ",
71 "c" => $_TD->{new}{id},
74 $marc->append_fields($new_901);
75 } elsif ($schema eq 'serial') {
76 my $new_901 = MARC::Field->new("901", " ", " ",
77 "c" => $_TD->{new}{id},
79 "o" => $_TD->{new}{owning_lib},
82 if ($_TD->{new}{record}) {
83 $new_901->add_subfields("r" => $_TD->{new}{record});
86 $marc->append_fields($new_901);
88 my $new_901 = MARC::Field->new("901", " ", " ",
89 "c" => $_TD->{new}{id},
92 $marc->append_fields($new_901);
95 my $xml = $marc->as_xml_record();
97 $xml =~ s/^<\?xml.+\?\s*>//go;
98 $xml =~ s/>\s+</></go;
101 # Embed a version of OpenILS::Application::AppUtils->entityize()
102 # to avoid having to set PERL5LIB for PostgreSQL as well
106 # Convert raw ampersands to entities
107 $xml =~ s/&(?!\S+;)/&/gso;
109 # Convert Unicode characters to entities
110 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
112 $xml =~ s/[\x00-\x1f]//go;
113 $_TD->{new}{marc} = $xml;
116 $func$ LANGUAGE PLPERLU;
118 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
121 use MARC::File::XML (BinaryEncoding => 'UTF-8');
124 use Unicode::Normalize;
126 MARC::Charset->assume_unicode(1);
128 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
129 my $schema = $_TD->{table_schema};
130 my $rec_id = $_TD->{new}{id};
132 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
133 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
134 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
138 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
139 my $ou_cni = 'EVRGRN';
142 if ($schema eq 'serial') {
143 $owner = $_TD->{new}{owning_lib};
145 # are.owner and bre.owner can be null, so fall back to the consortial setting
146 $owner = $_TD->{new}{owner} || 1;
149 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
150 if ($ous_rv->{processed}) {
151 $ou_cni = $ous_rv->{rows}[0]->{value};
152 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
154 # Fall back to the shortname of the OU if there was no OU setting
155 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
156 if ($ous_rv->{processed}) {
157 $ou_cni = $ous_rv->{rows}[0]->{shortname};
161 my ($create, $munge) = (0, 0);
163 my @scns = $record->field('035');
165 foreach my $id_field ('001', '003') {
167 my @controls = $record->field($id_field);
169 if ($id_field eq '001') {
170 $spec_value = $rec_id;
172 $spec_value = $ou_cni;
175 # Create the 001/003 if none exist
176 if (scalar(@controls) == 1) {
177 # Only one field; check to see if we need to munge it
178 unless (grep $_->data() eq $spec_value, @controls) {
182 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
183 foreach my $control (@controls) {
184 $record->delete_field($control);
186 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
191 my $cn = $record->field('001')->data();
192 # Special handling of OCLC numbers, often found in records that lack 003
193 if ($cn =~ /^o(c[nm]|n)\d/) {
194 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
195 $record->field('003')->data('OCoLC');
199 # Now, if we need to munge the 001, we will first push the existing 001/003
200 # into the 035; but if the record did not have one (and one only) 001 and 003
201 # to begin with, skip this process
202 if ($munge and not $create) {
204 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
206 # Do not create duplicate 035 fields
207 unless (grep $_->subfield('a') eq $scn, @scns) {
208 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
212 # Set the 001/003 and update the MARC
213 if ($create or $munge) {
214 $record->field('001')->data($rec_id);
215 $record->field('003')->data($ou_cni);
217 my $xml = $record->as_xml_record();
219 $xml =~ s/^<\?xml.+\?\s*>//go;
220 $xml =~ s/>\s+</></go;
221 $xml =~ s/\p{Cc}//go;
223 # Embed a version of OpenILS::Application::AppUtils->entityize()
224 # to avoid having to set PERL5LIB for PostgreSQL as well
228 # Convert raw ampersands to entities
229 $xml =~ s/&(?!\S+;)/&/gso;
231 # Convert Unicode characters to entities
232 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
234 $xml =~ s/[\x00-\x1f]//go;
235 $_TD->{new}{marc} = $xml;
241 $func$ LANGUAGE PLPERLU;
243 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
246 use Unicode::Normalize;
252 # Apply NACO normalization to input string; based on
253 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
255 # Note that unlike a strict reading of the NACO normalization rules,
256 # output is returned as lowercase instead of uppercase for compatibility
257 # with previous versions of the Evergreen naco_normalize routine.
259 # Convert to upper-case first; even though final output will be lowercase, doing this will
260 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
261 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
264 # remove non-filing strings
265 $str =~ s/\x{0098}.*?\x{009C}//g;
269 # additional substitutions - 3.6.
270 $str =~ s/\x{00C6}/AE/g;
271 $str =~ s/\x{00DE}/TH/g;
272 $str =~ s/\x{0152}/OE/g;
273 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}]['/DDOLl/d;
275 # transformations based on Unicode category codes
276 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
278 if ($sf && $sf =~ /^a/o) {
279 my $commapos = index($str, ',');
280 if ($commapos > -1) {
281 if ($commapos != length($str) - 1) {
282 $str =~ s/,/\x07/; # preserve first comma
287 # since we've stripped out the control characters, we can now
288 # use a few as placeholders temporarily
289 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
290 $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;
291 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
294 $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/;
296 # intentionally skipping step 8 of the NACO algorithm; if the string
297 # gets normalized away, that's fine.
299 # leading and trailing spaces
305 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
307 -- Currently, the only difference from naco_normalize is that search_normalize
308 -- turns apostrophes into spaces, while naco_normalize collapses them.
309 CREATE OR REPLACE FUNCTION public.search_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
312 use Unicode::Normalize;
318 # Apply NACO normalization to input string; based on
319 # http://www.loc.gov/catdir/pcc/naco/SCA_PccNormalization_Final_revised.pdf
321 # Note that unlike a strict reading of the NACO normalization rules,
322 # output is returned as lowercase instead of uppercase for compatibility
323 # with previous versions of the Evergreen naco_normalize routine.
325 # Convert to upper-case first; even though final output will be lowercase, doing this will
326 # ensure that the German eszett (ß) and certain ligatures (ff, fi, ffl, etc.) will be handled correctly.
327 # If there are any bugs in Perl's implementation of upcasing, they will be passed through here.
330 # remove non-filing strings
331 $str =~ s/\x{0098}.*?\x{009C}//g;
335 # additional substitutions - 3.6.
336 $str =~ s/\x{00C6}/AE/g;
337 $str =~ s/\x{00DE}/TH/g;
338 $str =~ s/\x{0152}/OE/g;
339 $str =~ tr/\x{0110}\x{00D0}\x{00D8}\x{0141}\x{2113}\x{02BB}\x{02BC}][/DDOLl/d;
341 # transformations based on Unicode category codes
342 $str =~ s/[\p{Cc}\p{Cf}\p{Co}\p{Cs}\p{Lm}\p{Mc}\p{Me}\p{Mn}]//g;
344 if ($sf && $sf =~ /^a/o) {
345 my $commapos = index($str, ',');
346 if ($commapos > -1) {
347 if ($commapos != length($str) - 1) {
348 $str =~ s/,/\x07/; # preserve first comma
353 # since we've stripped out the control characters, we can now
354 # use a few as placeholders temporarily
355 $str =~ tr/+&@\x{266D}\x{266F}#/\x01\x02\x03\x04\x05\x06/;
356 $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;
357 $str =~ tr/\x01\x02\x03\x04\x05\x06\x07/+&@\x{266D}\x{266F}#,/;
360 $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/;
362 # intentionally skipping step 8 of the NACO algorithm; if the string
363 # gets normalized away, that's fine.
365 # leading and trailing spaces
371 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
373 -- Evergreen DB patch XXXX.data.prefer_external_url_OUS.sql
375 -- FIXME: insert description of change, if needed
378 -- check whether patch can be applied
379 SELECT evergreen.upgrade_deps_block_check('0853', :eg_version);
381 INSERT into config.org_unit_setting_type
382 ( name, grp, label, description, datatype, fm_class ) VALUES
383 ( 'lib.prefer_external_url', 'lib',
384 'Use external "library information URL" in copy table, if available',
385 '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.',
390 SELECT evergreen.upgrade_deps_block_check('0854', :eg_version);
392 INSERT INTO permission.perm_list ( id, code, description ) VALUES (
394 'UPDATE_ORG_UNIT_SETTING.circ.min_item_price',
397 'UPDATE_ORG_UNIT_SETTING.circ.min_item_price',
403 'UPDATE_ORG_UNIT_SETTING.circ.max_item_price',
406 'UPDATE_ORG_UNIT_SETTING.circ.max_item_price',
412 INSERT into config.org_unit_setting_type
413 ( name, grp, label, description, datatype, fm_class )
415 'circ.min_item_price',
418 'circ.min_item_price',
419 'Minimum Item Price',
422 'circ.min_item_price',
423 'When charging for lost items, charge this amount as a minimum.',
424 'coust', 'description'),
428 'circ.max_item_price',
431 'circ.max_item_price',
432 'Maximum Item Price',
435 'circ.max_item_price',
436 'When charging for lost items, limit the charge to this as a maximum.',
437 'coust', 'description'),
442 -- Compiled list of all changed functions and views where we went from:
443 -- array_accum() to array_agg()
444 -- array_to_string(array_agg()) to string_agg()
447 SELECT evergreen.upgrade_deps_block_check('0855', :eg_version);
449 -- from 000.functions.general.sql
452 -- from 002.functions.config.sql
454 CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
455 SELECT regexp_replace(string_agg(output,' '),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT);
459 -- from 011.schema.authority.sql
461 CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$
462 SELECT ARRAY_AGG(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1;
465 CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$
466 SELECT ARRAY_AGG(y) from (
467 SELECT unnest(ARRAY_CAT(
469 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field)
471 FROM authority.browse_axis_authority_field_map a
475 CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$
476 SELECT ARRAY_AGG(authority_field) FROM authority.control_set_bib_field WHERE tag = $1
479 CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$
480 SELECT ARRAY_AGG(y) from (
481 SELECT unnest(ARRAY_CAT(
482 ARRAY[a.authority_field],
483 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field)
485 FROM authority.control_set_bib_field a
489 CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$
490 SELECT ARRAY_AGG(id) FROM authority.control_set_authority_field WHERE tag = $1
493 CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$
494 SELECT ARRAY_AGG(y) from (
495 SELECT unnest(ARRAY_CAT(
497 (SELECT ARRAY_AGG(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id)
499 FROM authority.control_set_authority_field a
504 -- from 012.schema.vandelay.sql
506 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$
508 transformed_xml TEXT;
511 xfrm config.xml_transform%ROWTYPE;
513 new_attrs HSTORE := ''::HSTORE;
514 attr_def config.record_attr_definition%ROWTYPE;
517 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP
519 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
520 SELECT STRING_AGG(x.value, COALESCE(attr_def.joiner,' ')) INTO attr_value
521 FROM vandelay.flatten_marc(xml) AS x
522 WHERE x.tag LIKE attr_def.tag
524 WHEN attr_def.sf_list IS NOT NULL
525 THEN POSITION(x.subfield IN attr_def.sf_list) > 0
532 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
533 attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field);
535 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
537 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
539 -- See if we can skip the XSLT ... it's expensive
540 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
541 -- Can't skip the transform
542 IF xfrm.xslt <> '---' THEN
543 transformed_xml := oils_xslt_process(xml,xfrm.xslt);
545 transformed_xml := xml;
548 prev_xfrm := xfrm.name;
551 IF xfrm.name IS NULL THEN
552 -- just grab the marcxml (empty) transform
553 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
554 prev_xfrm := xfrm.name;
557 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
559 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
560 SELECT m.value::TEXT INTO attr_value
561 FROM vandelay.marc21_physical_characteristics(xml) v
562 JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
563 WHERE v.subfield = attr_def.phys_char_sf
564 LIMIT 1; -- Just in case ...
568 -- apply index normalizers to attr_value
570 SELECT n.func AS func,
571 n.param_count AS param_count,
573 FROM config.index_normalizer n
574 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
575 WHERE attr = attr_def.name
577 EXECUTE 'SELECT ' || normalizer.func || '(' ||
578 quote_nullable( attr_value ) ||
580 WHEN normalizer.param_count > 0
581 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
588 -- Add the new value to the hstore
589 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
595 $_$ LANGUAGE PLPGSQL;
597 CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT ) RETURNS hstore AS $_$
598 SELECT vandelay.extract_rec_attrs( $1, (SELECT ARRAY_AGG(name) FROM config.record_attr_definition));
601 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
602 match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER
603 ) RETURNS SETOF vandelay.match_set_test_result AS $$
614 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
615 svf_rstore := vandelay.extract_rec_attrs(record_xml);
617 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
618 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
620 -- generate the where clause and return that directly (into wq), and as
621 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
622 wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore);
624 query_ := 'SELECT DISTINCT(record), ';
626 -- qrows table is for the quality bits we add to the SELECT clause
628 'COALESCE(n' || q::TEXT || '.quality, 0)', ' + '
629 ) INTO coal FROM _vandelay_tmp_qrows;
631 -- our query string so far is the SELECT clause and the inital FROM.
632 -- no JOINs yet nor the WHERE clause
633 query_ := query_ || coal || ' AS quality ' || E'\n';
635 -- jrows table is for the joins we must make (and the real text conditions)
636 SELECT STRING_AGG(j, E'\n') INTO joins
637 FROM _vandelay_tmp_jrows;
639 -- add those joins and the where clause to our query.
640 query_ := query_ || joins || E'\n';
642 -- join the record bucket
643 IF bucket_id IS NOT NULL THEN
644 query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' ||
645 'brebi ON (brebi.target_biblio_record_entry = record ' ||
646 'AND brebi.bucket = ' || bucket_id || E')\n';
649 query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted';
651 -- this will return rows of record,quality
652 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
656 DROP TABLE _vandelay_tmp_qrows;
657 DROP TABLE _vandelay_tmp_jrows;
662 CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore(
664 ) RETURNS HSTORE AS $func$
668 ARRAY_AGG(tag || (COALESCE(subfield, ''))),
672 SELECT tag, subfield, ARRAY_AGG(value)::TEXT AS value
675 CASE WHEN tag = '020' THEN -- caseless -- isbn
676 LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%')
677 WHEN tag = '022' THEN -- caseless -- issn
678 LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%')
679 WHEN tag = '024' THEN -- caseless -- upc (other)
684 FROM vandelay.flatten_marc(record_xml)) x
685 GROUP BY tag, subfield ORDER BY tag, subfield
689 $func$ LANGUAGE PLPGSQL;
691 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
692 node vandelay.match_set_point,
700 child vandelay.match_set_point;
702 SELECT ARRAY_AGG(id) INTO children FROM vandelay.match_set_point
703 WHERE parent = node.id;
705 IF ARRAY_LENGTH(children, 1) > 0 THEN
706 this_op := vandelay._get_expr_render_one(node);
709 WHILE children[i] IS NOT NULL LOOP
710 SELECT * INTO child FROM vandelay.match_set_point
711 WHERE id = children[i];
713 q := q || ' ' || this_op || ' ';
716 q := q || vandelay.get_expr_from_match_set_point(child, tags_rstore);
720 ELSIF node.bool_op IS NULL THEN
721 PERFORM vandelay._get_expr_push_qrow(node);
722 PERFORM vandelay._get_expr_push_jrow(node, tags_rstore);
723 RETURN vandelay._get_expr_render_one(node);
731 -- from 030.schema.metabib.sql
733 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
740 uri_owner_list TEXT[];
748 -- Clear any URI mappings and call numbers for this bib.
749 -- This leads to acn / auricnm inflation, but also enables
750 -- old acn/auricnm's to go away and for bibs to be deleted.
751 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
752 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
753 DELETE FROM asset.call_number WHERE id = uri_cn_id;
756 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
757 IF ARRAY_UPPER(uris,1) > 0 THEN
758 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
759 -- First we pull info out of the 856
762 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
763 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
764 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
766 IF uri_label IS NULL THEN
767 uri_label := uri_href;
769 CONTINUE WHEN uri_href IS NULL;
771 -- Get the distinct list of libraries wanting to use
773 DISTINCT REGEXP_REPLACE(
775 $re$^.*?\((\w+)\).*$$re$,
778 ) INTO uri_owner_list
781 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
786 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
788 -- look for a matching uri
789 IF uri_use IS NULL THEN
790 SELECT id INTO uri_id
792 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
794 IF NOT FOUND THEN -- create one
795 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
796 SELECT id INTO uri_id
798 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
801 SELECT id INTO uri_id
803 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
805 IF NOT FOUND THEN -- create one
806 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
807 SELECT id INTO uri_id
809 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
813 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
814 uri_owner := uri_owner_list[j];
816 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
817 CONTINUE WHEN NOT FOUND;
819 -- we need a call number to link through
820 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;
822 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
823 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
824 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;
827 -- now, link them if they're not already
828 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
830 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
842 $func$ LANGUAGE PLPGSQL;
844 -- from 100.circ_matrix.sql
846 CREATE OR REPLACE FUNCTION actor.calculate_system_penalties( match_user INT, context_org INT ) RETURNS SETOF actor.usr_standing_penalty AS $func$
848 user_object actor.usr%ROWTYPE;
849 new_sp_row actor.usr_standing_penalty%ROWTYPE;
850 existing_sp_row actor.usr_standing_penalty%ROWTYPE;
851 collections_fines permission.grp_penalty_threshold%ROWTYPE;
852 max_fines permission.grp_penalty_threshold%ROWTYPE;
853 max_overdue permission.grp_penalty_threshold%ROWTYPE;
854 max_items_out permission.grp_penalty_threshold%ROWTYPE;
855 max_lost permission.grp_penalty_threshold%ROWTYPE;
856 max_longoverdue permission.grp_penalty_threshold%ROWTYPE;
861 items_longoverdue INT;
862 context_org_list INT[];
863 current_fines NUMERIC(8,2) := 0.0;
864 tmp_fines NUMERIC(8,2);
867 tmp_org actor.org_unit%ROWTYPE;
868 tmp_penalty config.standing_penalty%ROWTYPE;
871 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
874 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
876 -- Fail if the user has a high fine balance
878 tmp_grp := user_object.profile;
880 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 1 AND org_unit = tmp_org.id;
882 IF max_fines.threshold IS NULL THEN
883 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
888 IF tmp_grp IS NULL THEN
893 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
897 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
901 IF max_fines.threshold IS NOT NULL THEN
905 FROM actor.usr_standing_penalty
906 WHERE usr = match_user
907 AND org_unit = max_fines.org_unit
908 AND (stop_date IS NULL or stop_date > NOW())
909 AND standing_penalty = 1;
911 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
913 SELECT SUM(f.balance_owed) INTO current_fines
914 FROM money.materialized_billable_xact_summary f
917 FROM booking.reservation r
918 WHERE r.usr = match_user
919 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
920 AND xact_finish IS NULL
924 WHERE g.usr = match_user
925 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
926 AND xact_finish IS NULL
929 FROM action.circulation circ
930 WHERE circ.usr = match_user
931 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
932 AND xact_finish IS NULL ) l USING (id);
934 IF current_fines >= max_fines.threshold THEN
935 new_sp_row.usr := match_user;
936 new_sp_row.org_unit := max_fines.org_unit;
937 new_sp_row.standing_penalty := 1;
938 RETURN NEXT new_sp_row;
942 -- Start over for max overdue
943 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
945 -- Fail if the user has too many overdue items
947 tmp_grp := user_object.profile;
950 SELECT * INTO max_overdue FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 2 AND org_unit = tmp_org.id;
952 IF max_overdue.threshold IS NULL THEN
953 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
958 IF tmp_grp IS NULL THEN
963 IF max_overdue.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
967 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
971 IF max_overdue.threshold IS NOT NULL THEN
975 FROM actor.usr_standing_penalty
976 WHERE usr = match_user
977 AND org_unit = max_overdue.org_unit
978 AND (stop_date IS NULL or stop_date > NOW())
979 AND standing_penalty = 2;
981 SELECT INTO items_overdue COUNT(*)
982 FROM action.circulation circ
983 JOIN actor.org_unit_full_path( max_overdue.org_unit ) fp ON (circ.circ_lib = fp.id)
984 WHERE circ.usr = match_user
985 AND circ.checkin_time IS NULL
986 AND circ.due_date < NOW()
987 AND (circ.stop_fines = 'MAXFINES' OR circ.stop_fines IS NULL);
989 IF items_overdue >= max_overdue.threshold::INT THEN
990 new_sp_row.usr := match_user;
991 new_sp_row.org_unit := max_overdue.org_unit;
992 new_sp_row.standing_penalty := 2;
993 RETURN NEXT new_sp_row;
997 -- Start over for max out
998 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1000 -- Fail if the user has too many checked out items
1002 tmp_grp := user_object.profile;
1004 SELECT * INTO max_items_out FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 3 AND org_unit = tmp_org.id;
1006 IF max_items_out.threshold IS NULL THEN
1007 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1012 IF tmp_grp IS NULL THEN
1017 IF max_items_out.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1021 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1026 -- Fail if the user has too many items checked out
1027 IF max_items_out.threshold IS NOT NULL THEN
1031 FROM actor.usr_standing_penalty
1032 WHERE usr = match_user
1033 AND org_unit = max_items_out.org_unit
1034 AND (stop_date IS NULL or stop_date > NOW())
1035 AND standing_penalty = 3;
1037 SELECT INTO items_out COUNT(*)
1038 FROM action.circulation circ
1039 JOIN actor.org_unit_full_path( max_items_out.org_unit ) fp ON (circ.circ_lib = fp.id)
1040 WHERE circ.usr = match_user
1041 AND circ.checkin_time IS NULL
1042 AND (circ.stop_fines IN (
1043 SELECT 'MAXFINES'::TEXT
1045 SELECT 'LONGOVERDUE'::TEXT
1051 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.tally_lost', circ.circ_lib)) ILIKE 'true' THEN 'true'
1056 SELECT 'CLAIMSRETURNED'::TEXT
1060 WHEN (SELECT value FROM actor.org_unit_ancestor_setting('circ.do_not_tally_claims_returned', circ.circ_lib)) ILIKE 'true' THEN 'true'
1064 ) OR circ.stop_fines IS NULL)
1065 AND xact_finish IS NULL;
1067 IF items_out >= max_items_out.threshold::INT THEN
1068 new_sp_row.usr := match_user;
1069 new_sp_row.org_unit := max_items_out.org_unit;
1070 new_sp_row.standing_penalty := 3;
1071 RETURN NEXT new_sp_row;
1075 -- Start over for max lost
1076 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1078 -- Fail if the user has too many lost items
1080 tmp_grp := user_object.profile;
1083 SELECT * INTO max_lost FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 5 AND org_unit = tmp_org.id;
1085 IF max_lost.threshold IS NULL THEN
1086 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1091 IF tmp_grp IS NULL THEN
1096 IF max_lost.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1100 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1104 IF max_lost.threshold IS NOT NULL THEN
1108 FROM actor.usr_standing_penalty
1109 WHERE usr = match_user
1110 AND org_unit = max_lost.org_unit
1111 AND (stop_date IS NULL or stop_date > NOW())
1112 AND standing_penalty = 5;
1114 SELECT INTO items_lost COUNT(*)
1115 FROM action.circulation circ
1116 JOIN actor.org_unit_full_path( max_lost.org_unit ) fp ON (circ.circ_lib = fp.id)
1117 WHERE circ.usr = match_user
1118 AND circ.checkin_time IS NULL
1119 AND (circ.stop_fines = 'LOST')
1120 AND xact_finish IS NULL;
1122 IF items_lost >= max_lost.threshold::INT AND 0 < max_lost.threshold::INT THEN
1123 new_sp_row.usr := match_user;
1124 new_sp_row.org_unit := max_lost.org_unit;
1125 new_sp_row.standing_penalty := 5;
1126 RETURN NEXT new_sp_row;
1130 -- Start over for max longoverdue
1131 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1133 -- Fail if the user has too many longoverdue items
1135 tmp_grp := user_object.profile;
1138 SELECT * INTO max_longoverdue
1139 FROM permission.grp_penalty_threshold
1140 WHERE grp = tmp_grp AND
1142 org_unit = tmp_org.id;
1144 IF max_longoverdue.threshold IS NULL THEN
1145 SELECT parent INTO tmp_grp
1146 FROM permission.grp_tree WHERE id = tmp_grp;
1151 IF tmp_grp IS NULL THEN
1156 IF max_longoverdue.threshold IS NOT NULL
1157 OR tmp_org.parent_ou IS NULL THEN
1161 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1165 IF max_longoverdue.threshold IS NOT NULL THEN
1169 FROM actor.usr_standing_penalty
1170 WHERE usr = match_user
1171 AND org_unit = max_longoverdue.org_unit
1172 AND (stop_date IS NULL or stop_date > NOW())
1173 AND standing_penalty = 35;
1175 SELECT INTO items_longoverdue COUNT(*)
1176 FROM action.circulation circ
1177 JOIN actor.org_unit_full_path( max_longoverdue.org_unit ) fp
1178 ON (circ.circ_lib = fp.id)
1179 WHERE circ.usr = match_user
1180 AND circ.checkin_time IS NULL
1181 AND (circ.stop_fines = 'LONGOVERDUE')
1182 AND xact_finish IS NULL;
1184 IF items_longoverdue >= max_longoverdue.threshold::INT
1185 AND 0 < max_longoverdue.threshold::INT THEN
1186 new_sp_row.usr := match_user;
1187 new_sp_row.org_unit := max_longoverdue.org_unit;
1188 new_sp_row.standing_penalty := 35;
1189 RETURN NEXT new_sp_row;
1194 -- Start over for collections warning
1195 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1197 -- Fail if the user has a collections-level fine balance
1199 tmp_grp := user_object.profile;
1201 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 4 AND org_unit = tmp_org.id;
1203 IF max_fines.threshold IS NULL THEN
1204 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1209 IF tmp_grp IS NULL THEN
1214 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1218 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1222 IF max_fines.threshold IS NOT NULL THEN
1226 FROM actor.usr_standing_penalty
1227 WHERE usr = match_user
1228 AND org_unit = max_fines.org_unit
1229 AND (stop_date IS NULL or stop_date > NOW())
1230 AND standing_penalty = 4;
1232 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
1234 SELECT SUM(f.balance_owed) INTO current_fines
1235 FROM money.materialized_billable_xact_summary f
1238 FROM booking.reservation r
1239 WHERE r.usr = match_user
1240 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
1241 AND r.xact_finish IS NULL
1244 FROM money.grocery g
1245 WHERE g.usr = match_user
1246 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
1247 AND g.xact_finish IS NULL
1250 FROM action.circulation circ
1251 WHERE circ.usr = match_user
1252 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
1253 AND circ.xact_finish IS NULL ) l USING (id);
1255 IF current_fines >= max_fines.threshold THEN
1256 new_sp_row.usr := match_user;
1257 new_sp_row.org_unit := max_fines.org_unit;
1258 new_sp_row.standing_penalty := 4;
1259 RETURN NEXT new_sp_row;
1263 -- Start over for in collections
1264 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1266 -- Remove the in-collections penalty if the user has paid down enough
1267 -- This penalty is different, because this code is not responsible for creating
1268 -- new in-collections penalties, only for removing them
1270 tmp_grp := user_object.profile;
1272 SELECT * INTO max_fines FROM permission.grp_penalty_threshold WHERE grp = tmp_grp AND penalty = 30 AND org_unit = tmp_org.id;
1274 IF max_fines.threshold IS NULL THEN
1275 SELECT parent INTO tmp_grp FROM permission.grp_tree WHERE id = tmp_grp;
1280 IF tmp_grp IS NULL THEN
1285 IF max_fines.threshold IS NOT NULL OR tmp_org.parent_ou IS NULL THEN
1289 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1293 IF max_fines.threshold IS NOT NULL THEN
1295 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit );
1297 -- first, see if the user had paid down to the threshold
1298 SELECT SUM(f.balance_owed) INTO current_fines
1299 FROM money.materialized_billable_xact_summary f
1302 FROM booking.reservation r
1303 WHERE r.usr = match_user
1304 AND r.pickup_lib IN (SELECT * FROM unnest(context_org_list))
1305 AND r.xact_finish IS NULL
1308 FROM money.grocery g
1309 WHERE g.usr = match_user
1310 AND g.billing_location IN (SELECT * FROM unnest(context_org_list))
1311 AND g.xact_finish IS NULL
1314 FROM action.circulation circ
1315 WHERE circ.usr = match_user
1316 AND circ.circ_lib IN (SELECT * FROM unnest(context_org_list))
1317 AND circ.xact_finish IS NULL ) l USING (id);
1319 IF current_fines IS NULL OR current_fines <= max_fines.threshold THEN
1320 -- patron has paid down enough
1322 SELECT INTO tmp_penalty * FROM config.standing_penalty WHERE id = 30;
1324 IF tmp_penalty.org_depth IS NOT NULL THEN
1326 -- since this code is not responsible for applying the penalty, it can't
1327 -- guarantee the current context org will match the org at which the penalty
1328 --- was applied. search up the org tree until we hit the configured penalty depth
1329 SELECT INTO tmp_org * FROM actor.org_unit WHERE id = context_org;
1330 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1332 WHILE tmp_depth >= tmp_penalty.org_depth LOOP
1336 FROM actor.usr_standing_penalty
1337 WHERE usr = match_user
1338 AND org_unit = tmp_org.id
1339 AND (stop_date IS NULL or stop_date > NOW())
1340 AND standing_penalty = 30;
1342 IF tmp_org.parent_ou IS NULL THEN
1346 SELECT * INTO tmp_org FROM actor.org_unit WHERE id = tmp_org.parent_ou;
1347 SELECT INTO tmp_depth depth FROM actor.org_unit_type WHERE id = tmp_org.ou_type;
1352 -- no penalty depth is defined, look for exact matches
1356 FROM actor.usr_standing_penalty
1357 WHERE usr = match_user
1358 AND org_unit = max_fines.org_unit
1359 AND (stop_date IS NULL or stop_date > NOW())
1360 AND standing_penalty = 30;
1369 $func$ LANGUAGE plpgsql;
1372 -- from 110.hold_matrix.sql
1374 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$
1377 user_object actor.usr%ROWTYPE;
1378 age_protect_object config.rule_age_hold_protect%ROWTYPE;
1379 standing_penalty config.standing_penalty%ROWTYPE;
1380 transit_range_ou_type actor.org_unit_type%ROWTYPE;
1381 transit_source actor.org_unit%ROWTYPE;
1382 item_object asset.copy%ROWTYPE;
1383 item_cn_object asset.call_number%ROWTYPE;
1384 item_status_object config.copy_status%ROWTYPE;
1385 item_location_object asset.copy_location%ROWTYPE;
1386 ou_skip actor.org_unit_setting%ROWTYPE;
1387 result action.matrix_test_result;
1388 hold_test config.hold_matrix_matchpoint%ROWTYPE;
1389 use_active_date TEXT;
1390 age_protect_date TIMESTAMP WITH TIME ZONE;
1392 hold_transit_prox INT;
1393 frozen_hold_count INT;
1394 context_org_list INT[];
1398 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
1399 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( pickup_ou );
1401 result.success := TRUE;
1403 -- The HOLD penalty block only applies to new holds.
1404 -- The CAPTURE penalty block applies to existing holds.
1405 hold_penalty := 'HOLD';
1406 IF retargetting THEN
1407 hold_penalty := 'CAPTURE';
1410 -- Fail if we couldn't find a user
1411 IF user_object.id IS NULL THEN
1412 result.fail_part := 'no_user';
1413 result.success := FALSE;
1419 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
1421 -- Fail if we couldn't find a copy
1422 IF item_object.id IS NULL THEN
1423 result.fail_part := 'no_item';
1424 result.success := FALSE;
1430 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(pickup_ou, request_ou, match_item, match_user, match_requestor);
1431 result.matchpoint := matchpoint_id;
1433 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
1435 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
1436 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
1437 result.fail_part := 'circ.holds.target_skip_me';
1438 result.success := FALSE;
1444 -- Fail if user is barred
1445 IF user_object.barred IS TRUE THEN
1446 result.fail_part := 'actor.usr.barred';
1447 result.success := FALSE;
1453 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1454 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
1455 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
1457 -- Fail if we couldn't find any matchpoint (requires a default)
1458 IF matchpoint_id IS NULL THEN
1459 result.fail_part := 'no_matchpoint';
1460 result.success := FALSE;
1466 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
1468 IF hold_test.holdable IS FALSE THEN
1469 result.fail_part := 'config.hold_matrix_test.holdable';
1470 result.success := FALSE;
1475 IF item_object.holdable IS FALSE THEN
1476 result.fail_part := 'item.holdable';
1477 result.success := FALSE;
1482 IF item_status_object.holdable IS FALSE THEN
1483 result.fail_part := 'status.holdable';
1484 result.success := FALSE;
1489 IF item_location_object.holdable IS FALSE THEN
1490 result.fail_part := 'location.holdable';
1491 result.success := FALSE;
1496 IF hold_test.transit_range IS NOT NULL THEN
1497 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
1498 IF hold_test.distance_is_from_owner THEN
1499 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;
1501 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
1504 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = pickup_ou;
1507 result.fail_part := 'transit_range';
1508 result.success := FALSE;
1514 FOR standing_penalty IN
1515 SELECT DISTINCT csp.*
1516 FROM actor.usr_standing_penalty usp
1517 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1518 WHERE usr = match_user
1519 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1520 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1521 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
1523 result.fail_part := standing_penalty.name;
1524 result.success := FALSE;
1529 IF hold_test.stop_blocked_user IS TRUE THEN
1530 FOR standing_penalty IN
1531 SELECT DISTINCT csp.*
1532 FROM actor.usr_standing_penalty usp
1533 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
1534 WHERE usr = match_user
1535 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
1536 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
1537 AND csp.block_list LIKE '%CIRC%' LOOP
1539 result.fail_part := standing_penalty.name;
1540 result.success := FALSE;
1546 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
1547 SELECT INTO hold_count COUNT(*)
1548 FROM action.hold_request
1549 WHERE usr = match_user
1550 AND fulfillment_time IS NULL
1551 AND cancel_time IS NULL
1552 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
1554 IF hold_count >= hold_test.max_holds THEN
1555 result.fail_part := 'config.hold_matrix_test.max_holds';
1556 result.success := FALSE;
1562 IF item_object.age_protect IS NOT NULL THEN
1563 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
1564 IF hold_test.distance_is_from_owner THEN
1565 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
1567 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
1569 IF use_active_date = 'true' THEN
1570 age_protect_date := COALESCE(item_object.active_date, NOW());
1572 age_protect_date := item_object.create_date;
1574 IF age_protect_date + age_protect_object.age > NOW() THEN
1575 IF hold_test.distance_is_from_owner THEN
1576 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
1577 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_cn_object.owning_lib AND to_org = pickup_ou;
1579 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = pickup_ou;
1582 IF hold_transit_prox > age_protect_object.prox THEN
1583 result.fail_part := 'config.rule_age_hold_protect.prox';
1584 result.success := FALSE;
1597 $func$ LANGUAGE plpgsql;
1600 -- from 300.schema.staged_search.sql
1603 -- from 990.schema.unapi.sql
1605 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT)
1606 RETURNS anyarray AS $$
1607 SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
1608 $$ LANGUAGE SQL STABLE;
1611 -- from 999.functions.global.sql
1613 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
1615 moved_objects INT := 0;
1616 source_cn asset.call_number%ROWTYPE;
1617 target_cn asset.call_number%ROWTYPE;
1618 metarec metabib.metarecord%ROWTYPE;
1619 hold action.hold_request%ROWTYPE;
1620 ser_rec serial.record_entry%ROWTYPE;
1621 ser_sub serial.subscription%ROWTYPE;
1622 acq_lineitem acq.lineitem%ROWTYPE;
1623 acq_request acq.user_request%ROWTYPE;
1624 booking booking.resource_type%ROWTYPE;
1625 source_part biblio.monograph_part%ROWTYPE;
1626 target_part biblio.monograph_part%ROWTYPE;
1627 multi_home biblio.peer_bib_copy_map%ROWTYPE;
1631 uri_text TEXT := '';
1634 -- move any 856 entries on records that have at least one MARC-mapped URI entry
1635 SELECT INTO uri_count COUNT(*)
1636 FROM asset.uri_call_number_map m
1637 JOIN asset.call_number cn ON (m.call_number = cn.id)
1638 WHERE cn.record = source_record;
1640 IF uri_count > 0 THEN
1642 -- This returns more nodes than you might expect:
1643 -- 7 instead of 1 for an 856 with $u $y $9
1644 SELECT COUNT(*) INTO counter
1645 FROM oils_xpath_table(
1648 'biblio.record_entry',
1650 'id=' || source_record
1651 ) as t(i int,c text);
1653 FOR i IN 1 .. counter LOOP
1654 SELECT '<datafield xmlns="http://www.loc.gov/MARC21/slim"' ||
1656 ' ind1="' || FIRST(ind1) || '"' ||
1657 ' ind2="' || FIRST(ind2) || '">' ||
1659 '<subfield code="' || subfield || '">' ||
1662 regexp_replace(data,'&','&','g'),
1666 ) || '</subfield>', ''
1667 ) || '</datafield>' INTO uri_datafield
1668 FROM oils_xpath_table(
1671 'biblio.record_entry',
1672 '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
1673 '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
1674 '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
1675 '//*[@tag="856"][position()=' || i || ']/*[@code]',
1676 'id=' || source_record
1677 ) as t(id int,ind1 text, ind2 text,subfield text,data text);
1679 -- As most of the results will be NULL, protect against NULLifying
1680 -- the valid content that we do generate
1681 uri_text := uri_text || COALESCE(uri_datafield, '');
1684 IF uri_text <> '' THEN
1685 UPDATE biblio.record_entry
1686 SET marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
1687 WHERE id = target_record;
1692 -- Find and move metarecords to the target record
1693 SELECT INTO metarec *
1694 FROM metabib.metarecord
1695 WHERE master_record = source_record;
1698 UPDATE metabib.metarecord
1699 SET master_record = target_record,
1701 WHERE id = metarec.id;
1703 moved_objects := moved_objects + 1;
1706 -- Find call numbers attached to the source ...
1707 FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
1709 SELECT INTO target_cn *
1710 FROM asset.call_number
1711 WHERE label = source_cn.label
1712 AND owning_lib = source_cn.owning_lib
1713 AND record = target_record
1716 -- ... and if there's a conflicting one on the target ...
1719 -- ... move the copies to that, and ...
1721 SET call_number = target_cn.id
1722 WHERE call_number = source_cn.id;
1724 -- ... move V holds to the move-target call number
1725 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
1727 UPDATE action.hold_request
1728 SET target = target_cn.id
1731 moved_objects := moved_objects + 1;
1736 -- ... just move the call number to the target record
1737 UPDATE asset.call_number
1738 SET record = target_record
1739 WHERE id = source_cn.id;
1742 moved_objects := moved_objects + 1;
1745 -- Find T holds targeting the source record ...
1746 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
1748 -- ... and move them to the target record
1749 UPDATE action.hold_request
1750 SET target = target_record
1753 moved_objects := moved_objects + 1;
1756 -- Find serial records targeting the source record ...
1757 FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
1758 -- ... and move them to the target record
1759 UPDATE serial.record_entry
1760 SET record = target_record
1761 WHERE id = ser_rec.id;
1763 moved_objects := moved_objects + 1;
1766 -- Find serial subscriptions targeting the source record ...
1767 FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
1768 -- ... and move them to the target record
1769 UPDATE serial.subscription
1770 SET record_entry = target_record
1771 WHERE id = ser_sub.id;
1773 moved_objects := moved_objects + 1;
1776 -- Find booking resource types targeting the source record ...
1777 FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
1778 -- ... and move them to the target record
1779 UPDATE booking.resource_type
1780 SET record = target_record
1781 WHERE id = booking.id;
1783 moved_objects := moved_objects + 1;
1786 -- Find acq lineitems targeting the source record ...
1787 FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
1788 -- ... and move them to the target record
1790 SET eg_bib_id = target_record
1791 WHERE id = acq_lineitem.id;
1793 moved_objects := moved_objects + 1;
1796 -- Find acq user purchase requests targeting the source record ...
1797 FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
1798 -- ... and move them to the target record
1799 UPDATE acq.user_request
1800 SET eg_bib = target_record
1801 WHERE id = acq_request.id;
1803 moved_objects := moved_objects + 1;
1806 -- Find parts attached to the source ...
1807 FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
1809 SELECT INTO target_part *
1810 FROM biblio.monograph_part
1811 WHERE label = source_part.label
1812 AND record = target_record;
1814 -- ... and if there's a conflicting one on the target ...
1817 -- ... move the copy-part maps to that, and ...
1818 UPDATE asset.copy_part_map
1819 SET part = target_part.id
1820 WHERE part = source_part.id;
1822 -- ... move P holds to the move-target part
1823 FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
1825 UPDATE action.hold_request
1826 SET target = target_part.id
1829 moved_objects := moved_objects + 1;
1834 -- ... just move the part to the target record
1835 UPDATE biblio.monograph_part
1836 SET record = target_record
1837 WHERE id = source_part.id;
1840 moved_objects := moved_objects + 1;
1843 -- Find multi_home items attached to the source ...
1844 FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
1845 -- ... and move them to the target record
1846 UPDATE biblio.peer_bib_copy_map
1847 SET peer_record = target_record
1848 WHERE id = multi_home.id;
1850 moved_objects := moved_objects + 1;
1853 -- And delete mappings where the item's home bib was merged with the peer bib
1854 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
1855 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
1856 FROM asset.copy WHERE id = target_copy
1859 -- Finally, "delete" the source record
1860 DELETE FROM biblio.record_entry WHERE id = source_record;
1862 -- That's all, folks!
1863 RETURN moved_objects;
1865 $func$ LANGUAGE plpgsql;
1867 -- from reporter-schema.sql
1869 CREATE OR REPLACE VIEW reporter.simple_record AS
1876 title.value AS title,
1877 uniform_title.value AS uniform_title,
1878 author.value AS author,
1879 publisher.value AS publisher,
1880 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
1881 series_title.value AS series_title,
1882 series_statement.value AS series_statement,
1883 summary.value AS summary,
1884 ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
1885 ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
1886 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
1887 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
1888 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
1889 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
1890 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
1891 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
1892 FROM biblio.record_entry r
1893 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
1894 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
1895 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1896 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
1897 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
1898 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
1899 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1900 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1901 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')
1902 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
1903 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
1904 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
1906 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
1912 FIRST(title.value) AS title,
1913 FIRST(author.value) AS author,
1914 STRING_AGG(DISTINCT publisher.value, ', ') AS publisher,
1915 STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate,
1916 CASE WHEN ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) = '{NULL}'
1918 ELSE ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') )
1920 CASE WHEN ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) = '{NULL}'
1922 ELSE ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') )
1924 FROM biblio.record_entry r
1925 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
1926 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
1927 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')
1928 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')
1929 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
1930 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
1935 SELECT evergreen.upgrade_deps_block_check('0856', :eg_version);
1937 CREATE OR REPLACE FUNCTION metabib.staged_browse(
1941 context_locations INT[],
1943 browse_superpage_size INT,
1944 count_up_from_zero BOOL, -- if false, count down from -1
1947 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
1955 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
1956 results_skipped INT := 0;
1957 row_counter INT := 0;
1962 all_records BIGINT[];
1963 all_brecords BIGINT[];
1964 all_arecords BIGINT[];
1965 superpage_of_records BIGINT[];
1968 IF count_up_from_zero THEN
1974 OPEN curs FOR EXECUTE query;
1977 FETCH curs INTO rec;
1979 IF result_row.pivot_point IS NOT NULL THEN
1980 RETURN NEXT result_row;
1986 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
1987 SELECT INTO all_arecords, result_row.sees, afields
1988 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
1989 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
1990 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
1992 FROM metabib.browse_entry_simple_heading_map mbeshm
1993 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
1994 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
1995 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
1996 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
1997 ash.atag = map.authority_field
1998 AND map.metabib_field = ANY(fields)
2000 WHERE mbeshm.entry = rec.id;
2003 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
2004 SELECT INTO all_brecords, result_row.authorities, bfields
2005 ARRAY_AGG(DISTINCT source),
2006 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
2007 ARRAY_AGG(DISTINCT def)
2008 FROM metabib.browse_entry_def_map
2009 WHERE entry = rec.id
2010 AND def = ANY(fields);
2012 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
2014 result_row.sources := 0;
2015 result_row.asources := 0;
2017 -- Bib-linked vis checking
2018 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
2020 full_end := ARRAY_LENGTH(all_brecords, 1);
2021 superpage_size := COALESCE(browse_superpage_size, full_end);
2023 slice_end := superpage_size;
2025 WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
2026 superpage_of_records := all_brecords[slice_start:slice_end];
2028 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2029 '1::INT AS rel FROM (SELECT UNNEST(' ||
2030 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2032 -- We use search.query_parser_fts() for visibility testing.
2033 -- We're calling it once per browse-superpage worth of records
2034 -- out of the set of records related to a given mbe, until we've
2035 -- either exhausted that set of records or found at least 1
2038 SELECT INTO result_row.sources visible
2039 FROM search.query_parser_fts(
2040 context_org, NULL, qpfts_query, NULL,
2041 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2043 WHERE qpfts.rel IS NULL;
2045 slice_start := slice_start + superpage_size;
2046 slice_end := slice_end + superpage_size;
2049 -- Accurate? Well, probably.
2050 result_row.accurate := browse_superpage_size IS NULL OR
2051 browse_superpage_size >= full_end;
2055 -- Authority-linked vis checking
2056 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
2058 full_end := ARRAY_LENGTH(all_arecords, 1);
2059 superpage_size := COALESCE(browse_superpage_size, full_end);
2061 slice_end := superpage_size;
2063 WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
2064 superpage_of_records := all_arecords[slice_start:slice_end];
2066 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
2067 '1::INT AS rel FROM (SELECT UNNEST(' ||
2068 quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
2070 -- We use search.query_parser_fts() for visibility testing.
2071 -- We're calling it once per browse-superpage worth of records
2072 -- out of the set of records related to a given mbe, via
2073 -- authority until we've either exhausted that set of records
2074 -- or found at least 1 visible record.
2076 SELECT INTO result_row.asources visible
2077 FROM search.query_parser_fts(
2078 context_org, NULL, qpfts_query, NULL,
2079 context_locations, 0, NULL, NULL, FALSE, staff, FALSE
2081 WHERE qpfts.rel IS NULL;
2083 slice_start := slice_start + superpage_size;
2084 slice_end := slice_end + superpage_size;
2088 -- Accurate? Well, probably.
2089 result_row.aaccurate := browse_superpage_size IS NULL OR
2090 browse_superpage_size >= full_end;
2094 IF result_row.sources > 0 OR result_row.asources > 0 THEN
2096 -- The function that calls this function needs row_number in order
2097 -- to correctly order results from two different runs of this
2099 result_row.row_number := row_number;
2101 -- Now, if row_counter is still less than limit, return a row. If
2102 -- not, but it is less than next_pivot_pos, continue on without
2103 -- returning actual result rows until we find
2104 -- that next pivot, and return it.
2106 IF row_counter < result_limit THEN
2107 result_row.browse_entry := rec.id;
2108 result_row.value := rec.value;
2110 RETURN NEXT result_row;
2112 result_row.browse_entry := NULL;
2113 result_row.authorities := NULL;
2114 result_row.fields := NULL;
2115 result_row.value := NULL;
2116 result_row.sources := NULL;
2117 result_row.sees := NULL;
2118 result_row.accurate := NULL;
2119 result_row.aaccurate := NULL;
2120 result_row.pivot_point := rec.id;
2122 IF row_counter >= next_pivot_pos THEN
2123 RETURN NEXT result_row;
2128 IF count_up_from_zero THEN
2129 row_number := row_number + 1;
2131 row_number := row_number - 1;
2134 -- row_counter is different from row_number.
2135 -- It simply counts up from zero so that we know when
2136 -- we've reached our limit.
2137 row_counter := row_counter + 1;
2141 $p$ LANGUAGE PLPGSQL;
2145 * Copyright (C) 2014 Equinox Software, Inc.
2146 * Mike Rylander <miker@esilibrary.com>
2148 * This program is free software; you can redistribute it and/or
2149 * modify it under the terms of the GNU General Public License
2150 * as published by the Free Software Foundation; either version 2
2151 * of the License, or (at your option) any later version.
2153 * This program is distributed in the hope that it will be useful,
2154 * but WITHOUT ANY WARRANTY; without even the implied warranty of
2155 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
2156 * GNU General Public License for more details.
2162 SELECT evergreen.upgrade_deps_block_check('0857', :eg_version);
2164 INSERT INTO config.global_flag (name, enabled, label)
2166 'opac.located_uri.act_as_copy',
2169 'opac.located_uri.act_as_copy',
2170 'When enabled, Located URIs will provide visiblity behavior identical to copies.',
2176 CREATE OR REPLACE FUNCTION search.query_parser_fts (
2178 param_search_ou INT,
2181 param_statuses INT[],
2182 param_locations INT[],
2188 deleted_search BOOL,
2189 param_pref_ou INT DEFAULT NULL
2190 ) RETURNS SETOF search.search_result AS $func$
2193 current_res search.search_result%ROWTYPE;
2194 search_org_list INT[];
2195 luri_org_list INT[];
2204 core_cursor REFCURSOR;
2205 core_rel_query TEXT;
2207 total_count INT := 0;
2208 check_count INT := 0;
2209 deleted_count INT := 0;
2210 visible_count INT := 0;
2211 excluded_count INT := 0;
2216 check_limit := COALESCE( param_check, 1000 );
2217 core_limit := COALESCE( param_limit, 25000 );
2218 core_offset := COALESCE( param_offset, 0 );
2220 SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
2222 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
2224 IF param_search_ou > 0 THEN
2225 IF param_depth IS NOT NULL THEN
2226 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
2228 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
2231 IF luri_as_copy THEN
2232 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
2234 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
2237 ELSIF param_search_ou < 0 THEN
2238 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
2240 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
2242 IF luri_as_copy THEN
2243 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
2245 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
2248 luri_org_list := luri_org_list || tmp_int_list;
2251 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
2253 ELSIF param_search_ou = 0 THEN
2254 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
2257 IF param_pref_ou IS NOT NULL THEN
2258 IF luri_as_copy THEN
2259 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
2261 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
2264 luri_org_list := luri_org_list || tmp_int_list;
2267 OPEN core_cursor FOR EXECUTE param_query;
2271 FETCH core_cursor INTO core_result;
2272 EXIT WHEN NOT FOUND;
2273 EXIT WHEN total_count >= core_limit;
2275 total_count := total_count + 1;
2277 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
2279 check_count := check_count + 1;
2281 IF NOT deleted_search THEN
2283 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
2285 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
2286 deleted_count := deleted_count + 1;
2291 FROM biblio.record_entry b
2292 JOIN config.bib_source s ON (b.source = s.id)
2293 WHERE s.transcendant
2294 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
2297 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
2298 visible_count := visible_count + 1;
2300 current_res.id = core_result.id;
2301 current_res.rel = core_result.rel;
2305 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2309 current_res.record = core_result.records[1];
2311 current_res.record = NULL;
2314 RETURN NEXT current_res;
2320 FROM asset.call_number cn
2321 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
2322 JOIN asset.uri uri ON (map.uri = uri.id)
2323 WHERE NOT cn.deleted
2324 AND cn.label = '##URI##'
2326 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
2327 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2328 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
2332 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
2333 visible_count := visible_count + 1;
2335 current_res.id = core_result.id;
2336 current_res.rel = core_result.rel;
2340 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2344 current_res.record = core_result.records[1];
2346 current_res.record = NULL;
2349 RETURN NEXT current_res;
2354 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
2357 FROM asset.call_number cn
2358 JOIN asset.copy cp ON (cp.call_number = cn.id)
2359 WHERE NOT cn.deleted
2361 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
2362 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2363 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2368 FROM biblio.peer_bib_copy_map pr
2369 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2370 WHERE NOT cp.deleted
2371 AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
2372 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2373 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2377 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
2378 excluded_count := excluded_count + 1;
2385 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
2388 FROM asset.call_number cn
2389 JOIN asset.copy cp ON (cp.call_number = cn.id)
2390 WHERE NOT cn.deleted
2392 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
2393 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2394 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2399 FROM biblio.peer_bib_copy_map pr
2400 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2401 WHERE NOT cp.deleted
2402 AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
2403 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2404 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2408 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
2409 excluded_count := excluded_count + 1;
2416 IF staff IS NULL OR NOT staff THEN
2419 FROM asset.opac_visible_copies
2420 WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2421 AND record IN ( SELECT * FROM unnest( core_result.records ) )
2426 FROM biblio.peer_bib_copy_map pr
2427 JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
2428 WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2429 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2434 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
2435 excluded_count := excluded_count + 1;
2443 FROM asset.call_number cn
2444 JOIN asset.copy cp ON (cp.call_number = cn.id)
2445 WHERE NOT cn.deleted
2447 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2448 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2454 FROM biblio.peer_bib_copy_map pr
2455 JOIN asset.copy cp ON (cp.id = pr.target_copy)
2456 WHERE NOT cp.deleted
2457 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
2458 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
2464 FROM asset.call_number cn
2465 JOIN asset.copy cp ON (cp.call_number = cn.id)
2466 WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
2471 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
2472 excluded_count := excluded_count + 1;
2483 visible_count := visible_count + 1;
2485 current_res.id = core_result.id;
2486 current_res.rel = core_result.rel;
2490 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
2494 current_res.record = core_result.records[1];
2496 current_res.record = NULL;
2499 RETURN NEXT current_res;
2501 IF visible_count % 1000 = 0 THEN
2502 -- RAISE NOTICE ' % visible so far ... ', visible_count;
2507 current_res.id = NULL;
2508 current_res.rel = NULL;
2509 current_res.record = NULL;
2510 current_res.total = total_count;
2511 current_res.checked = check_count;
2512 current_res.deleted = deleted_count;
2513 current_res.visible = visible_count;
2514 current_res.excluded = excluded_count;
2518 RETURN NEXT current_res;
2521 $func$ LANGUAGE PLPGSQL;
2523 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
2527 depth INT DEFAULT NULL,
2528 includes TEXT[] DEFAULT NULL::TEXT[],
2529 slimit HSTORE DEFAULT NULL,
2530 soffset HSTORE DEFAULT NULL,
2531 include_xmlns BOOL DEFAULT TRUE,
2532 pref_lib INT DEFAULT NULL
2538 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
2539 CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
2540 (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
2544 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
2547 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2549 FROM asset.opac_ou_record_copy_count($2, $1)
2553 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2555 FROM asset.staff_ou_record_copy_count($2, $1)
2559 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
2561 FROM asset.opac_ou_record_copy_count($9, $1)
2566 WHEN ('bmp' = ANY ($5)) THEN
2568 name monograph_parts,
2569 (SELECT XMLAGG(bmp) FROM (
2570 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)
2571 FROM biblio.monograph_part
2579 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
2581 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
2582 FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
2585 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
2586 FROM evergreen.located_uris($1, $2, $9) AS uris
2589 CASE WHEN ('ssub' = ANY ($5)) THEN
2592 (SELECT XMLAGG(ssub) FROM (
2593 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
2594 FROM serial.subscription
2595 WHERE record_entry = $1
2599 CASE WHEN ('acp' = ANY ($5)) THEN
2601 name foreign_copies,
2602 (SELECT XMLAGG(acp) FROM (
2603 SELECT unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
2604 FROM biblio.peer_bib_copy_map p
2605 JOIN asset.copy c ON (p.target_copy = c.id)
2606 WHERE NOT c.deleted AND p.peer_record = $1
2607 LIMIT ($6 -> 'acp')::INT
2608 OFFSET ($7 -> 'acp')::INT
2613 $F$ LANGUAGE SQL STABLE;
2617 SELECT evergreen.upgrade_deps_block_check('0858', :eg_version);
2619 -- Fix faulty seed data. Otherwise for ptype 'f' we have subfield 'e'
2620 -- overlapping subfield 'd'
2621 UPDATE config.marc21_physical_characteristic_subfield_map
2623 WHERE ptype_key = 'f' AND subfield = 'e';
2625 -- Evergreen DB patch 0859.data.staff-initials-settings.sql
2627 -- More granular configuration settings for requiring use of staff initials
2630 -- check whether patch can be applied
2631 SELECT evergreen.upgrade_deps_block_check('0859', :eg_version);
2633 -- add new granular settings for requiring use of staff initials
2634 INSERT INTO config.org_unit_setting_type
2635 (name, grp, label, description, datatype)
2637 'ui.staff.require_initials.patron_standing_penalty',
2640 'ui.staff.require_initials.patron_standing_penalty',
2641 'Require staff initials for entry/edit of patron standing penalties and messages.',
2646 'ui.staff.require_initials.patron_standing_penalty',
2647 'Appends staff initials and edit date into patron standing penalties and messages.',
2653 'ui.staff.require_initials.patron_info_notes',
2656 'ui.staff.require_initials.patron_info_notes',
2657 'Require staff initials for entry/edit of patron notes.',
2662 'ui.staff.require_initials.patron_info_notes',
2663 'Appends staff initials and edit date into patron note content.',
2669 'ui.staff.require_initials.copy_notes',
2672 'ui.staff.require_initials.copy_notes',
2673 'Require staff initials for entry/edit of copy notes.',
2678 'ui.staff.require_initials.copy_notes',
2679 'Appends staff initials and edit date into copy note content..',
2686 -- Update any existing setting so that the original set value is now passed to
2687 -- one of the newer settings.
2689 UPDATE actor.org_unit_setting
2690 SET name = 'ui.staff.require_initials.patron_standing_penalty'
2691 WHERE name = 'ui.staff.require_initials';
2693 -- Add similar values for new settings as old ones to preserve existing configured
2696 INSERT INTO actor.org_unit_setting (org_unit, name, value)
2697 SELECT org_unit, 'ui.staff.require_initials.patron_info_notes', value
2698 FROM actor.org_unit_setting
2699 WHERE name = 'ui.staff.require_initials.patron_standing_penalty';
2701 INSERT INTO actor.org_unit_setting (org_unit, name, value)
2702 SELECT org_unit, 'ui.staff.require_initials.copy_notes', value
2703 FROM actor.org_unit_setting
2704 WHERE name = 'ui.staff.require_initials.patron_standing_penalty';
2706 -- Update setting logs so that the original setting name's history is now transferred
2707 -- over to one of the newer settings.
2709 UPDATE config.org_unit_setting_type_log
2710 SET field_name = 'ui.staff.require_initials.patron_standing_penalty'
2711 WHERE field_name = 'ui.staff.require_initials';
2713 -- Remove the old setting entirely
2715 DELETE FROM config.org_unit_setting_type WHERE name = 'ui.staff.require_initials';
2719 SELECT evergreen.upgrade_deps_block_check('0860', :eg_version);
2721 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
2727 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
2729 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
2733 $$ LANGUAGE PLPGSQL;
2736 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
2737 SELECT DISTINCT l.version
2738 FROM config.upgrade_log l
2739 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
2740 WHERE d.db_patch = $1
2743 -- List applied db patches that are superseded by (and block the application of) my_db_patch
2744 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
2745 SELECT DISTINCT l.version
2746 FROM config.upgrade_log l
2747 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
2748 WHERE d.db_patch = $1
2752 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
2757 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
2758 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
2759 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
2761 Upgrade script % can not be applied:
2762 applied deprecated scripts %
2763 applied superseded scripts %
2767 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
2768 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
2769 evergreen.upgrade_list_applied_deprecated(my_db_patch),
2770 evergreen.upgrade_list_applied_superseded(my_db_patch);
2773 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
2776 $$ LANGUAGE PLPGSQL;
2780 SELECT evergreen.upgrade_deps_block_check('0861', :eg_version);
2782 CREATE INDEX authority_record_entry_create_date_idx ON authority.record_entry ( create_date );
2783 CREATE INDEX authority_record_entry_edit_date_idx ON authority.record_entry ( edit_date );
2787 SELECT evergreen.upgrade_deps_block_check('0863', :eg_version);
2790 -- cheat sheet for enabling Stripe payments:
2791 -- 'credit.payments.allow' must be true, and among other things it drives the
2792 -- opac to render a payment form at all
2793 -- NEW 'credit.processor.stripe.enabled' must be true (kind of redundant but
2794 -- my fault for setting the precedent with c.p.{authorizenet|paypal|payflowpro}.enabled)
2795 -- 'credit.default.processor' must be 'Stripe'
2796 -- NEW 'credit.processor.stripe.pubkey' must be set
2797 -- NEW 'credit.processor.stripe.secretkey' must be set
2799 INSERT into config.org_unit_setting_type
2800 ( name, grp, label, description, datatype, fm_class ) VALUES
2802 ( 'credit.processor.stripe.enabled', 'credit',
2803 oils_i18n_gettext('credit.processor.stripe.enabled',
2804 'Enable Stripe payments',
2806 oils_i18n_gettext('credit.processor.stripe.enabled',
2807 'Enable Stripe payments',
2808 'coust', 'description'),
2811 ,( 'credit.processor.stripe.pubkey', 'credit',
2812 oils_i18n_gettext('credit.processor.stripe.pubkey',
2813 'Stripe publishable key',
2815 oils_i18n_gettext('credit.processor.stripe.pubkey',
2816 'Stripe publishable key',
2817 'coust', 'description'),
2820 ,( 'credit.processor.stripe.secretkey', 'credit',
2821 oils_i18n_gettext('credit.processor.stripe.secretkey',
2822 'Stripe secret key',
2824 oils_i18n_gettext('credit.processor.stripe.secretkey',
2825 'Stripe secret key',
2826 'coust', 'description'),
2830 UPDATE config.org_unit_setting_type
2831 SET description = 'This might be "AuthorizeNet", "PayPal", "PayflowPro", or "Stripe".'
2832 WHERE name = 'credit.processor.default' AND description = 'This might be "AuthorizeNet", "PayPal", etc.'; -- don't clobber local edits or i18n
2835 SELECT evergreen.upgrade_deps_block_check('0864', :eg_version);
2837 CREATE EXTENSION IF NOT EXISTS intarray WITH SCHEMA public;
2839 -- while we have this opportunity, and before we start collecting
2840 -- CCVM IDs (below) carve out a nice space for stock ccvm values
2841 UPDATE config.coded_value_map SET id = id + 10000 WHERE id > 556;
2842 SELECT SETVAL('config.coded_value_map_id_seq'::TEXT,
2843 (SELECT GREATEST(max(id), 10000) FROM config.coded_value_map));
2845 ALTER TABLE config.record_attr_definition ADD COLUMN multi BOOL NOT NULL DEFAULT TRUE, ADD COLUMN composite BOOL NOT NULL DEFAULT FALSE;
2847 UPDATE config.record_attr_definition
2849 WHERE name IN ('bib_level','control_type','pubdate','cat_form','enc_level','item_type','titlesort','authorsort');
2851 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
2855 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
2856 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
2857 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
2858 retval biblio.marc21_physical_characteristics%ROWTYPE;
2861 FOR _007 IN SELECT oils_xpath_string('//*', value) FROM UNNEST(oils_xpath('//*[@tag="007"]', marc)) x(value) LOOP
2862 IF _007 IS NOT NULL AND _007 <> '' THEN
2863 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
2865 IF ptype.ptype_key IS NOT NULL THEN
2866 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
2867 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 );
2869 IF pval.id IS NOT NULL THEN
2872 retval.ptype := ptype.ptype_key;
2873 retval.subfield := psf.id;
2874 retval.value := pval.id;
2885 $func$ LANGUAGE PLPGSQL;
2887 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field_list( marc TEXT, ff TEXT ) RETURNS TEXT[] AS $func$
2893 collection TEXT[] := '{}'::TEXT[];
2895 rtype := (vandelay.marc21_record_type( marc )).code;
2896 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
2897 IF ff_pos.tag = 'ldr' THEN
2898 val := oils_xpath_string('//*[local-name()="leader"]', marc);
2899 IF val IS NOT NULL THEN
2900 val := SUBSTRING( val, ff_pos.start_pos + 1, ff_pos.length );
2901 collection := collection || val;
2904 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(ff_pos.tag) || '"]/text()', marc ) ) x(value) LOOP
2905 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
2906 collection := collection || val;
2909 val := REPEAT( ff_pos.default_val, ff_pos.length );
2910 collection := collection || val;
2915 $func$ LANGUAGE PLPGSQL;
2917 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field_list( rid BIGINT, ff TEXT ) RETURNS TEXT[] AS $func$
2918 SELECT * FROM vandelay.marc21_extract_fixed_field_list( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
2919 $func$ LANGUAGE SQL;
2921 -- DECREMENTING serial starts at -1
2922 CREATE SEQUENCE metabib.uncontrolled_record_attr_value_id_seq INCREMENT BY -1;
2924 CREATE TABLE metabib.uncontrolled_record_attr_value (
2925 id BIGINT PRIMARY KEY DEFAULT nextval('metabib.uncontrolled_record_attr_value_id_seq'),
2926 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name),
2929 CREATE UNIQUE INDEX muv_once_idx ON metabib.uncontrolled_record_attr_value (attr,value);
2931 CREATE TABLE metabib.record_attr_vector_list (
2932 source BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id),
2933 vlist INT[] NOT NULL -- stores id from ccvm AND murav
2935 CREATE INDEX mrca_vlist_idx ON metabib.record_attr_vector_list USING gin ( vlist gin__int_ops );
2937 CREATE TABLE metabib.record_sorter (
2938 id BIGSERIAL PRIMARY KEY,
2939 source BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
2940 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE,
2943 CREATE INDEX metabib_sorter_source_idx ON metabib.record_sorter (source); -- we may not need one of this or the next ... stats will tell
2944 CREATE INDEX metabib_sorter_s_a_idx ON metabib.record_sorter (source, attr);
2945 CREATE INDEX metabib_sorter_a_v_idx ON metabib.record_sorter (attr, value);
2947 CREATE TEMP TABLE attr_set ON COMMIT DROP AS SELECT DISTINCT id AS source, (each(attrs)).key,(each(attrs)).value FROM metabib.record_attr;
2948 DELETE FROM attr_set WHERE BTRIM(value) = '';
2950 -- Grab sort values for the new sorting mechanism
2951 INSERT INTO metabib.record_sorter (source,attr,value)
2952 SELECT a.source, a.key, a.value
2954 JOIN config.record_attr_definition d ON (d.name = a.key AND d.sorter AND a.value IS NOT NULL);
2956 -- Rewrite uncontrolled SVF record attrs as the seeds of an intarray vector
2957 INSERT INTO metabib.uncontrolled_record_attr_value (attr,value)
2958 SELECT DISTINCT a.key, a.value
2960 JOIN config.record_attr_definition d ON (d.name = a.key AND d.filter AND a.value IS NOT NULL)
2961 LEFT JOIN config.coded_value_map m ON (m.ctype = a.key)
2964 -- Now construct the record-specific vector from the SVF data
2965 INSERT INTO metabib.record_attr_vector_list (source,vlist)
2966 SELECT a.id, ARRAY_AGG(COALESCE(u.id, c.id))
2967 FROM metabib.record_attr a
2968 JOIN attr_set ON (a.id = attr_set.source)
2969 LEFT JOIN metabib.uncontrolled_record_attr_value u ON (u.attr = attr_set.key AND u.value = attr_set.value)
2970 LEFT JOIN config.coded_value_map c ON (c.ctype = attr_set.key AND c.code = attr_set.value)
2971 WHERE COALESCE(u.id,c.id) IS NOT NULL
2974 DROP VIEW IF EXISTS reporter.classic_current_circ;
2975 DROP VIEW metabib.rec_descriptor;
2976 DROP TABLE metabib.record_attr;
2978 CREATE TYPE metabib.record_attr_type AS (
2983 CREATE TABLE config.composite_attr_entry_definition(
2984 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
2985 definition TEXT NOT NULL -- JSON
2988 CREATE OR REPLACE VIEW metabib.record_attr_id_map AS
2989 SELECT id, attr, value FROM metabib.uncontrolled_record_attr_value
2991 SELECT c.id, c.ctype AS attr, c.code AS value
2992 FROM config.coded_value_map c
2993 JOIN config.record_attr_definition d ON (d.name = c.ctype AND NOT d.composite);
2995 CREATE VIEW metabib.composite_attr_id_map AS
2996 SELECT c.id, c.ctype AS attr, c.code AS value
2997 FROM config.coded_value_map c
2998 JOIN config.record_attr_definition d ON (d.name = c.ctype AND d.composite);
3000 CREATE OR REPLACE VIEW metabib.full_attr_id_map AS
3001 SELECT id, attr, value FROM metabib.record_attr_id_map
3003 SELECT id, attr, value FROM metabib.composite_attr_id_map;
3006 -- Back-compat view ... we're moving to an INTARRAY world
3007 CREATE VIEW metabib.record_attr_flat AS
3008 SELECT v.source AS id,
3011 FROM metabib.full_attr_id_map m
3012 JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) );
3014 CREATE VIEW metabib.record_attr AS
3015 SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1;
3017 CREATE VIEW metabib.rec_descriptor AS
3020 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
3021 FROM metabib.record_attr;
3023 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_init () RETURNS BOOL AS $f$
3024 $_SHARED{metabib_compile_composite_attr_cache} = {}
3025 if ! exists $_SHARED{metabib_compile_composite_attr_cache};
3026 return exists $_SHARED{metabib_compile_composite_attr_cache};
3027 $f$ LANGUAGE PLPERLU;
3029 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_disable () RETURNS BOOL AS $f$
3030 delete $_SHARED{metabib_compile_composite_attr_cache};
3031 return ! exists $_SHARED{metabib_compile_composite_attr_cache};
3032 $f$ LANGUAGE PLPERLU;
3034 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr_cache_invalidate () RETURNS BOOL AS $f$
3035 SELECT metabib.compile_composite_attr_cache_disable() AND metabib.compile_composite_attr_cache_init();
3038 CREATE OR REPLACE FUNCTION metabib.composite_attr_def_cache_inval_tgr () RETURNS TRIGGER AS $f$
3040 PERFORM metabib.compile_composite_attr_cache_invalidate();
3043 $f$ LANGUAGE PLPGSQL;
3045 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();
3047 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_def TEXT ) RETURNS query_int AS $func$
3052 my $def = decode_json($json);
3054 die("Composite attribute definition not supplied") unless $def;
3056 my $_cache = (exists $_SHARED{metabib_compile_composite_attr_cache}) ? 1 : 0;
3058 return $_SHARED{metabib_compile_composite_attr_cache}{$json}
3059 if ($_cache && $_SHARED{metabib_compile_composite_attr_cache}{$json});
3066 if (ref $d eq 'HASH') { # node or AND
3067 if (exists $d->{_attr}) { # it is a node
3068 my $plan = spi_prepare('SELECT * FROM metabib.full_attr_id_map WHERE attr = $1 AND value = $2', qw/TEXT TEXT/);
3069 my $id = spi_exec_prepared(
3070 $plan, {limit => 1}, $d->{_attr}, $d->{_val}
3072 spi_freeplan($plan);
3074 } elsif (exists $d->{_not} && scalar(keys(%$d)) == 1) { # it is a NOT
3075 return '!' . recurse($$d{_not});
3076 } else { # an AND list
3077 @list = map { recurse($$d{$_}) } sort keys %$d;
3079 } elsif (ref $d eq 'ARRAY') {
3081 @list = map { recurse($_) } @$d;
3084 @list = grep { defined && $_ ne '' } @list;
3086 return '(' . join($j,@list) . ')' if @list;
3090 my $val = recurse($def) || undef;
3091 $_SHARED{metabib_compile_composite_attr_cache}{$json} = $val if $_cache;
3094 $func$ IMMUTABLE LANGUAGE plperlu;
3096 CREATE OR REPLACE FUNCTION metabib.compile_composite_attr ( cattr_id INT ) RETURNS query_int AS $func$
3097 SELECT metabib.compile_composite_attr(definition) FROM config.composite_attr_entry_definition WHERE coded_value = $1;
3098 $func$ STRICT IMMUTABLE LANGUAGE SQL;
3101 CREATE OR REPLACE FUNCTION public.oils_tsearch2 () RETURNS TRIGGER AS $$
3105 temp_vector TEXT := '';
3111 NEW.index_vector = ''::tsvector;
3113 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
3115 SELECT n.func AS func,
3116 n.param_count AS param_count,
3118 FROM config.index_normalizer n
3119 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
3120 WHERE field = NEW.field AND m.pos < 0
3122 EXECUTE 'SELECT ' || normalizer.func || '(' ||
3123 quote_literal( value ) ||
3125 WHEN normalizer.param_count > 0
3126 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
3136 SELECT n.func AS func,
3137 n.param_count AS param_count,
3139 FROM config.index_normalizer n
3140 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
3141 WHERE field = NEW.field AND m.pos >= 0
3143 EXECUTE 'SELECT ' || normalizer.func || '(' ||
3144 quote_literal( value ) ||
3146 WHEN normalizer.param_count > 0
3147 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
3155 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
3157 value := ARRAY_TO_STRING(
3158 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
3160 value := public.search_normalize(value);
3161 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
3163 ELSIF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
3166 SELECT DISTINCT m.ts_config, m.index_weight
3167 FROM config.metabib_class_ts_map m
3168 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
3169 LEFT JOIN config.coded_value_map ccvm ON (
3170 ccvm.ctype IN ('item_lang', 'language') AND
3171 ccvm.code = m.index_lang AND
3172 r.vlist @> intset(ccvm.id)
3174 WHERE m.field_class = TG_ARGV[0]
3176 AND (m.always OR NOT EXISTS (SELECT 1 FROM config.metabib_field_ts_map WHERE metabib_field = NEW.field))
3177 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
3179 SELECT DISTINCT m.ts_config, m.index_weight
3180 FROM config.metabib_field_ts_map m
3181 LEFT JOIN metabib.record_attr_vector_list r ON (r.source = NEW.source)
3182 LEFT JOIN config.coded_value_map ccvm ON (
3183 ccvm.ctype IN ('item_lang', 'language') AND
3184 ccvm.code = m.index_lang AND
3185 r.vlist @> intset(ccvm.id)
3187 WHERE m.metabib_field = NEW.field
3189 AND (m.index_lang IS NULL OR ccvm.id IS NOT NULL)
3190 ORDER BY index_weight ASC
3194 IF cur_weight IS NOT NULL AND cur_weight != ts_rec.index_weight THEN
3195 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
3199 cur_weight = ts_rec.index_weight;
3200 SELECT INTO temp_vector temp_vector || ' ' || to_tsvector(ts_rec.ts_config::regconfig, value)::TEXT;
3203 NEW.index_vector = NEW.index_vector || setweight(temp_vector::tsvector,cur_weight);
3205 NEW.index_vector = to_tsvector(TG_ARGV[0]::regconfig, value);
3210 $$ LANGUAGE PLPGSQL;
3212 -- add new sr_format attribute definition
3214 INSERT INTO config.record_attr_definition (name, label, phys_char_sf)
3217 oils_i18n_gettext('sr_format', 'Sound recording format', 'crad', 'label'),
3221 INSERT INTO config.coded_value_map (id, ctype, code, value) VALUES
3222 (557, 'sr_format', 'a', oils_i18n_gettext(557, '16 rpm', 'ccvm', 'value')),
3223 (558, 'sr_format', 'b', oils_i18n_gettext(558, '33 1/3 rpm', 'ccvm', 'value')),
3224 (559, 'sr_format', 'c', oils_i18n_gettext(559, '45 rpm', 'ccvm', 'value')),
3225 (560, 'sr_format', 'f', oils_i18n_gettext(560, '1.4 m. per second', 'ccvm', 'value')),
3226 (561, 'sr_format', 'd', oils_i18n_gettext(561, '78 rpm', 'ccvm', 'value')),
3227 (562, 'sr_format', 'e', oils_i18n_gettext(562, '8 rpm', 'ccvm', 'value')),
3228 (563, 'sr_format', 'l', oils_i18n_gettext(563, '1 7/8 ips', 'ccvm', 'value')),
3229 (586, 'item_form', 'o', oils_i18n_gettext('586', 'Online', 'ccvm', 'value')),
3230 (587, 'item_form', 'q', oils_i18n_gettext('587', 'Direct electronic', 'ccvm', 'value'));
3232 INSERT INTO config.coded_value_map
3233 (id, ctype, code, value, search_label) VALUES
3234 (564, 'icon_format', 'book',
3235 oils_i18n_gettext(564, 'Book', 'ccvm', 'value'),
3236 oils_i18n_gettext(564, 'Book', 'ccvm', 'search_label')),
3237 (565, 'icon_format', 'braille',
3238 oils_i18n_gettext(565, 'Braille', 'ccvm', 'value'),
3239 oils_i18n_gettext(565, 'Braille', 'ccvm', 'search_label')),
3240 (566, 'icon_format', 'software',
3241 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'value'),
3242 oils_i18n_gettext(566, 'Software and video games', 'ccvm', 'search_label')),
3243 (567, 'icon_format', 'dvd',
3244 oils_i18n_gettext(567, 'DVD', 'ccvm', 'value'),
3245 oils_i18n_gettext(567, 'DVD', 'ccvm', 'search_label')),
3246 (568, 'icon_format', 'ebook',
3247 oils_i18n_gettext(568, 'E-book', 'ccvm', 'value'),
3248 oils_i18n_gettext(568, 'E-book', 'ccvm', 'search_label')),
3249 (569, 'icon_format', 'eaudio',
3250 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'value'),
3251 oils_i18n_gettext(569, 'E-audio', 'ccvm', 'search_label')),
3252 (570, 'icon_format', 'kit',
3253 oils_i18n_gettext(570, 'Kit', 'ccvm', 'value'),
3254 oils_i18n_gettext(570, 'Kit', 'ccvm', 'search_label')),
3255 (571, 'icon_format', 'map',
3256 oils_i18n_gettext(571, 'Map', 'ccvm', 'value'),
3257 oils_i18n_gettext(571, 'Map', 'ccvm', 'search_label')),
3258 (572, 'icon_format', 'microform',
3259 oils_i18n_gettext(572, 'Microform', 'ccvm', 'value'),
3260 oils_i18n_gettext(572, 'Microform', 'ccvm', 'search_label')),
3261 (573, 'icon_format', 'score',
3262 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'value'),
3263 oils_i18n_gettext(573, 'Music Score', 'ccvm', 'search_label')),
3264 (574, 'icon_format', 'picture',
3265 oils_i18n_gettext(574, 'Picture', 'ccvm', 'value'),
3266 oils_i18n_gettext(574, 'Picture', 'ccvm', 'search_label')),
3267 (575, 'icon_format', 'equip',
3268 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'value'),
3269 oils_i18n_gettext(575, 'Equipment, games, toys', 'ccvm', 'search_label')),
3270 (576, 'icon_format', 'serial',
3271 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'value'),
3272 oils_i18n_gettext(576, 'Serials and magazines', 'ccvm', 'search_label')),
3273 (577, 'icon_format', 'vhs',
3274 oils_i18n_gettext(577, 'VHS', 'ccvm', 'value'),
3275 oils_i18n_gettext(577, 'VHS', 'ccvm', 'search_label')),
3276 (578, 'icon_format', 'evideo',
3277 oils_i18n_gettext(578, 'E-video', 'ccvm', 'value'),
3278 oils_i18n_gettext(578, 'E-video', 'ccvm', 'search_label')),
3279 (579, 'icon_format', 'cdaudiobook',
3280 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'value'),
3281 oils_i18n_gettext(579, 'CD Audiobook', 'ccvm', 'search_label')),
3282 (580, 'icon_format', 'cdmusic',
3283 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'value'),
3284 oils_i18n_gettext(580, 'CD Music recording', 'ccvm', 'search_label')),
3285 (581, 'icon_format', 'casaudiobook',
3286 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'value'),
3287 oils_i18n_gettext(581, 'Cassette audiobook', 'ccvm', 'search_label')),
3288 (582, 'icon_format', 'casmusic',
3289 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'value'),
3290 oils_i18n_gettext(582, 'Audiocassette music recording', 'ccvm', 'search_label')),
3291 (583, 'icon_format', 'phonospoken',
3292 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'value'),
3293 oils_i18n_gettext(583, 'Phonograph spoken recording', 'ccvm', 'search_label')),
3294 (584, 'icon_format', 'phonomusic',
3295 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'value'),
3296 oils_i18n_gettext(584, 'Phonograph music recording', 'ccvm', 'search_label')),
3297 (585, 'icon_format', 'lpbook',
3298 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'value'),
3299 oils_i18n_gettext(585, 'Large Print Book', 'ccvm', 'search_label'))
3302 -- add the new icon format attribute definition
3304 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3308 'OPAC Format Icons Attribute',
3316 INSERT INTO config.record_attr_definition
3317 (name, label, multi, filter, composite) VALUES (
3321 'OPAC Format Icons',
3328 -- icon format composite definitions
3330 INSERT INTO config.composite_attr_entry_definition
3331 (coded_value, definition) VALUES
3333 (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"}]}'),
3336 (565, '{"0":{"_attr":"item_type","_val":"a"},"1":{"_attr":"item_form","_val":"f"}}'),
3339 (566, '{"_attr":"item_type","_val":"m"}'),
3342 (567, '{"_attr":"vr_format","_val":"v"}'),
3345 (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"}]}'),
3348 (569, '{"0":{"_attr":"item_type","_val":"i"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"q"},{"_attr":"item_form","_val":"s"}]}'),
3351 (570, '[{"_attr":"item_type","_val":"o"},{"_attr":"item_type","_val":"p"}]'),
3354 (571, '[{"_attr":"item_type","_val":"e"},{"_attr":"item_type","_val":"f"}]'),
3357 (572, '[{"_attr":"item_form","_val":"a"},{"_attr":"item_form","_val":"b"},{"_attr":"item_form","_val":"c"}]'),
3360 (573, '[{"_attr":"item_type","_val":"c"},{"_attr":"item_type","_val":"d"}]'),
3363 (574, '{"_attr":"item_type","_val":"k"}'),
3366 (575, '{"_attr":"item_type","_val":"r"}'),
3369 (576, '[{"_attr":"bib_level","_val":"b"},{"_attr":"bib_level","_val":"s"}]'),
3372 (577, '{"_attr":"vr_format","_val":"b"}'),
3375 (578, '{"0":{"_attr":"item_type","_val":"g"},"1":[{"_attr":"item_form","_val":"o"},{"_attr":"item_form","_val":"s"},{"_attr":"item_form","_val":"q"}]}'),
3378 (579, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"f"}}'),
3381 (580, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"f"}}'),
3384 (581, '{"0":{"_attr":"item_type","_val":"i"},"1":{"_attr":"sr_format","_val":"l"}}'),
3387 (582, '{"0":{"_attr":"item_type","_val":"j"},"1":{"_attr":"sr_format","_val":"l"}}'),
3390 (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"}]}'),
3393 (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"}]}'),
3396 (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"}]}');
3401 CREATE OR REPLACE FUNCTION unapi.mra (
3407 depth INT DEFAULT NULL,
3408 slimit HSTORE DEFAULT NULL,
3409 soffset HSTORE DEFAULT NULL,
3410 include_xmlns BOOL DEFAULT TRUE
3411 ) RETURNS XML AS $F$
3415 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
3416 'tag:open-ils.org:U2@mra/' || $1 AS id,
3417 'tag:open-ils.org:U2@bre/' || $1 AS record
3419 (SELECT XMLAGG(foo.y)
3425 cvm.value AS "coded-value",
3434 FROM metabib.record_attr_flat mra
3435 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
3436 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
3441 $F$ LANGUAGE SQL STABLE;
3444 SELECT evergreen.upgrade_deps_block_check('0865', :eg_version);
3446 -- First, explode the field into constituent parts
3447 WITH format_parts_array AS (
3449 STRING_TO_ARRAY(a.holdable_formats, '-') AS parts
3450 FROM action.hold_request a
3451 WHERE a.hold_type = 'M'
3452 AND a.fulfillment_time IS NULL
3453 ), format_parts_wide AS (
3455 regexp_split_to_array(parts[1], '') AS item_type,
3456 regexp_split_to_array(parts[2], '') AS item_form,
3457 parts[3] AS item_lang
3458 FROM format_parts_array
3459 ), converted_formats_flat AS (
3461 CASE WHEN ARRAY_LENGTH(item_type,1) > 0
3462 THEN '"0":[{"_attr":"item_type","_val":"' || ARRAY_TO_STRING(item_type,'"},{"_attr":"item_type","_val":"') || '"}]'
3465 CASE WHEN ARRAY_LENGTH(item_form,1) > 0
3466 THEN '"1":[{"_attr":"item_form","_val":"' || ARRAY_TO_STRING(item_form,'"},{"_attr":"item_form","_val":"') || '"}]'
3469 CASE WHEN item_lang <> ''
3470 THEN '"2":[{"_attr":"item_lang","_val":"' || item_lang ||'"}]'
3473 FROM format_parts_wide
3474 ) UPDATE action.hold_request SET holdable_formats = '{' ||
3475 converted_formats_flat.item_type || ',' ||
3476 converted_formats_flat.item_form || ',' ||
3477 converted_formats_flat.item_lang || '}'
3478 FROM converted_formats_flat WHERE converted_formats_flat.id = action.hold_request.id;
3482 SELECT evergreen.upgrade_deps_block_check('0866', :eg_version);
3484 DROP FUNCTION asset.record_has_holdable_copy (BIGINT);
3485 CREATE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
3490 JOIN asset.call_number acn ON acp.call_number = acn.id
3491 JOIN asset.copy_location acpl ON acp.location = acpl.id
3492 JOIN config.copy_status ccs ON acp.status = ccs.id
3495 AND acp.holdable = true
3496 AND acpl.holdable = true
3497 AND ccs.holdable = true
3498 AND acp.deleted = false
3499 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
3506 $f$ LANGUAGE PLPGSQL;
3508 DROP FUNCTION asset.metarecord_has_holdable_copy (BIGINT);
3509 CREATE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
3514 JOIN asset.call_number acn ON acp.call_number = acn.id
3515 JOIN asset.copy_location acpl ON acp.location = acpl.id
3516 JOIN config.copy_status ccs ON acp.status = ccs.id
3517 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
3519 mmsm.metarecord = rid
3520 AND acp.holdable = true
3521 AND acpl.holdable = true
3522 AND ccs.holdable = true
3523 AND acp.deleted = false
3524 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
3531 $f$ LANGUAGE PLPGSQL;
3533 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$
3538 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;
3540 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
3545 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3549 actor.org_unit_descendants(ans.id) d
3550 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
3551 JOIN asset.copy cp ON (cp.id = av.copy_id)
3552 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
3556 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3563 $f$ LANGUAGE PLPGSQL;
3565 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$
3570 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;
3572 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
3577 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3581 actor.org_unit_descendants(ans.id) d
3582 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
3583 JOIN asset.copy cp ON (cp.id = av.copy_id)
3584 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
3588 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3595 $f$ LANGUAGE PLPGSQL;
3597 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$
3602 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;
3604 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
3609 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3613 actor.org_unit_descendants(ans.id) d
3614 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
3615 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
3616 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
3620 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3627 $f$ LANGUAGE PLPGSQL;
3629 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$
3634 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;
3636 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
3641 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
3645 actor.org_unit_descendants(ans.id) d
3646 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
3647 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
3648 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
3652 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
3659 $f$ LANGUAGE PLPGSQL;
3661 CREATE OR REPLACE FUNCTION unapi.mmr_mra (
3667 depth INT DEFAULT NULL,
3668 slimit HSTORE DEFAULT NULL,
3669 soffset HSTORE DEFAULT NULL,
3670 include_xmlns BOOL DEFAULT TRUE,
3671 pref_lib INT DEFAULT NULL
3672 ) RETURNS XML AS $F$
3676 CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns,
3677 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord
3679 (SELECT XMLAGG(foo.y)
3681 SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id))
3682 COALESCE(cvm.id,uvm.id),
3687 cvm.value AS "coded-value",
3696 FROM metabib.record_attr_flat mra
3697 JOIN config.record_attr_definition rad ON (mra.attr = rad.name)
3698 LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value)
3699 LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value)
3701 WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id
3702 FROM actor.org_unit WHERE shortname = $5 LIMIT 1)
3704 FROM metabib.metarecord_source_map, aou
3705 WHERE metarecord = $1 AND (
3707 SELECT 1 FROM asset.opac_visible_copies
3708 WHERE record = source AND circ_lib IN (
3709 SELECT id FROM actor.org_unit_descendants(aou.id, $6))
3712 OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1)
3719 $F$ LANGUAGE SQL STABLE;
3721 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
3724 depth INT DEFAULT NULL,
3725 slimit HSTORE DEFAULT NULL,
3726 soffset HSTORE DEFAULT NULL,
3727 pref_lib INT DEFAULT NULL,
3728 includes TEXT[] DEFAULT NULL::TEXT[]
3729 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
3730 SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
3731 SELECT acn.id, aou.name, acn.label_sortkey,
3732 evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status),
3734 FROM asset.call_number acn
3735 JOIN asset.copy acp ON (acn.id = acp.call_number)
3736 JOIN actor.org_unit_descendants( $2, COALESCE(
3739 FROM actor.org_unit_type aout
3740 INNER JOIN actor.org_unit ou ON ou_type = aout.id
3743 ) AS aou ON (acp.circ_lib = aou.id)
3744 WHERE acn.record = ANY ($1)
3745 AND acn.deleted IS FALSE
3746 AND acp.deleted IS FALSE
3747 AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
3750 FROM asset.opac_visible_copies
3751 WHERE copy_id = acp.id AND record = acn.record
3753 GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
3755 ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status)
3758 GROUP BY ua.id, ua.name, ua.label_sortkey
3759 ORDER BY rank, ua.name, ua.label_sortkey
3760 LIMIT ($4 -> 'acn')::INT
3761 OFFSET ($5 -> 'acn')::INT;
3763 LANGUAGE SQL STABLE;
3765 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
3766 ( 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[] )
3767 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
3768 AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
3771 CREATE OR REPLACE FUNCTION evergreen.located_uris (
3774 pref_lib INT DEFAULT NULL
3775 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
3776 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
3777 SELECT DISTINCT ON (id) * FROM (
3778 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
3779 FROM asset.call_number acn
3780 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
3781 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
3782 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
3783 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
3785 WHERE acn.record = ANY ($1)
3786 AND acn.deleted IS FALSE
3787 AND auri.active IS TRUE
3788 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL)
3790 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
3791 FROM asset.call_number acn
3792 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
3793 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
3794 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
3795 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
3797 WHERE acn.record = ANY ($1)
3798 AND acn.deleted IS FALSE
3799 AND auri.active IS TRUE
3800 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x
3801 ORDER BY id, pref_ou DESC;
3803 LANGUAGE SQL STABLE;
3805 CREATE OR REPLACE FUNCTION evergreen.located_uris ( bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL)
3806 RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT)
3807 AS $$ SELECT * FROM evergreen.located_uris(ARRAY[$1],$2,$3) $$ LANGUAGE SQL STABLE;
3810 CREATE OR REPLACE FUNCTION unapi.mmr_holdings_xml (
3814 depth INT DEFAULT NULL,
3815 includes TEXT[] DEFAULT NULL::TEXT[],
3816 slimit HSTORE DEFAULT NULL,
3817 soffset HSTORE DEFAULT NULL,
3818 include_xmlns BOOL DEFAULT TRUE,
3819 pref_lib INT DEFAULT NULL
3825 CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
3826 CASE WHEN ('mmr' = ANY ($5)) THEN 'tag:open-ils.org:U2@mmr/' || $1 || '/' || $3 ELSE NULL END AS id,
3827 (SELECT metarecord_has_holdable_copy FROM asset.metarecord_has_holdable_copy($1)) AS has_holdable
3831 (SELECT XMLAGG(XMLELEMENT::XML) FROM (
3834 XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3836 FROM asset.opac_ou_metarecord_copy_count($2, $1)
3840 XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3842 FROM asset.staff_ou_metarecord_copy_count($2, $1)
3846 XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
3848 FROM asset.opac_ou_metarecord_copy_count($9, $1)
3852 -- XXX monograph_parts and foreign_copies are skipped in MRs ... put them back some day?
3855 (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
3857 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
3858 FROM evergreen.ranked_volumes((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $4, $6, $7, $9, $5) AS y
3861 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
3862 FROM evergreen.located_uris((SELECT ARRAY_AGG(source) FROM metabib.metarecord_source_map WHERE metarecord = $1), $2, $9) AS uris
3865 CASE WHEN ('ssub' = ANY ($5)) THEN
3868 (SELECT XMLAGG(ssub) FROM (
3869 SELECT unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
3870 FROM serial.subscription
3871 WHERE record_entry IN (SELECT source FROM metabib.metarecord_source_map WHERE metarecord = $1)
3876 $F$ LANGUAGE SQL STABLE;
3880 SELECT evergreen.upgrade_deps_block_check('0867', :eg_version);
3882 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3883 'opac.metarecord.holds.format_attr',
3885 'opac.metarecord.holds.format_attr',
3886 'OPAC Metarecord Hold Formats Attribute',
3894 -- until we have a custom attribute for the selector,
3895 -- default to the icon_format attribute
3896 INSERT INTO config.global_flag (name, label, value, enabled) VALUES (
3897 'opac.format_selector.attr',
3899 'opac.format_selector.attr',
3900 'OPAC Format Selector Attribute',
3909 INSERT INTO config.record_attr_definition
3910 (name, label, multi, filter, composite)
3915 'Metarecord Hold Formats',
3922 -- these formats are a subset of the "icon_format" attribute,
3923 -- modified to exclude electronic resources, which are not holdable
3925 -- for i18n purposes, these have to be listed individually
3926 INSERT INTO config.coded_value_map
3927 (id, ctype, code, value, search_label) VALUES
3928 (588, 'mr_hold_format', 'book',
3929 oils_i18n_gettext(588, 'Book', 'ccvm', 'value'),
3930 oils_i18n_gettext(588, 'Book', 'ccvm', 'search_label')),
3931 (589, 'mr_hold_format', 'braille',
3932 oils_i18n_gettext(589, 'Braille', 'ccvm', 'value'),
3933 oils_i18n_gettext(589, 'Braille', 'ccvm', 'search_label')),
3934 (590, 'mr_hold_format', 'software',
3935 oils_i18n_gettext(590, 'Software and video games', 'ccvm', 'value'),
3936 oils_i18n_gettext(590, 'Software and video games', 'ccvm', 'search_label')),
3937 (591, 'mr_hold_format', 'dvd',
3938 oils_i18n_gettext(591, 'DVD', 'ccvm', 'value'),
3939 oils_i18n_gettext(591, 'DVD', 'ccvm', 'search_label')),
3940 (592, 'mr_hold_format', 'kit',
3941 oils_i18n_gettext(592, 'Kit', 'ccvm', 'value'),
3942 oils_i18n_gettext(592, 'Kit', 'ccvm', 'search_label')),
3943 (593, 'mr_hold_format', 'map',
3944 oils_i18n_gettext(593, 'Map', 'ccvm', 'value'),
3945 oils_i18n_gettext(593, 'Map', 'ccvm', 'search_label')),
3946 (594, 'mr_hold_format', 'microform',
3947 oils_i18n_gettext(594, 'Microform', 'ccvm', 'value'),
3948 oils_i18n_gettext(594, 'Microform', 'ccvm', 'search_label')),
3949 (595, 'mr_hold_format', 'score',
3950 oils_i18n_gettext(595, 'Music Score', 'ccvm', 'value'),
3951 oils_i18n_gettext(595, 'Music Score', 'ccvm', 'search_label')),
3952 (596, 'mr_hold_format', 'picture',
3953 oils_i18n_gettext(596, 'Picture', 'ccvm', 'value'),
3954 oils_i18n_gettext(596, 'Picture', 'ccvm', 'search_label')),
3955 (597, 'mr_hold_format', 'equip',
3956 oils_i18n_gettext(597, 'Equipment, games, toys', 'ccvm', 'value'),
3957 oils_i18n_gettext(597, 'Equipment, games, toys', 'ccvm', 'search_label')),
3958 (598, 'mr_hold_format', 'serial',
3959 oils_i18n_gettext(598, 'Serials and magazines', 'ccvm', 'value'),
3960 oils_i18n_gettext(598, 'Serials and magazines', 'ccvm', 'search_label')),
3961 (599, 'mr_hold_format', 'vhs',
3962 oils_i18n_gettext(599, 'VHS', 'ccvm', 'value'),
3963 oils_i18n_gettext(599, 'VHS', 'ccvm', 'search_label')),
3964 (600, 'mr_hold_format', 'cdaudiobook',
3965 oils_i18n_gettext(600, 'CD Audiobook', 'ccvm', 'value'),
3966 oils_i18n_gettext(600, 'CD Audiobook', 'ccvm', 'search_label')),
3967 (601, 'mr_hold_format', 'cdmusic',
3968 oils_i18n_gettext(601, 'CD Music recording', 'ccvm', 'value'),
3969 oils_i18n_gettext(601, 'CD Music recording', 'ccvm', 'search_label')),
3970 (602, 'mr_hold_format', 'casaudiobook',
3971 oils_i18n_gettext(602, 'Cassette audiobook', 'ccvm', 'value'),
3972 oils_i18n_gettext(602, 'Cassette audiobook', 'ccvm', 'search_label')),
3973 (603, 'mr_hold_format', 'casmusic',
3974 oils_i18n_gettext(603, 'Audiocassette music recording', 'ccvm', 'value'),
3975 oils_i18n_gettext(603, 'Audiocassette music recording', 'ccvm', 'search_label')),
3976 (604, 'mr_hold_format', 'phonospoken',
3977 oils_i18n_gettext(604, 'Phonograph spoken recording', 'ccvm', 'value'),
3978 oils_i18n_gettext(604, 'Phonograph spoken recording', 'ccvm', 'search_label')),
3979 (605, 'mr_hold_format', 'phonomusic',
3980 oils_i18n_gettext(605, 'Phonograph music recording', 'ccvm', 'value'),
3981 oils_i18n_gettext(605, 'Phonograph music recording', 'ccvm', 'search_label')),
3982 (606, 'mr_hold_format', 'lpbook',
3983 oils_i18n_gettext(606, 'Large Print Book', 'ccvm', 'value'),
3984 oils_i18n_gettext(606, 'Large Print Book', 'ccvm', 'search_label'))
3987 -- but we can auto-generate the composite definitions
3990 DECLARE format TEXT;
3992 FOR format IN SELECT UNNEST(
3993 '{book,braille,software,dvd,kit,map,microform,score,picture,equip,serial,vhs,cdaudiobook,cdmusic,casaudiobook,casmusic,phonospoken,phonomusic,lpbook}'::text[]) LOOP
3995 INSERT INTO config.composite_attr_entry_definition
3996 (coded_value, definition) VALUES
3998 -- get the ID from the new ccvm above
3999 (SELECT id FROM config.coded_value_map
4000 WHERE code = format AND ctype = 'mr_hold_format'),
4001 -- get the def of the matching ccvm attached to the icon_format attr
4002 (SELECT definition FROM config.composite_attr_entry_definition ccaed
4003 JOIN config.coded_value_map ccvm ON (ccaed.coded_value = ccvm.id)
4004 WHERE ccvm.ctype = 'icon_format' AND ccvm.code = format)
4009 INSERT INTO config.coded_value_map
4010 (id, ctype, code, value, search_label) VALUES
4011 (607, 'icon_format', 'music',
4012 oils_i18n_gettext(607, 'Musical Sound Recording (Unknown Format)', 'ccvm', 'value'),
4013 oils_i18n_gettext(607, 'Musical Sound Recording (Unknown Format)', 'ccvm', 'search_label'));
4015 INSERT INTO config.composite_attr_entry_definition
4016 (coded_value, definition) VALUES
4017 (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"}]}}');
4020 INSERT INTO config.coded_value_map
4021 (id, ctype, code, value, search_label) VALUES
4022 (608, 'icon_format', 'blu-ray',
4023 oils_i18n_gettext(608, 'Blu-ray', 'ccvm', 'value'),
4024 oils_i18n_gettext(608, 'Blu-ray', 'ccvm', 'search_label'));
4025 INSERT INTO config.composite_attr_entry_definition
4026 (coded_value, definition) VALUES (608, '{"_attr":"vr_format","_val":"s"}');
4028 -- metarecord hold format for blu-ray
4029 INSERT INTO config.coded_value_map
4030 (id, ctype, code, value, search_label) VALUES
4031 (609, 'mr_hold_format', 'blu-ray',
4032 oils_i18n_gettext(609, 'Blu-ray', 'ccvm', 'value'),
4033 oils_i18n_gettext(609, 'Blu-ray', 'ccvm', 'search_label'));
4034 INSERT INTO config.composite_attr_entry_definition
4035 (coded_value, definition) VALUES (609, '{"_attr":"vr_format","_val":"s"}');
4039 SELECT evergreen.upgrade_deps_block_check('0869', :eg_version);
4041 CREATE OR REPLACE FUNCTION action.hold_copy_calculated_proximity_update () RETURNS TRIGGER AS $f$
4043 NEW.proximity := action.hold_copy_calculated_proximity(NEW.hold,NEW.target_copy);
4046 $f$ LANGUAGE PLPGSQL;
4048 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 ();
4050 -- Now, cause the update we need in a HOT-friendly manner (http://pgsql.tapoueh.org/site/html/misc/hot.html)
4051 UPDATE action.hold_copy_map SET proximity = proximity WHERE proximity IS NULL;
4055 * Copyright (C) 2014 Equinox Software, Inc.
4056 * Mike Rylander <miker@esilibrary.com>
4058 * This program is free software; you can redistribute it and/or
4059 * modify it under the terms of the GNU General Public License
4060 * as published by the Free Software Foundation; either version 2
4061 * of the License, or (at your option) any later version.
4063 * This program is distributed in the hope that it will be useful,
4064 * but WITHOUT ANY WARRANTY; without even the implied warranty of
4065 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
4066 * GNU General Public License for more details.
4072 SELECT evergreen.upgrade_deps_block_check('0870', :eg_version);
4074 CREATE OR REPLACE FUNCTION evergreen.located_uris (
4077 pref_lib INT DEFAULT NULL
4078 ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$
4079 WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy')
4080 SELECT DISTINCT ON (id) * FROM (
4081 SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
4082 FROM asset.call_number acn
4083 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
4084 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
4085 LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id)
4086 LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id),
4088 WHERE acn.record = ANY ($1)
4089 AND acn.deleted IS FALSE
4090 AND auri.active IS TRUE
4091 AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR (all_orgs.flag AND COALESCE(aou.id,aoud.id) IS NOT NULL))
4093 SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou
4094 FROM asset.call_number acn
4095 INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number
4096 INNER JOIN asset.uri auri ON auri.id = auricnm.uri
4097 LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id)
4098 LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id),
4100 WHERE acn.record = ANY ($1)
4101 AND acn.deleted IS FALSE
4102 AND auri.active IS TRUE
4103 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
4104 ORDER BY id, pref_ou DESC;
4106 LANGUAGE SQL STABLE;
4111 SELECT evergreen.upgrade_deps_block_check('0871', :eg_version);
4113 INSERT INTO config.record_attr_definition
4114 (name, label, multi, filter, composite) VALUES (
4116 oils_i18n_gettext('search_format', 'Search Formats', 'crad', 'label'),
4120 INSERT INTO config.coded_value_map
4121 (id, ctype, code, value, search_label) VALUES
4122 (610, 'search_format', 'book',
4123 oils_i18n_gettext(610, 'All Books', 'ccvm', 'value'),
4124 oils_i18n_gettext(610, 'All Books', 'ccvm', 'search_label')),
4125 (611, 'search_format', 'braille',
4126 oils_i18n_gettext(611, 'Braille', 'ccvm', 'value'),
4127 oils_i18n_gettext(611, 'Braille', 'ccvm', 'search_label')),
4128 (612, 'search_format', 'software',
4129 oils_i18n_gettext(612, 'Software and video games', 'ccvm', 'value'),
4130 oils_i18n_gettext(612, 'Software and video games', 'ccvm', 'search_label')),
4131 (613, 'search_format', 'dvd',
4132 oils_i18n_gettext(613, 'DVD', 'ccvm', 'value'),
4133 oils_i18n_gettext(613, 'DVD', 'ccvm', 'search_label')),
4134 (614, 'search_format', 'ebook',
4135 oils_i18n_gettext(614, 'E-book', 'ccvm', 'value'),
4136 oils_i18n_gettext(614, 'E-book', 'ccvm', 'search_label')),
4137 (615, 'search_format', 'eaudio',
4138 oils_i18n_gettext(615, 'E-audio', 'ccvm', 'value'),
4139 oils_i18n_gettext(615, 'E-audio', 'ccvm', 'search_label')),
4140 (616, 'search_format', 'kit',
4141 oils_i18n_gettext(616, 'Kit', 'ccvm', 'value'),
4142 oils_i18n_gettext(616, 'Kit', 'ccvm', 'search_label')),
4143 (617, 'search_format', 'map',
4144 oils_i18n_gettext(617, 'Map', 'ccvm', 'value'),
4145 oils_i18n_gettext(617, 'Map', 'ccvm', 'search_label')),
4146 (618, 'search_format', 'microform',
4147 oils_i18n_gettext(618, 'Microform', 'ccvm', 'value'),
4148 oils_i18n_gettext(618, 'Microform', 'ccvm', 'search_label')),
4149 (619, 'search_format', 'score',
4150 oils_i18n_gettext(619, 'Music Score', 'ccvm', 'value'),
4151 oils_i18n_gettext(619, 'Music Score', 'ccvm', 'search_label')),
4152 (620, 'search_format', 'picture',
4153 oils_i18n_gettext(620, 'Picture', 'ccvm', 'value'),
4154 oils_i18n_gettext(620, 'Picture', 'ccvm', 'search_label')),
4155 (621, 'search_format', 'equip',
4156 oils_i18n_gettext(621, 'Equipment, games, toys', 'ccvm', 'value'),
4157 oils_i18n_gettext(621, 'Equipment, games, toys', 'ccvm', 'search_label')),
4158 (622, 'search_format', 'serial',
4159 oils_i18n_gettext(622, 'Serials and magazines', 'ccvm', 'value'),
4160 oils_i18n_gettext(622, 'Serials and magazines', 'ccvm', 'search_label')),
4161 (623, 'search_format', 'vhs',
4162 oils_i18n_gettext(623, 'VHS', 'ccvm', 'value'),
4163 oils_i18n_gettext(623, 'VHS', 'ccvm', 'search_label')),
4164 (624, 'search_format', 'evideo',
4165 oils_i18n_gettext(624, 'E-video', 'ccvm', 'value'),
4166 oils_i18n_gettext(624, 'E-video', 'ccvm', 'search_label')),
4167 (625, 'search_format', 'cdaudiobook',
4168 oils_i18n_gettext(625, 'CD Audiobook', 'ccvm', 'value'),
4169 oils_i18n_gettext(625, 'CD Audiobook', 'ccvm', 'search_label')),
4170 (626, 'search_format', 'cdmusic',
4171 oils_i18n_gettext(626, 'CD Music recording', 'ccvm', 'value'),
4172 oils_i18n_gettext(626, 'CD Music recording', 'ccvm', 'search_label')),
4173 (627, 'search_format', 'casaudiobook',
4174 oils_i18n_gettext(627, 'Cassette audiobook', 'ccvm', 'value'),
4175 oils_i18n_gettext(627, 'Cassette audiobook', 'ccvm', 'search_label')),
4176 (628, 'search_format', 'casmusic',
4177 oils_i18n_gettext(628, 'Audiocassette music recording', 'ccvm', 'value'),
4178 oils_i18n_gettext(628, 'Audiocassette music recording', 'ccvm', 'search_label')),
4179 (629, 'search_format', 'phonospoken',
4180 oils_i18n_gettext(629, 'Phonograph spoken recording', 'ccvm', 'value'),
4181 oils_i18n_gettext(629, 'Phonograph spoken recording', 'ccvm', 'search_label')),
4182 (630, 'search_format', 'phonomusic',
4183 oils_i18n_gettext(630, 'Phonograph music recording', 'ccvm', 'value'),
4184 oils_i18n_gettext(630, 'Phonograph music recording', 'ccvm', 'search_label')),
4185 (631, 'search_format', 'lpbook',
4186 oils_i18n_gettext(631, 'Large Print Book', 'ccvm', 'value'),
4187 oils_i18n_gettext(631, 'Large Print Book', 'ccvm', 'search_label')),
4188 (632, 'search_format', 'music',
4189 oils_i18n_gettext(632, 'All Music', 'ccvm', 'label'),
4190 oils_i18n_gettext(632, 'All Music', 'ccvm', 'search_label')),
4191 (633, 'search_format', 'blu-ray',
4192 oils_i18n_gettext(633, 'Blu-ray', 'ccvm', 'value'),
4193 oils_i18n_gettext(633, 'Blu-ray', 'ccvm', 'search_label'));
4197 -- copy the composite definition from icon_format into
4198 -- search_format for a baseline data set
4200 DECLARE format config.coded_value_map%ROWTYPE;
4202 FOR format IN SELECT *
4203 FROM config.coded_value_map WHERE ctype = 'icon_format'
4205 INSERT INTO config.composite_attr_entry_definition
4206 (coded_value, definition) VALUES
4208 -- get the ID from the new ccvm above
4209 (SELECT id FROM config.coded_value_map
4210 WHERE code = format.code AND ctype = 'search_format'),
4212 -- def of the matching icon_format attr
4213 (SELECT definition FROM config.composite_attr_entry_definition
4214 WHERE coded_value = format.id)
4219 -- modify the 'book' definition so that it includes large print
4220 UPDATE config.composite_attr_entry_definition
4221 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"}]}'
4222 WHERE coded_value = 610;
4224 -- modify 'music' to include all recorded music, regardless of format
4225 UPDATE config.composite_attr_entry_definition
4226 SET definition = '{"_attr":"item_type","_val":"j"}'
4227 WHERE coded_value = 632;
4229 UPDATE config.global_flag
4230 SET value = 'search_format'
4231 WHERE name = 'opac.format_selector.attr';
4235 SELECT evergreen.upgrade_deps_block_check('0872', :eg_version);
4237 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$
4239 new_mapping BOOL := TRUE;
4242 tmp_mr metabib.metarecord%ROWTYPE;
4243 deleted_mrs BIGINT[];
4246 -- We need to make sure we're not a deleted master record of an MR
4247 IF bib_is_deleted THEN
4248 FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
4250 IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
4251 DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
4254 -- Now, are there any more sources on this MR?
4255 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
4257 IF source_count = 0 AND NOT retain_deleted THEN -- No other records
4258 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
4259 DELETE FROM metabib.metarecord WHERE id = old_mr;
4261 ELSE -- indeed there are. Update it with a null cache and recalcualated master record
4262 UPDATE metabib.metarecord
4264 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4269 ELSE -- insert or update
4271 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
4273 -- Find the first fingerprint-matching
4274 IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
4275 old_mr := tmp_mr.id;
4276 new_mapping := FALSE;
4278 ELSE -- Our fingerprint changed ... maybe remove the old MR
4279 DELETE FROM metabib.metarecord_source_map WHERE metarecord = old_mr AND source = bib_id; -- remove the old source mapping
4280 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
4281 IF source_count = 0 THEN -- No other records
4282 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
4283 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
4289 -- we found no suitable, preexisting MR based on old source maps
4290 IF old_mr IS NULL THEN
4291 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
4293 IF old_mr IS NULL THEN -- nope, create one and grab its id
4294 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
4295 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
4297 ELSE -- indeed there is. update it with a null cache and recalcualated master record
4298 UPDATE metabib.metarecord
4300 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4304 ELSE -- there was one we already attached to, update its mods cache and master_record
4305 UPDATE metabib.metarecord
4307 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
4312 INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
4317 IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
4318 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
4324 $func$ LANGUAGE PLPGSQL;
4326 DROP FUNCTION metabib.remap_metarecord_for_bib( bib_id BIGINT, fp TEXT );
4328 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
4333 IF NEW.deleted THEN -- If this bib is deleted
4335 PERFORM * FROM config.internal_flag WHERE
4336 name = 'ingest.metarecord_mapping.preserve_on_delete' AND enabled;
4338 tmp_bool := FOUND; -- Just in case this is changed by some other statement
4340 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint, TRUE, tmp_bool );
4342 IF NOT tmp_bool THEN
4343 -- One needs to keep these around to support searches
4344 -- with the #deleted modifier, so one should turn on the named
4345 -- internal flag for that functionality.
4346 DELETE FROM metabib.record_attr_vector_list WHERE source = NEW.id;
4349 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
4350 DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = NEW.id; -- Separate any multi-homed items
4351 DELETE FROM metabib.browse_entry_def_map WHERE source = NEW.id; -- Don't auto-suggest deleted bibs
4352 RETURN NEW; -- and we're done
4355 IF TG_OP = 'UPDATE' THEN -- re-ingest?
4356 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
4358 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
4363 -- Record authority linking
4364 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
4366 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
4369 -- Flatten and insert the mfr data
4370 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
4372 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
4374 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
4375 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
4377 PERFORM metabib.reingest_record_attributes(NEW.id, NULL, NEW.marc, TG_OP = 'INSERT' OR OLD.deleted);
4381 -- Gather and insert the field entry data
4382 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
4384 -- Located URI magic
4385 IF TG_OP = 'INSERT' THEN
4386 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4388 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4391 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
4393 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
4397 -- (re)map metarecord-bib linking
4398 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
4399 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
4401 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4403 ELSE -- we're doing an update, and we're not deleted, remap
4404 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
4406 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
4412 $func$ LANGUAGE PLPGSQL;
4414 CREATE OR REPLACE FUNCTION unapi.mmr (
4420 depth INT DEFAULT NULL,
4421 slimit HSTORE DEFAULT NULL,
4422 soffset HSTORE DEFAULT NULL,
4423 include_xmlns BOOL DEFAULT TRUE,
4424 pref_lib INT DEFAULT NULL
4428 mmrec metabib.metarecord%ROWTYPE;
4429 leadrec biblio.record_entry%ROWTYPE;
4430 subrec biblio.record_entry%ROWTYPE;
4431 layout unapi.bre_output_layout%ROWTYPE;
4432 xfrm config.xml_transform%ROWTYPE;
4434 xml_buf TEXT; -- growing XML document
4435 tmp_xml TEXT; -- single-use XML string
4436 xml_frag TEXT; -- single-use XML fragment
4441 subxml XML; -- subordinate records elements
4446 -- xpath for extracting bre.marc values from subordinate records
4447 -- so they may be appended to the MARC of the master record prior
4448 -- to XSLT processing.
4449 -- subjects, isbn, issn, upc -- anything else?
4451 '//*[starts-with(@tag, "6") or @tag="020" or @tag="022" or @tag="024"]';
4453 IF org = '-' OR org IS NULL THEN
4454 SELECT shortname INTO org FROM evergreen.org_top();
4457 SELECT id INTO ouid FROM actor.org_unit WHERE shortname = org;
4459 IF ouid IS NULL THEN
4463 SELECT INTO mmrec * FROM metabib.metarecord WHERE id = obj_id;
4468 -- TODO: aggregate holdings from constituent records
4469 IF format = 'holdings_xml' THEN -- the special case
4470 output := unapi.mmr_holdings_xml(
4471 obj_id, ouid, org, depth,
4472 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
4473 slimit, soffset, include_xmlns, pref_lib);
4477 SELECT * INTO layout FROM unapi.bre_output_layout WHERE name = format;
4479 IF layout.name IS NULL THEN
4483 SELECT * INTO xfrm FROM config.xml_transform WHERE name = layout.transform;
4485 SELECT INTO leadrec * FROM biblio.record_entry WHERE id = mmrec.master_record;
4487 -- Grab distinct MVF for all records if requested
4488 IF ('mra' = ANY (includes)) THEN
4489 axml := unapi.mmr_mra(obj_id,NULL,NULL,NULL,org,depth,NULL,NULL,TRUE,pref_lib);
4494 xml_buf = leadrec.marc;
4497 IF ('holdings_xml' = ANY (includes)) THEN
4498 hxml := unapi.mmr_holdings_xml(
4499 obj_id, ouid, org, depth,
4500 evergreen.array_remove_item_by_value(includes,'holdings_xml'),
4501 slimit, soffset, include_xmlns, pref_lib);
4504 subxml := NULL::XML;
4505 parts := '{}'::TEXT[];
4506 FOR subrec IN SELECT bre.* FROM biblio.record_entry bre
4507 JOIN metabib.metarecord_source_map mmsm ON (mmsm.source = bre.id)
4508 JOIN metabib.metarecord mmr ON (mmr.id = mmsm.metarecord)
4509 WHERE mmr.id = obj_id AND NOT bre.deleted
4510 ORDER BY CASE WHEN bre.id = mmr.master_record THEN 0 ELSE bre.id END
4511 LIMIT COALESCE((slimit->'bre')::INT, 5) LOOP
4513 IF subrec.id = leadrec.id THEN CONTINUE; END IF;
4514 -- Append choice data from the the non-lead records to the
4515 -- the lead record document
4517 parts := parts || xpath(sub_xpath, subrec.marc::XML)::TEXT[];
4520 SELECT ARRAY_TO_STRING( ARRAY_AGG( DISTINCT p ), '' )::XML INTO subxml FROM UNNEST(parts) p;
4522 -- append data from the subordinate records to the
4523 -- main record document before applying the XSLT
4525 IF subxml IS NOT NULL THEN
4526 xml_buf := REGEXP_REPLACE(xml_buf,
4527 '</record>(.*?)$', subxml || '</record>' || E'\\1');
4530 IF format = 'marcxml' THEN
4531 -- If we're not using the prefixed namespace in
4532 -- this record, then remove all declarations of it
4533 IF xml_buf !~ E'<marc:' THEN
4534 xml_buf := REGEXP_REPLACE(xml_buf,
4535 ' xmlns:marc="http://www.loc.gov/MARC21/slim"', '', 'g');
4538 xml_buf := oils_xslt_process(xml_buf, xfrm.xslt)::XML;
4541 -- update top_el to reflect the change in xml_buf, which may
4542 -- now be a different type of document (e.g. record -> mods)
4543 top_el := REGEXP_REPLACE(xml_buf, E'^.*?<((?:\\S+:)?' ||
4544 layout.holdings_element || ').*$', E'\\1');
4546 IF axml IS NOT NULL THEN
4547 xml_buf := REGEXP_REPLACE(xml_buf,
4548 '</' || top_el || '>(.*?)$', axml || '</' || top_el || E'>\\1');
4551 IF hxml IS NOT NULL THEN
4552 xml_buf := REGEXP_REPLACE(xml_buf,
4553 '</' || top_el || '>(.*?)$', hxml || '</' || top_el || E'>\\1');
4556 IF ('mmr.unapi' = ANY (includes)) THEN
4557 output := REGEXP_REPLACE(
4559 '</' || top_el || '>(.*?)',
4563 'http://www.w3.org/1999/xhtml' AS xmlns,
4564 'unapi-id' AS class,
4565 'tag:open-ils.org:U2@mmr/' || obj_id || '/' || org AS title
4567 )::TEXT || '</' || top_el || E'>\\1'
4573 -- remove ignorable whitesace
4574 output := REGEXP_REPLACE(output::TEXT,E'>\\s+<','><','gs')::XML;
4577 $F$ LANGUAGE PLPGSQL STABLE;
4579 -- Forcibly remap deleted master records, retaining the linkage if so configured.
4580 SELECT count(metabib.remap_metarecord_for_bib( bre.id, bre.fingerprint, TRUE, COALESCE(flag.enabled,FALSE)))
4581 FROM metabib.metarecord metar
4582 JOIN biblio.record_entry bre ON bre.id = metar.master_record,
4583 config.internal_flag flag
4584 WHERE bre.deleted = TRUE AND flag.name = 'ingest.metarecord_mapping.preserve_on_delete';
4588 SELECT evergreen.upgrade_deps_block_check('0873', :eg_version);
4590 CREATE OR REPLACE FUNCTION action.find_hold_matrix_matchpoint(pickup_ou integer, request_ou integer, match_item bigint, match_user integer, match_requestor integer)
4594 requestor_object actor.usr%ROWTYPE;
4595 user_object actor.usr%ROWTYPE;
4596 item_object asset.copy%ROWTYPE;
4597 item_cn_object asset.call_number%ROWTYPE;
4598 my_item_age INTERVAL;
4599 rec_descriptor metabib.rec_descriptor%ROWTYPE;
4600 matchpoint config.hold_matrix_matchpoint%ROWTYPE;
4601 weights config.hold_matrix_weights%ROWTYPE;
4602 denominator NUMERIC(6,2);
4603 v_pickup_ou ALIAS FOR pickup_ou;
4604 v_request_ou ALIAS FOR request_ou;
4606 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
4607 SELECT INTO requestor_object * FROM actor.usr WHERE id = match_requestor;
4608 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
4609 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4610 SELECT INTO rec_descriptor * FROM metabib.rec_descriptor WHERE record = item_cn_object.record;
4612 SELECT INTO my_item_age age(coalesce(item_object.active_date, now()));
4614 -- The item's owner should probably be the one determining if the item is holdable
4615 -- How to decide that is debatable. Decided to default to the circ library (where the item lives)
4616 -- This flag will allow for setting it to the owning library (where the call number "lives")
4617 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.weight_owner_not_circ' AND enabled;
4619 -- Grab the closest set circ weight setting.
4621 -- Default to circ library
4622 SELECT INTO weights hw.*
4623 FROM config.weight_assoc wa
4624 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
4625 JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) d ON (wa.org_unit = d.id)
4630 -- Flag is set, use owning library
4631 SELECT INTO weights hw.*
4632 FROM config.weight_assoc wa
4633 JOIN config.hold_matrix_weights hw ON (hw.id = wa.hold_weights)
4634 JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) d ON (wa.org_unit = d.id)
4640 -- No weights? Bad admin! Defaults to handle that anyway.
4641 IF weights.id IS NULL THEN
4642 weights.user_home_ou := 5.0;
4643 weights.request_ou := 5.0;
4644 weights.pickup_ou := 5.0;
4645 weights.item_owning_ou := 5.0;
4646 weights.item_circ_ou := 5.0;
4647 weights.usr_grp := 7.0;
4648 weights.requestor_grp := 8.0;
4649 weights.circ_modifier := 4.0;
4650 weights.marc_type := 3.0;
4651 weights.marc_form := 2.0;
4652 weights.marc_bib_level := 1.0;
4653 weights.marc_vr_format := 1.0;
4654 weights.juvenile_flag := 4.0;
4655 weights.ref_flag := 0.0;
4656 weights.item_age := 0.0;
4659 -- Determine the max (expected) depth (+1) of the org tree and max depth of the permisson tree
4660 -- If you break your org tree with funky parenting this may be wrong
4661 -- 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
4662 -- We use one denominator for all tree-based checks for when permission groups and org units have the same weighting
4663 WITH all_distance(distance) AS (
4664 SELECT depth AS distance FROM actor.org_unit_type
4666 SELECT distance AS distance FROM permission.grp_ancestors_distance((SELECT id FROM permission.grp_tree WHERE parent IS NULL))
4668 SELECT INTO denominator MAX(distance) + 1 FROM all_distance;
4670 -- To ATTEMPT to make this work like it used to, make it reverse the user/requestor profile ids.
4671 -- This may be better implemented as part of the upgrade script?
4672 -- Set usr_grp = requestor_grp, requestor_grp = 1 or something when this flag is already set
4673 -- Then remove this flag, of course.
4674 PERFORM * FROM config.internal_flag WHERE name = 'circ.holds.usr_not_requestor' AND enabled;
4677 -- Note: This, to me, is REALLY hacky. I put it in anyway.
4678 -- If you can't tell, this is a single call swap on two variables.
4679 SELECT INTO user_object.profile, requestor_object.profile
4680 requestor_object.profile, user_object.profile;
4683 -- Select the winning matchpoint into the matchpoint variable for returning
4684 SELECT INTO matchpoint m.*
4685 FROM config.hold_matrix_matchpoint m
4686 /*LEFT*/ JOIN permission.grp_ancestors_distance( requestor_object.profile ) rpgad ON m.requestor_grp = rpgad.id
4687 LEFT JOIN permission.grp_ancestors_distance( user_object.profile ) upgad ON m.usr_grp = upgad.id
4688 LEFT JOIN actor.org_unit_ancestors_distance( v_pickup_ou ) puoua ON m.pickup_ou = puoua.id
4689 LEFT JOIN actor.org_unit_ancestors_distance( v_request_ou ) rqoua ON m.request_ou = rqoua.id
4690 LEFT JOIN actor.org_unit_ancestors_distance( item_cn_object.owning_lib ) cnoua ON m.item_owning_ou = cnoua.id
4691 LEFT JOIN actor.org_unit_ancestors_distance( item_object.circ_lib ) iooua ON m.item_circ_ou = iooua.id
4692 LEFT JOIN actor.org_unit_ancestors_distance( user_object.home_ou ) uhoua ON m.user_home_ou = uhoua.id
4694 -- Permission Groups
4695 -- AND (m.requestor_grp IS NULL OR upgad.id IS NOT NULL) -- Optional Requestor Group?
4696 AND (m.usr_grp IS NULL OR upgad.id IS NOT NULL)
4698 AND (m.pickup_ou IS NULL OR (puoua.id IS NOT NULL AND (puoua.distance = 0 OR NOT m.strict_ou_match)))
4699 AND (m.request_ou IS NULL OR (rqoua.id IS NOT NULL AND (rqoua.distance = 0 OR NOT m.strict_ou_match)))
4700 AND (m.item_owning_ou IS NULL OR (cnoua.id IS NOT NULL AND (cnoua.distance = 0 OR NOT m.strict_ou_match)))
4701 AND (m.item_circ_ou IS NULL OR (iooua.id IS NOT NULL AND (iooua.distance = 0 OR NOT m.strict_ou_match)))
4702 AND (m.user_home_ou IS NULL OR (uhoua.id IS NOT NULL AND (uhoua.distance = 0 OR NOT m.strict_ou_match)))
4703 -- Static User Checks
4704 AND (m.juvenile_flag IS NULL OR m.juvenile_flag = user_object.juvenile)
4705 -- Static Item Checks
4706 AND (m.circ_modifier IS NULL OR m.circ_modifier = item_object.circ_modifier)
4707 AND (m.marc_type IS NULL OR m.marc_type = COALESCE(item_object.circ_as_type, rec_descriptor.item_type))
4708 AND (m.marc_form IS NULL OR m.marc_form = rec_descriptor.item_form)
4709 AND (m.marc_bib_level IS NULL OR m.marc_bib_level = rec_descriptor.bib_level)
4710 AND (m.marc_vr_format IS NULL OR m.marc_vr_format = rec_descriptor.vr_format)
4711 AND (m.ref_flag IS NULL OR m.ref_flag = item_object.ref)
4712 AND (m.item_age IS NULL OR (my_item_age IS NOT NULL AND m.item_age > my_item_age))
4714 -- Permission Groups
4715 CASE WHEN rpgad.distance IS NOT NULL THEN 2^(2*weights.requestor_grp - (rpgad.distance/denominator)) ELSE 0.0 END +
4716 CASE WHEN upgad.distance IS NOT NULL THEN 2^(2*weights.usr_grp - (upgad.distance/denominator)) ELSE 0.0 END +
4718 CASE WHEN puoua.distance IS NOT NULL THEN 2^(2*weights.pickup_ou - (puoua.distance/denominator)) ELSE 0.0 END +
4719 CASE WHEN rqoua.distance IS NOT NULL THEN 2^(2*weights.request_ou - (rqoua.distance/denominator)) ELSE 0.0 END +
4720 CASE WHEN cnoua.distance IS NOT NULL THEN 2^(2*weights.item_owning_ou - (cnoua.distance/denominator)) ELSE 0.0 END +
4721 CASE WHEN iooua.distance IS NOT NULL THEN 2^(2*weights.item_circ_ou - (iooua.distance/denominator)) ELSE 0.0 END +
4722 CASE WHEN uhoua.distance IS NOT NULL THEN 2^(2*weights.user_home_ou - (uhoua.distance/denominator)) ELSE 0.0 END +
4723 -- Static User Checks -- Note: 4^x is equiv to 2^(2*x)
4724 CASE WHEN m.juvenile_flag IS NOT NULL THEN 4^weights.juvenile_flag ELSE 0.0 END +
4725 -- Static Item Checks
4726 CASE WHEN m.circ_modifier IS NOT NULL THEN 4^weights.circ_modifier ELSE 0.0 END +
4727 CASE WHEN m.marc_type IS NOT NULL THEN 4^weights.marc_type ELSE 0.0 END +
4728 CASE WHEN m.marc_form IS NOT NULL THEN 4^weights.marc_form ELSE 0.0 END +
4729 CASE WHEN m.marc_vr_format IS NOT NULL THEN 4^weights.marc_vr_format ELSE 0.0 END +
4730 CASE WHEN m.ref_flag IS NOT NULL THEN 4^weights.ref_flag ELSE 0.0 END +
4731 -- Item age has a slight adjustment to weight based on value.
4732 -- This should ensure that a shorter age limit comes first when all else is equal.
4733 -- NOTE: This assumes that intervals will normally be in days.
4734 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,
4735 -- Final sort on id, so that if two rules have the same sorting in the previous sort they have a defined order
4736 -- This prevents "we changed the table order by updating a rule, and we started getting different results"
4739 -- Return just the ID for now
4740 RETURN matchpoint.id;
4742 $func$ LANGUAGE 'plpgsql';
4744 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$
4747 user_object actor.usr%ROWTYPE;
4748 age_protect_object config.rule_age_hold_protect%ROWTYPE;
4749 standing_penalty config.standing_penalty%ROWTYPE;
4750 transit_range_ou_type actor.org_unit_type%ROWTYPE;
4751 transit_source actor.org_unit%ROWTYPE;
4752 item_object asset.copy%ROWTYPE;
4753 item_cn_object asset.call_number%ROWTYPE;
4754 item_status_object config.copy_status%ROWTYPE;
4755 item_location_object asset.copy_location%ROWTYPE;
4756 ou_skip actor.org_unit_setting%ROWTYPE;
4757 result action.matrix_test_result;
4758 hold_test config.hold_matrix_matchpoint%ROWTYPE;
4759 use_active_date TEXT;
4760 age_protect_date TIMESTAMP WITH TIME ZONE;
4762 hold_transit_prox INT;
4763 frozen_hold_count INT;
4764 context_org_list INT[];
4767 v_pickup_ou ALIAS FOR pickup_ou;
4768 v_request_ou ALIAS FOR request_ou;
4770 SELECT INTO user_object * FROM actor.usr WHERE id = match_user;
4771 SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( v_pickup_ou );
4773 result.success := TRUE;
4775 -- The HOLD penalty block only applies to new holds.
4776 -- The CAPTURE penalty block applies to existing holds.
4777 hold_penalty := 'HOLD';
4778 IF retargetting THEN
4779 hold_penalty := 'CAPTURE';
4782 -- Fail if we couldn't find a user
4783 IF user_object.id IS NULL THEN
4784 result.fail_part := 'no_user';
4785 result.success := FALSE;
4791 SELECT INTO item_object * FROM asset.copy WHERE id = match_item;
4793 -- Fail if we couldn't find a copy
4794 IF item_object.id IS NULL THEN
4795 result.fail_part := 'no_item';
4796 result.success := FALSE;
4802 SELECT INTO matchpoint_id action.find_hold_matrix_matchpoint(v_pickup_ou, v_request_ou, match_item, match_user, match_requestor);
4803 result.matchpoint := matchpoint_id;
4805 SELECT INTO ou_skip * FROM actor.org_unit_setting WHERE name = 'circ.holds.target_skip_me' AND org_unit = item_object.circ_lib;
4807 -- Fail if the circ_lib for the item has circ.holds.target_skip_me set to true
4808 IF ou_skip.id IS NOT NULL AND ou_skip.value = 'true' THEN
4809 result.fail_part := 'circ.holds.target_skip_me';
4810 result.success := FALSE;
4816 -- Fail if user is barred
4817 IF user_object.barred IS TRUE THEN
4818 result.fail_part := 'actor.usr.barred';
4819 result.success := FALSE;
4825 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4826 SELECT INTO item_status_object * FROM config.copy_status WHERE id = item_object.status;
4827 SELECT INTO item_location_object * FROM asset.copy_location WHERE id = item_object.location;
4829 -- Fail if we couldn't find any matchpoint (requires a default)
4830 IF matchpoint_id IS NULL THEN
4831 result.fail_part := 'no_matchpoint';
4832 result.success := FALSE;
4838 SELECT INTO hold_test * FROM config.hold_matrix_matchpoint WHERE id = matchpoint_id;
4840 IF hold_test.holdable IS FALSE THEN
4841 result.fail_part := 'config.hold_matrix_test.holdable';
4842 result.success := FALSE;
4847 IF item_object.holdable IS FALSE THEN
4848 result.fail_part := 'item.holdable';
4849 result.success := FALSE;
4854 IF item_status_object.holdable IS FALSE THEN
4855 result.fail_part := 'status.holdable';
4856 result.success := FALSE;
4861 IF item_location_object.holdable IS FALSE THEN
4862 result.fail_part := 'location.holdable';
4863 result.success := FALSE;
4868 IF hold_test.transit_range IS NOT NULL THEN
4869 SELECT INTO transit_range_ou_type * FROM actor.org_unit_type WHERE id = hold_test.transit_range;
4870 IF hold_test.distance_is_from_owner THEN
4871 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;
4873 SELECT INTO transit_source * FROM actor.org_unit WHERE id = item_object.circ_lib;
4876 PERFORM * FROM actor.org_unit_descendants( transit_source.id, transit_range_ou_type.depth ) WHERE id = v_pickup_ou;
4879 result.fail_part := 'transit_range';
4880 result.success := FALSE;
4886 FOR standing_penalty IN
4887 SELECT DISTINCT csp.*
4888 FROM actor.usr_standing_penalty usp
4889 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
4890 WHERE usr = match_user
4891 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
4892 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
4893 AND csp.block_list LIKE '%' || hold_penalty || '%' LOOP
4895 result.fail_part := standing_penalty.name;
4896 result.success := FALSE;
4901 IF hold_test.stop_blocked_user IS TRUE THEN
4902 FOR standing_penalty IN
4903 SELECT DISTINCT csp.*
4904 FROM actor.usr_standing_penalty usp
4905 JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty)
4906 WHERE usr = match_user
4907 AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) )
4908 AND (usp.stop_date IS NULL or usp.stop_date > NOW())
4909 AND csp.block_list LIKE '%CIRC%' LOOP
4911 result.fail_part := standing_penalty.name;
4912 result.success := FALSE;
4918 IF hold_test.max_holds IS NOT NULL AND NOT retargetting THEN
4919 SELECT INTO hold_count COUNT(*)
4920 FROM action.hold_request
4921 WHERE usr = match_user
4922 AND fulfillment_time IS NULL
4923 AND cancel_time IS NULL
4924 AND CASE WHEN hold_test.include_frozen_holds THEN TRUE ELSE frozen IS FALSE END;
4926 IF hold_count >= hold_test.max_holds THEN
4927 result.fail_part := 'config.hold_matrix_test.max_holds';
4928 result.success := FALSE;
4934 IF item_object.age_protect IS NOT NULL THEN
4935 SELECT INTO age_protect_object * FROM config.rule_age_hold_protect WHERE id = item_object.age_protect;
4936 IF hold_test.distance_is_from_owner THEN
4937 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_cn_object.owning_lib);
4939 SELECT INTO use_active_date value FROM actor.org_unit_ancestor_setting('circ.holds.age_protect.active_date', item_object.circ_lib);
4941 IF use_active_date = 'true' THEN
4942 age_protect_date := COALESCE(item_object.active_date, NOW());
4944 age_protect_date := item_object.create_date;
4946 IF age_protect_date + age_protect_object.age > NOW() THEN
4947 IF hold_test.distance_is_from_owner THEN
4948 SELECT INTO item_cn_object * FROM asset.call_number WHERE id = item_object.call_number;
4949 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;
4951 SELECT INTO hold_transit_prox prox FROM actor.org_unit_proximity WHERE from_org = item_object.circ_lib AND to_org = v_pickup_ou;
4954 IF hold_transit_prox > age_protect_object.prox THEN
4955 result.fail_part := 'config.rule_age_hold_protect.prox';
4956 result.success := FALSE;
4969 $func$ LANGUAGE plpgsql;
4973 SELECT evergreen.upgrade_deps_block_check('0874', :eg_version);
4975 DROP FUNCTION IF EXISTS evergreen.oils_xpath( TEXT, TEXT, ANYARRAY);
4976 DROP FUNCTION IF EXISTS public.oils_xpath(TEXT, TEXT, ANYARRAY);
4977 DROP FUNCTION IF EXISTS public.oils_xpath(TEXT, TEXT);
4978 DROP FUNCTION IF EXISTS public.oils_xslt_process(TEXT, TEXT);
4980 CREATE OR REPLACE FUNCTION evergreen.xml_famous5_to_text( TEXT ) RETURNS TEXT AS $f$
4985 REPLACE( $1, '<', '<'),
4998 $f$ LANGUAGE SQL IMMUTABLE;
5000 CREATE OR REPLACE FUNCTION evergreen.oils_xpath ( TEXT, TEXT, TEXT[] ) RETURNS TEXT[] AS $f$
5002 CASE WHEN strpos(x,'<') = 1 THEN -- It's an element node
5004 ELSE -- it's text-ish
5005 evergreen.xml_famous5_to_text(x)
5008 FROM UNNEST(XPATH( $1, $2::XML, $3 )::TEXT[]) x;
5009 $f$ LANGUAGE SQL IMMUTABLE;
5011 -- Trust me, it's just simpler to duplicate these...
5012 CREATE OR REPLACE FUNCTION evergreen.oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS $f$
5014 CASE WHEN strpos(x,'<') = 1 THEN -- It's an element node
5016 ELSE -- it's text-ish
5017 evergreen.xml_famous5_to_text(x)
5020 FROM UNNEST(XPATH( $1, $2::XML)::TEXT[]) x;
5021 $f$ LANGUAGE SQL IMMUTABLE;
5023 CREATE OR REPLACE FUNCTION evergreen.oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
5032 # The following approach uses the older XML::LibXML 1.69 / XML::LibXSLT 1.68
5033 # methods of parsing XML documents and stylesheets, in the hopes of broader
5034 # compatibility with distributions
5035 my $parser = $_SHARED{'_xslt_process'}{parsers}{xml} || XML::LibXML->new();
5037 # Cache the XML parser, if we do not already have one
5038 $_SHARED{'_xslt_process'}{parsers}{xml} = $parser
5039 unless ($_SHARED{'_xslt_process'}{parsers}{xml});
5041 my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
5043 # Cache the XSLT processor, if we do not already have one
5044 $_SHARED{'_xslt_process'}{parsers}{xslt} = $xslt_parser
5045 unless ($_SHARED{'_xslt_process'}{parsers}{xslt});
5047 my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
5048 $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
5050 $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
5051 unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
5053 return $stylesheet->output_string(
5054 $stylesheet->transform(
5055 $parser->parse_string($doc)
5059 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
5061 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
5063 res authority.simple_heading%ROWTYPE;
5064 acsaf authority.control_set_authority_field%ROWTYPE;
5075 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
5078 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
5080 IF cset IS NULL THEN
5081 SELECT control_set INTO cset
5082 FROM authority.control_set_authority_field
5083 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
5087 res.record := auth_id;
5089 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
5091 res.atag := acsaf.id;
5092 tag_used := acsaf.tag;
5093 nfi_used := acsaf.nfi;
5094 joiner_text := COALESCE(acsaf.joiner, ' ');
5096 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)::TEXT[]) LOOP
5098 heading_text := COALESCE(
5099 oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml, joiner_text),
5103 IF nfi_used IS NOT NULL THEN
5105 sort_text := SUBSTRING(
5110 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
5122 sort_text := heading_text;
5125 IF heading_text IS NOT NULL AND heading_text <> '' THEN
5126 res.value := heading_text;
5127 res.sort_value := public.naco_normalize(sort_text);
5128 res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
5138 $func$ LANGUAGE PLPGSQL IMMUTABLE;
5140 CREATE OR REPLACE FUNCTION url_verify.extract_urls ( session_id INT, item_id INT ) RETURNS INT AS $$
5147 current_url_pos INT;
5148 current_selector url_verify.url_selector%ROWTYPE;
5152 FOR current_selector IN SELECT * FROM url_verify.url_selector s WHERE s.session = session_id LOOP
5153 current_url_pos := 1;
5155 SELECT (oils_xpath(current_selector.xpath || '/text()', b.marc))[current_url_pos] INTO current_url
5156 FROM biblio.record_entry b
5157 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5158 WHERE c.id = item_id;
5160 EXIT WHEN current_url IS NULL;
5162 SELECT (oils_xpath(current_selector.xpath || '/../@tag', b.marc))[current_url_pos] INTO current_tag
5163 FROM biblio.record_entry b
5164 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5165 WHERE c.id = item_id;
5167 IF current_tag IS NULL THEN
5168 current_tag := last_seen_tag;
5170 last_seen_tag := current_tag;
5173 SELECT (oils_xpath(current_selector.xpath || '/@code', b.marc))[current_url_pos] INTO current_sf
5174 FROM biblio.record_entry b
5175 JOIN container.biblio_record_entry_bucket_item c ON (c.target_biblio_record_entry = b.id)
5176 WHERE c.id = item_id;
5178 INSERT INTO url_verify.url (session, item, url_selector, tag, subfield, ord, full_url)
5179 VALUES ( session_id, item_id, current_selector.id, current_tag, current_sf, current_ord, current_url);
5181 current_url_pos := current_url_pos + 1;
5182 current_ord := current_ord + 1;
5186 RETURN current_ord - 1;
5188 $$ LANGUAGE PLPGSQL;
5190 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
5192 bib biblio.record_entry%ROWTYPE;
5193 idx config.metabib_field%ROWTYPE;
5194 xfrm config.xml_transform%ROWTYPE;
5196 transformed_xml TEXT;
5198 xml_node_list TEXT[];
5204 joiner TEXT := default_joiner; -- XXX will index defs supply a joiner?
5205 authority_text TEXT;
5206 authority_link BIGINT;
5207 output_row metabib.field_entry_template%ROWTYPE;
5210 -- Start out with no field-use bools set
5211 output_row.browse_field = FALSE;
5212 output_row.facet_field = FALSE;
5213 output_row.search_field = FALSE;
5216 SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
5218 -- Loop over the indexing entries
5219 FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
5221 joiner := COALESCE(idx.joiner, default_joiner);
5223 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
5225 -- See if we can skip the XSLT ... it's expensive
5226 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5227 -- Can't skip the transform
5228 IF xfrm.xslt <> '---' THEN
5229 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
5231 transformed_xml := bib.marc;
5234 prev_xfrm := xfrm.name;
5237 xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5240 FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
5241 CONTINUE WHEN xml_node !~ E'^\\s*<';
5243 -- XXX much of this should be moved into oils_xpath_string...
5244 curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
5245 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
5246 REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
5247 ), ' '), ''), -- throw away morally empty (bankrupt?) strings
5251 CONTINUE WHEN curr_text IS NULL OR curr_text = '';
5253 IF raw_text IS NOT NULL THEN
5254 raw_text := raw_text || joiner;
5257 raw_text := COALESCE(raw_text,'') || curr_text;
5259 -- autosuggest/metabib.browse_entry
5260 IF idx.browse_field THEN
5262 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
5263 browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5265 browse_text := curr_text;
5268 IF idx.browse_sort_xpath IS NOT NULL AND
5269 idx.browse_sort_xpath <> '' THEN
5271 sort_value := oils_xpath_string(
5272 idx.browse_sort_xpath, xml_node, joiner,
5273 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
5276 sort_value := browse_text;
5279 output_row.field_class = idx.field_class;
5280 output_row.field = idx.id;
5281 output_row.source = rid;
5282 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
5283 output_row.sort_value :=
5284 public.naco_normalize(sort_value);
5286 output_row.authority := NULL;
5288 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
5289 authority_text := oils_xpath_string(
5290 idx.authority_xpath, xml_node, joiner,
5292 ARRAY[xfrm.prefix, xfrm.namespace_uri],
5293 ARRAY['xlink','http://www.w3.org/1999/xlink']
5297 IF authority_text ~ '^\d+$' THEN
5298 authority_link := authority_text::BIGINT;
5299 PERFORM * FROM authority.record_entry WHERE id = authority_link;
5301 output_row.authority := authority_link;
5307 output_row.browse_field = TRUE;
5308 -- Returning browse rows with search_field = true for search+browse
5309 -- configs allows us to retain granularity of being able to search
5310 -- browse fields with "starts with" type operators (for example, for
5311 -- titles of songs in music albums)
5312 IF idx.search_field THEN
5313 output_row.search_field = TRUE;
5315 RETURN NEXT output_row;
5316 output_row.browse_field = FALSE;
5317 output_row.search_field = FALSE;
5318 output_row.sort_value := NULL;
5321 -- insert raw node text for faceting
5322 IF idx.facet_field THEN
5324 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
5325 facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
5327 facet_text := curr_text;
5330 output_row.field_class = idx.field_class;
5331 output_row.field = -1 * idx.id;
5332 output_row.source = rid;
5333 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
5335 output_row.facet_field = TRUE;
5336 RETURN NEXT output_row;
5337 output_row.facet_field = FALSE;
5342 CONTINUE WHEN raw_text IS NULL OR raw_text = '';
5344 -- insert combined node text for searching
5345 IF idx.search_field THEN
5346 output_row.field_class = idx.field_class;
5347 output_row.field = idx.id;
5348 output_row.source = rid;
5349 output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
5351 output_row.search_field = TRUE;
5352 RETURN NEXT output_row;
5353 output_row.search_field = FALSE;
5360 $func$ LANGUAGE PLPGSQL;
5362 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$
5364 transformed_xml TEXT;
5365 rmarc TEXT := prmarc;
5369 xfrm config.xml_transform%ROWTYPE;
5370 attr_vector INT[] := '{}'::INT[];
5371 attr_vector_tmp INT[];
5372 attr_list TEXT[] := pattr_list;
5374 norm_attr_value TEXT[];
5376 attr_def config.record_attr_definition%ROWTYPE;
5377 ccvm_row config.coded_value_map%ROWTYPE;
5380 IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
5381 SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition;
5384 IF rmarc IS NULL THEN
5385 SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
5388 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
5390 attr_value := '{}'::TEXT[];
5391 norm_attr_value := '{}'::TEXT[];
5392 attr_vector_tmp := '{}'::INT[];
5394 SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1;
5396 -- tag+sf attrs only support SVF
5397 IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
5398 SELECT ARRAY[ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' '))] INTO attr_value
5399 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
5401 AND tag LIKE attr_def.tag
5403 WHEN attr_def.sf_list IS NOT NULL
5404 THEN POSITION(subfield IN attr_def.sf_list) > 0
5411 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
5412 attr_value := vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
5414 IF NOT attr_def.multi THEN
5415 attr_value := ARRAY[attr_value[1]];
5418 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
5420 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
5422 -- See if we can skip the XSLT ... it's expensive
5423 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
5424 -- Can't skip the transform
5425 IF xfrm.xslt <> '---' THEN
5426 transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
5428 transformed_xml := rmarc;
5431 prev_xfrm := xfrm.name;
5434 IF xfrm.name IS NULL THEN
5435 -- just grab the marcxml (empty) transform
5436 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
5437 prev_xfrm := xfrm.name;
5440 FOR tmp_xml IN SELECT oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]) LOOP
5441 tmp_val := oils_xpath_string(
5444 COALESCE(attr_def.joiner,' '),
5445 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
5447 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5448 attr_value := attr_value || tmp_val;
5449 EXIT WHEN NOT attr_def.multi;
5453 ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
5454 SELECT ARRAY_AGG(m.value) INTO attr_value
5455 FROM vandelay.marc21_physical_characteristics(rmarc) v
5456 LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
5457 WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
5458 AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
5460 IF NOT attr_def.multi THEN
5461 attr_value := ARRAY[attr_value[1]];
5466 -- apply index normalizers to attr_value
5467 FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
5469 SELECT n.func AS func,
5470 n.param_count AS param_count,
5472 FROM config.index_normalizer n
5473 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
5474 WHERE attr = attr_def.name
5476 EXECUTE 'SELECT ' || normalizer.func || '(' ||
5477 COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
5479 WHEN normalizer.param_count > 0
5480 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
5486 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5487 norm_attr_value := norm_attr_value || tmp_val;
5491 IF attr_def.filter THEN
5492 -- Create unknown uncontrolled values and find the IDs of the values
5493 IF ccvm_row.id IS NULL THEN
5494 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
5495 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
5496 BEGIN -- use subtransaction to isolate unique constraint violations
5497 INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
5498 EXCEPTION WHEN unique_violation THEN END;
5502 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 );
5504 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
5507 -- Add the new value to the vector
5508 attr_vector := attr_vector || attr_vector_tmp;
5511 IF attr_def.sorter AND norm_attr_value[1] IS NOT NULL THEN
5512 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
5513 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
5518 /* We may need to rewrite the vlist to contain
5519 the intersection of new values for requested
5520 attrs and old values for ignored attrs. To
5521 do this, we take the old attr vlist and
5522 subtract any values that are valid for the
5523 requested attrs, and then add back the new
5524 set of attr values. */
5526 IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN
5527 SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
5528 SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
5529 attr_vector := attr_vector || attr_vector_tmp;
5532 -- On to composite attributes, now that the record attrs have been pulled. Processed in name order, so later composite
5533 -- attributes can depend on earlier ones.
5534 PERFORM metabib.compile_composite_attr_cache_init();
5535 FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
5537 FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
5539 tmp_val := metabib.compile_composite_attr( ccvm_row.id );
5540 CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
5542 IF attr_def.filter THEN
5543 IF attr_vector @@ tmp_val::query_int THEN
5544 attr_vector = attr_vector + intset(ccvm_row.id);
5545 EXIT WHEN NOT attr_def.multi;
5549 IF attr_def.sorter THEN
5550 IF attr_vector @@ tmp_val THEN
5551 DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
5552 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
5560 IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
5561 IF rdeleted THEN -- initial insert OR revivication
5562 DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
5563 INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
5565 UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
5571 $func$ LANGUAGE PLPGSQL;
5574 SELECT evergreen.upgrade_deps_block_check('0875', :eg_version);
5576 ALTER TABLE authority.record_entry ADD COLUMN heading TEXT, ADD COLUMN simple_heading TEXT;
5578 DROP INDEX IF EXISTS authority.unique_by_heading_and_thesaurus;
5579 DROP INDEX IF EXISTS authority.by_heading_and_thesaurus;
5580 DROP INDEX IF EXISTS authority.by_heading;
5582 -- Update without indexes for HOT update
5583 UPDATE authority.record_entry
5584 SET heading = authority.normalize_heading( marc ),
5585 simple_heading = authority.simple_normalize_heading( marc );
5587 CREATE INDEX by_heading_and_thesaurus ON authority.record_entry (heading) WHERE deleted IS FALSE or deleted = FALSE;
5588 CREATE INDEX by_heading ON authority.record_entry (simple_heading) WHERE deleted IS FALSE or deleted = FALSE;
5591 CREATE OR REPLACE FUNCTION authority.normalize_heading_for_upsert () RETURNS TRIGGER AS $f$
5593 NEW.heading := authority.normalize_heading( NEW.marc );
5594 NEW.simple_heading := authority.simple_normalize_heading( NEW.marc );
5597 $f$ LANGUAGE PLPGSQL;
5599 CREATE TRIGGER update_headings_tgr BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE authority.normalize_heading_for_upsert();
5601 ALTER FUNCTION authority.normalize_heading(TEXT, BOOL) STABLE STRICT;
5602 ALTER FUNCTION authority.normalize_heading(TEXT) STABLE STRICT;
5603 ALTER FUNCTION authority.simple_normalize_heading(TEXT) STABLE STRICT;
5604 ALTER FUNCTION authority.simple_heading_set(TEXT) STABLE STRICT;
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 -- re-enable the triggers we disabled before starting the transaction
5821 ALTER TABLE authority.record_entry ENABLE TRIGGER a_marcxml_is_well_formed;
5822 ALTER TABLE authority.record_entry ENABLE TRIGGER aaa_auth_ingest_or_delete;
5823 ALTER TABLE authority.record_entry ENABLE TRIGGER b_maintain_901;
5824 ALTER TABLE authority.record_entry ENABLE TRIGGER c_maintain_control_numbers;
5825 ALTER TABLE authority.record_entry ENABLE TRIGGER map_thesaurus_to_control_set;
5827 -- Not running changes from example.reporter-extension.sql since these are
5828 -- not installed by default, but including a helpful note.
5830 \qecho **** NOTICE ****
5831 \qecho 'There were changes in example.reporter-extension.sql.'
5832 \qecho 'Please run that script again if you use it in your system'
5833 \qecho 'to apply new changes.'
5836 \qecho **** Certain improvements in 2.6, particularly attribute improvements,
5837 \qecho **** require a reingest of all your bib records. In order to allow
5838 \qecho **** this to continue without locking your entire bibliographic data
5839 \qecho **** set, consider generating an SQL script with the following queries,
5840 \qecho **** then running it via psql.
5842 \qecho **** If you have a large number of bibs (100,000+), please consider this
5843 \qecho **** as a starting point only, as you will likely wish to parallelize
5844 \qecho **** this is some fashion.
5846 \qecho **** If you require a more responsive catalog/database while reingesting,
5847 \qecho **** consider adding 'pg_sleep()' calls between each reingest update.
5850 \qecho '\\o /tmp/reingest_2.6_bib_recs.sql'
5851 \qecho 'SELECT ''-- Grab current setting'';'
5852 \qecho 'SELECT ''\\set force_reingest '' || enabled FROM config.internal_flag WHERE name = ''ingest.reingest.force_on_same_marc'';'
5853 \qecho 'SELECT ''update config.internal_flag set enabled = true where name = ''''ingest.reingest.force_on_same_marc'''';'';'
5854 \qecho 'SELECT ''update biblio.record_entry set id = id where id = '' || id || '';'' FROM biblio.record_entry WHERE NOT DELETED AND id > 0;'
5855 \qecho 'SELECT ''-- Restore previous setting'';'
5856 \qecho 'SELECT ''update config.internal_flag set enabled = :force_reingest where name = \'\'ingest.reingest.force_on_same_marc\'\';'';'