From 8595c80aad53adfe414bad57fa7aab6a5d8d25c1 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 19 Dec 2022 16:54:47 +0300 Subject: PostgresSearchService: make it readable --- .../java/com/juick/service/PostgreGINService.java | 109 ------------------ .../com/juick/service/PostgresSearchService.java | 125 +++++++++++++++++++++ 2 files changed, 125 insertions(+), 109 deletions(-) delete mode 100644 src/main/java/com/juick/service/PostgreGINService.java create mode 100644 src/main/java/com/juick/service/PostgresSearchService.java (limited to 'src/main/java') diff --git a/src/main/java/com/juick/service/PostgreGINService.java b/src/main/java/com/juick/service/PostgreGINService.java deleted file mode 100644 index d02e79a7..00000000 --- a/src/main/java/com/juick/service/PostgreGINService.java +++ /dev/null @@ -1,109 +0,0 @@ -/* - * Copyright (C) 2008-2022, Juick - * - * 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 Affero 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 . - */ - -package com.juick.service; - -import com.juick.model.User; -import org.apache.commons.lang3.StringUtils; -import org.postgresql.PGConnection; -import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; -import org.springframework.stereotype.Repository; -import org.springframework.transaction.annotation.Transactional; - -import javax.inject.Inject; -import javax.sql.DataSource; -import java.sql.SQLException; -import java.util.Collections; -import java.util.List; -import java.util.stream.Collectors; - -/** - * Created by aalexeev on 11/18/16. - */ - -@Repository -@ConditionalOnProperty(name = "spring.sql.init.platform", havingValue = "postgresql") -@Transactional(readOnly = true) -public class PostgreGINService extends BaseJdbcService implements SearchService { - private static final int DEFAULT_MAX_RESULT = 20; - - private int maxResult = DEFAULT_MAX_RESULT; - - @Inject - UserService userService; - @Inject - DataSource dataSource; - - private String escapeQuery(String searchString) { - try (var connection = dataSource.getConnection()) { - var pg = connection.unwrap(PGConnection.class); - return pg.escapeLiteral(searchString); - } catch (SQLException e) { - e.printStackTrace(); - } - return searchString; - } - - public String sortHint(String escapedSearchString) { - boolean isOneWord = escapedSearchString.split("[^\\S\\+]+").length == 1; - return isOneWord ? " message_id desc" : String.format(" ts_rank(to_tsvector('russian', txt), websearch_to_tsquery('russian', '%s')) DESC, message_id desc", escapedSearchString); - } - - @Override - public List searchInAllMessages(User visitor, final String searchString, final int page) { - if (StringUtils.isBlank(searchString)) - return Collections.emptyList(); - - String usersFilter = userService.getUserBLUsers(visitor.getUid()).stream().map(u -> String.valueOf(u.getUid())).collect(Collectors.joining(",")); - var filter = usersFilter.isBlank() ? - "to_tsvector('russian', txt) @@ websearch_to_tsquery('russian', '" + escapeQuery(searchString) + "')" - : "to_tsvector('russian', txt) @@ websearch_to_tsquery('russian', '" + escapeQuery(searchString) + "') AND messages.user_id not in (" + usersFilter + ")"; - var offset = page * maxResult; - var sql = String.format(""" - SELECT messages.message_id FROM messages_txt inner join messages - on messages.message_id = messages_txt.message_id - WHERE %s - ORDER BY %s - LIMIT %d OFFSET %d - """, filter, sortHint(escapeQuery(searchString)), maxResult, offset); - return getJdbcTemplate().queryForList(sql, Integer.class); - } - - @Override - public List searchByStringAndUser(User visitor, final String searchString, final int userId, int page) { - if (StringUtils.isBlank(searchString)) - return Collections.emptyList(); - - var offset = page * maxResult; - var sql = String.format(""" - SELECT messages.message_id FROM messages_txt inner join messages - on messages.message_id = messages_txt.message_id - WHERE to_tsvector('russian', txt) @@ websearch_to_tsquery('russian', '%s') AND user_id=%d - ORDER BY %s - LIMIT %d OFFSET %d - """, escapeQuery(searchString), userId, sortHint(escapeQuery(searchString)), maxResult, offset); - return getJdbcTemplate().queryForList(sql, Integer.class); - } - - @Override - public void setMaxResult(int maxResult) { - if (maxResult <= 0) - throw new IllegalArgumentException("maxResult value (" + maxResult + ") must be greater then 0"); - - this.maxResult = maxResult; - } -} \ No newline at end of file diff --git a/src/main/java/com/juick/service/PostgresSearchService.java b/src/main/java/com/juick/service/PostgresSearchService.java new file mode 100644 index 00000000..500f3d20 --- /dev/null +++ b/src/main/java/com/juick/service/PostgresSearchService.java @@ -0,0 +1,125 @@ +/* + * Copyright (C) 2008-2022, Juick + * + * 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 Affero 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 . + */ + +package com.juick.service; + +import com.juick.model.User; +import org.apache.commons.lang3.StringUtils; +import org.postgresql.PGConnection; +import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; +import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; +import org.springframework.stereotype.Repository; +import org.springframework.transaction.annotation.Transactional; + +import javax.inject.Inject; +import javax.sql.DataSource; +import java.sql.SQLException; +import java.util.Collections; +import java.util.List; + +/** + * Created by aalexeev on 11/18/16. + */ + +@Repository +@ConditionalOnProperty(name = "spring.sql.init.platform", havingValue = "postgresql") +@Transactional(readOnly = true) +public class PostgresSearchService extends BaseJdbcService implements SearchService { + private static final int DEFAULT_MAX_RESULT = 20; + + private int maxResult = DEFAULT_MAX_RESULT; + + @Inject + UserService userService; + @Inject + DataSource dataSource; + + private String escapeQuery(String searchString) { + try (var connection = dataSource.getConnection()) { + var pg = connection.unwrap(PGConnection.class); + return pg.escapeLiteral(searchString); + } catch (SQLException e) { + e.printStackTrace(); + } + return searchString; + } + + + public List search(final int visitorId, final int userId, final String searchString, final int page) { + var usersFilter = userService.getUserBLUsers(visitorId).stream().map(User::getUid).toList(); + var offset = page * maxResult; + var query = escapeQuery(searchString); + var isOneWord = query.split("[^\\S\\+]+").length == 1; + var sql = """ + SELECT + message_id + FROM + ( + select + messages.message_id message_id, + messages.user_id user_id, + txt, + to_tsvector('russian', txt) v, + websearch_to_tsquery('russian', :query) q + from + messages_txt + inner join messages on messages.message_id = messages_txt.message_id + ) t + WHERE + v @@ q + """ + + ( userId > 0 ? "AND user_id = :user_id" : "" ) + + """ + """ + + (usersFilter.isEmpty() ? "" : " AND user_id NOT IN (:filter)") + + """ + ORDER BY + """ + + (isOneWord ? "message_id DESC" : "ts_rank_cd(v, q) DESC, message_id DESC") + + """ + LIMIT :limit OFFSET :offset + """; + return getNamedParameterJdbcTemplate().queryForList(sql, + new MapSqlParameterSource() + .addValue("query", escapeQuery(searchString)) + .addValue("user_id", userId) + .addValue("filter", usersFilter) + .addValue("limit", maxResult) + .addValue("offset", offset), Integer.class); + } + + @Override + public List searchInAllMessages(User visitor, final String searchString, final int page) { + if (StringUtils.isBlank(searchString)) + return Collections.emptyList(); + return search(visitor.getUid(), -1, searchString, page); + } + + @Override + public List searchByStringAndUser(User visitor, final String searchString, final int userId, int page) { + if (StringUtils.isBlank(searchString)) + return Collections.emptyList(); + return search(visitor.getUid(), userId, searchString, page); + } + + @Override + public void setMaxResult(int maxResult) { + if (maxResult <= 0) + throw new IllegalArgumentException("maxResult value (" + maxResult + ") must be greater then 0"); + this.maxResult = maxResult; + } +} \ No newline at end of file -- cgit v1.2.3