db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
Date Thu, 07 Apr 2016 01:59:25 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-6882:
---------------------------------
    Attachment: derby-6882-01-aa-initialCode.diff

Attaching derby-6882-01-aa-initialCode.diff. This patch adds the new syntax. This patch is
not ready for commit. Tests need to be written and regression tests need to be run. I have
only bothered to implement this feature for the new-style identity columns which were introduced
in Derby 10.10. That is, the ones which are backed by sequence generators.

With this patch, the following script runs correctly:

{noformat}
connect 'jdbc:derby:memory:db;create=true';

create table t
(
  a int generated always as identity,
  b int
);

create table s
(
  a int generated by default as identity,
  b int
);

insert into t(b) values (100);
insert into s(b) values (100);

-- fails
insert into t(a, b) values (-1, -100);
-- succeeds
insert into s(a, b) values (-1, -100);

alter table t alter column a set generated by default;
alter table s alter column a set generated always;

-- succeeds
insert into t(a, b) values (-2, -200);
-- fails
insert into s(a, b) values (-2, -200);

insert into t(b) values (200);
insert into s(b) values (200);

alter table t alter column a set generated always;
alter table s alter column a set generated by default;

-- fails
insert into t(a, b) values (-3, -300);
-- succeeds
insert into s(a, b) values (-3, -300);

insert into t(b) values (300);
insert into s(b) values (300);

select * from t order by a;
select * from s order by a;
{noformat}


> 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
>
>
> 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