dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #08293
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 1979: Fix bug: Exception into data-archive module.
------------------------------------------------------------
revno: 1979
committer: Tran Chau <tran.hispvietnam@xxxxxxxxx>
branch nick: dhis2
timestamp: Sat 2010-10-30 18:54:24 +0700
message:
Fix bug: Exception into data-archive module.
modified:
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java
dhis-2/dhis-services/dhis-service-options/src/main/resources/help_content.xml
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
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties
--
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-10-29 14:09:49 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/JdbcDataArchiveStore.java 2010-10-30 11:54:24 +0000
@@ -70,178 +70,85 @@
public void archiveData( Date startDate, Date endDate )
{
// Move data from datavalue to datavaluearchive
-
- String sql =
- "INSERT INTO datavaluearchive ( " +
- "SELECT d.* FROM datavalue AS d " +
- "JOIN period as p ON ( d.periodid=p.periodid ) " +
- "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
+ String sql = "INSERT INTO datavaluearchive ( " + "SELECT d.* FROM datavalue AS d "
+ + "JOIN period AS p ON d.periodid=p.periodid " + "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
+ "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
- log.info( sql );
+ log.info( sql );
jdbcTemplate.execute( sql );
-
- // Delete data from datavalue_audit
-
- sql =
- "DELETE FROM datavalue_audit WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue_audit AS d " +
- "JOIN period AS p ON ( d.periodid=p.periodid ) " +
- "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' )";
-
- log.info( sql );
- jdbcTemplate.execute( sql );
-
+
// Delete data from datavalue
-
- sql =
- "DELETE FROM datavalue WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue AS d " +
- "JOIN period AS p ON ( d.periodid=p.periodid ) " +
- "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' )";
-
- log.info( sql );
- jdbcTemplate.execute( sql );
+ sql = statementBuilder.archiveData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
}
public void unArchiveData( Date startDate, Date endDate )
{
- // Move data from datavaluearchive to datavalue
-
- String sql =
- "INSERT INTO datavalue ( " +
- "SELECT a.* FROM datavaluearchive AS a " +
- "JOIN period as p ON ( a.periodid=p.periodid ) " +
- "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
-
- log.info( sql );
- jdbcTemplate.execute( sql );
-
- // Delete data from datavaluearchive
-
- sql =
- "DELETE FROM datavaluearchive WHERE EXISTS ( " +
- "SELECT 1 FROM datavaluearchive AS a " +
- "JOIN period AS p ON ( a.periodid=p.periodid ) " +
- "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
- "AND p.enddate<='" + getMediumDateString( endDate ) + "' )";
-
- log.info( sql );
- jdbcTemplate.execute( sql );
+ // Move data from datavalue to datavaluearchive
+ String sql = "INSERT INTO datavalue ( " + "SELECT a.* FROM datavaluearchive AS a "
+ + "JOIN period AS p ON a.periodid=p.periodid " + "WHERE p.startdate>='" + getMediumDateString( startDate ) + "' " +
+ "AND p.enddate<='" + getMediumDateString( endDate ) + "' );";
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
+
+ // Delete data from datavalue
+
+ sql = statementBuilder.unArchiveData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
+
+ log.info( sql );
+ jdbcTemplate.execute( sql );
}
-
+
public int getNumberOfOverlappingValues()
{
- String sql =
- "SELECT COUNT(*) FROM datavaluearchive a " +
- "JOIN datavalue d ON (a.dataelementid=d.dataelementid AND a.periodid=d.periodid AND a.sourceid=d.sourceid AND a.categoryoptioncomboid=d.categoryoptioncomboid);";
-
- log.info( sql );
+ String sql = "SELECT COUNT(*) FROM datavaluearchive AS a "
+ + "JOIN datavalue AS d ON a.dataelementid=d.dataelementid " + "AND a.periodid=d.periodid "
+ + "AND a.sourceid=d.sourceid " + "AND a.categoryoptioncomboid=d.categoryoptioncomboid;";
+
+ log.info( sql );
+
return jdbcTemplate.queryForInt( sql );
}
-
+
public int getNumberOfArchivedValues()
{
- String sql = "SELECT COUNT(*) FROM datavaluearchive;";
-
- log.info( sql );
+ String sql = "SELECT COUNT(*) as dem FROM datavaluearchive;";
+
+ log.info( sql );
return jdbcTemplate.queryForInt( sql );
}
-
+
public void deleteRegularOverlappingData()
{
- // Delete from datavalue_audit
-
- String sql =
- "DELETE FROM datavalue_audit WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue_audit AS d " +
- "JOIN datavaluearchive AS a " +
- "ON ( d.dataelementid=a.dataelementid " +
- "AND d.periodid=a.periodid " +
- "AND d.sourceid=a.sourceid " +
- "AND d.categoryoptioncomboid=a.categoryoptioncomboid ) )";
+ String sql = statementBuilder.deleteRegularOverlappingData();
- log.info( sql );
- jdbcTemplate.execute( sql );
-
- // Delete from datavelue
-
- sql =
- "DELETE FROM datavalue WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue AS d " +
- "JOIN datavaluearchive AS a " +
- "ON ( d.dataelementid=a.dataelementid " +
- "AND d.periodid=a.periodid " +
- "AND d.sourceid=a.sourceid " +
- "AND d.categoryoptioncomboid=a.categoryoptioncomboid ) )";
-
- log.info( sql );
+ log.info( sql );
jdbcTemplate.execute( sql );
}
-
+
public void deleteArchivedOverlappingData()
{
- String sql =
- "DELETE FROM datavaluearchive WHERE EXISTS ( " +
- "SELECT 1 FROM datavaluearchive AS a " +
- "JOIN datavalue AS d " +
- "ON ( a.dataelementid=d.dataelementid " +
- "AND a.periodid=d.periodid " +
- "AND a.sourceid=d.sourceid " +
- "AND a.categoryoptioncomboid=d.categoryoptioncomboid ) )";
+ String sql = statementBuilder.deleteArchivedOverlappingData();
- log.info( sql );
+ log.info( sql );
jdbcTemplate.execute( sql );
- }
+ }
public void deleteOldestOverlappingData()
{
- // Delete overlaps from datavalue_audit which are older than datavaluearchive
-
- String sql =
- "DELETE FROM datavalue_audit WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue_audit AS d " +
- "JOIN datavaluearchive AS a " +
- "ON ( d.dataelementid=a.dataelementid " +
- "AND d.periodid=a.periodid " +
- "AND d.sourceid=a.sourceid " +
- "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
- "AND d.lastupdated<a.lastupdated ) )";
-
- log.info( sql );
- jdbcTemplate.execute( sql );
-
// Delete overlaps from datavalue which are older than datavaluearchive
-
- sql =
- "DELETE FROM datavalue WHERE EXISTS ( " +
- "SELECT 1 FROM datavalue AS d " +
- "JOIN datavaluearchive AS a " +
- "ON ( d.dataelementid=a.dataelementid " +
- "AND d.periodid=a.periodid " +
- "AND d.sourceid=a.sourceid " +
- "AND d.categoryoptioncomboid=a.categoryoptioncomboid " +
- "AND d.lastupdated<a.lastupdated ) )";
-
- log.info( sql );
+ String sql = statementBuilder.deleteOldestOverlappingDataValue();
+
+ log.info( sql );
jdbcTemplate.execute( sql );
-
+
// Delete overlaps from datavaluearchive which are older than datavalue
-
- sql =
- "DELETE FROM datavaluearchive WHERE EXISTS ( " +
- "SELECT 1 FROM datavaluearchive AS a " +
- "JOIN datavalue AS d " +
- "ON ( a.dataelementid=d.dataelementid " +
- "AND a.periodid=d.periodid " +
- "AND a.sourceid=d.sourceid " +
- "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
- "AND a.lastupdated<=d.lastupdated ) )";
-
- log.info( sql );
+ sql = statementBuilder.deleteOldestOverlappingArchiveData();
+
+ log.info( sql );
jdbcTemplate.execute( sql );
}
@@ -252,7 +159,6 @@
public void archivePatientData( Date startDate, Date endDate )
{
// Move data from patientdatavalue to patientdatavaluearchive
-
String sql ="INSERT INTO patientdatavaluearchive ( " + "SELECT pdv.* FROM patientdatavalue AS pdv "
+ "INNER JOIN programstageinstance AS psi "
+ "ON pdv.programstageinstanceid = psi.programstageinstanceid "
@@ -265,7 +171,6 @@
jdbcTemplate.execute( sql );
// Delete data from patientdatavalue
-
sql = statementBuilder.archivePatientData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
log.info( sql );
@@ -275,8 +180,7 @@
public void unArchivePatientData( Date startDate, Date endDate )
{
// Move data from patientdatavalue to patientdatavaluearchive
-
- String sql ="INSERT INTO patientdatavalue ( " + "SELECT * FROM patientdatavaluearchive AS pdv "
+ String sql ="INSERT INTO patientdatavalue ( " + "SELECT pdv.* FROM patientdatavaluearchive AS pdv "
+ "INNER JOIN programstageinstance AS psi "
+ "ON pdv.programstageinstanceid = psi.programstageinstanceid "
+ "INNER JOIN programinstance AS pi "
@@ -288,7 +192,6 @@
jdbcTemplate.execute( sql );
// Delete data from patientdatavalue
-
sql = statementBuilder.unArchivePatientData( getMediumDateString( startDate ), getMediumDateString( endDate ) );
log.info( sql );
@@ -334,17 +237,16 @@
public void deleteOldestOverlappingPatientData()
{
// Delete overlaps from patientdatavalue which are older than patientdatavaluearchive
-
String sql = statementBuilder.deleteOldestOverlappingPatientDataValue();
log.info( sql );
jdbcTemplate.execute( sql );
// Delete overlaps from patientdatavaluearchive which are older than patientdatavalue
-
sql = statementBuilder.deleteOldestOverlappingPatientArchiveData();
log.info( sql );
jdbcTemplate.execute( sql );
}
+
}
=== modified file 'dhis-2/dhis-services/dhis-service-options/src/main/resources/help_content.xml'
--- dhis-2/dhis-services/dhis-service-options/src/main/resources/help_content.xml 2010-10-24 15:41:13 +0000
+++ dhis-2/dhis-services/dhis-service-options/src/main/resources/help_content.xml 2010-10-30 11:54:24 +0000
@@ -556,11 +556,11 @@
<para>In some cases you might end up with overlapping data. For instance one might archive data for a given timespan, then later enter data for a period in that timespan. In such cases the system will automatically overwrite the oldest of the overlapping values with the newest during the archive or unarchive operation.</para>
</section>
<section id="patientDataArchive">
- <title>Patient Data Archive</title>
- <para>The purpose of the patient data archive function is to move patient data value which is currently not being used for analysis to a secondary storage location in order to improve performance of the application. Data can be both archived and unarchived. When archiving data one moves it from the primary storage to the secondary storage location, while unarchiving moves it from the secondary storage location to the primary. Analysis functionality in DHIS 2 heavily utilizes queries to the data value database table, and by reducing the size of this table these operations will be significantly faster. Typically one would want to archive patient data that is older than two years.</para>
- <para>To archive patient data, first enter a start date and an end date for the time span of the data which should be archived. Then press the archive button. The operation might take a few minutes.</para>
- <para>To unarchive patient data, first enter a start date and an end date for the time span of the data which should be unarchived. Then press the unarchive button. The operation might take a few minutes.</para>
- <para>In some cases you might end up with overlapping data. For instance one might archive patient data for a given timespan, then later enter data for a period in that timespan. In such cases the system will automatically overwrite the oldest of the overlapping values with the newest during the archive or unarchive operation.</para>
+ <title>Beneficiary Data Archive</title>
+ <para>The purpose of the beneficiary data archive function is to move beneficiary data value which is currently not being used for analysis to a secondary storage location in order to improve performance of the application. Data can be both archived and unarchived. When archiving data one moves it from the primary storage to the secondary storage location, while unarchiving moves it from the secondary storage location to the primary. Analysis functionality in DHIS 2 heavily utilizes queries to the data value database table, and by reducing the size of this table these operations will be significantly faster. Typically one would want to archive beneficiary data that is older than two years.</para>
+ <para>To archive beneficiary data, first enter a start date and an end date for the time span of the data which should be archived. Then press the archive button. The operation might take a few minutes.</para>
+ <para>To unarchive beneficiary data, first enter a start date and an end date for the time span of the data which should be unarchived. Then press the unarchive button. The operation might take a few minutes.</para>
+ <para>In some cases you might end up with overlapping data. For instance one might archive beneficiary data for a given timespan, then later enter data for a period in that timespan. In such cases the system will automatically overwrite the oldest of the overlapping values with the newest during the archive or unarchive operation.</para>
</section>
<section id="maintenance">
<title>Maintenance</title>
=== 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-10-29 14:09:49 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/StatementBuilder.java 2010-10-30 11:54:24 +0000
@@ -120,6 +120,18 @@
String getDeflatedDataValues( int dataElementId, String dataElementName, int categoryOptionComboId,
String periodIds, int organisationUnitId, String organisationUnitName, int lowerBound, int upperBound );
+ String archiveData( String startDate, String endDate );
+
+ String unArchiveData( String startDate, String endDate );
+
+ String deleteRegularOverlappingData();
+
+ String deleteArchivedOverlappingData();
+
+ String deleteOldestOverlappingDataValue();
+
+ String deleteOldestOverlappingArchiveData();
+
String archivePatientData ( String startDate, String endDate );
String unArchivePatientData ( String startDate, String endDate );
=== 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-10-29 14:09:49 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/DerbyStatementBuilder.java 2010-10-30 11:54:24 +0000
@@ -220,6 +220,67 @@
"AND ( CAST( dv.value AS " + getDoubleColumnType() + " ) < '" + lowerBound + "' " +
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
}
+
+ public String archiveData( String startDate, String endDate ){
+
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+ public String unArchiveData( String startDate, String endDate ){
+
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+ public String deleteRegularOverlappingData(){
+
+ return "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";
+
+ }
+
+ public String deleteArchivedOverlappingData(){
+
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+ }
+
+ public String deleteOldestOverlappingDataValue(){
+
+ return "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";
+ }
+
+ public String deleteOldestOverlappingArchiveData(){
+
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+ "AND a.lastupdated<=d.lastupdated";
+ }
public String archivePatientData ( String startDate, String endDate )
{
=== 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-10-29 14:09:49 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/H2StatementBuilder.java 2010-10-30 11:54:24 +0000
@@ -219,6 +219,73 @@
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
}
+ public String archiveData( String startDate, String endDate )
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "')";
+ }
+
+ public String unArchiveData( String startDate, String endDate )
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "')";
+ }
+
+ public String deleteRegularOverlappingData()
+ {
+ return "DELETE FROM datavalue AS d " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM datavaluearchive AS a " +
+ "WHERE d.dataelementid=a.dataelementid " +
+ "AND d.periodid=a.periodid " +
+ "AND d.sourceid=a.sourceid " +
+ "AND d.categoryoptioncomboid=a.categoryoptioncomboid)";
+
+ }
+
+ public String deleteArchivedOverlappingData()
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid)";
+ }
+
+ public String deleteOldestOverlappingDataValue()
+ {
+ return "DELETE FROM datavalue AS d " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM 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)";
+ }
+
+ public String deleteOldestOverlappingArchiveData()
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "WHERE EXISTS (" +
+ "SELECT 1 FROM datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+ "AND a.lastupdated<=d.lastupdated)";
+ }
+
public String archivePatientData ( String startDate, String endDate )
{
return "DELETE FROM patientdatavalue AS pdv "
=== 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-10-29 14:22:06 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/MySQLStatementBuilder.java 2010-10-30 11:54:24 +0000
@@ -221,6 +221,67 @@
"OR dv.value > '" + upperBound + "' )";
}
+ public String archiveData( String startDate, String endDate )
+ {
+ return "DELETE d FROM datavalue AS d " +
+ "INNER JOIN period as p " +
+ "WHERE d.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+ public String unArchiveData( String startDate, String endDate )
+ {
+ return "DELETE a FROM datavaluearchive AS a " +
+ "INNER JOIN period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+ public String deleteRegularOverlappingData()
+ {
+ return "DELETE d FROM datavalue AS d " +
+ "INNER JOIN datavaluearchive AS a " +
+ "WHERE d.dataelementid=a.dataelementid " +
+ "AND d.periodid=a.periodid " +
+ "AND d.sourceid=a.sourceid " +
+ "AND d.categoryoptioncomboid=a.categoryoptioncomboid";
+
+ }
+
+ public String deleteArchivedOverlappingData()
+ {
+ return "DELETE a FROM datavaluearchive AS a " +
+ "INNER JOIN datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+ }
+
+ public String deleteOldestOverlappingDataValue()
+ {
+ return "DELETE d FROM datavalue AS d " +
+ "INNER JOIN 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";
+ }
+
+ public String deleteOldestOverlappingArchiveData()
+ {
+ return "DELETE a FROM datavaluearchive AS a " +
+ "INNER JOIN datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+ "AND a.lastupdated<=d.lastupdated";
+ }
+
public String archivePatientData ( String startDate, String endDate )
{
return "DELETE pdv FROM patientdatavalue AS pdv "
=== 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-10-29 14:09:49 +0000
+++ dhis-2/dhis-support/dhis-support-jdbc/src/main/java/org/hisp/dhis/jdbc/statementbuilder/PostgreSQLStatementBuilder.java 2010-10-30 11:54:24 +0000
@@ -222,6 +222,67 @@
"OR CAST( dv.value AS " + getDoubleColumnType() + " ) > '" + upperBound + "' )";
}
+ public String archiveData( String startDate, String endDate )
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+
+ public String unArchiveData( String startDate, String endDate )
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING period AS p " +
+ "WHERE a.periodid=p.periodid " +
+ "AND p.startdate>='" + startDate + "' " +
+ "AND p.enddate<='" + endDate + "'";
+ }
+
+ public String deleteRegularOverlappingData()
+ {
+ return "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";
+ }
+
+ public String deleteArchivedOverlappingData()
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid";
+ }
+
+ public String deleteOldestOverlappingDataValue()
+ {
+ return "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";
+ }
+
+ public String deleteOldestOverlappingArchiveData()
+ {
+ return "DELETE FROM datavaluearchive AS a " +
+ "USING datavalue AS d " +
+ "WHERE a.dataelementid=d.dataelementid " +
+ "AND a.periodid=d.periodid " +
+ "AND a.sourceid=d.sourceid " +
+ "AND a.categoryoptioncomboid=d.categoryoptioncomboid " +
+ "AND a.lastupdated<=d.lastupdated";
+ }
+
public String archivePatientData ( String startDate, String endDate )
{
return "DELETE FROM patientdatavalue AS pdv "
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties 2010-10-29 14:12:06 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties 2010-10-30 11:54:24 +0000
@@ -118,7 +118,7 @@
intro_data_browser = Browse the database and get an overview of which data sets, organisation units and periods it exist data for.
intro_data_integrity = Run data integrity checks and unveil anomalies and problems in the meta data setup.
intro_data_archive = Archive data which is not currently relevant to your system in order to improve performance. Data can also be unarchived.
-intro_patient_data_archive = Archive patient data which is not currently relevant to your system. Data can also be unarchived.
+intro_patient_data_archive = Archive beneficiary data which is not currently relevant to your system. Data can also be unarchived.
intro_maintenance = Perform maintenance tasks such as pruning of data values and periods and clearing of database resource tables.
intro_resource_table = Generate resource database tables for the organisation unit hierarchy and group set structure among others.
intro_organisation_unit_merge = Merge two organisation units and their data values. Data is merged depending on existence and date of last modification.
@@ -316,5 +316,5 @@
data_set_list = Data Set list
data_element_group_list = Data Element Group list
orgunit_group_list = Organisation Unit Group list
-patient_data_archive = Patient Data Archive
-intro__patient_data_archive = Archive patient data which is not currently relevant to your system in order to improve performance. Data can also be unarchived.
\ No newline at end of file
+patient_data_archive = Beneficiary Data Archive
+intro__patient_data_archive = Archive beneficiary data which is not currently relevant to your system in order to improve performance. Data can also be unarchived.
\ No newline at end of file
