dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #11649
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 3378: Fixed bug data-mart-exports-fail-with-too-many-operands
------------------------------------------------------------
revno: 3378
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2011-04-13 18:09:45 +0200
message:
Fixed bug data-mart-exports-fail-with-too-many-operands
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-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java
dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.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-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 2011-01-20 21:08:24 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/DefaultCrossTabService.java 2011-04-13 16:09:45 +0000
@@ -53,7 +53,13 @@
private static final Log log = LogFactory.getLog( DefaultCrossTabService.class );
private static final int MAX_LENGTH = 20;
- private static final int MAX_COLUMNS = 1500;
+
+ private int maxColumns = 1500;
+
+ public void setMaxColumns( int maxColumns )
+ {
+ this.maxColumns = maxColumns;
+ }
// -------------------------------------------------------------------------
// Dependencies
@@ -94,7 +100,7 @@
{
if ( validate( operands, periodIds, organisationUnitIds ) )
{
- final PaginatedList<DataElementOperand> operandList = new PaginatedList<DataElementOperand>( operands, MAX_COLUMNS );
+ final PaginatedList<DataElementOperand> operandList = new PaginatedList<DataElementOperand>( operands, maxColumns );
final List<String> crossTabTableKeys = new ArrayList<String>();
@@ -110,7 +116,7 @@
crossTabStore.createCrossTabTable( operandPage, key );
final BatchHandler<Object> batchHandler = batchHandlerFactory.createBatchHandler( GenericBatchHandler.class );
- batchHandler.setTableName( CrossTabStore.TABLE_NAME + key );
+ batchHandler.setTableName( CrossTabStore.TABLE_PREFIX + key );
batchHandler.init();
for ( final Integer periodId : periodIds )
=== 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 2011-01-20 21:08:24 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/CrossTabStore.java 2011-04-13 16:09:45 +0000
@@ -40,8 +40,8 @@
public interface CrossTabStore
{
final String ID = CrossTabStore.class.getName();
- final String TABLE_NAME = "datavaluecrosstab_";
- final String TABLE_NAME_TRIMMED = "datavaluecrosstabtrimmed_";
+ final String TABLE_PREFIX = "datavaluecrosstab_";
+ final String TABLE_PREFIX_TRIMMED = "datavaluecrosstabtrimmed_";
/**
* Filters and returns the DataElementOperands with data from the given
=== 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 2011-01-20 21:08:24 +0000
+++ dhis-2/dhis-services/dhis-service-datamart-default/src/main/java/org/hisp/dhis/datamart/crosstab/jdbc/JDBCCrossTabStore.java 2011-04-13 16:09:45 +0000
@@ -28,6 +28,7 @@
*/
import static org.hisp.dhis.system.util.TextUtils.getCommaDelimitedString;
+import static org.hisp.dhis.system.util.TextUtils.trimEnd;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -46,9 +47,7 @@
*/
public class JDBCCrossTabStore
implements CrossTabStore
-{
- private static final String ALIAS_PREFIX = "c";
-
+{
// -------------------------------------------------------------------------
// Dependencies
// -------------------------------------------------------------------------
@@ -94,7 +93,7 @@
try
{
- final StringBuffer sql = new StringBuffer( "CREATE TABLE " + TABLE_NAME + key + " ( " );
+ final StringBuffer sql = new StringBuffer( "CREATE TABLE " + TABLE_PREFIX + key + " ( " );
sql.append( "periodid INTEGER NOT NULL, " );
sql.append( "sourceid INTEGER NOT NULL, " );
@@ -124,7 +123,7 @@
try
{
- final String sql = "DROP TABLE IF EXISTS " + TABLE_NAME + key;
+ final String sql = "DROP TABLE IF EXISTS " + TABLE_PREFIX + key;
holder.getStatement().executeUpdate( sql );
}
@@ -146,20 +145,13 @@
Collection<Integer> periodIds, Collection<Integer> sourceIds, List<String> keys )
{
final StatementHolder holder = statementManager.getHolder();
-
+
+ String sql = getCrossTabSelectJoin( keys );
+
+ sql += " WHERE c.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")";
+
try
- {
- String sql = "SELECT * FROM " + TABLE_NAME + keys.get( 0 ) + " AS c0 ";
-
- for ( int i = 1; i < keys.size(); i++ )
- {
- final String alias = ALIAS_PREFIX + i;
-
- sql += "FULL JOIN " + TABLE_NAME + keys.get( i ) + " AS " + alias + " ON c0.periodid=" + alias + ".periodid AND c0.sourceid=" + alias + ".sourceid ";
- }
-
- sql += "WHERE c0.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c0.sourceid IN (" + getCommaDelimitedString( sourceIds ) + ")";
-
+ {
final ResultSet resultSet = holder.getStatement().executeQuery( sql );
return getCrossTabDataValues( resultSet, operands );
@@ -178,20 +170,13 @@
Collection<Integer> periodIds, int sourceId, List<String> keys )
{
final StatementHolder holder = statementManager.getHolder();
+
+ String sql = getCrossTabSelectJoin( keys );
+ sql += " WHERE c.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c.sourceid = " + sourceId;
+
try
{
- String sql = "SELECT * FROM " + TABLE_NAME + keys.get( 0 ) + " AS c0 ";
-
- for ( int i = 1; i < keys.size(); i++ )
- {
- final String alias = ALIAS_PREFIX + i;
-
- sql += "FULL JOIN " + TABLE_NAME + keys.get( i ) + " AS " + alias + " ON c0.periodid=" + alias + ".periodid AND c0.sourceid=" + alias + ".sourceid ";
- }
-
- sql += "WHERE c0.periodid IN (" + getCommaDelimitedString( periodIds ) + ") AND c0.sourceid=" + sourceId;
-
final ResultSet resultSet = holder.getStatement().executeQuery( sql );
return getCrossTabDataValues( resultSet, operands );
@@ -239,4 +224,39 @@
return values;
}
+
+ private String getCrossTabSelectJoin( List<String> keys )
+ {
+ String sql = "SELECT";
+
+ if ( keys.size() == 1 )
+ {
+ sql += " * FROM " + TABLE_PREFIX + keys.get( 0 ) + " AS c";
+ }
+ else
+ {
+ sql += " c.periodid, c.sourceid";
+
+ for ( String key : keys )
+ {
+ sql += ", " + TABLE_PREFIX + key + ".*";
+ }
+
+ sql += " FROM ( SELECT DISTINCT periodid, sourceid FROM (";
+
+ for ( String key : keys )
+ {
+ sql += " SELECT periodid, sourceid FROM " + TABLE_PREFIX + key + " UNION";
+ }
+
+ sql = trimEnd( sql, " UNION".length() ) + " ) AS x ) AS c";
+
+ for ( String key : keys )
+ {
+ sql += " LEFT JOIN " + TABLE_PREFIX + key + " ON c.periodid = " + TABLE_PREFIX + key + ".periodid AND c.sourceid = " + TABLE_PREFIX + key + ".sourceid";
+ }
+ }
+
+ return sql;
+ }
}
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java'
--- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2011-02-15 13:44:03 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/TextUtils.java 2011-04-13 16:09:45 +0000
@@ -62,6 +62,24 @@
return string.substring( beginIndex, endIndex );
}
+
+ /**
+ * Trims the given string from the end.
+ *
+ * @param value the value to trim.
+ * @param length the number of characters to trim.
+ * @return the trimmed value, empty if given value is null or length is higher
+ * than the value length.
+ */
+ public static String trimEnd( String value, int length )
+ {
+ if ( value == null || length > value.length() )
+ {
+ return "";
+ }
+
+ return value.substring( 0, value.length() - length );
+ }
/**
* Transforms a collection of Integers into a comma delimited String.
=== modified file 'dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java'
--- dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java 2011-01-25 19:27:15 +0000
+++ dhis-2/dhis-support/dhis-support-system/src/test/java/org/hisp/dhis/system/util/TextUtilsTest.java 2011-04-13 16:09:45 +0000
@@ -27,19 +27,20 @@
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
-import junit.framework.TestCase;
+import static org.hisp.dhis.system.util.TextUtils.*;
+import static junit.framework.Assert.*;
-import static org.hisp.dhis.system.util.TextUtils.subString;
+import org.junit.Test;
/**
* @author Lars Helge Overland
* @version $Id $
*/
public class TextUtilsTest
- extends TestCase
{
private static final String STRING = "abcdefghij";
+ @Test
public void testSubString()
{
assertEquals( "abcdefghij", subString( STRING, 0, 10 ) );
@@ -56,4 +57,10 @@
assertEquals( "", subString( STRING, 4, 0 ) );
}
+
+ @Test
+ public void testTrim()
+ {
+ assertEquals( "abcdefgh", trimEnd( "abcdefghijkl", 4 ) );
+ }
}
