db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r178053 - in /incubator/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/catalog/types/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/ testing/org/apache/derbyTesting/functionTests/tests/tools/
Date Mon, 23 May 2005 23:08:02 GMT
Author: bandaram
Date: Mon May 23 16:08:02 2005
New Revision: 178053

URL: http://svn.apache.org/viewcvs?rev=178053&view=rev
Log:
Derby-167: Add support for BY DEFAULT option to IDENTITY columns.

Submitted by Tomohito Nakayama (tomonaka@basil.ocn.ne.jp)


Modified:
    incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/DefaultInfo.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
    incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
    incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/DefaultInfo.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/DefaultInfo.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/DefaultInfo.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/DefaultInfo.java Mon May
23 16:08:02 2005
@@ -21,9 +21,7 @@
 package org.apache.derby.catalog;
 
 /**
-	
-
- <p>An interface for describing a default for a column or parameter in Cloudscape systems.
+ <p>An interface for describing a default for a column or parameter in Cloudscape systems.</p>
  */
 public interface DefaultInfo
 {
@@ -33,4 +31,18 @@
 	 * @return The text of the default.
 	 */
 	public String getDefaultText();
+	
+	
+	/**
+	 * Is default value generated by auto increment?
+	 *
+	 * @return true if always generated by auto increment.
+	 */
+	
+	//ToCleanUp
+	//Additional definitive information of AutoIncremnt 
+	//such as autoIncrementStart and autoInrementInc 
+	//should be gotten from this interface.
+
+	public boolean isDefaultValueAutoinc();
 }

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/DefaultInfoImpl.java
Mon May 23 16:08:02 2005
@@ -53,6 +53,9 @@
 
 	private DataValueDescriptor	defaultValue;
 	private String				defaultText;
+	private int                     type;
+
+	final private static int BITS_MASK_IS_DEFAULTVALUE_AUTOINC = 0x1 << 0;
 
 	/**
 	 * Public niladic constructor. Needed for Formatable interface to work.
@@ -65,10 +68,11 @@
 	 *
 	 * @param defaultText	The text of the default.
 	 */
