Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 18756 invoked from network); 23 May 2005 23:08:11 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 23 May 2005 23:08:11 -0000 Received: (qmail 79130 invoked by uid 500); 23 May 2005 23:08:11 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 79091 invoked by uid 500); 23 May 2005 23:08:10 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 79042 invoked by uid 500); 23 May 2005 23:08:10 -0000 Delivered-To: apmail-incubator-derby-cvs@incubator.apache.org Received: (qmail 79032 invoked by uid 99); 23 May 2005 23:08:10 -0000 X-ASF-Spam-Status: No, hits=-9.8 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from minotaur.apache.org (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.28) with SMTP; Mon, 23 May 2005 16:08:07 -0700 Received: (qmail 18715 invoked by uid 65534); 23 May 2005 23:08:06 -0000 Message-ID: <20050523230805.18710.qmail@minotaur.apache.org> Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable 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 -0000 To: derby-cvs@incubator.apache.org From: bandaram@apache.org X-Mailer: svnmailer-1.0.0-dev X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N Author: bandaram Date: Mon May 23 16:08:02 2005 New Revision: 178053 URL: http://svn.apache.org/viewcvs?rev=3D178053&view=3Drev 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/Default= Info.java incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/D= efaultInfoImpl.java incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictio= nary/ColumnDescriptor.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ColumnDefinitionNode.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultColumnList.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultSetNode.java incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/autoincrement.out incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/ieptests.out incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/autoincrement.sql incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/tools/ieptests.sql Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/D= efaultInfo.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/catalog/DefaultInfo.java?rev=3D178053&r1=3D178052&r2=3D1780= 53&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/Default= Info.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/Default= Info.java Mon May 23 16:08:02 2005 @@ -21,9 +21,7 @@ package org.apache.derby.catalog; =20 /** -=09 - -

An interface for describing a default for a column or parameter in Clo= udscape systems. +

An interface for describing a default for a column or parameter in Clo= udscape systems.

