db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bpendle...@apache.org
Subject svn commit: r440203 - in /db/derby/code/branches/10.2/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache...
Date Mon, 04 Sep 2006 23:06:47 GMT
Author: bpendleton
Date: Mon Sep  4 16:06:46 2006
New Revision: 440203

URL: http://svn.apache.org/viewvc?view=rev&rev=440203
Log:
DERBY-119: Add ALTER TABLE option to change column from NULL to NOT NULL

This change merges the DERBY-119 changes from the trunk via
svn merge -r 437214:437215 ../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/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/branches/10.2/java/engine/org/apache/derby/loc/messages_en.properties
    db/derby/code/branches/10.2/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
    db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.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=440203&r1=440202&r2=440203
==============================================================================
--- 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
Mon Sep  4 16:06:46 2006
@@ -193,15 +193,17 @@
 			
 			else
 			{
-				// a column that is part of a primary key is being made
-				// nullable; can't be done.
+				// a column that is part of a primary key or unique constraint
+                // is being made nullable; can't be done.
 				if ((getNodeType() == 
 					 C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE) &&
-					(existingConstraint.getConstraintType() == 
-					 DataDictionary.PRIMARYKEY_CONSTRAINT))
+					((existingConstraint.getConstraintType() == 
+					 DataDictionary.PRIMARYKEY_CONSTRAINT) ||
+					 (existingConstraint.getConstraintType() == 
+					 DataDictionary.UNIQUE_CONSTRAINT)))
 				{
 				throw StandardException.newException(
-					 SQLState.LANG_MODIFY_COLUMN_PKEY_CONSTRAINT, name);
+					 SQLState.LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT, name);
 				}
 				// unique key or primary key.
 				ConstraintDescriptorList 

Modified: db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&rev=440203&r1=440202&r2=440203
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
(original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Mon Sep  4 16:06:46 2006
@@ -11791,22 +11791,10 @@
 	}
 |
 	<ALTER> [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)

-		typeDescriptor = DB2AlterColumn(autoIncrementInfo)
+		tableElement = columnAlterClause(columnName)
 	{
 		changeType[0] = DDLStatementNode.MODIFY_TYPE;
-		/* typeDescriptor is not null for MODIFY_COLUMN_TYPE_NODE */
-		if (typeDescriptor != null)
-			tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
-						C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
-						columnName, null,
-						typeDescriptor, null,
-						getContextManager()));
-		else
-			tableElementList.addTableElement((TableElementNode) nodeFactory.getNode(
-						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
-						columnName,
-						null, null, autoIncrementInfo,
-						getContextManager()));
+		tableElementList.addTableElement(tableElement);
 		return lockGranularity;
 	}
 |
@@ -11842,29 +11830,61 @@
 	}
 }
 
-
-TableElementNode 
-columnAlter() throws StandardException :
-{
-	String columnName;
-	TableElementNode tn;
-}
-{
-	columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) tn = columnAlterClause(columnName)
-	{
-		return tn;
-	}
-}
-
+/*
+ * Various variants of the ALTER TABLE ALTER COLUMN statement.
+ *
+ * By the type we get here, we've parsed
+ *    ALTER TABLE tablename ALTER [COLUMN] columnname
+ * and here we parse the remainder of the ALTER COLUMN clause, one of:
+ *		SET DATA TYPE data_type
+ *		SET INCREMENT BY increment_value
+ *		RESTART WITH increment_restart_value
+ *		[WITH] DEFAULT default_value
+ *      [NOT] NULL
+ */
 TableElementNode
 columnAlterClause(String columnName) throws StandardException :
 {
 	ValueNode	defaultNode;
-	DataTypeDescriptor typeDescriptor;
 	long[]				autoIncrementInfo = new long[4];
+	long				autoIncrementIncrement = 1;
+	long				autoIncrementRestartWith = 1;
+	DataTypeDescriptor	typeDescriptor = null;
 }
 {
-	// MODIFY column_name [WITH] DEFAULT <Value>
+	LOOKAHEAD( {getToken(2).kind == DATA} )
+	<SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
+	{
+		return (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
+						columnName, null,
+						typeDescriptor, null,
+						getContextManager());
+	}
+|
+	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 (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+						columnName,
+						null, null, autoIncrementInfo,
+						getContextManager());
+	}
+|
+	<RESTART> <WITH> autoIncrementRestartWith = exactNumber()
+	{
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_START_INDEX] = autoIncrementRestartWith;
+		autoIncrementInfo[QueryTreeNode.AUTOINCREMENT_CREATE_MODIFY] = ColumnDefinitionNode.MODIFY_AUTOINCREMENT_RESTART_VALUE;
+		return (TableElementNode) nodeFactory.getNode(
+						C_NodeTypes.MODIFY_COLUMN_DEFAULT_NODE,
+						columnName,
+						null, null, autoIncrementInfo,
+						getContextManager());
+	}
+|
 	LOOKAHEAD( {getToken(1).kind == WITH || getToken(1).kind == _DEFAULT })
 	defaultNode = defaultClause(autoIncrementInfo, columnName)
 	{
@@ -11881,7 +11901,6 @@
 								getContextManager());
 	}
 |
