View Javadoc
1   /*
2   Copyright (c) 2007 Health Market Science, Inc.
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  */
16  
17  package com.healthmarketscience.jackcess.query;
18  
19  import java.util.ArrayList;
20  import java.util.Arrays;
21  import java.util.HashMap;
22  import java.util.Iterator;
23  import java.util.List;
24  import java.util.Map;
25  
26  import com.healthmarketscience.jackcess.DataType;
27  import com.healthmarketscience.jackcess.Database;
28  import com.healthmarketscience.jackcess.TestUtil;
29  import com.healthmarketscience.jackcess.impl.query.QueryImpl;
30  import com.healthmarketscience.jackcess.impl.query.QueryImpl.Row;
31  import junit.framework.TestCase;
32  import org.apache.commons.lang.StringUtils;
33  
34  import static org.apache.commons.lang.SystemUtils.LINE_SEPARATOR;
35  import static com.healthmarketscience.jackcess.impl.query.QueryFormat.*;
36  
37  import static com.healthmarketscience.jackcess.impl.JetFormatTest.*;
38  
39  
40  /**
41   * @author James Ahlborn
42   */
43  public class QueryTest extends TestCase
44  {
45  
46    public QueryTest(String name) throws Exception {
47      super(name);
48    }
49  
50    public void testUnionQuery() throws Exception
51    {
52      String expr1 = "Select * from Table1";
53      String expr2 = "Select * from Table2";
54  
55      UnionQuery query = (UnionQuery)newQuery(
56          Query.Type.UNION, 
57          newRow(TABLE_ATTRIBUTE, expr1, null, UNION_PART1),
58          newRow(TABLE_ATTRIBUTE, expr2, null, UNION_PART2));
59      setFlag(query, 3);
60  
61      assertEquals(multiline("Select * from Table1",
62                             "UNION Select * from Table2;"), 
63                   query.toSQLString());
64  
65      setFlag(query, 1);
66  
67      assertEquals(multiline("Select * from Table1",
68                             "UNION ALL Select * from Table2;"), 
69                   query.toSQLString());
70  
71      addRows(query, newRow(ORDERBY_ATTRIBUTE, "Table1.id", 
72                                 null, null));
73  
74      assertEquals(multiline("Select * from Table1",
75                             "UNION ALL Select * from Table2",
76                             "ORDER BY Table1.id;"),
77                   query.toSQLString());
78  
79      removeRows(query, TABLE_ATTRIBUTE);
80  
81      try {
82        query.toSQLString();
83        fail("IllegalStateException should have been thrown");
84      } catch(IllegalStateException e) {
85        // success
86      }
87  
88    }
89  
90    public void testPassthroughQuery() throws Exception
91    {
92      String expr = "Select * from Table1";
93      String constr = "ODBC;";
94  
95      PassthroughQuery query = (PassthroughQuery)newQuery(
96          Query.Type.PASSTHROUGH, expr, constr);
97  
98      assertEquals(expr, query.toSQLString());
99      assertEquals(constr, query.getConnectionString());
100   }
101 
102   public void testDataDefinitionQuery() throws Exception
103   {
104     String expr = "Drop table Table1";
105 
106     DataDefinitionQuery query = (DataDefinitionQuery)newQuery(
107         Query.Type.DATA_DEFINITION, expr, null);
108 
109     assertEquals(expr, query.toSQLString());
110   }
111 
112   public void testUpdateQuery() throws Exception
113   {
114     UpdateQuery query = (UpdateQuery)newQuery(
115         Query.Type.UPDATE, 
116         newRow(TABLE_ATTRIBUTE, null, "Table1", null),
117         newRow(COLUMN_ATTRIBUTE, "\"some string\"", null, "Table1.id"),
118         newRow(COLUMN_ATTRIBUTE, "42", null, "Table1.col1"));
119 
120     assertEquals(
121         multiline("UPDATE Table1",
122                   "SET Table1.id = \"some string\", Table1.col1 = 42;"),
123         query.toSQLString());
124 
125     addRows(query, newRow(WHERE_ATTRIBUTE, "(Table1.col2 < 13)", 
126                                null, null));
127 
128     assertEquals(
129         multiline("UPDATE Table1",
130                   "SET Table1.id = \"some string\", Table1.col1 = 42",
131                   "WHERE (Table1.col2 < 13);"),
132         query.toSQLString());
133   }
134 
135   public void testSelectQuery() throws Exception
136   {
137     SelectQuery query = (SelectQuery)newQuery(
138         Query.Type.SELECT, 
139         newRow(TABLE_ATTRIBUTE, null, "Table1", null));
140     setFlag(query, 1);
141 
142     assertEquals(multiline("SELECT *",
143                            "FROM Table1;"),
144                  query.toSQLString());
145     
146     doTestColumns(query);
147     doTestSelectFlags(query);
148     doTestParameters(query);
149     doTestTables(query);
150     doTestRemoteDb(query);
151     doTestJoins(query);
152     doTestWhereExpression(query);
153     doTestGroupings(query);
154     doTestHavingExpression(query);
155     doTestOrderings(query);
156   }
157 
158   public void testBadQueries() throws Exception
159   {
160     List<Row> rowList = new ArrayList<Row>();
161     rowList.add(newRow(TYPE_ATTRIBUTE, null, -1, null, null));
162     QueryImpl query = QueryImpl.create(-1, "TestQuery", rowList, 13);
163     try {
164       query.toSQLString();
165       fail("UnsupportedOperationException should have been thrown");
166     } catch(UnsupportedOperationException e) {
167       // success
168     }
169 
170     addRows(query, newRow(TYPE_ATTRIBUTE, null, -1, null, null));
171     
172     try {
173       query.getTypeRow();
174       fail("IllegalStateException should have been thrown");
175     } catch(IllegalStateException e) {
176       // success
177     }
178 
179     try {
180       new QueryImpl("TestQuery", rowList, 13, Query.Type.UNION.getObjectFlag(),
181                     Query.Type.UNION) {
182         @Override protected void toSQLString(StringBuilder builder) {
183           throw new UnsupportedOperationException();
184         }};
185       fail("IllegalStateException should have been thrown");
186     } catch(IllegalStateException e) {
187       // success
188     }
189 
190   }
191 
192   public void testReadQueries() throws Exception
193   {
194     for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.QUERY, true)) {
195       Map<String,String> expectedQueries = new HashMap<String,String>();
196       expectedQueries.put(
197           "SelectQuery", multiline(
198               "SELECT DISTINCT Table1.*, Table2.col1, Table2.col2, Table3.col3",
199               "FROM (Table1 LEFT JOIN Table3 ON Table1.col1 = Table3.col1) INNER JOIN Table2 ON (Table3.col1 = Table2.col1) AND (Table3.col1 = Table2.col2)",
200               "WHERE (((Table2.col2)=\"foo\" Or (Table2.col2) In (\"buzz\",\"bazz\")))",
201               "ORDER BY Table2.col1;"));
202       expectedQueries.put(
203           "DeleteQuery", multiline(
204               "DELETE Table1.col1, Table1.col2, Table1.col3",
205               "FROM Table1",
206               "WHERE (((Table1.col1)>\"blah\"));"));
207       expectedQueries.put(
208           "AppendQuery",multiline(
209               "INSERT INTO Table3 (col2, col2, col3)",
210               "SELECT [Table1].[col2], [Table2].[col2], [Table2].[col3]",
211               "FROM Table3, Table1 INNER JOIN Table2 ON [Table1].[col1]=[Table2].[col1];"));
212       expectedQueries.put(
213           "UpdateQuery",multiline(
214               "PARAMETERS User Name Text;",
215               "UPDATE Table1",
216               "SET Table1.col1 = \"foo\", Table1.col2 = [Table2].[col3], [Table2].[col1] = [User Name]",
217               "WHERE ((([Table2].[col1]) Is Not Null));"));
218       expectedQueries.put(
219           "MakeTableQuery",multiline(
220               "SELECT Max(Table2.col1) AS MaxOfcol1, Table2.col2, Table3.col2 INTO Table4",
221               "FROM (Table2 INNER JOIN Table1 ON Table2.col1 = Table1.col2) RIGHT JOIN Table3 ON Table1.col2 = Table3.col3",
222               "GROUP BY Table2.col2, Table3.col2",
223               "HAVING (((Max(Table2.col1))=\"buzz\") AND ((Table2.col2)<>\"blah\"));"));
224       expectedQueries.put(
225           "CrosstabQuery", multiline(
226               "TRANSFORM Count([Table2].[col2]) AS CountOfcol2",
227               "SELECT Table2_1.col1, [Table2].[col3], Avg(Table2_1.col2) AS AvgOfcol2",
228               "FROM (Table1 INNER JOIN Table2 ON [Table1].[col1]=[Table2].[col1]) INNER JOIN Table2 AS Table2_1 ON [Table2].[col1]=Table2_1.col3",
229               "WHERE ((([Table1].[col1])>\"10\") And ((Table2_1.col1) Is Not Null) And ((Avg(Table2_1.col2))>\"10\"))",
230               "GROUP BY Table2_1.col1, [Table2].[col3]",
231               "ORDER BY [Table2].[col3]",
232               "PIVOT [Table1].[col1];"));
233       expectedQueries.put(
234           "UnionQuery", multiline(
235               "Select Table1.col1, Table1.col2",
236               "where Table1.col1 = \"foo\"",
237               "UNION",
238               "Select Table2.col1, Table2.col2",
239               "UNION ALL Select Table3.col1, Table3.col2",
240               "where Table3.col3 > \"blah\";"));
241       expectedQueries.put(
242           "PassthroughQuery", multiline(
243               "ALTER TABLE Table4 DROP COLUMN col5;\0"));
244       expectedQueries.put(
245           "DataDefinitionQuery", multiline(
246               "CREATE TABLE Table5 (col1 CHAR, col2 CHAR);\0"));
247 
248       Database db = TestUtil.open(testDB);
249 
250       for(Query q : db.getQueries()) {
251         assertEquals(expectedQueries.remove(q.getName()), q.toSQLString());
252       }
253 
254       assertTrue(expectedQueries.isEmpty());
255 
256       db.close();
257     }
258   }
259 
260   public void testAppendQuery() throws Exception
261   {
262     AppendQuery query = (AppendQuery)newQuery(
263         Query.Type.APPEND, null, "Table2",
264         // newRow(TABLE_ATTRIBUTE, null, "Table1", null),
265         newRow(COLUMN_ATTRIBUTE, "54", APPEND_VALUE_FLAG, null, null),
266         newRow(COLUMN_ATTRIBUTE, "'hello'", APPEND_VALUE_FLAG, null, null));
267 
268     assertEquals(multiline("INSERT INTO Table2", 
269                            "VALUES (54, 'hello');"), query.toSQLString());
270 
271     query = (AppendQuery)newQuery(
272         Query.Type.APPEND, null, "Table2",
273         // newRow(TABLE_ATTRIBUTE, null, "Table1", null),
274         newRow(COLUMN_ATTRIBUTE, "54", APPEND_VALUE_FLAG, null, "ID"),
275         newRow(COLUMN_ATTRIBUTE, "'hello'", APPEND_VALUE_FLAG, null, "Field 3"));
276 
277     assertEquals(multiline("INSERT INTO Table2 (ID, [Field 3])", 
278                            "VALUES (54, 'hello');"), query.toSQLString());
279   }
280 
281   private void doTestColumns(SelectQuery query) throws Exception
282   {
283     addRows(query, newRow(COLUMN_ATTRIBUTE, "Table1.id", null, null));
284     addRows(query, newRow(COLUMN_ATTRIBUTE, "Table1.col", "Some.Alias", null));
285 
286     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias], *",
287                            "FROM Table1;"),
288                  query.toSQLString());
289   }
290 
291   private void doTestSelectFlags(SelectQuery query) throws Exception
292   {
293     setFlag(query, 3);
294     
295     assertEquals(multiline("SELECT DISTINCT Table1.id, Table1.col AS [Some.Alias], *",
296                            "FROM Table1;"),
297                  query.toSQLString());
298 
299     setFlag(query, 9);
300     
301     assertEquals(multiline("SELECT DISTINCTROW Table1.id, Table1.col AS [Some.Alias], *",
302                            "FROM Table1;"),
303                  query.toSQLString());
304 
305     setFlag(query, 7);
306     
307     assertEquals(multiline("SELECT DISTINCT Table1.id, Table1.col AS [Some.Alias], *",
308                            "FROM Table1",
309                            "WITH OWNERACCESS OPTION;"),
310                  query.toSQLString());
311 
312     replaceRows(query, 
313                 newRow(FLAG_ATTRIBUTE, null, 49, null, "5", null));
314     
315     assertEquals(multiline("SELECT TOP 5 PERCENT Table1.id, Table1.col AS [Some.Alias], *",
316                            "FROM Table1;"),
317                  query.toSQLString());
318 
319     setFlag(query, 0);
320   }
321 
322   private void doTestParameters(SelectQuery query) throws Exception
323   {
324     addRows(query, newRow(PARAMETER_ATTRIBUTE, null, DataType.INT.getValue(), "INT_VAL", null));
325 
326     assertEquals(multiline("PARAMETERS INT_VAL Short;",
327                            "SELECT Table1.id, Table1.col AS [Some.Alias]",
328                            "FROM Table1;"),
329                  query.toSQLString());
330 
331     addRows(query, newRow(PARAMETER_ATTRIBUTE, null, DataType.TEXT.getValue(), 50, "TextVal", null),
332             newRow(PARAMETER_ATTRIBUTE, null, 0, 50, "[Some Value]", null));
333 
334     assertEquals(multiline("PARAMETERS INT_VAL Short, TextVal Text(50), [Some Value] Value;",
335                            "SELECT Table1.id, Table1.col AS [Some.Alias]",
336                            "FROM Table1;"),
337                  query.toSQLString());
338 
339     addRows(query, newRow(PARAMETER_ATTRIBUTE, null, -1, "BadVal", null));
340     try {
341       query.toSQLString();
342       fail("IllegalStateException should have been thrown");
343     } catch(IllegalStateException e) {
344       // success
345     }
346 
347     removeRows(query, PARAMETER_ATTRIBUTE);
348   }
349 
350   private void doTestTables(SelectQuery query) throws Exception
351   {
352     addRows(query, newRow(TABLE_ATTRIBUTE, null, "Table2", "Another Table"));
353     addRows(query, newRow(TABLE_ATTRIBUTE, "Select val from Table3", "val", "Table3Val"));
354 
355     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
356                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val;"),
357                  query.toSQLString());    
358   }
359 
360   private void doTestRemoteDb(SelectQuery query) throws Exception
361   {
362     addRows(query, newRow(REMOTEDB_ATTRIBUTE, null, 2, "other_db.mdb", null));
363 
364     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
365                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN 'other_db.mdb';"),
366                  query.toSQLString());
367 
368     replaceRows(query, newRow(REMOTEDB_ATTRIBUTE, "MDB_FILE;", 2, "other_db.mdb", null));
369 
370     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
371                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN 'other_db.mdb' [MDB_FILE;];"),
372                  query.toSQLString());
373 
374     replaceRows(query, newRow(REMOTEDB_ATTRIBUTE, "MDB_FILE;", 2, null, null));
375 
376     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
377                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val IN '' [MDB_FILE;];"),
378                  query.toSQLString());
379 
380     removeRows(query, REMOTEDB_ATTRIBUTE);
381   }
382 
383   private void doTestJoins(SelectQuery query) throws Exception
384   {
385     addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = [Another Table].id)", 1, "Table1", "Another Table"));
386 
387     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
388                            "FROM [Select val from Table3].val AS Table3Val, Table1 INNER JOIN Table2 AS [Another Table] ON (Table1.id = [Another Table].id);"),
389                  query.toSQLString());
390     
391     addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 2, "Table1", "Table3Val"));
392 
393     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
394                            "FROM (Table1 INNER JOIN Table2 AS [Another Table] ON (Table1.id = [Another Table].id)) LEFT JOIN [Select val from Table3].val AS Table3Val ON (Table1.id = Table3Val.id);"),
395                  query.toSQLString());
396 
397     addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 5, "Table1", "Table3Val"));
398 
399     try {
400       query.toSQLString();
401       fail("IllegalStateException should have been thrown");
402     } catch(IllegalStateException e) {
403       // success
404     }
405 
406     removeLastRows(query, 1);
407     query.toSQLString();
408 
409     addRows(query, newRow(JOIN_ATTRIBUTE, "(Table1.id = Table3Val.id)", 1, "BogusTable", "Table3Val"));
410 
411     try {
412       query.toSQLString();
413       fail("IllegalStateException should have been thrown");
414     } catch(IllegalStateException e) {
415       // success
416     }
417 
418     removeRows(query, JOIN_ATTRIBUTE);
419   }
420 
421   private void doTestWhereExpression(SelectQuery query) throws Exception
422   {
423     addRows(query, newRow(WHERE_ATTRIBUTE, "(Table1.col2 < 13)", 
424                           null, null));
425 
426     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
427                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val",
428                            "WHERE (Table1.col2 < 13);"),
429                  query.toSQLString());    
430   }
431 
432   private void doTestGroupings(SelectQuery query) throws Exception
433   {
434     addRows(query, newRow(GROUPBY_ATTRIBUTE, "Table1.id", null, null),
435             newRow(GROUPBY_ATTRIBUTE, "SUM(Table1.val)", null, null));
436 
437     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
438                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val",
439                            "WHERE (Table1.col2 < 13)",
440                            "GROUP BY Table1.id, SUM(Table1.val);"),
441                  query.toSQLString());    
442   }
443 
444   private void doTestHavingExpression(SelectQuery query) throws Exception
445   {
446     addRows(query, newRow(HAVING_ATTRIBUTE, "(SUM(Table1.val) = 500)", null, null));
447 
448     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
449                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val",
450                            "WHERE (Table1.col2 < 13)",
451                            "GROUP BY Table1.id, SUM(Table1.val)",
452                            "HAVING (SUM(Table1.val) = 500);"),
453                  query.toSQLString());    
454   }
455 
456   private void doTestOrderings(SelectQuery query) throws Exception
457   {
458     addRows(query, newRow(ORDERBY_ATTRIBUTE, "Table1.id", null, null),
459             newRow(ORDERBY_ATTRIBUTE, "Table2.val", "D", null));
460 
461     assertEquals(multiline("SELECT Table1.id, Table1.col AS [Some.Alias]",
462                            "FROM Table1, Table2 AS [Another Table], [Select val from Table3].val AS Table3Val",
463                            "WHERE (Table1.col2 < 13)",
464                            "GROUP BY Table1.id, SUM(Table1.val)",
465                            "HAVING (SUM(Table1.val) = 500)",
466                            "ORDER BY Table1.id, Table2.val DESC;"),
467                  query.toSQLString());    
468   }
469 
470 
471   private static Query newQuery(Query.Type type, Row... rows)
472   {
473     return newQuery(type, null, null, rows);
474   }
475 
476   private static Query newQuery(Query.Type type, String typeExpr,
477                                 String typeName1, Row... rows)
478   {
479     List<Row> rowList = new ArrayList<Row>();
480     rowList.add(newRow(TYPE_ATTRIBUTE, typeExpr, type.getValue(),
481                        null, typeName1, null));
482     rowList.addAll(Arrays.asList(rows));
483     return QueryImpl.create(type.getObjectFlag(), "TestQuery", rowList, 13);
484   }
485 
486   private static Row newRow(Byte attr, String expr, String name1, String name2)
487   {
488     return newRow(attr, expr, null, null, name1, name2);
489   }
490 
491   private static Row newRow(Byte attr, String expr, Number flagNum,
492                             String name1, String name2)
493   {
494     return newRow(attr, expr, flagNum, null, name1, name2);
495   }
496 
497   private static Row newRow(Byte attr, String expr, Number flagNum,
498                             Number extraNum, String name1, String name2)
499   {
500     Short flag = ((flagNum != null) ? flagNum.shortValue() : null);
501     Integer extra = ((extraNum != null) ? extraNum.intValue() : null);
502     return new Row(null, attr, expr, flag, extra, name1, name2, null, null);
503   }
504 
505   private static void setFlag(Query query, Number newFlagNum)
506   {
507     replaceRows(query, 
508                 newRow(FLAG_ATTRIBUTE, null, newFlagNum, null, null, null));
509   }
510 
511   private static void addRows(Query query, Row... rows)
512   {
513     ((QueryImpl)query).getRows().addAll(Arrays.asList(rows));
514   }
515 
516   private static void replaceRows(Query query, Row... rows)
517   {
518     removeRows(query, rows[0].attribute);
519     addRows(query, rows);
520   }
521 
522   private static void removeRows(Query query, Byte attr)
523   {
524     for(Iterator<Row> iter = ((QueryImpl)query).getRows().iterator(); iter.hasNext(); ) {
525       if(attr.equals(iter.next().attribute)) {
526         iter.remove();
527       }
528     }
529   }
530 
531   private static void removeLastRows(Query query, int num)
532   {
533     List<Row> rows = ((QueryImpl)query).getRows();
534     int size = rows.size();
535     rows.subList(size - num, size).clear();
536   }
537 
538   private static String multiline(String... strs)
539   {
540     return StringUtils.join(strs, LINE_SEPARATOR);
541   }
542 
543 }