diff options
author | osmarks <osmarks> | 2021-06-14 18:06:48 +0000 |
---|---|---|
committer | osmarks <osmarks> | 2021-06-14 18:06:48 +0000 |
commit | 1f447eda8ed0fb1ba7c4ff6da8d40fe56aaddabf (patch) | |
tree | f33666313dd1f356923115dda97c474c5cb0f9ea | |
parent | 5d394e581b108eaf1470d5d00b1699d6ade25b4c (diff) |
Implement search (SQLite FTS5)
-rw-r--r-- | apioforum/db.py | 20 | ||||
-rw-r--r-- | apioforum/forum.py | 23 | ||||
-rw-r--r-- | apioforum/fuzzy.py | 45 | ||||
-rw-r--r-- | apioforum/mdrender.py | 1 | ||||
-rw-r--r-- | apioforum/static/style.css | 4 | ||||
-rw-r--r-- | apioforum/templates/common.html | 2 | ||||
-rw-r--r-- | apioforum/templates/search_results.html | 18 |
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 %} |