3 INSERT INTO config.upgrade_log (version) VALUES ('2.0.9');
6 -- Fix LP#825303 by allowing for ancestor OUs to be checked
7 -- when retrieving the default classification scheme.
9 INSERT INTO config.upgrade_log (version) VALUES ('0600');
11 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
15 sortkey := NEW.label_sortkey;
17 IF NEW.label_class IS NULL THEN
18 NEW.label_class := COALESCE(
20 SELECT substring(value from E'\\d+')::integer
21 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
26 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
27 quote_literal( NEW.label ) || ')'
28 FROM asset.call_number_class acnc
29 WHERE acnc.id = NEW.label_class
31 NEW.label_sortkey = sortkey;
34 $func$ LANGUAGE PLPGSQL;
37 -- Correct actor.org_unit_ancestor_setting so that it returns
38 -- at most one setting value, rather than the entire set
39 -- of values defined for the OU and its ancestors.
41 INSERT INTO config.upgrade_log (version) VALUES ('0601');
43 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
50 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
55 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
56 EXIT WHEN cur_org IS NULL;
60 $$ LANGUAGE plpgsql STABLE ROWS 1;
63 INSERT INTO config.upgrade_log (version) VALUES ('0602'); -- dbs via miker
65 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$
70 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;
72 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
77 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
81 actor.org_unit_descendants(ans.id) d
82 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
83 JOIN asset.copy cp ON (cp.id = av.id)
84 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
88 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
98 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$
103 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;
105 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
110 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
114 actor.org_unit_descendants(ans.id) d
115 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
116 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
117 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
121 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
128 $f$ LANGUAGE PLPGSQL;
130 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$
135 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;
137 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
142 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
146 actor.org_unit_descendants(ans.id) d
147 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
148 JOIN asset.copy cp ON (cp.id = av.id)
152 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
159 $f$ LANGUAGE PLPGSQL;
161 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$
166 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;
168 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
173 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
177 actor.org_unit_descendants(ans.id) d
178 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
179 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
183 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
190 $f$ LANGUAGE PLPGSQL;
192 -- Correct the fact that actor.org_unit_parent_protect() may not work
193 -- due to 'IF' conditions in PL/pgSQL not necessarily processing in the
196 INSERT INTO config.upgrade_log (version) VALUES ('0605'); --dbwells via dbs
198 CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
200 current_aou actor.org_unit%ROWTYPE;
206 seen_ous := ARRAY[NEW.id];
208 IF (TG_OP = 'UPDATE') THEN
209 IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
210 RETURN NEW; -- Doing an UPDATE with no change, just return it
215 IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
216 RETURN NEW; -- No loop. Carry on.
218 IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
219 RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
222 SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
223 seen_ous := seen_ous || current_aou.id;
224 depth_count := depth_count + 1;
225 IF depth_count = 100 THEN
226 RAISE 'OU CHECK TOO DEEP';
235 INSERT INTO config.upgrade_log (version) VALUES ('0607');
237 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$
238 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
241 SELECT ou.parent_ou, ouad.distance+1
242 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
243 WHERE ou.parent_ou IS NOT NULL
245 SELECT ou.* FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
246 $$ LANGUAGE SQL ROWS 1;