From 7103baaa849e7b1864f1b7b04234ec2ead448aa7 Mon Sep 17 00:00:00 2001 From: Chris Fulljames Date: Fri, 31 Jan 2025 20:56:39 -0500 Subject: [PATCH] Activity indicator, db version, colors --- main.py | 38 +++++++++++++++++++++++++++++++++++++- schema_update.sql | 19 ++++++++++++------- static/styles.css | 12 ++++++++++-- templates/base.html | 21 ++++++++++++++++++++- test/test_offline.py | 37 +++++++++++++++++++++++++++++++++++++ 5 files changed, 116 insertions(+), 11 deletions(-) diff --git a/main.py b/main.py index 5b296a8..57774fa 100644 --- a/main.py +++ b/main.py @@ -22,6 +22,7 @@ from flask import Flask, render_template, request, redirect, g, session, abort, from werkzeug.utils import secure_filename from werkzeug.middleware.proxy_fix import ProxyFix +DB_VERSION = 1 DATA_DIR = Path(os.environ["DATA_DIR"]) if "DATA_DIR" in os.environ else Path(".") SCRIPT_DIR = Path(__file__).parent @@ -121,6 +122,7 @@ def login_post(): session["userid"] = user_data["userid"] session.permanent = True app.logger.info(f"{username} logged in") + return redirect(f"/users/{username}") flash("Invalid username/password", "error") @@ -128,6 +130,7 @@ def login_post(): return render_template("login.html") + @app.get("/logout") def logout(): if "username" in session: @@ -599,8 +602,36 @@ def activity(): """, [session["userid"]]) + timestamp = datetime.now(timezone.utc).isoformat() + query_db("update users set activitytime = ? where userid = ?", [timestamp, session["userid"]]) + get_db().commit() + return render_template("activity.html", comments=comments) +@app.get("/new-activity") +def new_activity(): + has_new_activity = False + if "userid" in session: + user_data = query_db("select activitytime from users where userid = ?", [session["userid"]], one=True) + comment_data = query_db( + """\ + select sc.created from song_comment_notifications as scn + inner join song_comments as sc on scn.commentid = sc.commentid + where scn.targetuserid = ? + order by sc.created desc + limit 1""", + [session["userid"]], + one=True) + + if comment_data: + comment_time = comment_data["created"] + last_checked = user_data["activitytime"] + + if (last_checked is None) or (last_checked < comment_time): + has_new_activity = True + + return {"new_activity": has_new_activity} + @app.get("/site-news") def site_news(): return render_template("news.html") @@ -623,8 +654,13 @@ def get_db(): db = getattr(g, '_database', None) if db is None: db = g._database = sqlite3.connect(DATA_DIR / "database.db") + + # Get current version + user_version = query_db("pragma user_version", one=True)[0] + + # Run update script if DB is out of date schema_update_script = SCRIPT_DIR / 'schema_update.sql' - if schema_update_script.exists(): + if user_version < DB_VERSION and schema_update_script.exists(): with app.open_resource(schema_update_script, mode='r') as f: db.cursor().executescript(f.read()) db.commit() diff --git a/schema_update.sql b/schema_update.sql index 3e0a353..1fbf6b9 100644 --- a/schema_update.sql +++ b/schema_update.sql @@ -1,6 +1,8 @@ -CREATE INDEX IF NOT EXISTS idx_songs_by_user ON songs(userid); +ALTER TABLE users ADD COLUMN activitytime TEXT; -CREATE TABLE IF NOT EXISTS song_comments ( +CREATE INDEX idx_songs_by_user ON songs(userid); + +CREATE TABLE song_comments ( commentid INTEGER PRIMARY KEY, songid INTEGER NOT NULL, userid INTEGER NOT NULL, @@ -10,16 +12,19 @@ CREATE TABLE IF NOT EXISTS song_comments ( FOREIGN KEY(songid) REFERENCES songs(songid) ON DELETE CASCADE, FOREIGN KEY(userid) REFERENCES users(userid) ON DELETE CASCADE ); -CREATE INDEX IF NOT EXISTS idx_comments_by_song ON song_comments(songid); -CREATE INDEX IF NOT EXISTS idx_comments_by_user ON song_comments(userid); -CREATE INDEX IF NOT EXISTS idx_comments_by_replyto ON song_comments(replytoid); +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); -CREATE TABLE IF NOT 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 IF NOT EXISTS idx_song_comment_notifications_by_target ON song_comment_notifications(targetuserid); +CREATE INDEX idx_song_comment_notifications_by_target ON song_comment_notifications(targetuserid); + +PRAGMA user_version = 1; diff --git a/static/styles.css b/static/styles.css index 2cf1fef..37c0b5f 100644 --- a/static/styles.css +++ b/static/styles.css @@ -2,7 +2,7 @@ :root { --yellow: #e8e6b5; --purple: #9373a9; - --blue: #8dcbc2; + --blue: #44b7b7; --black: #695c73; } @@ -134,7 +134,15 @@ div.navbar { justify-content: center; align-items: center; gap: 10px; - padding: 10px; + padding-bottom: 10px; +} + +#activity-indicator { + width: 8px; + height: 8px; + border-radius: 4px; + background: var(--blue); + display: inline-block; } /* Upload/Edit Form */ diff --git a/templates/base.html b/templates/base.html index fc2aa03..33c51ad 100644 --- a/templates/base.html +++ b/templates/base.html @@ -22,7 +22,7 @@ News {% if "username" in session %} My Profile - Activity + Activity Sign Out {% else %} Create Account @@ -35,6 +35,25 @@ + {% if "username" in session %} + + + {% endif %} + {% with messages = get_flashed_messages(with_categories=True) %} {% if messages %} diff --git a/test/test_offline.py b/test/test_offline.py index 1833f57..097a588 100644 --- a/test/test_offline.py +++ b/test/test_offline.py @@ -817,3 +817,40 @@ def test_activity_deleted_when_comment_deleted(client): response = client.get("/activity") assert b"hey cool song" not in response.data +################################################################################ +# New Activity Status +################################################################################ + +def test_no_new_activity_when_not_logged_in(client): + response = client.get("/new-activity") + assert response.status_code == 200 + assert not response.json["new_activity"] + +def test_no_new_activity_when_no_activity(client): + _create_user_and_song(client) + response = client.get("/new-activity") + assert response.status_code == 200 + assert not response.json["new_activity"] + +def test_new_activity_after_comment(client): + _create_user_and_song(client) + _create_user(client, "user2", login=True) + client.post("/comment?songid=1", data={"content": "hey cool song"}) + + client.post("/login", data={"username": "user", "password": "password"}) + response = client.get("/new-activity") + assert response.status_code == 200 + assert response.json["new_activity"] + +def test_no_new_activity_after_checking(client): + _create_user_and_song(client) + _create_user(client, "user2", login=True) + client.post("/comment?songid=1", data={"content": "hey cool song"}) + + client.post("/login", data={"username": "user", "password": "password"}) + client.get("/activity") # Check activity page + + response = client.get("/new-activity") + assert response.status_code == 200 + assert not response.json["new_activity"] + -- 2.39.5