Open-ILS/src/sql/Pg/upgrade/0226.schema.stored_proc_stability_adjustments.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0226');
   4
   5 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
   6         SELECT SUBSTRING( $1 FROM $_$^\S+$_$);
   7 $$ LANGUAGE SQL STRICT IMMUTABLE;
   8
   9 CREATE OR REPLACE FUNCTION public.normalize_space( TEXT ) RETURNS TEXT AS $$
  10     SELECT regexp_replace(regexp_replace(regexp_replace($1, E'\\n', ' ', 'g'), E'(?:^\\s+)|(\\s+$)', '', 'g'), E'\\s+', ' ', 'g');
  11 $$ LANGUAGE SQL STRICT IMMUTABLE;
  12
  13 CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
  14     SELECT regexp_replace($1, ',', '', 'g');
  15 $$ LANGUAGE SQL STRICT IMMUTABLE;
  16
  17 CREATE OR REPLACE FUNCTION public.remove_whitespace( TEXT ) RETURNS TEXT AS $$
  18     SELECT regexp_replace(normalize_space($1), E'\\s+', '', 'g');
  19 $$ LANGUAGE SQL STRICT IMMUTABLE;
  20
  21 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
  22     return lc(shift);
  23 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  24
  25 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
  26     return uc(shift);
  27 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  28
  29 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
  30     use Unicode::Normalize;
  31
  32     my $x = NFD(shift);
  33     $x =~ s/\pM+//go;
  34     return $x;
  35
  36 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  37
  38 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
  39     use Unicode::Normalize;
  40
  41     my $x = NFC(shift);
  42     $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
  43     return $x;
  44
  45 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
  46
  47 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
  48 DECLARE
  49     setting RECORD;
  50     cur_org INT;
  51 BEGIN
  52     cur_org := org_id;
  53     LOOP
  54         SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
  55         IF FOUND THEN
  56             RETURN NEXT setting;
  57         END IF;
  58         SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
  59         EXIT WHEN cur_org IS NULL;
  60     END LOOP;
  61     RETURN;
  62 END;
  63 $$ LANGUAGE plpgsql STABLE;
  64
  65 CREATE FUNCTION version_specific_xpath () RETURNS TEXT AS $wrapper_function$
  66 DECLARE
  67     out_text TEXT;
  68 BEGIN
  69
  70     IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT < 8.3 THEN
  71         out_text := 'Creating XPath functions that work like the native XPATH function in 8.3+';
  72
  73         EXECUTE $create_82_funcs$
  74
  75 CREATE OR REPLACE FUNCTION oils_xpath ( xpath TEXT, xml TEXT, ns ANYARRAY ) RETURNS TEXT[] AS $func$
  76 DECLARE
  77     node_text   TEXT;
  78     ns_regexp   TEXT;
  79     munged_xpath    TEXT;
  80 BEGIN
  81
  82     munged_xpath := xpath;
  83
  84     IF ns IS NOT NULL AND array_upper(ns, 1) IS NOT NULL THEN
  85         FOR namespace IN 1 .. array_upper(ns, 1) LOOP
  86             munged_xpath := REGEXP_REPLACE(
  87                 munged_xpath,
  88                 E'(' || ns[namespace][1] || E'):(\\w+)',
  89                 E'*[local-name() = "\\2" and namespace-uri() = "' || ns[namespace][2] || E'"]',
  90                 'g'
  91             );
  92         END LOOP;
  93
  94         munged_xpath := REGEXP_REPLACE( munged_xpath, E'\\]\\[(\\D)',E' and \\1', 'g');
  95     END IF;
  96
  97     -- RAISE NOTICE 'munged xpath: %', munged_xpath;
  98
  99     node_text := xpath_nodeset(xml, munged_xpath, 'XXX_OILS_NODESET');
 100     -- RAISE NOTICE 'node_text: %', node_text;
 101
 102     IF munged_xpath ~ $re$/[^/[]*@[^/]+$$re$ THEN
 103         node_text := REGEXP_REPLACE(node_text,'<XXX_OILS_NODESET>[^"]+"', '<XXX_OILS_NODESET>', 'g');
 104         node_text := REGEXP_REPLACE(node_text,'"</XXX_OILS_NODESET>', '</XXX_OILS_NODESET>', 'g');
 105     END IF;
 106
 107     node_text := REGEXP_REPLACE(node_text,'^<XXX_OILS_NODESET>', '');
 108     node_text := REGEXP_REPLACE(node_text,'</XXX_OILS_NODESET>$', '');
 109
 110     RETURN  STRING_TO_ARRAY(node_text, '</XXX_OILS_NODESET><XXX_OILS_NODESET>');
 111 END;
 112 $func$ LANGUAGE PLPGSQL IMMUTABLE;
 113
 114 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS $$SELECT oils_xpath( $1, $2, '{}'::TEXT[] );$$ LANGUAGE SQL IMMUTABLE;
 115
 116 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
 117     SELECT xslt_process( $1, $2 );
 118 $$ LANGUAGE SQL IMMUTABLE;
 119
 120         $create_82_funcs$;
 121     ELSIF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT = 8.3 THEN
 122         out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.3.  contrib/xml2 still required!';
 123
 124         EXECUTE $create_83_funcs$
 125 -- 8.3 or after
 126 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML, $3 )::TEXT[];' LANGUAGE SQL IMMUTABLE;
 127 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML )::TEXT[];' LANGUAGE SQL IMMUTABLE;
 128
 129 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
 130     SELECT xslt_process( $1, $2 );
 131 $$ LANGUAGE SQL IMMUTABLE;
 132
 133         $create_83_funcs$;
 134
 135     ELSE
 136         out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.4+, and plperlu-based xslt processor.  No contrib/xml2 needed!';
 137
 138         EXECUTE $create_84_funcs$
 139 -- 8.4 or after
 140 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML, $3 )::TEXT[];' LANGUAGE SQL IMMUTABLE;
 141 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML )::TEXT[];' LANGUAGE SQL IMMUTABLE;
 142
 143 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
 144   use strict;
 145
 146   use XML::LibXSLT;
 147   use XML::LibXML;
 148
 149   my $doc = shift;
 150   my $xslt = shift;
 151
 152   # The following approach uses the older XML::LibXML 1.69 / XML::LibXSLT 1.68
 153   # methods of parsing XML documents and stylesheets, in the hopes of broader
 154   # compatibility with distributions
 155   my $parser = $_SHARED{'_xslt_process'}{parsers}{xml} || XML::LibXML->new();
 156
 157   # Cache the XML parser, if we do not already have one
 158   $_SHARED{'_xslt_process'}{parsers}{xml} = $parser
 159     unless ($_SHARED{'_xslt_process'}{parsers}{xml});
 160
 161   my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
 162
 163   # Cache the XSLT processor, if we do not already have one
 164   $_SHARED{'_xslt_process'}{parsers}{xslt} = $xslt_parser
 165     unless ($_SHARED{'_xslt_process'}{parsers}{xslt});
 166
 167   my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
 168     $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
 169
 170   $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
 171     unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
 172
 173   return $stylesheet->output_string(
 174     $stylesheet->transform(
 175       $parser->parse_string($doc)
 176     )
 177   );
 178
 179 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
 180
 181         $create_84_funcs$;
 182
 183     END IF;
 184
 185     RETURN out_text;
 186 END;
 187 $wrapper_function$ LANGUAGE PLPGSQL;
 188
 189 SELECT version_specific_xpath();
 190 DROP FUNCTION version_specific_xpath();
 191
 192 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, TEXT, ANYARRAY ) RETURNS TEXT AS $func$
 193     SELECT  ARRAY_TO_STRING(
 194                 oils_xpath(
 195                     $1 ||
 196                         CASE WHEN $1 ~ $re$/[^/[]*@[^]]+$$re$ OR $1 ~ $re$text\(\)$$re$ THEN '' ELSE '//text()' END,
 197                     $2,
 198                     $4
 199                 ),
 200                 $3
 201             );
 202 $func$ LANGUAGE SQL IMMUTABLE;
 203
 204 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, TEXT ) RETURNS TEXT AS $func$
 205     SELECT oils_xpath_string( $1, $2, $3, '{}'::TEXT[] );
 206 $func$ LANGUAGE SQL IMMUTABLE;
 207
 208 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT AS $func$
 209     SELECT oils_xpath_string( $1, $2, '', $3 );
 210 $func$ LANGUAGE SQL IMMUTABLE;
 211
 212 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT ) RETURNS TEXT AS $func$
 213     SELECT oils_xpath_string( $1, $2, '{}'::TEXT[] );
 214 $func$ LANGUAGE SQL IMMUTABLE;
 215
 216
 217 CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$
 218 DECLARE
 219     xpath_list  TEXT[];
 220     select_list TEXT[];
 221     where_list  TEXT[];
 222     q           TEXT;
 223     out_record  RECORD;
 224     empty_test  RECORD;
 225 BEGIN
 226     xpath_list := STRING_TO_ARRAY( xpaths, '|' );
 227
 228     select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
 229
 230     FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
 231         select_list := ARRAY_APPEND(
 232             select_list,
 233             $sel$
 234             EXPLODE_ARRAY(
 235                 COALESCE(
 236                     NULLIF(
 237                         oils_xpath(
 238                             $sel$ ||
 239                                 quote_literal(
 240                                     CASE
 241                                         WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
 242                                         ELSE xpath_list[i] || '//text()'
 243                                     END
 244                                 ) ||
 245                             $sel$,
 246                             $sel$ || document_field || $sel$
 247                         ),
 248                        '{}'::TEXT[]
 249                     ),
 250                     '{NULL}'::TEXT[]
 251                 )
 252             ) AS c_$sel$ || i
 253         );
 254         where_list := ARRAY_APPEND(
 255             where_list,
 256             'c_' || i || ' IS NOT NULL'
 257         );
 258     END LOOP;
 259
 260     q := $q$
 261 SELECT * FROM (
 262     SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$)
 263 )x WHERE $q$ || ARRAY_TO_STRING( where_list, ' AND ' );
 264     -- RAISE NOTICE 'query: %', q;
 265
 266     FOR out_record IN EXECUTE q LOOP
 267         RETURN NEXT out_record;
 268     END LOOP;
 269
 270     RETURN;
 271 END;
 272 $func$ LANGUAGE PLPGSQL IMMUTABLE;
 273
 274 CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
 275 DECLARE
 276     query TEXT;
 277     output TEXT;
 278 BEGIN
 279     query := $q$
 280         SELECT  regexp_replace(
 281                     oils_xpath_string(
 282                         $q$ || quote_literal($3) || $q$,
 283                         marc,
 284                         ' '
 285                     ),
 286                     $q$ || quote_literal($4) || $q$,
 287                     '',
 288                     'g')
 289           FROM  $q$ || $1 || $q$
 290           WHERE id = $q$ || $2;
 291
 292     EXECUTE query INTO output;
 293
 294     -- RAISE NOTICE 'query: %, output; %', query, output;
 295
 296     RETURN output;
 297 END;
 298 $$ LANGUAGE PLPGSQL IMMUTABLE;
 299
 300 CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT ) RETURNS TEXT AS $$
 301     SELECT extract_marc_field($1,$2,$3,'');
 302 $$ LANGUAGE SQL IMMUTABLE;
 303
 304 CREATE OR REPLACE FUNCTION oils_i18n_xlate ( keytable TEXT, keyclass TEXT, keycol TEXT, identcol TEXT, keyvalue TEXT, raw_locale TEXT ) RETURNS TEXT AS $func$
 305 DECLARE
 306     locale      TEXT := REGEXP_REPLACE( REGEXP_REPLACE( raw_locale, E'[;, ].+$', '' ), E'_', '-', 'g' );
 307     language    TEXT := REGEXP_REPLACE( locale, E'-.+$', '' );
 308     result      config.i18n_core%ROWTYPE;
 309     fallback    TEXT;
 310     keyfield    TEXT := keyclass || '.' || keycol;
 311 BEGIN
 312
 313     -- Try the full locale
 314     SELECT  * INTO result
 315       FROM  config.i18n_core
 316       WHERE fq_field = keyfield
 317             AND identity_value = keyvalue
 318             AND translation = locale;
 319
 320     -- Try just the language
 321     IF NOT FOUND THEN
 322         SELECT  * INTO result
 323           FROM  config.i18n_core
 324           WHERE fq_field = keyfield
 325                 AND identity_value = keyvalue
 326                 AND translation = language;
 327     END IF;
 328
 329     -- Fall back to the string we passed in in the first place
 330     IF NOT FOUND THEN
 331     EXECUTE
 332             'SELECT ' ||
 333                 keycol ||
 334             ' FROM ' || keytable ||
 335             ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
 336                 INTO fallback;
 337         RETURN fallback;
 338     END IF;
 339
 340     RETURN result.string;
 341 END;
 342 $func$ LANGUAGE PLPGSQL STABLE;
 343
 344 CREATE OR REPLACE FUNCTION is_json (TEXT) RETURNS BOOL AS $func$
 345     use JSON::XS;
 346     my $json = shift();
 347     eval { decode_json( $json ) };
 348     return $@ ? 0 : 1;
 349 $func$ LANGUAGE PLPERLU IMMUTABLE;
 350
 351 COMMIT;
 352