db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Aleksei Kovura (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
Date Sat, 23 Apr 2016 04:18:12 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15252362#comment-15252362
] 

Aleksei Kovura edited comment on DERBY-6882 at 4/23/16 4:17 AM:
----------------------------------------------------------------

Some assistance would be nice actually. I tried checking out derby code:
mkdir derby ; svn co https://svn.apache.org/repos/asf/db/derby/code/trunk derby

... and switching to a revision you specified in the next post (r1740299):
svn up -r1740299

It builds successfully, but I end up with "10.13.0.0 alpha" version of jars in jars/sane/.
It's supposed to be some sort of 10.12.xx, right?
------------- EDIT: -------------
It seems JIRA is still in Lockdown, so I'll edit this comment because I can't post a new one.
I've built 10.12 branch. Below is an example where this new functionality doesn't quite work
as expected. If after this example I do ALTER TABLE ACTIONS ALTER COLUMN id RESTART WITH 5;
then the table starts working normally. It would be more handy if Derby automatically handled
ID gaps and skipped already existing IDs though. If that won't be implemented for whatever
reason, then it should be documented that users should restart ID generation according to
their current max ID value, increment and so on (I can contribute to documention). What do
you think?
------- USAGE SCENARIO --------
java -jar derbyrun.jar ij
ij version 10.12
ij> connect 'jdbc:derby:sample_alter;create=true';
ij> CREATE TABLE APP."ACTIONS" (id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT
PK_ACTION_ID PRIMARY KEY,description VARCHAR(128) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action1');
1 row inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
1 row inserted/updated/deleted
ij> select * from actions;
ID         |DESCRIPTION                                                                  
                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
1          |action1                                                                      
                                                  
2          |action2                                                                      
                                                  

2 rows selected
ij> ALTER TABLE ACTIONS ALTER COLUMN id SET GENERATED BY DEFAULT;
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (id, description) VALUES (4, 'action4');
1 row inserted/updated/deleted
ij> select * from actions;
ID         |DESCRIPTION                                                                  
                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
1          |action1                                                                      
                                                  
2          |action2                                                                      
                                                  
4          |action4                                                                      
                                                  

3 rows selected
ij> ALTER TABLE ACTIONS ALTER COLUMN id SET GENERATED ALWAYS;
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
1 row inserted/updated/deleted
ij> select * from actions;
ID         |DESCRIPTION                                                                  
                                                  
--------------------------------------------------------------------------------------------------------------------------------------------
1          |action1                                                                      
                                                  
2          |action2                                                                      
                                                  
4          |action4                                                                      
                                                  
3          |action2                                                                      
                                                  

4 rows selected
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value
in a unique or primary key constraint or unique index identified by 'PK_ACTION_ID' defined
on 'ACTIONS'.



was (Author: alex k.):
Some assistance would be nice actually. I tried checking out derby code:
mkdir derby ; svn co https://svn.apache.org/repos/asf/db/derby/code/trunk derby

... and switching to a revision you specified in the next post (r1740299):
svn up -r1740299

It builds successfully, but I end up with "10.13.0.0 alpha" version of jars in jars/sane/.
It's supposed to be some sort of 10.12.xx, right?

> Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
> --------------------------------------------------------------------------------------
>
>                 Key: DERBY-6882
>                 URL: https://issues.apache.org/jira/browse/DERBY-6882
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.12.1.1
>            Reporter: Aleksei Kovura
>            Assignee: Rick Hillegas
>              Labels: features
>         Attachments: derby-6882-01-aa-initialCode.diff, derby-6882-01-bb-withTests.diff,
derby-6882-02-aa-port_1738368_to_10.12.diff
>
>
> I'm trying to import data from another Derby database with foreignViews tool because
system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876).
Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing
Derby to generate new IDs in generated columns will break relationships between tables (old
tables have counter gaps there due to deletes - IDs won't match).
> For a clean import without breaking DDL information in DB version control I would like
to be able to switch between generated types as follows:
>  
> ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY]
> ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY]



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message