001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SetQueryTimeoutTest
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.jdbcapi;
023:
024: import java.sql.CallableStatement;
025: import java.sql.Connection;
026: import java.sql.SQLException;
027: import java.sql.Statement;
028: import java.sql.PreparedStatement;
029: import java.sql.ResultSet;
030:
031: import java.util.Collection;
032: import java.util.HashSet;
033: import java.util.Collections;
034:
035: import org.apache.derby.tools.ij;
036:
037: /**
038: * Functional test for the Statement.setQueryTimeout() method.
039: *
040: * This test consists of four parts:
041: *
042: * 1. Executes a SELECT query in 4 different threads concurrently.
043: * The query calls a user-defined, server-side function which
044: * delays the execution, so that it takes several seconds even
045: * though the data volume is really low. The fetch operations
046: * take longer time than the timeout value set. Hence, this part
047: * tests getting timeouts from calls to ResultSet.next().
048: *
049: * Two connections are used, two threads execute their statement
050: * in the context of one connection, the other two threads in the
051: * context of the other connection. Of the 4 threads, only one
052: * executes its statement with a timeout value. This way, the test
053: * ensures that the correct statement is affected by setQueryTimeout(),
054: * regardless of what connection/transaction it and other statements
055: * are executed in the context of.
056: *
057: * 2. Executes an INSERT query in multiple threads.
058: * This part tests getting timeouts from calls to Statement.execute().
059: * Each thread executes the query in the context of a separate
060: * connection. There is no point in executing multiple statements
061: * on the same connection; since only one statement per connection
062: * executes at a time, there will be no interleaving of execution
063: * between them (contrary to the first part of this test, where
064: * calls to ResultSet.next() may be interleaved between the different
065: * threads).
066: *
067: * Half of the threads execute their statement with a timeout value set,
068: * this is to verify that the correct statements are affected by the
069: * timeout, while the other statements execute to completion.
070: *
071: * 3. Sets an invalid (negative) timeout. Verifies that the correct
072: * exception is thrown.
073: *
074: * 4. Tests that the query timeout value is not forgotten after the execution
075: * of a statement (DERBY-1692).
076: */
077: public class SetQueryTimeoutTest {
078: private static final int TIMEOUT = 1; // In seconds
079: private static final int CONNECTIONS = 100;
080:
081: private static void printSQLException(SQLException e) {
082: while (e != null) {
083: e.printStackTrace();
084: e = e.getNextException();
085: }
086: }
087:
088: /**
089: * This Exception class is used for getting fail-fast behaviour in
090: * this test. There is no point in wasting cycles running a test to
091: * the end when we know that it has failed.
092: *
093: * In order to enable chaining of exceptions in J2ME, this class defines
094: * its own "cause", duplicating existing functionality in J2SE.
095: */
096: private static class TestFailedException extends Exception {
097: private Throwable cause;
098:
099: public TestFailedException(Throwable t) {
100: super ();
101: cause = t;
102: }
103:
104: public TestFailedException(String message) {
105: super (message);
106: cause = null;
107: }
108:
109: public TestFailedException(String message, Throwable t) {
110: super (message);
111: cause = t;
112: }
113:
114: public String toString() {
115: if (cause != null) {
116: return super .toString() + ": " + cause.toString();
117: } else {
118: return super .toString();
119: }
120: }
121:
122: public void printStackTrace() {
123: super .printStackTrace();
124: if (cause != null) {
125: if (cause instanceof SQLException) {
126: SetQueryTimeoutTest
127: .printSQLException((SQLException) cause);
128: } else {
129: cause.printStackTrace();
130: }
131: }
132: }
133: }
134:
135: /**
136: * Used for executing the SQL statements for setting up this test
137: * (the preparation phase). The queries testing setQueryTimeout()
138: * are run by the StatementExecutor class.
139: */
140: private static void exec(Connection connection, String queryString,
141: Collection ignoreExceptions) throws TestFailedException {
142: Statement statement = null;
143: try {
144: statement = connection.createStatement();
145: statement.execute(queryString);
146: } catch (SQLException e) {
147: String sqlState = e.getSQLState();
148: if (!ignoreExceptions.contains(sqlState)) {
149: throw new TestFailedException(e); // See finally block below
150: }
151: } finally {
152: if (statement != null) {
153: try {
154: statement.close();
155: } catch (SQLException ee) {
156: // This will discard an exception possibly thrown above :-(
157: // But we don't worry too much about this, since:
158: // 1. This is just a test
159: // 2. We don't expect close() to throw
160: // 3. If it does, this will be inspected by a developer
161: throw new TestFailedException(ee);
162: }
163: }
164: }
165: }
166:
167: // Convenience method
168: private static void exec(Connection connection, String queryString)
169: throws TestFailedException {
170: exec(connection, queryString, Collections.EMPTY_SET);
171: }
172:
173: private static void dropTables(Connection conn, String tablePrefix)
174: throws TestFailedException {
175: Collection ignore = new HashSet();
176: ignore.add("42Y55");
177:
178: exec(conn, "drop table " + tablePrefix + "_orig", ignore);
179: exec(conn, "drop table " + tablePrefix + "_copy", ignore);
180: }
181:
182: private static void prepareTables(Connection conn,
183: String tablePrefix) throws TestFailedException {
184: System.out.println("Initializing tables with prefix "
185: + tablePrefix);
186:
187: dropTables(conn, tablePrefix);
188:
189: exec(conn, "create table " + tablePrefix + "_orig (a int)");
190:
191: exec(conn, "create table " + tablePrefix + "_copy (a int)");
192:
193: exec(conn, "insert into " + tablePrefix + "_orig"
194: + " values(0),(1),(2),(3),(4),(5),(6)");
195: }
196:
197: /**
198: * This is the user-defined function which is called from our queries
199: */
200: public static int delay(int seconds, int value) throws SQLException {
201: try {
202: Thread.sleep(seconds * 1000);
203: } catch (InterruptedException e) {
204: // Ignore
205: }
206: return value;
207: }
208:
209: private static void prepareForTimedQueries(Connection conn)
210: throws TestFailedException {
211: System.out
212: .println("Preparing for testing queries with timeout");
213:
214: try {
215: conn.setAutoCommit(true);
216: } catch (SQLException e) {
217: throw new TestFailedException("Should not happen", e);
218: }
219:
220: try {
221: exec(conn, "DROP FUNCTION DELAY");
222: } catch (Exception e) {
223: // Ignore
224: }
225:
226: exec(
227: conn,
228: "CREATE FUNCTION DELAY(SECONDS INTEGER, VALUE INTEGER) RETURNS INTEGER PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.jdbcapi.SetQueryTimeoutTest.delay'");
229:
230: prepareTables(conn, "t");
231: }
232:
233: private static String getFetchQuery(String tablePrefix) {
234: /**
235: * The reason for using the mod function here is to force
236: * at least one invocation of ResultSet.next() to read
237: * more than one row from the table before returning.
238: * This is necessary since timeout is checked only when
239: * reading rows from base tables, and when the first row
240: * is read, the query still has not exceeded the timeout.
241: */
242: return "select a from " + tablePrefix
243: + "_orig where mod(DELAY(1,a),3)=0";
244: }
245:
246: private static String getExecQuery(String tablePrefix) {
247: return "insert into " + tablePrefix + "_copy select a from "
248: + tablePrefix + "_orig where DELAY(1,1)=1";
249: }
250:
251: private static class StatementExecutor extends Thread {
252: private PreparedStatement statement;
253: private boolean doFetch;
254: private int timeout;
255: private SQLException sqlException;
256: private String name;
257: private long highestRunTime;
258:
259: public StatementExecutor(PreparedStatement statement,
260: boolean doFetch, int timeout) {
261: this .statement = statement;
262: this .doFetch = doFetch;
263: this .timeout = timeout;
264: highestRunTime = 0;
265: sqlException = null;
266: if (timeout > 0) {
267: try {
268: statement.setQueryTimeout(timeout);
269: } catch (SQLException e) {
270: sqlException = e;
271: }
272: }
273: }
274:
275: private void setHighestRunTime(long runTime) {
276: synchronized (this ) {
277: highestRunTime = runTime;
278: }
279: }
280:
281: public long getHighestRunTime() {
282: synchronized (this ) {
283: return highestRunTime;
284: }
285: }
286:
287: private boolean fetchRow(ResultSet resultSet)
288: throws SQLException {
289: long startTime = System.currentTimeMillis();
290: boolean hasNext = resultSet.next();
291: long endTime = System.currentTimeMillis();
292: long runTime = endTime - startTime;
293: if (runTime > highestRunTime)
294: setHighestRunTime(runTime);
295: return hasNext;
296: }
297:
298: public void run() {
299: if (sqlException != null)
300: return;
301:
302: ResultSet resultSet = null;
303:
304: try {
305: if (doFetch) {
306: long startTime = System.currentTimeMillis();
307: resultSet = statement.executeQuery();
308: long endTime = System.currentTimeMillis();
309: setHighestRunTime(endTime - startTime);
310: while (fetchRow(resultSet)) {
311: yield();
312: }
313: } else {
314: long startTime = System.currentTimeMillis();
315: statement.execute();
316: long endTime = System.currentTimeMillis();
317: setHighestRunTime(endTime - startTime);
318: }
319: } catch (SQLException e) {
320: synchronized (this ) {
321: sqlException = e;
322: }
323: } finally {
324: if (resultSet != null) {
325: try {
326: resultSet.close();
327: } catch (SQLException ex) {
328: if (sqlException != null) {
329: System.err
330: .println("Discarding previous exception");
331: sqlException.printStackTrace();
332: }
333: sqlException = ex;
334: }
335: }
336: }
337: }
338:
339: public SQLException getSQLException() {
340: synchronized (this ) {
341: return sqlException;
342: }
343: }
344: }
345:
346: /**
347: * This method compares a thrown SQLException's SQLState value
348: * to an expected SQLState. If they do not match, a
349: * TestFailedException is thrown with the given message string.
350: */
351: private static void expectException(String expectSqlState,
352: SQLException sqlException, String failMsg)
353: throws TestFailedException {
354: if (sqlException == null) {
355: throw new TestFailedException(failMsg);
356: } else {
357: String sqlState = sqlException.getSQLState();
358: if (!expectSqlState.equals(sqlState)) {
359: throw new TestFailedException(sqlException);
360: }
361: }
362: }
363:
364: // A convenience method which wraps a SQLException
365: private static PreparedStatement prepare(Connection conn,
366: String query) throws TestFailedException {
367: try {
368: return conn.prepareStatement(query);
369: } catch (SQLException e) {
370: throw new TestFailedException(e);
371: }
372: }
373:
374: /**
375: * Part 1 of this test.
376: */
377: private static void testTimeoutWithFetch(Connection conn1,
378: Connection conn2) throws TestFailedException {
379: System.out.println("Testing timeout with fetch operations");
380:
381: try {
382: conn1.setAutoCommit(false);
383: conn2.setAutoCommit(false);
384: } catch (SQLException e) {
385: throw new TestFailedException("Should not happen", e);
386: }
387:
388: // The idea with these 4 statements is as follows:
389: // A - should time out
390: // B - different stmt on the same connection; should NOT time out
391: // C - different stmt on different connection; should NOT time out
392: // D - here just to create equal contention on conn1 and conn2
393:
394: PreparedStatement statementA = prepare(conn1,
395: getFetchQuery("t"));
396: PreparedStatement statementB = prepare(conn1,
397: getFetchQuery("t"));
398: PreparedStatement statementC = prepare(conn2,
399: getFetchQuery("t"));
400: PreparedStatement statementD = prepare(conn2,
401: getFetchQuery("t"));
402:
403: StatementExecutor[] statementExecutor = new StatementExecutor[4];
404: statementExecutor[0] = new StatementExecutor(statementA, true,
405: TIMEOUT);
406: statementExecutor[1] = new StatementExecutor(statementB, true,
407: 0);
408: statementExecutor[2] = new StatementExecutor(statementC, true,
409: 0);
410: statementExecutor[3] = new StatementExecutor(statementD, true,
411: 0);
412:
413: for (int i = 3; i >= 0; --i) {
414: statementExecutor[i].start();
415: }
416:
417: for (int i = 0; i < 4; ++i) {
418: try {
419: statementExecutor[i].join();
420: } catch (InterruptedException e) {
421: throw new TestFailedException("Should never happen", e);
422: }
423: }
424:
425: /**
426: * Actually, there is no guarantee that setting a query timeout
427: * for a statement will actually cause a timeout, even if execution
428: * of the statement takes longer than the specified timeout.
429: *
430: * However, these queries execute significantly longer than the
431: * specified query timeout. Also, the cancellation mechanism
432: * implemented should be quite responsive. In sum, we expect
433: * the statement to always time out.
434: *
435: * If it does not time out, however, we print the highest
436: * execution time for the query, as an assistance in determining
437: * why it failed. Compare the number to the TIMEOUT constant
438: * in this class (note that the TIMEOUT constant is in seconds,
439: * while the execution time is in milliseconds).
440: */
441: expectException("XCL52",
442: statementExecutor[0].getSQLException(),
443: "fetch did not time out. Highest execution time: "
444: + statementExecutor[0].getHighestRunTime()
445: + " ms");
446:
447: System.out.println("Statement 0 timed out");
448:
449: for (int i = 1; i < 4; ++i) {
450: SQLException sqlException = statementExecutor[i]
451: .getSQLException();
452: if (sqlException != null) {
453: throw new TestFailedException(
454: "Unexpected exception in " + i, sqlException);
455: }
456: System.out.println("Statement " + i + " completed");
457: }
458:
459: try {
460: statementA.close();
461: statementB.close();
462: statementC.close();
463: statementD.close();
464: conn1.commit();
465: conn2.commit();
466: } catch (SQLException e) {
467: throw new TestFailedException(e);
468: }
469: }
470:
471: /**
472: * Part two of this test.
473: */
474: private static void testTimeoutWithExec(Connection[] connections)
475: throws TestFailedException {
476: System.out.println("Testing timeout with an execute operation");
477:
478: for (int i = 0; i < connections.length; ++i) {
479: try {
480: connections[i].setAutoCommit(true);
481: } catch (SQLException e) {
482: throw new TestFailedException("Should not happen", e);
483: }
484: }
485:
486: PreparedStatement statements[] = new PreparedStatement[connections.length];
487: for (int i = 0; i < statements.length; ++i) {
488: statements[i] = prepare(connections[i], getExecQuery("t"));
489: }
490:
491: StatementExecutor[] executors = new StatementExecutor[statements.length];
492: for (int i = 0; i < executors.length; ++i) {
493: int timeout = (i % 2 == 0) ? TIMEOUT : 0;
494: executors[i] = new StatementExecutor(statements[i], false,
495: timeout);
496: }
497:
498: for (int i = 0; i < executors.length; ++i) {
499: executors[i].start();
500: }
501:
502: for (int i = 0; i < executors.length; ++i) {
503: try {
504: executors[i].join();
505: } catch (InterruptedException e) {
506: throw new TestFailedException("Should never happen", e);
507: }
508: }
509:
510: /**
511: * Actually, there is no guarantee that setting a query timeout
512: * for a statement will actually cause a timeout, even if execution
513: * of the statement takes longer than the specified timeout.
514: *
515: * However, these queries execute significantly longer than the
516: * specified query timeout. Also, the cancellation mechanism
517: * implemented should be quite responsive. In sum, we expect
518: * the statement to always time out.
519: *
520: * If it does not time out, however, we print the highest
521: * execution time for the query, as an assistance in determining
522: * why it failed. Compare the number to the TIMEOUT constant
523: * in this class (note that the TIMEOUT constant is in seconds,
524: * while the execution time is in milliseconds).
525: */
526: for (int i = 0; i < executors.length; ++i) {
527: int timeout = (i % 2 == 0) ? TIMEOUT : 0;
528: if (timeout > 0) {
529: expectException("XCL52",
530: executors[i].getSQLException(),
531: "exec did not time out. Execution time: "
532: + executors[i].getHighestRunTime()
533: + " ms");
534: } else {
535: SQLException sqlException = executors[i]
536: .getSQLException();
537: if (sqlException != null) {
538: throw new TestFailedException(sqlException);
539: }
540: }
541: }
542:
543: System.out
544: .println("Statements that should time out timed out, and statements that should complete completed");
545:
546: for (int i = 0; i < statements.length; ++i) {
547: try {
548: statements[i].close();
549: } catch (SQLException e) {
550: throw new TestFailedException(e);
551: }
552: }
553: }
554:
555: private static void testInvalidTimeoutValue(Connection conn)
556: throws TestFailedException {
557: System.out.println("Testing setting a negative timeout value");
558:
559: try {
560: conn.setAutoCommit(true);
561: } catch (SQLException e) {
562: throw new TestFailedException("Should not happen", e);
563: }
564:
565: // Create statement
566: PreparedStatement stmt = null;
567: try {
568: stmt = conn.prepareStatement("select * from sys.systables");
569: } catch (SQLException e) {
570: throw new TestFailedException("Should not happen", e);
571: }
572:
573: // Set (invalid) timeout value - expect exception
574: try {
575: stmt.setQueryTimeout(-1);
576: } catch (SQLException e) {
577: expectException("XJ074", e,
578: "negative timeout value should give exception");
579: }
580:
581: System.out
582: .println("Negative timeout value caused exception, as expected");
583:
584: // Execute the statement and fetch result
585: ResultSet rs = null;
586: try {
587: rs = stmt.executeQuery();
588: System.out.println("Execute returned a ResultSet");
589: rs.close();
590: } catch (SQLException e) {
591: throw new TestFailedException("Should not happen", e);
592: } finally {
593: try {
594: stmt.close();
595: } catch (SQLException e) {
596: // This will discard an exception possibly thrown above :-(
597: // But we don't worry too much about this, since:
598: // 1. This is just a test
599: // 2. We don't expect close() to throw
600: // 3. If it does, this will be inspected by a developer
601: throw new TestFailedException("close should not throw",
602: e);
603: }
604: }
605: }
606:
607: /** This tests timeout with executeUpdate call. */
608: private static void testTimeoutWithExecuteUpdate(Connection conn)
609: throws TestFailedException {
610: System.out.println("Testing timeout with executeUpdate call.");
611: try {
612: Statement stmt = conn.createStatement();
613: stmt.setQueryTimeout(TIMEOUT);
614: stmt.executeUpdate(getExecQuery("t"));
615: } catch (SQLException sqle) {
616: expectException("XCL52", sqle, "Should have timed out.");
617: }
618: }
619:
620: /** Test for DERBY-1692. */
621: private static void testRememberTimeoutValue(Connection conn)
622: throws TestFailedException {
623: String sql = getFetchQuery("t");
624: try {
625: Statement stmt = conn.createStatement();
626: testStatementRemembersTimeout(stmt);
627: PreparedStatement ps = conn.prepareStatement(sql);
628: testStatementRemembersTimeout(ps);
629: CallableStatement cs = conn.prepareCall(sql);
630: testStatementRemembersTimeout(cs);
631: } catch (SQLException sqle) {
632: throw new TestFailedException("Should not happen", sqle);
633: }
634: }
635:
636: /** Test that a statement remembers its timeout value when executed
637: * multiple times. */
638: private static void testStatementRemembersTimeout(Statement stmt)
639: throws SQLException, TestFailedException {
640: System.out.println("Testing that Statement remembers timeout.");
641: stmt.setQueryTimeout(1);
642: for (int i = 0; i < 3; i++) {
643: try {
644: ResultSet rs = stmt.executeQuery(getFetchQuery("t"));
645: while (rs.next())
646: ;
647: throw new TestFailedException("Should have timed out.");
648: } catch (SQLException sqle) {
649: expectException("XCL52", sqle, "Should have timed out.");
650: }
651: }
652: stmt.close();
653: }
654:
655: /** Test that a prepared statement remembers its timeout value when
656: * executed multiple times. */
657: private static void testStatementRemembersTimeout(
658: PreparedStatement ps) throws SQLException,
659: TestFailedException {
660: String name = (ps instanceof CallableStatement) ? "CallableStatement"
661: : "PreparedStatement";
662: System.out.println("Testing that " + name
663: + " remembers timeout.");
664: ps.setQueryTimeout(1);
665: for (int i = 0; i < 3; i++) {
666: try {
667: ResultSet rs = ps.executeQuery();
668: while (rs.next())
669: ;
670: throw new TestFailedException("Should have timed out.");
671: } catch (SQLException sqle) {
672: expectException("XCL52", sqle, "Should have timed out.");
673: }
674: }
675: ps.close();
676: }
677:
678: /**
679: * Main program, makes this class invocable from the command line
680: */
681: public static void main(String[] args) {
682: new SetQueryTimeoutTest().go(args);
683: }
684:
685: /**
686: * The actual main bulk of this test.
687: * Sets up the environment, prepares tables,
688: * runs the tests, and shuts down.
689: */
690: public void go(String[] args) {
691: System.out.println("Test SetQueryTimeoutTest starting");
692:
693: Connection[] connections = new Connection[CONNECTIONS];
694: for (int i = 0; i < connections.length; ++i) {
695: connections[i] = null;
696: }
697:
698: try {
699: // Load the JDBC Driver class
700: // use the ij utility to read the property file and
701: // create connections
702: ij.getPropertyArg(args);
703: for (int i = 0; i < connections.length; ++i) {
704: connections[i] = ij.startJBMS();
705: }
706:
707: System.out.println("Got connections");
708:
709: for (int i = 0; i < connections.length; ++i) {
710: connections[i]
711: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
712: }
713:
714: prepareForTimedQueries(connections[0]);
715: testTimeoutWithFetch(connections[0], connections[1]);
716: testTimeoutWithExec(connections);
717: testInvalidTimeoutValue(connections[0]);
718: testRememberTimeoutValue(connections[0]);
719: testTimeoutWithExecuteUpdate(connections[0]);
720:
721: System.out.println("Test SetQueryTimeoutTest PASSED");
722: } catch (Throwable e) {
723: System.out.println("Test SetQueryTimeoutTest FAILED");
724: e.printStackTrace();
725: } finally {
726: for (int i = connections.length - 1; i >= 0; --i) {
727: if (connections[i] != null) {
728: try {
729: connections[i].close();
730: } catch (SQLException ex) {
731: printSQLException(ex);
732: }
733: }
734: }
735: System.out.println("Closed connections");
736: }
737: }
738: }
|