Sequences: Constraints for optional arguments

[Suran Jayathilaka <su...@gmail.com>]

Hi Rick,

I've added the changes for the bind part of the optional clauses in Sequence
creation. The argument values now get saved in the catalog table.

In CreateSequenceNode's bindStatement() method, what kind of constraints
should be enforced on the optional arguments, datatype in particular?
And for the start value option, if neither that nor min value is specified,
would the default be "1" or the min value of the sequence's data type?

Also, in DBMSNodes.properties, I noticed that there are 2 values for the
property key "derby.module.cloudscapenodes.ci".

Thanks!
Suran

Re: Sequences: Constraints for optional arguments

[Rick Hillegas <Ri...@Sun.COM>]

Suran Jayathilaka wrote:
> Hi Rick,
>
> Thank you for your reply.
> Since the datatype of the sequence object can be either Smallint, Int 
> or Bigint, when the datatype is SMALLINT or INTEGER, is it necessary 
> to check whether the arguments for Min-value, Max-value,Initial-Value 
> etc. (if explicitly provided),  are within range for the given data type?
> e.g. For a SMALLINT datatyped Sequence object, check whether minValue 
> >= Short.MIN_VALUE and maxValue <= Short.MAX_VALUE
Yes, please. That is my understanding of the ANSI/ISO spec, part 2, 
section 9.24 <Creation of a sequence generator>, syntax rule 9.
>
> If so, is the init() method or the bindStatement() method of 
> CreateSequenceNode the correct place to do it? What kind of exception 
> should be thrown in such a case with the exception message ID 22003?
This kind of check is typically done during the bind() phase. I would 
recommend performing these checks in bindStatement().

Thanks,
-Rick
>
> Thanks!
> Suran
>
>

Re: Sequences: Constraints for optional arguments

[Suran Jayathilaka <su...@gmail.com>]

Hi Rick,

Thank you for your reply.
Since the datatype of the sequence object can be either Smallint, Int or
Bigint, when the datatype is SMALLINT or INTEGER, is it necessary to check
whether the arguments for Min-value, Max-value,Initial-Value etc. (if
explicitly provided),  are within range for the given data type?
e.g. For a SMALLINT datatyped Sequence object, check whether minValue >=
Short.MIN_VALUE and maxValue <= Short.MAX_VALUE

If so, is the init() method or the bindStatement() method of
CreateSequenceNode the correct place to do it? What kind of exception should
be thrown in such a case with the exception message ID 22003?

Thanks!
Suran

Re: Sequences: Constraints for optional arguments

[Rick Hillegas <Ri...@Sun.COM>]

Hi Suran,

Thanks for tackling this work. Some comments inline...

Suran Jayathilaka wrote:
> Hi Rick,
>
> I've added the changes for the bind part of the optional clauses in 
> Sequence creation. The argument values now get saved in the catalog 
> table.
>
> In CreateSequenceNode's bindStatement() method, what kind of 
> constraints should be enforced on the optional arguments, datatype in 
> particular?
I think there is a fairly complete set of constraints in the functional 
spec attached to DERBY-712. Look for the section titled "New CREATE 
SEQUENCE Syntax". Let me know if that doesn't seem complete enough and I 
will dive into the SQL Standard for more guidance. Concerning the 
datatype, the parser should have limited the datatype to just the 
integer types supported by Derby, so you shouldn't have to verify the 
datatype--of course, this deserves a regression test!
> And for the start value option, if neither that nor min value is 
> specified, would the default be "1" or the min value of the sequence's 
> data type?
According to the constraints in the functional spec, the default initial 
value is MINVALUE if INCREMENT is positive and MAXVALUE if INCREMENT is 
negative. If MINVALUE is not specified, it defaults to the smallest 
(most negative) integer which fits in the data type of the sequence 
generator. Similarly, if MAXVALUE is not specified, it defaults to the 
largest positive integer which fits in the data type.

>
> Also, in DBMSNodes.properties, I noticed that there are 2 values for 
> the property key "derby.module.cloudscapenodes.ci 
> <http://derby.module.cloudscapenodes.ci>".
I see that CastNode and CoalesceFunctionNode share this key. This looks 
like a mistake to me.

Thanks,
-Rick
>
> Thanks!
> Suran
>