summaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorosmarks <osmarks>2021-06-14 18:06:48 +0000
committerosmarks <osmarks>2021-06-14 18:06:48 +0000
commit1f447eda8ed0fb1ba7c4ff6da8d40fe56aaddabf (patch)
treef33666313dd1f356923115dda97c474c5cb0f9ea
parent5d394e581b108eaf1470d5d00b1699d6ade25b4c (diff)
Implement search (SQLite FTS5)
-rw-r--r--apioforum/db.py20
-rw-r--r--apioforum/forum.py23
-rw-r--r--apioforum/fuzzy.py45
-rw-r--r--apioforum/mdrender.py1
-rw-r--r--apioforum/static/style.css4
-rw-r--r--apioforum/templates/common.html2
-rw-r--r--apioforum/templates/search_results.html18
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 %}