| package org.apache.derbyTesting.functionTests.tests.jdbc4; |
| |
| import java.io.IOException; |
| import java.io.InputStream; |
| import java.io.Reader; |
| import java.sql.Blob; |
| import java.sql.Clob; |
| import java.sql.Connection; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.Arrays; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.functionTests.util.streams.LoopingAlphabetReader; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /* |
| Class org.apache.derbyTesting.functionTests.tests.jdbc4.Derby3650Test |
| |
| 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. |
| |
| */ |
| |
| /** |
| * These are tests to test the cases for DERBY-3650. |
| * The tests won't pass until that bug is fixed. |
| */ |
| |
| public class Derby3650Test extends BaseJDBCTestCase |
| { |
| /************************************************************************** |
| * Fields of the class |
| ************************************************************************** |
| */ |
| |
| // DERBY-3749 causes tests that commit after looking at the stream to |
| // break while trying to access the streams in subsequent rows. When |
| // that bug gets fixed, enable these tests. |
| private static final boolean runDerby3749tests = false; |
| |
| /************************************************************************** |
| * Constructors for This class: |
| ************************************************************************** |
| */ |
| |
| public Derby3650Test(String name) |
| { |
| super(name); |
| } |
| |
| public void setUp() |
| throws SQLException |
| { |
| getConnection().setAutoCommit(false); |
| } |
| |
| |
| /************************************************************************** |
| * Private/Protected methods of This class: |
| ************************************************************************** |
| */ |
| |
| /** |
| * Test select of multiple rows containing single clob column. |
| * <p> |
| * Expects input query to return 3 column's per row, which should be: |
| * (id, length of clob, clob) |
| * |
| * Will verify clob using verifyClob(). |
| * <p> |
| * Runs the query 4 times testing the following combinations: |
| * free clob on each row = true, commit xact after each row = true |
| * free clob on each row = true, commit xact after each row = false |
| * free clob on each row = false, commit xact after each row = true |
| * free clob on each row = false, commit xact after each row = false |
| * |
| * @param query query to run. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void runQueryCasesClob( |
| String query) |
| throws SQLException, IOException |
| { |
| if (runDerby3749tests) |
| runQueryClob(query, true, true); |
| runQueryClob(query, true, false); |
| if (runDerby3749tests) |
| runQueryClob(query, false, true); |
| runQueryClob(query, false, false); |
| } |
| |
| /** |
| * Test select of multiple rows containing single clob column. |
| * <p> |
| * Expects input query to return 3 column's per row, which should be: |
| * (id, length of clob, clob) |
| * |
| * Will verify clob using verifyClob(). |
| * <p> |
| * |
| * @param query query to run. |
| * @param freelob true if we should free the lob after it has |
| * been retrieved and verified. |
| * @param commitAfterLobVerify true if we should commit after the lob has |
| * been retrieved and verified. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void runQueryClob( |
| String query, |
| boolean freelob, |
| boolean commitAfterLobVerify) |
| throws SQLException, IOException |
| { |
| PreparedStatement ps = prepareStatement(query); |
| |
| ResultSet rs = ps.executeQuery(); |
| while (rs.next()) |
| { |
| int id = rs.getInt(1); |
| int length = rs.getInt(2); |
| Clob clob = rs.getClob(3); |
| |
| // verify that stream can be read and is right. |
| verifyClob( |
| clob.getCharacterStream(), |
| length, |
| new LoopingAlphabetReader(length)); |
| |
| if (freelob) |
| clob.free(); |
| if (commitAfterLobVerify) |
| commit(); |
| } |
| rs.close(); |
| commit(); |
| |
| rs = ps.executeQuery(); |
| while (rs.next()) |
| { |
| int id = rs.getInt(1); |
| int length = rs.getInt(2); |
| |
| // verify that stream can be read and is right. |
| verifyClob( |
| rs.getCharacterStream(3), |
| length, |
| new LoopingAlphabetReader(length)); |
| |
| if (commitAfterLobVerify) |
| commit(); |
| } |
| rs.close(); |
| commit(); |
| |
| ps.close(); |
| |
| } |
| |
| /** |
| * Test select of multiple rows containing single blob column. |
| * <p> |
| * Expects input query to return 3 column's per row, which should be: |
| * (id, length of clob, clob) |
| * Will verify blob using verifyBlob(). |
| * <p> |
| * Runs the query 4 times testing the following combinations: |
| * free blob on each row = true, commit xact after each row = true |
| * free blob on each row = true, commit xact after each row = false |
| * free blob on each row = false, commit xact after each row = true |
| * free blob on each row = false, commit xact after each row = false |
| * |
| * @param query query to run. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void runQueryCasesBlob( |
| String query) |
| throws SQLException, IOException |
| { |
| if (runDerby3749tests) |
| runQueryBlob(query, true, true); |
| runQueryBlob(query, true, false); |
| if (runDerby3749tests) |
| runQueryBlob(query, false, true); |
| runQueryBlob(query, false, false); |
| } |
| |
| /** |
| * Test select of multiple rows containing single blob column. |
| * <p> |
| * Expects input query to return single column per row, which is a blob. |
| * Will verify blob using verifyBlob(). |
| * <p> |
| * |
| * @param query query to run. |
| * @param freelob true if we should free the lob after it has |
| * been retrieved and verified. |
| * @param commitAfterLobVerify true if we should commit after the lob has |
| * been retrieved and verified. |
| * |
| * @exception StandardException Standard exception policy. |
| **/ |
| private void runQueryBlob( |
| String query, |
| boolean freelob, |
| boolean commitAfterLobVerify) |
| throws SQLException, IOException |
| { |
| PreparedStatement ps = prepareStatement(query); |
| |
| ResultSet rs = ps.executeQuery(); |
| while (rs.next()) |
| { |
| Blob blob = rs.getBlob(3); |
| verifyBlob(blob.getBinaryStream(), rs.getInt(2), rs.getInt(1)); |
| if (freelob) |
| blob.free(); |
| if (commitAfterLobVerify) |
| commit(); |
| } |
| rs.close(); |
| rollback(); |
| |
| rs = ps.executeQuery(); |
| while (rs.next()) |
| { |
| // note, the order of "getXXX" is important. This routine will |
| // fail in network client if the 3rd arg is requested before the |
| // 1st arg. In that case attempts to read from the stream get |
| // a closed error. This is why the values are retrieved first |
| // and then passed to the call. |
| int id = rs.getInt(1); |
| int length = rs.getInt(2); |
| InputStream stream = rs.getBinaryStream(3); |
| |
| verifyBlob(stream, length, id); |
| |
| if (commitAfterLobVerify) |
| commit(); |
| } |
| rs.close(); |
| commit(); |
| |
| ps.close(); |
| } |
| |
| private void verifyClob(Reader input, int length, Reader expected) |
| throws SQLException, IOException |
| { |
| int input_char; |
| int expect_char; |
| int charcount = 0; |
| |
| do |
| { |
| input_char = input.read(); |
| expect_char = expected.read(); |
| |
| if (input_char != -1) |
| { |
| charcount++; |
| if ((char) input_char != expect_char) |
| { |
| fail("Unexpected Character " + (char) input_char + |
| " expected " + (char) expect_char); |
| } |
| } |
| |
| } while (input_char != -1) ; |
| |
| if (charcount != length) |
| { |
| fail("Unexpected character count " + charcount + |
| "expected: " + length); |
| } |
| } |
| |
| private void verifyBlob(InputStream is, int length, int id) |
| throws SQLException, IOException |
| { |
| int b; |
| int bytecount = 0; |
| do |
| { |
| b = is.read(); |
| if (b != -1) |
| { |
| bytecount++; |
| if ((byte) b != id) |
| { |
| fail("Unexpected byte value " + (byte) b + |
| " expected: " + id); |
| } |
| } |
| } while (b != -1); |
| |
| if (bytecount != length) |
| { |
| fail("Unexpected byte count, got " + bytecount + |
| " expected " + length); |
| } |
| } |
| |
| /************************************************************************** |
| * Public Methods of This class: |
| ************************************************************************** |
| */ |
| |
| |
| /** |
| * Test a nested loop join for clobs. |
| * <p> |
| * Test case of a 1 to many row join where the 1 row contains and returns |
| * a clob as a stream. Before fix for DERBY-3650 each row returned from |
| * this join would contain a reference to the same stream which would fail |
| * in various ways depending on commit, free, and reading the stream. |
| * |
| * @throws SQLException |
| * @throws IOException |
| */ |
| public void test1ToManyJoinClob() |
| throws SQLException, IOException |
| { |
| runQueryCasesClob( |
| "select testClob.id, length, c from testClob " + |
| "join jointab on jointab.id = testClob.id"); |
| } |
| |
| /** |
| * Test a nested loop join for blobs. |
| * <p> |
| * Test case of a 1 to many row join where the 1 row contains and returns |
| * a blob as a stream. Before fix for DERBY-3650 each row returned from |
| * this join would contain a reference to the same stream which would fail |
| * in various ways depending on commit, free, and reading the stream. |
| * |
| * @throws SQLException |
| * @throws IOException |
| */ |
| public void test1ToManyJoinBlob() |
| throws SQLException, IOException |
| { |
| runQueryCasesBlob( |
| "select testBlob.id, length, c from testBlob " + |
| "join jointab on jointab.id = testBlob.id"); |
| } |
| |
| |
| public void test1ToManyHashJoinClob() |
| throws SQLException, IOException |
| { |
| runQueryCasesClob( |
| "select testClob.id, length, c from " + |
| "--DERBY-PROPERTIES joinOrder=FIXED \n" + |
| "testClob --DERBY-PROPERTIES joinStrategy=HASH \n" + |
| "join jointab on jointab.id = testClob.id"); |
| |
| runQueryCasesClob( |
| "select jointab.id, length, c from " + |
| "--DERBY-PROPERTIES joinOrder=FIXED \n" + |
| "jointab --DERBY-PROPERTIES joinStrategy=HASH \n" + |
| "join testClob on jointab.id = testClob.id"); |
| } |
| |
| public void test1ToManyHashJoinBlob() |
| throws SQLException, IOException |
| { |
| runQueryCasesBlob( |
| "select testBlob.id, length, c from " + |
| "--DERBY-PROPERTIES joinOrder=FIXED \n" + |
| "testBlob --DERBY-PROPERTIES joinStrategy=HASH \n" + |
| "join jointab on jointab.id = testBlob.id"); |
| |
| runQueryCasesBlob( |
| "select testBlob.id, length, c from " + |
| "--DERBY-PROPERTIES joinOrder=FIXED \n" + |
| "jointab --DERBY-PROPERTIES joinStrategy=HASH \n" + |
| "join testBlob on jointab.id = testBlob.id"); |
| } |
| |
| public void test1ToManyleftOuterJoinClob() |
| throws SQLException, IOException |
| { |
| runQueryCasesClob( |
| "select testClob.id, length, c from testClob " + |
| "left outer join jointab on jointab.id = testClob.id"); |
| |
| runQueryCasesClob( |
| "select jointab.id, length, c from jointab " + |
| "left outer join testClob on jointab.id = testClob.id"); |
| } |
| |
| public void test1ToManyleftOuterJoinBlob() |
| throws SQLException, IOException |
| { |
| runQueryCasesBlob( |
| "select testBlob.id, length, c from testBlob " + |
| "left outer join jointab on jointab.id = testBlob.id"); |
| runQueryCasesBlob( |
| "select jointab.id, length, c from jointab " + |
| "left outer join testBlob on jointab.id = testBlob.id"); |
| } |
| |
| |
| /** |
| * Test straight select from a heap scan of multiple rows containing clobs. |
| * |
| * @throws SQLException |
| * @throws IOException |
| */ |
| public void testClobSelect() |
| throws SQLException, IOException |
| { |
| runQueryCasesClob("select id, length, c from testMultipleClob"); |
| } |
| |
| /** |
| * Test straight select from a heap scan of multiple rows containing blobs. |
| * |
| * @throws SQLException |
| * @throws IOException |
| */ |
| public void testBlobSelect() |
| throws SQLException, IOException |
| { |
| runQueryCasesBlob("select id, length, c from testMultipleBlob"); |
| } |
| |
| private static void initializeClobTables(Statement stmt) |
| throws SQLException, IOException |
| { |
| // CLOB TEST SETUP........................................... |
| stmt.executeUpdate( |
| "CREATE TABLE testClob (id int, length int, c CLOB(2M))"); |
| |
| Connection conn = stmt.getConnection(); |
| PreparedStatement ps = |
| conn.prepareStatement("INSERT INTO TestClob VALUES(?,?,?)"); |
| |
| // insert 4 rows into "left" table containing clobs of join: |
| // (1, clob), (1, clob), (2, clob), (2, clob) |
| ps.setInt( 1, 1); |
| ps.setInt( 2, 40000); |
| ps.setCharacterStream(3, new LoopingAlphabetReader(40000)); |
| ps.executeUpdate(); |
| |
| ps.setInt( 1, 1); |
| ps.setInt( 2, 40001); |
| ps.setCharacterStream(3, new LoopingAlphabetReader(40001)); |
| ps.executeUpdate(); |
| |
| ps.setInt( 1, 2); |
| ps.setInt( 2, 40002); |
| ps.setCharacterStream(3, new LoopingAlphabetReader(40002)); |
| ps.executeUpdate(); |
| |
| ps.setInt( 1, 2); |
| ps.setInt( 2, 40003); |
| ps.setCharacterStream(3, new LoopingAlphabetReader(40003)); |
| ps.executeUpdate(); |
| ps.close(); |
| |
| stmt.executeUpdate( |
| "CREATE TABLE testMultipleClob (id int, length int, c CLOB(2M))"); |
| ps = conn.prepareStatement( |
| "INSERT INTO testMultipleClob VALUES(?,?,?)"); |
| |
| for (int i = 0; i < 100; i++) |
| { |
| ps.setInt( 1, i); |
| ps.setInt( 2, 40000 + i); |
| ps.setCharacterStream(3, new LoopingAlphabetReader(40000 + i)); |
| ps.executeUpdate(); |
| } |
| ps.close(); |
| conn.commit(); |
| } |
| |
| private static void initializeBlobTables(Statement stmt) |
| throws SQLException, IOException |
| { |
| // BLOB TEST SETUP........................................... |
| stmt.executeUpdate( |
| "CREATE TABLE testBlob (id int, length int, c BLOB(2M))"); |
| |
| Connection conn = stmt.getConnection(); |
| PreparedStatement ps = |
| conn.prepareStatement("INSERT INTO TestBlob VALUES(?,?,?)"); |
| |
| // insert 4 rows into "left" blob of join: |
| // (1, 40000, blob), (1, 40001, blob), |
| // (2, 40002, blob), (2, 40003, blob) |
| byte[] mybytes = new byte[40000]; |
| Arrays.fill(mybytes, (byte) 1); |
| ps.setInt( 1, 1); |
| ps.setInt( 2, 40000); |
| ps.setBytes(3, mybytes); |
| ps.executeUpdate(); |
| |
| mybytes = new byte[40001]; |
| Arrays.fill(mybytes, (byte) 1); |
| ps.setInt( 1, 1); |
| ps.setInt( 2, 40001); |
| ps.setBytes(3, mybytes); |
| ps.executeUpdate(); |
| |
| mybytes = new byte[40002]; |
| Arrays.fill(mybytes, (byte) 2); |
| ps.setInt( 1, 2); |
| ps.setInt( 2, 40002); |
| ps.setBytes(3, mybytes); |
| ps.executeUpdate(); |
| |
| mybytes = new byte[40003]; |
| Arrays.fill(mybytes, (byte) 2); |
| ps.setInt( 1, 2); |
| ps.setInt( 2, 40003); |
| ps.setBytes(3, mybytes); |
| ps.executeUpdate(); |
| |
| ps.close(); |
| |
| // insert 4 rows into "right" table of join: |
| stmt.executeUpdate("CREATE TABLE jointab (id int)"); |
| stmt.executeUpdate("INSERT INTO jointab values(1)"); |
| stmt.executeUpdate("INSERT INTO jointab values(1)"); |
| stmt.executeUpdate("INSERT INTO jointab values(2)"); |
| stmt.executeUpdate("INSERT INTO jointab values(2)"); |
| |
| stmt.executeUpdate( |
| "CREATE TABLE testMultipleBlob (id int, length int, c BLOB(2M))"); |
| ps = conn.prepareStatement( |
| "INSERT INTO testMultipleBlob VALUES(?,?,?)"); |
| |
| for (int i = 0; i < 100; i++) |
| { |
| mybytes = new byte[40000 + i]; |
| Arrays.fill(mybytes, (byte) i); |
| |
| ps.setInt( 1, i); |
| ps.setInt( 2, 40000 + i); |
| ps.setBytes(3,mybytes); |
| ps.executeUpdate(); |
| } |
| ps.close(); |
| conn.commit(); |
| } |
| |
| |
| protected static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite(name); |
| suite.addTestSuite(Derby3650Test.class); |
| return new CleanDatabaseTestSetup( |
| DatabasePropertyTestSetup.setLockTimeouts(suite, 2, 4)) |
| { |
| /** |
| * Creates the tables used in the test cases. |
| * @exception SQLException if a database error occurs |
| */ |
| protected void decorateSQL(Statement stmt) throws SQLException |
| { |
| try |
| { |
| initializeClobTables(stmt); |
| initializeBlobTables(stmt); |
| } |
| catch (IOException ioe) |
| { |
| fail("Unexpected I/O exception during setup: " + ioe); |
| } |
| } |
| }; |
| } |
| |
| |
| public static Test suite() |
| { |
| BaseTestSuite suite = new BaseTestSuite("Derby3650Test"); |
| suite.addTest(baseSuite("Derby3650Test:embedded")); |
| suite.addTest(TestConfiguration.clientServerDecorator( |
| baseSuite("Derby3650Test:client"))); |
| return suite; |
| |
| } |
| } |