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