db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r1135754 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang: AutoIncrementTest.java t_4437_2.dat
Date Tue, 14 Jun 2011 19:13:32 GMT
Author: rhillegas
Date: Tue Jun 14 19:13:32 2011
New Revision: 1135754

URL: http://svn.apache.org/viewvc?rev=1135754&view=rev
Log:
DERBY-4437: Add tests for the generator-based identity columns: ALTER TABLE, bulk import,
and deferred INSERT.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
  (with props)
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java?rev=1135754&r1=1135753&r2=1135754&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/AutoIncrementTest.java
Tue Jun 14 19:13:32 2011
@@ -33,10 +33,12 @@ import junit.framework.TestSuite;
 import org.apache.derbyTesting.junit.BaseJDBCTestCase;
 import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
 import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
 
 public class AutoIncrementTest extends BaseJDBCTestCase {
 
     private static  final   String  SEQUENCE_OVERFLOW = "2200H";
+    private static  final   String  IMPORT_FILE_NAME = "t_4437_2.dat";
     
 	public AutoIncrementTest(String name)
 	{
@@ -1084,9 +1086,227 @@ public class AutoIncrementTest extends B
 		assertStatementError("42XA7",s,"alter table d4006 alter column y default null");
 
 	}
+
+    /**
+     * <p>
+     * Test that alter table interacts well with the sequence-generator-based identity generators.
+     * </p>
+     */
+    public  void    test_4437_01_alterTable()   throws Exception
+    {
+		Statement s = createStatement();
+        String[][] expectedResults;
+        
+		s.execute("create table t_4437_1( a int, b int generated always as identity )");
+		s.execute("insert into t_4437_1( a ) values (100), (101), (102), (103), (104), (105)");
+        expectedResults = new String[][]
+        {
+            { "100",        "1", },
+            { "101",        "2", },
+            { "102",        "3", },
+            { "103",        "4", },
+            { "104",        "5", },
+            { "105",        "6", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults
);
+
+		s.execute("alter table t_4437_1 alter column b restart with 22");
+		s.execute("insert into t_4437_1( a ) values (110), (111), (112), (113), (114), (115)");
+        expectedResults = addResults
+            (
+             expectedResults,
+             new String[][]
+             {
+                 { "110",        "22", },
+                 { "111",        "23", },
+                 { "112",        "24", },
+                 { "113",        "25", },
+                 { "114",        "26", },
+                 { "115",        "27", },
+             }
+             );
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults
);
+
+		s.execute("alter table t_4437_1 alter column b set increment by 3");
+		s.execute("insert into t_4437_1( a ) values (120), (121), (122), (123), (124)");
+        expectedResults = addResults
+            (
+             expectedResults,
+             new String[][]
+             {
+                 { "120",        "30", },
+                 { "121",        "33", },
+                 { "122",        "36", },
+                 { "123",        "39", },
+                 { "124",        "42", },
+             }
+             );
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults
);
+
+		s.execute("alter table t_4437_1 alter column b set increment by 4");
+		s.execute("insert into t_4437_1( a ) values (130), (131), (132), (133), (134), (135)");
+        expectedResults = addResults
+            (
+             expectedResults,
+             new String[][]
+             {
+                 { "130",        "46", },
+                 { "131",        "50", },
+                 { "132",        "54", },
+                 { "133",        "58", },
+                 { "134",        "62", },
+                 { "135",        "66", },
+             }
+             );
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_1 order by a" ), expectedResults
);
+
+        s.execute( "drop table t_4437_1" );
+    }
+    private String[][]  addResults( String[][] original, String[][] extras )
+    {
+        int originalLength = original.length;
+        int extrasLength = extras.length;
+        String[][]  result = new String[ originalLength + extrasLength ][];
+
+        int idx = 0;
+        for ( int i = 0; i < originalLength; i++ ) { result[ idx++ ] = original[ i ];
}
+        for ( int i = 0; i < extrasLength; i++ ) { result[ idx++ ] = extras[ i ]; }
+
+        return result;
+    }
 	
