dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 16546
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2014-08-27 19:08:48 +0200
message:
  Resource tables, improved performance of geeration of table _categorystructure
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-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


--
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	2014-03-18 08:10:10 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableService.java	2014-08-27 17:08:48 +0000
@@ -69,6 +69,8 @@
     
     /**
      * Generates a resource table for all category option combos.
+     * 
+     * Depends on the category option combo names table.
      */
     void generateCategoryTable();
     

=== 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	2014-05-04 09:55:24 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/resourcetable/ResourceTableStore.java	2014-08-27 17:08:48 +0000
@@ -110,6 +110,8 @@
      */
     void createCategoryStructure( List<DataElementCategory> categories );
     
+    void populateCategoryStructure( List<DataElementCategory> categories );
+    
     /**
      * Creates table.
      */

=== 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	2014-08-26 09:25:52 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/DefaultResourceTableService.java	2014-08-27 17:08:48 +0000
@@ -51,7 +51,6 @@
 import org.hisp.dhis.dataelement.DataElement;
 import org.hisp.dhis.dataelement.DataElementCategory;
 import org.hisp.dhis.dataelement.DataElementCategoryCombo;
-import org.hisp.dhis.dataelement.DataElementCategoryOption;
 import org.hisp.dhis.dataelement.DataElementCategoryOptionCombo;
 import org.hisp.dhis.dataelement.DataElementCategoryService;
 import org.hisp.dhis.dataelement.DataElementGroupSet;
@@ -69,7 +68,6 @@
 import org.hisp.dhis.period.PeriodService;
 import org.hisp.dhis.period.PeriodType;
 import org.hisp.dhis.resourcetable.statement.CreateCategoryOptionGroupSetTableStatement;
-import org.hisp.dhis.resourcetable.statement.CreateCategoryTableStatement;
 import org.hisp.dhis.sqlview.SqlView;
 import org.hisp.dhis.sqlview.SqlViewService;
 import org.springframework.transaction.annotation.Transactional;
@@ -340,36 +338,9 @@
 
         Collections.sort( categories, IdentifiableObjectNameComparator.INSTANCE );
 
-        List<DataElementCategoryOptionCombo> categoryOptionCombos =
-            new ArrayList<>( categoryService.getAllDataElementCategoryOptionCombos() );
-
         resourceTableStore.createCategoryStructure( categories );
-
-        // ---------------------------------------------------------------------
-        // Populate table
-        // ---------------------------------------------------------------------
-
-        List<Object[]> batchArgs = new ArrayList<>();
-
-        for ( DataElementCategoryOptionCombo categoryOptionCombo : categoryOptionCombos )
-        {
-            List<Object> values = new ArrayList<>();
-
-            values.add( categoryOptionCombo.getId() );
-            values.add( categoryOptionCombo.getName() );
-
-            for ( DataElementCategory category : categories )
-            {
-                DataElementCategoryOption categoryOption = category.getCategoryOption( categoryOptionCombo );
-
-                values.add( categoryOption != null ? categoryOption.getName() : null );
-                values.add( categoryOption != null ? categoryOption.getUid() : null );
-            }
-
-            batchArgs.add( values.toArray() );
-        }
-
-        resourceTableStore.batchUpdate( (categories.size() * 2) + 2, CreateCategoryTableStatement.TABLE_NAME, batchArgs );
+        
+        resourceTableStore.populateCategoryStructure( categories );
 
         log.info( "Category table generated" );
     }

=== 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	2014-07-18 12:52:24 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/resourcetable/jdbc/JdbcResourceTableStore.java	2014-08-27 17:08:48 +0000
@@ -302,7 +302,7 @@
         
         jdbcTemplate.execute( statement.getStatement() );
     }
-        
+    
     public void populateOrganisationUnitGroupSetStructure( List<OrganisationUnitGroupSet> groupSets )
     {
         String sql = 
@@ -354,6 +354,41 @@
         jdbcTemplate.execute( statement.getStatement() );
     }
 
+    public void populateCategoryStructure( List<DataElementCategory> categories )
+    {
+        String sql = 
+            "insert into " + CreateCategoryTableStatement.TABLE_NAME + " " +
+            "select coc.categoryoptioncomboid as cocid, con.categoryoptioncomboname as cocname, ";
+        
+        for ( DataElementCategory category : categories )
+        {
+            sql += "(" +
+                "select co.name from categoryoptioncombos_categoryoptions cocco " +
+                "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " +
+                "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " +
+                "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " +
+                "and cco.categoryid = " + category.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( category.getName() ) + ", ";
+
+            sql += "(" +
+                "select co.uid from categoryoptioncombos_categoryoptions cocco " +
+                "inner join dataelementcategoryoption co on cocco.categoryoptionid = co.categoryoptionid " +
+                "inner join categories_categoryoptions cco on co.categoryoptionid = cco.categoryoptionid " +
+                "where coc.categoryoptioncomboid = cocco.categoryoptioncomboid " +
+                "and cco.categoryid = " + category.getId() + " " +
+                "limit 1) as " + statementBuilder.columnQuote( category.getUid() ) + ", ";
+        }
+
+        sql = TextUtils.removeLastComma( sql ) + " ";
+        sql += 
+            "from categoryoptioncombo coc " +
+            "inner join _categoryoptioncomboname con on coc.categoryoptioncomboid = con.categoryoptioncomboid";
+        
+        log.info( "Populate category structure SQL: " + sql );
+        
+        jdbcTemplate.execute( sql );        
+    }
+    
     // -------------------------------------------------------------------------
     // DataElementStructure
     // -------------------------------------------------------------------------