You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <de...@db.apache.org> on 2006/07/05 03:12:35 UTC
[jira] Updated: (DERBY-396) Support for ALTER STATEMENT to DROP ,
MODIFY, RENAME a COLUMN
[ http://issues.apache.org/jira/browse/DERBY-396?page=all ]
Bryan Pendleton updated DERBY-396:
----------------------------------
Attachment: dropColumn_1.diff
Attached file 'dropColumn_1.diff' is a first attempt at providing support for
ALTER TABLE DROP COLUMN
This patch contains some small changes to the parser/compiler to recognize
the new ALTER TABLE syntax, and uses the existing code in
AlterTableConstantAction.java for the execution support.
The patch contains a small amount of new test code, but really I haven't done
much testing yet; I suspect that there is a LOT more testing to do, and I'm
hoping that reviewers will suggest particular test cases that would be of
interest.
I'm particularly interested in feedback about the parser changes, and about testing,
but of course all feedback is very gratefully appreciated.
> Support for ALTER STATEMENT to DROP , MODIFY, RENAME a COLUMN
> --------------------------------------------------------------
>
> Key: DERBY-396
> URL: http://issues.apache.org/jira/browse/DERBY-396
> Project: Derby
> Type: New Feature
> Components: SQL
> Environment: LINUX
> Reporter: Kumar Matcha
> Assignee: Bryan Pendleton
> Attachments: dropColumn_1.diff
>
> Alter Statement should support dropping a column, modifying a column to a different data type , rename a column.
--
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
Re: DERBY-396: Drop Column dependency questions
Posted by Satheesh Bandaram <ba...@gmail.com>.
Hi Bryan,
Thanks for working on this long requested feature. More below...
Bryan Pendleton wrote:
>
> Does it seem correct that:
>
> a) RESTRICT processing should consider an index on a column to be
> a dependent object and fail the DROP COLUMN if the column is used
> in an index?
What happens when you drop a column with restrict that is part of a
composite index? How about if there are any constraints on that column?
Also have to worry about that being a triggering column or part of
trigger body.
I personally prefer if DROP COLUMN also implicitly drops an index on
that column, even for RESTRICT. It should also be able to drop a default
and any constraints that affect only that column. (though not sure if
there is a primary key on that column)
> b) CASCADE processing should cascade the DROP COLUMN to include
> dropping a view which uses the column that is dropped?
>
Derby doesn't support CASCADE option for any DDLs that I am aware of.
Like dropping a table with CASCADE option or dropping a schema with
CASCADE option to drop dependent objects. I wouldn't implement CASCADE
option only for DROP COLUMN... that probably depends on real cascade
implementation underneath it. Also would raise lots of questions about
what else you should drop... like constraints, triggers, composite
indexes etc...
Satheesh
> thanks,
>
> bryan
>
>
Re: DERBY-396: Drop Column dependency questions
Posted by Mamta Satoor <ms...@gmail.com>.
Yes, that is exactly what I meant in my earlier mail. Thanks for
clarification, Dan.
Mamta
On 7/14/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
>
> > I think in RESTRICT mode, revoking the privileges should be done as one
> of
> > the last steps, ie after we have checked for dependent objects. If there
> > are
> > dependent objects. DROP COLUMN RESTRICT will fail. If there are no
> > dependent
> > objects, then revoke privileges should simply work because there
> > couldn't be
> > any objects dependent on those privileges at this point in the code. And
> I
> > tend to agree with Dan that DROP COLUMN RESTRICT shouldn't throw
> exception
> > just because there are privileges granted on it. If there are no objects
> > based on those privileges, it should be fine to REVOKE them. But, I
> haven't
> > looked at the spec to verify this behavior.
>
> Just to be clear if an object is dependent on a column level privilege
> then it should already be dependent on the column itself. There should
> be no need to go through the list of privileges to see what objects are
> dependent on them.
>
> And of course with the dependency system it's no so much that code
> checks for dependent objects, more that code fires off an invalidation
> event (makeInvalid call) and the dependents either handle it or throw an
> exception to stop it.
>
> Dan.
>
>
>
Re: DERBY-396: Drop Column dependency questions
Posted by Daniel John Debrunner <dj...@apache.org>.
Mamta Satoor wrote:
> I think in RESTRICT mode, revoking the privileges should be done as one of
> the last steps, ie after we have checked for dependent objects. If there
> are
> dependent objects. DROP COLUMN RESTRICT will fail. If there are no
> dependent
> objects, then revoke privileges should simply work because there
> couldn't be
> any objects dependent on those privileges at this point in the code. And I
> tend to agree with Dan that DROP COLUMN RESTRICT shouldn't throw exception
> just because there are privileges granted on it. If there are no objects
> based on those privileges, it should be fine to REVOKE them. But, I haven't
> looked at the spec to verify this behavior.
Just to be clear if an object is dependent on a column level privilege
then it should already be dependent on the column itself. There should
be no need to go through the list of privileges to see what objects are
dependent on them.
And of course with the dependency system it's no so much that code
checks for dependent objects, more that code fires off an invalidation
event (makeInvalid call) and the dependents either handle it or throw an
exception to stop it.
Dan.
Re: DERBY-396: Drop Column dependency questions
Posted by Mamta Satoor <ms...@gmail.com>.
I think in RESTRICT mode, revoking the privileges should be done as one of
the last steps, ie after we have checked for dependent objects. If there are
dependent objects. DROP COLUMN RESTRICT will fail. If there are no dependent
objects, then revoke privileges should simply work because there couldn't be
any objects dependent on those privileges at this point in the code. And I
tend to agree with Dan that DROP COLUMN RESTRICT shouldn't throw exception
just because there are privileges granted on it. If there are no objects
based on those privileges, it should be fine to REVOKE them. But, I haven't
looked at the spec to verify this behavior.
Mamta
On 7/14/06, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Bryan Pendleton wrote:
>
> > Hi Mamta, thank you for investigating this.
> >
> >> DROP COLUMN will also need to see if there are any privileges granted
> >> on it and if yes, then those privileges should be revoked.
> >
> >
> > That makes sense to me, too.
> >
> > I am proposing to have the ALTER TABLE DROP COLUMN statement call the
> > already-existing code in AlterTableConstantAction.java, specifically
> > the dropColumnFromTable() method.
> >
> > I am proposing to implement both CASCADE and RESTRICT, using the
> existing
> > cascade/restrict functionality in the dropColumnFromTable() method.
> >
> > So I think that we just need to add the appropriate code to this method
> > for privileges, and then we should be fine. It sounds like we need some
> > code which looks for privileges granted on this column, and if there are
> > any, then if this is RESTRICT, throw an exception, otherwise
> automatically
> > revoke those privileges.
>
> I don't think that matches the SQL spec. From a quick look I don't see
> any text that stops the drop in restrict mode if privileges have been
> granted on the column.
>
> Dan.
>
>
Re: DERBY-396: Drop Column dependency questions
Posted by Daniel John Debrunner <dj...@apache.org>.
Bryan Pendleton wrote:
> Hi Mamta, thank you for investigating this.
>
>> DROP COLUMN will also need to see if there are any privileges granted
>> on it and if yes, then those privileges should be revoked.
>
>
> That makes sense to me, too.
>
> I am proposing to have the ALTER TABLE DROP COLUMN statement call the
> already-existing code in AlterTableConstantAction.java, specifically
> the dropColumnFromTable() method.
>
> I am proposing to implement both CASCADE and RESTRICT, using the existing
> cascade/restrict functionality in the dropColumnFromTable() method.
>
> So I think that we just need to add the appropriate code to this method
> for privileges, and then we should be fine. It sounds like we need some
> code which looks for privileges granted on this column, and if there are
> any, then if this is RESTRICT, throw an exception, otherwise automatically
> revoke those privileges.
I don't think that matches the SQL spec. From a quick look I don't see
any text that stops the drop in restrict mode if privileges have been
granted on the column.
Dan.
Re: DERBY-396: Drop Column dependency questions
Posted by Bryan Pendleton <bp...@amberpoint.com>.
Hi Mamta, thank you for investigating this.
> DROP COLUMN will also need to see if there are any privileges granted on
> it and if yes, then those privileges should be revoked.
That makes sense to me, too.
I am proposing to have the ALTER TABLE DROP COLUMN statement call the
already-existing code in AlterTableConstantAction.java, specifically
the dropColumnFromTable() method.
I am proposing to implement both CASCADE and RESTRICT, using the existing
cascade/restrict functionality in the dropColumnFromTable() method.
So I think that we just need to add the appropriate code to this method
for privileges, and then we should be fine. It sounds like we need some
code which looks for privileges granted on this column, and if there are
any, then if this is RESTRICT, throw an exception, otherwise automatically
revoke those privileges.
And add new tests, of course :)
Does this sound right to you?
thanks,
bryan
Re: DERBY-396: Drop Column dependency questions
Posted by Mamta Satoor <ms...@gmail.com>.
Hi Bryan,
DROP COLUMN will also need to see if there are any privileges granted on it
and if yes, then those privileges should be revoked. There might be
views/triggers/constraints dependent on that privilege but I think in the
case of RESTRICT, the DROP COLUMN will fail anyways because of the dependent
objects.
Are we implementing DROP COLUMN cascade too?
I haven't given this a whole lot of thought but wanted to bring up the
privilege issue with the DROP COLUMN functionality.
thanks,
Mamta
On 7/10/06, Rick Hillegas <Ri...@sun.com> wrote:
>
> Hi Bryan,
>
> My $0.02 follows. Cheers-Rick
>
> Bryan Pendleton wrote:
>
> > ...
> >
> > a) RESTRICT processing should consider an index on a column to be
> > a dependent object and fail the DROP COLUMN if the column is used
> > in an index?
>
> The ANSI spec doesn't provide much guidance here since ANSI doesn't talk
> about indexes. I would recommend using DROP TABLE as your guide.
> Dropping a table implicitly drops all indexes defined on that table. I
> would expect that dropping a column would implictly drop all indexes
> which mention that column.
>
> >
> > b) CASCADE processing should cascade the DROP COLUMN to include
> > dropping a view which uses the column that is dropped?
>
> My reading of the ANSI spec is that CASCADE is the opposite of RESTRICT.
> That is, if you specify CASCADE, then the database will drop the
> dependent objects which blocked the RESTRICTed version of the statement.
> According to the ANSI spec (Volume 2, section 11.18), these dependent
> objects include:
>
> o views which mention the column
> o certain constraints
> o triggers which mention the column
> o generated columns whose definitions mention the dropped column (we
> don't support this yet)
> o sql routines which mention the column (again, we don't support this)
>
> So my reading of the ANSI spec suggests that CASCADE should drop views
> which mention the column.
>
> I think that the RESTRICT-blocking logic is smart enough to compute the
> closure of dropped objects (the dependents of the dependents and so on).
> I would think that CASCADE would drop everything in that closure.
>
> >
> > thanks,
> >
> > bryan
> >
>
>
Re: DERBY-396: Drop Column dependency questions
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Bryan,
My $0.02 follows. Cheers-Rick
Bryan Pendleton wrote:
> ...
>
> a) RESTRICT processing should consider an index on a column to be
> a dependent object and fail the DROP COLUMN if the column is used
> in an index?
The ANSI spec doesn't provide much guidance here since ANSI doesn't talk
about indexes. I would recommend using DROP TABLE as your guide.
Dropping a table implicitly drops all indexes defined on that table. I
would expect that dropping a column would implictly drop all indexes
which mention that column.
>
> b) CASCADE processing should cascade the DROP COLUMN to include
> dropping a view which uses the column that is dropped?
My reading of the ANSI spec is that CASCADE is the opposite of RESTRICT.
That is, if you specify CASCADE, then the database will drop the
dependent objects which blocked the RESTRICTed version of the statement.
According to the ANSI spec (Volume 2, section 11.18), these dependent
objects include:
o views which mention the column
o certain constraints
o triggers which mention the column
o generated columns whose definitions mention the dropped column (we
don't support this yet)
o sql routines which mention the column (again, we don't support this)
So my reading of the ANSI spec suggests that CASCADE should drop views
which mention the column.
I think that the RESTRICT-blocking logic is smart enough to compute the
closure of dropped objects (the dependents of the dependents and so on).
I would think that CASCADE would drop everything in that closure.
>
> thanks,
>
> bryan
>
DERBY-396: Drop Column dependency questions
Posted by Bryan Pendleton <bp...@amberpoint.com>.
I've been writing additional tests for ALTER TABLE DROP COLUMN and
have come across a few questions:
1) When I specify RESTRICT, the code searches for dependent objects
and rejects the DROP COLUMN if a dependent item is found. However,
an index is apparently not considered a dependent item. That is:
ij> create table atdc_4 (a int, b int);
0 rows inserted/updated/deleted
ij> create index atdc_4_idx_1 on atdc_4 (a);
0 rows inserted/updated/deleted
ij> -- This succeeds, but it seems to me that it should fail.
alter table atdc_4 drop column a restrict;
0 rows inserted/updated/deleted
2) When I specify CASCADE, the code which drops the column also
drops the dependent objects. However, cascade processing apparently
is unable to drop a view. That is:
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> -- This fails, but it seems to me that it should succeed.
alter table atdc_5 drop column b cascade;
ERROR X0Y23: Operation 'DROP COLUMN' cannot be performed on object 'ATDC_5(B)'
because VIEW 'ATDC_VW_1' is dependent on that object.
Does it seem correct that:
a) RESTRICT processing should consider an index on a column to be
a dependent object and fail the DROP COLUMN if the column is used
in an index?
b) CASCADE processing should cascade the DROP COLUMN to include
dropping a view which uses the column that is dropped?
thanks,
bryan