db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1519008 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/DB2IsolationLevels.out tests/lang/DB2IsolationLevels.sql tests/lang/DB2IsolationLevelsTest.java tests/lang/_Suite.java
Date Fri, 30 Aug 2013 16:03:33 GMT
Author: dag
Date: Fri Aug 30 16:03:33 2013
New Revision: 1519008

URL: http://svn.apache.org/r1519008
Log:
DERBY-6276: Convert lang/DB2IsolationLevels.sql to JUnit

Patch which performs the conversion.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out?rev=1519008&r1=1519007&r2=1519008&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out Fri Aug 30 16:03:33 2013
@@ -1,578 +0,0 @@
-ij> --
---   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.
---
--- single user test for the various isolation levels
--- also notice in the runtimestatistics output that Fetch Size is 16 for various isolation levels ie we are doing bulk fetch for all isolation levels
--- this will test the fix for bug 5953 - which is to enable bulk fetching for RR and serializable isolation levels as well.
-prepare getIsolation as 'values current isolation';
-ij> autocommit off;
-ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 2000;
-ij> -- create a table
-create table t1(c1 int not null constraint asdf primary key);
-0 rows inserted/updated/deleted
-ij> commit;
-ij> -- insert a row
-insert into t1 values 1;
-1 row inserted/updated/deleted
-ij> -- verify table scan gets row lock at read committed
-select * from t1;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	-- verify table scan gets row lock at read committed
-select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation RR;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-RR  
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set isolation reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation read committed;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-CS  
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set current isolation = reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set current isolation = RS;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-RS  
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at repeatable read isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set isolation to reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation = dirty read;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-UR  
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at read uncommitted isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> -- test WITH ISOLATION clause
-set isolation serializable;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-RR  
-ij> select * from t1 with CS;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1 with CS
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set isolation cursor stability;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-CS  
-ij> select * from t1 with RR;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1 with RR
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set isolation serializable;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-RR  
-ij> select * from t1 with RS;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1 with RS
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at repeatable read isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> set current isolation to read committed;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1   
-----
-CS  
-ij> select * from t1 with ur;
-C1         
------------
-1          
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ---------------------------
-Statement Name: 
-	null
-Statement Text: 
-	select * from t1 with ur
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text: 
-Index Scan ResultSet for T1 using constraint ASDF at read uncommitted isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
-	constructor time (milliseconds) = 0
-	open time (milliseconds) = 0
-	next time (milliseconds) = 0
-	close time (milliseconds) = 0
-	next time in milliseconds/row = 0
-scan information:
-	Bit set of columns fetched={0}
-	Number of columns fetched=1
-	Number of deleted rows visited=0
-	Number of pages visited=1
-	Number of rows qualified=1
-	Number of rows visited=1
-	Scan type=btree
-	Tree height=1
-	start position:
-		None
-	stop position:
-		None
-	qualifiers:
-		None
-ij> -- unknown isolation level
-select * from t1 with rw;
-ERROR 42X01: Syntax error: Encountered "rw" at line 2, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with DIRTY READ;
-ERROR 42X01: Syntax error: Encountered "DIRTY" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with READ UNCOMMITTED;
-ERROR 42X01: Syntax error: Encountered "READ" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with READ COMMITTED;
-ERROR 42X01: Syntax error: Encountered "READ" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with CURSOR STABILITY;
-ERROR 42X01: Syntax error: Encountered "CURSOR" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with REPEATABLE READ;
-ERROR 42X01: Syntax error: Encountered "REPEATABLE" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with SERIALIZABLE;
-ERROR 42X01: Syntax error: Encountered "SERIALIZABLE" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> -- check the db2 isolation levels can be used as identifiers
-create table db2iso(cs int, rr int, ur int, rs int);
-0 rows inserted/updated/deleted
-ij> select cs, rr, ur, rs from db2iso;
-CS         |RR         |UR         |RS         
------------------------------------------------
-ij> -- cleanup
-drop table t1;
-0 rows inserted/updated/deleted
-ij> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql?rev=1519008&r1=1519007&r2=1519008&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql Fri Aug 30 16:03:33 2013
@@ -1,110 +0,0 @@
---
---   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.
---
--- single user test for the various isolation levels
--- also notice in the runtimestatistics output that Fetch Size is 16 for various isolation levels ie we are doing bulk fetch for all isolation levels
--- this will test the fix for bug 5953 - which is to enable bulk fetching for RR and serializable isolation levels as well.
-prepare getIsolation as 'values current isolation';
-
-autocommit off;
-call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-maximumdisplaywidth 2000;
-
--- create a table
-create table t1(c1 int not null constraint asdf primary key);
-commit;
-
--- insert a row
-insert into t1 values 1;
--- verify table scan gets row lock at read committed
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
--- verify SET ISOLATION commits and changes isolation level
-set isolation RR;
-execute getIsolation;
-
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation reset;
-execute getIsolation;
--- verify SET ISOLATION commits and changes isolation level
-set isolation read committed;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set current isolation = reset;
-execute getIsolation;
-
--- verify SET ISOLATION commits and changes isolation level
-set current isolation = RS;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation to reset;
-execute getIsolation;
-
--- verify SET ISOLATION commits and changes isolation level
-set isolation = dirty read;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
--- test WITH ISOLATION clause
-set isolation serializable;
-execute getIsolation;
-select * from t1 with CS;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation cursor stability;
-execute getIsolation;
-select * from t1 with RR;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation serializable;
-execute getIsolation;
-select * from t1 with RS;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set current isolation to read committed;
-execute getIsolation;
-select * from t1 with ur;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
--- unknown isolation level
-select * from t1 with rw;
-select * from t1 with DIRTY READ;
-select * from t1 with READ UNCOMMITTED;
-select * from t1 with READ COMMITTED;
-select * from t1 with CURSOR STABILITY;
-select * from t1 with REPEATABLE READ;
-select * from t1 with SERIALIZABLE;
-
--- check the db2 isolation levels can be used as identifiers
-create table db2iso(cs int, rr int, ur int, rs int);
-select cs, rr, ur, rs from db2iso;
--- cleanup
-drop table t1;

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java?rev=1519008&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java Fri Aug 30 16:03:33 2013
@@ -0,0 +1,272 @@
+/**
+ *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DB2IsolationLevelsTest
+ *
+ * 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.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.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+
+public final class DB2IsolationLevelsTest extends BaseJDBCTestCase {
+
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     */
+    public DB2IsolationLevelsTest(String name)
+    {
+        super(name);
+    }
+
+    public static Test suite()
+    {
+        TestSuite suite = new TestSuite("DB2IsolationLevelsTest Test");
+        suite.addTest(TestConfiguration.defaultSuite(
+                DB2IsolationLevelsTest.class));
+        return suite;
+    }
+
+    @Override
+    public void setUp() throws Exception {
+        super.setUp();
+        // Create a table
+        createStatement().executeUpdate(
+            "create table t1(c1 int not null constraint asdf primary key)");
+    }
+
+    @Override
+    public void tearDown() throws Exception {
+        createStatement().executeUpdate("drop table t1");
+        super.tearDown();
+    }
+
+    public void test_DB2IsolationLevelsTest() throws Exception
+    {
+        final Statement st = createStatement();
+        final PreparedStatement ps =
+                prepareStatement("values current isolation");
+
+        setAutoCommit(false);
+        st.executeUpdate("call syscs_util.syscs_set_runtimestatistics(1)");
+        st.executeUpdate("insert into t1 values 1");
+
+        /* -----------------------------------------------------------------
+         * Session isolation default: (CS) read committed
+         */
+        assertQueryResult(st.executeQuery("select * from t1"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "read committed isolation level using instantaneous share " +
+            "row locking chosen by the optimizer");
+        assertIsolation(ps, "CS");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: RR (serializable)
+         */
+        st.executeUpdate("set isolation RR");
+        assertIsolation(ps, "RR");
+        // Rollback should find nothing to do
+        rollback();
+
+        assertQueryResult(st.executeQuery("select * from t1"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "serializable isolation level using share table locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Set isolation back to default
+         */
+        st.executeUpdate("set isolation reset");
+        assertIsolation(ps, " ");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: CS (read committed)
+         */
+        st.executeUpdate("set isolation read committed");
+        assertIsolation(ps, "CS");
+        // rollback should find nothing to undo
+        rollback();
+
+        assertQueryResult(st.executeQuery("select * from t1"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "read committed isolation level using instantaneous share " +
+            "row locking chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Set isolation back to default
+         */
+        st.executeUpdate("set isolation to reset");
+        assertIsolation(ps, " ");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: RS (read committed)
+         */
+        st.executeUpdate("set current isolation = RS");
+        assertIsolation(ps, "RS");
+        // rollback should find nothing to undo
+        rollback();
+
+        assertQueryResult(st.executeQuery("select * from t1"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "repeatable read isolation level using share row locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Set isolation back to default
+         */
+        st.executeUpdate("set isolation reset");
+        assertIsolation(ps, " ");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: UR (dirty read)
+         */
+        st.executeUpdate("set isolation = dirty read");
+        assertIsolation(ps, "UR");
+        // rollback should find nothing to undo
+        rollback();
+
+        assertQueryResult(st.executeQuery("select * from t1"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "read uncommitted isolation level using share row locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: RR (serializable)
+         */
+        st.executeUpdate("set isolation serializable");
+        assertIsolation(ps, "RR");
+
+        /*
+         * Override session serializable (RR) with read committed (CS)
+         * on statement level
+         */
+        assertQueryResult(st.executeQuery("select * from t1 with CS"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "read committed isolation level using instantaneous share " +
+            "row locking chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: CS (read committed)
+         */
+        st.executeUpdate("set isolation cursor stability");
+        assertIsolation(ps, "CS");
+
+        /*
+         * Override session read committed (CS) with serializable (RR)
+         * on statement level
+         */
+        assertQueryResult(st.executeQuery("select * from t1 with RR"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "serializable isolation level using share table locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: RR (serializable)
+         */
+        st.executeUpdate("set isolation serializable");
+        assertIsolation(ps, "RR");
+        /*
+         * Override session RR (serializable) with repeatable read (RS)
+         * on statement level
+         */
+        assertQueryResult(st.executeQuery("select * from t1 with RS"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "repeatable read isolation level using share row locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Session isolation: CS (read committed)
+         */
+        st.executeUpdate("set current isolation to read committed");
+        assertIsolation(ps, "CS");
+        /*
+         * Override session CS (read committed) with UR (read uncommitted)
+         * on statement level
+         */
+        assertQueryResult(st.executeQuery("select * from t1 with ur"));
+        assertPlan(
+            st,
+            "Index Scan ResultSet for T1 using constraint ASDF at " +
+            "read uncommitted isolation level using share row locking " +
+            "chosen by the optimizer");
+
+        /* -----------------------------------------------------------------
+         * Unknown isolation levels: expect syntax errors
+         */
+        final String e = "42X01"; // syntax error
+        assertStatementError(e, st, "select * from t1 with rw");
+        assertStatementError(e, st, "select * from t1 with dirty read");
+        assertStatementError(e, st, "select * from t1 with read uncommitted");
+        assertStatementError(e, st, "select * from t1 with read committed");
+        assertStatementError(e, st, "select * from t1 with cursor stability");
+        assertStatementError(e, st, "select * from t1 with repeatable read");
+        assertStatementError(e, st, "select * from t1 with serializable");
+
+        /* -----------------------------------------------------------------
+         * Check the db2 isolation levels can be used as identifiers
+         */
+        st.executeUpdate("create table db2iso(cs int, rr int, ur int, rs int)");
+        ResultSet rs = st.executeQuery("select cs, rr, ur, rs from db2iso");
+        JDBC.assertEmpty(rs);
+        rollback();
+    }
+
+    private void assertQueryResult(ResultSet rs) throws SQLException {
+        JDBC.assertColumnNames(rs, new String [] {"C1"});
+        JDBC.assertFullResultSet(rs, new String [][]{{"1"}}, true);
+        rs.close();
+    }
+
+    private void assertPlan(Statement s, String expected)
+            throws SQLException {
+        SQLUtilities.getRuntimeStatisticsParser(s).
+            assertSequence(new String[]{expected});
+    }
+
+    private void assertIsolation(PreparedStatement p, String expected)
+            throws SQLException {
+        ResultSet rs = p.executeQuery();
+        JDBC.assertColumnNames(rs, new String [] {"1"});
+        JDBC.assertFullResultSet(rs, new String [][]{{expected}}, true);
+        rs.close();
+    }
+}

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=1519008&r1=1519007&r2=1519008&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 Fri Aug 30 16:03:33 2013
@@ -242,6 +242,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(XMLOptimizerTraceTest.suite());
         suite.addTest(MergeStatementTest.suite());
         suite.addTest(ConstraintCharacteristicsTest.suite());
+        suite.addTest(DB2IsolationLevelsTest.suite());
         return suite;
 	}
 }



Mime
View raw message