-	public DefaultInfoImpl(
+	public DefaultInfoImpl(boolean isDefaultValueAutoinc,
 		String defaultText,
 		DataValueDescriptor defaultValue)
 	{
+		this.type = calcType(isDefaultValueAutoinc);
 		this.defaultText = defaultText;
 		this.defaultValue = defaultValue;
 	}
@@ -101,7 +105,7 @@
 	{
 		defaultText = (String) in.readObject();
 		defaultValue = (DataValueDescriptor) in.readObject();
-		in.readInt(); // old provider info count - always 0.
+		type = in.readInt();
 	}
 
 	/**
@@ -116,7 +120,7 @@
 	{
 		out.writeObject( defaultText );
 		out.writeObject( defaultValue );
-		out.writeInt(0); // old provider info count - always 0.
+		out.writeInt(type);
 	}
  
 	/**
@@ -149,4 +153,26 @@
 	{
 		this.defaultValue = defaultValue;
 	}
+	
+	/**
+	 * @see DefaultInfo#isDefaultValueAutoinc
+	 */
+	public boolean isDefaultValueAutoinc(){
+		return (type & BITS_MASK_IS_DEFAULTVALUE_AUTOINC ) != 0;
+	}
+	
+	/**
+	 * This function returns stored value for flags and so on.
+	 */
+	private static int calcType(boolean isDefaultValueAutoinc){
+
+		int value = 0;
+
+		if(isDefaultValueAutoinc){
+			value |= BITS_MASK_IS_DEFAULTVALUE_AUTOINC;
+		}
+
+		return value;
+	}
+	
 }

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
Mon May 23 16:08:02 2005
@@ -107,14 +107,9 @@
 
 		if (SanityManager.DEBUG)
 		{
-			if (autoinc)
-			{
-				SanityManager.ASSERT((autoincInc != 0), "increment is zero for  autoincrement column");
-			}
-			else
-			{
-				SanityManager.ASSERT((autoincInc == 0), "increment is non-zero for non-autoincrement
column");
-			}
+			assertAutoinc(autoinc,
+				      autoincInc,
+				      columnDefaultInfo);
 		}
 
 		this.autoincStart = autoincStart;
@@ -160,29 +155,9 @@
 
 		if (SanityManager.DEBUG)
 		{
-			if (autoinc)
-			{
-				SanityManager.ASSERT(autoincInc != 0);
-			}
-			else
-			{
-				SanityManager.ASSERT(autoincInc == 0);
-			}
-		}
-		
-		this.autoincStart = autoincStart;
-		this.autoincInc = autoincInc;
-
-		if (SanityManager.DEBUG)
-		{
-			if (autoinc)
-			{
-				SanityManager.ASSERT((autoincInc != 0), "increment is 0 for autoincrement column");
-			}
-			else
-			{
-				SanityManager.ASSERT((autoincInc == 0), "increment is non-zero for non-autoincrement
column");
-			}
+			assertAutoinc(autoinc,
+				      autoincInc,
+				      columnDefaultInfo);
 		}
 		
 		this.autoincStart = autoincStart;
@@ -342,6 +317,13 @@
 	}
 
 	/**
+	 * Is this column to have autoincremented value always ?
+	 */
+	public boolean isAutoincAlways(){
+		return (columnDefaultInfo == null) && isAutoincrement();
+	}
+
+	/**
 	 * Get the start value of an autoincrement column
 	 * 
 	 * @return Get the start value of an autoincrement column
@@ -410,4 +392,25 @@
 	{
 		return "Column";
 	}
+
+	
+	private static void assertAutoinc(boolean autoinc,
+					  long autoincInc,
+					  DefaultInfo defaultInfo){
+		if (autoinc){
+			SanityManager.ASSERT((autoincInc != 0), "increment is zero for  autoincrement column");
+			SanityManager.ASSERT((defaultInfo == null ||
+					      defaultInfo.isDefaultValueAutoinc()),
+					     "If column is autoinc and have defaultInfo, " + 
+					     "isDefaultValueAutoinc must be true.");
+		}
+		else{
+			SanityManager.ASSERT((autoincInc == 0), "increment is non-zero for non-autoincrement column");
+			SanityManager.ASSERT((defaultInfo == null ||
+					      ! defaultInfo.isDefaultValueAutoinc()),
+					     "If column is not autoinc and have defaultInfo, " + 
+					     "isDefaultValueAutoinc can not be true");
+		}
+	}
+
 }

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
Mon May 23 16:08:02 2005
@@ -475,6 +475,15 @@
 		if (defaultNode == null)
 			return;
 
+		//Examin whether default value is autoincrement.
+		if (isAutoincrement){
+			defaultInfo = createDefaultInfoOfAutoInc();
+			return;
+		}
+		
+		
+		//Judged as default value is constant value.
+		
 		CompilerContext cc = getCompilerContext();
 
 		ValueNode defaultTree = defaultNode.getDefaultTree();
@@ -535,7 +544,9 @@
 
 			// Save off the default text
 			// RESOLVEDEFAULT - Convert to constant if possible
-			defaultInfo = new DefaultInfoImpl(defaultNode.getDefaultText(), defaultValue);
+			defaultInfo = new DefaultInfoImpl(false,
+							  defaultNode.getDefaultText(), 
+							  defaultValue);
 
 			if (SanityManager.DEBUG)
 			{
@@ -555,6 +566,14 @@
 			cc.setReliability(previousReliability);
 		}
 	}
+
+
+	private static DefaultInfoImpl createDefaultInfoOfAutoInc(){
+		return new DefaultInfoImpl(true,
+					   null, 
+					   null);
+	}
+	
 
 	/**
 	 * Check the validity of the default for this node

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Mon May 23 16:08:02 2005
@@ -3918,11 +3918,12 @@
 					(sourceRC.isAutoincrementGenerated()))
 				{
 					sourceRC.setColumnDescriptor(cd.getTableDescriptor(), cd);
-					continue;
-				}
-				throw StandardException.newException(
-									SQLState.LANG_AI_CANNOT_MODIFY_AI,
+
+				}else{
+					if(cd.isAutoincAlways())
+						throw StandardException.newException(SQLState.LANG_AI_CANNOT_MODIFY_AI,
 									rc.getName());
+				}
 			}
 		}
 	}

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultSetNode.java
Mon May 23 16:08:02 2005
@@ -1139,8 +1139,10 @@
 
             // Check for defaults
             DefaultInfoImpl defaultInfo = (DefaultInfoImpl) colDesc.getDefaultInfo();
-
-            if (defaultInfo != null)
+	    
+	    //Column has constant default value , 
+	    //if it have defaultInfo and not be autoincrement.
+            if (defaultInfo != null && ! colDesc.isAutoincrement())
             {
                 //RESOLVEPARAMETER - skip the tree if we have the value
                 /*

Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
(original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Mon May 23 16:08:02 2005
@@ -9061,6 +9061,10 @@
 /*
  * <A NAME="generatedColumnOption">generatedColumnOption</A>
  */
+
+//ToCleanUp
+//A specific class not such long[] should exists for autoIncrementInfo ...
+
 ValueNode
 generatedColumnOption(long[] autoIncrementInfo) throws StandardException :
 {
@@ -9073,7 +9077,19 @@
 	autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_IS_AUTOINCREMENT_INDEX] = 1;
     }
 
