← Back to team overview

dhis2-devs team mailing list archive

[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()
     {