Query


1   /*
2    * Copyright (c) 1998-2006 Caucho Technology -- all rights reserved
3    *
4    * This file is part of Resin(R) Open Source
5    *
6    * Each copy or derived work must preserve the copyright notice and this
7    * notice unmodified.
8    *
9    * Resin Open Source is free software; you can redistribute it and/or modify
10   * it under the terms of the GNU General Public License as published by
11   * the Free Software Foundation; either version 2 of the License, or
12   * (at your option) any later version.
13   *
14   * Resin Open Source is distributed in the hope that it will be useful,
15   * but WITHOUT ANY WARRANTY; without even the implied warranty of
16   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE, or any warranty
17   * of NON-INFRINGEMENT.  See the GNU General Public License for more
18   * details.
19   *
20   * You should have received a copy of the GNU General Public License
21   * along with Resin Open Source; if not, write to the
22   *
23   *   Free Software Foundation, Inc.
24   *   59 Temple Place, Suite 330
25   *   Boston, MA 02111-1307  USA
26   *
27   * @author Scott Ferguson
28   */
29  
30  package com.caucho.db.sql;
31  
32  import com.caucho.db.Database;
33  import com.caucho.db.store.Transaction;
34  import com.caucho.db.table.Column;
35  import com.caucho.db.table.Table;
36  import com.caucho.db.table.TableIterator;
37  import com.caucho.log.Log;
38  import com.caucho.sql.SQLExceptionWrapper;
39  import com.caucho.util.CharBuffer;
40  import com.caucho.util.L10N;
41  
42  import java.io.IOException  ;
43  import java.io.InputStream  ;
44  import java.sql.SQLException  ;
45  import java.util.ArrayList  ;
46  import java.util.logging.Level  ;
47  import java.util.logging.Logger  ;
48  
49  abstract public class Query {
50    private static final Logger   log = Log.open(Query.class);
51    private static final L10N L = new L10N(Query.class);
52  
53    private Database _db;
54  
55    private String   _sql;
56  
57    private FromItem []_fromItems;
58    private ParamExpr []_params;
59  
60    private boolean _isGroup;
61    private int _dataFieldCount;
62  
63    private Query _parent;
64    private SubSelectExpr _parentSubSelect;
65  
66    private Expr []_whereExprs;
67    protected Expr _whereExpr;
68  
69    private RowIterateExpr []_indexExprs;
70  
71    private ArrayList  <SubSelectParamExpr> _paramExprs
72      = new ArrayList  <SubSelectParamExpr>();
73  
74    protected Query(Database db, String   sql)
75    {
76      _db = db;
77      _sql = sql;
78    }
79  
80    protected Query(Database db, String   sql, FromItem []fromItems)
81    {
82      _db = db;
83      _sql = sql;
84      _fromItems = fromItems;
85    }
86  
87    /**
88     * Returns the owning database.
89     */
90    public Database getDatabase()
91    {
92      return _db;
93    }
94  
95    /**
96     * Sets the parent query
97     */
98    public void setParent(Query query)
99    {
100     _parent = query;
101   }
102 
103   /**
104    * Gets the parent query
105    */
106   public Query getParent()
107   {
108     return _parent;
109   }
110 
111   /**
112    * Sets the parent sub-select.
113    */
114   public void setSubSelect(SubSelectExpr subSelect)
115   {
116     _parentSubSelect = subSelect;
117   }
118 
119   /**
120    * Gets the parent sub-select.
121    */
122   public SubSelectExpr getSubSelect()
123   {
124     return _parentSubSelect;
125   }
126 
127   /**
128    * Returns the number of temporary data fields.
129    */
130   public int getDataFields()
131   {
132     return _dataFieldCount;
133   }
134 
135   /**
136    * Sets the number of temporary data fields.
137    */
138   public void setDataFields(int fieldCount)
139   {
140     _dataFieldCount = fieldCount;
141   }
142 
143   /**
144    * Returns any from items.
145    */
146   public FromItem []getFromItems()
147   {
148     return _fromItems;
149   }
150 
151   /**
152    * Sets from items.
153    */
154   protected void setFromItems(FromItem []fromItems)
155   {
156     _fromItems = fromItems;
157   }
158 
159   /**
160    * Sets from items.
161    */
162   protected void setFromItems(ArrayList  <FromItem> fromItems)
163   {
164     _fromItems = new FromItem [fromItems.size()];
165     fromItems.toArray(_fromItems);
166   }
167 
168   /**
169    * Sets the where expr.
170    */
171   public void setWhereExpr(Expr expr)
172   {
173     _whereExpr = expr;
174   }
175 
176   /**
177    * Returns the where exprs
178    */
179   public Expr []getWhereExprs()
180   {
181     return _whereExprs;
182   }
183 
184   /**
185    * Sets the where exprs.
186    */
187   protected void setWhereExprs(Expr []whereExprs)
188   {
189     _whereExprs = whereExprs;
190   }
191 
192   /**
193    * Sets the params.
194    */
195   public void setParams(ParamExpr []params)
196   {
197     _params = params;
198   }
199 
200   /**
201    * Returns the param exprs.
202    */
203   public ArrayList  <SubSelectParamExpr> getParamExprs()
204   {
205     return _paramExprs;
206   }
207 
208   /**
209    * Returns the SQL.
210    */
211   String   getSQL()
212   {
213     return _sql;
214   }
215 
216   /**
217    * Returns true for select queries.
218    */
219   public boolean isSelect()
220   {
221     return false;
222   }
223 
224   public boolean isReadOnly()
225   {
226     return true;
227   }
228 
229   /**
230    * Sets the current number of group fields.
231    */
232   public void setGroup(boolean isGroup)
233   {
234     _isGroup = isGroup;
235   }
236 
237   /**
238    * Sets true for group operations
239    */
240   public boolean isGroup()
241   {
242     return _isGroup;
243   }
244 
245   /**
246    * Binds the query.
247    */
248   protected void bind()
249     throws SQLException  
250   {
251     if (_whereExpr != null) {
252       generateWhere(_whereExpr);
253 
254       for (int i = 0; i < _whereExprs.length; i++) {
255     Expr expr = _whereExprs[i];
256 
257     if (expr != null)
258       _whereExprs[i] = expr.bind(this);
259       }
260     }
261     else if (_fromItems != null) {
262       _whereExprs = new Expr[_fromItems.length + 1];
263       _indexExprs = new RowIterateExpr[_fromItems.length];
264     }
265     else {
266       _whereExprs = new Expr[2];
267       _indexExprs = new RowIterateExpr[1];
268     }
269 
270     for (int i = 0; i < _indexExprs.length; i++) {
271       Expr expr = _indexExprs[i];
272 
273       if (expr == null)
274     _indexExprs[i] = new RowIterateExpr();
275       else
276     _indexExprs[i] = (RowIterateExpr) _indexExprs[i].bind(this);
277     }
278 
279     for (int i = 0; i < _paramExprs.size(); i++) {
280       SubSelectParamExpr expr = _paramExprs.get(i);
281 
282       expr = (SubSelectParamExpr) expr.bind(_parent);
283       _paramExprs.set(i, expr);
284     }
285   }
286 
287   /**
288    * Optimize the where and order the from items.
289    */
290   protected void generateWhere(Expr whereExpr)
291     throws SQLException  
292   {
293     ArrayList  <Expr> andProduct = new ArrayList  <Expr>();
294 
295     whereExpr.splitAnd(andProduct);
296 
297     FromItem []fromItems = getFromItems();
298 
299     Expr []whereExprs = new Expr[fromItems.length + 1];
300     RowIterateExpr []indexExprs = new RowIterateExpr[fromItems.length];
301 
302     _whereExprs = whereExprs;
303     _indexExprs = indexExprs;
304 
305     ArrayList  <FromItem> costItems = new ArrayList  <FromItem>();
306     orderFromItems(costItems, andProduct);
307 
308     costItems.clear();
309     for (int i = fromItems.length; i >= 0; i--) {
310       if (i < fromItems.length)
311     costItems.add(fromItems[i]);
312 
313       AndExpr subWhereExpr = null;
314       boolean hasExpr = false;
315 
316       int bestIndex = -1;
317       long bestCost;
318 
319       do {
320     bestCost = Long.MAX_VALUE;
321 
322     for (int j = andProduct.size() - 1; j >= 0; j--) {
323       Expr subExpr = andProduct.get(j);
324 
325       long cost = subExpr.cost(costItems);
326 
327       if (Integer.MAX_VALUE <= cost && i != 0) {
328       }
329       else if (cost < bestCost) {
330         bestCost = cost;
331         bestIndex = j;
332       }
333     }
334 
335     if (bestCost < Long.MAX_VALUE) {
336       Expr expr = andProduct.remove(bestIndex);
337       RowIterateExpr indexExpr = null;
338 
339       if (i < fromItems.length)
340         indexExpr = expr.getIndexExpr(fromItems[i]);
341 
342       if (indexExpr != null && indexExprs[i] == null) {
343         indexExprs[i] = indexExpr;
344       }
345       else {
346         // XXX: check if really need to add
347         if (subWhereExpr == null)
348           subWhereExpr = new AndExpr();
349 
350         subWhereExpr.add(expr);
351       }
352     }
353       } while (bestCost < Long.MAX_VALUE);
354 
355       if (subWhereExpr != null)
356     whereExprs[i] = subWhereExpr.getSingleExpr();
357     }
358 
359     for (int i = 0; i < whereExprs.length; i++) {
360       Expr expr = whereExprs[i];
361       /*
362       if (expr != null)
363     expr = expr.bind(this);
364       */
365 
366       whereExprs[i] = expr;
367     }
368 
369     _whereExprs = whereExprs;
370 
371     if (log.isLoggable(Level.FINE)) {
372       log.fine("where-" + (whereExprs.length - 1) +  ": static " + whereExprs[whereExprs.length - 1]);
373 
374       for (int i = whereExprs.length - 2; i >= 0; i--) {
375     if (_indexExprs[i] != null)
376       log.fine("index-" + i + ": " + _fromItems[i] + " " + _indexExprs[i]);
377 
378     log.fine("where-" + i + ": " + _fromItems[i] + " " + whereExprs[i]);
379       }
380     }
381   }
382 
383   private void orderFromItems(ArrayList  <FromItem> costItems,
384                   ArrayList  <Expr> topAndProduct)
385   {
386     FromItem []fromItems = getFromItems();
387 
388     ArrayList  <Expr> andProduct = new ArrayList  <Expr>(topAndProduct);
389 
390     for (int i = fromItems.length - 1; i >= 0; i--) {
391       costItems.clear();
392 
393       for (int j = i + 1; j < fromItems.length; j++)
394     costItems.add(fromItems[j]);
395 
396       int bestIndex = i;
397       long bestCost = Expr.COST_INVALID;
398 
399       loop:
400       for (int j = 0; j <= i; j++) {
401     FromItem item = fromItems[j];
402 
403     costItems.add(item);
404 
405     for (int k = 0; k < fromItems.length; k++) {
406       if (! fromItems[k].isValid(costItems)) {
407         costItems.remove(costItems.size() - 1);
408         continue loop;
409       }
410     }
411 
412     long cost = Long.MAX_VALUE;
413     for (int k = 0; k < andProduct.size(); k++) {
414       Expr expr = andProduct.get(k);
415 
416       long subCost = expr.cost(costItems);
417 
418       if (Expr.COST_INVALID <= subCost) {
419         costItems.remove(costItems.size() - 1);
420         continue loop;
421       }
422 
423       if (subCost < cost)
424         cost = subCost;
425     }
426 
427     costItems.remove(costItems.size() - 1);
428 
429     if (cost < bestCost) {
430       bestCost = cost;
431       bestIndex = j;
432     }
433       }
434 
435       FromItem tempItem = fromItems[i];
436       fromItems[i] = fromItems[bestIndex];
437       fromItems[bestIndex] = tempItem;
438 
439       costItems.add(fromItems[i]);
440       for (int k = andProduct.size() - 1; k >= 0; k--) {
441     Expr expr = andProduct.get(k);
442 
443     long subCost = expr.cost(costItems);
444 
445     if (subCost < Expr.COST_NO_TABLE) {
446       andProduct.remove(k);
447     }
448       }
449     }
450   }
451 
452   private String   logWhere()
453   {
454     CharBuffer cb = CharBuffer.allocate();
455 
456     cb.append("[");
457     for (int i = 0; i < _whereExprs.length; i++) {
458       if (i != 0)
459     cb.append(", ");
460 
461       if (_whereExprs[i] != null)
462     cb.append(_whereExprs[i]);
463     }
464 
465     cb.append("]");
466 
467     return cb.close();
468   }
469 
470   /**
471    * Returns a bound expression for the specified table.column.
472    */
473   protected Expr bind(String   tableName, String   columnName)
474     throws SQLException  
475   {
476     FromItem []fromItems = getFromItems();
477 
478     if (tableName == null) {
479       if ("resin_oid".equals(columnName))
480     return new OidExpr(fromItems[0].getTable(), 0);
481 
482       for (int i = 0; i < fromItems.length; i++) {
483     Table table = fromItems[i].getTable();
484 
485     int columnIndex = table.getColumnIndex(columnName);
486 
487     if (columnIndex >= 0) {
488       Column column = table.getColumn(columnName);
489 
490       return new IdExpr(fromItems[i], column);
491     }
492       }
493 
494       Expr expr = bindParent(tableName, columnName);
495       if (expr != null) {
496     return expr;
497       }
498 
499       throw new SQLException  (L.l("`{0}' is an unknown column.", columnName));
500     }
501     else {
502       for (int i = 0; i < fromItems.length; i++) {
503     if (tableName.equals(fromItems[i].getName())) {
504       Table table = fromItems[i].getTable();
505 
506       if ("resin_oid".equals(columnName))
507         return new OidExpr(table, i);
508 
509       int columnIndex = table.getColumnIndex(columnName);
510 
511       if (columnIndex < 0) {
512         Expr expr = bindParent(tableName, columnName);
513         if (expr != null)
514           return expr;
515 
516         throw new SQLException  (L.l("`{0}' is an unknown column in \n  {1}.",
517                        columnName, _sql));
518       }
519 
520       Column column = table.getColumn(columnName);
521 
522       return new IdExpr(fromItems[i], column);
523     }
524       }
525 
526       Expr expr = bindParent(tableName, columnName);
527       if (expr != null)
528     return expr;
529 
530 
531       throw new SQLException  (L.l("`{0}' is an unknown table.\n{1}",
532                  tableName, getSQL()));
533     }
534   }
535 
536   /**
537    * Binds as a subselect.
538    */
539   private Expr bindParent(String   tableName, String   columnName)
540     throws SQLException  
541   {
542     if (_parent != null) {
543       Expr expr = _parent.bind(tableName, columnName);
544 
545       if (expr != null) {
546     SubSelectParamExpr paramExpr;
547 
548     paramExpr = new SubSelectParamExpr(this, expr, _paramExprs.size());
549     _paramExprs.add(paramExpr);
550 
551     return paramExpr;
552       }
553     }
554 
555     return null;
556   }
557 
558   /**
559    * Clears the paramters.
560    */
561   public void clearParameters()
562   {
563     for (int i = 0; i < _params.length; i++)
564       _params[i].clear();
565   }
566 
567   /**
568    * Sets the indexed parameter as a boolean.
569    */
570   public void setBoolean(int index, boolean value)
571   {
572     _params[index - 1].setBoolean(value);
573   }
574 
575   /**
576    * Sets the indexed parameter as a string.
577    */
578   public void setString(int index, String   value)
579   {
580     _params[index - 1].setString(value);
581   }
582 
583   /**
584    * Sets the indexed parameter as a long.
585    */
586   public void setLong(int index, long value)
587   {
588     _params[index - 1].setLong(value);
589   }
590 
591   /**
592    * Sets the indexed parameter as a double.
593    */
594   public void setDouble(int index, double value)
595   {
596     _params[index - 1].setDouble(value);
597   }
598 
599   /**
600    * Sets the indexed parameter as a date value.
601    */
602   public void setDate(int index, long value)
603   {
604     _params[index - 1].setDate(value);
605   }
606 
607   /**
608    * Sets the indexed parameter as a binary stream
609    */
610   public void setBinaryStream(int index, InputStream   is, int length)
611   {
612     _params[index - 1].setBinaryStream(is, length);
613   }
614 
615   /**
616    * Executes the query.
617    */
618   abstract public void execute(QueryContext queryCtx, Transaction xa)
619     throws SQLException  ;
620 
621   /**
622    * Starts the query.
623    */
624   protected boolean start(TableIterator []rows, int rowLength,
625               QueryContext queryContext, Transaction xa)
626     throws SQLException  
627   {
628     try {
629       Expr []whereExprs = _whereExprs;
630 
631       // Test the constant expression
632       if (whereExprs == null || whereExprs[rowLength] == null) {
633       }
634       else if (! whereExprs[rowLength].isSelect(queryContext)) {
635     return false;
636       }
637 
638       if (rowLength == 0)
639     return true;
640 
641       for (int i = rowLength - 1; i >= 0; i--) {
642     TableIterator row = rows[i];
643     RowIterateExpr iterExpr = _indexExprs[i];
644 
645     if (! iterExpr.init(queryContext, row)) {
646       return false;
647     }
648 
649     // XXX: check to make sure others actually lock this properly
650     //if (! xa.isAutoCommit())
651     //  xa.lockRead(row.getTable().getLock());
652       }
653 
654       return (initBlockRow(rowLength - 1, rows, queryContext)
655           || nextBlock(rowLength - 1, rows, rowLength, queryContext));
656     } catch (IOException   e) {
657       throw new SQLExceptionWrapper(e);
658     }
659   }
660 
661   /**
662    * Returns the next tuple from the query.
663    */
664   protected boolean nextTuple(TableIterator []rows, int rowLength,
665                   QueryContext queryContext, Transaction xa)
666     throws SQLException  
667   {
668     try {
669       if (rowLength == 0)
670     return false;
671 
672       RowIterateExpr []indexExprs = _indexExprs;
673       Expr []whereExprs = _whereExprs;
674 
675       for (int i = 0; i < rowLength; i++) {
676     TableIterator table = rows[i];
677     RowIterateExpr indexExpr = indexExprs[i];
678 
679     Expr whereExpr = whereExprs == null ? null : whereExprs[i];
680 
681     while (indexExpr.nextRow(queryContext, table)) {
682       if (whereExpr == null || whereExpr.isSelect(queryContext)) {
683         if (i == 0 || initBlockRow(i - 1, rows, queryContext)) {
684           return true;
685         }
686       }
687     }
688       }
689 
690       return nextBlock(rowLength - 1, rows, rowLength, queryContext);
691     } catch (IOException   e) {
692       throw new SQLExceptionWrapper(e);
693     }
694   }
695 
696   /**
697    * Initialize this row and all previous rows within this block group.
698    */
699   private boolean nextBlock(int i,
700                 TableIterator []rows,
701                 int rowLength,
702                 QueryContext queryContext)
703     throws IOException  , SQLException  
704   {
705     TableIterator rowIter = rows[i];
706     RowIterateExpr iterExpr = _indexExprs[i];
707 
708     while (true) {
709       if (i > 0 && nextBlock(i - 1, rows, rowLength, queryContext)) {
710     return true;
711       }
712 
713       if (! iterExpr.nextBlock(queryContext, rowIter)) {
714     return false;
715       }
716 
717       if (! iterExpr.allowChildRowShift(queryContext, rows[i]))
718     return false;
719 
720       for (int j = i - 1; j >= 0; j--) {
721     if (! iterExpr.init(queryContext, rows[j]))
722       return false;
723       }
724 
725       if (initBlockRow(rowLength - 1, rows, queryContext))
726     return true;
727     }
728   }
729 
730   /**
731    * Initialize this row and all previous rows within this block group.
732    */
733   private boolean initBlockRow(int i,
734                    TableIterator []rows,
735                    QueryContext queryContext)
736     throws IOException  , SQLException  
737   {
738     RowIterateExpr iterExpr = _indexExprs[i];
739 
740     Expr []whereExprs = _whereExprs;
741     Expr expr = whereExprs == null ? null : whereExprs[i];
742 
743     TableIterator rowIter = rows[i];
744 
745     if (! iterExpr.initRow(queryContext, rowIter)) {
746       return false;
747     }
748 
749     while (expr != null && ! expr.isSelect(queryContext)
750        || i > 0 && ! initBlockRow(i - 1, rows, queryContext)) {
751       if (! iterExpr.nextRow(queryContext, rowIter)) {
752     return false;
753       }
754     }
755 
756     return true;
757   }
758 
759   /**
760    * Frees any blocks for the rows.
761    */
762   protected void freeRows(TableIterator []rows, int rowLength)
763   {
764     for (rowLength--; rowLength >= 0; rowLength--) {
765       if (rows[rowLength] != null)
766     rows[rowLength].free();
767     }
768   }
769 }
770
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags