Author: tchemit Date: 2012-10-11 19:13:23 +0200 (Thu, 11 Oct 2012) New Revision: 724 Url: http://forge.codelutin.com/repositories/revision/echobase/724 Log: fixes #1595: Permettre d'utiliser des requ?\195?\170tes imrbiqu?\195?\169es Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryInvalidNameException.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryNotFoundException.java Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/embeddedapplication/EmbeddedApplicationService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryService.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DownloadExportQueryResult.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ExportQueryResult.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/GetExportQueryResult.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ManageExportQuery.java trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQuery.jsp trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQueryResult.jsp Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/embeddedapplication/EmbeddedApplicationService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/embeddedapplication/EmbeddedApplicationService.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/embeddedapplication/EmbeddedApplicationService.java 2012-10-11 17:13:23 UTC (rev 724) @@ -40,6 +40,7 @@ import fr.ifremer.echobase.services.exportdb.ExportDbConfiguration; import fr.ifremer.echobase.services.exportdb.ExportDbMode; import fr.ifremer.echobase.services.exportdb.ExportDbService; +import fr.ifremer.echobase.services.exportquery.ExportQueryInvalidNameException; import fr.ifremer.echobase.services.exportquery.ExportQueryNameAlreadyExistException; import fr.ifremer.echobase.services.exportquery.ExportQueryService; import fr.ifremer.echobase.services.importdata.ImportException; @@ -144,7 +145,7 @@ protected EchoBaseUser importInternalDb(EmbeddedApplicationConfiguration model, File dir, - EchoBaseServiceContext newServiceContext) throws TopiaException, ExportQueryNameAlreadyExistException, WorkingDbConfigurationAlreadyExistException { + EchoBaseServiceContext newServiceContext) throws TopiaException, ExportQueryNameAlreadyExistException, WorkingDbConfigurationAlreadyExistException, ExportQueryInvalidNameException { File internalDir = new File(dir, "internaldb"); Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryInvalidNameException.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryInvalidNameException.java (rev 0) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryInvalidNameException.java 2012-10-11 17:13:23 UTC (rev 724) @@ -0,0 +1,35 @@ +package fr.ifremer.echobase.services.exportquery; + +/* + * #%L + * EchoBase :: Services + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2012 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + +/** + * To be thrown if a query has not a valid query name. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.4 + */ +public class ExportQueryInvalidNameException extends Exception { + + private static final long serialVersionUID = 1L; +} \ No newline at end of file Property changes on: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryInvalidNameException.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryNotFoundException.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryNotFoundException.java (rev 0) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryNotFoundException.java 2012-10-11 17:13:23 UTC (rev 724) @@ -0,0 +1,35 @@ +package fr.ifremer.echobase.services.exportquery; + +/* + * #%L + * EchoBase :: Services + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2012 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + +/** + * When a export query is not found. + * + * @author tchemit <chemit@codelutin.com> + * @since 1.4 + */ +public class ExportQueryNotFoundException extends Exception { + + private static final long serialVersionUID = 1L; +} \ No newline at end of file Property changes on: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryNotFoundException.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryService.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/ExportQueryService.java 2012-10-11 17:13:23 UTC (rev 724) @@ -32,6 +32,7 @@ import org.apache.commons.lang3.StringUtils; import org.nuiton.topia.TopiaException; import org.nuiton.topia.persistence.pager.TopiaPagerBean; +import org.nuiton.util.RecursiveProperties; import org.nuiton.util.csv.Export; import java.util.List; @@ -45,8 +46,12 @@ */ public class ExportQueryService extends EchoBaseServiceSupport { - public ExportQuery getExportQuery(String topiaId) { + public ExportQuery getExportQuery(String topiaId) throws ExportQueryNotFoundException { ExportQuery exportQuery = getEntityById(ExportQuery.class, topiaId); + + if (exportQuery == null) { + throw new ExportQueryNotFoundException(); + } ExportQuery entityToSave = newExportQuery(); entityToSave.setTopiaId(exportQuery.getTopiaId()); entityToSave.setName(exportQuery.getName()); @@ -58,7 +63,7 @@ } public ExportQuery createOrUpdate(ExportQuery exportQuery, - EchoBaseUser user) throws ExportQueryNameAlreadyExistException { + EchoBaseUser user) throws ExportQueryNameAlreadyExistException, ExportQueryInvalidNameException { try { ExportQueryDAO dao = getDAO(); @@ -70,6 +75,12 @@ String queryName = exportQuery.getName(); + if (!StringUtils.isAlphanumeric(queryName)) { + + // can not accept a non alpha numeric name + throw new ExportQueryInvalidNameException(); + } + // check query does not already exists with this name if (mustCreate) { @@ -117,15 +128,42 @@ } } + public String getSqlQuery(String queryId) throws ExportQueryNotFoundException { + + ExportQuery exportQuery = getExportQuery(queryId); + + String result = getSafeSql(exportQuery.getSqlQuery()); + return result; + } + + protected String getSafeSql(String sql) { + RecursiveProperties sqls = new RecursiveProperties(); + List<ExportQuery> queries = getEntities(ExportQuery.class); + for (ExportQuery query : queries) { + sqls.put(query.getName(), query.getSqlQuery()); + } + if (!sqls.containsKey(sql)) { + + sqls.put(sql, sql); + } + + String result = sqls.getProperty(sql); + return result; + } + public void testSql(String sql) throws TopiaException { - GenericSQLQuery sqlQuery = new GenericSQLQuery(sql, null); + String safeSql = getSafeSql(sql); + GenericSQLQuery sqlQuery = new GenericSQLQuery(safeSql, null); sqlQuery.getColumnNames(getTransaction()); } - public Map<String, Object>[] executeSql(String sql, TopiaPagerBean pager) { + public Map<String, Object>[] executeExportquery(String queryId, + TopiaPagerBean pager) throws ExportQueryNotFoundException { + String sql = getSqlQuery(queryId); + // get a query to count all rows for the request GenericSQLQuery sqlQuery = new GenericSQLQuery(sql, pager); try { @@ -160,8 +198,10 @@ } } - public String createCsvFileContent(String sql) { + public String createCsvFileContent(String queryId) throws ExportQueryNotFoundException { + String sql = getSqlQuery(queryId); + GenericSQLQuery sqlQuery = new GenericSQLQuery(sql, null); List<Map<String, Object>> rows; try { @@ -196,8 +236,6 @@ } protected ExportQueryDAO getDAO() { - return getDAO(ExportQuery.class, - ExportQueryDAO.class); + return getDAO(ExportQuery.class, ExportQueryDAO.class); } - } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DownloadExportQueryResult.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DownloadExportQueryResult.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/DownloadExportQueryResult.java 2012-10-11 17:13:23 UTC (rev 724) @@ -23,6 +23,7 @@ */ package fr.ifremer.echobase.ui.actions.exportQuery; +import fr.ifremer.echobase.entities.ExportQuery; import fr.ifremer.echobase.services.exportquery.ExportQueryService; import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; @@ -39,8 +40,13 @@ private static final long serialVersionUID = 1L; - /** Sql request to execute. */ - protected String sql; + /** + * Id of the query to call. + * + * @see ExportQuery#getTopiaId() + * @since 1.4 + */ + protected String queryId; /** Input stream of the file to download. */ protected transient InputStream inputStream; @@ -54,8 +60,8 @@ /** Content type of the file to download. */ protected String contentType; - public void setSql(String sql) { - this.sql = sql; + public void setQueryId(String queryId) { + this.queryId = queryId; } public void setFileName(String fileName) { @@ -84,7 +90,7 @@ ExportQueryService service = getService(ExportQueryService.class); contentType = "text/csv"; - String content = service.createCsvFileContent(sql); + String content = service.createCsvFileContent(queryId); contentLength = content.length(); inputStream = new ByteArrayInputStream(content.getBytes()); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ExportQueryResult.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ExportQueryResult.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ExportQueryResult.java 2012-10-11 17:13:23 UTC (rev 724) @@ -24,6 +24,7 @@ package fr.ifremer.echobase.ui.actions.exportQuery; import fr.ifremer.echobase.EchoBaseTechnicalException; +import fr.ifremer.echobase.entities.ExportQuery; import fr.ifremer.echobase.services.exportquery.ExportQueryService; import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; import org.apache.commons.logging.Log; @@ -42,8 +43,13 @@ /** Logger. */ private static final Log log = LogFactory.getLog(ExportQueryResult.class); - /** Sql request to execute. */ - protected String sql; + /** + * Id of the query to call. + * + * @see ExportQuery#getTopiaId() + * @since 1.4 + */ + protected String queryId; /** Default file name for export. */ protected String fileName; @@ -51,10 +57,6 @@ /** names of columns from the executed sql request. */ protected String[] columnNames; - public String getSql() { - return sql; - } - public String[] getColumnNames() { return columnNames; } @@ -63,17 +65,23 @@ return fileName; } - public void setSql(String sql) { - this.sql = sql; + public String getQueryId() { + return queryId; } + public void setQueryId(String queryId) { + this.queryId = queryId; + } + @Override public String execute() throws Exception { // obtain columNames from the request ExportQueryService service = getService(ExportQueryService.class); + String sql = service.getSqlQuery(queryId); try { + columnNames = service.getColumnNames(sql); } catch (EchoBaseTechnicalException e) { //TODO add a real nice message Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/GetExportQueryResult.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/GetExportQueryResult.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/GetExportQueryResult.java 2012-10-11 17:13:23 UTC (rev 724) @@ -23,6 +23,7 @@ */ package fr.ifremer.echobase.ui.actions.exportQuery; +import fr.ifremer.echobase.entities.ExportQuery; import fr.ifremer.echobase.services.exportquery.ExportQueryService; import fr.ifremer.echobase.ui.actions.AbstractJSONPaginedAction; @@ -38,14 +39,19 @@ private static final long serialVersionUID = 1L; - /** Sql request to execute. */ - protected String sql; + /** + * Id of the query to call. + * + * @see ExportQuery#getTopiaId() + * @since 1.4 + */ + protected String queryId; /** Datas of the given table. */ protected Map<?, ?>[] datas; - public void setSql(String sql) { - this.sql = sql; + public void setQueryId(String queryId) { + this.queryId = queryId; } public Map<?, ?>[] getDatas() { @@ -55,7 +61,8 @@ @Override public String execute() throws Exception { ExportQueryService service = getService(ExportQueryService.class); - datas = service.executeSql(sql, pager); + + datas = service.executeExportquery(queryId, pager); return SUCCESS; } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ManageExportQuery.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ManageExportQuery.java 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/exportQuery/ManageExportQuery.java 2012-10-11 17:13:23 UTC (rev 724) @@ -26,6 +26,7 @@ import com.opensymphony.xwork2.Preparable; import fr.ifremer.echobase.entities.EchoBaseUser; import fr.ifremer.echobase.entities.ExportQuery; +import fr.ifremer.echobase.services.exportquery.ExportQueryInvalidNameException; import fr.ifremer.echobase.services.exportquery.ExportQueryNameAlreadyExistException; import fr.ifremer.echobase.services.exportquery.ExportQueryService; import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; @@ -114,19 +115,20 @@ String result = INPUT; - boolean safeSql = checkQuery(getQuery().getSqlQuery()); + boolean safeSql = checkQuery(); if (safeSql) { try { - query = getService().createOrUpdate(getQuery(), - getEchoBaseSession().getUser() - ); + query = getService().createOrUpdate( + getQuery(), getEchoBaseSession().getUser()); result = SUCCESS; - } catch (ExportQueryNameAlreadyExistException e) { + } catch (ExportQueryInvalidNameException e) { addFieldError("query.name", + _("echobase.error.query.invalid.name")); + }catch (ExportQueryNameAlreadyExistException e) { + addFieldError("query.name", _("echobase.error.query.name.already.exists")); - } } return result; @@ -161,7 +163,7 @@ if (StringUtils.isNotEmpty(getQuery().getTopiaId())) { // test query - checkQuery(getQuery().getSqlQuery()); + checkQuery(); } return SUCCESS; } @@ -211,10 +213,11 @@ } } - private boolean checkQuery(String sql) { + private boolean checkQuery() { + boolean result; try { - getService().testSql(sql); + getService().testSql(getQuery().getSqlQuery()); result = true; } catch (Exception e) { Throwable cause = e.getCause(); Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2012-10-11 17:13:23 UTC (rev 724) @@ -211,6 +211,7 @@ echobase.error.no.embeddedApplication.exportFileFound=New portable database not found echobase.error.no.exportDb.configurationFound=Database export configuration not found echobase.error.no.exportDb.exportFileFound=Database export archive not found +echobase.error.query.invalid.name=\=Query names must only contains alphanumeric characters echobase.error.query.name.already.exists=Query name already exists echobase.error.required.email=E-mail address required echobase.error.required.password=Password is required Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2012-10-11 17:13:23 UTC (rev 724) @@ -211,6 +211,7 @@ echobase.error.no.embeddedApplication.exportFileFound=Application embarquée générée non trouvée echobase.error.no.exportDb.configurationFound=Configuration de l'export de la base non trouvée echobase.error.no.exportDb.exportFileFound=Archive d'export de la base introuvable +echobase.error.query.invalid.name=Le nom de la requête doit être constitué uniquement de caractères alphanumériques echobase.error.query.name.already.exists=Nom de requête déjà utilisé echobase.error.required.email=Courriel obligatoire echobase.error.required.password=Le mot de passe est obligatoire Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQuery.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQuery.jsp 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQuery.jsp 2012-10-11 17:13:23 UTC (rev 724) @@ -190,7 +190,7 @@ $.ajax( { url:'<s:url action="exportQueryResult" namespace="/exportQuery"/>', - data:{sql:$('[name="query.sqlQuery"]').val()}, + data:{queryId:$('[name="query.topiaId"]').val()}, success:function (data, textStatus, jqXHR) { $('#resultGrid').html(data); } Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQueryResult.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQueryResult.jsp 2012-10-11 17:13:02 UTC (rev 723) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/exportQuery/exportQueryResult.jsp 2012-10-11 17:13:23 UTC (rev 724) @@ -47,7 +47,7 @@ <s:else> <s:url id="loadUrl" action="getExportQueryResult" namespace="/exportQuery" escapeAmp="false"> - <s:param name="sql" value="%{sql}"/> + <s:param name="queryId" value="%{queryId}"/> </s:url> <s:form action="downloadExportQueryResult" namespace="/exportQuery"> @@ -55,7 +55,7 @@ <fieldset class="ui-corner-all"> <legend><s:text name="echobase.legend.sqlQuery.result"/></legend> - <s:hidden key="sql" label=""/> + <s:hidden key="queryId" label=""/> <s:textfield key="fileName" required="true" size="100" label="%{getText('echobase.label.exportFileName')}"/> <s:submit value="%{getText('echobase.action.exportSqlData')}" align="left"/>