[ http://issues.apache.org/jira/browse/DERBY-783?page=all ]
Mamta A. Satoor updated DERBY-783:
----------------------------------
Attachment: Derby783AlterTableRestartWith010306.txt
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.
The svn stat changes for this feature is as follows
M java\engine\org\apache\derby\impl\sql\compile\QueryTreeNode.java
M java\engine\org\apache\derby\impl\sql\compile\ColumnDefinitionNode.java
M java\engine\org\apache\derby\impl\sql\compile\CreateSchemaNode.java
M java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
M java\engine\org\apache\derby\impl\sql\compile\TableElementList.java
M java\engine\org\apache\derby\impl\sql\compile\CreateViewNode.java
M java\engine\org\apache\derby\impl\sql\compile\ModifyColumnNode.java
M java\engine\org\apache\derby\impl\sql\execute\ColumnInfo.java
M
java\engine\org\apache\derby\impl\sql\execute\CreateTableConstantAction.java
M
java\engine\org\apache\derby\impl\sql\execute\AlterTableConstantAction.java
M java\engine\org\apache\derby\impl\sql\catalog\SYSCOLUMNSRowFactory.java
M java\engine\org\apache\derby\iapi\sql\dictionary\ColumnDescriptor.java
M
java\testing\org\apache\derbyTesting\functionTests\tests\lang\autoincrement.sql
M
java\testing\org\apache\derbyTesting\functionTests\master\autoincrement.out
I have modified autoincrement.sql to add tests for RESTART WITH. The derbyall
suite ran fine on my Windows XP machine with
Sun's jdk142.
> Enhance ALTER TABLE syntax to allow users to change the next value to be
> generated for an identity column
> ---------------------------------------------------------------------------------------------------------
>
> Key: DERBY-783
> URL: http://issues.apache.org/jira/browse/DERBY-783
> Project: Derby
> Type: New Feature
> Components: SQL
> Versions: 10.2.0.0
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Attachments: Derby783AlterTableRestartWith010306.txt
>
> Derby allows a user to change the interval between consecutive values of the
> identity column using ALTER TABLE. But there is no way to change the next
> value to be generated for an identity column. Such a support in Derby will be
> very handy for tables with identity column defined as GENERATED BY DEFAULT
> and with a unique key defined on them. Column defined with GENERATED BY
> DEFAULT allows system to generate values for them or allows the user to
> manually supply the value for them. A column defined this way is very useful
> when the user might want to import some data into the generated column
> manually. But this can create problems when the system generated values
> conflict with manually inserted values.
> eg
> autocommit on;
> create table tauto(i int generated by default as identity, k int);
> create unique index tautoInd on tauto(i);
> insert into tauto(k) values 1,2; -- let system generate values for the
> identity column
> -- now do few manual inserts into identity column
> insert into tauto values (3,3);
> insert into tauto values (4,4);
> insert into tauto values (5,5);
> insert into tauto values (6,6);
> insert into tauto values (7,7);
> insert into tauto values (8,8);
> -- notice that identity column at this point has used 1 through 8
> -- now if the user wants to let the system generate a value, system will
> generate 3 but that is already used and hence
> -- insert will throw unique key failure exception. System has consumed 3 at
> this point.
> insert into tauto(k) values 9;
> -- the insert above will continue to fail with the unique key failure
> exceptions until system has consumed all the values till 8
> -- If we add ALTER TABLE syntax to allow changing the next value to be
> generated, then user can simply use that to change
> -- next value to be generated to 9 after the manual inserts above and then
> insert into tauto(k) values 9 will not fail
> SQL standard syntax for changing the next generated value
> ALTER TABLE <tablename> ALTER <columnName> RESTART WITH integer-constant
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