-	<GENERATED> <ALWAYS> <AS> <IDENTITY> [<LEFT_PAREN> value =
autoIncrementBeginEnd(autoIncrementInfo) <RIGHT_PAREN>]
+	<GENERATED> 
+	(
+	 <ALWAYS>  {
+		value = null;
+	}|
+	 <BY> <_DEFAULT> { 
+		checkVersion(DataDictionary.DD_VERSION_DERBY_10_1,
+			     "GENERATED BY DEFAULT");
+
+		value = (ValueNode) nodeFactory.getNode(C_NodeTypes.DEFAULT_NODE,
+							getContextManager()) ;}
+	)
+	<AS> <IDENTITY> [<LEFT_PAREN> autoIncrementBeginEnd(autoIncrementInfo)
<RIGHT_PAREN>]
     {
 		return value;
     }
@@ -9082,7 +9098,7 @@
 /*
  * <A NAME="autoIncrementBeginEnd">autoIncrementBeginEnd</A>
  */
-ValueNode
+void 
 autoIncrementBeginEnd(long[] autoIncrementInfo) throws StandardException :
 {
 	long		autoIncrementInitial = 1;
@@ -9092,14 +9108,14 @@
         <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
     {
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
-		return (ValueNode) null;
+		return;
     }
 |
 		<START> <WITH> autoIncrementInitial = exactNumber() [<COMMA> <INCREMENT>
<BY> autoIncrementIncrement = exactNumber() ]
     {
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementInitial;
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
-		return (ValueNode) null;
+		return;
     }
 }
 

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
Mon May 23 16:08:02 2005
@@ -1379,6 +1379,242 @@
 0 rows inserted/updated/deleted
 ij> drop table t3;
 0 rows inserted/updated/deleted
