001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (license2)
004: * Initial Developer: H2 Group
005: */
006: package org.h2.test.db;
007:
008: import java.io.File;
009: import java.io.StringReader;
010: import java.sql.Connection;
011: import java.sql.Date;
012: import java.sql.PreparedStatement;
013: import java.sql.ResultSet;
014: import java.sql.SQLException;
015: import java.sql.Statement;
016: import java.sql.Time;
017: import java.sql.Timestamp;
018: import java.util.Random;
019:
020: import org.h2.test.TestBase;
021:
022: /**
023: * Various test cases.
024: */
025: public class TestCases extends TestBase {
026:
027: public void test() throws Exception {
028: testDeleteGroup();
029: testDisconnect();
030: testExecuteTrace();
031: if (config.memory || config.logMode == 0) {
032: return;
033: }
034: testSpecialSQL();
035: testUpperCaseLowerCaseDatabase();
036: testManualCommitSet();
037: testSchemaIdentityReconnect();
038: testAlterTableReconnect();
039: testPersistentSettings();
040: testInsertSelectUnion();
041: testViewReconnect();
042: testDefaultQueryReconnect();
043: testBigString();
044: testRenameReconnect();
045: testAllSizes();
046: testCreateDrop();
047: testPolePos();
048: testQuick();
049: testMutableObjects();
050: testSelectForUpdate();
051: testDoubleRecovery();
052: testConstraintReconnect();
053: testCollation();
054: }
055:
056: private void testDeleteGroup() throws Exception {
057: deleteDb("cases");
058: Connection conn = getConnection("cases");
059: Statement stat = conn.createStatement();
060: stat.execute("set max_memory_rows 2");
061: stat.execute("create table test(id int primary key, x int)");
062: stat.execute("insert into test values(0, 0), (1, 1), (2, 2)");
063: stat
064: .execute("delete from test where id not in (select min(x) from test group by id)");
065: conn.close();
066: }
067:
068: private void testSpecialSQL() throws Exception {
069: deleteDb("cases");
070: Connection conn = getConnection("cases");
071: Statement stat = conn.createStatement();
072: try {
073: stat
074: .execute("create table address(id identity, name varchar check? instr(value, '@') > 1)");
075: } catch (SQLException e) {
076: checkNotGeneralException(e);
077: }
078: stat
079: .execute("SET AUTOCOMMIT OFF; \n//create sequence if not exists object_id;\n");
080: stat
081: .execute("SET AUTOCOMMIT OFF;\n//create sequence if not exists object_id;\n");
082: stat
083: .execute("SET AUTOCOMMIT OFF; //create sequence if not exists object_id;");
084: stat
085: .execute("SET AUTOCOMMIT OFF;//create sequence if not exists object_id;");
086: stat
087: .execute("SET AUTOCOMMIT OFF \n//create sequence if not exists object_id;");
088: stat
089: .execute("SET AUTOCOMMIT OFF\n//create sequence if not exists object_id;");
090: stat
091: .execute("SET AUTOCOMMIT OFF //create sequence if not exists object_id;");
092: stat
093: .execute("SET AUTOCOMMIT OFF//create sequence if not exists object_id;");
094: stat
095: .execute("SET AUTOCOMMIT OFF; \n///create sequence if not exists object_id;");
096: stat
097: .execute("SET AUTOCOMMIT OFF;\n///create sequence if not exists object_id;");
098: stat
099: .execute("SET AUTOCOMMIT OFF; ///create sequence if not exists object_id;");
100: stat
101: .execute("SET AUTOCOMMIT OFF;///create sequence if not exists object_id;");
102: stat
103: .execute("SET AUTOCOMMIT OFF \n///create sequence if not exists object_id;");
104: stat
105: .execute("SET AUTOCOMMIT OFF\n///create sequence if not exists object_id;");
106: stat
107: .execute("SET AUTOCOMMIT OFF ///create sequence if not exists object_id;");
108: stat
109: .execute("SET AUTOCOMMIT OFF///create sequence if not exists object_id;");
110: conn.close();
111: }
112:
113: private void testUpperCaseLowerCaseDatabase() throws Exception {
114: if (File.separatorChar != '\\') {
115: return;
116: }
117: deleteDb("cases");
118: deleteDb("CaSeS");
119: Connection conn, conn2;
120: ResultSet rs;
121: conn = getConnection("cases");
122: Statement stat = conn.createStatement();
123: stat.execute("CHECKPOINT");
124: stat.execute("CREATE TABLE TEST(ID INT)");
125: stat.execute("INSERT INTO TEST VALUES(1)");
126: stat.execute("CHECKPOINT");
127:
128: conn2 = getConnection("CaSeS");
129: rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
130: check(rs.next());
131: conn2.close();
132:
133: conn.close();
134:
135: conn = getConnection("cases");
136: rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
137: check(rs.next());
138: conn.close();
139:
140: conn = getConnection("CaSeS");
141: rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
142: check(rs.next());
143: conn.close();
144:
145: deleteDb("cases");
146: deleteDb("CaSeS");
147:
148: }
149:
150: private void testManualCommitSet() throws Exception {
151: deleteDb("cases");
152: Connection conn = getConnection("cases");
153: Connection conn2 = getConnection("cases");
154: conn.setAutoCommit(false);
155: conn2.setAutoCommit(false);
156: conn.createStatement().execute("SET MODE REGULAR");
157: conn2.createStatement().execute("SET MODE REGULAR");
158: conn.close();
159: conn2.close();
160: }
161:
162: private void testSchemaIdentityReconnect() throws Exception {
163: deleteDb("cases");
164: Connection conn = getConnection("cases");
165: Statement stat = conn.createStatement();
166: stat.execute("create schema s authorization sa");
167: stat.execute("create table s.test(id identity)");
168: conn.close();
169: conn = getConnection("cases");
170: ResultSet rs = conn.createStatement().executeQuery(
171: "SELECT * FROM S.TEST");
172: while (rs.next()) {
173: // ignore
174: }
175: conn.close();
176: }
177:
178: private void testDisconnect() throws Exception {
179: if (config.networked || config.codeCoverage) {
180: return;
181: }
182: deleteDb("cases");
183: Connection conn = getConnection("cases");
184: final Statement stat = conn.createStatement();
185: stat.execute("CREATE TABLE TEST(ID IDENTITY)");
186: for (int i = 0; i < 1000; i++) {
187: stat.execute("INSERT INTO TEST() VALUES()");
188: }
189: final SQLException[] stopped = new SQLException[1];
190: Thread t = new Thread(new Runnable() {
191: public void run() {
192: try {
193: long time = System.currentTimeMillis();
194: ResultSet rs = stat
195: .executeQuery("SELECT MAX(T.ID) FROM TEST T, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST, TEST");
196: rs.next();
197: time = System.currentTimeMillis() - time;
198: TestBase.logError("query was too quick; result: "
199: + rs.getInt(1) + " time:" + time, null);
200: } catch (SQLException e) {
201: stopped[0] = e;
202: // ok
203: }
204: }
205: });
206: t.start();
207: Thread.sleep(300);
208: long time = System.currentTimeMillis();
209: conn.close();
210: t.join(5000);
211: if (stopped[0] == null) {
212: error("query still running");
213: } else {
214: checkNotGeneralException(stopped[0]);
215: }
216: time = System.currentTimeMillis() - time;
217: if (time > 5000) {
218: error("closing took " + time);
219: }
220: deleteDb("cases");
221: }
222:
223: private void testExecuteTrace() throws Exception {
224: deleteDb("cases");
225: Connection conn = getConnection("cases");
226: Statement stat = conn.createStatement();
227: ResultSet rs = stat
228: .executeQuery("SELECT ? FROM DUAL {1: 'Hello'}");
229: rs.next();
230: check("Hello", rs.getString(1));
231: checkFalse(rs.next());
232:
233: rs = stat
234: .executeQuery("SELECT ? FROM DUAL UNION ALL SELECT ? FROM DUAL {1: 'Hello', 2:'World' }");
235: rs.next();
236: check("Hello", rs.getString(1));
237: rs.next();
238: check("World", rs.getString(1));
239: checkFalse(rs.next());
240:
241: conn.close();
242: }
243:
244: private void testAlterTableReconnect() throws Exception {
245: deleteDb("cases");
246: Connection conn = getConnection("cases");
247: Statement stat = conn.createStatement();
248: stat.execute("create table test(id identity);");
249: stat.execute("insert into test values(1);");
250: try {
251: stat
252: .execute("alter table test add column name varchar not null;");
253: error();
254: } catch (SQLException e) {
255: checkNotGeneralException(e);
256: }
257: conn.close();
258: conn = getConnection("cases");
259: ResultSet rs = conn.createStatement().executeQuery(
260: "SELECT * FROM TEST");
261: rs.next();
262: check(rs.getString(1), "1");
263: checkFalse(rs.next());
264: stat = conn.createStatement();
265: stat.execute("drop table test");
266: stat.execute("create table test(id identity)");
267: stat.execute("insert into test values(1)");
268: stat
269: .execute("alter table test alter column id set default 'x'");
270: conn.close();
271: conn = getConnection("cases");
272: stat = conn.createStatement();
273: rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
274: rs.next();
275: check(rs.getString(1), "1");
276: checkFalse(rs.next());
277: stat.execute("drop table test");
278: stat.execute("create table test(id identity)");
279: stat.execute("insert into test values(1)");
280: try {
281: stat.execute("alter table test alter column id date");
282: error();
283: } catch (SQLException e) {
284: checkNotGeneralException(e);
285: }
286: conn.close();
287: conn = getConnection("cases");
288: rs = conn.createStatement().executeQuery("SELECT * FROM TEST");
289: rs.next();
290: check(rs.getString(1), "1");
291: checkFalse(rs.next());
292: conn.close();
293: }
294:
295: private void testCollation() throws Exception {
296: deleteDb("cases");
297: Connection conn = getConnection("cases");
298: Statement stat = conn.createStatement();
299: stat.execute("SET COLLATION ENGLISH STRENGTH PRIMARY");
300: stat
301: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
302: stat
303: .execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'WORLD'), (4, 'HELLO')");
304: stat.execute("create index idxname on test(name)");
305: ResultSet rs;
306: rs = stat.executeQuery("select name from test order by name");
307: rs.next();
308: check(rs.getString(1), "Hello");
309: rs.next();
310: check(rs.getString(1), "HELLO");
311: rs.next();
312: check(rs.getString(1), "World");
313: rs.next();
314: check(rs.getString(1), "WORLD");
315: rs = stat
316: .executeQuery("select name from test where name like 'He%'");
317: rs.next();
318: check(rs.getString(1), "Hello");
319: rs.next();
320: check(rs.getString(1), "HELLO");
321: conn.close();
322: }
323:
324: private void testPersistentSettings() throws Exception {
325: deleteDb("cases");
326: Connection conn = getConnection("cases");
327: Statement stat = conn.createStatement();
328: stat.execute("SET COLLATION de_DE");
329: stat
330: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
331: stat.execute("CREATE INDEX IDXNAME ON TEST(NAME)");
332: // \u00f6 = oe
333: stat
334: .execute("INSERT INTO TEST VALUES(1, 'B\u00f6hlen'), (2, 'Bach'), (3, 'Bucher')");
335: conn.close();
336: conn = getConnection("cases");
337: ResultSet rs = conn.createStatement().executeQuery(
338: "SELECT NAME FROM TEST ORDER BY NAME");
339: rs.next();
340: check(rs.getString(1), "Bach");
341: rs.next();
342: check(rs.getString(1), "B\u00f6hlen");
343: rs.next();
344: check(rs.getString(1), "Bucher");
345: conn.close();
346: }
347:
348: private void testInsertSelectUnion() throws Exception {
349: deleteDb("cases");
350: Connection conn = getConnection("cases");
351: Statement stat = conn.createStatement();
352: stat
353: .execute("CREATE TABLE TEST(ORDER_ID INT PRIMARY KEY, ORDER_DATE DATETIME, USER_ID INT ,"
354: + "DESCRIPTION VARCHAR, STATE VARCHAR, TRACKING_ID VARCHAR)");
355: Timestamp orderDate = Timestamp.valueOf("2005-05-21 17:46:00");
356: String sql = "insert into TEST (ORDER_ID,ORDER_DATE,USER_ID,DESCRIPTION,STATE,TRACKING_ID) "
357: + "select cast(? as int),cast(? as date),cast(? as int),cast(? as varchar),cast(? as varchar),cast(? as varchar) union all select ?,?,?,?,?,?";
358: PreparedStatement ps = conn.prepareStatement(sql);
359: ps.setInt(1, 5555);
360: ps.setTimestamp(2, orderDate);
361: ps.setInt(3, 2222);
362: ps.setString(4, "test desc");
363: ps.setString(5, "test_state");
364: ps.setString(6, "testid");
365: ps.setInt(7, 5556);
366: ps.setTimestamp(8, orderDate);
367: ps.setInt(9, 2222);
368: ps.setString(10, "test desc");
369: ps.setString(11, "test_state");
370: ps.setString(12, "testid");
371: check(ps.executeUpdate(), 2);
372: ps.close();
373: conn.close();
374: }
375:
376: private void testViewReconnect() throws Exception {
377: trace("testViewReconnect");
378: deleteDb("cases");
379: Connection conn = getConnection("cases");
380: Statement stat = conn.createStatement();
381: stat.execute("create table test(id int)");
382: stat.execute("create view abc as select * from test");
383: stat.execute("drop table test");
384: conn.close();
385: conn = getConnection("cases");
386: stat = conn.createStatement();
387: try {
388: stat.execute("select * from abc");
389: error();
390: } catch (SQLException e) {
391: checkNotGeneralException(e);
392: }
393: conn.close();
394: }
395:
396: private void testDefaultQueryReconnect() throws Exception {
397: trace("testDefaultQueryReconnect");
398: deleteDb("cases");
399: Connection conn = getConnection("cases");
400: Statement stat = conn.createStatement();
401: stat.execute("create table parent(id int)");
402: stat.execute("insert into parent values(1)");
403: stat
404: .execute("create table test(id int default (select max(id) from parent), name varchar)");
405:
406: conn.close();
407: conn = getConnection("cases");
408: stat = conn.createStatement();
409: conn.setAutoCommit(false);
410: stat.execute("insert into parent values(2)");
411: stat.execute("insert into test(name) values('test')");
412: ResultSet rs = stat.executeQuery("select * from test");
413: rs.next();
414: check(rs.getInt(1), 2);
415: checkFalse(rs.next());
416: conn.close();
417: }
418:
419: private void testBigString() throws Exception {
420: trace("testBigString");
421: deleteDb("cases");
422: Connection conn = getConnection("cases");
423: Statement stat = conn.createStatement();
424: stat.execute("DROP TABLE IF EXISTS TEST");
425: stat
426: .execute("CREATE TABLE TEST(ID INT, TEXT VARCHAR, TEXT_C CLOB)");
427: PreparedStatement prep = conn
428: .prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
429: int len = getSize(1000, 66000);
430: char[] buff = new char[len];
431:
432: // The UCS code values 0xd800-0xdfff (UTF-16 surrogates)
433: // as well as 0xfffe and 0xffff (UCS non-characters)
434: // should not appear in conforming UTF-8 streams.
435: // (String.getBytes("UTF-8") only returns 1 byte for 0xd800-0xdfff)
436:
437: Random random = new Random();
438: random.setSeed(1);
439: for (int i = 0; i < len; i++) {
440: char c;
441: do {
442: c = (char) random.nextInt();
443: } while (c >= 0xd800 && c <= 0xdfff);
444: buff[i] = c;
445: }
446: String big = new String(buff);
447: prep.setInt(1, 1);
448: prep.setString(2, big);
449: prep.setString(3, big);
450: prep.execute();
451: prep.setInt(1, 2);
452: prep.setCharacterStream(2, new StringReader(big), 0);
453: prep.setCharacterStream(3, new StringReader(big), 0);
454: prep.execute();
455: ResultSet rs = stat
456: .executeQuery("SELECT * FROM TEST ORDER BY ID");
457: rs.next();
458: check(rs.getInt(1), 1);
459: check(rs.getString(2), big);
460: check(readString(rs.getCharacterStream(2)), big);
461: check(rs.getString(3), big);
462: check(readString(rs.getCharacterStream(3)), big);
463: rs.next();
464: check(rs.getInt(1), 2);
465: check(rs.getString(2), big);
466: check(readString(rs.getCharacterStream(2)), big);
467: check(rs.getString(3), big);
468: check(readString(rs.getCharacterStream(3)), big);
469: rs.next();
470: checkFalse(rs.next());
471: conn.close();
472: }
473:
474: private void testConstraintReconnect() throws Exception {
475: trace("testConstraintReconnect");
476: deleteDb("cases");
477: Connection conn = getConnection("cases");
478: Statement stat = conn.createStatement();
479: stat.execute("drop table if exists parent");
480: stat.execute("drop table if exists child");
481: stat.execute("create table parent(id int)");
482: stat
483: .execute("create table child(c_id int, p_id int, foreign key(p_id) references parent(id))");
484: stat.execute("insert into parent values(1), (2)");
485: stat.execute("insert into child values(1, 1)");
486: stat.execute("insert into child values(2, 2)");
487: stat.execute("insert into child values(3, 2)");
488: stat.execute("delete from child");
489: conn.close();
490: conn = getConnection("cases");
491: conn.close();
492: }
493:
494: private void testDoubleRecovery() throws Exception {
495: if (config.networked) {
496: return;
497: }
498: trace("testDoubleRecovery");
499: deleteDb("cases");
500: Connection conn = getConnection("cases");
501: deleteDb("twoPhaseCommit");
502: Statement stat = conn.createStatement();
503: stat.execute("SET WRITE_DELAY 0");
504: stat.execute("DROP TABLE IF EXISTS TEST");
505: stat
506: .execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))");
507: stat.execute("INSERT INTO TEST VALUES(1, 'Hello')");
508: conn.setAutoCommit(false);
509: stat.execute("INSERT INTO TEST VALUES(2, 'World')");
510: crash(conn);
511:
512: conn = getConnection("cases");
513: stat = conn.createStatement();
514: stat.execute("SET WRITE_DELAY 0");
515: stat.execute("INSERT INTO TEST VALUES(3, 'Break')");
516: crash(conn);
517:
518: conn = getConnection("cases");
519: stat = conn.createStatement();
520: ResultSet rs = stat
521: .executeQuery("SELECT * FROM TEST ORDER BY ID");
522: rs.next();
523: check(rs.getInt(1), 1);
524: check(rs.getString(2), "Hello");
525: rs.next();
526: check(rs.getInt(1), 3);
527: check(rs.getString(2), "Break");
528: conn.close();
529: }
530:
531: private void testRenameReconnect() throws Exception {
532: trace("testRenameReconnect");
533: deleteDb("cases");
534: Connection conn = getConnection("cases");
535: conn
536: .createStatement()
537: .execute(
538: "CREATE TABLE TEST_SEQ(ID INT IDENTITY, NAME VARCHAR(255))");
539: conn.createStatement().execute(
540: "CREATE TABLE TEST(ID INT PRIMARY KEY)");
541: conn.createStatement().execute(
542: "ALTER TABLE TEST RENAME TO TEST2");
543: conn
544: .createStatement()
545: .execute(
546: "CREATE TABLE TEST_B(ID INT PRIMARY KEY, NAME VARCHAR, UNIQUE(NAME))");
547: conn.close();
548: conn = getConnection("cases");
549: conn.createStatement().execute(
550: "INSERT INTO TEST_SEQ(NAME) VALUES('Hi')");
551: ResultSet rs = conn.createStatement().executeQuery(
552: "CALL IDENTITY()");
553: rs.next();
554: check(rs.getInt(1), 1);
555: conn.createStatement().execute("SELECT * FROM TEST2");
556: conn.createStatement().execute("SELECT * FROM TEST_B");
557: conn.createStatement().execute(
558: "ALTER TABLE TEST_B RENAME TO TEST_B2");
559: conn.close();
560: conn = getConnection("cases");
561: conn.createStatement().execute("SELECT * FROM TEST_B2");
562: conn.createStatement().execute(
563: "INSERT INTO TEST_SEQ(NAME) VALUES('World')");
564: rs = conn.createStatement().executeQuery("CALL IDENTITY()");
565: rs.next();
566: check(rs.getInt(1), 2);
567: conn.close();
568: }
569:
570: private void testAllSizes() throws Exception {
571: trace("testAllSizes");
572: deleteDb("cases");
573: Connection conn = getConnection("cases");
574: Statement stat = conn.createStatement();
575: stat
576: .execute("CREATE TABLE TEST(A INT, B INT, C INT, DATA VARCHAR)");
577: int increment = getSize(100, 1);
578: for (int i = 1; i < 500; i += increment) {
579: StringBuffer buff = new StringBuffer();
580: buff.append("CREATE TABLE TEST");
581: for (int j = 0; j < i; j++) {
582: buff.append('a');
583: }
584: buff.append("(ID INT)");
585: String sql = buff.toString();
586: stat.execute(sql);
587: stat.execute("INSERT INTO TEST VALUES(" + i + ", 0, 0, '"
588: + sql + "')");
589: }
590: conn.close();
591: conn = getConnection("cases");
592: stat = conn.createStatement();
593: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
594: while (rs.next()) {
595: int id = rs.getInt(1);
596: String s = rs.getString("DATA");
597: if (!s.endsWith(")")) {
598: error("id=" + id);
599: }
600: }
601: conn.close();
602: }
603:
604: private void testSelectForUpdate() throws Exception {
605: trace("testSelectForUpdate");
606: deleteDb("cases");
607: Connection conn1 = getConnection("cases");
608: Statement stat1 = conn1.createStatement();
609: stat1.execute("CREATE TABLE TEST(ID INT)");
610: stat1.execute("INSERT INTO TEST VALUES(1)");
611: conn1.setAutoCommit(false);
612: stat1.execute("SELECT * FROM TEST FOR UPDATE");
613: Connection conn2 = getConnection("cases");
614: Statement stat2 = conn2.createStatement();
615: try {
616: stat2.execute("UPDATE TEST SET ID=2");
617: error();
618: } catch (SQLException e) {
619: checkNotGeneralException(e);
620: }
621: conn1.commit();
622: stat2.execute("UPDATE TEST SET ID=2");
623: conn1.close();
624: conn2.close();
625: }
626:
627: private void testMutableObjects() throws Exception {
628: trace("testMutableObjects");
629: deleteDb("cases");
630: Connection conn = getConnection("cases");
631: Statement stat = conn.createStatement();
632: stat
633: .execute("CREATE TABLE TEST(ID INT, D DATE, T TIME, TS TIMESTAMP)");
634: stat
635: .execute("INSERT INTO TEST VALUES(1, '2001-01-01', '20:00:00', '2002-02-02 22:22:22.2')");
636: stat
637: .execute("INSERT INTO TEST VALUES(1, '2001-01-01', '20:00:00', '2002-02-02 22:22:22.2')");
638: ResultSet rs = stat.executeQuery("SELECT * FROM TEST");
639: rs.next();
640: Date d1 = rs.getDate("D");
641: Time t1 = rs.getTime("T");
642: Timestamp ts1 = rs.getTimestamp("TS");
643: rs.next();
644: Date d2 = rs.getDate("D");
645: Time t2 = rs.getTime("T");
646: Timestamp ts2 = rs.getTimestamp("TS");
647: check(ts1 != ts2);
648: check(d1 != d2);
649: check(t1 != t2);
650: check(t2 != rs.getObject("T"));
651: check(d2 != rs.getObject("D"));
652: check(ts2 != rs.getObject("TS"));
653: checkFalse(rs.next());
654: conn.close();
655: }
656:
657: private void testCreateDrop() throws Exception {
658: trace("testCreateDrop");
659: deleteDb("cases");
660: Connection conn = getConnection("cases");
661: Statement stat = conn.createStatement();
662: stat.execute("create table employee(id int, "
663: + "firstName VARCHAR(50), " + "salary decimal(10, 2), "
664: + "superior_id int, "
665: + "CONSTRAINT PK_employee PRIMARY KEY (id), "
666: + "CONSTRAINT FK_superior FOREIGN KEY (superior_id) "
667: + "REFERENCES employee(ID))");
668: stat.execute("DROP TABLE employee");
669: conn.close();
670: conn = getConnection("cases");
671: conn.close();
672: }
673:
674: private void testPolePos() throws Exception {
675: trace("testPolePos");
676: // poleposition-0.20
677:
678: Connection c0 = getConnection("cases");
679: c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
680: c0
681: .createStatement()
682: .executeUpdate(
683: "create table australia (ID INTEGER NOT NULL, Name VARCHAR(100), firstName VARCHAR(100), Points INTEGER, LicenseID INTEGER, PRIMARY KEY(ID))");
684: c0.createStatement().executeUpdate("COMMIT");
685: c0.close();
686:
687: c0 = getConnection("cases");
688: c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
689: PreparedStatement p15 = c0
690: .prepareStatement("insert into australia (id,Name,firstName,Points,LicenseID) values (?,?,?,?,?)");
691: int len = getSize(1, 1000);
692: for (int i = 0; i < len; i++) {
693: p15.setInt(1, i);
694: p15.setString(2, "Pilot_" + i);
695: p15.setString(3, "Herkules");
696: p15.setInt(4, i);
697: p15.setInt(5, i);
698: p15.executeUpdate();
699: }
700: c0.createStatement().executeUpdate("COMMIT");
701: c0.close();
702:
703: // c0=getConnection("cases");
704: // c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
705: // c0.createStatement().executeQuery("select * from australia");
706: // c0.createStatement().executeQuery("select * from australia");
707: // c0.close();
708:
709: // c0=getConnection("cases");
710: // c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
711: // c0.createStatement().executeUpdate("COMMIT");
712: // c0.createStatement().executeUpdate("delete from australia");
713: // c0.createStatement().executeUpdate("COMMIT");
714: // c0.close();
715:
716: c0 = getConnection("cases");
717: c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
718: c0.createStatement().executeUpdate("drop table australia");
719: c0
720: .createStatement()
721: .executeUpdate(
722: "create table australia (ID INTEGER NOT NULL, Name VARCHAR(100), firstName VARCHAR(100), Points INTEGER, LicenseID INTEGER, PRIMARY KEY(ID))");
723: c0.createStatement().executeUpdate("COMMIT");
724: c0.close();
725:
726: c0 = getConnection("cases");
727: c0.createStatement().executeUpdate("SET AUTOCOMMIT FALSE");
728: PreparedStatement p65 = c0
729: .prepareStatement("insert into australia (id,Name,FirstName,Points,LicenseID) values (?,?,?,?,?)");
730: len = getSize(1, 1000);
731: for (int i = 0; i < len; i++) {
732: p65.setInt(1, i);
733: p65.setString(2, "Pilot_" + i);
734: p65.setString(3, "Herkules");
735: p65.setInt(4, i);
736: p65.setInt(5, i);
737: p65.executeUpdate();
738: }
739: c0.createStatement().executeUpdate("COMMIT");
740: c0.createStatement().executeUpdate("COMMIT");
741: c0.createStatement().executeUpdate("COMMIT");
742: c0.close();
743:
744: c0 = getConnection("cases");
745: c0.close();
746: }
747:
748: private void testQuick() throws Exception {
749: trace("testQuick");
750: deleteDb("cases");
751:
752: Connection c0 = getConnection("cases");
753: c0.createStatement().executeUpdate(
754: "create table test (ID int PRIMARY KEY)");
755: c0.createStatement()
756: .executeUpdate("insert into test values(1)");
757: c0.createStatement().executeUpdate("drop table test");
758: c0.createStatement().executeUpdate(
759: "create table test (ID int PRIMARY KEY)");
760: c0.close();
761:
762: c0 = getConnection("cases");
763: c0.createStatement()
764: .executeUpdate("insert into test values(1)");
765: c0.close();
766:
767: c0 = getConnection("cases");
768: c0.close();
769: }
770:
771: }
|