001/*
002 * Copyright (c) 2018-2023 Tada AB and other contributors, as listed below.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the The BSD 3-Clause License
006 * which accompanies this distribution, and is available at
007 * http://opensource.org/licenses/BSD-3-Clause
008 *
009 * Contributors:
010 *   Chapman Flack
011 */
012package org.postgresql.pljava.example.annotation;
013
014import java.sql.Connection;
015import java.sql.DriverManager;
016import java.sql.PreparedStatement;
017import java.sql.ResultSet;
018import java.sql.ResultSetMetaData;
019import java.sql.SQLData;
020import java.sql.SQLInput;
021import java.sql.SQLOutput;
022import java.sql.SQLXML;
023import java.sql.Statement;
024import java.sql.Types;
025
026import java.sql.SQLDataException;
027import java.sql.SQLException;
028
029import java.io.ByteArrayInputStream;
030import java.io.InputStream;
031import java.io.OutputStream;
032import java.io.Reader;
033import java.io.StringReader;
034import java.io.StringWriter;
035import java.io.Writer;
036
037import java.io.IOException;
038
039import java.util.List;
040import java.util.Map;
041import java.util.HashMap;
042
043import javax.xml.parsers.DocumentBuilderFactory;
044import javax.xml.parsers.ParserConfigurationException;
045
046import static javax.xml.transform.OutputKeys.ENCODING;
047import javax.xml.transform.Result;
048import javax.xml.transform.Source;
049import javax.xml.transform.Templates;
050import javax.xml.transform.Transformer;
051import javax.xml.transform.TransformerFactory;
052
053import javax.xml.transform.TransformerException;
054import javax.xml.transform.TransformerConfigurationException;
055
056import javax.xml.transform.stream.StreamResult;
057import javax.xml.transform.stream.StreamSource;
058import javax.xml.transform.dom.DOMResult;
059import javax.xml.transform.dom.DOMSource;
060import javax.xml.transform.sax.SAXResult;
061import javax.xml.transform.sax.SAXSource;
062import javax.xml.transform.stax.StAXResult;
063import javax.xml.transform.stax.StAXSource;
064
065import javax.xml.validation.Schema;
066import javax.xml.validation.SchemaFactory;
067
068import org.postgresql.pljava.Adjusting;
069import static org.postgresql.pljava.Adjusting.XML.setFirstSupported;
070import org.postgresql.pljava.annotation.Function;
071import org.postgresql.pljava.annotation.MappedUDT;
072import org.postgresql.pljava.annotation.SQLAction;
073import org.postgresql.pljava.annotation.SQLType;
074
075import static org.postgresql.pljava.example.LoggerTest.logMessage;
076
077/* Imports needed just for the SAX flavor of "low-level XML echo" below */
078import javax.xml.parsers.ParserConfigurationException;
079import javax.xml.parsers.SAXParserFactory;
080import org.xml.sax.XMLReader;
081import org.xml.sax.ContentHandler;
082import org.xml.sax.DTDHandler;
083import org.xml.sax.ext.LexicalHandler;
084
085/* Imports needed just for the StAX flavor of "low-level XML echo" below */
086import javax.xml.stream.XMLEventReader;
087import javax.xml.stream.XMLEventWriter;
088import javax.xml.stream.XMLInputFactory;
089import javax.xml.stream.XMLOutputFactory;
090import javax.xml.stream.XMLStreamException;
091import org.xml.sax.SAXException;
092
093/* Imports needed just for xmlTextNode below (serializing via SAX, StAX, DOM) */
094import org.xml.sax.helpers.AttributesImpl;
095import org.w3c.dom.Document;
096import org.w3c.dom.DocumentFragment;
097import org.w3c.dom.bootstrap.DOMImplementationRegistry;
098
099
100/**
101 * Class illustrating use of {@link SQLXML} to operate on XML data.
102 *<p>
103 * This class also serves as the mapping class for a composite type
104 * {@code javatest.onexml}, the better to verify that {@link SQLData}
105 * input/output works too. That's why it has to implement SQLData.
106 *<p>
107 * Everything mentioning the type XML here needs a conditional implementor tag
108 * in case of being loaded into a PostgreSQL instance built without that type.
109 */
110@SQLAction(provides="postgresql_xml", install=
111    "SELECT CASE (SELECT 1 FROM pg_type WHERE typname = 'xml') WHEN 1" +
112    " THEN set_config('pljava.implementors', 'postgresql_xml,' || " +
113    " current_setting('pljava.implementors'), true) " +
114    "END"
115)
116
117@SQLAction(implementor="postgresql_xml", requires="echoXMLParameter",
118    install=
119    "WITH" +
120    " s(how) AS (SELECT generate_series(1, 7))," +
121    " t(x) AS (" +
122    "  SELECT table_to_xml('pg_catalog.pg_operator', true, false, '')" +
123    " )," +
124    " r(howin, howout, isdoc) AS (" +
125    "  SELECT" +
126    "   i.how, o.how," +
127    "   javatest.echoxmlparameter(x, i.how, o.how) IS DOCUMENT" +
128    "  FROM" +
129    "   t, s AS i, s AS o" +
130    "  WHERE" +
131    "   NOT (i.how = 6 and o.how = 7)" + // 6->7 unreliable in some JREs
132    " ) " +
133    "SELECT" +
134    " CASE WHEN every(isdoc)" +
135    "  THEN javatest.logmessage('INFO', 'SQLXML echos succeeded')" +
136    "  ELSE javatest.logmessage('WARNING', 'SQLXML echos had problems')" +
137    " END " +
138    "FROM" +
139    " r"
140)
141
142@SQLAction(implementor="postgresql_xml", requires="proxiedXMLEcho",
143    install=
144    "WITH" +
145    " s(how) AS (SELECT unnest('{1,2,4,5,6,7}'::int[]))," +
146    " t(x) AS (" +
147    "  SELECT table_to_xml('pg_catalog.pg_operator', true, false, '')" +
148    " )," +
149    " r(how, isdoc) AS (" +
150    "  SELECT" +
151    "   how," +
152    "   javatest.proxiedxmlecho(x, how) IS DOCUMENT" +
153    "  FROM" +
154    "   t, s" +
155    " )" +
156    "SELECT" +
157    " CASE WHEN every(isdoc)" +
158    "  THEN javatest.logmessage('INFO', 'proxied SQLXML echos succeeded')" +
159    "  ELSE javatest.logmessage('WARNING'," +
160    "       'proxied SQLXML echos had problems')" +
161    " END " +
162    "FROM" +
163    " r"
164)
165
166@SQLAction(implementor="postgresql_xml", requires="lowLevelXMLEcho",
167    install={
168    "SELECT" +
169    " preparexmlschema('schematest', $$" +
170    "<xs:schema" +
171    " xmlns:xs='http://www.w3.org/2001/XMLSchema'" +
172    " targetNamespace='urn:testme'" +
173    " elementFormDefault='qualified'>" +
174    " <xs:element name='row'>" +
175    "  <xs:complexType>" +
176    "   <xs:sequence>" +
177    "    <xs:element name='textcol' type='xs:string' nillable='true'/>" +
178    "    <xs:element name='intcol' type='xs:integer' nillable='true'/>" +
179    "   </xs:sequence>" +
180    "  </xs:complexType>" +
181    " </xs:element>" +
182    "</xs:schema>" +
183    "$$, 'http://www.w3.org/2001/XMLSchema', 5)",
184
185    "WITH" +
186    " s(how) AS (SELECT unnest('{4,5,7}'::int[]))," +
187    " r(isdoc) AS (" +
188    " SELECT" +
189    "  javatest.lowlevelxmlecho(" +
190    "   query_to_xml(" +
191    "    'SELECT ''hi'' AS textcol, 1 AS intcol', true, true, 'urn:testme'"+
192    "   ), how, params) IS DOCUMENT" +
193    " FROM" +
194    "  s," +
195    "  (SELECT 'schematest' AS schema) AS params" +
196    " )" +
197    "SELECT" +
198    " CASE WHEN every(isdoc)" +
199    "  THEN javatest.logmessage('INFO', 'XML Schema tests succeeded')" +
200    "  ELSE javatest.logmessage('WARNING'," +
201    "       'XML Schema tests had problems')" +
202    " END " +
203    "FROM" +
204    " r"
205    }
206)
207
208@SQLAction(implementor="postgresql_xml",
209           requires={"prepareXMLTransform", "transformXML"},
210    install={
211        "REVOKE EXECUTE ON FUNCTION javatest.prepareXMLTransformWithJava" +
212        " (pg_catalog.varchar, pg_catalog.xml, integer, boolean, boolean," +
213        "  pg_catalog.RECORD)" +
214        " FROM PUBLIC",
215
216        "SELECT" +
217        " javatest.prepareXMLTransform('distinctElementNames'," +
218        "'<xsl:transform version=''1.0''" +
219        " xmlns:xsl=''http://www.w3.org/1999/XSL/Transform''" +
220        " xmlns:exsl=''http://exslt.org/common''" +
221        " xmlns:set=''http://exslt.org/sets''" +
222        " extension-element-prefixes=''exsl set''" +
223        ">" +
224        " <xsl:output method=''xml'' indent=''no''/>" +
225        " <xsl:template match=''/''>" +
226        "  <xsl:variable name=''enames''>" +
227        "   <xsl:for-each select=''//*''>" +
228        "    <ename><xsl:value-of select=''local-name()''/></ename>" +
229        "   </xsl:for-each>" +
230        "  </xsl:variable>" +
231        "  <xsl:for-each" +
232        "   select=''set:distinct(exsl:node-set($enames)/ename)''>" +
233        "   <xsl:sort select=''string()''/>" +
234        "   <den><xsl:value-of select=''.''/></den>" +
235        "  </xsl:for-each>" +
236        " </xsl:template>" +
237        "</xsl:transform>', how => 5, enableExtensionFunctions => true)",
238
239        "SELECT" +
240        " javatest.prepareXMLTransformWithJava('getPLJavaVersion'," +
241        "'<xsl:transform version=''1.0''" +
242        " xmlns:xsl=''http://www.w3.org/1999/XSL/Transform''" +
243        " xmlns:java=''http://xml.apache.org/xalan/java''" +
244        " exclude-result-prefixes=''java''" +
245        ">" +
246        " <xsl:template match=''/''>" +
247        "  <xsl:value-of" +
248        "   select=''java:java.lang.System.getProperty(" +
249        "    \"org.postgresql.pljava.version\")''" +
250        "  />" +
251        " </xsl:template>" +
252        "</xsl:transform>', enableExtensionFunctions => true)",
253
254        "SELECT" +
255        " CASE WHEN" +
256        "  javatest.transformXML('distinctElementNames'," +
257        "   '<a><c/><e/><b/><b/><d/></a>', 5, 5)::text" +
258        "  =" +
259        "   '<den>a</den><den>b</den><den>c</den><den>d</den><den>e</den>'"+
260        "  THEN javatest.logmessage('INFO', 'XSLT 1.0 test succeeded')" +
261        "  ELSE javatest.logmessage('WARNING', 'XSLT 1.0 test failed')" +
262        " END",
263
264        "SELECT" +
265        " CASE WHEN" +
266        "  javatest.transformXML('getPLJavaVersion', '')::text" +
267        "  OPERATOR(pg_catalog.=) extversion" +
268        "  THEN javatest.logmessage('INFO', 'XSLT 1.0 with Java succeeded')" +
269        "  ELSE javatest.logmessage('WARNING', 'XSLT 1.0 with Java failed')" +
270        " END" +
271        " FROM pg_catalog.pg_extension" +
272        " WHERE extname = 'pljava'"
273    }
274)
275@MappedUDT(schema="javatest", name="onexml", structure="c1 xml",
276           implementor="postgresql_xml",
277           comment="A composite type mapped by the PassXML example class")
278public class PassXML implements SQLData
279{
280    static SQLXML s_sx;
281
282    static TransformerFactory s_tf = TransformerFactory.newDefaultInstance();
283
284    static Map<String,Templates> s_tpls = new HashMap<>();
285
286    static Map<String,Schema> s_schemas = new HashMap<>();
287
288    @Function(schema="javatest", implementor="postgresql_xml")
289    public static String inXMLoutString(SQLXML in) throws SQLException
290    {
291        return in.getString();
292    }
293
294    @Function(schema="javatest", implementor="postgresql_xml")
295    public static SQLXML inStringoutXML(String in) throws SQLException
296    {
297        Connection c = DriverManager.getConnection("jdbc:default:connection");
298        SQLXML result = c.createSQLXML();
299        result.setString(in);
300        return result;
301    }
302
303    /**
304     * Echo an XML parameter back, exercising seven different ways
305     * (howin =&gt; 1-7) of reading an SQLXML object, and seven
306     * (howout =&gt; 1-7) of returning one.
307     *<p>
308     * If howin =&gt; 0, the XML parameter is simply saved in a static. It can
309     * be read in a subsequent call with sx =&gt; null, but only in the same
310     * transaction.
311     *<p>
312     * The "echoing" is done (in the {@code echoXML} method below) using a
313     * {@code Transformer}, that is, the "TrAX" Transformation API for XML
314     * supplied in Java. It illustrates how an identity {@code Transformer} can
315     * be used to get the XML content from the source to the result for any of
316     * the APIs selectable by howin and howout.
317     *<p>
318     * It also illustrates something else. When using StAX (6 for howin
319     * or howout) and XML of the {@code CONTENT} flavor (multiple top-level
320     * elements, characters outside the top element, etc.), it is easy to
321     * construct examples that fail. The fault is not really with the StAX API,
322     * nor with TrAX proper, but with the small handful of bridge classes that
323     * were added to the JRE with StAX's first appearance, to make it
324     * interoperate with TrAX. It is not that those classes completely overlook
325     * the {@code CONTENT} case: they make some efforts to handle it. Just not
326     * the right ones, and given the Java developers' usual reluctance to change
327     * such longstanding behavior, that's probably not getting fixed.
328     *<p>
329     * Moral: StAX is a nice API, have no fear to use it directly in
330     * freshly-developed code, but: when using TrAX, make every effort to supply
331     * a {@code Transformer} with {@code Source} and {@code Result} objects of
332     * <em>any</em> kind other than StAX.
333     */
334    @Function(schema="javatest", implementor="postgresql_xml",
335              provides="echoXMLParameter")
336    public static SQLXML echoXMLParameter(SQLXML sx, int howin, int howout)
337    throws SQLException
338    {
339        if ( null == sx )
340            sx = s_sx;
341        if ( 0 == howin )
342        {
343            s_sx = sx;
344            return null;
345        }
346        return echoSQLXML(sx, howin, howout);
347    }
348
349    /**
350     * Echo an XML parameter back, but with parameter and return types of
351     * PostgreSQL {@code text}.
352     *<p>
353     * The other version of this method needs a conditional implementor tag
354     * because it cannot be declared in a PostgreSQL instance that was built
355     * without {@code libxml} support and the PostgreSQL {@code XML} type.
356     * But this version can, simply by mapping the {@code SQLXML} parameter
357     * and return types to the SQL {@code text} type. The Java code is no
358     * different.
359     *<p>
360     * Note that it's possible for both declarations to coexist in PostgreSQL
361     * (because as far as it is concerned, their signatures are different), but
362     * these two Java methods cannot have the same name (because they differ
363     * only in annotations, not in the declared Java types). So, this one needs
364     * a slightly tweaked name, and a {@code name} attribute in the annotation
365     * so PostgreSQL sees the right name.
366     */
367    @Function(schema="javatest", name="echoXMLParameter", type="text")
368    public static SQLXML echoXMLParameter_(
369        @SQLType("text") SQLXML sx, int howin, int howout)
370    throws SQLException
371    {
372        return echoXMLParameter(sx, howin, howout);
373    }
374
375    /**
376     * "Echo" an XML parameter not by creating a new writable {@code SQLXML}
377     * object at all, but simply returning the passed-in readable one untouched.
378     */
379    @Function(schema="javatest", implementor="postgresql_xml")
380    public static SQLXML bounceXMLParameter(SQLXML sx) throws SQLException
381    {
382        return sx;
383    }
384
385    /**
386     * Just like {@link bounceXMLParameter} but with parameter and return typed
387     * as {@code text}, and so usable on a PostgreSQL instance lacking the XML
388     * type.
389     */
390    @Function(schema="javatest", type="text", name="bounceXMLParameter")
391    public static SQLXML bounceXMLParameter_(@SQLType("text") SQLXML sx)
392    throws SQLException
393    {
394        return sx;
395    }
396
397    /**
398     * Just like {@link bounceXMLParameter} but with the parameter typed as
399     * {@code text} and the return type left as XML, so functions as a cast.
400     *<p>
401     * Slower than the other cases, because it must verify that the input really
402     * is XML before blindly calling it a PostgreSQL XML type. But the speed
403     * compares respectably to PostgreSQL's own CAST(text AS xml), at least for
404     * larger values; I am seeing Java pull ahead right around 32kB of XML data
405     * and beat PG by a factor of 2 or better at sizes of 1 or 2 MB.
406     * Unsurprisingly, PG has the clear advantage when values are very short.
407     */
408    @Function(schema="javatest", implementor="postgresql_xml")
409    public static SQLXML castTextXML(@SQLType("text") SQLXML sx)
410    throws SQLException
411    {
412        return sx;
413    }
414
415    /**
416     * Precompile an XSL transform {@code source} and save it (for the
417     * current session) as {@code name}.
418     *<p>
419     * Each value of {@code how}, 1-7, selects a different way of presenting
420     * the {@code SQLXML} object to the XSL processor.
421     *<p>
422     * Passing {@code true} for {@code enableExtensionFunctions} allows the
423     * transform to use extensions that the Java XSLT implementation supports,
424     * such as functions from EXSLT. Those are disabled by default.
425     *<p>
426     * Passing {@code false} for {@code builtin} will allow a
427     * {@code TransformerFactory} other than Java's built-in one to be found
428     * using the usual search order and the context class loader (normally
429     * the PL/Java class path for the schema where this function is declared).
430     * The default of {@code true} ensures that the built-in Java XSLT 1.0
431     * implementation is used. A transformer implementation other than Xalan
432     * may not recognize the feature controlled by
433     * {@code enableExtensionFunctions}, so failure to configure that feature
434     * will be logged as a warning if {@code builtin} is {@code false}, instead
435     * of thrown as an exception.
436     *<p>
437     * Out of the box, Java's transformers only support XSLT 1.0. See the S9
438     * example for more capabilities (at the cost of downloading the Saxon jar).
439     */
440    @Function(schema="javatest", implementor="postgresql_xml",
441              provides="prepareXMLTransform")
442    public static void prepareXMLTransform(String name, SQLXML source,
443        @SQLType(defaultValue="0") int how,
444        @SQLType(defaultValue="false") boolean enableExtensionFunctions,
445        @SQLType(defaultValue="true") boolean builtin,
446        @SQLType(defaultValue={}) ResultSet adjust)
447    throws SQLException
448    {
449        prepareXMLTransform(
450            name, source, how, enableExtensionFunctions, adjust, builtin,
451            /* withJava */ false);
452    }
453
454    /**
455     * Precompile an XSL transform {@code source} and save it (for the
456     * current session) as {@code name}, where the transform may call Java
457     * methods.
458     *<p>
459     * Otherwise identical to {@code prepareXMLTransform}, this version sets the
460     * {@code TransformerFactory}'s {@code extensionClassLoader} (to the context
461     * class loader, normally the PL/Java class path for the schema where this
462     * function is declared), so the transform will be able to use
463     * xalan's Java call syntax to call any public Java methods that would be
464     * accessible to this class. (That can make a big difference in usefulness
465     * for the otherwise rather limited XSLT 1.0.)
466     *<p>
467     * As with {@code enableExtensionFunctions}, failure by the transformer
468     * implementation to recognize or allow the {@code extensionClassLoader}
469     * property will be logged as a warning if {@code builtin} is {@code false},
470     * rather than thrown as an exception.
471     *<p>
472     * This example function will be installed with {@code EXECUTE} permission
473     * revoked from {@code PUBLIC}, as it essentially confers the ability to
474     * create arbitrary new Java functions, so should only be granted to roles
475     * you would be willing to grant {@code USAGE ON LANGUAGE java}.
476     *<p>
477     * Because this function only prepares the transform, and
478     * {@link #transformXML transformXML} applies it, there is some division of
479     * labor in determining what limits apply to its behavior. The use of this
480     * method instead of {@code prepareXMLTransform} determines whether the
481     * transform is allowed to see external Java methods at all; it will be
482     * the policy permissions granted to {@code transformXML} that control what
483     * those methods can do when the transform is applied. For now, that method
484     * is defined in the trusted/sandboxed {@code java} language, so this
485     * function could reasonably be granted to any role with {@code USAGE} on
486     * {@code java}. If, by contrast, {@code transformXML} were declared in the
487     * 'untrusted' {@code javaU}, it would be prudent to allow only superusers
488     * access to this function, just as only they can {@code CREATE FUNCTION} in
489     * an untrusted language.
490     */
491    @Function(schema="javatest", implementor="postgresql_xml",
492              provides="prepareXMLTransform")
493    public static void prepareXMLTransformWithJava(String name, SQLXML source,
494        @SQLType(defaultValue="0") int how,
495        @SQLType(defaultValue="false") boolean enableExtensionFunctions,
496        @SQLType(defaultValue="true") boolean builtin,
497        @SQLType(defaultValue={}) ResultSet adjust)
498    throws SQLException
499    {
500        prepareXMLTransform(
501            name, source, how, enableExtensionFunctions, adjust, builtin,
502            /* withJava */ true);
503    }
504
505    private static void prepareXMLTransform(String name, SQLXML source, int how,
506        boolean enableExtensionFunctions, ResultSet adjust, boolean builtin,
507        boolean withJava)
508    throws SQLException
509    {
510        TransformerFactory tf =
511            builtin
512            ? TransformerFactory.newDefaultInstance()
513            : TransformerFactory.newInstance();
514
515        String legacy_pfx = "http://www.oracle.com/xml/jaxp/properties/";
516        String java17_pfx = "jdk.xml.";
517        String exf_sfx = "enableExtensionFunctions";
518
519        String ecl_legacy = "jdk.xml.transform.extensionClassLoader";
520        String ecl_java17 = "jdk.xml.extensionClassLoader";
521
522        Source src = sxToSource(source, how, adjust);
523
524        try
525        {
526            Exception e;
527
528            e = setFirstSupported(tf::setFeature, enableExtensionFunctions,
529                List.of(TransformerConfigurationException.class), null,
530                java17_pfx + exf_sfx, legacy_pfx + exf_sfx);
531
532            if ( null != e )
533            {
534                if ( builtin )
535                    throw new SQLException(
536                        "Configuring XML transformation: " + e.getMessage(), e);
537                else
538                    logMessage("WARNING",
539                        "non-builtin transformer: ignoring " + e.getMessage());
540            }
541
542            if ( withJava )
543            {
544                e = setFirstSupported(tf::setAttribute,
545                    Thread.currentThread().getContextClassLoader(),
546                    List.of(IllegalArgumentException.class), null,
547                    ecl_java17, ecl_legacy);
548
549                if ( null != e )
550                {
551                    if ( builtin )
552                        throw new SQLException(
553                            "Configuring XML transformation: " + 
554                                e.getMessage(), e);
555                    else
556                        logMessage("WARNING",
557                            "non-builtin transformer: ignoring " + 
558                                e.getMessage());
559                }
560            }
561
562            s_tpls.put(name, tf.newTemplates(src));
563        }
564        catch ( TransformerException te )
565        {
566            throw new SQLException(
567                "Preparing XML transformation: " + te.getMessage(), te);
568        }
569    }
570
571    /**
572     * Transform some XML according to a named transform prepared with
573     * {@code prepareXMLTransform}.
574     *<p>
575     * Pass null for {@code transformName} to get a plain identity transform
576     * (not such an interesting thing to do, unless you also specify indenting).
577     */
578    @Function(schema="javatest", implementor="postgresql_xml",
579              provides="transformXML")
580    public static SQLXML transformXML(
581        String transformName, SQLXML source,
582        @SQLType(defaultValue="0") int howin,
583        @SQLType(defaultValue="0") int howout,
584        @SQLType(defaultValue={}) ResultSet adjust,
585        @SQLType(optional=true) Boolean indent,
586        @SQLType(optional=true) Integer indentWidth)
587    throws SQLException
588    {
589        Templates tpl = null == transformName? null: s_tpls.get(transformName);
590        Source src = sxToSource(source, howin, adjust);
591
592        if ( Boolean.TRUE.equals(indent)  &&  0 == howout )
593            howout = 4; // transformer only indents if writing a StreamResult
594
595        Connection c = DriverManager.getConnection("jdbc:default:connection");
596        SQLXML result = c.createSQLXML();
597        Result rlt = sxToResult(result, howout, adjust);
598
599        try
600        {
601            Transformer t =
602                null == tpl ? s_tf.newTransformer() : tpl.newTransformer();
603            /*
604             * For the non-SAX/StAX/DOM flavors of output, you're responsible
605             * for setting the Transformer to use the server encoding.
606             */
607            if ( rlt instanceof StreamResult )
608                t.setOutputProperty(ENCODING,
609                    System.getProperty("org.postgresql.server.encoding"));
610            else if ( Boolean.TRUE.equals(indent) )
611                logMessage("WARNING",
612                    "indent requested, but howout specifies a non-stream " +
613                    "Result type; no indenting will happen");
614
615            if ( null != indent )
616                t.setOutputProperty("indent", indent ? "yes" : "no");
617            if ( null != indentWidth )
618                t.setOutputProperty(
619                    "{http://xml.apache.org/xalan}indent-amount",
620                    "" + indentWidth);
621
622            t.transform(src, rlt);
623        }
624        catch ( TransformerException te )
625        {
626            throw new SQLException("Transforming XML: " + te.getMessage(), te);
627        }
628
629        return ensureClosed(rlt, result, howout);
630    }
631
632    /**
633     * Precompile a schema {@code source} in schema language {@code lang}
634     * and save it (for the current session) as {@code name}.
635     *<p>
636     * Each value of {@code how}, 1-7, selects a different way of presenting
637     * the {@code SQLXML} object to the schema parser.
638     *<p>
639     * The {@code lang} parameter is a URI that identifies a known schema
640     * language. The only language a Java runtime is required to support is
641     * W3C XML Schema 1.0, with URI {@code http://www.w3.org/2001/XMLSchema}.
642     */
643    @Function(schema="javatest", implementor="postgresql_xml")
644    public static void prepareXMLSchema(
645        String name, SQLXML source, String lang, int how)
646    throws SQLException
647    {
648        try
649        {
650            s_schemas.put(name,
651                SchemaFactory.newInstance(lang)
652                .newSchema(sxToSource(source, how)));
653        }
654        catch ( SAXException e )
655        {
656            throw new SQLException(
657                "failed to prepare schema: " + e.getMessage(), e);
658        }
659    }
660
661    private static SQLXML echoSQLXML(SQLXML sx, int howin, int howout)
662    throws SQLException
663    {
664        Connection c = DriverManager.getConnection("jdbc:default:connection");
665        SQLXML rx = c.createSQLXML();
666        Source src = sxToSource(sx, howin);
667        Result rlt = sxToResult(rx, howout);
668
669        try
670        {
671            Transformer t = s_tf.newTransformer();
672            /*
673             * For the non-SAX/StAX/DOM flavors of output, you're responsible
674             * for setting the Transformer to use the server encoding.
675             */
676            if ( howout < 5 )
677                t.setOutputProperty(ENCODING,
678                    System.getProperty("org.postgresql.server.encoding"));
679            t.transform(src, rlt);
680        }
681        catch ( TransformerException te )
682        {
683            throw new SQLException("XML transformation failed", te);
684        }
685
686        return ensureClosed(rlt, rx, howout);
687    }
688
689    /**
690     * Echo the XML parameter back, using lower-level manipulations than
691     * {@code echoXMLParameter}.
692     *<p>
693     * This illustrates how the simple use of {@code t.transform(src,rlt)}
694     * in {@code echoSQLXML} substitutes for a lot of fiddly case-by-case code,
695     * but when coding for a specific case, all the generality of {@code
696     * transform} may not be needed. It can be interesting to compare memory use
697     * when XML values are large.
698     *<p>
699     * This method has been revised to demonstrate, even for low-level
700     * manipulations, how much fiddliness can now be avoided through use of the
701     * {@link Adjusting.XML.SourceResult} class, and how to make adjustments to
702     * parsing restrictions by passing the optional row-typed parameter
703     * <em>adjust</em>, which defaults to an empty row. For example, passing
704     *<pre>
705     * adjust =&gt; (select a from
706     *            (true as allowdtd, true as expandentityreferences) as a)
707     *</pre>
708     * would allow a document that contains an internal DTD subset and uses
709     * entities defined there.
710     *<p>
711     * The older, pre-{@code SourceResult} code for doing low-level XML echo
712     * has been moved to the {@code oldSchoolLowLevelEcho} method below. It can
713     * still be exercised by calling this method, explicitly passing
714     * {@code adjust => NULL}.
715     */
716    @Function(schema="javatest", implementor="postgresql_xml",
717        provides="lowLevelXMLEcho")
718    public static SQLXML lowLevelXMLEcho(
719        SQLXML sx, int how, @SQLType(defaultValue={}) ResultSet adjust)
720    throws SQLException
721    {
722        Connection c = DriverManager.getConnection("jdbc:default:connection");
723        SQLXML rx = c.createSQLXML();
724
725        if ( null == adjust )
726            return oldSchoolLowLevelEcho(rx, sx, how);
727
728        Adjusting.XML.SourceResult axsr =
729            rx.setResult(Adjusting.XML.SourceResult.class);
730
731        switch ( how )
732        {
733        /*
734         * The first four cases all present the content as unparsed bytes or
735         * characters, so there is nothing to adjust on the source side.
736         */
737        case 1:
738            axsr.set(new StreamSource(sx.getBinaryStream()));
739            break;
740        case 2:
741            axsr.set(new StreamSource(sx.getCharacterStream()));
742            break;
743        case 3:
744            axsr.set(sx.getString());
745            break;
746        case 4:
747            axsr.set(sx.getSource(StreamSource.class));
748            break;
749        /*
750         * The remaining cases present the content in parsed form, and therefore
751         * may involve parsers that can be adjusted according to the supplied
752         * preferences.
753         */
754        case 5:
755            axsr.set(applyAdjustments(adjust,
756                sx.getSource(Adjusting.XML.SAXSource.class)));
757            break;
758        case 6:
759            axsr.set(applyAdjustments(adjust,
760                sx.getSource(Adjusting.XML.StAXSource.class)));
761            break;
762        case 7:
763            axsr.set(applyAdjustments(adjust,
764                sx.getSource(Adjusting.XML.DOMSource.class)));
765            break;
766        default:
767            throw new SQLDataException(
768                "how must be 1-7 for lowLevelXMLEcho", "22003");
769        }
770
771        /*
772         * Adjustments can also be applied to the SourceResult itself, where
773         * they will affect any implicitly-created parser used to verify or
774         * re-encode the content, if it was supplied in unparsed form.
775         */
776        return applyAdjustments(adjust, axsr).get().getSQLXML();
777    }
778
779    /**
780     * Apply adjustments (supplied as a row type with a named column for each
781     * desired adjustment and its value) to an instance of
782     * {@link Adjusting.XML.Parsing}.
783     *<p>
784     * Column names in the <em>adjust</em> row are case-insensitive versions of
785     * the method names in {@link Adjusting.XML.Parsing}, and the value of each
786     * column should be of the appropriate type (at present, boolean for all of
787     * them).
788     * @param adjust A row type as described above, possibly of no columns if no
789     * adjustments are wanted
790     * @param axp An instance of Adjusting.XML.Parsing
791     * @return axp, after applying any adjustments
792     */
793    public static <T extends Adjusting.XML.Parsing<? super T>>
794    T applyAdjustments(ResultSet adjust, T axp)
795    throws SQLException
796    {
797        ResultSetMetaData rsmd = adjust.getMetaData();
798        int n = rsmd.getColumnCount();
799
800        for ( int i = 1; i <= n; ++i )
801        {
802            String k = rsmd.getColumnLabel(i);
803            if ( "lax".equalsIgnoreCase(k) )
804                axp.lax(adjust.getBoolean(i));
805            else if ( "allowDTD".equalsIgnoreCase(k) )
806                axp.allowDTD(adjust.getBoolean(i));
807            else if ( "externalGeneralEntities".equalsIgnoreCase(k) )
808                axp.externalGeneralEntities(adjust.getBoolean(i));
809            else if ( "externalParameterEntities".equalsIgnoreCase(k) )
810                axp.externalParameterEntities(adjust.getBoolean(i));
811            else if ( "loadExternalDTD".equalsIgnoreCase(k) )
812                axp.loadExternalDTD(adjust.getBoolean(i));
813            else if ( "xIncludeAware".equalsIgnoreCase(k) )
814                axp.xIncludeAware(adjust.getBoolean(i));
815            else if ( "expandEntityReferences".equalsIgnoreCase(k) )
816                axp.expandEntityReferences(adjust.getBoolean(i));
817            else if ( "elementAttributeLimit".equalsIgnoreCase(k) )
818                axp.elementAttributeLimit(adjust.getInt(i));
819            else if ( "entityExpansionLimit".equalsIgnoreCase(k) )
820                axp.entityExpansionLimit(adjust.getInt(i));
821            else if ( "entityReplacementLimit".equalsIgnoreCase(k) )
822                axp.entityReplacementLimit(adjust.getInt(i));
823            else if ( "maxElementDepth".equalsIgnoreCase(k) )
824                axp.maxElementDepth(adjust.getInt(i));
825            else if ( "maxGeneralEntitySizeLimit".equalsIgnoreCase(k) )
826                axp.maxGeneralEntitySizeLimit(adjust.getInt(i));
827            else if ( "maxParameterEntitySizeLimit".equalsIgnoreCase(k) )
828                axp.maxParameterEntitySizeLimit(adjust.getInt(i));
829            else if ( "maxXMLNameLimit".equalsIgnoreCase(k) )
830                axp.maxXMLNameLimit(adjust.getInt(i));
831            else if ( "totalEntitySizeLimit".equalsIgnoreCase(k) )
832                axp.totalEntitySizeLimit(adjust.getInt(i));
833            else if ( "accessExternalDTD".equalsIgnoreCase(k) )
834                axp.accessExternalDTD(adjust.getString(i));
835            else if ( "accessExternalSchema".equalsIgnoreCase(k) )
836                axp.accessExternalSchema(adjust.getString(i));
837            else if ( "schema".equalsIgnoreCase(k) )
838            {
839                try
840                {
841                    axp.schema(s_schemas.get(adjust.getString(i)));
842                }
843                catch (UnsupportedOperationException e)
844                {
845                }
846            }
847            else
848                throw new SQLDataException(
849                    "unrecognized name \"" + k + "\" for parser adjustment",
850                    "22000");
851        }
852        return axp;
853    }
854
855    /**
856     * An obsolescent example, showing what was required to copy from one
857     * {@code SQLXML} object to another, using the various supported APIs,
858     * without using {@link Adjusting.XML.SourceResult}, or at least without
859     * using it much. It is still used in case 4 to be sure of getting a
860     * {@code StreamResult} that matches the byte-or-character-ness of the
861     * {@code StreamSource}. How to handle that case without
862     * {@code SourceResult} is left as an exercise.
863     */
864    private static SQLXML oldSchoolLowLevelEcho(SQLXML rx, SQLXML sx, int how)
865    throws SQLException
866    {
867        try
868        {
869            switch ( how )
870            {
871            case 1:
872                InputStream is = sx.getBinaryStream();
873                OutputStream os = rx.setBinaryStream();
874                shovelBytes(is, os);
875                break;
876            case 2:
877                Reader r = sx.getCharacterStream();
878                Writer w = rx.setCharacterStream();
879                shovelChars(r, w);
880                break;
881            case 3:
882                rx.setString(sx.getString());
883                break;
884            case 4:
885                StreamSource ss = sx.getSource(StreamSource.class);
886                Adjusting.XML.StreamResult sr =
887                    rx.setResult(Adjusting.XML.StreamResult.class);
888                is = ss.getInputStream();
889                r  = ss.getReader();
890                if ( null != is )
891                {
892                    os = sr.preferBinaryStream().get().getOutputStream();
893                    shovelBytes(is, os);
894                    break;
895                }
896                if ( null != r )
897                {
898                    w  = sr.preferCharacterStream().get().getWriter();
899                    shovelChars(r, w);
900                    break;
901                }
902                throw new SQLDataException(
903                    "StreamSource contained neither InputStream nor Reader");
904            case 5:
905                SAXSource sxs = sx.getSource(SAXSource.class);
906                SAXResult sxr = rx.setResult(SAXResult.class);
907                XMLReader xr  = sxs.getXMLReader();
908                if ( null == xr )
909                {
910                    SAXParserFactory spf = SAXParserFactory.newInstance();
911                    spf.setNamespaceAware(true);
912                    xr = spf.newSAXParser().getXMLReader();
913                    /*
914                     * Important: before copying this example code for another
915                     * use, consider whether the input XML might be untrusted.
916                     * If so, the new XMLReader created here should have several
917                     * features given safe default settings as outlined in the
918                     * OWASP guidelines. (This branch is not reached when sx is
919                     * a PL/Java native SQLXML instance, as xr will be non-null
920                     * and already configured.)
921                     */
922                }
923                ContentHandler ch = sxr.getHandler();
924                xr.setContentHandler(ch);
925                if ( ch instanceof DTDHandler )
926                    xr.setDTDHandler((DTDHandler)ch);
927                LexicalHandler lh = sxr.getLexicalHandler();
928                if ( null == lh  &&  ch instanceof LexicalHandler )
929                lh = (LexicalHandler)ch;
930                if ( null != lh )
931                    xr.setProperty(
932                        "http://xml.org/sax/properties/lexical-handler", lh);
933                xr.parse(sxs.getInputSource());
934                break;
935            case 6:
936                StAXSource sts = sx.getSource(StAXSource.class);
937                StAXResult str = rx.setResult(StAXResult.class);
938                XMLOutputFactory xof = XMLOutputFactory.newInstance();
939                /*
940                 * The Source has either an event reader or a stream reader. Use
941                 * the event reader directly, or create one around the stream
942                 * reader.
943                 */
944                XMLEventReader xer = sts.getXMLEventReader();
945                if ( null == xer )
946                {
947                    XMLInputFactory  xif = XMLInputFactory .newInstance();
948                    xif.setProperty(xif.IS_NAMESPACE_AWARE, true);
949                    /*
950                     * Important: before copying this example code for another
951                     * use, consider whether the input XML might be untrusted.
952                     * If so, the new XMLInputFactory created here might want
953                     * several properties given safe default settings as
954                     * outlined in the OWASP guidelines. (When sx is a PL/Java
955                     * native SQLXML instance, the XMLStreamReader obtained
956                     * below will already have been so configured.)
957                     */
958                    xer = xif.createXMLEventReader(sts.getXMLStreamReader());
959                }
960                /*
961                 * Were you thinking the above could be simply
962                 * createXMLEventReader(sts) by analogy with the writer below?
963                 * Good thought, but the XMLInputFactory implementation that's
964                 * included in OpenJDK doesn't implement the case where the
965                 * Source argument is a StAXSource! Two lines would do it.
966                 */
967                /*
968                 * Because of a regression in Java 9 and later, the line below,
969                 * while working in Java 8 and earlier, will produce a
970                 * ClassCastException in Java 9 through (for sure) 12, (almost
971                 * certainly) 13, and on until some future version fixes the
972                 * regression, if ever, if 'str' wraps any XMLStreamWriter
973                 * implementation other than the inaccessible one from the guts
974                 * of the JDK itself. The bug has been reported but (as of this
975                 * writing) is still in the maddening limbo phase of the Java
976                 * bug reporting cycle, where no bug number can refer to it. See
977                 * lowLevelXMLEcho() above for code to do this copy successfully
978                 * using an Adjusting.XML.SourceResult.
979                 */
980                XMLEventWriter xew = xof.createXMLEventWriter(str);
981                xew.add(xer);
982                xew.close();
983                xer.close();
984                break;
985            case 7:
986                DOMSource ds = sx.getSource(DOMSource.class);
987                DOMResult dr = rx.setResult(DOMResult.class);
988                dr.setNode(ds.getNode());
989                break;
990            default:
991                throw new SQLDataException(
992                    "how must be 1-7 for lowLevelXMLEcho", "22003");
993            }
994        }
995        catch ( IOException e )
996        {
997            throw new SQLException(
998                "IOException in lowLevelXMLEcho", "58030", e);
999        }
1000        catch (
1001            ParserConfigurationException | SAXException | XMLStreamException e )
1002        {
1003            throw new SQLException(
1004                "XML exception in lowLevelXMLEcho", "22000", e);
1005        }
1006        return rx;
1007    }
1008
1009    /**
1010     * Proxy a PL/Java SQLXML source object as if it were of a non-PL/Java
1011     * implementing class, to confirm that it can still be returned successfully
1012     * to PostgreSQL.
1013     * @param sx readable {@code SQLXML} object to proxy
1014     * @param how 1,2,4,5,6,7 determines what subclass of {@code Source} will be
1015     * returned by {@code getSource}.
1016     */
1017    @Function(schema="javatest", implementor="postgresql_xml",
1018              provides="proxiedXMLEcho")
1019    public static SQLXML proxiedXMLEcho(SQLXML sx, int how)
1020    throws SQLException
1021    {
1022        return new SQLXMLProxy(sx, how);
1023    }
1024
1025    /**
1026     * Supply a sequence of bytes to be the exact (encoded) content of an XML
1027     * value, which will be returned; if the encoding is not UTF-8, the value
1028     * should begin with an XML Decl that names the encoding.
1029     *<p>
1030     * Constructs an {@code SQLXML} instance that will return the supplied
1031     * content as a {@code StreamSource} wrapping an {@code InputStream}, or via
1032     * {@code getBinaryStream}, but fail if asked for any other form.
1033     */
1034    @Function(schema="javatest", implementor="postgresql_xml",
1035              provides="mockedXMLEchoB")
1036    public static SQLXML mockedXMLEcho(byte[] bytes)
1037    throws SQLException
1038    {
1039        return new SQLXMLMock(bytes);
1040    }
1041
1042    /**
1043     * Supply a sequence of characters to be the exact (Unicode) content of an
1044     * XML value, which will be returned; if the value begins with an XML Decl
1045     * that names an encoding, the content will be assumed to contain only
1046     * characters representable in that encoding.
1047     *<p>
1048     * Constructs an {@code SQLXML} instance that will return the supplied
1049     * content as a {@code StreamSource} wrapping a {@code Reader}, or via
1050     * {@code getCharacterStream}, but fail if asked for any other form.
1051     */
1052    @Function(schema="javatest", implementor="postgresql_xml",
1053              provides="mockedXMLEchoC")
1054    public static SQLXML mockedXMLEcho(String chars)
1055    throws SQLException
1056    {
1057        return new SQLXMLMock(chars);
1058    }
1059
1060    /**
1061     * Text-typed variant of lowLevelXMLEcho (does not require XML type).
1062     */
1063    @Function(schema="javatest", name="lowLevelXMLEcho",
1064        type="text")
1065    public static SQLXML lowLevelXMLEcho_(@SQLType("text") SQLXML sx, int how,
1066        @SQLType(defaultValue={}) ResultSet adjust)
1067    throws SQLException
1068    {
1069        return lowLevelXMLEcho(sx, how, adjust);
1070    }
1071
1072    /**
1073     * Low-level XML echo where the Java parameter and return type are String.
1074     */
1075    @Function(schema="javatest", implementor="postgresql_xml", type="xml")
1076    public static String lowLevelXMLEcho(@SQLType("xml") String x)
1077    throws SQLException
1078    {
1079        return x;
1080    }
1081
1082    /**
1083     * Create some XML, pass it to a {@code SELECT ?} prepared statement,
1084     * retrieve it from the result set, and return it via the out-parameter
1085     * result set of this {@code RECORD}-returning function.
1086     */
1087    @Function(schema="javatest", type="RECORD")
1088    public static boolean xmlInStmtAndRS(ResultSet out) throws SQLException
1089    {
1090        Connection c = DriverManager.getConnection("jdbc:default:connection");
1091        SQLXML x = c.createSQLXML();
1092        x.setString("<a/>");
1093        PreparedStatement ps = c.prepareStatement("SELECT ?");
1094        ps.setObject(1, x, Types.SQLXML);
1095        ResultSet rs = ps.executeQuery();
1096        rs.next();
1097        if ( Types.SQLXML != rs.getMetaData().getColumnType(1) )
1098            logMessage("WARNING",
1099                "ResultSetMetaData.getColumnType() misreports SQLXML");
1100        x = rs.getSQLXML(1);
1101        ps.close();
1102        out.updateObject(1, x);
1103        return true;
1104    }
1105
1106    /**
1107     * Test serialization into the PostgreSQL server encoding by returning
1108     * a text node, optionally wrapped in an element, containing the supplied
1109     * stuff.
1110     *<p>
1111     * The stuff is supplied as a {@code bytea} and a named <em>encoding</em>,
1112     * so it is easy to supply stuff that isn't in the server encoding and see
1113     * what the serializer does with it.
1114     *<p>
1115     * As of this writing, if the <em>stuff</em>, decoded according to
1116     * <em>encoding</em>, contains characters that are not representable in the
1117     * server encoding, the serializers supplied in the JRE will:
1118     *<ul>
1119     *<li>SAX, DOM: replace the character with a numeric character reference if
1120     * the node is wrapped in an element, but not outside of an element; there,
1121     * PL/Java ensures an {@code UnmappableCharacterException} is thrown, as the
1122     * serializer would otherwise silently lose information by replacing the
1123     * character with a {@code ?}.
1124     *<li>StAX: replace the character with a numeric character reference whether
1125     * wrapped in an element or not (outside of an element, this officially
1126     * violates the letter of the XML spec, but does not lose information, and
1127     * is closer to the spirit of SQL/XML with its {@code XML(CONTENT)} type).
1128     *</ul>
1129     * @param stuff Content to be used in the text node
1130     * @param encoding Name of an encoding; stuff will be decoded to Unicode
1131     * according to this encoding, and then serialized into the server encoding,
1132     * where possible.
1133     * @param how Integer specifying which XML API to test, like every other how
1134     * in this class; here the only valid choices are 5 (SAX), 6 (StAX), or
1135     * 7 (DOM).
1136     * @param inElement True if the text node should be wrapped in an element.
1137     * @return The resulting XML content.
1138     */
1139    @Function(schema="javatest", implementor="postgresql_xml")
1140    public static SQLXML xmlTextNode(
1141        byte[] stuff, String encoding, int how, boolean inElement)
1142        throws Exception
1143    {
1144        if ( 5 > how || how > 7 )
1145            throw new SQLDataException(
1146                "how must be 5-7 for xmlTextNode", "22003");
1147
1148        String stuffString = new String(stuff, encoding);
1149        Connection c = DriverManager.getConnection("jdbc:default:connection");
1150        SQLXML rx = c.createSQLXML();
1151
1152        switch ( how )
1153        {
1154        case 5:
1155            SAXResult sxr = rx.setResult(SAXResult.class);
1156            sxr.getHandler().startDocument();
1157            if ( inElement )
1158                sxr.getHandler().startElement("", "sax", "sax",
1159                    new AttributesImpl());
1160            sxr.getHandler().characters(
1161                stuffString.toCharArray(), 0, stuffString.length());
1162            if ( inElement )
1163                sxr.getHandler().endElement("", "sax", "sax");
1164            sxr.getHandler().endDocument();
1165            break;
1166        case 6:
1167            StAXResult stxr = rx.setResult(StAXResult.class);
1168            stxr.getXMLStreamWriter().writeStartDocument();
1169            if ( inElement )
1170                stxr.getXMLStreamWriter().writeStartElement("", "stax", "");
1171            stxr.getXMLStreamWriter().writeCharacters(stuffString);
1172            if ( inElement )
1173                stxr.getXMLStreamWriter().writeEndElement();
1174            stxr.getXMLStreamWriter().writeEndDocument();
1175            break;
1176        case 7:
1177            DOMResult dr = rx.setResult(DOMResult.class);
1178            /*
1179             * Why request features XML and Traversal?
1180             * If the only features requested are from the set
1181             * {Core, XML, LS} and maybe XPath, you get a brain-damaged
1182             * DOMImplementation that violates the org.w3c.dom.DOMImplementation
1183             * contract, as createDocument still tries to make a document
1184             * element even when passed null,null,null when, according to the
1185             * contract, it should not. To get the real implementation that
1186             * works, ask for some feature it supports outside of that core set.
1187             * I don't really need Traversal, but by asking for it, I get what
1188             * I do need.
1189             */
1190            Document d = DOMImplementationRegistry.newInstance()
1191                .getDOMImplementation("XML Traversal")
1192                .createDocument(null, null, null);
1193            DocumentFragment df = d.createDocumentFragment();
1194            ( inElement ? df.appendChild(d.createElement("dom")) : df )
1195                .appendChild(d.createTextNode(stuffString));
1196            dr.setNode(df);
1197            break;
1198        }
1199        return rx;
1200    }
1201
1202    /**
1203     * Create and leave some number of SQLXML objects unclosed, unused, and
1204     * unreferenced, as a test of reclamation.
1205     * @param howmany Number of SQLXML instances to create.
1206     * @param how If nonzero, the flavor of writing to request on the object
1207     * before abandoning it; if zero, it is left in its initial, writable state.
1208     */
1209    @Function(schema="javatest")
1210    public static void unclosedSQLXML(int howmany, int how) throws SQLException
1211    {
1212        Connection c = DriverManager.getConnection("jdbc:default:connection");
1213        while ( howmany --> 0 )
1214        {
1215            SQLXML sx = c.createSQLXML();
1216            if ( 0 < how )
1217                sxToResult(sx, how);
1218        }
1219    }
1220
1221
1222    /**
1223     * Return some instance of {@code Source} for reading an {@code SQLXML}
1224     * object, depending on the parameter {@code how}.
1225     *<p>
1226     * Note that this method always returns a {@code Source}, even for cases
1227     * 1 and 2 (obtaining readable streams directly from the {@code SQLXML}
1228     * object; this method wraps them in {@code Source}), and case 3
1229     * ({@code getString}; this method creates a {@code StringReader} and
1230     * returns it wrapped in a {@code Source}.
1231     */
1232    private static Source sxToSource(SQLXML sx, int how) throws SQLException
1233    {
1234        switch ( how )
1235        {
1236            case  1: return new StreamSource(sx.getBinaryStream());
1237            case  2: return new StreamSource(sx.getCharacterStream());
1238            case  3: return new StreamSource(new StringReader(sx.getString()));
1239            case  4: return     sx.getSource(StreamSource.class);
1240            case  5: return     sx.getSource(SAXSource.class);
1241            case  6: return     sx.getSource(StAXSource.class);
1242            case  7: return     sx.getSource(DOMSource.class);
1243            default: throw new SQLDataException("how should be 1-7", "22003");
1244        }
1245    }
1246
1247    /**
1248     * Return some instance of {@code Result} for writing an {@code SQLXML}
1249     * object, depending on the parameter {@code how}.
1250     *<p>
1251     * Note that this method always returns a {@code Result}, even for cases
1252     * 1 and 2 (obtaining writable streams directly from the {@code SQLXML}
1253     * object; this method wraps them in {@code Result}), and case 3
1254     * ({@code setString}; this method creates a {@code StringWriter} and
1255     * returns it wrapped in a {@code Result}.
1256     *<p>
1257     * In case 3, it will be necessary, after writing, to get the {@code String}
1258     * from the {@code StringWriter}, and call {@code setString} with it.
1259     */
1260    private static Result sxToResult(SQLXML sx, int how) throws SQLException
1261    {
1262        switch ( how )
1263        {
1264            case  1: return new StreamResult(sx.setBinaryStream());
1265            case  2: return new StreamResult(sx.setCharacterStream());
1266            case  3: return new StreamResult(new StringWriter());
1267            case  4: return     sx.setResult(StreamResult.class);
1268            case  5: return     sx.setResult(SAXResult.class);
1269            case  6: return     sx.setResult(StAXResult.class);
1270            case  7:
1271                DOMResult r = sx.setResult(DOMResult.class);
1272                allowFragment(r); // else it'll accept only DOCUMENT form
1273                return r;
1274            default: throw new SQLDataException("how should be 1-7", "22003");
1275        }
1276    }
1277
1278    /**
1279     * Return some instance of {@code Source} for reading an {@code SQLXML}
1280     * object, depending on the parameter {@code how}, applying any adjustments
1281     * in {@code adjust}.
1282     *<p>
1283     * Allows {@code how} to be zero, meaning to let the implementation choose
1284     * what kind of {@code Source} to present. Otherwise identical to the other
1285     * {@code sxToSource}.
1286     */
1287    private static Source sxToSource(SQLXML sx, int how, ResultSet adjust)
1288    throws SQLException
1289    {
1290        Source s;
1291        switch ( how )
1292        {
1293            case  0: s = sx.getSource(Adjusting.XML.Source.class); break;
1294            case  1:
1295            case  2:
1296            case  3:
1297            case  4:
1298                return sxToSource(sx, how); // no adjustments on a StreamSource
1299            case  5: s = sx.getSource(Adjusting.XML.SAXSource.class); break;
1300            case  6: s = sx.getSource(Adjusting.XML.StAXSource.class); break;
1301            case  7: s = sx.getSource(Adjusting.XML.DOMSource.class); break;
1302            default: throw new SQLDataException("how should be 0-7", "22003");
1303        }
1304
1305        if ( s instanceof Adjusting.XML.Source )
1306            return applyAdjustments(adjust, (Adjusting.XML.Source<?>)s).get();
1307        return s;
1308    }
1309
1310    /**
1311     * Return some instance of {@code Result} for writing an {@code SQLXML}
1312     * object, depending on the parameter {@code how} applying any adjustments
1313     * in {@code adjust}.
1314     *<p>
1315     * Allows {@code how} to be zero, meaning to let the implementation choose
1316     * what kind of {@code Result} to present. Otherwise identical to the other
1317     * {@code sxToResult}.
1318     */
1319    private static Result sxToResult(SQLXML sx, int how, ResultSet adjust)
1320    throws SQLException
1321    {
1322        Result r;
1323        switch ( how )
1324        {
1325            case  1: // you might wish you could adjust a raw BinaryStream
1326            case  2: // or CharacterStream
1327            case  3: // or String, but you can't. Ask for a StreamResult.
1328            case  5: // SAXResult needs no adjustment
1329            case  6: // StAXResult needs no adjustment
1330            case  7: // DOMResult needs no adjustment
1331                return sxToResult(sx, how);
1332            case  4: r = sx.setResult(Adjusting.XML.StreamResult.class); break;
1333            case  0: r = sx.setResult(Adjusting.XML.Result.class); break;
1334            default: throw new SQLDataException("how should be 0-7", "22003");
1335        }
1336
1337        if ( r instanceof Adjusting.XML.Result )
1338            return applyAdjustments(adjust, (Adjusting.XML.Result<?>)r).get();
1339        return r;
1340    }
1341
1342    /**
1343     * Ensure the closing of whatever method was used to add content to
1344     * an {@code SQLXML} object.
1345     *<p>
1346     * Before a {@code SQLXML} object that has been written to can be used by
1347     * PostgreSQL (returned as a function result, plugged in as a prepared
1348     * statement parameter or into a {@code ResultSet}, etc.), the method used
1349     * for writing it must be "closed" to ensure the writing is complete.
1350     *<p>
1351     * If it is set with {@link SQLXML#setString setString}, nothing more is
1352     * needed; {@code setString} obviously sets the whole value at once. Any
1353     * {@code OutputStream} or {@code Writer} obtained from
1354     * {@link SQLXML#setBinaryStream setBinaryStream} or
1355     * {@link SQLXML#setCharacterStream setCharacterStream}, or from
1356     * {@link SQLXML#setResult setResult}{@code (StreamResult.class)}, has to be
1357     * explicitly closed (a {@link Transformer} does not close its
1358     * {@link Result} when the transformation is complete!).
1359     * Those are cases 1, 2, and 4 here.
1360     *<p>
1361     * Cases 5 ({@code SAXResult}) and 6 ({@code StAXResult}) need no special
1362     * attention; though the {@code Transformer} does not close them, the ones
1363     * returned by this {@code SQLXML} implementation are set up to close
1364     * themselves when the {@code endDocument} event is written.
1365     *<p>
1366     * Case 3 (test of {@code setString} is handled specially here. As this
1367     * class allows testing of all techniques for writing the {@code SQLXML}
1368     * object, and most of those involve a {@code Result}, case 3 is handled
1369     * by also constructing a {@code Result} over a {@link StringWriter} and
1370     * having the content written into that; this method then extracts the
1371     * content from the {@code StringWriter} and passes it to {@code setString}.
1372     * For cases 1 and 2, likewise, the stream obtained with
1373     * {@code getBinaryStream} or {@code getCharacterStream} has been wrapped in
1374     * a {@code Result} for generality in this example.
1375     *<p>
1376     * A typical application will not need the generality seen here; it
1377     * will usually know which technique it is using to write the {@code SQLXML}
1378     * object, and only needs to know how to close that if it needs closing.
1379     * @param r The {@code Result} onto which writing was done.
1380     * @param sx The {@code SQLXML} object being written.
1381     * @param how The integer used in this example class to select which method
1382     * of writing the {@code SQLXML} object was to be tested.
1383     * @return The {@code SQLXML} object {@code sx}, because why not?
1384     */
1385    public static SQLXML ensureClosed(Result r, SQLXML sx, int how)
1386    throws SQLException
1387    {
1388        switch ( how )
1389        {
1390        case 1:
1391        case 2:
1392        case 4:
1393            StreamResult sr = (StreamResult)r;
1394            OutputStream os = sr.getOutputStream();
1395            Writer w = sr.getWriter();
1396            try
1397            {
1398                if ( null != os )
1399                    os.close();
1400                if ( null != w )
1401                    w.close();
1402            }
1403            catch ( IOException ioe )
1404            {
1405                throw new SQLException(
1406                    "Failure closing SQLXML result", "XX000");
1407            }
1408            break;
1409        case 3:
1410            StringWriter sw = (StringWriter)((StreamResult)r).getWriter();
1411            String s = sw.toString();
1412            sx.setString(s);
1413            break;
1414        }
1415        return sx;
1416    }
1417
1418    /**
1419     * Configure a {@code DOMResult} to accept {@code CONTENT} (a/k/a
1420     * document fragment), not only the more restrictive {@code DOCUMENT}.
1421     *<p>
1422     * The other forms of {@code Result} that can be requested will happily
1423     * accept {@code XML(CONTENT)} and not just {@code XML(DOCUMENT)}.
1424     * The {@code DOMResult} is pickier, however: if you first call
1425     * {@link DOMResult#setNode setNode} with a {@code DocumentFragment}, it
1426     * will accept either form, but if you leave the node unset when passing the
1427     * {@code DOMResult} to a transformer, the transformer will default to
1428     * putting a {@code Document} node there, and then it will not accept a
1429     * fragment.
1430     *<p>
1431     * If you need to handle fragments, this method illustrates how to pre-load
1432     * the {@code DOMResult} with an empty {@code DocumentFragment}. Note that
1433     * if you use some XML processing package that supplies its own classes
1434     * implementing DOM nodes, you may need to use a {@code DocumentFragment}
1435     * instance obtained from that package.
1436     */
1437    public static void allowFragment(DOMResult r) throws SQLException
1438    {
1439        try
1440        {
1441            r.setNode(DocumentBuilderFactory.newInstance()
1442                .newDocumentBuilder().newDocument()
1443                    .createDocumentFragment());
1444        }
1445        catch ( ParserConfigurationException pce )
1446        {
1447            throw new SQLException("Failed initializing DOMResult", pce);
1448        }
1449    }
1450
1451    private static void shovelBytes(InputStream is, OutputStream os)
1452    throws IOException
1453    {
1454        byte[] b = new byte[8192];
1455        int got;
1456        while ( -1 != (got = is.read(b)) )
1457            os.write(b, 0, got);
1458        is.close();
1459        os.close();
1460    }
1461
1462    private static void shovelChars(Reader r, Writer w)
1463    throws IOException
1464    {
1465        char[] b = new char[8192];
1466        int got;
1467        while ( -1 != (got = r.read(b)) )
1468            w.write(b, 0, got);
1469        r.close();
1470        w.close();
1471    }
1472
1473    /**
1474     * Test the MappedUDT (in one direction anyway).
1475     *<p>
1476     * Creates a {@code PassXML} object, the Java class that maps the
1477     * {@code javatest.onexml} composite type, which has one member, of XML
1478     * type. Stores a {@code SQLXML} value in that field of the {@code PassXML}
1479     * object, and passes that to an SQL query that expects and returns
1480     * {@code javatest.onexml}. Retrieves the XML from the value field of the
1481     * {@code PassXML} object created to map the result of the query.
1482     * @return The original XML value, if all goes well.
1483     */
1484    @Function(schema="javatest", implementor="postgresql_xml")
1485    public static SQLXML xmlFromComposite() throws SQLException
1486    {
1487        Connection c = DriverManager.getConnection("jdbc:default:connection");
1488        PreparedStatement ps =
1489            c.prepareStatement("SELECT CAST(? AS javatest.onexml)");
1490        SQLXML x = c.createSQLXML();
1491        x.setString("<a/>");
1492        PassXML obj = new PassXML();
1493        obj.m_value = x;
1494        obj.m_typeName = "javatest.onexml";
1495        ps.setObject(1, obj);
1496        ResultSet r = ps.executeQuery();
1497        r.next();
1498        obj = (PassXML)r.getObject(1);
1499        ps.close();
1500        return obj.m_value;
1501    }
1502
1503    /*
1504     * Required to serve as a MappedUDT:
1505     */
1506    /**
1507     * No-arg constructor required of objects that will implement
1508     * {@link SQLData}.
1509     */
1510    public PassXML() { }
1511
1512    private String m_typeName;
1513    private SQLXML m_value;
1514
1515    @Override
1516    public String getSQLTypeName() { return m_typeName; }
1517
1518    @Override
1519    public void readSQL(SQLInput stream, String typeName) throws SQLException
1520    {
1521        m_typeName = typeName;
1522        m_value = (SQLXML) stream.readObject();
1523    }
1524
1525    @Override
1526    public void writeSQL(SQLOutput stream) throws SQLException
1527    {
1528        stream.writeSQLXML(m_value);
1529    }
1530
1531    /**
1532     * Class that will proxy methods to another {@code SQLXML} class.
1533     *<p>
1534     * Used for testing the PL/Java can accept input for PostgreSQL from an
1535     * {@code SQLXML} object not of its own implementation (for example, one
1536     * obtained from a different JDBC driver from some other database).
1537     *<p>
1538     * Only the {@code getSource} method is specially treated, to allow
1539     * exercising the various flavors of source.
1540     */
1541    public static class SQLXMLProxy implements SQLXML
1542    {
1543        private SQLXML m_sx;
1544        private int m_how;
1545
1546        public SQLXMLProxy(SQLXML sx, int how)
1547        {
1548            if ( null == sx )
1549                throw new NullPointerException("Null SQLXMLProxy target");
1550            if ( 1 > how  ||  how > 7  ||  how == 3 )
1551                throw new IllegalArgumentException(
1552                    "\"how\" must be 1, 2, 4, 5, 6, or 7");
1553            m_sx = sx;
1554            m_how = how;
1555        }
1556
1557        @Override
1558        public void free() throws SQLException { m_sx.free(); }
1559
1560        @Override
1561        public InputStream getBinaryStream() throws SQLException
1562        {
1563            return m_sx.getBinaryStream();
1564        }
1565
1566        @Override
1567        public OutputStream setBinaryStream() throws SQLException
1568        {
1569            return m_sx.setBinaryStream();
1570        }
1571
1572        @Override
1573        public Reader getCharacterStream() throws SQLException
1574        {
1575            return m_sx.getCharacterStream();
1576        }
1577
1578        @Override
1579        public Writer setCharacterStream() throws SQLException
1580        {
1581            return m_sx.setCharacterStream();
1582        }
1583
1584        @Override
1585        public String getString() throws SQLException
1586        {
1587            return m_sx.getString();
1588        }
1589
1590        @Override
1591        public void setString(String value) throws SQLException
1592        {
1593            m_sx.setString(value);
1594        }
1595
1596        @Override
1597        @SuppressWarnings("unchecked") // all the fun's when sourceClass is null
1598        public <T extends Source> T getSource(Class<T> sourceClass)
1599        throws SQLException
1600        {
1601            if ( null == sourceClass )
1602            {
1603                switch ( m_how )
1604                {
1605                case 1:
1606                    return (T)new StreamSource(m_sx.getBinaryStream());
1607                case 2:
1608                    return (T)new StreamSource(m_sx.getCharacterStream());
1609                case 4:
1610                    sourceClass = (Class<T>)StreamSource.class;
1611                    break;
1612                case 5:
1613                    sourceClass = (Class<T>)SAXSource.class;
1614                    break;
1615                case 6:
1616                    sourceClass = (Class<T>)StAXSource.class;
1617                    break;
1618                case 7:
1619                    sourceClass = (Class<T>)DOMSource.class;
1620                    break;
1621                }
1622            }
1623            return m_sx.getSource(sourceClass);
1624        }
1625
1626        @Override
1627        public <T extends Result> T setResult(Class<T> resultClass)
1628        throws SQLException
1629        {
1630            return m_sx.setResult(resultClass);
1631        }
1632    }
1633
1634    /**
1635     * Class that will mock an {@code SQLXML} instance, returning only binary or
1636     * character stream data from a byte array or string supplied at
1637     * construction.
1638     */
1639    public static class SQLXMLMock implements SQLXML
1640    {
1641        private String m_chars;
1642        private byte[] m_bytes;
1643
1644        public SQLXMLMock(String content)
1645        {
1646            if ( null == content )
1647                throw new NullPointerException("Null SQLXMLMock content");
1648            m_chars = content;
1649        }
1650
1651        public SQLXMLMock(byte[] content)
1652        {
1653            if ( null == content )
1654                throw new NullPointerException("Null SQLXMLMock content");
1655            m_bytes = content;
1656        }
1657
1658        @Override
1659        public void free() throws SQLException { }
1660
1661        @Override
1662        public InputStream getBinaryStream() throws SQLException
1663        {
1664            if ( null != m_bytes )
1665                return new ByteArrayInputStream(m_bytes);
1666            throw new UnsupportedOperationException(
1667                "SQLXMLMock.getBinaryStream");
1668        }
1669
1670        @Override
1671        public OutputStream setBinaryStream() throws SQLException
1672        {
1673            throw new UnsupportedOperationException(
1674                "SQLXMLMock.setBinaryStream");
1675        }
1676
1677        @Override
1678        public Reader getCharacterStream() throws SQLException
1679        {
1680            if ( null != m_chars )
1681                return new StringReader(m_chars);
1682            throw new UnsupportedOperationException(
1683                "SQLXMLMock.getCharacterStream");
1684        }
1685
1686        @Override
1687        public Writer setCharacterStream() throws SQLException
1688        {
1689            throw new UnsupportedOperationException(
1690                "SQLXMLMock.setCharacterStream");
1691        }
1692
1693        @Override
1694        public String getString() throws SQLException
1695        {
1696            if ( null != m_chars )
1697                return m_chars;
1698            throw new UnsupportedOperationException(
1699                "SQLXMLMock.getString");
1700        }
1701
1702        @Override
1703        public void setString(String value) throws SQLException
1704        {
1705            throw new UnsupportedOperationException(
1706                "SQLXMLMock.setString");
1707        }
1708
1709        @Override
1710        @SuppressWarnings("unchecked") // sourceClass==StreamSource is verified
1711        public <T extends Source> T getSource(Class<T> sourceClass)
1712        throws SQLException
1713        {
1714            if ( null != sourceClass && StreamSource.class != sourceClass )
1715                throw new UnsupportedOperationException(
1716                    "SQLXMLMock.getSource(" + sourceClass.getName() + ")");
1717            if ( null != m_chars )
1718                return (T) new StreamSource(new StringReader(m_chars));
1719            return (T) new StreamSource(new ByteArrayInputStream(m_bytes));
1720        }
1721
1722        @Override
1723        public <T extends Result> T setResult(Class<T> resultClass)
1724        throws SQLException
1725        {
1726            throw new UnsupportedOperationException(
1727                "SQLXMLMock.setResult");
1728        }
1729    }
1730}