From a6e17eb7aa1366a29a1c23b4bd40a3eb253f8ca9 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 17 Dec 2022 21:19:46 +0300 Subject: postgres: search service --- .../java/com/juick/service/PostgreGINService.java | 97 ++++++++++++++++++++++ .../com/juick/service/SphinxSearchService.java | 2 + .../db/specific/postgresql/V1.22__schema.sql | 4 +- 3 files changed, 102 insertions(+), 1 deletion(-) create mode 100644 src/main/java/com/juick/service/PostgreGINService.java diff --git a/src/main/java/com/juick/service/PostgreGINService.java b/src/main/java/com/juick/service/PostgreGINService.java new file mode 100644 index 00000000..f4f70bf4 --- /dev/null +++ b/src/main/java/com/juick/service/PostgreGINService.java @@ -0,0 +1,97 @@ +/* + * Copyright (C) 2008-2020, 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.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 java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +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; + + public String sortHint(String searchString) { + boolean isOneWord = searchString.split("[^\\S\\+]+").length == 1; + return isOneWord ? " message_id desc" : String.format(" ts_rank(to_tsvector('russian', \"txt\"), plainto_tsquery('russian', '%s')) DESC, message_id desc", searchString); + } + + @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) @@ plainto_tsquery('russian', '" + searchString + "')" + : "to_tsvector('russian', txt) @@ plainto_tsquery('russian', '" + 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(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) @@ plainto_tsquery('russian', '%s') AND user_id=%d + ORDER BY %s + LIMIT %d OFFSET %d + """, searchString, userId, sortHint(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/SphinxSearchService.java b/src/main/java/com/juick/service/SphinxSearchService.java index 302f10de..06faafc5 100644 --- a/src/main/java/com/juick/service/SphinxSearchService.java +++ b/src/main/java/com/juick/service/SphinxSearchService.java @@ -19,6 +19,7 @@ package com.juick.service; import com.juick.model.User; import org.apache.commons.lang3.StringUtils; +import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @@ -34,6 +35,7 @@ import java.util.stream.Collectors; */ @Repository +@ConditionalOnProperty(name = "spring.sql.init.platform", havingValue = "mysql") @Transactional(readOnly = true) public class SphinxSearchService extends BaseJdbcService implements SearchService { private static final int DEFAULT_MAX_RESULT = 20; diff --git a/src/test/resources/db/specific/postgresql/V1.22__schema.sql b/src/test/resources/db/specific/postgresql/V1.22__schema.sql index fbd50408..aab050a6 100644 --- a/src/test/resources/db/specific/postgresql/V1.22__schema.sql +++ b/src/test/resources/db/specific/postgresql/V1.22__schema.sql @@ -199,7 +199,7 @@ CREATE TABLE public.favorites ( message_id bigint NOT NULL, ts timestamp with time zone, like_id smallint DEFAULT 1 NOT NULL, - user_uri character varying(255) DEFAULT ''::character varying + user_uri character varying(255) NOT NULL DEFAULT ''::character varying ); @@ -423,6 +423,8 @@ CREATE TABLE public.messages_txt ( ALTER TABLE public.messages_txt OWNER TO juick; +CREATE INDEX messages_idx ON messages_txt USING GIN (to_tsvector('russian', txt)); + -- -- Name: places; Type: TABLE; Schema: public; Owner: juick -- -- cgit v1.2.3