=== 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 2012-12-22 18:09:46 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2013-01-17 17:49:13 +0000 @@ -34,10 +34,20 @@ public interface AnalyticsTableManager { - public static final String TABLE_NAME = "analytics"; public static final String TABLE_TEMP_SUFFIX = "_temp"; - public static final String TABLE_NAME_TEMP = TABLE_NAME + TABLE_TEMP_SUFFIX; - + public static final String ANALYTICS_TABLE_NAME = "analytics"; + public static final String COMPLETENESS_TABLE_NAME = "completeness"; + + /** + * Returns the base table name. + */ + String getTableName(); + + /** + * Returns the temporary table name. + */ + String getTempTableName(); + /** * Attempts to drop and then create analytics table. * @@ -74,8 +84,12 @@ /** * Returns a list of string arrays in where the first index holds the database - * column name, the second index holds the database column type and the third - * column holds a table alias. + * column name, the second index holds the database column data type and the + * third column holds a table alias and name, i.e.: + * + * 0 = database column name + * 1 = database column data type + * 2 = column alias and name */ List getDimensionColumns(); === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java 2012-12-18 00:49:03 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/QueryPlanner.java 2013-01-17 17:49:13 +0000 @@ -45,7 +45,8 @@ * * @param params the data query params. * @param optimalQueries the number of optimal queries for the planner to return. + * @param tableName the base table name. * @return list of data query params. */ - List planQuery( DataQueryParams params, int optimalQueries ); + List planQuery( DataQueryParams params, int optimalQueries, String tableName ); } === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2013-01-17 14:59:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2013-01-17 17:49:13 +0000 @@ -46,6 +46,7 @@ import org.hisp.dhis.analytics.AnalyticsManager; import org.hisp.dhis.analytics.AnalyticsService; +import org.hisp.dhis.analytics.AnalyticsTableManager; import org.hisp.dhis.analytics.DataQueryParams; import org.hisp.dhis.analytics.DimensionOption; import org.hisp.dhis.analytics.QueryPlanner; @@ -201,7 +202,7 @@ int optimalQueries = MathUtils.getWithin( SystemUtils.getCpuCores(), 1, 6 ); - List queries = queryPlanner.planQuery( params, optimalQueries ); + List queries = queryPlanner.planQuery( params, optimalQueries, AnalyticsTableManager.ANALYTICS_TABLE_NAME ); t.getTime( "Planned query for optimal: " + optimalQueries + ", got: " + queries.size() ); === 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-01-13 13:49:33 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultQueryPlanner.java 2013-01-17 17:49:13 +0000 @@ -63,7 +63,7 @@ // DefaultQueryPlanner implementation // ------------------------------------------------------------------------- - public List planQuery( DataQueryParams params, int optimalQueries ) + public List planQuery( DataQueryParams params, int optimalQueries, String tableName ) { Assert.isTrue( !params.getDimensions().isEmpty() ); Assert.isTrue( params.dimensionsAsFilters().isEmpty() ); @@ -77,7 +77,7 @@ List queries = new ArrayList(); - List groupedByPartition = groupByPartition( params ); + List groupedByPartition = groupByPartition( params, tableName ); for ( DataQueryParams byPartition : groupedByPartition ) { @@ -196,31 +196,31 @@ * partition it should be executed against. Sets the partition table name on * each query. Queries are grouped based on both dimensions and filters. */ - private List groupByPartition( DataQueryParams params ) + private List groupByPartition( DataQueryParams params, String tableName ) { List queries = new ArrayList(); if ( params.getPeriods() != null && !params.getPeriods().isEmpty() ) { - ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods() ); + ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods(), tableName ); - for ( String tableName : tablePeriodMap.keySet() ) + for ( String table : tablePeriodMap.keySet() ) { DataQueryParams query = new DataQueryParams( params ); - query.setPeriods( tablePeriodMap.get( tableName ) ); - query.setTableName( tableName ); + query.setPeriods( tablePeriodMap.get( table ) ); + query.setTableName( table ); queries.add( query ); } } else { - ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getFilterPeriods() ); + ListMap tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getFilterPeriods(), tableName ); - for ( String tableName : tablePeriodMap.keySet() ) + for ( String table : tablePeriodMap.keySet() ) { DataQueryParams query = new DataQueryParams( params ); - query.setFilterPeriods( tablePeriodMap.get( tableName ) ); - query.setTableName( tableName ); + query.setFilterPeriods( tablePeriodMap.get( table ) ); + query.setTableName( table ); queries.add( query ); } } === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/AbstractJdbcTableManager.java 2013-01-17 17:49:13 +0000 @@ -0,0 +1,189 @@ +package org.hisp.dhis.analytics.table; + +/* + * Copyright (c) 2004-2012, University of Oslo + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright notice, this + * list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright notice, + * this list of conditions and the following disclaimer in the documentation + * and/or other materials provided with the distribution. + * * Neither the name of the HISP project nor the names of its contributors may + * be used to endorse or promote products derived from this software without + * specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND + * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR + * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON + * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +import java.util.ArrayList; +import java.util.List; +import java.util.concurrent.Future; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.hisp.dhis.analytics.AnalyticsTableManager; +import org.hisp.dhis.common.CodeGenerator; +import org.hisp.dhis.dataelement.DataElementService; +import org.hisp.dhis.jdbc.StatementBuilder; +import org.hisp.dhis.organisationunit.OrganisationUnitGroupService; +import org.hisp.dhis.organisationunit.OrganisationUnitService; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.jdbc.BadSqlGrammarException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.scheduling.annotation.Async; + +public abstract class AbstractJdbcTableManager + implements AnalyticsTableManager +{ + protected static final Log log = LogFactory.getLog( JdbcAnalyticsTableManager.class ); + + public static final String PREFIX_ORGUNITGROUPSET = "ougs_"; + public static final String PREFIX_ORGUNITLEVEL = "uidlevel"; + public static final String PREFIX_INDEX = "in_"; + + private static final String TABLE_TEMP_SUFFIX = "_temp"; + + @Autowired + protected OrganisationUnitService organisationUnitService; + + @Autowired + protected DataElementService dataElementService; + + @Autowired + protected OrganisationUnitGroupService organisationUnitGroupService; + + @Autowired + protected StatementBuilder statementBuilder; + + @Autowired + protected JdbcTemplate jdbcTemplate; + + // ------------------------------------------------------------------------- + // Implementation + // ------------------------------------------------------------------------- + + public String getTempTableName() + { + return getTableName() + TABLE_TEMP_SUFFIX; + } + + @Async + public Future createIndexesAsync( String tableName, List columns ) + { + for ( String column : columns ) + { + final String index = PREFIX_INDEX + column + "_" + tableName + "_" + CodeGenerator.generateCode(); + + final String sql = "create index " + index + " on " + tableName + " (" + column + ")"; + + executeSilently( sql ); + + log.info( "Created index: " + index ); + } + + log.info( "Indexes created" ); + + return null; + } + + public void swapTable( String tableName ) + { + final String realTable = tableName.replaceFirst( TABLE_TEMP_SUFFIX, "" ); + + final String sqlDrop = "drop table " + realTable; + + executeSilently( sqlDrop ); + + final String sqlAlter = "alter table " + tableName + " rename to " + realTable; + + executeSilently( sqlAlter ); + } + + public List getDimensionColumnNames() + { + List columns = getDimensionColumns(); + + List columnNames = new ArrayList(); + + for ( String[] column : columns ) + { + columnNames.add( column[0] ); + } + + return columnNames; + } + + public boolean pruneTable( String tableName ) + { + final String sqlCount = "select count(*) from " + tableName; + + log.info( "Count SQL: " + sqlCount ); + + final boolean empty = jdbcTemplate.queryForInt( sqlCount ) == 0; + + if ( empty ) + { + final String sqlDrop = "drop table " + tableName; + + executeSilently( sqlDrop ); + + log.info( "Drop SQL: " + sqlDrop ); + + return true; + } + + return false; + } + + @Async + public Future vacuumTableAsync( String tableName ) + { + final String sql = statementBuilder.getVacuum( tableName ); + + log.info( "Vacuum SQL:" + sql ); + + jdbcTemplate.execute( sql ); + + return null; + } + + public void dropTable( String tableName ) + { + final String realTable = tableName.replaceFirst( TABLE_TEMP_SUFFIX, "" ); + + executeSilently( "drop table " + tableName ); + executeSilently( "drop table " + realTable ); + } + + // ------------------------------------------------------------------------- + // Supportive methods + // ------------------------------------------------------------------------- + + /** + * Executes a SQL statement. Ignores existing tables/indexes when attempting + * to create new. + */ + protected void executeSilently( String sql ) + { + try + { + jdbcTemplate.execute( sql ); + } + catch ( BadSqlGrammarException ex ) + { + log.warn( ex.getMessage() ); + } + } +} === 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 2012-12-22 18:09:46 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2013-01-17 17:49:13 +0000 @@ -54,9 +54,13 @@ { private static final Log log = LogFactory.getLog( DefaultAnalyticsTableService.class ); - @Autowired private AnalyticsTableManager tableManager; + public void setTableManager( AnalyticsTableManager tableManager ) + { + this.tableManager = tableManager; + } + @Autowired private OrganisationUnitService organisationUnitService; @@ -78,7 +82,8 @@ final Date earliest = tableManager.getEarliestData(); final Date latest = tableManager.getLatestData(); - final List tables = PartitionUtils.getTempTableNames( earliest, latest ); + final String tableName = tableManager.getTableName(); + final List tables = PartitionUtils.getTempTableNames( earliest, latest, tableName ); clock.logTime( "Checked data timespan and got tables: " + tables ); //dropTables( tables ); === 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 2012-12-27 18:15:23 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2013-01-17 17:49:13 +0000 @@ -27,6 +27,8 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; + import java.util.ArrayList; import java.util.Arrays; import java.util.Collection; @@ -34,27 +36,14 @@ import java.util.List; import java.util.concurrent.Future; -import org.apache.commons.logging.Log; -import org.apache.commons.logging.LogFactory; -import org.hisp.dhis.analytics.AnalyticsTableManager; import org.hisp.dhis.analytics.DataQueryParams; -import org.hisp.dhis.common.CodeGenerator; import org.hisp.dhis.dataelement.DataElementGroupSet; -import org.hisp.dhis.dataelement.DataElementService; -import org.hisp.dhis.jdbc.StatementBuilder; -import org.hisp.dhis.organisationunit.OrganisationUnitGroupService; import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet; import org.hisp.dhis.organisationunit.OrganisationUnitLevel; -import org.hisp.dhis.organisationunit.OrganisationUnitService; import org.hisp.dhis.period.PeriodType; import org.hisp.dhis.system.util.DateUtils; -import org.springframework.beans.factory.annotation.Autowired; -import org.springframework.jdbc.BadSqlGrammarException; -import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.scheduling.annotation.Async; -import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; - /** * This class manages the analytics table. The analytics table is a denormalized * table designed for analysis which contains raw data values. It has columns for @@ -72,35 +61,17 @@ * @author Lars Helge Overland */ public class JdbcAnalyticsTableManager - implements AnalyticsTableManager + extends AbstractJdbcTableManager { - private static final Log log = LogFactory.getLog( JdbcAnalyticsTableManager.class ); - - public static final String PREFIX_ORGUNITGROUPSET = "ougs_"; - public static final String PREFIX_ORGUNITLEVEL = "uidlevel"; - public static final String PREFIX_INDEX = "in_"; - - @Autowired - private OrganisationUnitService organisationUnitService; - - @Autowired - private DataElementService dataElementService; - - @Autowired - private OrganisationUnitGroupService organisationUnitGroupService; - - @Autowired - private StatementBuilder statementBuilder; - - @Autowired - private JdbcTemplate jdbcTemplate; - // ------------------------------------------------------------------------- // Implementation // ------------------------------------------------------------------------- - - //TODO average aggregation operator data, pre-aggregate in time dimension, not in org unit dimension + public String getTableName() + { + return "analytics"; + } + public void createTable( String tableName ) { final String sqlDrop = "drop table " + tableName; @@ -122,38 +93,6 @@ } @Async - public Future createIndexesAsync( String tableName, List columns ) - { - for ( String column : columns ) - { - final String index = PREFIX_INDEX + column + "_" + tableName + "_" + CodeGenerator.generateCode(); - - final String sql = "create index " + index + " on " + tableName + " (" + column + ")"; - - executeSilently( sql ); - - log.info( "Created index: " + index ); - } - - log.info( "Indexes created" ); - - return null; - } - - public void swapTable( String tableName ) - { - final String realTable = tableName.replaceFirst( TABLE_TEMP_SUFFIX, "" ); - - final String sqlDrop = "drop table " + realTable; - - executeSilently( sqlDrop ); - - final String sqlAlter = "alter table " + tableName + " rename to " + realTable; - - executeSilently( sqlAlter ); - } - - @Async public Future populateTableAsync( String tableName, Date startDate, Date endDate ) { populateTable( tableName, startDate, endDate, "cast(dv.value as double precision)", "int" ); @@ -210,13 +149,6 @@ jdbcTemplate.execute( sql ); } - /** - * Returns a list of dimension columns. Each entry is an array with: - * - * 0 = column name - * 1 = data type - * 2 = column alias and name - */ public List getDimensionColumns() { List columns = new ArrayList(); @@ -265,20 +197,6 @@ return columns; } - public List getDimensionColumnNames() - { - List columns = getDimensionColumns(); - - List columnNames = new ArrayList(); - - for ( String[] column : columns ) - { - columnNames.add( column[0] ); - } - - return columnNames; - } - public Date getEarliestData() { final String sql = "select min(pe.startdate) from datavalue dv " + @@ -295,36 +213,6 @@ return jdbcTemplate.queryForObject( sql, Date.class ); } - public boolean pruneTable( String tableName ) - { - final String sqlCount = "select count(*) from " + tableName; - - log.info( "Count SQL: " + sqlCount ); - - final boolean empty = jdbcTemplate.queryForInt( sqlCount ) == 0; - - if ( empty ) - { - final String sqlDrop = "drop table " + tableName; - - executeSilently( sqlDrop ); - - log.info( "Drop SQL: " + sqlDrop ); - - return true; - } - - return false; - } - - public void dropTable( String tableName ) - { - final String realTable = tableName.replaceFirst( TABLE_TEMP_SUFFIX, "" ); - - executeSilently( "drop table " + tableName ); - executeSilently( "drop table " + realTable ); - } - public void applyAggregationLevels( String tableName, Collection dataElements, int aggregationLevel ) { StringBuilder sql = new StringBuilder( "update " + tableName + " set " ); @@ -347,36 +235,4 @@ jdbcTemplate.execute( sql.toString() ); } - - @Async - public Future vacuumTableAsync( String tableName ) - { - final String sql = statementBuilder.getVacuum( tableName ); - - log.info( "Vacuum SQL:" + sql ); - - jdbcTemplate.execute( sql ); - - return null; - } - - // ------------------------------------------------------------------------- - // Supportive methods - // ------------------------------------------------------------------------- - - /** - * Executes a SQL statement. Ignores existing tables/indexes when attempting - * to create new. - */ - private void executeSilently( String sql ) - { - try - { - jdbcTemplate.execute( sql ); - } - catch ( BadSqlGrammarException ex ) - { - log.warn( ex.getMessage() ); - } - } } === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcCompletenessTableManager.java 2013-01-17 17:49:13 +0000 @@ -0,0 +1,172 @@ +package org.hisp.dhis.analytics.table; + +/* + * Copyright (c) 2004-2012, University of Oslo + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright notice, this + * list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright notice, + * this list of conditions and the following disclaimer in the documentation + * and/or other materials provided with the distribution. + * * Neither the name of the HISP project nor the names of its contributors may + * be used to endorse or promote products derived from this software without + * specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND + * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR + * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON + * ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +import java.util.ArrayList; +import java.util.Collection; +import java.util.Date; +import java.util.List; +import java.util.concurrent.Future; + +import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet; +import org.hisp.dhis.organisationunit.OrganisationUnitLevel; +import org.hisp.dhis.period.PeriodType; +import org.hisp.dhis.system.util.DateUtils; +import org.springframework.scheduling.annotation.Async; + +public class JdbcCompletenessTableManager + extends AbstractJdbcTableManager +{ + public String getTableName() + { + return "completeness"; + } + + public void createTable( String tableName ) + { + final String sqlDrop = "drop table " + tableName; + + executeSilently( sqlDrop ); + + String sqlCreate = "create table " + tableName + " ("; + + for ( String[] col : getDimensionColumns() ) + { + sqlCreate += col[0] + " " + col[1] + ","; + } + + sqlCreate += "date date)"; + + log.info( "Create SQL: " + sqlCreate ); + + executeSilently( sqlCreate ); + } + + @Async + public Future populateTableAsync( String tableName, Date startDate, Date endDate ) + { + final String start = DateUtils.getMediumDateString( startDate ); + final String end = DateUtils.getMediumDateString( endDate ); + + String insert = "insert into " + tableName + " ("; + + for ( String[] col : getDimensionColumns() ) + { + insert += col[0] + ","; + } + + insert += "date) "; + + String select = "select "; + + for ( String[] col : getDimensionColumns() ) + { + select += col[2] + ","; + } + + select = select.replace( "organisationunitid", "sourceid" ); // Legacy fix + + select += + "cdr.date as date " + + "from completedatasetregistration cdr " + + "left join _organisationunitgroupsetstructure ougs on cdr.sourceid=ougs.organisationunitid " + + "left join _orgunitstructure ous on cdr.sourceid=ous.organisationunitid " + + "left join _periodstructure ps on cdr.periodid=ps.periodid " + + "left join period pe on cdr.periodid=pe.periodid " + + "left join dataset ds on cdr.datasetid=ds.datasetid " + + "where pe.startdate >= '" + start + "' " + + "and pe.startdate <= '" + end + "'" + + "and cdr.date is not null"; + + final String sql = insert + select; + + log.info( "Populate SQL: "+ sql ); + + jdbcTemplate.execute( sql ); + + return null; + } + + public List getDimensionColumns() + { + List columns = new ArrayList(); + + Collection orgUnitGroupSets = + organisationUnitGroupService.getCompulsoryOrganisationUnitGroupSets(); + + Collection levels = + organisationUnitService.getOrganisationUnitLevels(); + + for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets ) + { + String[] col = { groupSet.getUid(), "character(11)", "ougs." + groupSet.getUid() }; + columns.add( col ); + } + + for ( OrganisationUnitLevel level : levels ) + { + String column = PREFIX_ORGUNITLEVEL + level.getLevel(); + String[] col = { column, "character(11)", "ous." + column }; + columns.add( col ); + } + + for ( PeriodType periodType : PeriodType.getAvailablePeriodTypes().subList( 0, 7 ) ) + { + String column = periodType.getName().toLowerCase(); + String[] col = { column, "character varying(10)", "ps." + column }; + columns.add( col ); + } + + String[] de = { "ds", "character(11) not null", "ds.uid" }; + + columns.add( de ); + + return columns; + } + + public Date getEarliestData() + { + final String sql = "select min(pe.startdate) from completedatasetregistration cdr " + + "join period pe on cdr.periodid=pe.periodid"; + + return jdbcTemplate.queryForObject( sql, Date.class ); + } + + public Date getLatestData() + { + final String sql = "select max(pe.startdate) from completedatasetregistration cdr " + + "join period pe on cdr.periodid=pe.periodid"; + + return jdbcTemplate.queryForObject( sql, Date.class ); + } + + public void applyAggregationLevels( String tableName, Collection dataElements, int aggregationLevel ) + { + // Not relevant + } +} === 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-01-07 14:31:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/PartitionUtils.java 2013-01-17 17:49:13 +0000 @@ -27,14 +27,12 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ -import static org.hisp.dhis.analytics.AnalyticsTableManager.TABLE_NAME; -import static org.hisp.dhis.analytics.AnalyticsTableManager.TABLE_NAME_TEMP; - import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.List; +import org.hisp.dhis.analytics.AnalyticsTableManager; import org.hisp.dhis.common.IdentifiableObject; import org.hisp.dhis.period.Period; import org.hisp.dhis.period.PeriodType; @@ -47,7 +45,7 @@ private static final String SEP = "_"; - public static List getTempTableNames( Date earliest, Date latest ) + public static List getTempTableNames( Date earliest, Date latest, String tableName ) { if ( earliest == null || latest == null || earliest.after( latest ) ) { @@ -60,7 +58,7 @@ while ( period != null && period.getStartDate().before( latest ) ) { - String table = TABLE_NAME_TEMP + SEP + period.getIsoDate(); + String table = tableName + AnalyticsTableManager.TABLE_TEMP_SUFFIX + SEP + period.getIsoDate(); tables.add( table ); @@ -70,11 +68,11 @@ return tables; } - public static String getTable( Period period ) + public static String getTable( Period period, String tableName ) { Period quarter = PERIODTYPE.createPeriod( period.getStartDate() ); - return TABLE_NAME + SEP + quarter.getIsoDate(); + return tableName + SEP + quarter.getIsoDate(); } public static Period getPeriod( String tableName ) @@ -90,13 +88,13 @@ return PeriodType.getPeriodFromIsoString( isoPeriod ); } - public static ListMap getTablePeriodMap( Collection periods ) + public static ListMap getTablePeriodMap( Collection periods, String tableName ) { ListMap map = new ListMap(); for ( IdentifiableObject period : periods ) { - map.putValue( getTable( (Period) period ), period ); + map.putValue( getTable( (Period) period, tableName ), period ); } return map; === modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml' --- dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2012-12-17 16:58:29 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2013-01-17 17:49:13 +0000 @@ -3,9 +3,21 @@ xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd"> - + + + + + + + + + + + + + === 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-01-17 11:02:22 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java 2013-01-17 17:49:13 +0000 @@ -31,6 +31,7 @@ import static org.hisp.dhis.analytics.DataQueryParams.ORGUNIT_DIM_ID; import static org.hisp.dhis.analytics.DataQueryParams.PERIOD_DIM_ID; import static org.hisp.dhis.common.IdentifiableObjectUtils.getList; +import static org.hisp.dhis.analytics.AnalyticsTableManager.ANALYTICS_TABLE_NAME; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertTrue; @@ -257,7 +258,7 @@ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); params.setPeriods( getList( createPeriod( "2000Q1" ), createPeriod( "2000Q2" ), createPeriod( "2000Q3" ), createPeriod( "2000Q4" ), createPeriod( "2001Q1" ), createPeriod( "2001Q2" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 4, queries.size() ); @@ -281,7 +282,7 @@ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); params.setPeriods( getList( createPeriod( "2000Q1" ), createPeriod( "2000Q2" ), createPeriod( "2000" ), createPeriod( "200002" ), createPeriod( "200003" ), createPeriod( "200004" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 6, queries.size() ); @@ -319,7 +320,7 @@ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); params.setPeriods( getList( createPeriod( "2000Q1" ), createPeriod( "2000Q2" ), createPeriod( "2000Q3" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 5, queries.size() ); @@ -342,7 +343,7 @@ params.setPeriods( getList( createPeriod( "200001" ), createPeriod( "200002" ), createPeriod( "200003" ), createPeriod( "200004" ), createPeriod( "200005" ), createPeriod( "200006" ), createPeriod( "200007" ), createPeriod( "200008" ), createPeriod( "200009" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 3, queries.size() ); @@ -364,7 +365,7 @@ params.setPeriods( getList( createPeriod( "200001" ), createPeriod( "200002" ), createPeriod( "200003" ), createPeriod( "200004" ), createPeriod( "200005" ), createPeriod( "200006" ), createPeriod( "200007" ), createPeriod( "200008" ), createPeriod( "200009" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 3, queries.size() ); @@ -386,7 +387,7 @@ params.setPeriods( getList( createPeriod( "200001" ), createPeriod( "200002" ), createPeriod( "200003" ), createPeriod( "200004" ), createPeriod( "200005" ), createPeriod( "200006" ), createPeriod( "200007" ), createPeriod( "200008" ), createPeriod( "200009" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 3, queries.size() ); @@ -407,7 +408,7 @@ params.setDataElements( getList( deA, deB, deC ) ); params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); - queryPlanner.planQuery( params, 4 ); + queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); } /** @@ -423,7 +424,7 @@ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); params.setFilterPeriods( getList( createPeriod( "2000Q1" ), createPeriod( "2000Q2" ), createPeriod( "2000Q3" ), createPeriod( "2000Q4" ), createPeriod( "2001Q1" ), createPeriod( "2001Q2" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); assertEquals( 4, queries.size() ); } @@ -441,7 +442,7 @@ params.setOrganisationUnits( getList( ouA, ouB, ouC, ouD, ouE ) ); params.setPeriods( getList( createPeriod( "2000Q1" ), createPeriod( "2000Q2" ), createPeriod( "2000" ), createPeriod( "200002" ), createPeriod( "200003" ), createPeriod( "200004" ) ) ); - List queries = queryPlanner.planQuery( params, 4 ); + List queries = queryPlanner.planQuery( params, 4, ANALYTICS_TABLE_NAME ); 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-01-07 14:31:20 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java 2013-01-17 17:49:13 +0000 @@ -28,8 +28,7 @@ */ import static org.hisp.dhis.DhisConvenienceTest.createPeriod; -import static org.hisp.dhis.analytics.AnalyticsTableManager.TABLE_NAME; -import static org.hisp.dhis.analytics.AnalyticsTableManager.TABLE_NAME_TEMP; +import static org.hisp.dhis.analytics.AnalyticsTableManager.*; import static org.hisp.dhis.common.IdentifiableObjectUtils.getList; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; @@ -46,6 +45,9 @@ public class PartitionUtilsTest { + private static final String TABLE_NAME_TEMP = ANALYTICS_TABLE_NAME + TABLE_TEMP_SUFFIX; + private static final String TABLE_NAME = ANALYTICS_TABLE_NAME; + @Test public void testGetTableNames() { @@ -53,7 +55,7 @@ Date earliest = cal.set( 2000, 5, 4 ).time(); Date latest = cal.set( 2003, 2, 10 ).time(); - List tables = PartitionUtils.getTempTableNames( earliest, latest ); + List tables = PartitionUtils.getTempTableNames( earliest, latest, TABLE_NAME ); assertEquals( 4, tables.size() ); assertTrue( tables.contains( TABLE_NAME_TEMP + "_2000" ) ); @@ -65,10 +67,10 @@ @Test public void testGetTable() { - assertEquals( TABLE_NAME + "_2000", PartitionUtils.getTable( createPeriod( "200011" ) ) ); - assertEquals( TABLE_NAME + "_2001", PartitionUtils.getTable( createPeriod( "2001W02" ) ) ); - assertEquals( TABLE_NAME + "_2002", PartitionUtils.getTable( createPeriod( "2002Q2" ) ) ); - assertEquals( TABLE_NAME + "_2003", PartitionUtils.getTable( createPeriod( "2003S2" ) ) ); + 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 ) ); } @Test @@ -87,7 +89,7 @@ public void testGetTablePeriodMap() { ListMap map = PartitionUtils.getTablePeriodMap( getList( - createPeriod( "2000S1" ), createPeriod( "2000S2" ), createPeriod( "2001S1" ), createPeriod( "2001S2" ), createPeriod( "2002S1" ) ) ); + createPeriod( "2000S1" ), createPeriod( "2000S2" ), createPeriod( "2001S1" ), createPeriod( "2001S2" ), createPeriod( "2002S1" ) ), TABLE_NAME ); assertEquals( 3, map.size() ); === modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java' --- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2012-12-27 18:15:23 +0000 +++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.java 2013-01-17 17:49:13 +0000 @@ -27,6 +27,7 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import org.hisp.dhis.analytics.AnalyticsTableService; @@ -48,8 +49,11 @@ { public static final String RESOURCE_PATH = "/resourceTables"; - @Autowired + @Resource(name="org.hisp.dhis.analytics.AnalyticsTableService") private AnalyticsTableService analyticsTableService; + + @Resource(name="org.hisp.dhis.analytics.CompletenessTableService") + private AnalyticsTableService completenessTableService; @Autowired private ResourceTableService resourceTableService; @@ -65,6 +69,15 @@ ContextUtils.okResponse( response, "Initiated analytics table update" ); } + + @RequestMapping( value = "/completeness", method = RequestMethod.PUT ) + @PreAuthorize( "hasRole('ALL') or hasRole('F_DATA_MART_ADMIN')" ) + public void completeness( HttpServletResponse response ) + { + completenessTableService.update(); + + ContextUtils.okResponse( response, "Initiated completeness table update" ); + } @RequestMapping( method = RequestMethod.PUT ) @PreAuthorize( "hasRole('ALL') or hasRole('F_PERFORM_MAINTENANCE')" )