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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
# 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 .roles import forum_perms, overridden_perms
from sqlite3 import OperationalError
import datetime
bp = Blueprint("forum", __name__, url_prefix="/")
@bp.route("/")
def not_actual_index():
return redirect("/1")
def forum_path(forum_id):
db = get_db()
ancestors = db.execute("""
WITH RECURSIVE fs AS
(SELECT * FROM forums WHERE id = ?
UNION ALL
SELECT forums.* FROM forums, fs WHERE fs.parent=forums.id)
SELECT * FROM fs;
""",(forum_id,)).fetchall()
ancestors.reverse()
return ancestors
@bp.route("/<int:forum_id>")
def view_forum(forum_id):
db = get_db()
forum = db.execute("SELECT * FROM forums WHERE id = ?",(forum_id,)).fetchone()
threads = db.execute(
"""SELECT
threads.id, threads.title, threads.creator, threads.created,
threads.updated, number_of_posts.num_replies,
most_recent_posts.created as mrp_created,
most_recent_posts.author as mrp_author,
most_recent_posts.id as mrp_id,
most_recent_posts.content as mrp_content
FROM threads
INNER JOIN most_recent_posts ON most_recent_posts.thread = threads.id
INNER JOIN number_of_posts ON number_of_posts.thread = threads.id
WHERE threads.forum = ?
ORDER BY threads.updated DESC;
""",(forum_id,)).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()
subforums_rows = db.execute("""
SELECT max(threads.updated) as updated, forums.* FROM forums
LEFT OUTER JOIN threads ON threads.forum=forums.id
WHERE parent = ?
GROUP BY forums.id
ORDER BY name ASC
""",(forum_id,)).fetchall()
subforums = []
for s in subforums_rows:
a={}
a.update(s)
if a['updated'] is not None:
a['updated'] = datetime.datetime.fromisoformat(a['updated'])
subforums.append(a)
return render_template("view_forum.html",
forum=forum,
subforums=subforums,
threads=threads,
thread_tags=thread_tags,
)
@bp.route("/<int:forum_id>/create_thread",methods=("GET","POST"))
def create_thread(forum_id):
db = get_db()
forum = db.execute("SELECT * FROM forums WHERE id = ?",(forum_id,)).fetchone()
if forum is None:
flash("that forum doesn't exist")
return redirect(url_for('index'))
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,forum) VALUES (?,?,current_timestamp,current_timestamp,?);",
(title,g.user,forum_id)
)
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("/<int:forum_id>/roles",methods=("GET","POST"))
def edit_roles(forum_id):
db = get_db()
forum = db.execute("SELECT * FROM forums WHERE id = ?",(forum_id,)).fetchone()
role_configs = db.execute(
"SELECT * FROM role_config WHERE forum = ? ORDER BY ID ASC",
(forum_id,)).fetchall()
return render_template("edit_permissions.html",
forum=forum,
role_configs=role_configs,
other_roles=["the","test","placeholder"],
)
@bp.route("/<int:forum_id>/roles/new",methods=["POST"])
def add_role(forum_id):
return "placeholder"
@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)
|
