Java > Open Source Codes > org > hsqldb > test > TestJDBCSavepoints


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags