OmniSciDB  fe05a0c208
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
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 
7 import com.google.common.collect.ImmutableList;
9 
10 import org.apache.calcite.avatica.util.Casing;
11 import org.apache.calcite.linq4j.tree.Expression;
14 import org.apache.calcite.rel.type.RelProtoDataType;
15 import org.apache.calcite.schema.Function;
16 import org.apache.calcite.schema.Schema;
17 import org.apache.calcite.schema.SchemaPlus;
18 import org.apache.calcite.schema.SchemaVersion;
19 import org.apache.calcite.schema.Table;
20 import org.apache.calcite.sql.fun.SqlStdOperatorTable;
21 import org.apache.calcite.sql.parser.SqlParser;
22 import org.apache.calcite.tools.FrameworkConfig;
23 import org.apache.calcite.tools.Frameworks;
24 import org.apache.calcite.tools.Planner;
25 import org.junit.Test;
26 
27 import java.util.Arrays;
28 import java.util.Collection;
29 import java.util.Collections;
30 import java.util.HashSet;
31 import java.util.List;
32 import java.util.Set;
33 
35  private class MockSchema implements Schema {
36  @Override
37  public Table getTable(String name) {
38  return null;
39  }
40 
41  @Override
42  public Set<String> getTypeNames() {
43  return Collections.emptySet();
44  }
45 
46  @Override
47  public RelProtoDataType getType(String arg0) {
48  return null;
49  }
50 
51  @Override
52  public Set<String> getTableNames() {
53  return Collections.emptySet();
54  }
55 
56  @Override
57  public Collection<Function> getFunctions(String name) {
58  return Collections.emptySet();
59  }
60 
61  @Override
62  public Set<String> getFunctionNames() {
63  return Collections.emptySet();
64  }
65 
66  @Override
67  public Schema getSubSchema(String name) {
68  return null;
69  }
70 
71  @Override
72  public Set<String> getSubSchemaNames() {
73  return Collections.emptySet();
74  }
75 
76  @Override
77  public Expression getExpression(SchemaPlus parentSchema, String name) {
78  return null;
79  }
80 
81  @Override
82  public boolean isMutable() {
83  return false;
84  }
85 
86  @Override
87  public Schema snapshot(SchemaVersion version) {
88  return null;
89  }
90  }
91 
92  private Planner getPlanner() {
93  Schema mapd = new MockSchema() {
94 
95  };
96  final SchemaPlus rootSchema = Frameworks.createRootSchema(true);
97  final FrameworkConfig config =
98  Frameworks.newConfigBuilder()
99  .defaultSchema(rootSchema.add("omnisci", mapd))
100  .operatorTable(
101  new MapDSqlOperatorTable(SqlStdOperatorTable.instance()))
102  .parserConfig(SqlParser.configBuilder()
103  .setUnquotedCasing(Casing.UNCHANGED)
104  .setCaseSensitive(false)
105  .build())
106  .build();
107  return new MapDPlanner(config);
108  }
109 
110  public static ImmutableList<String> asImmutableList(String... vals) {
111  return ImmutableList.copyOf(asList(vals));
112  }
113 
114  public static ImmutableList<String> asImmutableList() {
115  return ImmutableList.of();
116  }
117 
118  public static Set<ImmutableList<String>> asSet(ImmutableList<String> val) {
119  Set<ImmutableList<String>> ret = new HashSet<ImmutableList<String>>();
120  ret.add(val);
121  return ret;
122  }
123 
124  public static Set<ImmutableList<String>> asSet(ImmutableList<String>... vals) {
125  Set<ImmutableList<String>> ret = new HashSet<ImmutableList<String>>();
126  ret.addAll(Arrays.asList(vals));
127  return ret;
128  }
129 
130  public static Set<ImmutableList<String>> asSet() {
131  Set<ImmutableList<String>> ret = new HashSet<ImmutableList<String>>();
132  return ret;
133  }
134 
135  public void testSelect(String sql, Set<ImmutableList<String>> expectedSelects)
136  throws Exception {
138  capturer.scan(getPlanner().parse(sql));
139 
140  assertEquals("selects", expectedSelects, capturer.selects);
141  assertEquals("inserts", asSet(), capturer.inserts);
142  assertEquals("updates", asSet(), capturer.updates);
143  assertEquals("deletes", asSet(), capturer.deletes);
144  }
145 
146  public void testUpdate(String sql,
147  Set<ImmutableList<String>> expectedUpdates,
148  Set<ImmutableList<String>> expectedSelects) throws Exception {
150  capturer.scan(getPlanner().parse(sql));
151 
152  assertEquals("selects", expectedSelects, capturer.selects);
153  assertEquals("inserts", asSet(), capturer.inserts);
154  assertEquals("updates", expectedUpdates, capturer.updates);
155  assertEquals("deletes", asSet(), capturer.deletes);
156  }
157 
158  public void testInsert(String sql,
159  Set<ImmutableList<String>> expectedInserts,
160  Set<ImmutableList<String>> expectedSelects) throws Exception {
162  capturer.scan(getPlanner().parse(sql));
163 
164  assertEquals("selects", expectedSelects, capturer.selects);
165  assertEquals("inserts", expectedInserts, capturer.inserts);
166  assertEquals("updates", asSet(), capturer.updates);
167  assertEquals("deletes", asSet(), capturer.deletes);
168  }
169 
170  public void testDelete(String sql,
171  Set<ImmutableList<String>> expectedDeletes,
172  Set<ImmutableList<String>> expectedSelects) throws Exception {
174  capturer.scan(getPlanner().parse(sql));
175 
176  assertEquals("selects", expectedSelects, capturer.selects);
177  assertEquals("inserts", asSet(), capturer.inserts);
178  assertEquals("updates", asSet(), capturer.updates);
179  assertEquals("deletes", expectedDeletes, capturer.deletes);
180  }
181 
182  @Test
183  public void testSelects() throws Exception {
184  String sql = "SELECT * FROM sales";
185  testSelect(sql, asSet(asImmutableList("sales")));
186 
187  sql = "SELECT * FROM sales AS s";
188  testSelect(sql, asSet(asImmutableList("sales")));
189 
190  sql = "SELECT * FROM sales AS s, reports AS r WHERE s.id = r.id";
191  testSelect(sql, asSet(asImmutableList("sales"), asImmutableList("reports")));
192 
193  sql = "SELECT * FROM sales AS s left outer join reports AS r on s.id = r.id";
194  testSelect(sql, asSet(asImmutableList("sales"), asImmutableList("reports")));
195 
196  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";
197  testSelect(sql,
198  asSet(asImmutableList("sales"),
199  asImmutableList("reports"),
200  asImmutableList("marketing")));
201 
202  sql = "SELECT * FROM sales UNION SELECT * FROM reports UNION SELECT * FROM marketing";
203  testSelect(sql,
204  asSet(asImmutableList("sales"),
205  asImmutableList("reports"),
206  asImmutableList("marketing")));
207 
208  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";
209  testSelect(sql, asSet(asImmutableList("query_rewrite_test")));
210 
211  sql = "SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY total_y DESC, str LIMIT 1";
212  testSelect(sql, asSet(asImmutableList("test")));
213 
214  sql = "SELECT str FROM (SELECT str, SUM(y) as total_y FROM test GROUP BY str ORDER BY total_y DESC, str LIMIT 1)";
215  testSelect(sql, asSet(asImmutableList("test")));
216 
217  sql = "SELECT deptno, dname FROM (SELECT * from dept) AS view_name LIMIT 10";
218  testSelect(sql, asSet(asImmutableList("dept")));
219 
220  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";
221  testSelect(sql, asSet(asImmutableList("emp"), asImmutableList("dept")));
222  }
223 
224  @Test
225  public void testSelectsWithSchema() throws Exception {
226  String sql = "SELECT * FROM mapd.sales";
227  testSelect(sql, asSet(asImmutableList("sales", "mapd")));
228 
229  sql = "SELECT * FROM mapd.sales AS s";
230  testSelect(sql, asSet(asImmutableList("sales", "mapd")));
231 
232  sql = "SELECT * FROM mapd.sales AS s, mapd.reports AS r WHERE s.id = r.id";
233  testSelect(sql,
234  asSet(asImmutableList("sales", "mapd"), asImmutableList("reports", "mapd")));
235 
236  sql = "SELECT * FROM mapd.sales AS s left outer join mapd.reports AS r on s.id = r.id";
237  testSelect(sql,
238  asSet(asImmutableList("sales", "mapd"), asImmutableList("reports", "mapd")));
239 
240  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";
241  testSelect(sql,
242  asSet(asImmutableList("sales", "mapd"),
243  asImmutableList("reports", "mapd"),
244  asImmutableList("marketing", "mapd")));
245 
246  sql = "SELECT * FROM mapd.sales UNION SELECT * FROM mapd.reports UNION SELECT * FROM mapd.marketing";
247  testSelect(sql,
248  asSet(asImmutableList("sales", "mapd"),
249  asImmutableList("reports", "mapd"),
250  asImmutableList("marketing", "mapd")));
251 
252  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";
253  testSelect(sql, asSet(asImmutableList("query_rewrite_test", "mapd")));
254 
255  sql = "SELECT str, SUM(y) as total_y FROM mapd.test GROUP BY str ORDER BY total_y DESC, str LIMIT 1";
256  testSelect(sql, asSet(asImmutableList("test", "mapd")));
257 
258  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)";
259  testSelect(sql, asSet(asImmutableList("test", "mapd")));
260 
261  sql = "SELECT deptno, dname FROM (SELECT * from mapd.dept) AS view_name LIMIT 10";
262  testSelect(sql, asSet(asImmutableList("dept", "mapd")));
263 
264  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";
265  testSelect(
266  sql, asSet(asImmutableList("emp", "mapd"), asImmutableList("dept", "mapd")));
267  }
268 
269  @Test
270  public void testInserts() throws Exception {
271  String sql = "INSERT INTO sales VALUES(10)";
272  testInsert(sql, asSet(asImmutableList("sales")), asSet());
273 
274  sql = "INSERT INTO sales(id, target) VALUES(10, 21321)";
275  testInsert(sql, asSet(asImmutableList("sales")), asSet());
276 
277  sql = "INSERT INTO sales(id, target) VALUES(10, (SELECT max(r.val) FROM reports AS r))";
278  testInsert(sql, asSet(asImmutableList("sales")), asSet(asImmutableList("reports")));
279 
280  sql = "INSERT INTO sales(id, target) VALUES((SELECT m.id FROM marketing m), (SELECT max(r.val) FROM reports AS r))";
281  testInsert(sql,
282  asSet(asImmutableList("sales")),
283  asSet(asImmutableList("reports"), asImmutableList("marketing")));
284  }
285 
286  @Test
287  public void testInsertsWithSchema() throws Exception {
288  String sql = "INSERT INTO mapd.sales VALUES(10)";
289  testInsert(sql, asSet(asImmutableList("sales", "mapd")), asSet());
290 
291  sql = "INSERT INTO mapd.sales(id, target) VALUES(10, 21321)";
292  testInsert(sql, asSet(asImmutableList("sales", "mapd")), asSet());
293 
294  sql = "INSERT INTO mapd.sales(id, target) VALUES(10, (SELECT max(r.val) FROM mapd.reports AS r))";
295  testInsert(sql,
296  asSet(asImmutableList("sales", "mapd")),
297  asSet(asImmutableList("reports", "mapd")));
298 
299  sql = "INSERT INTO mapd.sales(id, target) VALUES((SELECT m.id FROM mapd.marketing m), (SELECT max(r.val) FROM mapd.reports AS r))";
300  testInsert(sql,
301  asSet(asImmutableList("sales", "mapd")),
302  asSet(asImmutableList("reports", "mapd"),
303  asImmutableList("marketing", "mapd")));
304  }
305 
306  @Test
307  public void testUpdates() throws Exception {
308  String sql = "UPDATE sales SET id=10";
309  testUpdate(sql, asSet(asImmutableList("sales")), asSet());
310 
311  sql = "UPDATE sales SET id=10 WHERE id=1";
312  testUpdate(sql, asSet(asImmutableList("sales")), asSet());
313 
314  sql = "UPDATE sales SET id=(SELECT max(r.val) FROM reports AS r)";
315  testUpdate(sql, asSet(asImmutableList("sales")), asSet(asImmutableList("reports")));
316 
317  sql = "UPDATE sales SET id=(SELECT max(r.val) FROM reports AS r) WHERE id=(SELECT max(m.val) FROM marketing AS m)";
318  testUpdate(sql,
319  asSet(asImmutableList("sales")),
320  asSet(asImmutableList("reports"), asImmutableList("marketing")));
321 
322  sql = "UPDATE shardkey SET y=99 WHERE x=(SELECT max(id) from v2 LIMIT 1)";
323  testUpdate(sql, asSet(asImmutableList("shardkey")), asSet(asImmutableList("v2")));
324  }
325 
326  @Test
327  public void testUpdatesWithSchema() throws Exception {
328  String sql = "UPDATE mapd.sales SET id=10";
329  testUpdate(sql, asSet(asImmutableList("sales", "mapd")), asSet());
330 
331  sql = "UPDATE mapd.sales SET id=10 WHERE id=1";
332  testUpdate(sql, asSet(asImmutableList("sales", "mapd")), asSet());
333 
334  sql = "UPDATE mapd.sales SET id=(SELECT max(r.val) FROM mapd.reports AS r)";
335  testUpdate(sql,
336  asSet(asImmutableList("sales", "mapd")),
337  asSet(asImmutableList("reports", "mapd")));
338 
339  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)";
340  testUpdate(sql,
341  asSet(asImmutableList("sales", "mapd")),
342  asSet(asImmutableList("reports", "mapd"),
343  asImmutableList("marketing", "mapd")));
344 
345  sql = "UPDATE mapd.shardkey SET y=99 WHERE x=(SELECT max(id) from mapd.v2 LIMIT 1)";
346  testUpdate(sql,
347  asSet(asImmutableList("shardkey", "mapd")),
348  asSet(asImmutableList("v2", "mapd")));
349  }
350 
351  @Test
352  public void testDeletes() throws Exception {
353  String sql = "DELETE FROM sales";
354  testDelete(sql, asSet(asImmutableList("sales")), asSet());
355 
356  sql = "DELETE FROM sales WHERE id=1";
357  testDelete(sql, asSet(asImmutableList("sales")), asSet());
358 
359  sql = "DELETE FROM sales WHERE id=(SELECT max(r.val) FROM reports AS r)";
360  testDelete(sql, asSet(asImmutableList("sales")), asSet(asImmutableList("reports")));
361 
362  sql = "DELETE FROM sales WHERE id=(SELECT max(r.val) FROM reports AS r) AND id=(SELECT max(m.val) FROM marketing AS m)";
363  testDelete(sql,
364  asSet(asImmutableList("sales")),
365  asSet(asImmutableList("reports"), asImmutableList("marketing")));
366  }
367 
368  @Test
369  public void testDeletesWithSchema() throws Exception {
370  String sql = "DELETE FROM mapd.sales";
371  testDelete(sql, asSet(asImmutableList("sales", "mapd")), asSet());
372 
373  sql = "DELETE FROM mapd.sales WHERE id=1";
374  testDelete(sql, asSet(asImmutableList("sales", "mapd")), asSet());
375 
376  sql = "DELETE FROM mapd.sales WHERE id=(SELECT max(r.val) FROM mapd.reports AS r)";
377  testDelete(sql,
378  asSet(asImmutableList("sales", "mapd")),
379  asSet(asImmutableList("reports", "mapd")));
380 
381  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)";
382  testDelete(sql,
383  asSet(asImmutableList("sales", "mapd")),
384  asSet(asImmutableList("reports", "mapd"),
385  asImmutableList("marketing", "mapd")));
386  }
387 }
static Set< ImmutableList< String > > asSet(ImmutableList< String > val)
Expression getExpression(SchemaPlus parentSchema, String name)
static Set< ImmutableList< String > > asSet(ImmutableList< String >...vals)
string name
Definition: setup.in.py:72
void testInsert(String sql, Set< ImmutableList< String >> expectedInserts, Set< ImmutableList< String >> expectedSelects)
void testDelete(String sql, Set< ImmutableList< String >> expectedDeletes, Set< ImmutableList< String >> expectedSelects)
static ImmutableList< String > asImmutableList(String...vals)
void testUpdate(String sql, Set< ImmutableList< String >> expectedUpdates, Set< ImmutableList< String >> expectedSelects)
string version
Definition: setup.in.py:73
void testSelect(String sql, Set< ImmutableList< String >> expectedSelects)