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 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
 225         SELECT COUNT(id)::INT FROM (
 226                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
 227                         EXCEPT
 228                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
 229         ) z;
 230 $$ LANGUAGE SQL STABLE;
 231
 232 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
 233 DECLARE
 234     setting RECORD;
 235     cur_org INT;
 236 BEGIN
 237     cur_org := org_id;
 238     LOOP
 239         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
 240         IF FOUND THEN
 241             RETURN NEXT setting;
 242             EXIT;
 243         END IF;
 244         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
 245         EXIT WHEN cur_org IS NULL;
 246     END LOOP;
 247     RETURN;
 248 END;
 249 $$ LANGUAGE plpgsql STABLE ROWS 1;
 250
 251 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
 252 Search "up" the org_unit tree until we find the first occurrence of an
 253 org_unit_setting with the given name.
 254 $$;
 255
 256 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
 257 DECLARE
 258     cur_barcode TEXT;
 259     barcode_len INT;
 260     completion_len  INT;
 261     asset_barcodes  TEXT[];
 262     actor_barcodes  TEXT[];
 263     do_asset    BOOL = false;
 264     do_serial   BOOL = false;
 265     do_booking  BOOL = false;
 266     do_actor    BOOL = false;
 267     completion_set  config.barcode_completion%ROWTYPE;
 268 BEGIN
 269
 270     IF position('asset' in type) > 0 THEN
 271         do_asset = true;
 272     END IF;
 273     IF position('serial' in type) > 0 THEN
 274         do_serial = true;
 275     END IF;
 276     IF position('booking' in type) > 0 THEN
 277         do_booking = true;
 278     END IF;
 279     IF do_asset OR do_serial OR do_booking THEN
 280         asset_barcodes = asset_barcodes || in_barcode;
 281     END IF;
 282     IF position('actor' in type) > 0 THEN
 283         do_actor = true;
 284         actor_barcodes = actor_barcodes || in_barcode;
 285     END IF;
 286
 287     barcode_len := length(in_barcode);
 288
 289     FOR completion_set IN
 290       SELECT * FROM config.barcode_completion
 291         WHERE active
 292         AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
 293         LOOP
 294         IF completion_set.prefix IS NULL THEN
 295             completion_set.prefix := '';
 296         END IF;
 297         IF completion_set.suffix IS NULL THEN
 298             completion_set.suffix := '';
 299         END IF;
 300         IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
 301             cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
 302         ELSE
 303             completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
 304             IF completion_len >= barcode_len THEN
 305                 IF completion_set.padding_end THEN
 306                     cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
 307                 ELSE
 308                     cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
 309                 END IF;
 310                 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
 311             END IF;
 312         END IF;
 313         IF completion_set.actor THEN
 314             actor_barcodes = actor_barcodes || cur_barcode;
 315         END IF;
 316         IF completion_set.asset THEN
 317             asset_barcodes = asset_barcodes || cur_barcode;
 318         END IF;
 319     END LOOP;
 320
 321     IF do_asset AND do_serial THEN
 322         RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 323         RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 324     ELSIF do_asset THEN
 325         RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 326     ELSIF do_serial THEN
 327         RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 328     END IF;
 329     IF do_booking THEN
 330         RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
 331     END IF;
 332     IF do_actor THEN
 333         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;
 334     END IF;
 335     RETURN;
 336 END;
 337 $$ LANGUAGE plpgsql;
 338
 339 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
 340 Given user input, find an appropriate barcode in the proper class.
 341
 342 Will add prefix/suffix information to do so, and return all results.
 343 $$;
 344
 345 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
 346 BEGIN
 347
 348
 349 IF TG_OP = 'DELETE' THEN
 350
 351     DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
 352
 353 END IF;
 354
 355 IF TG_OP = 'UPDATE' THEN
 356
 357     IF NEW.parent_ou <> OLD.parent_ou THEN
 358
 359         DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
 360             INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
 361             SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
 362                 FROM  actor.org_unit l, actor.org_unit r
 363                 WHERE (l.id = NEW.id or r.id = NEW.id);
 364
 365     END IF;
 366
 367 END IF;
 368
 369 IF TG_OP = 'INSERT' THEN
 370
 371      INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
 372      SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
 373          FROM  actor.org_unit l, actor.org_unit r
 374          WHERE (l.id = NEW.id or r.id = NEW.id);
 375
 376 END IF;
 377
 378 RETURN null;
 379
 380 END;
 381 $$ LANGUAGE plpgsql;
 382
 383
 384 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 ();
 385