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.sql;
007:
008: import java.sql.Types;
009: import java.util.ArrayList;
010:
011: /**
012: * Represents an expression.
013: */
014: public class Expression {
015: boolean isCondition;
016:
017: private String sql;
018: private TestSynth config;
019: private Command command;
020:
021: public static String[] getRandomSelectList(TestSynth config,
022: Command command) {
023: if (config.random().getBoolean(30)) {
024: return new String[] { "*" };
025: }
026: ArrayList exp = new ArrayList();
027: String sql = "";
028: if (config.random().getBoolean(10)) {
029: sql += "DISTINCT ";
030: }
031: int len = config.random().getLog(8) + 1;
032: for (int i = 0; i < len; i++) {
033: sql += getRandomExpression(config, command).getSQL();
034: sql += " AS A" + i + " ";
035: exp.add(sql);
036: sql = "";
037: }
038: String[] list = new String[exp.size()];
039: exp.toArray(list);
040: return list;
041: }
042:
043: public static Expression getRandomCondition(TestSynth config,
044: Command command) {
045: Expression condition = new Expression(config, command, true);
046: if (config.random().getBoolean(50)) {
047: condition.create();
048: }
049: return condition;
050: }
051:
052: public static Expression getRandomExpression(TestSynth config,
053: Command command) {
054: Expression expression = new Expression(config, command, false);
055: String alias = command.getRandomTableAlias();
056: Column column = command.getTable(alias)
057: .getRandomConditionColumn();
058: if (column == null) {
059: expression.createValue();
060: } else {
061: expression.createExpression(alias, column);
062: }
063: return expression;
064: }
065:
066: private void createValue() {
067: Value v = Column.getRandomColumn(config).getRandomValue();
068: sql = v.getSQL();
069: }
070:
071: public static Expression getRandomJoinOn(TestSynth config,
072: Command command, String alias) {
073: Expression expression = new Expression(config, command, true);
074: expression.createJoinComparison(alias);
075: return expression;
076: }
077:
078: public static String getRandomOrder(TestSynth config,
079: Command command) {
080: int len = config.random().getLog(6);
081: String sql = "";
082: for (int i = 0; i < len; i++) {
083: if (i > 0) {
084: sql += ", ";
085: }
086: int max = command.selectList.length;
087: int idx = config.random().getInt(max);
088: // sql += getRandomExpression(command).getSQL();
089: // if (max > 1 && config.random().getBoolean(50)) {
090: sql += "A" + idx;
091: // } else {
092: // sql += String.valueOf(idx + 1);
093: // }
094: if (config.random().getBoolean(50)) {
095: if (config.random().getBoolean(10)) {
096: sql += " ASC";
097: } else {
098: sql += " DESC";
099: }
100: }
101: }
102: return sql;
103: }
104:
105: public String getSQL() {
106: return sql.trim().length() == 0 ? null : sql.trim();
107: }
108:
109: private Expression(TestSynth config, Command command,
110: boolean isCondition) {
111: this .config = config;
112: this .isCondition = isCondition;
113: this .command = command;
114: sql = "";
115: }
116:
117: private boolean is(int percent) {
118: return config.random().getBoolean(percent);
119: }
120:
121: private String oneOf(String[] list) {
122: int i = config.random().getInt(list.length);
123: if (!sql.endsWith(" ")) {
124: sql += " ";
125: }
126: sql += list[i] + " ";
127: return list[i];
128: }
129:
130: private String getColumnName(String alias, Column column) {
131: if (alias == null) {
132: return column.getName();
133: }
134: return alias + "." + column.getName();
135: }
136:
137: private void createJoinComparison(String alias) {
138: int len = config.random().getLog(5) + 1;
139: for (int i = 0; i < len; i++) {
140: if (i > 0) {
141: sql += "AND ";
142: }
143: Column column = command.getTable(alias)
144: .getRandomConditionColumn();
145: if (column == null) {
146: sql += "1=1";
147: return;
148: }
149: sql += getColumnName(alias, column);
150: sql += "=";
151: String a2;
152: do {
153: a2 = command.getRandomTableAlias();
154: } while (a2.equals(alias));
155: Table t2 = command.getTable(a2);
156: Column c2 = t2.getRandomColumnOfType(column.getType());
157: if (c2 == null) {
158: sql += column.getRandomValue().getSQL();
159: } else {
160: sql += getColumnName(a2, c2);
161: }
162: sql += " ";
163: }
164: }
165:
166: private void create() {
167: createComparison();
168: while (is(50)) {
169: oneOf(new String[] { "AND", "OR" });
170: createComparison();
171: }
172: }
173:
174: // private void createSubquery() {
175: // // String alias = command.getRandomTableAlias();
176: // // Table t1 = command.getTable(alias);
177: // Database db = command.getDatabase();
178: // Table t2 = db.getRandomTable();
179: // String a2 = command.getNextTableAlias();
180: // sql += "SELECT * FROM " + t2.getName() + " " + a2 + " WHERE ";
181: // command.addSubqueryTable(a2, t2);
182: // createComparison();
183: // command.removeSubqueryTable(a2);
184: // }
185:
186: private void createComparison() {
187: if (is(5)) {
188: sql += " NOT( ";
189: createComparisonSub();
190: sql += ")";
191: } else {
192: createComparisonSub();
193: }
194: }
195:
196: private void createComparisonSub() {
197: /*
198: * if (is(10)) { sql += " EXISTS("; createSubquery(); sql += ")";
199: * return; } else
200: */
201: if (is(10)) {
202: sql += "(";
203: create();
204: sql += ")";
205: return;
206: }
207: String alias = command.getRandomTableAlias();
208: Column column = command.getTable(alias)
209: .getRandomConditionColumn();
210: if (column == null) {
211: if (is(50)) {
212: sql += "1=1";
213: } else {
214: sql += "1=0";
215: }
216: return;
217: }
218: boolean columnFirst = is(90);
219: if (columnFirst) {
220: sql += getColumnName(alias, column);
221: } else {
222: Value v = column.getRandomValue();
223: sql += v.getSQL();
224: }
225: if (is(10)) {
226: oneOf(new String[] { "IS NULL", "IS NOT NULL" });
227: } else if (is(10)) {
228: oneOf(new String[] { "BETWEEN", "NOT BETWEEN" });
229: Value v = column.getRandomValue();
230: sql += v.getSQL();
231: sql += " AND ";
232: v = column.getRandomValue();
233: sql += v.getSQL();
234: // } else if (is(10)) {
235: // // oneOf(new String[] { "IN", "NOT IN" });
236: // sql += " IN ";
237: // sql += "(";
238: // int len = config.random().getInt(8) + 1;
239: // for (int i = 0; i < len; i++) {
240: // if (i > 0) {
241: // sql += ", ";
242: // }
243: // sql += column.getRandomValueNotNull().getSQL();
244: // }
245: // sql += ")";
246: } else {
247: if (column.getType() == Types.VARCHAR) {
248: oneOf(new String[] { "=", "=", "=", "<", ">", "<=",
249: ">=", "<>", "LIKE", "NOT LIKE" });
250: } else {
251: oneOf(new String[] { "=", "=", "=", "<", ">", "<=",
252: ">=", "<>" });
253: }
254: if (columnFirst) {
255: Value v = column.getRandomValue();
256: sql += v.getSQL();
257: } else {
258: sql += getColumnName(alias, column);
259: }
260: }
261: }
262:
263: public boolean isEmpty() {
264: return sql == null || sql.trim().length() == 0;
265: }
266:
267: void createExpression(String alias, Column type) {
268: boolean op = is(20);
269: // no null values if there is an operation
270: boolean allowNull = !op;
271: // boolean allowNull =true;
272:
273: createTerm(alias, type, true);
274: if (op) {
275: switch (type.getType()) {
276: case Types.INTEGER:
277: if (config.is(TestSynth.POSTGRESQL)) {
278: oneOf(new String[] { "+", "-", "/" });
279: } else {
280: oneOf(new String[] { "+", "-", "*", "/" });
281: }
282: createTerm(alias, type, allowNull);
283: break;
284: case Types.DECIMAL:
285: oneOf(new String[] { "+", "-", "*" });
286: createTerm(alias, type, allowNull);
287: break;
288: case Types.VARCHAR:
289: sql += " || ";
290: createTerm(alias, type, allowNull);
291: break;
292: case Types.BLOB:
293: case Types.CLOB:
294: case Types.DATE:
295: break;
296: }
297: }
298: }
299:
300: void createTerm(String alias, Column type, boolean allowNull) {
301: int dt = type.getType();
302: if (is(5) && (dt == Types.INTEGER) || (dt == Types.DECIMAL)) {
303: sql += " - ";
304: allowNull = false;
305: }
306: if (is(10)) {
307: sql += "(";
308: createTerm(alias, type, allowNull);
309: sql += ")";
310: return;
311: }
312: if (is(20)) {
313: // if (is(10)) {
314: // sql += "CAST(";
315: // // TODO cast
316: // Column c = Column.getRandomColumn(config);
317: // createTerm(alias, c, allowNull);
318: // sql += " AS ";
319: // sql += type.getTypeName();
320: // sql += ")";
321: // return;
322: // }
323: switch (dt) {
324: // case Types.INTEGER:
325: // String function = oneOf(new String[] { "LENGTH" /*, "MOD" */ });
326: // sql += "(";
327: // createTerm(alias, type, allowNull);
328: // sql += ")";
329: // break;
330: case Types.VARCHAR:
331: oneOf(new String[] { "LOWER", "UPPER" });
332: sql += "(";
333: createTerm(alias, type, allowNull);
334: sql += ")";
335: break;
336: default:
337: createTerm(alias, type, allowNull);
338: }
339: return;
340: }
341: if (is(60)) {
342: String a2 = command.getRandomTableAlias();
343: Column column = command.getTable(a2).getRandomColumnOfType(
344: dt);
345: if (column != null) {
346: sql += getColumnName(a2, column);
347: return;
348: }
349: }
350:
351: Value v = Value.getRandom(config, dt, 20, 2, allowNull);
352: sql += v.getSQL();
353: }
354:
355: public String toString() {
356: throw new Error("hey!");
357: }
358:
359: }
|