dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #36783
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 18805: Analytics, impl debug/sql resource for getting the raw data source SQL statment, will be useful f...
------------------------------------------------------------
revno: 18805
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Fri 2015-04-03 14:51:02 +0200
message:
Analytics, impl debug/sql resource for getting the raw data source SQL statment, will be useful for debugging requests.
added:
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsUtils.java
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/NameableObjectUtils.java
dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/AnalyticsController.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/utils/ContextUtils.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/common/NameableObjectUtils.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/NameableObjectUtils.java 2015-03-30 10:16:10 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/NameableObjectUtils.java 2015-04-03 12:51:02 +0000
@@ -265,4 +265,15 @@
return null;
}
+
+ /**
+ * Returns a copy of the given list. Returns an empty list if the argument is null.
+ *
+ * @param objects the objects.
+ * @param a list.
+ */
+ public static <T extends NameableObject> List<T> getCopyNullSafe( List<T> objects )
+ {
+ return objects != null ? new ArrayList<>( objects ) : new ArrayList<T>();
+ }
}
=== added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsUtils.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsUtils.java 1970-01-01 00:00:00 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsUtils.java 2015-04-03 12:51:02 +0000
@@ -0,0 +1,102 @@
+package org.hisp.dhis.analytics;
+
+/*
+ * Copyright (c) 2004-2015, 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 static org.hisp.dhis.system.util.DateUtils.getMediumDateString;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.commons.lang.StringUtils;
+import org.hisp.dhis.common.IdentifiableObjectUtils;
+import org.hisp.dhis.common.IllegalQueryException;
+import org.hisp.dhis.common.NameableObject;
+import org.hisp.dhis.common.NameableObjectUtils;
+import org.hisp.dhis.organisationunit.OrganisationUnit;
+import org.hisp.dhis.period.Period;
+import org.hisp.dhis.system.util.TextUtils;
+
+/**
+ * @author Lars Helge Overland
+ */
+public class AnalyticsUtils
+{
+ public static String getDebugDataSql( DataQueryParams params )
+ {
+ List<NameableObject> dataElements = new ArrayList<>( NameableObjectUtils.getCopyNullSafe( params.getDataElements() ) );
+ dataElements.addAll( NameableObjectUtils.getCopyNullSafe( params.getFilterDataElements() ) );
+
+ List<NameableObject> periods = new ArrayList<>( NameableObjectUtils.getCopyNullSafe( params.getPeriods() ) );
+ periods.addAll( NameableObjectUtils.getCopyNullSafe( params.getFilterPeriods() ) );
+
+ List<NameableObject> orgUnits = new ArrayList<>( NameableObjectUtils.getCopyNullSafe( params.getOrganisationUnits() ) );
+ orgUnits.addAll( NameableObjectUtils.getCopyNullSafe( params.getFilterOrganisationUnits() ) );
+
+ if ( dataElements.isEmpty() || periods.isEmpty() || orgUnits.isEmpty() )
+ {
+ throw new IllegalQueryException( "Query must contain at least one data element, one period and one organisation unit" );
+ }
+
+ String sql =
+ "select de.name as de_name, de.uid as de_uid, de.dataelementid as de_id, pe.startdate as start_date, pe.enddate as end_date, pt.name as pt_name, " +
+ "ou.name as ou_name, ou.uid as ou_uid, ou.organisationunitid as ou_id, " +
+ "cocn.categoryoptioncomboname as coc_name, coc.uid as coc_uid, coc.categoryoptioncomboid as coc_id, " +
+ "aocn.categoryoptioncomboname as aoc_name, aoc.uid as aoc_uid, aoc.categoryoptioncomboid as aoc_id, dv.value as datavalue " +
+ "from datavalue dv " +
+ "inner join dataelement de on dv.dataelementid = de.dataelementid " +
+ "inner join period pe on dv.periodid = pe.periodid " +
+ "inner join periodtype pt on pe.periodtypeid = pt.periodtypeid " +
+ "inner join organisationunit ou on dv.sourceid = ou.organisationunitid " +
+ "inner join categoryoptioncombo coc on dv.categoryoptioncomboid = coc.categoryoptioncomboid " +
+ "inner join _categoryoptioncomboname cocn on dv.categoryoptioncomboid = cocn.categoryoptioncomboid " +
+ "inner join categoryoptioncombo aoc on dv.attributeoptioncomboid = aoc.categoryoptioncomboid " +
+ "inner join _categoryoptioncomboname aocn on dv.attributeoptioncomboid = aocn.categoryoptioncomboid " +
+ "where dv.dataelementid in (" + StringUtils.join( IdentifiableObjectUtils.getIdentifiers( dataElements ), "," ) + ") " +
+ "and (";
+
+ for ( NameableObject period : periods )
+ {
+ Period pe = (Period) period;
+ sql += "(pe.startdate >= '" + getMediumDateString( pe.getStartDate() ) + "' and pe.enddate <= '" + getMediumDateString( pe.getEndDate() ) + "') or ";
+ }
+
+ sql = TextUtils.removeLastOr( sql ) + ") and (";
+
+ for ( NameableObject orgUnit : orgUnits )
+ {
+ OrganisationUnit ou = (OrganisationUnit) orgUnit;
+ int level = ou.getOrganisationUnitLevel();
+ sql += "(dv.sourceid in (select organisationunitid from _orgunitstructure where idlevel" + level + " = " + ou.getId() + ")) or ";
+ }
+
+ sql = TextUtils.removeLastOr( sql ) + ") limit 100000;";
+
+ return sql;
+ }
+}
=== modified file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java'
--- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2015-03-26 14:25:02 +0000
+++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/DataQueryParams.java 2015-04-03 12:51:02 +0000
@@ -1530,6 +1530,11 @@
// Get and set helpers for filters
// -------------------------------------------------------------------------
+ public List<NameableObject> getFilterDataElements()
+ {
+ return getFilterOptions( DATAELEMENT_DIM_ID );
+ }
+
public List<NameableObject> getFilterPeriods()
{
return getFilterOptions( PERIOD_DIM_ID );
=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/AnalyticsController.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/AnalyticsController.java 2015-03-26 14:25:02 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/AnalyticsController.java 2015-04-03 12:51:02 +0000
@@ -36,6 +36,7 @@
import org.hisp.dhis.analytics.AggregationType;
import org.hisp.dhis.analytics.AnalyticsService;
+import org.hisp.dhis.analytics.AnalyticsUtils;
import org.hisp.dhis.analytics.DataQueryParams;
import org.hisp.dhis.common.DisplayProperty;
import org.hisp.dhis.common.Grid;
@@ -52,6 +53,7 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
+import org.springframework.web.bind.annotation.ResponseBody;
/**
* @author Lars Helge Overland
@@ -63,7 +65,7 @@
@Autowired
private AnalyticsService analyticsService;
-
+
@Autowired
private ContextUtils contextUtils;
@@ -280,6 +282,34 @@
GridUtils.toJrxml( grid, null, response.getWriter() );
}
+ @RequestMapping( value = RESOURCE_PATH + "/debug/sql", method = RequestMethod.GET, produces = { "text/html", "text/plain" } )
+ public @ResponseBody String getDebugSql(
+ @RequestParam Set<String> dimension,
+ @RequestParam( required = false ) Set<String> filter,
+ @RequestParam( required = false ) AggregationType aggregationType,
+ @RequestParam( required = false ) String measureCriteria,
+ @RequestParam( required = false ) boolean skipMeta,
+ @RequestParam( required = false ) boolean skipRounding,
+ @RequestParam( required = false ) boolean hierarchyMeta,
+ @RequestParam( required = false ) boolean ignoreLimit,
+ @RequestParam( required = false ) boolean tableLayout,
+ @RequestParam( required = false ) boolean hideEmptyRows,
+ @RequestParam( required = false ) boolean showHierarchy,
+ @RequestParam( required = false ) DisplayProperty displayProperty,
+ @RequestParam( required = false ) IdentifiableProperty outputIdScheme,
+ @RequestParam( required = false ) String approvalLevel,
+ @RequestParam( required = false ) String columns,
+ @RequestParam( required = false ) String rows,
+ Model model,
+ HttpServletResponse response ) throws Exception
+ {
+ DataQueryParams params = analyticsService.getFromUrl( dimension, filter, aggregationType, measureCriteria, skipMeta, skipRounding,
+ hierarchyMeta, ignoreLimit, hideEmptyRows, showHierarchy, displayProperty, outputIdScheme, approvalLevel, i18nManager.getI18nFormat() );
+
+ contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_TEXT, CacheStrategy.NO_CACHE, "debug.sql", false );
+ return AnalyticsUtils.getDebugDataSql( params );
+ }
+
// -------------------------------------------------------------------------
// Exception handlers
// -------------------------------------------------------------------------
=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/utils/ContextUtils.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/utils/ContextUtils.java 2015-02-19 09:18:17 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/utils/ContextUtils.java 2015-04-03 12:51:02 +0000
@@ -108,7 +108,7 @@
}
public void configureResponse( HttpServletResponse response, String contentType, CacheStrategy cacheStrategy,
- String filename, boolean attachment )
+ String filename, boolean attachment )
{
if ( contentType != null )
{
