3 INSERT INTO config.upgrade_log (version) VALUES ('0226');
5 CREATE OR REPLACE FUNCTION public.first_word ( TEXT ) RETURNS TEXT AS $$
6 SELECT SUBSTRING( $1 FROM $_$^\S+$_$);
7 $$ LANGUAGE SQL STRICT IMMUTABLE;
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;
13 CREATE OR REPLACE FUNCTION public.remove_commas( TEXT ) RETURNS TEXT AS $$
14 SELECT regexp_replace($1, ',', '', 'g');
15 $$ LANGUAGE SQL STRICT IMMUTABLE;
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;
21 CREATE OR REPLACE FUNCTION public.lowercase( TEXT ) RETURNS TEXT AS $$
23 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
25 CREATE OR REPLACE FUNCTION public.uppercase( TEXT ) RETURNS TEXT AS $$
27 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
29 CREATE OR REPLACE FUNCTION public.remove_diacritics( TEXT ) RETURNS TEXT AS $$
30 use Unicode::Normalize;
36 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
38 CREATE OR REPLACE FUNCTION public.entityize( TEXT ) RETURNS TEXT AS $$
39 use Unicode::Normalize;
42 $x =~ s/([\x{0080}-\x{fffd}])/sprintf('&#x%X;',ord($1))/sgoe;
45 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
47 CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_setting( setting_name TEXT, org_id INT ) RETURNS SETOF actor.org_unit_setting AS $$
54 SELECT INTO setting * FROM actor.org_unit_setting WHERE org_unit = cur_org AND name = setting_name;
58 SELECT INTO cur_org parent_ou FROM actor.org_unit WHERE id = cur_org;
59 EXIT WHEN cur_org IS NULL;
63 $$ LANGUAGE plpgsql STABLE;
65 CREATE FUNCTION version_specific_xpath () RETURNS TEXT AS $wrapper_function$
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+';
73 EXECUTE $create_82_funcs$
75 CREATE OR REPLACE FUNCTION oils_xpath ( xpath TEXT, xml TEXT, ns ANYARRAY ) RETURNS TEXT[] AS $func$
82 munged_xpath := xpath;
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(
88 E'(' || ns[namespace][1] || E'):(\\w+)',
89 E'*[local-name() = "\\2" and namespace-uri() = "' || ns[namespace][2] || E'"]',
94 munged_xpath := REGEXP_REPLACE( munged_xpath, E'\\]\\[(\\D)',E' and \\1', 'g');
97 -- RAISE NOTICE 'munged xpath: %', munged_xpath;
99 node_text := xpath_nodeset(xml, munged_xpath, 'XXX_OILS_NODESET');
100 -- RAISE NOTICE 'node_text: %', node_text;
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');
107 node_text := REGEXP_REPLACE(node_text,'^<XXX_OILS_NODESET>', '');
108 node_text := REGEXP_REPLACE(node_text,'</XXX_OILS_NODESET>$', '');
110 RETURN STRING_TO_ARRAY(node_text, '</XXX_OILS_NODESET><XXX_OILS_NODESET>');
112 $func$ LANGUAGE PLPGSQL IMMUTABLE;
114 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS $$SELECT oils_xpath( $1, $2, '{}'::TEXT[] );$$ LANGUAGE SQL IMMUTABLE;
116 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
117 SELECT xslt_process( $1, $2 );
118 $$ LANGUAGE SQL IMMUTABLE;
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!';
124 EXECUTE $create_83_funcs$
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;
129 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
130 SELECT xslt_process( $1, $2 );
131 $$ LANGUAGE SQL IMMUTABLE;
136 out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.4+, and plperlu-based xslt processor. No contrib/xml2 needed!';
138 EXECUTE $create_84_funcs$
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;
143 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
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();
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});
161 my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
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});
167 my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
168 $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
170 $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
171 unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
173 return $stylesheet->output_string(
174 $stylesheet->transform(
175 $parser->parse_string($doc)
179 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
187 $wrapper_function$ LANGUAGE PLPGSQL;
189 SELECT version_specific_xpath();
190 DROP FUNCTION version_specific_xpath();
192 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, TEXT, ANYARRAY ) RETURNS TEXT AS $func$
193 SELECT ARRAY_TO_STRING(
196 CASE WHEN $1 ~ $re$/[^/[]*@[^]]+$$re$ OR $1 ~ $re$text\(\)$$re$ THEN '' ELSE '//text()' END,
202 $func$ LANGUAGE SQL IMMUTABLE;
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;
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;
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;
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$
226 xpath_list := STRING_TO_ARRAY( xpaths, '|' );
228 select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
230 FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
231 select_list := ARRAY_APPEND(
241 WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
242 ELSE xpath_list[i] || '//text()'
246 $sel$ || document_field || $sel$
254 where_list := ARRAY_APPEND(
256 'c_' || i || ' IS NOT NULL'
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;
266 FOR out_record IN EXECUTE q LOOP
267 RETURN NEXT out_record;
272 $func$ LANGUAGE PLPGSQL IMMUTABLE;
274 CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
280 SELECT regexp_replace(
282 $q$ || quote_literal($3) || $q$,
286 $q$ || quote_literal($4) || $q$,
289 FROM $q$ || $1 || $q$
290 WHERE id = $q$ || $2;
292 EXECUTE query INTO output;
294 -- RAISE NOTICE 'query: %, output; %', query, output;
298 $$ LANGUAGE PLPGSQL IMMUTABLE;
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;
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$
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;
310 keyfield TEXT := keyclass || '.' || keycol;
313 -- Try the full locale
315 FROM config.i18n_core
316 WHERE fq_field = keyfield
317 AND identity_value = keyvalue
318 AND translation = locale;
320 -- Try just the language
323 FROM config.i18n_core
324 WHERE fq_field = keyfield
325 AND identity_value = keyvalue
326 AND translation = language;
329 -- Fall back to the string we passed in in the first place
334 ' FROM ' || keytable ||
335 ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
340 RETURN result.string;
342 $func$ LANGUAGE PLPGSQL STABLE;
344 CREATE OR REPLACE FUNCTION is_json (TEXT) RETURNS BOOL AS $func$
347 eval { decode_json( $json ) };
349 $func$ LANGUAGE PLPERLU IMMUTABLE;