-	// MODIFY column_name NULL
 	LOOKAHEAD ({getToken(1).kind == NULL })
 	<NULL>
 	{
@@ -11896,7 +11915,6 @@
 								getContextManager());
 	}
 |
-	// MODIFY column_name NOT NULL
 	LOOKAHEAD({getToken(1).kind == NOT})
 	<NOT> <NULL>
 	{
@@ -11909,49 +11927,6 @@
 								C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NOT_NULL_NODE,
 								columnName, null, null, null,
 								getContextManager());
-	}
-|
-	// MODIFY column_name varchar(64)
-	typeDescriptor = dataTypeDDL()
-	{
-		return (TableElementNode) nodeFactory.getNode(
-								C_NodeTypes.MODIFY_COLUMN_TYPE_NODE,
-								columnName,
-								null, typeDescriptor, null,
-								getContextManager());
-	}
-}
-
-/*
- * DB2 syntax for ALTER COLUMN
- */
-DataTypeDescriptor
-DB2AlterColumn(long[] autoIncrementInfo) throws StandardException :
-{
-	long				autoIncrementIncrement = 1;
-	long				autoIncrementRestartWith = 1;
-	DataTypeDescriptor	typeDescriptor = null;
-}
-{
-	LOOKAHEAD( {getToken(2).kind == DATA} )
-	<SET> <DATA> <TYPE> typeDescriptor = dataTypeDDL()
-	{
-		return typeDescriptor;
-	}
-|
-	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/branches/10.2/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/engine/org/apache/derby/loc/messages_en.properties?view=diff&rev=440203&r1=440202&r2=440203
==============================================================================
--- db/derby/code/branches/10.2/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ db/derby/code/branches/10.2/java/engine/org/apache/derby/loc/messages_en.properties Mon
Sep  4 16:06:46 2006
@@ -720,7 +720,7 @@
 42Z17=Invalid length specified for column ''{0}''. Length must be greater than the current
column length.
 42Z18=Column ''{0}'' is part of a foreign key constraint ''{1}''. To alter the length of
this column, you should drop the constraint first, perform the ALTER TABLE, and then recreate
the constraint.
 42Z19=Column ''{0}'' is being referenced by at least one foreign key constraint ''{1}''.
To alter the length of this column, you should drop referencing constraints, perform the ALTER
TABLE and then recreate the constraints. 
-42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key, which cannot have
any nullable columns.
+42Z20=Column ''{0}'' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
 
 #####
 # end of alter table modify constraints.

Modified: db/derby/code/branches/10.2/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/shared/org/apache/derby/shared/common/reference/SQLState.java?view=diff&rev=440203&r1=440202&r2=440203
==============================================================================
--- db/derby/code/branches/10.2/java/shared/org/apache/derby/shared/common/reference/SQLState.java
(original)
+++ db/derby/code/branches/10.2/java/shared/org/apache/derby/shared/common/reference/SQLState.java
Mon Sep  4 16:06:46 2006
@@ -939,7 +939,7 @@
 	String LANG_MODIFY_COLUMN_INVALID_LENGTH						   = "42Z17";
 	String LANG_MODIFY_COLUMN_FKEY_CONSTRAINT						   = "42Z18";
 	String LANG_MODIFY_COLUMN_REFERENCED							   = "42Z19";
-	String LANG_MODIFY_COLUMN_PKEY_CONSTRAINT 						   = "42Z20";
+	String LANG_MODIFY_COLUMN_EXISTING_CONSTRAINT					   = "42Z20";
 
 	String LANG_AI_INVALID_INCREMENT								   = "42Z21";
 	String LANG_AI_INVALID_TYPE										   = "42Z22";

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?view=diff&rev=440203&r1=440202&r2=440203
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
Mon Sep  4 16:06:46 2006
@@ -868,4 +868,63 @@
 0 rows inserted/updated/deleted
 ij> declare global temporary table session.logged(logged int) on commit delete rows not
logged;
 0 rows inserted/updated/deleted
+ij> -- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+0 rows inserted/updated/deleted
+ij> -- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+ERROR 23502: Column 'B'  cannot accept a NULL value.
+ij> insert into atmcn_1 values (1,1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+ij> alter table atmcn_1 alter column a not null;
+0 rows inserted/updated/deleted
+ij> -- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+ERROR 23502: Column 'A'  cannot accept a NULL value.
+ij> insert into atmcn_1 values (2,2);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+2          |2          
+ij> alter table atmcn_1 alter column b null;
+0 rows inserted/updated/deleted
+ij> insert into atmcn_1 (a) values (1);
+1 row inserted/updated/deleted
+ij> select * from atmcn_1;
+A          |B          
+-----------------------
+1          |1          
+2          |2          
+1          |NULL       
+ij> -- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+ERROR X0Y80: ALTER table 'APP.ATMCN_1' failed. Null data found in column 'B'.
+ij> -- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_2 alter column a null;
+ERROR 42Z20: Column 'A' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
+ij> create table atmcn_3 (a integer not null, b integer not null);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_3 alter column b null;
+ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
+ij> -- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- statement is optional:
+create table atmcn_4 (a integer not null, b integer);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_4 alter a null;
+0 rows inserted/updated/deleted
+ij> -- show that a column which has a UNIQUE constraint cannot be modified NULL:
+create table atmcn_5 (a integer not null, b integer not null unique);
+0 rows inserted/updated/deleted
+ij> alter table atmcn_5 alter column b null;
+ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint,
which cannot have any nullable columns.
 ij> 

Modified: db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?view=diff&rev=440203&r1=440202&r2=440203
==============================================================================
--- db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
(original)
+++ db/derby/code/branches/10.2/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
Mon Sep  4 16:06:46 2006
@@ -552,3 +552,32 @@
 drop table logged;
 declare global temporary table session.logged(logged int) on commit delete rows not logged;
 
+-- tests for ALTER TABLE ALTER COLUMN [NOT] NULL
+create table atmcn_1 (a integer, b integer not null);
+-- should fail because b cannot be null
+insert into atmcn_1 (a) values (1);
+insert into atmcn_1 values (1,1);
+select * from atmcn_1;
+alter table atmcn_1 alter column a not null;
+-- should fail because a cannot be null
+insert into atmcn_1 (b) values (2);
+insert into atmcn_1 values (2,2);
+select * from atmcn_1;
+alter table atmcn_1 alter column b null;
+insert into atmcn_1 (a) values (1);
+select * from atmcn_1;
+-- Now that B has a null value, trying to modify it to NOT NULL should fail
+alter table atmcn_1 alter column b not null;
+-- show that a column which is part of the PRIMARY KEY cannot be modified NULL
+create table atmcn_2 (a integer not null primary key, b integer not null);
+alter table atmcn_2 alter column a null;
+create table atmcn_3 (a integer not null, b integer not null);
+alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b);
+alter table atmcn_3 alter column b null;
+-- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ...
+-- statement is optional:
+create table atmcn_4 (a integer not null, b integer);
+alter table atmcn_4 alter a null;
+-- show that a column which has a UNIQUE constraint cannot be modified NULL:
+create table atmcn_5 (a integer not null, b integer not null unique);
+alter table atmcn_5 alter column b null;



Mime
View raw message