1 --Upgrade Script for 2.2.0 to 2.2.1
3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.2.1', :eg_version);
4 -- Evergreen DB patch 0722.schema.acq-po-state-constraint.sql
8 -- check whether patch can be applied
9 SELECT evergreen.upgrade_deps_block_check('0722', :eg_version);
11 ALTER TABLE acq.purchase_order ADD CONSTRAINT valid_po_state
12 CHECK (state IN ('new','pending','on-order','received','cancelled'));
14 -- Evergreen DB patch 0723.schema.function.get_locale_name.sql
18 -- check whether patch can be applied
19 SELECT evergreen.upgrade_deps_block_check('0723', :eg_version);
21 CREATE OR REPLACE FUNCTION evergreen.get_locale_name(
29 eg_locale := LOWER(SUBSTRING(locale FROM 1 FOR 2)) || '-' || UPPER(SUBSTRING(locale FROM 4 FOR 2));
31 SELECT i18nc.string INTO name
32 FROM config.i18n_locale i18nl
33 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
34 WHERE i18nc.identity_value = eg_locale
36 AND i18nc.fq_field = 'i18n_l.name';
39 SELECT i18nl.name INTO name
40 FROM config.i18n_locale i18nl
41 WHERE code = eg_locale;
44 SELECT i18nc.string INTO description
45 FROM config.i18n_locale i18nl
46 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
47 WHERE i18nc.identity_value = eg_locale
49 AND i18nc.fq_field = 'i18n_l.description';
51 IF description IS NULL THEN
52 SELECT i18nl.description INTO description
53 FROM config.i18n_locale i18nl
54 WHERE code = eg_locale;
57 $$ LANGUAGE PLPGSQL COST 1 STABLE;
60 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0724', :eg_version); -- denials/gmcharlt
62 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
65 use MARC::File::XML (BinaryEncoding => 'UTF-8');
68 use Unicode::Normalize;
70 MARC::Charset->assume_unicode(1);
72 my $schema = $_TD->{table_schema};
73 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
75 my @old901s = $marc->field('901');
76 $marc->delete_fields(@old901s);
78 if ($schema eq 'biblio') {
79 my $tcn_value = $_TD->{new}{tcn_value};
81 # Set TCN value to record ID?
82 my $id_as_tcn = spi_exec_query("
84 FROM config.global_flag
85 WHERE name = 'cat.bib.use_id_for_tcn'
87 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
88 $tcn_value = $_TD->{new}{id};
91 my $new_901 = MARC::Field->new("901", " ", " ",
93 "b" => $_TD->{new}{tcn_source},
94 "c" => $_TD->{new}{id},
98 if ($_TD->{new}{owner}) {
99 $new_901->add_subfields("o" => $_TD->{new}{owner});
102 if ($_TD->{new}{share_depth}) {
103 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
106 $marc->append_fields($new_901);
107 } elsif ($schema = 'authority') {
108 $marc->append_fields(
110 "c" => $_TD->{new}{id},
114 } elsif ($schema = 'serial') {
115 my $new_901 = MARC::Field->new("901", " ", " ",
116 "c" => $_TD->{new}{id},
118 "o" => $_TD->{new}{owning_lib},
121 if ($_TD->{new}{record}) {
122 $new_901->add_subfields("r" => $_TD->{new}{record});
125 $marc->append_fields($new_901);
127 $marc->append_fields(
129 "c" => $_TD->{new}{id},
135 my $xml = $marc->as_xml_record();
137 $xml =~ s/^<\?xml.+\?\s*>//go;
138 $xml =~ s/>\s+</></go;
139 $xml =~ s/\p{Cc}//go;
141 # Embed a version of OpenILS::Application::AppUtils->entityize()
142 # to avoid having to set PERL5LIB for PostgreSQL as well
144 # If we are going to convert non-ASCII characters to XML entities,
145 # we had better be dealing with a UTF8 string to begin with
146 $xml = decode_utf8($xml);
150 # Convert raw ampersands to entities
151 $xml =~ s/&(?!\S+;)/&/gso;
153 # Convert Unicode characters to entities
154 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
156 $xml =~ s/[\x00-\x1f]//go;
157 $_TD->{new}{marc} = $xml;
160 $func$ LANGUAGE PLPERLU;
163 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0725', :eg_version); -- gmcharlt/denials
165 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
168 use MARC::File::XML (BinaryEncoding => 'UTF-8');
171 use Unicode::Normalize;
173 MARC::Charset->assume_unicode(1);
175 my $schema = $_TD->{table_schema};
176 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
178 my @old901s = $marc->field('901');
179 $marc->delete_fields(@old901s);
181 if ($schema eq 'biblio') {
182 my $tcn_value = $_TD->{new}{tcn_value};
184 # Set TCN value to record ID?
185 my $id_as_tcn = spi_exec_query("
187 FROM config.global_flag
188 WHERE name = 'cat.bib.use_id_for_tcn'
190 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
191 $tcn_value = $_TD->{new}{id};
194 my $new_901 = MARC::Field->new("901", " ", " ",
196 "b" => $_TD->{new}{tcn_source},
197 "c" => $_TD->{new}{id},
201 if ($_TD->{new}{owner}) {
202 $new_901->add_subfields("o" => $_TD->{new}{owner});
205 if ($_TD->{new}{share_depth}) {
206 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
209 $marc->append_fields($new_901);
210 } elsif ($schema eq 'authority') {
211 $marc->append_fields(
213 "c" => $_TD->{new}{id},
217 } elsif ($schema eq 'serial') {
218 my $new_901 = MARC::Field->new("901", " ", " ",
219 "c" => $_TD->{new}{id},
221 "o" => $_TD->{new}{owning_lib},
224 if ($_TD->{new}{record}) {
225 $new_901->add_subfields("r" => $_TD->{new}{record});
228 $marc->append_fields($new_901);
230 $marc->append_fields(
232 "c" => $_TD->{new}{id},
238 my $xml = $marc->as_xml_record();
240 $xml =~ s/^<\?xml.+\?\s*>//go;
241 $xml =~ s/>\s+</></go;
242 $xml =~ s/\p{Cc}//go;
244 # Embed a version of OpenILS::Application::AppUtils->entityize()
245 # to avoid having to set PERL5LIB for PostgreSQL as well
247 # If we are going to convert non-ASCII characters to XML entities,
248 # we had better be dealing with a UTF8 string to begin with
249 $xml = decode_utf8($xml);
253 # Convert raw ampersands to entities
254 $xml =~ s/&(?!\S+;)/&/gso;
256 # Convert Unicode characters to entities
257 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
259 $xml =~ s/[\x00-\x1f]//go;
260 $_TD->{new}{marc} = $xml;
263 $func$ LANGUAGE PLPERLU;
266 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0726', :eg_version); -- denials
268 CREATE OR REPLACE FUNCTION evergreen.maintain_901 () RETURNS TRIGGER AS $func$
271 use MARC::File::XML (BinaryEncoding => 'UTF-8');
274 use Unicode::Normalize;
276 MARC::Charset->assume_unicode(1);
278 my $schema = $_TD->{table_schema};
279 my $marc = MARC::Record->new_from_xml($_TD->{new}{marc});
281 my @old901s = $marc->field('901');
282 $marc->delete_fields(@old901s);
284 if ($schema eq 'biblio') {
285 my $tcn_value = $_TD->{new}{tcn_value};
287 # Set TCN value to record ID?
288 my $id_as_tcn = spi_exec_query("
290 FROM config.global_flag
291 WHERE name = 'cat.bib.use_id_for_tcn'
293 if (($id_as_tcn->{processed}) && $id_as_tcn->{rows}[0]->{enabled} eq 't') {
294 $tcn_value = $_TD->{new}{id};
297 my $new_901 = MARC::Field->new("901", " ", " ",
299 "b" => $_TD->{new}{tcn_source},
300 "c" => $_TD->{new}{id},
304 if ($_TD->{new}{owner}) {
305 $new_901->add_subfields("o" => $_TD->{new}{owner});
308 if ($_TD->{new}{share_depth}) {
309 $new_901->add_subfields("d" => $_TD->{new}{share_depth});
312 $marc->append_fields($new_901);
313 } elsif ($schema eq 'authority') {
314 my $new_901 = MARC::Field->new("901", " ", " ",
315 "c" => $_TD->{new}{id},
318 $marc->append_fields($new_901);
319 } elsif ($schema eq 'serial') {
320 my $new_901 = MARC::Field->new("901", " ", " ",
321 "c" => $_TD->{new}{id},
323 "o" => $_TD->{new}{owning_lib},
326 if ($_TD->{new}{record}) {
327 $new_901->add_subfields("r" => $_TD->{new}{record});
330 $marc->append_fields($new_901);
332 my $new_901 = MARC::Field->new("901", " ", " ",
333 "c" => $_TD->{new}{id},
336 $marc->append_fields($new_901);
339 my $xml = $marc->as_xml_record();
341 $xml =~ s/^<\?xml.+\?\s*>//go;
342 $xml =~ s/>\s+</></go;
343 $xml =~ s/\p{Cc}//go;
345 # Embed a version of OpenILS::Application::AppUtils->entityize()
346 # to avoid having to set PERL5LIB for PostgreSQL as well
348 # If we are going to convert non-ASCII characters to XML entities,
349 # we had better be dealing with a UTF8 string to begin with
350 $xml = decode_utf8($xml);
354 # Convert raw ampersands to entities
355 $xml =~ s/&(?!\S+;)/&/gso;
357 # Convert Unicode characters to entities
358 $xml =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
360 $xml =~ s/[\x00-\x1f]//go;
361 $_TD->{new}{marc} = $xml;
364 $func$ LANGUAGE PLPERLU;