dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20640
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9548: Analytics, impl generation of completeness tables
------------------------------------------------------------
revno: 9548
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2013-01-17 18:49:13 +0100
message:
Analytics, impl generation of completeness tables
added:
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/JdbcCompletenessTableManager.java
modified:
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/QueryPlanner.java
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/DefaultQueryPlanner.java
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/JdbcAnalyticsTableManager.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/main/resources/META-INF/dhis/beans.xml
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/data/QueryPlannerTest.java
dhis-2/dhis-services/dhis-service-analytics/src/test/java/org/hisp/dhis/analytics/table/PartitionUtilsTest.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/ResourceTableController.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/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<String[]> 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<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, optimalQueries );
+ List<DataQueryParams> 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<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries )
+ public List<DataQueryParams> planQuery( DataQueryParams params, int optimalQueries, String tableName )
{
Assert.isTrue( !params.getDimensions().isEmpty() );
Assert.isTrue( params.dimensionsAsFilters().isEmpty() );
@@ -77,7 +77,7 @@
List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
- List<DataQueryParams> groupedByPartition = groupByPartition( params );
+ List<DataQueryParams> 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<DataQueryParams> groupByPartition( DataQueryParams params )
+ private List<DataQueryParams> groupByPartition( DataQueryParams params, String tableName )
{
List<DataQueryParams> queries = new ArrayList<DataQueryParams>();
if ( params.getPeriods() != null && !params.getPeriods().isEmpty() )
{
- ListMap<String, IdentifiableObject> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getPeriods() );
+ ListMap<String, IdentifiableObject> 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<String, IdentifiableObject> tablePeriodMap = PartitionUtils.getTablePeriodMap( params.getFilterPeriods() );
+ ListMap<String, IdentifiableObject> 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<String> 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<String> getDimensionColumnNames()
+ {
+ List<String[]> columns = getDimensionColumns();
+
+ List<String> columnNames = new ArrayList<String>();
+
+ 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<String> tables = PartitionUtils.getTempTableNames( earliest, latest );
+ final String tableName = tableManager.getTableName();
+ final List<String> 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<String> 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<String[]> getDimensionColumns()
{
List<String[]> columns = new ArrayList<String[]>();
@@ -265,20 +197,6 @@
return columns;
}
- public List<String> getDimensionColumnNames()
- {
- List<String[]> columns = getDimensionColumns();
-
- List<String> columnNames = new ArrayList<String>();
-
- 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<String> 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<String[]> getDimensionColumns()
+ {
+ List<String[]> columns = new ArrayList<String[]>();
+
+ Collection<OrganisationUnitGroupSet> orgUnitGroupSets =
+ organisationUnitGroupService.getCompulsoryOrganisationUnitGroupSets();
+
+ Collection<OrganisationUnitLevel> 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<String> 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<String> getTempTableNames( Date earliest, Date latest )
+ public static List<String> 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<String, IdentifiableObject> getTablePeriodMap( Collection<IdentifiableObject> periods )
+ public static ListMap<String, IdentifiableObject> getTablePeriodMap( Collection<IdentifiableObject> periods, String tableName )
{
ListMap<String, IdentifiableObject> map = new ListMap<String, IdentifiableObject>();
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";>
<bean id="org.hisp.dhis.analytics.AnalyticsTableManager" class="org.hisp.dhis.analytics.table.JdbcAnalyticsTableManager" />
- <bean id="org.hisp.dhis.analytics.AnalyticsTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService" />
+
+ <bean id="org.hisp.dhis.analytics.CompletenessTableManager" class="org.hisp.dhis.analytics.table.JdbcCompletenessTableManager" />
+
+ <bean id="org.hisp.dhis.analytics.AnalyticsTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService">
+ <property name="tableManager" ref="org.hisp.dhis.analytics.AnalyticsTableManager" />
+ </bean>
+
+ <bean id="org.hisp.dhis.analytics.CompletenessTableService" class="org.hisp.dhis.analytics.table.DefaultAnalyticsTableService">
+ <property name="tableManager" ref="org.hisp.dhis.analytics.CompletenessTableManager" />
+ </bean>
+
<bean id="org.hisp.dhis.analytics.AnalyticsManager" class="org.hisp.dhis.analytics.data.JdbcAnalyticsManager" />
+
<bean id="org.hisp.dhis.analytics.AnalyticsService" class="org.hisp.dhis.analytics.data.DefaultAnalyticsService" />
+
<bean id="org.hisp.dhis.analytics.QueryPlanner" class="org.hisp.dhis.analytics.data.DefaultQueryPlanner" />
</beans>
=== 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<DataQueryParams> queries = queryPlanner.planQuery( params, 4 );
+ List<DataQueryParams> 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<String> tables = PartitionUtils.getTempTableNames( earliest, latest );
+ List<String> 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<String, IdentifiableObject> 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')" )
