dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #06907
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1944: Splitting multiple SQL statements into single, not supported by mysql
------------------------------------------------------------
revno: 1944
committer: Lars <larshelg@larshelg-laptop>
branch nick: trunk
timestamp: Thu 2010-08-19 23:37:22 +0200
message:
Splitting multiple SQL statements into single, not supported by mysql
modified:
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java
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/dataprune/jdbc/JdbcDataPruneStore.java
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataelement/hibernate/HibernateDataElementStore.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/dataarchive/jdbc/JdbcDataArchiveStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java 2010-05-18 18:37:07 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java 2010-08-19 21:37:22 +0000
@@ -58,78 +58,79 @@
public void archiveData( Date startDate, Date endDate )
{
- final String sql =
-
- // Move data from datavalue to datavaluearchive
-
+ // Move data from datavalue to datavaluearchive
+
+ String sql =
"INSERT INTO datavaluearchive ( " +
"SELECT d.* FROM datavalue AS d " +
"JOIN period as p USING (periodid) " +
"WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' );" +
-
- // Delete data from datavalue
-
+ "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete data from datavalue
+
+ sql =
"DELETE FROM datavalue AS d " +
"USING period as p " +
"WHERE d.periodid=p.periodid " +
"AND p.startdate>='" + getMediumDateString( startDate ) + "' " +
"AND p.enddate<='" + getMediumDateString( endDate ) + "';";
- log.info( sql );
-
+ log.info( sql );
jdbcTemplate.execute( sql );
}
public void unArchiveData( Date startDate, Date endDate )
{
- final String sql =
-
- // Move data from datavalue to datavaluearchive
-
+ // Move data from datavalue to datavaluearchive
+
+ String sql =
"INSERT INTO datavalue ( " +
"SELECT a.* FROM datavaluearchive AS a " +
"JOIN period as p USING (periodid) " +
"WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' );" +
-
- // Delete data from datavalue
-
+ "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete data from datavalue
+
+ sql =
"DELETE FROM datavaluearchive AS a " +
"USING period AS p " +
"WHERE a.periodid=p.periodid " +
"AND p.startdate>='" + getMediumDateString( startDate ) + "' " +
"AND p.enddate<='" + getMediumDateString( endDate ) + "';";
- log.info( sql );
-
+ log.info( sql );
jdbcTemplate.execute( sql );
}
public int getNumberOfOverlappingValues()
{
- final String sql =
+ String sql =
"SELECT COUNT(*) FROM datavaluearchive " +
"JOIN datavalue USING (dataelementid, periodid, sourceid, categoryoptioncomboid);";
- log.info( sql );
-
+ log.info( sql );
return jdbcTemplate.queryForInt( sql );
}
public int getNumberOfArchivedValues()
{
- final String sql =
- "SELECT COUNT(*) FROM datavaluearchive;";
-
- log.info( sql );
-
+ String sql = "SELECT COUNT(*) FROM datavaluearchive;";
+
+ log.info( sql );
return jdbcTemplate.queryForInt( sql );
}
public void deleteRegularOverlappingData()
{
- final String sql =
+ String sql =
"DELETE FROM datavalue AS d " +
"USING datavaluearchive AS a " +
"WHERE d.dataelementid=a.dataelementid " +
@@ -137,14 +138,13 @@
"AND d.sourceid=a.sourceid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid;";
- log.info( sql );
-
+ log.info( sql );
jdbcTemplate.execute( sql );
}
public void deleteArchivedOverlappingData()
{
- final String sql =
+ String sql =
"DELETE FROM datavaluearchive AS a " +
"USING datavalue AS d " +
"WHERE a.dataelementid=d.dataelementid " +
@@ -152,27 +152,29 @@
"AND a.sourceid=d.sourceid " +
"AND a.categoryoptioncomboid=d.categoryoptioncomboid;";
- log.info( sql );
-
+ log.info( sql );
jdbcTemplate.execute( sql );
}
public void deleteOldestOverlappingData()
{
- final String sql =
-
- // Delete overlaps from datavalue which are older than datavaluearchive
-
+ // Delete overlaps from datavalue which are older than datavaluearchive
+
+ String sql =
"DELETE FROM datavalue AS d " +
"USING datavaluearchive AS a " +
"WHERE d.dataelementid=a.dataelementid " +
"AND d.periodid=a.periodid " +
"AND d.sourceid=a.sourceid " +
"AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
- "AND d.lastupdated<a.lastupdated;" +
-
- // Delete overlaps from datavaluearchive which are older than datavalue
-
+ "AND d.lastupdated<a.lastupdated;";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete overlaps from datavaluearchive which are older than datavalue
+
+ sql =
"DELETE FROM datavaluearchive AS a " +
"USING datavalue AS d " +
"WHERE a.dataelementid=d.dataelementid " +
@@ -181,8 +183,7 @@
"AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
"AND a.lastupdated<=d.lastupdated;";
- log.info( sql );
-
+ log.info( sql );
jdbcTemplate.execute( sql );
}
}
=== 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-05-18 18:37:07 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/datamerge/jdbc/JdbcDataMergeStore.java 2010-08-19 21:37:22 +0000
@@ -67,14 +67,9 @@
final int sourceDataElementId = sourceDataElemenet.getId();
final int sourceCategoryOptionComboId = sourceCategoryOptionCombo.getId();
- // -------------------------------------------------------------------------
- // DataMergeStore implementation
- // -------------------------------------------------------------------------
-
- final String sql =
-
- // Move from source to destination where destination does not exist
-
+ // 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 ( " +
@@ -82,28 +77,37 @@
"WHERE d2.dataelementid=" + destDataElementId + " " +
"AND d2.categoryoptioncomboid=" + destCategoryOptionComboId + " " +
"AND d1.periodid=d2.periodid " +
- "AND d1.sourceid=d2.sourceid ); " +
-
- // Update destination with source where source is last updated
-
+ "AND d1.sourceid=d2.sourceid );";
+
+ 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 + ";" +
-
- // Delete remaining source data value audits
-
- "DELETE FROM datavalueaudit WHERE dataelementid=" + sourceDataElementId + " AND categoryoptioncomboid=" + sourceCategoryOptionComboId + ";" +
-
- // Delete remaining source data values
-
- "DELETE FROM datavalue WHERE dataelementid=" + sourceDataElementId + " AND categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
-
- log.info( sql );
-
+ "AND d2.dataelementid=" + sourceDataElementId + " AND d2.categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete remaining source data value audits
+
+ sql = "DELETE FROM datavalue_audit WHERE dataelementid=" + sourceDataElementId + " AND categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete remaining source data values
+
+ sql = "DELETE FROM datavalue WHERE dataelementid=" + sourceDataElementId + " AND categoryoptioncomboid=" + sourceCategoryOptionComboId + ";";
+
+ log.info( sql );
jdbcTemplate.execute( sql );
}
@@ -111,11 +115,10 @@
{
final int destId = dest.getId();
final int sourceId = source.getId();
+
+ // Move from source to destination where destination does not exist
- final String sql =
-
- // 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 ( " +
@@ -123,10 +126,14 @@
"WHERE d2.sourceid=" + destId + " " +
"AND d1.dataelementid=d2.dataelementid " +
"AND d1.periodid=d2.periodid " +
- "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );" +
-
- // Summarize destination and source where matching
-
+ "AND d1.categoryoptioncomboid=d2.categoryoptioncomboid );";
+
+ 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 " +
@@ -137,24 +144,32 @@
"FROM dataelement AS de " +
"WHERE d1.sourceid=" + destId + " " +
"AND d1.dataelementid=de.dataelementid " +
- "AND de.valuetype='int';" +
-
- //TODO also deal with bool and string
-
- // Delete remaining source data value audits
-
- "DELETE FROM datavalueaudit WHERE sourceid=" + sourceId + ";" +
-
- // Delete remaining source data values
-
- "DELETE FROM datavalue WHERE sourceid=" + sourceId + ";" +
-
- // Delete complete data set registrations
-
- "DELETE FROM completedatasetregistration WHERE sourceid=" + sourceId + ";";
-
- log.info( sql );
-
+ "AND de.valuetype='int';";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // TODO also deal with bool and string
+
+ // Delete remaining source data value audits
+
+ sql = "DELETE FROM datavalue_audit WHERE sourceid=" + sourceId + ";";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete remaining source data values
+
+ sql = "DELETE FROM datavalue WHERE sourceid=" + sourceId + ";";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete complete data set registrations
+
+ sql = "DELETE FROM completedatasetregistration WHERE sourceid=" + sourceId + ";";
+
+ log.info( sql );
jdbcTemplate.execute( sql );
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataprune/jdbc/JdbcDataPruneStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataprune/jdbc/JdbcDataPruneStore.java 2010-07-04 04:39:44 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataprune/jdbc/JdbcDataPruneStore.java 2010-08-19 21:37:22 +0000
@@ -64,8 +64,6 @@
String orgUnitIds = TextUtils.getCommaDelimitedString( ConversionUtils.getIdentifiers( OrganisationUnit.class, orgUnits )) ;
- long before = System.currentTimeMillis();
-
String sql = "delete from datasetlocksource where sourceid in (" + orgUnitIds + ");";
jdbcTemplate.execute( sql );
@@ -134,6 +132,6 @@
sql = "delete from source where sourceid in (" + orgUnitIds + ");";
jdbcTemplate.execute( sql );
- log.info( "Deleting " + orgUnits.size() + " organisations units sucessfully in " + (System.currentTimeMillis() - before) + " ms.");
+ log.info( "Deleting " + orgUnits.size() + " organisations units sucessfully" );
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataelement/hibernate/HibernateDataElementStore.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataelement/hibernate/HibernateDataElementStore.java 2010-05-18 15:42:54 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/dataelement/hibernate/HibernateDataElementStore.java 2010-08-19 21:37:22 +0000
@@ -422,7 +422,7 @@
"FROM dataelement as de " +
"JOIN categorycombo as cc on de.categorycomboid=cc.categorycomboid " +
"JOIN categorycombos_optioncombos as ccoc on cc.categorycomboid=ccoc.categorycomboid " +
- "JOIN categoryoptioncomboname as cocn on ccoc.categoryoptioncomboid=cocn.categoryoptioncomboid;";
+ "JOIN _categoryoptioncomboname as cocn on ccoc.categoryoptioncomboid=cocn.categoryoptioncomboid;";
try
{
@@ -447,7 +447,7 @@
"FROM dataelement as de " +
"JOIN categorycombo as cc on de.categorycomboid=cc.categorycomboid " +
"JOIN categorycombos_optioncombos as ccoc on cc.categorycomboid=ccoc.categorycomboid " +
- "JOIN categoryoptioncomboname as cocn on ccoc.categoryoptioncomboid=cocn.categoryoptioncomboid " +
+ "JOIN _categoryoptioncomboname as cocn on ccoc.categoryoptioncomboid=cocn.categoryoptioncomboid " +
"WHERE de.dataelementid IN (" + dataElementString + ");";
try
