From 29fcedfa8d21c10dcb27fafcf0752d6bcce77104 Mon Sep 17 00:00:00 2001 From: Chris Fulljames Date: Sat, 26 Jul 2025 08:00:16 -0400 Subject: [PATCH] Refactor song SQL queries to use a view --- src/littlesongplace/db.py | 2 +- src/littlesongplace/songs.py | 168 ++++++++-------------- src/littlesongplace/sql/schema.sql | 59 ++++---- src/littlesongplace/sql/schema_revert.sql | 8 +- src/littlesongplace/sql/schema_update.sql | 46 +++--- 5 files changed, 108 insertions(+), 175 deletions(-) diff --git a/src/littlesongplace/db.py b/src/littlesongplace/db.py index ec1ebad..0fd94c9 100644 --- a/src/littlesongplace/db.py +++ b/src/littlesongplace/db.py @@ -6,7 +6,7 @@ from flask import abort, g, current_app from . import datadir -DB_VERSION = 5 +DB_VERSION = 6 def get(): db = getattr(g, '_database', None) diff --git a/src/littlesongplace/songs.py b/src/littlesongplace/songs.py index be04b46..82f5310 100644 --- a/src/littlesongplace/songs.py +++ b/src/littlesongplace/songs.py @@ -46,26 +46,14 @@ class Song: return comments.for_thread(self.threadid) def by_id(songid): - songs = _from_db( - """ - select * from songs - inner join users on songs.userid = users.userid - where songid = ? - """, - [songid]) + songs = _from_db("SELECT * FROM songs_view WHERE songid = ?", [songid]) if not songs: raise ValueError(f"No song for ID {songid:d}") return songs[0] def by_threadid(threadid): - songs = _from_db( - """ - select * from songs - inner join users on songs.userid = users.userid - where songs.threadid = ? - """, - [threadid]) + songs = _from_db("SELECT * FROM songs_view WHERE threadid = ?", [threadid]) if not songs: raise ValueError(f"No song for Thread ID {songid:d}") @@ -74,131 +62,89 @@ def by_threadid(threadid): def get_all_for_userid(userid): return _from_db( """ - select * from songs - inner join users on songs.userid = users.userid - where songs.userid = ? - order by songs.created desc - """, - [userid]) + SELECT * FROM songs_view + WHERE userid = ? + ORDER BY created DESC + """, [userid]) def get_all_for_username(username): return _from_db( """ - select * from songs - inner join users on songs.userid = users.userid - where users.username = ? - order by songs.created desc - """, - [username]) + SELECT * FROM songs_view + WHERE username = ? + ORDER BY created DESC + """, [username]) def get_all_for_username_and_tag(username, tag): return _from_db( """ - select * from song_tags - inner join songs on song_tags.songid = songs.songid - inner join users on songs.userid = users.userid - where (username = ? and tag = ?) - order by songs.created desc + SELECT * FROM song_tags + INNER JOIN songs_view on song_tags.songid = songs_view.songid + WHERE (username = ? and tag = ?) + ORDER BY created DESC """, [username, tag]) def get_all_for_tag(tag): return _from_db( """ - select * from song_tags - inner join songs on song_tags.songid = songs.songid - inner join users on songs.userid = users.userid - where (tag = ?) - order by songs.created desc + SELECT * FROM song_tags + INNER JOIN songs_view on song_tags.songid = songs_view.songid + WHERE tag = ? + ORDER BY created DESC """, [tag]) def get_latest(count): return _from_db( """ - select * from songs - inner join users on songs.userid = users.userid - order by songs.created desc - limit ? + SELECT * FROM songs_view + ORDER BY created DESC + LIMIT ? """, [count]) def get_random(count): - # Get random songs + 10 extras so I can filter out my own - # (I uploaded too many :/) songs = _from_db( """ - select * from songs - inner join users on songs.userid = users.userid - where songid in ( - select songid from songs - order by random() - limit ? + SELECT * FROM songs_view + WHERE songid IN ( + SELECT songid FROM songs + ORDER BY random() + LIMIT ? ) """, - [count + 10]) - random.shuffle(songs) - - # Prevent my songs from showing up in the first 10 results - for i in reversed(range(min(10, len(songs)))): - if songs[i].username == "cfulljames": - del songs[i] - - # Drop any extra songs (since we asked for 10 extras) - songs = songs[:count] + [count]) + random.shuffle(songs) return songs def get_for_playlist(playlistid): return _from_db( """ - select * from playlist_songs - inner join songs on playlist_songs.songid = songs.songid - inner join users on songs.userid = users.userid - where playlistid = ? - order by playlist_songs.position asc + SELECT * FROM playlist_songs + INNER JOIN songs_view ON playlist_songs.songid = songs_view.songid + WHERE playlistid = ? + ORDER BY playlist_songs.position ASC """, [playlistid]) def get_for_event(eventid): - return _from_db( - """ - SELECT * FROM songs - INNER JOIN users ON songs.userid = users.userid - WHERE songs.eventid = ? - """, - [eventid]) - -def _get_song_info_list(table, column, songid): - rows = db.query( - f"SELECT ({column}) FROM {table} WHERE songid = ?", [songid]) - return [r[column] for r in rows if r[column]] + return _from_db("SELECT * FROM songs_view WHERE eventid = ?", [eventid]) def _from_db(query, args=()): songs_data = db.query(query, args) songs = [] for sd in songs_data: songid = sd["songid"] - song_tags = _get_song_info_list("song_tags", "tag", songid) - song_collabs = _get_song_info_list("song_collaborators", "name", songid) + song_tags = sd["tags"].split(",") if sd["tags"] else [] + song_collabs = sd["collaborators"].split(",") if sd["collaborators"] else [] # Song is hidden if it was submitted to an event that hasn't ended yet hidden = False - jamid = None - event_title = None - - if sd["eventid"]: - event_row = db.query( - "SELECT * FROM jam_events WHERE eventid = ?", - [sd["eventid"]], - one=True) - if event_row: - jamid = event_row["jamid"] - event_title = event_row["title"] - if event_row["enddate"]: - enddate = datetime.fromisoformat(event_row["enddate"]) - hidden = datetime.now(timezone.utc) < enddate - + if sd["event_enddate"]: + enddate = datetime.fromisoformat(sd["event_enddate"]) + hidden = datetime.now(timezone.utc) < enddate created = ( datetime.fromisoformat(sd["created"]) @@ -218,8 +164,8 @@ def _from_db(query, args=()): user_has_pfp=users.user_has_pfp(sd["userid"]), hidden=hidden, eventid=sd["eventid"], - jamid=jamid, - event_title=event_title, + jamid=sd["jamid"], + event_title=sd["event_title"], )) return songs @@ -351,7 +297,7 @@ def update_song(): collaborators = [c.strip() for c in request.form["collabs"].split(",") if c] # Make sure song exists and the logged-in user owns it - song_data = db.query("select * from songs where songid = ?", [songid], one=True) + song_data = db.query("SELECT * FROM songs WHERE songid = ?", [songid], one=True) if song_data is None: abort(400) elif session["userid"] != song_data["userid"]: @@ -374,8 +320,8 @@ def update_song(): # Update songs table db.query( """ - update songs set title = ?, description = ? - where songid = ? + UPDATE songs SET title = ?, description = ? + WHERE songid = ? """, [title, description, songid]) @@ -384,18 +330,18 @@ def update_song(): for tag in tags: db.query( """ - insert into song_tags (tag, songid) - values (?, ?) + INSERT INTO song_tags (tag, songid) + VALUES (?, ?) """, [tag, songid]) # Update song_collaborators table - db.query("delete from song_collaborators where songid = ?", [songid]) + db.query("DELETE FROM song_collaborators WHERE songid = ?", [songid]) for collab in collaborators: db.query( """ - insert into song_collaborators (name, songid) - values (?, ?) + INSERT INTO song_collaborators (name, songid) + VALUES (?, ?) """, [collab, songid]) @@ -430,9 +376,9 @@ def create_song(): timestamp = datetime.now(timezone.utc).isoformat() song_data = db.query( """ - insert into songs (userid, title, description, created, threadid, eventid) - values (?, ?, ?, ?, ?, ?) - returning (songid) + INSERT INTO songs (userid, title, description, created, threadid, eventid) + VALUES (?, ?, ?, ?, ?, ?) + RETURNING (songid) """, [session["userid"], title, description, timestamp, threadid, eventid], one=True) @@ -446,13 +392,13 @@ def create_song(): songid = song_data["songid"] for tag in tags: db.query( - "insert into song_tags (tag, songid) values (?, ?)", + "INSERT INTO song_tags (tag, songid) VALUES (?, ?)", [tag, songid]) # Assign collaborators for collab in collaborators: db.query( - "insert into song_collaborators (songid, name) values (?, ?)", + "INSERT INTO song_collaborators (songid, name) VALUES (?, ?)", [songid, collab]) db.commit() @@ -515,7 +461,7 @@ def yt_import(tmp_file, yt_url): @bp.get("/delete-song/") def delete_song(songid): song_data = db.query( - "select * from songs where songid = ?", [songid], one=True) + "SELECT * FROM songs WHERE songid = ?", [songid], one=True) if not song_data: current_app.logger.warning( @@ -529,11 +475,11 @@ def delete_song(songid): abort(401) # Delete tags, collaborators - db.query("delete from song_tags where songid = ?", [songid]) - db.query("delete from song_collaborators where songid = ?", [songid]) + db.query("DELETE FROM song_tags WHERE songid = ?", [songid]) + db.query("DELETE FROM song_collaborators WHERE songid = ?", [songid]) # Delete song database entry - db.query("delete from songs where songid = ?", [songid]) + db.query("DELETE FROM songs WHERE songid = ?", [songid]) db.commit() # Delete song file from disk diff --git a/src/littlesongplace/sql/schema.sql b/src/littlesongplace/sql/schema.sql index 51057b6..4ea3bf1 100644 --- a/src/littlesongplace/sql/schema.sql +++ b/src/littlesongplace/sql/schema.sql @@ -21,9 +21,12 @@ CREATE TABLE songs ( title TEXT NOT NULL, description TEXT, threadid INTEGER, - FOREIGN KEY(userid) REFERENCES users(userid) + eventid INTEGER, + FOREIGN KEY(userid) REFERENCES users(userid), + FOREIGN KEY(eventid) REFERENCES jam_events(eventid) ); CREATE INDEX idx_songs_by_user ON songs(userid); +CREATE INDEX idx_songs_by_eventid ON songs(eventid); DROP TABLE IF EXISTS song_collaborators; CREATE TABLE song_collaborators ( @@ -42,34 +45,6 @@ CREATE TABLE song_tags ( ); CREATE INDEX idx_song_tags_tag ON song_tags(tag); --- Old comment system (superceded by comments/comment_threads/comment_notifications --- --- DROP TABLE IF EXISTS song_comments; --- CREATE TABLE song_comments ( --- commentid INTEGER PRIMARY KEY, --- songid INTEGER NOT NULL, --- userid INTEGER NOT NULL, --- replytoid INTEGER, --- created TEXT NOT NULL, --- content TEXT NOT NULL, --- FOREIGN KEY(songid) REFERENCES songs(songid) ON DELETE CASCADE, --- FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE --- ); --- CREATE INDEX idx_comments_by_song ON song_comments(songid); --- CREATE INDEX idx_comments_by_user ON song_comments(userid); --- CREATE INDEX idx_comments_by_replyto ON song_comments(replytoid); --- CREATE INDEX idx_comments_by_time ON song_comments(created); --- --- DROP TABLE IF EXISTS song_comment_notifications; --- CREATE TABLE song_comment_notifications ( --- notificationid INTEGER PRIMARY KEY, --- commentid INTEGER NOT NULL, --- targetuserid INTEGER NOT NULL, --- FOREIGN KEY(commentid) REFERENCES song_comments(commentid) ON DELETE CASCADE, --- FOREIGN KEY(targetuserid) REFERENCES users(userid) ON DELETE CASCADE --- ); --- CREATE INDEX idx_song_comment_notifications_by_target ON song_comment_notifications(targetuserid); - DROP TABLE IF EXISTS playlists; CREATE TABLE playlists ( playlistid INTEGER PRIMARY KEY, @@ -159,5 +134,29 @@ BEGIN DELETE FROM notifications WHERE objectid = OLD.commentid AND objecttype = 0; END; -PRAGMA user_version = 4; +DROP TABLE IF EXISTS jams; +CREATE TABLE jams ( + jamid INTEGER PRIMARY KEY, + ownerid INTEGER NOT NULL, + created TEXT NOT NULL, + title TEXT NOT NULL, + description TEXT, + FOREIGN KEY(ownerid) REFERENCES users(userid) +); + +DROP TABLE IF EXISTS jam_events; +CREATE TABLE jam_events( + eventid INTEGER PRIMARY KEY, + jamid INTEGER NOT NULL, + threadid INTEGER NOT NULL, + created TEXT NOT NULL, + title TEXT NOT NULL, -- Hidden until startdate + startdate TEXT, + enddate TEXT, + description TEXT, -- Hidden until startdate + FOREIGN KEY(jamid) REFERENCES jams(jamid), + FOREIGN KEY(threadid) REFERENCES comment_threads(threadid) +); + +PRAGMA user_version = 5; diff --git a/src/littlesongplace/sql/schema_revert.sql b/src/littlesongplace/sql/schema_revert.sql index a6cd883..408ad84 100644 --- a/src/littlesongplace/sql/schema_revert.sql +++ b/src/littlesongplace/sql/schema_revert.sql @@ -1,7 +1,3 @@ -DROP INDEX idx_songs_by_eventid; -ALTER TABLE songs DROP COLUMN eventid; -DROP TABLE IF EXISTS jams; -DROP TABLE IF EXISTS jam_events; - -PRAGMA user_version = 4; +DROP VIEW songs_view; +PRAGMA user_version = 5; diff --git a/src/littlesongplace/sql/schema_update.sql b/src/littlesongplace/sql/schema_update.sql index ffb9b60..b39b3fe 100644 --- a/src/littlesongplace/sql/schema_update.sql +++ b/src/littlesongplace/sql/schema_update.sql @@ -1,29 +1,21 @@ ---DROP TABLE IF EXISTS jams; -CREATE TABLE jams ( - jamid INTEGER PRIMARY KEY, - ownerid INTEGER NOT NULL, - created TEXT NOT NULL, - title TEXT NOT NULL, - description TEXT, - FOREIGN KEY(ownerid) REFERENCES users(userid) -); +CREATE VIEW songs_view AS + SELECT + songs.*, + users.username, + users.fgcolor, + users.bgcolor, + users.accolor, + jam_events.title AS event_title, + jam_events.jamid AS jamid, + jam_events.enddate AS event_enddate, + group_concat(song_tags.tag) AS tags, + group_concat(song_collaborators.name) AS collaborators + FROM songs + INNER JOIN users ON songs.userid = users.userid + LEFT JOIN song_tags ON song_tags.songid = songs.songid + LEFT JOIN song_collaborators ON song_collaborators.songid = songs.songid + LEFT JOIN jam_events ON jam_events.eventid = songs.eventid + GROUP BY songs.songid; ---DROP TABLE IF EXISTS jam_events; -CREATE TABLE jam_events( - eventid INTEGER PRIMARY KEY, - jamid INTEGER NOT NULL, - threadid INTEGER NOT NULL, - created TEXT NOT NULL, - title TEXT NOT NULL, -- Hidden until startdate - startdate TEXT, - enddate TEXT, - description TEXT, -- Hidden until startdate - FOREIGN KEY(jamid) REFERENCES jams(jamid), - FOREIGN KEY(threadid) REFERENCES comment_threads(threadid) -); - -ALTER TABLE songs ADD COLUMN eventid INTEGER REFERENCES jam_events(eventid); -CREATE INDEX idx_songs_by_eventid ON songs(eventid); - -PRAGMA user_version = 5; +PRAGMA user_version = 6; -- 2.39.5