1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
# view threads in a forum
# currently there is only ever one forum however
from flask import (
Blueprint, render_template, request,
g, redirect, url_for, flash
)
from .db import get_db
from .mdrender import render
from sqlite3 import OperationalError
bp = Blueprint("forum", __name__, url_prefix="/")
@bp.route("/")
def view_forum():
db = get_db()
threads = db.execute(
"""SELECT threads.id, threads.title, threads.creator, threads.created,
threads.updated, count(posts.id) as num_replies, max(posts.id), posts.author as last_user
FROM threads
INNER JOIN posts ON posts.thread = threads.id
GROUP BY threads.id
ORDER BY threads.updated DESC;
""").fetchall()
thread_tags = {}
#todo: somehow optimise this
for thread in threads:
thread_tags[thread['id']] = db.execute(
"""SELECT tags.* FROM tags
INNER JOIN thread_tags ON thread_tags.tag = tags.id
WHERE thread_tags.thread = ?
ORDER BY tags.id;
""",(thread['id'],)).fetchall()
return render_template("view_forum.html",threads=threads,thread_tags=thread_tags)
@bp.route("/create_thread",methods=("GET","POST"))
def create_thread():
db = get_db()
if g.user is None:
flash("you need to be logged in to create a thread")
return redirect(url_for('index'))
if request.method == "POST":
title = request.form['title']
content = request.form['content']
err = None
if len(title.strip()) == 0 or len(content.strip()) == 0:
err = "title and content can't be empty"
if err is None:
cur = db.cursor()
cur.execute(
"INSERT INTO threads (title,creator,created,updated) VALUES (?,?,current_timestamp,current_timestamp);",
(title,g.user)
)
thread_id = cur.lastrowid
cur.execute(
"INSERT INTO posts (thread,created,author,content) VALUES (?,current_timestamp,?,?);",
(thread_id,g.user,content)
)
db.commit()
return redirect(url_for('thread.view_thread',thread_id=thread_id))
flash(err)
return render_template("create_thread.html")
@bp.route("/search")
def search():
db = get_db()
query = request.args["q"]
try:
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()
except OperationalError:
flash('your search query was malformed.')
return redirect(url_for("forum.view_forum"))
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"]
return render_template("search_results.html", results=results, query=query, display_thread_id=display_thread_id)
|
