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 Sat, 09 Apr 2016 14:41: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-bb-withTests.diff

Attaching derby-6882-01-bb-withTests.diff. This is the previous patch with the following changes:

1) Adds regression test cases, including upgrade cases.

2) Corrects a mistake in the first patch: It was release 10.11 which backed identity columns
with sequence generators, not release 10.10. So the SET GENERATED clause will only be allowed
if the database is at level 10.11 or higher.

3) Improves the new error message as Bryan suggested.

The additional test cases include a case which hopefully captures the functionality needed
by Aleksei as discussed on DERBY-6876 and in email thread http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427.
This is the test case:

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

create table source_6882
(
  a int generated always as identity primary key,
  b int
);

create table target_6882
(
  a int generated always as identity primary key,
  b int
);

insert into source_6882(b) values (100), (200), (300);

-- should fail
insert into target_6882 select * from source_6882;

alter table target_6882 alter column a set generated by default;

-- should succeed
insert into target_6882 select * from source_6882;

alter table target_6882 alter column a set generated always;

-- should fail because the sequence generator starts
-- at 1 and there is already a key with that value
insert into target_6882(b) values (400);

-- so reset the start value of the generator
alter table target_6882 alter column a restart with 4;

-- now succeeds
insert into target_6882(b) values (400);

select * from target_6882 order by a;
{noformat}

I am running the full tests now.

Touches the following additional files:

-------------------

M       java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbynet.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyTesting.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyclient.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derby.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyTesting.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyclient.jar.lastcontents
M       java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derby.jar.lastcontents
A       java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_13.java
M       java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/UpgradeRun.java

Adds an upgrade test for the new language. Verifies that the SET GENERATED clause succeeds
only if the database is at level 10.11 or higher.

-------------------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/AlterTableTest.java

Adds regression test cases for the new SET GENERATED clause.


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