*/ public interface DefaultInfo { @@ -33,4 +31,18 @@ * @return The text of the default. */ public String getDefaultText(); +=09 +=09 + /** + * Is default value generated by auto increment? + * + * @return true if always generated by auto increment. + */ +=09 + //ToCleanUp + //Additional definitive information of AutoIncremnt=20 + //such as autoIncrementStart and autoInrementInc=20 + //should be gotten from this interface. + + public boolean isDefaultValueAutoinc(); } Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/t= ypes/DefaultInfoImpl.java URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/o= rg/apache/derby/catalog/types/DefaultInfoImpl.java?rev=3D178053&r1=3D178052= &r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/D= efaultInfoImpl.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/catalog/types/D= efaultInfoImpl.java Mon May 23 16:08:02 2005 @@ -53,6 +53,9 @@ =20 private DataValueDescriptor defaultValue; private String defaultText; + private int type; + + final private static int BITS_MASK_IS_DEFAULTVALUE_AUTOINC =3D 0x1 << 0; =20 /** * 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 =3D calcType(isDefaultValueAutoinc); this.defaultText =3D defaultText; this.defaultValue =3D defaultValue; } @@ -101,7 +105,7 @@ { defaultText =3D (String) in.readObject(); defaultValue =3D (DataValueDescriptor) in.readObject(); - in.readInt(); // old provider info count - always 0. + type =3D in.readInt(); } =20 /** @@ -116,7 +120,7 @@ { out.writeObject( defaultText ); out.writeObject( defaultValue ); - out.writeInt(0); // old provider info count - always 0. + out.writeInt(type); } =20 /** @@ -149,4 +153,26 @@ { this.defaultValue =3D defaultValue; } +=09 + /** + * @see DefaultInfo#isDefaultValueAutoinc + */ + public boolean isDefaultValueAutoinc(){ + return (type & BITS_MASK_IS_DEFAULTVALUE_AUTOINC ) !=3D 0; + } +=09 + /** + * This function returns stored value for flags and so on. + */ + private static int calcType(boolean isDefaultValueAutoinc){ + + int value =3D 0; + + if(isDefaultValueAutoinc){ + value |=3D BITS_MASK_IS_DEFAULTVALUE_AUTOINC; + } + + return value; + } +=09 } 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/o= rg/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java?rev=3D178053&r1= =3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictio= nary/ColumnDescriptor.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictio= nary/ColumnDescriptor.java Mon May 23 16:08:02 2005 @@ -107,14 +107,9 @@ =20 if (SanityManager.DEBUG) { - if (autoinc) - { - SanityManager.ASSERT((autoincInc !=3D 0), "increment is zero for auto= increment column"); - } - else - { - SanityManager.ASSERT((autoincInc =3D=3D 0), "increment is non-zero for= non-autoincrement column"); - } + assertAutoinc(autoinc, + autoincInc, + columnDefaultInfo); } =20 this.autoincStart =3D autoincStart; @@ -160,29 +155,9 @@ =20 if (SanityManager.DEBUG) { - if (autoinc) - { - SanityManager.ASSERT(autoincInc !=3D 0); - } - else - { - SanityManager.ASSERT(autoincInc =3D=3D 0); - } - } - =09 - this.autoincStart =3D autoincStart; - this.autoincInc =3D autoincInc; - - if (SanityManager.DEBUG) - { - if (autoinc) - { - SanityManager.ASSERT((autoincInc !=3D 0), "increment is 0 for autoincr= ement column"); - } - else - { - SanityManager.ASSERT((autoincInc =3D=3D 0), "increment is non-zero for= non-autoincrement column"); - } + assertAutoinc(autoinc, + autoincInc, + columnDefaultInfo); } =09 this.autoincStart =3D autoincStart; @@ -342,6 +317,13 @@ } =20 /** + * Is this column to have autoincremented value always ? + */ + public boolean isAutoincAlways(){ + return (columnDefaultInfo =3D=3D null) && isAutoincrement(); + } + + /** * Get the start value of an autoincrement column *=20 * @return Get the start value of an autoincrement column @@ -410,4 +392,25 @@ { return "Column"; } + +=09 + private static void assertAutoinc(boolean autoinc, + long autoincInc, + DefaultInfo defaultInfo){ + if (autoinc){ + SanityManager.ASSERT((autoincInc !=3D 0), "increment is zero for autoi= ncrement column"); + SanityManager.ASSERT((defaultInfo =3D=3D null || + defaultInfo.isDefaultValueAutoinc()), + "If column is autoinc and have defaultInfo, " +=20 + "isDefaultValueAutoinc must be true."); + } + else{ + SanityManager.ASSERT((autoincInc =3D=3D 0), "increment is non-zero for = non-autoincrement column"); + SanityManager.ASSERT((defaultInfo =3D=3D null || + ! defaultInfo.isDefaultValueAutoinc()), + "If column is not autoinc and have defaultInfo, " +=20 + "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/o= rg/apache/derby/impl/sql/compile/ColumnDefinitionNode.java?rev=3D178053&r1= =3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ColumnDefinitionNode.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ColumnDefinitionNode.java Mon May 23 16:08:02 2005 @@ -475,6 +475,15 @@ if (defaultNode =3D=3D null) return; =20 + //Examin whether default value is autoincrement. + if (isAutoincrement){ + defaultInfo =3D createDefaultInfoOfAutoInc(); + return; + } + =09 + =09 + //Judged as default value is constant value. + =09 CompilerContext cc =3D getCompilerContext(); =20 ValueNode defaultTree =3D defaultNode.getDefaultTree(); @@ -535,7 +544,9 @@ =20 // Save off the default text // RESOLVEDEFAULT - Convert to constant if possible - defaultInfo =3D new DefaultInfoImpl(defaultNode.getDefaultText(), defau= ltValue); + defaultInfo =3D new DefaultInfoImpl(false, + defaultNode.getDefaultText(),=20 + defaultValue); =20 if (SanityManager.DEBUG) { @@ -555,6 +566,14 @@ cc.setReliability(previousReliability); } } + + + private static DefaultInfoImpl createDefaultInfoOfAutoInc(){ + return new DefaultInfoImpl(true, + null,=20 + null); + } +=09 =20 /** * 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/o= rg/apache/derby/impl/sql/compile/ResultColumnList.java?rev=3D178053&r1=3D17= 8052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultColumnList.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/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/o= rg/apache/derby/impl/sql/compile/ResultSetNode.java?rev=3D178053&r1=3D17805= 2&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultSetNode.java (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/ResultSetNode.java Mon May 23 16:08:02 2005 @@ -1139,8 +1139,10 @@ =20 // Check for defaults DefaultInfoImpl defaultInfo =3D (DefaultInfoImpl) colDesc.getD= efaultInfo(); - - if (defaultInfo !=3D null) + =20 + //Column has constant default value ,=20 + //if it have defaultInfo and not be autoincrement. + if (defaultInfo !=3D 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/o= rg/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=3D178053&r1=3D178052&r2= =3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj (original) +++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compil= e/sqlgrammar.jj Mon May 23 16:08:02 2005 @@ -9061,6 +9061,10 @@ /* * generatedColumnOption */ + +//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] =3D= 1; } =20 - [ value =3D autoIncremen= tBeginEnd(autoIncrementInfo) ] + =20 + ( + { + value =3D null; + }| + <_DEFAULT> {=20 + checkVersion(DataDictionary.DD_VERSION_DERBY_10_1, + "GENERATED BY DEFAULT"); + + value =3D (ValueNode) nodeFactory.getNode(C_NodeTypes.DEFAULT_NODE, + getContextManager()) ;} + ) + [ autoIncrementBeginEnd(autoIncrementInfo) ] { return value; } @@ -9082,7 +9098,7 @@ /* * autoIncrementBeginEnd */ -ValueNode +void=20 autoIncrementBeginEnd(long[] autoIncrementInfo) throws StandardException : { long autoIncrementInitial =3D 1; @@ -9092,14 +9108,14 @@ autoIncrementIncrement =3D exactNumber() { autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] =3D autoIncreme= ntIncrement; - return (ValueNode) null; + return; } | autoIncrementInitial =3D exactNumber() [ autoIncrementIncrement =3D exactNumber() ] { autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] =3D autoIncre= mentInitial; autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] =3D autoIncreme= ntIncrement; - return (ValueNode) null; + return; } } =20 Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/f= unctionTests/master/autoincrement.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/autoincrement.out?rev=3D178053= &r1=3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/autoincrement.out (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/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=20 +----------------------- +1 |1 =20 +1 |2 =20 +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=20 +----------------------- +1 |1 =20 +1 |2 =20 +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'.=20 +ij> insert into t1(i,t1_autogen) values(2,2); +ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'.=20 +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=20 +----------------------- +2 |1 =20 +2 |2 =20 +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=20 +----------------------- +2 |1 =20 +2 |2 =20 +2 |1 =20 +2 |2 =20 +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> --with increment by=20 +create table t1(i int, t1_autogen int generated always as identity(increme= nt 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=20 +----------------------- +1 |1 =20 +1 |11 =20 +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=20 +----------------------- +1 |1 =20 +1 |11 =20 +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(inc= rement 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'.=20 +ij> insert into t1(i,t1_autogen) values(2,2); +ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'.=20 +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=20 +----------------------- +2 |1 =20 +2 |11 =20 +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=20 +----------------------- +2 |1 =20 +2 |2 =20 +2 |1 =20 +2 |11 =20 +ij> drop table t1; +0 rows inserted/updated/deleted +ij> drop table t2; +0 rows inserted/updated/deleted +ij> --with start with, increment by=20 +create table t1(i int, t1_autogen int generated always as identity(start w= ith 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=20 +----------------------- +1 |100 =20 +1 |120 =20 +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=20 +----------------------- +1 |100 =20 +1 |120 =20 +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(sta= rt 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'.=20 +ij> insert into t1(i,t1_autogen) values(2,2); +ERROR 42Z23: Attempt to modify an identity column 'T1_AUTOGEN'.=20 +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=20 +----------------------- +2 |100 =20 +2 |120 =20 +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=20 +----------------------- +2 |1 =20 +2 |2 =20 +2 |100 =20 +2 |120 =20 +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(star= t 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 dupl= icate key value in a unique or primary key constraint or unique index ident= ified 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 dupl= icate key value in a unique or primary key constraint or unique index ident= ified 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=20 +----------------------- +1 |0 =20 +2 |1 =20 +5 |2 =20 +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(star= t 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 dupl= icate key value in a unique or primary key constraint or unique index ident= ified 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 dupl= icate key value in a unique or primary key constraint or unique index ident= ified 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=20 +----------------------- +1 |0 =20 +2 |1 =20 +5 |2 =20 +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/f= unctionTests/master/ieptests.out URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/= org/apache/derbyTesting/functionTests/master/ieptests.out?rev=3D178053&r1= =3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/master/ieptests.out (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/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>=20 +ij> --test for autoincrement values +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.d= at',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/autoi= nc.dat',null,null,null,0); +ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity c= olumn 'I'.' was thrown while evaluating an expression. +ERROR 42Z23: Attempt to modify an identity column 'I'.=20 +ij> select * from dest_always; +I =20 +----------- +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/a= utoinc.dat',null,null,null,0); +0 rows inserted/updated/deleted +ij> select * from dest_by_default; +I =20 +----------- +1 =20 +2 =20 +3 =20 +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/autoi= nc.dat',null,null,null,1); +ERROR 38000: The exception 'SQL Exception: Attempt to modify an identity c= olumn 'I'.' was thrown while evaluating an expression. +ERROR 42Z23: Attempt to modify an identity column 'I'.=20 +ij> select * from dest_always; +I =20 +----------- +ij> CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/a= utoinc.dat',null,null,null,1); +0 rows inserted/updated/deleted +ij> select * from dest_by_default; +I =20 +----------- +1 =20 +2 =20 +3 =20 +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/f= unctionTests/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=3D17= 8053&r1=3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/autoincrement.sql (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/lang/autoincrement.sql Mon May 23 16:08:02 2005 @@ -692,6 +692,144 @@ drop table t2; drop table t3; =20 + +-- 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=20 + +create table t1(i int, t1_autogen int generated always as identity(increme= nt by 10)); +create table t2(i int, t2_autogen int generated by default as identity(inc= rement 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(increme= nt by 10)); +create table t2(i int, t2_autogen int generated by default as identity(inc= rement 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=20 + +create table t1(i int, t1_autogen int generated always as identity(start w= ith 100, increment by 20)); +create table t2(i int, t2_autogen int generated by default as identity(sta= rt 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 w= ith 100, increment by 20)); +create table t2(i int, t2_autogen int generated by default as identity(sta= rt 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(star= t 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(star= t 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/f= unctionTests/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=3D178053= &r1=3D178052&r2=3D178053&view=3Ddiff =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D --- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/tools/ieptests.sql (original) +++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functio= nTests/tests/tools/ieptests.sql Mon May 23 16:08:02 2005 @@ -413,9 +413,30 @@ select detail_description from position_info where position_code=3D'AG1000= '; =20 =20 +--test for autoincrement values +CALL SYSCS_UTIL.SYSCS_EXPORT_QUERY('values(1),(2),(3)','extinout/autoinc.d= at',null,null,null); =20 =20 +create table dest_always(i int generated always as identity); +create table dest_by_default(i int generated by default as identity); =20 +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_always','extinout/autoinc.d= at',null,null,null,0); +select * from dest_always; +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoi= nc.dat',null,null,null,0); +select * from dest_by_default; =20 +drop table dest_always; +drop table dest_by_default; =20 + +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.d= at',null,null,null,1); +select * from dest_always; +CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE('APP','dest_by_default','extinout/autoi= nc.dat',null,null,null,1); +select * from dest_by_default; + +drop table dest_always; +drop table dest_by_default; =20