dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20459
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9341: Query planner, impl support for multiple period types and org unit levels in same query
------------------------------------------------------------
revno: 9341
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Mon 2012-12-17 17:58:29 +0100
message:
Query planner, impl support for multiple period types and org unit levels in same query
added:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java
renamed:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/QueryPlanner.java => dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java
modified:
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/JdbcAnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.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/main/resources/META-INF/dhis/beans.xml
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/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.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-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 2012-12-14 15:04:48 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2012-12-17 16:58:29 +0000
@@ -47,6 +47,7 @@
public static final String CATEGORYOPTIONCOMBO_DIM_ID = "coc";
public static final String PERIOD_DIM_ID = "pe";
public static final String ORGUNIT_DIM_ID = "ou";
+ public static final String VALUE_ID = "value";
private List<String> indicators = new ArrayList<String>();
@@ -59,9 +60,17 @@
private Map<String, List<String>> dimensions = new HashMap<String, List<String>>();
private boolean categories = false;
+
+ // -------------------------------------------------------------------------
+ // Transient properties
+ // -------------------------------------------------------------------------
private transient String tableName;
+ private transient String periodType;
+
+ private transient int organisationUnitLevel;
+
// -------------------------------------------------------------------------
// Constructors
// -------------------------------------------------------------------------
@@ -89,18 +98,26 @@
this.organisationUnits = params.getOrganisationUnits();
this.dimensions = params.getDimensions();
this.categories = params.isCategories();
+
+ this.tableName = params.getTableName();
+ this.periodType = params.getPeriodType();
+ this.organisationUnitLevel = params.getOrganisationUnitLevel();
}
// -------------------------------------------------------------------------
// Logic
// -------------------------------------------------------------------------
+ /**
+ * Prioritizing to split on dimensions with high cardinality, which is typically
+ * organisation unit and data element in that order.
+ */
public SortedMap<String, List<String>> getDimensionValuesMap()
{
SortedMap<String, List<String>> map = new TreeMap<String, List<String>>();
-
+
+ map.put( ORGUNIT_DIM_ID, organisationUnits );
map.put( DATAELEMENT_DIM_ID, dataElements );
- map.put( ORGUNIT_DIM_ID, organisationUnits );
map.put( PERIOD_DIM_ID, periods );
if ( dimensions != null )
@@ -192,12 +209,114 @@
return dimension;
}
-
+
+ @Override
+ public int hashCode()
+ {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + ( categories ? 1231 : 1237);
+ result = prime * result + ( ( indicators == null ) ? 0 : indicators.hashCode() );
+ result = prime * result + ( ( dataElements == null ) ? 0 : dataElements.hashCode() );
+ result = prime * result + ( ( periods == null ) ? 0 : periods.hashCode() );
+ result = prime * result + ( ( organisationUnits == null ) ? 0 : organisationUnits.hashCode() );
+ result = prime * result + ( ( dimensions == null ) ? 0 : dimensions.hashCode() );
+ return result;
+ }
+
+ @Override
+ public boolean equals( Object object )
+ {
+ if ( this == object )
+ {
+ return true;
+ }
+
+ if ( object == null )
+ {
+ return false;
+ }
+
+ if ( getClass() != object.getClass() )
+ {
+ return false;
+ }
+
+ DataQueryParams other = (DataQueryParams) object;
+
+ if ( indicators == null )
+ {
+ if ( other.indicators != null )
+ {
+ return false;
+ }
+ }
+ else if ( !indicators.equals( other.indicators ) )
+ {
+ return false;
+ }
+
+ if ( dataElements == null )
+ {
+ if ( other.dataElements != null )
+ {
+ return false;
+ }
+ }
+ else if ( !dataElements.equals( other.dataElements ) )
+ {
+ return false;
+ }
+
+ if ( periods == null )
+ {
+ if ( other.periods != null )
+ {
+ return false;
+ }
+ }
+ else if ( !periods.equals( other.periods ) )
+ {
+ return false;
+ }
+
+ if ( organisationUnits == null )
+ {
+ if ( other.organisationUnits != null )
+ {
+ return false;
+ }
+ }
+ else if ( !organisationUnits.equals( other.organisationUnits ) )
+ {
+ return false;
+ }
+
+ if ( dimensions == null )
+ {
+ if ( other.dimensions != null )
+ {
+ return false;
+ }
+ }
+ else if ( !dimensions.equals( other.dimensions ) )
+ {
+ return false;
+ }
+
+ if ( categories != other.categories )
+ {
+ return false;
+ }
+
+ return true;
+ }
+
@Override
public String toString()
{
return "[in: " + indicators + ", de: " + dataElements + ", pe: " + periods
- + ", ou: " + organisationUnits + "]";
+ + ", ou: " + organisationUnits + ", categories: " + categories + "]";
}
// -------------------------------------------------------------------------
@@ -279,4 +398,24 @@
{
this.tableName = tableName;
}
+
+ public String getPeriodType()
+ {
+ return periodType;
+ }
+
+ public void setPeriodType( String periodType )
+ {
+ this.periodType = periodType;
+ }
+
+ public int getOrganisationUnitLevel()
+ {
+ return organisationUnitLevel;
+ }
+
+ public void setOrganisationUnitLevel( int organisationUnitLevel )
+ {
+ this.organisationUnitLevel = organisationUnitLevel;
+ }
}
=== added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java 2012-12-17 16:58:29 +0000
@@ -0,0 +1,58 @@
+package org.hisp.dhis.analytics;
+
+/*
+ * Copyright (c) 2004-2012, 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.
+ */
+
+import java.util.List;
+
+public interface QueryPlanner
+{
+ /**
+ * Creates a list of DataQueryParams. It is mandatory to group the queries by
+ * the following criteria: 1) partition / year 2) period type 3) organisation
+ * unit level. If the number of queries produced by this grouping is equal or
+ * larger than the number of optimal queries, those queries are returned.
+ *
+ * @param params the data query params.
+ * @param optimalQueries the number of optimal queries for the planner to return.
+ * @return
+ */
+ List<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries );
+
+ /**
+ * Gets the data dimension must suitable as partition key. Will first check
+ * if any of the dimensions have enough values to satisfy a optimal number of
+ * queries, and return that dimension if so. If not returns the dimension
+ * with the highest number of values. The order of the fixed dimensions are
+ * data element, organisation unit, period.
+ *
+ * @param params the data query parameters.
+ * @param optimalQueries the optimal number of queries to create.
+ */
+ String getPartitionDimension( DataQueryParams params, int optimalQueries );
+
+}
=== 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 2012-12-14 17:23:37 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-17 16:58:29 +0000
@@ -36,6 +36,7 @@
import org.hisp.dhis.analytics.AnalyticsManager;
import org.hisp.dhis.analytics.AnalyticsService;
import org.hisp.dhis.analytics.DataQueryParams;
+import org.hisp.dhis.analytics.QueryPlanner;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.GridHeader;
import org.hisp.dhis.system.grid.ListGrid;
@@ -45,17 +46,23 @@
public class DefaultAnalyticsService
implements AnalyticsService
{
- private static final String VALUE_NAME = "value";
private static final String VALUE_HEADER_NAME = "Value";
//TODO period aggregation for multiple period types
//TODO hierarchy aggregation for org units at multiple levels
//TODO indicator aggregation
//TODO category sub-totals and totals
+ //TODO use data mart when query can be satisfied
+ //TODO create data mart for average, less-than yearly data elements
+
+ //NOTE split on high-cardinality columns like data element and org unit, not period, improves performance
@Autowired
private AnalyticsManager analyticsManager;
+ @Autowired
+ private QueryPlanner queryPlanner;
+
public Grid getAggregatedDataValueTotals( DataQueryParams params ) throws Exception
{
Map<String, Double> map = getAggregatedDataValueMap( params );
@@ -67,7 +74,7 @@
grid.addHeader( new GridHeader( col, col, String.class.getName(), false, true ) );
}
- grid.addHeader( new GridHeader( VALUE_NAME, VALUE_HEADER_NAME, Double.class.getName(), false, false ) );
+ grid.addHeader( new GridHeader( DataQueryParams.VALUE_ID, VALUE_HEADER_NAME, Double.class.getName(), false, false ) );
for ( Map.Entry<String, Double> entry : map.entrySet() )
{
@@ -83,7 +90,7 @@
{
Timer t = new Timer().start();
- List<DataQueryParams> queries = QueryPlanner.planQuery( params, 6 );
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 6 );
t.getTime( "Planned query" );
=== renamed file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/QueryPlanner.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/QueryPlanner.java 2012-12-12 15:29:04 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2012-12-17 16:58:29 +0000
@@ -28,82 +28,100 @@
*/
import java.util.ArrayList;
+import java.util.Collection;
import java.util.List;
import java.util.SortedMap;
import org.hisp.dhis.analytics.DataQueryParams;
+import org.hisp.dhis.analytics.QueryPlanner;
import org.hisp.dhis.analytics.table.PartitionUtils;
+import org.hisp.dhis.organisationunit.OrganisationUnitService;
+import org.hisp.dhis.period.PeriodType;
import org.hisp.dhis.system.util.ListMap;
import org.hisp.dhis.system.util.PaginatedList;
+import org.springframework.beans.factory.annotation.Autowired;
-public class QueryPlanner
+public class DefaultQueryPlanner
+ implements QueryPlanner
{
- /**
- * Creates a list of DataQueryParams.
- *
- * @param params the data query params.
- * @param optimalQueries the number of optimal queries for the planner to return.
- * @return
- */
- public static List<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries )
+ @Autowired
+ private OrganisationUnitService organisationUnitService;
+
+ // -------------------------------------------------------------------------
+ // DefaultQueryPlanner implementation
+ // -------------------------------------------------------------------------
+
+ public List<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries )
{
+ // ---------------------------------------------------------------------
+ // Group queries by partition, period type and organisation unit level
+ // ---------------------------------------------------------------------
+
params = new DataQueryParams( params );
List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
-
- ListMap<String, String> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods() );
-
- boolean periodSatisfies = tablePeriodMap.size() >= optimalQueries;
-
- if ( periodSatisfies )
- {
- for ( String tableName : tablePeriodMap.keySet() )
- {
- DataQueryParams query = new DataQueryParams( params );
- query.setPeriods( tablePeriodMap.get( tableName ) );
- query.setTableName( tableName );
- queries.add( query );
- }
- }
- else
- {
- int pages = optimalQueries / tablePeriodMap.size(); // TODO individual no per table
-
- for ( String tableName : tablePeriodMap.keySet() )
- {
- params.setPeriods( tablePeriodMap.get( tableName ) );
-
- String dimension = getPartitionDimension( params, pages );
-
- List<String> partitionValues = params.getDimension( dimension );
-
- List<List<String>> partitionValuePages = new PaginatedList<String>( partitionValues ).setNumberOfPages( pages ).getPages();
+
+ List<DataQueryParams> groupedByPartition = groupByPartition( params );
+
+ for ( DataQueryParams byPartition : groupedByPartition )
+ {
+ List<DataQueryParams> groupedByPeriodType = groupByPeriodType( byPartition );
- for ( List<String> valuePage : partitionValuePages )
+ for ( DataQueryParams byPeriodType : groupedByPeriodType )
+ {
+ List<DataQueryParams> groupedByOrgUnitLevel = groupByOrgUnitLevel( byPeriodType );
+
+ for ( DataQueryParams byOrgUnitLevel : groupedByOrgUnitLevel )
{
- DataQueryParams query = new DataQueryParams( params );
- query.setPeriods( tablePeriodMap.get( tableName ) );
- query.setDimension( dimension, valuePage );
- query.setTableName( tableName );
- queries.add( query );
+ byOrgUnitLevel.setTableName( byPartition.getTableName() );
+ byOrgUnitLevel.setPeriodType( byPeriodType.getPeriodType() );
+
+ queries.add( byOrgUnitLevel );
}
}
}
-
- return queries;
+
+ // ---------------------------------------------------------------------
+ // Return if number of queries are equal or larger than optimal
+ // ---------------------------------------------------------------------
+
+ boolean satisfied = queries.size() >= optimalQueries;
+
+ if ( satisfied )
+ {
+ return queries;
+ }
+
+ // ---------------------------------------------------------------------
+ // Group by dimensions
+ // ---------------------------------------------------------------------
+
+ List<DataQueryParams> subQueries = new ArrayList<DataQueryParams>();
+
+ Double pages = Math.ceil( (double) optimalQueries / queries.size() );
+
+ int optimal = pages.intValue();
+
+ for ( DataQueryParams query : queries )
+ {
+ String dimension = getPartitionDimension( query, optimal );
+
+ List<String> partitionValues = params.getDimension( dimension );
+
+ List<List<String>> partitionValuePages = new PaginatedList<String>( partitionValues ).setNumberOfPages( optimal ).getPages();
+
+ for ( List<String> valuePage : partitionValuePages )
+ {
+ DataQueryParams subQuery = new DataQueryParams( query );
+ subQuery.setDimension( dimension, valuePage );
+ subQueries.add( subQuery );
+ }
+ }
+
+ return subQueries;
}
- /**
- * Gets the data dimension must suitable as partition key. Will first check
- * if any of the dimensions have enough values to satisfy a optimal number of
- * queries, and return that dimension if so. If not returns the dimension
- * with the highest number of values. The order of the fixed dimensions are
- * data element, organisation unit, period.
- *
- * @param params the data query parameters.
- * @param optimalQueries the optimal number of queries to create.
- */
- public static String getPartitionDimension( DataQueryParams params, int optimalQueries )
+ public String getPartitionDimension( DataQueryParams params, int optimalQueries )
{
SortedMap<String, List<String>> map = params.getDimensionValuesMap();
@@ -117,4 +135,112 @@
return params.getLargestDimension();
}
+
+ public boolean canQueryFromDataMart( DataQueryParams params )
+ {
+ return true;
+ }
+
+ // -------------------------------------------------------------------------
+ // Supportive methods
+ // -------------------------------------------------------------------------
+
+ /**
+ * Groups the given query into sub queries based on its periods and which
+ * partition it should be executed against. Sets the partition table name on
+ * each query.
+ */
+ private List<DataQueryParams> groupByPartition( DataQueryParams params )
+ {
+ List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
+
+ ListMap<String, String> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods() );
+
+ for ( String tableName : tablePeriodMap.keySet() )
+ {
+ DataQueryParams query = new DataQueryParams( params );
+ query.setPeriods( tablePeriodMap.get( tableName ) );
+ query.setTableName( tableName );
+ queries.add( query );
+ }
+
+ return queries;
+ }
+
+ /**
+ * Groups the given query into sub queries based on the period type of its
+ * periods. Sets the period type name on each query.
+ */
+ private List<DataQueryParams> groupByPeriodType( DataQueryParams params )
+ {
+ List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
+
+ ListMap<String, String> periodTypePeriodMap = getPeriodTypePeriodMap( params.getPeriods() );
+
+ for ( String periodType : periodTypePeriodMap.keySet() )
+ {
+ DataQueryParams query = new DataQueryParams( params );
+ query.setPeriods( periodTypePeriodMap.get( periodType ) );
+ query.setPeriodType( periodType );
+ queries.add( query );
+ }
+
+ return queries;
+ }
+
+ /**
+ * Groups the given query into sub queries based on the level of its organisation
+ * units. Sets the organisation unit level on each query.
+ */
+ private List<DataQueryParams> groupByOrgUnitLevel( DataQueryParams params )
+ {
+ List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
+
+ ListMap<Integer, String> levelOrgUnitMap = getLevelOrgUnitMap( params.getOrganisationUnits() );
+
+ for ( Integer level : levelOrgUnitMap.keySet() )
+ {
+ DataQueryParams query = new DataQueryParams( params );
+ query.setOrganisationUnits( levelOrgUnitMap.get( level ) );
+ query.setOrganisationUnitLevel( level );
+ queries.add( query );
+ }
+
+ return queries;
+ }
+
+ /**
+ * Creates a mapping between period type name and period for the given periods.
+ */
+ private ListMap<String, String> getPeriodTypePeriodMap( Collection<String> isoPeriods )
+ {
+ ListMap<String, String> map = new ListMap<String, String>();
+
+ for ( String period : isoPeriods )
+ {
+ String periodTypeName = PeriodType.getPeriodTypeFromIsoString( period ).getName();
+
+ map.putValue( periodTypeName, period );
+ }
+
+ return map;
+ }
+
+ /**
+ * Creates a mapping between level and organisation unit for the given organisation
+ * units.
+ */
+ private ListMap<Integer, String> getLevelOrgUnitMap( Collection<String> orgUnits )
+ {
+ ListMap<Integer, String> map = new ListMap<Integer, String>();
+
+ for ( String orgUnit : orgUnits )
+ {
+ int level = organisationUnitService.getLevelOfOrganisationUnit( orgUnit );
+
+ map.putValue( level, orgUnit );
+ }
+
+ return map;
+ }
}
=== 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 2012-12-14 15:04:48 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-17 16:58:29 +0000
@@ -39,10 +39,6 @@
import org.apache.commons.logging.LogFactory;
import org.hisp.dhis.analytics.AnalyticsManager;
import org.hisp.dhis.analytics.DataQueryParams;
-import org.hisp.dhis.expression.ExpressionService;
-import org.hisp.dhis.organisationunit.OrganisationUnitService;
-import org.hisp.dhis.period.PeriodService;
-import org.hisp.dhis.period.PeriodType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
@@ -64,15 +60,6 @@
@Autowired
private JdbcTemplate jdbcTemplate;
- @Autowired
- private OrganisationUnitService organisationUnitService;
-
- @Autowired
- private PeriodService periodService;
-
- @Autowired
- private ExpressionService expressionService;
-
// -------------------------------------------------------------------------
// Implementation
// -------------------------------------------------------------------------
@@ -82,28 +69,26 @@
@Async
public Future<Map<String, Double>> getAggregatedDataValueTotals( DataQueryParams params )
{
- int level = organisationUnitService.getLevelOfOrganisationUnit( params.getOrganisationUnits().iterator().next() );
-
- String periodType = PeriodType.getPeriodTypeFromIsoString( params.getPeriods().iterator().next() ).getName().toLowerCase();
-
- List<String> dimensions = params.getDimensionNames();
+ int level = params.getOrganisationUnitLevel();
+ String periodType = params.getPeriodType();
+ List<String> dimensions = params.getDimensionNames();
List<String> extraDimensions = params.getDynamicDimensionNames();
String sql =
- "SELECT " + dimensions.get( 0 ) + ", " +
+ "select " + dimensions.get( 0 ) + ", " +
dimensions.get( 1 ) + ", " +
periodType + " as " + dimensions.get( 2 ) + ", " +
"uidlevel" + level + " as " + dimensions.get( 3 ) + ", " +
getCommaDelimitedString( extraDimensions, false, true ) +
- "SUM(value) as value " +
+ "sum(value) as value " +
- "FROM " + params.getTableName() + " " +
- "WHERE " + dimensions.get( 0 ) + " IN ( " + getQuotedCommaDelimitedString( params.getDataElements() ) + " ) " +
- "AND " + periodType + " IN ( " + getQuotedCommaDelimitedString( params.getPeriods() ) + " ) " +
- "AND uidlevel" + level + " IN ( " + getQuotedCommaDelimitedString( params.getOrganisationUnits() ) + " ) " +
+ "from " + params.getTableName() + " " +
+ "where " + dimensions.get( 0 ) + " in ( " + getQuotedCommaDelimitedString( params.getDataElements() ) + " ) " +
+ "and " + periodType + " in ( " + getQuotedCommaDelimitedString( params.getPeriods() ) + " ) " +
+ "and uidlevel" + level + " in ( " + getQuotedCommaDelimitedString( params.getOrganisationUnits() ) + " ) " +
getExtraDimensionQuery( params ) +
- "GROUP BY " + dimensions.get( 0 ) + ", " +
+ "group by " + dimensions.get( 0 ) + ", " +
dimensions.get( 1 ) + ", " +
periodType + ", " +
"uidlevel" + level +
@@ -111,7 +96,7 @@
log.info( sql );
- SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql );
+ SqlRowSet rowSet = jdbcTemplate.queryForRowSet( sql.toLowerCase() );
Map<String, Double> map = new HashMap<String, Double>();
@@ -126,7 +111,7 @@
key.deleteCharAt( key.length() - SEP.length() );
- Double value = rowSet.getDouble( "value" );
+ Double value = rowSet.getDouble( DataQueryParams.VALUE_ID );
map.put( key.toString(), value );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2012-12-13 20:45:26 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2012-12-17 16:58:29 +0000
@@ -68,7 +68,7 @@
final Date earliest = tableManager.getEarliestData();
final Date latest = tableManager.getLatestData();
final List<String> tables = PartitionUtils.getTempTableNames( earliest, latest );
- clock.logTime( "Checked data timespan" );
+ clock.logTime( "Checked data timespan and got tables: " + tables );
//dropTables( tables );
@@ -107,11 +107,18 @@
private void populateTables( List<String> tables )
{
int pageSize = Math.max( ( SystemUtils.getCpuCores() - 1 ), 1 );
+
+ log.info( "Page size: " + pageSize );
List<List<String>> tablePages = new PaginatedList<String>( tables ).setPageSize( pageSize ).getPages();
+ log.info( "No of pages: " + tablePages.size() );
+
for ( List<String> tablePage : tablePages )
{
+ System.out.println();
+ System.out.println(tablePage);
+
List<Future<?>> futures = new ArrayList<Future<?>>();
for ( String table : tablePage )
=== 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 2012-12-14 17:27:05 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-17 16:58:29 +0000
@@ -190,7 +190,9 @@
"left join period pe on dv.periodid=pe.periodid " +
"where de.valuetype='" + valueType + "' " +
"and pe.startdate >= '" + start + "' " +
- "and pe.startdate <= '" + end + "'";
+ "and pe.startdate <= '" + end + "'" +
+ "and dv.value != ''" +
+ "and dv.value is not null";
final String sql = insert + select;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2012-12-14 13:46:47 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2012-12-17 16:58:29 +0000
@@ -6,5 +6,6 @@
<bean id="org.hisp.dhis.analytics.AnalyticsTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService" />
<bean id="org.hisp.dhis.analytics.AnalyticsManager" class="org.hisp.dhis.analytics.data.JdbcAnalyticsManager" />
<bean id="org.hisp.dhis.analytics.AnalyticsService" class="org.hisp.dhis.analytics.data.DefaultAnalyticsService" />
+ <bean id="org.hisp.dhis.analytics.QueryPlanner" class="org.hisp.dhis.analytics.data.DefaultQueryPlanner" />
</beans>
=== 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 2012-12-12 15:29:04 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2012-12-17 16:58:29 +0000
@@ -27,17 +27,66 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
+import static org.hisp.dhis.analytics.DataQueryParams.DATAELEMENT_DIM_ID;
+import static org.hisp.dhis.analytics.DataQueryParams.ORGUNIT_DIM_ID;
+import static org.hisp.dhis.analytics.DataQueryParams.PERIOD_DIM_ID;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertTrue;
+
+import java.util.ArrayList;
import java.util.Arrays;
+import java.util.Iterator;
import java.util.List;
+import org.hisp.dhis.DhisSpringTest;
import org.hisp.dhis.analytics.DataQueryParams;
+import org.hisp.dhis.analytics.QueryPlanner;
+import org.hisp.dhis.organisationunit.OrganisationUnit;
+import org.hisp.dhis.organisationunit.OrganisationUnitService;
+import org.hisp.dhis.period.Cal;
+import org.hisp.dhis.period.PeriodType;
import org.junit.Test;
-
-import static org.hisp.dhis.analytics.DataQueryParams.*;
-import static org.junit.Assert.*;
+import org.springframework.beans.factory.annotation.Autowired;
public class QueryPlannerTest
+ extends DhisSpringTest
{
+ @Autowired
+ private QueryPlanner queryPlanner;
+
+ @Autowired
+ private OrganisationUnitService organisationUnitService;
+
+ // -------------------------------------------------------------------------
+ // Fixture
+ // -------------------------------------------------------------------------
+
+ private OrganisationUnit ouA;
+ private OrganisationUnit ouB;
+ private OrganisationUnit ouC;
+ private OrganisationUnit ouD;
+ private OrganisationUnit ouE;
+
+ @Override
+ public void setUpTest()
+ {
+ ouA = createOrganisationUnit( 'A' );
+ ouB = createOrganisationUnit( 'B' );
+ ouC = createOrganisationUnit( 'C' );
+ ouD = createOrganisationUnit( 'D' );
+ ouE = createOrganisationUnit( 'E' );
+
+ organisationUnitService.addOrganisationUnit( ouA );
+ organisationUnitService.addOrganisationUnit( ouB );
+ organisationUnitService.addOrganisationUnit( ouC );
+ organisationUnitService.addOrganisationUnit( ouD );
+ organisationUnitService.addOrganisationUnit( ouE );
+ }
+
+ // -------------------------------------------------------------------------
+ // Tests
+ // -------------------------------------------------------------------------
+
@Test
public void getPartitionDimension()
{
@@ -46,23 +95,160 @@
params.setOrganisationUnits( Arrays.asList( "a", "b", "c", "d", "e" ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3", "2000Q4", "2001Q1", "2001Q2" ) );
- assertEquals( DATAELEMENT_DIM_ID, QueryPlanner.getPartitionDimension( params, 3 ) );
- assertEquals( DATAELEMENT_DIM_ID, QueryPlanner.getPartitionDimension( params, 4 ) );
- assertEquals( ORGUNIT_DIM_ID, QueryPlanner.getPartitionDimension( params, 5 ) );
- assertEquals( PERIOD_DIM_ID, QueryPlanner.getPartitionDimension( params, 6 ) );
- assertEquals( PERIOD_DIM_ID, QueryPlanner.getPartitionDimension( params, 7 ) );
+ assertEquals( DATAELEMENT_DIM_ID, queryPlanner.getPartitionDimension( params, 3 ) );
+ assertEquals( DATAELEMENT_DIM_ID, queryPlanner.getPartitionDimension( params, 4 ) );
+ assertEquals( ORGUNIT_DIM_ID, queryPlanner.getPartitionDimension( params, 5 ) );
+ assertEquals( PERIOD_DIM_ID, queryPlanner.getPartitionDimension( params, 6 ) );
+ assertEquals( PERIOD_DIM_ID, queryPlanner.getPartitionDimension( params, 7 ) );
}
+ /**
+ * Query spans 2 partitions. Splits in 2 queries for each partition, then
+ * splits in 2 queries on data elements to satisfy optimal for a total of 4
+ * queries.
+ */
@Test
- public void planQuery()
+ public void planQueryA()
{
DataQueryParams params = new DataQueryParams();
params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
- params.setOrganisationUnits( Arrays.asList( "a", "b", "c", "d", "e" ) );
+ params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3", "2000Q4", "2001Q1", "2001Q2" ) );
- List<DataQueryParams> queries = QueryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
assertEquals( 4, queries.size() );
+
+ for ( DataQueryParams query : queries )
+ {
+ assertTrue( samePeriodType( query.getPeriods() ) );
+ assertTrue( samePartition( query.getPeriods() ) );
+ }
+ }
+
+ /**
+ * Query spans 3 period types. Splits in 3 queries for each period type, then
+ * splits in 2 queries on data elements to satisfy optimal for a total of 6
+ * queries.
+ */
+ @Test
+ public void planQueryB()
+ {
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
+ params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000", "200002", "200003", "200004" ) );
+
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+
+ assertEquals( 6, queries.size() );
+
+ for ( DataQueryParams query : queries )
+ {
+ assertTrue( samePeriodType( query.getPeriods() ) );
+ assertTrue( samePartition( query.getPeriods() ) );
+ }
+ }
+
+ /**
+ * Query spans 3 organisation unit levels. Splits in 3 queries for each level,
+ * then splits in 2 queries on data elements to satisfy optimal for a total
+ * of 6 queries.
+ */
+ @Test
+ public void planQueryC()
+ {
+ ouB.setParent( ouA );
+ ouC.setParent( ouA );
+ ouD.setParent( ouB );
+ ouE.setParent( ouC );
+ ouA.getChildren().add( ouB );
+ ouA.getChildren().add( ouC );
+ ouD.getChildren().add( ouB );
+ ouC.getChildren().add( ouE );
+ organisationUnitService.updateOrganisationUnit( ouA );
+ organisationUnitService.updateOrganisationUnit( ouB );
+ organisationUnitService.updateOrganisationUnit( ouC );
+ organisationUnitService.updateOrganisationUnit( ouD );
+ organisationUnitService.updateOrganisationUnit( ouE );
+
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( Arrays.asList( "a", "b", "c", "d" ) );
+ params.setOrganisationUnits( Arrays.asList( ouA.getUid(), ouB.getUid(), ouC.getUid(), ouD.getUid(), ouE.getUid() ) );
+ params.setPeriods( Arrays.asList( "2000Q1", "2000Q2", "2000Q3" ) );
+
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+
+ assertEquals( 6, queries.size() );
+
+ for ( DataQueryParams query : queries )
+ {
+ assertTrue( samePeriodType( query.getPeriods() ) );
+ assertTrue( samePartition( query.getPeriods() ) );
+ }
+ }
+
+ /**
+ * Splits on largest dimension which is period.
+ */
+ @Test
+ public void planQueryD()
+ {
+ DataQueryParams params = new DataQueryParams();
+ params.setDataElements( Arrays.asList( "a", "b", "c" ) );
+ params.setOrganisationUnits( Arrays.asList( ouA.getUid() ) );
+ params.setPeriods( Arrays.asList( "200001", "200002", "200003", "200004", "200005", "200006", "200007", "200008", "200009" ) );
+
+ List<DataQueryParams> queries = queryPlanner.planQuery( params, 6 );
+
+ assertEquals( 5, queries.size() );
+
+ for ( DataQueryParams query : queries )
+ {
+ assertTrue( samePeriodType( query.getPeriods() ) );
+ assertTrue( samePartition( query.getPeriods() ) );
+ }
+ }
+
+ // -------------------------------------------------------------------------
+ // Supportive methods
+ // -------------------------------------------------------------------------
+
+ private static boolean samePeriodType( List<String> isoPeriods )
+ {
+ Iterator<String> periods = new ArrayList<String>( isoPeriods ).iterator();
+
+ PeriodType first = PeriodType.getPeriodTypeFromIsoString( periods.next() );
+
+ while ( periods.hasNext() )
+ {
+ PeriodType next = PeriodType.getPeriodTypeFromIsoString( periods.next() );
+
+ if ( !first.equals( next ) )
+ {
+ return false;
+ }
+ }
+
+ return true;
+ }
+
+ private static boolean samePartition( List<String> isoPeriods )
+ {
+ Iterator<String> periods = new ArrayList<String>( isoPeriods ).iterator();
+
+ int year = new Cal().set( PeriodType.getPeriodFromIsoString( periods.next() ).getStartDate() ).getYear();
+
+ while ( periods.hasNext() )
+ {
+ int next = new Cal().set( PeriodType.getPeriodFromIsoString( periods.next() ).getStartDate() ).getYear();
+
+ if ( year != next )
+ {
+ return false;
+ }
+ }
+
+ return true;
}
}
