Frustration: lack of alter table functionality

[Alexander Schatten]

I have to say, that I am really frustrated today. I evaluated Derby for
some time, and liked it for many reasons, but then I realise that Derby
does have significant limits in the alter table syntax: it is neither
possible to properly change the type of a column, nor to rename a column
or table and particularly not to drop a column.

so unfortunately this is really a show-stopper that makes me deeply worried.

I made the mistake, that I have a column in one table declared as
timestamp and want to change it to date. so what should I do? changing
is not possible. So I thought, I create a new column, copy the values
(not easily possible either) then drop the original one then rename the
second.

I created the second one just to figure out that there is no option to
drop a column. actually, I never thought that such a fundamental command
is missing...

now this gets serious. the only option I see now is: to somehow backup
all data from this table, then drop the table, but before this: drop all
constraints refering to this table (a LOT in this case!! foreign keys),
memorize all those constreints. then recreate this table restore the
data, which is probably very, very difficult because of the foreign key
constraints, so in effect I probably have to recreate the whole database
and figure out a restore option then.

restore is also an issue, as I use identity columns, and I was once
trying to import data and then had big problems in getting identity
columns running again.

ok, at the moment this is only a development database, and I can
recreate it (with all test data), but I am thinking: what happens if I
have to do this when there are live systems running? I cannot easily
provide a migration script for such an update. this means that such
restructurings are practically impossible or really big undertakings
with the need to shutdown databases, making backups and restores,
probably needing to write software that makes the migration... hence
really a big deal.

for what? altering one column...???? this is hard to justify!!


honestly, this is so severe, that I am considering to go back to hsqldb,
that has some other limits that are really not nice.

however, an important argument for java databases is the good
integration in the development process; and restructuring databases is a
very important issue here.


can someone give me a hint how I could solve this problem in an easier
way with derby? did I oversee something? or is it foreseeable that a new
version of Derby will come out supporting proper alter table statements?


thank you very much!



Alex

[Stephen Caine]

Alex,

Post by Alexander Schatten
I have to say, that I am really frustrated today. I evaluated Derby
for some time, and liked it for many reasons, but then I realise
it is neither possible to properly change the type of a column, nor
to rename a column or table and particularly not to drop a column.

I can't agree with you more, but the developers seem to be aware of
these limitations and are working on addressing them. I can only
hope that the limits to the alter table syntax will be addressed soon.

Stephen Caine
CommonGround Softworks, Inc.

[David Van Couvering]

My understanding is that improvements are planned for the next release,
10.3. I'm not sure where we stand on the date for that release - anyone
have any info on the expected release date?

David

Post by Stephen Caine
Alex,

Post by Alexander Schatten
I have to say, that I am really frustrated today. I evaluated Derby
for some time, and liked it for many reasons, but then I realise
it is neither possible to properly change the type of a column, nor
to rename a column or table and particularly not to drop a column.

I can't agree with you more, but the developers seem to be aware of
these limitations and are working on addressing them. I can only
hope that the limits to the alter table syntax will be addressed soon.
Stephen Caine
CommonGround Softworks, Inc.

[Andrew McIntyre]

Post by David Van Couvering
My understanding is that improvements are planned for the next release,
10.3.

Altering a column to change its null / not null constraint [1], as
well as the default value for a column [2], made it into the recent
10.2.1.6 release.

Alter table drop column has been checked into the trunk [3], although
there remains an issue when using alter table drop column with SQL
authorization [4] that remains unresolved. It was decided that there
was an acceptable solution for changing the data type for a column
with the combined above functionality, see the discussion in
DERBY-1515 [5].

[1] http://issues.apache.org/jira/browse/DERBY-119
[2] http://issues.apache.org/jira/browse/DERBY-1491
[3] http://issues.apache.org/jira/browse/DERBY-1489
[4] http://issues.apache.org/jira/browse/DERBY-1909
[5] http://issues.apache.org/jira/browse/DERBY-1515

Post by David Van Couvering
I'm not sure where we stand on the date for that release - anyone
have any info on the expected release date?

