KB73 Add full text search to post lists.

Change-Id: Ib8333b39cef1d7035ab7fac0ff8a03b400adcb40

7 files changed, 211 insertions, 16 deletions

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<Text extends PostText<?>> extends PanacheEntityBase {
       @CheckForNull User owner,
       @CheckForNull Integer cursor,
       CriteriaBuilder cb,
-      boolean forward) {
+      boolean forward,
+      @CheckForNull String searchQuery) {
     CriteriaQuery<T> query = cb.createQuery(entityClass);
 
     var conditions = new ArrayList<Predicate>();
@@ -177,6 +179,23 @@ public abstract class Post<Text extends PostText<?>> 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<Text extends PostText<?>> extends PanacheEntityBase {
 
   public static PostPage<Post<? extends PostText>> 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<Post<? extends PostText>> findViewable(
@@ -257,7 +276,8 @@ public abstract class Post<Text extends PostText<?>> extends PanacheEntityBase {
       @CheckForNull User viewer,
       @CheckForNull User owner,
       @CheckForNull Integer cursor,
-      @CheckForNull Integer count) {
+      @CheckForNull Integer count,
+      @CheckForNull String searchQuery) {
     Class<? extends Post> entityClass;
     switch (postFilter) {
       case BOOKMARKS_ONLY:
@@ -269,7 +289,7 @@ public abstract class Post<Text extends PostText<?>> 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 <T extends Post<? extends PostText>> PostPage<T> findViewable(
@@ -278,7 +298,8 @@ public abstract class Post<Text extends PostText<?>> 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<Text extends PostText<?>> 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<Text extends PostText<?>> 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<OwningPost extends Post<?>> 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 @@
+<?xml version="1.1" encoding="UTF-8" standalone="no"?>
+<databaseChangeLog
+  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+  xsi:schemaLocation="
+    http://www.liquibase.org/xml/ns/dbchangelog
+    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd">
+
+  <changeSet author="mulk" id="1.6-1">
+    <sql>
+      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;
+    </sql>
+
+    <sql>
+      CREATE INDEX bookmark_texts_search_idx ON benki.bookmark_texts
+        USING GIN (to_tsvector(language_regconfig(language), title || ' ' || description));
+    </sql>
+
+    <sql>
+      CREATE INDEX lazychat_message_texts_search_idx ON benki.lazychat_message_texts
+        USING GIN (to_tsvector(language_regconfig(language), content));
+    </sql>
+  </changeSet>
+
+  <changeSet id="1.6-2" author="mulk">
+    <createProcedure procedureName="post_matches_websearch">
+      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;
+    </createProcedure>
+
+    <createProcedure procedureName="post_matches_websearch">
+      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;
+    </createProcedure>
+  </changeSet>
+
+  <changeSet id="1.6-3" author="mulk">
+    <addColumn tableName="bookmark_texts" schemaName="benki">
+      <column name="search_terms" type="tsvector"/>
+    </addColumn>
+    <sql>
+      CREATE INDEX bookmark_texts_search_terms_idx ON benki.bookmark_texts
+        USING GIN (search_terms);
+    </sql>
+
+    <addColumn tableName="lazychat_message_texts" schemaName="benki">
+      <column name="search_terms" type="tsvector"/>
+    </addColumn>
+    <sql>
+      CREATE INDEX lazychat_message_texts_search_terms_idx ON benki.lazychat_message_texts
+        USING GIN (search_terms);
+    </sql>
+
+    <addColumn tableName="post_texts" schemaName="benki">
+      <column name="search_terms" type="tsvector"/>
+    </addColumn>
+
+    <createProcedure procedureName="bookmark_search_term_update_trigger">
+      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;
+    </createProcedure>
+
+    <createProcedure procedureName="lazychat_message_search_term_update_trigger">
+      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;
+    </createProcedure>
+
+    <sql>
+      CREATE TRIGGER bookmark_search_term_update
+      BEFORE INSERT OR UPDATE
+      ON benki.bookmark_texts
+      FOR EACH ROW
+      EXECUTE FUNCTION
+        bookmark_search_term_update_trigger();
+    </sql>
+
+    <sql>
+      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();
+    </sql>
+  </changeSet>
+
+  <changeSet id="1.6-4" author="mulk">
+    <sql>
+      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');
+    </sql>
+
+    <sql>
+      UPDATE benki.lazychat_message_texts
+        SET search_terms =
+          to_tsvector(language_regconfig(language), coalesce(content,''));
+    </sql>
+  </changeSet>
+
+</databaseChangeLog>
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 @@
   <include file="db/changeLog-1.3.xml"/>
   <include file="db/changeLog-1.4.xml"/>
   <include file="db/changeLog-1.5.xml"/>
+  <include file="db/changeLog-1.6.xml"/>
 
 </databaseChangeLog>
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}
 
 <div class="paging">
-  {#if hasPreviousPage}<a href="?i={previousCursor}&n={pageSize}" class="pure-button">⇠ previous page</a>{/if}
-  <span class="filler"></span>
-  {#if hasNextPage}<a href="?i={nextCursor}&n={pageSize}" class="pure-button">next page ⇢</a>{/if}
+  <form id="post-search-bar" method="get" class="pure-form">
+    <input placeholder="Search..." value="{searchQuery}" aria-label="Search" id="post-search-query" name="search-query" type="search"/>
+    <input type="submit" aria-label="Submit search query" class="pure-button pure-button-primary" value="&#x1f50e;"/>
+  </form>
+  {#if hasPreviousPage}<a href="?i={previousCursor}&n={pageSize}&search-query={searchQuery}" class="pure-button">⇠ previous page</a>{/if}
+  {#if hasNextPage}<a href="?i={nextCursor}&n={pageSize}&search-query={searchQuery}" class="pure-button">next page ⇢</a>{/if}
 </div>
 
 <section id="main-content">
@@ -127,9 +131,9 @@
 </section>
 
 <div class="paging">
-  {#if hasPreviousPage}<a href="?i={previousCursor}&n={pageSize}" class="pure-button">⇠ previous page</a>{/if}
+  {#if hasPreviousPage}<a href="?i={previousCursor}&n={pageSize}&search-query={searchQuery}" class="pure-button">⇠ previous page</a>{/if}
   <span class="filler"></span>
-  {#if hasNextPage}<a href="?i={nextCursor}&n={pageSize}" class="pure-button">next page ⇢</a>{/if}
+  {#if hasNextPage}<a href="?i={nextCursor}&n={pageSize}&search-query={searchQuery}" class="pure-button">next page ⇢</a>{/if}
 </div>
 
 {/body}