-	public static Test suite() {
-		return new CleanDatabaseTestSetup(
+    /**
+     * <p>
+     * Test that bulk import interacts well with the sequence-generator-based identity generators.
+     * </p>
+     */
+    public  void    test_4437_02_bulkImport()   throws Exception
+    {
+		Statement s = createStatement();
+        String[][] expectedResults;
+
+		s.execute( "create table t_4437_2( a int, b int generated always as identity (start with
-10, increment by -3) )" );
+		s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)"
);
+        expectedResults = new String[][]
+        {
+            { "110",        "-10", },
+            { "111",        "-13", },
+            { "112",        "-16", },
+            { "113",        "-19", },
+            { "114",        "-22", },
+            { "115",        "-25", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults
);
+
+        // import running in replace mode resets the sequence counter
+		s.execute( "call syscs_util.syscs_import_data( null, 'T_4437_2', 'A', null, 'extin/" +
IMPORT_FILE_NAME + "', null, null, null, 1 )" );
+        expectedResults = new String[][]
+        {
+            { "100",        "-10", },
+            { "101",        "-13", },
+            { "102",        "-16", },
+            { "103",        "-19", },
+            { "104",        "-22", },
+            { "105",        "-25", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults
);
+
+        // truncate does not reset the counter
+		s.execute( "truncate table t_4437_2" );
+		s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)"
);
+        expectedResults = new String[][]
+        {
+            { "110",        "-28", },
+            { "111",        "-31", },
+            { "112",        "-34", },
+            { "113",        "-37", },
+            { "114",        "-40", },
+            { "115",        "-43", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults
);
+
+        // delete does not reset the counter either
+		s.execute( "delete from t_4437_2" );
+		s.execute( "insert into t_4437_2( a ) values (110), (111), (112), (113), (114), (115)"
);
+        expectedResults = new String[][]
+        {
+            { "110",        "-46", },
+            { "111",        "-49", },
+            { "112",        "-52", },
+            { "113",        "-55", },
+            { "114",        "-58", },
+            { "115",        "-61", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults
);
+
+        // import running in insert mode does not reset the sequence counter
+		s.execute( "call syscs_util.syscs_import_data( null, 'T_4437_2', 'A', null, 'extin/" +
IMPORT_FILE_NAME + "', null, null, null, 0 )" );
+        expectedResults = new String[][]
+        {
+            { "100",        "-64", },
+            { "101",        "-67", },
+            { "102",        "-70", },
+            { "103",        "-73", },
+            { "104",        "-76", },
+            { "105",        "-79", },
+            { "110",        "-46", },
+            { "111",        "-49", },
+            { "112",        "-52", },
+            { "113",        "-55", },
+            { "114",        "-58", },
+            { "115",        "-61", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_2 order by a" ), expectedResults
);
+
+        s.execute( "drop table t_4437_2" );
+    }
+        
+    /**
+     * <p>
+     * Test that deferred inserts work well with the sequence-generator-based identity generators.
+     * </p>
+     */
+    public  void    test_4437_03_deferredInsert()   throws Exception
+    {
+		Statement s = createStatement();
+        String[][] expectedResults;
+
+		s.execute( "create table t_4437_3( a int, b smallint generated always as identity (start
with 9990, increment by -10) )" );
+		s.execute( "insert into t_4437_3( a ) values (100), (101), (102), (103), (104), (105)"
);
+        expectedResults = new String[][]
+        {
+            { "100",        "9990", },
+            { "101",        "9980", },
+            { "102",        "9970", },
+            { "103",        "9960", },
+            { "104",        "9950", },
+            { "105",        "9940", },
+        };
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_3 order by a" ), expectedResults
);
+
+        // deferred inserts should continue the sequence where it left off
+		s.execute( "insert into t_4437_3( a ) select a + 10 from ( select a from t_4437_3 order
by a ) s" );
+        expectedResults = addResults
+            (
+             expectedResults,
+             new String[][]
+             {
+                 { "110",        "9930", },
+                 { "111",        "9920", },
+                 { "112",        "9910", },
+                 { "113",        "9900", },
+                 { "114",        "9890", },
+                 { "115",        "9880", },
+             }
+             );
+		JDBC.assertFullResultSet( s.executeQuery( "select * from t_4437_3 order by a" ), expectedResults
);
+
+        s.execute( "drop table t_4437_3" );
+    }
+
+	public static Test suite()
+    {
+		Test    cleanDatabaseSuite = new CleanDatabaseTestSetup(
 				new TestSuite(AutoIncrementTest.class, "AutoIncrementTest")) {
 			protected void decorateSQL(Statement s)
 			throws SQLException
@@ -1094,5 +1314,16 @@ public class AutoIncrementTest extends B
 				createSchemaObjects(s);
 			}
 		};
+        
+        //
+        // Copies the data file to a location which can be read.
+        //
+        Test        result = new SupportFilesSetup
+            (
+             cleanDatabaseSuite,
+             new String [] { "functionTests/tests/lang/" + IMPORT_FILE_NAME }
+             );
+
+        return result;
 	}
 }

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat?rev=1135754&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
(added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
Tue Jun 14 19:13:32 2011
@@ -0,0 +1,6 @@
+100
+101
+102
+103
+104
+105

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/t_4437_2.dat
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message