Author: rhillegas
Date: Fri Feb 12 22:07:23 2010
New Revision: 909633

URL: http://svn.apache.org/viewvc?rev=909633&view=rev
Log:
DERBY-4539: Make it possible to state the optional clauses of the CREATE SEQUENCE statement in an order.

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/compile/sqlgrammar.jj
    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/tests/lang/GeneratedColumnsHelper.java
    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=909633&r1=909632&r2=909633&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 Fri Feb 12 22:07:23 2010
@@ -217,15 +217,6 @@
                     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() {

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=909633&r1=909632&r2=909633&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Fri Feb 12 22:07:23 2010
@@ -205,6 +205,15 @@
     private static final int INTERSECT_OP = 5;
     private static final int INTERSECT_ALL_OP = 6;
 
+    // indexes into array of optional clauses for CREATE SEQUENCE statement
+    private static final int IDX_DATA_TYPE = 0;
+    private static final int IDX_START_WITH_OPTION = IDX_DATA_TYPE + 1;
+    private static final int IDX_INCREMENT_BY_OPTION = IDX_START_WITH_OPTION + 1;
+    private static final int IDX_MAX_VALUE_OPTION = IDX_INCREMENT_BY_OPTION + 1;
+    private static final int IDX_MIN_VALUE_OPTION = IDX_MAX_VALUE_OPTION + 1;
+    private static final int IDX_CYCLE_OPTION = IDX_MIN_VALUE_OPTION + 1;
+    private static final int IDX_OPTION_COUNT = IDX_CYCLE_OPTION + 1;
+
 	private Object[]					paramDefaults;
 	private String						statementSQLText;
 	private NodeFactory					nodeFactory;
@@ -10079,27 +10088,38 @@
 	Long maxValue = null;
 	Long minValue = null;
     Boolean cycle = Boolean.FALSE;
+    Object[] optionalClauses = new Object[ IDX_OPTION_COUNT ];
 }
 {
 	<SEQUENCE> qualifiedSequenceName = qualifiedName(Limits.MAX_IDENTIFIER_LENGTH)
-    [ <AS> dtd = exactIntegerType() ]
-    [ <START> <WITH> initialValue = exactIntegerObject() ]
-    [ <INCREMENT> <BY> stepValue = exactIntegerObject() ]
-    [
-        ( <MAXVALUE> maxValue = exactIntegerObject() )
-        |
-        LOOKAHEAD( { getToken( 2 ).kind == MAXVALUE } )
-        ( <NO> <MAXVALUE> )
-    ]
-    [
-        ( <MINVALUE> minValue = exactIntegerObject() )
-        |
-        LOOKAHEAD( { getToken( 2 ).kind == MINVALUE } )
-        ( <NO> <MINVALUE> )
-    ]
-    [ cycle = cycleClause() ]
+    ( sequenceGeneratorOption( optionalClauses ) ) *
 	{
 		checkVersion( DataDictionary.DD_VERSION_DERBY_10_6, "SEQUENCES");
+
+        if ( optionalClauses[ IDX_DATA_TYPE ] != null )
+        { dtd = (DataTypeDescriptor) optionalClauses[ IDX_DATA_TYPE ]; }
+
+        if ( optionalClauses[ IDX_START_WITH_OPTION ] != null )
+        { initialValue = (Long) optionalClauses[ IDX_START_WITH_OPTION ]; }
+
+        if ( optionalClauses[ IDX_INCREMENT_BY_OPTION ] != null )
+        { stepValue = (Long) optionalClauses[ IDX_INCREMENT_BY_OPTION ]; }
+
+        if (
+                (optionalClauses[ IDX_MAX_VALUE_OPTION ] != null) &&
+                ( !( optionalClauses[ IDX_MAX_VALUE_OPTION ] instanceof Boolean ) )
+            )
+        { maxValue = (Long) optionalClauses[ IDX_MAX_VALUE_OPTION ]; }
+
+        if (
+                (optionalClauses[ IDX_MIN_VALUE_OPTION ] != null) &&
+                ( !( optionalClauses[ IDX_MIN_VALUE_OPTION ] instanceof Boolean ) )
+            )
+        { minValue = (Long) optionalClauses[ IDX_MIN_VALUE_OPTION ]; }
+
+        if ( optionalClauses[ IDX_CYCLE_OPTION ] != null )
+        { cycle = (Boolean) optionalClauses[ IDX_CYCLE_OPTION ]; }
+
 		return (StatementNode) nodeFactory.getNode(
 			C_NodeTypes.CREATE_SEQUENCE_NODE,
 			qualifiedSequenceName,
@@ -10114,21 +10134,82 @@
 }
 
 /*
+ * <A NAME="sequenceGeneratorOption">sequenceGeneratorOption</A>
+ */
+void
+sequenceGeneratorOption( Object[] optionalClauses ) throws StandardException :
+{
+    Object option =  null;
+    int  optionIndex = -1;
+    Boolean[] cycleOption = new Boolean[ 1 ];
+    String optionName = null;
+    Token optionToken = null;
+}
+{
+    (
+        optionToken = <AS> option = exactIntegerType()
+        { optionIndex = IDX_DATA_TYPE; }
+	|
+        optionToken = <START> <WITH> option = exactIntegerObject()
+        { optionIndex = IDX_START_WITH_OPTION; }
+	|
+        optionToken = <INCREMENT> <BY> option = exactIntegerObject()
+        { optionIndex = IDX_INCREMENT_BY_OPTION; }
+	|
+        (
+            ( optionToken = <MAXVALUE> option = exactIntegerObject() )
+            |
+            LOOKAHEAD( { getToken( 2 ).kind == MAXVALUE } )
+            ( <NO> optionToken = <MAXVALUE> ) { option = Boolean.FALSE; }
+        )
+        { optionIndex = IDX_MAX_VALUE_OPTION; }
+	|
+        (
+            ( optionToken = <MINVALUE> option = exactIntegerObject() )
+            |
+            LOOKAHEAD( { getToken( 2 ).kind == MINVALUE } )
+            ( <NO> optionToken = <MINVALUE> ) { option = Boolean.FALSE; }
+        )
+        { optionIndex = IDX_MIN_VALUE_OPTION; }
+	|
+        optionToken = cycleClause( cycleOption )
+        {
+            option = cycleOption[ 0 ];
+            optionIndex = IDX_CYCLE_OPTION;
+        }
+    )
+	{
+        if ( optionIndex != -1 )
+        {
+            if ( optionalClauses[ optionIndex ] != null )
+            {
+                throw StandardException.newException( SQLState.LANG_DUPLICATE_CS_CLAUSE, optionToken.image );
+            }
+
+            optionalClauses[ optionIndex ] = option;
+        }
+	}
+}
+
+/*
  * <A NAME="cycleClause">cycleClause</A>
  */
-Boolean
-cycleClause() throws StandardException :
+Token
+cycleClause( Boolean[] cycleOption ) throws StandardException :
 {
+    Token token = null;
 }
 {
-    <CYCLE>
+    token = <CYCLE>
     {
-        return Boolean.TRUE;
+        cycleOption[0] = Boolean.TRUE;
+        return token;
     }
 |
-    <NO> <CYCLE>
+    <NO> token = <CYCLE>
     {
-        return Boolean.FALSE;
+        cycleOption[0] = Boolean.FALSE;
+        return token;
     }
 }
 

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=909633&r1=909632&r2=909633&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 Fri Feb 12 22:07:23 2010
@@ -2084,14 +2084,6 @@
                 <text>'INCREMENT BY' value can not be zero.</text>
             </msg>
 
-            <msg>
-                <name>42XAD</name>
-                <text>Invalid 'INCREMENT BY' value '{0}'. Must be between 'MINVALUE: {1}' and 'MAXVALUE: {2}'.</text>
-                <arg>stepValue</arg>
-                <arg>minValue</arg>
-                <arg>maxValue</arg>
-            </msg>
-
              <msg>
                 <name>42XAE</name>
                 <text>'{0}' value out of range of datatype '{1}'. Must be between '{2}' and '{3}'.</text>
@@ -2128,6 +2120,12 @@
             </msg>
 
             <msg>
+                <name>42XAJ</name>
+                <text>The CREATE SEQUENCE statement has a redundant '{0}' clause.</text>
+                <arg>clauseName</arg>
+            </msg>
+
+            <msg>
                 <name>42Y00</name>
                 <text>Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition and thus cannot be used as an aggregate expression.</text>
                 <arg>className</arg>

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=909633&r1=909632&r2=909633&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 Fri Feb 12 22:07:23 2010
@@ -913,12 +913,12 @@
     String LANG_GEN_COL_BEFORE_TRIG                                             = "42XAA";
     String LANG_NOT_NULL_NEEDS_DATATYPE                                    = "42XAB";
     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_NEXT_VALUE_FOR_ILLEGAL                                      = "42XAH";    
     String LANG_SEQUENCE_REFERENCED_TWICE                                      = "42XAI";    
+    String LANG_DUPLICATE_CS_CLAUSE                                      = "42XAJ";    
     String LANG_INVALID_USER_AGGREGATE_DEFINITION2                     = "42Y00";
 	String LANG_INVALID_CHECK_CONSTRAINT                               = "42Y01";
 	// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE                = "42Y02";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java?rev=909633&r1=909632&r2=909633&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java Fri Feb 12 22:07:23 2010
@@ -104,6 +104,7 @@
     protected static  final   String  ILLEGAL_COMPARISON = "42818";
     protected static  final   String  ILLEGAL_STORAGE = "42821";
     protected static  final   String  BAD_CAST = "42846";
+    protected static  final   String  DUPLICATE_CLAUSE = "42XAJ";
 
     ///////////////////////////////////////////////////////////////////////////////////
     //

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=909633&r1=909632&r2=909633&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 Fri Feb 12 22:07:23 2010
@@ -21,6 +21,8 @@
 package org.apache.derbyTesting.functionTests.tests.lang;
 
 import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 
@@ -195,7 +197,7 @@
         adminCon.close();
     }
 
-    public void testCreateSequenceWithArguments() throws Exception {
+    public void test_09a_createSequenceWithArguments() throws Exception {
         Connection alphaCon = openUserConnection(ALPHA);
 
         goodStatement(alphaCon,
@@ -243,21 +245,11 @@
                         + " 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");
 
@@ -361,5 +353,122 @@
         goodStatement( conn, "select next value for seq_11_a, next value for seq_11_b from sys.systables where 1=2\n" );
     }
 
+    /**
+     * Verify that optional clauses can appear in any order and redundant clauses
+     * are forbidden.
+     */
+    public void test_12_clauseOrder() throws Exception
+    {
+        Connection conn = openUserConnection(ALPHA);
+
+        goodSequence
+            (
+             conn,
+             "seq_12_a", // name
+             "", // clauses
+             "INTEGER", // datatype
+             Integer.MIN_VALUE, // initial
+             Integer.MIN_VALUE, // min
+             Integer.MAX_VALUE, // max
+             1L, // step
+             false // cycle
+             );
+
+        goodSequence
+            (
+             conn,
+             "seq_12_b", // name
+             "minvalue 5 increment by 3 cycle start with 100 maxvalue 1000000 as bigint", // clauses
+             "BIGINT", // datatype
+             100L, // initial
+             5L, // min
+             1000000L, // max
+             3L, // step
+             true // cycle
+             );
+
+        goodSequence
+            (
+             conn,
+             "seq_12_c", // name
+             "increment by 3 as smallint no cycle no maxvalue", // clauses
+             "SMALLINT", // datatype
+             Short.MIN_VALUE, // initial
+             Short.MIN_VALUE, // min
+             Short.MAX_VALUE, // max
+             3L, // step
+             false // cycle
+             );
+
+        goodSequence
+            (
+             conn,
+             "seq_12_d", // name
+             "maxvalue 1000000000 start with -50 increment by -3 cycle no minvalue", // clauses
+             "INTEGER", // datatype
+             -50L, // initial
+             Integer.MIN_VALUE, // min
+             1000000000, // max
+             -3L, // step
+             true // cycle
+             );
+
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 as smallint as bigint\n" );
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 start with 3 start with 7\n" );
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 minvalue 5 no minvalue\n" );
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 maxvalue 5 no maxvalue\n" );
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 increment by 7 increment by -7\n" );
+        expectCompilationError
+            ( conn, DUPLICATE_CLAUSE, "create sequence bad_12 no cycle cycle\n" );
+    }
+
+    private void goodSequence
+        (
+         Connection conn,
+         String sequenceName,
+         String clauses,
+         String datatype,
+         long initialValue,
+         long minValue,
+         long maxValue,
+         long stepValue,
+         boolean cycle
+         )
+        throws Exception
+    {
+        String statement = "create sequence " + sequenceName + " " + clauses;
+    
+        goodStatement( conn, statement );
+
+        PreparedStatement ps = chattyPrepare
+            (
+             conn,
+             "select sequencedatatype, startvalue, minimumvalue, maximumvalue, increment, cycleoption\n" +
+             "from sys.syssequences\n" +
+             "where sequencename = ?"
+             );
+        ps.setString( 1, sequenceName.toUpperCase() );
+
+        ResultSet rs = ps.executeQuery();
+
+        rs.next();
+        int col = 1;
+
+        assertEquals( datatype, rs.getString( col++ ) );
+        assertEquals( initialValue, rs.getLong( col++ ) );
+        assertEquals( minValue, rs.getLong( col++ ) );
+        assertEquals( maxValue, rs.getLong( col++ ) );
+        assertEquals( stepValue, rs.getLong( col++ ) );
+        assertEquals( cycle, rs.getString( col++ ).equals( "Y" ) );
+
+        rs.close();
+        ps.close();
+    }
+
 
 }