Source code: org/hsqldb/test/TestHsql.java
1 /* Copyrights and Licenses
2 *
3 * This product includes Hypersonic SQL.
4 * Originally developed by Thomas Mueller and the Hypersonic SQL Group.
5 *
6 * Copyright (c) 1995-2000 by the Hypersonic SQL Group. All rights reserved.
7 * Redistribution and use in source and binary forms, with or without modification, are permitted
8 * provided that the following conditions are met:
9 * - Redistributions of source code must retain the above copyright notice, this list of conditions
10 * and the following disclaimer.
11 * - Redistributions in binary form must reproduce the above copyright notice, this list of
12 * conditions and the following disclaimer in the documentation and/or other materials
13 * provided with the distribution.
14 * - All advertising materials mentioning features or use of this software must display the
15 * following acknowledgment: "This product includes Hypersonic SQL."
16 * - Products derived from this software may not be called "Hypersonic SQL" nor may
17 * "Hypersonic SQL" appear in their names without prior written permission of the
18 * Hypersonic SQL Group.
19 * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
20 * product includes Hypersonic SQL."
21 * This software is provided "as is" and any expressed or implied warranties, including, but
22 * not limited to, the implied warranties of merchantability and fitness for a particular purpose are
23 * disclaimed. In no event shall the Hypersonic SQL Group or its contributors be liable for any
24 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but
25 * not limited to, procurement of substitute goods or services; loss of use, data, or profits;
26 * or business interruption). However caused any on any theory of liability, whether in contract,
27 * strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this
28 * software, even if advised of the possibility of such damage.
29 * This software consists of voluntary contributions made by many individuals on behalf of the
30 * Hypersonic SQL Group.
31 *
32 *
33 * For work added by the HSQL Development Group:
34 *
35 * Copyright (c) 2001-2002, The HSQL Development Group
36 * All rights reserved.
37 *
38 * Redistribution and use in source and binary forms, with or without
39 * modification, are permitted provided that the following conditions are met:
40 *
41 * Redistributions of source code must retain the above copyright notice, this
42 * list of conditions and the following disclaimer, including earlier
43 * license statements (above) and comply with all above license conditions.
44 *
45 * Redistributions in binary form must reproduce the above copyright notice,
46 * this list of conditions and the following disclaimer in the documentation
47 * and/or other materials provided with the distribution, including earlier
48 * license statements (above) and comply with all above license conditions.
49 *
50 * Neither the name of the HSQL Development Group nor the names of its
51 * contributors may be used to endorse or promote products derived from this
52 * software without specific prior written permission.
53 *
54 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
55 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
56 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
57 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
58 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
59 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
60 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
61 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
62 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
63 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
64 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
65 */
66
67
68 package org.hsqldb.test;
69
70 import java.sql.*;
71 import java.io.*;
72 import junit.framework.Test;
73 import junit.framework.TestCase;
74 import junit.framework.TestSuite;
75 import org.hsqldb.jdbcDriver;
76
77 /**
78 * Main test class, containing several JDBC and script based tests to
79 * verify correct operation of the engine.<br?
80 *
81 * This is based on TestSelf.java but does not have all the tests.
82 *
83 *@ version 1.7.0
84 */
85 public class TestHsql extends TestCase {
86
87 public TestHsql(String name) {
88 super(name);
89 }
90
91 public static Test suite() {
92 return new TestSuite(org.hsqldb.test.TestHsql.class);
93 }
94
95 public static void main(String argv[]) {
96
97 print("Usage: TestSelf [records [-m]] (-m means in-memory only)");
98
99 int max = 500;
100
101 if (argv.length >= 1) {
102 max = Integer.parseInt(argv[0]);
103 }
104
105 boolean persistent = true;
106 boolean update = false;
107
108 if (argv.length >= 2) {
109 String a1 = argv[1];
110
111 if (a1.equals("-m")) {
112 persistent = false;
113 }
114 }
115
116 test(max, persistent);
117 }
118
119 public void testHsql() {
120
121 int max = 500;
122 boolean persistent = true;
123
124 test(max, persistent);
125 }
126
127 /**
128 * Method declaration
129 *
130 * @param max
131 * @param persistent
132 */
133 static void test(int max, boolean persistent) {
134
135 // DriverManager.setLogStream(System.out);
136 try {
137 DriverManager.registerDriver(new org.hsqldb.jdbcDriver());
138
139 if (persistent) {
140 delete("test2.backup");
141 delete("test2.properties");
142 delete("test2.script");
143 delete("test2.data");
144 test("Persistent", "jdbc:hsqldb:test2", "sa", "", max, true);
145 }
146
147 test("In-Memory", "jdbc:hsqldb:.", "sa", "", max, false);
148 } catch (Exception e) {
149 print("TestHsql error: " + e.getMessage());
150 e.printStackTrace();
151 }
152 }
153
154 /**
155 * Method declaration
156 *
157 * @param file
158 */
159 static void delete(String file) {
160
161 try {
162 new File(file).delete();
163 } catch (Exception e) {}
164 }
165
166 /**
167 * Method declaration
168 *
169 * @param name
170 * @param url
171 * @param user
172 * @param password
173 * @param max
174 * @param persistent
175 * @throws Exception
176 */
177 static void test(String name, String url, String user, String password,
178 int max, boolean persistent) throws Exception {
179
180 print(name);
181
182 Statement sStatement = null;
183 Connection cConnection = null;
184
185 try {
186 cConnection = DriverManager.getConnection(url, user, password);
187 sStatement = cConnection.createStatement();
188 } catch (Exception e) {
189 e.printStackTrace();
190 print("TestHsql init error: " + e.getMessage());
191 }
192
193 LineNumberReader read =
194 new LineNumberReader(new FileReader("TestSelf.txt"));
195 String s = "";
196
197 while (true) {
198 String line = read.readLine();
199
200 if (line == null) {
201 break;
202 }
203
204 if (line.startsWith(" ")) {
205 s += line;
206 } else {
207 test(sStatement, s);
208
209 s = line;
210 }
211 }
212
213 long start;
214 boolean bDropError = false;
215 ResultSet r;
216
217 try {
218
219 // test duplicate keys & small transaction rollback
220 s = "CREATE TABLE marotest (id int PRIMARY KEY, dat int);"
221 + "INSERT INTO marotest VALUES (1,0);"
222 + "INSERT INTO marotest VALUES (2,0);"
223 + "INSERT INTO marotest VALUES (2,0);";
224
225 try {
226 sStatement.execute(s);
227
228 s = "";
229 } catch (Exception e) {}
230
231 if (s.equals("")) {
232 throw new Exception("Duplicate key gave no error on insert");
233 }
234
235 try {
236 s = "UPDATE marotest SET id=1, dat=-1 WHERE dat=0";
237
238 sStatement.execute(s);
239
240 s = "";
241 } catch (Exception e) {}
242
243 if (s.equals("")) {
244 throw new Exception("Duplicate key gave no error on update");
245 }
246
247 int count = 0;
248
249 s = "SELECT *, id as marotest_id FROM marotest";
250 r = sStatement.executeQuery(s);
251
252 while (r.next()) {
253 r.getFloat(1);
254 r.getString("id");
255 r.getInt("DaT");
256 r.getInt("marotest_id");
257
258 if (r.getShort("dat") != 0) {
259 throw new Exception("Bad update worked");
260 }
261
262 r.getLong("DAT");
263 r.getString(2);
264 r.getObject("ID");
265 r.clearWarnings();
266
267 try {
268
269 // this must throw an error
270 r.getTimestamp("Timestamp?");
271
272 count = 99;
273 } catch (Exception e) {}
274
275 count++;
276 }
277
278 r.close();
279
280 if (count != 2) {
281 throw new Exception("Should have 2 but has " + count
282 + " rows");
283 }
284
285 // test database meta data
286 DatabaseMetaData dbMeta = cConnection.getMetaData();
287
288 r = dbMeta.getColumns(null, "dbo", "MAROTEST", "%");
289
290 while (r.next()) {
291 s = r.getString(4).trim(); // COLUMN_NAME
292
293 int i = r.getInt(5); // DATA_TYPE
294
295 s += i + r.getString("TYPE_NAME");
296 i = r.getInt(7); // COLUMN_SIZE
297 i = r.getInt(9); // "Decimal_Digits"
298 i = r.getInt(11); // NULLABLE
299 s = s.toUpperCase();
300
301 if (!s.equals("ID4INTEGER") &&!s.equals("DAT4INTEGER")) {
302 throw new Exception("Wrong database meta data");
303 }
304 }
305
306 s = "DROP TABLE marotest";
307
308 sStatement.execute(s);
309
310 { // prepared statements
311 s = "create table TabProfile(id int primary key,"
312 + "car char,won bit,licence varbinary,"
313 + "name char,sex char,chance double,birthday date)";
314
315 sStatement.execute(s);
316
317 s = "insert into TabProfile values ( ?, ?, ?, ?,"
318 + "'\"John\" the bird''s best friend', 'M',?,?);";
319
320 PreparedStatement p = cConnection.prepareStatement(s);
321
322 p.clearParameters();
323 p.setInt(1, 10);
324 p.setString(2, "Matchcartoon");
325 p.setBoolean(3, true);
326
327 byte[] b1 = {
328 0, 1, -128, 44, 12
329 };
330
331 p.setBytes(4, b1);
332 p.setDouble(5, 50.5);
333 p.setNull(6, Types.DATE);
334 p.executeUpdate();
335 p.clearParameters();
336 p.setInt(1, -2);
337 p.setString(2, "\"Birdie\"'s car ?");
338 p.setBoolean(3, false);
339
340 byte b2[] = {
341 10, 127
342 };
343
344 p.setBytes(4, b2);
345 p.setDouble(5, -3.1415e-20);
346
347 java.util.Calendar cal = java.util.Calendar.getInstance();
348
349 cal.set(2000, 2, 29);
350
351 // fredt@users - who designed the java.util.Calendar API?
352 p.setDate(6, new Date(cal.getTime().getTime()));
353 p.executeUpdate();
354
355 s = "select * from TabProfile where id=-2";
356 r = sStatement.executeQuery(s);
357
358 r.next();
359
360 if (!r.getString(2).equals("\"Birdie\"'s car ?")) {
361 throw new Exception("Unicode error.");
362 }
363
364 r.close();
365
366 s = "drop table TabProfile";
367
368 sStatement.execute(s);
369
370 s = "create table obj(id int,o object)";
371
372 sStatement.execute(s);
373
374 s = "insert into obj values(?,?)";
375 p = cConnection.prepareStatement(s);
376
377 p.setInt(1, 1);
378
379 int ia1[] = {
380 1, 2, 3
381 };
382
383 p.setObject(2, ia1);
384 p.executeUpdate();
385 p.clearParameters();
386 p.setInt(1, 2);
387
388 java.awt.Rectangle r1 = new java.awt.Rectangle(10, 11, 12,
389 13);
390
391 p.setObject(2, r1);
392 p.executeUpdate();
393
394 r = sStatement.executeQuery(
395 "SELECT o FROM obj ORDER BY id DESC");
396
397 r.next();
398
399 java.awt.Rectangle r2 = (java.awt.Rectangle) r.getObject(1);
400
401 if (r2.x != 10 || r2.y != 11 || r2.width != 12
402 || r2.height != 13) {
403 throw new Exception("Object data error: Rectangle");
404 }
405
406 r.next();
407
408 int ia2[] = (int[]) (r.getObject(1));
409
410 if (ia2[0] != 1 || ia2[1] != 2 || ia2[2] != 3
411 || ia2.length != 3) {
412 throw new Exception("Object data error: int[]");
413 }
414
415 s = "drop table obj";
416
417 sStatement.execute(s);
418 }
419
420 // cache, index and performance tests
421 s = "CREATE CACHED TABLE Addr(ID INT PRIMARY KEY,First CHAR,"
422 + "Name CHAR,ZIP INT)";
423
424 sStatement.execute(s);
425
426 s = "CREATE INDEX iName ON Addr(Name)";
427
428 sStatement.execute(s);
429
430 s = "SET WRITE_DELAY TRUE";
431
432 sStatement.execute(s);
433
434 start = System.currentTimeMillis();
435
436 for (int i = 0; i < max; i++) {
437 s = "INSERT INTO Addr VALUES(" + i + ",'Marcel" + i + "',"
438 + "'Renggli" + (max - i - (i % 31)) + "',"
439 + (3000 + i % 100) + ")";
440
441 if (sStatement.executeUpdate(s) != 1) {
442 throw new Exception("Insert failed");
443 }
444
445 if (i % 100 == 0) {
446 printStatus("insert ", i, max, start);
447 }
448 }
449
450 printStatus("insert ", max, max, start);
451 print("");
452
453 s = "SELECT COUNT(*) FROM Addr";
454 r = sStatement.executeQuery(s);
455
456 r.next();
457
458 int c = r.getInt(1);
459
460 if (c != max) {
461 throw new Exception("Count should be " + (max) + " but is "
462 + c);
463 }
464
465 if (persistent) {
466
467 // close & reopen to test backup
468 cConnection.close();
469
470 cConnection = DriverManager.getConnection(url, user,
471 password);
472 sStatement = cConnection.createStatement();
473 }
474
475 start = System.currentTimeMillis();
476
477 for (int i = 0; i < max; i++) {
478 s = "UPDATE Addr SET Name='Robert" + (i + (i % 31))
479 + "' WHERE ID=" + i;
480
481 if (sStatement.executeUpdate(s) != 1) {
482 throw new Exception("Update failed");
483 }
484
485 if (i % 100 == 0) {
486 printStatus("updated ", i, max, start);
487
488 // s="SELECT COUNT(*) FROM Addr";
489 // r=sStatement.executeQuery(s);
490 // r.next();
491 // int c=r.getInt(1);
492 // if(c!=max) {
493 // throw new Exception("Count should be "+max+" but is "+c);
494 // }
495 }
496 }
497
498 printStatus("update ", max, max, start);
499 print("");
500
501 if (persistent) {
502 s = "SHUTDOWN IMMEDIATELY";
503
504 sStatement.execute(s);
505
506 // open the database; it must be restored after shutdown
507 cConnection.close();
508
509 cConnection = DriverManager.getConnection(url, user,
510 password);
511 sStatement = cConnection.createStatement();
512 }
513
514 start = System.currentTimeMillis();
515
516 for (int i = 0; i < max; i++) {
517 s = "DELETE FROM Addr WHERE ID=" + (max - 1 - i);
518
519 if (sStatement.executeUpdate(s) != 1) {
520 throw new Exception("Delete failed");
521 }
522
523 if (i % 100 == 0) {
524 printStatus("deleting ", i, max, start);
525
526 // s="SELECT COUNT(*) FROM Addr";
527 // r=sStatement.executeQuery(s);
528 // r.next();
529 // int c=r.getInt(1);
530 // if(c!=max-i-1) {
531 // throw new Exception("Count should be "+(max-i-1)+" but is "+c);
532 // }
533 }
534 }
535
536 printStatus("delete ", max, max, start);
537 print("");
538 sStatement.execute("DROP TABLE Addr");
539 } catch (Exception e) {
540 print("");
541 print("SelfTest error: " + e);
542 print("with SQL command: " + s);
543 e.printStackTrace();
544 }
545
546 cConnection.close();
547 print("Test finished");
548 }
549
550 /**
551 * Method declaration
552 *
553 * @param stat
554 * @param s
555 * @throws Exception
556 */
557 static void test(Statement stat, String s) throws Exception {
558
559 String result = "";
560 char type = ' ';
561
562 if (s.startsWith("/*")) {
563 type = s.charAt(2);
564
565 int end = s.indexOf("*/");
566
567 result = s.substring(3, end);
568 }
569
570 try {
571 stat.execute(s);
572
573 int u = stat.getUpdateCount();
574 int i = 0;
575 ResultSet r;
576
577 switch (type) {
578
579 case ' ' :
580 break;
581
582 case 'u' :
583 if (u != Integer.parseInt(result)) {
584 throw new Exception("Expected update count=" + result
585 + " but update count was " + u
586 + " / " + s);
587 }
588 break;
589
590 case 'r' :
591 if (u != -1) {
592 throw new Exception("Expected ResultSet"
593 + " but update count was " + u
594 + " / " + s);
595 }
596
597 r = stat.getResultSet();
598
599 r.next();
600
601 String col = r.getString(1);
602
603 if (r.wasNull() || col == null) {
604 if (!result.equals("")) {
605 throw new Exception("Expected " + result
606 + " but got null / " + s);
607 }
608 } else if (!col.equals(result)) {
609 throw new Exception("Expected >" + result + "<"
610 + " but got >" + col + "< / "
611 + s);
612 }
613 break;
614
615 case 'c' :
616 if (u != -1) {
617 throw new Exception("Expected ResultSet"
618 + " but update count was " + u
619 + " / " + s);
620 }
621
622 r = stat.getResultSet();
623
624 while (r.next()) {
625 i++;
626 }
627
628 if (i != Integer.parseInt(result)) {
629 throw new Exception("Expected " + result + " rows "
630 + " but got " + i + " rows / "
631 + s);
632 }
633 break;
634
635 case 'e' :
636 throw new Exception("Expected error "
637 + "but got no error / " + s);
638 }
639 } catch (SQLException e) {
640 if (type != 'e') {
641 throw new Exception("Expected " + type + "/" + result
642 + " but got error " + e.getMessage()
643 + " / " + s);
644 }
645 }
646 }
647
648 /**
649 * Method declaration
650 *
651 * @param s
652 * @param i
653 * @param max
654 * @param start
655 */
656 static void printStatus(String s, int i, int max, long start) {
657
658 System.out.print(s + ": " + i + "/" + max + " " + (100 * i / max)
659 + "% ");
660
661 long now = System.currentTimeMillis();
662
663 if (now > start) {
664 System.out.print((i * 1000 / (now - start)));
665 }
666
667 System.out.print(" rows/s \r");
668 }
669
670 /**
671 * Method declaration
672 *
673 * @param s
674 */
675 private static void print(String s) {
676 System.out.println(s);
677 }
678 }