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_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
197 WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
200 SELECT ou.parent_ou, ouad.distance+1
201 FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
202 WHERE ou.parent_ou IS NOT NULL
204 SELECT * FROM org_unit_ancestors_distance;
205 $$ LANGUAGE SQL STABLE ROWS 1;
207 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
209 FROM actor.org_unit AS aou
211 (SELECT au.id, t.depth FROM actor.org_unit_ancestors($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id))
213 (SELECT au.id, t.depth FROM actor.org_unit_descendants($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id))
214 ) AS ad ON (aou.id=ad.id)
216 $$ LANGUAGE SQL STABLE ROWS 1;
218 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
219 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
220 $$ LANGUAGE SQL STABLE ROWS 1;
222 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
224 FROM actor.org_unit_ancestors($1)
227 FROM actor.org_unit_ancestors($2);
228 $$ LANGUAGE SQL STABLE ROWS 1;
230 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
232 FROM actor.org_unit_ancestors($1)
235 FROM actor.org_unit_ancestors($2);
236 $$ LANGUAGE SQL STABLE ROWS 1;
238 -- Given the IDs of two rows in actor.org_unit, *the second being an ancestor
239 -- of the first*, return in array form the path from the ancestor to the
240 -- descendant, with each point in the path being an org_unit ID. This is
241 -- useful for sorting org_units by their position in a depth-first (display
242 -- order) representation of the tree.
244 -- This breaks with the precedent set by actor.org_unit_full_path() and others,
245 -- and gets the parameters "backwards," but otherwise this function would
246 -- not be very usable within json_query.
247 CREATE OR REPLACE FUNCTION actor.org_unit_simple_path(INT, INT)
249 WITH RECURSIVE descendant_depth(id, path) AS (
252 FROM actor.org_unit aou
253 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
257 dd.path || ARRAY[aou.id]
258 FROM actor.org_unit aou
259 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
260 JOIN descendant_depth dd ON (dd.id = aou.parent_ou)
262 FROM actor.org_unit aou
263 JOIN descendant_depth dd USING (id)
264 WHERE aou.id = $1 ORDER BY dd.path;
265 $$ LANGUAGE SQL STABLE;
267 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
268 SELECT COUNT(id)::INT FROM (
269 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
271 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
273 $$ LANGUAGE SQL STABLE;
275 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
282 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
287 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
288 EXIT WHEN cur_org IS NULL;
292 $$ LANGUAGE plpgsql STABLE ROWS 1;
294 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
295 Search "up" the org_unit tree until we find the first occurrence of an
296 org_unit_setting with the given name.
299 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting_batch( org_id INT, setting_names TEXT[] ) RETURNS SETOF actor.org_unit_setting AS $$
305 FOREACH setting_name IN ARRAY setting_names
309 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
314 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
315 EXIT WHEN cur_org IS NULL;
320 $$ LANGUAGE plpgsql STABLE;
322 COMMENT ON FUNCTION actor.org_unit_ancestor_setting_batch( INT, TEXT[] ) IS $$
323 For each setting name passed, search "up" the org_unit tree until
324 we find the first occurrence of an org_unit_setting with the given name.
327 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting_batch_by_org(
328 setting_name TEXT, org_ids INTEGER[])
329 RETURNS SETOF actor.org_unit_setting AS
335 /* Returns one actor.org_unit_setting row per org unit ID provided.
336 When no setting exists for a given org unit, the setting row
337 will contain all empty values. */
338 FOREACH org_id IN ARRAY org_ids LOOP
339 SELECT INTO setting * FROM
340 actor.org_unit_ancestor_setting(setting_name, org_id);
345 $FUNK$ LANGUAGE plpgsql STABLE;
347 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
352 asset_barcodes TEXT[];
353 actor_barcodes TEXT[];
354 do_asset BOOL = false;
355 do_serial BOOL = false;
356 do_booking BOOL = false;
357 do_actor BOOL = false;
358 completion_set config.barcode_completion%ROWTYPE;
361 IF position('asset' in type) > 0 THEN
364 IF position('serial' in type) > 0 THEN
367 IF position('booking' in type) > 0 THEN
370 IF do_asset OR do_serial OR do_booking THEN
371 asset_barcodes = asset_barcodes || in_barcode;
373 IF position('actor' in type) > 0 THEN
375 actor_barcodes = actor_barcodes || in_barcode;
378 barcode_len := length(in_barcode);
380 FOR completion_set IN
381 SELECT * FROM config.barcode_completion
383 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
385 IF completion_set.prefix IS NULL THEN
386 completion_set.prefix := '';
388 IF completion_set.suffix IS NULL THEN
389 completion_set.suffix := '';
391 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
392 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
394 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
395 IF completion_len >= barcode_len THEN
396 IF completion_set.padding_end THEN
397 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
399 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
401 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
404 IF completion_set.actor THEN
405 actor_barcodes = actor_barcodes || cur_barcode;
407 IF completion_set.asset THEN
408 asset_barcodes = asset_barcodes || cur_barcode;
412 IF do_asset AND do_serial THEN
413 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
414 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
416 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
418 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
421 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
424 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE
425 ((c.barcode = ANY(actor_barcodes) AND c.active) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr;
431 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
432 Given user input, find an appropriate barcode in the proper class.
434 Will add prefix/suffix information to do so, and return all results.
437 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
441 IF TG_OP = 'DELETE' THEN
443 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
447 IF TG_OP = 'UPDATE' THEN
449 IF NEW.parent_ou <> OLD.parent_ou THEN
451 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
452 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
453 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
454 FROM actor.org_unit l, actor.org_unit r
455 WHERE (l.id = NEW.id or r.id = NEW.id);
461 IF TG_OP = 'INSERT' THEN
463 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
464 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
465 FROM actor.org_unit l, actor.org_unit r
466 WHERE (l.id = NEW.id or r.id = NEW.id);
476 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 ();
478 CREATE OR REPLACE FUNCTION evergreen.get_locale_name(
486 eg_locale := LOWER(SUBSTRING(locale FROM 1 FOR 2)) || '-' || UPPER(SUBSTRING(locale FROM 4 FOR 2));
488 SELECT i18nc.string INTO name
489 FROM config.i18n_locale i18nl
490 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
491 WHERE i18nc.identity_value = eg_locale
493 AND i18nc.fq_field = 'i18n_l.name';
496 SELECT i18nl.name INTO name
497 FROM config.i18n_locale i18nl
498 WHERE code = eg_locale;
501 SELECT i18nc.string INTO description
502 FROM config.i18n_locale i18nl
503 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
504 WHERE i18nc.identity_value = eg_locale
506 AND i18nc.fq_field = 'i18n_l.description';
508 IF description IS NULL THEN
509 SELECT i18nl.description INTO description
510 FROM config.i18n_locale i18nl
511 WHERE code = eg_locale;
514 $$ LANGUAGE PLPGSQL COST 1 STABLE;