dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #35635
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 18228: Sql view, basic impl of 'query' sql type, which means a SQL query not created as view. This type ...
------------------------------------------------------------
revno: 18228
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Wed 2015-02-11 23:32:01 +0100
message:
Sql view, basic impl of 'query' sql type, which means a SQL query not created as view. This type will allow for variables which will be substituted at runtime. Allows for more dynamic SQL queries.
modified:
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-services/dhis-service-administration/src/main/resources/org/hisp/dhis/sqlview/hibernate/SqlView.hbm.xml
dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/sqlview/SqlViewServiceTest.java
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java
dhis-2/dhis-support/dhis-support-test/src/main/java/org/hisp/dhis/DhisConvenienceTest.java
dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/SqlViewController.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/AddSqlViewAction.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/CheckViewTableExistenceAction.java
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/UpdateSqlViewAction.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java
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/webapp/dhis-web-maintenance-dataadmin/addSqlViewForm.vm
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/sqlView.vm
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/updateSqlViewForm.vm
--
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/sqlview/SqlView.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2015-02-05 06:53:38 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlView.java 2015-02-11 22:32:01 +0000
@@ -65,6 +65,8 @@
private String sqlQuery;
+ private boolean query;
+
// -------------------------------------------------------------------------
// Constructors
// -------------------------------------------------------------------------
@@ -74,10 +76,11 @@
}
- public SqlView( String name, String sqlQuery )
+ public SqlView( String name, String sqlQuery, boolean query )
{
this.name = name;
this.sqlQuery = sqlQuery;
+ this.query = query;
}
// -------------------------------------------------------------------------
@@ -158,6 +161,20 @@
this.sqlQuery = sqlQuery;
}
+ @JsonProperty
+ @JsonView( { DetailedView.class, ExportView.class } )
+ @JacksonXmlProperty( namespace = DxfNamespaces.DXF_2_0 )
+ public boolean isQuery()
+ {
+ return query;
+ }
+
+ public void setQuery( boolean query )
+ {
+ this.query = query;
+ }
+
+
@Override
public void mergeWith( IdentifiableObject other, MergeStrategy strategy )
{
@@ -171,11 +188,13 @@
{
description = sqlView.getDescription();
sqlQuery = sqlView.getSqlQuery();
+ query = sqlView.isQuery();
}
else if ( MergeStrategy.MERGE_IF_NOT_NULL.equals( strategy ) )
{
description = sqlView.getDescription() == null ? description : sqlView.getDescription();
sqlQuery = sqlView.getSqlQuery() == null ? sqlQuery : sqlView.getSqlQuery();
+ query = sqlView.isQuery();
}
}
}
=== 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 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewExpandStore.java 2015-02-11 22:32:01 +0000
@@ -47,6 +47,8 @@
void dropViewTable( String sqlViewName );
void setUpDataSqlViewTable( Grid sqlViewGrid, String viewTableName, Map<String, String> criteria );
+
+ void executeQuery( Grid grid, String sql );
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 2015-01-19 10:46:09 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/sqlview/SqlViewService.java 2015-02-11 22:32:01 +0000
@@ -80,8 +80,28 @@
String createViewTable( SqlView sqlViewInstance );
void dropViewTable( String viewName );
-
- Grid getSqlViewGrid( SqlView sqlView, Map<String, String> criteria );
+
+ /**
+ * Returns the SQL view as a grid.
+ *
+ * @param sqlView the SQL view to render.
+ * @param criteria the criteria on the format key:value, will be applied as
+ * criteria on the SQL result set.
+ * @param variables the variables on the format key:value, will be substituted
+ * with variables inside the SQL view.
+ * @return a grid.
+ */
+ Grid getSqlViewGrid( SqlView sqlView, Map<String, String> criteria, Map<String, String> variables );
+
+ /**
+ * Substitutes the given SQL string with the given variables. SQL variables
+ * are on the format ${key}.
+ *
+ * @param sql the SQL string.
+ * @param variables the variables.
+ * @return the substituted SQL.
+ */
+ String substituteSql( String sql, Map<String, String> variables );
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 2015-01-19 10:46:09 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/DefaultSqlViewService.java 2015-02-11 22:32:01 +0000
@@ -31,6 +31,7 @@
import java.util.Collection;
import java.util.Map;
+import org.apache.commons.lang.StringUtils;
import org.hisp.dhis.common.GenericIdentifiableObjectStore;
import org.hisp.dhis.common.Grid;
import org.hisp.dhis.system.grid.ListGrid;
@@ -163,15 +164,49 @@
}
@Override
- public Grid getSqlViewGrid( SqlView sqlView, Map<String, String> criteria )
+ public Grid getSqlViewGrid( SqlView sqlView, Map<String, String> criteria, Map<String, String> variables )
{
Grid grid = new ListGrid();
grid.setTitle( sqlView.getName() );
- sqlViewExpandStore.setUpDataSqlViewTable( grid, sqlView.getViewName(), criteria );
-
+ if ( sqlView.isQuery() )
+ {
+ final String sql = substituteSql( sqlView.getSqlQuery(), variables );
+
+ sqlViewExpandStore.executeQuery( grid, sql );
+ }
+ else
+ {
+ sqlViewExpandStore.setUpDataSqlViewTable( grid, sqlView.getViewName(), criteria );
+ }
+
return grid;
}
+
+ @Override
+ public String substituteSql( String sql, Map<String, String> variables )
+ {
+ String sqlQuery = sql;
+
+ if ( variables != null )
+ {
+ for ( String key : variables.keySet() )
+ {
+ if ( key != null && StringUtils.isAlphanumericSpace( key ) )
+ {
+ final String regex = "\\$\\{(" + key + ")\\}";
+ final String var = variables.get( key );
+
+ if ( var != null && StringUtils.isAlphanumericSpace( var ) )
+ {
+ sqlQuery = sqlQuery.replaceAll( regex, var );
+ }
+ }
+ }
+ }
+
+ return sqlQuery;
+ }
@Override
public String testSqlGrammar( String sql )
=== 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 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/sqlview/jdbc/JdbcSqlViewExpandStore.java 2015-02-11 22:32:01 +0000
@@ -128,10 +128,16 @@
}
}
+ executeQuery( grid, sql );
+ }
+
+ @Override
+ public void executeQuery( Grid grid, String sql )
+ {
+ SqlRowSet rs = jdbcTemplate.queryForRowSet( sql );
+
log.info( "Get view SQL: " + sql );
- SqlRowSet rs = jdbcTemplate.queryForRowSet( sql );
-
grid.addHeaders( rs );
grid.addRows( rs );
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/resources/org/hisp/dhis/sqlview/hibernate/SqlView.hbm.xml'
--- dhis-2/dhis-services/dhis-service-administration/src/main/resources/org/hisp/dhis/sqlview/hibernate/SqlView.hbm.xml 2014-11-03 12:28:28 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/resources/org/hisp/dhis/sqlview/hibernate/SqlView.hbm.xml 2015-02-11 22:32:01 +0000
@@ -18,6 +18,8 @@
<property name="description" type="text" />
<property name="sqlQuery" not-null="true" type="text" />
+
+ <property name="query" />
<!-- Access properties -->
<property name="externalAccess" />
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/sqlview/SqlViewServiceTest.java'
--- dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/sqlview/SqlViewServiceTest.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/test/java/org/hisp/dhis/sqlview/SqlViewServiceTest.java 2015-02-11 22:32:01 +0000
@@ -33,6 +33,9 @@
import static org.junit.Assert.assertNotSame;
import static org.junit.Assert.assertNull;
+import java.util.HashMap;
+import java.util.Map;
+
import org.hisp.dhis.DhisSpringTest;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
@@ -171,4 +174,35 @@
assertEquals( "_view_sqlviewc", sqlViewC.getViewName() );
assertNotSame( "_view_sqlviewc", sqlViewD.getViewName() );
}
+
+ @Test
+ public void testSubsituteSql()
+ {
+ Map<String, String> variables = new HashMap<>();
+ variables.put( "level", "4" );
+ variables.put( "id", "abc" );
+
+ String sql = "select * from datavalue where level=${level} and id='${id}'";
+
+ String expected = "select * from datavalue where level=4 and id='abc'";
+
+ String actual = sqlViewService.substituteSql( sql, variables );
+
+ assertEquals( expected, actual );
+ }
+
+ @Test
+ public void testSubsituteSqlMalicious()
+ {
+ Map<String, String> variables = new HashMap<>();
+ variables.put( "level", "; delete from datavalue;" );
+
+ String sql = "select * from datavalue where level=${level}";
+
+ String expected = "select * from datavalue where level=${level}";
+
+ String actual = sqlViewService.substituteSql( sql, variables );
+
+ assertEquals( expected, actual );
+ }
}
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/startup/TableAlteror.java 2015-02-11 22:32:01 +0000
@@ -695,6 +695,7 @@
executeSql( "alter table validationrulegroup rename column validationgroupid to validationrulegroupid" );
executeSql( "update sqlview set sqlviewid=viweid" );
executeSql( "alter table sqlview drop column viewid" );
+ executeSql( "update sqlview set stored = false where stored is null" );
executeSql( "UPDATE dashboard SET publicaccess='--------' WHERE publicaccess is null" );
=== modified file 'dhis-2/dhis-support/dhis-support-test/src/main/java/org/hisp/dhis/DhisConvenienceTest.java'
--- dhis-2/dhis-support/dhis-support-test/src/main/java/org/hisp/dhis/DhisConvenienceTest.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-support/dhis-support-test/src/main/java/org/hisp/dhis/DhisConvenienceTest.java 2015-02-11 22:32:01 +0000
@@ -1309,6 +1309,7 @@
sqlView.setName( "SqlView" + uniqueCharacter );
sqlView.setDescription( "Description" + uniqueCharacter );
sqlView.setSqlQuery( sql );
+ sqlView.setQuery( false );
return sqlView;
}
=== modified file 'dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/SqlViewController.java'
--- dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/SqlViewController.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-api/src/main/java/org/hisp/dhis/webapi/controller/SqlViewController.java 2015-02-11 22:32:01 +0000
@@ -62,11 +62,12 @@
@RequestMapping( value = "/{uid}/data", method = RequestMethod.GET, produces = ContextUtils.CONTENT_TYPE_JSON )
public String getViewJson( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, Model model, HttpServletResponse response )
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ Model model, HttpServletResponse response )
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
model.addAttribute( "model", grid );
model.addAttribute( "viewClass", "detailed" );
@@ -78,11 +79,12 @@
@RequestMapping( value = "/{uid}/data.xml", method = RequestMethod.GET )
public void getViewXml( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_XML, CacheStrategy.RESPECT_SYSTEM_SETTING );
@@ -91,11 +93,12 @@
@RequestMapping( value = "/{uid}/data.csv", method = RequestMethod.GET )
public void getViewCsv( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_CSV, CacheStrategy.RESPECT_SYSTEM_SETTING, "sqlview.csv", true );
@@ -104,11 +107,12 @@
@RequestMapping( value = "/{uid}/data.xls", method = RequestMethod.GET )
public void getViewXls( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_EXCEL, CacheStrategy.RESPECT_SYSTEM_SETTING, "sqlview.xls", true );
@@ -117,11 +121,12 @@
@RequestMapping( value = "/{uid}/data.html", method = RequestMethod.GET )
public void getViewHtml( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_HTML, CacheStrategy.RESPECT_SYSTEM_SETTING );
@@ -130,11 +135,12 @@
@RequestMapping( value = "/{uid}/data.html+css", method = RequestMethod.GET )
public void getViewHtmlCss( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_HTML, CacheStrategy.RESPECT_SYSTEM_SETTING );
@@ -143,11 +149,12 @@
@RequestMapping( value = "/{uid}/data.pdf", method = RequestMethod.GET )
public void getViewPdf( @PathVariable( "uid" ) String uid,
- @RequestParam( required = false ) Set<String> criteria, HttpServletResponse response ) throws Exception
+ @RequestParam( required = false ) Set<String> criteria, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response ) throws Exception
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
- Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ) );
+ Grid grid = sqlViewService.getSqlViewGrid( sqlView, SqlView.getCriteria( criteria ), SqlView.getCriteria( var ) );
contextUtils.configureResponse( response, ContextUtils.CONTENT_TYPE_PDF, CacheStrategy.RESPECT_SYSTEM_SETTING );
@@ -155,7 +162,8 @@
}
@RequestMapping( value = "/{uid}/execute", method = RequestMethod.POST )
- public void executeView( @PathVariable( "uid" ) String uid, HttpServletResponse response )
+ public void executeView( @PathVariable( "uid" ) String uid, @RequestParam( required = false ) Set<String> var,
+ HttpServletResponse response )
{
SqlView sqlView = sqlViewService.getSqlViewByUid( uid );
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/AddSqlViewAction.java'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/AddSqlViewAction.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/AddSqlViewAction.java 2015-02-11 22:32:01 +0000
@@ -76,6 +76,13 @@
{
this.sqlquery = sqlquery;
}
+
+ private boolean query;
+
+ public void setQuery( boolean query )
+ {
+ this.query = query;
+ }
// -------------------------------------------------------------------------
// Action implementation
@@ -84,21 +91,12 @@
@Override
public String execute()
{
- if ( (name == null) || (name.trim().isEmpty()) )
- {
- return ERROR;
- }
-
- if ( (sqlquery == null) || (sqlquery.trim().isEmpty()) )
- {
- return ERROR;
- }
-
SqlView sqlView = new SqlView();
sqlView.setName( reduceWhiteSpaces( name ) );
sqlView.setDescription( reduceWhiteSpaces( description ) );
sqlView.setSqlQuery( sqlViewService.makeUpForQueryStatement( sqlquery ) );
+ sqlView.setQuery( query );
sqlViewService.saveSqlView( sqlView );
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/CheckViewTableExistenceAction.java'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/CheckViewTableExistenceAction.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/CheckViewTableExistenceAction.java 2015-02-11 22:32:01 +0000
@@ -29,7 +29,9 @@
*/
import com.opensymphony.xwork2.Action;
+
import org.hisp.dhis.i18n.I18n;
+import org.hisp.dhis.sqlview.SqlView;
import org.hisp.dhis.sqlview.SqlViewService;
/**
@@ -98,7 +100,14 @@
return ERROR;
}
- String viewTableName = sqlViewService.getSqlView( id ).getViewName();
+ SqlView sqlView = sqlViewService.getSqlView( id );
+
+ if ( sqlView.isQuery() )
+ {
+ return SUCCESS;
+ }
+
+ String viewTableName = sqlView.getViewName();
if ( !sqlViewService.viewTableExists( viewTableName ) )
{
=== 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 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ExportSqlViewResultAction.java 2015-02-11 22:32:01 +0000
@@ -104,7 +104,7 @@
{
sqlView = sqlViewService.getSqlView( id );
- grid = sqlViewService.getSqlViewGrid( sqlView, null );
+ grid = sqlViewService.getSqlViewGrid( sqlView, null, null );
return type != null ? type : DEFAULT_TYPE;
}
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/UpdateSqlViewAction.java'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/UpdateSqlViewAction.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/UpdateSqlViewAction.java 2015-02-11 22:32:01 +0000
@@ -76,6 +76,13 @@
this.sqlquery = sqlquery;
}
+ private boolean query;
+
+ public void setQuery( boolean query )
+ {
+ this.query = query;
+ }
+
// -------------------------------------------------------------------------
// Action implementation
// -------------------------------------------------------------------------
@@ -83,17 +90,13 @@
@Override
public String execute()
{
- if ( id == null || (id.intValue() == -1) )
- {
- return ERROR;
- }
-
- SqlView sqlViewInstance = sqlViewService.getSqlView( id );
-
- sqlViewInstance.setDescription( description.replaceAll( "\\s+", " " ).trim() );
- sqlViewInstance.setSqlQuery( sqlViewService.makeUpForQueryStatement( sqlquery ) );
-
- sqlViewService.updateSqlView( sqlViewInstance );
+ SqlView sqlView = sqlViewService.getSqlView( id );
+
+ sqlView.setDescription( description.replaceAll( "\\s+", " " ).trim() );
+ sqlView.setSqlQuery( sqlViewService.makeUpForQueryStatement( sqlquery ) );
+ sqlView.setQuery( query );
+
+ sqlViewService.updateSqlView( sqlView );
return SUCCESS;
}
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java 2015-01-17 07:41:26 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/java/org/hisp/dhis/dataadmin/action/sqlview/ValidateAddUpdateSqlViewAction.java 2015-02-11 22:32:01 +0000
@@ -156,7 +156,7 @@
if ( !s.matches( REGEX_SELECT_QUERY ) || tmp.contains( INTO ) )
{
- message = i18n.getString( "sqlquery_is_invalid" ) + "<br/>" + i18n.getString( "sqlquery_is_welformed" );
+ message = i18n.getString( "sqlquery_is_invalid" ) + "<br/>" + i18n.getString( "sqlquery_invalid_note" );
return INPUT;
}
=== 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 2015-01-16 13:34:21 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/resources/org/hisp/dhis/dataadmin/i18n_module.properties 2015-02-11 22:32:01 +0000
@@ -184,6 +184,11 @@
generate=Generate
sql_view_management=SQL View Management
sql_view=SQL View
+sql_type=SQL type
+sql_view_type=SQL view
+sql_query_type=SQL query
+created_in_database=created in database
+allows_for_variables=allows for variables
sql_statement=SQL Statement
run=Execute query
hide_warning=Hide warning
@@ -194,9 +199,9 @@
code_in_use=Code is in use
language_country_in_use=The language and country set is already in use
sqlquery_is_empty=SQL statement cannot be null
-sqlquery_is_invalid=This SQL statement is invalid.
-sqlquery_is_welformed=Notes: Only SELECT query is allowed (Without INTO keyword).
-sql_view_instance_invalid=This resource viewer is invalid or does not exist.
+sqlquery_is_invalid=This SQL statement is invalid
+sqlquery_invalid_note=Note: Only SELECT query is allowed (without INTO keyword)
+sql_view_instance_invalid=This resource viewer is invalid or does not exist
sql_view_table_name=SQL view table with name
is_created=is created
sql_view_table_is_not_created_yet=Please execute query to create View table before viewing
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/addSqlViewForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/addSqlViewForm.vm 2014-10-02 10:20:51 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/addSqlViewForm.vm 2015-02-11 22:32:01 +0000
@@ -18,6 +18,15 @@
<td><textarea id="description" name="description"></textarea></td>
</tr>
<tr>
+ <td><label for="query">$encoder.htmlEncode( $i18n.getString( "sql_type" ) )</label></td>
+ <td>
+ <select id="query" name="query">
+ <option value="false">$i18n.getString( "sql_view_type" ) ($i18n.getString( "created_in_database" ))</option>
+ <option value="true">$i18n.getString( "sql_query_type" ) ($i18n.getString( "allows_for_variables" ))</option>
+ </select>
+ </td>
+ </tr>
+ <tr>
<td><label for="sql_statement">$encoder.htmlEncode( $i18n.getString( "sql_statement" ) ) <em title="$i18n.getString( 'required' )" class="required">*</em></label></td>
<td><textarea type="text" id="sqlquery" name="sqlquery" style="width:80em;height:20em">SELECT </textarea></td>
</tr>
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/sqlView.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/sqlView.vm 2015-01-16 13:34:21 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/sqlView.vm 2015-02-11 22:32:01 +0000
@@ -20,7 +20,7 @@
<div id="contextMenu" class="contextMenu">
<ul id="contextMenuItems" class="contextMenuItems">
<li data-enabled="canManage"><a data-target-fn="showSharingDialogWithContext"><i class="fa fa-share"></i> $i18n.getString( "sharing_settings" )</a></li>
- <li><a data-target-fn="runSqlViewQuery"><i class="fa fa-caret-square-o-right"></i> $i18n.getString( "run" )</a></li>
+ <li data-enabled="canRun"><a data-target-fn="runSqlViewQuery"><i class="fa fa-caret-square-o-right"></i> $i18n.getString( "run" )</a></li>
<li><a data-target-fn="showDataSqlViewForm"><i class="fa fa-table"></i> $i18n.getString( "show_sql_view" )</a></li>
<li data-enabled="canUpdate"><a data-target-fn="showUpdateSqlViewForm"><i class="fa fa-edit"></i> $i18n.getString( "edit" )</a></li>
<li data-enabled="canDelete"><a data-target-fn="removeSqlViewObject"><i class="fa fa-trash-o"></i> $i18n.getString( "remove" )</a></li>
@@ -46,9 +46,11 @@
</thead>
<tbody id="list">
#foreach( $sqlViewObject in $sqlViewObjectList )
+ #set( $canRun = $sqlViewObject.query == false )
<tr id="tr${sqlViewObject.id}">
<td data-id="$!sqlViewObject.id" data-uid="$!sqlViewObject.uid" data-type="SqlView" data-name="$encoder.htmlEncode( $!sqlViewObject.displayName )"
data-can-manage="$security.canManage( $sqlViewObject )"
+ data-can-run="$canRun"
data-can-update="$security.canUpdate( $sqlViewObject )"
data-can-delete="$security.canDelete( $sqlViewObject )">
$encoder.htmlEncode( $!sqlViewObject.displayName )
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/updateSqlViewForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/updateSqlViewForm.vm 2014-10-02 10:20:51 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/updateSqlViewForm.vm 2015-02-11 22:32:01 +0000
@@ -26,6 +26,15 @@
</td>
</tr>
<tr>
+ <td><label for="query">$encoder.htmlEncode( $i18n.getString( "sql_type" ) )</label></td>
+ <td>
+ <select id="query" name="query">
+ <option value="false">$i18n.getString( "sql_view_type" ) ($i18n.getString( "created_in_database" ))</option>
+ <option value="true"#if( $sqlViewObject.query == true ) selected="selected"#end>$i18n.getString( "sql_query_type" ) ($i18n.getString( "allows_for_variables" ))</option>
+ </select>
+ </td>
+ </tr>
+ <tr>
<td><label for="sql_statement">$encoder.htmlEncode( $i18n.getString( "sql_statement" ) ) <em title="$i18n.getString( 'required' )" class="required">*</em></label></td>
<td>
<textarea type="text" id="sqlquery" name="sqlquery" style="width:80em;height:20em">$!encoder.htmlEncode( $!sqlViewObject.sqlQuery )</textarea>
