2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
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.
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.
22 CREATE OR REPLACE FUNCTION oils_xml_transform ( TEXT, TEXT ) RETURNS TEXT AS $_$
23 SELECT CASE WHEN (SELECT COUNT(*) FROM config.xml_transform WHERE name = $2 AND xslt = '---') > 0 THEN $1
24 ELSE xslt_process($1, (SELECT xslt FROM config.xml_transform WHERE name = $2))
26 $_$ LANGUAGE SQL STRICT IMMUTABLE;
28 CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
29 SELECT regexp_replace(array_to_string( array_accum( output ),' ' ),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT);
34 CREATE FUNCTION version_specific_xpath () RETURNS TEXT AS $wrapper_function$
39 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT < 8.3 THEN
40 out_text := 'Creating XPath functions that work like the native XPATH function in 8.3+';
42 EXECUTE $create_82_funcs$
44 CREATE OR REPLACE FUNCTION oils_xpath ( xpath TEXT, xml TEXT, ns ANYARRAY ) RETURNS TEXT[] AS $func$
51 munged_xpath := xpath;
53 IF ns IS NOT NULL THEN
54 FOR namespace IN 1 .. array_upper(ns, 1) LOOP
55 munged_xpath := REGEXP_REPLACE(
57 E'(' || ns[namespace][1] || E'):(\\w+)',
58 E'*[local-name() = "\\2" and namespace-uri() = "' || ns[namespace][2] || E'"]',
63 munged_xpath := REGEXP_REPLACE( munged_xpath, E'\\]\\[(\\D)',E' and \\1', 'g');
66 -- RAISE NOTICE 'munged xpath: %', munged_xpath;
68 node_text := xpath_nodeset(xml, munged_xpath, 'XXX_OILS_NODESET');
69 -- RAISE NOTICE 'node_text: %', node_text;
71 IF munged_xpath ~ $re$/[^/[]*@[^/]+$$re$ THEN
72 node_text := REGEXP_REPLACE(node_text,'<XXX_OILS_NODESET>[^"]+"', '<XXX_OILS_NODESET>', 'g');
73 node_text := REGEXP_REPLACE(node_text,'"</XXX_OILS_NODESET>', '</XXX_OILS_NODESET>', 'g');
76 node_text := REGEXP_REPLACE(node_text,'^<XXX_OILS_NODESET>', '');
77 node_text := REGEXP_REPLACE(node_text,'</XXX_OILS_NODESET>$', '');
79 RETURN STRING_TO_ARRAY(node_text, '</XXX_OILS_NODESET><XXX_OILS_NODESET>');
81 $func$ LANGUAGE PLPGSQL;
83 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT oils_xpath( $1, $2, NULL::TEXT[] );' LANGUAGE SQL;
85 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
86 SELECT xslt_process( $1, $2 );
90 ELSIF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT = 8.3 THEN
91 out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.3. contrib/xml2 still required!';
93 EXECUTE $create_83_funcs$
95 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML, $3 )::TEXT[];' LANGUAGE SQL;
96 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML )::TEXT[];' LANGUAGE SQL;
98 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $$
99 SELECT xslt_process( $1, $2 );
105 out_text := 'Creating XPath wrapper functions around the native XPATH function in 8.4+, and plperlu-based xslt processor. No contrib/xml2 needed!';
107 EXECUTE $create_84_funcs$
109 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML, $3 )::TEXT[];' LANGUAGE SQL;
110 CREATE OR REPLACE FUNCTION oils_xpath ( TEXT, TEXT ) RETURNS TEXT[] AS 'SELECT XPATH( $1, $2::XML )::TEXT[];' LANGUAGE SQL;
112 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
121 # The following approach uses the older XML::LibXML 1.69 / XML::LibXSLT 1.68
122 # methods of parsing XML documents and stylesheets, in the hopes of broader
123 # compatibility with distributions
124 my $parser = $_SHARED{'_xslt_process'}{parsers}{xml} || XML::LibXML->new();
126 # Cache the XML parser, if we do not already have one
127 $_SHARED{'_xslt_process'}{parsers}{xml} = $parser
128 unless ($_SHARED{'_xslt_process'}{parsers}{xml});
130 my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
132 # Cache the XSLT processor, if we do not already have one
133 $_SHARED{'_xslt_process'}{parsers}{xslt} = $xslt_parser
134 unless ($_SHARED{'_xslt_process'}{parsers}{xslt});
136 my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
137 $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
139 $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
140 unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
142 return $stylesheet->output_string(
143 $stylesheet->transform(
144 $parser->parse_string($doc)
148 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;
156 $wrapper_function$ LANGUAGE PLPGSQL;
158 SELECT version_specific_xpath();
159 DROP FUNCTION version_specific_xpath();
162 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, TEXT, ANYARRAY ) RETURNS TEXT AS $func$
163 SELECT ARRAY_TO_STRING(
166 CASE WHEN $1 ~ $re$/[^/]*@[^]]+$$re$ OR $1 ~ $re$text\(\)$$re$ THEN '' ELSE '//text()' END,
174 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, TEXT ) RETURNS TEXT AS $func$
175 SELECT oils_xpath_string( $1, $2, $3, NULL::TEXT[] );
178 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT, ANYARRAY ) RETURNS TEXT AS $func$
179 SELECT oils_xpath_string( $1, $2, '', $3 );
182 CREATE OR REPLACE FUNCTION oils_xpath_string ( TEXT, TEXT ) RETURNS TEXT AS $func$
183 SELECT oils_xpath_string( $1, $2, NULL::TEXT[] );
187 CREATE OR REPLACE FUNCTION oils_xpath_table ( key TEXT, document_field TEXT, relation_name TEXT, xpaths TEXT, criteria TEXT ) RETURNS SETOF RECORD AS $func$
196 xpath_list := STRING_TO_ARRAY( xpaths, '|' );
198 select_list := ARRAY_APPEND( select_list, key || '::INT AS key' );
200 FOR i IN 1 .. ARRAY_UPPER(xpath_list,1) LOOP
201 select_list := ARRAY_APPEND(
211 WHEN xpath_list[i] ~ $re$/[^/[]*@[^/]+$$re$ OR xpath_list[i] ~ $re$text\(\)$$re$ THEN xpath_list[i]
212 ELSE xpath_list[i] || '//text()'
216 $sel$ || document_field || $sel$
224 where_list := ARRAY_APPEND(
226 'c_' || i || ' IS NOT NULL'
232 SELECT $q$ || ARRAY_TO_STRING( select_list, ', ' ) || $q$ FROM $q$ || relation_name || $q$ WHERE ($q$ || criteria || $q$)
233 )x WHERE $q$ || ARRAY_TO_STRING( where_list, ' AND ' );
234 -- RAISE NOTICE 'query: %', q;
236 FOR out_record IN EXECUTE q LOOP
237 RETURN NEXT out_record;
242 $func$ LANGUAGE PLPGSQL;
245 CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
250 SELECT regexp_replace(
252 $q$ || quote_literal($3) || $q$,
256 $q$ || quote_literal($4) || $q$,
259 FROM $q$ || $1 || $q$
260 WHERE id = $q$ || $2 INTO output;
265 CREATE OR REPLACE FUNCTION extract_marc_field ( TEXT, BIGINT, TEXT ) RETURNS TEXT AS $$
266 SELECT extract_marc_field($1,$2,$3,'');
271 CREATE OR REPLACE FUNCTION oils_i18n_xlate ( keytable TEXT, keyclass TEXT, keycol TEXT, identcol TEXT, keyvalue TEXT, raw_locale TEXT ) RETURNS TEXT AS $func$
273 locale TEXT := REGEXP_REPLACE( REGEXP_REPLACE( raw_locale, E'[;, ].+$', '' ), E'_', '-', 'g' );
274 language TEXT := REGEXP_REPLACE( locale, E'-.+$', '' );
275 result config.i18n_core%ROWTYPE;
277 keyfield TEXT := keyclass || '.' || keycol;
280 -- Try the full locale
282 FROM config.i18n_core
283 WHERE fq_field = keyfield
284 AND identity_value = keyvalue
285 AND translation = locale;
287 -- Try just the language
290 FROM config.i18n_core
291 WHERE fq_field = keyfield
292 AND identity_value = keyvalue
293 AND translation = language;
296 -- Fall back to the string we passed in in the first place
301 ' FROM ' || keytable ||
302 ' WHERE ' || identcol || ' = ' || quote_literal(keyvalue)
307 RETURN result.string;
309 $func$ LANGUAGE PLPGSQL;
311 -- Functions for marking translatable strings in SQL statements
312 -- Parameters are: primary key, string, class hint, property
313 CREATE OR REPLACE FUNCTION oils_i18n_gettext( INT, TEXT, TEXT, TEXT ) RETURNS TEXT AS $$
317 CREATE OR REPLACE FUNCTION oils_i18n_gettext( TEXT, TEXT, TEXT, TEXT ) RETURNS TEXT AS $$
321 CREATE OR REPLACE FUNCTION is_json (TEXT) RETURNS BOOL AS $func$
324 eval { decode_json( $json ) };
326 $func$ LANGUAGE PLPERLU;
328 CREATE OR REPLACE FUNCTION oils_xslt_process(TEXT, TEXT) RETURNS TEXT AS $func$
337 # The following approach uses the older XML::LibXML 1.69 / XML::LibXSLT 1.68
338 # methods of parsing XML documents and stylesheets, in the hopes of broader
339 # compatibility with distributions
340 my $parser = $_SHARED{'_xslt_process'}{parsers}{xml} || XML::LibXML->new();
342 # Cache the XML parser, if we do not already have one
343 $_SHARED{'_xslt_process'}{parsers}{xml} = $parser
344 unless ($_SHARED{'_xslt_process'}{parsers}{xml});
346 my $xslt_parser = $_SHARED{'_xslt_process'}{parsers}{xslt} || XML::LibXSLT->new();
348 # Cache the XSLT processor, if we do not already have one
349 $_SHARED{'_xslt_process'}{parsers}{xslt} = $xslt_parser
350 unless ($_SHARED{'_xslt_process'}{parsers}{xslt});
352 my $stylesheet = $_SHARED{'_xslt_process'}{stylesheets}{$xslt} ||
353 $xslt_parser->parse_stylesheet( $parser->parse_string($xslt) );
355 $_SHARED{'_xslt_process'}{stylesheets}{$xslt} = $stylesheet
356 unless ($_SHARED{'_xslt_process'}{stylesheets}{$xslt});
358 return $stylesheet->output_string(
359 $stylesheet->transform(
360 $parser->parse_string($doc)
364 $func$ LANGUAGE 'plperlu' STRICT IMMUTABLE;