db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6852) Allow identity columns to cycle (as defined in SQL:2003)
Date Sat, 06 Aug 2016 20:25:20 GMT

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

Bryan Pendleton commented on DERBY-6852:
----------------------------------------

This is really exciting progress to me! I was able to get a
number of syntax variants to work (I didn't do any testing of behavior,
just of syntax, for now). This is great!

Below are some tests I tried. I hope this might provide some inspiration
for some of your own tests, to see how the new parser is behaving.

{quote}
connect 'jdbc:derby:memory:test;create=true';

-- Of the tests below, it seems to me like tests t2, t7, t10, t11, and t2
-- should have failed, but unexpectedly (to me) succeeded

create table t1( a int generated by default as identity);

create table t2( a int generated by default as identity ());

create table t3( a int generated by default as identity (start with 47));

create table t4( a int generated by default as identity (start 47));

create table t5( a int generated by default as identity (start with 47, increment by 7 cycle));

create table t6( a int generated always as identity (cycle));

create table t7( a int generated always as identity (cycle cycle));

create table t8( a int generated always as identity (cycle , increment by 4));

create table t9( a int generated always as identity (increment by 4 start with 4));

create table t10( a int generated always as identity ( start with 4 start with 8));

create table t11( a int generated always as identity ( start with 4 , , , cycle));

create table t12( a int generated always as identity (no cycle));
{quote}

> Allow identity columns to cycle (as defined in SQL:2003)
> --------------------------------------------------------
>
>                 Key: DERBY-6852
>                 URL: https://issues.apache.org/jira/browse/DERBY-6852
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Peter Hansson
>            Assignee: Danoja Dias
>         Attachments: derby-6852_1.diff, derby_6852_2.diff, derby_6852_3.diff, script.sql
>
>
> Currently when an IDENTITY column reaches its maximum value it will produce an error.
> For tables that are used as 'transaction logs' or 'event logs' it often makes sense to
let the table automatically start over with the first identity value again when the max is
reached. This would be similar to the CYCLE option on Oracle's SEQUENCE and as defined in
SQL:2003. And Derby is probably used quite often for this purpose, I guess, perhaps even more
than other RDBMSs.
> At the moment every developer have to program their own logic for this.
> I propose to introduce the CYCLE option.
> The idea of CYCLE is based on the assumption that there's been a prior cleanup in the
table rows so that it will be possible to re-use ids that have been used previously. If that
is not the case - and a rollover happens - then a duplicate value error will occur. In this
sense it can be argued that the CYCLE option will trade a _certain_ error for a _potential_
error. Most Derby users would possibly gladly accept such a bargain. In other words: This
option will greatly enhance the usability of IDENTITY columns.
> The current implementation of IDENTITY columns SQL grammar in Derby is a subset of the
SQL:2003 standard which is the first of the SQL standards to define IDENTITY columns. Interestingly
the standard also defines the CYCLE option but this was never implemented in Derby. Also see
[SQL-99 and SQL-2003 features mapped to Derby|https://wiki.apache.org/db-derby/SQLvsDerbyFeatures]
(scroll to T174).
> In other words: The proposal is simply to implement CYCLE as defined in SQL:2003.



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

Mime
View raw message