dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #36995
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 18928: Analytics. Re-implemented population of data approval column in analytics tables. Introduced a da...
------------------------------------------------------------
revno: 18928
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Thu 2015-04-16 23:17:08 +0200
message:
Analytics. Re-implemented population of data approval column in analytics tables. Introduced a dataapprovalminlevel resource table which contains the minimum (highest) approval level for a dataset/period/orgunit/attroptioncombo combination. Using this table during approval table generation to avoid aggregate subquery in select statement. Improves performance around 50 times.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/setting/SystemSettingManager.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/scheduling/ResourceTableTask.java
dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml
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-core/src/main/java/org/hisp/dhis/setting/DefaultSystemSettingManager.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-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java 2015-03-03 16:18:51 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java 2015-04-16 21:17:08 +0000
@@ -96,6 +96,11 @@
void generateDataElementCategoryOptionComboTable();
/**
+ * Generates a resource table for data approval aggregated to minimum level.
+ */
+ void generateDataApprovalMinLevelTable();
+
+ /**
* Create all SQL views.
*/
void createAllSqlViews();
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java 2015-04-16 21:17:08 +0000
@@ -29,12 +29,14 @@
*/
import java.util.List;
+import java.util.Set;
import org.hisp.dhis.dataelement.CategoryOptionGroupSet;
import org.hisp.dhis.dataelement.DataElementCategory;
import org.hisp.dhis.dataelement.DataElementGroupSet;
import org.hisp.dhis.indicator.IndicatorGroupSet;
import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
+import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
/**
* @author Lars Helge Overland
@@ -49,6 +51,7 @@
final String TABLE_NAME_PERIOD_STRUCTURE = "_periodstructure";
final String TABLE_NAME_DATE_PERIOD_STRUCTURE = "_dateperiodstructure";
final String TABLE_NAME_DATA_ELEMENT_CATEGORY_OPTION_COMBO = "_dataelementcategoryoptioncombo";
+ final String TABLE_NAME_DATA_APPROVAL_MIN_LEVEL = "_dataapprovalminlevel";
/**
* Performs a batch update.
@@ -130,5 +133,12 @@
/**
* Creates and generates table.
*/
- public void createAndGenerateDataElementCategoryOptionCombo();
+ void createAndGenerateDataElementCategoryOptionCombo();
+
+ /**
+ * Creates and populates data approval minimum level table.
+ *
+ * @param levels the organisation unit levels part of approval levels.
+ */
+ void createAndPopulateDataApprovalMinLevel( Set<OrganisationUnitLevel> levels );
}
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/setting/SystemSettingManager.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/setting/SystemSettingManager.java 2015-04-07 09:42:13 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/setting/SystemSettingManager.java 2015-04-16 21:17:08 +0000
@@ -193,6 +193,8 @@
boolean systemNotificationEmailValid();
+ boolean hideUnapprovedDataInAnalytics();
+
String googleAnalyticsUA();
Integer credentialsExpires();
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2015-04-07 13:23:35 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java 2015-04-16 21:17:08 +0000
@@ -42,6 +42,7 @@
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
@@ -49,6 +50,7 @@
import org.hisp.dhis.common.IdentifiableObjectManager;
import org.hisp.dhis.common.IdentifiableObjectUtils;
import org.hisp.dhis.common.comparator.IdentifiableObjectNameComparator;
+import org.hisp.dhis.dataapproval.DataApprovalLevelService;
import org.hisp.dhis.dataelement.CategoryOptionGroup;
import org.hisp.dhis.dataelement.CategoryOptionGroupSet;
import org.hisp.dhis.dataelement.DataElement;
@@ -61,6 +63,7 @@
import org.hisp.dhis.indicator.IndicatorGroupSet;
import org.hisp.dhis.organisationunit.OrganisationUnit;
import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
+import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.hisp.dhis.organisationunit.OrganisationUnitService;
import org.hisp.dhis.period.Cal;
import org.hisp.dhis.period.DailyPeriodType;
@@ -126,6 +129,13 @@
this.sqlViewService = sqlViewService;
}
+ private DataApprovalLevelService dataApprovalLevelService;
+
+ public void setDataApprovalLevelService( DataApprovalLevelService dataApprovalLevelService )
+ {
+ this.dataApprovalLevelService = dataApprovalLevelService;
+ }
+
// -------------------------------------------------------------------------
// OrganisationUnitStructure
// -------------------------------------------------------------------------
@@ -513,6 +523,24 @@
log.info( "Data element category option combo table generated" );
}
+
+ // -------------------------------------------------------------------------
+ // DataApprovalMinLevelTable
+ // -------------------------------------------------------------------------
+
+ @Override
+ @Transactional
+ public void generateDataApprovalMinLevelTable()
+ {
+ Set<OrganisationUnitLevel> levels = dataApprovalLevelService.getOrganisationUnitApprovalLevels();
+
+ if ( !levels.isEmpty() )
+ {
+ resourceTableStore.createAndPopulateDataApprovalMinLevel( levels );
+
+ log.info( "Data approval min level table generated" );
+ }
+ }
// -------------------------------------------------------------------------
// SQL Views. Each view is created/dropped in separate transactions so that
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2015-04-15 14:23:40 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java 2015-04-16 21:17:08 +0000
@@ -29,6 +29,7 @@
*/
import java.util.List;
+import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
@@ -38,6 +39,7 @@
import org.hisp.dhis.indicator.IndicatorGroupSet;
import org.hisp.dhis.jdbc.StatementBuilder;
import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
+import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.hisp.dhis.period.PeriodType;
import org.hisp.dhis.resourcetable.ResourceTableStore;
import org.hisp.dhis.resourcetable.statement.CreateCategoryOptionGroupSetTableStatement;
@@ -537,11 +539,11 @@
"dataelementuid VARCHAR(11) NOT NULL, " +
"categoryoptioncomboid INTEGER NOT NULL, " +
"categoryoptioncombouid VARCHAR(11) NOT NULL)";
-
- jdbcTemplate.execute( create );
-
+
log.info( "Create data element category option combo SQL: " + create );
+ jdbcTemplate.execute( create );
+
final String sql =
"insert into " + TABLE_NAME_DATA_ELEMENT_CATEGORY_OPTION_COMBO +
" (dataelementid, dataelementuid, categoryoptioncomboid, categoryoptioncombouid) " +
@@ -562,4 +564,67 @@
jdbcTemplate.execute( index );
}
+
+ // -------------------------------------------------------------------------
+ // DataApprovalMinLevelTable
+ // -------------------------------------------------------------------------
+
+ @Override
+ public void createAndPopulateDataApprovalMinLevel( Set<OrganisationUnitLevel> levels )
+ {
+ try
+ {
+ jdbcTemplate.execute( "drop table if exists " + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL );
+ }
+ catch ( BadSqlGrammarException ex )
+ {
+ // Do nothing, table does not exist
+ }
+
+ final String create = "create table " + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL + "(" +
+ "datasetid integer not null, " +
+ "periodid integer not null, " +
+ "organisationunitid integer not null, " +
+ "attributeoptioncomboid integer not null, " +
+ "minlevel integer not null);";
+
+ log.info( "Create data approval min level SQL: " + create );
+
+ jdbcTemplate.execute( create );
+
+ String sql =
+ "insert into " + TABLE_NAME_DATA_APPROVAL_MIN_LEVEL +
+ " (datasetid,periodid,organisationunitid,attributeoptioncomboid,minlevel) " +
+ "select da.datasetid, da.periodid, da.organisationunitid, da.attributeoptioncomboid, dal.level as minlevel " +
+ "from dataapproval da " +
+ "inner join dataapprovallevel dal on da.dataapprovallevelid=dal.dataapprovallevelid " +
+ "where not exists ( " +
+ "select 1 from dataapproval da2 " +
+ "inner join dataapprovallevel dal2 on da2.dataapprovallevelid=dal2.dataapprovallevelid " +
+ "inner join _orgunitstructure ous2 on da2.organisationunitid=ous2.organisationunitid " +
+ "where da.datasetid=da2.datasetid and da.periodid=da2.periodid and da.attributeoptioncomboid=da2.attributeoptioncomboid " +
+ "and dal2.level < dal.level " +
+ "and ( ";
+
+ for ( OrganisationUnitLevel level : levels )
+ {
+ sql += "da.organisationunitid = ous2.idlevel" + level.getLevel() + " or ";
+ }
+
+ sql = TextUtils.removeLastOr( sql ) + ") );";
+
+ log.info( "Insert data approval min level SQL: " + sql );
+
+ jdbcTemplate.execute( sql );
+
+ final String index =
+ "create index in_dataapprovalminlevel_datasetid on _dataapprovalminlevel(datasetid);" +
+ "create index in_dataapprovalminlevel_periodid on _dataapprovalminlevel(periodid);" +
+ "create index in_dataapprovalminlevel_organisationunitid on _dataapprovalminlevel(organisationunitid);" +
+ "create index in_dataapprovalminlevel_attributeoptioncomboid on _dataapprovalminlevel(attributeoptioncomboid);";
+
+ log.info( "Create data approval min level index SQL: " + index );
+
+ jdbcTemplate.execute( index );
+ }
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/scheduling/ResourceTableTask.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/scheduling/ResourceTableTask.java 2015-03-08 08:26:56 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/scheduling/ResourceTableTask.java 2015-04-16 21:17:08 +0000
@@ -106,6 +106,8 @@
// Supportive methods
// -------------------------------------------------------------------------
+ //TODO use DefaultAnalyticsTableService generateAll ?
+
private void generateAll()
{
resourceTableService.dropAllSqlViews();
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml'
--- dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml 2015-04-05 20:08:26 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/resources/META-INF/dhis/beans.xml 2015-04-16 21:17:08 +0000
@@ -16,6 +16,7 @@
<property name="categoryService" ref="org.hisp.dhis.dataelement.DataElementCategoryService" />
<property name="periodService" ref="org.hisp.dhis.period.PeriodService" />
<property name="sqlViewService" ref="org.hisp.dhis.sqlview.SqlViewService" />
+ <property name="dataApprovalLevelService" ref="org.hisp.dhis.dataapproval.DataApprovalLevelService" />
</bean>
<!-- Data integrity -->
=== 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 2015-03-20 14:18:25 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2015-04-16 21:17:08 +0000
@@ -178,6 +178,12 @@
resourceTableService.generatePeriodTable();
resourceTableService.generateDatePeriodTable();
resourceTableService.generateDataElementCategoryOptionComboTable();
+
+ if ( systemSettingManager.hideUnapprovedDataInAnalytics() )
+ {
+ resourceTableService.generateDataApprovalMinLevelTable();
+ }
+
resourceTableService.createAllSqlViews();
}
=== 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 2015-04-08 15:06:59 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2015-04-16 21:17:08 +0000
@@ -40,6 +40,7 @@
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.Future;
+import org.apache.commons.lang.StringUtils;
import org.hisp.dhis.analytics.AnalyticsTable;
import org.hisp.dhis.analytics.DataQueryParams;
import org.hisp.dhis.dataelement.CategoryOptionGroupSet;
@@ -49,7 +50,6 @@
import org.hisp.dhis.organisationunit.OrganisationUnitGroupSet;
import org.hisp.dhis.organisationunit.OrganisationUnitLevel;
import org.hisp.dhis.period.PeriodType;
-import org.hisp.dhis.setting.SystemSettingManager;
import org.hisp.dhis.system.util.DateUtils;
import org.hisp.dhis.system.util.MathUtils;
import org.hisp.dhis.system.util.TextUtils;
@@ -145,6 +145,8 @@
{
final String dbl = statementBuilder.getDoubleColumnType();
+ final String approvalClause = getApprovalJoinClause();
+
taskLoop : while ( true )
{
AnalyticsTable table = tables.poll();
@@ -158,15 +160,15 @@
"dv.value " + statementBuilder.getRegexpMatch() + " '" + MathUtils.NUMERIC_LENIENT_REGEXP + "' " +
"and ( dv.value != '0' or de.aggregationtype = 'average' or de.zeroissignificant = true ) ";
- populateTable( table, "cast(dv.value as " + dbl + ")", "null", DataElement.VALUE_TYPE_INT, intClause );
+ populateTable( table, "cast(dv.value as " + dbl + ")", "null", DataElement.VALUE_TYPE_INT, intClause, approvalClause );
- populateTable( table, "1", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'" );
+ populateTable( table, "1", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'true'", approvalClause );
- populateTable( table, "0", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'" );
-
- populateTable( table, "1", "null", DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'" );
-
- populateTable( table, "null", "dv.value", DataElement.VALUE_TYPE_STRING, null );
+ populateTable( table, "0", "null", DataElement.VALUE_TYPE_BOOL, "dv.value = 'false'", approvalClause );
+
+ populateTable( table, "1", "null", DataElement.VALUE_TYPE_TRUE_ONLY, "dv.value = 'true'", approvalClause );
+
+ populateTable( table, "null", "dv.value", DataElement.VALUE_TYPE_STRING, null, approvalClause );
}
return null;
@@ -183,7 +185,7 @@
* @param valueType data element value type to include data for.
* @param whereClause where clause to constrain data query.
*/
- private void populateTable( AnalyticsTable table, String valueExpression, String textValueExpression, String valueType, String whereClause )
+ private void populateTable( AnalyticsTable table, String valueExpression, String textValueExpression, String valueType, String whereClause, String approvalClause )
{
final String start = DateUtils.getMediumDateString( table.getPeriod().getStartDate() );
final String end = DateUtils.getMediumDateString( table.getPeriod().getEndDate() );
@@ -226,6 +228,7 @@
"inner join period pe on dv.periodid=pe.periodid " +
"inner join _periodstructure ps on dv.periodid=ps.periodid " +
"inner join organisationunit ou on dv.sourceid=ou.organisationunitid " +
+ approvalClause +
"where de.valuetype = '" + valueType + "' " +
"and de.domaintype = 'AGGREGATE' " +
"and pe.startdate >= '" + start + "' " +
@@ -245,25 +248,26 @@
* data element resource table which will indicate level 0 (highest) if approval
* is not required. Then looks for highest level in dataapproval table.
*/
- private String getApprovalSubquery()
+ private String getApprovalJoinClause()
{
- String sql = "(" +
- "select coalesce(des.datasetapprovallevel, aon.approvallevel, min(dal.level), " + APPROVAL_LEVEL_UNAPPROVED + ") " +
- "from dataapproval da " +
- "inner join dataapprovallevel dal on da.dataapprovallevelid = dal.dataapprovallevelid " +
- "where da.periodid = dv.periodid " +
- "and da.attributeoptioncomboid = dv.attributeoptioncomboid " +
- "and des.datasetid = da.datasetid " +
- "and (";
-
- Set<OrganisationUnitLevel> levels = dataApprovalLevelService.getOrganisationUnitApprovalLevels();
-
- for ( OrganisationUnitLevel level : levels )
+ if ( isApprovalEnabled() )
{
- sql += "ous.idlevel" + level.getLevel() + " = da.organisationunitid or ";
+ String sql =
+ "left join _dataapprovalminlevel da " +
+ "on des.datasetid=da.datasetid and da.periodid=dv.periodid and da.attributeoptioncomboid=dv.attributeoptioncomboid " +
+ "and (";
+
+ Set<OrganisationUnitLevel> levels = dataApprovalLevelService.getOrganisationUnitApprovalLevels();
+
+ for ( OrganisationUnitLevel level : levels )
+ {
+ sql += "ous.idlevel" + level.getLevel() + " = da.organisationunitid or ";
+ }
+
+ return TextUtils.removeLastOr( sql ) + ") ";
}
- return TextUtils.removeLastOr( sql ) + ") ) as approvallevel";
+ return StringUtils.EMPTY;
}
@Override
@@ -343,8 +347,10 @@
columns.addAll( Arrays.asList( de, co, ou, level ) );
if ( isApprovalEnabled() )
- {
- String[] al = { quote( "approvallevel" ), "integer", getApprovalSubquery() };
+ {
+ String col = "coalesce(des.datasetapprovallevel, aon.approvallevel, da.minlevel, " + APPROVAL_LEVEL_UNAPPROVED + ")";
+
+ String[] al = { quote( "approvallevel" ), "integer", col };
columns.add( al );
}
@@ -434,6 +440,9 @@
*/
private boolean isApprovalEnabled()
{
- return (Boolean) systemSettingManager.getSystemSetting( SystemSettingManager.KEY_HIDE_UNAPPROVED_DATA_IN_ANALYTICS, false );
+ boolean setting = systemSettingManager.hideUnapprovedDataInAnalytics();
+ boolean levels = !dataApprovalLevelService.getAllDataApprovalLevels().isEmpty();
+
+ return setting && levels;
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/setting/DefaultSystemSettingManager.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/setting/DefaultSystemSettingManager.java 2015-03-26 14:13:53 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/setting/DefaultSystemSettingManager.java 2015-04-16 21:17:08 +0000
@@ -213,6 +213,12 @@
}
@Override
+ public boolean hideUnapprovedDataInAnalytics()
+ {
+ return (Boolean) getSystemSetting( SystemSettingManager.KEY_HIDE_UNAPPROVED_DATA_IN_ANALYTICS, false );
+ }
+
+ @Override
public String googleAnalyticsUA()
{
return StringUtils.trimToNull( (String) getSystemSetting( KEY_GOOGLE_ANALYTICS_UA ) );
