Implement search (SQLite FTS5)

7 files changed, 88 insertions, 25 deletions

diff --git a/apioforum/db.py b/apioforum/db.py
index b138aae..c24aa0e 100644
--- a/apioforum/db.py
+++ b/apioforum/db.py
@@ -46,6 +46,26 @@ CREATE INDEX posts_thread_idx ON posts (thread);
 ALTER TABLE posts ADD COLUMN edited INT NOT NULL DEFAULT 0;
 ALTER TABLE posts ADD COLUMN updated TIMESTAMP;
 """,
+"""
+CREATE VIRTUAL TABLE posts_fts USING fts5(
+    content,
+    content=posts,
+    content_rowid=id,
+    tokenize='porter unicode61 remove_diacritics 2'
+);
+INSERT INTO posts_fts (rowid, content) SELECT id, content FROM posts;
+
+CREATE TRIGGER posts_ai AFTER INSERT ON posts BEGIN
+    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
+END;
+CREATE TRIGGER posts_ad AFTER DELETE ON posts BEGIN
+    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
+END;
+CREATE TRIGGER posts_au AFTER UPDATE ON posts BEGIN
+    INSERT INTO posts_fts(posts_fts, rowid, content) VALUES('delete', old.id, old.content);
+    INSERT INTO posts_fts(rowid, content) VALUES (new.id, new.content);
+END;
+"""
 ]
 
 def init_db():
diff --git a/apioforum/forum.py b/apioforum/forum.py
index 83362fc..81674a8 100644
--- a/apioforum/forum.py
+++ b/apioforum/forum.py
@@ -6,6 +6,7 @@ from flask import (
     g, redirect, url_for, flash
 )
 from .db import get_db
+from .mdrender import render
 
 bp = Blueprint("forum", __name__, url_prefix="/")
 
@@ -55,3 +56,25 @@ def create_thread():
         
     return render_template("create_thread.html")
 
+@bp.route("/search")
+def search():
+    db = get_db()
+    query = request.args["q"]
+    results = db.execute("""
+    SELECT posts.id, highlight(posts_fts, 0, '<mark>', '</mark>') AS content, posts.thread, posts.author, posts.created, posts.edited, posts.updated, threads.title AS thread_title
+    FROM posts_fts
+    JOIN posts ON posts_fts.rowid = posts.id
+    JOIN threads ON threads.id = posts.thread
+    WHERE posts_fts MATCH ?
+    ORDER BY rank
+    LIMIT 50
+    """, (query,)).fetchall()
+
+    display_thread_id = [ True ] * len(results)
+    last_thread = None
+    for ix, result in enumerate(results):
+        if result["thread"] == last_thread:
+            display_thread_id[ix] = False
+        last_thread = result["thread"]
+    rendered_posts = [render(q['content']) for q in results]
+    return render_template("search_results.html", results=results, query=query, rendered_posts=rendered_posts, display_thread_id=display_thread_id)
\ No newline at end of file
diff --git a/apioforum/fuzzy.py b/apioforum/fuzzy.py
index 58f8c6b..94e99c9 100644
--- a/apioforum/fuzzy.py
+++ b/apioforum/fuzzy.py
@@ -1,35 +1,32 @@
 # fuzzy datetime things
 
-times = (
-    ("year","years",365*24*60*60), # leap years aren't real
-    ("day","days",24*60*60),
-    ("hour","hours",60*60),
-    ("minute","minutes",60),
-    ("second","seconds",1),
+units = (
+    ("y", "year","years",365*24*60*60), # leap years aren't real
+    ("d", "day","days",24*60*60),
+    ("h", "hour","hours",60*60),
+    ("m", "minute","minutes",60),
+    ("s", "second","seconds",1),
 )
 
-from datetime import datetime, timedelta 
+from datetime import datetime, timedelta, timezone
 
-def fuzzy(seconds,ago=True):
-    if isinstance(seconds,timedelta):
+def fuzzy(seconds, ago=False):
+    if isinstance(seconds, timedelta):
         seconds = seconds.total_seconds()
-    elif isinstance(seconds,datetime):
-        seconds = (seconds-datetime.now()).total_seconds()
+    elif isinstance(seconds, datetime):
+        seconds = (seconds.replace(tzinfo=timezone.utc) - datetime.now(tz=timezone.utc)).total_seconds()
 
     fmt = "{}"
+    buf = ""
     if ago:
         fmt = "in {}" if seconds > 0 else "{} ago"
+    elif seconds > 0: fmt = "in {}"
     seconds = abs(seconds)
-    for t in times:
-        if seconds >= t[2]:
-            rounded = round((seconds / t[2])*100)/100
-            if int(rounded) == rounded:
-                rounded = int(rounded)
-            if rounded == 1:
-                word = t[0]
-            else:
-                word = t[1]
-            return fmt.format(f'{rounded} {word}')
-    else:
-        return "now"
-            
+    for short, _, _, unit_length in units:
+        if seconds >= unit_length:
+            qty = seconds // unit_length
+            buf += str(int(qty)) + short
+            seconds -= qty * unit_length
+    if not buf: return "now"
+
+    return fmt.format(buf)
diff --git a/apioforum/mdrender.py b/apioforum/mdrender.py
index fde1c85..1df104f 100644
--- a/apioforum/mdrender.py
+++ b/apioforum/mdrender.py
@@ -10,6 +10,7 @@ allowed_tags = [
     'h6',
     'pre',
     'del',
+    'mark'
 ]
 allowed_tags.extend(bleach.sanitizer.ALLOWED_TAGS)
 
diff --git a/apioforum/static/style.css b/apioforum/static/style.css
index 4efc1ec..1ff0eff 100644
--- a/apioforum/static/style.css
+++ b/apioforum/static/style.css
@@ -149,3 +149,7 @@ blockquote {
     padding-left: 10px;
     border-left: 3px solid grey;
 }
+
+.search-form {
+    display: inline;
+}
\ No newline at end of file
diff --git a/apioforum/templates/common.html b/apioforum/templates/common.html
index 2206ac3..fc41410 100644
--- a/apioforum/templates/common.html
+++ b/apioforum/templates/common.html
@@ -14,7 +14,7 @@
             <a class="actionbutton"
                href="{{url_for('thread.delete_post',post_id=post.id)}}">delete</a>
         {% endif %}
-        <a class="post-anchor-link" href="#post_{{post.id}}">#{{post.id}}</a>
+        <a class="post-anchor-link" href="{{url_for('thread.view_thread', thread_id=post.thread)}}}#post_{{post.id}}">#{{post.id}}</a>
         </span>
     </div>
     <div class="post-content">
diff --git a/apioforum/templates/search_results.html b/apioforum/templates/search_results.html
new file mode 100644
index 0000000..7035e8f
--- /dev/null
+++ b/apioforum/templates/search_results.html
@@ -0,0 +1,18 @@
+{% from 'common.html' import disp_post %}
+{% extends 'base.html' %}
+{% block header %}
+<h1>{%block title %}Results for {{query}}{% endblock %}</h1>
+{% endblock %}
+
+{%block content%}
+<div class="results">
+    {% for result in results %}
+    {% if display_thread_id[loop.index0] %}
+        <h3><a href="{{url_for('thread.view_thread', thread_id=result.thread)}}">{{ result.thread_title }}</a></h3>
+    {% endif %}
+    {% call disp_post(result, True) %}
+        {{ rendered_posts[loop.index0] | safe}}
+    {% endcall %}
+    {% endfor %}
+</div>
+{% endblock %}