db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From banda...@apache.org
Subject svn commit: r370885 - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/impl/sql/catalog/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ testing/org/apache/derbyTesti...
Date Fri, 20 Jan 2006 19:15:08 GMT
Author: bandaram
Date: Fri Jan 20 11:14:54 2006
New Revision: 370885

URL: http://svn.apache.org/viewcvs?rev=370885&view=rev
Log:
DERBY-783: Enhance ALTER TABLE to support RESTART option to enable setting of identity column
start value.

Submitted by Mamta Satoor (msatoor@gmail.com)

Here is more info from the contributor:

I have attached a review package for this feature to the JIRA entry. Following is a brief
description of the changes involved.

Changed sqlgrammar.jj to add parser support for ALTER TABLE <tablename> ALTER <columnName>
RESTART WITH integer-constant
Also, added another element to the array which keeps track of autoincrement information in
the parser. This 4th element will record if the autoincrement column is getting added or it
is getting altered for INCREMENT BY value change or it is getting altered for RESTART WITH
value change. This information is required later in the compile and execute phase.

In the compile phase, this information is used to see if a user is trying to sneak in a value
of 0 for INCREMENT BY. A value of 0 for INCREMENT BY should be caught at the time of autoincrement
column add or at the time of autoincrement column alter to change the INCREMENT BY value.
At the time of autoincrement column alter to change the RESTART WITH value,  the INCREMENT
BY value should not be checked. This is done in ColumnDefinitionNode.java. TableElementList
generates ColumnInfo which needs to keep track of autoincrement column change status from
ColumnDefinitionNode. This infromation in ColumnInfo will be used at execute time.

In the execute phase, we need to know which columns of SYSCOLUMNS table need to be changed
for an ALTER TABLE command on the autoincrement column. In the past, we only allowed to change
the INCREMENT BY criteria of an autoincrement column but with this feature, it is possible
for a user to change the start with value of autoincrement column and leave the INCREMENT
BY unchanged. This autoincrement column change information is passed to the execute phase
via ColumnInfo.

In order to provide this distinction between ALTER BY..INCREMENT BY.. and ALTER BY..RESTART
WITH.., I have had to add a variable in ColumnDefinitionNode.java, ColumnInfo.java and ColumnDescriptor.java.
The value of the variable in each of these classes depend on what parser recorded for the
autoincrement column status ie adding an autoincrement column/changing INCREMENT BY of the
autoincrement column/changing RESTART WITH of the autoincrement column.

Hope this information along with the comments in the code will help in the code review. Please
let me know if you have any comments.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ColumnDescriptor.java
Fri Jan 20 11:14:54 2006
@@ -30,6 +30,8 @@
 import org.apache.derby.catalog.DefaultInfo;
 import org.apache.derby.catalog.UUID;
 
