Java tutorial
/* * Copyright 2014 Red Hat, Inc. and/or its affiliates. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.dashbuilder.dataprovider.sql; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.commons.lang3.StringUtils; import org.dashbuilder.DataSetCore; import org.dashbuilder.dataprovider.DataSetProvider; import org.dashbuilder.dataprovider.DataSetProviderType; import org.dashbuilder.dataprovider.StaticDataSetProvider; import org.dashbuilder.dataprovider.sql.dialect.Dialect; import org.dashbuilder.dataprovider.sql.model.Column; import org.dashbuilder.dataprovider.sql.model.Condition; import org.dashbuilder.dataprovider.sql.model.Select; import org.dashbuilder.dataprovider.sql.model.SortColumn; import org.dashbuilder.dataprovider.sql.model.Table; import org.dashbuilder.dataset.ColumnType; import org.dashbuilder.dataset.DataColumn; import org.dashbuilder.dataset.DataSet; import org.dashbuilder.dataset.DataSetFactory; import org.dashbuilder.dataset.DataSetLookup; import org.dashbuilder.dataset.DataSetMetadata; import org.dashbuilder.dataset.DataSetOp; import org.dashbuilder.dataset.DataSetOpEngine; import org.dashbuilder.dataset.IntervalBuilderDynamicDate; import org.dashbuilder.dataset.date.DateUtils; import org.dashbuilder.dataset.date.TimeFrame; import org.dashbuilder.dataset.def.DataColumnDef; import org.dashbuilder.dataset.def.DataSetDef; import org.dashbuilder.dataset.def.DataSetDefRegistry; import org.dashbuilder.dataset.def.DataSetDefRegistryListener; import org.dashbuilder.dataset.def.SQLDataSetDef; import org.dashbuilder.dataset.engine.group.IntervalBuilder; import org.dashbuilder.dataset.engine.group.IntervalBuilderLocator; import org.dashbuilder.dataset.engine.group.IntervalList; import org.dashbuilder.dataset.filter.ColumnFilter; import org.dashbuilder.dataset.filter.CoreFunctionFilter; import org.dashbuilder.dataset.filter.CoreFunctionType; import org.dashbuilder.dataset.filter.DataSetFilter; import org.dashbuilder.dataset.filter.FilterFactory; import org.dashbuilder.dataset.filter.LogicalExprFilter; import org.dashbuilder.dataset.filter.LogicalExprType; import org.dashbuilder.dataset.group.AggregateFunctionType; import org.dashbuilder.dataset.group.ColumnGroup; import org.dashbuilder.dataset.group.DataSetGroup; import org.dashbuilder.dataset.group.DateIntervalType; import org.dashbuilder.dataset.group.GroupFunction; import org.dashbuilder.dataset.group.GroupStrategy; import org.dashbuilder.dataset.group.Interval; import org.dashbuilder.dataset.impl.DataColumnImpl; import org.dashbuilder.dataset.impl.DataSetMetadataImpl; import org.dashbuilder.dataset.impl.MemSizeEstimator; import org.dashbuilder.dataset.sort.ColumnSort; import org.dashbuilder.dataset.sort.DataSetSort; import org.dashbuilder.dataset.sort.SortOrder; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * DataSetProvider implementation for JDBC-compliant data sources. * * <p>The SQL provider resolves every data set lookup request by transforming such request into the proper SQL query. * In some cases, an extra processing of the resulting data is required since some lookup requests do not map directly * into the SQL world. In such cases, specially the grouping of date based data, the core data set operation engine is * used.</p> * * <p> * Pending stuff: * - Filter on foreign data sets * - Group (fixed) by date of week * </p> */ public class SQLDataSetProvider implements DataSetProvider, DataSetDefRegistryListener { private static SQLDataSetProvider SINGLETON = null; public static SQLDataSetProvider get() { if (SINGLETON == null) { DataSetCore dataSetCore = DataSetCore.get(); StaticDataSetProvider staticDataSetProvider = dataSetCore.getStaticDataSetProvider(); DataSetDefRegistry dataSetDefRegistry = dataSetCore.getDataSetDefRegistry(); DataSetOpEngine dataSetOpEngine = dataSetCore.getSharedDataSetOpEngine(); IntervalBuilderLocator intervalBuilderLocator = dataSetCore.getIntervalBuilderLocator(); IntervalBuilderDynamicDate intervalBuilderDynamicDate = dataSetCore.getIntervalBuilderDynamicDate(); SINGLETON = new SQLDataSetProvider(staticDataSetProvider, new SQLDataSourceLocatorImpl(), intervalBuilderLocator, intervalBuilderDynamicDate, dataSetOpEngine); dataSetDefRegistry.addListener(SINGLETON); } return SINGLETON; } protected Logger log = LoggerFactory.getLogger(SQLDataSetProvider.class); protected StaticDataSetProvider staticDataSetProvider; protected SQLDataSourceLocator dataSourceLocator; protected IntervalBuilderLocator intervalBuilderLocator; protected IntervalBuilderDynamicDate intervalBuilderDynamicDate; protected DataSetOpEngine opEngine; public SQLDataSetProvider() { } public SQLDataSetProvider(StaticDataSetProvider staticDataSetProvider, SQLDataSourceLocator dataSourceLocator, IntervalBuilderLocator intervalBuilderLocator, IntervalBuilderDynamicDate intervalBuilderDynamicDate, DataSetOpEngine opEngine) { this.staticDataSetProvider = staticDataSetProvider; this.dataSourceLocator = dataSourceLocator; this.intervalBuilderLocator = intervalBuilderLocator; this.intervalBuilderDynamicDate = intervalBuilderDynamicDate; this.opEngine = opEngine; } public StaticDataSetProvider getStaticDataSetProvider() { return staticDataSetProvider; } public void setStaticDataSetProvider(StaticDataSetProvider staticDataSetProvider) { this.staticDataSetProvider = staticDataSetProvider; } public SQLDataSourceLocator getDataSourceLocator() { return dataSourceLocator; } public void setDataSourceLocator(SQLDataSourceLocator dataSourceLocator) { this.dataSourceLocator = dataSourceLocator; } public IntervalBuilderLocator getIntervalBuilderLocator() { return intervalBuilderLocator; } public void setIntervalBuilderLocator(IntervalBuilderLocator intervalBuilderLocator) { this.intervalBuilderLocator = intervalBuilderLocator; } public IntervalBuilderDynamicDate getIntervalBuilderDynamicDate() { return intervalBuilderDynamicDate; } public void setIntervalBuilderDynamicDate(IntervalBuilderDynamicDate intervalBuilderDynamicDate) { this.intervalBuilderDynamicDate = intervalBuilderDynamicDate; } public DataSetOpEngine getOpEngine() { return opEngine; } public void setOpEngine(DataSetOpEngine opEngine) { this.opEngine = opEngine; } public DataSetProviderType getType() { return DataSetProviderType.SQL; } public DataSet lookupDataSet(DataSetDef def, DataSetLookup lookup) throws Exception { SQLDataSetDef sqlDef = (SQLDataSetDef) def; if (StringUtils.isBlank(sqlDef.getDataSource())) { throw new IllegalArgumentException("Missing data source in SQL data set definition: " + sqlDef); } if (StringUtils.isBlank(sqlDef.getDbSQL()) && StringUtils.isBlank(sqlDef.getDbTable())) { throw new IllegalArgumentException("Missing DB table or SQL in the data set definition: " + sqlDef); } // Look first into the static data set provider cache. if (sqlDef.isCacheEnabled()) { DataSet dataSet = staticDataSetProvider.lookupDataSet(def.getUUID(), null); if (dataSet != null) { // Lookup from cache. return staticDataSetProvider.lookupDataSet(def.getUUID(), lookup); } else { // Fetch always from database if existing rows are greater than the cache max. rows DataSetMetadata metadata = getDataSetMetadata(def); int rows = metadata.getNumberOfRows(); if (rows > sqlDef.getCacheMaxRows()) { return _lookupDataSet(sqlDef, lookup); } // Fetch from database and register into the static cache. Further requests will lookup from cache. dataSet = _lookupDataSet(sqlDef, null); dataSet.setUUID(def.getUUID()); dataSet.setDefinition(def); staticDataSetProvider.registerDataSet(dataSet); return staticDataSetProvider.lookupDataSet(def.getUUID(), lookup); } } // If cache is disabled then always fetch from database. return _lookupDataSet(sqlDef, lookup); } public boolean isDataSetOutdated(DataSetDef def) { // Non fetched data sets can't get outdated. MetadataHolder last = _metadataMap.remove(def.getUUID()); if (last == null) return false; // Check if the metadata has changed since the last time it was fetched. try { DataSetMetadata current = getDataSetMetadata(def); return !current.equals(last.metadata); } catch (Exception e) { log.error("Error fetching metadata: " + def, e); return false; } } public DataSetMetadata getDataSetMetadata(DataSetDef def) throws Exception { SQLDataSetDef sqlDef = (SQLDataSetDef) def; DataSource ds = dataSourceLocator.lookup(sqlDef); Connection conn = ds.getConnection(); try { return _getDataSetMetadata(sqlDef, conn, true); } finally { conn.close(); } } // Listen to changes on the data set definition registry @Override public void onDataSetDefStale(DataSetDef def) { if (DataSetProviderType.SQL.equals(def.getProvider())) { staticDataSetProvider.removeDataSet(def.getUUID()); } } @Override public void onDataSetDefModified(DataSetDef olDef, DataSetDef newDef) { if (DataSetProviderType.SQL.equals(olDef.getProvider())) { String uuid = olDef.getUUID(); _metadataMap.remove(uuid); staticDataSetProvider.removeDataSet(uuid); } } @Override public void onDataSetDefRemoved(DataSetDef oldDef) { if (DataSetProviderType.SQL.equals(oldDef.getProvider())) { String uuid = oldDef.getUUID(); _metadataMap.remove(uuid); staticDataSetProvider.removeDataSet(uuid); } } @Override public void onDataSetDefRegistered(DataSetDef newDef) { } // Internal implementation logic protected class MetadataHolder { DataSetMetadataImpl metadata; List<Column> columns; } protected transient Map<String, MetadataHolder> _metadataMap = new HashMap<String, MetadataHolder>(); protected Column _getDbColumn(Collection<Column> dbColumns, String columnId) { for (Column dbColumn : dbColumns) { if (dbColumn.getName().equalsIgnoreCase(columnId)) { return dbColumn; } } return null; } protected DataSetMetadata _getDataSetMetadata(SQLDataSetDef def, Connection conn, boolean skipCache) throws Exception { // Check the cache if (!skipCache) { MetadataHolder result = _metadataMap.get(def.getUUID()); if (result != null) { return result.metadata; } } // Fetch the DB columns from the table or sql List<Column> dbColumns = _getColumns(def, conn); List<String> targetDbColumnIds = new ArrayList<String>(); List<ColumnType> targetDbColumnTypes = new ArrayList<ColumnType>(); List<Integer> targetDbColumnsLength = new ArrayList<Integer>(); // Check the definition columns match those in the DB if (def.getColumns() != null) { for (DataColumnDef column : def.getColumns()) { Column dbColumn = _getDbColumn(dbColumns, column.getId()); if (dbColumn == null) { throw new IllegalArgumentException( "The DataSetDef's column does not exist in DB: " + column.getId()); } targetDbColumnIds.add(dbColumn.getName()); targetDbColumnTypes.add(column.getColumnType()); targetDbColumnsLength.add(dbColumn.getLength()); } } // Add or skip non-existing columns depending on the data set definition. for (Column dbColumn : dbColumns) { String dbColumnId = dbColumn.getName(); int columnIdx = targetDbColumnIds.indexOf(dbColumnId); boolean columnExists = columnIdx != -1; if (!columnExists) { // Add any table column if (def.isAllColumnsEnabled()) { targetDbColumnIds.add(dbColumnId); targetDbColumnTypes.add(dbColumn.getType()); targetDbColumnsLength.add(dbColumn.getLength()); } // Skip non existing columns else { continue; } } } // Ensure the column set is valid if (targetDbColumnIds.isEmpty()) { throw new IllegalArgumentException("No data set columns found: " + def); } // Creates a brand new metadata holder instance MetadataHolder result = new MetadataHolder(); result.columns = dbColumns; result.metadata = new DataSetMetadataImpl(def, def.getUUID(), 0, targetDbColumnIds.size(), targetDbColumnIds, targetDbColumnTypes, 0); // Calculate the estimated size int rowCount = _getRowCount(result.metadata, def, conn); int estimatedSize = 0; for (int i = 0; i < targetDbColumnIds.size(); i++) { ColumnType cType = targetDbColumnTypes.get(i); if (ColumnType.DATE.equals(cType)) { estimatedSize += MemSizeEstimator.sizeOf(Date.class) * rowCount; } else if (ColumnType.NUMBER.equals(cType)) { estimatedSize += MemSizeEstimator.sizeOf(Double.class) * rowCount; } else { int length = targetDbColumnsLength.get(i); estimatedSize += length / 2 * rowCount; } } // Update the metadata result.metadata.setNumberOfRows(rowCount); result.metadata.setEstimatedSize(estimatedSize); // Store in the cache if (!skipCache) { _metadataMap.put(def.getUUID(), result); } else if (log.isDebugEnabled()) { log.debug("Using look-up in test mode. Skipping adding data set metadata for uuid [" + def.getUUID() + "] into cache."); } return result.metadata; } protected List<Column> _getColumns(SQLDataSetDef def, Connection conn) throws Exception { Dialect dialect = JDBCUtils.dialect(conn); if (!StringUtils.isBlank(def.getDbSQL())) { Select query = SQLFactory.select(conn).from(def.getDbSQL()).limit(1); return JDBCUtils.getColumns(logSQL(query).fetch(), dialect.getExcludedColumns()); } else { Select query = SQLFactory.select(conn).from(_createTable(def)).limit(1); return JDBCUtils.getColumns(logSQL(query).fetch(), dialect.getExcludedColumns()); } } protected int _getRowCount(DataSetMetadata metadata, SQLDataSetDef def, Connection conn) throws Exception { // Count rows, either on an SQL or a DB table Select _query = SQLFactory.select(conn); _appendFrom(def, _query); // Filters set must be taken into account DataSetFilter filterOp = def.getDataSetFilter(); if (filterOp != null) { List<ColumnFilter> filterList = filterOp.getColumnFilterList(); for (ColumnFilter filter : filterList) { _appendFilterBy(metadata, def, filter, _query); } } return _query.fetchCount(); } protected DataSet _lookupDataSet(SQLDataSetDef def, DataSetLookup lookup) throws Exception { LookupProcessor processor = new LookupProcessor(def, lookup); return processor.run(); } protected Table _createTable(SQLDataSetDef def) { if (StringUtils.isBlank(def.getDbSchema())) return SQLFactory.table(def.getDbTable()); else return SQLFactory.table(def.getDbSchema(), def.getDbTable()); } protected void _appendFrom(SQLDataSetDef def, Select _query) { if (!StringUtils.isBlank(def.getDbSQL())) _query.from(def.getDbSQL()); else _query.from(_createTable(def)); } protected void _appendFilterBy(DataSetMetadata metadata, SQLDataSetDef def, DataSetFilter filterOp, Select _query) { List<ColumnFilter> filterList = filterOp.getColumnFilterList(); for (ColumnFilter filter : filterList) { _appendFilterBy(metadata, def, filter, _query); } } protected void _appendFilterBy(DataSetMetadata metadata, SQLDataSetDef def, ColumnFilter filter, Select _query) { Condition condition = _createCondition(metadata, def, filter); if (condition != null) { _query.where(condition); } } protected Condition _createCondition(DataSetMetadata metadata, SQLDataSetDef def, ColumnFilter filter) { if (filter instanceof CoreFunctionFilter) { String filterId = _columnFromMetadata(metadata, filter.getColumnId()); Column _column = SQLFactory.column(filterId); CoreFunctionFilter f = (CoreFunctionFilter) filter; CoreFunctionType type = f.getType(); List params = f.getParameters(); if (CoreFunctionType.IS_NULL.equals(type)) { return _column.isNull(); } if (CoreFunctionType.NOT_NULL.equals(type)) { return _column.notNull(); } if (CoreFunctionType.EQUALS_TO.equals(type)) { if (params.isEmpty()) { return null; } if (params.size() == 1) { return _column.equalsTo(params.get(0)); } return _column.in(params); } if (CoreFunctionType.NOT_EQUALS_TO.equals(type)) { if (params.isEmpty()) { return null; } if (params.size() == 1) { return _column.notEquals(params.get(0)); } return _column.in(params).not(); } if (CoreFunctionType.LIKE_TO.equals(type)) { String pattern = (String) params.get(0); boolean caseSensitive = params.size() < 2 || Boolean.parseBoolean(params.get(1).toString()); if (caseSensitive) { return _column.like(pattern); } else { return _column.lower().like(pattern.toLowerCase()); } } if (CoreFunctionType.LOWER_THAN.equals(type)) { return _column.lowerThan(params.get(0)); } if (CoreFunctionType.LOWER_OR_EQUALS_TO.equals(type)) { return _column.lowerOrEquals(params.get(0)); } if (CoreFunctionType.GREATER_THAN.equals(type)) { return _column.greaterThan(params.get(0)); } if (CoreFunctionType.GREATER_OR_EQUALS_TO.equals(type)) { return _column.greaterOrEquals(params.get(0)); } if (CoreFunctionType.BETWEEN.equals(type)) { Object low = params.get(0); Object high = params.get(1); if (low == null && high == null) { return null; } if (low != null && high == null) { return _column.greaterOrEquals(low); } if (low == null && high != null) { return _column.lowerOrEquals(high); } return _column.between(low, high); } if (CoreFunctionType.TIME_FRAME.equals(type)) { TimeFrame timeFrame = TimeFrame.parse(params.get(0).toString()); if (timeFrame != null) { java.sql.Date past = new java.sql.Date(timeFrame.getFrom().getTimeInstant().getTime()); java.sql.Date future = new java.sql.Date(timeFrame.getTo().getTimeInstant().getTime()); return _column.between(past, future); } } if (CoreFunctionType.IN.equals(type) && params instanceof List) { if (params.isEmpty()) { return null; } return _column.inSql((List<?>) params); } if (CoreFunctionType.NOT_IN.equals(type) && params instanceof List) { if (params.isEmpty()) { return null; } return _column.notInSql((List<?>) params); } } if (filter instanceof LogicalExprFilter) { LogicalExprFilter f = (LogicalExprFilter) filter; LogicalExprType type = f.getLogicalOperator(); Condition condition = null; List<ColumnFilter> logicalTerms = f.getLogicalTerms(); for (int i = 0; i < logicalTerms.size(); i++) { Condition next = _createCondition(metadata, def, logicalTerms.get(i)); if (LogicalExprType.AND.equals(type)) { if (condition == null) condition = next; else condition = condition.and(next); } if (LogicalExprType.OR.equals(type)) { if (condition == null) condition = next; else condition = condition.or(next); } if (LogicalExprType.NOT.equals(type)) { if (condition == null) condition = next.not(); else condition = condition.and(next.not()); } } return condition; } throw new IllegalArgumentException("Filter not supported: " + filter); } protected String _columnFromMetadata(DataSetMetadata metadata, String columnId) { int idx = _assertColumnExists(metadata, columnId); return metadata.getColumnId(idx); } protected int _assertColumnExists(DataSetMetadata metadata, String columnId) { for (int i = 0; i < metadata.getNumberOfColumns(); i++) { if (metadata.getColumnId(i).equalsIgnoreCase(columnId)) { return i; } } throw new RuntimeException("Column '" + columnId + "' not found in data set: " + metadata.getUUID()); } public Select logSQL(Select q) { String sql = q.getSQL(); log.debug(sql); return q; } /** * Class that provides an isolated context for the processing of a single lookup request. */ private class LookupProcessor { private static final String NOT_SUPPORTED = "' not supported"; SQLDataSetDef def; DataSetLookup lookup; DataSetMetadata metadata; Select _query; Connection conn; Date[] dateLimits; DateIntervalType dateIntervalType; List<DataSetOp> postProcessingOps = new ArrayList<DataSetOp>(); public LookupProcessor(SQLDataSetDef def, DataSetLookup lookup) { this.def = def; this.lookup = lookup; DataSetFilter dataSetFilter = def.getDataSetFilter(); if (dataSetFilter != null) { if (lookup == null) { this.lookup = new DataSetLookup(def.getUUID(), dataSetFilter); } else { this.lookup.addOperation(dataSetFilter); } } } public DataSet run() throws Exception { DataSource ds = dataSourceLocator.lookup(def); conn = ds.getConnection(); try { metadata = _getDataSetMetadata(def, conn, lookup.testMode()); int totalRows = metadata.getNumberOfRows(); boolean trim = (lookup != null && (lookup.getNumberOfRows() > 0 || lookup.getRowOffset() > 0)); // The whole data set if (lookup == null || lookup.getOperationList().isEmpty()) { // Prepare the select _query = SQLFactory.select(conn).columns(_createAllColumns()); _appendFrom(def, _query); // Row limits if (trim) { totalRows = _query.fetchCount(); _query.limit(lookup.getNumberOfRows()).offset(lookup.getRowOffset()); } // Fetch the results and build the data set ResultSet _results = logSQL(_query).fetch(); List<DataColumn> columns = calculateColumns(null); DataSet dataSet = _buildDataSet(columns, _results); if (trim) { dataSet.setRowCountNonTrimmed(totalRows); } return dataSet; } // ... or a list of operations. else { DataSetGroup groupOp = null; int groupIdx = lookup.getFirstGroupOpIndex(0, null, false); if (groupIdx != -1) groupOp = lookup.getOperation(groupIdx); // Prepare the select _query = SQLFactory.select(conn).columns(_createColumns(groupOp)); _appendFrom(def, _query); // Append the filter clauses for (DataSetFilter filterOp : lookup.getOperationList(DataSetFilter.class)) { _appendFilterBy(metadata, def, filterOp, _query); } // Append the interval selections List<DataSetGroup> intervalSelects = lookup.getFirstGroupOpSelections(); for (DataSetGroup intervalSelect : intervalSelects) { _appendIntervalSelection(intervalSelect, _query); } // ... the group by clauses ColumnGroup cg = null; if (groupOp != null) { cg = groupOp.getColumnGroup(); if (cg != null) { _appendGroupBy(groupOp); } } // ... the sort clauses DataSetSort sortOp = lookup.getFirstSortOp(); if (sortOp != null) { if (cg != null) { _appendOrderGroupBy(groupOp, sortOp); } else { _appendOrderBy(sortOp); } } else if (cg != null) { _appendOrderGroupBy(groupOp); } // ... and the row limits if (trim) { totalRows = _query.fetchCount(); _query.limit(lookup.getNumberOfRows()).offset(lookup.getRowOffset()); } // Fetch the results and build the data set ResultSet _results = logSQL(_query).fetch(); List<DataColumn> columns = calculateColumns(groupOp); DataSet dataSet = _buildDataSet(columns, _results); if (trim) dataSet.setRowCountNonTrimmed(totalRows); return dataSet; } } finally { conn.close(); } } protected DateIntervalType calculateDateInterval(ColumnGroup cg) { if (dateIntervalType != null) { return dateIntervalType; } if (GroupStrategy.DYNAMIC.equals(cg.getStrategy())) { Date[] limits = calculateDateLimits(cg.getSourceId()); if (limits != null) { dateIntervalType = intervalBuilderDynamicDate.calculateIntervalSize(limits[0], limits[1], cg); return dateIntervalType; } } dateIntervalType = DateIntervalType.getByName(cg.getIntervalSize()); return dateIntervalType; } protected Date[] calculateDateLimits(String dateColumnId) { if (dateLimits != null) { return dateLimits; } Date minDate = calculateDateLimit(dateColumnId, true); Date maxDate = calculateDateLimit(dateColumnId, false); return dateLimits = new Date[] { minDate, maxDate }; } protected Date calculateDateLimit(String dateColumnId, boolean min) { String dbColumnId = _columnFromMetadata(metadata, dateColumnId); Column _dateColumn = SQLFactory.column(dbColumnId); Select _limitsQuery = SQLFactory.select(conn).columns(_dateColumn); _appendFrom(def, _limitsQuery); // Append the filter clauses for (DataSetFilter filterOp : lookup.getOperationList(DataSetFilter.class)) { _appendFilterBy(metadata, def, filterOp, _limitsQuery); } // Append group interval selection filters List<DataSetGroup> intervalSelects = lookup.getFirstGroupOpSelections(); for (DataSetGroup intervalSelect : intervalSelects) { _appendIntervalSelection(intervalSelect, _limitsQuery); } try { // Fetch the date ResultSet rs = logSQL(_limitsQuery.where(_dateColumn.notNull()) .orderBy(min ? _dateColumn.asc() : _dateColumn.desc()).limit(1)).fetch(); if (!rs.next()) { return null; } else { return rs.getDate(1); } } catch (SQLException e) { log.error("Error reading date limit from query results", e); return null; } } protected List<DataColumn> calculateColumns(DataSetGroup gOp) { List<DataColumn> result = new ArrayList<DataColumn>(); if (gOp == null) { for (int i = 0; i < metadata.getNumberOfColumns(); i++) { String columnId = metadata.getColumnId(i); ColumnType columnType = metadata.getColumnType(i); DataColumn column = new DataColumnImpl(columnId, columnType); result.add(column); } } else { ColumnGroup cg = gOp.getColumnGroup(); for (GroupFunction gf : gOp.getGroupFunctions()) { String sourceId = gf.getSourceId(); String columnId = _getTargetColumnId(gf); DataColumnImpl column = new DataColumnImpl(); column.setId(columnId); column.setGroupFunction(gf); result.add(column); // Group column if (cg != null && cg.getSourceId().equals(sourceId) && gf.getFunction() == null) { column.setColumnType(ColumnType.LABEL); column.setColumnGroup(cg); if (ColumnType.DATE.equals(metadata.getColumnType(sourceId))) { column.setIntervalType(dateIntervalType != null ? dateIntervalType.toString() : null); column.setMinValue(dateLimits != null ? dateLimits[0] : null); column.setMaxValue(dateLimits != null ? dateLimits[1] : null); } } // Aggregated column else if (gf.getFunction() != null) { column.setColumnType(ColumnType.NUMBER); } // Existing Column else { column.setColumnType(metadata.getColumnType(sourceId)); } } } return result; } protected void _appendOrderBy(DataSetSort sortOp) { List<SortColumn> _columns = new ArrayList<SortColumn>(); List<ColumnSort> sortList = sortOp.getColumnSortList(); for (ColumnSort columnSort : sortList) { String dbColumnId = _columnFromMetadata(metadata, columnSort.getColumnId()); if (SortOrder.DESCENDING.equals(columnSort.getOrder())) { _columns.add(SQLFactory.column(dbColumnId).desc()); } else { _columns.add(SQLFactory.column(dbColumnId).asc()); } } _query.orderBy(_columns); } protected boolean isDynamicDateGroup(DataSetGroup groupOp) { ColumnGroup cg = groupOp.getColumnGroup(); if (!ColumnType.DATE.equals(metadata.getColumnType(cg.getSourceId()))) { return false; } if (!GroupStrategy.DYNAMIC.equals(cg.getStrategy())) { return false; } return true; } protected void _appendOrderGroupBy(DataSetGroup groupOp) { if (isDynamicDateGroup(groupOp)) { ColumnGroup cg = groupOp.getColumnGroup(); _query.orderBy(_createColumn(cg).asc()); // If the group column is in the resulting data set then ensure the data set order GroupFunction gf = groupOp.getGroupFunction(cg.getSourceId()); if (gf != null) { DataSetSort sortOp = new DataSetSort(); String targetId = _getTargetColumnId(gf); sortOp.addSortColumn(new ColumnSort(targetId, SortOrder.ASCENDING)); postProcessingOps.add(sortOp); } } } protected void _appendOrderGroupBy(DataSetGroup groupOp, DataSetSort sortOp) { List<SortColumn> _columns = new ArrayList<SortColumn>(); List<ColumnSort> sortList = sortOp.getColumnSortList(); ColumnGroup cg = groupOp.getColumnGroup(); for (ColumnSort cs : sortList) { GroupFunction gf = groupOp.getGroupFunction(cs.getColumnId()); // Sort by the group column if (cg.getSourceId().equals(cs.getColumnId()) || cg.getColumnId().equals(cs.getColumnId())) { if (SortOrder.DESCENDING.equals(cs.getOrder())) { _columns.add(_createColumn(cg).desc()); if (isDynamicDateGroup(groupOp)) { postProcessingOps.add(sortOp); } } else { _columns.add(_createColumn(cg).asc()); if (isDynamicDateGroup(groupOp)) { postProcessingOps.add(sortOp); } } } // Sort by an aggregation else if (gf != null) { // In SQL, sort is only permitted for columns belonging to the result set. if (SortOrder.DESCENDING.equals(cs.getOrder())) { _columns.add(_createColumn(gf).desc()); } else { _columns.add(_createColumn(gf).asc()); } } } _query.orderBy(_columns); } protected void _appendIntervalSelection(DataSetGroup intervalSel, Select _query) { if (intervalSel != null && intervalSel.isSelect()) { ColumnGroup cg = intervalSel.getColumnGroup(); List<Interval> intervalList = intervalSel.getSelectedIntervalList(); // Get the filter values List<Comparable> names = new ArrayList<Comparable>(); Comparable min = null; Comparable max = null; for (Interval interval : intervalList) { names.add(interval.getName()); Comparable intervalMin = (Comparable) interval.getMinValue(); Comparable intervalMax = (Comparable) interval.getMaxValue(); if (intervalMin != null) { if (min == null) min = intervalMin; else if (min.compareTo(intervalMin) > 0) min = intervalMin; } if (intervalMax != null) { if (max == null) max = intervalMax; else if (max.compareTo(intervalMax) > 0) max = intervalMax; } } // Min can't be greater than max. if (min != null && max != null && min.compareTo(max) > 0) { min = max; } // Apply the filter ColumnFilter filter; if (min != null && max != null) { filter = FilterFactory.between(cg.getSourceId(), min, max); } else if (min != null) { filter = FilterFactory.greaterOrEqualsTo(cg.getSourceId(), min); } else if (max != null) { filter = FilterFactory.lowerOrEqualsTo(cg.getSourceId(), max); } else { filter = FilterFactory.equalsTo(cg.getSourceId(), names); } _appendFilterBy(metadata, def, filter, _query); } } protected void _appendGroupBy(DataSetGroup groupOp) { ColumnGroup cg = groupOp.getColumnGroup(); String sourceId = cg.getSourceId(); String dbColumnId = _columnFromMetadata(metadata, sourceId); ColumnType columnType = metadata.getColumnType(dbColumnId); boolean postProcessing = false; // Group by Text => not supported if (ColumnType.TEXT.equals(columnType)) { throw new IllegalArgumentException("Group by text '" + sourceId + NOT_SUPPORTED); } // Group by Date else if (ColumnType.DATE.equals(columnType)) { _query.groupBy(_createColumn(cg)); postProcessing = true; } // Group by Label or Number (treated as label) else { _query.groupBy(SQLFactory.column(dbColumnId)); for (GroupFunction gf : groupOp.getGroupFunctions()) { if (!sourceId.equals(gf.getSourceId()) && gf.getFunction() == null) { postProcessing = true; } } } // Also add any non-aggregated column (columns pick up) to the group statement for (GroupFunction gf : groupOp.getGroupFunctions()) { if (gf.getFunction() == null && !gf.getSourceId().equalsIgnoreCase(cg.getSourceId())) { String dbGfId = _columnFromMetadata(metadata, gf.getSourceId()); _query.groupBy(SQLFactory.column(dbGfId)); } } // The group operation might require post processing if (postProcessing) { DataSetGroup postGroup = groupOp.cloneInstance(); GroupFunction gf = postGroup.getGroupFunction(sourceId); if (gf != null) { String targetId = _getTargetColumnId(gf); postGroup.getColumnGroup().setSourceId(targetId); postGroup.getColumnGroup().setColumnId(targetId); } for (GroupFunction pgf : postGroup.getGroupFunctions()) { AggregateFunctionType pft = pgf.getFunction(); pgf.setSourceId(_getTargetColumnId(pgf)); if (pft != null && (AggregateFunctionType.DISTINCT.equals(pft) || AggregateFunctionType.COUNT.equals(pft))) { pgf.setFunction(AggregateFunctionType.SUM); } } postProcessingOps.add(postGroup); } } protected DataSet _buildDataSet(List<DataColumn> columns, ResultSet _rs) throws Exception { DataSet dataSet = DataSetFactory.newEmptyDataSet(); dataSet.setUUID(def.getUUID()); dataSet.setDefinition(def); DataColumn dateGroupColumn = null; boolean dateIncludeEmptyIntervals = false; // Create an empty data set for (int i = 0; i < columns.size(); i++) { DataColumn column = columns.get(i).cloneEmpty(); dataSet.addColumn(column); } // Offset post-processing if (_query.isOffsetPostProcessing() && _query.getOffset() > 0) { // Move the cursor to the specified offset or until the end of the result set is reached for (int i = 0; i < _query.getOffset() && _rs.next(); i++) ; } // Populate the data set int rowIdx = 0; int numRows = _query.getLimit(); while (_rs.next() && (numRows < 0 || rowIdx++ < numRows)) { for (int i = 0; i < columns.size(); i++) { DataColumn column = dataSet.getColumnByIndex(i); column.getValues().add(_rs.getObject(i + 1)); } } // Process the data set values according to each column type and the JDBC dialect Dialect dialect = JDBCUtils.dialect(conn); for (DataColumn column : dataSet.getColumns()) { ColumnType columnType = column.getColumnType(); List values = column.getValues(); if (ColumnType.LABEL.equals(columnType)) { ColumnGroup cg = column.getColumnGroup(); if (cg != null && ColumnType.DATE.equals(metadata.getColumnType(cg.getSourceId()))) { dateGroupColumn = column; dateIncludeEmptyIntervals = cg.areEmptyIntervalsAllowed(); // If grouped by date then convert back to absolute dates // in order to allow the post processing of the data set. column.setColumnType(ColumnType.DATE); for (int j = 0; j < values.size(); j++) { Object val = values.remove(j); Date dateObj = DateUtils.parseDate(column, val); values.add(j, dateObj); } } else { for (int j = 0; j < values.size(); j++) { Object value = dialect.convertToString(values.remove(j)); values.add(j, value); } } } else if (ColumnType.NUMBER.equals(columnType)) { for (int j = 0; j < values.size(); j++) { Object value = dialect.convertToDouble(values.remove(j)); values.add(j, value); } } else if (ColumnType.DATE.equals(columnType)) { for (int j = 0; j < values.size(); j++) { Object value = dialect.convertToDate(values.remove(j)); values.add(j, value); } } else { for (int j = 0; j < values.size(); j++) { Object value = dialect.convertToString(values.remove(j)); values.add(j, value); } } column.setValues(values); } // Some operations requires some in-memory post-processing if (!postProcessingOps.isEmpty()) { DataSet tempSet = opEngine.execute(dataSet, postProcessingOps); dataSet = dataSet.cloneEmpty(); dataSet.setUUID(def.getUUID()); dataSet.setDefinition(def); for (int i = 0; i < columns.size(); i++) { DataColumn source = tempSet.getColumnByIndex(i); DataColumn target = dataSet.getColumnByIndex(i); target.setColumnType(source.getColumnType()); target.setIntervalType(source.getIntervalType()); target.setMinValue(target.getMinValue()); target.setMaxValue(target.getMaxValue()); target.setValues(new ArrayList(source.getValues())); } } // Group by date might require to include empty intervals if (dateIncludeEmptyIntervals) { IntervalBuilder intervalBuilder = intervalBuilderLocator.lookup(ColumnType.DATE, dateGroupColumn.getColumnGroup().getStrategy()); IntervalList intervalList = intervalBuilder.build(dateGroupColumn); if (intervalList.size() > dataSet.getRowCount()) { List values = dateGroupColumn.getValues(); int valueIdx = 0; for (int intervalIdx = 0; intervalIdx < intervalList.size(); intervalIdx++) { String interval = intervalList.get(intervalIdx).getName(); String value = values.isEmpty() ? null : (String) values.get(valueIdx++); if (value == null || !value.equals(interval)) { dataSet.addEmptyRowAt(intervalIdx); dateGroupColumn.getValues().set(intervalIdx, interval); } } } } return dataSet; } protected Collection<Column> _createAllColumns() { Collection<Column> columns = new ArrayList<Column>(); for (int i = 0; i < metadata.getNumberOfColumns(); i++) { String columnId = metadata.getColumnId(i); columns.add(SQLFactory.column(columnId)); } return columns; } protected Collection<Column> _createColumns(DataSetGroup gOp) { if (gOp == null) { return _createAllColumns(); } ColumnGroup cg = gOp.getColumnGroup(); Collection<Column> _columns = new ArrayList<Column>(); for (GroupFunction gf : gOp.getGroupFunctions()) { String sourceId = gf.getSourceId(); if (StringUtils.isBlank(sourceId)) { sourceId = metadata.getColumnId(0); } else { _assertColumnExists(metadata, sourceId); } String targetId = gf.getColumnId(); if (StringUtils.isBlank(targetId)) { targetId = sourceId; } if (cg != null && cg.getSourceId().equals(sourceId) && gf.getFunction() == null) { _columns.add(_createColumn(cg).as(targetId)); } else { _columns.add(_createColumn(gf).as(targetId)); } } return _columns; } protected Column _createColumn(GroupFunction gf) { String sourceId = gf.getSourceId(); if (sourceId == null) { sourceId = metadata.getColumnId(0); } AggregateFunctionType ft = gf.getFunction(); String dbColumnId = _columnFromMetadata(metadata, sourceId); return SQLFactory.column(dbColumnId).function(ft); } protected Column _createColumn(ColumnGroup cg) { String sourceId = cg.getSourceId(); String dbColumnId = _columnFromMetadata(metadata, sourceId); ColumnType type = metadata.getColumnType(dbColumnId); if (ColumnType.DATE.equals(type)) { DateIntervalType size = calculateDateInterval(cg); return SQLFactory.column(dbColumnId, cg.getStrategy(), size); } if (ColumnType.TEXT.equals(type)) { throw new IllegalArgumentException("Group by text '" + sourceId + NOT_SUPPORTED); } return SQLFactory.column(dbColumnId); } protected String _getTargetColumnId(GroupFunction gf) { String sourceId = gf.getSourceId(); if (sourceId != null) { _assertColumnExists(metadata, sourceId); } return gf.getColumnId() == null ? sourceId : gf.getColumnId(); } } }