=== added directory 'dhis-2/dhis-services/dhis-service-analytics' === added file 'dhis-2/dhis-services/dhis-service-analytics/pom.xml' --- dhis-2/dhis-services/dhis-service-analytics/pom.xml 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/pom.xml 2012-12-03 21:04:39 +0000 @@ -0,0 +1,65 @@ + + 4.0.0 + + + org.hisp.dhis + dhis-services + 2.11-SNAPSHOT + + + dhis-service-analytics + jar + DHIS Analytics + + + + + + + org.hisp.dhis + dhis-api + + + org.hisp.dhis + dhis-service-core + + + org.hisp.dhis + dhis-service-administration + + + org.hisp.dhis + dhis-support-system + + + + + + jep + jep + + + commons-logging + commons-logging + + + commons-lang + commons-lang + + + org.amplecode + quick + + + com.whalin + Memcached-Java-Client + 3.0.1 + + + + + ../../ + + === added directory 'dhis-2/dhis-services/dhis-service-analytics/src' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics' === added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsManager.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,40 @@ +package org.hisp.dhis.analytics; + +/* + * 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.Collection; +import java.util.List; +import java.util.concurrent.Future; + +import org.hisp.dhis.aggregation.AggregatedDataValue; + +public interface AnalyticsManager +{ + Future> getAggregatedDataValueTotals( Collection values, Collection dataElementIds, + Collection periodIds, Collection organisationUnitIds ); +} === added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsService.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,39 @@ +package org.hisp.dhis.analytics; + +/* + * 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.Collection; +import java.util.List; + +import org.hisp.dhis.aggregation.AggregatedDataValue; + +public interface AnalyticsService +{ + List getAggregatedDataValueTotals( Collection dataElementIds, + Collection periodIds, Collection organisationUnitIds ) throws Exception; +} === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableManager.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,54 @@ +package org.hisp.dhis.analytics; + +/* + * 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.List; +import java.util.concurrent.Future; + +public interface AnalyticsTableManager +{ + void dropTable(); + + void createTable(); + + void populateTable(); + + Future createIndexesAsync( List columns ); + + /** + * 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. + */ + List getDimensionColumns(); + + /** + * Returns a list of database column names. + */ + List getDimensionColumnNames(); +} === added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableService.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableService.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/AnalyticsTableService.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,35 @@ +package org.hisp.dhis.analytics; + +/* + * 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.concurrent.Future; + +public interface AnalyticsTableService +{ + Future update(); +} === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data' === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/DefaultAnalyticsService.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,73 @@ +package org.hisp.dhis.analytics.data; + +/* + * 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.List; +import java.util.concurrent.Future; + +import org.hisp.dhis.aggregation.AggregatedDataValue; +import org.hisp.dhis.analytics.AnalyticsManager; +import org.hisp.dhis.analytics.AnalyticsService; +import org.hisp.dhis.system.util.PaginatedList; +import org.hisp.dhis.system.util.Timer; +import org.springframework.beans.factory.annotation.Autowired; + +public class DefaultAnalyticsService + implements AnalyticsService +{ + @Autowired + private AnalyticsManager analyticsManager; + + public List getAggregatedDataValueTotals( Collection dataElementIds, + Collection periodIds, Collection organisationUnitIds ) throws Exception + { + Timer t = new Timer().start(); + + List> dePages = new PaginatedList( dataElementIds ).setNumberOfPages( 4 ).getPages(); + + List>> futures = new ArrayList>>(); + + List values = new ArrayList(); + + for ( List dePage : dePages ) + { + futures.add( analyticsManager.getAggregatedDataValueTotals( values, dePage, periodIds, organisationUnitIds ) ); + } + + for ( Future> future : futures ) + { + values.addAll( future.get() ); + } + + t.getTime( "Got aggregated values" ); + + return values; + } +} === added file 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java' --- dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/data/JdbcAnalyticsManager.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,102 @@ +package org.hisp.dhis.analytics.data; + +/* + * 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 static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString; +import static org.hisp.dhis.system.util.TextUtils.getQuotedCommaDelimitedString; + +import java.util.Collection; +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.aggregation.AggregatedDataValue; +import org.hisp.dhis.analytics.AnalyticsManager; +import org.hisp.dhis.expression.ExpressionService; +import org.hisp.dhis.organisationunit.OrganisationUnitService; +import org.hisp.dhis.period.PeriodService; +import org.hisp.dhis.period.PeriodType; +import org.hisp.dhis.system.objectmapper.AggregatedDataValueRowMapper; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.scheduling.annotation.Async; +import org.springframework.scheduling.annotation.AsyncResult; + +/** + * This class is responsible for producing aggregated data values. It reads data + * from the analytics table. Organisation units provided as arguments must be on + * the same level in the hierarchy. + * + * @author Lars Helge Overland + */ +public class JdbcAnalyticsManager + implements AnalyticsManager +{ + private static final Log log = LogFactory.getLog( JdbcAnalyticsManager.class ); + + @Autowired + private JdbcTemplate jdbcTemplate; + + @Autowired + private OrganisationUnitService organisationUnitService; + + @Autowired + private PeriodService periodService; + + @Autowired + private ExpressionService expressionService; + + // ------------------------------------------------------------------------- + // Implementation + // ------------------------------------------------------------------------- + + //TODO period aggregation for multiple period types + //TODO hierarchy aggregation for org units at multiple levels + //TODO indicator aggregation + + @Async + public Future> getAggregatedDataValueTotals( Collection values, Collection dataElementIds, + Collection periodIds, Collection organisationUnitIds ) + { + int level = organisationUnitService.getLevelOfOrganisationUnit( organisationUnitIds.iterator().next() ); + String periodType = PeriodType.getPeriodTypeFromIsoString( periodIds.iterator().next() ).getName().toLowerCase(); + + final String sql = + "SELECT dataelementid, 0 as categoryoptioncomboid, periodid, idlevel" + level + " as organisationunitid, SUM(value) as value " + + "FROM analytics " + + "WHERE dataelementid IN ( " + getCommaDelimitedString( dataElementIds ) + " ) " + + "AND " + periodType + " IN ( " + getQuotedCommaDelimitedString( periodIds ) + " ) " + + "AND idlevel" + level + " IN ( " + getCommaDelimitedString( organisationUnitIds ) + " ) " + + "GROUP BY dataelementid, periodid, idlevel" + level; + + log.info( sql ); + + return new AsyncResult>( jdbcTemplate.query( sql, new AggregatedDataValueRowMapper() ) ); + } +} === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table' === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/DefaultAnalyticsTableService.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,97 @@ +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.analytics.AnalyticsTableService; +import org.hisp.dhis.system.util.ConcurrentUtils; +import org.hisp.dhis.system.util.PaginatedList; +import org.hisp.dhis.system.util.SystemUtils; +import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.scheduling.annotation.Async; + +public class DefaultAnalyticsTableService + implements AnalyticsTableService +{ + private static final Log log = LogFactory.getLog( DefaultAnalyticsTableService.class ); + + @Autowired + private AnalyticsTableManager tableManager; + + // ------------------------------------------------------------------------- + // Implementation + // ------------------------------------------------------------------------- + + //TODO generateOrganisationUnitStructures + //TODO generateOrganisationUnitGroupSetTable + //TODO generatePeriodStructure + + @Async + public Future update() + { + log.info( "Starting update..." ); + + tableManager.dropTable(); + tableManager.createTable(); + tableManager.populateTable(); + + createIndexes(); + + log.info( "Update done" ); + + return null; + } + + // ------------------------------------------------------------------------- + // Supportive methods + // ------------------------------------------------------------------------- + + private void createIndexes() + { + int pages = Math.max( ( SystemUtils.getCpuCores() - 1 ), 1 ); + + log.info( "No of pages: " + pages ); + + List> futures = new ArrayList>(); + + List> columnPages = new PaginatedList( tableManager.getDimensionColumnNames() ).setNumberOfPages( pages ).getPages(); + + for ( List columnPage : columnPages ) + { + futures.add( tableManager.createIndexesAsync( columnPage ) ); + } + + ConcurrentUtils.waitForCompletion( futures ); + } +} === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/java/org/hisp/dhis/analytics/table/JdbcAnalyticsTableManager.java 2012-12-03 21:04:39 +0000 @@ -0,0 +1,244 @@ +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.Arrays; +import java.util.Collection; +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.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.springframework.beans.factory.annotation.Autowired; +import org.springframework.jdbc.BadSqlGrammarException; +import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.scheduling.annotation.Async; + +/** + * 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 + * each organisation unit group set and organisation unit level. Also, columns + * for dataelementid, periodid, organisationunitid, categoryoptioncomboid, value. + * + * The data records in this table are not aggregated. Typically, queries will + * aggregate in organisation unit hierarchy dimension, in the period/time dimension, + * and the category dimensions, as well as org unit group set dimensions. + * + * @author Lars Helge Overland + */ +public class JdbcAnalyticsTableManager + implements AnalyticsTableManager +{ + private static final Log log = LogFactory.getLog( JdbcAnalyticsTableManager.class ); + + public static final String PREFIX_ORGUNITGROUPSET = "ougs_"; + public static final String PREFIX_ORGUNITLEVEL = "idlevel"; + public static final String PREFIX_INDEX = "index_"; + public static final String TABLE_NAME = "analytics"; + + @Autowired + private OrganisationUnitService organisationUnitService; + + @Autowired + private OrganisationUnitGroupService organisationUnitGroupService; + + @Autowired + private JdbcTemplate jdbcTemplate; + + // ------------------------------------------------------------------------- + // Implementation + // ------------------------------------------------------------------------- + + //TODO all data types + //TODO create temp table then swap + //TODO shard on data year + //TODO average aggregation operator data, pre-aggregate in time dimension, not in org unit dimension + + public void dropTable() + { + final String sql = "drop table " + TABLE_NAME; + + executeSilently( sql ); + + log.info( "Dropped table: " + TABLE_NAME ); + } + + public void createTable() + { + String sql = "create table " + TABLE_NAME + " ("; + + for ( String[] col : getDimensionColumns() ) + { + sql += col[0] + " " + col[1] + ","; + } + + sql += "value double precision)"; + + log.info( "Create SQL: " + sql ); + + executeSilently( sql ); + + log.info( "Created table: " + TABLE_NAME ); + } + + @Async + public Future createIndexesAsync( List columns ) + { + for ( String column : columns ) + { + final String sql = "create index " + PREFIX_INDEX + + column + " on " + TABLE_NAME + " (" + column + ")"; + + executeSilently( sql ); + + log.info( "Created index on column: " + column ); + } + + log.info( "Indexes created" ); + + return null; + } + + public void populateTable() + { + String insert = "insert into analytics ("; + + for ( String[] col : getDimensionColumns() ) + { + insert += col[0] + ","; + } + + insert += "value) "; + + String select = "select "; + + for ( String[] col : getDimensionColumns() ) + { + select += col[2] + col[0] + ","; + } + + select = select.replace( "organisationunitid", "sourceid" ); // Legacy fix + + select += + "cast(dv.value as double precision) " + + "from datavalue dv " + + "left join _organisationunitgroupsetstructure ougs on dv.sourceid=ougs.organisationunitid " + + "left join _orgunitstructure ous on dv.sourceid=ous.organisationunitid " + + "left join _period_no_disaggregation_structure ps on dv.periodid=ps.periodid " + + "left join dataelement de on dv.dataelementid=de.dataelementid " + + "left join period pe on dv.periodid=pe.periodid " + + "where de.valuetype='int' and pe.startdate >= '2011-10-01'"; + + final String sql = insert + select; + + log.info( "Populate SQL: "+ sql ); + + jdbcTemplate.execute( sql ); + + log.info( "Populated analytics table" ); + } + + public List getDimensionColumns() + { + List columns = new ArrayList(); + + Collection orgUnitGroupSets = + organisationUnitGroupService.getCompulsoryOrganisationUnitGroupSets(); + + Collection levels = + organisationUnitService.getOrganisationUnitLevels(); + + for ( OrganisationUnitGroupSet groupSet : orgUnitGroupSets ) + { + String[] col = { PREFIX_ORGUNITGROUPSET + groupSet.getUid(), "integer", "ougs." }; + columns.add( col ); + } + + for ( OrganisationUnitLevel level : levels ) + { + String[] col = { PREFIX_ORGUNITLEVEL + level.getLevel(), "integer", "ous." }; + columns.add( col ); + } + + for ( PeriodType periodType : PeriodType.getAvailablePeriodTypes().subList( 0, 7 ) ) + { + String[] col = { periodType.getName().toLowerCase(), "character varying(10)", "ps." }; + columns.add( col ); + } + + String[] de = { "dataelementid", "integer not null", "dv." }; + String[] pe = { "periodid", "integer not null", "dv." }; + String[] ou = { "organisationunitid", "integer not null", "dv." }; + String[] co = { "categoryoptioncomboid", "integer not null", "dv." }; + + columns.addAll( Arrays.asList( de, pe, ou, co ) ); + + return columns; + } + + public List getDimensionColumnNames() + { + List columns = getDimensionColumns(); + + List columnNames = new ArrayList(); + + for ( String[] column : columns ) + { + columnNames.add( column[0] ); + } + + return columnNames; + } + + // ------------------------------------------------------------------------- + // 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 directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF' === added directory 'dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis' === added 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 1970-01-01 00:00:00 +0000 +++ dhis-2/dhis-services/dhis-service-analytics/src/main/resources/META-INF/dhis/beans.xml 2012-12-03 21:04:39 +0000 @@ -0,0 +1,17 @@ + + + + + + + + + + + + === modified file 'dhis-2/dhis-services/pom.xml' --- dhis-2/dhis-services/pom.xml 2012-11-20 13:38:49 +0000 +++ dhis-2/dhis-services/pom.xml 2012-12-03 21:04:39 +0000 @@ -22,6 +22,7 @@ dhis-service-reporting dhis-service-mapgeneration dhis-service-aggregationengine-default + dhis-service-analytics dhis-service-sms dhis-service-patient dhis-service-mobile === modified file 'dhis-2/pom.xml' --- dhis-2/pom.xml 2012-11-29 16:21:29 +0000 +++ dhis-2/pom.xml 2012-12-03 21:04:39 +0000 @@ -285,6 +285,11 @@ org.hisp.dhis + dhis-service-analytics + ${project.version} + + + org.hisp.dhis dhis-support-jdbc ${project.version}