|
1
5 package org.h2.test.synth;
6
7 import java.sql.Connection  ;
8 import java.sql.DriverManager ;
9 import java.sql.PreparedStatement ;
10 import java.sql.ResultSet ;
11 import java.sql.ResultSetMetaData ;
12 import java.sql.SQLException ;
13 import java.sql.Statement ;
14 import java.util.ArrayList ;
15 import java.util.Collections ;
16 import java.util.Random ;
17
18 import org.h2.test.TestBase;
19 import org.h2.util.StringUtils;
20
21 public class TestJoin extends TestBase {
22
23 private ArrayList connections = new ArrayList ();
24 private Random random;
25 private int paramCount;
26 private StringBuffer buff;
27
28 public void test() throws Exception {
29 BASE_DIR = "dataJoin";
30 testJoin();
31 BASE_DIR = "data";
32 }
33
34 private void testJoin() throws Exception {
35 deleteDb("join");
36 String shortestFailed = null;
37
38 Connection c1 = getConnection("join");
39 connections.add(c1);
40
41 Class.forName("org.postgresql.Driver");
42 Connection c2 = DriverManager.getConnection("jdbc:postgresql:test", "sa", "sa");
43 connections.add(c2);
44
45
49
53
68
69 execute("DROP TABLE ONE", null, true);
70 execute("DROP TABLE TWO", null, true);
71 execute("CREATE TABLE ONE(A INT PRIMARY KEY, B INT)", null);
72 execute("INSERT INTO ONE VALUES(0, NULL)", null);
73 execute("INSERT INTO ONE VALUES(1, 0)", null);
74 execute("INSERT INTO ONE VALUES(2, 1)", null);
75 execute("INSERT INTO ONE VALUES(3, 4)", null);
76 execute("CREATE TABLE TWO(A INT PRIMARY KEY, B INT)", null);
77 execute("INSERT INTO TWO VALUES(0, NULL)", null);
78 execute("INSERT INTO TWO VALUES(1, 0)", null);
79 execute("INSERT INTO TWO VALUES(2, 2)", null);
80 execute("INSERT INTO TWO VALUES(3, 3)", null);
81 execute("INSERT INTO TWO VALUES(4, NULL)", null);
82 random = new Random ();
83 long start = System.currentTimeMillis();
84 for(int i=0;; i++) {
85 paramCount = 0;
86 buff = new StringBuffer ();
87 long time = System.currentTimeMillis();
88 if(time - start > 5000) {
89 printTime("i:"+i);
90 start = time;
91 }
92 buff.append("SELECT ");
93 int tables = 1 + random.nextInt(5);
94 for(int j=0; j<tables; j++) {
95 if(j > 0) {
96 buff.append(", ");
97 }
98 buff.append("T" + (char)('0' + j) + ".A");
99 }
100 buff.append(" FROM ");
101 appendRandomTable();
102 buff.append(" T0 ");
103 for(int j=1; j<tables; j++) {
104 if(random.nextBoolean()) {
105 buff.append("INNER");
106 } else {
107 buff.append("LEFT");
111 }
113 buff.append(" JOIN ");
114 appendRandomTable();
115 buff.append(" T");
116 buff.append((char)('0' + j));
117 buff.append(" ON ");
118 appendRandomCondition(j);
119 }
120 if(random.nextBoolean()) {
121 buff.append("WHERE ");
122 appendRandomCondition(tables-1);
123 }
124 String sql = buff.toString();
125 Object [] params = new Object [paramCount];
126 for(int j=0; j<paramCount; j++) {
127 params[j] = random.nextInt(4)==1 ? null : new Integer (random.nextInt(10)-3);
128 }
129 try {
130 execute(sql, params);
131 } catch(Exception e) {
132 if(shortestFailed == null || shortestFailed.length() > sql.length()) {
133 TestBase.logError("/*SHORT*/ " + sql, null);
134 shortestFailed = sql;
135 }
136 }
137 }
138 }
141
142 private void appendRandomTable() {
143 if(random.nextBoolean()) {
144 buff.append("ONE");
145 } else {
146 buff.append("TWO");
147 }
148 }
149
150 private void appendRandomCondition(int j) {
151 if(random.nextInt(10)==1) {
152 buff.append("NOT ");
153 appendRandomCondition(j);
154 } else if(random.nextInt(5)==1) {
155 buff.append("(");
156 appendRandomCondition(j);
157 if(random.nextBoolean()) {
158 buff.append(") OR (");
159 } else {
160 buff.append(") AND (");
161 }
162 appendRandomCondition(j);
163 buff.append(")");
164 } else {
165 if(j>0 && random.nextBoolean()) {
166 buff.append("T" + (char)('0' + j-1) + ".A=T" + (char)('0' + j)+".A ");
167 } else {
168 appendRandomConditionPart(j);
169 }
170 }
171 }
172
173 private void appendRandomConditionPart(int j) {
174 int t1 = j <= 1 ? 0 : random.nextInt(j + 1);
175 int t2 = j <= 1 ? 0 : random.nextInt(j + 1);
176 String c1 = random.nextBoolean() ? "A" : "B";
177 String c2 = random.nextBoolean() ? "A" : "B";
178 buff.append("T" + (char)('0' + t1));
179 buff.append("."+c1);
180 if(random.nextInt(4)==1) {
181 if(random.nextInt(5)==1) {
182 buff.append(" IS NOT NULL");
183 } else {
184 buff.append(" IS NULL");
185 }
186 } else {
187 if(random.nextInt(5)==1) {
188 switch(random.nextInt(5)) {
189 case 0:
190 buff.append(">");
191 break;
192 case 1:
193 buff.append("<");
194 break;
195 case 2:
196 buff.append("<=");
197 break;
198 case 3:
199 buff.append(">=");
200 break;
201 case 4:
202 buff.append("<>");
203 break;
204 }
205 } else {
206 buff.append("=");
207 }
208 if(random.nextBoolean()) {
209 buff.append("T" + (char)('0' + t2));
210 buff.append("."+c2);
211 } else {
212 buff.append(random.nextInt(5)-1);
213 }
214 }
215 buff.append(" ");
216 }
217
218 private void execute(String sql, Object [] params) throws Exception {
219 execute(sql, params, false);
220 }
221
222 private void execute(String sql, Object [] params, boolean ignoreDifference) throws Exception {
223 String first = null;
224 for(int i=0; i<connections.size(); i++) {
225 Connection conn = (Connection ) connections.get(i);
226 String s;
227 try {
228 Statement stat;
229 boolean result;
230 if(params == null || params.length==0) {
231 stat = conn.createStatement();
232 result = stat.execute(sql);
233 } else {
234 PreparedStatement prep = conn.prepareStatement(sql);
235 stat = prep;
236 for(int j=0; j<params.length; j++) {
237 prep.setObject(j+1, params[j]);
238 }
239 result = prep.execute();
240 }
241 if(result) {
242 ResultSet rs = stat.getResultSet();
243 s = "rs: " + readResult(rs);
244 } else {
245 s = "updateCount: " + stat.getUpdateCount();
246 }
247 } catch(SQLException e) {
248 s = "exception";
249 }
250 if(i==0) {
251 first = s;
252 } else {
253 if(!ignoreDifference && !s.equals(first)) {
254 throw new Exception ("FAIL s:" + s + " first:" + first + " sql:"+sql);
255 }
256 }
257 }
258 }
259
260 private String readResult(ResultSet rs) throws SQLException {
261 StringBuffer buff = new StringBuffer ();
262 ResultSetMetaData meta = rs.getMetaData();
263 int columnCount = meta.getColumnCount();
264 for(int i=0; i<columnCount; i++) {
265 if(i>0) {
266 buff.append(",");
267 }
268 buff.append(StringUtils.toUpperEnglish(meta.getColumnLabel(i+1)));
269 }
270 buff.append(":\n");
271 String result = buff.toString();
272 ArrayList list = new ArrayList ();
273 while(rs.next()) {
274 buff = new StringBuffer ();
275 for(int i=0; i<columnCount; i++) {
276 if(i>0) {
277 buff.append(",");
278 }
279 buff.append(rs.getString(i+1));
280 }
281 list.add(buff.toString());
282 }
283 Collections.sort(list);
284 for(int i=0; i<list.size(); i++) {
285 result += list.get(i) + "\n";
286 }
287 return result;
288 }
289
290 }
291 |
Popular Tags |
Java API By Example, From Geeks To Geeks.