View Javadoc
1   /*
2   Copyright (c) 2008 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.impl.query;
18  
19  import java.util.ArrayList;
20  import java.util.Collection;
21  import java.util.Iterator;
22  import java.util.List;
23  
24  import com.healthmarketscience.jackcess.RowId;
25  import com.healthmarketscience.jackcess.impl.DatabaseImpl;
26  import com.healthmarketscience.jackcess.impl.RowIdImpl;
27  import com.healthmarketscience.jackcess.impl.RowImpl;
28  import static com.healthmarketscience.jackcess.impl.query.QueryFormat.*;
29  import com.healthmarketscience.jackcess.query.Query;
30  import org.apache.commons.lang.builder.ToStringBuilder;
31  import org.apache.commons.logging.Log;
32  import org.apache.commons.logging.LogFactory;
33  
34  
35  /**
36   * Base class for classes which encapsulate information about an Access query.
37   * The {@link #toSQLString()} method can be used to convert this object into
38   * the actual SQL string which this query data represents.
39   * 
40   * @author James Ahlborn
41   */
42  public abstract class QueryImpl implements Query
43  {
44    protected static final Log LOG = LogFactory.getLog(QueryImpl.class);  
45  
46    private static final Row EMPTY_ROW = new Row();
47  
48    private final String _name;
49    private final List<Row> _rows;
50    private final int _objectId;
51    private final Type _type;
52    private final int _objectFlag;
53  
54    protected QueryImpl(String name, List<Row> rows, int objectId, int objectFlag, 
55                        Type type) 
56    {
57      _name = name;
58      _rows = rows;
59      _objectId = objectId;
60      _type = type;
61      _objectFlag = objectFlag;
62  
63      if(type != Type.UNKNOWN) {
64        short foundType = getShortValue(getQueryType(rows),
65                                        _type.getValue());
66        if(foundType != _type.getValue()) {
67          throw new IllegalStateException(withErrorContext(
68                  "Unexpected query type " + foundType));
69        }
70      }
71    }
72  
73    /**
74     * Returns the name of the query.
75     */
76    public String getName() {
77      return _name;
78    }
79  
80    /**
81     * Returns the type of the query.
82     */
83    public Type getType() {
84      return _type;
85    }
86  
87    public boolean isHidden() {
88      return((_objectFlag & DatabaseImpl.HIDDEN_OBJECT_FLAG) != 0);
89    }
90  
91    /**
92     * Returns the unique object id of the query.
93     */
94    public int getObjectId() {
95      return _objectId;
96    }
97  
98    public int getObjectFlag() {
99      return _objectFlag;
100   }
101 
102   /**
103    * Returns the rows from the system query table from which the query
104    * information was derived.
105    */
106   public List<Row> getRows() {
107     return _rows;
108   }
109 
110   protected List<Row> getRowsByAttribute(Byte attribute) {
111     return getRowsByAttribute(getRows(), attribute);
112   }
113 
114   protected Row getRowByAttribute(Byte attribute) {
115     return getUniqueRow(getRowsByAttribute(getRows(), attribute));
116   }
117 
118   public Row getTypeRow() {
119     return getRowByAttribute(TYPE_ATTRIBUTE);
120   }
121 
122   protected List<Row> getParameterRows() {
123     return getRowsByAttribute(PARAMETER_ATTRIBUTE);
124   }
125 
126   protected Row getFlagRow() {
127     return getRowByAttribute(FLAG_ATTRIBUTE);
128   }
129 
130   protected Row getRemoteDatabaseRow() {
131     return getRowByAttribute(REMOTEDB_ATTRIBUTE);
132   }
133 
134   protected List<Row> getTableRows() {
135     return getRowsByAttribute(TABLE_ATTRIBUTE);
136   }
137 
138   protected List<Row> getColumnRows() {
139     return getRowsByAttribute(COLUMN_ATTRIBUTE);
140   }
141 
142   protected List<Row> getJoinRows() {
143     return getRowsByAttribute(JOIN_ATTRIBUTE);
144   }
145 
146   protected Row getWhereRow() {
147     return getRowByAttribute(WHERE_ATTRIBUTE);
148   }
149 
150   protected List<Row> getGroupByRows() {
151     return getRowsByAttribute(GROUPBY_ATTRIBUTE);
152   }
153 
154   protected Row getHavingRow() {
155     return getRowByAttribute(HAVING_ATTRIBUTE);
156   }
157 
158   protected List<Row> getOrderByRows() {
159     return getRowsByAttribute(ORDERBY_ATTRIBUTE);
160   }
161 
162   protected abstract void toSQLString(StringBuilder builder);
163 
164   protected void toSQLParameterString(StringBuilder builder) {
165     // handle any parameters
166     List<String> params = getParameters();
167     if(!params.isEmpty()) {
168       builder.append("PARAMETERS ").append(params)
169         .append(';').append(NEWLINE);
170     }
171   }
172 
173   public List<String> getParameters() 
174   {
175     return (new RowFormatter(getParameterRows()) {
176         @Override protected void format(StringBuilder builder, Row row) {
177           String typeName = PARAM_TYPE_MAP.get(row.flag);
178           if(typeName == null) {
179             throw new IllegalStateException(withErrorContext(
180                 "Unknown param type " + row.flag));
181           }
182               
183           builder.append(row.name1).append(' ').append(typeName);
184           if((TEXT_FLAG.equals(row.flag)) && (getIntValue(row.extra, 0) > 0)) {
185             builder.append('(').append(row.extra).append(')');
186           }
187         }
188       }).format();
189   }
190 
191   protected List<String> getFromTables() 
192   {
193     // grab the list of query tables
194     List<TableSource> tableExprs = new ArrayList<TableSource>();
195     for(Row table : getTableRows()) {
196       StringBuilder builder = new StringBuilder();
197 
198       if(table.expression != null) {
199         toQuotedExpr(builder, table.expression).append(IDENTIFIER_SEP_CHAR);
200       }
201       if(table.name1 != null) {
202         toOptionalQuotedExpr(builder, table.name1, true);
203       }
204       toAlias(builder, table.name2);
205 
206       String key = ((table.name2 != null) ? table.name2 : table.name1);
207       tableExprs.add(new SimpleTable(key, builder.toString()));
208     }
209 
210     // combine the tables with any query joins
211     List<Row> joins = getJoinRows();
212     for(Row joinRow : joins) {
213         
214       String fromTable = joinRow.name1;
215       String toTable = joinRow.name2;
216 
217       TableSource fromTs = null;
218       TableSource toTs = null;
219 
220       // combine existing join expressions containing the target tables
221       for(Iterator<TableSource> joinIter = tableExprs.iterator(); 
222           (joinIter.hasNext() && ((fromTs == null) || (toTs == null))); ) {
223         TableSource ts = joinIter.next();
224 
225         if((fromTs == null) && ts.containsTable(fromTable)) {
226           fromTs = ts;
227 
228           // special case adding expr to existing join
229           if((toTs == null) && ts.containsTable(toTable)) {
230             toTs = ts;
231             break;
232           }
233 
234           joinIter.remove();
235 
236         } else if((toTs == null) && ts.containsTable(toTable)) {
237 
238           toTs = ts;
239           joinIter.remove();
240         }
241       }
242 
243       if(fromTs == null) {
244         fromTs = new SimpleTable(fromTable);
245       }
246       if(toTs == null) {
247         toTs = new SimpleTable(toTable);
248       }
249 
250       if(fromTs == toTs) {
251 
252         if(fromTs.sameJoin(joinRow.flag, joinRow.expression)) {
253           // easy-peasy, we just added the join expression to existing join,
254           // nothing more to do
255           continue;
256         }
257 
258         throw new IllegalStateException(withErrorContext(
259                 "Inconsistent join types for " + fromTable + " and " + toTable));
260       }
261 
262       // new join expression
263       tableExprs.add(new Join(fromTs, toTs, joinRow.flag, joinRow.expression));
264     }
265 
266     // convert join objects to SQL strings
267     List<String> result = new AppendableList<String>();
268     for(TableSource ts : tableExprs) {
269       result.add(ts.toString());
270     }
271 
272     return result;
273   }
274 
275   protected String getFromRemoteDbPath() 
276   {
277     return getRemoteDatabaseRow().name1;
278   }
279 
280   protected String getFromRemoteDbType() 
281   {
282     return getRemoteDatabaseRow().expression;
283   }
284 
285   protected String getWhereExpression()
286   {
287     return getWhereRow().expression;
288   }
289 
290   protected List<String> getOrderings() 
291   {
292     return (new RowFormatter(getOrderByRows()) {
293         @Override protected void format(StringBuilder builder, Row row) {
294           builder.append(row.expression);
295           if(DESCENDING_FLAG.equalsIgnoreCase(row.name1)) {
296             builder.append(" DESC");
297           }
298         }
299       }).format();
300   }
301 
302   public String getOwnerAccessType() {
303     return(hasFlag(OWNER_ACCESS_SELECT_TYPE) ?
304            "WITH OWNERACCESS OPTION" : DEFAULT_TYPE);
305   }
306 
307   protected boolean hasFlag(int flagMask)
308   {
309     return hasFlag(getFlagRow(), flagMask);
310   }
311 
312   protected boolean supportsStandardClauses() {
313     return true;
314   }
315 
316   /**
317    * Returns the actual SQL string which this query data represents.
318    */
319   public String toSQLString() 
320   {
321     StringBuilder builder = new StringBuilder();
322     if(supportsStandardClauses()) {
323       toSQLParameterString(builder);
324     }
325 
326     toSQLString(builder);
327 
328     if(supportsStandardClauses()) {
329 
330       String accessType = getOwnerAccessType();
331       if(!DEFAULT_TYPE.equals(accessType)) {
332         builder.append(NEWLINE).append(accessType);
333       }
334       
335       builder.append(';');
336     }
337     return builder.toString();
338   }
339 
340   @Override
341   public String toString() {
342     return ToStringBuilder.reflectionToString(this);
343   }
344 
345   /**
346    * Creates a concrete Query instance from the given query data.
347    *
348    * @param objectFlag the flag indicating the type of the query
349    * @param name the name of the query
350    * @param rows the rows from the system query table containing the data
351    *             describing this query
352    * @param objectId the unique object id of this query
353    *
354    * @return a Query instance for the given query data
355    */
356   public static QueryImpl create(int objectFlag, String name, List<Row> rows, 
357                                  int objectId)
358   {
359     // remove other object flags before testing for query type
360     int objTypeFlag = objectFlag & OBJECT_FLAG_MASK;
361 
362     if(objTypeFlag == 0) {
363       // sometimes the query rows tell a different story
364       short rowTypeFlag = getShortValue(getQueryType(rows), objTypeFlag);
365       Type rowType = TYPE_MAP.get(rowTypeFlag);
366       if((rowType != null) && (rowType.getObjectFlag() != objTypeFlag)) {
367         // use row type instead of object flag type
368         objTypeFlag = rowType.getObjectFlag();
369       }
370     }
371 
372     try {
373       switch(objTypeFlag) {
374       case SELECT_QUERY_OBJECT_FLAG:
375         return new SelectQueryImpl(name, rows, objectId, objectFlag);
376       case MAKE_TABLE_QUERY_OBJECT_FLAG:
377         return new MakeTableQueryImpl(name, rows, objectId, objectFlag);
378       case APPEND_QUERY_OBJECT_FLAG:
379         return new AppendQueryImpl(name, rows, objectId, objectFlag);
380       case UPDATE_QUERY_OBJECT_FLAG:
381         return new UpdateQueryImpl(name, rows, objectId, objectFlag);
382       case DELETE_QUERY_OBJECT_FLAG:
383         return new DeleteQueryImpl(name, rows, objectId, objectFlag);
384       case CROSS_TAB_QUERY_OBJECT_FLAG:
385         return new CrossTabQueryImpl(name, rows, objectId, objectFlag);
386       case DATA_DEF_QUERY_OBJECT_FLAG:
387         return new DataDefinitionQueryImpl(name, rows, objectId, objectFlag);
388       case PASSTHROUGH_QUERY_OBJECT_FLAG:
389         return new PassthroughQueryImpl(name, rows, objectId, objectFlag);
390       case UNION_QUERY_OBJECT_FLAG:
391         return new UnionQueryImpl(name, rows, objectId, objectFlag);
392       default:
393         // unknown querytype
394         throw new IllegalStateException(withErrorContext(
395                 "unknown query object flag " + objTypeFlag, name));
396       }
397     } catch(IllegalStateException e) {
398       LOG.warn(withErrorContext("Failed parsing query", name), e);
399     }
400 
401     // return unknown query
402     return new UnknownQueryImpl(name, rows, objectId, objectFlag);
403   }
404 
405   private static Short getQueryType(List<Row> rows)
406   {
407     return getFirstRowByAttribute(rows, TYPE_ATTRIBUTE).flag;
408   }
409 
410   private static List<Row> getRowsByAttribute(List<Row> rows, Byte attribute) {
411     List<Row> result = new ArrayList<Row>();
412     for(Row row : rows) {
413       if(attribute.equals(row.attribute)) {
414         result.add(row);
415       }
416     }
417     return result;
418   }
419 
420   private static Row getFirstRowByAttribute(List<Row> rows, Byte attribute) {
421     for(Row row : rows) {
422       if(attribute.equals(row.attribute)) {
423         return row;
424       }
425     }
426     return EMPTY_ROW;
427   }
428 
429   protected Row getUniqueRow(List<Row> rows) {
430     if(rows.size() == 1) {
431       return rows.get(0);
432     }
433     if(rows.isEmpty()) {
434       return EMPTY_ROW;
435     }
436     throw new IllegalStateException(withErrorContext(
437             "Unexpected number of rows for" + rows));
438   }
439 
440   protected static List<Row> filterRowsByFlag(
441       List<Row> rows, final short flag) 
442   {
443     return new RowFilter() {
444         @Override protected boolean keep(Row row) {
445           return hasFlag(row, flag);
446         }
447       }.filter(rows);
448   }
449 
450   protected static List<Row> filterRowsByNotFlag(
451       List<Row> rows, final short flag) 
452   {
453     return new RowFilter() {
454         @Override protected boolean keep(Row row) {
455           return !hasFlag(row, flag);
456         }
457       }.filter(rows);
458   }
459 
460   protected static boolean hasFlag(Row row, int flagMask)
461   {
462     return((getShortValue(row.flag, 0) & flagMask) != 0);
463   }
464 
465   protected static short getShortValue(Short s, int def) {
466     return ((s != null) ? (short)s : (short)def);
467   }
468 
469   protected static int getIntValue(Integer i, int def) {
470     return ((i != null) ? (int)i : def);
471   }
472 
473   protected static StringBuilder toOptionalQuotedExpr(StringBuilder builder, 
474                                                       String fullExpr,
475                                                       boolean isIdentifier)
476   {
477     String[] exprs = (isIdentifier ? 
478                       IDENTIFIER_SEP_PAT.split(fullExpr) : 
479                       new String[]{fullExpr});
480     for(int i = 0; i < exprs.length; ++i) {
481       String expr = exprs[i];
482       if(QUOTABLE_CHAR_PAT.matcher(expr).find()) {
483         toQuotedExpr(builder, expr);
484       } else {
485         builder.append(expr);
486       }
487       if(i < (exprs.length - 1)) {
488         builder.append(IDENTIFIER_SEP_CHAR);
489       }
490     }
491     return builder;
492   }
493 
494   protected static StringBuilder toQuotedExpr(StringBuilder builder, 
495                                               String expr)
496   {
497     return (!isQuoted(expr) ? 
498             builder.append('[').append(expr).append(']') :
499             builder.append(expr));
500   }
501 
502   protected static boolean isQuoted(String expr) {
503     return ((expr.length() >= 2) && 
504             (expr.charAt(0) == '[') && (expr.charAt(expr.length() - 1) == ']'));
505   }
506 
507   protected static StringBuilder toRemoteDb(StringBuilder builder,
508                                             String remoteDbPath,
509                                             String remoteDbType) {
510     if((remoteDbPath != null) || (remoteDbType != null)) {
511       // note, always include path string, even if empty
512       builder.append(" IN '");
513       if(remoteDbPath != null) {
514         builder.append(remoteDbPath);
515       }
516       builder.append('\'');
517       if(remoteDbType != null) {
518         builder.append(" [").append(remoteDbType).append(']');
519       }
520     }
521     return builder;
522   }
523 
524   protected static StringBuilder toAlias(StringBuilder builder,
525                                          String alias) {
526     if(alias != null) {
527       toOptionalQuotedExpr(builder.append(" AS "), alias, false);
528     }
529     return builder;
530   }
531 
532   private String withErrorContext(String msg) {
533     return withErrorContext(msg, getName());
534   }
535 
536   private static String withErrorContext(String msg, String queryName) {
537     return msg + " (Query: " + queryName + ")";
538   }
539 
540 
541   private static final class UnknownQueryImpl extends QueryImpl
542   {
543     private UnknownQueryImpl(String name, List<Row> rows, int objectId, 
544                              int objectFlag) 
545     {
546       super(name, rows, objectId, objectFlag, Type.UNKNOWN);
547     }
548 
549     @Override
550     protected void toSQLString(StringBuilder builder) {
551       throw new UnsupportedOperationException();
552     }
553   }
554 
555   /**
556    * Struct containing the information from a single row of the system query
557    * table.
558    */
559   public static final class Row
560   {
561     private final RowId _id;
562     public final Byte attribute;
563     public final String expression;
564     public final Short flag;
565     public final Integer extra;
566     public final String name1;
567     public final String name2;
568     public final Integer objectId;
569     public final byte[] order;
570 
571     private Row() {
572       this._id = null;
573       this.attribute = null;
574       this.expression = null;
575       this.flag = null;
576       this.extra = null;
577       this.name1 = null;
578       this.name2= null;
579       this.objectId = null;
580       this.order = null;
581     }
582 
583     public Row(com.healthmarketscience.jackcess.Row tableRow) {
584       this(tableRow.getId(),
585            tableRow.getByte(COL_ATTRIBUTE),
586            tableRow.getString(COL_EXPRESSION),
587            tableRow.getShort(COL_FLAG),
588            tableRow.getInt(COL_EXTRA),
589            tableRow.getString(COL_NAME1),
590            tableRow.getString(COL_NAME2),
591            tableRow.getInt(COL_OBJECTID),
592            tableRow.getBytes(COL_ORDER));
593     }
594 
595     public Row(RowId id, Byte attribute, String expression, Short flag,
596                Integer extra, String name1, String name2,
597                Integer objectId, byte[] order)
598     {
599       this._id = id;
600       this.attribute = attribute;
601       this.expression = expression;
602       this.flag = flag;
603       this.extra = extra;
604       this.name1 = name1;
605       this.name2= name2;
606       this.objectId = objectId;
607       this.order = order;
608     }
609 
610     public com.healthmarketscience.jackcess.Row toTableRow()
611     {
612       com.healthmarketscience.jackcess.Row tableRow = new RowImpl((RowIdImpl)_id);
613 
614       tableRow.put(COL_ATTRIBUTE, attribute);
615       tableRow.put(COL_EXPRESSION, expression);
616       tableRow.put(COL_FLAG, flag);
617       tableRow.put(COL_EXTRA, extra);
618       tableRow.put(COL_NAME1, name1);
619       tableRow.put(COL_NAME2, name2);
620       tableRow.put(COL_OBJECTID, objectId);
621       tableRow.put(COL_ORDER, order);
622 
623       return tableRow;
624     }
625 
626     @Override
627     public String toString() {
628       return ToStringBuilder.reflectionToString(this);
629     }
630   }
631 
632   protected static abstract class RowFormatter
633   {
634     private final List<Row> _list;
635 
636     protected RowFormatter(List<Row> list) {
637       _list = list;
638     }
639 
640     public List<String> format() {
641       return format(new AppendableList<String>());
642     }
643 
644     public List<String> format(List<String> strs) {
645       for(Row row : _list) {
646         StringBuilder builder = new StringBuilder();
647         format(builder, row);
648         strs.add(builder.toString());
649       }
650       return strs;
651     }
652 
653     protected abstract void format(StringBuilder builder, Row row);
654   }
655 
656   protected static abstract class RowFilter
657   {
658     protected RowFilter() {
659     }
660 
661     public List<Row> filter(List<Row> list) {
662       for(Iterator<Row> iter = list.iterator(); iter.hasNext(); ) {
663         if(!keep(iter.next())) {
664           iter.remove();
665         }
666       }
667       return list;
668     }
669 
670     protected abstract boolean keep(Row row);
671   }
672 
673   protected static class AppendableList<E> extends ArrayList<E>
674   {
675     private static final long serialVersionUID = 0L;
676 
677     protected AppendableList() {
678     }
679 
680     protected AppendableList(Collection<? extends E> c) {
681       super(c);
682     }
683 
684     protected String getSeparator() {
685       return ", ";
686     }
687 
688     @Override
689     public String toString() {
690       StringBuilder builder = new StringBuilder();
691       for(Iterator<E> iter = iterator(); iter.hasNext(); ) {
692         builder.append(iter.next().toString());
693         if(iter.hasNext()) {
694           builder.append(getSeparator());
695         }
696       }
697       return builder.toString();
698     }
699   }
700 
701   /**
702    * Base type of something which provides table data in a query
703    */
704   private static abstract class TableSource
705   {
706     @Override
707     public String toString() {
708       StringBuilder sb = new StringBuilder();
709       toString(sb, true);
710       return sb.toString();
711     }
712 
713     protected abstract void toString(StringBuilder sb, boolean isTopLevel);
714 
715     public abstract boolean containsTable(String table);
716 
717     public abstract boolean sameJoin(short type, String on);
718   }
719 
720   /**
721    * Table data provided by a single table expression.
722    */
723   private static final class SimpleTable extends TableSource
724   {
725     private final String _tableName;
726     private final String _tableExpr;
727 
728     private SimpleTable(String tableName) {
729       this(tableName, toOptionalQuotedExpr(
730                new StringBuilder(), tableName, true).toString());
731     }
732 
733     private SimpleTable(String tableName, String tableExpr) {
734       _tableName = tableName;
735       _tableExpr = tableExpr;
736     }
737 
738     @Override
739     protected void toString(StringBuilder sb, boolean isTopLevel) {
740       sb.append(_tableExpr);
741     }
742 
743     @Override
744     public boolean containsTable(String table) {
745       return _tableName.equalsIgnoreCase(table);
746     }
747 
748     @Override
749     public boolean sameJoin(short type, String on) {
750       return false;
751     }
752   }
753 
754   /**
755    * Table data provided by a join expression.
756    */
757   private final class Join extends TableSource
758   {
759     private final TableSource _from;
760     private final TableSource _to;
761     private final short _jType;
762     // combine all the join expressions with "AND"
763     private final List<String> _on = new AppendableList<String>() {
764       private static final long serialVersionUID = 0L;
765       @Override
766       protected String getSeparator() {
767         return ") AND (";
768       }
769     };
770 
771     private Join(TableSource from, TableSource to, short type, String on) {
772       _from = from;
773       _to = to;
774       _jType = type;
775       _on.add(on);
776     }
777 
778     @Override
779     protected void toString(StringBuilder sb, boolean isTopLevel) {
780       String joinType = JOIN_TYPE_MAP.get(_jType);
781       if(joinType == null) {
782         throw new IllegalStateException(withErrorContext(
783                                             "Unknown join type " + _jType));
784       }
785 
786       if(!isTopLevel) {
787         sb.append("(");
788       }
789 
790       _from.toString(sb, false);
791       sb.append(joinType);
792       _to.toString(sb, false);
793       sb.append(" ON ");
794 
795       boolean multiOnExpr = (_on.size() > 1);
796       if(multiOnExpr) {
797         sb.append("(");
798       }
799       sb.append(_on);
800       if(multiOnExpr) {
801         sb.append(")");
802       }
803 
804       if(!isTopLevel) {
805         sb.append(")");
806       }
807     }
808 
809     @Override
810     public boolean containsTable(String table) {
811       return _from.containsTable(table) || _to.containsTable(table);
812     }
813 
814     @Override
815     public boolean sameJoin(short type, String on) {
816       if(_jType == type) {
817         // note, AND conditions are added in _reverse_ order
818         _on.add(0, on);
819         return true;
820       }
821       return false;
822     }
823   }
824 }