Source code: org/hsqldb/test/TestSelf.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 org.hsqldb.jdbcDriver;
73
74 /**
75 * Main test class, containing several JDBC and script based tests to
76 * verify correct operation of the engine.
77 *
78 * @version 1.7.0
79 */
80 class TestSelf {
81
82 /**
83 * Method declaration
84 *
85 * @param argv
86 */
87 public static void main(String argv[]) {
88
89 print("Usage: TestSelf [records [-m]] (-m means in-memory only)");
90
91 int max = 500;
92
93 if (argv.length >= 1) {
94 max = Integer.parseInt(argv[0]);
95 }
96
97 boolean persistent = true;
98 boolean update = false;
99
100 if (argv.length >= 2) {
101 String a1 = argv[1];
102
103 if (a1.equals("-m")) {
104 persistent = false;
105 }
106 }
107
108 test(max, persistent);
109 }
110
111 /**
112 * Method declaration
113 *
114 * @param max
115 * @param persistent
116 */
117 static void test(int max, boolean persistent) {
118
119 // DriverManager.setLogStream(System.out);
120 try {
121 DriverManager.registerDriver(new org.hsqldb.jdbcDriver());
122
123 if (persistent) {
124 testPersistence();
125 deleteDatabase("test2");
126 test("jdbc:hsqldb:test2", "sa", "", true);
127 testPerformance("jdbc:hsqldb:test2", "sa", "", max, true);
128 }
129
130 test("jdbc:hsqldb:.", "sa", "", false);
131 testPerformance("jdbc:hsqldb:.", "sa", "", max, false);
132 } catch (Exception e) {
133 print("TestSelf error: " + e.getMessage());
134 e.printStackTrace();
135 }
136 }
137
138 static void delete(String file) {
139
140 try {
141 new File(file).delete();
142 } catch (Exception e) {}
143 }
144
145 static void deleteDatabase(String path) {
146
147 delete(path + ".backup");
148 delete(path + ".properties");
149 delete(path + ".script");
150 delete(path + ".data");
151 }
152
153 static void test(String url, String user, String password,
154 boolean persistent) throws Exception {
155
156 String name = persistent ? "Persistent"
157 : "Memory";
158
159 print(name);
160
161 Connection cConnection = null;
162
163 try {
164 cConnection = DriverManager.getConnection(url, user, password);
165 } catch (Exception e) {
166 e.printStackTrace();
167 print("TestSelf init error: " + e.getMessage());
168 }
169
170 testMainScript(cConnection, persistent);
171 testTabProfile(cConnection, persistent);
172 testMarotest(cConnection, persistent);
173 cConnection.close();
174 }
175
176 static void testPersistence() {
177
178 deleteDatabase("test1");
179
180 try {
181 String url = "jdbc:hsqldb:test1";
182 String user = "sa";
183 String password = "";
184 Connection cConnection = null;
185
186 cConnection = DriverManager.getConnection(url, user, password);
187
188 testScript(cConnection, "TestSelfCreate.txt");
189 cConnection.close();
190
191 cConnection = DriverManager.getConnection(url, user, password);
192
193 testScript(cConnection, "TestSelfModify.txt");
194 cConnection.close();
195
196 cConnection = DriverManager.getConnection(url, user, password);
197
198 testScript(cConnection, "TestSelfVerify.txt");
199 cConnection.close();
200 } catch (Exception e) {
201 e.printStackTrace();
202 print("TestSelf init error: " + e.getMessage());
203 }
204 }
205
206 static void testMainScript(Connection cConnection, boolean persistent) {
207
208 String name = persistent ? "Persistent"
209 : "Memory";
210
211 print(name + " TestScript");
212
213 // location of TestSelf.txt relative to the development environment
214 String path = "TestSelf.txt";
215
216 testScript(cConnection, path);
217 }
218
219 static void testScript(Connection cConnection, String path) {
220
221 try {
222 Statement sStatement = cConnection.createStatement();
223 File testfile = new File(path);
224 LineNumberReader read =
225 new LineNumberReader(new FileReader(testfile));
226 String s = "";
227
228 print("Opened test script file: " + testfile.getAbsolutePath());
229
230 while (true) {
231 String line = read.readLine();
232
233 if (line == null) {
234 break;
235 }
236
237 if (line.startsWith(" ")) {
238 s += line;
239 } else {
240 test(sStatement, s);
241
242 s = line;
243 }
244 }
245
246 sStatement.close();
247 } catch (Exception e) {
248 e.printStackTrace();
249 print("test script file error: " + e.getMessage());
250 }
251 }
252
253 static void testTabProfile(Connection cConnection, boolean persistent) {
254
255 Statement sStatement = null;
256 ResultSet r;
257 String s = "";
258 long start;
259 boolean bDropError = false;
260 String name = persistent ? "Persistent"
261 : "Memory";
262
263 print(name + " TabProfile");
264
265 try {
266 sStatement = cConnection.createStatement();
267 } catch (Exception e) {
268 e.printStackTrace();
269 print("TabProfile init error: " + e.getMessage());
270
271 return;
272 }
273
274 try {
275
276 // prepared statements
277 s = "create table TabProfile(id int primary key,"
278 + "car char,won bit,licence varbinary,"
279 + "name char,sex char,chance double,birthday date)";
280
281 sStatement.execute(s);
282
283 s = "insert into TabProfile values ( ?, ?, ?, ?,"
284 + "'\"John\" the bird''s best friend', 'M',?,?);";
285
286 PreparedStatement p = cConnection.prepareStatement(s);
287
288 p.clearParameters();
289 p.setInt(1, 10);
290 p.setString(2, "Matchcartoon");
291 p.setBoolean(3, true);
292
293 byte[] b1 = {
294 0, 1, -128, 44, 12
295 };
296
297 p.setBytes(4, b1);
298 p.setDouble(5, 50.5);
299 p.setNull(6, Types.DATE);
300 p.executeUpdate();
301 p.clearParameters();
302 p.setInt(1, -2);
303 p.setString(2, "\"Birdie\"'s car ?");
304 p.setBoolean(3, false);
305
306 byte b2[] = {
307 10, 127
308 };
309
310 p.setBytes(4, b2);
311 p.setDouble(5, -3.1415e-20);
312
313 java.util.Calendar cal = java.util.Calendar.getInstance();
314
315 cal.set(2000, 2, 29);
316
317 // fredt@users - who designed the java.util.Calendar API?
318 p.setDate(6, new Date(cal.getTime().getTime()));
319 p.executeUpdate();
320
321 s = "select * from TabProfile where id=-2";
322 r = sStatement.executeQuery(s);
323
324 r.next();
325
326 if (!r.getString(2).equals("\"Birdie\"'s car ?")) {
327 throw new Exception("Unicode error.");
328 }
329
330 r.close();
331
332 s = "drop table TabProfile";
333
334 sStatement.execute(s);
335
336 s = "create table obj(id int,o object)";
337
338 sStatement.execute(s);
339
340 s = "insert into obj values(?,?)";
341 p = cConnection.prepareStatement(s);
342
343 p.setInt(1, 1);
344
345 int ia1[] = {
346 1, 2, 3
347 };
348
349 p.setObject(2, ia1);
350 p.executeUpdate();
351 p.clearParameters();
352 p.setInt(1, 2);
353
354 java.awt.Rectangle r1 = new java.awt.Rectangle(10, 11, 12, 13);
355
356 p.setObject(2, r1);
357 p.executeUpdate();
358
359 r = sStatement.executeQuery("SELECT o FROM obj ORDER BY id DESC");
360
361 r.next();
362
363 java.awt.Rectangle r2 = (java.awt.Rectangle) r.getObject(1);
364
365 if (r2.x != 10 || r2.y != 11 || r2.width != 12
366 || r2.height != 13) {
367 throw new Exception("Object data error: Rectangle");
368 }
369
370 r.next();
371
372 int ia2[] = (int[]) (r.getObject(1));
373
374 if (ia2[0] != 1 || ia2[1] != 2 || ia2[2] != 3
375 || ia2.length != 3) {
376 throw new Exception("Object data error: int[]");
377 }
378
379 s = "drop table obj";
380
381 sStatement.execute(s);
382 sStatement.close();
383 } catch (Exception e) {
384 print("");
385 print("TabProfile error: " + e);
386 print("with SQL command: " + s);
387 e.printStackTrace();
388 }
389 }
390
391 static void testMarotest(Connection cConnection, boolean persistent) {
392
393 Statement sStatement = null;
394 ResultSet r;
395 String s = "";
396 long start;
397 boolean bDropError = false;
398 String name = persistent ? "Persistent"
399 : "Memory";
400
401 print(name + " Marotest");
402
403 try {
404 sStatement = cConnection.createStatement();
405 } catch (Exception e) {
406 e.printStackTrace();
407 print("Marotest init error: " + e.getMessage());
408 }
409
410 try {
411
412 // test duplicate keys & small transaction rollback
413 s = "CREATE TABLE marotest (id int PRIMARY KEY, dat int);"
414 + "INSERT INTO marotest VALUES (1,0);"
415 + "INSERT INTO marotest VALUES (2,0);"
416 + "INSERT INTO marotest VALUES (2,0);";
417
418 try {
419 sStatement.execute(s);
420
421 s = "";
422 } catch (Exception e) {}
423
424 if (s.equals("")) {
425 throw new Exception("Duplicate key gave no error on insert");
426 }
427
428 try {
429 s = "UPDATE marotest SET id=1, dat=-1 WHERE dat=0";
430
431 sStatement.execute(s);
432
433 s = "";
434 } catch (Exception e) {}
435
436 if (s.equals("")) {
437 throw new Exception("Duplicate key gave no error on update");
438 }
439
440 int count = 0;
441
442 s = "SELECT *, id as marotest_id FROM marotest";
443 r = sStatement.executeQuery(s);
444
445 while (r.next()) {
446 r.getFloat(1);
447 r.getString("id");
448 r.getInt("DaT");
449 r.getInt("marotest_id");
450
451 if (r.getShort("dat") != 0) {
452 throw new Exception("Bad update worked");
453 }
454
455 r.getLong("DAT");
456 r.getString(2);
457 r.getObject("ID");
458 r.clearWarnings();
459
460 try {
461
462 // this must throw an error
463 r.getTimestamp("Timestamp?");
464
465 count = 99;
466 } catch (Exception e) {}
467
468 count++;
469 }
470
471 r.close();
472
473 if (count != 2) {
474 throw new Exception("Should have 2 but has " + count
475 + " rows");
476 }
477
478 // test database meta data
479 DatabaseMetaData dbMeta = cConnection.getMetaData();
480
481 r = dbMeta.getColumns(null, "dbo", "MAROTEST", "%");
482
483 while (r.next()) {
484 s = r.getString(4).trim(); // COLUMN_NAME
485
486 int i = r.getInt(5); // DATA_TYPE
487
488 s += i + r.getString("TYPE_NAME");
489 i = r.getInt(7); // COLUMN_SIZE
490 i = r.getInt(9); // "Decimal_Digits"
491 i = r.getInt(11); // NULLABLE
492 s = s.toUpperCase();
493
494 if (!s.equals("ID4INTEGER") &&!s.equals("DAT4INTEGER")) {
495 throw new Exception("Wrong database meta data");
496 }
497 }
498
499 s = "DROP TABLE marotest";
500
501 sStatement.execute(s);
502 sStatement.close();
503 } catch (Exception e) {
504 print("");
505 print("Marotest error: " + e);
506 print("with SQL command: " + s);
507 e.printStackTrace();
508 }
509 }
510
511 static void testPerformance(String url, String user, String password,
512 int max,
513 boolean persistent) throws Exception {
514
515 if (persistent) {
516 delete("test2.backup");
517 delete("test2.properties");
518 delete("test2.script");
519 delete("test2.data");
520 }
521
522 Statement sStatement = null;
523 Connection cConnection = null;
524 ResultSet r;
525 String s = "";
526 long start;
527 boolean bDropError = false;
528 String name = persistent ? "Persistent"
529 : "Memory";
530
531 print(name + " Performance");
532
533 try {
534 cConnection = DriverManager.getConnection(url, user, password);
535 sStatement = cConnection.createStatement();
536 } catch (Exception e) {
537 e.printStackTrace();
538 print("TestSelf init error: " + e.getMessage());
539 }
540
541 try {
542
543 // cache, index and performance tests
544 s = "CREATE CACHED TABLE Addr(ID INT PRIMARY KEY,First CHAR,"
545 + "Name CHAR,ZIP INT)";
546
547 sStatement.execute(s);
548
549 s = "CREATE INDEX iName ON Addr(Name)";
550
551 sStatement.execute(s);
552
553 s = "SET WRITE_DELAY TRUE";
554
555 sStatement.execute(s);
556
557 start = System.currentTimeMillis();
558
559 for (int i = 0; i < max; i++) {
560 s = "INSERT INTO Addr VALUES(" + i + ",'Marcel" + i + "',"
561 + "'Renggli" + (max - i - (i % 31)) + "',"
562 + (3000 + i % 100) + ")";
563
564 if (sStatement.executeUpdate(s) != 1) {
565 throw new Exception("Insert failed");
566 }
567
568 if (i % 100 == 0) {
569 printStatus("insert ", i, max, start);
570 }
571 }
572
573 printStatus("insert ", max, max, start);
574 print("");
575
576 s = "SELECT COUNT(*) FROM Addr";
577 r = sStatement.executeQuery(s);
578
579 r.next();
580
581 int c = r.getInt(1);
582
583 if (c != max) {
584 throw new Exception("Count should be " + (max) + " but is "
585 + c);
586 }
587
588 if (persistent) {
589
590 // close & reopen to test backup
591 cConnection.close();
592
593 cConnection = DriverManager.getConnection(url, user,
594 password);
595 sStatement = cConnection.createStatement();
596 }
597
598 start = System.currentTimeMillis();
599
600 for (int i = 0; i < max; i++) {
601 s = "UPDATE Addr SET Name='Robert" + (i + (i % 31))
602 + "' WHERE ID=" + i;
603
604 if (sStatement.executeUpdate(s) != 1) {
605 throw new Exception("Update failed");
606 }
607
608 if (i % 100 == 0) {
609 printStatus("updated ", i, max, start);
610
611 // s="SELECT COUNT(*) FROM Addr";
612 // r=sStatement.executeQuery(s);
613 // r.next();
614 // int c=r.getInt(1);
615 // if(c!=max) {
616 // throw new Exception("Count should be "+max+" but is "+c);
617 // }
618 }
619 }
620
621 printStatus("update ", max, max, start);
622 print("");
623
624 if (persistent) {
625 s = "SHUTDOWN IMMEDIATELY";
626
627 sStatement.execute(s);
628
629 // open the database; it must be restored after shutdown
630 cConnection.close();
631
632 cConnection = DriverManager.getConnection(url, user,
633 password);
634 sStatement = cConnection.createStatement();
635 }
636
637 start = System.currentTimeMillis();
638
639 for (int i = 0; i < max; i++) {
640 s = "DELETE FROM Addr WHERE ID=" + (max - 1 - i);
641
642 if (sStatement.executeUpdate(s) != 1) {
643 throw new Exception("Delete failed");
644 }
645
646 if (i % 100 == 0) {
647 printStatus("deleting ", i, max, start);
648
649 // s="SELECT COUNT(*) FROM Addr";
650 // r=sStatement.executeQuery(s);
651 // r.next();
652 // int c=r.getInt(1);
653 // if(c!=max-i-1) {
654 // throw new Exception("Count should be "+(max-i-1)+" but is "+c);
655 // }
656 }
657 }
658
659 printStatus("delete ", max, max, start);
660 print("");
661 sStatement.execute("DROP TABLE Addr");
662 } catch (Exception e) {
663 print("");
664 print("TestSelf error: " + e);
665 print("with SQL command: " + s);
666 e.printStackTrace();
667 }
668
669 cConnection.close();
670 print("Test finished");
671 }
672
673 /**
674 * Method declaration
675 *
676 * @param stat
677 * @param s
678 * @throws Exception
679 */
680 static void test(Statement stat, String s) throws Exception {
681
682 String result = "";
683 char type = ' ';
684
685 if (s.startsWith("/*")) {
686 type = s.charAt(2);
687
688 int end = s.indexOf("*/");
689
690 result = s.substring(3, end);
691 }
692
693 try {
694 stat.execute(s);
695
696 int u = stat.getUpdateCount();
697 int i = 0;
698 ResultSet r;
699
700 switch (type) {
701
702 case ' ' :
703 break;
704
705 case 'u' :
706 if (u != Integer.parseInt(result)) {
707 throw new Exception("Expected update count=" + result
708 + " but update count was " + u
709 + " / " + s);
710 }
711 break;
712
713 case 'r' :
714 if (u != -1) {
715 throw new Exception("Expected ResultSet"
716 + " but update count was " + u
717 + " / " + s);
718 }
719
720 r = stat.getResultSet();
721
722 r.next();
723
724 String col = r.getString(1);
725
726 if (r.wasNull() || col == null) {
727 if (!result.equals("")) {
728 throw new Exception("Expected " + result
729 + " but got null / " + s);
730 }
731 } else if (!col.equals(result)) {
732 throw new Exception("Expected >" + result + "<"
733 + " but got >" + col + "< / "
734 + s);
735 }
736 break;
737
738 case 'c' :
739 if (u != -1) {
740 throw new Exception("Expected ResultSet"
741 + " but update count was " + u
742 + " / " + s);
743 }
744
745 r = stat.getResultSet();
746
747 while (r.next()) {
748 i++;
749 }
750
751 if (i != Integer.parseInt(result)) {
752 throw new Exception("Expected " + result + " rows "
753 + " but got " + i + " rows / "
754 + s);
755 }
756 break;
757
758 case 'e' :
759 throw new Exception("Expected error "
760 + "but got no error / " + s);
761 }
762 } catch (SQLException e) {
763 if (type != 'e') {
764 throw new Exception("Expected " + type + "/" + result
765 + " but got error " + e.getMessage()
766 + " / " + s);
767 }
768 }
769 }
770
771 /**
772 * Method declaration
773 *
774 * @param s
775 * @param i
776 * @param max
777 * @param start
778 */
779 static void printStatus(String s, int i, int max, long start) {
780
781 System.out.print(s + ": " + i + "/" + max + " " + (100 * i / max)
782 + "% ");
783
784 long now = System.currentTimeMillis();
785
786 if (now > start) {
787 System.out.print((i * 1000 / (now - start)));
788 }
789
790 System.out.print(" rows/s \r");
791 }
792
793 /**
794 * Method declaration
795 *
796 * @param s
797 */
798 private static void print(String s) {
799 System.out.println(s);
800 }
801 }