|
1
5 package org.h2.command.dml;
6
7 import java.sql.SQLException  ;
8 import java.util.HashMap ;
9 import java.util.HashSet ;
10
11 import org.h2.engine.Constants;
12 import org.h2.engine.Session;
13 import org.h2.expression.Comparison;
14 import org.h2.expression.ConditionAndOr;
15 import org.h2.expression.Expression;
16 import org.h2.expression.ExpressionColumn;
17 import org.h2.expression.ExpressionVisitor;
18 import org.h2.expression.ValueExpression;
19 import org.h2.expression.Wildcard;
20 import org.h2.index.Index;
21 import org.h2.message.Message;
22 import org.h2.result.LocalResult;
23 import org.h2.result.SortOrder;
24 import org.h2.table.Column;
25 import org.h2.table.ColumnResolver;
26 import org.h2.table.Table;
27 import org.h2.table.TableFilter;
28 import org.h2.util.ObjectArray;
29 import org.h2.util.StringUtils;
30 import org.h2.util.ValueHashMap;
31 import org.h2.value.Value;
32 import org.h2.value.ValueArray;
33 import org.h2.value.ValueInt;
34 import org.h2.value.ValueNull;
35
36
37
46 public class Select extends Query {
47 private TableFilter topTableFilter;
48 private ObjectArray filters = new ObjectArray();
49 private ObjectArray topFilters = new ObjectArray();
50 private ObjectArray expressions;
51 private Expression having;
52 private Expression condition;
53 private int visibleColumnCount, distinctColumnCount;
54 private ObjectArray orderList;
55 private ObjectArray group;
56 private int[] groupIndex;
57 private boolean[] groupByExpression;
58 private boolean distinct;
59 private HashMap currentGroup;
60 private int havingIndex;
61 private boolean isGroupQuery;
62 private boolean isForUpdate;
63 private double cost;
64 private boolean isQuickQuery;
65 private boolean isPrepared, checkInit;
66 private SortOrder sort;
67
68 public Select(Session session) {
69 super(session);
70 }
71
72 public void addTableFilter(TableFilter filter, boolean isTop) {
73 filters.add(filter);
80 if(isTop) {
81 topFilters.add(filter);
82 }
83 }
84
85 public ObjectArray getTopFilters() {
86 return topFilters;
87 }
88
89 public void setExpressions(ObjectArray expressions) {
90 this.expressions = expressions;
91 }
92
93 public void setGroupQuery() {
94 isGroupQuery = true;
95 }
96
97 public void setGroupBy(ObjectArray group) {
98 this.group = group;
99 }
100
101 public HashMap getCurrentGroup() {
102 return currentGroup;
103 }
104
105 public void setOrder(ObjectArray order) {
106 orderList = order;
107 }
108
109 public void addCondition(Expression cond) {
110 if(condition == null) {
111 condition = cond;
112 } else {
113 condition = new ConditionAndOr(ConditionAndOr.AND, cond, condition);
114 }
115 }
116
117 private void queryGroup(int columnCount, LocalResult result) throws SQLException {
118 ValueHashMap groups = new ValueHashMap(session.getDatabase());
119 int rowNumber = 0;
120 setCurrentRowNumber(0);
121 ValueArray defaultGroup = ValueArray.get(new Value[0]);
122 while (topTableFilter.next()) {
123 checkCancelled();
124 setCurrentRowNumber(rowNumber+1);
125 if (condition == null || Boolean.TRUE.equals(condition.getBooleanValue(session))) {
126 Value key;
127 rowNumber++;
128 if(groupIndex == null) {
129 key = defaultGroup;
130 } else {
131 Value[] keyValues = new Value[groupIndex.length];
132 for (int i = 0; i < groupIndex.length; i++) {
134 int idx = groupIndex[i];
135 Expression expr = (Expression) expressions.get(idx);
136 keyValues[i] = expr.getValue(session);
137 }
138 key = ValueArray.get(keyValues);
139 }
140 HashMap values = (HashMap )groups.get(key);
141 if(values == null) {
142 values = new HashMap ();
143 groups.put(key, values);
144 }
145 currentGroup = values;
146 int len = columnCount;
147 for (int i = 0; i < len; i++) {
148 if(groupByExpression == null || !groupByExpression[i]) {
149 Expression expr = (Expression) expressions.get(i);
150 expr.updateAggregate(session);
151 }
152 }
153 if(sampleSize > 0 && rowNumber >= sampleSize) {
154 break;
155 }
156 }
157 }
158 if(groupIndex == null && groups.size()==0) {
159 groups.put(defaultGroup, new HashMap ());
160 }
161 ObjectArray keys = groups.keys();
162 for (int i=0; i<keys.size(); i++) {
163 ValueArray key = (ValueArray) keys.get(i);
164 currentGroup = (HashMap ) groups.get(key);
165 Value[] keyValues = key.getList();
166 Value[] row = new Value[columnCount];
167 for (int j=0; groupIndex != null && j<groupIndex.length; j++) {
168 row[groupIndex[j]] = keyValues[j];
169 }
170 for (int j = 0; j < columnCount; j++) {
171 if(groupByExpression != null && groupByExpression[j]) {
172 continue;
173 }
174 Expression expr = (Expression) expressions.get(j);
175 row[j] = expr.getValue(session);
176 }
177 if (havingIndex > 0) {
178 Value v = row[havingIndex];
179 if (v == ValueNull.INSTANCE) {
180 continue;
181 }
182 if (!Boolean.TRUE.equals(v.getBoolean())) {
183 continue;
184 }
185 }
186 if(columnCount != distinctColumnCount) {
187 Value[] r2 = new Value[distinctColumnCount];
189 System.arraycopy(row, 0, r2, 0, distinctColumnCount);
190 row = r2;
191 }
192 result.addRow(row);
193 }
194 }
195
196 private Index getSortIndex() throws SQLException {
197 if(sort == null) {
198 return null;
199 }
200 int[] sortTypes = sort.getSortTypes();
201 for(int i=0; i<sortTypes.length; i++) {
202 if((sortTypes[i] & (SortOrder.DESCENDING | SortOrder.NULLS_LAST)) != 0) {
203 return null;
204 }
205 }
206 int[] indexes = sort.getIndexes();
207 ObjectArray sortColumns = new ObjectArray();
208 for(int i=0; i<indexes.length; i++) {
209 int idx = indexes[i];
210 if(idx < 0 || idx >= expressions.size()) {
211 throw Message.getInvalidValueException("order by", ""+idx);
212 }
213 Expression expr = (Expression) expressions.get(idx);
214 expr = expr.getNonAliasExpression();
215 if(expr.isConstant()) {
216 continue;
217 }
218 if(!(expr instanceof ExpressionColumn)) {
219 return null;
220 }
221 Column col = ((ExpressionColumn) expr).getColumn();
222 if(col.getTable() != topTableFilter.getTable()) {
223 return null;
224 }
225 sortColumns.add(col);
226 }
227 Column[] sortCols = new Column[sortColumns.size()];
228 sortColumns.toArray(sortCols);
229 if(sortCols.length == 0) {
230 return topTableFilter.getTable().getScanIndex(session);
232 }
233 ObjectArray list = topTableFilter.getTable().getIndexes();
234 for(int i=0; list != null && i<list.size(); i++) {
235 Index index = (Index) list.get(i);
236 if(index.getCreateSQL() == null) {
237 continue;
239 }
240 if(index.indexType.isHash()) {
241 continue;
242 }
243 Column[] indexCols = index.getColumns();
244 if(indexCols.length < sortCols.length) {
245 continue;
246 }
247 boolean ok = true;
248 for(int j=0; j<sortCols.length; j++) {
249 if(indexCols[j] != sortCols[j]) {
251 ok = false;
252 break;
253 }
254 }
255 if(ok) {
256 return index;
257 }
258 }
259 return null;
260 }
261
262 private void queryFlat(int columnCount, LocalResult result) throws SQLException {
263 int limitRows;
264 if(limit == null) {
265 limitRows = 0;
266 } else {
267 limitRows = limit.getValue(session).getInt();
268 if(offset != null) {
269 limitRows += offset.getValue(session).getInt();
270 }
271 }
272 int rowNumber = 0;
273 setCurrentRowNumber(0);
274 while (topTableFilter.next()) {
275 checkCancelled();
276 setCurrentRowNumber(rowNumber+1);
277 if (condition == null || Boolean.TRUE.equals(condition.getBooleanValue(session))) {
278 Value[] row = new Value[columnCount];
279 for (int i = 0; i < columnCount; i++) {
280 Expression expr = (Expression) expressions.get(i);
281 row[i] = expr.getValue(session);
282 }
283 result.addRow(row);
284 rowNumber++;
285 if(sort == null && limitRows != 0 && result.getRowCount() >= limitRows) {
286 break;
287 }
288 if(sampleSize > 0 && rowNumber >= sampleSize) {
289 break;
290 }
291 }
292 }
293 }
294
295 private void queryQuick(int columnCount, LocalResult result) throws SQLException {
296 Value[] row = new Value[columnCount];
297 for (int i = 0; i < columnCount; i++) {
298 Expression expr = (Expression) expressions.get(i);
299 row[i] = expr.getValue(session);
300 }
301 result.addRow(row);
302 }
303
304 public LocalResult queryWithoutCache(int maxrows) throws SQLException {
305 if(maxrows != 0) {
306 if(limit != null) {
307 maxrows = Math.min(limit.getValue(session).getInt(), maxrows);
308 }
309 limit = ValueExpression.get(ValueInt.get(maxrows));
310 }
311 int columnCount = expressions.size();
312 LocalResult result = new LocalResult(session, expressions, visibleColumnCount);
313 result.setSortOrder(sort);
314 if(distinct) {
315 result.setDistinct();
316 }
317 topTableFilter.startQuery();
318 topTableFilter.reset();
319 topTableFilter.lock(session, isForUpdate);
321 if(isQuickQuery) {
322 queryQuick(columnCount, result);
323 } else if(isGroupQuery) {
324 queryGroup(columnCount, result);
325 } else {
326 queryFlat(columnCount, result);
327 }
328 if(offset != null) {
329 result.setOffset(offset.getValue(session).getInt());
330 }
331 if(limit != null) {
332 result.setLimit(limit.getValue(session).getInt());
333 }
334 result.done();
335 return result;
336 }
337
338 private void expandColumnList() throws SQLException {
339 for (int i = 0; i < expressions.size(); i++) {
341 Expression expr = (Expression) expressions.get(i);
342 if (!expr.isWildcard()) {
343 continue;
344 }
345 String tableAlias = expr.getTableAlias();
346 if (tableAlias == null) {
347 int temp = i;
348 expressions.remove(i);
349 for (int j = 0; j < filters.size(); j++) {
350 TableFilter filter = (TableFilter) filters.get(j);
351 Wildcard c2 = new Wildcard(filter.getTable().getSchema().getName(), filter.getTableAlias());
352 expressions.add(i++, c2);
353 }
354 i = temp - 1;
355 } else {
356 TableFilter filter = null;
357 for (int j = 0; j < filters.size(); j++) {
358 TableFilter f = (TableFilter) filters.get(j);
359 if (tableAlias.equals(f.getTableAlias())) {
360 filter = f;
361 break;
362 }
363 }
364 if (filter == null) {
365 throw Message.getSQLException(Message.TABLE_OR_VIEW_NOT_FOUND_1, tableAlias);
366 }
367 Table t = filter.getTable();
368 String alias = filter.getTableAlias();
369 expressions.remove(i);
370 Column[] columns = t.getColumns();
371 for (int j = 0; j < columns.length; j++) {
372 Column c = columns[j];
373 ExpressionColumn ec = new ExpressionColumn(session.getDatabase(), this, null, alias, c.getName());
374 expressions.add(i++, ec);
375 }
376 i--;
377 }
378 }
379 }
380
381 public void init() throws SQLException {
382 if(Constants.CHECK && checkInit) {
383 throw Message.getInternalError();
384 }
385 checkInit = true;
386 expandColumnList();
387 visibleColumnCount = expressions.size();
388 if(orderList != null) {
389 sort = initOrder(expressions, orderList, visibleColumnCount, distinct);
390 orderList = null;
391 }
392 distinctColumnCount = expressions.size();
393
394 if(having != null) {
395 expressions.add(having);
396 havingIndex = expressions.size()-1;
397 having = null;
398 } else {
399 havingIndex = -1;
400 }
401
402 if(group != null) {
404 groupIndex = new int[group.size()];
405 ObjectArray expressionSQL = new ObjectArray();
406 for(int i=0; i<expressions.size(); i++) {
407 Expression expr = (Expression) expressions.get(i);
408 expr = expr.getNonAliasExpression();
409 String sql = expr.getSQL();
410 expressionSQL.add(sql);
411 }
412 for(int i=0; i<group.size(); i++) {
413 Expression expr = (Expression) group.get(i);
414 String sql = expr.getSQL();
415 int found = -1;
416 for(int j=0; j<expressionSQL.size(); j++) {
417 String s2 = (String ) expressionSQL.get(j);
418 if(s2.equals(sql)) {
419 found = j;
420 break;
421 }
422 }
423 if(found < 0) {
424 int index = expressions.size();
425 groupIndex[i] = index;
426 expressions.add(expr);
427 } else {
428 groupIndex[i] = found;
429 }
430 }
431 groupByExpression = new boolean[expressions.size()];
432 for(int i=0; i<groupIndex.length; i++) {
433 groupByExpression[groupIndex[i]] = true;
434 }
435 group = null;
436 }
437 for (int i = 0; i < filters.size(); i++) {
439 TableFilter f = (TableFilter) filters.get(i);
440 for (int j = 0; j < expressions.size(); j++) {
441 Expression expr = (Expression) expressions.get(j);
442 expr.mapColumns(f, 0);
443 }
444 if (condition != null) {
445 condition.mapColumns(f, 0);
446 }
447 }
448 }
449
450 public void prepare() throws SQLException {
451 if(isPrepared) {
452 return;
454 }
455 if(Constants.CHECK && !checkInit) {
456 throw Message.getInternalError("already prepared");
457 }
458 isPrepared = true;
459 for(int i=0; i<expressions.size(); i++) {
460 Expression e = (Expression) expressions.get(i);
461 expressions.set(i, e.optimize(session));
462 }
463 if(condition != null) {
464 condition = condition.optimize(session);
465 for (int j = 0; j < filters.size(); j++) {
466 TableFilter f = (TableFilter) filters.get(j);
467 condition.createIndexConditions(f);
468 }
469 }
470 if(condition == null && isGroupQuery && groupIndex == null && havingIndex<0 && filters.size()==1) {
471 ExpressionVisitor optimizable = ExpressionVisitor.get(ExpressionVisitor.OPTIMIZABLE_MIN_MAX_COUNT_ALL);
472 optimizable.table = ((TableFilter)filters.get(0)).getTable();
473 isQuickQuery = isEverything(optimizable);
474 }
475 cost = preparePlan();
476 if(sort != null && !isQuickQuery && !isGroupQuery) {
477 Index index = getSortIndex();
478 Index current = topTableFilter.getIndex();
479 if(index != null && (current.indexType.isScan() || current == index)) {
480 topTableFilter.setIndex(index);
481 sort = null;
482 }
483 }
484 }
485
486 public double getCost() {
487 return cost;
488 }
489
490 public HashSet getTables() {
491 HashSet set = new HashSet ();
492 for(int i=0; i<filters.size(); i++) {
493 TableFilter filter = (TableFilter) filters.get(i);
494 set.add(filter.getTable());
495 }
496 return set;
497 }
498
499 private double preparePlan() throws SQLException {
500
501 TableFilter[] topArray = new TableFilter[topFilters.size()];
502 topFilters.toArray(topArray);
503 for(int i=0; i<topArray.length; i++) {
504 topArray[i].setFullCondition(condition);
505 }
506
507 Optimizer optimizer = new Optimizer(topArray, condition, session);
508 optimizer.optimize();
509 topTableFilter = optimizer.getTopFilter();
510 double cost = optimizer.getCost();
511
512 TableFilter f = topTableFilter;
513 while(f != null) {
514 f.setEvaluatable(f, true);
515 if(condition != null) {
516 condition.setEvaluatable(f, true);
517 }
518 Expression on = f.getJoinCondition();
519 if(on != null) {
520 if(!on.isEverything(ExpressionVisitor.EVALUATABLE)) {
521 f.removeJoinCondition();
522 addCondition(on);
523 }
524 }
525 on = f.getFilterCondition();
526 if(on != null) {
527 if(!on.isEverything(ExpressionVisitor.EVALUATABLE)) {
528 f.removeFilterCondition();
529 addCondition(on);
530 }
531 }
532 f = f.getJoin();
533 }
534
535 topTableFilter.prepare();
536 return cost;
537 }
538
539 public String getPlan() {
540 StringBuffer buff = new StringBuffer ();
541 Expression[] exprList = new Expression[expressions.size()];
542 expressions.toArray(exprList);
543 buff.append("SELECT ");
544 if(distinct) {
545 buff.append("DISTINCT ");
546 }
547 for(int i=0; i<visibleColumnCount; i++) {
548 if(i>0) {
549 buff.append(", ");
550 }
551 Expression expr = exprList[i];
552 buff.append(StringUtils.unEnclose(expr.getSQL()));
553 }
554 buff.append("\nFROM ");
555 TableFilter filter = topTableFilter;
556 boolean join = false;
557 int id=0;
558 do {
559 if(id > 0) {
560 buff.append('\n');
561 }
562 id++;
563 buff.append(filter.getPlanSQL(join));
564 join = true;
565 filter = filter.getJoin();
566 } while(filter != null);
567 if(condition != null) {
568 buff.append("\nWHERE " + StringUtils.unEnclose(condition.getSQL()));
569 }
570 if(groupIndex != null) {
571 buff.append("\nGROUP BY ");
572 for(int i=0; i<groupIndex.length; i++) {
573 Expression gro = exprList[groupIndex[i]];
574 if(i>0) {
575 buff.append(", ");
576 }
577 buff.append(StringUtils.unEnclose(gro.getSQL()));
578 }
579 }
580 if(havingIndex >= 0) {
581 Expression hav = exprList[havingIndex];
582 buff.append("\nHAVING " + StringUtils.unEnclose(hav.getSQL()));
583 }
584 if(sort != null) {
585 buff.append("\nORDER BY ");
586 buff.append(sort.getSQL(exprList, visibleColumnCount));
587 }
588 if(limit != null) {
589 buff.append("\nLIMIT ");
590 buff.append(StringUtils.unEnclose(limit.getSQL()));
591 if(offset != null) {
592 buff.append(" OFFSET ");
593 buff.append(StringUtils.unEnclose(offset.getSQL()));
594 }
595 }
596 if(isForUpdate) {
597 buff.append("\nFOR UPDATE");
598 }
599 return buff.toString();
600 }
601
602 public void setDistinct(boolean b) {
603 distinct = b;
604 }
605
606 public void setHaving(Expression having) {
607 this.having = having;
608 }
609
610 public int getColumnCount() {
611 return visibleColumnCount;
612 }
613
614 public TableFilter getTopTableFilter() {
615 return topTableFilter;
616 }
617
618 public ObjectArray getExpressions() {
619 return expressions;
620 }
621
622 public Expression getCondition() {
623 return condition;
624 }
625
626 public boolean isDistinct() {
627 return distinct;
628 }
629
630 public ObjectArray getGroupBy() {
631 return group;
632 }
633
634 public void setForUpdate(boolean b) {
635 this.isForUpdate = b;
636 }
637
638 public void mapColumns(ColumnResolver resolver, int level) throws SQLException {
639 for(int i=0; i<expressions.size(); i++) {
640 Expression e = (Expression) expressions.get(i);
641 e.mapColumns(resolver, level);
642 }
643 if(condition != null) {
644 condition.mapColumns(resolver, level);
645 }
646 }
647
648 public void setEvaluatable(TableFilter tableFilter, boolean b) {
649 for(int i=0; i<expressions.size(); i++) {
650 Expression e = (Expression) expressions.get(i);
651 e.setEvaluatable(tableFilter, b);
652 }
653 if(condition != null) {
654 condition.setEvaluatable(tableFilter, b);
655 }
656 }
657
658 public boolean isQuickQuery() {
659 return isQuickQuery;
660 }
661
662 public void addGlobalCondition(Expression expr, int columnId, int comparisonType) throws SQLException {
663 Expression col = (Expression)expressions.get(columnId);
664 Expression comp = new Comparison(session, comparisonType, col, expr);
665 comp = comp.optimize(session);
666 if(isGroupQuery) {
667 if(having == null) {
668 having = comp;
669 } else {
670 having = new ConditionAndOr(ConditionAndOr.AND, having, comp);
671 }
672 } else {
673 if(condition == null) {
674 condition = comp;
675 } else {
676 condition = new ConditionAndOr(ConditionAndOr.AND, condition, comp);
677 }
678 }
679 }
680
681 public boolean isEverything(ExpressionVisitor visitor) {
682 if(visitor.type == ExpressionVisitor.SET_MAX_DATA_MODIFICATION_ID) {
683 for(int i=0; i<filters.size(); i++) {
684 TableFilter f = (TableFilter) filters.get(i);
685 long m = f.getTable().getMaxDataModificationId();
686 visitor.addDataModificationId(m);
687 }
688 }
689 if(visitor.type == ExpressionVisitor.EVALUATABLE) {
690 if(!Constants.OPTIMIZE_EVALUATABLE_SUBQUERIES) {
691 return false;
692 }
693 }
694 if(visitor.type != ExpressionVisitor.EVALUATABLE) {
695 visitor.queryLevel(1);
696 }
697 boolean result = true;
698 for(int i=0; i<expressions.size(); i++) {
699 Expression e = (Expression) expressions.get(i);
700 if(!e.isEverything(visitor)) {
701 result = false;
702 break;
703 }
704 }
705 if(result && condition != null && !condition.isEverything(visitor)) {
706 result = false;
707 }
708 if(result && having != null && !having.isEverything(visitor)) {
709 result = false;
710 }
711 if(visitor.type != ExpressionVisitor.EVALUATABLE) {
712 visitor.queryLevel(-1);
713 }
714 return result;
715 }
716
717 public boolean isReadOnly() {
718 return isEverything(ExpressionVisitor.READONLY);
719 }
720
721 }
722 |
Popular Tags |
Java API By Example, From Geeks To Geeks.