You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/05/24 01:08:02 UTC
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/
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;