=== 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 indicators = new ArrayList(); @@ -59,9 +60,17 @@ private Map> dimensions = new HashMap>(); 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> getDimensionValuesMap() { SortedMap> map = new TreeMap>(); - + + 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 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 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 entry : map.entrySet() ) { @@ -83,7 +90,7 @@ { Timer t = new Timer().start(); - List queries = QueryPlanner.planQuery( params, 6 ); + List 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 planQuery( DataQueryParams params, int optimalQueries ) + @Autowired + private OrganisationUnitService organisationUnitService; + + // ------------------------------------------------------------------------- + // DefaultQueryPlanner implementation + // ------------------------------------------------------------------------- + + public List planQuery( DataQueryParams params, int optimalQueries ) { + // --------------------------------------------------------------------- + // Group queries by partition, period type and organisation unit level + // --------------------------------------------------------------------- + params = new DataQueryParams( params ); List queries = new ArrayList(); - - ListMap 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 partitionValues = params.getDimension( dimension ); - - List> partitionValuePages = new PaginatedList( partitionValues ).setNumberOfPages( pages ).getPages(); + + List groupedByPartition = groupByPartition( params ); + + for ( DataQueryParams byPartition : groupedByPartition ) + { + List groupedByPeriodType = groupByPeriodType( byPartition ); - for ( List valuePage : partitionValuePages ) + for ( DataQueryParams byPeriodType : groupedByPeriodType ) + { + List 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 subQueries = new ArrayList(); + + Double pages = Math.ceil( (double) optimalQueries / queries.size() ); + + int optimal = pages.intValue(); + + for ( DataQueryParams query : queries ) + { + String dimension = getPartitionDimension( query, optimal ); + + List partitionValues = params.getDimension( dimension ); + + List> partitionValuePages = new PaginatedList( partitionValues ).setNumberOfPages( optimal ).getPages(); + + for ( List 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> 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 groupByPartition( DataQueryParams params ) + { + List queries = new ArrayList(); + + ListMap 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 groupByPeriodType( DataQueryParams params ) + { + List queries = new ArrayList(); + + ListMap 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 groupByOrgUnitLevel( DataQueryParams params ) + { + List queries = new ArrayList(); + + ListMap 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 getPeriodTypePeriodMap( Collection isoPeriods ) + { + ListMap map = new ListMap(); + + 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 getLevelOrgUnitMap( Collection orgUnits ) + { + ListMap map = new ListMap(); + + 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> getAggregatedDataValueTotals( DataQueryParams params ) { - int level = organisationUnitService.getLevelOfOrganisationUnit( params.getOrganisationUnits().iterator().next() ); - - String periodType = PeriodType.getPeriodTypeFromIsoString( params.getPeriods().iterator().next() ).getName().toLowerCase(); - - List dimensions = params.getDimensionNames(); + int level = params.getOrganisationUnitLevel(); + String periodType = params.getPeriodType(); + List dimensions = params.getDimensionNames(); List 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 map = new HashMap(); @@ -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 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 tables ) { int pageSize = Math.max( ( SystemUtils.getCpuCores() - 1 ), 1 ); + + log.info( "Page size: " + pageSize ); List> tablePages = new PaginatedList( tables ).setPageSize( pageSize ).getPages(); + log.info( "No of pages: " + tablePages.size() ); + for ( List tablePage : tablePages ) { + System.out.println(); + System.out.println(tablePage); + List> futures = new ArrayList>(); 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 @@ + === 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 queries = QueryPlanner.planQuery( params, 4 ); + List 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 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 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 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 isoPeriods ) + { + Iterator periods = new ArrayList( 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 isoPeriods ) + { + Iterator periods = new ArrayList( 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; } }