dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #00582
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 164: Improved the trimming of the temporary table used in datamart
------------------------------------------------------------
revno: 164
committer: Lars Helge Oeverland larshelge@xxxxxxxxx
branch nick: trunk
timestamp: Tue 2009-04-07 23:55:16 +0200
message:
Improved the trimming of the temporary table used in datamart
modified:
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java
dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java
dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2009-03-13 11:51:35 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2009-04-07 21:55:16 +0000
@@ -27,9 +27,6 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import static org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore.COLUMN_PREFIX;
-import static org.hisp.dhis.datamart.crosstab.jdbc.CrossTabStore.SEPARATOR;
-
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
@@ -91,7 +88,7 @@
public Collection<Operand> populateCrossTabTable( final Collection<Operand> operands,
final Collection<Integer> periodIds, final Collection<Integer> organisationUnitIds )
{
- final Set<Operand> emptyOperands = new HashSet<Operand>( operands );
+ final Set<Operand> operandsWithData = new HashSet<Operand>( operands );
if ( validate( operands, periodIds, organisationUnitIds ) )
{
@@ -111,8 +108,6 @@
batchHandler.init();
- emptyOperands.addAll( operands );
-
Map<Operand, String> map = null;
List<String> valueList = null;
@@ -152,7 +147,7 @@
if ( value != null )
{
hasValues = true;
- emptyOperands.remove( operand );
+ operandsWithData.add( operand );
}
valueList.add( value );
@@ -170,7 +165,7 @@
batchHandler.flush();
}
- return emptyOperands;
+ return operandsWithData;
}
public void dropCrossTabTable()
@@ -178,14 +173,15 @@
crossTabStore.dropCrossTabTable();
}
- public void trimCrossTabTable( Collection<Operand> emptyOperands )
+ public void trimCrossTabTable( Collection<Operand> operands )
{
- final Collection<String> columnNames = getCrossTabColumnNames( emptyOperands );
-
- for ( final String columnName : columnNames )
- {
- crossTabStore.dropCrossTabColumn( columnName );
- }
+ // TODO use H2 in-memory table for datavaluecrosstab table ?
+
+ crossTabStore.createTrimmedCrossTabTable( operands );
+
+ crossTabStore.dropCrossTabTable();
+
+ crossTabStore.renameTrimmedCrossTabTable();
}
public Map<Operand, Integer> getOperandIndexMap( Collection<Operand> operands )
@@ -225,21 +221,6 @@
// -------------------------------------------------------------------------
/**
- * Generates a collection of crosstab column names based on a collection of Operands.
- */
- private Collection<String> getCrossTabColumnNames( Collection<Operand> operands )
- {
- final Collection<String> columnNames = new HashSet<String>( operands.size() );
-
- for ( final Operand operand : operands )
- {
- columnNames.add( COLUMN_PREFIX + operand.getDataElementId() + SEPARATOR + operand.getOptionComboId() );
- }
-
- return columnNames;
- }
-
- /**
* Validates whether the given collections of identifiers are not null and of size greater than 0.
*/
private boolean validate( Collection<Operand> operands, Collection<Integer> periodIds, Collection<Integer> unitIds )
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2009-03-13 11:51:35 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2009-04-07 21:55:16 +0000
@@ -44,12 +44,16 @@
String SEPARATOR = "_";
void createCrossTabTable( List<Operand> operands );
+
+ Map<Integer, String> getCrossTabTableColumns();
void dropCrossTabTable();
- Map<Integer, String> getCrossTabTableColumns();
-
- void dropCrossTabColumn( String columnName );
+ void dropTrimmedCrossTabTable();
+
+ void renameTrimmedCrossTabTable();
+
+ void createTrimmedCrossTabTable( Collection<Operand> operands );
int validateCrossTabTable( Collection<Operand> operands );
}
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2009-03-13 11:51:35 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2009-04-07 21:55:16 +0000
@@ -96,27 +96,7 @@
holder.close();
}
}
-
- public void dropCrossTabTable()
- {
- final StatementHolder holder = statementManager.getHolder();
-
- try
- {
- final String sql = "DROP TABLE IF EXISTS datavaluecrosstab";
-
- holder.getStatement().executeUpdate( sql );
- }
- catch ( SQLException ex )
- {
- throw new RuntimeException( "Failed to drop datavalue crosstab table", ex );
- }
- finally
- {
- holder.close();
- }
- }
-
+
public Map<Integer, String> getCrossTabTableColumns()
{
final StatementHolder holder = statementManager.getHolder();
@@ -149,25 +129,98 @@
holder.close();
}
}
-
- public void dropCrossTabColumn( final String columnName )
- {
- final StatementHolder holder = statementManager.getHolder();
-
- try
- {
- final String sql = "ALTER TABLE datavaluecrosstab DROP COLUMN " + columnName;
-
- holder.getStatement().executeUpdate( sql );
- }
- catch ( SQLException ex )
- {
- throw new RuntimeException( "Failed to drop datavalue crosstab column", ex );
- }
- finally
- {
- holder.close();
- }
+
+ public void dropCrossTabTable()
+ {
+ final StatementHolder holder = statementManager.getHolder();
+
+ try
+ {
+ final String sql = "DROP TABLE IF EXISTS datavaluecrosstab";
+
+ holder.getStatement().executeUpdate( sql );
+ }
+ catch ( SQLException ex )
+ {
+ throw new RuntimeException( "Failed to drop datavalue crosstab table", ex );
+ }
+ finally
+ {
+ holder.close();
+ }
+ }
+
+ public void dropTrimmedCrossTabTable()
+ {
+ final StatementHolder holder = statementManager.getHolder();
+
+ try
+ {
+ final String sql = "DROP TABLE IF EXISTS datavaluecrosstabtrimmed";
+
+ holder.getStatement().executeUpdate( sql );
+ }
+ catch ( SQLException ex )
+ {
+ throw new RuntimeException( "Failed to drop trimmed datavalue crosstab table", ex );
+ }
+ finally
+ {
+ holder.close();
+ }
+ }
+
+ public void renameTrimmedCrossTabTable()
+ {
+ final StatementHolder holder = statementManager.getHolder();
+
+ try
+ {
+ final String sql = "ALTER TABLE datavaluecrosstabtrimmed RENAME TO datavaluecrosstab";
+
+ holder.getStatement().executeUpdate( sql );
+ }
+ catch ( SQLException ex )
+ {
+ throw new RuntimeException( "Failed to rename trimmed crosstab table", ex );
+ }
+ finally
+ {
+ holder.close();
+ }
+ }
+
+ public void createTrimmedCrossTabTable( Collection<Operand> operands )
+ {
+ final StatementHolder holder = statementManager.getHolder();
+
+ try
+ {
+ final StringBuffer buffer = new StringBuffer( "CREATE TABLE datavaluecrosstabtrimmed AS SELECT periodid, sourceid, " );
+
+ for ( final Operand operand : operands )
+ {
+ buffer.append( COLUMN_PREFIX ).append( operand.getDataElementId() ).append( SEPARATOR ).append( operand.getOptionComboId() ).append( ", " );
+ }
+
+ if ( buffer.length() > 1 )
+ {
+ buffer.deleteCharAt( buffer.length() - 1 );
+ buffer.deleteCharAt( buffer.length() - 1 );
+ }
+
+ buffer.append( " FROM datavaluecrosstab" );
+
+ holder.getStatement().executeUpdate( buffer.toString() );
+ }
+ catch ( SQLException ex )
+ {
+ throw new RuntimeException( "Failed to get crosstab table columns", ex );
+ }
+ finally
+ {
+ holder.close();
+ }
}
public int validateCrossTabTable( final Collection<Operand> operands )
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2009-03-03 16:46:36 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabServiceTest.java 2009-04-07 21:55:16 +0000
@@ -209,9 +209,9 @@
public void testTrimCrossTabTable()
{
- Collection<Operand> emptyOperands = crossTabService.populateCrossTabTable( operands, periodIds, organisationUnitIds );
+ Collection<Operand> operandsWithData = crossTabService.populateCrossTabTable( operands, periodIds, organisationUnitIds );
- crossTabService.trimCrossTabTable( emptyOperands );
+ crossTabService.trimCrossTabTable( operandsWithData );
}
public void testGetOperandIndexMap()
=== modified file 'dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java'
--- dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2009-03-03 16:46:36 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/test/java/org/hisp/dhis/datamart/crosstab/CrossTabStoreTest.java 2009-04-07 21:55:16 +0000
@@ -56,11 +56,4 @@
crossTabStore.dropCrossTabTable();
}
-
- public void testDropCrossTabColumn()
- {
- crossTabStore.createCrossTabTable( operands );
-
- crossTabStore.dropCrossTabColumn( "de1_1" );
- }
}
--
Trunk
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.
