3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.3');
4 INSERT INTO config.upgrade_log (version) VALUES ('0490'); -- miker
6 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$
11 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;
13 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
18 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
22 actor.org_unit_descendants(ans.id) d
23 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
24 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
28 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
37 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$
42 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;
44 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
49 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
53 actor.org_unit_descendants(ans.id) d
54 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
55 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
59 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
68 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$
73 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;
75 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
80 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
84 actor.org_unit_descendants(ans.id) d
85 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
86 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
87 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
91 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
100 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$
105 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;
107 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
112 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
116 actor.org_unit_descendants(ans.id) d
117 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
118 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
119 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
123 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
130 $f$ LANGUAGE PLPGSQL;
132 INSERT INTO config.upgrade_log (version) VALUES ('0491'); -- dbwells
134 CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
136 use_id_for_tcn BOOLEAN;
138 -- Remove any existing 901 fields before we insert the authoritative one
139 NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield[^>]*?tag="901".+?</datafield>', '', 'g');
141 IF TG_TABLE_SCHEMA = 'biblio' THEN
142 -- Set TCN value to record ID?
143 SELECT enabled FROM config.global_flag INTO use_id_for_tcn
144 WHERE name = 'cat.bib.use_id_for_tcn';
146 IF use_id_for_tcn = 't' THEN
147 NEW.tcn_value := NEW.id;
150 NEW.marc := REGEXP_REPLACE(
152 E'(</(?:[^:]*?:)?record>)',
153 E'<datafield tag="901" ind1=" " ind2=" ">' ||
154 '<subfield code="a">' || NEW.tcn_value || E'</subfield>' ||
155 '<subfield code="b">' || NEW.tcn_source || E'</subfield>' ||
156 '<subfield code="c">' || NEW.id || E'</subfield>' ||
157 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
158 CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
159 CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
162 ELSIF TG_TABLE_SCHEMA = 'authority' THEN
163 NEW.marc := REGEXP_REPLACE(
165 E'(</(?:[^:]*?:)?record>)',
166 E'<datafield tag="901" ind1=" " ind2=" ">' ||
167 '<subfield code="c">' || NEW.id || E'</subfield>' ||
168 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
171 ELSIF TG_TABLE_SCHEMA = 'serial' THEN
172 NEW.marc := REGEXP_REPLACE(
174 E'(</(?:[^:]*?:)?record>)',
175 E'<datafield tag="901" ind1=" " ind2=" ">' ||
176 '<subfield code="c">' || NEW.id || E'</subfield>' ||
177 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
178 '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
179 CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
183 NEW.marc := REGEXP_REPLACE(
185 E'(</(?:[^:]*?:)?record>)',
186 E'<datafield tag="901" ind1=" " ind2=" ">' ||
187 '<subfield code="c">' || NEW.id || E'</subfield>' ||
188 '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
195 $func$ LANGUAGE PLPGSQL;
197 INSERT INTO config.upgrade_log (version) VALUES ('0492'); --miker
198 UPDATE asset.call_number SET id = id;
200 INSERT INTO config.upgrade_log (version) VALUES ('0494'); -- dbs
202 UPDATE config.metabib_field
203 SET xpath = $$//mods32:mods/mods32:subject$$
204 WHERE field_class = 'subject' AND name = 'complete';
206 UPDATE config.metabib_field
207 SET xpath = $$//marc:datafield[@tag='099']$$
208 WHERE field_class = 'identifier' AND name = 'bibcn';
210 INSERT INTO config.upgrade_log (version) VALUES ('0496'); -- dbs
212 UPDATE config.metabib_field
213 SET xpath = $$//marc:datafield[@tag='024' and @ind1='1']/marc:subfield[@code='a' or @code='z']$$
214 WHERE field_class = 'identifier' AND name = 'upc';
216 UPDATE config.metabib_field
217 SET xpath = $$//marc:datafield[@tag='024' and @ind1='2']/marc:subfield[@code='a' or @code='z']$$
218 WHERE field_class = 'identifier' AND name = 'ismn';
220 UPDATE config.metabib_field
221 SET xpath = $$//marc:datafield[@tag='024' and @ind1='3']/marc:subfield[@code='a' or @code='z']$$
222 WHERE field_class = 'identifier' AND name = 'ean';
224 UPDATE config.metabib_field
225 SET xpath = $$//marc:datafield[@tag='024' and @ind1='0']/marc:subfield[@code='a' or @code='z']$$
226 WHERE field_class = 'identifier' AND name = 'isrc';
228 UPDATE config.metabib_field
229 SET xpath = $$//marc:datafield[@tag='024' and @ind1='4']/marc:subfield[@code='a' or @code='z']$$
230 WHERE field_class = 'identifier' AND name = 'sici';