/
S9.java
3367 lines (3098 loc) · 125 KB
/
S9.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
* Copyright (c) 2018-2020 Tada AB and other contributors, as listed below.
*
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the The BSD 3-Clause License
* which accompanies this distribution, and is available at
* http://opensource.org/licenses/BSD-3-Clause
*
* Contributors:
* Chapman Flack
*/
package org.postgresql.pljava.example.saxon;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import static java.sql.ResultSetMetaData.columnNoNulls;
import java.sql.SQLXML;
import java.sql.Statement;
import java.sql.Types;
import java.sql.SQLException;
import java.sql.SQLDataException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.SQLNonTransientException;
import java.sql.SQLSyntaxErrorException;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.OffsetTime;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import static java.time.ZoneOffset.UTC;
import static java.util.Arrays.asList;
import static java.util.Arrays.fill;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.xml.transform.Source;
import javax.xml.transform.Result;
import static javax.xml.XMLConstants.W3C_XML_SCHEMA_NS_URI;
import static javax.xml.XMLConstants.XML_NS_URI;
import static javax.xml.XMLConstants.XML_NS_PREFIX;
import static javax.xml.XMLConstants.XMLNS_ATTRIBUTE_NS_URI;
import static javax.xml.XMLConstants.XMLNS_ATTRIBUTE;
import net.sf.saxon.event.Receiver;
import net.sf.saxon.lib.ConversionRules;
import net.sf.saxon.lib.NamespaceConstant;
import static net.sf.saxon.om.NameChecker.isValidNCName;
import net.sf.saxon.query.StaticQueryContext;
import net.sf.saxon.regex.RegexIterator;
import net.sf.saxon.regex.RegularExpression;
import net.sf.saxon.s9api.Destination;
import net.sf.saxon.s9api.DocumentBuilder;
import net.sf.saxon.s9api.ItemType;
import net.sf.saxon.s9api.ItemTypeFactory;
import net.sf.saxon.s9api.OccurrenceIndicator;
import net.sf.saxon.s9api.Processor;
import net.sf.saxon.s9api.QName;
import net.sf.saxon.s9api.SAXDestination;
import net.sf.saxon.s9api.SequenceType;
import static net.sf.saxon.s9api.SequenceType.makeSequenceType;
import net.sf.saxon.s9api.XdmAtomicValue;
import static net.sf.saxon.s9api.XdmAtomicValue.makeAtomicValue;
import net.sf.saxon.s9api.XdmEmptySequence;
import net.sf.saxon.s9api.XdmItem;
import net.sf.saxon.s9api.XdmNode;
import static net.sf.saxon.s9api.XdmNodeKind.DOCUMENT;
import net.sf.saxon.s9api.XdmValue;
import net.sf.saxon.s9api.XdmSequenceIterator;
import net.sf.saxon.s9api.XQueryCompiler;
import net.sf.saxon.s9api.XQueryEvaluator;
import net.sf.saxon.s9api.XQueryExecutable;
import net.sf.saxon.s9api.SaxonApiException;
import net.sf.saxon.trans.XPathException;
import net.sf.saxon.serialize.SerializationProperties;
import net.sf.saxon.type.AtomicType;
import net.sf.saxon.type.Converter;
import net.sf.saxon.value.AtomicValue;
import net.sf.saxon.value.Base64BinaryValue;
import net.sf.saxon.value.CalendarValue;
import net.sf.saxon.value.HexBinaryValue;
import net.sf.saxon.value.StringValue;
import static net.sf.saxon.value.StringValue.getStringLength;
import org.postgresql.pljava.ResultSetProvider;
import org.postgresql.pljava.annotation.Function;
import org.postgresql.pljava.annotation.SQLType;
import static org.postgresql.pljava.annotation.Function.OnNullInput.CALLED;
/* For the xmltext function, which only needs plain SAX and not Saxon */
import javax.xml.transform.sax.SAXResult;
import org.xml.sax.ContentHandler;
import org.xml.sax.SAXException;
/**
* Class illustrating use of XQuery with Saxon as the
* implementation, using its native "s9api".
*<p>
* Supplies alternative, XML Query-based (as the SQL/XML standard dictates)
* implementation of some of SQL/XML, where the implementation in core
* PostgreSQL is limited to the capabilities of XPath (and XPath 1.0, at that).
*<p>
* Without the syntatic sugar built into the core PostgreSQL parser, calls to
* a function in this class can look a bit more verbose in SQL, but reflect a
* straightforward rewriting from the standard syntax. For example, suppose
* there is a table {@code catalog_as_xml} with a single row whose {@code x}
* column is a (respectably sized) XML document recording the stuff in
* {@code pg_catalog}. It could be created like this:
*<pre>
* CREATE TABLE catalog_as_xml(x) AS
* SELECT schema_to_xml('pg_catalog', false, true, '');
*</pre>
*<h2>Functions/predicates from ISO 9075-14 SQL/XML</h2>
*<h3>XMLQUERY</h3>
*<p>
* In the syntax of the SQL/XML standard, here is a query that would return
* an XML element representing the declaration of a function with a specified
* name:
*<pre>
* SELECT XMLQUERY('/pg_catalog/pg_proc[proname eq $FUNCNAME]'
* PASSING BY VALUE x, 'numeric_avg' AS FUNCNAME
* RETURNING CONTENT EMPTY ON EMPTY)
* FROM catalog_as_xml;
*</pre>
*<p>
* It binds the 'context item' of the query to {@code x}, and the {@code NAME}
* parameter to the given value, then evaluates the query and returns XML
* "CONTENT" (a tree structure with a document node at the root, but not
* necessarily meeting all the requirements of an XML "DOCUMENT"). It can be
* rewritten as this call to the {@link #xq_ret_content xq_ret_content} method:
*<pre>
* SELECT javatest.xq_ret_content('/pg_catalog/pg_proc[proname eq $FUNCNAME]',
* PASSING => p, nullOnEmpty => false)
* FROM catalog_as_xml,
* LATERAL (SELECT x AS ".", 'numeric_avg' AS "FUNCNAME") AS p;
*</pre>
*<p>
* In the rewritten form, the form of result wanted ({@code RETURNING CONTENT})
* is implicit in the called function name ({@code xq_ret_content}), and the
* parameters to pass to the query are moved out to a separate {@code SELECT}
* that supplies their values, types, and names (with the context item now given
* the name ".") and is passed by its alias into the query function.
*<p>
* Because of an unconditional uppercasing that PL/Java's JDBC driver currently
* applies to column names, any parameter names, such as {@code FUNCNAME} above,
* must be spelled in uppercase where used in the XQuery text, or they will not
* be recognized. Because the unconditional uppercasing is highly likely to be
* dropped in a future PL/Java release, it is wisest until then to use only
* parameter names that really are uppercase, both in the XQuery text where they
* are used and in the SQL expression that supplies them. In PostgreSQL,
* identifiers that are not quoted are <em>lower</em>cased, so they must be both
* uppercase and quoted, in the SQL syntax, to be truly uppercase.
*<p>
* In the standard, parameters and results (of XML types) can be passed
* {@code BY VALUE} or {@code BY REF}, where the latter means that the same
* nodes will retain their XQuery node identities over calls (note that this is
* a meaning unrelated to what "by value" and "by reference" usually mean in
* PostgreSQL's documentation). PostgreSQL's implementation of the XML type
* provides no way for {@code BY REF} semantics to be implemented, so everything
* happening here happens {@code BY VALUE} implicitly, and does not need to be
* specified.
*<h3>XMLEXISTS</h3>
*<p>
* The function {@link #xmlexists xmlexists} here implements the
* standard function of the same name. Because it is the same name, it has to
* be either schema-qualified or double-quoted in a call to avoid confusion
* with the reserved word. In the syntax of the SQL/XML standard, here is a
* query returning a boolean value indicating whether a function with the
* specified name is declared:
*<pre>
* SELECT XMLEXISTS('/pg_catalog/pg_proc[proname eq $FUNCNAME]'
* PASSING BY VALUE x, 'numeric_avg' AS FUNCNAME)
* FROM catalog_as_xml;
*</pre>
*<p>
* It can be rewritten as this call to the {@link #xmlexists xmlexists} method:
*<pre>
* SELECT "xmlexists"('/pg_catalog/pg_proc[proname eq $FUNCNAME]',
* PASSING => p)
* FROM catalog_as_xml,
* LATERAL (SELECT x AS ".", 'numeric_avg' AS "FUNCNAME") AS p;
*</pre>
*<h3>XMLTABLE</h3>
*<p>
* The function {@link #xmltable xmltable} here implements (much of) the
* standard function of the same name. Because it is the same name, it has to
* be either schema-qualified or double-quoted in a call to avoid confusion
* with the reserved word. A rewritten form of the <a href=
'https://www.postgresql.org/docs/10/static/functions-xml.html#FUNCTIONS-XML-PROCESSING-XMLTABLE'
>first example in the PostgreSQL manual</a> could be:
*<pre>
* SELECT xmltable.*
* FROM
* xmldata,
*
* LATERAL (SELECT data AS ".", 'not specified'::text AS "DPREMIER") AS p,
*
* "xmltable"('//ROWS/ROW', PASSING => p, COLUMNS => ARRAY[
* 'data(@id)', null, 'COUNTRY_NAME',
* 'COUNTRY_ID', 'SIZE[@unit eq "sq_km"]',
* 'concat(SIZE[@unit ne "sq_km"], " ", SIZE[@unit ne "sq_km"]/@unit)',
* 'let $e := PREMIER_NAME
* return if ( empty($e) )then $DPREMIER else $e'
* ]) AS (
* id int, ordinality int8, "COUNTRY_NAME" text, country_id text,
* size_sq_km float, size_other text, premier_name text
* );
*</pre>
*<p>
* In the first column expression, without the {@code data()} function, the
* result would be a bare attribute node (one not enclosed in an XML element).
* Many implementations will accept a bare attribute as a column expression
* result, and simply assume the attribute's value is wanted, but it appears
* that a strict implementation of the spec must raise {@code err:XPTY0004} in
* such a case. This implementation is meant to be strict, so the attribute is
* wrapped in {@code data()} to extract and return its value. (See
* "About bare attribute nodes" in {@link #assignRowValues assignRowValues}
* for more explanation.)
*<p>
* The {@code DPREMIER} parameter passed from SQL to the XQuery expression is
* spelled in uppercase (and also, in the SQL expression supplying it, quoted),
* for the reasons explained above for the {@code xq_ret_content} function.
*<h3>XMLCAST</h3>
*<p>
* An ISO standard cast expression like
*<pre>
* XMLCAST(v AS wantedtype)
*</pre>
* can be rewritten with this idiom and the {@link #xmlcast xmlcast} function
* provided here:
*<pre>
* (SELECT r FROM (SELECT v) AS o, xmlcast(o) AS (r wantedtype))
*</pre>
*<h2>XQuery regular-expression functions in ISO 9075-2 Foundations</h2>
* The methods {@link #like_regex like_regex},
* {@link #occurrences_regex occurrences_regex},
* {@link #position_regex position_regex},
* {@link #substring_regex substring_regex}, and
* {@link #translate_regex translate_regex} provide, with slightly altered
* syntax, the ISO SQL predicate and functions of the same names.
*<p>
* For the moment, they will only match newlines in the way W3C XQuery
* specifies, not in the more-flexible Unicode-compatible way ISO SQL specifies,
* and for the ones where ISO SQL allows {@code USING CHARACTERS} or
* {@code USING OCTETS}, only {@code USING CHARACTERS} will work.
*<h2>Extensions</h2>
*<h3>XQuery module prolog allowed</h3>
*<p>
* Where any function here accepts an XQuery
*<a href='https://www.w3.org/TR/xquery-31/#id-expressions'
*>"expression"</a> according to the SQL specification, in fact an XQuery
*<a href='https://www.w3.org/TR/xquery-31/#dt-main-module'
*>"main module"</a> will be accepted. Therefore, the query can be preceded by
* a prolog declaring namespaces, options, local variables and functions, etc.
*<h3>Saxon extension to XQuery regular expressions</h3>
*<p>
* Saxon's implementation of XQuery regular expressions will accept a
* nonstandard <em>flag</em> string ending with {@code ;j} to use Java regular
* expressions rather than XQuery ones. That extension is available in the
* XQuery regular-expression methods provided here.
* @author Chapman Flack
*/
public class S9 implements ResultSetProvider.Large
{
private S9(
XdmSequenceIterator<XdmItem> xsi,
XQueryEvaluator[] columnXQEs,
SequenceType[] columnStaticTypes,
XMLBinary enc)
{
m_sequenceIterator = xsi;
m_columnXQEs = columnXQEs;
m_columnStaticTypes = columnStaticTypes;
m_atomize = new AtomizingFunction [ columnStaticTypes.length ];
m_xmlbinary = enc;
}
final XdmSequenceIterator<XdmItem> m_sequenceIterator;
final XQueryEvaluator[] m_columnXQEs;
final SequenceType[] m_columnStaticTypes;
final SequenceType s_01untypedAtomic = makeSequenceType(
ItemType.UNTYPED_ATOMIC, OccurrenceIndicator.ZERO_OR_ONE);
final AtomizingFunction[] m_atomize;
final XMLBinary m_xmlbinary;
Binding.Assemblage m_outBindings;
static final Connection s_dbc;
static final Processor s_s9p = new Processor(false);
static final ItemTypeFactory s_itf = new ItemTypeFactory(s_s9p);
static final Pattern s_intervalSigns;
static final Pattern s_intervalSignSite;
enum XMLBinary { HEX, BASE64 };
enum Nulls { ABSENT, NIL };
static
{
try
{
s_dbc = DriverManager.getConnection("jdbc:default:connection");
/*
* XML Schema thinks an ISO 8601 duration must have no sign
* anywhere but at the very beginning before the P. PostgreSQL
* thinks that's the one place a sign must never be, and instead
* it should appear in front of every numeric field. (PostgreSQL
* accepts input where the signs vary, and there are cases where it
* cannot be normalized away: P1M-1D is a thing, and can't be
* simplified until anchored at a date to know how long the month
* is! The XML Schema type simply can't represent that, so mapping
* of such a value must simply fail, as we'll ensure below.)
* So, here's a regex with a capturing group for a leading -, and
* one for any field-leading -, and one for the absence of a field-
* leading -. Any PostgreSQL or XS duration ought to match overall,
* but the capturing group matches should be either (f,f,t) or
* (f,t,f) for a PostgreSQL duration, or either (f,f,t) or (t,f,t)
* for an XS duration. (f,t,t) would be a PostgreSQL interval with
* mixed signs, and inconvertible.
*/
s_intervalSigns = Pattern.compile(
"(-)?+(?:[PYMWDTH](?:(?:(-)|())\\d++)?+)++(?:(?:[.,]\\d*+)?+S)?+");
/*
* To convert from the leading-sign form, need to find every spot
* where a digit follows a [PYMWDTH] to insert a - there.
*/
s_intervalSignSite = Pattern.compile("(?<=[PYMWDTH])(?=\\d)");
}
catch ( SQLException e )
{
throw new ExceptionInInitializerError(e);
}
}
static class PredefinedQueryHolders
{
static final XQueryCompiler s_xqc = s_s9p.newXQueryCompiler();
static final QName s_qEXPR = new QName("EXPR");
static class DocumentWrapUnwrap
{
static final XQueryExecutable INSTANCE;
static
{
try
{
INSTANCE = s_xqc.compile(
"declare construction preserve;" +
"declare variable $EXPR as item()* external;" +
"data(document{$EXPR}/child::node())");
}
catch ( SaxonApiException e )
{
throw new ExceptionInInitializerError(e);
}
}
}
}
/**
* PostgreSQL (as of 12) lacks the XMLTEXT function, so here it is.
*<p>
* As long as PostgreSQL does not have the {@code XML(SEQUENCE)} type,
* this can only be the {@code XMLTEXT(sve RETURNING CONTENT)} flavor, which
* does create a text node with {@code sve} as its value, but returns the
* text node wrapped in a document node.
*<p>
* This function doesn't actually require Saxon, but otherwise fits in with
* the theme here, implementing missing parts of SQL/XML for PostgreSQL.
* @param sve SQL string value to use in a text node
* @return XML content, the text node wrapped in a document node
*/
@Function(schema="javatest")
public static SQLXML xmltext(String sve) throws SQLException
{
SQLXML rx = s_dbc.createSQLXML();
ContentHandler ch = rx.setResult(SAXResult.class).getHandler();
try
{
ch.startDocument();
/*
* It seems XMLTEXT() should be such a trivial function to write,
* but already it reveals a subtlety in the SAX API docs. They say
* the third argument to characters() is "the number of characters
* to read from the array" and that follows a long discussion of how
* individual characters can (with code points above U+FFFF) consist
* of more than one Java char value.
*
* And yet, when you try it out (and include some characters above
* U+FFFF in the input), you discover the third argument isn't the
* number of characters, has to be the number of Java char values.
*/
ch.characters(sve.toCharArray(), 0, sve.length());
ch.endDocument();
}
catch ( SAXException e )
{
rx.free();
throw new SQLException(e.getMessage(), e);
}
return rx;
}
/**
* An implementation of XMLCAST.
*<p>
* Will be declared to take and return type {@code RECORD}, where each must
* have exactly one component, just because that makes it easy to use
* existing JDBC metadata queries to find out the operand and target SQL
* data types.
*<p>
* Serving suggestion: rewrite this ISO standard expression
*<pre>
* XMLCAST(v AS wantedtype)
*</pre>
* to this idiomatic one:
*<pre>
* (SELECT r FROM (SELECT v) AS o, xmlcast(o) AS (r wantedtype))
*</pre>
* @param operand a one-row, one-column record supplied by the caller, whose
* one typed value is the operand to be cast.
* @param base64 true if binary SQL values should be base64-encoded in XML;
* if false (the default), values will be encoded in hex.
* @param target a one-row, one-column record supplied by PL/Java from the
* {@code AS} clause after the function call, whose one column's type is the
* type to be cast to.
*/
@Function(
schema="javatest",
type="pg_catalog.record",
onNullInput=CALLED,
settings="IntervalStyle TO iso_8601"
)
public static boolean xmlcast(
ResultSet operand, @SQLType(defaultValue="false") Boolean base64,
ResultSet target)
throws SQLException
{
if ( null == operand )
throw new SQLDataException(
"xmlcast \"operand\" must be (in this implementation) " +
"a non-null row type", "22004");
if ( null == base64 )
throw new SQLDataException(
"xmlcast \"base64\" must be true or false, not null", "22004");
XMLBinary enc = base64 ? XMLBinary.BASE64 : XMLBinary.HEX;
assert null != target : "PL/Java supplied a null output record???";
if ( 1 != operand.getMetaData().getColumnCount() )
throw new SQLDataException(
"xmlcast \"operand\" must be a row type with exactly " +
"one component", "22000");
if ( 1 != target.getMetaData().getColumnCount() )
throw new SQLDataException(
"xmlcast \"target\" must be a row type with exactly " +
"one component", "22000");
Binding.Parameter op =
new BindingsFromResultSet(operand, false).iterator().next();
Binding.Parameter tg =
new BindingsFromResultSet(target, null).iterator().next();
int sd = op.typeJDBC();
int td = tg.typeJDBC();
int castcase =
(Types.SQLXML == sd ? 2 : 0) | (Types.SQLXML == td ? 1 : 0);
switch ( castcase )
{
case 0: // neither sd nor td is an XML type
throw new SQLSyntaxErrorException(
"at least one of xmlcast \"operand\" or \"target\" must " +
"be of XML type", "42804");
case 3: // both XML
/*
* In an implementation closely following the spec, this case would
* be handled in parse analysis and rewritten from an XMLCAST to a
* plain CAST, and this code would never see it. This is a plain
* example function without benefit of a parser that can do that.
* In a DBMS with all the various SQL:2006 XML subtypes, there would
* be nontrivial work to do here, but casting from PostgreSQL's one
* XML type to itself is more of a warm-up exercise.
*/
target.updateSQLXML(1, operand.getSQLXML(1));
return true;
case 1: // something non-XML being cast to XML
assertCanCastAsXmlSequence(sd, "operand");
Object v = op.valueJDBC();
if ( null == v )
{
target.updateNull(1);
return true;
}
ItemType xsbt =
mapSQLDataTypeToXMLSchemaDataType(op, enc, Nulls.ABSENT);
Iterator<XdmItem> tv =
xmlCastAsSequence(v, enc, xsbt).iterator();
try
{
target.updateSQLXML(1,
returnContent(tv, /*nullOnEmpty*/ false));
}
catch ( SaxonApiException | XPathException e )
{
throw new SQLException(e.getMessage(), "10000", e);
}
return true;
case 2: // XML being cast to something non-XML
assertCanCastAsXmlSequence(td, "target");
SQLXML sx = operand.getSQLXML(1);
if ( null == sx )
{
target.updateNull(1);
return true;
}
DocumentBuilder dBuilder = s_s9p.newDocumentBuilder();
Source source = sx.getSource(null);
try
{
XdmValue xv = dBuilder.build(source);
XQueryEvaluator xqe =
PredefinedQueryHolders.DocumentWrapUnwrap.INSTANCE.load();
xqe.setExternalVariable(PredefinedQueryHolders.s_qEXPR, xv);
xv = xqe.evaluate();
/*
* It's zero-or-one, or XPTY0004 was thrown here.
*/
if ( 0 == xv.size() )
{
target.updateNull(1);
return true;
}
XdmAtomicValue av = (XdmAtomicValue)xv;
xmlCastAsNonXML(
av, ItemType.UNTYPED_ATOMIC, tg, target, 1, enc);
}
catch ( SaxonApiException | XPathException e )
{
throw new SQLException(e.getMessage(), "10000", e);
}
return true;
}
throw new SQLFeatureNotSupportedException(
"cannot yet xmlcast from " + op.typePG() +
" to " + tg.typePG(), "0A000");
}
/**
* A simple example corresponding to {@code XMLQUERY(expression
* PASSING BY VALUE passing RETURNING CONTENT {NULL|EMPTY} ON EMPTY)}.
* @param expression An XQuery expression. Must not be {@code null} (in the
* SQL standard {@code XMLQUERY} syntax, it is not even allowed to be an
* SQL expression at all, only a string literal).
* @param nullOnEmpty pass {@code true} to get a null return in place of
* an empty sequence, or {@code false} to just get the empty sequence.
* @param passing A row value whose columns will be supplied to the query
* as parameters. Columns with names (typically supplied with {@code AS})
* appear as predeclared external variables with matching names (in no
* namespace) in the query, with types derived from the SQL types of the
* row value's columns. There may be one (and no more than one)
* column with {@code AS "."} which, if present, will be bound as the
* context item. (The name {@code ?column?}, which PostgreSQL uses for an
* otherwise-unnamed column, is also accepted, which will often allow the
* context item to be specified with no {@code AS} at all. Beware, though,
* that PostgreSQL likes to invent column names from any function or type
* name that may appear in the value expression, so this shorthand will not
* always work, while {@code AS "."} will.) PL/Java's internal JDBC uppercases all column
* names, so any uses of the corresponding variables in the query must have
* the names in upper case. It is safest to also uppercase their appearances
* in the SQL (for which, in PostgreSQL, they must be quoted), so that the
* JDBC uppercasing is not being relied on. It is likely to be dropped in a
* future PL/Java release.
* @param namespaces An even-length String array where, of each pair of
* consecutive entries, the first is a namespace prefix and the second is
* the URI to which to bind it. The zero-length prefix sets the default
* element and type namespace; if the prefix has zero length, the URI may
* also have zero length, to declare that unprefixed elements are in no
* namespace.
*/
@Function(
schema="javatest",
onNullInput=CALLED,
settings="IntervalStyle TO iso_8601"
)
public static SQLXML xq_ret_content(
String expression, Boolean nullOnEmpty,
@SQLType(defaultValue={}) ResultSet passing,
@SQLType(defaultValue={}) String[] namespaces)
throws SQLException
{
/*
* The expression itself may not be null (in the standard, it isn't
* even allowed to be dynamic, and can only be a string literal!).
*/
if ( null == expression )
throw new SQLDataException(
"XMLQUERY expression may not be null", "22004");
if ( null == nullOnEmpty )
throw new SQLDataException(
"XMLQUERY nullOnEmpty may not be null", "22004");
try
{
XdmSequenceIterator<XdmItem> x1 =
evalXQuery(expression, passing, namespaces);
return null == x1 ? null : returnContent(x1, nullOnEmpty);
}
catch ( SaxonApiException | XPathException e )
{
throw new SQLException(e.getMessage(), "10000", e);
}
}
/**
* An implementation of {@code XMLEXISTS(expression
* PASSING BY VALUE passing)}, using genuine XQuery.
* @param expression An XQuery expression. Must not be {@code null} (in the
* SQL standard {@code XMLQUERY} syntax, it is not even allowed to be an
* SQL expression at all, only a string literal).
* @param passing A row value whose columns will be supplied to the query
* as parameters. Columns with names (typically supplied with {@code AS})
* appear as predeclared external variables with matching names (in no
* namespace) in the query, with types derived from the SQL types of the
* row value's columns. There may be one (and no more than one)
* column with {@code AS "."} which, if present, will be bound as the
* context item. (The name {@code ?column?}, which PostgreSQL uses for an
* otherwise-unnamed column, is also accepted, which will often allow the
* context item to be specified with no {@code AS} at all. Beware, though,
* that PostgreSQL likes to invent column names from any function or type
* name that may appear in the value expression, so this shorthand will not
* always work, while {@code AS "."} will.) PL/Java's internal JDBC uppercases all column
* names, so any uses of the corresponding variables in the query must have
* the names in upper case. It is safest to also uppercase their appearances
* in the SQL (for which, in PostgreSQL, they must be quoted), so that the
* JDBC uppercasing is not being relied on. It is likely to be dropped in a
* future PL/Java release.
* @param namespaces An even-length String array where, of each pair of
* consecutive entries, the first is a namespace prefix and the second is
* the URI to which to bind it. The zero-length prefix sets the default
* element and type namespace; if the prefix has zero length, the URI may
* also have zero length, to declare that unprefixed elements are in no
* namespace.
* @return True if the expression evaluates to a nonempty sequence, false if
* it evaluates to an empty one. Null if a context item is passed and its
* SQL value is null.
*/
@Function(
schema="javatest",
onNullInput=CALLED,
settings="IntervalStyle TO iso_8601"
)
public static Boolean xmlexists(
String expression,
@SQLType(defaultValue={}) ResultSet passing,
@SQLType(defaultValue={}) String[] namespaces)
throws SQLException
{
/*
* The expression itself may not be null (in the standard, it isn't
* even allowed to be dynamic, and can only be a string literal!).
*/
if ( null == expression )
throw new SQLDataException(
"XMLEXISTS expression may not be null", "22004");
XdmSequenceIterator<XdmItem> x1 =
evalXQuery(expression, passing, namespaces);
if ( null == x1 )
return null;
if ( ! x1.hasNext() )
return false;
x1.close();
return true;
}
/**
* Implementation factor of XMLEXISTS and XMLQUERY.
* @return null if a context item is passed and its SQL value is null
*/
private static XdmSequenceIterator<XdmItem> evalXQuery(
String expression, ResultSet passing, String[] namespaces)
throws SQLException
{
Binding.Assemblage bindings = new BindingsFromResultSet(passing, true);
try
{
XQueryCompiler xqc = createStaticContextWithPassedTypes(
bindings, namespaceBindings(namespaces));
XQueryEvaluator xqe = xqc.compile(expression).load();
if ( storePassedValuesInDynamicContext(xqe, bindings, true) )
return null;
/*
* For now, punt on whether the <XQuery expression> is evaluated
* with XML 1.1 or 1.0 lexical rules.... XXX
*/
return xqe.iterator();
}
catch ( SaxonApiException | XPathException e )
{
throw new SQLException(e.getMessage(), "10000", e);
}
}
/**
* Perform the final steps of <em>something</em> {@code RETURNING CONTENT},
* with or without {@code nullOnEmpty}.
*<p>
* The effects are to be the same as if the supplied sequence were passed
* as {@code $EXPR} to {@code document{$EXPR}}.
*/
private static SQLXML returnContent(
Iterator<XdmItem> x, boolean nullOnEmpty)
throws SQLException, SaxonApiException, XPathException
{
if ( nullOnEmpty && ! x.hasNext() )
return null;
SQLXML rsx = s_dbc.createSQLXML();
/*
* Keep this simple by requesting a specific type of Result rather
* than letting PL/Java choose. It happens (though this is a detail of
* the implementation) that SAXResult won't be a bad choice.
*/
SAXResult sr = rsx.setResult(SAXResult.class);
/*
* Michael Kay recommends the following as equivalent to the SQL/XML-
* mandated behavior of evaluating document{$x}.
* https://sourceforge.net/p/saxon/mailman/message/36969060/
*/
SAXDestination d = new SAXDestination(sr.getHandler());
Receiver r = d.getReceiver(
s_s9p.getUnderlyingConfiguration().makePipelineConfiguration(),
new SerializationProperties());
r.open();
while ( x.hasNext() )
r.append(x.next().getUnderlyingValue());
r.close();
return rsx;
}
/**
* An implementation of (much of) XMLTABLE, using genuine XML Query.
*<p>
* The {@code columns} array must supply a valid XML Query expression for
* every column in the column definition list that follows the call of this
* function in SQL, except that the column for ordinality, if wanted, is
* identified by a {@code null} entry in {@code columns}. Syntax sugar in
* the standard allows an omitted column expression to imply an element test
* for an element with the same name as the column; that doesn't work here.
*<p>
* For now, this implementation lacks the ability to specify defaults for
* when a column expression produces an empty sequence. It is possible to
* do defaults explicitly by rewriting a query expression <em>expr</em> as
* {@code let $e := }<em>expr</em>{@code return if(empty($e))then $D else $e}
* and supplying the default <em>D</em> as another query parameter, though
* such defaults will be evaluated only once when {@code xmltable} is called
* and will not be able to refer to other values in an output row.
* @param rows The single XQuery expression whose result sequence generates
* the rows of the resulting table. Must not be null.
* @param columns Array of XQuery expressions, exactly as many as result
* columns in the column definition list that follows the SQL call to this
* function. This array must not be null. It is allowed for one element (and
* no more than one) to be null, marking the corresponding column to be
* "FOR ORDINALITY" (the column must be of "exact numeric with scale zero"
* type; PostgreSQL supports 64-bit row counters, so {@code int8} is
* recommended).
* @param passing A row value whose columns will be supplied to the query
* as parameters, just as described for
* {@link #xq_ret_content xq_ret_content()}. If a context item is supplied,
* it is the context item for the {@code rows} query (the {@code columns}
* queries get their context item from the {@code rows} query's result). Any
* named parameters supplied here are available both in the {@code rows}
* expression and (though this goes beyond the standard) in every expression
* of {@code columns}, with their values unchanging from row to row.
* @param namespaces An even-length String array where, of each pair of
* consecutive entries, the first is a namespace prefix and the second is
* to URI to which to bind it, just as described for
* {@link #xq_ret_content xq_ret_content()}.
* @param base64 whether the effective, in-scope 'xmlbinary' setting calls
* for base64 or (the default, false) hexadecimal.
*/
@Function(
schema="javatest",
onNullInput=CALLED,
settings="IntervalStyle TO iso_8601"
)
public static ResultSetProvider xmltable(
String rows, String[] columns,
@SQLType(defaultValue={}) ResultSet passing,
@SQLType(defaultValue={}) String[] namespaces,
@SQLType(defaultValue="false") Boolean base64)
throws SQLException
{
if ( null == rows )
throw new SQLDataException(
"XMLTABLE row expression may not be null", "22004");
if ( null == columns )
throw new SQLDataException(
"XMLTABLE columns expression array may not be null", "22004");
if ( null == base64 )
throw new SQLDataException(
"XMLTABLE base64 parameter may not be null", "22004");
XMLBinary enc = base64 ? XMLBinary.BASE64 : XMLBinary.HEX;
Binding.Assemblage rowBindings =
new BindingsFromResultSet(passing, true);
Iterable<Map.Entry<String,String>> namespacepairs =
namespaceBindings(namespaces);
XQueryEvaluator[] columnXQEs = new XQueryEvaluator[ columns.length ];
SequenceType[] columnStaticTypes = new SequenceType[ columns.length ];
try
{
XQueryCompiler rowXQC = createStaticContextWithPassedTypes(
rowBindings, namespacepairs);
XQueryExecutable rowXQX = rowXQC.compile(rows);
Binding.Assemblage columnBindings =
new BindingsFromXQX(rowXQX, rowBindings);
XQueryCompiler columnXQC = createStaticContextWithPassedTypes(
columnBindings, namespacepairs);
boolean ordinalitySeen = false;
for ( int i = 0; i < columns.length; ++ i )
{
String expr = columns[i];
if ( null == expr )
{
if ( ordinalitySeen )
throw new SQLSyntaxErrorException(
"No more than one column expression may be null " +
"(=> \"for ordinality\")", "42611");
ordinalitySeen = true;
continue;
}
XQueryExecutable columnXQX = columnXQC.compile(expr);
columnStaticTypes[i] = makeSequenceType(
columnXQX.getResultItemType(),
columnXQX.getResultCardinality());
columnXQEs[i] = columnXQX.load();
storePassedValuesInDynamicContext(
columnXQEs[i], columnBindings, false);
}
XQueryEvaluator rowXQE = rowXQX.load();
XdmSequenceIterator<XdmItem> rowIterator;
if ( storePassedValuesInDynamicContext(rowXQE, rowBindings, true) )
rowIterator = (XdmSequenceIterator<XdmItem>)
XdmEmptySequence.getInstance().iterator();
else
rowIterator = rowXQE.iterator();
return new S9(rowIterator, columnXQEs, columnStaticTypes, enc);
}
catch ( SaxonApiException | XPathException e )
{
throw new SQLException(e.getMessage(), "10000", e);
}
}
/**
* Called when PostgreSQL has no need for more rows of the tabular result.
*/
@Override
public void close()
{
m_sequenceIterator.close();
}
/**
* <a id='assignRowValues'>Produce and return one row</a> of
* the {@code XMLTABLE} result table per call.
*<p>
* The row expression has already been compiled and its evaluation begun,
* producing a sequence iterator. The column XQuery expressions have all
* been compiled and are ready to evaluate, and the compiler's static
* analysis has bounded the data types they will produce. Because of the
* way the set-returning function protocol works, we don't know the types
* of the SQL output columns yet, until the first call of this function,
* when the {@code receive} parameter's {@code ResultSetMetaData} can be
* inspected to find out. So that will be the first thing done when called
* with {@code currentRow} of zero.
*<p>
* Each call will then: (a) get the next value from the row expression's
* sequence iterator, then for each column, (b) evaluate that column's
* XQuery expression on the row value, and (c) assign that column's result
* to the SQL output column, casting to the proper type (which the SQL/XML
* spec has very exacting rules on how to do).
*<p>
* A note before going any further: this implementation, while fairly
* typical of a PostgreSQL set-returning user function, is <em>not</em> the
* way the SQL/XML spec defines {@code XMLTABLE}. The official behavior of
* {@code XMLTABLE} is defined in terms of a rewriting, at the SQL level,
* into a much-expanded SQL query where each result column appears as an
* {@code XMLQUERY} call applying the column expression, wrapped in an
* {@code XMLCAST} to the result column type (with a
* {@code CASE WHEN XMLEXISTS} thrown in to support column defaults).
*<p>
* As an ordinary user function, this example cannot rely on any fancy
* query rewriting during PostgreSQL's parse analysis. The slight syntax
* desugaring needed to transform a standard {@code XMLTABLE} call into a
* call of this "xmltable" is not too hard to learn and do by hand, but no
* one would ever want to write out by hand the whole longwinded "official"
* expansion prescribed in the spec. So this example is a compromise.
*<p>
* The main thing lost in the compromise is the handling of column defaults.
* The full rewriting with per-column SQL expressions means that each
* column default expression can be evaluated exactly when/if needed, which
* is often the desired behavior. This implementation as an ordinary
* function, whose arguments all get evaluated ahead of the call, can't
* really do that. Otherwise, there's nothing in the spec that's inherently
* unachievable in this implementation.
*<p>
* Which brings us to the matter of casting each column expression result
* to the proper type for its SQL result column.
*<p>
* Like any spec, {@code SQL/XML} does not mandate that an implementation
* must be done in exactly the way presented in the spec (rewritten so each
* column value is produced by an {@code XMLQUERY} wrapped in an
* {@code XMLCAST}). The requirement is to produce the equivalent result.
*<p>
* A look at the rewritten query shows that each column XQuery result value
* must be representable as some value in SQL's type system, not once, but
* twice: first as the result returned by {@code XMLQUERY} and passed along
* to {@code XMLCAST}, and finally with the output column's type as the
* result of the {@code XMLCAST}.
*<p>
* Now, the output column type can be whatever is wanted. Importantly, it
* can be either an XML type, or any ordinary SQL scalar type, like a
* {@code float} or a {@code date}. Likewise, the XQuery column expression
* may have produced some atomic value (like an {@code xs:double} or
* {@code xs:date}), or some XML node, or any sequence of any of those.
*<p>
* What are the choices for the type in the middle: the SQL value returned
* by {@code XMLQUERY} and passed on to {@code XMLCAST}?
*<p>
* There are two. An ISO-standard SQL {@code XMLQUERY} can specify
* {@code RETURNING SEQUENCE} or {@code RETURNING CONTENT}. The first option
* produces the type {@code XML(SEQUENCE)}, a useful type that PostgreSQL
* does not currently have. {@code XML(SEQUENCE)} can hold exactly whatever
* an XQuery expression can produce: a sequence of any length, of any
* mixture of atomic values and XML nodes (even such oddities as attribute
* nodes outside of any element), in any order. An {@code XML(SEQUENCE)}
* value need not look anything like what "XML" normally brings to mind.
*<p>
* With the other option, {@code RETURNING CONTENT}, the result of
* {@code XMLQUERY} has to be something that PostgreSQL's {@code xml} type
* could store: a serialized document with XML structure, but without the
* strict requirements of exactly one root element with no text outside it.
* At the limit, a completely non-XMLish string of ordinary text is
* perfectly acceptable XML {@code CONTENT}, as long as it uses the right
* {@code &...;} escapes for any characters that could look like XML markup.
*<p>