=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/CodeGenerator.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/CodeGenerator.java 2013-01-28 17:14:42 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/CodeGenerator.java 2013-01-28 18:38:14 +0000 @@ -43,7 +43,7 @@ public static final int NUMBER_OF_CODEPOINTS = allowedChars.length(); public static final int CODESIZE = 11; - private static final Pattern CODE_PATTERN = Pattern.compile( "^[a-zA-Z0-9]{11}$" ); + private static final Pattern CODE_PATTERN = Pattern.compile( "^[a-zA-Z]{1}[a-zA-Z0-9]{10}$" ); /** * Generates a pseudo random string using the allowed characters. Code is === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2013-01-07 15:58:50 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java 2013-01-28 19:43:36 +0000 @@ -251,5 +251,5 @@ * * @param rs the result set. */ - Grid addRow( ResultSet rs ); + Grid addRows( ResultSet rs ); } === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2012-07-21 13:43:46 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2013-01-28 19:43:36 +0000 @@ -27,12 +27,17 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import java.util.HashMap; +import java.util.Map; +import java.util.Set; import java.util.regex.Pattern; import com.fasterxml.jackson.annotation.JsonProperty; import com.fasterxml.jackson.annotation.JsonView; import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlProperty; import com.fasterxml.jackson.dataformat.xml.annotation.JacksonXmlRootElement; + +import org.apache.commons.lang.StringUtils; import org.hisp.dhis.common.BaseIdentifiableObject; import org.hisp.dhis.common.Dxf2Namespace; import org.hisp.dhis.common.IdentifiableObject; @@ -48,6 +53,8 @@ { public static final String PREFIX_VIEWNAME = "_view"; + private static final String CRITERIA_SEP = ":"; + // ------------------------------------------------------------------------- // Variables // ------------------------------------------------------------------------- @@ -134,6 +141,31 @@ return PREFIX_VIEWNAME + input; } + public static Map getCriteria( Set params ) + { + Map map = new HashMap(); + + if ( params != null ) + { + for ( String param : params ) + { + if ( param != null && param.split( CRITERIA_SEP ).length == 2 ) + { + String[] criteria = param.split( CRITERIA_SEP ); + String filter = criteria[0]; + String value = criteria[1]; + + if ( StringUtils.isAlphanumeric( filter ) && StringUtils.isAlphanumeric( value ) ) + { + map.put( filter, value ); + } + } + } + } + + return map; + } + // ------------------------------------------------------------------------- // Getters and setters // ------------------------------------------------------------------------- === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java 2012-07-21 13:43:46 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java 2013-01-28 19:43:36 +0000 @@ -1,6 +1,7 @@ package org.hisp.dhis.sqlview; import java.util.Collection; +import java.util.Map; import org.hisp.dhis.common.Grid; @@ -24,7 +25,7 @@ void dropViewTable( String sqlViewName ); - void setUpDataSqlViewTable( Grid sqlViewGrid, String viewTableName ); + void setUpDataSqlViewTable( Grid sqlViewGrid, String viewTableName, Map criteria ); String testSqlGrammar( String sql ); } === modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewService.java' --- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewService.java 2013-01-28 16:38:11 +0000 +++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewService.java 2013-01-28 19:43:36 +0000 @@ -28,6 +28,7 @@ */ import java.util.Collection; +import java.util.Map; import org.hisp.dhis.common.Grid; @@ -81,7 +82,7 @@ void dropAllSqlViewTables(); - Grid getDataSqlViewGrid( SqlView sqlView ); + Grid getSqlViewGrid( SqlView sqlView, Map criteria ); String testSqlGrammar( String sql ); } === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/DefaultSqlViewService.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/DefaultSqlViewService.java 2013-01-28 16:38:11 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/DefaultSqlViewService.java 2013-01-28 19:43:36 +0000 @@ -27,13 +27,14 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import java.util.Collection; +import java.util.Map; + import org.hisp.dhis.common.GenericIdentifiableObjectStore; import org.hisp.dhis.common.Grid; import org.hisp.dhis.system.grid.ListGrid; import org.springframework.transaction.annotation.Transactional; -import java.util.Collection; - /** * @author Dang Duy Hieu * @version $Id DefaultSqlViewService.java July 06, 2010$ @@ -169,11 +170,11 @@ } @Override - public Grid getDataSqlViewGrid( SqlView sqlView ) + public Grid getSqlViewGrid( SqlView sqlView, Map criteria ) { Grid sqlViewGrid = new ListGrid(); - sqlViewExpandStore.setUpDataSqlViewTable( sqlViewGrid, sqlView.getViewName() ); + sqlViewExpandStore.setUpDataSqlViewTable( sqlViewGrid, sqlView.getViewName(), criteria ); return sqlViewGrid; } === modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java' --- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2013-01-28 09:49:23 +0000 +++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2013-01-28 19:43:36 +0000 @@ -34,11 +34,15 @@ import java.sql.Statement; import java.util.Collection; import java.util.HashSet; +import java.util.Map; import java.util.Set; +import org.apache.commons.logging.LogFactory; +import org.apache.commons.logging.Log; import org.hisp.dhis.common.Grid; import org.hisp.dhis.sqlview.SqlView; import org.hisp.dhis.sqlview.SqlViewExpandStore; +import org.hisp.dhis.system.util.SqlHelper; import org.springframework.jdbc.BadSqlGrammarException; import org.springframework.jdbc.core.JdbcTemplate; @@ -49,12 +53,11 @@ public class JdbcSqlViewExpandStore implements SqlViewExpandStore { + private static final Log log = LogFactory.getLog( JdbcSqlViewExpandStore.class ); + private static final String PREFIX_CREATEVIEW_QUERY = "CREATE VIEW "; - private static final String PREFIX_DROPVIEW_QUERY = "DROP VIEW IF EXISTS "; - private static final String PREFIX_SELECT_QUERY = "SELECT * FROM "; - private static final String[] types = { "VIEW" }; // ------------------------------------------------------------------------- @@ -75,12 +78,11 @@ @Override public Collection getAllSqlViewNames() { - DatabaseMetaData mtdt; Set viewersName = new HashSet(); try { - mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData(); + DatabaseMetaData mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData(); ResultSet rs = mtdt.getTables( null, null, SqlView.PREFIX_VIEWNAME + "%", types ); @@ -101,11 +103,10 @@ @Override public boolean isViewTableExists( String viewTableName ) { - DatabaseMetaData mtdt; - try { - mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData(); + DatabaseMetaData mtdt = jdbcTemplate.getDataSource().getConnection().getMetaData(); + ResultSet rs = mtdt.getTables( null, null, viewTableName.toLowerCase(), types ); return rs.next(); @@ -121,11 +122,15 @@ { String viewName = sqlViewInstance.getViewName(); + dropViewTable( viewName ); + + final String sql = PREFIX_CREATEVIEW_QUERY + viewName + " AS " + sqlViewInstance.getSqlQuery(); + + log.debug( "Create view SQL: " + sql ); + try { - this.dropViewTable( viewName ); - - jdbcTemplate.execute( PREFIX_CREATEVIEW_QUERY + viewName + " AS " + sqlViewInstance.getSqlQuery() ); + jdbcTemplate.execute( sql ); } catch ( BadSqlGrammarException bge ) { @@ -136,21 +141,33 @@ } @Override - public void setUpDataSqlViewTable( Grid grid, String viewTableName ) + public void setUpDataSqlViewTable( Grid grid, String viewTableName, Map criteria ) { - ResultSet rs; - + String sql = PREFIX_SELECT_QUERY + viewTableName; + + if ( criteria != null && !criteria.isEmpty() ) + { + SqlHelper helper = new SqlHelper(); + + for ( String filter : criteria.keySet() ) + { + sql += " " + helper.whereAnd() + " " + filter + "='" + criteria.get( filter ) + "'"; + } + } + + log.info( "Get view SQL: " + sql ); + try { - rs = this.getResultSet( PREFIX_SELECT_QUERY + viewTableName, jdbcTemplate ); + ResultSet rs = getResultSet( sql ); + + grid.addHeaders( rs ); + grid.addRows( rs ); } catch ( SQLException e ) { throw new RuntimeException( "Failed to get data from view " + viewTableName, e ); } - - grid.addHeaders( rs ); - grid.addRow( rs ); } @Override @@ -158,9 +175,13 @@ { String viewNameCheck = SqlView.PREFIX_VIEWNAME + System.currentTimeMillis(); + sql = PREFIX_CREATEVIEW_QUERY + viewNameCheck + " AS " + sql; + + log.debug( "Test view SQL: " + sql ); + try { - jdbcTemplate.execute( PREFIX_CREATEVIEW_QUERY + viewNameCheck + " AS " + sql ); + jdbcTemplate.execute( sql ); dropViewTable( viewNameCheck ); } @@ -190,19 +211,13 @@ // ------------------------------------------------------------------------- /** - * Uses StatementManager to obtain a scrollable, read-only ResultSet based - * on the query string. - * - * @param sql the query - * @param holder the StatementHolder object - * @return null or the ResultSet + * Obtains a scrollable, read-only result set based on the query string. */ - private ResultSet getResultSet( String sql, JdbcTemplate jdbcTemplate ) + private ResultSet getResultSet( String sql ) throws SQLException { Connection con = jdbcTemplate.getDataSource().getConnection(); Statement stm = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY ); - stm.execute( sql ); - return stm.getResultSet(); + return stm.executeQuery( sql ); } } \ No newline at end of file === modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java' --- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2013-01-07 15:58:50 +0000 +++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java 2013-01-28 19:43:36 +0000 @@ -576,7 +576,7 @@ return this; } - public Grid addRow( ResultSet rs ) + public Grid addRows( ResultSet rs ) { try { === modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/SqlViewController.java' --- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/SqlViewController.java 2013-01-28 15:21:17 +0000 +++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/SqlViewController.java 2013-01-28 19:43:36 +0000 @@ -27,6 +27,8 @@ * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ +import java.util.Set; + import javax.servlet.http.HttpServletResponse; import org.hisp.dhis.api.utils.ContextUtils; @@ -41,6 +43,7 @@ import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; +import org.springframework.web.bind.annotation.RequestParam; /** * @author Morten Olav Hansen @@ -59,11 +62,12 @@ private ContextUtils contextUtils; @RequestMapping( value = "/{uid}/data", method = RequestMethod.GET, produces = ContextUtils.CONTENT_TYPE_JSON ) - public String getViewJson( @PathVariable( "uid" ) String uid, Model model, HttpServletResponse response ) throws Exception + public String getViewJson( @PathVariable( "uid" ) String uid, + @RequestParam(required=false) Set criteria, Model model, HttpServletResponse response ) { SqlView sqlView = sqlViewService.getSqlViewByUid( uid ); - Grid grid = sqlViewService.getDataSqlViewGrid( sqlView ); + Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) ); model.addAttribute( "model", grid ); model.addAttribute( "viewClass", "detailed" ); @@ -74,11 +78,12 @@ } @RequestMapping( value = "/{uid}/data.xml", method = RequestMethod.GET ) - public void getViewXml( @PathVariable( "uid" ) String uid, HttpServletResponse response ) throws Exception + public void getViewXml( @PathVariable( "uid" ) String uid, + @RequestParam(required=false) Set criteria, HttpServletResponse response ) throws Exception { SqlView sqlView = sqlViewService.getSqlViewByUid( uid ); - Grid grid = sqlViewService.getDataSqlViewGrid( sqlView ); + Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) ); contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_XML, CacheStrategy.RESPECT_SYSTEM_SETTING ); @@ -86,11 +91,12 @@ } @RequestMapping( value = "/{uid}/data.csv", method = RequestMethod.GET ) - public void getViewCsv( @PathVariable( "uid" ) String uid, HttpServletResponse response ) throws Exception + public void getViewCsv( @PathVariable( "uid" ) String uid, + @RequestParam(required=false) Set criteria, HttpServletResponse response ) throws Exception { SqlView sqlView = sqlViewService.getSqlViewByUid( uid ); - Grid grid = sqlViewService.getDataSqlViewGrid( sqlView ); + Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) ); contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_CSV, CacheStrategy.RESPECT_SYSTEM_SETTING, "sqlview.csv", true ); @@ -98,11 +104,12 @@ } @RequestMapping( value = "/{uid}/data.xls", method = RequestMethod.GET ) - public void getViewXls( @PathVariable( "uid" ) String uid, HttpServletResponse response ) throws Exception + public void getViewXls( @PathVariable( "uid" ) String uid, + @RequestParam(required=false) Set criteria, HttpServletResponse response ) throws Exception { SqlView sqlView = sqlViewService.getSqlViewByUid( uid ); - Grid grid = sqlViewService.getDataSqlViewGrid( sqlView ); + Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) ); contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_EXCEL, CacheStrategy.RESPECT_SYSTEM_SETTING, "sqlview.xls", true ); @@ -110,11 +117,12 @@ } @RequestMapping( value = "/{uid}/data.html", method = RequestMethod.GET ) - public void getViewHtml( @PathVariable( "uid" ) String uid, HttpServletResponse response ) throws Exception + public void getViewHtml( @PathVariable( "uid" ) String uid, + @RequestParam(required=false) Set criteria, HttpServletResponse response ) throws Exception { SqlView sqlView = sqlViewService.getSqlViewByUid( uid ); - Grid grid = sqlViewService.getDataSqlViewGrid( sqlView ); + Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) ); contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_HTML, CacheStrategy.RESPECT_SYSTEM_SETTING ); === modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ExportSqlViewResultAction.java' --- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ExportSqlViewResultAction.java 2013-01-28 16:38:11 +0000 +++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ExportSqlViewResultAction.java 2013-01-28 19:43:36 +0000 @@ -104,7 +104,7 @@ { sqlView = sqlViewService.getSqlView( id ); - grid = sqlViewService.getDataSqlViewGrid( sqlView ); + grid = sqlViewService.getSqlViewGrid( sqlView, null ); return type != null ? type : DEFAULT_TYPE; }