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_ancestors($1)
212 FROM actor.org_unit_descendants($1);
213 $$ LANGUAGE SQL STABLE ROWS 1;
215 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
216 SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
217 $$ LANGUAGE SQL STABLE ROWS 1;
219 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
221 FROM actor.org_unit_ancestors($1)
224 FROM actor.org_unit_ancestors($2);
225 $$ LANGUAGE SQL STABLE ROWS 1;
227 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
229 FROM actor.org_unit_ancestors($1)
232 FROM actor.org_unit_ancestors($2);
233 $$ LANGUAGE SQL STABLE ROWS 1;
235 -- Given the IDs of two rows in actor.org_unit, *the second being an ancestor
236 -- of the first*, return in array form the path from the ancestor to the
237 -- descendant, with each point in the path being an org_unit ID. This is
238 -- useful for sorting org_units by their position in a depth-first (display
239 -- order) representation of the tree.
241 -- This breaks with the precedent set by actor.org_unit_full_path() and others,
242 -- and gets the parameters "backwards," but otherwise this function would
243 -- not be very usable within json_query.
244 CREATE OR REPLACE FUNCTION actor.org_unit_simple_path(INT, INT)
246 WITH RECURSIVE descendant_depth(id, path) AS (
249 FROM actor.org_unit aou
250 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
254 dd.path || ARRAY[aou.id]
255 FROM actor.org_unit aou
256 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
257 JOIN descendant_depth dd ON (dd.id = aou.parent_ou)
259 FROM actor.org_unit aou
260 JOIN descendant_depth dd USING (id)
261 WHERE aou.id = $1 ORDER BY dd.path;
262 $$ LANGUAGE SQL STABLE;
264 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
265 SELECT COUNT(id)::INT FROM (
266 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
268 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
270 $$ LANGUAGE SQL STABLE;
272 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
279 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
284 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
285 EXIT WHEN cur_org IS NULL;
289 $$ LANGUAGE plpgsql STABLE ROWS 1;
291 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
292 Search "up" the org_unit tree until we find the first occurrence of an
293 org_unit_setting with the given name.
296 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting_batch( org_id INT, setting_names TEXT[] ) RETURNS SETOF actor.org_unit_setting AS $$
302 FOREACH setting_name IN ARRAY setting_names
306 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
311 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
312 EXIT WHEN cur_org IS NULL;
317 $$ LANGUAGE plpgsql STABLE;
319 COMMENT ON FUNCTION actor.org_unit_ancestor_setting_batch( INT, TEXT[] ) IS $$
320 For each setting name passed, search "up" the org_unit tree until
321 we find the first occurrence of an org_unit_setting with the given name.
324 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting_batch_by_org(
325 setting_name TEXT, org_ids INTEGER[])
326 RETURNS SETOF actor.org_unit_setting AS
332 /* Returns one actor.org_unit_setting row per org unit ID provided.
333 When no setting exists for a given org unit, the setting row
334 will contain all empty values. */
335 FOREACH org_id IN ARRAY org_ids LOOP
336 SELECT INTO setting * FROM
337 actor.org_unit_ancestor_setting(setting_name, org_id);
342 $FUNK$ LANGUAGE plpgsql STABLE;
344 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
349 asset_barcodes TEXT[];
350 actor_barcodes TEXT[];
351 do_asset BOOL = false;
352 do_serial BOOL = false;
353 do_booking BOOL = false;
354 do_actor BOOL = false;
355 completion_set config.barcode_completion%ROWTYPE;
358 IF position('asset' in type) > 0 THEN
361 IF position('serial' in type) > 0 THEN
364 IF position('booking' in type) > 0 THEN
367 IF do_asset OR do_serial OR do_booking THEN
368 asset_barcodes = asset_barcodes || in_barcode;
370 IF position('actor' in type) > 0 THEN
372 actor_barcodes = actor_barcodes || in_barcode;
375 barcode_len := length(in_barcode);
377 FOR completion_set IN
378 SELECT * FROM config.barcode_completion
380 AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
382 IF completion_set.prefix IS NULL THEN
383 completion_set.prefix := '';
385 IF completion_set.suffix IS NULL THEN
386 completion_set.suffix := '';
388 IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
389 cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
391 completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
392 IF completion_len >= barcode_len THEN
393 IF completion_set.padding_end THEN
394 cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
396 cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
398 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
401 IF completion_set.actor THEN
402 actor_barcodes = actor_barcodes || cur_barcode;
404 IF completion_set.asset THEN
405 asset_barcodes = asset_barcodes || cur_barcode;
409 IF do_asset AND do_serial THEN
410 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
411 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
413 RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
415 RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
418 RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
421 RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE
422 ((c.barcode = ANY(actor_barcodes) AND c.active) OR c.barcode = in_barcode) AND NOT u.deleted ORDER BY usr;
428 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
429 Given user input, find an appropriate barcode in the proper class.
431 Will add prefix/suffix information to do so, and return all results.
434 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
438 IF TG_OP = 'DELETE' THEN
440 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
444 IF TG_OP = 'UPDATE' THEN
446 IF NEW.parent_ou <> OLD.parent_ou THEN
448 DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
449 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
450 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
451 FROM actor.org_unit l, actor.org_unit r
452 WHERE (l.id = NEW.id or r.id = NEW.id);
458 IF TG_OP = 'INSERT' THEN
460 INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
461 SELECT l.id, r.id, actor.org_unit_proximity(l.id,r.id)
462 FROM actor.org_unit l, actor.org_unit r
463 WHERE (l.id = NEW.id or r.id = NEW.id);
473 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 ();
475 CREATE OR REPLACE FUNCTION evergreen.get_locale_name(
483 eg_locale := LOWER(SUBSTRING(locale FROM 1 FOR 2)) || '-' || UPPER(SUBSTRING(locale FROM 4 FOR 2));
485 SELECT i18nc.string INTO name
486 FROM config.i18n_locale i18nl
487 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
488 WHERE i18nc.identity_value = eg_locale
490 AND i18nc.fq_field = 'i18n_l.name';
493 SELECT i18nl.name INTO name
494 FROM config.i18n_locale i18nl
495 WHERE code = eg_locale;
498 SELECT i18nc.string INTO description
499 FROM config.i18n_locale i18nl
500 INNER JOIN config.i18n_core i18nc ON i18nl.code = i18nc.translation
501 WHERE i18nc.identity_value = eg_locale
503 AND i18nc.fq_field = 'i18n_l.description';
505 IF description IS NULL THEN
506 SELECT i18nl.description INTO description
507 FROM config.i18n_locale i18nl
508 WHERE code = eg_locale;
511 $$ LANGUAGE PLPGSQL COST 1 STABLE;