001: /*
002: *
003: * Derby - Class HoldabilityTest
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,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
017: * either express or implied. See the License for the specific
018: * language governing permissions and limitations under the License.
019: */
020: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
021:
022: import junit.framework.*;
023: import java.sql.*;
024:
025: import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
026:
027: /**
028: * Tests holdable resultsets.
029: */
030: public class HoldabilityTest extends SURBaseTest {
031:
032: /** Creates a new instance of HoldabilityTest */
033: public HoldabilityTest(String name) {
034: super (name, 1000); // We will use 1000 records
035: }
036:
037: public static Test suite() {
038: TestSuite suite = new TestSuite();
039:
040: // DB2 client doesn't support this functionality
041: if (usingDerbyNet())
042: return suite;
043:
044: suite.addTestSuite(HoldabilityTest.class);
045:
046: return new CleanDatabaseTestSetup(suite);
047:
048: }
049:
050: /**
051: * Sets up the connection, then create the data model
052: */
053: public void setUp() throws Exception {
054: // For the holdability tests, we recreate the model
055: // for each testcase (since we do commits)
056:
057: // We also use more records to ensure that the disk
058: // is being used.
059: SURDataModelSetup.createDataModel(
060: SURDataModelSetup.SURDataModel.MODEL_WITH_PK,
061: getConnection(), recordCount);
062: commit();
063: }
064:
065: /**
066: * Drop the data model, and close the connection
067: * @throws Exception
068: */
069: public void tearDown() throws Exception {
070: try {
071: rollback();
072: Statement dropStatement = createStatement();
073: dropStatement.execute("drop table t1");
074: dropStatement.close();
075: } catch (SQLException e) {
076: printStackTrace(e); // Want to propagate the real exception.
077: }
078: super .tearDown();
079: }
080:
081: /**
082: * Test that a forward only resultset can be held over commit while
083: * it has not done any scanning
084: */
085: public void testHeldForwardOnlyResultSetScanInit()
086: throws SQLException {
087: Statement s = createStatement();
088: ResultSet rs = s.executeQuery(selectStatement);
089:
090: commit(); // scan initialized
091:
092: scrollForward(rs);
093: s.close();
094: }
095:
096: /**
097: * Test that a forward only resultset can be held over commit while
098: * it is in progress of scanning
099: */
100: public void testHeldForwardOnlyResultSetScanInProgress()
101: throws SQLException {
102: Statement s = createStatement();
103: ResultSet rs = s.executeQuery(selectStatement);
104:
105: for (int i = 0; i < this .recordCount / 2; i++) {
106: rs.next();
107: verifyTuple(rs);
108: }
109: commit(); // Scan is in progress
110:
111: while (rs.next()) {
112: verifyTuple(rs);
113: }
114: s.close();
115: }
116:
117: /**
118: * Test that a forward only resultset can be held over commit while
119: * it has not done any scanning, and be updatable
120: */
121: public void testHeldForwardOnlyUpdatableResultSetScanInit()
122: throws SQLException {
123: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
124: ResultSet.CONCUR_UPDATABLE);
125: ResultSet rs = s.executeQuery(selectStatement);
126: commit(); // scan initialized
127: rs.next(); // naviagate to a new tuple
128: updateTuple(rs); // Updatable
129: scrollForward(rs);
130: s.close();
131: }
132:
133: /**
134: * Test that a forward only resultset can be held over commit while
135: * it is in progress of scanning, and that after a compress the
136: * resultset is still updatable.
137: */
138: public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInProgress()
139: throws SQLException {
140: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
141: ResultSet.CONCUR_UPDATABLE);
142: ResultSet rs = s.executeQuery(selectStatement);
143:
144: for (int i = 0; i < this .recordCount / 2; i++) {
145: rs.next();
146: verifyTuple(rs);
147: }
148: updateTuple(rs);
149: commit(); // Scan is in progress
150:
151: // Verifies resultset can do updates after compress
152: verifyResultSetUpdatableAfterCompress(rs);
153: s.close();
154:
155: }
156:
157: /**
158: * Test that a forward only resultset can be held over commit while
159: * it has not done any scanning, and that after a compress it is
160: * still updatable.
161: */
162: public void testCompressOnHeldForwardOnlyUpdatableResultSetScanInit()
163: throws SQLException {
164: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
165: ResultSet.CONCUR_UPDATABLE);
166: ResultSet rs = s.executeQuery(selectStatement);
167: commit(); // scan initialized
168:
169: // Verifies resultset can do updates after compress
170: verifyResultSetUpdatableAfterCompress(rs);
171: s.close();
172: }
173:
174: /**
175: * Test that a forward only resultset can be held over commit while
176: * it is in progress of scanning
177: */
178: public void testHeldForwardOnlyUpdatableResultSetScanInProgress()
179: throws SQLException {
180: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
181: ResultSet.CONCUR_UPDATABLE);
182: ResultSet rs = s.executeQuery(selectStatement);
183:
184: for (int i = 0; i < this .recordCount / 2; i++) {
185: rs.next();
186: verifyTuple(rs);
187: }
188: updateTuple(rs);
189: commit(); // Scan is in progress
190: rs.next();
191: updateTuple(rs); // Still updatable
192: while (rs.next()) {
193: verifyTuple(rs); // complete the scan
194: }
195: s.close();
196: }
197:
198: /**
199: * Test that a scrollable resultset can be held over commit while
200: * it has not done any scanning
201: */
202: public void testHeldScrollableResultSetScanInit()
203: throws SQLException {
204: Statement s = createStatement(
205: ResultSet.TYPE_SCROLL_INSENSITIVE,
206: ResultSet.CONCUR_READ_ONLY);
207: ResultSet rs = s.executeQuery(selectStatement);
208:
209: commit(); // scan initialized
210:
211: scrollForward(rs);
212: scrollBackward(rs);
213:
214: s.close();
215: }
216:
217: /**
218: * Test that a scrollable resultset can be held over commit while
219: * it is in progress of scanning
220: */
221: public void testHeldScrollableResultSetScanInProgress()
222: throws SQLException {
223: Statement s = createStatement(
224: ResultSet.TYPE_SCROLL_INSENSITIVE,
225: ResultSet.CONCUR_READ_ONLY);
226: ResultSet rs = s.executeQuery(selectStatement);
227:
228: for (int i = 0; i < this .recordCount / 2; i++) {
229: rs.next();
230: verifyTuple(rs);
231: }
232: commit(); // Scan is in progress
233:
234: while (rs.next()) {
235: verifyTuple(rs);
236: }
237: scrollBackward(rs);
238: s.close();
239: }
240:
241: /**
242: * Test that a scrollable resultset can be held over commit
243: * after the resultset has been populated
244: */
245: public void testHeldScrollableResultSetScanDone()
246: throws SQLException {
247: Statement s = createStatement(
248: ResultSet.TYPE_SCROLL_INSENSITIVE,
249: ResultSet.CONCUR_READ_ONLY);
250: ResultSet rs = s.executeQuery(selectStatement);
251:
252: scrollForward(rs); // Scan is done
253:
254: commit();
255:
256: scrollBackward(rs);
257: s.close();
258: }
259:
260: /**
261: * Test that a scrollable updatable resultset can be held over commit
262: * while it has not done any scanning
263: */
264: public void testHeldScrollableUpdatableResultSetScanInit()
265: throws SQLException {
266: Statement s = createStatement(
267: ResultSet.TYPE_SCROLL_INSENSITIVE,
268: ResultSet.CONCUR_UPDATABLE);
269: ResultSet rs = s.executeQuery(selectStatement);
270:
271: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
272: assertTrue(
273: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
274: false);
275: }
276: commit(); // scan initialized
277:
278: scrollForward(rs);
279: scrollBackwardAndUpdate(rs);
280:
281: s.close();
282: }
283:
284: /**
285: * Test that a scrollable updatable resultset can be held over commit while
286: * it is in progress of scanning
287: */
288: public void testHeldScrollableUpdatableResultSetScanInProgress()
289: throws SQLException {
290: Statement s = createStatement(
291: ResultSet.TYPE_SCROLL_INSENSITIVE,
292: ResultSet.CONCUR_UPDATABLE);
293: ResultSet rs = s.executeQuery(selectStatement);
294: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
295: assertTrue(
296: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
297: false);
298: }
299: for (int i = 0; i < this .recordCount / 2; i++) {
300: rs.next();
301: verifyTuple(rs);
302: }
303: commit(); // Scan is in progress
304:
305: while (rs.next()) {
306: verifyTuple(rs);
307: }
308: scrollBackwardAndUpdate(rs);
309:
310: s.close();
311: }
312:
313: /**
314: * Test that a scrollable updatable resultset can be held over commit
315: * after the resultset has been populated
316: */
317: public void testHeldScrollableUpdatableResultSetScanDone()
318: throws SQLException {
319: Statement s = createStatement(
320: ResultSet.TYPE_SCROLL_INSENSITIVE,
321: ResultSet.CONCUR_UPDATABLE);
322: ResultSet rs = s.executeQuery(selectStatement);
323:
324: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
325: assertTrue(
326: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
327: false);
328: }
329:
330: scrollForward(rs); // Scan is done
331:
332: commit();
333:
334: scrollBackwardAndUpdate(rs);
335:
336: s.close();
337: }
338:
339: /**
340: * Test that updateRow() after a commit requires a renavigation
341: * on a held forward only ResulTset.
342: */
343: public void testUpdateRowAfterCommitOnHeldForwardOnlyResultSet()
344: throws SQLException {
345: Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
346: ResultSet.CONCUR_UPDATABLE);
347: ResultSet rs = s.executeQuery(selectStatement);
348:
349: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
350: assertTrue(
351: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
352: false);
353: }
354: rs.next();
355: commit();
356: try {
357: rs.updateInt(2, -100);
358: rs.updateRow();
359: assertTrue("Expected updateRow() to throw exception", false);
360: } catch (SQLException e) {
361: assertEquals("Unexpected SQLState",
362: INVALID_CURSOR_STATE_NO_CURRENT_ROW, e
363: .getSQLState());
364: }
365: s.close();
366: }
367:
368: /**
369: * Test that updateRow() after a commit requires a renavigation
370: * on a held scrollinsensitve ResulTset.
371: */
372: public void testUpdateRowAfterCommitOnHeldScrollInsensitiveResultSet()
373: throws SQLException {
374: Statement s = createStatement(
375: ResultSet.TYPE_SCROLL_INSENSITIVE,
376: ResultSet.CONCUR_UPDATABLE);
377: ResultSet rs = s.executeQuery(selectStatement);
378:
379: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
380: assertTrue(
381: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
382: false);
383: }
384: rs.next();
385: commit();
386: try {
387: rs.updateInt(2, -100);
388: rs.updateRow();
389: assertTrue("Expected updateRow() to throw exception", false);
390: } catch (SQLException e) {
391: assertEquals("Unexpected SQLState",
392: INVALID_CURSOR_STATE_NO_CURRENT_ROW, e
393: .getSQLState());
394: }
395: s.close();
396: }
397:
398: /**
399: * Test that running a compress on a holdable scrollable updatable
400: * resultset will not invalidate the ResultSet from doing updates,
401: * if the scan is initialized
402: */
403: public void testCompressOnHeldScrollableUpdatableResultSetScanInit()
404: throws SQLException {
405: // First: Read all records in the table into the ResultSet:
406: Statement s = createStatement(
407: ResultSet.TYPE_SCROLL_INSENSITIVE,
408: ResultSet.CONCUR_UPDATABLE);
409:
410: ResultSet rs = s.executeQuery(selectStatement);
411: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
412: assertTrue(
413: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
414: false);
415: }
416: commit(); // commit
417:
418: // Verifies resultset can do updates after compress
419: verifyResultSetUpdatableAfterCompress(rs);
420:
421: s.close();
422: }
423:
424: /**
425: * Test that running a compress on a holdable scrollable updatable
426: * resultset will invalidate the Resultset from doing updates after
427: * a renavigate, if the scan is in progress.
428: */
429: public void testCompressOnHeldScrollableUpdatableResultSetScanInProgress()
430: throws SQLException {
431: // First: Read all records in the table into the ResultSet:
432: Statement s = createStatement(
433: ResultSet.TYPE_SCROLL_INSENSITIVE,
434: ResultSet.CONCUR_UPDATABLE);
435: ResultSet rs = s.executeQuery(selectStatement);
436: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
437: assertTrue(
438: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
439: false);
440: }
441: rs.next(); // Scan is in progress.
442:
443: commit(); // commit, releases the lock on the records
444:
445: verifyCompressInvalidation(rs);
446:
447: s.close();
448: }
449:
450: /**
451: * Test that running a compress on a holdable scrollable updatable
452: * resultset will invalidate the Resultset from doing updates after
453: * a renavigate.
454: */
455: public void testCompressOnHeldScrollableUpdatableResultSetScanDone()
456: throws SQLException {
457: // First: Read all records in the table into the ResultSet:
458: Statement s = createStatement(
459: ResultSet.TYPE_SCROLL_INSENSITIVE,
460: ResultSet.CONCUR_UPDATABLE);
461: ResultSet rs = s.executeQuery(selectStatement);
462: if (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
463: assertTrue(
464: "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: // Delete all records except the first:
483: Statement delStatement = createStatement();
484: int deleted = delStatement
485: .executeUpdate("delete from T1 where id>0");
486: int expectedDeleted = recordCount - 1;
487:
488: assertEquals("Invalid number of records deleted",
489: expectedDeleted, deleted);
490: commit();
491:
492: // Execute online compress
493: onlineCompress(true, true, true);
494:
495: // Now reinsert the tuples:
496: PreparedStatement ps = prepareStatement("insert into t1 values (?,?,?,?)");
497:
498: for (int i = 0; i < recordCount * 2; i++) {
499: int recordId = i + recordCount + 1000;
500: ps.setInt(1, recordId);
501: ps.setInt(2, recordId);
502: ps.setInt(3, recordId * 2 + 17);
503: ps.setString(4, "m" + recordId);
504: ps.addBatch();
505: }
506: ps.executeBatch();
507: commit();
508:
509: rs.next();
510: updateTuple(rs);
511:
512: SQLWarning warn = rs.getWarnings();
513: assertNull("Expected no warning when updating this row", warn);
514:
515: // This part if only for scrollable resultsets
516: if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
517:
518: // Update last tuple
519: rs.last();
520: updateTuple(rs);
521:
522: warn = rs.getWarnings();
523: assertNull("Expected no warning when updating this row",
524: 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",
531: warn);
532: }
533:
534: commit();
535:
536: // Verify data
537: rs = createStatement().executeQuery(selectStatement);
538: while (rs.next()) {
539: verifyTuple(rs);
540: }
541: }
542:
543: /**
544: * Verifies that the ResultSet is invalidated from doing updates after
545: * a compress.
546: * @param rs ResultSet which we test is being invalidated
547: */
548: private void verifyCompressInvalidation(ResultSet rs)
549: throws SQLException {
550:
551: // Delete all records except the first:
552: Statement delStatement = createStatement();
553: int deleted = delStatement
554: .executeUpdate("delete from T1 where id>0");
555: int expectedDeleted = recordCount - 1;
556: delStatement.close();
557:
558: assertEquals("Invalid number of records deleted",
559: expectedDeleted, deleted);
560: commit();
561:
562: // Execute online compress
563: onlineCompress(true, true, true);
564:
565: // Now reinsert the tuples:
566: PreparedStatement ps = 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, boolean defragment,
611: boolean truncate) throws SQLException {
612: // Use a new connection to compress the table
613: final Connection con2 = openDefaultConnection();
614: final String connId = con2.toString();
615: con2
616: .setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
617:
618: final PreparedStatement ps2 = con2
619: .prepareStatement("call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE(?,?,?,?,?)");
620: ps2.setString(1, "APP"); // schema
621: ps2.setString(2, "T1"); // table name
622: ps2.setBoolean(3, purge);
623: ps2.setBoolean(4, defragment);
624: ps2.setBoolean(5, truncate);
625:
626: try {
627: ps2.executeUpdate();
628: ps2.close();
629: con2.commit();
630: } finally {
631: con2.close();
632: }
633: }
634:
635: private final static String selectStatement = "select * from t1";
636: }
|