KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > derbyTesting > functionTests > tests > jdbcapi > HoldabilityTest


1 /*
2  *
3  * Derby - Class HoldabilityTest
4  *
5  * Licensed to the Apache Software Foundation (ASF) under one or more
6  * contributor license agreements. See the NOTICE file distributed with
7  * this work for additional information regarding copyright ownership.
8  * The ASF licenses this file to You under the Apache License, Version 2.0
9  * (the "License"); you may not use this file except in compliance with
10  * the License. You may obtain a copy of the License at
11  *
12  * http://www.apache.org/licenses/LICENSE-2.0
13  *
14  * Unless required by applicable law or agreed to in writing,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
17  * either express or implied. See the License for the specific
18  * language governing permissions and limitations under the License.
19  */
20 package org.apache.derbyTesting.functionTests.tests.jdbcapi;
21 import junit.framework.*;
22 import java.sql.*;
23
24 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
25
26 /**
27  * Tests holdable resultsets.
28  */
29 public class HoldabilityTest extends SURBaseTest {
30     
31     /** Creates a new instance of HoldabilityTest */
32     public HoldabilityTest(String JavaDoc name) {
33         super(name, 1000); // We will use 1000 records
34 }
35     
36     public static Test suite() {
37         TestSuite suite = new TestSuite();
38                
39         // DB2 client doesn't support this functionality
40 if (usingDerbyNet())
41             return suite;
42         
43         suite.addTestSuite(HoldabilityTest.class);
44         
45         return new CleanDatabaseTestSetup(suite);
46
47     }
48
49     /**
50      * Sets up the connection, then create the data model
51      */
52     public void setUp()
53         throws Exception JavaDoc
54     {
55        // For the holdability tests, we recreate the model
56 // for each testcase (since we do commits)
57
58         // We also use more records to ensure that the disk
59 // is being used.
60 SURDataModelSetup.createDataModel
61             (SURDataModelSetup.SURDataModel.MODEL_WITH_PK, getConnection(),
62              recordCount);
63         commit();
64     }
65     
66     /**
67      * Drop the data model, and close the connection
68      * @throws Exception
69      */
70     public void tearDown() throws Exception JavaDoc
71     {
72         try {
73             rollback();
74             Statement dropStatement = createStatement();
75             dropStatement.execute("drop table t1");
76             dropStatement.close();
77         } catch (SQLException e) {
78             printStackTrace(e); // Want to propagate the real exception.
79 }
80         super.tearDown();
81     }
82     
83     /**
84      * Test that a forward only resultset can be held over commit while
85      * it has not done any scanning
86      */
87     public void testHeldForwardOnlyResultSetScanInit()
88         throws SQLException
89     {
90         Statement s = createStatement();
91         ResultSet rs = s.executeQuery(selectStatement);
92         
93         commit(); // scan initialized
94
95         scrollForward(rs);
96         s.close();
97     }
98     
99     /**
100      * Test that a forward only resultset can be held over commit while
101      * it is in progress of scanning
102      */
103     public void testHeldForwardOnlyResultSetScanInProgress()
104         throws SQLException
105     {
106         Statement s = createStatement();
107         ResultSet rs = s.executeQuery(selectStatement);
108
109         for (int i=0; i<this.recordCount/2; i++) {
110             rs.next();
111             verifyTuple(rs);
112         }
113         commit(); // Scan is in progress
114
115         while (rs.next()) {
116             verifyTuple(rs);
117         }
118         s.close();
119     }
120
121     /**
122      * Test that a forward only resultset can be held over commit while
123      * it has not done any scanning, and be updatable
124      */
125     public void testHeldForwardOnlyUpdatableResultSetScanInit()
126         throws SQLException
127     {
128         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
129                                           ResultSet.CONCUR_UPDATABLE);
130         ResultSet rs = s.executeQuery(selectStatement);
131         commit(); // scan initialized
132 rs.next(); // naviagate to a new tuple
133 updateTuple(rs); // Updatable
134 scrollForward(rs);
135         s.close();
136     }
137     
138     
139     /**
140      * Test that a forward only resultset can be held over commit while
141      * it is in progress of scanning, and that after a compress the
142      * resultset is still updatable.
143      */
144     public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInProgress()
145         throws SQLException
146     {
147         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
148                                           ResultSet.CONCUR_UPDATABLE);
149         ResultSet rs = s.executeQuery(selectStatement);
150
151         for (int i=0; i<this.recordCount/2; i++) {
152             rs.next();
153             verifyTuple(rs);
154         }
155         updateTuple(rs);
156         commit(); // Scan is in progress
157
158         // Verifies resultset can do updates after compress
159 verifyResultSetUpdatableAfterCompress(rs);
160         s.close();
161         
162     }
163
164     /**
165      * Test that a forward only resultset can be held over commit while
166      * it has not done any scanning, and that after a compress it is
167      * still updatable.
168      */
169     public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInit()
170         throws SQLException
171     {
172         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
173                                           ResultSet.CONCUR_UPDATABLE);
174         ResultSet rs = s.executeQuery(selectStatement);
175         commit(); // scan initialized
176
177         // Verifies resultset can do updates after compress
178 verifyResultSetUpdatableAfterCompress(rs);
179         s.close();
180     }
181         
182     /**
183      * Test that a forward only resultset can be held over commit while
184      * it is in progress of scanning
185      */
186     public void testHeldForwardOnlyUpdatableResultSetScanInProgress()
187         throws SQLException
188     {
189         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
190                                           ResultSet.CONCUR_UPDATABLE);
191         ResultSet rs = s.executeQuery(selectStatement);
192
193         for (int i=0; i<this.recordCount/2; i++) {
194             rs.next();
195             verifyTuple(rs);
196         }
197         updateTuple(rs);
198         commit(); // Scan is in progress
199 rs.next();
200         updateTuple(rs); // Still updatable
201 while (rs.next()) {
202             verifyTuple(rs); // complete the scan
203 }
204         s.close();
205     }
206     
207     /**
208      * Test that a scrollable resultset can be held over commit while
209      * it has not done any scanning
210      */
211     public void testHeldScrollableResultSetScanInit()
212         throws SQLException
213     {
214         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
215                                           ResultSet.CONCUR_READ_ONLY);
216         ResultSet rs = s.executeQuery(selectStatement);
217         
218         commit(); // scan initialized
219
220         scrollForward(rs);
221         scrollBackward(rs);
222         
223         s.close();
224     }
225         
226     /**
227      * Test that a scrollable resultset can be held over commit while
228      * it is in progress of scanning
229      */
230     public void testHeldScrollableResultSetScanInProgress()
231         throws SQLException
232     {
233         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
234                                           ResultSet.CONCUR_READ_ONLY);
235         ResultSet rs = s.executeQuery(selectStatement);
236
237         for (int i=0; i<this.recordCount/2; i++) {
238             rs.next();
239             verifyTuple(rs);
240         }
241         commit(); // Scan is in progress
242
243         while (rs.next()) {
244             verifyTuple(rs);
245         }
246         scrollBackward(rs);
247         s.close();
248     }
249
250     /**
251      * Test that a scrollable resultset can be held over commit
252      * after the resultset has been populated
253      */
254     public void testHeldScrollableResultSetScanDone()
255         throws SQLException
256     {
257         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
258                                           ResultSet.CONCUR_READ_ONLY);
259         ResultSet rs = s.executeQuery(selectStatement);
260         
261         scrollForward(rs); // Scan is done
262
263         commit();
264         
265         scrollBackward(rs);
266         s.close();
267     }
268
269     /**
270      * Test that a scrollable updatable resultset can be held over commit
271      * while it has not done any scanning
272      */
273     public void testHeldScrollableUpdatableResultSetScanInit()
274         throws SQLException
275     {
276         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
277                                           ResultSet.CONCUR_UPDATABLE);
278         ResultSet rs = s.executeQuery(selectStatement);
279         
280         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
281             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
282                        false);
283         }
284         commit(); // scan initialized
285
286         scrollForward(rs);
287         scrollBackwardAndUpdate(rs);
288         
289         s.close();
290     }
291     
292     /**
293      * Test that a scrollable updatable resultset can be held over commit while
294      * it is in progress of scanning
295      */
296     public void testHeldScrollableUpdatableResultSetScanInProgress()
297         throws SQLException
298     {
299         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
300                                           ResultSet.CONCUR_UPDATABLE);
301         ResultSet rs = s.executeQuery(selectStatement);
302         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
303             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
304                        false);
305         }
306         for (int i=0; i<this.recordCount/2; i++) {
307             rs.next();
308             verifyTuple(rs);
309         }
310         commit(); // Scan is in progress
311
312         while (rs.next()) {
313             verifyTuple(rs);
314         }
315         scrollBackwardAndUpdate(rs);
316         
317         s.close();
318     }
319
320     /**
321      * Test that a scrollable updatable resultset can be held over commit
322      * after the resultset has been populated
323      */
324     public void testHeldScrollableUpdatableResultSetScanDone()
325         throws SQLException
326     {
327         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
328                                           ResultSet.CONCUR_UPDATABLE);
329         ResultSet rs = s.executeQuery(selectStatement);
330         
331         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
332             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
333                        false);
334         }
335       
336         scrollForward(rs); // Scan is done
337
338         commit();
339         
340         scrollBackwardAndUpdate(rs);
341         
342         s.close();
343     }
344
345     /**
346      * Test that updateRow() after a commit requires a renavigation
347      * on a held forward only ResulTset.
348      */
349     public void testUpdateRowAfterCommitOnHeldForwardOnlyResultSet()
350         throws SQLException
351     {
352         Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
353                                           ResultSet.CONCUR_UPDATABLE);
354         ResultSet rs = s.executeQuery(selectStatement);
355         
356         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
357             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
358                        false);
359         }
360         rs.next();
361         commit();
362         try {
363             rs.updateInt(2, -100);
364             rs.updateRow();
365             assertTrue("Expected updateRow() to throw exception", false);
366         } catch (SQLException e) {
367             assertEquals("Unexpected SQLState",
368                          INVALID_CURSOR_STATE_NO_CURRENT_ROW, e.getSQLState());
369         }
370         s.close();
371     }
372
373     /**
374      * Test that updateRow() after a commit requires a renavigation
375      * on a held scrollinsensitve ResulTset.
376      */
377     public void testUpdateRowAfterCommitOnHeldScrollInsensitiveResultSet()
378         throws SQLException
379     {
380         Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
381                                           ResultSet.CONCUR_UPDATABLE);
382         ResultSet rs = s.executeQuery(selectStatement);
383         
384         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
385             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
386                        false);
387         }
388         rs.next();
389         commit();
390         try {
391             rs.updateInt(2, -100);
392             rs.updateRow();
393             assertTrue("Expected updateRow() to throw exception", false);
394         } catch (SQLException e) {
395             assertEquals("Unexpected SQLState",
396                          INVALID_CURSOR_STATE_NO_CURRENT_ROW, e.getSQLState());
397         }
398         s.close();
399     }
400
401     /**
402      * Test that running a compress on a holdable scrollable updatable
403      * resultset will not invalidate the ResultSet from doing updates,
404      * if the scan is initialized
405      */
406     public void testCompressOnHeldScrollableUpdatableResultSetScanInit()
407         throws SQLException
408     {
409         // First: Read all records in the table into the ResultSet:
410 Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
411                                           ResultSet.CONCUR_UPDATABLE);
412         
413         ResultSet rs = s.executeQuery(selectStatement);
414         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
415             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
416                        false);
417         }
418         commit(); // commit
419
420         // Verifies resultset can do updates after compress
421 verifyResultSetUpdatableAfterCompress(rs);
422         
423         s.close();
424     }
425
426     /**
427      * Test that running a compress on a holdable scrollable updatable
428      * resultset will invalidate the Resultset from doing updates after
429      * a renavigate, if the scan is in progress.
430      */
431     public void testCompressOnHeldScrollableUpdatableResultSetScanInProgress()
432         throws SQLException
433     {
434         // First: Read all records in the table into the ResultSet:
435 Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
436                                           ResultSet.CONCUR_UPDATABLE);
437         ResultSet rs = s.executeQuery(selectStatement);
438         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
439             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
440                        false);
441         }
442         rs.next(); // Scan is in progress.
443
444         commit(); // commit, releases the lock on the records
445
446         verifyCompressInvalidation(rs);
447         
448         s.close();
449     }
450     
451     /**
452      * Test that running a compress on a holdable scrollable updatable
453      * resultset will invalidate the Resultset from doing updates after
454      * a renavigate.
455      */
456     public void testCompressOnHeldScrollableUpdatableResultSetScanDone()
457         throws SQLException
458     {
459         // First: Read all records in the table into the ResultSet:
460 Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
461                                           ResultSet.CONCUR_UPDATABLE);
462         ResultSet rs = s.executeQuery(selectStatement);
463         if (rs.getConcurrency()==ResultSet.CONCUR_READ_ONLY) {
464             assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY",
465                        false);
466         }
467         
468         scrollForward(rs); // scan is done
469
470         commit(); // commit, releases the lock on the records
471
472         verifyCompressInvalidation(rs);
473         s.close();
474     }
475
476     /**
477      * Verifies that even after a compress, the ResultSet of this type and
478      * state is updatable.
479      */
480     private void verifyResultSetUpdatableAfterCompress(ResultSet rs)
481         throws SQLException
482     {
483                 // Delete all records except the first:
484 Statement delStatement = createStatement();
485         int deleted = delStatement.executeUpdate("delete from T1 where id>0");
486         int expectedDeleted = recordCount-1;
487         
488         assertEquals("Invalid number of records deleted", expectedDeleted,
489                      deleted);
490         commit();
491         
492         // Execute online compress
493 onlineCompress(true, true, true);
494         
495         // Now reinsert the tuples:
496 PreparedStatement ps =
497             prepareStatement("insert into t1 values (?,?,?,?)");
498         
499         for (int i=0; i<recordCount*2; i++) {
500             int recordId = i + recordCount + 1000;
501             ps.setInt(1, recordId);
502             ps.setInt(2, recordId);
503             ps.setInt(3, recordId *2 + 17);
504             ps.setString(4, "m" + recordId);
505             ps.addBatch();
506         }
507         ps.executeBatch();
508         commit();
509
510         rs.next();
511         updateTuple(rs);
512         
513         SQLWarning warn = rs.getWarnings();
514         assertNull("Expected no warning when updating this row", warn);
515         
516         // This part if only for scrollable resultsets
517 if (rs.getType()!=ResultSet.TYPE_FORWARD_ONLY) {
518             
519             // Update last tuple
520 rs.last();
521             updateTuple(rs);
522             
523             warn = rs.getWarnings();
524             assertNull("Expected no warning when updating this row", warn);
525             
526             // Update first tuple
527 rs.first();
528             updateTuple(rs);
529             warn = rs.getWarnings();
530             assertNull("Expected no warning when updating this row", warn);
531         }
532         
533         commit();
534         
535         // Verify data
536 rs = createStatement().executeQuery(selectStatement);
537         while (rs.next()) {
538             verifyTuple(rs);
539         }
540     }
541
542     /**
543      * Verifies that the ResultSet is invalidated from doing updates after
544      * a compress.
545      * @param rs ResultSet which we test is being invalidated
546      */
547     private void verifyCompressInvalidation(ResultSet rs)
548         throws SQLException
549     {
550         
551         // Delete all records except the first:
552 Statement delStatement = createStatement();
553         int deleted = delStatement.executeUpdate("delete from T1 where id>0");
554         int expectedDeleted = recordCount-1;
555         delStatement.close();
556         
557         assertEquals("Invalid number of records deleted", expectedDeleted,
558                      deleted);
559         commit();
560         
561         // Execute online compress
562 onlineCompress(true, true, true);
563         
564         // Now reinsert the tuples:
565 PreparedStatement ps =
566             prepareStatement("insert into t1 values (?,?,?,?)");
567         
568         for (int i=0; i<recordCount*2; i++) {
569             int recordId = i + recordCount + 1000;
570             ps.setInt(1, recordId);
571             ps.setInt(2, recordId);
572             ps.setInt(3, recordId *2 + 17);
573             ps.setString(4, "m" + recordId);
574             ps.addBatch();
575         }
576         ps.executeBatch();
577         ps.close();
578         commit();
579         
580         // Update last tuple
581 rs.last();
582         rs.updateInt(2, -100);
583         rs.updateRow();
584         SQLWarning warn = rs.getWarnings();
585         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
586         rs.clearWarnings();
587         
588         // Update first tuple
589 rs.first();
590         rs.updateInt(2, -100);
591         updateTuple(rs);
592         warn = rs.getWarnings();
593         assertWarning(warn, CURSOR_OPERATION_CONFLICT);
594         commit();
595         
596         // Verify data
597 rs = createStatement().executeQuery(selectStatement);
598         while (rs.next()) {
599             // This will fail if we managed to update reinserted tuple
600 verifyTuple(rs);
601         }
602     }
603
604     /**
605      * Executes online compress
606      * @param purge set to true to purge rows
607      * @param defragment set to true to defragment rows
608      * @param truncate set to true to truncate pages
609      */
610     private void onlineCompress(boolean purge,
611                                 boolean defragment,
612                                 boolean truncate)
613         throws SQLException
614     {
615                // Use a new connection to compress the table
616 final Connection con2 = openDefaultConnection();
617         final String JavaDoc connId = con2.toString();
618         con2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
619         
620         final PreparedStatement ps2 = con2.prepareStatement
621             ("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
622         ps2.setString(1, "APP"); // schema
623 ps2.setString(2, "T1"); // table name
624 ps2.setBoolean(3, purge);
625         ps2.setBoolean(4, defragment);
626         ps2.setBoolean(5, truncate);
627         
628         try {
629             ps2.executeUpdate();
630             ps2.close();
631             con2.commit();
632         } finally {
633             con2.close();
634         }
635     }
636
637     private final static String JavaDoc selectStatement = "select * from t1";
638 }
639
Popular Tags