001: /*
002: *
003: * Derby - Class SURQueryMixTest
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 java.sql.DatabaseMetaData;
023: import java.sql.ResultSet;
024: import java.sql.ResultSetMetaData;
025: import java.sql.SQLException;
026: import java.sql.PreparedStatement;
027: import java.sql.Statement;
028: import java.sql.Types;
029: import java.util.ArrayList;
030: import java.util.HashMap;
031: import java.util.HashSet;
032: import java.util.Iterator;
033: import java.util.List;
034: import java.util.Map;
035: import java.util.Random;
036: import java.util.Set;
037: import junit.extensions.TestSetup;
038: import junit.framework.Test;
039: import junit.framework.TestSuite;
040:
041: /**
042: * Tests for Scrollable Updatable ResultSet (SUR). This TestCase tests
043: * scrolling (navigation), updates (using updateXXX() and updateRow() or
044: * positioned updates), deletion of records (using deleteRow() or positioned
045: * deletes) of ResultSets.
046: * @author Andreas Korneliussen
047: */
048: public class SURQueryMixTest extends SURBaseTest {
049: /**
050: * Constructor
051: * @param name model name of data model for this TestCase
052: * @param query to use for producing the resultset
053: * @param cursorName name of cursor
054: * @param positioned flag to determine if the Test should use positioned
055: * updates/deletes instead of updateRow() and deleteRow()
056: */
057: public SURQueryMixTest(final String model, final String query,
058: final String cursorName, final boolean positioned) {
059: super ("SURQueryMixTest{Model=" + model + ",Query=" + query
060: + ",Cursor=" + cursorName + ",Positioned=" + positioned
061: + "}");
062: this .query = query;
063: this .cursorName = cursorName;
064: this .positioned = positioned;
065: this .checkRowUpdated = false;
066: this .checkRowDeleted = false;
067: }
068:
069: /**
070: * Test SUR properties of the query
071: */
072: public void runTest() throws SQLException {
073: println(query);
074: DatabaseMetaData dbMeta = getConnection().getMetaData();
075:
076: if (dbMeta
077: .ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)) {
078: checkRowDeleted = true;
079: }
080:
081: Statement s = createStatement(
082: ResultSet.TYPE_SCROLL_INSENSITIVE,
083: ResultSet.CONCUR_UPDATABLE);
084:
085: s.setCursorName(cursorName);
086: ResultSet rs = s.executeQuery(query);
087:
088: checkRowUpdated = dbMeta.ownUpdatesAreVisible(rs.getType());
089: checkRowDeleted = dbMeta.ownDeletesAreVisible(rs.getType());
090:
091: // Create map with rows
092: Map rows = createRowMap(rs);
093:
094: // Set of rows which are updated (contains Integer with position in RS)
095: final Set updatedRows = new HashSet();
096:
097: // Set of rows which are deleted (contains Integer with position in RS)
098: final Set deletedRows = new HashSet();
099:
100: // Test navigation
101: testNavigation(rs, rows, updatedRows, deletedRows);
102:
103: // Only test updatability if the ResultSet is updatable:
104: // (Note: this enables the test do run successfully even if
105: // scrollable updatable resultsets are not implemented.
106: // If SUR is not implemented, a well behaved JDBC driver will
107: // downgrade the concurrency mode to READ_ONLY).
108: // SUR may be implemented incrementally, i.e first in embedded mode
109: // then in the network driver.)
110: if (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
111:
112: // update a random sample of 2 records
113: updateRandomSampleOfNRecords(rs, rows, updatedRows, 2);
114: testNavigation(rs, rows, updatedRows, deletedRows);
115:
116: // update a random sample of 5 records
117: updateRandomSampleOfNRecords(rs, rows, updatedRows, 5);
118: testNavigation(rs, rows, updatedRows, deletedRows);
119:
120: // update a random sample of 10 records
121: updateRandomSampleOfNRecords(rs, rows, updatedRows, 10);
122: testNavigation(rs, rows, updatedRows, deletedRows);
123:
124: // delete a random sample of 2 records
125: deleteRandomSampleOfNRecords(rs, rows, deletedRows, 2);
126: testNavigation(rs, rows, updatedRows, deletedRows);
127:
128: // delete a random sample of 5 records
129: deleteRandomSampleOfNRecords(rs, rows, deletedRows, 5);
130: testNavigation(rs, rows, updatedRows, deletedRows);
131:
132: // delete a random sample of 10 records
133: deleteRandomSampleOfNRecords(rs, rows, deletedRows, 10);
134: testNavigation(rs, rows, updatedRows, deletedRows);
135: } else {
136: assertTrue(
137: "ResultSet concurrency downgraded to CONCUR_READ_ONLY",
138: false);
139: }
140:
141: rs.close();
142: s.close();
143: }
144:
145: /**
146: * Creates a Map of the values in the ResultSet.
147: * The key object in the map, is the postion in the
148: * ResultSet (Integer 1..n), while the value is a
149: * concatenation of the strings for all columns in the row.
150: */
151: private Map createRowMap(final ResultSet rs) throws SQLException {
152: final Map rows = new HashMap();
153: rs.beforeFirst();
154: assertTrue("Unexpected return from isBeforeFirst()", rs
155: .isBeforeFirst());
156:
157: int i = 0;
158: int sum = 0;
159: int expectedSum = 0;
160: boolean checkSum = true;
161: while (rs.next()) {
162: expectedSum += i;
163: i++;
164: String row = getRowString(rs);
165: println(row);
166: rows.put(new Integer(i), row);
167: sum += rs.getInt(1);
168: if (rs.getInt(1) < 0) {
169: checkSum = false;
170: }
171: }
172: if (i < SURDataModelSetup.recordCount) {
173: checkSum = false;
174: }
175:
176: assertTrue("Unexpected return from isAfterLast()", rs
177: .isAfterLast());
178:
179: if (checkSum) {
180: assertEquals("Sum for column 1 is not correct",
181: expectedSum, sum);
182: }
183:
184: return rows;
185: }
186:
187: /**
188: * Create a random sample of rows
189: * @param rows Map to create sample from
190: * @param k number of rows in the sample
191: * @return a list containing k elements of rows
192: **/
193: private List createRandomSample(final Map rows, int k) {
194: Random r = new Random();
195: ArrayList sampledKeys = new ArrayList();
196: int n = 0;
197: for (Iterator i = rows.keySet().iterator(); i.hasNext();) {
198: Object key = i.next();
199: n++;
200: if (n <= k) {
201: sampledKeys.add(key);
202: } else {
203: // sampledKeys now has a size of k
204: double d = r.nextDouble();
205: // p = probability of going into the sample
206: double p = (double) k / (double) n;
207: if (d < p) {
208: // Replace a random value from the sample with the new value
209: int keyToReplace = Math.abs(r.nextInt()) % k;
210: sampledKeys.set(keyToReplace, key);
211: }
212: }
213: }
214: return sampledKeys;
215: }
216:
217: /**
218: * Delete a random sample of n records in the resultset
219: * @param rs result set to be updated
220: * @param rows map of rows, will also be updated
221: * @param deletedRows set of rows being deleted (position in RS)
222: * @param k number of records to be deleted
223: */
224: private void deleteRandomSampleOfNRecords(final ResultSet rs,
225: final Map rows, final Set deletedRows, final int k)
226: throws SQLException {
227: List sampledKeys = createRandomSample(rows, k);
228: println("Sampled keys:" + sampledKeys);
229: ResultSetMetaData meta = rs.getMetaData();
230: for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
231: Integer key = (Integer) i.next();
232: rs.absolute(key.intValue());
233: if (rs.rowDeleted())
234: continue; // skip deleting row if already deleted
235: if (positioned) {
236: createStatement().executeUpdate(
237: "DELETE FROM T1 WHERE CURRENT OF \""
238: + cursorName + "\"");
239: } else {
240: rs.deleteRow();
241: }
242: rs.relative(0);
243: println("Deleted row " + key);
244: // Update the rows table
245: rows.put(key, getRowString(rs));
246:
247: // Update the updatedRows set
248: deletedRows.add(key);
249: }
250: }
251:
252: /**
253: * Update a random sample of n records in the resultset
254: * @param rs result set to be updated
255: * @param rows map of rows, will also be updated
256: * @param updatedRows set of being updated (position in RS)
257: * @param k number of records to be updated
258: */
259: private void updateRandomSampleOfNRecords(final ResultSet rs,
260: final Map rows, final Set updatedRows, final int k)
261: throws SQLException {
262: List sampledKeys = createRandomSample(rows, k);
263: println("Sampled keys:" + sampledKeys);
264: ResultSetMetaData meta = rs.getMetaData();
265: for (Iterator i = sampledKeys.iterator(); i.hasNext();) {
266: Integer key = (Integer) i.next();
267: rs.absolute(key.intValue());
268:
269: if (positioned) {
270: updatePositioned(rs, meta);
271: rs.relative(0); // If this call is not here, the old values are
272: // returned in rs.getXXX calls
273: } else {
274: updateRow(rs, meta);
275: }
276: // Update the rows table
277: rows.put(key, getRowString(rs));
278:
279: // Update the updatedRows set
280: updatedRows.add(key);
281: }
282: }
283:
284: /**
285: * Updates the current row in the ResultSet using updateRow()
286: * @param rs ResultSet to be updated
287: * @param meta meta for the ResultSet
288: **/
289: private void updateRow(final ResultSet rs,
290: final ResultSetMetaData meta) throws SQLException {
291: for (int column = 1; column <= meta.getColumnCount(); column++) {
292: if (meta.getColumnType(column) == Types.INTEGER) {
293: // Set to negative value
294: rs.updateInt(column, -rs.getInt(column));
295: } else {
296: rs.updateString(column, "UPDATED_"
297: + rs.getString(column));
298: }
299: }
300: rs.updateRow();
301: }
302:
303: /**
304: * Updates the current row in the ResultSet using updateRow()
305: * @param rs ResultSet to be updated
306: * @param meta meta for the ResultSet
307: **/
308: private void updatePositioned(final ResultSet rs,
309: final ResultSetMetaData meta) throws SQLException {
310: StringBuffer sb = new StringBuffer();
311: sb.append("UPDATE T1 SET ");
312: for (int column = 1; column <= meta.getColumnCount(); column++) {
313: sb.append(meta.getColumnName(column));
314: sb.append("=?");
315: if (column < meta.getColumnCount()) {
316: sb.append(",");
317: }
318: }
319: sb.append(" WHERE CURRENT OF \"");
320: sb.append(cursorName);
321: sb.append("\"");
322: println(sb.toString());
323: PreparedStatement ps = prepareStatement(sb.toString());
324:
325: for (int column = 1; column <= meta.getColumnCount(); column++) {
326: if (meta.getColumnType(column) == Types.INTEGER) {
327: // Set to negative value
328: ps.setInt(column, -rs.getInt(column));
329: } else {
330: ps.setString(column, "UPDATED_" + rs.getString(column));
331: }
332: }
333: assertEquals("Expected one row to be updated", 1, ps
334: .executeUpdate());
335: }
336:
337: /**
338: * Tests navigation in ResultSet.
339: * @param ResultSet rs ResultSet to test navigation of.
340: * Needs to be scrollable
341: * @param Map rows a sample of the rows which are in the ResultSet. Maps
342: * position to a concatenation of the string values
343: * @param Set updatedRows a integer set of which rows that have been
344: * updated. Used to test rowUpdated()
345: * @param Set deletedRows a integer set of which rows that have been
346: * deleted. Used to test rowDeleted()
347: */
348: private void testNavigation(final ResultSet rs, final Map rows,
349: final Set updatedRows, final Set deletedRows)
350: throws SQLException {
351: rs.afterLast();
352: {
353: int i = rows.size();
354: while (rs.previous()) {
355: String rowString = getRowString(rs);
356: assertEquals(
357: "Navigating with rs.previous(). The row is "
358: + "different compared to the value when navigating "
359: + "forward.", rows.get(new Integer(i)),
360: rowString);
361:
362: if (checkRowUpdated
363: && updatedRows.contains(new Integer(i))) {
364: assertTrue(
365: "Expected rs.rowUpdated() to return true on "
366: + "updated row " + rowString, rs
367: .rowUpdated());
368: }
369: if (checkRowDeleted
370: && deletedRows.contains(new Integer(i))) {
371: assertTrue(
372: "Expected rs.rowDeleted() to return true on "
373: + "deleted row " + rowString, rs
374: .rowDeleted());
375: }
376: i--;
377: }
378: }
379: // Test absolute
380: for (int i = 1; i <= rows.size(); i++) {
381: assertTrue("Unexpected return from absolute()", rs
382: .absolute(i));
383: String rowString = getRowString(rs);
384: assertEquals("Navigating with rs.absolute(). The row is "
385: + "different compared to the value"
386: + " when navigating forward.", rows
387: .get(new Integer(i)), rowString);
388: if (checkRowUpdated && updatedRows.contains(new Integer(i))) {
389: assertTrue(
390: "Expected rs.rowUpdated() to return true on "
391: + "updated row " + rowString, rs
392: .rowUpdated());
393: }
394: if (checkRowDeleted && deletedRows.contains(new Integer(i))) {
395: assertTrue(
396: "Expected rs.rowDeleted() to return true on "
397: + "deleted row " + rowString, rs
398: .rowDeleted());
399: }
400: }
401: assertFalse("Unexpected return from absolute()", rs.absolute(0));
402: assertTrue("Unexpected return from isBeforeFirst()", rs
403: .isBeforeFirst());
404: assertFalse("Unexpected return from absolute()", rs
405: .absolute(rows.size() + 1));
406: assertTrue("Unexpected return from isAfterLast()", rs
407: .isAfterLast());
408: assertTrue("Unexpected return from absolute()", rs.absolute(-1));
409: assertTrue("Unexpected return from isLast()", rs.isLast());
410: assertTrue("Unexpected return from absolute()", rs.absolute(1));
411: assertTrue("Unexpected return from isFirst()", rs.isFirst());
412:
413: // Test relative
414: {
415: rs.beforeFirst();
416: assertTrue("Unexptected return from isBeforeFirst()", rs
417: .isBeforeFirst());
418:
419: int relativePos = rows.size();
420: assertTrue("Unexpected return from relative()", rs
421: .relative(relativePos));
422:
423: // Should now be on the last row
424: assertTrue("Unexptected return from isLast()", rs.isLast());
425: assertEquals("Navigating with rs.relative(+). "
426: + "A tuple was different compared to the value"
427: + " when navigating forward.", rows
428: .get(new Integer(relativePos)), getRowString(rs));
429:
430: assertTrue("Unexpected return from relative()", rs
431: .relative((-relativePos + 1)));
432:
433: // Should now be on the first row
434: assertTrue("Unexptected return from isFirst()", rs
435: .isFirst());
436:
437: assertEquals("Navigating with rs.relative(-). "
438: + "A tuple was different compared to the value"
439: + " when navigating forward.", rows
440: .get(new Integer(1)), getRowString(rs));
441:
442: }
443: // Test navigation in the end of the ResultSet
444: rs.afterLast();
445: assertTrue("Unexpected return from isAfterLast()", rs
446: .isAfterLast());
447: assertTrue("Unexpected return from previous()", rs.previous());
448: assertTrue("Unexpected return from isLast()", rs.isLast());
449: assertFalse("Unexpected return from next()", rs.next());
450: assertTrue("Unexpected return from isAfterLast()", rs
451: .isAfterLast());
452: rs.last();
453: assertTrue("Unexpected return from isLast()", rs.isLast());
454: assertFalse("Unexpected return from next()", rs.next());
455: assertTrue("Unexpected return from isAfterLast()", rs
456: .isAfterLast());
457:
458: // Test navigation in the beginning of the ResultSet
459: rs.beforeFirst();
460: assertTrue("Unexpected return from isBeforeFirst()", rs
461: .isBeforeFirst());
462: assertTrue("Unexpected return from next()", rs.next());
463: assertTrue("Unexpected return from isFirst", rs.isFirst());
464: assertFalse("Unexpected return from previous()", rs.previous());
465: assertTrue("Unexpected return from isBeforeFirst()", rs
466: .isBeforeFirst());
467:
468: rs.first();
469: assertTrue("Unexpected return from isFirst", rs.isFirst());
470: assertFalse("Unexpected return from previous()", rs.previous());
471: assertTrue("Unexpected return from isBeforeFirst()", rs
472: .isBeforeFirst());
473: }
474:
475: /**
476: * Get a concatenation of the values of the
477: * current Row in the ResultSet
478: */
479: private String getRowString(final ResultSet rs) throws SQLException {
480: int numberOfColumns = rs.getMetaData().getColumnCount();
481: StringBuffer sb = new StringBuffer();
482: if (rs.rowDeleted())
483: return "";
484: for (int i = 1; i <= numberOfColumns; i++) {
485: sb.append(rs.getString(i));
486: if (i < numberOfColumns) {
487: sb.append(',');
488: }
489: }
490: return sb.toString();
491: }
492:
493: private final String query;
494: private final String cursorName;
495: private final boolean positioned;
496: private boolean checkRowUpdated;
497: private boolean checkRowDeleted;
498:
499: private final static String[] selectConditions = new String[] {
500: "WHERE c like 'T%'", " ", "WHERE b > 5", "WHERE id >= a",
501: "WHERE id > 1 and id < 900", "WHERE id = 1",
502: "WHERE id in (1,3,4,600,900,955,966,977,978)",
503: "WHERE a in (1,3,4,600,9200,955,966,977,978)",
504: "WHERE a>2 and a<9000" };
505:
506: private final static String[] projectConditions = new String[] {
507: "id,c,a,b", "id,c", "a,b", "*", "id,a,b,c", "id,a",
508: "a,b,c", "a,c" };
509:
510: private static TestSuite createTestCases(final String modelName) {
511: TestSuite suite = new TestSuite();
512: for (int doPos = 0; doPos < 2; doPos++) {
513: boolean positioned = doPos > 0; // true if to use positioned updates
514:
515: for (int i = 0; i < selectConditions.length; i++) {
516: for (int j = 0; j < projectConditions.length; j++) {
517: final String cursorName = "cursor_" + i + "_" + j;
518:
519: final String stmtString = "SELECT "
520: + projectConditions[j] + " FROM T1 "
521: + selectConditions[i];
522: suite.addTest(new SURQueryMixTest(modelName,
523: stmtString, cursorName, positioned));
524: }
525: }
526: }
527: return suite;
528: }
529:
530: /**
531: * The suite contains all testcases in this class running on different data models
532: */
533: public static Test suite() {
534: TestSuite mainSuite = new TestSuite();
535:
536: // DB2 client doesn't support this functionality
537: if (usingDerbyNet())
538: return mainSuite;
539:
540: // Iterate over all data models and decorate the tests:
541: for (Iterator i = SURDataModelSetup.SURDataModel.values()
542: .iterator(); i.hasNext();) {
543:
544: SURDataModelSetup.SURDataModel model = (SURDataModelSetup.SURDataModel) i
545: .next();
546:
547: TestSuite suite = createTestCases(model.toString());
548: TestSetup decorator = new SURDataModelSetup(suite, model);
549: mainSuite.addTest(decorator);
550: }
551: return mainSuite;
552: }
553:
554: protected void tearDown() throws Exception {
555: super.tearDown();
556: con = null;
557: }
558:
559: }
|