001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.lang.JitTest
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.lang;
023:
024: import java.sql.Connection;
025: import java.sql.DatabaseMetaData;
026: import java.sql.ResultSet;
027: import java.sql.Statement;
028: import java.sql.SQLException;
029:
030: import org.apache.derby.tools.ij;
031: import org.apache.derby.tools.JDBCDisplayUtil;
032:
033: /**
034: * DERBY-1327
035: * Identity column can be created with wrong and very large start with
036: * value with "J2RE 1.5.0 IBM Windows 32 build pwi32dev-20060412 (SR2)"
037: * with JIT on
038: */
039: public class JitTest {
040:
041: public static void main(String args[]) {
042: try {
043: /* Load the JDBC Driver class */
044: // use the ij utility to read the property file and
045: // make the initial connection.
046: ij.getPropertyArg(args);
047: Connection conn = ij.startJBMS();
048:
049: System.out.println("Start JitTest");
050: //add tests specific to a jit issue
051: testDerby1327BadStartWithForAutoIncColumn(conn);
052: conn.close();
053: } catch (Exception e) {
054: System.out.println("FAIL -- unexpected exception " + e);
055: JDBCDisplayUtil.ShowException(System.out, e);
056: e.printStackTrace();
057: }
058: }
059:
060: /**
061: * After some number of table creations with JIT turned on, the START WITH
062: * value for the table being created and all the ones already created gets
063: * mysteriously changed with pwi32dev-20060412 (SR2)
064: *
065: * @throws Exception
066: */
067: public static void testDerby1327BadStartWithForAutoIncColumn(
068: Connection conn) throws Exception {
069: conn.setAutoCommit(false);
070: Statement stmt = null;
071:
072: dropAllAppTables(conn);
073: System.out
074: .println("Create tables until we get a wrong Start with value");
075: stmt = conn.createStatement();
076:
077: // numBadStartWith will be changed if any columns get a bad start with value.
078: int numBadStartWith = 0;
079: String createTableSQL = null;
080: try {
081: // create 200 tables. Break out if we get a table that has a bad
082: // start with value.
083: for (int i = 0; (i < 200) && (numBadStartWith == 0); i++) {
084: String tableName = "APP.MYTABLE" + i;
085: createTableSQL = "CREATE TABLE "
086: + tableName
087: + " (ROLEID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ("
088: + "START WITH 2, INCREMENT BY 1), INSTANCEID INTEGER, STATUS"
089: + " INTEGER, LOGICAL_STATE INTEGER, LSTATE_TSTAMP TIMESTAMP,"
090: + " UPDT_TSTAMP TIMESTAMP, TSTAMP TIMESTAMP,"
091: + " CLALEVEL1_CLALEVEL2_CLALEVEL2ID VARCHAR(255), "
092: + "CLALEVEL1_CLALEVEL2_CLALEVEL3_CLALEVEL3ID VARCHAR(255))";
093:
094: stmt.executeUpdate(createTableSQL);
095: conn.commit();
096: numBadStartWith = checkBadStartWithCols(conn, 2);
097: if (numBadStartWith > 0)
098: break;
099: }
100: } catch (SQLException se) {
101: System.out.println("Failed on " + createTableSQL);
102: JDBCDisplayUtil.ShowSQLException(System.out, se);
103:
104: }
105:
106: if (numBadStartWith == 0) {
107: System.out
108: .println("PASS: All 200 tables created without problems");
109: dropAllAppTables(conn);
110: }
111: stmt.close();
112: conn.rollback();
113: }
114:
115: /**
116: * Check that all tables in App do not have a an autoincrementstart value
117: * greater tan maxautoincrementstart
118: * @param conn
119: * @param maxautoincrementstart Maximum expected autoincrementstart value
120: * @return number of columns with bad autoincrementstart value
121: */
122: private static int checkBadStartWithCols(Connection conn,
123: int maxautoincrementstart) throws Exception {
124: Statement stmt = conn.createStatement();
125: ResultSet rs = stmt
126: .executeQuery("select count(autoincrementstart) from"
127: + " sys.syscolumns c, sys.systables t, sys.sysschemas s WHERE"
128: + " t.schemaid = s.schemaid and s.schemaname = 'APP' and"
129: + " autoincrementstart > "
130: + maxautoincrementstart);
131:
132: rs.next();
133: int numBadStartWith = rs.getInt(1);
134: if (numBadStartWith > 0)
135: System.out.println(numBadStartWith
136: + " columns have bad START WITH VALUE");
137: rs.close();
138:
139: if (numBadStartWith > 0) {
140: rs = stmt
141: .executeQuery("select tablename, columnname,"
142: + " autoincrementstart from sys.syscolumns c, sys.systables t,"
143: + " sys.sysschemas s WHERE t.schemaid = s.schemaid and"
144: + " s.schemaname = 'APP' and autoincrementstart > 2 ORDER"
145: + " BY tablename");
146: while (rs.next()) {
147: System.out
148: .println("Unexpected start value: "
149: + rs.getLong(3) + " on column "
150: + rs.getString(1) + "("
151: + rs.getString(2) + ")");
152:
153: }
154: }
155: return numBadStartWith;
156: }
157:
158: /**
159: * Drop all tables in schema APP
160: * @param conn
161: * @throws SQLException
162: */
163: private static void dropAllAppTables(Connection conn)
164: throws SQLException {
165: Statement stmt1 = conn.createStatement();
166: Statement stmt2 = conn.createStatement();
167: System.out.println("Drop all tables in APP schema");
168: ResultSet rs = stmt1
169: .executeQuery("SELECT tablename from sys.systables"
170: + " t, sys.sysschemas s where t.schemaid = s.schemaid and"
171: + " s.schemaname = 'APP'");
172:
173: while (rs.next()) {
174: String tableName = rs.getString(1);
175:
176: try {
177: stmt2.executeUpdate("DROP TABLE " + tableName);
178: } catch (SQLException se) {
179: System.out.println("Error dropping table:" + tableName);
180: se.printStackTrace();
181: continue;
182: }
183: }
184: }
185: }
|