Author: rhillegas Date: Mon May 17 15:02:46 2010 New Revision: 945197 URL: http://svn.apache.org/viewvc?rev=945197&view=rev Log: DERBY-4663: Add regression test to track the behavior of ResultSet.getBoolean(). Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java (with props) Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java?rev=945197&view=auto ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java (added) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Mon May 17 15:02:46 2010 @@ -0,0 +1,486 @@ +/* + + Derby - Class org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest + + 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.lang; + +import java.sql.Blob; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import junit.framework.Test; +import junit.framework.TestSuite; + +import org.apache.derbyTesting.junit.TestConfiguration; +import org.apache.derbyTesting.junit.JDBC; + +/** + *

+ * Test Derby's expanding support for BOOLEAN values. + *

+ */ +public class BooleanValuesTest extends GeneratedColumnsHelper +{ + /////////////////////////////////////////////////////////////////////////////////// + // + // CONSTANTS + // + /////////////////////////////////////////////////////////////////////////////////// + + private static final String ILLEGAL_GET = "22005"; + private static final String ILLEGAL_XML_SELECTION = "42Z71"; + + /////////////////////////////////////////////////////////////////////////////////// + // + // STATE + // + /////////////////////////////////////////////////////////////////////////////////// + + /////////////////////////////////////////////////////////////////////////////////// + // + // CONSTRUCTOR + // + /////////////////////////////////////////////////////////////////////////////////// + + + /** + * Create a new instance. + */ + + public BooleanValuesTest(String name) + { + super(name); + } + + /////////////////////////////////////////////////////////////////////////////////// + // + // JUnit BEHAVIOR + // + /////////////////////////////////////////////////////////////////////////////////// + + + /** + * Construct top level suite in this JUnit test + */ + public static Test suite() + { + Test result = (TestSuite) TestConfiguration.defaultSuite(BooleanValuesTest.class); + + return result; + } + + protected void setUp() + throws Exception + { + super.setUp(); + + Connection conn = getConnection(); + + if ( !routineExists( conn, "MAKESIMPLEBLOB" ) ) + { + goodStatement + ( + conn, + "create function makeSimpleBlob( ) returns blob\n" + + "language java parameter style java no sql deterministic\n" + + "external name 'org.apache.derbyTesting.functionTests.tests.lang.BooleanValuesTest.makeSimpleBlob'\n" + ); + } + if ( !tableExists( conn, "ALL_TYPES" ) ) + { + goodStatement + ( + conn, + "create table all_types\n" + + "(\n" + + " key_col int,\n" + + " bigint_col BIGINT,\n" + + " blob_col BLOB(2147483647),\n" + + " char_col CHAR(10),\n" + + " char_for_bit_data_col CHAR (10) FOR BIT DATA,\n" + + " clob_col CLOB(2147483647),\n" + + " date_col DATE,\n" + + " decimal_col DECIMAL(5,2),\n" + + " real_col REAL,\n" + + " double_col DOUBLE,\n" + + " int_col INTEGER,\n" + + " long_varchar_col LONG VARCHAR,\n" + + " long_varchar_for_bit_data_col LONG VARCHAR FOR BIT DATA,\n" + + " numeric_col NUMERIC(5,2), \n" + + " smallint_col SMALLINT,\n" + + " time_col TIME,\n" + + " timestamp_col TIMESTAMP,\n" + + " varchar_col VARCHAR(10),\n" + + " varchar_for_bit_data_col VARCHAR (10) FOR BIT DATA,\n" + + " xml_col xml\n" + + ")\n" + ); + + goodStatement + ( + conn, + "insert into all_types\n" + + "(\n" + + " key_col,\n" + + " bigint_col,\n" + + " blob_col,\n" + + " char_col,\n" + + " char_for_bit_data_col,\n" + + " clob_col,\n" + + " date_col,\n" + + " decimal_col,\n" + + " real_col,\n" + + " double_col,\n" + + " int_col,\n" + + " long_varchar_col,\n" + + " long_varchar_for_bit_data_col,\n" + + " numeric_col, \n" + + " smallint_col,\n" + + " time_col,\n" + + " timestamp_col,\n" + + " varchar_col,\n" + + " varchar_for_bit_data_col,\n" + + " xml_col\n" + + ")\n" + + "values\n" + + "(\n" + + " 0,\n" + + " 0,\n" + + " makeSimpleBlob(),\n" + + " '0',\n" + + " X'DE',\n" + + " '0',\n" + + " date('1994-02-23'),\n" + + " 0.00,\n" + + " 0.0,\n" + + " 0.0,\n" + + " 0,\n" + + " '0',\n" + + " X'DE',\n" + + " 0.00, \n" + + " 0,\n" + + " time('15:09:02'),\n" + + " timestamp('1962-09-23 03:23:34.234'),\n" + + " '0',\n" + + " X'DE',\n" + + " xmlparse( document ' ' preserve whitespace )\n" + + "),\n" + + "(\n" + + " 1,\n" + + " 1,\n" + + " makeSimpleBlob(),\n" + + " '1',\n" + + " X'DE',\n" + + " '1',\n" + + " date('1994-02-23'),\n" + + " 1.00,\n" + + " 1.0,\n" + + " 1.0,\n" + + " 1,\n" + + " '1',\n" + + " X'DE',\n" + + " 1.00, \n" + + " 1,\n" + + " time('15:09:02'),\n" + + " timestamp('1962-09-23 03:23:34.234'),\n" + + " '1',\n" + + " X'DE',\n" + + " xmlparse( document ' ' preserve whitespace )\n" + + "),\n" + + "(\n" + + " 2,\n" + + " 2,\n" + + " makeSimpleBlob(),\n" + + " '2',\n" + + " X'DE',\n" + + " '2',\n" + + " date('1994-02-23'),\n" + + " 2.00,\n" + + " 2.0,\n" + + " 2.0,\n" + + " 2,\n" + + " '2',\n" + + " X'DE',\n" + + " 2.00, \n" + + " 2,\n" + + " time('15:09:02'),\n" + + " timestamp('1962-09-23 03:23:34.234'),\n" + + " '2',\n" + + " X'DE',\n" + + " xmlparse( document ' ' preserve whitespace )\n" + + "),\n" + + "(\n" + + " 3,\n" + + " 3,\n" + + " makeSimpleBlob(),\n" + + " 'baffle',\n" + + " X'DE',\n" + + " 'baffle',\n" + + " date('1994-02-23'),\n" + + " 3.00,\n" + + " 3.0,\n" + + " 3.0,\n" + + " 3,\n" + + " 'baffle',\n" + + " X'DE',\n" + + " 3.00, \n" + + " 3,\n" + + " time('15:09:02'),\n" + + " timestamp('1962-09-23 03:23:34.234'),\n" + + " 'baffle',\n" + + " X'DE',\n" + + " xmlparse( document ' ' preserve whitespace )\n" + + ")\n" + ); + } + } + + /////////////////////////////////////////////////////////////////////////////////// + // + // TESTS + // + /////////////////////////////////////////////////////////////////////////////////// + + /** + *

+ * Verify the number of datatypes supported by Derby. If this number changes, + * then you need to add a new datatype to this test: + *

+ * + * + */ + public void test_01_datatypeCount() throws Exception + { + Connection conn = getConnection(); + + ResultSet rs = conn.getMetaData().getTypeInfo(); + int actualTypeCount = 0; + while ( rs.next() ) { actualTypeCount++; } + rs.close(); + + assertEquals( 21, actualTypeCount ); + } + + /** + *

+ * Test that ResultSet.getBoolean() behaves well for the datatypes for + * which the JDBC spec defines correct values: CHAR, VARCHAR, TINYINT, + * SMALLINT, INTEGER, BIGINT. + *

+ */ + public void test_02_defined() throws Exception + { + Connection conn = getConnection(); + + vet_getBoolean( conn, "BIGINT_COL" ); + vet_getBoolean( conn, "CHAR_COL" ); + vet_getBoolean( conn, "INT_COL" ); + vet_getBoolean( conn, "SMALLINT_COL" ); + vet_getBoolean( conn, "VARCHAR_COL" ); + } + /** + *

+ * Verifies Derby's behavior is the same for embedded and client + * drivers on datatypes for which the JDBC spec does not define results + * but which Derby handles. + *

+ */ + public void test_03_undefinedButLegal() throws Exception + { + Connection conn = getConnection(); + + vet_getBoolean( conn, "DECIMAL_COL" ); + vet_getBoolean( conn, "REAL_COL" ); + vet_getBoolean( conn, "DOUBLE_COL" ); + vet_getBoolean( conn, "LONG_VARCHAR_COL" ); + vet_getBoolean( conn, "NUMERIC_COL" ); + } + /** + *

+ * Verify Derby's behavior is the same for embedded and client + * drivers on datatypes for which the JDBC spec does not define results + * and which Derby does not handle. + *

+ */ + public void test_04_undefinedAndIllegal() throws Exception + { + Connection conn = getConnection(); + + vet_getBooleanIsIllegal( conn, "BLOB_COL" ); + vet_getBooleanIsIllegal( conn, "CHAR_FOR_BIT_DATA_COL" ); + vet_getBooleanIsIllegal( conn, "CLOB_COL" ); + vet_getBooleanIsIllegal( conn, "DATE_COL" ); + vet_getBooleanIsIllegal( conn, "LONG_VARCHAR_FOR_BIT_DATA_COL" ); + vet_getBooleanIsIllegal( conn, "TIME_COL" ); + vet_getBooleanIsIllegal( conn, "TIMESTAMP_COL" ); + vet_getBooleanIsIllegal( conn, "VARCHAR_FOR_BIT_DATA_COL" ); + } + /** + *

+ * Regression tests for outliers. If this behavior changes, + * hopefully the new behavior will fit into one of the categories above. + *

+ */ + public void test_05_undefinedIllegalOutliers() throws Exception + { + expectCompilationError + ( + ILLEGAL_XML_SELECTION, + makeQuery( "XML_COL" ) + ); + } + /** + *

+ * Test that ResultSet.getBoolean() returns the correct value. Expects to + * select a result set with the following rows: + *

+ * + *
+     * 0       0
+     * 1       1
+     * 2       2
+     * 3       for string columns, something other than a number
+     * 
+ */ + private void vet_getBoolean( Connection conn, String columnName ) throws Exception + { + PreparedStatement ps = chattyPrepare( conn, makeQuery( columnName ) ); + ResultSet rs = ps.executeQuery(); + + // 0 => getBoolean() = false + rs.next(); + assertFalse( rs.getBoolean( columnName ) ); + + // 1 => getBoolean() = true + rs.next(); + assertTrue( rs.getBoolean( columnName ) ); + + // 2 => getBoolean() = true + // + // If the value is not 0 or 1, the JDBC spec leaves it up to the implementation. + // For Derby, the answer is ( value != 0 ) + rs.next(); + assertTrue( rs.getBoolean( columnName ) ); + + // non-number => getBoolean() = true + // + // If the value is not 0 or 1, the JDBC spec leaves it up to the implementation. + // For Derby, the answer is ( value != 0 ) + rs.next(); + assertTrue( rs.getBoolean( columnName ) ); + + rs.close(); + ps.close(); + } + private String makeQuery( String columnName ) + { + return "select key_col, " + columnName + " from all_types order by key_col"; + } + /** + *

+ * Test that ResultSet.getBoolean() raises an error on datatypes which + * Derby doesn't handle. + *

+ */ + private void vet_getBooleanIsIllegal( Connection conn, String columnName ) throws Exception + { + vet_getBooleanException( conn, columnName, ILLEGAL_GET ); + } + /** + *

+ * Test that ResultSet.getBoolean() raises the expected error. + *

+ */ + private void vet_getBooleanException( Connection conn, String columnName, String sqlstate ) throws Exception + { + PreparedStatement ps = chattyPrepare( conn, makeQuery( columnName ) ); + ResultSet rs = ps.executeQuery(); + + rs.next(); + + try { + rs.getBoolean( columnName ); + fail( "getBoolean() on " + columnName + " should not have succeeded." ); + } + catch (SQLException se) + { + assertSQLState( "getBoolean() on " + columnName, sqlstate, se ); + } + + } + + /////////////////////////////////////////////////////////////////////////////////// + // + // SQL ROUTINES + // + /////////////////////////////////////////////////////////////////////////////////// + + public static Blob makeSimpleBlob() + { + return new StringColumnVTI.SimpleBlob( new byte[] { 1 } ); + } + + /////////////////////////////////////////////////////////////////////////////////// + // + // MINIONS + // + /////////////////////////////////////////////////////////////////////////////////// + + /** Return true if the SQL routine exists */ + private boolean routineExists( Connection conn, String functionName ) throws Exception + { + PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.sysaliases where alias = ?" ); + ps.setString( 1, functionName ); + + ResultSet rs = ps.executeQuery(); + rs.next(); + + boolean retval = rs.getInt( 1 ) > 0 ? true : false; + + rs.close(); + ps.close(); + + return retval; + } + + /** Return true if the table exists */ + private boolean tableExists( Connection conn, String tableName ) throws Exception + { + PreparedStatement ps = chattyPrepare( conn, "select count (*) from sys.systables where tablename = ?" ); + ps.setString( 1, tableName ); + + ResultSet rs = ps.executeQuery(); + rs.next(); + + boolean retval = rs.getInt( 1 ) > 0 ? true : false; + + rs.close(); + ps.close(); + + return retval; + } + +} Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=945197&r1=945196&r2=945197&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Mon May 17 15:02:46 2010 @@ -205,6 +205,7 @@ public class _Suite extends BaseTestCase suite.addTest(RestrictedVTITest.suite()); suite.addTest(UDTTest.suite()); suite.addTest(UDTPermsTest.suite()); + suite.addTest(BooleanValuesTest.suite()); suite.addTest(AlterColumnTest.suite()); suite.addTest(UserLobTest.suite()); suite.addTest(OffsetFetchNextTest.suite());