dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #21403
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 10121: Analytics, handling situations where period is put as filter, and spans multiple partitions/years...
------------------------------------------------------------
revno: 10121
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2013-03-10 17:13:56 +0100
message:
Analytics, handling situations where period is put as filter, and spans multiple partitions/years. Using aggregate funcion in inner union all selects.
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/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/PartitionUtils.java
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/table/PartitionUtilsTest.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 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<String, IdentifiableObject> 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<DataQueryParams> getPartitionFilterParams()
+ {
+ List<DataQueryParams> filters = new ArrayList<DataQueryParams>();
+
+ if ( !filterSpansMultiplePartitions() )
+ {
+ return filters;
+ }
+
+ for ( String tableName : tableNamePeriodMap.keySet() )
+ {
+ List<IdentifiableObject> 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<IdentifiableObject> 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<String, IdentifiableObject> getTableNamePeriodMap()
+ {
+ return tableNamePeriodMap;
+ }
+
+ public void setTableNamePeriodMap( ListMap<String, IdentifiableObject> 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<String, IdentifiableObject> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods(), tableName );
+ ListMap<String, IdentifiableObject> 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<String, IdentifiableObject> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getFilterPeriods(), tableName );
+ ListMap<String, IdentifiableObject> 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<String, IdentifiableObject> getTablePeriodMap( Collection<IdentifiableObject> periods, String tableName )
+ public static ListMap<String, IdentifiableObject> getTableNamePeriodMap( Collection<IdentifiableObject> periods, String tableName )
{
ListMap<String, IdentifiableObject> map = new ListMap<String, IdentifiableObject>();
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<String, IdentifiableObject> map = PartitionUtils.getTablePeriodMap( getList(
+ ListMap<String, IdentifiableObject> map = PartitionUtils.getTableNamePeriodMap( getList(
createPeriod( "2000S1" ), createPeriod( "2000S2" ), createPeriod( "2001S1" ), createPeriod( "2001S2" ), createPeriod( "2002S1" ) ), TABLE_NAME );
assertEquals( 3, map.size() );
