|
1
30
31
32 package org.hsqldb.test;
33
34 import java.sql.Connection  ;
35 import java.sql.DriverManager ;
36 import java.sql.PreparedStatement ;
37 import java.sql.ResultSet ;
38 import java.sql.SQLException ;
39 import java.sql.Statement ;
40 import java.util.Random ;
41
42 import org.hsqldb.lib.StopWatch;
43 import org.hsqldb.persist.HsqlProperties;
44
45
49 public class TestAllTypes {
50
51 protected String url = "jdbc:hsqldb:";
52
53 protected String filepath = "/hsql/testalltypes/test";
55
56 boolean network = true;
58 String user;
59 String password;
60 Statement sStatement;
61 Connection cConnection;
62
63 boolean reportProgress = false;
65 boolean cachedTable = true;
66 int cacheScale = 12;
67 int logType = 3;
68 int writeDelay = 60;
69 boolean indexZip = true;
70 boolean indexLastName = false;
71 boolean addForeignKey = false;
72 boolean refIntegrity = true;
73 boolean createTempTable = false;
74
75 boolean deleteWhileInsert = false;
77 int deleteWhileInsertInterval = 10000;
78
79 int bigrows = 1000;
81
82 protected void setUp() {
83
84 user = "sa";
85 password = "";
86
87 try {
88 sStatement = null;
89 cConnection = null;
90
91 HsqlProperties props = new HsqlProperties(filepath);
92 boolean fileexists = props.checkFileExists();
93
94 Class.forName("org.hsqldb.jdbcDriver");
95
96 if (!network &&!fileexists == false) {
97 cConnection = DriverManager.getConnection(url + filepath,
98 user, password);
99 sStatement = cConnection.createStatement();
100
101 sStatement.execute("SET SCRIPTFORMAT " + logType);
102 sStatement.execute("SET LOGSIZE " + 400);
103 sStatement.execute("SET WRITE_DELAY " + writeDelay);
104 sStatement.execute("SHUTDOWN");
105 cConnection.close();
106 props.load();
107 props.setProperty("hsqldb.cache_scale", "" + cacheScale);
108 props.save();
109
110 cConnection = DriverManager.getConnection(url + filepath,
111 user, password);
112 sStatement = cConnection.createStatement();
113 }
114 } catch (Exception e) {
115 e.printStackTrace();
116 System.out.println("TestSql.setUp() error: " + e.getMessage());
117 }
118 }
119
120
125 public void testFillUp() {
126
127 StopWatch sw = new StopWatch();
128 int smallrows = 0xfff;
129 double value = 0;
130 String ddl1 = "DROP TABLE test IF EXISTS;"
131 + "DROP TABLE zip IF EXISTS;";
132 String ddl2 = "CREATE TABLE zip( zip INT IDENTITY );";
133 String ddl3 = "CREATE " + (cachedTable ? "CACHED "
134 : "") + "TABLE test( id INT IDENTITY,"
135 + " firstname VARCHAR, "
136 + " lastname VARCHAR, "
137 + " zip INTEGER, "
138 + " longfield BIGINT, "
139 + " doublefield DOUBLE, "
140 + " bigdecimalfield DECIMAL, "
141 + " datefield DATE, "
142 + " filler VARCHAR); ";
143
144 String ddl4 = "CREATE INDEX idx1 ON TEST (lastname);";
146
147 String ddl5 = "CREATE INDEX idx2 ON TEST (zip);";
149
150 String ddl6 =
152 "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip);";
153 String filler =
154 "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";
155
156 try {
157 System.out.println("Connecting");
158 sw.zero();
159
160 cConnection = null;
161 sStatement = null;
162 cConnection = DriverManager.getConnection(url + filepath, user,
163 password);
164
165 System.out.println("connected: " + sw.elapsedTime());
166 sw.zero();
167
168 sStatement = cConnection.createStatement();
169
170 java.util.Random randomgen = new java.util.Random ();
171
172 sStatement.execute(ddl1);
173 sStatement.execute(ddl2);
174 sStatement.execute(ddl3);
175 System.out.println("test table with no index");
176
177 if (indexLastName) {
178 sStatement.execute(ddl4);
179 System.out.println("create index on lastname");
180 }
181
182 if (indexZip) {
183 sStatement.execute(ddl5);
184 System.out.println("create index on zip");
185 }
186
187 if (addForeignKey) {
188 sStatement.execute(ddl6);
189 System.out.println("add foreign key");
190 }
191
192 int i;
193
194 for (i = 0; i <= smallrows; i++) {
195 sStatement.execute("INSERT INTO zip VALUES(null);");
196 }
197
198 PreparedStatement ps = cConnection.prepareStatement(
199 "INSERT INTO test (firstname,lastname,zip,longfield,doublefield,bigdecimalfield,datefield,filler) VALUES (?,?,?,?,?,?,?,?)");
200
201 ps.setString(1, "Julia");
202 ps.setString(2, "Clancy");
203
204 for (i = 0; i < bigrows; i++) {
205 ps.setInt(3, nextIntRandom(randomgen, smallrows));
206
207 int nextrandom = nextIntRandom(randomgen, filler.length());
208 int randomlength = nextIntRandom(randomgen, filler.length());
209
210 ps.setLong(4, randomgen.nextLong());
211 ps.setDouble(5, randomgen.nextDouble());
212 ps.setBigDecimal(6, null);
213
214 ps.setDate(7, new java.sql.Date (nextIntRandom(randomgen, 1000)
216 * 24 * 3600 * 1000));
217
218 String varfiller = filler.substring(0, randomlength);
219
220 ps.setString(8, nextrandom + varfiller);
221 ps.execute();
222
223 if (reportProgress && (i + 1) % 10000 == 0) {
224 System.out.println("Insert " + (i + 1) + " : "
225 + sw.elapsedTime());
226 }
227
228 if (deleteWhileInsert && i != 0
230 && i % deleteWhileInsertInterval == 0) {
231 sStatement.execute("CALL IDENTITY();");
232
233 ResultSet rs = sStatement.getResultSet();
234
235 rs.next();
236
237 int lastId = rs.getInt(1);
238
239 sStatement.execute(
240 "SELECT * INTO TEMP tempt FROM test WHERE id > "
241 + (lastId - 4000) + " ;");
242 sStatement.execute("DELETE FROM test WHERE id > "
243 + (lastId - 4000) + " ;");
244 sStatement.execute(
245 "INSERT INTO test SELECT * FROM tempt;");
246 sStatement.execute("DROP TABLE tempt;");
247 }
248 }
249
250 System.out.println("Total insert: " + i);
254 System.out.println("Insert time: " + sw.elapsedTime() + " rps: "
255 + (i * 1000 / sw.elapsedTime()));
256 sw.zero();
257
258 if (!network) {
259 sStatement.execute("SHUTDOWN");
260 }
261
262 cConnection.close();
263 System.out.println("Shutdown Time: " + sw.elapsedTime());
264 } catch (SQLException e) {
265 System.out.println(e.getMessage());
266 }
267 }
268
269 protected void tearDown() {}
270
271 protected void checkResults() {
272
273 try {
274 StopWatch sw = new StopWatch();
275 ResultSet rs;
276
277 cConnection = DriverManager.getConnection(url + filepath, user,
278 password);
279
280 System.out.println("Reopened database: " + sw.elapsedTime());
281 sw.zero();
282
283 sStatement = cConnection.createStatement();
284
285 sStatement.execute("SET WRITE_DELAY " + writeDelay);
286
287 sStatement.execute("SELECT count(*) from TEST");
290
291 rs = sStatement.getResultSet();
292
293 rs.next();
294 System.out.println("Row Count: " + rs.getInt(1));
295 System.out.println("Time to count: " + sw.elapsedTime());
296
297 sw.zero();
299 sStatement.execute("SELECT count(*) from TEST where zip > -1");
300
301 rs = sStatement.getResultSet();
302
303 rs.next();
304 System.out.println("Row Count: " + rs.getInt(1));
305 System.out.println("Time to count: " + sw.elapsedTime());
306 checkSelects();
307 checkUpdates();
308 sw.zero();
309 cConnection.close();
310 System.out.println("Closed connection: " + sw.elapsedTime());
311 } catch (SQLException e) {
312 System.out.println(e.getMessage());
313 }
314 }
315
316 private void checkSelects() {
317
318 StopWatch sw = new StopWatch();
319 int smallrows = 0xfff;
320 java.util.Random randomgen = new java.util.Random ();
321 int i = 0;
322 boolean slow = false;
323
324 try {
325 for (; i < bigrows; i++) {
326 PreparedStatement ps = cConnection.prepareStatement(
327 "SELECT TOP 1 firstname,lastname,zip,filler FROM test WHERE zip = ?");
328
329 ps.setInt(1, nextIntRandom(randomgen, smallrows));
330 ps.execute();
331
332 if ((i + 1) == 100 && sw.elapsedTime() > 5000) {
333 slow = true;
334 }
335
336 if (reportProgress && (i + 1) % 10000 == 0
337 || (slow && (i + 1) % 100 == 0)) {
338 System.out.println("Select " + (i + 1) + " : "
339 + sw.elapsedTime() + " rps: "
340 + (i * 1000 / sw.elapsedTime()));
341 }
342 }
343 } catch (SQLException e) {}
344
345 System.out.println("Select random zip " + i + " rows : "
346 + sw.elapsedTime() + " rps: "
347 + (i * 1000 / sw.elapsedTime()));
348 sw.zero();
349
350 try {
351 for (i = 0; i < bigrows; i++) {
352 PreparedStatement ps = cConnection.prepareStatement(
353 "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");
354
355 ps.setInt(1, nextIntRandom(randomgen, bigrows - 1));
356 ps.execute();
357
358 if (reportProgress && (i + 1) % 10000 == 0
359 || (slow && (i + 1) % 100 == 0)) {
360 System.out.println("Select " + (i + 1) + " : "
361 + sw.elapsedTime());
362 }
363 }
364 } catch (SQLException e) {}
365
366 System.out.println("Select random id " + i + " rows : "
367 + sw.elapsedTime() + " rps: "
368 + (i * 1000 / sw.elapsedTime()));
369 }
370
371 private void checkUpdates() {
372
373 StopWatch sw = new StopWatch();
374 int smallrows = 0xfff;
375 java.util.Random randomgen = new java.util.Random ();
376 int i = 0;
377 boolean slow = false;
378 int count = 0;
379
380 try {
381 for (; i < smallrows; i++) {
382 PreparedStatement ps = cConnection.prepareStatement(
383 "UPDATE test SET filler = filler || zip WHERE zip = ?");
384 int random = nextIntRandom(randomgen, smallrows - 1);
385
386 ps.setInt(1, random);
387
388 count += ps.executeUpdate();
389
390 if (reportProgress && count % 10000 < 20) {
391 System.out.println("Update " + count + " : "
392 + sw.elapsedTime());
393 }
394 }
395 } catch (SQLException e) {}
396
397 System.out.println("Update with random zip " + i
398 + " UPDATE commands, " + count + " rows : "
399 + sw.elapsedTime() + " rps: "
400 + (count * 1000 / sw.elapsedTime()));
401 sw.zero();
402
403 try {
404 for (i = 0; i < bigrows; i++) {
405 PreparedStatement ps = cConnection.prepareStatement(
406 "UPDATE test SET zip = zip + 1 WHERE id = ?");
407 int random = nextIntRandom(randomgen, bigrows - 1);
408
409 ps.setInt(1, random);
410 ps.execute();
411
412 if (reportProgress && (i + 1) % 10000 == 0
413 || (slow && (i + 1) % 100 == 0)) {
414 System.out.println("Update " + (i + 1) + " : "
415 + sw.elapsedTime() + " rps: "
416 + (i * 1000 / sw.elapsedTime()));
417 }
418 }
419 } catch (SQLException e) {}
420
421 System.out.println("Update with random id " + i + " rows : "
422 + sw.elapsedTime() + " rps: "
423 + (i * 1000 / sw.elapsedTime()));
424 }
425
426 int nextIntRandom(Random r, int range) {
427
428 int b = Math.abs(r.nextInt());
429
430 return b % range;
431 }
432
433 public static void main(String [] argv) {
434
435 StopWatch sw = new StopWatch();
436 TestAllTypes test = new TestAllTypes();
437
438 test.setUp();
439 test.testFillUp();
440 test.tearDown();
441 test.checkResults();
442 System.out.println("Total Test Time: " + sw.elapsedTime());
443 }
444 }
445 |
Popular Tags |
Java API By Example, From Geeks To Geeks.