db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r475081 - in /db/derby/code/branches/10.2/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Wed, 15 Nov 2006 00:14:16 GMT
Author: bpendleton
Date: Tue Nov 14 16:14:16 2006
New Revision: 475081

URL: http://svn.apache.org/viewvc?view=rev&rev=475081
Log:
DERBY-1495: Error modifying an identity column after altering the column
DERBY-1645: ALTER TABLE SET INCREMENT turns off "Generated By Default"

Merged the fix from the trunk by svn merge -r 474501:474502 ../trunk/


Modified:
    db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
Tue Nov 14 16:14:16 2006
@@ -43,6 +43,7 @@
 import org.apache.derby.impl.sql.execute.ColumnInfo;
 import org.apache.derby.catalog.TypeDescriptor;
 import org.apache.derby.catalog.UUID;
+import org.apache.derby.catalog.types.DefaultInfoImpl;
 
 /**
  * A ModifyColumnNode represents a modify column in an ALTER TABLE statement.
@@ -285,6 +286,23 @@
 		{
 			return;
 		}
+
+		// If the statement is not setting the column's default, then
+		// recover the old default and re-use it. If the statement is
+		// changing the start value for the auto-increment, then recover
+		// the old increment-by value and re-use it. If the statement is
+		// changing the increment-by value, then recover the old start value
+		// and re-use it. This way, the column alteration only changes the
+		// aspects of the autoincrement settings that it intends to change,
+		// and does not lose the other aspecs.
+		if (defaultNode == null)
+			defaultInfo = (DefaultInfoImpl)cd.getDefaultInfo();
+		if (autoinc_create_or_modify_Start_Increment ==
+				ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE)
+			autoincrementIncrement = cd.getAutoincInc();
+		if (autoinc_create_or_modify_Start_Increment ==
+				ColumnDefinitionNode.MODIFY_AUTOINCREMENT_INC_VALUE)
+			autoincrementStart = cd.getAutoincStart();
 
 		/* Fill in the DataTypeServices from the DataDictionary */
 		dataTypeServices = cd.getType();

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/autoincrement.out
Tue Nov 14 16:14:16 2006
@@ -1855,4 +1855,78 @@
 ij(CONN2)> select * from newTable2;
 C11        |RESTART    
 -----------------------
+ij(CONN2)> -- Verify that if we change the START WITH value for a GENERATED_BY_DEFAULT
+-- column, the column is still GENERATED_BY_DEFAULT and its INCREMENT BY
+-- value is preserved
+CREATE TABLE DERBY_1495 (
+  id INT GENERATED BY DEFAULT AS IDENTITY
+		(START WITH 1, INCREMENT BY 1) NOT NULL
+ ,col2 INT NOT NULL);
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+COLUMNDEFAULT  |COLUMNDEFAULTID                     |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART
 |AUTOINCREMENTINC    
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1                   |1                   |1   
               
+ij(CONN2)> -- Insert using an explicit value on the ID-field
+INSERT INTO DERBY_1495(ID, COL2) VALUES(2, 2);
+1 row inserted/updated/deleted
+ij(CONN2)> -- Reset the identity field
+ALTER TABLE DERBY_1495 ALTER COLUMN id RESTART WITH 3;
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+COLUMNDEFAULT  |COLUMNDEFAULTID                     |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART
 |AUTOINCREMENTINC    
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|3                   |3                   |1   
               
+ij(CONN2)> INSERT INTO DERBY_1495(ID, COL2) VALUES(4, 4);
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO DERBY_1495(COL2) VALUES(4);
+1 row inserted/updated/deleted
+ij(CONN2)> -- Similarly, verify that if we change the INCREMENT BY value for a
+-- GENERATED_BY_DEFAULT column, the column remains GENERATED_BY_DEFAULT
+-- and its START WITH value is preserved.
+create table derby_1645 (
+   TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+   StringValue VARCHAR(20) not null,
+   constraint PK_derby_1645 primary key (TableId));
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+COLUMNDEFAULT  |COLUMNDEFAULTID                     |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART
 |AUTOINCREMENTINC    
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1                   |1                   |1   
               
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (1, 'test1');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (2, 'test2');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (3, 'test3');
+1 row inserted/updated/deleted
+ij(CONN2)> ALTER TABLE derby_1645 ALTER TableId SET INCREMENT BY 50;
+0 rows inserted/updated/deleted
+ij(CONN2)> SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+COLUMNDEFAULT  |COLUMNDEFAULTID                     |AUTOINCREMENTVALUE  |AUTOINCREMENTSTART
 |AUTOINCREMENTINC    
+-------------------------------------------------------------------------------------------------------------------
+GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|53                  |1                   |50  
               
+ij(CONN2)> INSERT INTO derby_1645 (StringValue) VALUES ('test53');
+1 row inserted/updated/deleted
+ij(CONN2)> INSERT INTO derby_1645 (TableId, StringValue) VALUES (-999, 'test3');
+1 row inserted/updated/deleted
 ij(CONN2)> 

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql?view=diff&rev=475081&r1=475080&r2=475081
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/autoincrement.sql
Tue Nov 14 16:14:16 2006
@@ -964,3 +964,66 @@
 create table newTable2 (c11 int);
 alter table newTable2 add column RESTART int;
 select * from newTable2;
+
+-- Verify that if we change the START WITH value for a GENERATED_BY_DEFAULT
+-- column, the column is still GENERATED_BY_DEFAULT and its INCREMENT BY
+-- value is preserved
+CREATE TABLE DERBY_1495 (
+  id INT GENERATED BY DEFAULT AS IDENTITY
+		(START WITH 1, INCREMENT BY 1) NOT NULL
+ ,col2 INT NOT NULL);
+
+SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+
+-- Insert using an explicit value on the ID-field
+INSERT INTO DERBY_1495(ID, COL2) VALUES(2, 2);
+
+-- Reset the identity field
+ALTER TABLE DERBY_1495 ALTER COLUMN id RESTART WITH 3;
+
+SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1495' AND ColumnName = 'ID';
+
+INSERT INTO DERBY_1495(ID, COL2) VALUES(4, 4);
+INSERT INTO DERBY_1495(COL2) VALUES(4);
+
+-- Similarly, verify that if we change the INCREMENT BY value for a
+-- GENERATED_BY_DEFAULT column, the column remains GENERATED_BY_DEFAULT
+-- and its START WITH value is preserved.
+create table derby_1645 (
+   TableId INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+   StringValue VARCHAR(20) not null,
+   constraint PK_derby_1645 primary key (TableId));
+
+SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (1, 'test1');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (2, 'test2');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (3, 'test3');
+
+ALTER TABLE derby_1645 ALTER TableId SET INCREMENT BY 50;
+
+SELECT		col.columndefault, col.columndefaultid,
+			col.autoincrementvalue, col.autoincrementstart,
+			col.autoincrementinc
+	FROM sys.syscolumns col
+		INNER JOIN sys.systables tab ON col.referenceId = tab.tableid
+	WHERE tab.tableName = 'DERBY_1645' AND ColumnName = 'TABLEID';
+
+INSERT INTO derby_1645 (StringValue) VALUES ('test53');
+INSERT INTO derby_1645 (TableId, StringValue) VALUES (-999, 'test3');
+



Mime
View raw message