dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #32966
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 16770: Analytics. Implemented support for text data / data elements of value type text.
Merge authors:
Lars Helge Øverland (larshelge)
------------------------------------------------------------
revno: 16770 [merge]
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2014-09-22 17:30:14 +0200
message:
Analytics. Implemented support for text data / data elements of value type text.
added:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java 2014-07-15 18:31:16 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/AggregationType.java 2014-09-22 12:42:22 +0000
@@ -41,7 +41,8 @@
STDDEV( "stddev" ),
VARIANCE( "variance" ),
MIN( "min" ),
- MAX( "max" );
+ MAX( "max" ),
+ NONE( "none" );
private final String value;
=== added file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/analytics/DataType.java 2014-09-22 12:42:22 +0000
@@ -0,0 +1,37 @@
+package org.hisp.dhis.analytics;
+
+/*
+ * Copyright (c) 2004-2014, University of Oslo
+ * All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ * Redistributions of source code must retain the above copyright notice, this
+ * list of conditions and the following disclaimer.
+ *
+ * Redistributions in binary form must reproduce the above copyright notice,
+ * this list of conditions and the following disclaimer in the documentation
+ * and/or other materials provided with the distribution.
+ * Neither the name of the HISP project nor the names of its contributors may
+ * be used to endorse or promote products derived from this software without
+ * specific prior written permission.
+ *
+ * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
+ * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+ * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
+ * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
+ * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
+ * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
+ * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
+ * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
+ * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
+ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+ */
+
+/**
+ * @author Lars Helge Overland
+ */
+public enum DataType
+{
+ NUMERIC, TEXT;
+}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 2014-03-18 08:10:10 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 2014-09-22 15:30:14 +0000
@@ -43,12 +43,12 @@
* Retrieves aggregated data values for the given query. The data is returned
* as a mapping where the key is concatenated from the dimension options for
* all dimensions, and the value is the data value. This method is invoked
- * asynchronously.
+ * asynchronously. The value class can be Double or String.
*
* @param params the query to retrieve aggregated data for.
* @return a map.
*/
- Future<Map<String, Double>> getAggregatedDataValues( DataQueryParams params );
+ Future<Map<String, Object>> getAggregatedDataValues( DataQueryParams params );
/**
* Inserts entries for the aggregation periods mapped to each data period
@@ -59,5 +59,5 @@
* @param dataPeriodAggregationPeriodMap the mapping between data periods and
* aggregation periods for this query.
*/
- void replaceDataPeriodsWithAggregationPeriods( Map<String, Double> dataValueMap, DataQueryParams params, ListMap<NameableObject, NameableObject> dataPeriodAggregationPeriodMap );
+ void replaceDataPeriodsWithAggregationPeriods( Map<String, Object> dataValueMap, DataQueryParams params, ListMap<NameableObject, NameableObject> dataPeriodAggregationPeriodMap );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2014-08-25 10:18:10 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2014-09-22 12:42:22 +0000
@@ -106,7 +106,7 @@
protected List<DimensionalObject> filters = new ArrayList<>();
protected AggregationType aggregationType;
-
+
private Map<MeasureFilter, Double> measureCriteria = new HashMap<>();
/**
@@ -155,6 +155,11 @@
protected transient Partitions partitions;
/**
+ * The data type for this query.
+ */
+ protected transient DataType dataType;
+
+ /**
* The aggregation period type for this query.
*/
protected transient String periodType;
@@ -201,6 +206,7 @@
params.hideEmptyRows = this.hideEmptyRows;
params.partitions = new Partitions( this.partitions );
+ params.dataType = this.dataType;
params.periodType = this.periodType;
params.dataPeriodType = this.dataPeriodType;
params.skipPartitioning = this.skipPartitioning;
@@ -604,6 +610,14 @@
return index == -1 ? null : index;
}
+
+ /**
+ * Indicates whether this object is of the given data type.
+ */
+ public boolean isDataType( DataType dataType )
+ {
+ return this.dataType != null && this.dataType.equals( dataType );
+ }
/**
* Indicates whether this object is of the given aggregation type.
@@ -916,6 +930,15 @@
this.dataApprovalLevels = new HashMap<>();
}
+ /**
+ * Indicates whether this params requires aggregation of data. No aggregation
+ * takes place if aggregation type is none or if data type is text.
+ */
+ public boolean isAggregation()
+ {
+ return !( AggregationType.NONE.equals( aggregationType ) || DataType.TEXT.equals( dataType ) );
+ }
+
// -------------------------------------------------------------------------
// Static methods
// -------------------------------------------------------------------------
@@ -1152,6 +1175,16 @@
this.partitions = partitions;
}
+ public DataType getDataType()
+ {
+ return dataType;
+ }
+
+ public void setDataType( DataType dataType )
+ {
+ this.dataType = dataType;
+ }
+
public String getPeriodType()
{
return periodType;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2014-09-22 04:51:49 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2014-09-22 15:30:14 +0000
@@ -338,13 +338,13 @@
dataSourceParams.removeDimension( INDICATOR_DIM_ID );
dataSourceParams.removeDimension( DATASET_DIM_ID );
- Map<String, Double> aggregatedDataMap = getAggregatedDataValueMap( dataSourceParams );
+ Map<String, Object> aggregatedDataMap = getAggregatedDataValueMapObjectTyped( dataSourceParams );
- for ( Map.Entry<String, Double> entry : aggregatedDataMap.entrySet() )
+ for ( Map.Entry<String, Object> entry : aggregatedDataMap.entrySet() )
{
grid.addRow();
grid.addValues( entry.getKey().split( DIMENSION_SEP ) );
- grid.addValue( params.isSkipRounding() ? entry.getValue() : MathUtils.getRounded( entry.getValue() ) );
+ grid.addValue( params.isSkipRounding() ? entry.getValue() : getRounded( entry.getValue() ) );
}
}
}
@@ -648,6 +648,19 @@
*/
private Map<String, Double> getAggregatedDataValueMap( DataQueryParams params )
{
+ return getDoubleMap( getAggregatedValueMap( params, ANALYTICS_TABLE_NAME ) );
+ }
+
+ /**
+ * Generates aggregated values for the given query. Creates a mapping between
+ * a dimension key and the aggregated value. The dimension key is a
+ * concatenation of the identifiers of the dimension items separated by "-".
+ *
+ * @param params the data query parameters.
+ * @return a mapping between a dimension key and the aggregated value.
+ */
+ private Map<String, Object> getAggregatedDataValueMapObjectTyped( DataQueryParams params )
+ {
return getAggregatedValueMap( params, ANALYTICS_TABLE_NAME );
}
@@ -661,7 +674,7 @@
*/
private Map<String, Double> getAggregatedCompletenessValueMap( DataQueryParams params )
{
- return getAggregatedValueMap( params, COMPLETENESS_TABLE_NAME );
+ return getDoubleMap( getAggregatedValueMap( params, COMPLETENESS_TABLE_NAME ) );
}
/**
@@ -674,7 +687,7 @@
*/
private Map<String, Double> getAggregatedCompletenessTargetMap( DataQueryParams params )
{
- return getAggregatedValueMap( params, COMPLETENESS_TARGET_TABLE_NAME );
+ return getDoubleMap( getAggregatedValueMap( params, COMPLETENESS_TARGET_TABLE_NAME ) );
}
/**
@@ -688,7 +701,7 @@
*/
private Map<String, Double> getAggregatedOrganisationUnitTargetMap( DataQueryParams params )
{
- return getAggregatedValueMap( params, ORGUNIT_TARGET_TABLE_NAME );
+ return getDoubleMap( getAggregatedValueMap( params, ORGUNIT_TARGET_TABLE_NAME ) );
}
/**
@@ -698,7 +711,7 @@
*
* @param params the data query parameters.
*/
- private Map<String, Double> getAggregatedValueMap( DataQueryParams params, String tableName )
+ private Map<String, Object> getAggregatedValueMap( DataQueryParams params, String tableName )
{
queryPlanner.validateMaintenanceMode();
@@ -710,22 +723,22 @@
t.getSplitTime( "Planned query, got: " + queryGroups.getLargestGroupSize() + " for optimal: " + optimalQueries );
- Map<String, Double> map = new HashMap<>();
+ Map<String, Object> map = new HashMap<>();
for ( List<DataQueryParams> queries : queryGroups.getSequentialQueries() )
{
- List<Future<Map<String, Double>>> futures = new ArrayList<>();
+ List<Future<Map<String, Object>>> futures = new ArrayList<>();
for ( DataQueryParams query : queries )
{
futures.add( analyticsManager.getAggregatedDataValues( query ) );
}
- for ( Future<Map<String, Double>> future : futures )
+ for ( Future<Map<String, Object>> future : futures )
{
try
{
- Map<String, Double> taskValues = future.get();
+ Map<String, Object> taskValues = future.get();
if ( taskValues != null )
{
@@ -1230,7 +1243,7 @@
* in the given grid. Returns an empty map if the grid or cocIndex parameters
* are null.
*
- * @param grid the grid.
+ * @param grid the grid.
* @param cocIndex the category option combo index in the grid.
*/
private Map<String, String> getCocNameMap( Grid grid, Integer cocIndex )
@@ -1263,4 +1276,36 @@
return (cores == null || cores == 0) ? SystemUtils.getCpuCores() : cores;
}
+
+ /**
+ * Converts a String, Object map into a specific String, Double map.
+ *
+ * @param map the map to convert.
+ */
+ private Map<String, Double> getDoubleMap( Map<String, Object> map )
+ {
+ Map<String, Double> typedMap = new HashMap<>();
+
+ for ( Map.Entry<String, Object> entry : map.entrySet() )
+ {
+ final Object value = entry.getValue();
+
+ if ( value != null && Double.class.equals( value.getClass() ) )
+ {
+ typedMap.put( entry.getKey(), (Double) entry.getValue() );
+ }
+ }
+
+ return typedMap;
+ }
+
+ /**
+ * Returns the given value. If of class Double the value is rounded.
+ *
+ * @param value the value to return and potentially round.
+ */
+ private Object getRounded( Object value )
+ {
+ return value != null && Double.class.equals( value.getClass() ) ? MathUtils.getRounded( (Double) value ) : value;
+ }
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2014-08-25 10:18:10 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2014-09-22 15:30:14 +0000
@@ -52,6 +52,7 @@
import org.hisp.dhis.analytics.AggregationType;
import org.hisp.dhis.analytics.DataQueryGroups;
import org.hisp.dhis.analytics.DataQueryParams;
+import org.hisp.dhis.analytics.DataType;
import org.hisp.dhis.analytics.Partitions;
import org.hisp.dhis.analytics.QueryPlanner;
import org.hisp.dhis.analytics.partition.PartitionManager;
@@ -233,29 +234,34 @@
for ( DataQueryParams byPeriodType : groupedByPeriodType )
{
- List<DataQueryParams> groupedByAggregationType = groupByAggregationType( byPeriodType );
-
- for ( DataQueryParams byAggregationType : groupedByAggregationType )
+ List<DataQueryParams> groupedByDataType = groupByDataType( byPeriodType );
+
+ for ( DataQueryParams byDataType : groupedByDataType )
{
- if ( AVERAGE_INT_DISAGGREGATION.equals( byAggregationType.getAggregationType() ) )
- {
- List<DataQueryParams> groupedByDataPeriodType = groupByDataPeriodType( byAggregationType );
-
- for ( DataQueryParams byDataPeriodType : groupedByDataPeriodType )
- {
- byDataPeriodType.setPartitions( byPartition.getPartitions() );
- byDataPeriodType.setPeriodType( byPeriodType.getPeriodType() );
- byDataPeriodType.setAggregationType( byAggregationType.getAggregationType() );
-
- queries.add( byDataPeriodType );
- }
- }
- else
- {
- byAggregationType.setPartitions( byPartition.getPartitions() );
- byAggregationType.setPeriodType( byPeriodType.getPeriodType() );
-
- queries.add( byAggregationType );
+ List<DataQueryParams> groupedByAggregationType = groupByAggregationType( byDataType );
+
+ for ( DataQueryParams byAggregationType : groupedByAggregationType )
+ {
+ if ( AVERAGE_INT_DISAGGREGATION.equals( byAggregationType.getAggregationType() ) )
+ {
+ List<DataQueryParams> groupedByDataPeriodType = groupByDataPeriodType( byAggregationType );
+
+ for ( DataQueryParams byDataPeriodType : groupedByDataPeriodType )
+ {
+ byDataPeriodType.setPartitions( byPartition.getPartitions() );
+ byDataPeriodType.setPeriodType( byPeriodType.getPeriodType() );
+ byDataPeriodType.setAggregationType( byAggregationType.getAggregationType() );
+
+ queries.add( byDataPeriodType );
+ }
+ }
+ else
+ {
+ byAggregationType.setPartitions( byPartition.getPartitions() );
+ byAggregationType.setPeriodType( byPeriodType.getPeriodType() );
+
+ queries.add( byAggregationType );
+ }
}
}
}
@@ -500,7 +506,38 @@
return queries;
}
-
+
+ private List<DataQueryParams> groupByDataType( DataQueryParams params )
+ {
+ List<DataQueryParams> queries = new ArrayList<>();
+
+ if ( params.getDataElements() != null && !params.getDataElements().isEmpty() )
+ {
+ ListMap<DataType, NameableObject> dataTypeDataElementMap = getDataTypeDataElementMap( params.getDataElements() );
+
+ for ( DataType dataType : dataTypeDataElementMap.keySet() )
+ {
+ DataQueryParams query = params.instance();
+ query.setDataElements( dataTypeDataElementMap.get( dataType ) );
+ query.setDataType( dataType );
+ queries.add( query );
+ }
+ }
+ else
+ {
+ DataQueryParams query = params.instance();
+ query.setDataType( DataType.NUMERIC );
+ queries.add( query );
+ }
+
+ if ( queries.size() > 1 )
+ {
+ log.debug( "Split on data type: " + queries.size() );
+ }
+
+ return queries;
+ }
+
/**
* Groups the given query in sub queries based on the aggregation type of its
* data elements. The aggregation type can be sum, average aggregation or
@@ -578,7 +615,7 @@
return queries;
}
-
+
/**
* Groups the given query in sub queries based on the period type of its
* data elements. Sets the data period type on each query.
@@ -634,7 +671,26 @@
return map;
}
-
+
+ /**
+ * Creates a mapping between data type and data element for the given data elements.
+ */
+ private ListMap<DataType, NameableObject> getDataTypeDataElementMap( Collection<NameableObject> dataElements )
+ {
+ ListMap<DataType, NameableObject> map = new ListMap<>();
+
+ for ( NameableObject element : dataElements )
+ {
+ DataElement de = (DataElement) element;
+
+ DataType dataType = DataElement.VALUE_TYPE_STRING.equals( de.getType() ) ? DataType.TEXT : DataType.NUMERIC;
+
+ map.putValue( dataType, de );
+ }
+
+ return map;
+ }
+
/**
* Creates a mapping between the aggregation type and data element for the
* given data elements and period type.
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2014-08-15 07:40:20 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2014-09-22 15:30:14 +0000
@@ -32,11 +32,13 @@
import static org.hisp.dhis.analytics.AggregationType.AVERAGE_INT;
import static org.hisp.dhis.analytics.AggregationType.AVERAGE_INT_DISAGGREGATION;
import static org.hisp.dhis.analytics.AggregationType.COUNT;
+import static org.hisp.dhis.analytics.AggregationType.MAX;
+import static org.hisp.dhis.analytics.AggregationType.MIN;
import static org.hisp.dhis.analytics.AggregationType.STDDEV;
import static org.hisp.dhis.analytics.AggregationType.VARIANCE;
-import static org.hisp.dhis.analytics.AggregationType.MIN;
-import static org.hisp.dhis.analytics.AggregationType.MAX;
+import static org.hisp.dhis.analytics.DataQueryParams.LEVEL_PREFIX;
import static org.hisp.dhis.analytics.DataQueryParams.VALUE_ID;
+import static org.hisp.dhis.analytics.DataType.TEXT;
import static org.hisp.dhis.analytics.MeasureFilter.EQ;
import static org.hisp.dhis.analytics.MeasureFilter.GE;
import static org.hisp.dhis.analytics.MeasureFilter.GT;
@@ -47,7 +49,6 @@
import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString;
import static org.hisp.dhis.system.util.TextUtils.removeLastOr;
import static org.hisp.dhis.system.util.TextUtils.trimEnd;
-import static org.hisp.dhis.analytics.DataQueryParams.LEVEL_PREFIX;
import java.util.Collection;
import java.util.HashMap;
@@ -110,7 +111,7 @@
// -------------------------------------------------------------------------
@Async
- public Future<Map<String, Double>> getAggregatedDataValues( DataQueryParams params )
+ public Future<Map<String, Object>> getAggregatedDataValues( DataQueryParams params )
{
try
{
@@ -133,7 +134,7 @@
log.debug( sql );
- Map<String, Double> map = null;
+ Map<String, Object> map = null;
try
{
@@ -143,7 +144,7 @@
{
log.info( "Query failed, likely because the requested analytics table does not exist", ex );
- return new AsyncResult<Map<String, Double>>( new HashMap<String, Double>() );
+ return new AsyncResult<Map<String, Object>>( new HashMap<String, Object>() );
}
replaceDataPeriodsWithAggregationPeriods( map, params, dataPeriodAggregationPeriodMap );
@@ -158,7 +159,7 @@
}
}
- public void replaceDataPeriodsWithAggregationPeriods( Map<String, Double> dataValueMap, DataQueryParams params, ListMap<NameableObject, NameableObject> dataPeriodAggregationPeriodMap )
+ public void replaceDataPeriodsWithAggregationPeriods( Map<String, Object> dataValueMap, DataQueryParams params, ListMap<NameableObject, NameableObject> dataPeriodAggregationPeriodMap )
{
if ( params.isAggregationType( AVERAGE_INT_DISAGGREGATION ) )
{
@@ -181,7 +182,7 @@
Assert.notNull( periods, dataPeriodAggregationPeriodMap.toString() );
- Double value = dataValueMap.get( key );
+ Object value = dataValueMap.get( key );
for ( NameableObject period : periods )
{
@@ -205,6 +206,24 @@
private String getSelectClause( DataQueryParams params )
{
String sql = "select " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() ) + ", ";
+
+ if ( params.isDataType( TEXT ) )
+ {
+ sql += "textvalue";
+ }
+ else // NUMERIC
+ {
+ sql += getNumericValueColumn( params );
+ }
+
+ sql += " as value ";
+
+ return sql;
+ }
+
+ private String getNumericValueColumn( DataQueryParams params )
+ {
+ String sql = "";
if ( params.isAggregationType( AVERAGE_INT ) )
{
@@ -241,9 +260,7 @@
sql += "sum(value)";
}
- sql += " as value ";
-
- return sql;
+ return sql;
}
/**
@@ -347,7 +364,12 @@
*/
private String getGroupByClause( DataQueryParams params )
{
- String sql = "group by " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() );
+ String sql = "";
+
+ if ( params.isAggregation() )
+ {
+ sql = "group by " + getCommaDelimitedQuotedColumns( params.getQueryDimensions() );
+ }
return sql;
}
@@ -356,10 +378,10 @@
* Retrieves data from the database based on the given query and SQL and puts
* into a value key and value mapping.
*/
- private Map<String, Double> getKeyValueMap( DataQueryParams params, String sql )
+ private Map<String, Object> getKeyValueMap( DataQueryParams params, String sql )
throws BadSqlGrammarException
{
- Map<String, Double> map = new HashMap<>();
+ Map<String, Object> map = new HashMap<>();
Timer t = new Timer().start();
@@ -369,13 +391,6 @@
while ( rowSet.next() )
{
- Double value = rowSet.getDouble( VALUE_ID );
-
- if ( !measureCriteriaSatisfied( params, value ) )
- {
- continue;
- }
-
StringBuilder key = new StringBuilder();
for ( DimensionalObject dim : params.getQueryDimensions() )
@@ -385,7 +400,26 @@
key.deleteCharAt( key.length() - 1 );
- map.put( key.toString(), value );
+ if ( params.isDataType( TEXT ) )
+ {
+ String value = rowSet.getString( VALUE_ID );
+
+ map.put( key.toString(), value );
+ }
+ else // NUMERIC
+ {
+ Double value = rowSet.getDouble( VALUE_ID );
+
+ if ( value != null && Double.class.equals( value.getClass() ) )
+ {
+ if ( !measureCriteriaSatisfied( params, (Double) value ) )
+ {
+ continue;
+ }
+ }
+
+ map.put( key.toString(), value );
+ }
}
return map;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2014-09-12 15:26:18 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2014-09-22 12:42:22 +0000
@@ -117,7 +117,7 @@
sqlCreate += col[0] + " " + col[1] + ",";
}
- sqlCreate += "daysxvalue " + dbl + ", daysno integer not null, value " + dbl + ") ";
+ sqlCreate += "daysxvalue " + dbl + ", daysno integer not null, value " + dbl + ", textvalue varchar(50000)) ";
sqlCreate += statementBuilder.getTableOptions( false );
@@ -144,13 +144,15 @@
"dv.value " + statementBuilder.getRegexpMatch() + " '" + MathUtils.NUMERIC_LENIENT_REGEXP + "' " +
"and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant = true ) ";
- populateTable( table, "cast(dv.value as " + dbl + ")", "int", intClause );
+ populateTable( table, "cast(dv.value as " + dbl + ")", "null", "int", intClause );
- populateTable( table, "1" , DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'" );
+ populateTable( table, "1", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'" );
- populateTable( table, "0" , DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'" );
-
- populateTable( table, "1" , DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'" );
+ populateTable( table, "0", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'" );
+
+ populateTable( table, "1", "null", DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'" );
+
+ populateTable( table, "null", "dv.value", DataElement.VALUE_TYPE_STRING, null );
}
return null;
@@ -158,7 +160,16 @@
// TODO join categoryoptiongroupsetstructure on both categoryoptioncomboid and attributeoptioncomboid
- private void populateTable( AnalyticsTable table, String valueExpression, String valueType, String clause )
+ /**
+ * Populates the given analytics table.
+ *
+ * @param table analytics table to populate.
+ * @param valueExpression numeric value expression.
+ * @param textValueExpression textual value expression.
+ * @param valueType data element value type to include data for.
+ * @param whereClause where clause to constrain data query.
+ */
+ private void populateTable( AnalyticsTable table, String valueExpression, String textValueExpression, String valueType, String whereClause )
{
final String start = DateUtils.getMediumDateString( table.getPeriod().getStartDate() );
final String end = DateUtils.getMediumDateString( table.getPeriod().getEndDate() );
@@ -170,7 +181,7 @@
sql += col[0] + ",";
}
- sql += "daysxvalue, daysno, value) select ";
+ sql += "daysxvalue, daysno, value, textvalue) select ";
for ( String[] col : getDimensionColumns( table ) )
{
@@ -180,7 +191,8 @@
sql +=
valueExpression + " * ps.daysno as daysxvalue, " +
"ps.daysno as daysno, " +
- valueExpression + " as value " +
+ valueExpression + " as value, " +
+ textValueExpression + " as textvalue " +
"from datavalue dv " +
"left join _dataelementgroupsetstructure degs on dv.dataelementid=degs.dataelementid " +
"left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " +
@@ -196,8 +208,12 @@
"and de.domaintype = 'AGGREGATE' " +
"and pe.startdate >= '" + start + "' " +
"and pe.startdate <= '" + end + "' " +
- "and dv.value is not null " +
- "and " + clause;
+ "and dv.value is not null ";
+
+ if ( whereClause != null )
+ {
+ sql += "and " + whereClause;
+ }
log.info( "Populate SQL: "+ sql );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java 2014-08-15 07:40:20 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/AnalyticsManagerTest.java 2014-09-22 13:22:24 +0000
@@ -67,7 +67,7 @@
params.setDataPeriodType( new YearlyPeriodType() );
params.setAggregationType( AggregationType.AVERAGE_INT_DISAGGREGATION );
- Map<String, Double> dataValueMap = new HashMap<>();
+ Map<String, Object> dataValueMap = new HashMap<>();
dataValueMap.put( BASE_UID + "A-2012-" + BASE_UID + "A", 1d );
dataValueMap.put( BASE_UID + "B-2012-" + BASE_UID + "A", 1d );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2014-08-15 07:40:20 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2014-09-22 12:42:22 +0000
@@ -36,7 +36,9 @@
import static org.hisp.dhis.common.NameableObjectUtils.getList;
import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_AVERAGE;
import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_SUM;
+import static org.hisp.dhis.dataelement.DataElement.AGGREGATION_OPERATOR_NONE;
import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_INT;
+import static org.hisp.dhis.dataelement.DataElement.VALUE_TYPE_STRING;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
@@ -111,6 +113,8 @@
private DataElement deB;
private DataElement deC;
private DataElement deD;
+ private DataElement deE;
+ private DataElement deF;
private DataSet dsA;
private DataSet dsB;
@@ -140,11 +144,14 @@
deB = createDataElement( 'B', VALUE_TYPE_INT, AGGREGATION_OPERATOR_SUM );
deC = createDataElement( 'C', VALUE_TYPE_INT, AGGREGATION_OPERATOR_AVERAGE );
deD = createDataElement( 'D', VALUE_TYPE_INT, AGGREGATION_OPERATOR_AVERAGE );
+ deE = createDataElement( 'E', VALUE_TYPE_STRING, AGGREGATION_OPERATOR_NONE );
+ deF = createDataElement( 'F', VALUE_TYPE_STRING, AGGREGATION_OPERATOR_NONE );
dataElementService.addDataElement( deA );
dataElementService.addDataElement( deB );
dataElementService.addDataElement( deC );
dataElementService.addDataElement( deD );
+ dataElementService.addDataElement( deE );
dsA = createDataSet( 'A', pt );
dsB = createDataSet( 'B', pt );
@@ -592,7 +599,7 @@
/**
* Query spans 3 period types. Splits in 3 queries for each period type, then
* splits in 4 queries on data elements units to satisfy optimal for a total
- * of 12 queries, because query has 2 different aggregation types.
+ * of 12 queries, because query has 2 different aggregation types.
*/
@Test
public void planQueryI()
@@ -653,7 +660,33 @@
assertDimensionNameNotNull( query );
}
}
-
+
+ /**
+ * Splits in 2 queries for each aggregation type, then 2 queries for each
+ * data type, then 2 queries for each organisation unit to satisfy optimal
+ * for a total of 4 queries across 2 sequential queries.
+ */
+ @Test
+ public void planQueryL()
+ {
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( getList( deA, deB, deE, deF ) );
+ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD ) );
+ params.setFilterPeriods( getList( createPeriod( "2000Q1" ) ) );
+
+ DataQueryGroups queryGroups = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME );
+
+ assertEquals( 8, queryGroups.getAllQueries().size() );
+ assertEquals( 2, queryGroups.getSequentialQueries().size() );
+ assertEquals( 4, queryGroups.getLargestGroupSize() );
+
+ for ( DataQueryParams query : queryGroups.getAllQueries() )
+ {
+ assertDimensionNameNotNull( query );
+ assertNotNull( query.getDataType() );
+ }
+ }
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
