From 02e499acaff955e210f0c00e17820dd5a3362ab4 Mon Sep 17 00:00:00 2001 From: Chris Fulljames Date: Sat, 7 Feb 2026 14:08:34 -0500 Subject: [PATCH] Minor cleanup --- src/littlesongplace/activity.py | 41 ++++++++------- src/littlesongplace/comments.py | 88 +++++++++++++++------------------ 2 files changed, 63 insertions(+), 66 deletions(-) diff --git a/src/littlesongplace/activity.py b/src/littlesongplace/activity.py index ea78655..5a4ec29 100644 --- a/src/littlesongplace/activity.py +++ b/src/littlesongplace/activity.py @@ -14,7 +14,7 @@ def activity(): # Get comment notifications notifications = db.query( """ - select + SELECT c.content, c.commentid, c.replytoid, @@ -22,13 +22,13 @@ def activity(): rc.content as replyto_content, c.threadid, t.threadtype - from notifications as n - inner join comments as c on n.objectid == c.commentid - inner join comment_threads as t on c.threadid = t.threadid - left join comments as rc on c.replytoid == rc.commentid - inner join users as cu on cu.userid == c.userid - where (n.targetuserid = ?) and (n.objecttype = ?) - order by c.created desc + FROM notifications AS n + INNER JOIN comments AS c ON n.objectid == c.commentid + INNER JOIN comment_threads AS t USING (threadid) + LEFT JOIN comments AS rc ON c.replytoid == rc.commentid + INNER JOIN users AS cu USING (userid) + WHERE (n.targetuserid = ?) AND (n.objecttype = ?) + ORDER BY c.created DESC """, [session["userid"], comments.ObjectType.COMMENT]) @@ -41,21 +41,23 @@ def activity(): comment["title"] = song.title comment["content_userid"] = song.userid comment["content_username"] = song.username + elif threadtype == comments.ThreadType.PROFILE: profile = db.query( - "select * from users where threadid = ?", + "SELECT * FROM users WHERE threadid = ?", [comment["threadid"]], one=True) if profile is None: continue comment["content_userid"] = profile["userid"] comment["content_username"] = profile["username"] + elif threadtype == comments.ThreadType.PLAYLIST: playlist = db.query( """\ - select * from playlists - inner join users on playlists.userid == users.userid - where playlists.threadid = ? + SELECT * FROM playlists + INNER JOIN users USING (userid) + WHERE playlists.threadid = ? """, [comment["threadid"]], one=True, @@ -66,11 +68,12 @@ def activity(): comment["name"] = playlist["name"] comment["content_userid"] = playlist["userid"] comment["content_username"] = playlist["username"] + elif threadtype == comments.ThreadType.JAM_EVENT: jam_event = db.query( """\ SELECT * FROM jam_events - INNER JOIN jams ON jam_events.jamid = jams.jamid + INNER JOIN jams USING (jamid) INNER JOIN users ON jams.ownerid = users.userid WHERE jam_events.threadid = ? """, [comment["threadid"]], one=True) @@ -87,7 +90,7 @@ def activity(): timestamp = datetime.now(timezone.utc).isoformat() db.query( - "update users set activitytime = ? where userid = ?", + "UPDATE users SET activitytime = ? WHERE userid = ?", [timestamp, session["userid"]]) db.commit() @@ -98,15 +101,15 @@ def new_activity(): has_new_activity = False if "userid" in session: user_data = db.query( - "select activitytime from users where userid = ?", + "SELECT activitytime FROM users WHERE userid = ?", [session["userid"]], one=True) comment_data = db.query( """\ - select created from notifications - where targetuserid = ? - order by created desc - limit 1 + SELECT created FROM notifications + WHERE targetuserid = ? + ORDER BY created DESC + LIMIT 1 """, [session["userid"]], one=True) diff --git a/src/littlesongplace/comments.py b/src/littlesongplace/comments.py index 1a41367..dfe7b19 100644 --- a/src/littlesongplace/comments.py +++ b/src/littlesongplace/comments.py @@ -20,9 +20,9 @@ class ObjectType(enum.IntEnum): def create_thread(threadtype, userid): thread = db.query( """ - insert into comment_threads (threadtype, userid) - values (?, ?) - returning threadid + INSERT INTO comment_threads (threadtype, userid) + VALUES (?, ?) + RETURNING threadid """, [threadtype, userid], one=True) @@ -32,9 +32,9 @@ def create_thread(threadtype, userid): def for_thread(threadid): thread_comments = db.query( """ - select * from comments - inner join users on comments.userid == users.userid - where comments.threadid = ? + SELECT * FROM comments + INNER JOIN users USING (userid) + WHERE comments.threadid = ? """, [threadid]) thread_comments = [dict(c) for c in thread_comments] @@ -46,6 +46,7 @@ def for_thread(threadid): [dict(c) for c in thread_comments if c["replytoid"] is None], key=lambda c: c["created"]) song_comments = list(reversed(song_comments)) + # Replies (can only reply to top-level) for comment in song_comments: comment["replies"] = sorted( @@ -57,18 +58,13 @@ def for_thread(threadid): @bp.route("/comment", methods=["GET", "POST"]) @auth.requires_login def comment(): - if not "threadid" in request.args: - abort(400) # Must have threadid - thread = db.query( """ - select * from comment_threads - where threadid = ? + SELECT * FROM comment_threads + WHERE threadid = ? """, [request.args["threadid"]], - one=True) - if not thread: - abort(404) # Invalid threadid + expect_one=True) # Check for comment being replied to replyto = None @@ -76,14 +72,12 @@ def comment(): replytoid = request.args["replytoid"] replyto = db.query( """ - select * from comments - inner join users on comments.userid == users.userid - where commentid = ? + SELECT * FROM comments + INNER JOIN users USING (userid) + WHERE commentid = ? """, [replytoid], - one=True) - if not replyto: - abort(404) # Invalid comment + expect_one=True) # Check for comment being edited comment = None @@ -91,14 +85,13 @@ def comment(): commentid = request.args["commentid"] comment = db.query( """ - select * from comments - inner join users on comments.userid == users.userid - where commentid = ? + SELECT * FROM comments + INNER JOIN users USING (userid) + WHERE commentid = ? """, [commentid], - one=True) - if not comment: - abort(404) # Invalid comment + expect_one=True) + if comment["userid"] != session["userid"]: abort(403) # User doesn't own this comment @@ -109,22 +102,26 @@ def comment(): song = None profile = None playlist = None + if threadtype == ThreadType.SONG: song = songs.by_threadid(request.args["threadid"]) + elif threadtype == ThreadType.PROFILE: profile = db.query( - "select * from users where threadid = ?", + "SELECT * FROM users WHERE threadid = ?", [request.args["threadid"]], one=True) + elif threadtype == ThreadType.PLAYLIST: - profile = db.query( + playlist = db.query( """ - select * from playlists - inner join users on playlists.userid = users.userid - where playlists.threadid = ? + SELECT * FROM playlists + INNER JOIN users USING (userid) + WHERE playlists.threadid = ? """, [request.args["threadid"]], one=True) + return render_template( "comment.html", song=song, @@ -140,7 +137,7 @@ def comment(): if comment: # Update existing comment db.query( - "update comments set content = ? where commentid = ?", + "UPDATE comments SET content = ? WHERE commentid = ?", args=[content, comment["commentid"]]) else: # Add new comment @@ -151,10 +148,10 @@ def comment(): threadid = request.args["threadid"] comment = db.query( """ - insert into comments + INSERT INTO comments (threadid, userid, replytoid, created, content) - values (?, ?, ?, ?, ?) - returning (commentid) + VALUES (?, ?, ?, ?, ?) + RETURNING (commentid) """, args=[threadid, userid, replytoid, timestamp, content], one=True) @@ -168,7 +165,7 @@ def comment(): # Notify previous repliers in thread previous_replies = db.query( - "select * from comments where replytoid = ?", [replytoid]) + "SELECT * FROM comments WHERE replytoid = ?", [replytoid]) for reply in previous_replies: notification_targets.add(reply["userid"]) @@ -180,9 +177,9 @@ def comment(): for target in notification_targets: db.query( """ - insert into notifications + INSERT INTO notifications (objectid, objecttype, targetuserid, created) - values (?, ?, ?, ?) + VALUES (?, ?, ?, ?) """, [commentid, ObjectType.COMMENT, target, timestamp]) @@ -212,16 +209,13 @@ def comment_delete(commentid): comment = db.query( """ - select c.userid as comment_user, t.userid as thread_user - from comments as c - inner join comment_threads as t - on c.threadid == t.threadid - where commentid = ? + SELECT c.userid AS comment_user, t.userid AS thread_user + FROM comments AS c + INNER JOIN comment_threads AS t USING (threadid) + WHERE commentid = ? """, [commentid], - one=True) - if not comment: - abort(404) # Invalid comment + expect_one=True) # Only commenter and song owner can delete comments if not ((comment["comment_user"] == session["userid"]) @@ -229,7 +223,7 @@ def comment_delete(commentid): abort(403) db.query( - "delete from comments where (commentid = ?) or (replytoid = ?)", + "DELETE FROM comments WHERE (commentid = ?) OR (replytoid = ?)", [commentid, commentid]) db.commit() -- 2.39.5