=== 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 2013-03-10 14:26:48 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2013-03-10 16:13:56 +0000 @@ -93,6 +93,8 @@ private transient String tableName; + private ListMap tableNamePeriodMap; + private transient String periodType; private transient PeriodType dataPeriodType; @@ -118,6 +120,7 @@ this.periodType = params.getPeriodType(); this.dataPeriodType = params.getDataPeriodType(); this.skipPartitioning = params.isSkipPartitioning(); + this.tableNamePeriodMap = params.getTableNamePeriodMap(); } // ------------------------------------------------------------------------- @@ -142,6 +145,44 @@ } /** + * Indicates whether the filters of this query spans more than one partition. + * If true it means that a period filter exists and that the periods span + * multiple years. + */ + public boolean filterSpansMultiplePartitions() + { + return tableNamePeriodMap != null && !tableNamePeriodMap.isEmpty(); + } + + /** + * If the filters of this query spans more than partition, this method will + * return a list of queries with a query for each partition, generated from + * this query, where the table name and filter period items are set according + * to the relevant partition. + */ + public List getPartitionFilterParams() + { + List filters = new ArrayList(); + + if ( !filterSpansMultiplePartitions() ) + { + return filters; + } + + for ( String tableName : tableNamePeriodMap.keySet() ) + { + List periods = tableNamePeriodMap.get( tableName ); + + DataQueryParams params = new DataQueryParams( this ); + params.setTableName( tableName ); + params.updateFilterOptions( PERIOD_DIM_ID, periods ); + filters.add( params ); + } + + return filters; + } + + /** * Creates a mapping between dimension identifiers and filter dimensions. Filters * are guaranteed not to be null. */ @@ -575,6 +616,23 @@ return this; } + /** + * Updates the options for the given filter. + */ + public DataQueryParams updateFilterOptions( String filter, List options ) + { + int index = filters.indexOf( new Dimension( filter ) ); + + if ( index != -1 ) + { + Dimension existing = filters.get( index ); + + filters.set( index, new Dimension( existing.getDimension(), existing.getType(), existing.getDimensionName(), options ) ); + } + + return this; + } + // ------------------------------------------------------------------------- // Static methods // ------------------------------------------------------------------------- @@ -810,6 +868,16 @@ this.tableName = tableName; } + public ListMap getTableNamePeriodMap() + { + return tableNamePeriodMap; + } + + public void setTableNamePeriodMap( ListMap tableNamePeriodMap ) + { + this.tableNamePeriodMap = tableNamePeriodMap; + } + public String getPeriodType() { return periodType; === 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 2013-03-10 14:13:41 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2013-03-10 16:13:56 +0000 @@ -282,27 +282,23 @@ } else if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) { - ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods(), tableName ); + ListMap tableNamePeriodMap = PartitionUtils.getTableNamePeriodMap( params.getPeriods(), tableName ); - for ( String table : tablePeriodMap.keySet() ) + for ( String table : tableNamePeriodMap.keySet() ) { DataQueryParams query = new DataQueryParams( params ); - query.setPeriods( tablePeriodMap.get( table ) ); + query.setPeriods( tableNamePeriodMap.get( table ) ); query.setTableName( table ); queries.add( query ); } } - else if ( params.getFilterPeriods() != null && !params.getFilterPeriods().isEmpty() ) //TODO fix + else if ( params.getFilterPeriods() != null && !params.getFilterPeriods().isEmpty() ) { - ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getFilterPeriods(), tableName ); + ListMap tableNamePeriodMap = PartitionUtils.getTableNamePeriodMap( params.getFilterPeriods(), tableName ); - for ( String table : tablePeriodMap.keySet() ) - { - DataQueryParams query = new DataQueryParams( params ); - query.setFilterPeriods( tablePeriodMap.get( table ) ); - query.setTableName( table ); - queries.add( query ); - } + DataQueryParams query = new DataQueryParams( params ); + query.setTableNamePeriodMap( tableNamePeriodMap ); + queries.add( query ); } else { === 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 2013-03-10 14:53:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2013-03-10 16:13:56 +0000 @@ -40,6 +40,7 @@ import static org.hisp.dhis.analytics.MeasureFilter.LT; import static org.hisp.dhis.common.IdentifiableObjectUtils.getUids; import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; +import static org.hisp.dhis.system.util.TextUtils.trimEnd; import java.util.Collection; import java.util.HashMap; @@ -100,7 +101,14 @@ String sql = getSelectClause( params ); - sql += getFromWhereClause( params ); + if ( params.filterSpansMultiplePartitions() ) + { + sql += getFromWhereClauseMultiplePartitionFilters( params ); + } + else + { + sql += getFromWhereClause( params ); + } sql += getGroupByClause( params ); @@ -197,6 +205,28 @@ } /** + * Generates the from clause of the SQL query. This method should be used for + * queries where the period filter spans multiple partitions. + */ + private String getFromWhereClauseMultiplePartitionFilters( DataQueryParams params ) + { + String sql = "from ("; + + for ( DataQueryParams filterParams : params.getPartitionFilterParams() ) + { + sql += "select " + getCommaDelimitedString( filterParams.getQueryDimensions() ) + ", value as value "; + + sql += getFromWhereClause( filterParams ); + + sql += "union all "; + } + + sql = trimEnd( sql, "union all ".length() ) + ") as data "; + + return sql; + } + + /** * Generates the from clause of the query SQL. */ private String getFromWhereClause( DataQueryParams params ) @@ -232,7 +262,7 @@ } } - sql = sql.substring( 0, sql.length() - " or ".length() ) + ") "; + sql = trimEnd( sql, " or ".length() ) + ") "; } return sql; === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java 2013-02-26 18:33:01 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java 2013-03-10 16:13:56 +0000 @@ -74,11 +74,11 @@ return tables; } - public static String getTable( Period period, String tableName ) + public static String getTableName( Period period, String tableName ) { - Period quarter = PERIODTYPE.createPeriod( period.getStartDate() ); + Period year = PERIODTYPE.createPeriod( period.getStartDate() ); - return tableName + SEP + quarter.getIsoDate(); + return tableName + SEP + year.getIsoDate(); } public static Period getPeriod( String tableName ) @@ -94,13 +94,13 @@ return PeriodType.getPeriodFromIsoString( isoPeriod ); } - public static ListMap getTablePeriodMap( Collection periods, String tableName ) + public static ListMap getTableNamePeriodMap( Collection periods, String tableName ) { ListMap map = new ListMap(); for ( IdentifiableObject period : periods ) { - map.putValue( getTable( (Period) period, tableName ), period ); + map.putValue( getTableName( (Period) period, tableName ), period ); } return map; === 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 2013-03-09 12:34:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2013-03-10 16:13:56 +0000 @@ -517,9 +517,8 @@ } /** - * Query filters span 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. + * Query filters span 2 partitions. Splits in 4 queries on data elements to + * satisfy optimal for a total of 4 queries. */ @Test public void planQueryH() @@ -535,9 +534,11 @@ for ( DataQueryParams query : queries ) { - assertTrue( samePeriodType( query.getFilterPeriods() ) ); - assertTrue( samePartition( query.getFilterPeriods() ) ); assertDimensionNameNotNull( query ); + + assertTrue( query.filterSpansMultiplePartitions() ); + assertEquals( 2, query.getTableNamePeriodMap().size() ); + assertEquals( 2, query.getPartitionFilterParams().size() ); } } === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java' --- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java 2013-02-26 18:33:01 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java 2013-03-10 16:13:56 +0000 @@ -67,10 +67,10 @@ @Test public void testGetTable() { - assertEquals( TABLE_NAME + "_2000", PartitionUtils.getTable( createPeriod( "200011" ), TABLE_NAME ) ); - assertEquals( TABLE_NAME + "_2001", PartitionUtils.getTable( createPeriod( "2001W02" ), TABLE_NAME ) ); - assertEquals( TABLE_NAME + "_2002", PartitionUtils.getTable( createPeriod( "2002Q2" ), TABLE_NAME ) ); - assertEquals( TABLE_NAME + "_2003", PartitionUtils.getTable( createPeriod( "2003S2" ), TABLE_NAME ) ); + assertEquals( TABLE_NAME + "_2000", PartitionUtils.getTableName( createPeriod( "200011" ), TABLE_NAME ) ); + assertEquals( TABLE_NAME + "_2001", PartitionUtils.getTableName( createPeriod( "2001W02" ), TABLE_NAME ) ); + assertEquals( TABLE_NAME + "_2002", PartitionUtils.getTableName( createPeriod( "2002Q2" ), TABLE_NAME ) ); + assertEquals( TABLE_NAME + "_2003", PartitionUtils.getTableName( createPeriod( "2003S2" ), TABLE_NAME ) ); } @Test @@ -88,7 +88,7 @@ @Test public void testGetTablePeriodMap() { - ListMap map = PartitionUtils.getTablePeriodMap( getList( + ListMap map = PartitionUtils.getTableNamePeriodMap( getList( createPeriod( "2000S1" ), createPeriod( "2000S2" ), createPeriod( "2001S1" ), createPeriod( "2001S2" ), createPeriod( "2002S1" ) ), TABLE_NAME ); assertEquals( 3, map.size() );