At the moment, I don't think there are any concrete plans for a new
feature release. Anyone interested in contributing to new features or
release planning should subscribe to the Derby development mailing
list and join the discussions there.

andrew

[Duncan Groenewald]

Been there done that... this is what I do now to make making changes
easy.


1. Define tables the normal way but don't include any constraint
definitions in the table definition.
2. Define all constraints(foreign keys etc.) as separate alter table
commands. with easy to use constraint names.

create table WalletDetail
(ID int NOT NULL,
UserName varchar(32) DEFAULT USER NOT NULL,
Detail long varchar ,
TStamp timestamp DEFAULT CURRENT_TIMESTAMP NOT
NULL,
PRIMARY KEY (ID)
);
alter table WalletDetail add CONSTRAINT WalletDetailFK01 FOREIGN KEY
(ID)
REFERENCES WalletStructure(ID);

If you subsequently want to change the table definition then do the
following:
1. Create a new table using the new table definition(in this example
I just modify the UserName field from a varchar(32) to a varchar(64).
2. Copy the data from the old table to the new one.

create table tmp_WalletDetail
(ID int NOT NULL,
UserName varchar(64) DEFAULT USER NOT NULL,
Detail long varchar ,
TStamp timestamp DEFAULT CURRENT_TIMESTAMP NOT
NULL,
PRIMARY KEY (ID)
);

insert into tmp_WalletDetail(ID, UserName,Detail)
select ID, UserName, Detail from WalletDetail ;

3. Now drop any FOREIGN KEYS defined on other tables that refer to
the original table above and drop this table. You will get errors if
you have not removed all these references. I keep the whole
definition in a text file and copy all the required commands into a
script to make it easier. Forget trying to memorise anything. I use
jEdit with the SQL plugin to send the commands directly to the database.

4. Now create a new table with the original tables name and copy
everything back. Now recreate all the FOREIGN KEYS and other
constraints that refer to this table and your back in business again.

5. Don't forget to back everything up before you start !!

Post by Alexander Schatten
I have to say, that I am really frustrated today. I evaluated Derby
for some time, and liked it for many reasons, but then I realise
it is neither possible to properly change the type of a column, nor
to rename a column or table and particularly not to drop a column.
so unfortunately this is really a show-stopper that makes me deeply
worried.
I made the mistake, that I have a column in one table declared as
timestamp and want to change it to date. so what should I do?
changing is not possible. So I thought, I create a new column, copy
the values (not easily possible either) then drop the original one
then rename the second.
I created the second one just to figure out that there is no option
to drop a column. actually, I never thought that such a fundamental
command is missing...
now this gets serious. the only option I see now is: to somehow
backup all data from this table, then drop the table, but before
this: drop all constraints refering to this table (a LOT in this
case!! foreign keys), memorize all those constreints. then recreate
this table restore the data, which is probably very, very difficult
because of the foreign key constraints, so in effect I probably
have to recreate the whole database and figure out a restore option
then.
restore is also an issue, as I use identity columns, and I was once
trying to import data and then had big problems in getting identity
columns running again.
ok, at the moment this is only a development database, and I can
recreate it (with all test data), but I am thinking: what happens
if I have to do this when there are live systems running? I cannot
easily provide a migration script for such an update. this means
that such restructurings are practically impossible or really big
undertakings with the need to shutdown databases, making backups
and restores, probably needing to write software that makes the
migration... hence really a big deal.
for what? altering one column...???? this is hard to justify!!
honestly, this is so severe, that I am considering to go back to
hsqldb, that has some other limits that are really not nice.
however, an important argument for java databases is the good
integration in the development process; and restructuring databases
is a very important issue here.
can someone give me a hint how I could solve this problem in an
easier way with derby? did I oversee something? or is it
foreseeable that a new version of Derby will come out supporting
proper alter table statements?
thank you very much!
Alex

[Alexander Schatten]

Post by Duncan Groenewald
Been there done that... this is what I do now to make making changes
easy.

thank you for your hints, I was doing something similar with huge
effort, but after this experience, I invested some hours and switched
back to hsqldb, this is---sorry to say---by far more convenient for
development purpose. similar restructuring with hsqldb took me seconds.
It is really impossible to develop with a database, where restructuring
is not feasible, and at the moment I have to say, it is even worse to
roll out with a database under such conditions.

I think I wait for the next release and will then probably change back
to Derby. the hsqldb concept with the script file is just perfect for
development issues.

I really hope for the next release of Derby...


thank you for answering!



Alex

[Paul J DeCoursey]

Post by Alexander Schatten
thank you for your hints, I was doing something similar with huge
effort, but after this experience, I invested some hours and switched
back to hsqldb, this is---sorry to say---by far more convenient for
development purpose. similar restructuring with hsqldb took me
seconds. It is really impossible to develop with a database, where
restructuring is not feasible, and at the moment I have to say, it is
even worse to roll out with a database under such conditions.
I think I wait for the next release and will then probably change back
to Derby. the hsqldb concept with the script file is just perfect for
development issues.
I really hope for the next release of Derby...

Maybe I'm in the minority but I've never had issues. While in
development I never do alter tables, I drop the entire database and
rebuild the entire thing including test data using scripts. It's far
easier and I can ensure that it will react as if a new install each
time. I also put a lot into testing and planning to be sure that my
final results will not need altering after deployment. And when it has
come up where I needed to alter something after deployment I build a new
database and copy data into it. That isn't ideal, but it's clean.

Paul

[Alexander Schatten]

yes, that works for sure, however, I think that this is far too
expensive, that I do not even want to think about it. Actually with
hsqldb such operations are a matter of seconds, not to speak of the
flexibility to work with the script file. And not to think of the case,
when I have to upgrade databases "in the wild".

however, I still hope, that Derby will support this soon, because I
really think this is an extremly important feature, as every database I
know supports this.

and I fear, many users will be damn disappointed and frustrated (as I
was) when they have some work invested into a schema and then realise
this lack of functionality (because actually I could not believe, that
any database would not support this).

And it is really a pity, as Derby has great functionality and then is
(at least for me) practically not usable by this reason.


thanks anyway



Alex

Post by Paul J DeCoursey

Post by Alexander Schatten
thank you for your hints, I was doing something similar with huge
effort, but after this experience, I invested some hours and switched
back to hsqldb, this is---sorry to say---by far more convenient for
development purpose. similar restructuring with hsqldb took me
seconds. It is really impossible to develop with a database, where
restructuring is not feasible, and at the moment I have to say, it is
even worse to roll out with a database under such conditions.
I think I wait for the next release and will then probably change
back to Derby. the hsqldb concept with the script file is just
perfect for development issues.
I really hope for the next release of Derby...

Maybe I'm in the minority but I've never had issues. While in
development I never do alter tables, I drop the entire database and
rebuild the entire thing including test data using scripts. It's far
easier and I can ensure that it will react as if a new install each
time. I also put a lot into testing and planning to be sure that my
final results will not need altering after deployment. And when it has
come up where I needed to alter something after deployment I build a
new database and copy data into it. That isn't ideal, but it's clean.
Paul

[Ryan Bobko]

Post by Duncan Groenewald
Been there done that... this is what I do now to make making changes
easy.
1. Define tables the normal way but don't include any constraint
definitions in the table definition.
2. Define all constraints(foreign keys etc.) as separate alter table
commands. with easy to use constraint names.
create table WalletDetail
(ID int NOT NULL,
UserName varchar(32) DEFAULT USER NOT NULL,
Detail long varchar ,
TStamp timestamp DEFAULT CURRENT_TIMESTAMP NOT
NULL,
PRIMARY KEY (ID)
);
alter table WalletDetail add CONSTRAINT WalletDetailFK01 FOREIGN KEY
(ID)
REFERENCES WalletStructure(ID);

Doesn't svnlook do this for you as well? At least, it'll provide the
text for you so you can split it up easier. After all, he's already got
a db he needs to work with.

[SNIP]