dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #07165
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2026: Fix mysql bug on Duplicate Data Elimination function.
------------------------------------------------------------
revno: 2026
committer: Quang <Quang@Quang-PC>
branch nick: trunk
timestamp: Tue 2010-08-31 22:58:23 +0700
message:
Fix mysql bug on Duplicate Data Elimination function.
modified:
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java
dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.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-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java 2010-08-31 15:58:23 +0000
@@ -69,30 +69,13 @@
// Move from source to destination where destination does not exist
- String sql =
- "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid=" + destCategoryOptionComboId + " " +
- "WHERE dataelementid=" + sourceDataElementId + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " +
- "AND NOT EXISTS ( " +
- "SELECT * FROM datavalue AS d2 " +
- "WHERE d2.dataelementid=" + destDataElementId + " " +
- "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " +
- "AND d1.periodid=d2.periodid " +
- "AND d1.sourceid=d2.sourceid );";
-
+ String sql = statementBuilder.getMoveFromSourceToDestination( destDataElementId, destCategoryOptionComboId, sourceDataElementId, sourceCategoryOptionComboId );
log.info( sql );
jdbcTemplate.execute( sql );
// Update destination with source where source is last update
- sql =
- "UPDATE datavalue SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup " +
- "FROM datavalue AS d2 " +
- "WHERE datavalue.periodid=d2.periodid " +
- "AND datavalue.sourceid=d2.sourceid " +
- "AND datavalue.lastupdated<d2.lastupdated " +
- "AND datavalue.dataelementid=" + destDataElementId + " AND datavalue.categoryoptioncomboid=" + destCategoryOptionComboId + " " +
- "AND d2.dataelementid=" + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
-
+ sql = statementBuilder.getUpdateDateDestination( destDataElementId, destCategoryOptionComboId, sourceDataElementId, sourceCategoryOptionComboId );
log.info( sql );
jdbcTemplate.execute( sql );
@@ -150,5 +133,6 @@
log.info( sql );
jdbcTemplate.execute( sql );
+
}
}
=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-08-31 15:58:23 +0000
@@ -104,7 +104,12 @@
String getDropDatasetForeignKeyForDataEntryFormTable();
String getMoveDataValueToDestination( int sourceId, int destinationId );
-
+
String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destinationId );
+ String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId );
+
+ String getUpdateDateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId );
}
=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-08-31 15:58:23 +0000
@@ -154,4 +154,35 @@
+ sourceId + " ) ) " + "FROM dataelement AS de " + "WHERE d1.sourceid=" + destId + " "
+ "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
}
+
+ @Override
+ public String getUpdateDateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid="
+ + destCategoryOptionComboId + " " + "WHERE dataelementid=" + sourceDataElementId
+ + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " + "AND NOT EXISTS ( "
+ + "SELECT * FROM datavalue AS d2 " + "WHERE d2.dataelementid=" + destDataElementId + " "
+ + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " + "AND d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid );";
+
+ }
+
+ @Override
+ public String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup "
+ + "FROM datavalue AS d2 "
+ + "WHERE datavalue.periodid=d2.periodid "
+ + "AND datavalue.sourceid=d2.sourceid "
+ + "AND datavalue.lastupdated<d2.lastupdated "
+ + "AND datavalue.dataelementid="
+ + destDataElementId
+ + " AND datavalue.categoryoptioncomboid="
+ + destCategoryOptionComboId
+ + " "
+ + "AND d2.dataelementid="
+ + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+ }
}
=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-08-31 15:58:23 +0000
@@ -152,4 +152,35 @@
+ "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
}
+
+ @Override
+ public String getUpdateDateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid="
+ + destCategoryOptionComboId + " " + "WHERE dataelementid=" + sourceDataElementId
+ + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " + "AND NOT EXISTS ( "
+ + "SELECT * FROM datavalue AS d2 " + "WHERE d2.dataelementid=" + destDataElementId + " "
+ + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " + "AND d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid );";
+
+ }
+
+ @Override
+ public String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup "
+ + "FROM datavalue AS d2 "
+ + "WHERE datavalue.periodid=d2.periodid "
+ + "AND datavalue.sourceid=d2.sourceid "
+ + "AND datavalue.lastupdated<d2.lastupdated "
+ + "AND datavalue.dataelementid="
+ + destDataElementId
+ + " AND datavalue.categoryoptioncomboid="
+ + destCategoryOptionComboId
+ + " "
+ + "AND d2.dataelementid="
+ + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+ }
}
=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-08-31 15:58:23 +0000
@@ -153,4 +153,35 @@
+ sourceId + " ) ) " + "WHERE d1.sourceid=" + destId + " "
+ "AND d1.dataelementid in ( SELECT dataelementid FROM dataelement WHERE valuetype='int' );";
}
+
+ @Override
+ public String getUpdateDateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid="
+ + destCategoryOptionComboId + " " + "WHERE dataelementid=" + sourceDataElementId
+ + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " + "AND NOT EXISTS ( "
+ + "SELECT * FROM (SELECT * FROM datavalue) AS d2 " + "WHERE d2.dataelementid=" + destDataElementId + " "
+ + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " + "AND d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid );";
+
+ }
+
+ @Override
+ public String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue d1, datavalue d2 SET d1.value=d2.value,d1.storedby=d2.storedby,d1.lastupdated=d2.lastupdated,d1.comment=d2.comment,d1.followup=d2.followup "
+ + "WHERE d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid "
+ + "AND d1.lastupdated<d2.lastupdated "
+ + "AND d1.dataelementid="
+ + destDataElementId
+ + " AND d1.categoryoptioncomboid="
+ + destCategoryOptionComboId
+ + " "
+ + "AND d2.dataelementid="
+ + sourceDataElementId
+ + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+ }
}
=== modified file 'dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java'
--- dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-08-27 17:55:48 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-08-31 15:58:23 +0000
@@ -154,4 +154,35 @@
+ sourceId + " ) ) " + "FROM dataelement AS de " + "WHERE d1.sourceid=" + destId + " "
+ "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
}
+
+ @Override
+ public String getUpdateDateDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue AS d1 SET dataelementid=" + destDataElementId + ", categoryoptioncomboid="
+ + destCategoryOptionComboId + " " + "WHERE dataelementid=" + sourceDataElementId
+ + " and categoryoptioncomboid=" + sourceCategoryOptionComboId + " " + "AND NOT EXISTS ( "
+ + "SELECT * FROM datavalue AS d2 " + "WHERE d2.dataelementid=" + destDataElementId + " "
+ + "AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " + "AND d1.periodid=d2.periodid "
+ + "AND d1.sourceid=d2.sourceid );";
+
+ }
+
+ @Override
+ public String getMoveFromSourceToDestination( int destDataElementId, int destCategoryOptionComboId,
+ int sourceDataElementId, int sourceCategoryOptionComboId )
+ {
+ return "UPDATE datavalue SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup "
+ + "FROM datavalue AS d2 "
+ + "WHERE datavalue.periodid=d2.periodid "
+ + "AND datavalue.sourceid=d2.sourceid "
+ + "AND datavalue.lastupdated<d2.lastupdated "
+ + "AND datavalue.dataelementid="
+ + destDataElementId
+ + " AND datavalue.categoryoptioncomboid="
+ + destCategoryOptionComboId
+ + " "
+ + "AND d2.dataelementid="
+ + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+ }
}
