dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #07076
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1986: Fix mysql bug on Organisation Unit Merge function.
------------------------------------------------------------
revno: 1986
committer: Quang <Quang@Quang-PC>
branch nick: trunk
timestamp: Sat 2010-08-28 00:55:48 +0700
message:
Fix mysql bug on Organisation Unit Merge 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-19 21:37:22 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java 2010-08-27 17:55:48 +0000
@@ -118,35 +118,14 @@
// Move from source to destination where destination does not exist
- String sql =
- "UPDATE datavalue AS d1 SET sourceid=" + destId + " " +
- "WHERE sourceid=" + sourceId + " " +
- "AND NOT EXISTS ( " +
- "SELECT * from datavalue AS d2 " +
- "WHERE d2.sourceid=" + destId + " " +
- "AND d1.dataelementid=d2.dataelementid " +
- "AND d1.periodid=d2.periodid " +
- "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
-
+ String sql = statementBuilder.getMoveDataValueToDestination( sourceId, destId );
log.info( sql );
jdbcTemplate.execute( sql );
// Summarize destination and source where matching
-
- sql =
- "UPDATE datavalue AS d1 SET value=( " +
- "SELECT SUM( CAST( value AS " + statementBuilder.getDoubleColumnType() + " ) ) " +
- "FROM datavalue as d2 " +
- "WHERE d1.dataelementid=d2.dataelementid " +
- "AND d1.periodid=d2.periodid " +
- "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid " +
- "AND d2.sourceid IN ( " + destId + ", " + sourceId + " ) ) " +
- "FROM dataelement AS de " +
- "WHERE d1.sourceid=" + destId + " " +
- "AND d1.dataelementid=de.dataelementid " +
- "AND de.valuetype='int';";
- log.info( sql );
+ sql = statementBuilder.getSummarizeDestinationAndSourceWhereMatching( sourceId, destId );
+ log.info( sql );
jdbcTemplate.execute( sql );
// TODO also deal with bool and string
=== 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-06-23 17:50:25 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-08-27 17:55:48 +0000
@@ -102,4 +102,9 @@
* @return
*/
String getDropDatasetForeignKeyForDataEntryFormTable();
+
+ String getMoveDataValueToDestination( int sourceId, int destinationId );
+
+ String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destinationId );
+
}
=== 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-06-23 17:50:25 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-08-27 17:55:48 +0000
@@ -134,4 +134,24 @@
{
return "ALTER TABLE dataentryform DROP FOREIGN KEY fk_dataentryform_datasetid;" ;
}
+
+ @Override
+ public String getMoveDataValueToDestination( int sourceId, int destinationId )
+ {
+ return "UPDATE datavalue AS d1 SET sourceid=" + destinationId + " " + "WHERE sourceid=" + sourceId + " "
+ + "AND NOT EXISTS ( " + "SELECT * from datavalue AS d2 " + "WHERE d2.sourceid=" + destinationId + " "
+ + "AND d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
+ }
+
+ @Override
+ public String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destId )
+ {
+ return "UPDATE datavalue AS d1 SET value=( " + "SELECT SUM( CAST( value AS "
+ + getDoubleColumnType() + " ) ) " + "FROM datavalue as d2 "
+ + "WHERE d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid " + "AND d2.sourceid IN ( " + destId + ", "
+ + sourceId + " ) ) " + "FROM dataelement AS de " + "WHERE d1.sourceid=" + destId + " "
+ + "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
+ }
}
=== 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-06-23 17:50:25 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-08-27 17:55:48 +0000
@@ -131,4 +131,25 @@
{
return "ALTER TABLE dataentryform DROP CONSTRAINT fk_dataentryform_datasetid;";
}
+
+ @Override
+ public String getMoveDataValueToDestination( int sourceId, int destinationId )
+ {
+ return "UPDATE datavalue AS d1 SET sourceid=" + destinationId + " " + "WHERE sourceid=" + sourceId + " "
+ + "AND NOT EXISTS ( " + "SELECT * from datavalue AS d2 " + "WHERE d2.sourceid=" + destinationId + " "
+ + "AND d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
+ }
+
+ @Override
+ public String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destId )
+ {
+ return "UPDATE datavalue AS d1 SET value=( " + "SELECT SUM( CAST( value AS "
+ + getDoubleColumnType() + " ) ) " + "FROM datavalue as d2 "
+ + "WHERE d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid " + "AND d2.sourceid IN ( " + destId + ", "
+ + sourceId + " ) ) " + "FROM dataelement AS de " + "WHERE d1.sourceid=" + destId + " "
+ + "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
+ }
+
}
=== 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-06-23 17:50:25 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-08-27 17:55:48 +0000
@@ -134,4 +134,23 @@
{
return "ALTER TABLE dataentryform DROP FOREIGN KEY fk_dataentryform_datasetid;" ;
}
+
+ @Override
+ public String getMoveDataValueToDestination( int sourceId, int destinationId )
+ {
+ return "UPDATE datavalue AS d1 SET sourceid=" + destinationId + " " + "WHERE sourceid=" + sourceId + " "
+ + "AND NOT EXISTS ( " + "SELECT * from ( SELECT * FROM datavalue ) AS d2 " + "WHERE d2.sourceid=" + destinationId + " "
+ + "AND d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
+ }
+
+ @Override
+ public String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destId )
+ {
+ return "UPDATE datavalue AS d1 SET value=( " + "SELECT SUM( value ) " + "FROM (SELECT * FROM datavalue) as d2 "
+ + "WHERE d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid " + "AND d2.sourceid IN ( " + destId + ", "
+ + sourceId + " ) ) " + "WHERE d1.sourceid=" + destId + " "
+ + "AND d1.dataelementid in ( SELECT dataelementid FROM dataelement WHERE valuetype='int' );";
+ }
}
=== 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-06-23 17:50:25 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-08-27 17:55:48 +0000
@@ -134,4 +134,24 @@
{
return "ALTER TABLE dataentryform DROP CONSTRAINT fk_dataentryform_datasetid;" ;
}
+
+ @Override
+ public String getMoveDataValueToDestination( int sourceId, int destinationId )
+ {
+ return "UPDATE datavalue AS d1 SET sourceid=" + destinationId + " " + "WHERE sourceid=" + sourceId + " "
+ + "AND NOT EXISTS ( " + "SELECT * from datavalue AS d2 " + "WHERE d2.sourceid=" + destinationId + " "
+ + "AND d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
+ }
+
+ @Override
+ public String getSummarizeDestinationAndSourceWhereMatching( int sourceId, int destId )
+ {
+ return "UPDATE datavalue AS d1 SET value=( " + "SELECT SUM( CAST( value AS "
+ + getDoubleColumnType() + " ) ) " + "FROM datavalue as d2 "
+ + "WHERE d1.dataelementid=d2.dataelementid " + "AND d1.periodid=d2.periodid "
+ + "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid " + "AND d2.sourceid IN ( " + destId + ", "
+ + sourceId + " ) ) " + "FROM dataelement AS de " + "WHERE d1.sourceid=" + destId + " "
+ + "AND d1.dataelementid=de.dataelementid " + "AND de.valuetype='int';";
+ }
}
