Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Date: Sat, 13 Aug 2016 17:08:22 +0000 (UTC)
From: "ASF subversion and git services (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Message-ID: <JIRA.12928849.1452333666000.298985.1471108102514@Atlassian.JIRA>
In-Reply-To: <JIRA.12928849.1452333666000@Atlassian.JIRA>
References: <JIRA.12928849.1452333666000@Atlassian.JIRA> <JIRA.12928849.1452333666437@arcas>
Subject: [jira] [Commented] (DERBY-6852) Allow identity columns to cycle (as
 defined in SQL:2003)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
archived-at: Sat, 13 Aug 2016 17:08:25 -0000


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

ASF subversion and git services commented on DERBY-6852:
--------------------------------------------------------

Commit 1756287 from [~bryanpendleton] in branch 'code/trunk'
[ https://svn.apache.org/r1756287 ]

DERBY-6852: Allow identity columns to cycle

This patch was contributed by Danoja Dias (danojadias at gmail dot com)

This change introduces the new CYCLE keyword to the syntax of a
generated identity column. The CYCLE keyword, if specified, causes Derby
to select the CYCLE option in the sequence object that is created
internally to implement the identity column.

In turn, this means that values can be generated for the identity column
beyond the maximum value for the column's datatype; when that maximum
is reached, the sequence "cycles" and begins over again, (re-)generating
the values all over again.

Note that, as part of this change, when a Derby sequence cycles, it now
cycles to its minimum/maximum value (depending on whether it has a 
positive increment or a negative increment) rather than to its start
value; we believe this is improved behavior and is still standards-compliant.

With this change, the basic behavior is operational and there are tests
to demonstrate this. Various syntax checks are made, and the underlying
sequence is created correctly.

There is follow-up work to be done, which will be addressed possibly as
separate projects; we believe the basic feature is certainly usable
as of this change (and once we add documentation for it).

> 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, derby6852doc.diff, derby_6852_2.diff, derby_6852_3.diff, derby_6852_4.diff, failTests.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)