dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #20771
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 9648: Impl function for supplying criteria for sql view rendering. You can query on any column in the s...
Merge authors:
Lars Helge Øverland (larshelge)
------------------------------------------------------------
revno: 9648 [merge]
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2013-01-29 09:56:12 +0200
message:
Impl function for supplying criteria for sql view rendering. You can query on any column in the sql view through the request query string. Format is ?criteria=<column>:<value>&criteria=<column>:<value>. Will document soon.
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/CodeGenerator.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/common/Grid.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewService.java
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/jdbc/JdbcSqlViewExpandStore.java
dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/grid/ListGrid.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/api/controller/SqlViewController.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ExportSqlViewResultAction.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-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<String, String> getCriteria( Set<String> params )
+ {
+ Map<String, String> map = new HashMap<String, String>();
+
+ 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<String, String> 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<String, String> 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<String, String> 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<String> getAllSqlViewNames()
{
- DatabaseMetaData mtdt;
Set<String> viewersName = new HashSet<String>();
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<String, String> 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 <mortenoh@xxxxxxxxx>
@@ -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<String> 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<String> 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<String> 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<String> 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<String> 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;
}
