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.synth;
007:
008: import java.sql.Connection;
009: import java.sql.DriverManager;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.ResultSetMetaData;
013: import java.sql.SQLException;
014: import java.sql.Statement;
015: import java.util.ArrayList;
016: import java.util.Collections;
017: import java.util.Random;
018:
019: import org.h2.test.TestBase;
020: import org.h2.util.StringUtils;
021:
022: /**
023: * A test that runs random join statements against two databases and compares
024: * the results.
025: */
026: public class TestJoin extends TestBase {
027:
028: private ArrayList connections = new ArrayList();
029: private Random random;
030: private int paramCount;
031: private StringBuffer buff;
032:
033: public void test() throws Exception {
034: String old = baseDir;
035: baseDir = TestBase.getTestDir("join");
036: testJoin();
037: baseDir = old;
038: }
039:
040: private void testJoin() throws Exception {
041: deleteDb("join");
042: String shortestFailed = null;
043:
044: Connection c1 = getConnection("join");
045: connections.add(c1);
046:
047: Class.forName("org.postgresql.Driver");
048: Connection c2 = DriverManager.getConnection(
049: "jdbc:postgresql:test", "sa", "sa");
050: connections.add(c2);
051:
052: // Class.forName("com.mysql.jdbc.Driver");
053: // Connection c2 =
054: // DriverManager.getConnection("jdbc:mysql://localhost/test", "sa",
055: // "sa");
056: // connections.add(c2);
057:
058: // Class.forName("org.hsqldb.jdbcDriver");
059: // Connection c2 = DriverManager.getConnection("jdbc:hsqldb:join", "sa",
060: // "");
061: // connections.add(c2);
062:
063: /*
064: DROP TABLE ONE;
065: DROP TABLE TWO;
066: CREATE TABLE ONE(A INT PRIMARY KEY, B INT);
067: INSERT INTO ONE VALUES(0, NULL);
068: INSERT INTO ONE VALUES(1, 0);
069: INSERT INTO ONE VALUES(2, 1);
070: INSERT INTO ONE VALUES(3, 4);
071: CREATE TABLE TWO(A INT PRIMARY KEY, B INT);
072: INSERT INTO TWO VALUES(0, NULL);
073: INSERT INTO TWO VALUES(1, 0);
074: INSERT INTO TWO VALUES(2, 2);
075: INSERT INTO TWO VALUES(3, 3);
076: INSERT INTO TWO VALUES(4, NULL);
077: */
078:
079: execute("DROP TABLE ONE", null, true);
080: execute("DROP TABLE TWO", null, true);
081: execute("CREATE TABLE ONE(A INT PRIMARY KEY, B INT)", null);
082: execute("INSERT INTO ONE VALUES(0, NULL)", null);
083: execute("INSERT INTO ONE VALUES(1, 0)", null);
084: execute("INSERT INTO ONE VALUES(2, 1)", null);
085: execute("INSERT INTO ONE VALUES(3, 4)", null);
086: execute("CREATE TABLE TWO(A INT PRIMARY KEY, B INT)", null);
087: execute("INSERT INTO TWO VALUES(0, NULL)", null);
088: execute("INSERT INTO TWO VALUES(1, 0)", null);
089: execute("INSERT INTO TWO VALUES(2, 2)", null);
090: execute("INSERT INTO TWO VALUES(3, 3)", null);
091: execute("INSERT INTO TWO VALUES(4, NULL)", null);
092: random = new Random();
093: long start = System.currentTimeMillis();
094: for (int i = 0;; i++) {
095: paramCount = 0;
096: buff = new StringBuffer();
097: long time = System.currentTimeMillis();
098: if (time - start > 5000) {
099: printTime("i:" + i);
100: start = time;
101: }
102: buff.append("SELECT ");
103: int tables = 1 + random.nextInt(5);
104: for (int j = 0; j < tables; j++) {
105: if (j > 0) {
106: buff.append(", ");
107: }
108: buff.append("T" + (char) ('0' + j) + ".A");
109: }
110: buff.append(" FROM ");
111: appendRandomTable();
112: buff.append(" T0 ");
113: for (int j = 1; j < tables; j++) {
114: if (random.nextBoolean()) {
115: buff.append("INNER");
116: } else {
117: // if(random.nextInt(4)==1) {
118: // buff.append("RIGHT");
119: // } else {
120: buff.append("LEFT");
121: // }
122: }
123: buff.append(" JOIN ");
124: appendRandomTable();
125: buff.append(" T");
126: buff.append((char) ('0' + j));
127: buff.append(" ON ");
128: appendRandomCondition(j);
129: }
130: if (random.nextBoolean()) {
131: buff.append("WHERE ");
132: appendRandomCondition(tables - 1);
133: }
134: String sql = buff.toString();
135: Object[] params = new Object[paramCount];
136: for (int j = 0; j < paramCount; j++) {
137: params[j] = random.nextInt(4) == 1 ? null
138: : new Integer(random.nextInt(10) - 3);
139: }
140: try {
141: execute(sql, params);
142: } catch (Exception e) {
143: if (shortestFailed == null
144: || shortestFailed.length() > sql.length()) {
145: TestBase.logError("/*SHORT*/ " + sql, null);
146: shortestFailed = sql;
147: }
148: }
149: }
150: // c1.close();
151: // c2.close();
152: }
153:
154: private void appendRandomTable() {
155: if (random.nextBoolean()) {
156: buff.append("ONE");
157: } else {
158: buff.append("TWO");
159: }
160: }
161:
162: private void appendRandomCondition(int j) {
163: if (random.nextInt(10) == 1) {
164: buff.append("NOT ");
165: appendRandomCondition(j);
166: } else if (random.nextInt(5) == 1) {
167: buff.append("(");
168: appendRandomCondition(j);
169: if (random.nextBoolean()) {
170: buff.append(") OR (");
171: } else {
172: buff.append(") AND (");
173: }
174: appendRandomCondition(j);
175: buff.append(")");
176: } else {
177: if (j > 0 && random.nextBoolean()) {
178: buff.append("T" + (char) ('0' + j - 1) + ".A=T"
179: + (char) ('0' + j) + ".A ");
180: } else {
181: appendRandomConditionPart(j);
182: }
183: }
184: }
185:
186: private void appendRandomConditionPart(int j) {
187: int t1 = j <= 1 ? 0 : random.nextInt(j + 1);
188: int t2 = j <= 1 ? 0 : random.nextInt(j + 1);
189: String c1 = random.nextBoolean() ? "A" : "B";
190: String c2 = random.nextBoolean() ? "A" : "B";
191: buff.append("T" + (char) ('0' + t1));
192: buff.append("." + c1);
193: if (random.nextInt(4) == 1) {
194: if (random.nextInt(5) == 1) {
195: buff.append(" IS NOT NULL");
196: } else {
197: buff.append(" IS NULL");
198: }
199: } else {
200: if (random.nextInt(5) == 1) {
201: switch (random.nextInt(5)) {
202: case 0:
203: buff.append(">");
204: break;
205: case 1:
206: buff.append("<");
207: break;
208: case 2:
209: buff.append("<=");
210: break;
211: case 3:
212: buff.append(">=");
213: break;
214: case 4:
215: buff.append("<>");
216: break;
217: }
218: } else {
219: buff.append("=");
220: }
221: if (random.nextBoolean()) {
222: buff.append("T" + (char) ('0' + t2));
223: buff.append("." + c2);
224: } else {
225: buff.append(random.nextInt(5) - 1);
226: }
227: }
228: buff.append(" ");
229: }
230:
231: private void execute(String sql, Object[] params) throws Exception {
232: execute(sql, params, false);
233: }
234:
235: private void execute(String sql, Object[] params,
236: boolean ignoreDifference) throws Exception {
237: String first = null;
238: for (int i = 0; i < connections.size(); i++) {
239: Connection conn = (Connection) connections.get(i);
240: String s;
241: try {
242: Statement stat;
243: boolean result;
244: if (params == null || params.length == 0) {
245: stat = conn.createStatement();
246: result = stat.execute(sql);
247: } else {
248: PreparedStatement prep = conn.prepareStatement(sql);
249: stat = prep;
250: for (int j = 0; j < params.length; j++) {
251: prep.setObject(j + 1, params[j]);
252: }
253: result = prep.execute();
254: }
255: if (result) {
256: ResultSet rs = stat.getResultSet();
257: s = "rs: " + readResult(rs);
258: } else {
259: s = "updateCount: " + stat.getUpdateCount();
260: }
261: } catch (SQLException e) {
262: s = "exception";
263: }
264: if (i == 0) {
265: first = s;
266: } else {
267: if (!ignoreDifference && !s.equals(first)) {
268: throw new Exception("FAIL s:" + s + " first:"
269: + first + " sql:" + sql);
270: }
271: }
272: }
273: }
274:
275: private String readResult(ResultSet rs) throws SQLException {
276: StringBuffer buff = new StringBuffer();
277: ResultSetMetaData meta = rs.getMetaData();
278: int columnCount = meta.getColumnCount();
279: for (int i = 0; i < columnCount; i++) {
280: if (i > 0) {
281: buff.append(",");
282: }
283: buff.append(StringUtils.toUpperEnglish(meta
284: .getColumnLabel(i + 1)));
285: }
286: buff.append(":\n");
287: String result = buff.toString();
288: ArrayList list = new ArrayList();
289: while (rs.next()) {
290: buff = new StringBuffer();
291: for (int i = 0; i < columnCount; i++) {
292: if (i > 0) {
293: buff.append(",");
294: }
295: buff.append(rs.getString(i + 1));
296: }
297: list.add(buff.toString());
298: }
299: Collections.sort(list);
300: for (int i = 0; i < list.size(); i++) {
301: result += list.get(i) + "\n";
302: }
303: return result;
304: }
305:
306: }
|