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.naco_normalize( TEXT, TEXT ) RETURNS TEXT AS $func$
  37         use Unicode::Normalize;
  38         use Encode;
  39
  40         # When working with Unicode data, the first step is to decode it to
  41         # a byte string; after that, lowercasing is safe
  42         my $txt = lc(decode_utf8(shift));
  43         my $sf = shift;
  44
  45         $txt = NFD($txt);
  46         $txt =~ s/\pM+//go;     # Remove diacritics
  47
  48         $txt =~ s/\xE6/AE/go;   # Convert ae digraph
  49         $txt =~ s/\x{153}/OE/go;# Convert oe digraph
  50         $txt =~ s/\xFE/TH/go;   # Convert Icelandic thorn
  51
  52         $txt =~ tr/\x{2070}\x{2071}\x{2072}\x{2073}\x{2074}\x{2075}\x{2076}\x{2077}\x{2078}\x{2079}\x{207A}\x{207B}/0123456789+-/;# Convert superscript numbers
  53         $txt =~ tr/\x{2080}\x{2081}\x{2082}\x{2083}\x{2084}\x{2085}\x{2086}\x{2087}\x{2088}\x{2089}\x{208A}\x{208B}/0123456889+-/;# Convert subscript numbers
  54
  55         $txt =~ tr/\x{0251}\x{03B1}\x{03B2}\x{0262}\x{03B3}/AABGG/;             # Convert Latin and Greek
  56         $txt =~ tr/\x{2113}\xF0\!\"\(\)\-\{\}\<\>\;\:\.\?\xA1\xBF\/\\\@\*\%\=\xB1\+\xAE\xA9\x{2117}\$\xA3\x{FFE1}\xB0\^\_\~\`/LD /;     # Convert Misc
  57         $txt =~ tr/\'\[\]\|//d;                                                 # Remove Misc
  58
  59         if ($sf && $sf =~ /^a/o) {
  60                 my $commapos = index($txt,',');
  61                 if ($commapos > -1) {
  62                         if ($commapos != length($txt) - 1) {
  63                                 my @list = split /,/, $txt;
  64                                 my $first = shift @list;
  65                                 $txt = $first . ',' . join(' ', @list);
  66                         } else {
  67                                 $txt =~ s/,/ /go;
  68                         }
  69                 }
  70         } else {
  71                 $txt =~ s/,/ /go;
  72         }
  73
  74         $txt =~ s/\s+/ /go;     # Compress multiple spaces
  75         $txt =~ s/^\s+//o;      # Remove leading space
  76         $txt =~ s/\s+$//o;      # Remove trailing space
  77
  78         # Encoding the outgoing string is good practice, but not strictly
  79         # necessary in this case because we've stripped everything from it
  80         return encode_utf8($txt);
  81 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
  82
  83 CREATE OR REPLACE FUNCTION public.naco_normalize( TEXT ) RETURNS TEXT AS $func$
  84         SELECT public.naco_normalize($1,'');
  85 $func$ LANGUAGE 'sql' STRICT IMMUTABLE;
  86
  87 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
  88         SELECT SUBSTRING( $1 FROM $_$^\S+$_$);
  89 $$ LANGUAGE SQL;
  90
  91 CREATE OR REPLACE FUNCTION public.naco_normalize_keep_comma( TEXT ) RETURNS TEXT AS $func$
  92         SELECT public.naco_normalize($1,'a');
  93 $func$ LANGUAGE SQL STRICT IMMUTABLE;
  94
  95 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
  96     SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
  97 $$ LANGUAGE SQL;
  98
  99 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
 100     return lc(shift);
 101 $$ LANGUAGE PLPERLU;
 102
 103 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
 104     return uc(shift);
 105 $$ LANGUAGE PLPERLU;
 106
 107 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
 108     use Unicode::Normalize;
 109
 110     my $x = NFD(shift);
 111     $x =~ s/\pM+//go;
 112     return $x;
 113
 114 $$ LANGUAGE PLPERLU;
 115
 116 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
 117     use Unicode::Normalize;
 118
 119     my $x = NFC(shift);
 120     $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
 121     return $x;
 122
 123 $$ LANGUAGE PLPERLU;
 124
 125 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT ) RETURNS TEXT AS $$
 126         my $txt = shift;
 127         $txt =~ s/^\s+//o;
 128         $txt =~ s/[\[\]\{\}\(\)`'"#<>\*\?\-\+\$\\]+//og;
 129         $txt =~ s/\s+$//o;
 130         if ($txt =~ /(\d{3}(?:\.\d+)?)/o) {
 131                 return $1;
 132         } else {
 133                 return (split /\s+/, $txt)[0];
 134         }
 135 $$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
 136
 137 CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS $$
 138         SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2);
 139 $$ LANGUAGE SQL STRICT IMMUTABLE;
 140
 141 CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$
 142         BEGIN
 143                 RETURN $1::regclass;
 144         END;
 145 $$ language 'plpgsql';
 146
 147
 148 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$
 149         SELECT  a.*
 150           FROM  connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,$1::text,100,'.'::text)
 151                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 152                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
 153           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
 154 $$ LANGUAGE SQL STABLE;
 155
 156 CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$
 157         SELECT  a.*
 158           FROM  connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text)
 159                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 160                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
 161         JOIN actor.org_unit_type tp ON tp.id = a.ou_type
 162         ORDER BY tp.depth, a.name;
 163 $$ LANGUAGE SQL STABLE;
 164
 165 CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$
 166         SELECT  a.*
 167           FROM  connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,
 168                                 (SELECT x.id
 169                                    FROM actor.org_unit_ancestors($1) x
 170                                         JOIN actor.org_unit_type y ON x.ou_type = y.id
 171                                   WHERE y.depth = $2)::text
 172                 ,100,'.'::text)
 173                         AS t(keyid text, parent_keyid text, level int, branch text,pos int)
 174                 JOIN actor.org_unit a ON a.id::text = t.keyid::text
 175           ORDER BY  CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name;
 176 $$ LANGUAGE SQL STABLE;
 177
 178 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$
 179         SELECT  a.*
 180           FROM  actor.org_unit a
 181           WHERE id = ( SELECT FIRST(x.id)
 182                          FROM   actor.org_unit_ancestors($1) x
 183                                 JOIN actor.org_unit_type y
 184                                         ON x.ou_type = y.id AND y.depth = $2);
 185 $$ LANGUAGE SQL STABLE;
 186
 187 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$
 188         SELECT  *
 189           FROM  actor.org_unit_ancestors($1)
 190                         UNION
 191         SELECT  *
 192           FROM  actor.org_unit_descendants($1);
 193 $$ LANGUAGE SQL STABLE;
 194
 195 CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 196         SELECT  * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id)
 197 $$ LANGUAGE SQL STABLE;
 198
 199 CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 200         SELECT  *
 201           FROM  actor.org_unit_ancestors($1)
 202                         UNION
 203         SELECT  *
 204           FROM  actor.org_unit_ancestors($2);
 205 $$ LANGUAGE SQL STABLE;
 206
 207 CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$
 208         SELECT  *
 209           FROM  actor.org_unit_ancestors($1)
 210                         INTERSECT
 211         SELECT  *
 212           FROM  actor.org_unit_ancestors($2);
 213 $$ LANGUAGE SQL STABLE;
 214
 215 CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$
 216         SELECT COUNT(id)::INT FROM (
 217                 SELECT id FROM actor.org_unit_combined_ancestors($1, $2)
 218                         EXCEPT
 219                 SELECT id FROM actor.org_unit_common_ancestors($1, $2)
 220         ) z;
 221 $$ LANGUAGE SQL STABLE;
 222
 223 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
 224 DECLARE
 225     setting RECORD;
 226     cur_org INT;
 227 BEGIN
 228     cur_org := org_id;
 229     LOOP
 230         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
 231         IF FOUND THEN
 232             RETURN NEXT setting;
 233         END IF;
 234         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
 235         EXIT WHEN cur_org IS NULL;
 236     END LOOP;
 237     RETURN;
 238 END;
 239 $$ LANGUAGE plpgsql;
 240
 241 COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$
 242 /**
 243 * Search "up" the org_unit tree until we find the first occurrence of an
 244 * org_unit_setting with the given name.
 245 */
 246 $$;
 247