1 --Upgrade Script for 2.3.0 to 2.3.1
2 \set eg_version '''2.3.1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.1', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0740', :eg_version);
9 FUNCTION metabib.suggest_browse_entries(
10 raw_query_text TEXT, -- actually typed by humans at the UI level
11 search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc
12 headline_opts TEXT, -- markup options for ts_headline()
13 visibility_org INTEGER,-- null if you don't want opac visibility test
14 query_limit INTEGER,-- use in LIMIT clause of interal query
15 normalization INTEGER -- argument to TS_RANK_CD()
19 buoyant_and_class_match BOOL,
24 match TEXT -- marked up
27 prepared_query_texts TEXT[];
30 opac_visibility_join TEXT;
31 search_class_join TEXT;
34 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
36 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
37 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
39 visibility_org := NULLIF(visibility_org,-1);
40 IF visibility_org IS NOT NULL THEN
41 opac_visibility_join := '
42 JOIN asset.opac_visible_copies aovc ON (
43 aovc.record = x.source AND
44 aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
47 opac_visibility_join := '';
50 -- The following determines whether we only provide suggestsons matching
51 -- the user's selected search_class, or whether we show other suggestions
52 -- too. The reason for MIN() is that for search_classes like
53 -- 'title|proper|uniform' you would otherwise get multiple rows. The
54 -- implication is that if title as a class doesn't have restrict,
55 -- nor does the proper field, but the uniform field does, you're going
56 -- to get 'false' for your overall evaluation of 'should we restrict?'
57 -- To invert that, change from MIN() to MAX().
61 MIN(cmc.restrict::INT) AS restrict_class,
62 MIN(cmf.restrict::INT) AS restrict_field
63 FROM metabib.search_class_to_registered_components(search_class)
64 AS _registered (field_class TEXT, field INT)
66 config.metabib_class cmc ON (cmc.name = _registered.field_class)
68 config.metabib_field cmf ON (cmf.id = _registered.field);
70 -- evaluate 'should we restrict?'
71 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
72 search_class_join := '
74 metabib.search_class_to_registered_components($2)
75 AS _registered (field_class TEXT, field INT) ON (
76 (_registered.field IS NULL AND
77 _registered.field_class = cmf.field_class) OR
78 (_registered.field = cmf.id)
82 search_class_join := '
84 metabib.search_class_to_registered_components($2)
85 AS _registered (field_class TEXT, field INT) ON (
86 _registered.field_class = cmc.name
91 RETURN QUERY EXECUTE '
100 TS_HEADLINE(value, $7, $3)
101 FROM (SELECT DISTINCT
104 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
105 _registered.field = cmf.id AS restrict,
107 TS_RANK_CD(mbe.index_vector, $1, $6),
110 FROM metabib.browse_entry_def_map mbedm
111 JOIN (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000) mbe ON (mbe.id = mbedm.entry)
112 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
113 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
114 ' || search_class_join || '
115 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
117 ' || opac_visibility_join || '
118 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
120 ' -- sic, repeat the order by clause in the outer select too
122 query, search_class, headline_opts,
123 visibility_org, query_limit, normalization, plain_query
127 -- buoyant AND chosen class = match class
128 -- chosen field = match field
135 $func$ LANGUAGE PLPGSQL;
138 SELECT evergreen.upgrade_deps_block_check('0742', :eg_version);
140 -- Prepare for the July 2013 introduction of OCLC's "on" prefix
143 CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$
146 use MARC::File::XML (BinaryEncoding => 'UTF-8');
149 use Unicode::Normalize;
151 MARC::Charset->assume_unicode(1);
153 my $record = MARC::Record->new_from_xml($_TD->{new}{marc});
154 my $schema = $_TD->{table_schema};
155 my $rec_id = $_TD->{new}{id};
157 # Short-circuit if maintaining control numbers per MARC21 spec is not enabled
158 my $enable = spi_exec_query("SELECT enabled FROM config.global_flag WHERE name = 'cat.maintain_control_numbers'");
159 if (!($enable->{processed}) or $enable->{rows}[0]->{enabled} eq 'f') {
163 # Get the control number identifier from an OU setting based on $_TD->{new}{owner}
164 my $ou_cni = 'EVRGRN';
167 if ($schema eq 'serial') {
168 $owner = $_TD->{new}{owning_lib};
170 # are.owner and bre.owner can be null, so fall back to the consortial setting
171 $owner = $_TD->{new}{owner} || 1;
174 my $ous_rv = spi_exec_query("SELECT value FROM actor.org_unit_ancestor_setting('cat.marc_control_number_identifier', $owner)");
175 if ($ous_rv->{processed}) {
176 $ou_cni = $ous_rv->{rows}[0]->{value};
177 $ou_cni =~ s/"//g; # Stupid VIM syntax highlighting"
179 # Fall back to the shortname of the OU if there was no OU setting
180 $ous_rv = spi_exec_query("SELECT shortname FROM actor.org_unit WHERE id = $owner");
181 if ($ous_rv->{processed}) {
182 $ou_cni = $ous_rv->{rows}[0]->{shortname};
186 my ($create, $munge) = (0, 0);
188 my @scns = $record->field('035');
190 foreach my $id_field ('001', '003') {
192 my @controls = $record->field($id_field);
194 if ($id_field eq '001') {
195 $spec_value = $rec_id;
197 $spec_value = $ou_cni;
200 # Create the 001/003 if none exist
201 if (scalar(@controls) == 1) {
202 # Only one field; check to see if we need to munge it
203 unless (grep $_->data() eq $spec_value, @controls) {
207 # Delete the other fields, as with more than 1 001/003 we do not know which 003/001 to match
208 foreach my $control (@controls) {
209 $record->delete_field($control);
211 $record->insert_fields_ordered(MARC::Field->new($id_field, $spec_value));
216 my $cn = $record->field('001')->data();
217 # Special handling of OCLC numbers, often found in records that lack 003
218 if ($cn =~ /^o(c[nm]|n)\d/) {
219 $cn =~ s/^o(c[nm]|n)0*(\d+)/$2/;
220 $record->field('003')->data('OCoLC');
224 # Now, if we need to munge the 001, we will first push the existing 001/003
225 # into the 035; but if the record did not have one (and one only) 001 and 003
226 # to begin with, skip this process
227 if ($munge and not $create) {
229 my $scn = "(" . $record->field('003')->data() . ")" . $cn;
231 # Do not create duplicate 035 fields
232 unless (grep $_->subfield('a') eq $scn, @scns) {
233 $record->insert_fields_ordered(MARC::Field->new('035', '', '', 'a' => $scn));
237 # Set the 001/003 and update the MARC
238 if ($create or $munge) {
239 $record->field('001')->data($rec_id);
240 $record->field('003')->data($ou_cni);
242 my $xml = $record->as_xml_record();
244 $xml =~ s/^<\?xml.+\?\s*>//go;
245 $xml =~ s/>\s+</></go;
246 $xml =~ s/\p{Cc}//go;
248 # Embed a version of OpenILS::Application::AppUtils->entityize()
249 # to avoid having to set PERL5LIB for PostgreSQL as well
251 # If we are going to convert non-ASCII characters to XML entities,
252 # we had better be dealing with a UTF8 string to begin with
253 $xml = decode_utf8($xml);
257 # Convert raw ampersands to entities
258 $xml =~ s/&(?!\S+;)/&/gso;
260 # Convert Unicode characters to entities
261 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
263 $xml =~ s/[\x00-\x1f]//go;
264 $_TD->{new}{marc} = $xml;
270 $func$ LANGUAGE PLPERLU;