| /* |
| |
| Derby - Class org.apache.derbyTesting.functionTests.tests.lang.SequenceTest |
| |
| Licensed to the Apache Software Foundation (ASF) under one or more |
| contributor license agreements. See the NOTICE file distributed with |
| this work for additional information regarding copyright ownership. |
| The ASF licenses this file to You under the Apache License, Version 2.0 |
| (the "License"); you may not use this file except in compliance with |
| the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| |
| */ |
| package org.apache.derbyTesting.functionTests.tests.lang; |
| |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import junit.framework.Test; |
| import org.apache.derby.iapi.reference.SQLState; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| import org.apache.derbyTesting.junit.CleanDatabaseTestSetup; |
| import org.apache.derbyTesting.junit.DatabasePropertyTestSetup; |
| import org.apache.derbyTesting.junit.JDBC; |
| import org.apache.derbyTesting.junit.TestConfiguration; |
| |
| /** |
| * Test sequences. |
| */ |
| public class SequenceTest extends GeneratedColumnsHelper { |
| |
| private static final String TEST_DBO = "TEST_DBO"; |
| private static final String ALPHA = "ALPHA"; |
| private static final String BETA = "BETA"; |
| private static final String[] LEGAL_USERS = {TEST_DBO, ALPHA, BETA}; |
| |
| private static final String TOO_MANY_UNUSED_SEQUENCES = "X0Y93"; |
| |
| public SequenceTest(String name) { |
| super(name); |
| // TODO Auto-generated constructor stub |
| } |
| |
| // SETUP |
| |
| /** |
| * Construct top level suite in this JUnit test |
| */ |
| public static Test suite() { |
| BaseTestSuite suite = |
| new BaseTestSuite(SequenceTest.class, "Sequence Test"); |
| |
| // Need atleast JSR169 to run these tests |
| if (!JDBC.vmSupportsJSR169() && !JDBC.vmSupportsJDBC3()) { |
| return suite; |
| } |
| |
| Test cleanTest = new CleanDatabaseTestSetup(suite); |
| Test authenticatedTest = DatabasePropertyTestSetup.builtinAuthentication |
| (cleanTest, LEGAL_USERS, "sequence"); |
| Test authorizedTest = TestConfiguration.sqlAuthorizationDecorator(authenticatedTest); |
| |
| return authorizedTest; |
| } |
| |
| public void test_01_CreateSequence() throws SQLException { |
| Statement s = createStatement(); |
| s.executeUpdate("CREATE SEQUENCE mySeq"); |
| } |
| |
| public void test_02_DropSequence() throws SQLException { |
| Statement s = createStatement(); |
| s.executeUpdate("CREATE SEQUENCE mySeq1"); |
| s.executeUpdate("DROP SEQUENCE mySeq1 restrict"); |
| } |
| |
| public void test_03_DuplicateCreationFailure() throws SQLException { |
| Statement s = null; |
| try { |
| s = createStatement(); |
| s.executeUpdate("CREATE SEQUENCE mySeq1"); |
| s.executeUpdate("CREATE SEQUENCE mySeq1"); |
| } catch (SQLException sqle) { |
| assertSQLState("X0Y68", sqle); |
| }finally{ |
| s.executeUpdate("DROP SEQUENCE mySeq1 restrict"); // Drop the one created. |
| } |
| } |
| |
| public void test_04_ImplicitSchemaCreation() throws SQLException { |
| Connection adminCon = openUserConnection(TEST_DBO); |
| |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmt = alphaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| stmt.executeUpdate("CREATE SEQUENCE alpha_seq"); |
| stmt.executeUpdate("DROP SEQUENCE alpha_seq restrict"); |
| stmt.close(); |
| alphaCon.close(); |
| adminCon.close(); |
| } |
| |
| public void test_05CreateWithSchemaSpecified() throws SQLException { |
| |
| // create DB |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmt = alphaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| stmt.executeUpdate("CREATE SEQUENCE alpha.alpha_seq"); |
| stmt.executeUpdate("DROP SEQUENCE alpha.alpha_seq restrict"); |
| stmt.close(); |
| alphaCon.close(); |
| } |
| |
| public void test_06_CreateWithSchemaSpecifiedCreateTrue() throws SQLException { |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmt = alphaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| stmt.executeUpdate("CREATE SEQUENCE alpha.alpha_seq"); |
| stmt.executeUpdate("DROP SEQUENCE alpha.alpha_seq restrict"); |
| stmt.close(); |
| alphaCon.close(); |
| } |
| |
| public void test_07_CreateWithSchemaDropWithNoSchema() throws SQLException { |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmt = alphaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| stmt.executeUpdate("CREATE SEQUENCE alpha.alpha_seq"); |
| stmt.executeUpdate("DROP SEQUENCE alpha_seq restrict"); |
| stmt.close(); |
| alphaCon.close(); |
| } |
| |
| /** |
| * Test trying to drop a sequence in a schema that doesn't belong to one |
| */ |
| public void test_08_DropOtherSchemaSequence() throws SQLException { |
| Connection adminCon = openUserConnection(TEST_DBO); |
| |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmtAlpha = alphaCon.createStatement(); |
| stmtAlpha.executeUpdate("CREATE SEQUENCE alpha_seq"); |
| |
| Connection betaCon = openUserConnection(BETA); |
| Statement stmtBeta = betaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| assertStatementError("42507", stmtBeta, "DROP SEQUENCE alpha.alpha_seq restrict"); |
| |
| // Cleanup: |
| stmtAlpha.executeUpdate("DROP SEQUENCE alpha_seq restrict"); |
| |
| stmtAlpha.close(); |
| stmtBeta.close(); |
| alphaCon.close(); |
| betaCon.close(); |
| adminCon.close(); |
| } |
| |
| /** |
| * Test trying to create a sequence in a schema that doesn't belong to one |
| */ |
| public void test_09_CreateOtherSchemaSequence() throws SQLException { |
| // create DB |
| Connection adminCon = openUserConnection(TEST_DBO); |
| |
| Connection alphaCon = openUserConnection(ALPHA); |
| Statement stmtAlpha = alphaCon.createStatement(); |
| stmtAlpha.executeUpdate("CREATE SEQUENCE alpha_seq"); |
| |
| Connection betaCon = openUserConnection(BETA); |
| Statement stmtBeta = betaCon.createStatement(); |
| |
| // should implicitly create schema ALPHA |
| assertStatementError("42507", stmtBeta, "CREATE SEQUENCE alpha.alpha_seq3"); |
| |
| // Cleanup: |
| stmtAlpha.executeUpdate("DROP SEQUENCE alpha_seq restrict"); |
| |
| stmtAlpha.close(); |
| stmtBeta.close(); |
| alphaCon.close(); |
| betaCon.close(); |
| adminCon.close(); |
| } |
| |
| public void test_09a_createSequenceWithArguments() 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 0 |
| expectCompilationError(alphaCon, |
| SQLState.LANG_SEQ_INCREMENT_ZERO, |
| "CREATE SEQUENCE int5 AS INTEGER INCREMENT BY 0"); |
| |
| 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 |
| */ |
| public void test_10_NextValue() throws SQLException { |
| Statement s = createStatement(); |
| s.executeUpdate("CREATE SEQUENCE mySeq1"); |
| s.execute("SELECT NEXT VALUE FOR mySeq1 from sys.systables"); |
| s.execute("DROP SEQUENCE mySeq1 restrict"); |
| } |
| |
| /** |
| * Verify that sequences can't be used in many contexts. |
| */ |
| public void test_11_forbiddenContexts() throws Exception |
| { |
| Connection conn = openUserConnection(ALPHA); |
| |
| goodStatement( conn, "create sequence seq_11_a\n" ); |
| goodStatement( conn, "create sequence seq_11_b\n" ); |
| |
| String illegalSequence = SQLState.LANG_NEXT_VALUE_FOR_ILLEGAL; |
| |
| // sequences not allowed in WHERE clause |
| expectCompilationError( conn, illegalSequence, "select * from sys.systables where ( next value for seq_11_a ) > 100\n" ); |
| |
| // sequences not allowed in HAVING clause |
| expectCompilationError |
| ( conn, illegalSequence, |
| "select max( conglomeratenumber ), tableid\n" + |
| "from sys.sysconglomerates\n" + |
| "group by tableid\n" + |
| "having max( conglomeratenumber ) > ( next value for seq_11_a )\n" |
| ); |
| |
| // sequences not allowed in ON clause |
| expectCompilationError |
| ( conn, illegalSequence, "select * from sys.sysconglomerates left join sys.sysschemas on conglomeratenumber = ( next value for seq_11_a )\n" ); |
| |
| // sequences not allowed in CHECK constraints |
| expectCompilationError |
| ( conn, illegalSequence, "create table t_11_1( a int check ( a > ( next value for seq_11_a ) ) )\n" ); |
| |
| // sequences not allowed in generated columns |
| expectCompilationError |
| ( conn, illegalSequence, "create table t_11_1( a int, b generated always as ( a + ( next value for seq_11_a ) ) )\n" ); |
| |
| // sequences not allowed in aggregates |
| expectCompilationError |
| ( conn, illegalSequence, "select max( next value for seq_11_a ) from sys.systables\n" ); |
| |
| // sequences not allowed in CASE expressions |
| expectCompilationError |
| ( conn, illegalSequence, "values case when ( next value for seq_11_a ) < 0 then 100 else 200 end\n" ); |
| |
| // sequences not allowed in DISTINCT clauses |
| expectCompilationError |
| ( conn, illegalSequence, "select distinct( next value for seq_11_a ) from sys.systables\n" ); |
| |
| // sequences not allowed in ORDER BY clauses |
| expectCompilationError |
| ( conn, illegalSequence, "select tableid, ( next value for seq_11_a ) a from sys.systables order by a\n" ); |
| |
| // sequences not allowed in GROUP BY expressions |
| expectCompilationError |
| ( conn, illegalSequence, "select max( tableid ), ( next value for seq_11_a ) from sys.systables group by ( next value for seq_11_a )\n" ); |
| |
| // given sequence only allowed once per statement. see DERBY-4513. |
| expectCompilationError |
| ( conn, SQLState.LANG_SEQUENCE_REFERENCED_TWICE, "select next value for seq_11_a, next value for seq_11_a from sys.systables where 1=2\n" ); |
| |
| // however, two different sequences can appear in a statement |
| 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(); |
| } |
| |
| /** |
| * Verify that restricted drops prevent objects from being orphaned. |
| */ |
| public void test_13_restrictedDrop() throws Exception |
| { |
| Connection conn = openUserConnection(ALPHA); |
| |
| goodStatement( conn, "create table t_13_a( a int )" ); |
| goodStatement( conn, "create table t_13_b( a int )" ); |
| |
| String createStatement; |
| String dropStatement; |
| String createDependentObject; |
| String dropDependentObject; |
| String badDropState; |
| |
| createStatement = "create sequence seq_13_a"; |
| dropStatement = "drop sequence seq_13_a restrict"; |
| createDependentObject = "create trigger trig_13 after insert on t_13_a for each row insert into t_13_b( a ) values ( next value for seq_13_a )\n"; |
| dropDependentObject = "drop trigger trig_13"; |
| badDropState = FORBIDDEN_DROP_TRIGGER; |
| verifyRestrictedDrop |
| ( |
| conn, |
| createDependentObject, |
| dropDependentObject, |
| createStatement, |
| dropStatement, |
| badDropState |
| ); |
| |
| createStatement = "create sequence seq_13_b"; |
| dropStatement = "drop sequence seq_13_b restrict"; |
| createDependentObject = "create view v_13( a, b ) as select a, next value for seq_13_b from t_13_a\n"; |
| dropDependentObject = "drop view v_13"; |
| badDropState = VIEW_DEPENDENCY; |
| verifyRestrictedDrop |
| ( |
| conn, |
| createDependentObject, |
| dropDependentObject, |
| createStatement, |
| dropStatement, |
| badDropState |
| ); |
| |
| } |
| |
| /** |
| * Verify that you can use sequences in insert statements driven |
| * by selects. See DERBY-4803. |
| */ |
| public void test_14_insertSelect() throws Exception |
| { |
| Connection conn = openUserConnection(ALPHA); |
| |
| goodStatement( conn, "create sequence sequence_is" ); |
| goodStatement( conn, "create table tis_1( a int )" ); |
| goodStatement( conn, "create table tis_2( a int, b int )" ); |
| goodStatement( conn, "insert into tis_1( a ) values ( 1 ), ( 2 )" ); |
| goodStatement( conn, "insert into tis_2 select next value for sequence_is, a from tis_1" ); |
| |
| assertResults |
| ( |
| conn, |
| "select * from tis_2 order by b", |
| new String[][] |
| { |
| { "-2147483648", "1" }, |
| { "-2147483647", "2" }, |
| }, |
| true |
| ); |
| } |
| |
| /** |
| * Verify that the new sequence-related keywords are non-reserved keywords. |
| */ |
| public void test_15_5254() throws Exception |
| { |
| Connection conn = openUserConnection(ALPHA); |
| |
| goodStatement( conn, "create table t_5254( cycle int, minvalue int, maxvalue int )" ); |
| goodStatement( conn, "drop table t_5254" ); |
| } |
| |
| /** |
| * Verify that trigger recompilation doesn't choke trying to create |
| * two nested writable transactions. |
| */ |
| public void test_16_6137() throws Exception |
| { |
| //DERBY-6176 (Couple failures in SequenceGeneratorTest suite on |
| // trunk(1466748) with weme 6.2. Disabling the test for small |
| // devices. |
| if ( JDBC.vmSupportsJSR169() ) { return; } |
| |
| Connection conn = openUserConnection( TEST_DBO ); |
| |
| goodStatement( conn, "call syscs_util.syscs_set_database_property( 'derby.language.sequence.preallocator', '2' )" ); |
| goodStatement( conn, "create table t_6137( rateID int generated always as identity primary key, amount decimal( 5,2 ) )" ); |
| goodStatement( conn, "create table t_history_6137( changeID int primary key, amount decimal( 5,2 ) )" ); |
| goodStatement( conn, "create sequence seq_6137 start with 1" ); |
| goodStatement |
| ( |
| conn, |
| "create trigger trg_t_6137_hist_del\n" + |
| "after delete on t_6137\n" + |
| "referencing old row as old\n" + |
| "for each row\n" + |
| " insert into t_history_6137 ( changeID, amount ) values (( next value for seq_6137 ), old.amount )\n" |
| ); |
| goodStatement( conn, "insert into t_6137( amount ) values ( 30.04 ), ( 60.04 ), ( 90.04 )" ); |
| |
| // invalidate the stored statements so that the trigger will have to be recompiled |
| goodStatement( conn, "call syscs_util.syscs_invalidate_stored_statements()" ); |
| |
| // put the sequence in the cache |
| assertResults |
| ( |
| conn, |
| "values next value for seq_6137", |
| new String[][] |
| { |
| { "1" }, |
| }, |
| true |
| ); |
| |
| // verify that the trigger recompiles and fires correctly |
| goodStatement( conn, "delete from t_6137 where rateID = 1" ); |
| goodStatement( conn, "delete from t_6137 where rateID = 2" ); |
| assertResults |
| ( |
| conn, |
| "select * from t_history_6137 order by changeID", |
| new String[][] |
| { |
| { "2", "30.04" }, |
| { "3", "60.04" }, |
| }, |
| true |
| ); |
| |
| // verify current value of sequence |
| String peekAtSequence = "values syscs_util.syscs_peek_at_sequence('" + TEST_DBO + "', 'SEQ_6137')"; |
| assertResults |
| ( |
| conn, |
| peekAtSequence, |
| new String[][] |
| { |
| { "4" }, |
| }, |
| true |
| ); |
| |
| // tidy up |
| goodStatement( conn, "drop trigger trg_t_6137_hist_del" ); |
| goodStatement( conn, "drop table t_history_6137" ); |
| goodStatement( conn, "drop table t_6137" ); |
| goodStatement( conn, "drop sequence seq_6137 restrict" ); |
| goodStatement( conn, "call syscs_util.syscs_set_database_property( 'derby.language.sequence.preallocator', null )" ); |
| |
| // verify that the uncleared cache does not leave a phantom sequence hanging around |
| expectExecutionError( conn, MISSING_OBJECT, peekAtSequence ); |
| expectCompilationError( conn, OBJECT_DOES_NOT_EXIST, "values next value for seq_6137" ); |
| } |
| |
| /** |
| * Verify that a sequence can be used in the same transaction |
| * which created it. |
| */ |
| public void test_17_6554() throws Exception |
| { |
| Connection alphaConn = openUserConnection( ALPHA ); |
| Connection betaConn = openUserConnection( BETA ); |
| |
| alphaConn.setAutoCommit( false ); |
| betaConn.setAutoCommit( false ); |
| |
| String createSequence = "create sequence seq6554"; |
| String nextValueFor = "values next value for seq6554"; |
| String[][] nextValueForResults = |
| new String[][] |
| { |
| { "-2147483648" }, |
| }; |
| |
| goodStatement( alphaConn, createSequence ); |
| assertResults( alphaConn, nextValueFor, nextValueForResults, true ); |
| alphaConn.rollback(); |
| |
| goodStatement( betaConn, createSequence ); |
| assertResults( betaConn, nextValueFor, nextValueForResults, true ); |
| betaConn.rollback(); |
| |
| // now try creating and using the sequence inside the nested |
| // transaction context of a database procedure |
| |
| goodStatement |
| ( |
| alphaConn, |
| "create procedure createSequence( sequenceName varchar( 128 ) )\n" + |
| "language java parameter style java modifies sql data\n" + |
| "external name 'org.apache.derbyTesting.functionTests.tests.lang.SequenceTest.createSequence'\n" |
| ); |
| goodStatement( alphaConn, "grant execute on procedure createSequence to public" ); |
| alphaConn.commit(); |
| |
| String runProcedure = "call alpha.createSequence( 'seq6554_2' )"; |
| String postProcedureQuery= "values next value for seq6554_2"; |
| String[][] postProcedureResults = |
| new String[][] |
| { |
| { "-2147483647" }, |
| }; |
| |
| goodStatement( alphaConn, runProcedure ); |
| assertResults( alphaConn, postProcedureQuery, postProcedureResults, true ); |
| alphaConn.rollback(); |
| |
| goodStatement( betaConn, runProcedure ); |
| assertResults( betaConn, postProcedureQuery, postProcedureResults, true ); |
| betaConn.rollback(); |
| |
| alphaConn.setAutoCommit( true ); |
| betaConn.setAutoCommit( true ); |
| } |
| |
| /** |
| * Verify that sequence numbers pick up where they left off after eviction from |
| * the sequence cache. |
| */ |
| public void test_17_6554_cacheEviction() throws Exception |
| { |
| Connection control = openUserConnection( TEST_DBO ); |
| |
| // set the size of the sequence generator cache to 5 entries. |
| // bounce the database so that the value takes effect. |
| goodStatement( control, "call syscs_util.syscs_set_database_property( 'derby.language.sequenceGeneratorCacheSize', '5' )" ); |
| getTestConfiguration().shutdownDatabase(); |
| |
| Connection conn1 = openUserConnection( ALPHA ); |
| Connection conn2 = openUserConnection( ALPHA ); |
| |
| goodStatement( conn1, "create sequence s000" ); |
| assertResults |
| ( |
| conn1, |
| "values next value for s000", |
| new String[][] |
| { |
| { "-2147483648" }, |
| }, |
| true |
| ); |
| goodStatement( conn1, "create sequence s001" ); |
| assertResults |
| ( |
| conn1, |
| "values next value for s001", |
| new String[][] |
| { |
| { "-2147483648" }, |
| }, |
| true |
| ); |
| assertResults |
| ( |
| conn1, |
| "values next value for s001", |
| new String[][] |
| { |
| { "-2147483647" }, |
| }, |
| true |
| ); |
| |
| // now create enough sequences to evict the first two sequences from the cache |
| goodStatement( conn2, "create sequence s002" ); |
| goodStatement( conn2, "create sequence s003" ); |
| goodStatement( conn2, "create sequence s004" ); |
| goodStatement( conn2, "create sequence s005" ); |
| goodStatement( conn2, "create sequence s006" ); |
| goodStatement( conn2, "create sequence s007" ); |
| goodStatement( conn2, "create sequence s008" ); |
| goodStatement( conn2, "create sequence s009" ); |
| goodStatement( conn2, "create sequence s010" ); |
| |
| // now we pick up where we left off |
| assertResults |
| ( |
| conn1, |
| "values next value for s000", |
| new String[][] |
| { |
| { "-2147483647" }, |
| }, |
| true |
| ); |
| assertResults |
| ( |
| conn1, |
| "values next value for s001", |
| new String[][] |
| { |
| { "-2147483646" }, |
| }, |
| true |
| ); |
| |
| // restore the original size of the sequence generator cache |
| control = openUserConnection( TEST_DBO ); |
| |
| goodStatement( control, "call syscs_util.syscs_set_database_property( 'derby.language.sequenceGeneratorCacheSize', '1000' )" ); |
| getTestConfiguration().shutdownDatabase(); |
| } |
| |
| ////////////////////////////////////////////////////////////////////// |
| // |
| // SQL ROUTINES |
| // |
| ////////////////////////////////////////////////////////////////////// |
| |
| public static void createSequence( String sequenceName ) |
| throws Exception |
| { |
| Connection conn = DriverManager.getConnection( "jdbc:default:connection" ); |
| |
| conn.prepareStatement( "create sequence " + sequenceName ).execute(); |
| |
| ResultSet rs = conn.prepareStatement( "values next value for " + sequenceName ).executeQuery(); |
| rs.next(); |
| assertEquals( -2147483648L, rs.getLong( 1 ) ); |
| rs.close(); |
| } |
| |
| } |
