OmniSciDB  c07336695a
SqlIdentifierCapturerTest.java
Go to the documentation of this file.
1 package com.mapd.parser.server.test;
2 
3 import static org.junit.Assert.assertEquals;
4 
5 import static java.util.Arrays.asList;
6 
8 
9 import org.apache.calcite.avatica.util.Casing;
10 import org.apache.calcite.linq4j.tree.Expression;
13 import org.apache.calcite.rel.type.RelProtoDataType;
14 import org.apache.calcite.schema.Function;
15 import org.apache.calcite.schema.Schema;
16 import org.apache.calcite.schema.SchemaPlus;
17 import org.apache.calcite.schema.SchemaVersion;
18 import org.apache.calcite.schema.Table;
19 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
20 import org.apache.calcite.sql.parser.SqlParser;
21 import org.apache.calcite.tools.FrameworkConfig;
22 import org.apache.calcite.tools.Frameworks;
23 import org.apache.calcite.tools.Planner;
24 import org.junit.Test;
25 
26 import java.util.Collection;
27 import java.util.Collections;
28 import java.util.HashSet;
29 import java.util.Set;
30 
32  private class MockSchema implements Schema {
33  @Override
34  public Table getTable(String name) {
35  return null;
36  }
37 
38  @Override
39  public Set<String> getTypeNames() {
40  return Collections.emptySet();
41  }
42 
43  @Override
44  public RelProtoDataType getType(String arg0) {
45  return null;
46  }
47 
48  @Override
49  public Set<String> getTableNames() {
50  return Collections.emptySet();
51  }
52 
53  @Override
54  public Collection<Function> getFunctions(String name) {
55  return Collections.emptySet();
56  }
57 
58  @Override
59  public Set<String> getFunctionNames() {
60  return Collections.emptySet();
61  }
62 
63  @Override
64  public Schema getSubSchema(String name) {
65  return null;
66  }
67 
68  @Override
69  public Set<String> getSubSchemaNames() {
70  return Collections.emptySet();
71  }
72 
73  @Override
74  public Expression getExpression(SchemaPlus parentSchema, String name) {
75  return null;
76  }
77 
78  @Override
79  public boolean isMutable() {
80  return false;
81  }
82 
83  @Override
84  public Schema snapshot(SchemaVersion version) {
85  return null;
86  }
87  }
88 
89  private Planner getPlanner() {
90  Schema mapd = new MockSchema() {
91 
92  };
93  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
94  final FrameworkConfig config =
95  Frameworks.newConfigBuilder()
96  .defaultSchema(rootSchema.add("omnisci", mapd))
97  .operatorTable(
98  new MapDSqlOperatorTable(SqlStdOperatorTable.instance()))
99  .parserConfig(SqlParser.configBuilder()
100  .setUnquotedCasing(Casing.UNCHANGED)
101  .setCaseSensitive(false)
102  .build())
103  .build();
104  return new MapDPlanner(config);
105  }
106 
107  public static String[] asArray(String... vals) {
108  return vals;
109  }
110 
111  public static Set<String> asSet(String... vals) {
112  return new HashSet<String>(asList(vals));
113  }
114 
115  public void testSelect(String sql, String[] expectedSelects) throws Exception {
117  capturer.scan(getPlanner().parse(sql));
118 
119  assertEquals("selects", asSet(expectedSelects), capturer.selects);
120  assertEquals("inserts", asSet(), capturer.inserts);
121  assertEquals("updates", asSet(), capturer.updates);
122  assertEquals("deletes", asSet(), capturer.deletes);
123  }
124 
125  public void testUpdate(String sql, String[] expectedUpdates, String[] expectedSelects)
126  throws Exception {
128  capturer.scan(getPlanner().parse(sql));
129 
130  assertEquals("selects", asSet(expectedSelects), capturer.selects);
131  assertEquals("inserts", asSet(), capturer.inserts);
132  assertEquals("updates", asSet(expectedUpdates), capturer.updates);
133  assertEquals("deletes", asSet(), capturer.deletes);
134  }
135 
136  public void testInsert(String sql, String[] expectedInserts, String[] expectedSelects)
137  throws Exception {
139  capturer.scan(getPlanner().parse(sql));
140 
141  assertEquals("selects", asSet(expectedSelects), capturer.selects);
142  assertEquals("inserts", asSet(expectedInserts), capturer.inserts);
143  assertEquals("updates", asSet(), capturer.updates);
144  assertEquals("deletes", asSet(), capturer.deletes);
145  }
146 
147  public void testDelete(String sql, String[] expectedDeletes, String[] expectedSelects)
148  throws Exception {
150  capturer.scan(getPlanner().parse(sql));
151 
152  assertEquals("selects", asSet(expectedSelects), capturer.selects);
153  assertEquals("inserts", asSet(), capturer.inserts);
154  assertEquals("updates", asSet(), capturer.updates);
155  assertEquals("deletes", asSet(expectedDeletes), capturer.deletes);
156  }
157 
158  @Test
159  public void testSelects() throws Exception {
160  String sql = "SELECT * FROM sales";
161  testSelect(sql, asArray("sales"));
162 
163  sql = "SELECT * FROM sales AS s";
164  testSelect(sql, asArray("sales"));
165 
166  sql = "SELECT * FROM sales AS s, reports AS r WHERE s.id = r.id";
167  testSelect(sql, asArray("sales", "reports"));
168 
169  sql = "SELECT * FROM sales AS s left outer join reports AS r on s.id = r.id";
170  testSelect(sql, asArray("sales", "reports"));
171 
172  sql = "SELECT *, (SELECT sum(val) FROM marketing m WHERE m.id=a.id) FROM sales AS s left outer join reports AS r on s.id = r.id";
173  testSelect(sql, asArray("sales", "reports", "marketing"));
174 
175  sql = "SELECT * FROM sales UNION SELECT * FROM reports UNION SELECT * FROM marketing";
176  testSelect(sql, asArray("sales", "reports", "marketing"));
177 
178  sql = "SELECT COUNT(*) AS n, str FROM query_rewrite_test WHERE str IN ('str2', 'str99') GROUP BY str HAVING n > 0 ORDER BY n DESC";
179  testSelect(sql, asArray("query_rewrite_test"));
180 
181  sql = "SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY total_y DESC, str LIMIT 1";
182  testSelect(sql, asArray("test"));
183 
184  sql = "SELECT str FROM (SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY total_y DESC, str LIMIT 1)";
185  testSelect(sql, asArray("test"));
186 
187  sql = "SELECT deptno, dname FROM (SELECT * from dept) AS view_name LIMIT 10";
188  testSelect(sql, asArray("dept"));
189 
190  sql = "WITH d1 AS (SELECT deptno, dname FROM dept LIMIT 10) SELECT ename, dname FROM emp, d1 WHERE emp.deptno = d1.deptno ORDER BY ename ASC LIMIT 10";
191  testSelect(sql, asArray("emp", "dept"));
192  }
193 
194  @Test
195  public void testSelectsWithSchema() throws Exception {
196  String sql = "SELECT * FROM mapd.sales";
197  testSelect(sql, asArray("sales"));
198 
199  sql = "SELECT * FROM mapd.sales AS s";
200  testSelect(sql, asArray("sales"));
201 
202  sql = "SELECT * FROM mapd.sales AS s, mapd.reports AS r WHERE s.id = r.id";
203  testSelect(sql, asArray("sales", "reports"));
204 
205  sql = "SELECT * FROM mapd.sales AS s left outer join mapd.reports AS r on s.id = r.id";
206  testSelect(sql, asArray("sales", "reports"));
207 
208  sql = "SELECT *, (SELECT sum(val) FROM mapd.marketing m WHERE m.id=a.id) FROM mapd.sales AS s left outer join mapd.reports AS r on s.id = r.id";
209  testSelect(sql, asArray("sales", "reports", "marketing"));
210 
211  sql = "SELECT * FROM mapd.sales UNION SELECT * FROM mapd.reports UNION SELECT * FROM mapd.marketing";
212  testSelect(sql, asArray("sales", "reports", "marketing"));
213 
214  sql = "SELECT COUNT(*) AS n, str FROM mapd.query_rewrite_test WHERE str IN ('str2', 'str99') GROUP BY str HAVING n > 0 ORDER BY n DESC";
215  testSelect(sql, asArray("query_rewrite_test"));
216 
217  sql = "SELECT str, SUM(y) as total_y FROM mapd.test GROUP BY str ORDER BY total_y DESC, str LIMIT 1";
218  testSelect(sql, asArray("test"));
219 
220  sql = "SELECT str FROM (SELECT str, SUM(y) as total_y FROM mapd.test GROUP BY str ORDER BY total_y DESC, str LIMIT 1)";
221  testSelect(sql, asArray("test"));
222 
223  sql = "SELECT deptno, dname FROM (SELECT * from mapd.dept) AS view_name LIMIT 10";
224  testSelect(sql, asArray("dept"));
225 
226  sql = "WITH d1 AS (SELECT deptno, dname FROM mapd.dept LIMIT 10) SELECT ename, dname FROM mapd.emp, d1 WHERE emp.deptno = d1.deptno ORDER BY ename ASC LIMIT 10";
227  testSelect(sql, asArray("emp", "dept"));
228  }
229 
230  @Test
231  public void testInserts() throws Exception {
232  String sql = "INSERT INTO sales VALUES(10)";
233  testInsert(sql, asArray("sales"), asArray());
234 
235  sql = "INSERT INTO sales(id, target) VALUES(10, 21321)";
236  testInsert(sql, asArray("sales"), asArray());
237 
238  sql = "INSERT INTO sales(id, target) VALUES(10, (SELECT max(r.val) FROM reports AS r))";
239  testInsert(sql, asArray("sales"), asArray("reports"));
240 
241  sql = "INSERT INTO sales(id, target) VALUES((SELECT m.id FROM marketing m), (SELECT max(r.val) FROM reports AS r))";
242  testInsert(sql, asArray("sales"), asArray("reports", "marketing"));
243  }
244 
245  @Test
246  public void testInsertsWithSchema() throws Exception {
247  String sql = "INSERT INTO mapd.sales VALUES(10)";
248  testInsert(sql, asArray("sales"), asArray());
249 
250  sql = "INSERT INTO mapd.sales(id, target) VALUES(10, 21321)";
251  testInsert(sql, asArray("sales"), asArray());
252 
253  sql = "INSERT INTO mapd.sales(id, target) VALUES(10, (SELECT max(r.val) FROM mapd.reports AS r))";
254  testInsert(sql, asArray("sales"), asArray("reports"));
255 
256  sql = "INSERT INTO mapd.sales(id, target) VALUES((SELECT m.id FROM mapd.marketing m), (SELECT max(r.val) FROM mapd.reports AS r))";
257  testInsert(sql, asArray("sales"), asArray("reports", "marketing"));
258  }
259 
260  @Test
261  public void testUpdates() throws Exception {
262  String sql = "UPDATE sales SET id=10";
263  testUpdate(sql, asArray("sales"), asArray());
264 
265  sql = "UPDATE sales SET id=10 WHERE id=1";
266  testUpdate(sql, asArray("sales"), asArray());
267 
268  sql = "UPDATE sales SET id=(SELECT max(r.val) FROM reports AS r)";
269  testUpdate(sql, asArray("sales"), asArray("reports"));
270 
271  sql = "UPDATE sales SET id=(SELECT max(r.val) FROM reports AS r) WHERE id=(SELECT max(m.val) FROM marketing AS m)";
272  testUpdate(sql, asArray("sales"), asArray("reports", "marketing"));
273 
274  sql = "UPDATE shardkey SET y=99 WHERE x=(SELECT max(id) from v2 LIMIT 1)";
275  testUpdate(sql, asArray("shardkey"), asArray("v2"));
276  }
277 
278  @Test
279  public void testUpdatesWithSchema() throws Exception {
280  String sql = "UPDATE mapd.sales SET id=10";
281  testUpdate(sql, asArray("sales"), asArray());
282 
283  sql = "UPDATE mapd.sales SET id=10 WHERE id=1";
284  testUpdate(sql, asArray("sales"), asArray());
285 
286  sql = "UPDATE mapd.sales SET id=(SELECT max(r.val) FROM mapd.reports AS r)";
287  testUpdate(sql, asArray("sales"), asArray("reports"));
288 
289  sql = "UPDATE mapd.sales SET id=(SELECT max(r.val) FROM mapd.reports AS r) WHERE id=(SELECT max(m.val) FROM mapd.marketing AS m)";
290  testUpdate(sql, asArray("sales"), asArray("reports", "marketing"));
291 
292  sql = "UPDATE mapd.shardkey SET y=99 WHERE x=(SELECT max(id) from mapd.v2 LIMIT 1)";
293  testUpdate(sql, asArray("shardkey"), asArray("v2"));
294  }
295 
296  @Test
297  public void testDeletes() throws Exception {
298  String sql = "DELETE FROM sales";
299  testDelete(sql, asArray("sales"), asArray());
300 
301  sql = "DELETE FROM sales WHERE id=1";
302  testDelete(sql, asArray("sales"), asArray());
303 
304  sql = "DELETE FROM sales WHERE id=(SELECT max(r.val) FROM reports AS r)";
305  testDelete(sql, asArray("sales"), asArray("reports"));
306 
307  sql = "DELETE FROM sales WHERE id=(SELECT max(r.val) FROM reports AS r) AND id=(SELECT max(m.val) FROM marketing AS m)";
308  testDelete(sql, asArray("sales"), asArray("reports", "marketing"));
309  }
310 
311  @Test
312  public void testDeletesWithSchema() throws Exception {
313  String sql = "DELETE FROM mapd.sales";
314  testDelete(sql, asArray("sales"), asArray());
315 
316  sql = "DELETE FROM mapd.sales WHERE id=1";
317  testDelete(sql, asArray("sales"), asArray());
318 
319  sql = "DELETE FROM mapd.sales WHERE id=(SELECT max(r.val) FROM mapd.reports AS r)";
320  testDelete(sql, asArray("sales"), asArray("reports"));
321 
322  sql = "DELETE FROM mapd.sales WHERE id=(SELECT max(r.val) FROM mapd.reports AS r) AND id=(SELECT max(m.val) FROM mapd.marketing AS m)";
323  testDelete(sql, asArray("sales"), asArray("reports", "marketing"));
324  }
325 }
Expression getExpression(SchemaPlus parentSchema, String name)
void testDelete(String sql, String[] expectedDeletes, String[] expectedSelects)
auto sql(const std::string &sql_stmts)
void testUpdate(String sql, String[] expectedUpdates, String[] expectedSelects)
Definition: DataGen.cpp:60
void testInsert(String sql, String[] expectedInserts, String[] expectedSelects)