2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
30 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
31 ELSE $2::TEXT::INT + 1
34 $$ LANGUAGE SQL STRICT IMMUTABLE;
36 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
37 SELECT COALESCE(SUBSTRING( $1 FROM $_$^\S+$_$), '');
38 $$ LANGUAGE SQL STRICT IMMUTABLE;
40 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
41 SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
42 $$ LANGUAGE SQL STRICT IMMUTABLE;
44 CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
45 SELECT regexp_replace($1, ',', '', 'g');
46 $$ LANGUAGE SQL STRICT IMMUTABLE;
48 CREATE OR REPLACE FUNCTION public.remove_paren_substring( TEXT ) RETURNS TEXT AS $func$
49 SELECT regexp_replace($1, $$\([^)]+\)$$, '', 'g');
50 $func$ LANGUAGE SQL STRICT IMMUTABLE;
52 CREATE OR REPLACE FUNCTION public.remove_whitespace( TEXT ) RETURNS TEXT AS $$
53 SELECT regexp_replace(normalize_space($1), E'\\s+', '', 'g');
54 $$ LANGUAGE SQL STRICT IMMUTABLE;
56 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
58 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
60 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
62 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
64 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
65 use Unicode::Normalize;
71 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
73 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
74 use Unicode::Normalize;
77 $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
80 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
82 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
85 $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
87 if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
90 return (split /\s+/, $txt)[0];
92 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
94 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
95 SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
96 $$ LANGUAGE SQL STRICT IMMUTABLE;
98 CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
102 $$ language 'plpgsql';
104 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$
105 WITH RECURSIVE descendant_depth AS (
109 FROM actor.org_unit ou
110 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
111 JOIN anscestor_depth ad ON (ad.id = ou.id)
117 FROM actor.org_unit ou
118 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
119 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
120 ), anscestor_depth AS (
124 FROM actor.org_unit ou
125 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
131 FROM actor.org_unit ou
132 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
133 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
134 ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
135 $$ LANGUAGE SQL ROWS 1;
137 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$
138 WITH RECURSIVE descendant_depth AS (
142 FROM actor.org_unit ou
143 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
149 FROM actor.org_unit ou
150 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
151 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
152 ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id);
153 $$ LANGUAGE SQL ROWS 1;
155 CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
156 WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
159 SELECT ou.id, oudd.distance+1
160 FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id)
162 SELECT * FROM org_unit_descendants_distance;
163 $$ LANGUAGE SQL STABLE ROWS 1;
165 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$
166 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
169 SELECT ou.parent_ou, ouad.distance+1
170 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
171 WHERE ou.parent_ou IS NOT NULL
173 SELECT ou.* FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
174 $$ LANGUAGE SQL ROWS 1;
176 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
178 FROM actor.org_unit a
179 WHERE id = ( SELECT FIRST(x.id)
180 FROM actor.org_unit_ancestors($1) x
181 JOIN actor.org_unit_type y
182 ON x.ou_type = y.id AND y.depth = $2);
183 $$ LANGUAGE SQL STABLE;
185 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
186 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
189 SELECT ou.parent_ou, ouad.distance+1
190 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
191 WHERE ou.parent_ou IS NOT NULL
193 SELECT * FROM org_unit_ancestors_distance;
194 $$ LANGUAGE SQL STABLE ROWS 1;
196 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
198 FROM actor.org_unit_ancestors($1)
201 FROM actor.org_unit_descendants($1);
202 $$ LANGUAGE SQL STABLE ROWS 1;
204 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
205 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
206 $$ LANGUAGE SQL STABLE ROWS 1;
208 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
210 FROM actor.org_unit_ancestors($1)
213 FROM actor.org_unit_ancestors($2);
214 $$ LANGUAGE SQL STABLE ROWS 1;
216 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
218 FROM actor.org_unit_ancestors($1)
221 FROM actor.org_unit_ancestors($2);
222 $$ LANGUAGE SQL STABLE ROWS 1;
224 -- Given the IDs of two rows in actor.org_unit, *the second being an ancestor
225 -- of the first*, return in array form the path from the ancestor to the
226 -- descendant, with each point in the path being an org_unit ID. This is
227 -- useful for sorting org_units by their position in a depth-first (display
228 -- order) representation of the tree.
230 -- This breaks with the precedent set by actor.org_unit_full_path() and others,
231 -- and gets the parameters "backwards," but otherwise this function would
232 -- not be very usable within json_query.
233 CREATE OR REPLACE FUNCTION actor.org_unit_simple_path(INT, INT)
235 WITH RECURSIVE descendant_depth(id, path) AS (
238 FROM actor.org_unit aou
239 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
243 dd.path || ARRAY[aou.id]
244 FROM actor.org_unit aou
245 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
246 JOIN descendant_depth dd ON (dd.id = aou.parent_ou)
248 FROM actor.org_unit aou
249 JOIN descendant_depth dd USING (id)
250 WHERE aou.id = $1 ORDER BY dd.path;
251 $$ LANGUAGE SQL STABLE;
253 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
254 SELECT COUNT(id)::INT FROM (
255 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
257 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
259 $$ LANGUAGE SQL STABLE;
261 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
268 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
273 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
274 EXIT WHEN cur_org IS NULL;
278 $$ LANGUAGE plpgsql STABLE ROWS 1;
280 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
281 Search "up" the org_unit tree until we find the first occurrence of an
282 org_unit_setting with the given name.
285 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
290 asset_barcodes TEXT[];
291 actor_barcodes TEXT[];
292 do_asset BOOL = false;
293 do_serial BOOL = false;
294 do_booking BOOL = false;
295 do_actor BOOL = false;
296 completion_set config.barcode_completion%ROWTYPE;
299 IF position('asset' in type) > 0 THEN
302 IF position('serial' in type) > 0 THEN
305 IF position('booking' in type) > 0 THEN
308 IF do_asset OR do_serial OR do_booking THEN
309 asset_barcodes = asset_barcodes || in_barcode;
311 IF position('actor' in type) > 0 THEN
313 actor_barcodes = actor_barcodes || in_barcode;
316 barcode_len := length(in_barcode);
318 FOR completion_set IN
319 SELECT * FROM config.barcode_completion
321 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
323 IF completion_set.prefix IS NULL THEN
324 completion_set.prefix := '';
326 IF completion_set.suffix IS NULL THEN
327 completion_set.suffix := '';
329 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
330 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
332 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
333 IF completion_len >= barcode_len THEN
334 IF completion_set.padding_end THEN
335 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
337 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
339 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
342 IF completion_set.actor THEN
343 actor_barcodes = actor_barcodes || cur_barcode;
345 IF completion_set.asset THEN
346 asset_barcodes = asset_barcodes || cur_barcode;
350 IF do_asset AND do_serial THEN
351 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
352 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
354 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
356 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
359 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
362 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr;
368 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
369 Given user input, find an appropriate barcode in the proper class.
371 Will add prefix/suffix information to do so, and return all results.
374 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
378 IF TG_OP = 'DELETE' THEN
380 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
384 IF TG_OP = 'UPDATE' THEN
386 IF NEW.parent_ou <> OLD.parent_ou THEN
388 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
389 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
390 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
391 FROM actor.org_unit l, actor.org_unit r
392 WHERE (l.id = NEW.id or r.id = NEW.id);
398 IF TG_OP = 'INSERT' THEN
400 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
401 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
402 FROM actor.org_unit l, actor.org_unit r
403 WHERE (l.id = NEW.id or r.id = NEW.id);
413 CREATE TRIGGER proximity_update_tgr AFTER INSERT OR UPDATE OR DELETE ON actor.org_unit FOR EACH ROW EXECUTE PROCEDURE actor.org_unit_prox_update ();