dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #28118
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 14014: Event aggregate analytics. Taking into account which partitions exist before planning aggregate u...
------------------------------------------------------------
revno: 14014
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2014-02-13 15:40:12 +0100
message:
Event aggregate analytics. Taking into account which partitions exist before planning aggregate union query. When doing aggregate query with start/enddate, potentially multiple partitions must be combined with a sql union statement. Now partitions which do not exist are omitted from the statemnet. Fixes issue with a query failing when only some of many partitions do not exist.
modified:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/Partitions.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryPlanner.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventQueryPlanner.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.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/event/data/EventQueryPlannerTest.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/Partitions.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/Partitions.java 2013-10-16 18:47:07 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/Partitions.java 2014-02-13 14:40:12 +0000
@@ -60,21 +60,58 @@
// Logic
// -------------------------------------------------------------------------
+ /**
+ * Adds a partition.
+ */
public Partitions add( String partition )
{
partitions.add( partition );
return this;
}
-
+
+ /**
+ * Indicates whether this instance contains multiple partitions.
+ */
public boolean isMultiple()
{
return partitions != null && partitions.size() > 1;
}
+ /**
+ * Indicates whether this instance has any partitions.
+ */
+ public boolean hasAny()
+ {
+ return partitions != null && !partitions.isEmpty();
+ }
+
+ /**
+ * Returns the first partition of this instance.
+ */
public String getSinglePartition()
{
return partitions.get( 0 );
}
+
+ /**
+ * Prunes this instance so that it retains only the partitions included in
+ * the given list. No operation takes place if the given live is null.
+ *
+ * @param validPartitions list of valid partitions to retain.
+ */
+ public Partitions prunePartitions( List<String> validPartitions )
+ {
+ if ( validPartitions != null )
+ {
+ partitions.retainAll( validPartitions );
+ }
+
+ return this;
+ }
+
+ // -------------------------------------------------------------------------
+ // toString, hashCode, equals
+ // -------------------------------------------------------------------------
@Override
public String toString()
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java 2013-09-30 19:54:38 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventAnalyticsManager.java 2014-02-13 14:40:12 +0000
@@ -28,7 +28,10 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
+import java.util.List;
+
import org.hisp.dhis.common.Grid;
+import org.hisp.dhis.program.Program;
/**
* @author Lars Helge Overland
@@ -40,4 +43,6 @@
Grid getEvents( EventQueryParams params, Grid grid );
int getEventCount( EventQueryParams params );
+
+ public List<String> getAnalyticsTables( Program program );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryPlanner.java 2013-09-29 15:41:22 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/EventQueryPlanner.java 2014-02-13 14:40:12 +0000
@@ -37,9 +37,17 @@
*/
public interface EventQueryPlanner
{
+ final String TABLE_PREFIX = "analytics_event";
+
void validate( EventQueryParams params )
throws IllegalQueryException;
- List<EventQueryParams> planQuery( EventQueryParams params );
-
+ /**
+ * Plans the given params and returns a list of params.
+ *
+ * @param params the query params.
+ * @param validPartitions the list of existing database partition names, only
+ * required for aggregate queries.
+ */
+ List<EventQueryParams> planQuery( EventQueryParams params, List<String> validPartitions );
}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java 2014-02-12 20:18:42 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventAnalyticsService.java 2014-02-13 14:40:12 +0000
@@ -139,6 +139,8 @@
public Grid getAggregatedEventData( EventQueryParams params )
{
queryPlanner.validate( params );
+
+ List<String> validPartitions = analyticsManager.getAnalyticsTables( params.getProgram() );
Grid grid = new ListGrid();
@@ -162,7 +164,7 @@
// Data
// ---------------------------------------------------------------------
- List<EventQueryParams> queries = queryPlanner.planQuery( params );
+ List<EventQueryParams> queries = queryPlanner.planQuery( params, validPartitions );
for ( EventQueryParams query : queries )
{
@@ -222,7 +224,7 @@
Timer t = new Timer().start();
- List<EventQueryParams> queries = queryPlanner.planQuery( params );
+ List<EventQueryParams> queries = queryPlanner.planQuery( params, null );
t.getSplitTime( "Planned query, got: " + queries.size() );
@@ -402,8 +404,7 @@
{
items.add( getItem( program, dimension, null, null ) );
}
- else
- // Filter
+ else // Filter
{
String[] split = dimension.split( DIMENSION_NAME_SEP );
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventQueryPlanner.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventQueryPlanner.java 2013-10-16 20:44:25 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/DefaultEventQueryPlanner.java 2014-02-13 14:40:12 +0000
@@ -58,8 +58,6 @@
{
private static final Log log = LogFactory.getLog( DefaultEventQueryPlanner.class );
- private static final String TABLE_PREFIX = "analytics_event";
-
@Autowired
private QueryPlanner queryPlanner;
@@ -109,11 +107,11 @@
}
}
- public List<EventQueryParams> planQuery( EventQueryParams params )
+ public List<EventQueryParams> planQuery( EventQueryParams params, List<String> validPartitions )
{
List<EventQueryParams> queries = new ArrayList<EventQueryParams>();
- List<EventQueryParams> groupedByPartition = groupByPartition( params );
+ List<EventQueryParams> groupedByPartition = groupByPartition( params, validPartitions );
for ( EventQueryParams byPartition : groupedByPartition )
{
@@ -128,7 +126,7 @@
return queries;
}
- private List<EventQueryParams> groupByPartition( EventQueryParams params )
+ private List<EventQueryParams> groupByPartition( EventQueryParams params, List<String> validPartitions )
{
List<EventQueryParams> queries = new ArrayList<EventQueryParams>();
@@ -145,8 +143,12 @@
queryPeriod.setEndDate( params.getEndDate() );
EventQueryParams query = params.instance();
- query.setPartitions( PartitionUtils.getPartitions( queryPeriod, TABLE_PREFIX, tableSuffix ) );
- queries.add( query );
+ query.setPartitions( PartitionUtils.getPartitions( queryPeriod, TABLE_PREFIX, tableSuffix, validPartitions ) );
+
+ if ( query.getPartitions().hasAny() )
+ {
+ queries.add( query );
+ }
}
else // Event query - split in one query per partition/year
{
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java 2013-12-31 09:28:55 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/event/data/JdbcEventAnalyticsManager.java 2014-02-13 14:40:12 +0000
@@ -36,10 +36,13 @@
import static org.hisp.dhis.system.util.TextUtils.removeLast;
import static org.hisp.dhis.system.util.TextUtils.trimEnd;
+import java.util.List;
+
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hisp.dhis.analytics.event.EventAnalyticsManager;
import org.hisp.dhis.analytics.event.EventQueryParams;
+import org.hisp.dhis.analytics.event.EventQueryPlanner;
import org.hisp.dhis.common.DimensionalObject;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.common.IdentifiableObject;
@@ -47,6 +50,7 @@
import org.hisp.dhis.common.QueryItem;
import org.hisp.dhis.jdbc.StatementBuilder;
import org.hisp.dhis.organisationunit.OrganisationUnit;
+import org.hisp.dhis.program.Program;
import org.hisp.dhis.system.grid.ListGrid;
import org.hisp.dhis.system.util.TextUtils;
import org.hisp.dhis.system.util.Timer;
@@ -288,6 +292,16 @@
return count;
}
+ public List<String> getAnalyticsTables( Program program )
+ {
+ final String sql =
+ "select table_name from information_schema.tables " +
+ "where table_name like '" + EventQueryPlanner.TABLE_PREFIX + "_%_" + program.getUid().toLowerCase() + "' " +
+ "and table_type = 'BASE TABLE'";
+
+ return jdbcTemplate.queryForList( sql, String.class );
+ }
+
// -------------------------------------------------------------------------
// Supportive methods
// -------------------------------------------------------------------------
=== 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-10-16 20:37:25 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java 2014-02-13 14:40:12 +0000
@@ -69,7 +69,7 @@
//TODO optimize by including required filter periods only
- public static Partitions getPartitions( Period period, String tablePrefix, String tableSuffix )
+ public static Partitions getPartitions( Period period, String tablePrefix, String tableSuffix, List<String> validPartitions )
{
tablePrefix = StringUtils.trimToEmpty( tablePrefix );
tableSuffix = StringUtils.trimToEmpty( tableSuffix );
@@ -81,11 +81,12 @@
while ( startYear <= endYear )
{
- partitions.add( tablePrefix + SEP + startYear + tableSuffix );
+ String name = tablePrefix + SEP + startYear + tableSuffix;
+ partitions.add( name.toLowerCase() );
startYear++;
}
- return partitions;
+ return partitions.prunePartitions( validPartitions );
}
public static Partitions getPartitions( List<NameableObject> periods, String tablePrefix, String tableSuffix )
@@ -94,7 +95,7 @@
for ( NameableObject period : periods )
{
- partitions.addAll( getPartitions( (Period) period, tablePrefix, tableSuffix ).getPartitions() );
+ partitions.addAll( getPartitions( (Period) period, tablePrefix, tableSuffix, null ).getPartitions() );
}
return new Partitions( new ArrayList<String>( partitions ) );
@@ -106,7 +107,7 @@
for ( NameableObject period : periods )
{
- map.putValue( getPartitions( (Period) period, tablePrefix, tableSuffix ), period );
+ map.putValue( getPartitions( (Period) period, tablePrefix, tableSuffix, null ), period );
}
return map;
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java 2013-10-16 18:47:07 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/event/data/EventQueryPlannerTest.java 2014-02-13 14:40:12 +0000
@@ -77,7 +77,7 @@
params.setEndDate( new Cal( 2012, 3, 20 ).time() );
params.setOrganisationUnits( Arrays.asList( ouA ) );
- List<EventQueryParams> queries = queryPlanner.planQuery( params );
+ List<EventQueryParams> queries = queryPlanner.planQuery( params, null );
assertEquals( 3, queries.size() );
@@ -102,7 +102,7 @@
params.setEndDate( new Cal( 2010, 9, 20 ).time() );
params.setOrganisationUnits( Arrays.asList( ouA ) );
- List<EventQueryParams> queries = queryPlanner.planQuery( params );
+ List<EventQueryParams> queries = queryPlanner.planQuery( params, null );
assertEquals( 1, queries.size() );
@@ -121,7 +121,7 @@
params.setEndDate( new Cal( 2012, 3, 20 ).time() );
params.setOrganisationUnits( Arrays.asList( ouA, ouB ) );
- List<EventQueryParams> queries = queryPlanner.planQuery( params );
+ List<EventQueryParams> queries = queryPlanner.planQuery( params, null );
assertEquals( 6, queries.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-10-16 20:37:25 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java 2014-02-13 14:40:12 +0000
@@ -54,13 +54,13 @@
@Test
public void testGetPartitions()
{
- assertEquals( new Partitions().add( TBL + "_2000" ), PartitionUtils.getPartitions( createPeriod( "200001" ), TBL, null ) );
- assertEquals( new Partitions().add( TBL + "_2001" ), PartitionUtils.getPartitions( createPeriod( "200110" ), TBL, null ) );
- assertEquals( new Partitions().add( TBL + "_2002" ), PartitionUtils.getPartitions( createPeriod( "2002Q2" ), TBL, null ) );
- assertEquals( new Partitions().add( TBL + "_2003" ), PartitionUtils.getPartitions( createPeriod( "2003S2" ), TBL, null ) );
+ assertEquals( new Partitions().add( TBL + "_2000" ), PartitionUtils.getPartitions( createPeriod( "200001" ), TBL, null, null ) );
+ assertEquals( new Partitions().add( TBL + "_2001" ), PartitionUtils.getPartitions( createPeriod( "200110" ), TBL, null, null ) );
+ assertEquals( new Partitions().add( TBL + "_2002" ), PartitionUtils.getPartitions( createPeriod( "2002Q2" ), TBL, null, null ) );
+ assertEquals( new Partitions().add( TBL + "_2003" ), PartitionUtils.getPartitions( createPeriod( "2003S2" ), TBL, null, null ) );
- assertEquals( new Partitions().add( TBL + "_2000" ).add( TBL + "_2001" ), PartitionUtils.getPartitions( createPeriod( "2000July" ), TBL, null ) );
- assertEquals( new Partitions().add( TBL + "_2001" ).add( TBL + "_2002" ), PartitionUtils.getPartitions( createPeriod( "2001April" ), TBL, null ) );
+ assertEquals( new Partitions().add( TBL + "_2000" ).add( TBL + "_2001" ), PartitionUtils.getPartitions( createPeriod( "2000July" ), TBL, null, null ) );
+ assertEquals( new Partitions().add( TBL + "_2001" ).add( TBL + "_2002" ), PartitionUtils.getPartitions( createPeriod( "2001April" ), TBL, null, null ) );
}
@Test
@@ -83,7 +83,7 @@
Partitions expected = new Partitions().add( TBL + "_2008" ).add( TBL + "_2009" ).add( TBL + "_2010" ).add( TBL + "_2011" );
- assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null ) );
+ assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null, null ) );
period = new Period();
period.setStartDate( new Cal( 2009, 8, 1 ).time() );
@@ -91,9 +91,29 @@
expected = new Partitions().add( TBL + "_2009" ).add( TBL + "_2010" );
- assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null ) );
- }
-
+ assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null, null ) );
+ }
+
+ @Test
+ public void getGetPartitionsLongPeriodsPrune()
+ {
+ Period period = new Period();
+ period.setStartDate( new Cal( 2008, 3, 1 ).time() );
+ period.setEndDate( new Cal( 2011, 7, 1 ).time() );
+
+ Partitions expected = new Partitions().add( TBL + "_2008" ).add( TBL + "_2009" ).add( TBL + "_2010" ).add( TBL + "_2011" );
+
+ assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null, null ) );
+
+ List<String> validPartitions = new ArrayList<String>();
+ validPartitions.add( TBL + "_2008" );
+ validPartitions.add( TBL + "_2010" );
+
+ expected = new Partitions().add( TBL + "_2008" ).add( TBL + "_2010" );
+
+ assertEquals( expected, PartitionUtils.getPartitions( period, TBL, null, validPartitions ) );
+ }
+
@Test
public void testGetTablePeriodMapA()
{