1 --Upgrade Script for 2.1.1 to 2.1.2
3 INSERT INTO config.upgrade_log (version) VALUES ('2.1.2');
5 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
12 uri_owner_list TEXT[];
20 -- Clear any URI mappings and call numbers for this bib.
21 -- This leads to acn / auricnm inflation, but also enables
22 -- old acn/auricnm's to go away and for bibs to be deleted.
23 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
24 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
25 DELETE FROM asset.call_number WHERE id = uri_cn_id;
28 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
29 IF ARRAY_UPPER(uris,1) > 0 THEN
30 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
31 -- First we pull info out of the 856
34 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
35 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
36 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
38 IF uri_label IS NULL THEN
39 uri_label := uri_href;
41 CONTINUE WHEN uri_href IS NULL;
43 -- Get the distinct list of libraries wanting to use
45 DISTINCT REGEXP_REPLACE(
47 $re$^.*?\((\w+)\).*$$re$,
53 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
58 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
60 -- look for a matching uri
61 IF uri_use IS NULL THEN
64 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
66 IF NOT FOUND THEN -- create one
67 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
70 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
75 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
77 IF NOT FOUND THEN -- create one
78 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
81 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
85 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
86 uri_owner := uri_owner_list[j];
88 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
89 CONTINUE WHEN NOT FOUND;
91 -- we need a call number to link through
92 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;
94 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
95 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
96 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;
99 -- now, link them if they're not already
100 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
102 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
114 $func$ LANGUAGE PLPGSQL;
117 INSERT INTO config.upgrade_log (version) VALUES ('0658');
119 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
120 # Derived from the Koha C4::ClassSortRoutine::Dewey module
121 # Copyright (C) 2007 LibLime
122 # Licensed under the GPL v2 or later
127 my $init = uc(shift);
131 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
132 my @tokens = split /\.|\s+/, $init;
133 my $digit_group_count = 0;
134 for (my $i = 0; $i <= $#tokens; $i++) {
135 if ($tokens[$i] =~ /^\d+$/) {
136 $digit_group_count++;
137 if (2 == $digit_group_count) {
138 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
139 $tokens[$i] =~ tr/ /0/;
143 # Pad the first digit_group if there was only one
144 if (1 == $digit_group_count) {
145 $tokens[0] .= '_000000000000000'
147 my $key = join("_", @tokens);
148 $key =~ s/[^\p{IsAlnum}_]//g;
152 $func$ LANGUAGE PLPERLU;
154 INSERT INTO config.upgrade_log (version) VALUES ('0693');
156 -- Delete the index normalizer that was meant to remove spaces from ISSNs
157 -- but ended up breaking records with multiple ISSNs
158 DELETE FROM config.metabib_field_index_norm_map WHERE id IN (
159 SELECT map.id FROM config.metabib_field_index_norm_map map
160 INNER JOIN config.metabib_field cmf ON cmf.id = map.field
161 INNER JOIN config.index_normalizer cin ON cin.id = map.norm
162 WHERE cin.func = 'replace'
163 AND cmf.field_class = 'identifier'
164 AND cmf.name = 'issn'
165 AND map.params = $$[" ",""]$$
170 \qecho We will attempt to create indexes that may have already been
171 \qecho created if you upgraded to 2.0.11. You might see failures
173 -- Placeholder for backported fix
174 INSERT INTO config.upgrade_log (version) VALUES ('0691');
176 CREATE INDEX poi_po_idx ON acq.po_item (purchase_order);
178 CREATE INDEX ie_inv_idx on acq.invoice_entry (invoice);
179 CREATE INDEX ie_po_idx on acq.invoice_entry (purchase_order);
180 CREATE INDEX ie_li_idx on acq.invoice_entry (lineitem);
182 CREATE INDEX ii_inv_idx on acq.invoice_item (invoice);
183 CREATE INDEX ii_po_idx on acq.invoice_item (purchase_order);
184 CREATE INDEX ii_poi_idx on acq.invoice_item (po_item);
186 \qecho Finished schema updates; now updating the indexes for
187 \qecho Dewey call numbers and ISSNs
189 -- regenerate sort keys for any dewey call numbers
190 UPDATE asset.call_number SET id = id WHERE label_class = 2;
192 -- Reindex records that have more than just a single ISSN
193 -- to ensure that spaces are maintained
194 SELECT metabib.reingest_metabib_field_entries(source)
195 FROM metabib.identifier_field_entry mife
196 INNER JOIN config.metabib_field cmf ON cmf.id = mife.field
197 WHERE cmf.field_class = 'identifier'
198 AND cmf.name = 'issn'
199 AND char_length(value) > 9
202 -- Fix sorting by pubdate by ensuring migrated records
203 -- have a pubdate attribute in metabib.record_attr.attrs
204 UPDATE metabib.record_attr
205 SET attrs = attrs || ('pubdate' => (attrs->'date1'))
206 WHERE defined(attrs, 'pubdate') IS FALSE
207 AND defined(attrs, 'date1') IS TRUE;