Open-ILS/src/sql/Pg/020.schema.functions.sql

   1 /*
   2  * Copyright (C) 2004-2008  Georgia Public Library Service
   3  * Copyright (C) 2007-2008  Equinox Software, Inc.
   4  * Mike Rylander <miker@esilibrary.com>
   5  *
   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.
  10  *
  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.
  15  *
  16  */
  17
  18 CREATE OR REPLACE FUNCTION public.non_filing_normalize ( TEXT, "char" ) RETURNS TEXT AS $$
  19         SELECT  SUBSTRING(
  20                         REGEXP_REPLACE(
  21                                 REGEXP_REPLACE(
  22                                         $1,
  23                                         E'\W*$',
  24                                         ''
  25                                 ),
  26                                 '  ',
  27                                 ' '
  28                         ),
  29                         CASE
  30                                 WHEN $2::INT NOT BETWEEN 48 AND 57 THEN 1
  31                                 ELSE $2::TEXT::INT + 1
  32                         END
  33                 );
  34 $$ LANGUAGE SQL STRICT IMMUTABLE;
  35
  36 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
  37         SELECT COALESCE(SUBSTRING( $1 FROM $_$^\S+$_$), '');
  38 $$ LANGUAGE SQL STRICT IMMUTABLE;
  39
  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;
  43
  44 CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
  45     SELECT regexp_replace($1, ',', '', 'g');
  46 $$ LANGUAGE SQL STRICT IMMUTABLE;
  47
  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;
  51
  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;
  55
  56 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
  57     return lc(shift);
  58 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  59
  60 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
  61     return uc(shift);
  62 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  63
  64 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
  65     use Unicode::Normalize;
  66
  67     my $x = NFD(shift);
  68     $x =~ s/\pM+//go;
  69     return $x;
  70
  71 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  72
  73 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
  74     use Unicode::Normalize;
  75
  76     my $x = NFC(shift);
  77     $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
  78     return $x;
  79
  80 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  81
  82 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
  83         my $txt = shift;
  84         $txt =~ s/^\s+//o;
  85         $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
  86         $txt =~ s/\s+$//o;
  87         if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
  88                 return $1;
  89         } else {
  90                 return (split /\s+/, $txt)[0];
  91         }
  92 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
  93
  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;
  97
  98 CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
  99         BEGIN
 100                 RETURN $1::regclass;
 101         END;
 102 $$ language 'plpgsql';
 103
 104 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 105     WITH RECURSIVE descendant_depth AS (
 106         SELECT  ou.id,
 107                 ou.parent_ou,
 108                 out.depth
 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)
 112           WHERE ad.depth = $2
 113             UNION ALL
 114         SELECT  ou.id,
 115                 ou.parent_ou,
 116                 out.depth
 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 (
 121         SELECT  ou.id,
 122                 ou.parent_ou,
 123                 out.depth
 124           FROM  actor.org_unit ou
 125                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
 126           WHERE ou.id = $1
 127             UNION ALL
 128         SELECT  ou.id,
 129                 ou.parent_ou,
 130                 out.depth
 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;
 136
 137 CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$
 138     WITH RECURSIVE descendant_depth AS (
 139         SELECT  ou.id,
 140                 ou.parent_ou,
 141                 out.depth
 142           FROM  actor.org_unit ou
 143                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
 144           WHERE ou.id = $1
 145             UNION ALL
 146         SELECT  ou.id,
 147                 ou.parent_ou,
 148                 out.depth
 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;
 154
 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 (
 157             SELECT $1, 0
 158         UNION
 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)
 161     )
 162     SELECT * FROM org_unit_descendants_distance;
 163 $$ LANGUAGE SQL STABLE ROWS 1;
 164
 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 (
 167             SELECT $1, 0
 168         UNION
 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
 172     )
 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;
 175
 176 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
 177         SELECT  a.*
 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;
 184
 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 (
 187             SELECT $1, 0
 188         UNION
 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
 192     )
 193     SELECT * FROM org_unit_ancestors_distance;
 194 $$ LANGUAGE SQL STABLE ROWS 1;
 195
 196 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
 197         SELECT  *
 198           FROM  actor.org_unit_ancestors($1)
 199                         UNION
 200         SELECT  *
 201           FROM  actor.org_unit_descendants($1);
 202 $$ LANGUAGE SQL STABLE ROWS 1;
 203
 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;
 207
 208 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 209         SELECT  *
 210           FROM  actor.org_unit_ancestors($1)
 211                         UNION
 212         SELECT  *
 213           FROM  actor.org_unit_ancestors($2);
 214 $$ LANGUAGE SQL STABLE ROWS 1;
 215
 216 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 217         SELECT  *
 218           FROM  actor.org_unit_ancestors($1)
 219                         INTERSECT
 220         SELECT  *
 221           FROM  actor.org_unit_ancestors($2);
 222 $$ LANGUAGE SQL STABLE ROWS 1;
 223
 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.
 229 --
 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)
 234 RETURNS INT[] AS $$
 235     WITH RECURSIVE descendant_depth(id, path) AS (
 236         SELECT  aou.id,
 237                 ARRAY[aou.id]
 238           FROM  actor.org_unit aou
 239                 JOIN actor.org_unit_type aout ON (aout.id = aou.ou_type)
 240           WHERE aou.id = $2
 241             UNION ALL
 242         SELECT  aou.id,
 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)
 247     ) SELECT dd.path
 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;
 252
 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)
 256                         EXCEPT
 257                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
 258         ) z;
 259 $$ LANGUAGE SQL STABLE;
 260
 261 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
 262 DECLARE
 263     setting RECORD;
 264     cur_org INT;
 265 BEGIN
 266     cur_org := org_id;
 267     LOOP
 268         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
 269         IF FOUND THEN
 270             RETURN NEXT setting;
 271             EXIT;
 272         END IF;
 273         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
 274         EXIT WHEN cur_org IS NULL;
 275     END LOOP;
 276     RETURN;
 277 END;
 278 $$ LANGUAGE plpgsql STABLE ROWS 1;
 279
 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.
 283 $$;
 284
 285 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
 286 DECLARE
 287     cur_barcode TEXT;
 288     barcode_len INT;
 289     completion_len  INT;
 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;
 297 BEGIN
 298
 299     IF position('asset' in type) > 0 THEN
 300         do_asset = true;
 301     END IF;
 302     IF position('serial' in type) > 0 THEN
 303         do_serial = true;
 304     END IF;
 305     IF position('booking' in type) > 0 THEN
 306         do_booking = true;
 307     END IF;
 308     IF do_asset OR do_serial OR do_booking THEN
 309         asset_barcodes = asset_barcodes || in_barcode;
 310     END IF;
 311     IF position('actor' in type) > 0 THEN
 312         do_actor = true;
 313         actor_barcodes = actor_barcodes || in_barcode;
 314     END IF;
 315
 316     barcode_len := length(in_barcode);
 317
 318     FOR completion_set IN
 319       SELECT * FROM config.barcode_completion
 320         WHERE active
 321         AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
 322         LOOP
 323         IF completion_set.prefix IS NULL THEN
 324             completion_set.prefix := '';
 325         END IF;
 326         IF completion_set.suffix IS NULL THEN
 327             completion_set.suffix := '';
 328         END IF;
 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;
 331         ELSE
 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);
 336                 ELSE
 337                     cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
 338                 END IF;
 339                 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
 340             END IF;
 341         END IF;
 342         IF completion_set.actor THEN
 343             actor_barcodes = actor_barcodes || cur_barcode;
 344         END IF;
 345         IF completion_set.asset THEN
 346             asset_barcodes = asset_barcodes || cur_barcode;
 347         END IF;
 348     END LOOP;
 349
 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;
 353     ELSIF do_asset THEN
 354         RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 355     ELSIF do_serial THEN
 356         RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 357     END IF;
 358     IF do_booking THEN
 359         RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
 360     END IF;
 361     IF do_actor THEN
 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;
 363     END IF;
 364     RETURN;
 365 END;
 366 $$ LANGUAGE plpgsql;
 367
 368 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
 369 Given user input, find an appropriate barcode in the proper class.
 370
 371 Will add prefix/suffix information to do so, and return all results.
 372 $$;
 373
 374 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
 375 BEGIN
 376
 377
 378 IF TG_OP = 'DELETE' THEN
 379
 380     DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
 381
 382 END IF;
 383
 384 IF TG_OP = 'UPDATE' THEN
 385
 386     IF NEW.parent_ou <> OLD.parent_ou THEN
 387
 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);
 393
 394     END IF;
 395
 396 END IF;
 397
 398 IF TG_OP = 'INSERT' THEN
 399
 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);
 404
 405 END IF;
 406
 407 RETURN null;
 408
 409 END;
 410 $$ LANGUAGE plpgsql;
 411
 412
 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 ();
 414