Return-Path: Delivered-To: apmail-db-derby-commits-archive@www.apache.org Received: (qmail 66958 invoked from network); 1 Feb 2010 19:00:10 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 1 Feb 2010 19:00:10 -0000 Received: (qmail 62374 invoked by uid 500); 1 Feb 2010 19:00:10 -0000 Delivered-To: apmail-db-derby-commits-archive@db.apache.org Received: (qmail 62327 invoked by uid 500); 1 Feb 2010 19:00:10 -0000 Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: "Derby Development" List-Id: Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 62318 invoked by uid 99); 1 Feb 2010 19:00:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 Feb 2010 19:00:10 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 01 Feb 2010 19:00:06 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 0EB0723889BB; Mon, 1 Feb 2010 18:59:44 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r905369 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sql/execute/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functi... Date: Mon, 01 Feb 2010 18:59:43 -0000 To: derby-commits@db.apache.org From: rhillegas@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20100201185944.0EB0723889BB@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: rhillegas Date: Mon Feb 1 18:59:43 2010 New Revision: 905369 URL: http://svn.apache.org/viewvc?rev=905369&view=rev Log: DERBY-712: Committed Suran's patch createseq_args_bind_b.diff, making it possible to create sequences with different datatypes, boundaries, steps, and cycling. Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateSequenceNode.java Mon Feb 1 18:59:43 2010 @@ -71,17 +71,42 @@ Object minValue, Object cycle ) throws StandardException { + _sequenceName = (TableName) sequenceName; initAndCheck(_sequenceName); - _dataType = (DataTypeDescriptor) dataType; - _initialValue = (Long) initialValue; - _stepValue = (Long) stepValue; - _maxValue = (Long) maxValue; - _minValue = (Long) minValue; - _cycle = (Boolean) cycle; + if (dataType != null) { + _dataType = (DataTypeDescriptor) dataType; + } else { + _dataType = DataTypeDescriptor.INTEGER; + } + + _stepValue = (stepValue != null ? (Long) stepValue : new Long(1)); + + if (_dataType.getTypeId().equals(TypeId.SMALLINT_ID)) { + _minValue = (minValue != null ? (Long) minValue : new Long(Short.MIN_VALUE)); + _maxValue = (maxValue != null ? (Long) maxValue : new Long(Short.MAX_VALUE)); + } else if (_dataType.getTypeId().equals(TypeId.INTEGER_ID)) { + _minValue = (minValue != null ? (Long) minValue : new Long(Integer.MIN_VALUE)); + _maxValue = (maxValue != null ? (Long) maxValue : new Long(Integer.MAX_VALUE)); + } else { + // Could only be BIGINT + _minValue = (minValue != null ? (Long) minValue : new Long(Long.MIN_VALUE)); + _maxValue = (maxValue != null ? (Long) maxValue : new Long(Long.MAX_VALUE)); + } + + if (initialValue != null) { + _initialValue = (Long) initialValue; + } else { + if (_stepValue.longValue() > 0L) { + _initialValue = _minValue; + } else { + _initialValue = _maxValue; + } + } + _cycle = (cycle != null ? (Boolean) cycle : Boolean.FALSE); - // automcatically create the schema if it doesn't exist + // automatically create the schema if it doesn't exist implicitCreateSchema = true; } @@ -113,19 +138,94 @@ // this method also compiles permissions checks SchemaDescriptor sd = getSchemaDescriptor(); - // set the default schema name if the user did not explicitly specify a schema + // set the default schema name if the user did not explicitly specify a schema if (_sequenceName.getSchemaName() == null) { _sequenceName.setSchemaName(sd.getSchemaName()); } - // Right now we only support vanilla sequences - if ( (_dataType != null) && ( !_dataType.getTypeId().equals( TypeId.INTEGER_ID ) ) ) { throw unimplementedFeature(); } - if ( (_initialValue != null) && ( _initialValue.longValue() != -2147483648L ) ) { throw unimplementedFeature(); } - if ( (_stepValue != null) && ( _stepValue.longValue() != 1L ) ) { throw unimplementedFeature(); } - if ( (_maxValue != null) && ( _maxValue.longValue() != 2147483647L ) ) { throw unimplementedFeature(); } - if ( (_minValue != null) && ( _minValue.longValue() != -2147483648L ) ) { throw unimplementedFeature(); } - if ( (_cycle != null) && ( _cycle != Boolean.FALSE ) ) { throw unimplementedFeature(); } - + if (_dataType.getTypeId().equals(TypeId.SMALLINT_ID)) { + if (_minValue.longValue() < Short.MIN_VALUE || _minValue.longValue() >= Short.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MINVALUE", + "SMALLINT", + Short.MIN_VALUE + "", + Short.MAX_VALUE + ""); + } + if (_maxValue.longValue() <= Short.MIN_VALUE || _maxValue.longValue() > Short.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MAXVALUE", + "SMALLINT", + Short.MIN_VALUE + "", + Short.MAX_VALUE + ""); + } + } else if (_dataType.getTypeId().equals(TypeId.INTEGER_ID)) { + if (_minValue.longValue() < Integer.MIN_VALUE || _minValue.longValue() >= Integer.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MINVALUE", + "INTEGER", + Integer.MIN_VALUE + "", + Integer.MAX_VALUE + ""); + } + if (_maxValue.longValue() <= Integer.MIN_VALUE || _maxValue.longValue() > Integer.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MAXVALUE", + "INTEGER", + Integer.MIN_VALUE + "", + Integer.MAX_VALUE + ""); + } + } else { + // BIGINT + if (_minValue.longValue() < Long.MIN_VALUE || _minValue.longValue() >= Long.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MINVALUE", + "BIGINT", + Long.MIN_VALUE + "", + Long.MAX_VALUE + ""); + } + if (_maxValue.longValue() <= Long.MIN_VALUE || _maxValue.longValue() > Long.MAX_VALUE) { + throw StandardException.newException( + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "MAXVALUE", + "BIGINT", + Long.MIN_VALUE + "", + Long.MAX_VALUE + ""); + } + } + + if (_minValue.longValue() >= _maxValue.longValue()) { + throw StandardException.newException( + SQLState.LANG_SEQ_MIN_EXCEEDS_MAX, + _minValue.toString(), + _maxValue.toString()); + } + + if (_initialValue.longValue() < _minValue.longValue() || _initialValue.longValue() > _maxValue.longValue()) { + throw StandardException.newException( + SQLState.LANG_SEQ_INVALID_START, + _initialValue.toString(), + _minValue.toString(), + _maxValue.toString()); + } + + if (_stepValue.longValue() == 0L) { + throw StandardException.newException( + SQLState.LANG_SEQ_INCREMENT_ZERO); + } + + if (_stepValue.longValue() > _maxValue.longValue() + || _stepValue.longValue() < _minValue.longValue()) { + throw StandardException.newException( + SQLState.LANG_SEQ_INCREMENT_OUT_OF_RANGE, + _stepValue.toString(), + _minValue.toString(), + _maxValue.toString()); + } + } public String statementToString() { @@ -141,14 +241,16 @@ * Thrown on failure */ public ConstantAction makeConstantAction() { - return getGenericConstantActionFactory(). - getCreateSequenceConstantAction(_sequenceName); + return getGenericConstantActionFactory(). + getCreateSequenceConstantAction( + _sequenceName, + _dataType, + _initialValue.longValue(), + _stepValue.longValue(), + _maxValue.longValue(), + _minValue.longValue(), + _cycle.booleanValue()); } - /** Report an unimplemented feature */ - private StandardException unimplementedFeature() - { - return StandardException.newException( SQLState.BTREE_UNIMPLEMENTED_FEATURE ); - } } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/CreateSequenceConstantAction.java Mon Feb 1 18:59:43 2010 @@ -39,8 +39,14 @@ */ class CreateSequenceConstantAction extends DDLConstantAction { - private String sequenceName; - private String schemaName; + private String _sequenceName; + private String _schemaName; + private DataTypeDescriptor _dataType; + private long _initialValue; + private long _stepValue; + private long _maxValue; + private long _minValue; + private boolean _cycle; // CONSTRUCTORS /** @@ -48,10 +54,33 @@ * When executed, will create a sequence by the given name. * * @param sequenceName The name of the sequence being created + * @param dataType Exact numeric type of the new sequence + * @param initialValue Starting value + * @param stepValue Increment amount + * @param maxValue Largest value returned by the sequence generator + * @param minValue Smallest value returned by the sequence generator + * @param cycle True if the generator should wrap around, false otherwise */ - public CreateSequenceConstantAction(String schemaName, String sequenceName) { - this.schemaName = schemaName; - this.sequenceName = sequenceName; + public CreateSequenceConstantAction + ( + String schemaName, + String sequenceName, + DataTypeDescriptor dataType, + long initialValue, + long stepValue, + long maxValue, + long minValue, + boolean cycle + ) + { + this._schemaName = schemaName; + this._sequenceName = sequenceName; + this._dataType = dataType; + this._initialValue = initialValue; + this._stepValue = stepValue; + this._maxValue = maxValue; + this._minValue = minValue; + this._cycle = cycle; } // INTERFACE METHODS @@ -74,22 +103,30 @@ dd.startWriting(lcc); - schemaDescriptor = DDLConstantAction.getSchemaDescriptorForCreate(dd, activation, schemaName); + schemaDescriptor = DDLConstantAction.getSchemaDescriptorForCreate(dd, activation, _schemaName); // // Check if this sequence already exists. If it does, throw. // - SequenceDescriptor seqDef = dd.getSequenceDescriptor(schemaDescriptor, sequenceName); + SequenceDescriptor seqDef = dd.getSequenceDescriptor(schemaDescriptor, _sequenceName); if (seqDef != null) { throw StandardException. newException(SQLState.LANG_OBJECT_ALREADY_EXISTS, - seqDef.getDescriptorType(), sequenceName); + seqDef.getDescriptorType(), _sequenceName); } - seqDef = ddg.newSequenceDescriptor(schemaDescriptor, + seqDef = ddg.newSequenceDescriptor( + schemaDescriptor, dd.getUUIDFactory().createUUID(), - sequenceName, DataTypeDescriptor.INTEGER_NOT_NULL, Integer.MIN_VALUE, Integer.MIN_VALUE, Integer.MIN_VALUE, Integer.MAX_VALUE, 1, false); // is definition + _sequenceName, + _dataType, + _initialValue, // current value + _initialValue, + _minValue, + _maxValue, + _stepValue, + _cycle); // whether the sequence can wrap-around dd.addDescriptor(seqDef, null, // parent @@ -103,6 +140,6 @@ public String toString() { // Do not put this under SanityManager.DEBUG - it is needed for // error reporting. - return "CREATE SEQUENCE " + sequenceName; + return "CREATE SEQUENCE " + _sequenceName; } } Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/execute/GenericConstantActionFactory.java Mon Feb 1 18:59:43 2010 @@ -47,6 +47,7 @@ import org.apache.derby.iapi.services.sanity.SanityManager; import org.apache.derby.iapi.types.RowLocation; +import org.apache.derby.iapi.types.DataTypeDescriptor; import org.apache.derby.catalog.UUID; import org.apache.derby.catalog.AliasInfo; @@ -323,10 +324,32 @@ * Make the ConstantAction for a CREATE SEQUENCE statement. * * @param sequenceName Name of sequence. - */ - public ConstantAction getCreateSequenceConstantAction(TableName sequenceName) + * @param dataType + * @param initialValue + * @param stepValue + * @param maxValue + * @param minValue + * @param cycle + */ + public ConstantAction getCreateSequenceConstantAction + ( + TableName sequenceName, + DataTypeDescriptor dataType, + long initialValue, + long stepValue, + long maxValue, + long minValue, + boolean cycle + ) { - return new CreateSequenceConstantAction(sequenceName.getSchemaName(), sequenceName.getTableName()); + return new CreateSequenceConstantAction(sequenceName.getSchemaName(), + sequenceName.getTableName(), + dataType, + initialValue, + stepValue, + maxValue, + minValue, + cycle); } /** Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original) +++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Mon Feb 1 18:59:43 2010 @@ -2074,6 +2074,43 @@ + 42XAC + 'INCREMENT BY' value can not be zero. + + + + 42XAD + Invalid 'INCREMENT BY' value '{0}'. Must be between 'MINVALUE: {1}' and 'MAXVALUE: {2}'. + stepValue + minValue + maxValue + + + + 42XAE + '{0}' value out of range of datatype '{1}'. Must be between '{2}' and '{3}'. + argName + datatypeName + minValue + maxValue + + + + 42XAF + Invalid 'MINVALUE' value '{0}'. Must be smaller than 'MAXVALUE: {1}'. + minValue + maxValue + + + + 42XAG + Invalid 'START WITH' value '{0}'. Must be between '{1}' and '{2}'. + startValue + minValue + maxValue + + + 42Y00 Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition and thus cannot be used as an aggregate expression. className Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original) +++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Mon Feb 1 18:59:43 2010 @@ -911,7 +911,12 @@ String LANG_NEEDS_DATATYPE = "42XA9"; String LANG_GEN_COL_BEFORE_TRIG = "42XAA"; String LANG_NOT_NULL_NEEDS_DATATYPE = "42XAB"; - String LANG_INVALID_USER_AGGREGATE_DEFINITION2 = "42Y00"; + String LANG_SEQ_INCREMENT_ZERO = "42XAC"; + String LANG_SEQ_INCREMENT_OUT_OF_RANGE = "42XAD"; + String LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE = "42XAE"; + String LANG_SEQ_MIN_EXCEEDS_MAX = "42XAF"; + String LANG_SEQ_INVALID_START = "42XAG"; + String LANG_INVALID_USER_AGGREGATE_DEFINITION2 = "42Y00"; String LANG_INVALID_CHECK_CONSTRAINT = "42Y01"; // String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE = "42Y02"; String LANG_NO_SUCH_METHOD_ALIAS = "42Y03.S.0"; Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test.out Mon Feb 1 18:59:43 2010 @@ -5789,7 +5789,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 @@ -5877,7 +5877,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 @@ -5945,7 +5945,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/dblook_test_territory.out Mon Feb 1 18:59:43 2010 @@ -5789,7 +5789,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 @@ -5877,7 +5877,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 @@ -5945,7 +5945,7 @@ -- DDL Statements for sequences -- ---------------------------------------------- CREATE SEQUENCE "APP"."SEQUENCE_1" - AS INTEGER + AS INTEGER START WITH -2147483648 INCREMENT BY 1 MAXVALUE 2147483647 Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java?rev=905369&r1=905368&r2=905369&view=diff ============================================================================== --- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java (original) +++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java Mon Feb 1 18:59:43 2010 @@ -32,11 +32,12 @@ import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; import org.apache.derbyTesting.junit.JDBC; import org.apache.derbyTesting.junit.TestConfiguration; +import org.apache.derby.iapi.reference.SQLState; /** * Test sequences. */ -public class SequenceTest extends BaseJDBCTestCase { +public class SequenceTest extends GeneratedColumnsHelper { private static final String TEST_DBO = "TEST_DBO"; private static final String ALPHA = "ALPHA"; @@ -194,6 +195,98 @@ adminCon.close(); } + public void testCreateSequenceWithArguments() throws Exception { + Connection alphaCon = openUserConnection(ALPHA); + + goodStatement(alphaCon, + "CREATE SEQUENCE small1 AS SMALLINT START WITH 0 INCREMENT BY 1"); + + goodStatement(alphaCon, + "CREATE SEQUENCE small2 AS SMALLINT START WITH " + Short.MIN_VALUE + + " MAXVALUE " + Short.MAX_VALUE); + + goodStatement(alphaCon, + "CREATE SEQUENCE small3 AS SMALLINT START WITH 1200" + + " INCREMENT BY -5 MAXVALUE 32000 NO MINVALUE CYCLE"); + + // maxvalue out of range + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "CREATE SEQUENCE small3 AS SMALLINT START WITH " + Short.MIN_VALUE + + " MAXVALUE " + Integer.MAX_VALUE); + + // start with out of range + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_INVALID_START, + "CREATE SEQUENCE small4 AS SMALLINT START WITH " + Integer.MIN_VALUE); + + // minvalue larger than maxvalue negative + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_MIN_EXCEEDS_MAX, + "CREATE SEQUENCE small5 AS SMALLINT MAXVALUE -20000 MINVALUE -1"); + + goodStatement(alphaCon, + "CREATE SEQUENCE int1 AS INTEGER START WITH " + Integer.MIN_VALUE + " INCREMENT BY -10 CYCLE"); + + goodStatement(alphaCon, + "CREATE SEQUENCE int2 AS INTEGER INCREMENT BY 5" + + " MAXVALUE " + Integer.MAX_VALUE); + + goodStatement(alphaCon, + "CREATE SEQUENCE int3 AS INTEGER START WITH 1200 INCREMENT BY 5 " + + "NO MAXVALUE MINVALUE -320000 CYCLE"); + + // minvalue out of range + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "CREATE SEQUENCE int4 AS INTEGER START WITH " + Integer.MIN_VALUE + + " MAXVALUE " + Short.MAX_VALUE + + " MINVALUE " + Long.MIN_VALUE); + + // increment out of range + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_INCREMENT_OUT_OF_RANGE, + "CREATE SEQUENCE int5 AS INTEGER INCREMENT BY " + Long.MAX_VALUE); + + // increment 0 + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_INCREMENT_ZERO, + "CREATE SEQUENCE int5 AS INTEGER INCREMENT BY 0"); + + // increment too big + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_INCREMENT_OUT_OF_RANGE, + "CREATE SEQUENCE int6 AS INTEGER INCREMENT BY " + Long.MAX_VALUE); + + goodStatement(alphaCon, + "CREATE SEQUENCE long1 AS BIGINT START WITH " + Long.MIN_VALUE + " INCREMENT BY -100 NO CYCLE"); + + goodStatement(alphaCon, + "CREATE SEQUENCE long2 AS BIGINT INCREMENT BY 25" + + " MAXVALUE " + Integer.MAX_VALUE); + + goodStatement(alphaCon, + "CREATE SEQUENCE long3 AS BIGINT START WITH 0 INCREMENT BY 5 " + + "NO MAXVALUE MINVALUE " + Long.MIN_VALUE + " CYCLE"); + + // invalid minvalue + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_ARG_OUT_OF_DATATYPE_RANGE, + "CREATE SEQUENCE long4 AS BIGINT START WITH " + Integer.MAX_VALUE + + " MINVALUE " + Long.MAX_VALUE); + + // minvalue larger than maxvalue + expectCompilationError(alphaCon, + SQLState.LANG_SEQ_MIN_EXCEEDS_MAX, + "CREATE SEQUENCE long5 AS BIGINT START WITH 0 MAXVALUE 100000 MINVALUE 100001"); + + // should fail for non-int TYPES + expectCompilationError(alphaCon, + SQLState.LANG_SYNTAX_ERROR, + "CREATE SEQUENCE char1 AS CHAR INCREMENT BY 1"); + + } + /** * initial test for next value * @throws SQLException on error @@ -204,4 +297,5 @@ s.execute("SELECT NEXT VALUE FOR mySeq1 from sys.systables"); } + }