This is an automated email from the git hooks/post-receive script. New commit to branch feature/7974-cloud-tag-on-project in repository coselmar. See http://git.forge.codelutin.com/coselmar.git commit d84fffe330a8261abfe8f1d7f7cd4d9c8b33813b Author: Yannick Martel <martel@©odelutin.com> Date: Tue Feb 16 17:25:37 2016 +0100 refs-60 #7974 Add service with *postgresql* request to get top words from project and associated documents --- .../coselmar/config/CoselmarServicesConfig.java | 7 +++ .../persistence/entity/QuestionTopiaDao.java | 63 ++++++++++++++++++++++ .../migration/V1_5_0_1__7974_add_pg_dictionary.sql | 32 +++++++++++ .../coselmar/services/v1/QuestionsWebService.java | 29 ++++++++++ coselmar-rest/src/main/resources/mapping | 1 + 5 files changed, 132 insertions(+) diff --git a/coselmar-persistence/src/main/java/fr/ifremer/coselmar/config/CoselmarServicesConfig.java b/coselmar-persistence/src/main/java/fr/ifremer/coselmar/config/CoselmarServicesConfig.java index 8e72e04..9cde4b3 100644 --- a/coselmar-persistence/src/main/java/fr/ifremer/coselmar/config/CoselmarServicesConfig.java +++ b/coselmar-persistence/src/main/java/fr/ifremer/coselmar/config/CoselmarServicesConfig.java @@ -37,6 +37,8 @@ import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuiton.config.ApplicationConfig; import org.nuiton.config.ArgumentsParserException; +import org.nuiton.topia.persistence.TopiaApplicationContext; +import org.nuiton.topia.persistence.TopiaConfiguration; /** * @author ymartel <martel@codelutin.com> @@ -162,4 +164,9 @@ public class CoselmarServicesConfig { public String getVersion() { return applicationConfig.getOption(CoselmarServicesConfigOption.APPLICATION_VERSION.key); } + + public boolean isPostgresqlDatabase() { + String hibernateDriverClass = applicationConfig.getOption("hibernate.connection.driver_class"); + return hibernateDriverClass.toLowerCase().contains("postgresql"); + } } diff --git a/coselmar-persistence/src/main/java/fr/ifremer/coselmar/persistence/entity/QuestionTopiaDao.java b/coselmar-persistence/src/main/java/fr/ifremer/coselmar/persistence/entity/QuestionTopiaDao.java index 0074d88..8c66d18 100644 --- a/coselmar-persistence/src/main/java/fr/ifremer/coselmar/persistence/entity/QuestionTopiaDao.java +++ b/coselmar-persistence/src/main/java/fr/ifremer/coselmar/persistence/entity/QuestionTopiaDao.java @@ -24,14 +24,20 @@ package fr.ifremer.coselmar.persistence.entity; * #L% */ +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; +import fr.ifremer.coselmar.beans.CloudWord; import fr.ifremer.coselmar.beans.QuestionSearchExample; import fr.ifremer.coselmar.persistence.DaoUtils; import org.apache.commons.lang3.StringUtils; import org.nuiton.topia.persistence.TopiaQueryBuilderAddCriteriaOrRunQueryStep; +import org.nuiton.topia.persistence.support.TopiaSqlQuery; import org.nuiton.util.pagination.PaginationParameter; import org.nuiton.util.pagination.PaginationResult; @@ -239,6 +245,15 @@ public class QuestionTopiaDao extends AbstractQuestionTopiaDao<Question> { return values; } + public List<CloudWord> findTopWords(String questionId) { + + forTopiaIdEquals(questionId).findAny(); + + List<CloudWord> values = topiaSqlSupport.findMultipleResult(new QuestionTermStatSqlQuery(questionId)); + + return values; + } + public String refineSearch(QuestionSearchExample searchExample, String alias, Map args) { StringBuilder finerHqlBuilder = new StringBuilder(" 1=1 "); @@ -349,4 +364,52 @@ public class QuestionTopiaDao extends AbstractQuestionTopiaDao<Question> { return finerHql; } + private static class QuestionTermStatSqlQuery extends TopiaSqlQuery<CloudWord> { + + private final String sql; + + private final String getSql(String questionId) { + return "SELECT word, nentry FROM ts_stat( ' select to_tsvector(''public.simple_english_conf'', q.title)" + + " || to_tsvector(''public.simple_english_conf'', q.summary)" + + " || to_tsvector(''public.simple_english_conf'', qt.theme)" + + " || COALESCE(to_tsvector(''public.simple_english_conf'', d.name),'''')" + + " || COALESCE(to_tsvector(''public.simple_english_conf'', dk.keywords),'''')" + + " || COALESCE(to_tsvector(''public.simple_english_conf'', d.summary),'''') FROM question q" + + " LEFT JOIN relateddocuments_relatedquestion ON" + + " relateddocuments_relatedquestion.relatedquestion = q.topiaid" + + " LEFT JOIN closingdocuments_relatedquestion ON" + + " closingdocuments_relatedquestion.relatedquestion = q.topiaid" + + " LEFT JOIN document d on" + + " d.topiaid = closingdocuments_relatedquestion.closingdocuments OR" + + " d.topiaid = relateddocuments_relatedquestion.relateddocuments" + + " LEFT JOIN question_theme qt ON" + + " qt.owner = q.topiaid" + + " LEFT JOIN document_keywords dk ON" + + " dk.owner = d.topiaid" + + " WHERE q.topiaid = ''" + questionId + "''" + + " ')" + + " WHERE char_length(word) > 3 " + + " ORDER BY nentry DESC " + + " LIMIT 30"; + } + + QuestionTermStatSqlQuery(String questionId) { + this.sql = getSql(questionId); + } + + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + PreparedStatement preparedStatement = connection.prepareStatement(sql); + return preparedStatement; + } + + @Override + public CloudWord prepareResult(ResultSet set) throws SQLException { + + CloudWord cloudWord = new CloudWord(set.getString(1), set.getLong(2)); + + return cloudWord; + + } + } } //QuestionTopiaDao diff --git a/coselmar-persistence/src/main/resources/db/migration/V1_5_0_1__7974_add_pg_dictionary.sql b/coselmar-persistence/src/main/resources/db/migration/V1_5_0_1__7974_add_pg_dictionary.sql new file mode 100644 index 0000000..2c1890d --- /dev/null +++ b/coselmar-persistence/src/main/resources/db/migration/V1_5_0_1__7974_add_pg_dictionary.sql @@ -0,0 +1,32 @@ +--- +-- #%L +-- Coselmar :: Persistence +-- %% +-- Copyright (C) 2014 - 2016 Ifremer, Code Lutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU 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 General Public +-- License along with this program. If not, see +-- <http://www.gnu.org/licenses/gpl-3.0.html>. +-- #L% +--- + +CREATE TEXT SEARCH DICTIONARY public.simple_english_dict ( + TEMPLATE = pg_catalog.simple, + STOPWORDS = english +); + +CREATE TEXT SEARCH CONFIGURATION public.simple_english_conf ( COPY = pg_catalog.english ); + +ALTER TEXT SEARCH CONFIGURATION simple_english_conf + ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word, hword, hword_part + WITH simple_english_dict; diff --git a/coselmar-rest/src/main/java/fr/ifremer/coselmar/services/v1/QuestionsWebService.java b/coselmar-rest/src/main/java/fr/ifremer/coselmar/services/v1/QuestionsWebService.java index 7a8eaab..d4491f0 100644 --- a/coselmar-rest/src/main/java/fr/ifremer/coselmar/services/v1/QuestionsWebService.java +++ b/coselmar-rest/src/main/java/fr/ifremer/coselmar/services/v1/QuestionsWebService.java @@ -38,6 +38,7 @@ import com.google.common.base.Preconditions; import com.google.common.collect.Collections2; import com.google.common.collect.Lists; import com.google.common.collect.Sets; +import fr.ifremer.coselmar.beans.CloudWord; import fr.ifremer.coselmar.beans.DocumentBean; import fr.ifremer.coselmar.beans.LinkBean; import fr.ifremer.coselmar.beans.QuestionBean; @@ -62,6 +63,7 @@ import fr.ifremer.coselmar.persistence.entity.QuestionImpl; import fr.ifremer.coselmar.persistence.entity.Status; import fr.ifremer.coselmar.services.CoselmarWebServiceSupport; import fr.ifremer.coselmar.services.errors.InvalidCredentialException; +import fr.ifremer.coselmar.services.errors.NoResultException; import fr.ifremer.coselmar.services.errors.UnauthorizedException; import fr.ifremer.coselmar.services.indexation.QuestionsIndexationService; import org.apache.commons.io.IOUtils; @@ -1078,6 +1080,33 @@ public class QuestionsWebService extends CoselmarWebServiceSupport { } + public List<CloudWord> getTopWords(String questionId) throws InvalidCredentialException, UnauthorizedException, NoResultException { + + // Check authentication + String authorization = getContext().getHeader("Authorization"); + UserWebToken userWebToken = checkAuthentication(authorization); + + // Check current user + String fullCurrentUserId = getFullUserIdFromShort(userWebToken.getUserId()); + getCoselmarUserDao().forTopiaIdEquals(fullCurrentUserId).findAny(); + + List<CloudWord> topWords; + if (getCoselmarServicesConfig().isPostgresqlDatabase()) { + try { + topWords = getQuestionDao().findTopWords(getFullIdFromShort(Question.class, questionId)); + } catch (TopiaNoResultException e) { + if (log.isErrorEnabled()) { + log.error("Try to find top words for non existing questionId" + questionId, e); + } + throw new NoResultException("Question does not exist"); + } + } else { + topWords = Collections.EMPTY_LIST; + } + + return topWords; + } + //////////////////////////////////////////////////////////////////////////// /////////////////////// Internal Parts ///////////////////////////// //////////////////////////////////////////////////////////////////////////// diff --git a/coselmar-rest/src/main/resources/mapping b/coselmar-rest/src/main/resources/mapping index 7c9ac1b..b498a97 100644 --- a/coselmar-rest/src/main/resources/mapping +++ b/coselmar-rest/src/main/resources/mapping @@ -70,6 +70,7 @@ POST /v1/questions QuestionsWebService.addQuestion DELETE /v1/questions/{questionId} QuestionsWebService.deleteQuestion GET /v1/questions/{questionId}/ancestors QuestionsWebService.getAncestors depth=2 GET /v1/questions/{questionId}/descendants QuestionsWebService.getDescendants depth=2 +GET /v1/questions/{questionId}/topwords QuestionsWebService.getTopWords # Transverse Api -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.