[jira] Commented: (DERBY-712) Support for sequences

["Rick Hillegas (JIRA)" <ji...@apache.org>]

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

Rick Hillegas commented on DERBY-712:
-------------------------------------

Thanks for the new patch, Suran. It looks very good. First some responses to your questions:

>What is the correct SQLState code to be used for Exception messages in cases like the INCREMENT BY value being given as '0' or the MINVALUE being larger than MAXVALUE? In this patch I used message ID "22008.S" (LANG_INVALID_FUNCTION_ARGUMENT) for such scenarios.

You will need to create some new codes in org.apache.derby.shared.common.reference.SQLState and some corresponding new messages in trunk/java/engine/org/apache/derby/loc/messages.xml

For the codes themselves, I can't find any guidance in the ANSI/ISO standard beyond the general rule that SQLStates for syntax errors should begin with 42. I see that there is an empty range of Derby SQLStates starting with 42XAC. I recommend that you allocate your codes out of that range.


>Also, I noticed that the START WITH and INCREMENT BY options are order bound, i.e. they have to appear just after the DATATYPE, and INCREMENT BY has to follow START WITH if both are present. Is this a valid constraint or does the parser code need to be modified?

You are right, Derby is being over-strict here. The ANSI/ISO standard allows the optional clauses to appear in any order. The only restriction is that a given option can be specified at most once. You are welcome to relax Derby's restrictions here. You may want to use the CREATE FUNCTION/PROCEDURE syntax as a template for how to do this. However, I would recommend doing this work in another patch after you have finished the bind() logic for the optional clauses.


Concerning the patch:

In CreateSequenceNode, I like the way that you put the defaulting logic in the initializer and left validation to the bind() stage.

As noted above, you will want to create specific error messages for the validation checks you perform in CreateSequenceNode.bindStatement(). The generic error messages don't give the user enough information to figure out what the problem is.

It seems to me that you only need to compare max, min, and initial values in one place, rather than duplicating this check for each datatype.

Thanks!

> Support for sequences
> ---------------------
>
>                 Key: DERBY-712
>                 URL: https://issues.apache.org/jira/browse/DERBY-712
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>         Environment: feature request 
>            Reporter: Tony Dahbura
>            Assignee: Suran Jayathilaka
>             Fix For: 10.6.0.0
>
>         Attachments: altertable.diff, catalogs_a.patch, catalogs_b.patch, catalogs_c.patch, catalogs_d.patch, catalogs_e.patch, catalogs_f.patch, catalogs_f_2.patch, catalogs_g.diff, catalogs_h.diff, create_drop_sequence_a.patch, create_drop_sequence_b.patch, create_drop_sequence_c.patch, create_drop_sequence_d.patch, create_sequence_a.patch, createseq_args_bind_a.diff, derby-712-02-aa-privilegeNodeCleanup.diff, derby-712-03-aa-usagePrivilege.diff, derby-712-03-ac-usagePrivilege.diff, derby-712-04-aa-dblook.diff, SequenceGenerator.html, sequences_next_value_a.patch
>
>
> 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.