From 8563a3c03538600ae148b75debd31af16047e2d5 Mon Sep 17 00:00:00 2001 From: Matthias Andreas Benkard Date: Wed, 16 Sep 2020 17:57:24 +0200 Subject: KB73 Add full text search to post lists. Change-Id: Ib8333b39cef1d7035ab7fac0ff8a03b400adcb40 --- .../java/eu/mulk/mulkcms2/benki/posts/Post.java | 36 ++++-- .../eu/mulk/mulkcms2/benki/posts/PostResource.java | 10 +- .../eu/mulk/mulkcms2/benki/posts/PostText.java | 6 + .../resources/META-INF/resources/cms2/base.css | 21 ++++ src/main/resources/db/changeLog-1.6.xml | 139 +++++++++++++++++++++ src/main/resources/db/changeLog.xml | 1 + .../resources/templates/benki/posts/postList.html | 14 ++- 7 files changed, 211 insertions(+), 16 deletions(-) create mode 100644 src/main/resources/db/changeLog-1.6.xml (limited to 'src/main') diff --git a/src/main/java/eu/mulk/mulkcms2/benki/posts/Post.java b/src/main/java/eu/mulk/mulkcms2/benki/posts/Post.java index 8f2166c..fd023d7 100644 --- a/src/main/java/eu/mulk/mulkcms2/benki/posts/Post.java +++ b/src/main/java/eu/mulk/mulkcms2/benki/posts/Post.java @@ -19,6 +19,7 @@ import java.util.Objects; import java.util.Set; import java.util.TimeZone; import java.util.stream.Collectors; +import java.util.stream.Stream; import javax.annotation.CheckForNull; import javax.annotation.Nullable; import javax.json.bind.annotation.JsonbTransient; @@ -132,7 +133,8 @@ public abstract class Post> extends PanacheEntityBase { @CheckForNull User owner, @CheckForNull Integer cursor, CriteriaBuilder cb, - boolean forward) { + boolean forward, + @CheckForNull String searchQuery) { CriteriaQuery query = cb.createQuery(entityClass); var conditions = new ArrayList(); @@ -177,6 +179,23 @@ public abstract class Post> extends PanacheEntityBase { } } + if (searchQuery != null && !searchQuery.isBlank()) { + var postTexts = post.join(Post_.texts); + var localizedSearches = + Stream.of("de", "en") + .map( + language -> + cb.isTrue( + cb.function( + "post_matches_websearch", + Boolean.class, + postTexts.get(PostText_.searchTerms), + cb.literal(language), + cb.literal(searchQuery)))) + .toArray(n -> new Predicate[n]); + conditions.add(cb.or(localizedSearches)); + } + query.where(conditions.toArray(new Predicate[0])); return query; @@ -248,7 +267,7 @@ public abstract class Post> extends PanacheEntityBase { public static PostPage> findViewable( PostFilter postFilter, Session session, @CheckForNull User viewer, @CheckForNull User owner) { - return findViewable(postFilter, session, viewer, owner, null, null); + return findViewable(postFilter, session, viewer, owner, null, null, null); } public static PostPage> findViewable( @@ -257,7 +276,8 @@ public abstract class Post> extends PanacheEntityBase { @CheckForNull User viewer, @CheckForNull User owner, @CheckForNull Integer cursor, - @CheckForNull Integer count) { + @CheckForNull Integer count, + @CheckForNull String searchQuery) { Class entityClass; switch (postFilter) { case BOOKMARKS_ONLY: @@ -269,7 +289,7 @@ public abstract class Post> extends PanacheEntityBase { default: entityClass = Post.class; } - return findViewable(entityClass, session, viewer, owner, cursor, count); + return findViewable(entityClass, session, viewer, owner, cursor, count, searchQuery); } protected static > PostPage findViewable( @@ -278,7 +298,8 @@ public abstract class Post> extends PanacheEntityBase { @CheckForNull User viewer, @CheckForNull User owner, @CheckForNull Integer cursor, - @CheckForNull Integer count) { + @CheckForNull Integer count, + @CheckForNull String searchQuery) { if (cursor != null) { Objects.requireNonNull(count); @@ -286,7 +307,7 @@ public abstract class Post> extends PanacheEntityBase { var cb = session.getCriteriaBuilder(); - var forwardCriteria = queryViewable(entityClass, viewer, owner, cursor, cb, true); + var forwardCriteria = queryViewable(entityClass, viewer, owner, cursor, cb, true, searchQuery); var forwardQuery = session.createQuery(forwardCriteria); if (count != null) { @@ -300,7 +321,8 @@ public abstract class Post> extends PanacheEntityBase { if (cursor != null) { // Look backwards as well so we can find the prevCursor. - var backwardCriteria = queryViewable(entityClass, viewer, owner, cursor, cb, false); + var backwardCriteria = + queryViewable(entityClass, viewer, owner, cursor, cb, false, searchQuery); var backwardQuery = session.createQuery(backwardCriteria); backwardQuery.setMaxResults(count); var backwardResults = backwardQuery.getResultList(); diff --git a/src/main/java/eu/mulk/mulkcms2/benki/posts/PostResource.java b/src/main/java/eu/mulk/mulkcms2/benki/posts/PostResource.java index 47e1594..59cacee 100644 --- a/src/main/java/eu/mulk/mulkcms2/benki/posts/PostResource.java +++ b/src/main/java/eu/mulk/mulkcms2/benki/posts/PostResource.java @@ -116,13 +116,14 @@ public abstract class PostResource { @Transactional public TemplateInstance getIndex( @QueryParam("i") @CheckForNull Integer cursor, - @QueryParam("n") @CheckForNull Integer maxResults) { + @QueryParam("n") @CheckForNull Integer maxResults, + @QueryParam("search-query") @CheckForNull String searchQuery) { maxResults = maxResults == null ? defaultMaxResults : maxResults; @CheckForNull var reader = getCurrentUser(); var session = entityManager.unwrap(Session.class); - var q = Post.findViewable(postFilter, session, reader, null, cursor, maxResults); + var q = Post.findViewable(postFilter, session, reader, null, cursor, maxResults, searchQuery); q.cacheDescriptions(); @@ -142,7 +143,8 @@ public abstract class PostResource { .data("hasNextPage", q.nextCursor != null) .data("previousCursor", q.prevCursor) .data("nextCursor", q.nextCursor) - .data("pageSize", maxResults); + .data("pageSize", maxResults) + .data("searchQuery", searchQuery); } @GET @@ -159,7 +161,7 @@ public abstract class PostResource { @CheckForNull var reader = getCurrentUser(); var owner = User.findByNickname(ownerName); var session = entityManager.unwrap(Session.class); - var q = Post.findViewable(postFilter, session, reader, owner, cursor, maxResults); + var q = Post.findViewable(postFilter, session, reader, owner, cursor, maxResults, null); q.cacheDescriptions(); diff --git a/src/main/java/eu/mulk/mulkcms2/benki/posts/PostText.java b/src/main/java/eu/mulk/mulkcms2/benki/posts/PostText.java index 8b1697c..25955bd 100644 --- a/src/main/java/eu/mulk/mulkcms2/benki/posts/PostText.java +++ b/src/main/java/eu/mulk/mulkcms2/benki/posts/PostText.java @@ -13,6 +13,8 @@ import javax.persistence.InheritanceType; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.Table; +import org.hibernate.annotations.Generated; +import org.hibernate.annotations.GenerationTime; @Entity @Table(name = "post_texts", schema = "benki") @@ -38,6 +40,10 @@ public abstract class PostText> extends PanacheEntity @CheckForNull public String cachedDescriptionHtml; + @Column(name = "search_terms") + @Generated(GenerationTime.ALWAYS) + public String searchTerms; + @ManyToOne(fetch = FetchType.LAZY, targetEntity = Post.class) @JoinColumn(name = "post", referencedColumnName = "id", nullable = false) @JsonbTransient diff --git a/src/main/resources/META-INF/resources/cms2/base.css b/src/main/resources/META-INF/resources/cms2/base.css index 70d164c..c041364 100644 --- a/src/main/resources/META-INF/resources/cms2/base.css +++ b/src/main/resources/META-INF/resources/cms2/base.css @@ -353,6 +353,27 @@ elix-expandable-section.editor-pane::part(header) { flex-basis: content; } +#post-search-bar { + flex: 1; + + display: inline-flex; + flex-direction: row; + flex-wrap: nowrap; + min-width: 5em; +} + +#post-search-query { + flex: 1; + flex-basis: content; + min-width: 0; +} + +#post-search-bar input[type=submit] { + flex: 1; + flex-grow: 0; + flex-basis: content; +} + elix-expandable-section .expandable-section-title { margin-top: 0; margin-bottom: 0; diff --git a/src/main/resources/db/changeLog-1.6.xml b/src/main/resources/db/changeLog-1.6.xml new file mode 100644 index 0000000..d03f63b --- /dev/null +++ b/src/main/resources/db/changeLog-1.6.xml @@ -0,0 +1,139 @@ + + + + + + CREATE FUNCTION language_regconfig(language VARCHAR) + RETURNS regconfig + AS $$ + SELECT CASE + WHEN $1 = 'de' THEN 'german' + WHEN $1 = 'en' THEN 'english' + WHEN $1 = 'fr' THEN 'french' + ELSE 'simple' + END :: regconfig + $$ IMMUTABLE LANGUAGE SQL; + + + + CREATE INDEX bookmark_texts_search_idx ON benki.bookmark_texts + USING GIN (to_tsvector(language_regconfig(language), title || ' ' || description)); + + + + CREATE INDEX lazychat_message_texts_search_idx ON benki.lazychat_message_texts + USING GIN (to_tsvector(language_regconfig(language), content)); + + + + + + CREATE FUNCTION post_matches_websearch( + post_language TEXT, + post_text TEXT, + websearch_language TEXT, + websearch_text TEXT) + RETURNS BOOLEAN + AS $$ + SELECT to_tsvector(language_regconfig($1), $2) @@ websearch_to_tsquery(language_regconfig($3), $4) + $$ LANGUAGE SQL IMMUTABLE; + + + + CREATE FUNCTION post_matches_websearch( + post_tsvector tsvector, + websearch_language TEXT, + websearch_text TEXT) + RETURNS BOOLEAN + AS $$ + SELECT $1 @@ websearch_to_tsquery(language_regconfig($2), $3) + $$ LANGUAGE SQL IMMUTABLE; + + + + + + + + + CREATE INDEX bookmark_texts_search_terms_idx ON benki.bookmark_texts + USING GIN (search_terms); + + + + + + + CREATE INDEX lazychat_message_texts_search_terms_idx ON benki.lazychat_message_texts + USING GIN (search_terms); + + + + + + + + CREATE FUNCTION bookmark_search_term_update_trigger() + RETURNS trigger + AS $$ + BEGIN + NEW.search_terms := + setweight(to_tsvector(language_regconfig(NEW.language), coalesce(NEW.title, '')), 'A') || + setweight(to_tsvector(language_regconfig(NEW.language), coalesce(NEW.description, '')), 'B'); + RETURN NEW; + END + $$ LANGUAGE plpgsql; + + + + CREATE FUNCTION lazychat_message_search_term_update_trigger() + RETURNS trigger + AS $$ + BEGIN + NEW.search_terms := + to_tsvector(language_regconfig(NEW.language), coalesce(NEW.content,'')); + RETURN NEW; + END + $$ LANGUAGE plpgsql; + + + + CREATE TRIGGER bookmark_search_term_update + BEFORE INSERT OR UPDATE + ON benki.bookmark_texts + FOR EACH ROW + EXECUTE FUNCTION + bookmark_search_term_update_trigger(); + + + + CREATE TRIGGER lazychat_message_search_term_update + BEFORE INSERT OR UPDATE + ON benki.lazychat_message_texts + FOR EACH ROW + EXECUTE FUNCTION + lazychat_message_search_term_update_trigger(); + + + + + + UPDATE benki.bookmark_texts + SET search_terms = + setweight(to_tsvector(language_regconfig(language), coalesce(title, '')), 'A') || + setweight(to_tsvector(language_regconfig(language), coalesce(description, '')), 'B'); + + + + UPDATE benki.lazychat_message_texts + SET search_terms = + to_tsvector(language_regconfig(language), coalesce(content,'')); + + + + diff --git a/src/main/resources/db/changeLog.xml b/src/main/resources/db/changeLog.xml index 1c5c4ea..f1c0849 100644 --- a/src/main/resources/db/changeLog.xml +++ b/src/main/resources/db/changeLog.xml @@ -12,5 +12,6 @@ + diff --git a/src/main/resources/templates/benki/posts/postList.html b/src/main/resources/templates/benki/posts/postList.html index deccfcd..89cea69 100644 --- a/src/main/resources/templates/benki/posts/postList.html +++ b/src/main/resources/templates/benki/posts/postList.html @@ -6,6 +6,7 @@ {@java.lang.Integer previousCursor} {@java.lang.Integer nextCursor} {@java.lang.Integer pageSize} +{@java.lang.String searchQuery} {#include base.html} @@ -43,9 +44,12 @@ {/if}
- {#if hasPreviousPage}⇠ previous page{/if} - - {#if hasNextPage}next page ⇢{/if} +
+ + +
+ {#if hasPreviousPage}⇠ previous page{/if} + {#if hasNextPage}next page ⇢{/if}
@@ -127,9 +131,9 @@
- {#if hasPreviousPage}⇠ previous page{/if} + {#if hasPreviousPage}⇠ previous page{/if} - {#if hasNextPage}next page ⇢{/if} + {#if hasNextPage}next page ⇢{/if}
{/body} -- cgit v1.2.3