Author: tchemit Date: 2012-03-10 00:45:00 +0100 (Sat, 10 Mar 2012) New Revision: 342 Url: http://forge.codelutin.com/repositories/revision/echobase/342 Log: improve export sql service Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ExportQueryNameAlreadyExistException.java trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/ExportQueryServiceTest.java Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportQueryService.java trunk/echobase-services/src/main/resources/i18n/echobase-services_en_GB.properties trunk/echobase-services/src/main/resources/i18n/echobase-services_fr_FR.properties Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportQueryService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportQueryService.java 2012-03-09 23:36:57 UTC (rev 341) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/ExportQueryService.java 2012-03-09 23:45:00 UTC (rev 342) @@ -29,12 +29,14 @@ import fr.ifremer.echobase.entities.ExportQuery; import fr.ifremer.echobase.entities.ExportQueryImpl; import fr.ifremer.echobase.services.csv.ExportQueryCsvModel; +import fr.ifremer.echobase.services.exceptions.ExportQueryNameAlreadyExistException; import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; import org.nuiton.topia.framework.TopiaContextImplementor; +import org.nuiton.topia.framework.TopiaQuery; import org.nuiton.topia.framework.TopiaSQLQuery; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.util.PagerUtil; @@ -71,20 +73,40 @@ } public ExportQuery createOrUpdate(ExportQuery exportQuery, - EchoBaseUser user) { + EchoBaseUser user) throws ExportQueryNameAlreadyExistException { try { TopiaDAO<ExportQuery> dao = getDAO(ExportQuery.class); - ExportQuery entityToSave; // No id, creating new one entity String id = exportQuery.getTopiaId(); - if (StringUtils.isEmpty(id)) { + + boolean mustCreate = StringUtils.isEmpty(id); + + String queryName = exportQuery.getName(); + + TopiaQuery query = dao.createQuery(); + query.addWhere(ExportQuery.PROPERTY_NAME, TopiaQuery.Op.EQ, queryName); + + if (!mustCreate) { + query.addWhere(ExportQuery.TOPIA_ID, TopiaQuery.Op.NEQ, id); + } + + // check query does not already exists with this name + boolean queryExists = dao.existByQuery(query); + + if (queryExists) { + throw new ExportQueryNameAlreadyExistException(); + } + + ExportQuery entityToSave; + + if (mustCreate) { entityToSave = dao.create(); } else { entityToSave = dao.findByTopiaId(id); } - entityToSave.setName(exportQuery.getName()); + entityToSave.setName(queryName); entityToSave.setDescription(exportQuery.getDescription()); entityToSave.setSqlQuery(exportQuery.getSqlQuery()); entityToSave.setLastModifiedDate(newDate()); @@ -110,6 +132,13 @@ } } + public void testSql(String sql) throws TopiaException { + + GenericSQLQuery sqlQuery = new GenericSQLQuery(sql, null); + + sqlQuery.getColumnNames(getTransaction()); + } + public Map<String, Object>[] executeSql(String sql, PagerUtil.PagerBean pager) { // get a query to count all rows for the request @@ -171,6 +200,12 @@ return new ExportQueryImpl(); } + public String processLibreOfficeSqlQuery(String libreOfficeQuery) { + + String result = libreOfficeQuery.replaceAll("\\\"", ""); + return result; + } + private static class GenericSQLQuery extends TopiaSQLQuery<Map<String, Object>> { protected String[] columnNames; @@ -202,6 +237,10 @@ return rows; } + public void testQuery(TopiaContext tx) throws TopiaException { + findSingleResult((TopiaContextImplementor) tx); + } + public String[] getColumnNames(TopiaContext tx) throws TopiaException { findSingleResult((TopiaContextImplementor) tx); return columnNames; Added: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ExportQueryNameAlreadyExistException.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ExportQueryNameAlreadyExistException.java (rev 0) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ExportQueryNameAlreadyExistException.java 2012-03-09 23:45:00 UTC (rev 342) @@ -0,0 +1,35 @@ +/* + * #%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% + */ +package fr.ifremer.echobase.services.exceptions; + +/** + * TODO + * + * @author tchemit <chemit@codelutin.com> + * @since 0.3 + */ +public class ExportQueryNameAlreadyExistException extends Exception{ + + private static final long serialVersionUID = 1L; +} Property changes on: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exceptions/ExportQueryNameAlreadyExistException.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/echobase-services/src/main/resources/i18n/echobase-services_en_GB.properties =================================================================== --- trunk/echobase-services/src/main/resources/i18n/echobase-services_en_GB.properties 2012-03-09 23:36:57 UTC (rev 341) +++ trunk/echobase-services/src/main/resources/i18n/echobase-services_en_GB.properties 2012-03-09 23:45:00 UTC (rev 342) @@ -24,7 +24,6 @@ echobase.common.resultsImportMode.acoustic= echobase.common.resultsImportMode.region= echobase.common.resultsImportMode.voyage= -echobase.common.sampleFile= echobase.common.subSampleFile= echobase.common.totalSampleFile= echobase.common.transectFile= @@ -35,7 +34,6 @@ echobase.importError.esduCell.notfound= echobase.importError.fromFile= echobase.importError.no.transit.between.date= -echobase.importError.no.transit.imported= echobase.importError.no.voyage.imported= echobase.importError.transect.notfound= echobase.importError.transit.notfound= Modified: trunk/echobase-services/src/main/resources/i18n/echobase-services_fr_FR.properties =================================================================== --- trunk/echobase-services/src/main/resources/i18n/echobase-services_fr_FR.properties 2012-03-09 23:36:57 UTC (rev 341) +++ trunk/echobase-services/src/main/resources/i18n/echobase-services_fr_FR.properties 2012-03-09 23:45:00 UTC (rev 342) @@ -34,7 +34,6 @@ echobase.importError.esduCell.notfound=Pas de cellule esdu %s déclarée pour la campagne %s lors de l'iomport d'une cellule élémentaire %s echobase.importError.fromFile=Erreur lors de l'import du fichier %s \: %s echobase.importError.no.transit.between.date=Pas de transit trouvé pour le voyage %s entre les dates %s et %s -echobase.importError.no.transit.imported=Aucun transit importé echobase.importError.no.voyage.imported=Aucun voyage importé echobase.importError.transect.notfound=Transit non trouvé pour la campagne %s et la date %s et le navire %s echobase.importError.transit.notfound=Transit non trouvé pour la campagne %s et la date %s Added: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/ExportQueryServiceTest.java =================================================================== --- trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/ExportQueryServiceTest.java (rev 0) +++ trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/ExportQueryServiceTest.java 2012-03-09 23:45:00 UTC (rev 342) @@ -0,0 +1,70 @@ +/* + * #%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% + */ +package fr.ifremer.echobase.services; + +import org.junit.Assert; +import org.junit.Before; +import org.junit.Ignore; +import org.junit.Test; + +/** + * To test {@link ExportQueryService}. + * + * @author tchemit <chemit@codelutin.com> + * @since 0.3 + */ +public class ExportQueryServiceTest extends EchoBaseTestServiceSupport{ + @Override + protected FakeEchoBaseServiceContext initContext() { + return new FakeEchoBaseServiceContext(null); + } + + ExportQueryService service; + + @Before + public void setup() { + service = serviceContext.newService(ExportQueryService.class); + } + + @Ignore + @Test + public void processLibreOfficeSqlQuery() throws Exception { + + processLibreOfficeSqlQuery("select \"t\".name FROM \"t\" WHERE rien;", + "select t.name FROM t WHERE rien;"); + + processLibreOfficeSqlQuery("select \"t\".name, \"s\".name FROM \"t\", \"s\" WHERE rien;", + "select t.name, s.name FROM t, s WHERE rien;"); + + processLibreOfficeSqlQuery("select \"t\".name FROM \"t\" WHERE (select \"s\".name FROM \"s\" WHERE rien2);", + "select t.name FROM t WHERE (select s.name FROM s WHERE rien2);"); + } + + protected void processLibreOfficeSqlQuery(String libreOfficeQuery, + String expected) { + + String actual = service.processLibreOfficeSqlQuery(libreOfficeQuery); + Assert.assertEquals(expected, actual); + } +} Property changes on: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/ExportQueryServiceTest.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native