METAMODEL-1165: Fixed - added default_table alias table
[metamodel.git] / core / src / main / java / org / apache / metamodel / MetaModelHelper.java
1 /**
2 * Licensed to the Apache Software Foundation (ASF) under one
3 * or more contributor license agreements. See the NOTICE file
4 * distributed with this work for additional information
5 * regarding copyright ownership. The ASF licenses this file
6 * to you under the Apache License, Version 2.0 (the
7 * "License"); you may not use this file except in compliance
8 * with the License. You may obtain a copy of the License at
9 *
10 * http://www.apache.org/licenses/LICENSE-2.0
11 *
12 * Unless required by applicable law or agreed to in writing,
13 * software distributed under the License is distributed on an
14 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15 * KIND, either express or implied. See the License for the
16 * specific language governing permissions and limitations
17 * under the License.
18 */
19 package org.apache.metamodel;
20
21 import java.util.ArrayList;
22 import java.util.Arrays;
23 import java.util.Collection;
24 import java.util.Collections;
25 import java.util.Comparator;
26 import java.util.HashMap;
27 import java.util.HashSet;
28 import java.util.Iterator;
29 import java.util.List;
30 import java.util.Map;
31 import java.util.Map.Entry;
32 import java.util.Set;
33 import java.util.stream.Collectors;
34 import java.util.stream.Stream;
35
36 import org.apache.metamodel.data.CachingDataSetHeader;
37 import org.apache.metamodel.data.DataSet;
38 import org.apache.metamodel.data.DataSetHeader;
39 import org.apache.metamodel.data.DefaultRow;
40 import org.apache.metamodel.data.EmptyDataSet;
41 import org.apache.metamodel.data.FilteredDataSet;
42 import org.apache.metamodel.data.FirstRowDataSet;
43 import org.apache.metamodel.data.IRowFilter;
44 import org.apache.metamodel.data.InMemoryDataSet;
45 import org.apache.metamodel.data.MaxRowsDataSet;
46 import org.apache.metamodel.data.Row;
47 import org.apache.metamodel.data.ScalarFunctionDataSet;
48 import org.apache.metamodel.data.SimpleDataSetHeader;
49 import org.apache.metamodel.data.SubSelectionDataSet;
50 import org.apache.metamodel.query.FilterItem;
51 import org.apache.metamodel.query.FromItem;
52 import org.apache.metamodel.query.GroupByItem;
53 import org.apache.metamodel.query.OrderByItem;
54 import org.apache.metamodel.query.Query;
55 import org.apache.metamodel.query.ScalarFunction;
56 import org.apache.metamodel.query.SelectItem;
57 import org.apache.metamodel.query.parser.QueryParser;
58 import org.apache.metamodel.schema.Column;
59 import org.apache.metamodel.schema.ColumnType;
60 import org.apache.metamodel.schema.Schema;
61 import org.apache.metamodel.schema.SuperColumnType;
62 import org.apache.metamodel.schema.Table;
63 import org.apache.metamodel.schema.WrappingSchema;
64 import org.apache.metamodel.schema.WrappingTable;
65 import org.apache.metamodel.util.AggregateBuilder;
66 import org.apache.metamodel.util.CollectionUtils;
67 import org.apache.metamodel.util.ObjectComparator;
68 import org.slf4j.Logger;
69 import org.slf4j.LoggerFactory;
70
71 /**
72 * This class contains various helper functionality to common tasks in MetaModel, eg.:
73 *
74 * <ul>
75 * <li>Easy-access for traversing common schema items</li>
76 * <li>Manipulate data in memory. These methods are primarily used to enable queries for non-queryable data sources like
77 * CSV files and spreadsheets.</li>
78 * <li>Query rewriting, traversing and manipulation.</li>
79 * </ul>
80 *
81 * The class is mainly intended for internal use within the framework operations, but is kept stable, so it can also be
82 * used by framework users.
83 */
84 public final class MetaModelHelper {
85
86 private final static Logger logger = LoggerFactory.getLogger(MetaModelHelper.class);
87
88 private MetaModelHelper() {
89 // Prevent instantiation
90 }
91
92 /**
93 * Creates an array of tables where all occurences of tables in the provided list of tables and columns are included
94 */
95 public static Table[] getTables(Collection<Table> tableList, Iterable<Column> columnList) {
96 HashSet<Table> set = new HashSet<Table>();
97 set.addAll(tableList);
98 for (Column column : columnList) {
99 set.add(column.getTable());
100 }
101 return set.toArray(new Table[set.size()]);
102 }
103
104 /**
105 * Determines if a schema is an information schema
106 *
107 * @param schema
108 * @return
109 */
110 public static boolean isInformationSchema(Schema schema) {
111 String name = schema.getName();
112 return isInformationSchema(name);
113 }
114
115 /**
116 * Determines if a schema name is the name of an information schema
117 *
118 * @param name
119 * @return
120 */
121 public static boolean isInformationSchema(String name) {
122 if (name == null) {
123 return false;
124 }
125 return QueryPostprocessDataContext.INFORMATION_SCHEMA_NAME.equals(name.toLowerCase());
126 }
127
128 /**
129 * Converts a list of columns to a corresponding array of tables
130 *
131 * @param columns the columns that the tables will be extracted from
132 * @return an array containing the tables of the provided columns.
133 */
134 public static Table[] getTables(Iterable<Column> columns) {
135 ArrayList<Table> result = new ArrayList<Table>();
136 for (Column column : columns) {
137 Table table = column.getTable();
138 if (!result.contains(table)) {
139 result.add(table);
140 }
141 }
142 return result.toArray(new Table[result.size()]);
143 }
144
145 /**
146 * Creates a subset array of columns, where only columns that are contained within the specified table are included.
147 *
148 * @param table
149 * @param columns
150 * @return an array containing the columns that exist in the table
151 */
152 public static Column[] getTableColumns(Table table, Iterable<Column> columns) {
153 if (table == null) {
154 return new Column[0];
155 }
156 final List<Column> result = new ArrayList<Column>();
157 for (Column column : columns) {
158 final boolean sameTable = table.equals(column.getTable());
159 if (sameTable) {
160 result.add(column);
161 }
162 }
163 return result.toArray(new Column[result.size()]);
164 }
165
166 /**
167 * Creates a subset array of columns, where only columns that are contained within the specified table are included.
168 *
169 * @param table
170 * @param columns
171 * @return an array containing the columns that exist in the table
172 */
173 public static Column[] getTableColumns(Table table, Column[] columns) {
174 return getTableColumns(table, Arrays.asList(columns));
175 }
176
177 public static DataSet getCarthesianProduct(DataSet... fromDataSets) {
178 return getCarthesianProduct(fromDataSets, new FilterItem[0]);
179 }
180
181 public static DataSet getCarthesianProduct(DataSet[] fromDataSets, FilterItem... filterItems) {
182 return getCarthesianProduct(fromDataSets, Arrays.asList(filterItems));
183 }
184
185 public static DataSet getCarthesianProduct(DataSet[] fromDataSets, Iterable<FilterItem> whereItems) {
186 assert (fromDataSets.length > 0);
187 // First check if carthesian product is even nescesary
188 if (fromDataSets.length == 1) {
189 return getFiltered(fromDataSets[0], whereItems);
190 }
191 // do a nested loop join, no matter what
192 Iterator<DataSet> dsIter = Arrays.asList(fromDataSets).iterator();
193
194 DataSet joined = dsIter.next();
195
196 while (dsIter.hasNext()) {
197 joined = nestedLoopJoin(dsIter.next(), joined, (whereItems));
198
199 }
200
201 return joined;
202
203 }
204
205 /**
206 * Executes a simple nested loop join. The innerLoopDs will be copied in an in-memory dataset.
207 *
208 */
209 public static InMemoryDataSet nestedLoopJoin(DataSet innerLoopDs, DataSet outerLoopDs,
210 Iterable<FilterItem> filtersIterable) {
211
212 List<FilterItem> filters = new ArrayList<>();
213 for (FilterItem fi : filtersIterable) {
214 filters.add(fi);
215 }
216 List<Row> innerRows = innerLoopDs.toRows();
217
218 List<SelectItem> allItems = new ArrayList<>(outerLoopDs.getSelectItems());
219 allItems.addAll(innerLoopDs.getSelectItems());
220
221 Set<FilterItem> applicableFilters = applicableFilters(filters, allItems);
222
223 DataSetHeader jointHeader = new CachingDataSetHeader(allItems);
224
225 List<Row> resultRows = new ArrayList<>();
226 for (Row outerRow : outerLoopDs) {
227 for (Row innerRow : innerRows) {
228
229 Object[] joinedRowObjects = new Object[outerRow.getValues().length + innerRow.getValues().length];
230
231 System.arraycopy(outerRow.getValues(), 0, joinedRowObjects, 0, outerRow.getValues().length);
232 System.arraycopy(innerRow.getValues(), 0, joinedRowObjects, outerRow.getValues().length,
233 innerRow.getValues().length);
234
235 Row joinedRow = new DefaultRow(jointHeader, joinedRowObjects);
236
237 if (applicableFilters.isEmpty() || applicableFilters.stream().allMatch(fi -> fi.accept(joinedRow))) {
238 resultRows.add(joinedRow);
239 }
240 }
241 }
242
243 return new InMemoryDataSet(jointHeader, resultRows);
244 }
245
246 /**
247 * Filters the FilterItems such that only the FilterItems are returned, which contain SelectItems that are contained
248 * in selectItemList
249 *
250 * @param filters
251 * @param selectItemList
252 * @return
253 */
254 private static Set<FilterItem> applicableFilters(Collection<FilterItem> filters,
255 Collection<SelectItem> selectItemList) {
256
257 Set<SelectItem> items = new HashSet<SelectItem>(selectItemList);
258
259 return filters.stream().filter(fi -> {
260 Collection<SelectItem> fiSelectItems = new ArrayList<>();
261 fiSelectItems.add(fi.getSelectItem());
262 Object operand = fi.getOperand();
263 if (operand instanceof SelectItem) {
264 fiSelectItems.add((SelectItem) operand);
265 }
266
267 return items.containsAll(fiSelectItems);
268
269 }).collect(Collectors.toSet());
270 }
271
272 public static DataSet getFiltered(DataSet dataSet, Iterable<FilterItem> filterItems) {
273 List<IRowFilter> filters = CollectionUtils.map(filterItems, filterItem -> {
274 return filterItem;
275 });
276 if (filters.isEmpty()) {
277 return dataSet;
278 }
279
280 return new FilteredDataSet(dataSet, filters.toArray(new IRowFilter[filters.size()]));
281 }
282
283 public static DataSet getFiltered(DataSet dataSet, FilterItem... filterItems) {
284 return getFiltered(dataSet, Arrays.asList(filterItems));
285 }
286
287 public static DataSet getSelection(final List<SelectItem> selectItems, final DataSet dataSet) {
288 final List<SelectItem> dataSetSelectItems = dataSet.getSelectItems();
289
290 // check if the selection is already the same
291 if (selectItems.equals(dataSetSelectItems)) {
292 // return the DataSet unmodified
293 return dataSet;
294 }
295
296 final List<SelectItem> scalarFunctionSelectItemsToEvaluate = new ArrayList<>();
297
298 for (SelectItem selectItem : selectItems) {
299 if (selectItem.getScalarFunction() != null) {
300 if (!dataSetSelectItems.contains(selectItem)
301 && dataSetSelectItems.contains(selectItem.replaceFunction(null))) {
302 scalarFunctionSelectItemsToEvaluate.add(selectItem);
303 }
304 }
305 }
306
307 if (scalarFunctionSelectItemsToEvaluate.isEmpty()) {
308 return new SubSelectionDataSet(selectItems, dataSet);
309 }
310
311 final ScalarFunctionDataSet scalaFunctionDataSet =
312 new ScalarFunctionDataSet(scalarFunctionSelectItemsToEvaluate, dataSet);
313 return new SubSelectionDataSet(selectItems, scalaFunctionDataSet);
314 }
315
316 public static DataSet getSelection(SelectItem[] selectItems, DataSet dataSet) {
317 return getSelection(Arrays.asList(selectItems), dataSet);
318 }
319
320 public static DataSet getGrouped(List<SelectItem> selectItems, DataSet dataSet, Collection<GroupByItem> groupByItems) {
321 DataSet result = dataSet;
322 if (groupByItems != null && groupByItems.size() > 0) {
323 Map<Row, Map<SelectItem, List<Object>>> uniqueRows = new HashMap<Row, Map<SelectItem, List<Object>>>();
324
325 final List<SelectItem> groupBySelects =
326 groupByItems.stream().map(gbi -> gbi.getSelectItem()).collect(Collectors.toList());
327 final DataSetHeader groupByHeader = new CachingDataSetHeader(groupBySelects);
328
329 // Creates a list of SelectItems that have aggregate functions
330 List<SelectItem> functionItems = getAggregateFunctionSelectItems(selectItems);
331
332 // Loop through the dataset and identify groups
333 while (dataSet.next()) {
334 Row row = dataSet.getRow();
335
336 // Subselect a row prototype with only the unique values that
337 // define the group
338 Row uniqueRow = row.getSubSelection(groupByHeader);
339
340 // function input is the values used for calculating aggregate
341 // functions in the group
342 Map<SelectItem, List<Object>> functionInput;
343 if (!uniqueRows.containsKey(uniqueRow)) {
344 // If this group already exist, use an existing function
345 // input
346 functionInput = new HashMap<SelectItem, List<Object>>();
347 for (SelectItem item : functionItems) {
348 functionInput.put(item, new ArrayList<Object>());
349 }
350 uniqueRows.put(uniqueRow, functionInput);
351 } else {
352 // If this is a new group, create a new function input
353 functionInput = uniqueRows.get(uniqueRow);
354 }
355
356 // Loop through aggregate functions to check for validity
357 for (SelectItem item : functionItems) {
358 List<Object> objects = functionInput.get(item);
359 Column column = item.getColumn();
360 if (column != null) {
361 Object value = row.getValue(new SelectItem(column));
362 objects.add(value);
363 } else if (SelectItem.isCountAllItem(item)) {
364 // Just use the empty string, since COUNT(*) don't
365 // evaluate values (but null values should be prevented)
366 objects.add("");
367 } else {
368 throw new IllegalArgumentException("Expression function not supported: " + item);
369 }
370 }
371 }
372
373 dataSet.close();
374 final List<Row> resultData = new ArrayList<Row>();
375 final DataSetHeader resultHeader = new CachingDataSetHeader(selectItems);
376
377 // Loop through the groups to generate aggregates
378 for (Entry<Row, Map<SelectItem, List<Object>>> entry : uniqueRows.entrySet()) {
379 Row row = entry.getKey();
380 Map<SelectItem, List<Object>> functionInput = entry.getValue();
381 Object[] resultRow = new Object[selectItems.size()];
382 // Loop through select items to generate a row
383 int i = 0;
384 for (SelectItem item : selectItems) {
385 int uniqueRowIndex = row.indexOf(item);
386 if (uniqueRowIndex != -1) {
387 // If there's already a value for the select item in the
388 // row, keep it (it's one of the grouped by columns)
389 resultRow[i] = row.getValue(uniqueRowIndex);
390 } else {
391 // Use the function input to calculate the aggregate
392 // value
393 List<Object> objects = functionInput.get(item);
394 if (objects != null) {
395 Object functionResult = item.getAggregateFunction().evaluate(objects.toArray());
396 resultRow[i] = functionResult;
397 } else {
398 if (item.getAggregateFunction() != null) {
399 logger.error("No function input found for SelectItem: {}", item);
400 }
401 }
402 }
403 i++;
404 }
405 resultData.add(new DefaultRow(resultHeader, resultRow, null));
406 }
407
408 if (resultData.isEmpty()) {
409 result = new EmptyDataSet(selectItems);
410 } else {
411 result = new InMemoryDataSet(resultHeader, resultData);
412 }
413 }
414 result = getSelection(selectItems, result);
415 return result;
416 }
417
418 /**
419 * Applies aggregate values to a dataset. This method is to be invoked AFTER any filters have been applied.
420 *
421 * @param workSelectItems all select items included in the processing of the query (including those originating from
422 * other clauses than the SELECT clause).
423 * @param dataSet
424 * @return
425 */
426 public static DataSet getAggregated(List<SelectItem> workSelectItems, DataSet dataSet) {
427 final List<SelectItem> functionItems = getAggregateFunctionSelectItems(workSelectItems);
428 if (functionItems.isEmpty()) {
429 return dataSet;
430 }
431
432 final Map<SelectItem, AggregateBuilder<?>> aggregateBuilders = new HashMap<SelectItem, AggregateBuilder<?>>();
433 for (SelectItem item : functionItems) {
434 aggregateBuilders.put(item, item.getAggregateFunction().createAggregateBuilder());
435 }
436
437 final DataSetHeader header;
438 final boolean onlyAggregates;
439 if (functionItems.size() != workSelectItems.size()) {
440 onlyAggregates = false;
441 header = new CachingDataSetHeader(workSelectItems);
442 } else {
443 onlyAggregates = true;
444 header = new SimpleDataSetHeader(workSelectItems);
445 }
446
447 final List<Row> resultRows = new ArrayList<Row>();
448 while (dataSet.next()) {
449 final Row inputRow = dataSet.getRow();
450 for (SelectItem item : functionItems) {
451 final AggregateBuilder<?> aggregateBuilder = aggregateBuilders.get(item);
452 final Column column = item.getColumn();
453 if (column != null) {
454 Object value = inputRow.getValue(new SelectItem(column));
455 aggregateBuilder.add(value);
456 } else if (SelectItem.isCountAllItem(item)) {
457 // Just use the empty string, since COUNT(*) don't
458 // evaluate values (but null values should be prevented)
459 aggregateBuilder.add("");
460 } else {
461 throw new IllegalArgumentException("Expression function not supported: " + item);
462 }
463 }
464
465 // If the result should also contain non-aggregated values, we
466 // will keep those in the rows list
467 if (!onlyAggregates) {
468 final Object[] values = new Object[header.size()];
469 for (int i = 0; i < header.size(); i++) {
470 final Object value = inputRow.getValue(header.getSelectItem(i));
471 if (value != null) {
472 values[i] = value;
473 }
474 }
475 resultRows.add(new DefaultRow(header, values));
476 }
477 }
478 dataSet.close();
479
480 // Collect the aggregates
481 Map<SelectItem, Object> functionResult = new HashMap<SelectItem, Object>();
482 for (SelectItem item : functionItems) {
483 AggregateBuilder<?> aggregateBuilder = aggregateBuilders.get(item);
484 Object result = aggregateBuilder.getAggregate();
485 functionResult.put(item, result);
486 }
487
488 // if there are no result rows (no matching records at all), we still
489 // need to return a record with the aggregates
490 final boolean noResultRows = resultRows.isEmpty();
491
492 if (onlyAggregates || noResultRows) {
493 // We will only create a single row with all the aggregates
494 Object[] values = new Object[header.size()];
495 for (int i = 0; i < header.size(); i++) {
496 values[i] = functionResult.get(header.getSelectItem(i));
497 }
498 Row row = new DefaultRow(header, values);
499 resultRows.add(row);
500 } else {
501 // We will create the aggregates as well as regular values
502 for (int i = 0; i < resultRows.size(); i++) {
503 Row row = resultRows.get(i);
504 Object[] values = row.getValues();
505 for (Entry<SelectItem, Object> entry : functionResult.entrySet()) {
506 SelectItem item = entry.getKey();
507 int itemIndex = row.indexOf(item);
508 if (itemIndex != -1) {
509 Object value = entry.getValue();
510 values[itemIndex] = value;
511 }
512 }
513 resultRows.set(i, new DefaultRow(header, values));
514 }
515 }
516
517 return new InMemoryDataSet(header, resultRows);
518 }
519
520 public static List<SelectItem> getAggregateFunctionSelectItems(Iterable<SelectItem> selectItems) {
521 return CollectionUtils.filter(selectItems, arg -> {
522 return arg.getAggregateFunction() != null;
523 });
524 }
525
526 public static List<SelectItem> getScalarFunctionSelectItems(Iterable<SelectItem> selectItems) {
527 return CollectionUtils.filter(selectItems, arg -> {
528 return arg.getScalarFunction() != null;
529 });
530 }
531
532 public static DataSet getOrdered(DataSet dataSet, List<OrderByItem> orderByItems) {
533 return getOrdered(dataSet, orderByItems.toArray(new OrderByItem[orderByItems.size()]));
534 }
535
536 public static DataSet getOrdered(DataSet dataSet, final OrderByItem... orderByItems) {
537 if (orderByItems != null && orderByItems.length != 0) {
538 final int[] sortIndexes = new int[orderByItems.length];
539 for (int i = 0; i < orderByItems.length; i++) {
540 OrderByItem item = orderByItems[i];
541 int indexOf = dataSet.indexOf(item.getSelectItem());
542 sortIndexes[i] = indexOf;
543 }
544
545 final List<Row> data = readDataSetFull(dataSet);
546 if (data.isEmpty()) {
547 return new EmptyDataSet(dataSet.getSelectItems());
548 }
549
550 final Comparator<Object> valueComparator = ObjectComparator.getComparator();
551
552 // create a comparator for doing the actual sorting/ordering
553 final Comparator<Row> comparator = new Comparator<Row>() {
554 public int compare(Row o1, Row o2) {
555 for (int i = 0; i < sortIndexes.length; i++) {
556 int sortIndex = sortIndexes[i];
557 Object sortObj1 = o1.getValue(sortIndex);
558 Object sortObj2 = o2.getValue(sortIndex);
559 int compare = valueComparator.compare(sortObj1, sortObj2);
560 if (compare != 0) {
561 OrderByItem orderByItem = orderByItems[i];
562 boolean ascending = orderByItem.isAscending();
563 if (ascending) {
564 return compare;
565 } else {
566 return compare * -1;
567 }
568 }
569 }
570 return 0;
571 }
572 };
573
574 Collections.sort(data, comparator);
575
576 dataSet = new InMemoryDataSet(data);
577 }
578 return dataSet;
579 }
580
581 public static List<Row> readDataSetFull(DataSet dataSet) {
582 final List<Row> result;
583 if (dataSet instanceof InMemoryDataSet) {
584 // if dataset is an in memory dataset we have a shortcut to avoid
585 // creating a new list
586 result = ((InMemoryDataSet) dataSet).getRows();
587 } else {
588 result = new ArrayList<Row>();
589 while (dataSet.next()) {
590 result.add(dataSet.getRow());
591 }
592 }
593 dataSet.close();
594 return result;
595 }
596
597 /**
598 * Examines a query and extracts an array of FromItem's that refer (directly) to tables (hence Joined FromItems and
599 * SubQuery FromItems are traversed but not included).
600 *
601 * @param q the query to examine
602 * @return an array of FromItem's that refer directly to tables
603 */
604 public static FromItem[] getTableFromItems(Query q) {
605 List<FromItem> result = new ArrayList<FromItem>();
606 List<FromItem> items = q.getFromClause().getItems();
607 for (FromItem item : items) {
608 result.addAll(getTableFromItems(item));
609 }
610 return result.toArray(new FromItem[result.size()]);
611 }
612
613 public static List<FromItem> getTableFromItems(FromItem item) {
614 List<FromItem> result = new ArrayList<FromItem>();
615 if (item.getTable() != null) {
616 result.add(item);
617 } else if (item.getSubQuery() != null) {
618 FromItem[] sqItems = getTableFromItems(item.getSubQuery());
619 for (int i = 0; i < sqItems.length; i++) {
620 result.add(sqItems[i]);
621 }
622 } else if (item.getJoin() != null) {
623 FromItem leftSide = item.getLeftSide();
624 result.addAll(getTableFromItems(leftSide));
625 FromItem rightSide = item.getRightSide();
626 result.addAll(getTableFromItems(rightSide));
627 } else {
628 throw new IllegalStateException("FromItem was neither of Table type, SubQuery type or Join type: " + item);
629 }
630 return result;
631 }
632
633 /**
634 * Executes a single row query, like "SELECT COUNT(*), MAX(SOME_COLUMN) FROM MY_TABLE" or similar.
635 *
636 * @param dataContext the DataContext object to use for executing the query
637 * @param query the query to execute
638 * @return a row object representing the single row returned from the query
639 * @throws MetaModelException if less or more than one Row is returned from the query
640 */
641 public static Row executeSingleRowQuery(DataContext dataContext, Query query) throws MetaModelException {
642 DataSet dataSet = dataContext.executeQuery(query);
643 boolean next = dataSet.next();
644 if (!next) {
645 throw new MetaModelException("No rows returned from query: " + query);
646 }
647 Row row = dataSet.getRow();
648 next = dataSet.next();
649 if (next) {
650 throw new MetaModelException("More than one row returned from query: " + query);
651 }
652 dataSet.close();
653 return row;
654 }
655
656 /**
657 * Performs a left join (aka left outer join) operation on two datasets.
658 *
659 * @param ds1 the left dataset
660 * @param ds2 the right dataset
661 * @param onConditions the conditions to join by
662 * @return the left joined result dataset
663 */
664 public static DataSet getLeftJoin(DataSet ds1, DataSet ds2, FilterItem[] onConditions) {
665 if (ds1 == null) {
666 throw new IllegalArgumentException("Left DataSet cannot be null");
667 }
668 if (ds2 == null) {
669 throw new IllegalArgumentException("Right DataSet cannot be null");
670 }
671 List<SelectItem> si1 = ds1.getSelectItems();
672 List<SelectItem> si2 = ds2.getSelectItems();
673 List<SelectItem> selectItems = Stream.concat(si1.stream(), si2.stream()).collect(Collectors.toList());
674 List<Row> resultRows = new ArrayList<Row>();
675 List<Row> ds2data = readDataSetFull(ds2);
676 if (ds2data.isEmpty()) {
677 // no need to join, simply return a new view (with null values) on
678 // the previous dataset.
679 return getSelection(selectItems, ds1);
680 }
681
682 final DataSetHeader header = new CachingDataSetHeader(selectItems);
683
684 while (ds1.next()) {
685
686 // Construct a single-row dataset for making a carthesian product
687 // against ds2
688 Row ds1row = ds1.getRow();
689 List<Row> ds1rows = new ArrayList<Row>();
690 ds1rows.add(ds1row);
691
692 DataSet carthesianProduct =
693 getCarthesianProduct(new DataSet[] { new InMemoryDataSet(new CachingDataSetHeader(si1), ds1rows),
694 new InMemoryDataSet(new CachingDataSetHeader(si2), ds2data) }, onConditions);
695 List<Row> carthesianRows = readDataSetFull(carthesianProduct);
696 if (carthesianRows.size() > 0) {
697 resultRows.addAll(carthesianRows);
698 } else {
699 Object[] values = ds1row.getValues();
700 Object[] row = new Object[selectItems.size()];
701 System.arraycopy(values, 0, row, 0, values.length);
702 resultRows.add(new DefaultRow(header, row));
703 }
704 }
705 ds1.close();
706
707 if (resultRows.isEmpty()) {
708 return new EmptyDataSet(selectItems);
709 }
710
711 return new InMemoryDataSet(header, resultRows);
712 }
713
714 /**
715 * Performs a right join (aka right outer join) operation on two datasets.
716 *
717 * @param ds1 the left dataset
718 * @param ds2 the right dataset
719 * @param onConditions the conditions to join by
720 * @return the right joined result dataset
721 */
722 public static DataSet getRightJoin(DataSet ds1, DataSet ds2, FilterItem[] onConditions) {
723 List<SelectItem> ds1selects = ds1.getSelectItems();
724 List<SelectItem> ds2selects = ds2.getSelectItems();
725 List<SelectItem> leftOrderedSelects = new ArrayList<>();
726 leftOrderedSelects.addAll(ds1selects);
727 leftOrderedSelects.addAll(ds2selects);
728
729 // We will reuse the left join algorithm (but switch the datasets
730 // around)
731 DataSet dataSet = getLeftJoin(ds2, ds1, onConditions);
732
733 dataSet = getSelection(leftOrderedSelects, dataSet);
734 return dataSet;
735 }
736
737 public static SelectItem[] createSelectItems(Column... columns) {
738 SelectItem[] items = new SelectItem[columns.length];
739 for (int i = 0; i < items.length; i++) {
740 items[i] = new SelectItem(columns[i]);
741 }
742 return items;
743 }
744
745 public static DataSet getDistinct(DataSet dataSet) {
746 List<SelectItem> selectItems = dataSet.getSelectItems();
747 List<GroupByItem> groupByItems = selectItems.stream().map(GroupByItem::new).collect(Collectors.toList());
748
749 return getGrouped(selectItems, dataSet, groupByItems);
750 }
751
752 public static Table[] getTables(Column[] columns) {
753 return getTables(Arrays.asList(columns));
754 }
755
756 public static Column[] getColumnsByType(Column[] columns, final ColumnType columnType) {
757 return CollectionUtils.filter(columns, column -> {
758 return column.getType() == columnType;
759 }).toArray(new Column[0]);
760 }
761
762 public static Column[] getColumnsBySuperType(Column[] columns, final SuperColumnType superColumnType) {
763 return CollectionUtils.filter(columns, column -> {
764 return column.getType().getSuperType() == superColumnType;
765 }).toArray(new Column[0]);
766 }
767
768 public static Query parseQuery(DataContext dc, String queryString) {
769 final QueryParser parser = new QueryParser(dc, queryString);
770 return parser.parse();
771 }
772
773 public static DataSet getPaged(DataSet dataSet, int firstRow, int maxRows) {
774 if (firstRow > 1) {
775 dataSet = new FirstRowDataSet(dataSet, firstRow);
776 }
777 if (maxRows != -1) {
778 dataSet = new MaxRowsDataSet(dataSet, maxRows);
779 }
780 return dataSet;
781 }
782
783 public static List<SelectItem> getEvaluatedSelectItems(final List<FilterItem> items) {
784 final List<SelectItem> result = new ArrayList<SelectItem>();
785 for (FilterItem item : items) {
786 addEvaluatedSelectItems(result, item);
787 }
788 return result;
789 }
790
791 private static void addEvaluatedSelectItems(List<SelectItem> result, FilterItem item) {
792 final FilterItem[] orItems = item.getChildItems();
793 if (orItems != null) {
794 for (FilterItem filterItem : orItems) {
795 addEvaluatedSelectItems(result, filterItem);
796 }
797 }
798 final SelectItem selectItem = item.getSelectItem();
799 if (selectItem != null && !result.contains(selectItem)) {
800 result.add(selectItem);
801 }
802 final Object operand = item.getOperand();
803 if (operand != null && operand instanceof SelectItem && !result.contains(operand)) {
804 result.add((SelectItem) operand);
805 }
806 }
807
808 /**
809 * This method returns the select item of the given alias name.
810 *
811 * @param query
812 * @return
813 */
814 public static SelectItem getSelectItemByAlias(Query query, String alias) {
815 List<SelectItem> selectItems = query.getSelectClause().getItems();
816 for (SelectItem selectItem : selectItems) {
817 if (selectItem.getAlias() != null && selectItem.getAlias().equals(alias)) {
818 return selectItem;
819 }
820 }
821 return null;
822 }
823
824 /**
825 * Determines if a query contains {@link ScalarFunction}s in any clause of the query EXCEPT for the SELECT clause.
826 * This is a handy thing to determine because decorating with {@link ScalarFunctionDataSet} only gives you
827 * select-item evaluation so if the rest of the query is pushed to an underlying datastore, then it may create
828 * issues.
829 *
830 * @param query
831 * @return
832 */
833 public static boolean containsNonSelectScalaFunctions(Query query) {
834 // check FROM clause
835 final List<FromItem> fromItems = query.getFromClause().getItems();
836 for (FromItem fromItem : fromItems) {
837 // check sub-queries
838 final Query subQuery = fromItem.getSubQuery();
839 if (subQuery != null) {
840 if (containsNonSelectScalaFunctions(subQuery)) {
841 return true;
842 }
843 if (!getScalarFunctionSelectItems(subQuery.getSelectClause().getItems()).isEmpty()) {
844 return true;
845 }
846 }
847 }
848
849 // check WHERE clause
850 if (!getScalarFunctionSelectItems(query.getWhereClause().getEvaluatedSelectItems()).isEmpty()) {
851 return true;
852 }
853
854 // check GROUP BY clause
855 if (!getScalarFunctionSelectItems(query.getGroupByClause().getEvaluatedSelectItems()).isEmpty()) {
856 return true;
857 }
858
859 // check HAVING clause
860 if (!getScalarFunctionSelectItems(query.getHavingClause().getEvaluatedSelectItems()).isEmpty()) {
861 return true;
862 }
863
864 // check ORDER BY clause
865 if (!getScalarFunctionSelectItems(query.getOrderByClause().getEvaluatedSelectItems()).isEmpty()) {
866 return true;
867 }
868
869 return false;
870 }
871
872 public static Table resolveTable(FromItem fromItem) {
873 final Table table = fromItem.getTable();
874 return resolveUnderlyingTable(table);
875 }
876
877 public static Table resolveUnderlyingTable(Table table) {
878 while (table instanceof WrappingTable) {
879 table = ((WrappingTable) table).getWrappedTable();
880 }
881 return table;
882 }
883
884 public static Schema resolveUnderlyingSchema(Schema schema) {
885 while (schema instanceof WrappingSchema) {
886 schema = ((WrappingSchema) schema).getWrappedSchema();
887 }
888 return schema;
889 }
890 }