1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
/*
Derby - Class org.apache.derbyTesting.system.nstest.init.DbSetup
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.system.nstest.init;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.derbyTesting.system.nstest.NsTest;
/**
* DbSetup: Creates database and builds single user table with indexes
*/
public class DbSetup {
/**
* The main database setup method
*/
public static boolean doIt(Connection conn) throws Throwable {
Statement s = null;
ResultSet rs = null;
boolean finished = false;
NsTest.logger.println("dbSetup.doIt() starting...");
try {
conn.setAutoCommit(false);
} catch (Exception e) {
NsTest.logger.println("FAIL - setAutoCommit() failed:");
printException("setting autocommit in dbSetup", e);
return (false);
}
try {
s = conn.createStatement();
rs = s.executeQuery("select tablename from sys.systables "
+ " where tablename = 'NSTESTTAB'");
if (rs.next()) {
rs.close();
NsTest.logger.println("table 'NSTESTTAB' already exists");
finished = true;
NsTest.schemaCreated = true; // indicates to other classes
// that the schema already exists
}
} catch (Exception e) {
NsTest.logger
.println("dbSetup.doIt() check existance of table: FAIL -- unexpected exception:");
printException(
"executing query or processing resultSet to check for table existence",
e);
return (false);
}
// if we reach here then the table does not exist, so we create it
if (finished == false) {
try {
NsTest.logger
.println("creating table 'NSTESTTAB' and corresponding indices");
s.execute("create table nstesttab (" + "id int,"
+ "t_char char(100)," + "t_date date,"
+ "t_decimal decimal," + "t_decimal_nn decimal(10,10),"
+ "t_double double precision," + "t_float float,"
+ "t_int int," + "t_longint bigint,"
+ "t_numeric_large numeric(30,10)," + "t_real real,"
+ "t_smallint smallint," + "t_time time,"
+ "t_timestamp timestamp," + "t_varchar varchar(100),"
+ "t_clob clob(1K)," + "t_blob blob(10K),"
+ "serialkey bigint generated always as identity, "
+ "sequenceColumn bigint, "
+ "unique (serialkey)) ");
s.execute("create index t_char_ind on nstesttab ( t_char)");
s.execute("create index t_date_ind on nstesttab ( t_date)");
s
.execute("create index t_decimal_ind on nstesttab ( t_decimal)");
s
.execute("create index t_decimal_nn_ind on nstesttab ( t_decimal_nn)");
s.execute("create index t_double_ind on nstesttab ( t_double)");
s.execute("create index t_float_ind on nstesttab ( t_float)");
s.execute("create index t_int_ind on nstesttab ( t_int)");
s
.execute("create index t_longint_ind on nstesttab ( t_longint)");
s
.execute("create index t_num_lrg_ind on nstesttab ( t_numeric_large)");
s.execute("create index t_real_ind on nstesttab ( t_real)");
s
.execute("create index t_smallint_ind on nstesttab ( t_smallint)");
s.execute("create index t_time_ind on nstesttab ( t_time)");
s
.execute("create index t_timestamp_ind on nstesttab ( t_timestamp)");
s
.execute("create index t_varchar_ind on nstesttab ( t_varchar)");
s
.execute("create index t_serialkey_ind on nstesttab (serialkey)");
NsTest.logger.println( "Creating nstesttab_seq sequence" );
s.execute( "create sequence nstesttab_seq as bigint start with 0" );
NsTest.logger
.println("creating table 'NSTRIGTAB' and corresponding indices");
s.execute("create table NSTRIGTAB (" + "id int,"
+ "t_char char(100)," + "t_date date,"
+ "t_decimal decimal," + "t_decimal_nn decimal(10,10),"
+ "t_double double precision," + "t_float float,"
+ "t_int int," + "t_longint bigint,"
+ "t_numeric_large numeric(30,10)," + "t_real real,"
+ "t_smallint smallint," + "t_time time,"
+ "t_timestamp timestamp," + "t_varchar varchar(100),"
+ "t_clob clob(1K)," + "t_blob blob(10K),"
+ "serialkey bigint, "
+ "sequenceColumn bigint )");
// create trigger
s.execute("CREATE TRIGGER NSTEST_TRIG AFTER DELETE ON nstesttab "
+ "REFERENCING OLD AS OLDROW FOR EACH ROW MODE DB2SQL "
+ "INSERT INTO NSTRIGTAB values("
+ "OLDROW.ID, OLDROW.T_CHAR,OLDROW.T_DATE,"
+ "OLDROW.T_DECIMAL,OLDROW.T_DECIMAL_NN,OLDROW.T_DOUBLE,"
+ "OLDROW.T_FLOAT, OLDROW.T_INT,OLDROW.T_LONGINT, OLDROW.T_numeric_large,"
+ "OLDROW.T_real,OLDROW.T_smallint,OLDROW.T_time,OLDROW.T_timestamp,OLDROW.T_varchar,"
+ "OLDROW.T_clob,OLDROW.T_blob, "
+ "OLDROW.serialkey, "
+ "OLDROW.sequenceColumn )");
} catch (Exception e) {
if ( NsTest.justCountErrors() ) { NsTest.printException( DbSetup.class.getName(), e ); }
else { e.printStackTrace( NsTest.logger ); }
NsTest.logger
.println("FAIL - unexpected exception in dbSetup.doIt() while creating schema:");
printException("executing statements to create schema", e);
return (false);
}
}// end of if(finished==false)
conn.commit();
return (true);
}// end of method doIt()
// ** This method abstracts exception message printing for all exception
// messages. You may want to change
// ****it if more detailed exception messages are desired.
// ***Method is synchronized so that the output file will contain sensible
// stack traces that are not
// ****mixed but rather one exception printed at a time
public static synchronized void printException(String where, Exception e) {
if ( NsTest.justCountErrors() )
{
NsTest.addError( e );
return;
}
if (e instanceof SQLException) {
SQLException se = (SQLException) e;
if (se.getSQLState().equals("40001"))
NsTest.logger.println("deadlocked detected");
if (se.getSQLState().equals("40XL1"))
NsTest.logger.println(" lock timeout exception");
if (se.getSQLState().equals("23500"))
NsTest.logger.println(" duplicate key violation");
if (se.getNextException() != null) {
String m = se.getNextException().getSQLState();
NsTest.logger.println(se.getNextException().getMessage()
+ " SQLSTATE: " + m);
}
}
if (e.getMessage() == null) {
NsTest.logger.println("NULL error message detected");
NsTest.logger.println("Here is the NULL exection - " + e.toString());
NsTest.logger.println("Stack trace of the NULL exception - ");
e.printStackTrace( NsTest.logger );
}
NsTest.logger.println("During " + where + ", exception thrown was : "
+ e.getMessage());
}
}//end of class definition
|
