=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2014-06-04 16:18:39 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2015-01-06 21:00:39 +0000 @@ -47,21 +47,12 @@ public static final String EVENT_ANALYTICS_TABLE_NAME = "analytics_event"; /** - * Returns analytics tables which yearly partitions. Yearly partitions will - * be generated starting from the earliest existing data value until the - * latest existing data value. - * - * @param lastYears the number of last years of data to include, null if all years. - */ - List getTables( Integer lastYears ); - - /** * Returns analytics tables which yearly partitions. * * @param earliest the start date for the first year to generate table partitions. * @param latest the end date for the last year to generate table partitions. */ - List getTables( Date earliest, Date latest ); + List getTables( Date earliest ); /** * Checks if the database content is in valid state for analytics table generation. @@ -111,22 +102,11 @@ Future populateTableAsync( ConcurrentLinkedQueue tables ); /** - * Retrieves the start date of the period of the earliest data value row. - */ - Date getEarliestData(); - - /** - * Retrieves the end date of the period of the latest data value row. - */ - Date getLatestData(); - - /** - * Checks whether the given table has no rows, if so drops the table. Returns - * true if the table was empty and pruned, if not false. + * Returns all years for which it exists data values. * - * @param table the analytics table. + * @param earliest the earliest date to include as data year, null if no restriction. */ - boolean pruneTable( AnalyticsTable table ); + List getDataYears( Date earliest ); /** * Drops the given table. === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2014-11-11 03:58:32 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2015-01-06 21:00:39 +0000 @@ -29,7 +29,7 @@ */ import java.util.ArrayList; -import java.util.Calendar; +import java.util.Collections; import java.util.Date; import java.util.List; import java.util.Set; @@ -49,13 +49,11 @@ import org.hisp.dhis.jdbc.StatementBuilder; import org.hisp.dhis.organisationunit.OrganisationUnitGroupService; import org.hisp.dhis.organisationunit.OrganisationUnitService; -import org.hisp.dhis.period.Cal; import org.hisp.dhis.period.Period; import org.hisp.dhis.setting.SystemSettingManager; import org.hisp.dhis.system.timer.SystemTimer; import org.hisp.dhis.system.timer.Timer; import org.hisp.dhis.system.util.ListUtils; -import org.joda.time.DateTime; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.BadSqlGrammarException; import org.springframework.jdbc.core.JdbcTemplate; @@ -74,9 +72,6 @@ public static final String PREFIX_ORGUNITLEVEL = "uidlevel"; public static final String PREFIX_INDEX = "in_"; - private static Date MIN_EARLIEST_DATE = new DateTime( 1800, 1, 1, 0, 0 ).toDate(); - private static Date MAX_LATEST_DATE = new DateTime( 2100, 1, 1, 0, 0 ).toDate(); - @Autowired protected OrganisationUnitService organisationUnitService; @@ -124,45 +119,23 @@ @Override @Transactional - public List getTables( Integer lastYears ) - { - Date earliest; - - if ( lastYears != null ) - { - earliest = new Cal().now().subtract( Calendar.YEAR, ( lastYears - 1 ) ).set( 1, 1 ).time(); - } - else - { - earliest = getEarliestData(); - } - - Date latest = getLatestData(); - - earliest = earliest != null && earliest.before( MIN_EARLIEST_DATE ) ? MIN_EARLIEST_DATE : earliest; - latest = latest != null && latest.after( MAX_LATEST_DATE ) ? MAX_LATEST_DATE : latest; - - return getTables( earliest, latest ); - } - - @Override - @Transactional - public List getTables( Date earliest, Date latest ) - { - log.info( "Get tables using earliest: " + earliest + ", latest: " + latest ); + public List getTables( Date earliest ) + { + log.info( "Get tables using earliest: " + earliest ); List tables = new ArrayList<>(); - if ( earliest != null && latest != null ) - { - String baseName = getTableName(); + List dataYears = getDataYears( earliest ); + + Collections.sort( dataYears ); + + String baseName = getTableName(); + + for ( Integer year : dataYears ) + { + Period period = PartitionUtils.getPeriod( year ); - List periods = PartitionUtils.getPeriods( earliest, latest ); - - for ( Period period : periods ) - { - tables.add( new AnalyticsTable( baseName, getDimensionColumns( null ), period ) ); - } + tables.add( new AnalyticsTable( baseName, getDimensionColumns( null ), period ) ); } return tables; @@ -217,25 +190,6 @@ } @Override - public boolean pruneTable( AnalyticsTable table ) - { - String tableName = table.getTempTableName(); - - if ( !hasRows( tableName ) ) - { - final String sqlDrop = "drop table " + tableName; - - executeSilently( sqlDrop ); - - log.info( "Drop SQL: " + sqlDrop ); - - return true; - } - - return false; - } - - @Override public void dropTable( String tableName ) { final String realTable = tableName.replaceFirst( TABLE_TEMP_SUFFIX, "" ); @@ -345,7 +299,7 @@ */ protected void populateAndLog( String sql, String tableName ) { - log.info( "Populate SQL for " + tableName + ": " + sql ); + log.info( "Populating " + tableName ); Timer t = new SystemTimer().start(); === 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 2014-12-15 18:08:16 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2015-01-06 21:00:39 +0000 @@ -30,7 +30,7 @@ import java.util.ArrayList; import java.util.Collection; -import java.util.Iterator; +import java.util.Date; import java.util.List; import java.util.concurrent.ConcurrentLinkedQueue; import java.util.concurrent.Future; @@ -111,7 +111,9 @@ return; } - final List tables = tableManager.getTables( lastYears ); + Date earliest = PartitionUtils.getEarliestDate( lastYears ); + + final List tables = tableManager.getTables( earliest ); clock.logTime( "Partition tables: " + tables + ", last years: " + lastYears ); notifier.notify( taskId, "Creating analytics tables, processes: " + processNo + ", org unit levels: " + orgUnitLevelNo ); @@ -124,11 +126,6 @@ populateTables( tables ); clock.logTime( "Populated analytics tables" ); - notifier.notify( taskId, "Pruned analytics tables" ); - - pruneTables( tables ); - - clock.logTime( "Pruned analytics tables" ); notifier.notify( taskId, "Applying aggregation levels" ); applyAggregationLevels( tables ); @@ -216,20 +213,7 @@ ConcurrentUtils.waitForCompletion( futures ); } - - private void pruneTables( List tables ) - { - Iterator iterator = tables.iterator(); - while ( iterator.hasNext() ) - { - if ( tableManager.pruneTable( iterator.next() ) ) - { - iterator.remove(); - } - } - } - private void applyAggregationLevels( List tables ) { int maxLevels = organisationUnitService.getMaxOfOrganisationUnitLevels(); === 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 2014-12-15 18:08:16 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2015-01-06 21:00:39 +0000 @@ -341,23 +341,20 @@ } @Override - public Date getEarliestData() - { - final String sql = "select min(pe.startdate) from datavalue dv " + - "join period pe on dv.periodid=pe.periodid " + - "where pe.startdate is not null"; - - return jdbcTemplate.queryForObject( sql, Date.class ); - } - - @Override - public Date getLatestData() - { - final String sql = "select max(pe.enddate) from datavalue dv " + - "join period pe on dv.periodid=pe.periodid " + - "where pe.enddate is not null "; - - return jdbcTemplate.queryForObject( sql, Date.class ); + public List getDataYears( Date earliest ) + { + String sql = + "select distinct(extract(year from pe.startdate)) " + + "from datavalue dv " + + "inner join period pe on dv.periodid=pe.periodid " + + "where pe.startdate is not null "; + + if ( earliest != null ) + { + sql += "and pe.startdate >= '" + DateUtils.getMediumDateString( earliest ) + "'"; + } + + return jdbcTemplate.queryForList( sql, Integer.class ); } @Override === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java 2014-11-03 01:54:15 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java 2015-01-06 21:00:39 +0000 @@ -192,25 +192,22 @@ } @Override - public Date getEarliestData() + public List getDataYears( Date earliest ) { - final String sql = "select min(pe.startdate) from completedatasetregistration cdr " + - "join period pe on cdr.periodid=pe.periodid " + + String sql = + "select distinct(extract(year from pe.startdate)) " + + "from completedatasetregistration cdr " + + "inner join period pe on cdr.periodid=pe.periodid " + "where pe.startdate is not null"; - - return jdbcTemplate.queryForObject( sql, Date.class ); - } - - @Override - public Date getLatestData() - { - final String sql = "select max(pe.enddate) from completedatasetregistration cdr " + - "join period pe on cdr.periodid=pe.periodid " + - "where pe.enddate is not null"; - - return jdbcTemplate.queryForObject( sql, Date.class ); - } - + + if ( earliest != null ) + { + sql += "and pe.startdate >= '" + DateUtils.getMediumDateString( earliest ) + "'"; + } + + return jdbcTemplate.queryForList( sql, Integer.class ); + } + @Override @Async public Future applyAggregationLevels( ConcurrentLinkedQueue tables, Collection dataElements, int aggregationLevel ) === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2014-11-03 01:54:15 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTargetTableManager.java 2015-01-06 21:00:39 +0000 @@ -49,7 +49,7 @@ { @Override @Transactional - public List getTables( Integer lastYears ) + public List getTables( Date earliest ) { List tables = new ArrayList<>(); tables.add( new AnalyticsTable( getTableName(), getDimensionColumns( null ) ) ); @@ -173,17 +173,11 @@ } @Override - public Date getEarliestData() - { - return null; // Not relevant - } - - @Override - public Date getLatestData() - { - return null; // Not relevant - } - + public List getDataYears( Date earliest ) + { + return null; // Not relevant + } + @Override @Async public Future applyAggregationLevels( ConcurrentLinkedQueue tables, Collection dataElements, int aggregationLevel ) === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 2014-11-03 01:54:15 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcEventAnalyticsTableManager.java 2015-01-06 21:00:39 +0000 @@ -33,6 +33,7 @@ import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; +import java.util.Collections; import java.util.Date; import java.util.List; import java.util.concurrent.ConcurrentLinkedQueue; @@ -68,27 +69,32 @@ @Override @Transactional - public List getTables( Date earliest, Date latest ) + public List getTables( Date earliest ) { - log.info( "Get tables using earliest: " + earliest + ", latest: " + latest ); + log.info( "Get tables using earliest: " + earliest ); List tables = new ArrayList<>(); - if ( earliest != null && latest != null ) + List dataYears = getDataYears( earliest ); + + Collections.sort( dataYears ); + + String baseName = getTableName(); + + for ( Integer year : dataYears ) { - String baseName = getTableName(); - - List periods = PartitionUtils.getPeriods( earliest, latest ); - - for ( Period period : periods ) + Period period = PartitionUtils.getPeriod( year ); + + List programs = getDataPrograms( period ); + + for ( Integer id : programs ) { - for ( Program program : programService.getAllPrograms() ) - { - AnalyticsTable table = new AnalyticsTable( baseName, null, period, program ); - List dimensionColumns = getDimensionColumns( table ); - table.setDimensionColumns( dimensionColumns ); - tables.add( table ); - } + Program program = programService.getProgram( id ); + + AnalyticsTable table = new AnalyticsTable( baseName, null, period, program ); + List dimensionColumns = getDimensionColumns( table ); + table.setDimensionColumns( dimensionColumns ); + tables.add( table ); } } @@ -286,23 +292,38 @@ } @Override - public Date getEarliestData() - { - final String sql = "select min(psi.executiondate) from programstageinstance psi " - + "where psi.executiondate is not null"; - - return jdbcTemplate.queryForObject( sql, Date.class ); - } - - @Override - public Date getLatestData() - { - final String sql = "select max(psi.executiondate) from programstageinstance psi " - + "where psi.executiondate is not null"; - - return jdbcTemplate.queryForObject( sql, Date.class ); - } - + public List getDataYears( Date earliest ) + { + String sql = + "select distinct(extract(year from psi.executiondate)) " + + "from programstageinstance psi " + + "where psi.executiondate is not null"; + + if ( earliest != null ) + { + sql += "and psi.executiondate >= '" + DateUtils.getMediumDateString( earliest ) + "'"; + } + + return jdbcTemplate.queryForList( sql, Integer.class ); + } + + private List getDataPrograms( Period period ) + { + final String start = DateUtils.getMediumDateString( period.getStartDate() ); + final String end = DateUtils.getMediumDateString( period.getEndDate() ); + + final String sql = + "select distinct pi.programid " + + "from programstageinstance psi " + + "inner join programinstance pi on psi.programinstanceid = pi.programinstanceid " + + "where psi.executiondate >= '" + start + "' " + + "and psi.executiondate <= '" + end + "' " + + "and psi.organisationunitid is not null " + + "and psi.executiondate is not null"; + + return jdbcTemplate.queryForList( sql, Integer.class ); + } + @Override @Async public Future applyAggregationLevels( ConcurrentLinkedQueue tables, === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcOrgUnitTargetTableManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcOrgUnitTargetTableManager.java 2014-11-03 01:54:15 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcOrgUnitTargetTableManager.java 2015-01-06 21:00:39 +0000 @@ -48,7 +48,7 @@ { @Override @Transactional - public List getTables( Integer lastYears ) + public List getTables( Date earliest ) { List tables = new ArrayList<>(); tables.add( new AnalyticsTable( getTableName(), getDimensionColumns( null ) ) ); @@ -163,17 +163,11 @@ } @Override - public Date getEarliestData() - { - return null; // Not relevant - } - - @Override - public Date getLatestData() - { - return null; // Not relevant - } - + public List getDataYears( Date earliest ) + { + return null; // Not relevant + } + @Override @Async public Future applyAggregationLevels( ConcurrentLinkedQueue tables, Collection dataElements, int aggregationLevel ) === 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 2014-09-22 09:56:45 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java 2015-01-06 21:00:39 +0000 @@ -29,6 +29,7 @@ */ import java.util.ArrayList; +import java.util.Calendar; import java.util.Collection; import java.util.Date; import java.util.List; @@ -43,6 +44,7 @@ import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.period.YearlyPeriodType; import org.hisp.dhis.system.util.UniqueArrayList; +import org.joda.time.DateTime; /** * @author Lars Helge Overland @@ -52,22 +54,26 @@ private static final YearlyPeriodType PERIODTYPE = new YearlyPeriodType(); private static final String SEP = "_"; - - public static List getPeriods( Date earliest, Date latest ) - { - List periods = new ArrayList<>(); - - Period period = PERIODTYPE.createPeriod( earliest ); - - while ( period != null && period.getStartDate().before( latest ) ) + + public static Period getPeriod( Integer year ) + { + DateTime time = new DateTime( year, 1, 1, 0, 0 ); + + return PERIODTYPE.createPeriod( time.toDate() ); + } + + public static Date getEarliestDate( Integer lastYears ) + { + Date earliest = null; + + if ( lastYears != null ) { - periods.add( period ); - period = PERIODTYPE.getNextPeriod( period ); + earliest = new Cal().now().subtract( Calendar.YEAR, ( lastYears - 1 ) ).set( 1, 1 ).time(); } - - return periods; + + return earliest; } - + //TODO optimize by including required filter periods only public static Partitions getPartitions( Period period, String tablePrefix, String tableSuffix, Set validPartitions )