From 0aa4f23b2a59ede64139c466418301d6934697e1 Mon Sep 17 00:00:00 2001 From: Chris Fulljames Date: Sun, 30 Mar 2025 08:19:27 -0400 Subject: [PATCH] Remove DB schema update logic now that update is applied --- main.py | 42 ---------------- schema.sql | 124 +++++++++++++++++++++++++++++++++++----------- schema_revert.sql | 10 ---- schema_update.sql | 67 ------------------------- 4 files changed, 96 insertions(+), 147 deletions(-) diff --git a/main.py b/main.py index b154ccc..4c81b24 100644 --- a/main.py +++ b/main.py @@ -1079,48 +1079,6 @@ def get_db(): with app.open_resource(schema_update_script, mode='r') as f: db.cursor().executescript(f.read()) db.commit() - - if DB_VERSION == 4: - # TODO: Remove after deploying - assign_thread_ids(db, "songs", "songid", threadtype=ThreadType.SONG) - assign_thread_ids(db, "users", "userid", threadtype=ThreadType.PROFILE) - assign_thread_ids(db, "playlists", "playlistid", threadtype=ThreadType.PLAYLIST) - - # Copy song comments to new comments table - cur = db.execute( - """\ - select commentid, threadid, sc.userid, replytoid, sc.created, content - from song_comments as sc - inner join songs on sc.songid = songs.songid - """) - for row in cur: - comment_cur = db.execute( - """\ - insert into comments (commentid, threadid, userid, replytoid, created, content) - values (?, ?, ?, ?, ?, ?) - """, - [row["commentid"], row["threadid"], row["userid"], row["replytoid"], row["created"], row["content"]]) - comment_cur.close() - cur.close() - - # Copy song comment notifications to new notifications table - cur = db.execute("""\ - select * from song_comment_notifications as scn - inner join song_comments as sc on - scn.commentid = sc.commentid - """) - for row in cur: - db.execute( - """\ - insert into notifications (notificationid, objectid, objecttype, targetuserid, created) - values (?, ?, ?, ?, ?) - """, - [row["notificationid"], row["commentid"], ObjectType.COMMENT, row["targetuserid"], row["created"]]) - - db.execute("PRAGMA user_version = 4").close() - - db.commit() - return db # TODO: Remove after deploying diff --git a/schema.sql b/schema.sql index b1aa9c8..5d09331 100644 --- a/schema.sql +++ b/schema.sql @@ -8,7 +8,8 @@ CREATE TABLE users ( activitytime TEXT, bgcolor TEXT, fgcolor TEXT, - accolor TEXT + accolor TEXT, + threadid INTEGER, ); CREATE INDEX users_by_name ON users(username); @@ -19,6 +20,7 @@ CREATE TABLE songs ( userid INTEGER NOT NULL, title TEXT NOT NULL, description TEXT, + threadid INTEGER, FOREIGN KEY(userid) REFERENCES users(userid) ); CREATE INDEX idx_songs_by_user ON songs(userid); @@ -39,32 +41,34 @@ CREATE TABLE song_tags ( PRIMARY KEY(songid, tag) ); CREATE INDEX idx_song_tags_tag ON song_tags(tag); - -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); + +-- 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 ( @@ -74,6 +78,7 @@ CREATE TABLE playlists ( userid INTEGER NOT NULL, name TEXT NOT NULL, private INTEGER NOT NULL, + threadid INTEGER, FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE ); @@ -91,5 +96,68 @@ CREATE TABLE playlist_songs ( ); CREATE INDEX playlist_songs_by_playlist ON playlist_songs(playlistid); -PRAGMA user_version = 3; +DROP TABLE IF EXISTS comment_threads; +CREATE TABLE comment_threads ( + threadid INTEGER PRIMARY KEY, + threadtype INTEGER NOT NULL, + userid INTEGER NOT NULL, + FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE +); + +-- Delete comment thread when song deleted +CREATE TRIGGER trg_delete_song_comments +BEFORE DELETE ON songs FOR EACH ROW +BEGIN + DELETE FROM comment_threads WHERE threadid = OLD.threadid; +END; + +-- Delete comment thread when profile deleted +CREATE TRIGGER trg_delete_profile_comments +BEFORE DELETE ON users FOR EACH ROW +BEGIN + DELETE FROM comment_threads WHERE threadid = OLD.threadid; +END; + +-- Delete comment thread when playlist deleted +CREATE TRIGGER trg_delete_playlist_comments +BEFORE DELETE ON playlists FOR EACH ROW +BEGIN + DELETE FROM comment_threads WHERE threadid = OLD.threadid; +END; + +DROP TABLE IF EXISTS comments; +CREATE TABLE comments ( + commentid INTEGER PRIMARY KEY, + threadid INTEGER NOT NULL, + userid INTEGER NOT NULL, + replytoid INTEGER, + created TEXT NOT NULL, + content TEXT NOT NULL, + FOREIGN KEY(threadid) REFERENCES comment_threads(threadid) ON DELETE CASCADE, + FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE +); +CREATE INDEX idx_comments_user ON comments(userid); +CREATE INDEX idx_comments_replyto ON comments(replytoid); +CREATE INDEX idx_comments_time ON comments(created); + +DROP TABLE IF EXISTS notifications; +CREATE TABLE notifications ( + notificationid INTEGER PRIMARY KEY, + objectid INTEGER NOT NULL, + objecttype INTEGER NOT NULL, + targetuserid INTEGER NOT NULL, + created TEXT NOT NULL, + FOREIGN KEY(targetuserid) REFERENCES users(userid) ON DELETE CASCADE +); +CREATE INDEX idx_notifications_by_target ON notifications(targetuserid); +CREATE INDEX idx_notifications_by_object ON notifications(objectid); + +-- Delete comment notifications when comment deleted +CREATE TRIGGER trg_delete_notifications +BEFORE DELETE ON comments FOR EACH ROW +BEGIN + DELETE FROM notifications WHERE objectid = OLD.commentid AND objecttype = 0; +END; + +PRAGMA user_version = 4; diff --git a/schema_revert.sql b/schema_revert.sql index 3670241..e69de29 100644 --- a/schema_revert.sql +++ b/schema_revert.sql @@ -1,10 +0,0 @@ -DROP TRIGGER trg_delete_song_comments; -DROP TRIGGER trg_delete_profile_comments; -DROP TRIGGER trg_delete_playlist_comments; -ALTER TABLE songs DROP COLUMN threadid; -ALTER TABLE users DROP COLUMN threadid; -ALTER TABLE playlists DROP COLUMN threadid; -DROP TABLE notifications; -DROP TABLE comments; -DROP TABLE comment_threads; -PRAGMA user_version = 3; diff --git a/schema_update.sql b/schema_update.sql index cc9a48a..139597f 100644 --- a/schema_update.sql +++ b/schema_update.sql @@ -1,69 +1,2 @@ --- Add comment thread to songs table -ALTER TABLE songs ADD COLUMN threadid INTEGER; --- Add profile comment thread to users table -ALTER TABLE users ADD COLUMN threadid INTEGER; - --- Add playlist comment thread to playlists table -ALTER TABLE playlists ADD COLUMN threadid INTEGER; - -CREATE TABLE comment_threads ( - threadid INTEGER PRIMARY KEY, - threadtype INTEGER NOT NULL, - userid INTEGER NOT NULL, - FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE -); - --- Delete comment thread when song deleted -CREATE TRIGGER trg_delete_song_comments -BEFORE DELETE ON songs FOR EACH ROW -BEGIN - DELETE FROM comment_threads WHERE threadid = OLD.threadid; -END; - --- Delete comment thread when profile deleted -CREATE TRIGGER trg_delete_profile_comments -BEFORE DELETE ON users FOR EACH ROW -BEGIN - DELETE FROM comment_threads WHERE threadid = OLD.threadid; -END; - --- Delete comment thread when playlist deleted -CREATE TRIGGER trg_delete_playlist_comments -BEFORE DELETE ON playlists FOR EACH ROW -BEGIN - DELETE FROM comment_threads WHERE threadid = OLD.threadid; -END; - -CREATE TABLE comments ( - commentid INTEGER PRIMARY KEY, - threadid INTEGER NOT NULL, - userid INTEGER NOT NULL, - replytoid INTEGER, - created TEXT NOT NULL, - content TEXT NOT NULL, - FOREIGN KEY(threadid) REFERENCES comment_threads(threadid) ON DELETE CASCADE, - FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE -); -CREATE INDEX idx_comments_user ON comments(userid); -CREATE INDEX idx_comments_replyto ON comments(replytoid); -CREATE INDEX idx_comments_time ON comments(created); - -CREATE TABLE notifications ( - notificationid INTEGER PRIMARY KEY, - objectid INTEGER NOT NULL, - objecttype INTEGER NOT NULL, - targetuserid INTEGER NOT NULL, - created TEXT NOT NULL, - FOREIGN KEY(targetuserid) REFERENCES users(userid) ON DELETE CASCADE -); -CREATE INDEX idx_notifications_by_target ON notifications(targetuserid); -CREATE INDEX idx_notifications_by_object ON notifications(objectid); - --- Delete comment notifications when comment deleted -CREATE TRIGGER trg_delete_notifications -BEFORE DELETE ON comments FOR EACH ROW -BEGIN - DELETE FROM notifications WHERE objectid = OLD.commentid AND objecttype = 0; -END; -- 2.39.5