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_pref_lib (
 156     ou INT,
 157     depth INT,
 158     pref_ou INT
 159 ) RETURNS SETOF actor.org_unit AS $$
 160     SELECT ou.* FROM actor.org_unit_descendants($1, $2) AS ou
 161     UNION
 162     SELECT ou.* FROM actor.org_unit ou WHERE id = $3;
 163 $$ LANGUAGE SQL STABLE ROWS 1;
 164
 165 COMMENT ON FUNCTION actor.org_unit_descendants_pref_lib( INT, INT, INT) IS $$
 166 Returns the descendants by depth of the specified library, but adds the
 167 preferred library if it is not contained in the set of descendants.
 168 $$;
 169
 170 CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
 171     WITH RECURSIVE org_unit_descendants_distance(id, distance) AS (
 172             SELECT $1, 0
 173         UNION
 174             SELECT ou.id, oudd.distance+1
 175             FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id)
 176     )
 177     SELECT * FROM org_unit_descendants_distance;
 178 $$ LANGUAGE SQL STABLE ROWS 1;
 179
 180 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$
 181     WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
 182             SELECT $1, 0
 183         UNION
 184             SELECT ou.parent_ou, ouad.distance+1
 185             FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
 186             WHERE ou.parent_ou IS NOT NULL
 187     )
 188     SELECT ou.* FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
 189 $$ LANGUAGE SQL ROWS 1;
 190
 191 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
 192         SELECT  a.*
 193           FROM  actor.org_unit a
 194           WHERE id = ( SELECT FIRST(x.id)
 195                          FROM   actor.org_unit_ancestors($1) x
 196                                 JOIN actor.org_unit_type y
 197                                         ON x.ou_type = y.id AND y.depth = $2);
 198 $$ LANGUAGE SQL STABLE;
 199
 200 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
 201     WITH RECURSIVE org_unit_ancestors_distance(id, distance) AS (
 202             SELECT $1, 0
 203         UNION
 204             SELECT ou.parent_ou, ouad.distance+1
 205             FROM actor.org_unit ou JOIN org_unit_ancestors_distance ouad ON (ou.id = ouad.id)
 206             WHERE ou.parent_ou IS NOT NULL
 207     )
 208     SELECT * FROM org_unit_ancestors_distance;
 209 $$ LANGUAGE SQL STABLE ROWS 1;
 210
 211 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
 212         SELECT  *
 213           FROM  actor.org_unit_ancestors($1)
 214                         UNION
 215         SELECT  *
 216           FROM  actor.org_unit_descendants($1);
 217 $$ LANGUAGE SQL STABLE ROWS 1;
 218
 219 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 220         SELECT  * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
 221 $$ LANGUAGE SQL STABLE ROWS 1;
 222
 223 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 224         SELECT  *
 225           FROM  actor.org_unit_ancestors($1)
 226                         UNION
 227         SELECT  *
 228           FROM  actor.org_unit_ancestors($2);
 229 $$ LANGUAGE SQL STABLE ROWS 1;
 230
 231 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 232         SELECT  *
 233           FROM  actor.org_unit_ancestors($1)
 234                         INTERSECT
 235         SELECT  *
 236           FROM  actor.org_unit_ancestors($2);
 237 $$ LANGUAGE SQL STABLE ROWS 1;
 238
 239 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
 240         SELECT COUNT(id)::INT FROM (
 241                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
 242                         EXCEPT
 243                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
 244         ) z;
 245 $$ LANGUAGE SQL STABLE;
 246
 247 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
 248 DECLARE
 249     setting RECORD;
 250     cur_org INT;
 251 BEGIN
 252     cur_org := org_id;
 253     LOOP
 254         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
 255         IF FOUND THEN
 256             RETURN NEXT setting;
 257             EXIT;
 258         END IF;
 259         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
 260         EXIT WHEN cur_org IS NULL;
 261     END LOOP;
 262     RETURN;
 263 END;
 264 $$ LANGUAGE plpgsql STABLE ROWS 1;
 265
 266 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
 267 Search "up" the org_unit tree until we find the first occurrence of an
 268 org_unit_setting with the given name.
 269 $$;
 270
 271 CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$
 272 DECLARE
 273     cur_barcode TEXT;
 274     barcode_len INT;
 275     completion_len  INT;
 276     asset_barcodes  TEXT[];
 277     actor_barcodes  TEXT[];
 278     do_asset    BOOL = false;
 279     do_serial   BOOL = false;
 280     do_booking  BOOL = false;
 281     do_actor    BOOL = false;
 282     completion_set  config.barcode_completion%ROWTYPE;
 283 BEGIN
 284
 285     IF position('asset' in type) > 0 THEN
 286         do_asset = true;
 287     END IF;
 288     IF position('serial' in type) > 0 THEN
 289         do_serial = true;
 290     END IF;
 291     IF position('booking' in type) > 0 THEN
 292         do_booking = true;
 293     END IF;
 294     IF do_asset OR do_serial OR do_booking THEN
 295         asset_barcodes = asset_barcodes || in_barcode;
 296     END IF;
 297     IF position('actor' in type) > 0 THEN
 298         do_actor = true;
 299         actor_barcodes = actor_barcodes || in_barcode;
 300     END IF;
 301
 302     barcode_len := length(in_barcode);
 303
 304     FOR completion_set IN
 305       SELECT * FROM config.barcode_completion
 306         WHERE active
 307         AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou)
 308         LOOP
 309         IF completion_set.prefix IS NULL THEN
 310             completion_set.prefix := '';
 311         END IF;
 312         IF completion_set.suffix IS NULL THEN
 313             completion_set.suffix := '';
 314         END IF;
 315         IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN
 316             cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix;
 317         ELSE
 318             completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix);
 319             IF completion_len >= barcode_len THEN
 320                 IF completion_set.padding_end THEN
 321                     cur_barcode = rpad(in_barcode, completion_len, completion_set.padding);
 322                 ELSE
 323                     cur_barcode = lpad(in_barcode, completion_len, completion_set.padding);
 324                 END IF;
 325                 cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix;
 326             END IF;
 327         END IF;
 328         IF completion_set.actor THEN
 329             actor_barcodes = actor_barcodes || cur_barcode;
 330         END IF;
 331         IF completion_set.asset THEN
 332             asset_barcodes = asset_barcodes || cur_barcode;
 333         END IF;
 334     END LOOP;
 335
 336     IF do_asset AND do_serial THEN
 337         RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 338         RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 339     ELSIF do_asset THEN
 340         RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 341     ELSIF do_serial THEN
 342         RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false;
 343     END IF;
 344     IF do_booking THEN
 345         RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes);
 346     END IF;
 347     IF do_actor THEN
 348         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;
 349     END IF;
 350     RETURN;
 351 END;
 352 $$ LANGUAGE plpgsql;
 353
 354 COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$
 355 Given user input, find an appropriate barcode in the proper class.
 356
 357 Will add prefix/suffix information to do so, and return all results.
 358 $$;
 359
 360 CREATE OR REPLACE FUNCTION actor.org_unit_prox_update () RETURNS TRIGGER as $$
 361 BEGIN
 362
 363
 364 IF TG_OP = 'DELETE' THEN
 365
 366     DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
 367
 368 END IF;
 369
 370 IF TG_OP = 'UPDATE' THEN
 371
 372     IF NEW.parent_ou <> OLD.parent_ou THEN
 373
 374         DELETE FROM actor.org_unit_proximity WHERE (from_org = OLD.id or to_org= OLD.id);
 375             INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
 376             SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
 377                 FROM  actor.org_unit l, actor.org_unit r
 378                 WHERE (l.id = NEW.id or r.id = NEW.id);
 379
 380     END IF;
 381
 382 END IF;
 383
 384 IF TG_OP = 'INSERT' THEN
 385
 386      INSERT INTO actor.org_unit_proximity (from_org, to_org, prox)
 387      SELECT  l.id, r.id, actor.org_unit_proximity(l.id,r.id)
 388          FROM  actor.org_unit l, actor.org_unit r
 389          WHERE (l.id = NEW.id or r.id = NEW.id);
 390
 391 END IF;
 392
 393 RETURN null;
 394
 395 END;
 396 $$ LANGUAGE plpgsql;
 397
 398
 399 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 ();
 400