drop column functionality

[Tim Dudgeon <td...@informaticsmatters.com>]

I'm looking to migrate an application to Derby as it appears to solve a 
number of performance issues I have with the current db.
But it looks like there is no ALTER TABLE DROP COLUMN ... functionality. 
Is this really correct?
If so, it's a complete show stopper for me as I need to dynamically add 
and remove columns for tables. It's like being able to open a door, but 
not close it, like an airplane being able to take off but not land....

I see that there is a JIRA issue for this 
http://issues.apache.org/jira/browse/DERBY-1489?page=all
Is there an expectation for resolution?

Thanks

Tim

Re: drop column functionality

[Bryan Pendleton <bp...@amberpoint.com>]

Tim Dudgeon wrote:
> 1. Should I apply the patch to the 10.1.3.1 sources (e.g. the 
> db-derby-10.1.3.1-src.zip download) or something else?

The patch is intended to be applied to the current trunk, which
you can fetch from svn via

   svn checkout https://svn.apache.org/repos/asf/db/derby/code/trunk/

> 2. Syntax: presumably
> ALTER TABLE a_table DROP COLUMN a_column;

Yes, pretty much just like that, with two additional notes:
  - the keyword "COLUMN" is optional, and
  - there is an optional [ CASCADE | RESTRICT ] at the end; if you
    don't say CASCADE or RESTRICT the default is CASCADE.

If you have more comments about the patch or the implementation
we should probably move this discussion to the developer's list
(derby-dev@db.apache.org).

thanks,

bryan

Re: drop column functionality

[Tim Dudgeon <td...@informaticsmatters.com>]

Bryan Pendleton wrote:

> There are two open problems regarding that patch that I am still studying:
>  - DROP COLUMN may not work properly if there are GRANTs on that column
>  - DROP COLUMN may not work properly if there are VIEWs on that column

Neither of those is an issue for me. My db is very simple.
I'll try the patch and see how it goes.

2 questions:

1. Should I apply the patch to the 10.1.3.1 sources (e.g. the 
db-derby-10.1.3.1-src.zip download) or something else?

2. Syntax: presumably
ALTER TABLE a_table DROP COLUMN a_column;


Thanks

Tim

Re: drop column functionality

[Bryan Pendleton <bp...@amberpoint.com>]

> But it looks like there is no ALTER TABLE DROP COLUMN ... functionality. 
...
> Is there an expectation for resolution?

Hi Tim,

Thanks for considering Derby; I think you will find it is very powerful.

You are correct that ALTER TABLE DROP COLUMN is not currently present.

However, as you've observed, there is an active issue for it and that
issue has a patch available.

Can you try building Derby with the patch attached to that JIRA issue,
and let us know if the feature appears to be working properly for you?

Getting some additional testing would definitely accelerate the process
of integrating this feature into the codeline.

There are two open problems regarding that patch that I am still studying:
  - DROP COLUMN may not work properly if there are GRANTs on that column
  - DROP COLUMN may not work properly if there are VIEWs on that column

Other than that, I believe that the feature is working properly, so it
would be wonderful to get some additional experience about how it works
"in real life".

If you need assistance building Derby with the patch, please ask on the
developers list (derby-dev@db.apache.org) and we'll be glad to help.

thanks,

bryan