1 --Upgrade Script for 2.4.3 to 2.4.4
2 \set eg_version '''2.4.4'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.4.4', :eg_version);
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0838', :eg_version);
9 DELETE FROM config.metabib_field_index_norm_map
10 WHERE field = 25 AND norm IN (
12 FROM config.index_normalizer
13 WHERE func IN ('search_normalize','split_date_range')
17 SELECT evergreen.upgrade_deps_block_check('0840', :eg_version);
19 INSERT INTO config.usr_setting_type (name,grp,opac_visible,label,description,datatype) VALUES (
20 'ui.grid_columns.conify.config.circ_matrix_matchpoint',
24 'ui.grid_columns.conify.config.circ_matrix_matchpoint',
25 'Circulation Policy Configuration',
30 'ui.grid_columns.conify.config.circ_matrix_matchpoint',
31 'Circulation Policy Configuration Column Settings',
39 -- check whether patch can be applied
40 SELECT evergreen.upgrade_deps_block_check('0841', :eg_version);
42 ALTER TABLE config.metabib_field_ts_map DROP CONSTRAINT metabib_field_ts_map_metabib_field_fkey;
43 ALTER TABLE config.metabib_search_alias DROP CONSTRAINT metabib_search_alias_field_fkey;
44 ALTER TABLE metabib.browse_entry_def_map DROP CONSTRAINT browse_entry_def_map_def_fkey;
46 ALTER TABLE config.metabib_field_ts_map ADD CONSTRAINT metabib_field_ts_map_metabib_field_fkey FOREIGN KEY (metabib_field) REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
47 ALTER TABLE config.metabib_search_alias ADD CONSTRAINT metabib_search_alias_field_fkey FOREIGN KEY (field) REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
48 ALTER TABLE metabib.browse_entry_def_map ADD CONSTRAINT browse_entry_def_map_def_fkey FOREIGN KEY (def) REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
51 DROP FUNCTION IF EXISTS config.modify_metabib_field(source INT, target INT);
52 CREATE FUNCTION config.modify_metabib_field(v_source INT, target INT) RETURNS INT AS $func$
58 SELECT field_class INTO f_class FROM config.metabib_field WHERE id = v_source;
62 IF target IS NULL THEN
63 target_id = v_source + 1000;
67 SELECT id FROM config.metabib_field INTO check_id WHERE id = target_id;
69 RAISE NOTICE 'Cannot bump config.metabib_field.id from % to %; the target ID already exists.', v_source, target_id;
72 UPDATE config.metabib_field SET id = target_id WHERE id = v_source;
73 EXECUTE ' UPDATE metabib.' || f_class || '_field_entry SET field = ' || target_id || ' WHERE field = ' || v_source;
74 UPDATE config.metabib_field_ts_map SET metabib_field = target_id WHERE metabib_field = v_source;
75 UPDATE config.metabib_field_index_norm_map SET field = target_id WHERE field = v_source;
76 UPDATE search.relevance_adjustment SET field = target_id WHERE field = v_source;
77 UPDATE config.metabib_search_alias SET field = target_id WHERE field = v_source;
78 UPDATE metabib.browse_entry_def_map SET def = target_id WHERE def = v_source;
81 $func$ LANGUAGE PLPGSQL;
83 SELECT config.modify_metabib_field(id, NULL)
84 FROM config.metabib_field
87 SELECT SETVAL('config.metabib_field_id_seq', GREATEST(1000, (SELECT MAX(id) FROM config.metabib_field)));
90 -- check whether patch can be applied
91 SELECT evergreen.upgrade_deps_block_check('0842', :eg_version);
93 -- this upgrade is only for people coming from 2_3, and is a NO-OP for those on 2_4 or later
94 ALTER TABLE config.metabib_field_ts_map DROP CONSTRAINT metabib_field_ts_map_metabib_field_fkey;
96 ALTER TABLE config.metabib_field_ts_map ADD CONSTRAINT metabib_field_ts_map_metabib_field_fkey FOREIGN KEY (metabib_field) REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
99 SELECT evergreen.upgrade_deps_block_check('0846', :eg_version);
101 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
104 use MARC::File::XML (BinaryEncoding => 'UTF-8');
108 MARC::Charset->assume_unicode(1);
110 my $target_xml = shift;
111 my $source_xml = shift;
112 my $field_spec = shift;
113 my $force_add = shift || 0;
115 my $target_r = MARC::Record->new_from_xml( $target_xml );
116 my $source_r = MARC::Record->new_from_xml( $source_xml );
118 return $target_xml unless ($target_r && $source_r);
120 my @field_list = split(',', $field_spec);
123 for my $f (@field_list) {
124 $f =~ s/^\s*//; $f =~ s/\s*$//;
125 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
131 $match =~ s/^\s*//; $match =~ s/\s*$//;
132 $fields{$field} = { sf => [ split('', $sf) ] };
134 my ($msf,$mre) = split('~', $match);
135 if (length($msf) > 0 and length($mre) > 0) {
136 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
137 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
138 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
144 for my $f ( keys %fields) {
145 if ( @{$fields{$f}{sf}} ) {
146 for my $from_field ($source_r->field( $f )) {
147 my @tos = $target_r->field( $f );
149 next if (exists($fields{$f}{match}) and !$force_add);
150 my @new_fields = map { $_->clone } $source_r->field( $f );
151 $target_r->insert_fields_ordered( @new_fields );
153 for my $to_field (@tos) {
154 if (exists($fields{$f}{match})) {
155 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
157 for my $old_sf ($from_field->subfields) {
158 $to_field->add_subfields( @$old_sf ) if grep(/$$old_sf[0]/,@{$fields{$f}{sf}});
164 my @new_fields = map { $_->clone } $source_r->field( $f );
165 $target_r->insert_fields_ordered( @new_fields );
169 $target_xml = $target_r->as_xml_record;
170 $target_xml =~ s/^<\?.+?\?>$//mo;
171 $target_xml =~ s/\n//sgo;
172 $target_xml =~ s/>\s+</></sgo;
176 $_$ LANGUAGE PLPERLU;
180 SELECT evergreen.upgrade_deps_block_check('0847', :eg_version);
182 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
185 main_entry authority.control_set_authority_field%ROWTYPE;
186 bib_field authority.control_set_bib_field%ROWTYPE;
187 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
189 replace_data XML[] DEFAULT '{}'::XML[];
190 replace_rules TEXT[] DEFAULT '{}'::TEXT[];
195 IF auth_id IS NULL THEN
199 -- Default to the LoC controll set
200 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
202 -- if none, make a best guess
204 SELECT control_set INTO cset
205 FROM authority.control_set_authority_field
207 SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
208 FROM authority.record_entry
214 -- if STILL none, no-op change
218 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
219 XMLELEMENT( name leader, '00881nam a2200193 4500'),
222 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
225 XMLATTRIBUTES('d' AS code),
232 FOR main_entry IN SELECT * FROM authority.control_set_authority_field acsaf WHERE acsaf.control_set = cset AND acsaf.main_entry IS NULL LOOP
233 auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
234 auth_i1 = (XPATH('@ind1',auth_field[1]))[1];
235 auth_i2 = (XPATH('@ind2',auth_field[1]))[1];
236 IF ARRAY_LENGTH(auth_field,1) > 0 THEN
237 FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
238 SELECT XMLELEMENT( -- XMLAGG avoids magical <element> creation, but requires unnest subquery
240 XMLATTRIBUTES(bib_field.tag AS tag, auth_i1 AS ind1, auth_i2 AS ind2),
242 ) INTO tmp_data FROM UNNEST(XPATH('//*[local-name()="subfield"]', auth_field[1]));
243 replace_data := replace_data || tmp_data;
244 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
251 SELECT XMLAGG(UNNEST) INTO tmp_data FROM UNNEST(replace_data);
255 XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
256 XMLELEMENT( name leader, '00881nam a2200193 4500'),
260 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
263 XMLATTRIBUTES('r' AS code),
264 ARRAY_TO_STRING(replace_rules,',')
269 $f$ STABLE LANGUAGE PLPGSQL;
272 SELECT evergreen.upgrade_deps_block_check('0848', :eg_version);
274 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
276 acsaf authority.control_set_authority_field%ROWTYPE;
287 auth_id INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT;
289 SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
292 SELECT control_set INTO cset
293 FROM authority.control_set_authority_field
294 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
298 thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
299 IF thes_code IS NULL THEN
301 ELSIF thes_code = 'z' THEN
302 thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' );
306 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
307 tag_used := acsaf.tag;
308 nfi_used := acsaf.nfi;
311 FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
312 FOR sf_node IN SELECT unnest(oils_xpath('//*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
314 tmp_text := oils_xpath_string('.', sf_node);
315 sf := oils_xpath_string('./@code', sf_node);
317 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
319 tmp_text := SUBSTRING(
324 oils_xpath_string('./@ind'||nfi_used, tag_node),
339 IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
340 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
344 EXIT WHEN heading_text <> '';
347 EXIT WHEN heading_text <> '';
350 IF heading_text <> '' THEN
351 IF no_thesaurus IS TRUE THEN
352 heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
354 heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
357 heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
362 $func$ LANGUAGE PLPGSQL IMMUTABLE;
364 CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$
366 res authority.simple_heading%ROWTYPE;
367 acsaf authority.control_set_authority_field%ROWTYPE;
377 auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT;
380 res.record := auth_id;
382 SELECT control_set INTO cset
383 FROM authority.control_set_authority_field
384 WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
387 FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
389 res.atag := acsaf.id;
390 tag_used := acsaf.tag;
391 nfi_used := acsaf.nfi;
393 FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
395 heading_text := public.naco_normalize(
397 oils_xpath_string('//*[contains("'||acsaf.sf_list||'",@code)]',tmp_xml::TEXT, ' '),
402 IF nfi_used IS NOT NULL THEN
404 sort_text := SUBSTRING(
409 oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
421 sort_text := heading_text;
424 IF heading_text IS NOT NULL AND heading_text <> '' THEN
425 res.value := heading_text;
426 res.sort_value := sort_text;
436 $func$ LANGUAGE PLPGSQL IMMUTABLE;
441 \qecho If your site's bibcn searches are affected the issue addressed in 0838
442 \qecho you may wish to reingest your bib records now. It's probably not worth
443 \qecho it for many sites.