1 --Upgrade Script for 2.2.0 to 2.2.1
2 \set eg_version '''2.2.1'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.2.1', :eg_version);
5 -- Evergreen DB patch 0722.schema.acq-po-state-constraint.sql
9 -- check whether patch can be applied
10 SELECT evergreen.upgrade_deps_block_check('0722', :eg_version);
12 ALTER TABLE acq.purchase_order ADD CONSTRAINT valid_po_state
13 CHECK (state IN ('new','pending','on-order','received','cancelled'));
15 -- Evergreen DB patch 0723.schema.function.get_locale_name.sql
19 -- check whether patch can be applied
20 SELECT evergreen.upgrade_deps_block_check('0723', :eg_version);
22 CREATE OR REPLACE FUNCTION evergreen.get_locale_name(
30 eg_locale := LOWER(SUBSTRING(locale FROM 1 FOR 2)) || '-' || UPPER(SUBSTRING(locale FROM 4 FOR 2));
32 SELECT i18nc.string INTO name
33 FROM config.i18n_locale i18nl
34 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
35 WHERE i18nc.identity_value = eg_locale
37 AND i18nc.fq_field = 'i18n_l.name';
40 SELECT i18nl.name INTO name
41 FROM config.i18n_locale i18nl
42 WHERE code = eg_locale;
45 SELECT i18nc.string INTO description
46 FROM config.i18n_locale i18nl
47 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
48 WHERE i18nc.identity_value = eg_locale
50 AND i18nc.fq_field = 'i18n_l.description';
52 IF description IS NULL THEN
53 SELECT i18nl.description INTO description
54 FROM config.i18n_locale i18nl
55 WHERE code = eg_locale;
58 $$ LANGUAGE PLPGSQL COST 1 STABLE;
61 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0724', :eg_version); -- denials/gmcharlt
63 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
66 use MARC::File::XML (BinaryEncoding => 'UTF-8');
69 use Unicode::Normalize;
71 MARC::Charset->assume_unicode(1);
73 my $schema = $_TD->{table_schema};
74 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
76 my @old901s = $marc->field('901');
77 $marc->delete_fields(@old901s);
79 if ($schema eq 'biblio') {
80 my $tcn_value = $_TD->{new}{tcn_value};
82 # Set TCN value to record ID?
83 my $id_as_tcn = spi_exec_query("
85 FROM config.global_flag
86 WHERE name = 'cat.bib.use_id_for_tcn'
88 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
89 $tcn_value = $_TD->{new}{id};
92 my $new_901 = MARC::Field->new("901", " ", " ",
94 "b" => $_TD->{new}{tcn_source},
95 "c" => $_TD->{new}{id},
99 if ($_TD->{new}{owner}) {
100 $new_901->add_subfields("o" => $_TD->{new}{owner});
103 if ($_TD->{new}{share_depth}) {
104 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
107 $marc->append_fields($new_901);
108 } elsif ($schema = 'authority') {
109 $marc->append_fields(
111 "c" => $_TD->{new}{id},
115 } elsif ($schema = 'serial') {
116 my $new_901 = MARC::Field->new("901", " ", " ",
117 "c" => $_TD->{new}{id},
119 "o" => $_TD->{new}{owning_lib},
122 if ($_TD->{new}{record}) {
123 $new_901->add_subfields("r" => $_TD->{new}{record});
126 $marc->append_fields($new_901);
128 $marc->append_fields(
130 "c" => $_TD->{new}{id},
136 my $xml = $marc->as_xml_record();
138 $xml =~ s/^<\?xml.+\?\s*>//go;
139 $xml =~ s/>\s+</></go;
140 $xml =~ s/\p{Cc}//go;
142 # Embed a version of OpenILS::Application::AppUtils->entityize()
143 # to avoid having to set PERL5LIB for PostgreSQL as well
145 # If we are going to convert non-ASCII characters to XML entities,
146 # we had better be dealing with a UTF8 string to begin with
147 $xml = decode_utf8($xml);
151 # Convert raw ampersands to entities
152 $xml =~ s/&(?!\S+;)/&/gso;
154 # Convert Unicode characters to entities
155 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
157 $xml =~ s/[\x00-\x1f]//go;
158 $_TD->{new}{marc} = $xml;
161 $func$ LANGUAGE PLPERLU;
164 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0725', :eg_version); -- gmcharlt/denials
166 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
169 use MARC::File::XML (BinaryEncoding => 'UTF-8');
172 use Unicode::Normalize;
174 MARC::Charset->assume_unicode(1);
176 my $schema = $_TD->{table_schema};
177 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
179 my @old901s = $marc->field('901');
180 $marc->delete_fields(@old901s);
182 if ($schema eq 'biblio') {
183 my $tcn_value = $_TD->{new}{tcn_value};
185 # Set TCN value to record ID?
186 my $id_as_tcn = spi_exec_query("
188 FROM config.global_flag
189 WHERE name = 'cat.bib.use_id_for_tcn'
191 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
192 $tcn_value = $_TD->{new}{id};
195 my $new_901 = MARC::Field->new("901", " ", " ",
197 "b" => $_TD->{new}{tcn_source},
198 "c" => $_TD->{new}{id},
202 if ($_TD->{new}{owner}) {
203 $new_901->add_subfields("o" => $_TD->{new}{owner});
206 if ($_TD->{new}{share_depth}) {
207 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
210 $marc->append_fields($new_901);
211 } elsif ($schema eq 'authority') {
212 $marc->append_fields(
214 "c" => $_TD->{new}{id},
218 } elsif ($schema eq 'serial') {
219 my $new_901 = MARC::Field->new("901", " ", " ",
220 "c" => $_TD->{new}{id},
222 "o" => $_TD->{new}{owning_lib},
225 if ($_TD->{new}{record}) {
226 $new_901->add_subfields("r" => $_TD->{new}{record});
229 $marc->append_fields($new_901);
231 $marc->append_fields(
233 "c" => $_TD->{new}{id},
239 my $xml = $marc->as_xml_record();
241 $xml =~ s/^<\?xml.+\?\s*>//go;
242 $xml =~ s/>\s+</></go;
243 $xml =~ s/\p{Cc}//go;
245 # Embed a version of OpenILS::Application::AppUtils->entityize()
246 # to avoid having to set PERL5LIB for PostgreSQL as well
248 # If we are going to convert non-ASCII characters to XML entities,
249 # we had better be dealing with a UTF8 string to begin with
250 $xml = decode_utf8($xml);
254 # Convert raw ampersands to entities
255 $xml =~ s/&(?!\S+;)/&/gso;
257 # Convert Unicode characters to entities
258 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
260 $xml =~ s/[\x00-\x1f]//go;
261 $_TD->{new}{marc} = $xml;
264 $func$ LANGUAGE PLPERLU;
267 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0726', :eg_version); -- denials
269 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
272 use MARC::File::XML (BinaryEncoding => 'UTF-8');
275 use Unicode::Normalize;
277 MARC::Charset->assume_unicode(1);
279 my $schema = $_TD->{table_schema};
280 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
282 my @old901s = $marc->field('901');
283 $marc->delete_fields(@old901s);
285 if ($schema eq 'biblio') {
286 my $tcn_value = $_TD->{new}{tcn_value};
288 # Set TCN value to record ID?
289 my $id_as_tcn = spi_exec_query("
291 FROM config.global_flag
292 WHERE name = 'cat.bib.use_id_for_tcn'
294 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
295 $tcn_value = $_TD->{new}{id};
298 my $new_901 = MARC::Field->new("901", " ", " ",
300 "b" => $_TD->{new}{tcn_source},
301 "c" => $_TD->{new}{id},
305 if ($_TD->{new}{owner}) {
306 $new_901->add_subfields("o" => $_TD->{new}{owner});
309 if ($_TD->{new}{share_depth}) {
310 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
313 $marc->append_fields($new_901);
314 } elsif ($schema eq 'authority') {
315 my $new_901 = MARC::Field->new("901", " ", " ",
316 "c" => $_TD->{new}{id},
319 $marc->append_fields($new_901);
320 } elsif ($schema eq 'serial') {
321 my $new_901 = MARC::Field->new("901", " ", " ",
322 "c" => $_TD->{new}{id},
324 "o" => $_TD->{new}{owning_lib},
327 if ($_TD->{new}{record}) {
328 $new_901->add_subfields("r" => $_TD->{new}{record});
331 $marc->append_fields($new_901);
333 my $new_901 = MARC::Field->new("901", " ", " ",
334 "c" => $_TD->{new}{id},
337 $marc->append_fields($new_901);
340 my $xml = $marc->as_xml_record();
342 $xml =~ s/^<\?xml.+\?\s*>//go;
343 $xml =~ s/>\s+</></go;
344 $xml =~ s/\p{Cc}//go;
346 # Embed a version of OpenILS::Application::AppUtils->entityize()
347 # to avoid having to set PERL5LIB for PostgreSQL as well
349 # If we are going to convert non-ASCII characters to XML entities,
350 # we had better be dealing with a UTF8 string to begin with
351 $xml = decode_utf8($xml);
355 # Convert raw ampersands to entities
356 $xml =~ s/&(?!\S+;)/&/gso;
358 # Convert Unicode characters to entities
359 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
361 $xml =~ s/[\x00-\x1f]//go;
362 $_TD->{new}{marc} = $xml;
365 $func$ LANGUAGE PLPERLU;