Skip to content

Commit 9362681

Browse files
bajinshengbajinsheng
authored
MySQL Join implementation (#921)
1 parent 49afc88 commit 9362681

6 files changed

Lines changed: 155 additions & 12 deletions

File tree

‎src/sqlancer/mysql/MySQLExpectedValueVisitor.java‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -13,10 +13,12 @@
1313
import sqlancer.mysql.ast.MySQLExists;
1414
import sqlancer.mysql.ast.MySQLExpression;
1515
import sqlancer.mysql.ast.MySQLInOperation;
16+
import sqlancer.mysql.ast.MySQLJoin;
1617
import sqlancer.mysql.ast.MySQLOrderByTerm;
1718
import sqlancer.mysql.ast.MySQLSelect;
1819
import sqlancer.mysql.ast.MySQLStringExpression;
1920
import sqlancer.mysql.ast.MySQLTableReference;
21+
import sqlancer.mysql.ast.MySQLText;
2022
import sqlancer.mysql.ast.MySQLUnaryPostfixOperation;
2123

2224
public class MySQLExpectedValueVisitor implements MySQLVisitor {
@@ -153,4 +155,15 @@ public void visit(MySQLCollate collate) {
153155
visit(collate.getExpectedValue());
154156
}
155157

158+
@Override
159+
public void visit(MySQLJoin join) {
160+
print(join);
161+
visit(join.getOnClause());
162+
}
163+
164+
@Override
165+
public void visit(MySQLText text) {
166+
print(text);
167+
}
168+
156169
}

‎src/sqlancer/mysql/MySQLProvider.java‎

Lines changed: 1 addition & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -63,11 +63,6 @@ enum Action implements AbstractAction<MySQLGlobalState> {
6363
SELECT_INFO((g) -> new SQLQueryAdapter(
6464
"select TABLE_NAME, ENGINE from information_schema.TABLES where table_schema = '" + g.getDatabaseName()
6565
+ "'")), //
66-
CREATE_TABLE((g) -> {
67-
// TODO refactor
68-
String tableName = DBMSCommon.createTableName(g.getSchema().getDatabaseTables().size());
69-
return MySQLTableGenerator.generate(g, tableName);
70-
}), //
7166
UPDATE(MySQLUpdateGenerator::create), //
7267
DELETE(MySQLDeleteGenerator::delete), //
7368
DROP_INDEX(MySQLDropIndex::generate);
@@ -94,9 +89,6 @@ private static int mapActions(MySQLGlobalState globalState, Action a) {
9489
case SHOW_TABLES:
9590
nrPerformed = r.getInteger(0, 1);
9691
break;
97-
case CREATE_TABLE:
98-
nrPerformed = r.getInteger(0, 1);
99-
break;
10092
case INSERT:
10193
nrPerformed = r.getInteger(0, globalState.getOptions().getMaxNumberInserts());
10294
break;
@@ -148,7 +140,7 @@ private static int mapActions(MySQLGlobalState globalState, Action a) {
148140

149141
@Override
150142
public void generateDatabase(MySQLGlobalState globalState) throws Exception {
151-
while (globalState.getSchema().getDatabaseTables().size() < Randomly.smallNumber() + 1) {
143+
while (globalState.getSchema().getDatabaseTables().size() < Randomly.getNotCachedInteger(1, 2)) {
152144
String tableName = DBMSCommon.createTableName(globalState.getSchema().getDatabaseTables().size());
153145
SQLQueryAdapter createTable = MySQLTableGenerator.generate(globalState, tableName);
154146
globalState.executeStatement(createTable);

‎src/sqlancer/mysql/MySQLToStringVisitor.java‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,11 +17,13 @@
1717
import sqlancer.mysql.ast.MySQLExists;
1818
import sqlancer.mysql.ast.MySQLExpression;
1919
import sqlancer.mysql.ast.MySQLInOperation;
20+
import sqlancer.mysql.ast.MySQLJoin;
2021
import sqlancer.mysql.ast.MySQLOrderByTerm;
2122
import sqlancer.mysql.ast.MySQLOrderByTerm.MySQLOrder;
2223
import sqlancer.mysql.ast.MySQLSelect;
2324
import sqlancer.mysql.ast.MySQLStringExpression;
2425
import sqlancer.mysql.ast.MySQLTableReference;
26+
import sqlancer.mysql.ast.MySQLText;
2527
import sqlancer.mysql.ast.MySQLUnaryPostfixOperation;
2628

2729
public class MySQLToStringVisitor extends ToStringVisitor<MySQLExpression> implements MySQLVisitor {
@@ -278,4 +280,41 @@ public void visit(MySQLCollate collate) {
278280
sb.append(")");
279281
}
280282

283+
@Override
284+
public void visit(MySQLJoin join) {
285+
sb.append(" ");
286+
switch (join.getType()) {
287+
case NATURAL:
288+
sb.append("NATURAL ");
289+
break;
290+
case INNER:
291+
sb.append("INNER ");
292+
break;
293+
case STRAIGHT:
294+
sb.append("STRAIGHT_");
295+
break;
296+
case LEFT:
297+
sb.append("LEFT ");
298+
break;
299+
case RIGHT:
300+
sb.append("RIGHT ");
301+
break;
302+
case CROSS:
303+
sb.append("CROSS ");
304+
break;
305+
default:
306+
throw new AssertionError(join.getType());
307+
}
308+
sb.append("JOIN ");
309+
sb.append(join.getTable().getName());
310+
if (join.getOnClause() != null) {
311+
sb.append(" ON ");
312+
visit(join.getOnClause());
313+
}
314+
}
315+
316+
@Override
317+
public void visit(MySQLText text) {
318+
sb.append(text.getText());
319+
}
281320
}

‎src/sqlancer/mysql/MySQLVisitor.java‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,10 +12,12 @@
1212
import sqlancer.mysql.ast.MySQLExists;
1313
import sqlancer.mysql.ast.MySQLExpression;
1414
import sqlancer.mysql.ast.MySQLInOperation;
15+
import sqlancer.mysql.ast.MySQLJoin;
1516
import sqlancer.mysql.ast.MySQLOrderByTerm;
1617
import sqlancer.mysql.ast.MySQLSelect;
1718
import sqlancer.mysql.ast.MySQLStringExpression;
1819
import sqlancer.mysql.ast.MySQLTableReference;
20+
import sqlancer.mysql.ast.MySQLText;
1921
import sqlancer.mysql.ast.MySQLUnaryPostfixOperation;
2022

2123
public interface MySQLVisitor {
@@ -52,6 +54,10 @@ public interface MySQLVisitor {
5254

5355
void visit(MySQLCollate collate);
5456

57+
void visit(MySQLJoin join);
58+
59+
void visit(MySQLText text);
60+
5561
default void visit(MySQLExpression expr) {
5662
if (expr instanceof MySQLConstant) {
5763
visit((MySQLConstant) expr);
@@ -77,6 +83,8 @@ default void visit(MySQLExpression expr) {
7783
visit((MySQLOrderByTerm) expr);
7884
} else if (expr instanceof MySQLExists) {
7985
visit((MySQLExists) expr);
86+
} else if (expr instanceof MySQLJoin) {
87+
visit((MySQLJoin) expr);
8088
} else if (expr instanceof MySQLStringExpression) {
8189
visit((MySQLStringExpression) expr);
8290
} else if (expr instanceof MySQLBetweenOperation) {
@@ -85,6 +93,8 @@ default void visit(MySQLExpression expr) {
8593
visit((MySQLTableReference) expr);
8694
} else if (expr instanceof MySQLCollate) {
8795
visit((MySQLCollate) expr);
96+
} else if (expr instanceof MySQLText) {
97+
visit((MySQLText) expr);
8898
} else {
8999
throw new AssertionError(expr);
90100
}

‎src/sqlancer/mysql/ast/MySQLJoin.java‎

Lines changed: 78 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,85 @@
11
package sqlancer.mysql.ast;
22

3+
import java.util.ArrayList;
4+
import java.util.Arrays;
5+
import java.util.List;
6+
7+
import sqlancer.Randomly;
8+
import sqlancer.mysql.MySQLGlobalState;
9+
import sqlancer.mysql.MySQLSchema.MySQLColumn;
10+
import sqlancer.mysql.MySQLSchema.MySQLTable;
11+
import sqlancer.mysql.gen.MySQLExpressionGenerator;
12+
313
public class MySQLJoin implements MySQLExpression {
414

5-
@Override
6-
public MySQLConstant getExpectedValue() {
7-
throw new UnsupportedOperationException();
15+
public enum JoinType {
16+
NATURAL, INNER, STRAIGHT, LEFT, RIGHT, CROSS;
17+
}
18+
19+
private final MySQLTable table;
20+
private MySQLExpression onClause;
21+
private JoinType type;
22+
23+
public MySQLJoin(MySQLJoin other) {
24+
this.table = other.table;
25+
this.onClause = other.onClause;
26+
this.type = other.type;
827
}
928

29+
public MySQLJoin(MySQLTable table, MySQLExpression onClause, JoinType type) {
30+
this.table = table;
31+
this.onClause = onClause;
32+
this.type = type;
33+
}
34+
35+
public MySQLTable getTable() {
36+
return table;
37+
}
38+
39+
public MySQLExpression getOnClause() {
40+
return onClause;
41+
}
42+
43+
public JoinType getType() {
44+
return type;
45+
}
46+
47+
public void setOnClause(MySQLExpression onClause) {
48+
this.onClause = onClause;
49+
}
50+
51+
public void setType(JoinType type) {
52+
this.type = type;
53+
}
54+
55+
public static List<MySQLJoin> getRandomJoinClauses(List<MySQLTable> tables, MySQLGlobalState globalState) {
56+
List<MySQLJoin> joinStatements = new ArrayList<>();
57+
List<JoinType> options = new ArrayList<>(Arrays.asList(JoinType.values()));
58+
List<MySQLColumn> columns = new ArrayList<>();
59+
if (tables.size() > 1) {
60+
int nrJoinClauses = (int) Randomly.getNotCachedInteger(0, tables.size());
61+
// Natural join is incompatible with other joins
62+
// because it needs unique column names
63+
// while other joins will produce duplicate column names
64+
if (nrJoinClauses > 1) {
65+
options.remove(JoinType.NATURAL);
66+
}
67+
for (int i = 0; i < nrJoinClauses; i++) {
68+
MySQLTable table = Randomly.fromList(tables);
69+
tables.remove(table);
70+
columns.addAll(table.getColumns());
71+
MySQLExpressionGenerator joinGen = new MySQLExpressionGenerator(globalState).setColumns(columns);
72+
MySQLExpression joinClause = joinGen.generateExpression();
73+
JoinType selectedOption = Randomly.fromList(options);
74+
if (selectedOption == JoinType.NATURAL) {
75+
// NATURAL joins do not have an ON clause
76+
joinClause = null;
77+
}
78+
MySQLJoin j = new MySQLJoin(table, joinClause, selectedOption);
79+
joinStatements.add(j);
80+
}
81+
82+
}
83+
return joinStatements;
84+
}
1085
}

‎src/sqlancer/mysql/ast/MySQLText.java‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
package sqlancer.mysql.ast;
2+
3+
public class MySQLText implements MySQLExpression {
4+
5+
private final String text;
6+
7+
public MySQLText(String text) {
8+
this.text = text;
9+
}
10+
11+
public String getText() {
12+
return text;
13+
}
14+
}

0 commit comments

Comments
 (0)