+ij> -- Defaults/always
+-- without increment option
+create table t1(i int, t1_autogen int generated always as identity);
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity);
+0 rows inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+1          |1          
+1          |2          
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+1          |1          
+1          |2          
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> create table t1(i int, t1_autogen int generated always as identity);
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity);
+0 rows inserted/updated/deleted
+ij> insert into t1(i,t1_autogen) values(2,1);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i,t1_autogen) values(2,2);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+2          |1          
+2          |2          
+ij> insert into t2(i,t2_autogen) values(2,1);
+1 row inserted/updated/deleted
+ij> insert into t2(i,t2_autogen) values(2,2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+2          |1          
+2          |2          
+2          |1          
+2          |2          
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> --with increment by 
+create table t1(i int, t1_autogen int generated always as identity(increment by 10));
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity(increment by
10));
+0 rows inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+1          |1          
+1          |11         
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+1          |1          
+1          |11         
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> create table t1(i int, t1_autogen int generated always as identity(increment by 10));
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity(increment by
10));
+0 rows inserted/updated/deleted
+ij> insert into t1(i,t1_autogen) values(2,1);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i,t1_autogen) values(2,2);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+2          |1          
+2          |11         
+ij> insert into t2(i,t2_autogen) values(2,1);
+1 row inserted/updated/deleted
+ij> insert into t2(i,t2_autogen) values(2,2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+2          |1          
+2          |2          
+2          |1          
+2          |11         
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> --with start with, increment by 
+create table t1(i int, t1_autogen int generated always as identity(start with 100, increment
by 20));
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity(start with
100, increment by 20));
+0 rows inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+1          |100        
+1          |120        
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(1);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+1          |100        
+1          |120        
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> create table t1(i int, t1_autogen int generated always as identity(start with 100,
increment by 20));
+0 rows inserted/updated/deleted
+ij> create table t2(i int, t2_autogen int generated by default as identity(start with
100, increment by 20));
+0 rows inserted/updated/deleted
+ij> insert into t1(i,t1_autogen) values(2,1);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i,t1_autogen) values(2,2);
+ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'. 
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t1(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t1;
+I          |T1_AUTOGEN 
+-----------------------
+2          |100        
+2          |120        
+ij> insert into t2(i,t2_autogen) values(2,1);
+1 row inserted/updated/deleted
+ij> insert into t2(i,t2_autogen) values(2,2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> insert into t2(i) values(2);
+1 row inserted/updated/deleted
+ij> select * from t2;
+I          |T2_AUTOGEN 
+-----------------------
+2          |1          
+2          |2          
+2          |100        
+2          |120        
+ij> drop table t1;
+0 rows inserted/updated/deleted
+ij> drop table t2;
+0 rows inserted/updated/deleted
+ij> --with unique constraint
+create table t3(i int,t3_autogen int generated by default as identity(start with 0, increment
by 1) unique);
+0 rows inserted/updated/deleted
+ij> insert into t3(i,t3_autogen) values(1,0);
+1 row inserted/updated/deleted
+ij> insert into t3(i,t3_autogen) values(2,1);
+1 row inserted/updated/deleted
+ij> insert into t3(i) values(3);
+ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx'
defined on 'T3'.
+ij> insert into t3(i) values(4);
+ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx'
defined on 'T3'.
+ij> insert into t3(i) values(5);
+1 row inserted/updated/deleted
+ij> select i,t3_autogen from t3;
+I          |T3_AUTOGEN 
+-----------------------
+1          |0          
+2          |1          
+5          |2          
+ij> drop table t3;
+0 rows inserted/updated/deleted
+ij> --with unique index
+create table t4(i int,t4_autogen int generated by default as identity(start with 0, increment
by 1));
+0 rows inserted/updated/deleted
+ij> create unique index idx_t4_autogen on t4(t4_autogen);
+0 rows inserted/updated/deleted
+ij> insert into t4(i,t4_autogen) values(1,0);
+1 row inserted/updated/deleted
+ij> insert into t4(i,t4_autogen) values(2,1);
+1 row inserted/updated/deleted
+ij> insert into t4(i) values(3);
+ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'IDX_T4_AUTOGEN' defined
on 'T4'.
+ij> insert into t4(i) values(4);
+ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'IDX_T4_AUTOGEN' defined
on 'T4'.
+ij> insert into t4(i) values(5);
+1 row inserted/updated/deleted
+ij> select i,t4_autogen from t4;
+I          |T4_AUTOGEN 
+-----------------------
+1          |0          
+2          |1          
+5          |2          
+ij> drop index idx_t4_autogen;
+0 rows inserted/updated/deleted
+ij> drop table t4;
+0 rows inserted/updated/deleted
 ij> -- test IDENTITY_VAL_LOCAL function with 2 different connections
 -- connection one
 connect 'wombat' as conn1;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/ieptests.out
Mon May 23 16:08:02 2005
@@ -734,4 +734,51 @@
 --------------------------------------------------------------------------------------------------------------------------------
 Essential Duties and Responsibilities (include but not limited to):
 *Assist the director in his work activities in leading the&
-ij> 
+ij> --test for autoincrement values
+CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.dat',null,null,null);
+0 rows inserted/updated/deleted
+ij> create table dest_always(i int generated always as identity);
+0 rows inserted/updated/deleted
+ij> create table dest_by_default(i int generated by default as identity);
+0 rows inserted/updated/deleted
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,0);
+ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was
thrown while evaluating an expression.
+ERROR 42Z23: Attempt to modify an identity column 'I'. 
+ij> select * from dest_always;
+I          
+-----------
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,0);
+0 rows inserted/updated/deleted
+ij> select * from dest_by_default;
+I          
+-----------
+1          
+2          
+3          
+ij> drop table dest_always;
+0 rows inserted/updated/deleted
+ij> drop table dest_by_default;
+0 rows inserted/updated/deleted
+ij> create table dest_always(i int generated always as identity);
+0 rows inserted/updated/deleted
+ij> create table dest_by_default(i int generated by default as identity);
+0 rows inserted/updated/deleted
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,1);
+ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity column 'I'.' was
thrown while evaluating an expression.
+ERROR 42Z23: Attempt to modify an identity column 'I'. 
+ij> select * from dest_always;
+I          
+-----------
+ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,1);
+0 rows inserted/updated/deleted
+ij> select * from dest_by_default;
+I          
+-----------
+1          
+2          
+3          
+ij> drop table dest_always;
+0 rows inserted/updated/deleted
+ij> drop table dest_by_default;
+0 rows inserted/updated/deleted
+ij>
\ No newline at end of file

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
Mon May 23 16:08:02 2005
@@ -692,6 +692,144 @@
 drop table t2;
 drop table t3;
 
