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 bp...@apache.org on 2006/10/06 01:11:34 UTC
svn commit: r453420 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/sql/compile/
engine/org/apache/derby/iapi/sql/depend/
engine/org/apache/derby/iapi/sql/dictionary/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/s...
Author: bpendleton
Date: Thu Oct 5 16:11:32 2006
New Revision: 453420
URL: http://svn.apache.org/viewvc?view=rev&rev=453420
Log:
DERBY-1489: Provide ALTER TABLE DROP COLUMN functionality
This patch provides support for ALTER TABLE t DROP COLUMN c.
The patch modifies the SQL parser so that it supports statements of the form:
ALTER TABLE t DROP [COLUMN] c [CASCADE|RESTRICT]
If you don't specify CASCADE or RESTRICT, the default is CASCADE.
If you specify RESTRICT, then the column drop will be rejected if it would
cause a dependent view, trigger, check constraint, unique constraint,
foreign key constraint, or primary key constraint to become invalid.
Currently, column privileges are not properly adjusted when dropping a
column. This is bug DERBY-1909, and for now we simply reject DROP COLUMN
if it is specified when sqlAuthorization is true. When DERBY-1909 is fixed,
the tests in altertableDropColumn.sql should be merged into altertable.sql,
and altertableDropColumn.sql (and .out) should be removed.
This new feature is currently undocumented. DERBY-1926 tracks the documentation
changes necessary to document this feature.
The execution logic for ALTER TABLE DROP COLUMN is in AlterTableConstantAction,
and was not substantially modified by this change. The primary changes to
that existing code were:
- to hook RESTRICT processing up to the dependency manager so that
dependent view processing was sensitive to whether the user
had specified CASCADE or RESTRICT
- to reread the table descriptor from the catalogs after dropping all the
dependent schema objects and before compressing the table, so that the
proper scheman information was used during the compress.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out (with props)
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql (with props)
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.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/NodeFactoryImpl.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/depend/BasicDependencyManager.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java Thu Oct 5 16:11:32 2006
@@ -142,7 +142,7 @@
static final int USER_NODE = 110; // // special function USER
static final int IS_NODE = 111;
static final int LOCK_TABLE_NODE = 112;
- // 113
+ static final int DROP_COLUMN_NODE = 113;
static final int ALTER_TABLE_NODE = 114;
static final int AGGREGATE_NODE = 115;
static final int COLUMN_DEFINITION_NODE = 116;
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/depend/DependencyManager.java Thu Oct 5 16:11:32 2006
@@ -324,6 +324,7 @@
// issued, this invalidation action will be sent to all
// it's dependents.
public static final int REVOKE_PRIVILEGE_RESTRICT = 45;
+ public static final int DROP_COLUMN_RESTRICT = 46;
/**
* Extensions to this interface may use action codes > MAX_ACTION_CODE without fear of
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/SPSDescriptor.java Thu Oct 5 16:11:32 2006
@@ -908,6 +908,7 @@
case DependencyManager.CREATE_TRIGGER:
case DependencyManager.DROP_TRIGGER:
case DependencyManager.DROP_COLUMN:
+ case DependencyManager.DROP_COLUMN_RESTRICT:
case DependencyManager.UPDATE_STATISTICS:
case DependencyManager.DROP_STATISTICS:
case DependencyManager.TRUNCATE_TABLE:
@@ -982,6 +983,7 @@
case DependencyManager.CREATE_TRIGGER:
case DependencyManager.DROP_TRIGGER:
case DependencyManager.DROP_COLUMN:
+ case DependencyManager.DROP_COLUMN_RESTRICT:
case DependencyManager.UPDATE_STATISTICS:
case DependencyManager.DROP_STATISTICS:
case DependencyManager.TRUNCATE_TABLE:
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/dictionary/ViewDescriptor.java Thu Oct 5 16:11:32 2006
@@ -248,6 +248,7 @@
*/
case DependencyManager.CREATE_INDEX:
case DependencyManager.DROP_INDEX:
+ case DependencyManager.DROP_COLUMN:
case DependencyManager.CREATE_CONSTRAINT:
case DependencyManager.ALTER_TABLE:
case DependencyManager.CREATE_TRIGGER:
@@ -282,6 +283,11 @@
//show throw an exception.
//In Derby, at this point, REVOKE_PRIVILEGE_RESTRICT gets sent
//when execute privilege on a routine is getting revoked.
+ // DROP_COLUMN_RESTRICT is similar. Any case which arrives
+ // at this default: statement causes the exception to be
+ // thrown, indicating that the DDL modification should be
+ // rejected because a view is dependent on the underlying
+ // object (table, column, privilege, etc.)
default:
DependencyManager dm;
@@ -330,12 +336,18 @@
//types SELECT, UPDATE, DELETE, INSERT, REFERENCES, TRIGGER), we
//make the ViewDescriptor drop itself.
case DependencyManager.REVOKE_PRIVILEGE:
+ case DependencyManager.DROP_COLUMN:
dropViewWork(getDataDictionary(),
getDataDictionary().getDependencyManager(), lcc,
lcc.getTransactionExecute(),
getDataDictionary().getTableDescriptor(uuid).getSchemaDescriptor(),
getDataDictionary().getTableDescriptor(uuid), false);
- return;
+
+ lcc.getLastActivation().addWarning(
+ StandardException.newWarning(
+ SQLState.LANG_VIEW_DROPPED,
+ this.getObjectName() ));
+ return;
default:
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ModifyColumnNode.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- 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 Thu Oct 5 16:11:32 2006
@@ -241,6 +241,8 @@
return ColumnInfo.MODIFY_COLUMN_CONSTRAINT;
case C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NOT_NULL_NODE:
return ColumnInfo.MODIFY_COLUMN_CONSTRAINT_NOT_NULL;
+ case C_NodeTypes.DROP_COLUMN_NODE:
+ return ColumnInfo.DROP;
default:
if (SanityManager.DEBUG)
{
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java Thu Oct 5 16:11:32 2006
@@ -396,6 +396,7 @@
case C_NodeTypes.MODIFY_COLUMN_TYPE_NODE:
case C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NODE:
case C_NodeTypes.MODIFY_COLUMN_CONSTRAINT_NOT_NULL_NODE:
+ case C_NodeTypes.DROP_COLUMN_NODE:
return C_NodeNames.MODIFY_COLUMN_NODE_NAME;
case C_NodeTypes.NON_STATIC_METHOD_CALL_NODE:
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- 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 Thu Oct 5 16:11:32 2006
@@ -11804,13 +11804,12 @@
return lockGranularity;
}
|
- LOOKAHEAD( {getToken(1).kind == DROP
- && (getToken(2).kind == CONSTRAINT
- || getToken(2).kind == PRIMARY
- || getToken(2).kind == FOREIGN
- || getToken(2).kind == UNIQUE
- || getToken(2).kind == CHECK)} )
- tableElement = dropTableConstraintDefinition()
+ <DROP>
+ (
+ tableElement = dropColumnDefinition(behavior)
+ |
+ tableElement = dropTableConstraintDefinition()
+ )
{
changeType[0] = DDLStatementNode.DROP_TYPE;
tableElementList.addTableElement(tableElement);
@@ -11824,6 +11823,46 @@
}
}
+/*
+ * Handle
+ *
+ * ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+ */
+TableElementNode
+dropColumnDefinition(int []behavior) throws StandardException :
+{
+ String columnName;
+ TableElementNode tableElement;
+}
+{
+ [ <COLUMN> ] columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true)
+ dropColumnReferentialAction(behavior)
+ {
+ if( getLanguageConnectionContext().usesSqlAuthorization())
+ throw StandardException.newException(
+ SQLState.NOT_IMPLEMENTED,
+ "ALTER TABLE DROP COLUMN (sqlAuthorization=true)");
+ return (TableElementNode) nodeFactory.getNode(
+ C_NodeTypes.DROP_COLUMN_NODE,
+ columnName, null,
+ null, null,
+ getContextManager());
+ }
+}
+void
+dropColumnReferentialAction(int []behavior) :
+{
+ int refBehavior = StatementType.DROP_CASCADE;
+}
+{
+ [ <CASCADE> {refBehavior = StatementType.DROP_CASCADE;}
+ | <RESTRICT> {refBehavior = StatementType.DROP_RESTRICT;}
+ ]
+ {
+ behavior[0] = refBehavior;
+ }
+}
+
TableElementNode
addColumnDefinition(TableElementList tableElementList) throws StandardException :
{
@@ -11943,9 +11982,9 @@
TableName constraintName;
}
{
- LOOKAHEAD( {getToken(2).kind == CONSTRAINT} )
+ LOOKAHEAD( {getToken(1).kind == CONSTRAINT} )
/* changed constraintName() to qualifiedName() for compaction */
- <DROP> <CONSTRAINT> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
+ <CONSTRAINT> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
{
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.CONSTRAINT_DEFINITION_NODE,
@@ -11960,8 +11999,8 @@
);
}
|
- LOOKAHEAD( {getToken(2).kind == PRIMARY} )
- <DROP> <PRIMARY> <KEY>
+ LOOKAHEAD( {getToken(1).kind == PRIMARY} )
+ <PRIMARY> <KEY>
{
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.CONSTRAINT_DEFINITION_NODE,
@@ -11976,8 +12015,8 @@
);
}
|
- LOOKAHEAD( {getToken(2).kind == FOREIGN} )
- <DROP> <FOREIGN> <KEY> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
+ LOOKAHEAD( {getToken(1).kind == FOREIGN} )
+ <FOREIGN> <KEY> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
{
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.CONSTRAINT_DEFINITION_NODE,
@@ -11993,8 +12032,8 @@
);
}
|
- LOOKAHEAD( {getToken(2).kind == UNIQUE} )
- <DROP> <UNIQUE> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
+ LOOKAHEAD( {getToken(1).kind == UNIQUE} )
+ <UNIQUE> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
{
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.CONSTRAINT_DEFINITION_NODE,
@@ -12010,7 +12049,7 @@
);
}
|
- <DROP> <CHECK> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
+ <CHECK> constraintName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
{
return (TableElementNode) nodeFactory.getNode(
C_NodeTypes.CONSTRAINT_DEFINITION_NODE,
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/depend/BasicDependencyManager.java Thu Oct 5 16:11:32 2006
@@ -853,6 +853,9 @@
case DROP_COLUMN:
return "DROP COLUMN";
+ case DROP_COLUMN_RESTRICT:
+ return "DROP COLUMN RESTRICT";
+
case DROP_STATISTICS:
return "DROP STATISTICS";
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/AlterTableConstantAction.java?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- 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 Thu Oct 5 16:11:32 2006
@@ -663,6 +663,67 @@
/**
* Workhorse for dropping a column from a table.
*
+ * This routine drops a column from a table, taking care
+ * to properly handle the various related schema objects.
+ *
+ * The syntax which gets you here is:
+ *
+ * ALTER TABLE tbl DROP [COLUMN] col [CASCADE|RESTRICT]
+ *
+ * The keyword COLUMN is optional, and if you don't
+ * specify CASCADE or RESTRICT, the default is CASCADE
+ * (the default is chosen in the parser, not here).
+ *
+ * If you specify RESTRICT, then the column drop should be
+ * rejected if it would cause a dependent schema object
+ * to become invalid.
+ *
+ * If you specify CASCADE, then the column drop should
+ * additionally drop other schema objects which have
+ * become invalid.
+ *
+ * You may not drop the last (only) column in a table.
+ *
+ * Schema objects of interest include:
+ * - views
+ * - triggers
+ * - constraints
+ * - check constraints
+ * - primary key constraints
+ * - foreign key constraints
+ * - unique key constraints
+ * - not null constraints
+ * - privileges
+ * - indexes
+ * - default values
+ *
+ * Dropping a column may also change the column position
+ * numbers of other columns in the table, which may require
+ * fixup of schema objects (such as triggers and column
+ * privileges) which refer to columns by column position number.
+ *
+ * Currently, column privileges are not repaired when
+ * dropping a column. This is bug DERBY-1909, and for the
+ * time being we simply reject DROP COLUMN if it is specified
+ * when sqlAuthorization is true (that check occurs in the
+ * parser, not here). When DERBY-1909 is fixed:
+ * - Update this comment
+ * - Remove the check in dropColumnDefinition() in the parser
+ * - consolidate all the tests in altertableDropColumn.sql
+ * back into altertable.sql and remove the separate
+ * altertableDropColumn files
+ *
+ * Indexes are a bit interesting. The official SQL spec
+ * doesn't talk about indexes; they are considered to be
+ * an imlementation-specific performance optimization.
+ * The current Derby behavior is that:
+ * - CASCADE/RESTRICT doesn't matter for indexes
+ * - when a column is dropped, it is removed from any indexes
+ * which contain it.
+ * - if that column was the only column in the index, the
+ * entire index is dropped.
+ *
+ * @param activation the current activation
* @param ix the index of the column specfication in the ALTER
* statement-- currently we allow only one.
* @exception StandardException thrown on failure.
@@ -711,7 +772,10 @@
toDrop.set(columnPosition);
td.setReferencedColumnMap(toDrop);
- dm.invalidateFor(td, DependencyManager.DROP_COLUMN, lcc);
+ dm.invalidateFor(td,
+ (cascade ? DependencyManager.DROP_COLUMN
+ : DependencyManager.DROP_COLUMN_RESTRICT),
+ lcc);
// If column has a default we drop the default and any dependencies
if (columnDescriptor.getDefaultInfo() != null)
@@ -812,13 +876,13 @@
if (! cascade)
{
- if (numRefCols > 1 || cd.getConstraintType() == DataDictionary.PRIMARYKEY_CONSTRAINT)
- {
- throw StandardException.newException(SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
+ // Reject the DROP COLUMN, because there exists a constraint
+ // which references this column.
+ //
+ throw StandardException.newException(SQLState.LANG_PROVIDER_HAS_DEPENDENT_OBJECT,
dm.getActionString(DependencyManager.DROP_COLUMN),
columnInfo[ix].name, "CONSTRAINT",
cd.getConstraintName() );
- }
}
if (cd instanceof ReferencedKeyConstraintDescriptor)
@@ -865,6 +929,18 @@
dm.invalidateFor(cd, DependencyManager.DROP_CONSTRAINT, lcc);
dm.clearDependencies(lcc, cd);
}
+
+ /*
+ * The work we've done above, specifically the possible
+ * dropping of primary key, foreign key, and unique constraints
+ * and their underlying indexes, may have affected the table
+ * descriptor. By re-reading the table descriptor here, we
+ * ensure that the compressTable code is working with an
+ * accurate table descriptor. Without this line, we may get
+ * conglomerate-not-found errors and the like due to our
+ * stale table descriptor.
+ */
+ td = dd.getTableDescriptor(tableId);
compressTable(activation);
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertable.out Thu Oct 5 16:11:32 2006
@@ -958,4 +958,11 @@
-1 |b
3 |three
42 |forty two
+ij> -- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
+-- mode. This is because of bug DERBY-1909, which involves how to fix up
+-- the GRANTed column permissions following a DROP COLUMN.
+create table atdc_1 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> alter table atdc_1 drop column b;
+ERROR 0A000: Feature not implemented: ALTER TABLE DROP COLUMN (sqlAuthorization=true).
ij>
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out?view=auto&rev=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out Thu Oct 5 16:11:32 2006
@@ -0,0 +1,389 @@
+ij> -- alter table tests for ALTER TABLE DROP COLUMN.
+-- These tests are in a separate file from altertable.sql because of
+-- bug DERBY-1909 involving DROP COLUMN being broken under sqlAuthorization.
+-- When DROP COLUMN works correctly with sqlAuthorization = true, these tests
+-- should be merged back into altertable.sql, and this file should be deleted
+-- Some tests of ALTER TABLE DROP COLUMN
+-- The overall syntax is:
+-- ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+--
+create table atdc_0 (a integer);
+0 rows inserted/updated/deleted
+ij> create table atdc_1 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 1);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B
+-----------------------
+1 |1
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+B |2 |INTEGER
+ij> alter table atdc_1 drop column b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A
+-----------
+1
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+ij> alter table atdc_1 add column b varchar (20);
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (1, 'new val');
+1 row inserted/updated/deleted
+ij> insert into atdc_1 (a, b) values (2, 'two val');
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B
+--------------------------------
+1 |NULL
+1 |new val
+2 |two val
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+B |2 |VARCHAR(20)
+ij> alter table atdc_1 add column c integer;
+0 rows inserted/updated/deleted
+ij> insert into atdc_1 values (3, null, 3);
+1 row inserted/updated/deleted
+ij> select * from atdc_1;
+A |B |C
+--------------------------------------------
+1 |NULL |NULL
+1 |new val |NULL
+2 |two val |NULL
+3 |NULL |3
+ij> alter table atdc_1 drop b;
+0 rows inserted/updated/deleted
+ij> select * from atdc_1;
+A |C
+-----------------------
+1 |NULL
+1 |NULL
+2 |NULL
+3 |3
+ij> select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE
+------------------------------------------------------------------------------------------------------------------------------------------------------------
+A |1 |INTEGER
+C |2 |INTEGER
+ij> -- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_01 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_01 has been dropped.
+ij> create table atdc_1_02 (a int not null primary key, b int);
+0 rows inserted/updated/deleted
+ij> create table atdc_1_03 (a03 int,
+ constraint a03_fk foreign key (a03) references atdc_1_02(a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_02 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_02 has been dropped.
+WARNING 01500: The constraint A03_FK on table ATDC_1_03 has been dropped.
+ij> -- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+0 rows inserted/updated/deleted
+ij> select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|ATDC_CONSTRAINT_1 |C|xxxxFILTERED-UUIDxxxx|E|0
+ij> select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+ where sc.constraintid = con.constraintid and con.tableid = st.tableid
+ and st.tablename = 'ATDC_1';
+CONSTRAINTID |CHECKDEFINITION |REFERENCEDCOLU&
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx|(a > 0) |(1)
+ij> alter table atdc_1 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'ATDC_CONSTRAINT_1' is dependent on that object.
+ij> -- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_CONSTRAINT_1 on table ATDC_1 has been dropped.
+ij> select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> -- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+ b int not null,
+ c int constraint atdc_1_c_chk check (c is not null),
+ d int not null unique,
+ e int,
+ f int,
+ constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+0 rows inserted/updated/deleted
+ij> -- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |INTEGER |0 |10 |10 |NULL |NULL |NO
+B |INTEGER |0 |10 |10 |NULL |NULL |NO
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |NO
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> alter table atdc_1_constraints drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column b restrict;
+0 rows inserted/updated/deleted
+ij> alter table atdc_1_constraints drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'ATDC_1_C_CHK' is dependent on that object.
+ij> alter table atdc_1_constraints drop column d restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'D' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_1_constraints drop column e restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'E' because CONSTRAINT 'ATDC_1_E_FK' is dependent on that object.
+ij> describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+A |INTEGER |0 |10 |10 |NULL |NULL |NO
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |NO
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> alter table atdc_1_constraints drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+WARNING 01500: The constraint ATDC_1_E_FK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint ATDC_1_C_CHK on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column d cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped.
+ij> alter table atdc_1_constraints drop column e cascade;
+0 rows inserted/updated/deleted
+ij> describe atdc_1_constraints;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+F |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> -- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'ATDC_NOSUCH' because it does not exist.
+ij> -- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+0 rows inserted/updated/deleted
+ij> alter table atdc_2 drop column b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> alter table atdc_2 drop b;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'.
+ij> -- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+ERROR 42X01: Syntax error: Encountered "\'a\'" at line 2, column 32.
+ij> -- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+ERROR 42X01: Syntax error: Encountered "column" at line 2, column 32.
+ij> alter table atdc_2 drop column;
+ERROR 42X01: Syntax error: Encountered "<EOF>" at line 1, column 30.
+ij> alter table atdc_2 drop column constraint;
+ERROR 42X01: Syntax error: Encountered "constraint" at line 1, column 32.
+ij> alter table atdc_2 drop column primary;
+ERROR 42X01: Syntax error: Encountered "primary" at line 1, column 32.
+ij> alter table atdc_2 drop column foreign;
+ERROR 42X01: Syntax error: Encountered "foreign" at line 1, column 32.
+ij> alter table atdc_2 drop column check;
+ERROR 42X01: Syntax error: Encountered "check" at line 1, column 32.
+ij> create table atdc_3 (a integer);
+0 rows inserted/updated/deleted
+ij> create index atdc_3_idx_1 on atdc_3 (a);
+0 rows inserted/updated/deleted
+ij> -- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'THE *LAST* COLUMN A' because TABLE 'APP.ATDC_3' is dependent on that object.
+ij> drop index atdc_3_idx_1;
+0 rows inserted/updated/deleted
+ij> -- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+0 rows inserted/updated/deleted
+ij> insert into atdc_4 values (1,2,3,4,5);
+1 row inserted/updated/deleted
+ij> create index atdc_4_idx_1 on atdc_4 (a);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_2 on atdc_4 (b, c, d);
+0 rows inserted/updated/deleted
+ij> create index atdc_4_idx_3 on atdc_4 (c, a);
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx |false
+ATDC_4_IDX_1 |true
+ATDC_4_IDX_2 |true
+ATDC_4_IDX_3 |true
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |A |true |3 |A |NULL |NULL
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |A |true |3 |A |NULL |NULL
+ij> -- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+-- index atdc_4_idx_1 is entirely dropped
+-- index atdc_4_idx_2 is left alone but the column positions are fixed up
+-- index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+xxxxFILTERED-UUIDxxxx |false
+ATDC_4_IDX_2 |true
+ATDC_4_IDX_3 |true
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ATDC_4 |C |true |3 |A |NULL |NULL
+ij> describe atdc_4;
+COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
+------------------------------------------------------------------------------
+B |INTEGER |0 |10 |10 |NULL |NULL |YES
+C |INTEGER |0 |10 |10 |NULL |NULL |YES
+D |INTEGER |0 |10 |10 |NULL |NULL |YES
+E |INTEGER |0 |10 |10 |NULL |NULL |YES
+ij> -- The effect of dropping column c is that:
+-- index atdc_4_idx_2 is modified to refer to columns b and d
+-- index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+0 rows inserted/updated/deleted
+ij> show indexes from atdc_4;
+TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES
+----------------------------------------------------------------------------
+ATDC_4 |B |true |3 |A |NULL |NULL
+ATDC_4 |D |true |3 |A |NULL |NULL
+ij> select * from atdc_4 where c = 3;
+ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table.
+ij> select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+1
+-----------
+1
+ij> select conglomeratename, isindex from sys.sysconglomerates
+ where conglomeratename like 'ATDC_4%';
+CONGLOMERATENAME |ISIN&
+--------------------------------------------------------------------------------------------------------------------------------------
+ATDC_4_IDX_2 |true
+ij> drop index atdc_4_idx_2;
+0 rows inserted/updated/deleted
+ij> -- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_1 (vw_b) as select b from atdc_5;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5 drop column b restrict;
+ERROR X0Y23: Operation 'DROP COLUMN RESTRICT' cannot be performed on object 'ATDC_5(B)' because VIEW 'ATDC_VW_1' is dependent on that object.
+ij> select * from atdc_vw_1;
+VW_B
+-----------
+ij> -- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_1 has been dropped.
+ij> select * from atdc_vw_1;
+ERROR 42X05: Table/View 'ATDC_VW_1' does not exist.
+ij> -- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+0 rows inserted/updated/deleted
+ij> create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+0 rows inserted/updated/deleted
+ij> alter table atdc_5a drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01501: The view ATDC_VW_5A_2 has been dropped.
+WARNING 01501: The view ATDC_VW_5A_1 has been dropped.
+ij> select * from atdc_vw_5a_1;
+ERROR 42X05: Table/View 'ATDC_VW_5A_1' does not exist.
+ij> select * from atdc_vw_5a_2;
+ERROR 42X05: Table/View 'ATDC_VW_5A_2' does not exist.
+ij> -- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+0 rows inserted/updated/deleted
+ij> create trigger atdc_6_trigger_1 after update of b on atdc_6
+ for each row mode db2sql values current_date;
+0 rows inserted/updated/deleted
+ij> alter table atdc_6 drop column b restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'B' because TRIGGER 'ATDC_6_TRIGGER_1' is dependent on that object.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME
+--------------------------------------------------------------------------------------------------------------------------------
+ATDC_6_TRIGGER_1
+ij> alter table atdc_6 drop column b cascade;
+0 rows inserted/updated/deleted
+WARNING 01502: The trigger ATDC_6_TRIGGER_1 on table ATDC_6 has been dropped.
+ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+TRIGGERNAME
+--------------------------------------------------------------------------------------------------------------------------------
+ij> create table atdc_7 (a int, b int, c int, primary key (a));
+0 rows inserted/updated/deleted
+ij> alter table atdc_7 drop column a restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_7 drop column a cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_7 has been dropped.
+ij> create table atdc_8 (a int, b int, c int, primary key (b, c));
+0 rows inserted/updated/deleted
+ij> alter table atdc_8 drop column c restrict;
+ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
+ij> alter table atdc_8 drop column c cascade;
+0 rows inserted/updated/deleted
+WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_8 has been dropped.
+ij> create table atdc_9 (a int not null, b int);
+0 rows inserted/updated/deleted
+ij> alter table atdc_9 drop column a restrict;
+0 rows inserted/updated/deleted
+ij> -- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+0 rows inserted/updated/deleted
+ij> grant select(a, b, c) on atdc_10 to bryan;
+ERROR 42Z60: GRANT not allowed unless database property derby.database.sqlAuthorization has value 'TRUE'.
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> alter table atdc_10 drop column b restrict;
+0 rows inserted/updated/deleted
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij> alter table atdc_10 drop column b cascade;
+ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_10'.
+ij> select * from sys.syscolperms;
+COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij>
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/altertableDropColumn.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Thu Oct 5 16:11:32 2006
@@ -270,10 +270,9 @@
-- beetle 5205
create table table tt (a int, b int, c int);
ERROR 42X01: Syntax error: Encountered "table" at line 4, column 14.
-ij> alter table tt drop column b;
-ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 16.
-ij> alter table tt rename to ttnew;
-ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 16.
+ij> -- alter table tt drop column b; This is now supported by Derby
+alter table tt rename to ttnew;
+ERROR 42X01: Syntax error: Encountered "rename" at line 2, column 16.
ij> alter table tt rename c to d;
ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 16.
ij> rename column tt.c to tt.d;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/grantRevokeDDL.out Thu Oct 5 16:11:32 2006
@@ -904,6 +904,7 @@
ij(MAMTA1)> -- this revoke should drop the dependent view in schema mamta2
revoke select on t11ViewTest from public;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- the view shouldn't exist anymore because one of the privileges required by it was revoked
select * from v21ViewTest;
@@ -1043,6 +1044,7 @@
ij(MAMTA1)> -- revoke a column level privilege. It should drop the view
revoke select(c122) on t12ViewTest from public;
0 rows inserted/updated/deleted
+WARNING 01501: The view V31VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta3;
ij(MAMTA3)> -- the view got dropped because of revoke issued earlier
select * from v31ViewTest;
@@ -1119,6 +1121,7 @@
ij(MAMTA1)> -- this revoke should drop the view mamta3.v31ViewTest
revoke select(c111) on t11ViewTest from mamta3;
0 rows inserted/updated/deleted
+WARNING 01501: The view V31VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta3;
ij(MAMTA3)> -- View shouldn't exist anymore
select * from v31ViewTest;
@@ -1158,6 +1161,7 @@
-- This will be fixed in a subsequent patch for revoke privilege
revoke select (c111) on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> select * from v21ViewTest;
ERROR: Failed with SQLSTATE 42X05
@@ -1231,6 +1235,7 @@
ij(MAMTA1)> -- This will drop the dependent view
revoke select on t11TriggerTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> select * from v21ViewTest;
ERROR: Failed with SQLSTATE 42X05
@@ -1299,6 +1304,7 @@
-- same privilege available at the PUBLIC level
revoke select on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -1318,6 +1324,7 @@
-- check if the view got dropped automatically
revoke select on t11ViewTest from PUBLIC;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -1355,6 +1362,7 @@
-- same privilege available at the table level
revoke select(c111) on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -1374,6 +1382,7 @@
-- check if the view got dropped automatically
revoke select on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -1412,6 +1421,7 @@
-- column level SELECT privilege.
revoke select on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- view doesn't exist anymore
select * from v21ViewTest;
@@ -1466,6 +1476,7 @@
ij(MAMTA1)> -- revoke the privilege from mamta2, should be ok, previous view is not created.
revoke select on t11ViewTest from mamta2;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21VIEWTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- this view is not created, should fail
select * from v21ViewTest;
@@ -1863,6 +1874,8 @@
ij(MAMTA1)> -- this should drop both the dependent views
revoke select, update on t11MiscTest from mamta2, mamta3;
0 rows inserted/updated/deleted
+WARNING 01501: The view V21MISCTEST has been dropped.
+WARNING 01501: The view V31MISCTEST has been dropped.
ij(MAMTA1)> set connection mamta2;
ij(MAMTA2)> -- should fail because it got dropped as part of revoke statement
select * from v21MiscTest;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Thu Oct 5 16:11:32 2006
@@ -11,6 +11,7 @@
lang/aggregate.sql
lang/aggregateOptimization.sql
lang/altertable.sql
+lang/altertableDropColumn.sql
lang/arithmetic.sql
lang/authorize.sql
lang/autoincrement.sql
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertable.sql Thu Oct 5 16:11:32 2006
@@ -594,3 +594,9 @@
insert into atmod_1 values (default, 'forty two');
select * from atmod_1;
+
+-- Demonstrate that ALTER TABLE DROP COLUMN doesnt work in sqlAuthorization
+-- mode. This is because of bug DERBY-1909, which involves how to fix up
+-- the GRANTed column permissions following a DROP COLUMN.
+create table atdc_1 (a integer, b integer);
+alter table atdc_1 drop column b;
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql?view=auto&rev=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql Thu Oct 5 16:11:32 2006
@@ -0,0 +1,178 @@
+-- alter table tests for ALTER TABLE DROP COLUMN.
+-- These tests are in a separate file from altertable.sql because of
+-- bug DERBY-1909 involving DROP COLUMN being broken under sqlAuthorization.
+-- When DROP COLUMN works correctly with sqlAuthorization = true, these tests
+-- should be merged back into altertable.sql, and this file should be deleted
+
+-- Some tests of ALTER TABLE DROP COLUMN
+-- The overall syntax is:
+-- ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ]
+--
+create table atdc_0 (a integer);
+create table atdc_1 (a integer, b integer);
+insert into atdc_1 values (1, 1);
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 drop column b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column b varchar (20);
+insert into atdc_1 values (1, 'new val');
+insert into atdc_1 (a, b) values (2, 'two val');
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+alter table atdc_1 add column c integer;
+insert into atdc_1 values (3, null, 3);
+select * from atdc_1;
+alter table atdc_1 drop b;
+select * from atdc_1;
+select columnname,columnnumber,columndatatype
+ from sys.syscolumns where referenceid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Demonstrate that we can drop a column which is the primary key. Also
+-- demonstrate that when we drop a column which is the primary key, that
+-- cascade processing will drop the corresponding foreign key constraint
+create table atdc_1_01 (a int, b int, c int not null primary key);
+alter table atdc_1_01 drop column c cascade;
+create table atdc_1_02 (a int not null primary key, b int);
+create table atdc_1_03 (a03 int,
+ constraint a03_fk foreign key (a03) references atdc_1_02(a));
+alter table atdc_1_02 drop column a cascade;
+-- drop column restrict should fail because column is used in a constraint:
+alter table atdc_1 add constraint atdc_constraint_1 check (a > 0);
+select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st
+ where sc.constraintid = con.constraintid and con.tableid = st.tableid
+ and st.tablename = 'ATDC_1';
+alter table atdc_1 drop column a restrict;
+-- drop column cascade should also drop the check constraint:
+alter table atdc_1 drop column a cascade;
+select * from sys.sysconstraints where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_1');
+-- Verify the behavior of the various constraint types:
+-- check, primary key, foreign key, unique, not null
+create table atdc_1_constraints (a int not null primary key,
+ b int not null,
+ c int constraint atdc_1_c_chk check (c is not null),
+ d int not null unique,
+ e int,
+ f int,
+ constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a));
+-- In restrict mode, none of the columns a, c, d, or e should be droppable,
+-- but in cascade mode each of them should be droppable, and at the end
+-- we should have only column f
+-- column b is droppable because an unnamed NOT NULL constraint doesn't
+-- prevent DROP COLUMN, only an explicit CHECK constraint does.
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a restrict;
+alter table atdc_1_constraints drop column b restrict;
+alter table atdc_1_constraints drop column c restrict;
+alter table atdc_1_constraints drop column d restrict;
+alter table atdc_1_constraints drop column e restrict;
+describe atdc_1_constraints;
+alter table atdc_1_constraints drop column a cascade;
+alter table atdc_1_constraints drop column c cascade;
+alter table atdc_1_constraints drop column d cascade;
+alter table atdc_1_constraints drop column e cascade;
+describe atdc_1_constraints;
+-- Some negative testing of ALTER TABLE DROP COLUMN
+-- Table does not exist:
+alter table atdc_nosuch drop column a;
+-- Table exists, but column does not exist:
+create table atdc_2 (a integer);
+alter table atdc_2 drop column b;
+alter table atdc_2 drop b;
+-- Column name is spelled incorrectly (wrong case)
+alter table atdc_2 drop column 'a';
+-- Some special reserved words to cause parser errors
+alter table atdc_2 drop column column;
+alter table atdc_2 drop column;
+alter table atdc_2 drop column constraint;
+alter table atdc_2 drop column primary;
+alter table atdc_2 drop column foreign;
+alter table atdc_2 drop column check;
+create table atdc_3 (a integer);
+create index atdc_3_idx_1 on atdc_3 (a);
+-- This fails because a is the only column in the table.
+alter table atdc_3 drop column a restrict;
+drop index atdc_3_idx_1;
+-- cascade/restrict processing doesn't currently consider indexes.
+-- The column being dropped is automatically dropped from all indexes
+-- as well. If that was the only (last) column in the index, then the
+-- index is dropped, too.
+create table atdc_4 (a int, b int, c int, d int, e int);
+insert into atdc_4 values (1,2,3,4,5);
+create index atdc_4_idx_1 on atdc_4 (a);
+create index atdc_4_idx_2 on atdc_4 (b, c, d);
+create index atdc_4_idx_3 on atdc_4 (c, a);
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+-- This succeeds, because cascade/restrict doesn't matter for indexes. The
+-- effect of dropping column a is that:
+-- index atdc_4_idx_1 is entirely dropped
+-- index atdc_4_idx_2 is left alone but the column positions are fixed up
+-- index atdc_4_idx_3 is modified to refer only to column c
+alter table atdc_4 drop column a restrict;
+select conglomeratename,isindex from sys.sysconglomerates where tableid in
+ (select tableid from sys.systables where tablename = 'ATDC_4');
+show indexes from atdc_4;
+describe atdc_4;
+-- The effect of dropping column c is that:
+-- index atdc_4_idx_2 is modified to refer to columns b and d
+-- index atdc_4_idx_3 is entirely dropped
+alter table atdc_4 drop column c restrict;
+show indexes from atdc_4;
+select * from atdc_4 where c = 3;
+select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2';
+select conglomeratename, isindex from sys.sysconglomerates
+ where conglomeratename like 'ATDC_4%';
+drop index atdc_4_idx_2;
+-- drop column restrict should fail becuase column is used in a view:
+create table atdc_5 (a int, b int);
+create view atdc_vw_1 (vw_b) as select b from atdc_5;
+alter table atdc_5 drop column b restrict;
+select * from atdc_vw_1;
+-- drop column cascade drops the column, and also drops the dependent view:
+alter table atdc_5 drop column b cascade;
+select * from atdc_vw_1;
+-- cascade processing should transitively drop a view dependent on a view
+-- dependent in turn on the column being dropped:
+create table atdc_5a (a int, b int, c int);
+create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a;
+create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1;
+alter table atdc_5a drop column b cascade;
+select * from atdc_vw_5a_1;
+select * from atdc_vw_5a_2;
+-- drop column restrict should fail because column is used in a trigger:
+create table atdc_6 (a integer, b integer);
+create trigger atdc_6_trigger_1 after update of b on atdc_6
+ for each row mode db2sql values current_date;
+alter table atdc_6 drop column b restrict;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+alter table atdc_6 drop column b cascade;
+select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1';
+create table atdc_7 (a int, b int, c int, primary key (a));
+alter table atdc_7 drop column a restrict;
+alter table atdc_7 drop column a cascade;
+create table atdc_8 (a int, b int, c int, primary key (b, c));
+alter table atdc_8 drop column c restrict;
+alter table atdc_8 drop column c cascade;
+create table atdc_9 (a int not null, b int);
+alter table atdc_9 drop column a restrict;
+-- Verify that a GRANTED privilege fails a drop column in RESTRICT mode,
+-- and verify that the privilege is dropped in CASCADE mode:
+create table atdc_10 (a int, b int, c int);
+grant select(a, b, c) on atdc_10 to bryan;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b restrict;
+select * from sys.syscolperms;
+alter table atdc_10 drop column b cascade;
+select * from sys.syscolperms;
Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/altertableDropColumn.sql
------------------------------------------------------------------------------
svn:eol-style = native
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql?view=diff&rev=453420&r1=453419&r2=453420
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/db2Compatibility.sql Thu Oct 5 16:11:32 2006
@@ -208,7 +208,7 @@
-- ALTER RENAME TABLE/COLUMN
-- beetle 5205
create table table tt (a int, b int, c int);
-alter table tt drop column b;
+-- alter table tt drop column b; This is now supported by Derby
alter table tt rename to ttnew;
alter table tt rename c to d;
rename column tt.c to tt.d;