1 -- Fix sorting by pubdate by ensuring migrated records
2 -- have a pubdate attribute in metabib.record_attr.attrs
3 UPDATE metabib.record_attr
4 SET attrs = attrs || ('pubdate' => (attrs->'date1'))
5 WHERE defined(attrs, 'pubdate') IS FALSE
6 AND defined(attrs, 'date1') IS TRUE;
8 CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$
15 uri_owner_list TEXT[];
23 -- Clear any URI mappings and call numbers for this bib.
24 -- This leads to acn / auricnm inflation, but also enables
25 -- old acn/auricnm's to go away and for bibs to be deleted.
26 FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP
27 DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id;
28 DELETE FROM asset.call_number WHERE id = uri_cn_id;
31 uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml);
32 IF ARRAY_UPPER(uris,1) > 0 THEN
33 FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP
34 -- First we pull info out of the 856
37 uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
38 uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1];
39 uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
41 IF uri_label IS NULL THEN
42 uri_label := uri_href;
44 CONTINUE WHEN uri_href IS NULL;
46 -- Get the distinct list of libraries wanting to use
48 DISTINCT REGEXP_REPLACE(
50 $re$^.*?\((\w+)\).*$$re$,
56 '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',
61 IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN
63 -- look for a matching uri
64 IF uri_use IS NULL THEN
67 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active
69 IF NOT FOUND THEN -- create one
70 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
73 WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active;
78 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active
80 IF NOT FOUND THEN -- create one
81 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
84 WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
88 FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
89 uri_owner := uri_owner_list[j];
91 SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
92 CONTINUE WHEN NOT FOUND;
94 -- we need a call number to link through
95 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;
97 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
98 VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##');
99 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;
102 -- now, link them if they're not already
103 SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
105 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
117 $func$ LANGUAGE PLPGSQL;
119 INSERT INTO config.upgrade_log (version) VALUES ('0693');
121 -- Delete the index normalizer that was meant to remove spaces from ISSNs
122 -- but ended up breaking records with multiple ISSNs
123 DELETE FROM config.metabib_field_index_norm_map WHERE id IN (
124 SELECT map.id FROM config.metabib_field_index_norm_map map
125 INNER JOIN config.metabib_field cmf ON cmf.id = map.field
126 INNER JOIN config.index_normalizer cin ON cin.id = map.norm
127 WHERE cin.func = 'replace'
128 AND cmf.field_class = 'identifier'
129 AND cmf.name = 'issn'
130 AND map.params = $$[" ",""]$$
133 -- Reindex records that have more than just a single ISSN
134 -- to ensure that spaces are maintained
135 SELECT metabib.reingest_metabib_field_entries(source)
136 FROM metabib.identifier_field_entry mife
137 INNER JOIN config.metabib_field cmf ON cmf.id = mife.field
138 WHERE cmf.field_class = 'identifier'
139 AND cmf.name = 'issn'
140 AND char_length(value) > 9
143 -- Add indexes to speed up acquisitions search
144 INSERT INTO config.upgrade_log (version) VALUES ('0691');
146 CREATE INDEX poi_po_idx ON acq.po_item (purchase_order);
148 CREATE INDEX ie_inv_idx on acq.invoice_entry (invoice);
149 CREATE INDEX ie_po_idx on acq.invoice_entry (purchase_order);
150 CREATE INDEX ie_li_idx on acq.invoice_entry (lineitem);
152 CREATE INDEX ii_inv_idx on acq.invoice_item (invoice);
153 CREATE INDEX ii_po_idx on acq.invoice_item (purchase_order);
154 CREATE INDEX ii_poi_idx on acq.invoice_item (po_item);