=== 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.lastupdatedIn 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.
- Patient Data Archive - 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. - 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. - 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. - 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. + Beneficiary Data Archive + 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. + 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. + 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. + 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.
Maintenance === 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 '" + 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 '" + 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 '" + 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