001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: package org.hsqldb.test;
032:
033: import java.sql.Connection;
034: import java.sql.DriverManager;
035: import java.sql.PreparedStatement;
036: import java.sql.ResultSet;
037: import java.sql.Savepoint;
038: import java.sql.Statement;
039:
040: import org.hsqldb.WebServer;
041:
042: import junit.framework.TestCase;
043: import junit.framework.TestResult;
044:
045: /**
046: * Tests JDBC java.sql.Savepoint support in context of new engine SQL-savepoint
047: * support and new HSQL protocol extensions for savepoint support. <p>
048: *
049: * @author boucher@users
050: * @version 1.7.2
051: * @since 1.7.2
052: */
053: public class TestJDBCSavepoints extends TestCase {
054:
055: // You change the url and serverProps to reflect your preferred settings
056: // String serverProps = "database.0=mem:test;silent=false;trace=true" // debugging
057: String serverProps = "database.0=mem:test;silent=true;trace=false";
058:
059: //String url = "jdbc:hsqldb:hsql://localhost";
060: String url = "jdbc:hsqldb:http://localhost";
061: String user;
062: String password;
063: Statement stmt;
064: Connection conn1;
065: Connection conn2;
066:
067: // Server server;
068: // this exercises everything:
069: // the engine and JDBC savepoint support,
070: // the new HSQL protocol and tunneling HSQL protocol over HTTP
071: WebServer server;
072:
073: public TestJDBCSavepoints(String name) {
074: super (name);
075: }
076:
077: protected void setUp() {
078:
079: user = "sa";
080: password = "";
081: stmt = null;
082: conn1 = null;
083: conn2 = null;
084:
085: // server = new Server();
086: server = new WebServer();
087:
088: server.putPropertiesFromString(serverProps);
089: server.setLogWriter(null);
090: server.start();
091:
092: try {
093: Class.forName("org.hsqldb.jdbcDriver");
094:
095: conn1 = DriverManager.getConnection(url, user, password);
096: conn2 = DriverManager.getConnection(url, user, password);
097: stmt = conn1.createStatement();
098: } catch (Exception e) {
099:
100: //e.printStackTrace();
101: System.out.println(this + ".setUp() error: "
102: + e.getMessage());
103: }
104: }
105:
106: protected void tearDown() {
107:
108: try {
109: conn1.close();
110: } catch (Exception e) {
111:
112: //e.printStackTrace();
113: System.out.println(this + ".tearDown() error: "
114: + e.getMessage());
115: }
116:
117: try {
118: conn2.close();
119: } catch (Exception e) {
120:
121: //e.printStackTrace();
122: System.out.println(this + ".tearDown() error: "
123: + e.getMessage());
124: }
125:
126: server.stop();
127: }
128:
129: public void testJDBCSavepoints() throws Exception {
130:
131: String sql;
132: String msg;
133: int i;
134: PreparedStatement ps;
135: ResultSet rs;
136: Savepoint sp1;
137: Savepoint sp2;
138: Savepoint sp3;
139: Savepoint sp4;
140: Savepoint sp5;
141: Savepoint sp6;
142: Savepoint sp7;
143: int rowcount = 0;
144:
145: sql = "drop table t if exists";
146:
147: stmt.executeUpdate(sql);
148:
149: sql = "create table t(id int, fn varchar, ln varchar, zip int)";
150:
151: stmt.executeUpdate(sql);
152: conn1.setAutoCommit(true);
153:
154: //-- Test 1 : The execution of an SQL savepoint statement shall
155: // raise an exception in the absence of an active
156: // enclosing transaction
157: // fredt@users - there is always an active transaction when autocommit
158: // is true. The transaction is committed automatically if the next
159: // Statement.execute() or similar call is performed successfully.
160: /*
161: msg = "savepoint set successfully in the abscence of an active transaction";
162: try {
163: conn.setSavepoint("savepoint1");
164: assertTrue(msg,false);
165: } catch (Exception e) {}
166:
167: */
168:
169: //-- setup for following tests
170: conn1.setAutoCommit(false);
171:
172: sql = "insert into t values(?,?,?,?)";
173: ps = conn1.prepareStatement(sql);
174:
175: ps.setString(2, "Mary");
176: ps.setString(3, "Peterson-Clancy");
177:
178: i = 0;
179:
180: for (; i < 10; i++) {
181: ps.setInt(1, i);
182: ps.setInt(4, i);
183: ps.executeUpdate();
184: }
185:
186: sp1 = conn1.setSavepoint("savepoint1");
187:
188: for (; i < 20; i++) {
189: ps.setInt(1, i);
190: ps.setInt(4, i);
191: ps.executeUpdate();
192: }
193:
194: sp2 = conn1.setSavepoint("savepoint2");
195:
196: for (; i < 30; i++) {
197: ps.setInt(1, i);
198: ps.setInt(4, i);
199: ps.executeUpdate();
200: }
201:
202: sp3 = conn1.setSavepoint("savepoint3");
203:
204: for (; i < 40; i++) {
205: ps.setInt(1, i);
206: ps.setInt(4, i);
207: ps.executeUpdate();
208: }
209:
210: sp4 = conn1.setSavepoint("savepoint4");
211:
212: for (; i < 50; i++) {
213: ps.setInt(1, i);
214: ps.setInt(4, i);
215: ps.executeUpdate();
216: }
217:
218: sp5 = conn1.setSavepoint("savepoint5");
219: sp6 = conn1.setSavepoint("savepoint6");
220: sp7 = conn1.setSavepoint("savepoint7");
221: rs = stmt.executeQuery("select count(*) from t");
222:
223: rs.next();
224:
225: rowcount = rs.getInt(1);
226:
227: rs.close();
228:
229: //-- Test 2 : count of rows matches # rows inserted (assertion req'd by
230: // following tests, but not directly related to the feature
231: // being tested)
232: msg = "select count(*) from t value";
233:
234: try {
235: assertEquals(msg, 50, rowcount);
236: } catch (Exception e) {
237: }
238:
239: conn2.setAutoCommit(false);
240: conn2.setSavepoint("savepoint1");
241: conn2.setSavepoint("savepoint2");
242:
243: //-- test 3 : A JDBC Savepoint shall be considered invalid if used to
244: // release an SQL-savepoint in an SQL-session other than that
245: // of the originating Connection object
246: msg = "savepoint released succesfully on non-originating connection";
247:
248: try {
249: conn2.releaseSavepoint(sp2);
250: assertTrue(msg, false);
251: } catch (Exception e) {
252: }
253:
254: //-- test 4 : A JDBC Savepoint shall be invalid if used to roll back to
255: // an SQL-savepoint in an SQL-session other than that of the
256: // originating Connection object
257: try {
258: conn2.rollback(sp1);
259:
260: msg = "succesful rollback to savepoint on "
261: + "non-originating connection";
262:
263: assertTrue(msg, false);
264: } catch (Exception e) {
265: }
266:
267: //-- test 5 : Direct execution of a <release savepoint> statement shall
268: // not fail to release an existing indicated savepoint,
269: // regardless of how the indicated savepoint was created
270: msg = "direct execution of <release savepoint> statement failed to "
271: + "release JDBC-created SQL-savepoint with identical savepoint name";
272:
273: try {
274: conn2.createStatement().executeUpdate(
275: "release savepoint \"savepoint2\"");
276: } catch (Exception e) {
277: try {
278: assertTrue(msg, false);
279: } catch (Exception e2) {
280: }
281: }
282:
283: //-- test 6 : Direct execution of a <rollback to savepoint> statement
284: // shall not fail to roll back to an existing indicated
285: // savepoint due and only due to how the indicated savepoint
286: // was created
287: msg = "direct execution of <rollback to savepoint> statement failed to "
288: + "roll back to existing JDBC-created SQL-savepoint with identical "
289: + "savepoint name";
290:
291: try {
292: conn2.createStatement().executeUpdate(
293: "rollback to savepoint \"savepoint1\"");
294: } catch (Exception e) {
295: e.printStackTrace();
296:
297: try {
298: assertTrue(msg, false);
299: } catch (Exception e2) {
300: }
301: }
302:
303: conn1.releaseSavepoint(sp6);
304:
305: //-- test 7 : Releasing an SQL-savepoint shall destroy that savepoint
306: msg = "savepoint released succesfully > 1 times";
307:
308: try {
309: conn1.releaseSavepoint(sp6);
310: assertTrue(msg, false);
311: } catch (Exception e) {
312: }
313:
314: //-- test 8 : Releasing an SQL-savepoint shall destroy all subsequent SQL-
315: // savepoints in the same savepoint level
316: msg = "savepoint released successfully after preceding savepoint released";
317:
318: try {
319: conn1.releaseSavepoint(sp7);
320: assertTrue(msg, false);
321: } catch (Exception e) {
322: }
323:
324: //-- test 9 : Releasing an SQL-savepoint shall not affect preceding
325: // savepoints
326: msg = "preceding same-point savepoint destroyed by following savepoint release";
327:
328: try {
329: conn1.releaseSavepoint(sp5);
330: } catch (Exception e) {
331: try {
332: assertTrue(msg, false);
333: } catch (Exception e2) {
334: }
335: }
336:
337: conn1.rollback(sp4);
338:
339: rs = stmt.executeQuery("select count(*) from t");
340:
341: rs.next();
342:
343: rowcount = rs.getInt(1);
344:
345: rs.close();
346:
347: //-- Test 10 : count of rows matches # rows inserted less the number
348: // of insertions rolled back
349: msg = "select * rowcount after 50 inserts - 10 rolled back:";
350:
351: try {
352: assertEquals(msg, 40, rowcount);
353: } catch (Exception e) {
354: }
355:
356: //-- test 11 : An SQL-savepoint shall be destroyed in the
357: // process of rolling back to that savepoint
358: msg = "savepoint rolled back succesfully > 1 times";
359:
360: try {
361: conn1.rollback(sp4);
362: assertTrue(msg, false);
363: } catch (Exception e) {
364: }
365:
366: conn1.rollback(sp3);
367:
368: rs = stmt.executeQuery("select count(*) from t");
369:
370: rs.next();
371:
372: rowcount = rs.getInt(1);
373:
374: rs.close();
375:
376: //-- Test 12 : count of rows matches # rows inserted less the number
377: // of insertions rolled back
378: msg = "select count(*) after 50 inserts - 20 rolled back:";
379:
380: try {
381: assertEquals(msg, 30, rowcount);
382: } catch (Exception e) {
383: }
384:
385: //-- test 13 : An SQL-savepoint shall be destroyed in the
386: // process of rolling back to that savepoint
387: msg = "savepoint released succesfully after use in rollback";
388:
389: try {
390: conn1.releaseSavepoint(sp3);
391: assertTrue(msg, false);
392: } catch (Exception e) {
393: }
394:
395: conn1.rollback(sp1);
396:
397: //-- test 14 : All subsequent savepoints (in a savepoint level)
398: // shall be destroyed by the process of rolling back to
399: // a preceeding savepoint (in the same savepoint level)
400: msg = "savepoint rolled back without raising an exception after "
401: + "rollback to a preceeding savepoint";
402:
403: try {
404: conn1.rollback(sp2);
405: assertTrue(msg, false);
406: } catch (Exception e) {
407: }
408:
409: conn1.rollback();
410:
411: //-- test 15 : All subsequent savepoints (in a savepoint level)
412: // shall be destroyed by the process of
413: // rolling back the active transaction
414: msg = "savepoint released succesfully when it should have been "
415: + "destroyed by a full rollback";
416:
417: try {
418: conn1.releaseSavepoint(sp1);
419: assertTrue(msg, false);
420: } catch (Exception e) {
421: }
422:
423: conn1.setAutoCommit(false);
424:
425: sp1 = conn1.setSavepoint("savepoint1");
426:
427: conn1.rollback();
428: conn1.setAutoCommit(false);
429: conn1.createStatement().executeUpdate(
430: "savepoint \"savepoint1\"");
431:
432: //-- test 16 : A JDBC Savepoint shall be considered invalid if used to
433: // release an SQL-savepoint other than precisely the
434: // one created in correspondence to the creation of that
435: // JDBC Savepoint object
436: // fredt@users - we allow this if the name is valid
437: /*
438: msg = "JDBC Savepoint used to successfully release an identically named "
439: + "savepoint in a transaction distinct from the originating "
440: + "transaction";
441: try {
442: conn1.releaseSavepoint(sp1);
443: assertTrue(msg, false);
444: } catch (Exception e) {}
445: */
446: conn1.setAutoCommit(false);
447:
448: sp1 = conn1.setSavepoint("savepoint1");
449:
450: conn1.createStatement().executeUpdate(
451: "savepoint \"savepoint1\"");
452:
453: //-- test 17 : A JDBC Savepoint shall be considered invalid if used to
454: // release an SQL-savepoint other than precisely the
455: // one created in correspondence to the creation of that
456: // JDBC Savepoint object
457: // fredt@users - we allow this if the name is valid
458: /*
459: msg = "JDBC Savepoint used to successfully release an identically named "
460: + "savepoint in a transaction other than the originating "
461: + "transaction";
462: try {
463: conn1.releaseSavepoint(sp1);
464: assertTrue(msg, false);
465: } catch (Exception e) {}
466: */
467:
468: //-- test 18 : A JDBC Savepoint shall be considered invalid if used to
469: // roll back to an SQL-savepoint other than precisely the
470: // one created in correspondence to the creation of that
471: // JDBC Savepoint object
472: // fredt@users - we allow this if the name is valid
473: /*
474: msg = "JDBC Savepoint used to successfully to roll back to an "
475: + "identically named savepoint in a transaction distinct "
476: + "from the originating transaction";
477: try {
478: conn1.rollback(sp1);
479: assertTrue(msg, false);
480: } catch (Exception e) {}
481: */
482: conn1.setAutoCommit(false);
483:
484: sp1 = conn1.setSavepoint("savepoint1");
485:
486: conn1.createStatement().executeUpdate(
487: "savepoint \"savepoint1\"");
488:
489: //-- test 19 : A JDBC Savepoint shall be considered invalid if used to
490: // roll back to an SQL-savepoint other than precisely the
491: // one created in correspondence to the creation of that
492: // JDBC Savepoint object
493: // fredt@users - we allow this if the name is valid
494: /*
495: msg = "JDBC Savepoint used to successfully release an identically named "
496: + "savepoint in a transaction other than the originating "
497: + "transaction";
498: try {
499: conn1.releaseSavepoint(sp1);
500: assertTrue(msg, false);
501: } catch (Exception e) {}
502: */
503: }
504:
505: /**
506: * @param args the command line arguments
507: */
508: public static void main(String[] args) throws Exception {
509:
510: TestResult result;
511: TestCase test;
512: java.util.Enumeration failures;
513: int count;
514:
515: result = new TestResult();
516: test = new TestJDBCSavepoints("testJDBCSavepoints");
517:
518: test.run(result);
519:
520: count = result.failureCount();
521:
522: System.out
523: .println("TestJDBCSavepoints failure count: " + count);
524:
525: failures = result.failures();
526:
527: while (failures.hasMoreElements()) {
528: System.out.println(failures.nextElement());
529: }
530: }
531: }
|