001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.xmlBinding
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.io.FileReader;
025: import java.io.InputStream;
026: import java.io.InputStreamReader;
027:
028: import java.sql.Connection;
029: import java.sql.PreparedStatement;
030: import java.sql.ResultSet;
031: import java.sql.Statement;
032: import java.sql.SQLException;
033: import java.sql.Types;
034:
035: import org.apache.derby.tools.ij;
036: import org.apache.derby.tools.JDBCDisplayUtil;
037:
038: /**
039: * This class checks to make sure that the XML data type and
040: * the corresponding XML operations all work as expected
041: * from the JDBC side of things. In particular, this test
042: * verifies that 1) it is NOT possible to bind to/from an XML
043: * datatype (because the JDBC specification doesn't indicate
044: * how that should be done), and 2) the correct behavior
045: * occurs when null values (both Java and SQL) are bound
046: * into the bindable parameters for the XML operators.
047: * This file also checks that insertion from XML files
048: * via a character stream works, which is important since
049: * XML files can be arbitrarily long and thus stream-based
050: * processing is a must.
051: */
052: public class xmlBinding {
053: /**
054: * Create an instance of this class and do the test.
055: */
056: public static void main(String[] args) {
057: new xmlBinding().go(args);
058: }
059:
060: /**
061: * Create a JDBC connection using the arguments passed
062: * in from the harness, and then run the binding
063: * tests.
064: * @param args Arguments from the harness.
065: */
066: public void go(String[] args) {
067: try {
068:
069: // use the ij utility to read the property file and
070: // make the initial connection.
071: ij.getPropertyArg(args);
072: Connection conn = ij.startJBMS();
073:
074: // Create our test table.
075: Statement st = conn.createStatement();
076: st.execute("create table xTable.t1 "
077: + "(i int generated always as identity, x xml)");
078:
079: // Do the tests.
080: doBindTests(conn);
081: doXMLParseTests(conn);
082: doXMLSerializeTests(conn);
083: doXMLExistsTests(conn);
084:
085: // Clean up.
086: st.close();
087: conn.close();
088:
089: System.out.println("[ Done. ]\n");
090:
091: } catch (Exception e) {
092:
093: System.out.println("Unexpected error: ");
094: e.printStackTrace(System.out);
095:
096: }
097: }
098:
099: /**
100: * Performs a series of binding checks to make sure
101: * binding to an XML value never works.
102: * @param conn A connection to the test database.
103: */
104: private void doBindTests(Connection conn) {
105: // Make sure that attempts to bind _to_ XML will fail.
106: System.out.println("\n[ Beginning XML binding tests. ]\n");
107:
108: // Binding to an XML column.
109: PreparedStatement pSt = null;
110: try {
111:
112: // If we're running in embedded mode or else with
113: // the Derby Client, then the next line will fail
114: // because there is NO deferred prepare. If, however,
115: // we're running with JCC, the default is to defer
116: // the prepare until execution, so the next line will
117: // be fine, but the following four checks will fail.
118: // This difference in behavior okay--it requires two
119: // different masters, but ultimately it's a good way
120: // to check behavior in both cases.
121: pSt = conn
122: .prepareStatement("insert into xTable.t1(x) values (?)");
123:
124: System.out.print("XML column -- bind String to XML: ");
125: bindAndExecute(pSt, 1, Types.VARCHAR, "shouldn't work",
126: "42Z70", false);
127:
128: System.out.print("XML column -- bind Java null to XML: ");
129: bindAndExecute(pSt, 1, Types.VARCHAR, null, "42Z70", false);
130:
131: System.out.print("XML column -- bind SQL NULL to XML: ");
132: bindAndExecute(pSt, 1, Types.VARCHAR, null, "42Z70", true);
133:
134: System.out.print("XML column -- bind integer to XML: ");
135: bindAndExecute(pSt, 1, Types.INTEGER, new Integer(8),
136: "42Z70", false);
137:
138: } catch (SQLException se) {
139: // Must be running with embedded or Derby Network Client.
140: System.out
141: .print("XML column -- insertion via parameter: ");
142: checkException(se, "42Z70");
143: }
144:
145: // Binding to an XML value in the XMLSERIALIZE operator.
146: // Should get compile-time error saying that
147: // parameters aren't allowed for XML data values.
148: System.out.print("Trying to bind to XML in XMLSERIALIZE: ");
149: try {
150: pSt = conn
151: .prepareStatement("select XMLSERIALIZE(? AS CLOB) FROM XTABLE.T1");
152: bindAndExecute(pSt, 1, Types.VARCHAR, null, "42Z70", true);
153: } catch (SQLException se) {
154: checkException(se, "42Z70");
155: }
156:
157: // Binding to an XML value in the XMLEXISTS operator.
158: // Should get compile-time error saying that
159: // parameters aren't allowed for XML data values.
160: System.out.print("Trying to bind to XML in XMLEXISTS: ");
161: try {
162: pSt = conn
163: .prepareStatement("select i from xTable.t1 where "
164: + "XMLEXISTS('//*' PASSING BY REF ?)");
165: bindAndExecute(pSt, 1, Types.VARCHAR, null, "42Z70", true);
166: } catch (SQLException se) {
167: checkException(se, "42Z70");
168: }
169:
170: // Make sure that attempts to bind _from_ XML will fail.
171: // We should fail at compile time, even before
172: // we get a chance to execute the query.
173: System.out.print("XML value in result set: ");
174: try {
175: pSt = conn.prepareStatement("select x from xTable.t1");
176: pSt.execute();
177: } catch (SQLException se) {
178: checkException(se, "42Z71");
179: }
180:
181: System.out.println("\n[ End XML binding tests. ]\n");
182: }
183:
184: /**
185: * Test insertion of documents larger than 32K (this
186: * will test stream processing of XML data), and
187: * test binding of null values in the XMLPARSE
188: * operator.
189: * @param conn A connection to the test database.
190: */
191: private void doXMLParseTests(Connection conn) {
192: System.out.println("\n[ Beginning XMLPARSE tests. ]\n");
193:
194: System.out.println("Test insertions from file: ");
195: try {
196:
197: // Test parsing of > 32K XML documents.
198: insertFiles(conn, "xTable.t1", "xmlTestFiles/wide40k.xml",
199: 1);
200: insertFiles(conn, "xTable.t1", "xmlTestFiles/deep40k.xml",
201: 1);
202:
203: // Test parsing of docs that use schemas. Since server
204: // and client tests run in a subdirectory, we have to modify
205: // the XML documents that use DTDs so that they can find
206: // the DTD files.
207:
208: insertDocWithDTD(conn, "xTable.t1",
209: "xmlTestFiles/dtdDoc.xml", "personal.dtd", 1);
210: insertFiles(conn, "xTable.t1", "xmlTestFiles/xsdDoc.xml", 1);
211:
212: // XMLPARSE is not supposed to validate, so the following
213: // inserts should SUCCEED, even though the documents
214: // don't adhere to their schemas.
215: insertDocWithDTD(conn, "xTable.t1",
216: "xmlTestFiles/dtdDoc_invalid.xml", "personal.dtd",
217: 1);
218: insertFiles(conn, "xTable.t1",
219: "xmlTestFiles/xsdDoc_invalid.xml", 1);
220:
221: System.out.println("--> Insertions all PASS.");
222:
223: } catch (SQLException se) {
224: System.out.println("FAIL: Unexpected exception: ");
225: while (se != null) {
226: se.printStackTrace(System.out);
227: se = se.getNextException();
228: }
229: } catch (Exception e) {
230: System.out.println("FAIL: Unexpected exception: ");
231: e.printStackTrace(System.out);
232: }
233:
234: // Test binding nulls to the XMLPARSE operand.
235:
236: try {
237:
238: PreparedStatement pSt = conn
239: .prepareStatement("insert into xTable.t1(x) values "
240: + "(XMLPARSE (DOCUMENT CAST (? as CLOB) PRESERVE WHITESPACE))");
241:
242: // This should work. Note we check binding to
243: // a character stream method in "insertFiles".
244: System.out.print("Binding string in XMLPARSE: ");
245: bindAndExecute(pSt, 1, Types.CHAR,
246: "<simple> doc </simple>", null, false);
247:
248: // Null should work, too.
249: System.out.print("Binding Java null string in XMLPARSE: ");
250: bindAndExecute(pSt, 1, Types.CHAR, null, null, false);
251: System.out.print("Binding SQL NULL string in XMLPARSE: ");
252: bindAndExecute(pSt, 1, Types.CLOB, null, null, true);
253:
254: } catch (Exception e) {
255: System.out.println("Unexpected exception: ");
256: e.printStackTrace(System.out);
257: }
258:
259: System.out.println("\n[ End XMLPARSE tests. ]\n");
260: }
261:
262: /**
263: * Test serialization of the XML values inserted by
264: * the doXMLParseTests() method above. For the documents
265: * that are larger than 32K, this tests that they can
266: * be correctly read from disk as a stream (instead of
267: * just as as string).
268: * @param conn A connection to the test database.
269: */
270: private void doXMLSerializeTests(Connection conn) {
271: System.out.println("\n[ Beginning XMLSERIALIZE tests. ]\n");
272:
273: try {
274:
275: PreparedStatement pSt = conn
276: .prepareStatement("select i, XMLSERIALIZE(X AS CLOB) FROM xTable.t1");
277: ResultSet rs = pSt.executeQuery();
278:
279: String xResult = null;
280: int rowCount = 0;
281: while (rs.next()) {
282: xResult = rs.getString(2);
283: if (!rs.wasNull()) {
284: System.out.println(rs.getInt(1) + ", "
285: + "[ roughly " + (xResult.length() / 1000)
286: + "k ]");
287: } else
288: System.out.println(rs.getInt(1) + ", NULL");
289: rowCount++;
290: }
291:
292: } catch (Exception e) {
293: System.out.println("Unexpected exception: ");
294: e.printStackTrace(System.out);
295: }
296:
297: // Test binding to the XMLSERIALIZE operand. Since
298: // the operand is an XML value, and since we don't
299: // allow binding to an XML value (see "doBindTests()"
300: // above), there's nothing more to do here.
301:
302: System.out.println("\n[ End XMLSERIALIZE tests. ]\n");
303: }
304:
305: /**
306: * Run some simple XPath queries against the documents
307: * inserted in doXMLParseTests() above, and then test
308: * binding of null values in the XMLEXISTS operator.
309: * @param conn A connection to the test database.
310: */
311: private void doXMLExistsTests(Connection conn) {
312: System.out.println("\n[ Begin XMLEXISTS tests. ]\n");
313:
314: // Run some sample queries.
315: try {
316:
317: existsQuery(conn, "xTable.t1", "//abb");
318: existsQuery(conn, "xTable.t1", "//d50");
319: existsQuery(conn, "xTable.t1", "//person/email");
320: existsQuery(conn, "xTable.t1", "/personnel");
321: existsQuery(conn, "xTable.t1", "//person/@id");
322:
323: // This next one is important because it verifies
324: // that implicit/default values which are defined
325: // in a DTD _are_ actually processed, even though
326: // we don't perform validation. Thus this next
327: // query _should_ return a match.
328: int rowCount = existsQuery(conn, "xTable.t1",
329: "//person/@noteTwo");
330: if (rowCount == 0) {
331: System.out
332: .println("FAILED: Query on DTD default didn't "
333: + "return any matches.");
334: }
335:
336: } catch (Exception e) {
337: System.out.println("Unexpected exception: ");
338: e.printStackTrace(System.out);
339: }
340:
341: // Test binding to the XMLEXISTS operands. Binding
342: // of the second (XML) operand is not allowed and was
343: // checked in "doBindTests()" above. Here we check
344: // binding of the first operand, which should fail
345: // because SQL/XML spec says the first operand must
346: // be a string literal.
347: try {
348:
349: System.out
350: .print("Parameter as first operand in XMLEXISTS: ");
351:
352: // If we're running in embedded mode or else with
353: // the Derby Client, then the next line will fail
354: // because there is NO deferred prepare. If, however,
355: // we're running with JCC, the default is to defer
356: // the prepare until execution, so the next line will
357: // be fine, but the subsequent "execute" should fail.
358: PreparedStatement pSt = conn
359: .prepareStatement("select i from xTable.t1 where "
360: + "XMLEXISTS (? PASSING BY REF x)");
361: pSt.setString(1, "//*");
362: pSt.execute();
363:
364: } catch (SQLException se) {
365: checkException(se, "42Z75");
366: }
367:
368: System.out.println("\n[ End XMLEXISTS tests. ]\n");
369: }
370:
371: /**
372: * Helper method. Inserts the contents of a file into
373: * the received table using "setCharacterStream".
374: * @param conn A connection to the test database.
375: * @param tableName Name of the target table
376: * @param fName Name of the file whose content we
377: * want to insert.
378: * @param numRows Number of times we should insert
379: * the received file's content.
380: */
381: private void insertFiles(Connection conn, String tableName,
382: String fName, int numRows) throws Exception {
383: // First we have to figure out many chars long the
384: // file is.
385: InputStream iS = this .getClass().getResourceAsStream(fName);
386: InputStreamReader reader = new InputStreamReader(iS);
387: char[] cA = new char[1024];
388: int charCount = 0;
389: for (int len = reader.read(cA, 0, cA.length); len != -1; charCount += len, len = reader
390: .read(cA, 0, cA.length))
391: ;
392:
393: reader.close();
394:
395: // Now that we know the number of characters, we can
396: // insert using a stream.
397:
398: PreparedStatement pSt = conn
399: .prepareStatement("insert into xTable.t1(x) values ("
400: + "xmlparse(document cast (? as clob) preserve whitespace))");
401:
402: for (int i = 0; i < numRows; i++) {
403:
404: iS = this .getClass().getResourceAsStream(fName);
405: reader = new InputStreamReader(iS);
406: pSt.setCharacterStream(1, reader, charCount);
407: pSt.execute();
408: reader.close();
409: System.out.println("Inserted roughly " + (charCount / 1000)
410: + "k of data.");
411:
412: }
413: }
414:
415: /**
416: * Helper method. Inserts an XML document into the
417: * received table using setString. This method
418: * parallels "insertFiles" above, except that it
419: * should be used for documents that require a DTD
420: * in order to be complete. In that case, the
421: * location of the DTD has to modified _in_ the
422: * document so that it can be found regardless of
423: * whether we're running in embedded mode or in
424: * server/client mode.
425: * @param conn A connection to the test database.
426: * @param tableName Name of the target table
427: * @param fName Name of the file whose content we
428: * want to insert.
429: * @param dtdName Name of the DTD file that the
430: * received file uses.
431: * @param numRows Number of times we should insert
432: * the received file's content.
433: */
434: private void insertDocWithDTD(Connection conn, String tableName,
435: String fName, String dtdName, int numRows) throws Exception {
436: boolean needsUpdate = true;
437: String currPath = System.getProperty("user.dir");
438: String fileSep = System.getProperty("file.separator");
439:
440: String dtdPath = currPath;
441: boolean foundDTD = false;
442: while (!foundDTD) {
443:
444: try {
445:
446: FileReader fR = new FileReader(dtdPath + fileSep
447: + dtdName);
448:
449: // If we get here, then we found the DTD in
450: // the current path, so we're done.
451: foundDTD = true;
452: dtdPath = "file:///" + dtdPath + fileSep + dtdName;
453: break;
454:
455: } catch (java.io.IOException ie) {
456:
457: // Couldn't find the DTD in the current path.
458: // The harness uses a lot of subdirectories when
459: // running tests (for client, or server, or
460: // suites, or nested suites...etc.), so we
461: // back up one directory and try again.
462:
463: int pos = dtdPath.lastIndexOf(fileSep);
464: if (pos == -1) {
465: // we're at the top of the path and haven't
466: // found the DTD yet. This shouldn't happen.
467: throw new Exception("Couldn't find DTD '" + dtdName
468: + "' for insertion of file '" + fName
469: + "'.");
470: }
471: dtdPath = dtdPath.substring(0, pos);
472:
473: }
474: }
475:
476: // Read the file into memory so we can update it.
477: InputStream iS = this .getClass().getResourceAsStream(fName);
478: InputStreamReader reader = new InputStreamReader(iS);
479: char[] cA = new char[1024];
480: StringBuffer sBuf = new StringBuffer();
481: int charCount = 0;
482: for (int len = reader.read(cA, 0, cA.length); len != -1; charCount += len, len = reader
483: .read(cA, 0, cA.length)) {
484: sBuf.append(cA, 0, len);
485: }
486:
487: reader.close();
488:
489: // Now replace the DTD location, if needed.
490: String docAsString = sBuf.toString();
491: int pos = docAsString.indexOf(dtdName);
492: if (pos != -1)
493: sBuf.replace(pos, pos + dtdName.length(), dtdPath);
494:
495: // Now (finally) do the insert using the in-memory
496: // document with the correct DTD location.
497: docAsString = sBuf.toString();
498: PreparedStatement pSt = conn
499: .prepareStatement("insert into xTable.t1(x) values ("
500: + "xmlparse(document cast (? as clob) preserve whitespace))");
501:
502: charCount = docAsString.length();
503: for (int i = 0; i < numRows; i++) {
504:
505: pSt.setString(1, docAsString);
506: pSt.execute();
507: System.out.println("Inserted roughly " + (charCount / 1000)
508: + "k of data.");
509:
510: }
511: }
512:
513: /**
514: * Helper method. Selects all rows from the received
515: * table name that have at least one node matching
516: * the received XPath expression. Does this query
517: * using the XMLEXISTS operator.
518: * @param conn A connection to the test database.
519: * @param tableName Table to query.
520: * @param xPath The XPath expression to evaluate.
521: * @return The number of rows that match the
522: * XPath expression.
523: */
524: private int existsQuery(Connection conn, String tableName,
525: String xPath) throws Exception {
526: PreparedStatement pSt = conn.prepareStatement("select i from "
527: + tableName + " where " + "xmlexists('" + xPath
528: + "' passing by ref x)");
529:
530: System.out.println("Running XMLEXISTS with: " + xPath);
531: ResultSet rs = pSt.executeQuery();
532: String xResult = null;
533: int rowCount = 0;
534: while (rs.next()) {
535: rowCount++;
536: }
537:
538: System.out.println("--> Matching rows: " + rowCount);
539: return rowCount;
540: }
541:
542: /**
543: * Helper method. Attempts to bind a parameter to a
544: * given value using the given type, and then prints
545: * the result of that attempt (PASS/FAIL).
546: * @param pSt The prepared statement holding the parameter
547: * that we want to bind.
548: * @param paramNum Which parameter in pSt we want to bind.
549: * @param paramType The type of the value to be bound.
550: * @param bindValue The value to be used for binding.
551: * @param sqlState The expected SQLState for the binding
552: * error, if one is expected. Null if the bind is expected
553: * to succeed.
554: * @param bindSqlNull True if we should bind using a SQL
555: * NULL (i.e. "setNull()").
556: */
557: private void bindAndExecute(PreparedStatement pSt, int paramNum,
558: int paramType, Object bindValue, String sqlState,
559: boolean bindSqlNull) {
560: SQLException actualException = null;
561: try {
562:
563: // First try to bind.
564: if (bindSqlNull) {
565: pSt.setNull(paramNum, paramType);
566: } else {
567: switch (paramType) {
568: case Types.CHAR:
569: case Types.VARCHAR:
570:
571: pSt.setString(paramNum, (String) bindValue);
572: break;
573:
574: case Types.INTEGER:
575:
576: pSt.setInt(paramNum, ((Integer) bindValue)
577: .intValue());
578: break;
579:
580: default:
581:
582: System.out.println("ERROR: Unexpected bind type ("
583: + paramType + ") in call to doBind.");
584: break;
585: }
586: }
587:
588: // Now try to execute.
589: pSt.execute();
590:
591: } catch (SQLException e) {
592: actualException = e;
593: }
594:
595: checkException(actualException, sqlState);
596: }
597:
598: /**
599: * Helper method. Checks to see if the received SQLException
600: * has a SQLState that matches the target/expected SQLState.
601: * Prints out a message saying the result of this check, and
602: * in the case where the actual error is NOT the expected
603: * error, prints a full stack trace to System.out.
604: * @param se The SQLException to be checked.
605: * @param targetState The expected SQLState; null if no
606: * error was expected.
607: */
608: private void checkException(SQLException se, String targetState) {
609: if (targetState == null) {
610: if (se == null) {
611: System.out
612: .println("PASS -- Completed without exception, "
613: + "as expected.");
614: } else {
615: System.out
616: .println("FAIL -- Was expected to succeed, but "
617: + "failed with error "
618: + se.getSQLState() + ".");
619: se.printStackTrace(System.out);
620: }
621: return;
622: }
623:
624: if (se == null) {
625: System.out
626: .println("FAIL -- Completed without exception when "
627: + "error " + targetState + " was expected.");
628: return;
629: }
630:
631: if (!targetState.equals(se.getSQLState())) {
632: System.out.println("FAIL: Caught error " + se.getSQLState()
633: + " when was expecting error " + targetState + ".");
634: se.printStackTrace(System.out);
635: return;
636: }
637:
638: System.out.println("PASS -- caught expected error "
639: + targetState + ".");
640: }
641: }
|