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.sql.Connection;
009: import java.sql.DatabaseMetaData;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Savepoint;
013: import java.sql.Statement;
014: import java.util.Random;
015: import java.util.Vector;
016:
017: import org.h2.test.TestBase;
018:
019: /**
020: * Transactional tests, including transaction isolation tests, and tests related
021: * to savepoints.
022: */
023: public class TestTransaction extends TestBase {
024:
025: public void test() throws Exception {
026: testReferential();
027: testSavepoint();
028: testIsolation();
029: }
030:
031: private void testReferential() throws Exception {
032: deleteDb("transaction");
033: Connection c1 = getConnection("transaction");
034: c1.setAutoCommit(false);
035: Statement s1 = c1.createStatement();
036: s1.execute("drop table if exists a");
037: s1.execute("drop table if exists b");
038: s1
039: .execute("create table a (id integer identity not null, code varchar(10) not null, primary key(id))");
040: s1
041: .execute("create table b (name varchar(100) not null, a integer, primary key(name), foreign key(a) references a(id))");
042: Connection c2 = getConnection("transaction");
043: c2.setAutoCommit(false);
044: s1.executeUpdate("insert into A(code) values('one')");
045: Statement s2 = c2.createStatement();
046: try {
047: s2.executeUpdate("insert into B values('two', 1)");
048: error();
049: } catch (SQLException e) {
050: checkNotGeneralException(e);
051: }
052: c2.commit();
053: c1.rollback();
054: c1.close();
055: c2.close();
056: }
057:
058: public void testSavepoint() throws Exception {
059: deleteDb("transaction");
060: Connection conn = getConnection("transaction");
061: Statement stat = conn.createStatement();
062: stat.execute("CREATE TABLE TEST0(ID IDENTITY, NAME VARCHAR)");
063: stat
064: .execute("CREATE TABLE TEST1(NAME VARCHAR, ID IDENTITY, X TIMESTAMP DEFAULT CURRENT_TIMESTAMP)");
065: conn.setAutoCommit(false);
066: int[] count = new int[2];
067: int[] countCommitted = new int[2];
068: int[] countSave = new int[2];
069: int len = getSize(2000, 10000);
070: Random random = new Random(10);
071: Savepoint sp = null;
072: for (int i = 0; i < len; i++) {
073: int tableId = random.nextInt(2);
074: String table = "TEST" + tableId;
075: int op = random.nextInt(6);
076: switch (op) {
077: case 0:
078: stat.execute("INSERT INTO " + table
079: + "(NAME) VALUES('op" + i + "')");
080: count[tableId]++;
081: break;
082: case 1:
083: if (count[tableId] > 0) {
084: stat.execute("DELETE FROM " + table
085: + " WHERE ID=SELECT MIN(ID) FROM " + table);
086: count[tableId]--;
087: }
088: break;
089: case 2:
090: sp = conn.setSavepoint();
091: countSave[0] = count[0];
092: countSave[1] = count[1];
093: break;
094: case 3:
095: if (sp != null) {
096: conn.rollback(sp);
097: count[0] = countSave[0];
098: count[1] = countSave[1];
099: }
100: break;
101: case 4:
102: conn.commit();
103: sp = null;
104: countCommitted[0] = count[0];
105: countCommitted[1] = count[1];
106: break;
107: case 5:
108: conn.rollback();
109: sp = null;
110: count[0] = countCommitted[0];
111: count[1] = countCommitted[1];
112: break;
113: }
114: checkTableCount(stat, "TEST0", count[0]);
115: checkTableCount(stat, "TEST1", count[1]);
116: }
117: conn.close();
118: }
119:
120: private void checkTableCount(Statement stat, String tableName,
121: int count) throws Exception {
122: ResultSet rs;
123: rs = stat.executeQuery("SELECT COUNT(*) FROM " + tableName);
124: rs.next();
125: check(count, rs.getInt(1));
126: }
127:
128: public void testIsolation() throws Exception {
129: Connection conn = getConnection("transaction");
130: trace("default TransactionIsolation="
131: + conn.getTransactionIsolation());
132: conn
133: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
134: check(conn.getTransactionIsolation() == Connection.TRANSACTION_READ_COMMITTED);
135: conn
136: .setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
137: check(conn.getTransactionIsolation() == Connection.TRANSACTION_SERIALIZABLE);
138: Statement stat = conn.createStatement();
139: check(conn.getAutoCommit());
140: conn.setAutoCommit(false);
141: checkFalse(conn.getAutoCommit());
142: conn.setAutoCommit(true);
143: check(conn.getAutoCommit());
144: test(stat, "CREATE TABLE TEST(ID INT PRIMARY KEY)");
145: conn.commit();
146: test(stat, "INSERT INTO TEST VALUES(0)");
147: conn.rollback();
148: testValue(stat, "SELECT COUNT(*) FROM TEST", "1");
149: conn.setAutoCommit(false);
150: test(stat, "DELETE FROM TEST");
151: // testValue("SELECT COUNT(*) FROM TEST", "0");
152: conn.rollback();
153: testValue(stat, "SELECT COUNT(*) FROM TEST", "1");
154: conn.commit();
155: conn.setAutoCommit(true);
156: testNestedResultSets(conn);
157: conn.setAutoCommit(false);
158: testNestedResultSets(conn);
159: conn.close();
160: }
161:
162: void testNestedResultSets(Connection conn) throws Exception {
163: Statement stat = conn.createStatement();
164: test(stat,
165: "CREATE TABLE NEST1(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
166: test(stat,
167: "CREATE TABLE NEST2(ID INT PRIMARY KEY,VALUE VARCHAR(255))");
168: DatabaseMetaData meta = conn.getMetaData();
169: Vector result;
170: ResultSet rs1, rs2;
171: result = new Vector();
172: rs1 = meta.getTables(null, null, "NEST%", null);
173: while (rs1.next()) {
174: String table = rs1.getString("TABLE_NAME");
175: rs2 = meta.getColumns(null, null, table, null);
176: while (rs2.next()) {
177: String column = rs2.getString("COLUMN_NAME");
178: trace("Table: " + table + " Column: " + column);
179: result.add(table + "." + column);
180: }
181: }
182: if (result.size() != 4) {
183: error("Wrong result, should be NEST1.ID, NEST1.NAME, NEST2.ID, NEST2.NAME but is "
184: + result);
185: }
186: result = new Vector();
187: test(stat, "INSERT INTO NEST1 VALUES(1,'A')");
188: test(stat, "INSERT INTO NEST1 VALUES(2,'B')");
189: test(stat, "INSERT INTO NEST2 VALUES(1,'1')");
190: test(stat, "INSERT INTO NEST2 VALUES(2,'2')");
191: Statement s1 = conn.createStatement();
192: Statement s2 = conn.createStatement();
193: rs1 = s1.executeQuery("SELECT * FROM NEST1 ORDER BY ID");
194: while (rs1.next()) {
195: rs2 = s2.executeQuery("SELECT * FROM NEST2 ORDER BY ID");
196: while (rs2.next()) {
197: String v1 = rs1.getString("VALUE");
198: String v2 = rs2.getString("VALUE");
199: result.add(v1 + "/" + v2);
200: }
201: }
202: if (result.size() != 4) {
203: error("Wrong result, should be A/1, A/2, B/1, B/2 but is "
204: + result);
205: }
206: result = new Vector();
207: rs1 = s1.executeQuery("SELECT * FROM NEST1 ORDER BY ID");
208: rs2 = s1.executeQuery("SELECT * FROM NEST2 ORDER BY ID");
209: try {
210: rs1.next();
211: error("next worked on a closed result set");
212: } catch (SQLException e) {
213: checkNotGeneralException(e);
214: }
215: // this is already closed, so but closing again should no do any harm
216: rs1.close();
217: while (rs2.next()) {
218: String v1 = rs2.getString("VALUE");
219: result.add(v1);
220: }
221: if (result.size() != 2) {
222: error("Wrong result, should be A, B but is " + result);
223: }
224: test(stat, "DROP TABLE NEST1");
225: test(stat, "DROP TABLE NEST2");
226: }
227:
228: void testValue(Statement stat, String sql, String data)
229: throws Exception {
230: ResultSet rs = stat.executeQuery(sql);
231: rs.next();
232: String s = rs.getString(1);
233: if (s == null ? (data != null) : (!s.equals(data))) {
234: error("s= " + s + " should be: " + data);
235: }
236: }
237:
238: void test(Statement stat, String sql) throws Exception {
239: trace(sql);
240: stat.execute(sql);
241: }
242:
243: }
|