3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.2');
4 INSERT INTO config.upgrade_log (version) VALUES ('0484'); -- miker
6 DROP FUNCTION asset.metarecord_copy_count ( INT, BIGINT, BOOL );
7 DROP FUNCTION asset.record_copy_count ( INT, BIGINT, BOOL );
9 DROP FUNCTION asset.opac_ou_record_copy_count (INT, BIGINT);
10 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
15 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
17 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
22 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
26 actor.org_unit_descendants(ans.id) d
27 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
28 JOIN asset.copy cp ON (cp.id = av.id)
32 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
41 DROP FUNCTION asset.opac_lasso_record_copy_count (INT, BIGINT);
42 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
47 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
49 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
54 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
58 actor.org_unit_descendants(ans.id) d
59 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
60 JOIN asset.copy cp ON (cp.id = av.id)
64 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
73 DROP FUNCTION asset.staff_ou_record_copy_count (INT, BIGINT);
74 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
79 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
81 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
86 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
90 actor.org_unit_descendants(ans.id) d
91 JOIN asset.copy cp ON (cp.circ_lib = d.id)
92 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
96 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
103 $f$ LANGUAGE PLPGSQL;
105 DROP FUNCTION asset.staff_lasso_record_copy_count (INT, BIGINT);
106 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
111 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
113 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
118 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
122 actor.org_unit_descendants(ans.id) d
123 JOIN asset.copy cp ON (cp.circ_lib = d.id)
124 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
128 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
135 $f$ LANGUAGE PLPGSQL;
137 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
139 IF staff IS TRUE THEN
141 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
143 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
147 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
149 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
155 $f$ LANGUAGE PLPGSQL;
157 DROP FUNCTION asset.opac_ou_metarecord_copy_count (INT, BIGINT);
158 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
163 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
165 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
170 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
174 actor.org_unit_descendants(ans.id) d
175 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
176 JOIN asset.copy cp ON (cp.id = av.id)
177 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
181 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
188 $f$ LANGUAGE PLPGSQL;
190 DROP FUNCTION asset.opac_lasso_metarecord_copy_count (INT, BIGINT);
191 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
196 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
198 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
203 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
207 actor.org_unit_descendants(ans.id) d
208 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
209 JOIN asset.copy cp ON (cp.id = av.id)
210 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
214 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
221 $f$ LANGUAGE PLPGSQL;
223 DROP FUNCTION asset.staff_ou_metarecord_copy_count (INT, BIGINT);
224 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
229 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
231 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
236 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
240 actor.org_unit_descendants(ans.id) d
241 JOIN asset.copy cp ON (cp.circ_lib = d.id)
242 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
243 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
247 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
254 $f$ LANGUAGE PLPGSQL;
256 DROP FUNCTION asset.staff_lasso_metarecord_copy_count (INT, BIGINT);
257 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
262 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
264 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
269 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
273 actor.org_unit_descendants(ans.id) d
274 JOIN asset.copy cp ON (cp.circ_lib = d.id)
275 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number)
276 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
280 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
287 $f$ LANGUAGE PLPGSQL;
289 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
291 IF staff IS TRUE THEN
293 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
295 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
299 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
301 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
307 $f$ LANGUAGE PLPGSQL;
309 INSERT INTO config.upgrade_log (version) VALUES ('0485'); -- dbs
311 CREATE OR REPLACE VIEW reporter.simple_record AS
318 title.value AS title,
319 uniform_title.value AS uniform_title,
320 author.value AS author,
321 publisher.value AS publisher,
322 SUBSTRING(pubdate.value FROM $$\d+$$) AS pubdate,
323 series_title.value AS series_title,
324 series_statement.value AS series_statement,
325 summary.value AS summary,
326 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
327 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn,
328 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject,
329 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject,
330 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre,
331 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '600' AND subfield = 'a' AND record = r.id)) AS name_subject,
332 ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '610' AND subfield = 'a' AND record = r.id)) AS corporate_subject,
333 ARRAY((SELECT value FROM metabib.full_rec WHERE tag = '856' AND subfield IN ('3','y','u') AND record = r.id ORDER BY CASE WHEN subfield IN ('3','y') THEN 0 ELSE 1 END)) AS external_uri
334 FROM biblio.record_entry r
335 JOIN metabib.metarecord_source_map s ON (s.source = r.id)
336 LEFT JOIN metabib.full_rec uniform_title ON (r.id = uniform_title.record AND uniform_title.tag = '240' AND uniform_title.subfield = 'a')
337 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
338 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag = '100' AND author.subfield = 'a')
339 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
340 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
341 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
342 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
343 LEFT JOIN metabib.full_rec series_title ON (r.id = series_title.record AND series_title.tag IN ('830','440') AND series_title.subfield = 'a')
344 LEFT JOIN metabib.full_rec series_statement ON (r.id = series_statement.record AND series_statement.tag = '490' AND series_statement.subfield = 'a')
345 LEFT JOIN metabib.full_rec summary ON (r.id = summary.record AND summary.tag = '520' AND summary.subfield = 'a')
346 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
348 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
354 FIRST(title.value) AS title,
355 FIRST(author.value) AS author,
356 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher,
357 ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate,
358 ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn,
359 ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn
360 FROM biblio.record_entry r
361 LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a')
362 LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a')
363 LEFT JOIN metabib.full_rec publisher ON (r.id = publisher.record AND publisher.tag = '260' AND publisher.subfield = 'b')
364 LEFT JOIN metabib.full_rec pubdate ON (r.id = pubdate.record AND pubdate.tag = '260' AND pubdate.subfield = 'c')
365 LEFT JOIN metabib.full_rec isbn ON (r.id = isbn.record AND isbn.tag IN ('024', '020') AND isbn.subfield IN ('a','z'))
366 LEFT JOIN metabib.full_rec issn ON (r.id = issn.record AND issn.tag = '022' AND issn.subfield = 'a')
369 -- Update reporter.materialized_simple_record with normalized ISBN values
370 -- This might not get all of them, but most ISBNs will have more than one hyphen
371 DELETE FROM reporter.materialized_simple_record WHERE id IN (
372 SELECT record FROM metabib.full_rec WHERE tag = '020' AND subfield IN ('a', 'z') AND value LIKE '%-%-%'
375 INSERT INTO reporter.materialized_simple_record
376 SELECT DISTINCT rossr.* FROM reporter.old_super_simple_record rossr INNER JOIN metabib.full_rec mfr ON mfr.record = rossr.id
377 WHERE mfr.tag = '020' AND mfr.subfield IN ('a', 'z') AND mfr.value LIKE '%-%-%'
380 INSERT INTO config.upgrade_log (version) VALUES ('0488'); -- dbs
382 CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
384 use_id_for_tcn BOOLEAN;
386 -- Remove any existing 901 fields before we insert the authoritative one
387 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield\s*[^<>]*?\s*tag="901".+?</datafield>', '', 'g');
389 IF TG_TABLE_SCHEMA = 'biblio' THEN
390 -- Set TCN value to record ID?
391 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
392 WHERE name = 'cat.bib.use_id_for_tcn';
394 IF use_id_for_tcn = 't' THEN
395 NEW.tcn_value := NEW.id;
398 NEW.marc := REGEXP_REPLACE(
400 E'(</(?:[^:]*?:)?record>)',
401 E'<datafield tag="901" ind1=" " ind2=" ">' ||
402 '<subfield code="a">' || NEW.tcn_value || E'</subfield>' ||
403 '<subfield code="b">' || NEW.tcn_source || E'</subfield>' ||
404 '<subfield code="c">' || NEW.id || E'</subfield>' ||
405 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
406 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
407 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
410 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
411 NEW.marc := REGEXP_REPLACE(
413 E'(</(?:[^:]*?:)?record>)',
414 E'<datafield tag="901" ind1=" " ind2=" ">' ||
415 '<subfield code="c">' || NEW.id || E'</subfield>' ||
416 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
419 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
420 NEW.marc := REGEXP_REPLACE(
422 E'(</(?:[^:]*?:)?record>)',
423 E'<datafield tag="901" ind1=" " ind2=" ">' ||
424 '<subfield code="c">' || NEW.id || E'</subfield>' ||
425 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
426 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
427 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
431 NEW.marc := REGEXP_REPLACE(
433 E'(</(?:[^:]*?:)?record>)',
434 E'<datafield tag="901" ind1=" " ind2=" ">' ||
435 '<subfield code="c">' || NEW.id || E'</subfield>' ||
436 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
443 $func$ LANGUAGE PLPGSQL;