+import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
+
 /**
  * This class represents a column descriptor.
  *
@@ -67,9 +69,18 @@
 	UUID				defaultUUID;
 	long				autoincStart;
 	long				autoincInc;
+	//Following variable is used to see if the user is adding an autoincrement 
+	//column, or if user is altering the existing autoincrement column to change 
+	//the increment value or to change the start value. If none of the above,
+	//then it will be set to -1
+	long				autoinc_create_or_modify_Start_Increment = -1; 
 
 	/**
-	 * Constructor for a ColumnDescriptor
+	 * Constructor for a ColumnDescriptor when the column involved
+	 * is an autoincrement column. The last parameter to this method
+	 * indicates if an autoincrement column is getting added or if
+	 * the autoincrement column is being modified to change the
+	 * increment value or to change the start value
 	 *
 	 * @param columnName		The name of the column
 	 * @param columnPosition	The ordinal position of the column
@@ -85,14 +96,49 @@
 	 * @param autoincStart	Start value for an autoincrement column.
 	 * @param autoincInc	Increment for autoincrement column
 	 * @param autoinc		boolean value for sanity checking.
+	 * @param userChangedWhat		Adding an autoincrement column OR
+	 *						changing increment value or start value of
+	 *						the autoincrement column.
 	 */
 
 	public ColumnDescriptor(String columnName, int columnPosition,
 					 DataTypeDescriptor columnType, DataValueDescriptor columnDefault,
 					 DefaultInfo columnDefaultInfo,
 					 TableDescriptor table,
-					 UUID defaultUUID, long autoincStart, long autoincInc, boolean autoinc)
+					 UUID defaultUUID, long autoincStart, long autoincInc, boolean autoinc,
+					 long userChangedWhat)
 	{
+		this(columnName, columnPosition, columnType, columnDefault,
+				columnDefaultInfo, table, defaultUUID, autoincStart,
+				autoincInc, autoinc);				
+		autoinc_create_or_modify_Start_Increment = userChangedWhat;
+	}
+
+		/**
+		 * Constructor for a ColumnDescriptor
+		 *
+		 * @param columnName		The name of the column
+		 * @param columnPosition	The ordinal position of the column
+		 * @param columnType		A DataTypeDescriptor for the type of
+		 *				the column
+		 * @param columnDefault		A DataValueDescriptor representing the
+		 *							default value of the column, if any
+		 *							(null if no default)
+		 * @param columnDefaultInfo		The default info for the column.
+		 * @param table			A TableDescriptor for the table the
+		 *						column is in
+		 * @param defaultUUID			The UUID for the default, if any.
+		 * @param autoincStart	Start value for an autoincrement column.
+		 * @param autoincInc	Increment for autoincrement column
+		 * @param autoinc		boolean value for sanity checking.
+		 */
+
+		public ColumnDescriptor(String columnName, int columnPosition,
+						 DataTypeDescriptor columnType, DataValueDescriptor columnDefault,
+						 DefaultInfo columnDefaultInfo,
+						 TableDescriptor table,
+						 UUID defaultUUID, long autoincStart, long autoincInc, boolean autoinc)
+		{
 		this.columnName = columnName;
 		this.columnPosition = columnPosition;
 		this.columnType = columnType;
@@ -335,6 +381,11 @@
 	public long getAutoincInc()
 	{
 		return autoincInc;
+	}
+
+	public long getAutoinc_create_or_modify_Start_Increment()
+	{
+		return autoinc_create_or_modify_Start_Increment;
 	}
 
 	/**

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/catalog/SYSCOLUMNSRowFactory.java
Fri Jan 20 11:14:54 2006
@@ -64,6 +64,8 @@
 import org.apache.derby.iapi.services.uuid.UUIDFactory;
 import org.apache.derby.catalog.UUID;
 
+import org.apache.derby.impl.sql.compile.ColumnDefinitionNode;
+
 import org.apache.derby.catalog.types.DefaultInfoImpl;
 
 import org.apache.derby.iapi.types.*;
@@ -199,6 +201,13 @@
 		Object					defaultSerializable = null;
 		long					autoincStart = 0;
 		long					autoincInc = 0;
+		//The SYSCOLUMNS table's autoinc related columns change with different
+		//values depending on what happened to the autoinc column, ie is the 
+		//user adding an autoincrement column, or is user changing the existing 
+		//autoincrement column to change it's increment value or to change it's
+		//start value? Following variable is used to keep track of what happened 
+		//to the autoincrement column.
+		long autoinc_create_or_modify_Start_Increment = -1;
 
 		if (td != null)
 		{
@@ -212,6 +221,7 @@
 			colID = new Integer(column.getPosition() );
 			autoincStart = column.getAutoincStart();
 			autoincInc   = column.getAutoincInc();
+			autoinc_create_or_modify_Start_Increment = column.getAutoinc_create_or_modify_Start_Increment();
 			if (column.getDefaultInfo() != null)
 			{
 				defaultSerializable = column.getDefaultInfo();
@@ -256,14 +266,25 @@
 		/* 6th column is DEFAULTID (UUID - char(36)) */
 		row.setColumn(SYSCOLUMNS_COLUMNDEFAULTID, dvf.getCharDataValue(defaultID));
 
-		if (autoincInc != 0)
-		{
+		if (autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.CREATE_AUTOINCREMENT
||
+				autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE)
+		{//user is adding an autoinc column or is changing the increment value of autoinc column
 			row.setColumn(SYSCOLUMNS_AUTOINCREMENTVALUE, 
 						  new SQLLongint(autoincStart));
 			row.setColumn(SYSCOLUMNS_AUTOINCREMENTSTART, 
 						  new SQLLongint(autoincStart));
 			row.setColumn(SYSCOLUMNS_AUTOINCREMENTINC, 
 						  new SQLLongint(autoincInc));
+		} else if (autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE)
+		{//user asked for restart with a new value, so don't change increment by and original start
+			//with values in the SYSCOLUMNS table. Just record the RESTART WITH value as the
+			//next value to be generated in the SYSCOLUMNS table
+			ColumnDescriptor  column = (ColumnDescriptor)td;
+			row.setColumn(SYSCOLUMNS_AUTOINCREMENTVALUE, new SQLLongint(autoincStart));
+			row.setColumn(SYSCOLUMNS_AUTOINCREMENTSTART, new SQLLongint(
+					column.getTableDescriptor().getColumnDescriptor(colName).getAutoincStart()));
+			row.setColumn(SYSCOLUMNS_AUTOINCREMENTINC, new SQLLongint(
+					column.getTableDescriptor().getColumnDescriptor(colName).getAutoincInc()));
 		}
 		else
 		{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
Fri Jan 20 11:14:54 2006
@@ -72,8 +72,27 @@
 	DefaultNode					defaultNode;
 	long						autoincrementIncrement;
 	long						autoincrementStart;
+	//This variable tells if the autoincrement column is participating 
+	//in create or alter table. And if it is participating in alter
+	//table, then it further knows if it is represting a change in 
+	//increment value or a change in start value.
+	//This information is later used to make sure that the autoincrement
+	//column's increment value is not 0 at the time of create, or is not
+	//getting set to 0 at the time of increment value modification.
+	long						autoinc_create_or_modify_Start_Increment;
 	boolean						autoincrementVerify;
 
+	//autoinc_create_or_modify_Start_Increment will be set to one of the
+	//following 3 values.
+	//CREATE_AUTOINCREMENT - this autoincrement column definition is for create table
+	public static final int CREATE_AUTOINCREMENT = 0;
+	//MODIFY_AUTOINCREMENT_RESTART_VALUE - this column definition is for
+	//alter table command to change the start value of the column
+	public static final int MODIFY_AUTOINCREMENT_RESTART_VALUE = 1;
+	//MODIFY_AUTOINCREMENT_INC_VALUE - this column definition is for
+	//alter table command to change the increment value of the column
+	public static final int MODIFY_AUTOINCREMENT_INC_VALUE = 2;
+	
 	/**
 	 * Initializer for a ColumnDefinitionNode
 	 *
@@ -122,7 +141,13 @@
 				long[] aii = (long[]) autoIncrementInfo;
 				autoincrementStart = aii[QueryTreeNode.AUTOINCREMENT_START_INDEX];
 				autoincrementIncrement = aii[QueryTreeNode.AUTOINCREMENT_INC_INDEX];
-
+				//Parser has passed the info about autoincrement column's status in the
+				//following array element. It will tell if the autoinc column is part of 
+				//a create table or if is a part of alter table. And if it is part of 
+				//alter table, is it for changing the increment value or for changing 
+				//the start value?
+				autoinc_create_or_modify_Start_Increment = aii[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY];
+				
 				/*
 				 * If using DB2 syntax to set increment value, will need to check if column
 				 * is already created for autoincrement.
@@ -224,7 +249,12 @@
 	{
 		if (SanityManager.DEBUG)
 		{
-			if (isAutoincrement && autoincrementIncrement == 0)
+			//increment value for autoincrement column can't be 0 if the autoinc column
+			//is part of create table or it is part of alter table to change the 
+			//increment value. 
+			if (isAutoincrement && autoincrementIncrement == 0 && 
+					(autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.CREATE_AUTOINCREMENT
||
+							autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE))
 			{
 				SanityManager.THROWASSERT(
 					"autoincrementIncrement expected to be non-zero");
@@ -270,6 +300,26 @@
 	}
 
 	/**
+	 * Get the status of this autoincrement column 
+	 *
+	 * @return ColumnDefinitionNode.CREATE_AUTOINCREMENT - 
+	 * 		if this definition is for autoincrement column creatoin
+	 *   ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE -
+	 * 		if this definition is for alter sutoincrement column to change the start value 
+	 *   ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE 
+	 * 		if this definition is for alter autoincrement column to change the increment value
+	 */
+	long getAutoinc_create_or_modify_Start_Increment()
+	{
+		if (SanityManager.DEBUG)
+		{
+			SanityManager.ASSERT(isAutoincrement,
+				"isAutoincrement expected to be true");
+		}
+		return autoinc_create_or_modify_Start_Increment;
+	}
+	
+	/**
 	 * Check the validity of a user type.  Checks whether this column
 	 * definition describes a user type that either doesn't exist or is
 	 * inaccessible, or that doesn't implement Serializable.
@@ -406,7 +456,12 @@
 		if (tableType == TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE)
 			throw StandardException.newException(SQLState.LANG_NOT_ALLOWED_FOR_DECLARED_GLOBAL_TEMP_TABLE);
 
-		if (autoincrementIncrement == 0)
+		//increment value for autoincrement column can't be 0 if the autoinc column
+		//is part of create table or it is part of alter table to change the 
+		//increment value. 
+		if (autoincrementIncrement == 0 && 
+				(autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.CREATE_AUTOINCREMENT
||
+						autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE))
 			throw StandardException.newException(SQLState.LANG_AI_INVALID_INCREMENT, getColumnName());
 		int jdbctype = dataTypeServices.getTypeId().getJDBCTypeId();
 		switch (jdbctype)

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSchemaNode.java
Fri Jan 20 11:14:54 2006
@@ -38,7 +38,6 @@
 
 import org.apache.derby.impl.sql.compile.ActivationClassBuilder;
 import org.apache.derby.impl.sql.execute.BaseActivation;
-import org.apache.derby.impl.sql.execute.ColumnInfo;
 
 import java.util.Properties;
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateViewNode.java
Fri Jan 20 11:14:54 2006
@@ -358,7 +358,7 @@
 											 null,
 											 null,
 											 ColumnInfo.CREATE,
-											 0, 0);
+											 0, 0, 0);
 		}
 	}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
Fri Jan 20 11:14:54 2006
@@ -227,7 +227,10 @@
 		switch (getNodeType())
 		{
 		case C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE:
-			return ColumnInfo.MODIFY_COLUMN_DEFAULT;
+			if (autoinc_create_or_modify_Start_Increment == ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE)
+				return ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART;
+			else
+				return ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT;
 		case C_NodeTypes.MODIFY_COLUMN_TYPE_NODE:
 			return ColumnInfo.MODIFY_COLUMN_TYPE;
 		case C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE:

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/QueryTreeNode.java Fri
Jan 20 11:14:54 2006
@@ -81,6 +81,9 @@
 	public static final int AUTOINCREMENT_START_INDEX = 0;
 	public static final int AUTOINCREMENT_INC_INDEX   = 1;
 	public static final int AUTOINCREMENT_IS_AUTOINCREMENT_INDEX   = 2;
+	//Parser uses this static field to make a note if the autoincrement column 
+	//is participating in create or alter table.
+	public static final int AUTOINCREMENT_CREATE_MODIFY  = 3;
 
 	private int		beginOffset = -1;		// offset into SQL input of the substring
 	                                // which this query node encodes.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
Fri Jan 20 11:14:54 2006
@@ -377,7 +377,7 @@
 				colInfos[index] = new ColumnInfo(
 								((TableElementNode) elementAt(index)).getName(),
 								null, null, null, null, null,
-								ColumnInfo.DROP, 0, 0);
+								ColumnInfo.DROP, 0, 0, 0);
 				break;
 			}
 
@@ -408,7 +408,9 @@
 							   (coldef.isAutoincrementColumn() ? 
 								coldef.getAutoincrementStart() : 0),
 							   (coldef.isAutoincrementColumn() ? 
-								coldef.getAutoincrementIncrement() : 0));
+								coldef.getAutoincrementIncrement() : 0),
+							   (coldef.isAutoincrementColumn() ? 
+								coldef.getAutoinc_create_or_modify_Start_Increment() : -1));
 
 			/* Remember how many constraints that we've seen */
 			if (coldef.hasConstraint())

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Fri Jan
20 11:14:54 2006
@@ -2180,6 +2180,7 @@
 |	<PRECISION: "precision">
 |	<RELEASE: "release">
 |	<REPEATABLE: "repeatable">
+|	<RESTART: "restart">
 |	<RETURNS: "returns">
 |	<ROW: "row">
 |	<SAVEPOINT: "savepoint">
@@ -3608,7 +3609,7 @@
 	DataTypeDescriptor	typeDescriptor = null;
 	ValueNode			defaultNode = null;
 	String				columnName;
-	long[]				autoIncrementInfo = new long[3];
+	long[]				autoIncrementInfo = new long[4];
 }
 {
 	/*
@@ -9802,6 +9803,7 @@
 	autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = 1;
 	autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = 1;
 	autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_IS_AUTOINCREMENT_INDEX] = 1;
+	autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.CREATE_AUTOINCREMENT;
     }
 
 	<GENERATED> 
@@ -9835,6 +9837,7 @@
         <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
     {
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.CREATE_AUTOINCREMENT;
 		return;
     }
 |
@@ -9842,6 +9845,7 @@
     {
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementInitial;
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.CREATE_AUTOINCREMENT;
 		return;
     }
 }
@@ -11350,7 +11354,7 @@
 	DataTypeDescriptor	typeDescriptor;
 	Token				tok = null;
 	String				columnName;
-	long[]				autoIncrementInfo = new long[3];
+	long[]				autoIncrementInfo = new long[4];
 }
 {
 	<ADD>
@@ -11372,7 +11376,7 @@
 		return lockGranularity;
 	}
 |
-	<ALTER> [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
<SET>
+	<ALTER> [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)

 		typeDescriptor = DB2AlterColumn(autoIncrementInfo)
 	{
 		changeType[0] = DDLStatementNode.MODIFY_TYPE;
@@ -11443,7 +11447,7 @@
 {
 	ValueNode	defaultNode;
 	DataTypeDescriptor typeDescriptor;
-	long[]				autoIncrementInfo = new long[3];
+	long[]				autoIncrementInfo = new long[4];
 }
 {
 	// MODIFY column_name [WITH] DEFAULT <Value>
@@ -11511,17 +11515,28 @@
 DB2AlterColumn(long[] autoIncrementInfo) throws StandardException :
 {
 	long				autoIncrementIncrement = 1;
+	long				autoIncrementRestartWith = 1;
 	DataTypeDescriptor	typeDescriptor = null;
 }
 {
-	<DATA> <TYPE> typeDescriptor = dataTypeDDL()
+	LOOKAHEAD( {getToken(2).kind == DATA} )
+	<SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
 	{
 		return typeDescriptor;
 	}
 |
-	<INCREMENT> <BY> autoIncrementIncrement = exactNumber()
+	LOOKAHEAD( {getToken(2).kind == INCREMENT} )
+	<SET> <INCREMENT> <BY> autoIncrementIncrement = exactNumber()
 	{
 		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_INC_INDEX] = autoIncrementIncrement;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE;
+		return typeDescriptor;
+	}
+|
+	<RESTART> <WITH> autoIncrementRestartWith = exactNumber()
+	{
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
 		return typeDescriptor;
 	}
 }

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
Fri Jan 20 11:14:54 2006
@@ -398,7 +398,8 @@
 					addNewColumnToTable(activation, ix);
 				}
 				else if (columnInfo[ix].action == 
-						 ColumnInfo.MODIFY_COLUMN_DEFAULT)
+						 ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART ||
+						 columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT)
 				{
 					modifyColumnDefault(activation, ix);
 				}
@@ -1039,7 +1040,8 @@
 												   defaultUUID,
 												   columnInfo[ix].autoincStart,
 												   columnInfo[ix].autoincInc,
-												   columnInfo[ix].autoincInc != 0
+												   columnInfo[ix].autoincInc != 0,
+												   columnInfo[ix].autoinc_create_or_modify_Start_Increment
 												   );
 
 		// Update the ColumnDescriptor with new default info
@@ -1047,7 +1049,7 @@
 		dd.addDescriptor(columnDescriptor, td,
 						 DataDictionary.SYSCOLUMNS_CATALOG_NUM, false, tc);
 	
-		if (columnInfo[ix].autoincInc != 0)
+		if (columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_INCREMENT)
 		{
 			// adding an autoincrement default-- calculate the maximum value 
 			// of the autoincrement column.
@@ -1056,7 +1058,11 @@
 										 columnInfo[ix].autoincStart);
 			dd.setAutoincrementValue(tc, td.getUUID(), columnInfo[ix].name,
 									 maxValue, true);
-		}
+		} else if (columnInfo[ix].action == ColumnInfo.MODIFY_COLUMN_DEFAULT_RESTART)
+		{
+			dd.setAutoincrementValue(tc, td.getUUID(), columnInfo[ix].name,
+					 columnInfo[ix].autoincStart, false);
+		} 
 	}
 
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/ColumnInfo.java Fri
Jan 20 11:14:54 2006
@@ -76,13 +76,21 @@
 	// autoinc columns.
 	public long 						autoincStart;
 	public long 						autoincInc;
+	//if this is an autoincrement column, then following variable will have CREATE or
+	//MODIFY_COLUMN_DEFAULT_RESTART or MODIFY_COLUMN_DEFAULT_INCREMENT. Otherwise,
+	//this variable will be set to -1.
+	public long 						autoinc_create_or_modify_Start_Increment = -1;
 
+	//This indicates column is for CREATE TABLE
 	public static final int CREATE					= 0;
-	public static final int MODIFY_COLUMN_DEFAULT	= 1;
-	public static final int DROP					= 2;
-	public static final int MODIFY_COLUMN_TYPE      = 3;
-	public static final int MODIFY_COLUMN_CONSTRAINT = 4;
-	public static final int MODIFY_COLUMN_CONSTRAINT_NOT_NULL = 5;
+	public static final int DROP					= 1;
+	public static final int MODIFY_COLUMN_TYPE      = 2;
+	public static final int MODIFY_COLUMN_CONSTRAINT = 3;
+	public static final int MODIFY_COLUMN_CONSTRAINT_NOT_NULL = 4;
+	//This indicates column is for ALTER TABLE to change the start value of autoinc column 
+	public static final int MODIFY_COLUMN_DEFAULT_RESTART	= 5;
+	//This indicates column is for ALTER TABLE to change the increment value of autoinc column

+	public static final int MODIFY_COLUMN_DEFAULT_INCREMENT	= 6;
 	// CONSTRUCTORS
 
 	/**
@@ -115,7 +123,8 @@
 					   UUID							oldDefaultUUID,
 					   int							action,
 					   long							autoincStart,
-					   long							autoincInc)
+					   long							autoincInc,
+					   long							autoinc_create_or_modify_Start_Increment)
 	{
 		this.name = name;
 		this.dataType = dataType;
@@ -126,6 +135,7 @@
 		this.action = action;
 		this.autoincStart = autoincStart;
 		this.autoincInc = autoincInc;
+		this.autoinc_create_or_modify_Start_Increment = autoinc_create_or_modify_Start_Increment;
 	}
 
 	// Formatable methods

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateTableConstantAction.java
Fri Jan 20 11:14:54 2006
@@ -266,7 +266,8 @@
 								   defaultUUID,
 								   columnInfo[ix].autoincStart,
 								   columnInfo[ix].autoincInc,
-								   columnInfo[ix].autoincInc != 0
+								   columnInfo[ix].autoincInc != 0,
+								   columnInfo[ix].autoinc_create_or_modify_Start_Increment
 							   );
 
 			cdlArray[ix] = columnDescriptor;

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
Fri Jan 20 11:14:54 2006
@@ -1709,4 +1709,116 @@
 0 rows inserted/updated/deleted
 ij(CONN2)> drop table t2;
 0 rows inserted/updated/deleted
+ij(CONN2)> -- Test RESTART WITH syntax of ALTER TABLE for autoincrment columns
+create table t1(c11 int generated by default as identity(start with 2, increment by 2), c12
int);
+0 rows inserted/updated/deleted
+ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC

+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |2                   |2                   |2      
            
+ij(CONN2)> insert into t1 values(2,2);
+1 row inserted/updated/deleted
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+2          |2          
+ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC

+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |2                   |2                   |2      
            
+ij(CONN2)> insert into t1(c12) values(9999);
+1 row inserted/updated/deleted
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+2          |2          
+2          |9999       
+ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC

+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |4                   |2                   |2      
            
+ij(CONN2)> -- try RESTART WITH on a non-autoincrement column. It should fail
+alter table t1 alter column c12 RESTART WITH 2;
+ERROR 42837: ALTER TABLE 'APP.T1' specified attributes for column 'C12' that are not compatible
with the existing column.
+ij(CONN2)> -- try RESTART WITH with a non-integer column
+alter table t1 alter column c11 RESTART WITH 2.20;
+ERROR 42X49: Value '2.20' is not a valid integer literal.
+ij(CONN2)> alter table t1 alter column c11 RESTART WITH 2;
+0 rows inserted/updated/deleted
+ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC

+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |2                   |2                   |2      
            
+ij(CONN2)> autocommit off;
+ij(CONN2)> drop table t1;
+0 rows inserted/updated/deleted
+ij(CONN2)> create table t1(c11 int generated by default as identity (start with 1, increment
by 1), c12 int);
+0 rows inserted/updated/deleted
+ij(CONN2)> --following puts locks on system table SYSCOLUMNS's row for t1.c11
+--Later when a user tries to have the system generate a value for the
+--t1.c11, system can't generate that value in a transaction of it's own
+--and hence it reverts to the user transaction to generate the next value.
+--This use of user transaction to generate a value can be problematic if
+--user statement to generate the next value runs into statement rollback.
+--This statement rollback will cause the next value generation to rollback
+--too and system will not be able to consume the generated value. 
+--In a case like this, user can use ALTER TABLE....RESTART WITH to change the
+--start value of the autoincrement column as shown below.
+create unique index t1i1 on t1(c11);
+0 rows inserted/updated/deleted
+ij(CONN2)> insert into t1 values(1,1);
+1 row inserted/updated/deleted
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+1          |1          
+ij(CONN2)> -- you will notice that the next value for generated column is 1 at this point
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |1                   |1                   |1      
            
+ij(CONN2)> insert into t1(c12) 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 'T1I1' defined on 'T1'.
+ij(CONN2)> -- the insert above fails as expected because there is already a *1* in the
table. 
+--But the generated value doesn't get consumed and following select will still show 
+--next value for generated column as 1. If autocommit was set to on, you would see
+-- the next generated value at this point to be 2.
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |1                   |1                   |1      
            
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+1          |1          
+ij(CONN2)> --the following insert will keep failing because it is going to use 1 as the
generated
+--value for c11 again and that will cause unique key violation
+insert into t1(c12) 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 'T1I1' defined on 'T1'.
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+1          |1          
+ij(CONN2)> --User can change the RESTART WITH for autoincrement column to say 2 at this
point,
+--and then the insert above will not fail
+alter table t1 alter column c11 restart with 2;
+0 rows inserted/updated/deleted
+ij(CONN2)> select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC

+from sys.syscolumns where COLUMNNAME = 'C11';
+COLUMNNAME                                                                              
                                       |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART  |AUTOINCREMENTINC
   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+C11                                                                                     
                                       |2                   |1                   |1      
            
+ij(CONN2)> insert into t1(c12) values(3);
+1 row inserted/updated/deleted
+ij(CONN2)> select * from t1;
+C11        |C12        
+-----------------------
+1          |1          
+2          |3          
 ij(CONN2)> 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?rev=370885&r1=370884&r2=370885&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
Fri Jan 20 11:14:54 2006
@@ -878,4 +878,64 @@
 drop table t1;
 drop table t2;
 
+-- Test RESTART WITH syntax of ALTER TABLE for autoincrment columns
+create table t1(c11 int generated by default as identity(start with 2, increment by 2), c12
int);
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
 
+insert into t1 values(2,2);
+select * from t1;
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+
+insert into t1(c12) values(9999);
+select * from t1;
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+
+-- try RESTART WITH on a non-autoincrement column. It should fail
+alter table t1 alter column c12 RESTART WITH 2;
+-- try RESTART WITH with a non-integer column
+alter table t1 alter column c11 RESTART WITH 2.20;
+alter table t1 alter column c11 RESTART WITH 2;
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+
+autocommit off;
+drop table t1;
+create table t1(c11 int generated by default as identity (start with 1, increment by 1),
c12 int);
+--following puts locks on system table SYSCOLUMNS's row for t1.c11
+--Later when a user tries to have the system generate a value for the
+--t1.c11, system can't generate that value in a transaction of it's own
+--and hence it reverts to the user transaction to generate the next value.
+--This use of user transaction to generate a value can be problematic if
+--user statement to generate the next value runs into statement rollback.
+--This statement rollback will cause the next value generation to rollback
+--too and system will not be able to consume the generated value. 
+--In a case like this, user can use ALTER TABLE....RESTART WITH to change the
+--start value of the autoincrement column as shown below.
+create unique index t1i1 on t1(c11); 
+insert into t1 values(1,1);
+select * from t1;
+-- you will notice that the next value for generated column is 1 at this point
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+insert into t1(c12) values(3);
+-- the insert above fails as expected because there is already a *1* in the table. 
+--But the generated value doesn't get consumed and following select will still show 
+--next value for generated column as 1. If autocommit was set to on, you would see
+-- the next generated value at this point to be 2.
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+select * from t1;
+--the following insert will keep failing because it is going to use 1 as the generated
+--value for c11 again and that will cause unique key violation
+insert into t1(c12) values(3);
+select * from t1;
+--User can change the RESTART WITH for autoincrement column to say 2 at this point,
+--and then the insert above will not fail
+alter table t1 alter column c11 restart with 2;
+select COLUMNNAME, AUTOINCREMENTVALUE, AUTOINCREMENTSTART, AUTOINCREMENTINC 
+from sys.syscolumns where COLUMNNAME = 'C11';
+insert into t1(c12) values(3);
+select * from t1;



Mime
View raw message