| /* |
| * |
| * Derby - Class SURQueryMixTest |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one or more |
| * contributor license agreements. See the NOTICE file distributed with |
| * this work for additional information regarding copyright ownership. |
| * The ASF licenses this file to You under the Apache License, Version 2.0 |
| * (the "License"); you may not use this file except in compliance with |
| * the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, |
| * either express or implied. See the License for the specific |
| * language governing permissions and limitations under the License. |
| */ |
| package org.apache.derbyTesting.functionTests.tests.jdbcapi; |
| import java.sql.DatabaseMetaData; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Types; |
| import java.util.ArrayList; |
| import java.util.HashMap; |
| import java.util.HashSet; |
| import java.util.Iterator; |
| import java.util.List; |
| import java.util.Map; |
| import java.util.Random; |
| import java.util.Set; |
| import junit.extensions.TestSetup; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Tests for Scrollable Updatable ResultSet (SUR). This TestCase tests |
| * scrolling (navigation), updates (using updateXXX() and updateRow() or |
| * positioned updates), deletion of records (using deleteRow() or positioned |
| * deletes) of ResultSets. |
| */ |
| public class SURQueryMixTest extends SURBaseTest |
| { |
| /** |
| * Constructor |
| * @param model name of data model for this TestCase |
| * @param query to use for producing the resultset |
| * @param cursorName name of cursor |
| * @param positioned flag to determine if the Test should use positioned |
| * updates/deletes instead of updateRow() and deleteRow() |
| */ |
| public SURQueryMixTest(final String model, final String query, |
| final String cursorName, final boolean positioned) |
| { |
| super("SURQueryMixTest{Model=" + model + ",Query=" +query + ",Cursor=" |
| + cursorName + ",Positioned=" + positioned + "}"); |
| this.query = query; |
| this.cursorName = cursorName; |
| this.positioned = positioned; |
| this.checkRowUpdated = false; |
| this.checkRowDeleted = false; |
| } |
| |
| /** |
| * Test SUR properties of the query |
| */ |
| public void runTest() |
| throws SQLException |
| { |
| println(query); |
| DatabaseMetaData dbMeta = getConnection().getMetaData(); |
| |
| if (dbMeta.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)) { |
| checkRowDeleted = true; |
| } |
| |
| Statement s = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, |
| ResultSet.CONCUR_UPDATABLE); |
| |
| s.setCursorName(cursorName); |
| ResultSet rs = s.executeQuery(query); |
| |
| checkRowUpdated = dbMeta.ownUpdatesAreVisible(rs.getType()); |
| checkRowDeleted = dbMeta.ownDeletesAreVisible(rs.getType()); |
| |
| // Create map with rows |
| Map<Integer, String> rows = createRowMap(rs); |
| |
| // Set of rows which are updated (contains Integer with position in RS) |
| final Set<Integer> updatedRows = new HashSet<Integer>(); |
| |
| // Set of rows which are deleted (contains Integer with position in RS) |
| final Set<Integer> deletedRows = new HashSet<Integer>(); |
| |
| // Test navigation |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // Only test updatability if the ResultSet is updatable: |
| // (Note: this enables the test do run successfully even if |
| // scrollable updatable resultsets are not implemented. |
| // If SUR is not implemented, a well behaved JDBC driver will |
| // downgrade the concurrency mode to READ_ONLY). |
| // SUR may be implemented incrementally, i.e first in embedded mode |
| // then in the network driver.) |
| if (rs.getConcurrency()==ResultSet.CONCUR_UPDATABLE) { |
| |
| // update a random sample of 2 records |
| updateRandomSampleOfNRecords(rs, rows, updatedRows, 2); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // update a random sample of 5 records |
| updateRandomSampleOfNRecords(rs, rows, updatedRows, 5); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // update a random sample of 10 records |
| updateRandomSampleOfNRecords(rs, rows, updatedRows, 10); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // delete a random sample of 2 records |
| deleteRandomSampleOfNRecords(rs, rows, deletedRows, 2); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // delete a random sample of 5 records |
| deleteRandomSampleOfNRecords(rs, rows, deletedRows, 5); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| |
| // delete a random sample of 10 records |
| deleteRandomSampleOfNRecords(rs, rows, deletedRows, 10); |
| testNavigation(rs, rows, updatedRows, deletedRows); |
| } else { |
| assertTrue("ResultSet concurrency downgraded to CONCUR_READ_ONLY", |
| false); |
| } |
| |
| rs.close(); |
| s.close(); |
| } |
| |
| /** |
| * Creates a Map of the values in the ResultSet. |
| * The key object in the map, is the postion in the |
| * ResultSet (Integer 1..n), while the value is a |
| * concatenation of the strings for all columns in the row. |
| */ |
| private Map<Integer, String> createRowMap(final ResultSet rs) |
| throws SQLException |
| { |
| final Map<Integer, String> rows = new HashMap<Integer, String>(); |
| rs.beforeFirst(); |
| assertTrue("Unexpected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| |
| int i = 0; |
| int sum = 0; |
| int expectedSum = 0; |
| boolean checkSum = true; |
| while (rs.next()) { |
| expectedSum += i; |
| i++; |
| String row = getRowString(rs); |
| println(row); |
| rows.put(i, row); |
| sum += rs.getInt(1); |
| if (rs.getInt(1) < 0) { |
| checkSum = false; |
| } |
| } |
| if (i<SURDataModelSetup.recordCount) { |
| checkSum = false; |
| } |
| |
| assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast()); |
| |
| if (checkSum) { |
| assertEquals("Sum for column 1 is not correct", expectedSum, sum); |
| } |
| |
| return rows; |
| } |
| |
| /** |
| * Create a random sample of rows |
| * @param rows Map to create sample from |
| * @param k number of rows in the sample |
| * @return a list containing k elements of rows |
| **/ |
| private List createRandomSample(final Map<Integer, String> rows, int k) { |
| Random r = new Random(); |
| ArrayList<Integer> sampledKeys = new ArrayList<Integer>(); |
| int n = 0; |
| for (Integer key : rows.keySet()) { |
| n++; |
| if (n<=k) { |
| sampledKeys.add(key); |
| } else { |
| // sampledKeys now has a size of k |
| double d = r.nextDouble(); |
| // p = probability of going into the sample |
| double p = (double) k / (double) n; |
| if (d<p) { |
| // Replace a random value from the sample with the new value |
| int keyToReplace = r.nextInt(k); |
| sampledKeys.set(keyToReplace, key); |
| } |
| } |
| } |
| return sampledKeys; |
| } |
| |
| /** |
| * Delete a random sample of n records in the resultset |
| * @param rs result set to be updated |
| * @param rows map of rows, will also be updated |
| * @param deletedRows set of rows being deleted (position in RS) |
| * @param k number of records to be deleted |
| */ |
| private void deleteRandomSampleOfNRecords(final ResultSet rs, |
| final Map<Integer, String> rows, |
| final Set<Integer> deletedRows, |
| final int k) |
| throws SQLException |
| { |
| List sampledKeys = createRandomSample(rows, k); |
| println("Sampled keys:" + sampledKeys); |
| ResultSetMetaData meta = rs.getMetaData(); |
| for (Iterator i = sampledKeys.iterator(); i.hasNext();) { |
| Integer key = (Integer) i.next(); |
| rs.absolute(key.intValue()); |
| if (rs.rowDeleted()) continue; // skip deleting row if already deleted |
| if (positioned) { |
| createStatement().executeUpdate |
| ("DELETE FROM T1 WHERE CURRENT OF \"" + cursorName + |
| "\""); |
| } else { |
| rs.deleteRow(); |
| } |
| rs.relative(0); |
| println("Deleted row " + key); |
| // Update the rows table |
| rows.put(key, getRowString(rs)); |
| |
| // Update the updatedRows set |
| deletedRows.add(key); |
| } |
| } |
| |
| /** |
| * Update a random sample of n records in the resultset |
| * @param rs result set to be updated |
| * @param rows map of rows, will also be updated |
| * @param updatedRows set of being updated (position in RS) |
| * @param k number of records to be updated |
| */ |
| private void updateRandomSampleOfNRecords(final ResultSet rs, |
| final Map<Integer, String> rows, |
| final Set<Integer> updatedRows, |
| final int k) |
| throws SQLException |
| { |
| List sampledKeys = createRandomSample(rows, k); |
| println("Sampled keys:" + sampledKeys); |
| ResultSetMetaData meta = rs.getMetaData(); |
| for (Iterator i = sampledKeys.iterator(); i.hasNext();) { |
| Integer key = (Integer) i.next(); |
| rs.absolute(key.intValue()); |
| |
| if (positioned) { |
| updatePositioned(rs, meta); |
| rs.relative(0); // If this call is not here, the old values are |
| // returned in rs.getXXX calls |
| } else { |
| updateRow(rs, meta); |
| } |
| // Update the rows table |
| rows.put(key, getRowString(rs)); |
| |
| // Update the updatedRows set |
| updatedRows.add(key); |
| } |
| } |
| |
| /** |
| * Updates the current row in the ResultSet using updateRow() |
| * @param rs ResultSet to be updated |
| * @param meta meta for the ResultSet |
| **/ |
| private void updateRow(final ResultSet rs, final ResultSetMetaData meta) |
| throws SQLException |
| { |
| for (int column = 1; column<=meta.getColumnCount(); column++) { |
| if (meta.getColumnType(column)==Types.INTEGER) { |
| // Set to negative value |
| rs.updateInt(column, -rs.getInt(column)); |
| } else { |
| rs.updateString(column, "UPDATED_" + rs.getString(column)); |
| } |
| } |
| rs.updateRow(); |
| } |
| |
| /** |
| * Updates the current row in the ResultSet using updateRow() |
| * @param rs ResultSet to be updated |
| * @param meta meta for the ResultSet |
| **/ |
| private void updatePositioned(final ResultSet rs, |
| final ResultSetMetaData meta) |
| throws SQLException |
| { |
| StringBuffer sb = new StringBuffer(); |
| sb.append("UPDATE T1 SET "); |
| for (int column = 1; column<=meta.getColumnCount(); column++) { |
| sb.append(meta.getColumnName(column)); |
| sb.append("=?"); |
| if (column<meta.getColumnCount()) { |
| sb.append(","); |
| } |
| } |
| sb.append(" WHERE CURRENT OF \""); |
| sb.append(cursorName); |
| sb.append("\""); |
| println(sb.toString()); |
| PreparedStatement ps = prepareStatement(sb.toString()); |
| |
| for (int column = 1; column<=meta.getColumnCount(); column++) { |
| if (meta.getColumnType(column)==Types.INTEGER) { |
| // Set to negative value |
| ps.setInt(column, -rs.getInt(column)); |
| } else { |
| ps.setString(column, "UPDATED_" + rs.getString(column)); |
| } |
| } |
| assertEquals("Expected one row to be updated", 1, ps.executeUpdate()); |
| } |
| |
| |
| /** |
| * Tests navigation in ResultSet. |
| * @param rs ResultSet to test navigation of. |
| * Needs to be scrollable |
| * @param rows a sample of the rows which are in the ResultSet. Maps |
| * position to a concatenation of the string values |
| * @param updatedRows a integer set of which rows that have been |
| * updated. Used to test rowUpdated() |
| * @param deletedRows a integer set of which rows that have been |
| * deleted. Used to test rowDeleted() |
| */ |
| private void testNavigation(final ResultSet rs, final Map rows, |
| final Set updatedRows, final Set deletedRows) |
| throws SQLException |
| { |
| rs.afterLast(); |
| { |
| int i = rows.size(); |
| while (rs.previous()) { |
| String rowString = getRowString(rs); |
| assertEquals("Navigating with rs.previous(). The row is " + |
| "different compared to the value when navigating " + |
| "forward.", rows.get(i), rowString); |
| |
| |
| if (checkRowUpdated && updatedRows.contains(i)) { |
| assertTrue("Expected rs.rowUpdated() to return true on " + |
| "updated row " + rowString, rs.rowUpdated()); |
| } |
| if (checkRowDeleted && deletedRows.contains(i)) { |
| assertTrue("Expected rs.rowDeleted() to return true on " + |
| "deleted row " + rowString, rs.rowDeleted()); |
| } |
| i--; |
| } |
| } |
| // Test absolute |
| for (int i = 1; i <= rows.size(); i++) { |
| assertTrue("Unexpected return from absolute()", rs.absolute(i)); |
| String rowString = getRowString(rs); |
| assertEquals("Navigating with rs.absolute(). The row is " + |
| "different compared to the value" + |
| " when navigating forward.", |
| rows.get(i), |
| rowString); |
| if (checkRowUpdated && updatedRows.contains(i)) { |
| assertTrue("Expected rs.rowUpdated() to return true on " + |
| "updated row " + rowString, rs.rowUpdated()); |
| } |
| if (checkRowDeleted && deletedRows.contains(i)) { |
| assertTrue("Expected rs.rowDeleted() to return true on " + |
| "deleted row " + rowString, rs.rowDeleted()); |
| } |
| } |
| assertFalse("Unexpected return from absolute()", rs.absolute(0)); |
| assertTrue("Unexpected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| assertFalse("Unexpected return from absolute()", |
| rs.absolute(rows.size() + 1)); |
| assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast()); |
| assertTrue("Unexpected return from absolute()", rs.absolute(-1)); |
| assertTrue("Unexpected return from isLast()", rs.isLast()); |
| assertTrue("Unexpected return from absolute()", rs.absolute(1)); |
| assertTrue("Unexpected return from isFirst()", rs.isFirst()); |
| |
| // Test relative |
| { |
| rs.beforeFirst(); |
| assertTrue("Unexptected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| |
| int relativePos = rows.size(); |
| assertTrue("Unexpected return from relative()", |
| rs.relative(relativePos)); |
| |
| // Should now be on the last row |
| assertTrue("Unexptected return from isLast()", rs.isLast()); |
| assertEquals("Navigating with rs.relative(+). " + |
| "A tuple was different compared to the value" + |
| " when navigating forward.", |
| rows.get(relativePos), |
| getRowString(rs)); |
| |
| assertTrue("Unexpected return from relative()", |
| rs.relative((-relativePos + 1))); |
| |
| // Should now be on the first row |
| assertTrue("Unexptected return from isFirst()", rs.isFirst()); |
| |
| assertEquals("Navigating with rs.relative(-). " + |
| "A tuple was different compared to the value" + |
| " when navigating forward.", |
| rows.get(1), |
| getRowString(rs)); |
| |
| } |
| // Test navigation in the end of the ResultSet |
| rs.afterLast(); |
| assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast()); |
| assertTrue("Unexpected return from previous()", rs.previous()); |
| assertTrue("Unexpected return from isLast()", rs.isLast()); |
| assertFalse("Unexpected return from next()", rs.next()); |
| assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast()); |
| rs.last(); |
| assertTrue("Unexpected return from isLast()", rs.isLast()); |
| assertFalse("Unexpected return from next()", rs.next()); |
| assertTrue("Unexpected return from isAfterLast()", rs.isAfterLast()); |
| |
| |
| // Test navigation in the beginning of the ResultSet |
| rs.beforeFirst(); |
| assertTrue("Unexpected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| assertTrue("Unexpected return from next()", rs.next()); |
| assertTrue("Unexpected return from isFirst", rs.isFirst()); |
| assertFalse("Unexpected return from previous()", rs.previous()); |
| assertTrue("Unexpected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| |
| rs.first(); |
| assertTrue("Unexpected return from isFirst", rs.isFirst()); |
| assertFalse("Unexpected return from previous()", rs.previous()); |
| assertTrue("Unexpected return from isBeforeFirst()", |
| rs.isBeforeFirst()); |
| } |
| |
| /** |
| * Get a concatenation of the values of the |
| * current Row in the ResultSet |
| */ |
| private String getRowString(final ResultSet rs) |
| throws SQLException |
| { |
| int numberOfColumns = rs.getMetaData().getColumnCount(); |
| StringBuffer sb = new StringBuffer(); |
| if (rs.rowDeleted()) return ""; |
| for (int i = 1; i <= numberOfColumns; i++) { |
| sb.append(rs.getString(i)); |
| if (i < numberOfColumns) { |
| sb.append(','); |
| } |
| } |
| return sb.toString(); |
| } |
| |
| private final String query; |
| private final String cursorName; |
| private final boolean positioned; |
| private boolean checkRowUpdated; |
| private boolean checkRowDeleted; |
| |
| private final static String[] selectConditions = new String[] { |
| "WHERE c like 'T%'", |
| " ", |
| "WHERE b > 5", |
| "WHERE id >= a", |
| "WHERE id > 1 and id < 900", |
| "WHERE id = 1", |
| "WHERE id in (1,3,4,600,900,955,966,977,978)", |
| "WHERE a in (1,3,4,600,9200,955,966,977,978)", |
| "WHERE a>2 and a<9000" |
| }; |
| |
| private final static String[] projectConditions = new String[] { |
| "id,c,a,b", |
| "id,c", |
| "a,b", |
| "*", |
| "id,a,b,c", |
| "id,a", |
| "a,b,c", |
| "a,c" |
| }; |
| |
| private static BaseTestSuite createTestCases(final String modelName) { |
| BaseTestSuite suite = new BaseTestSuite(); |
| for (int doPos = 0; doPos<2; doPos++) { |
| boolean positioned = doPos>0; // true if to use positioned updates |
| |
| for (int i = 0; i < selectConditions.length; i++) { |
| for (int j = 0; j < projectConditions.length; j++) { |
| final String cursorName = "cursor_" + i + "_" + j; |
| |
| final String stmtString = "SELECT " + projectConditions[j] + |
| " FROM T1 " + selectConditions[i]; |
| suite.addTest(new SURQueryMixTest(modelName, stmtString, cursorName, |
| positioned)); |
| } |
| } |
| } |
| return suite; |
| } |
| |
| /** |
| * Run in client and embedded. |
| */ |
| public static Test suite() |
| { |
| BaseTestSuite mainSuite = new BaseTestSuite("SURQueryMixTest suite"); |
| |
| mainSuite.addTest(baseSuite("SURQueryMixTest:embedded")); |
| mainSuite.addTest( |
| TestConfiguration.clientServerDecorator( |
| baseSuite("SURQueryMixTest:client"))); |
| |
| return mainSuite; |
| |
| } |
| |
| /** |
| * The suite contains all testcases in this class running on different data models |
| */ |
| private static Test baseSuite(String name) { |
| BaseTestSuite mainSuite = new BaseTestSuite(name); |
| |
| // Iterate over all data models and decorate the tests: |
| for (Iterator i = SURDataModelSetup.SURDataModel.values().iterator(); |
| i.hasNext();) { |
| |
| SURDataModelSetup.SURDataModel model = |
| (SURDataModelSetup.SURDataModel) i.next(); |
| |
| BaseTestSuite suite = createTestCases(model.toString()); |
| TestSetup decorator = new SURDataModelSetup(suite, model); |
| mainSuite.addTest(decorator); |
| } |
| return mainSuite; |
| } |
| } |