+
+-- Defaults/always
+-- without increment option
+create table t1(i int, t1_autogen int generated always as identity);
+create table t2(i int, t2_autogen int generated by default as identity);
+
+insert into t1(i) values(1);
+insert into t1(i) values(1);
+select * from t1;
+
+insert into t2(i) values(1);
+insert into t2(i) values(1);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+create table t1(i int, t1_autogen int generated always as identity);
+create table t2(i int, t2_autogen int generated by default as identity);
+
+insert into t1(i,t1_autogen) values(2,1);
+insert into t1(i,t1_autogen) values(2,2);
+insert into t1(i) values(2);
+insert into t1(i) values(2);
+select * from t1;
+
+insert into t2(i,t2_autogen) values(2,1);
+insert into t2(i,t2_autogen) values(2,2);
+insert into t2(i) values(2);
+insert into t2(i) values(2);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+
+--with increment by 
+
+create table t1(i int, t1_autogen int generated always as identity(increment by 10));
+create table t2(i int, t2_autogen int generated by default as identity(increment by 10));
+
+insert into t1(i) values(1);
+insert into t1(i) values(1);
+select * from t1;
+
+insert into t2(i) values(1);
+insert into t2(i) values(1);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+create table t1(i int, t1_autogen int generated always as identity(increment by 10));
+create table t2(i int, t2_autogen int generated by default as identity(increment by 10));
+
+insert into t1(i,t1_autogen) values(2,1);
+insert into t1(i,t1_autogen) values(2,2);
+insert into t1(i) values(2);
+insert into t1(i) values(2);
+select * from t1;
+
+insert into t2(i,t2_autogen) values(2,1);
+insert into t2(i,t2_autogen) values(2,2);
+insert into t2(i) values(2);
+insert into t2(i) values(2);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+
+--with start with, increment by 
+
+create table t1(i int, t1_autogen int generated always as identity(start with 100, increment
by 20));
+create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment
by 20));
+
+insert into t1(i) values(1);
+insert into t1(i) values(1);
+select * from t1;
+
+insert into t2(i) values(1);
+insert into t2(i) values(1);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+create table t1(i int, t1_autogen int generated always as identity(start with 100, increment
by 20));
+create table t2(i int, t2_autogen int generated by default as identity(start with 100, increment
by 20));
+
+insert into t1(i,t1_autogen) values(2,1);
+insert into t1(i,t1_autogen) values(2,2);
+insert into t1(i) values(2);
+insert into t1(i) values(2);
+select * from t1;
+
+insert into t2(i,t2_autogen) values(2,1);
+insert into t2(i,t2_autogen) values(2,2);
+insert into t2(i) values(2);
+insert into t2(i) values(2);
+select * from t2;
+
+drop table t1;
+drop table t2;
+
+
+--with unique constraint
+
+create table t3(i int,t3_autogen int generated by default as identity(start with 0, increment
by 1) unique);
+
+insert into t3(i,t3_autogen) values(1,0);
+insert into t3(i,t3_autogen) values(2,1);
+
+insert into t3(i) values(3);
+insert into t3(i) values(4);
+insert into t3(i) values(5);
+
+select i,t3_autogen from t3;
+
+drop table t3;
+
+--with unique index
+
+create table t4(i int,t4_autogen int generated by default as identity(start with 0, increment
by 1));
+create unique index idx_t4_autogen on t4(t4_autogen);
+
+insert into t4(i,t4_autogen) values(1,0);
+insert into t4(i,t4_autogen) values(2,1);
+
+insert into t4(i) values(3);
+insert into t4(i) values(4);
+insert into t4(i) values(5);
+
+select i,t4_autogen from t4;
+
+drop index idx_t4_autogen;
+drop table t4;
+
 -- test IDENTITY_VAL_LOCAL function with 2 different connections
 -- connection one
 connect 'wombat' as conn1;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql?rev=178053&r1=178052&r2=178053&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ieptests.sql
Mon May 23 16:08:02 2005
@@ -413,9 +413,30 @@
 select detail_description from position_info where position_code='AG1000';
 
 
+--test for autoincrement values
+CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.dat',null,null,null);
 
 
+create table dest_always(i int generated always as identity);
+create table dest_by_default(i int generated by default as identity);
 
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,0);
+select * from dest_always;
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,0);
+select * from dest_by_default;
 
+drop table dest_always;
+drop table dest_by_default;
 
+
+create table dest_always(i int generated always as identity);
+create table dest_by_default(i int generated by default as identity);
+
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.dat',null,null,null,1);
+select * from dest_always;
+CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoinc.dat',null,null,null,1);
+select * from dest_by_default;
+
+drop table dest_always;
+drop table dest_by_default;
 



Mime
View raw message