[
https://issues.apache.org/jira/browse/DERBY-712?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12663457#action_12663457
]
Rick Hillegas commented on DERBY-712:
-------------------------------------
Hi Suran,
Thanks for expressing interest in this feature. It would be a great addition to
Derby.
I think that a successful feature starts out with a good plan. You will want to
think about the following:
A) Figure out how much of the ANSI/ISO syntax you want to implement.
B) Understand how much work is involved.
I recommend reading a functional spec for one of our new features in order to
get a good overview of what you will need to think about. I recommend taking a
look at the functional spec for generated columns. See
http://issues.apache.org/jira/browse/DERBY-481
If you don't already have a copy of the 2008 SQL standard, you can get it here:
http://www.wiscorp.com/SQLStandards.html
More comments below.
------------ A ----------------
I think that the right place to start is to indicate how much of the ANSI/ISO
syntax you want to implement.
The key syntax sections of the 2008 SQL Standard seem to be in part 2:
4.21 Sequence generators (The overview of how sequence generators behave)
6.13 <next value expression> (The syntax for selecting the next value from a
sequence generator)
11.62<sequence generator definition> (The syntax for declaring a sequence
generator)
11.63<alter sequence generator statement> (The syntax for changing a sequence
generator)
11.64<drop sequence generator statement> (The syntax for deleting a sequence
generator)
I recommend starting out with a modest plan, which you can grow incrementally
over time. Start out with the simplest create/drop syntax:
CREATE SEQUENCE schemaName.sequenceGeneratorName
DROP SEQUENCE schemaName.sequenceGeneratorName RESTRICT
That is,
1) Don't start out implementing the fancy clauses on the CREATE SEQUENCE
statement.
2) Don't bother implementing ALTER SEQUENCE right away.
3) Don't bother implementing DROP SEQUENCE ... CASCADE right away.
When you add support for
NEXT VALUE FOR schemaName.sequenceGeneratorName
you will need to prevent this clause from appearing in a number of contexts.
Besides the syntax, catalog support will be the other major piece of the
external api for your feature. You will probably end up adding a new system
catalog, SYS.SYSSEQUENCES. A good place to start here is to study the layout of
the SEQUENCES table in part 11 of the 2008 SQL standard, section 6.42.
I don't think this feature has any implications for Derby's implementations of
JDBC metadata.
------------ B ----------------
If you read one of our recent functional specs, you will see that you will need
to plan for the following pieces of work. Other people may volunteer to do some
of this work. At a minimum, I'm sure the community will give you plenty of
advice:
1) Syntax (discussed above)
2) Catalog support (discussed above)
3) Dependency management
4) Upgrade logic
5) dblook support for recreating your CREATE SEQUENCE statements
6) User documentation
> Support for sequences
> ---------------------
>
> Key: DERBY-712
> URL: https://issues.apache.org/jira/browse/DERBY-712
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Environment: feature request
> Reporter: Tony Dahbura
> Assignee: Suran Jayathilaka
>
> Would like to see support added for sequences. This would permit a select
> against the sequence to always obtain a ever increasing/decreasing value.
> The identity column works fine but there are times for applications where the
> application needs to obtain the sequence number and use it prior to the
> database write. Subsequent calls to the table/column would result in a new
> number on each call.
> SQL such as the following:
> SELECT NEXT VALUE FOR sequence_name FROM sometable ; would result in a next
> value.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
