File size: 10,508 Bytes
8c9a0f8 f2da7fe 8c9a0f8 f2da7fe 8c9a0f8 | 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 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 | """SQLite database module for music memories app."""
import sqlite3
from contextlib import contextmanager
from datetime import datetime
from typing import Optional
DATABASE_PATH = "./music_memories.db"
@contextmanager
def get_db_connection():
"""Get a database connection with row factory."""
conn = sqlite3.connect(DATABASE_PATH)
conn.row_factory = sqlite3.Row
try:
yield conn
finally:
conn.close()
def init_database() -> None:
"""Initialize the SQLite database with all tables."""
with get_db_connection() as conn:
# Songs table
conn.execute("""
CREATE TABLE IF NOT EXISTS songs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
artist TEXT NOT NULL,
album TEXT,
duration INTEGER,
bpm INTEGER,
energy_level INTEGER CHECK(energy_level BETWEEN 1 AND 10),
audio_file_path TEXT,
file_size INTEGER
)
""")
# Users table
conn.execute("""
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
# Playlists table
conn.execute("""
CREATE TABLE IF NOT EXISTS playlists (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
vibe_code TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)
""")
# Contexts table
conn.execute("""
CREATE TABLE IF NOT EXISTS contexts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
weather TEXT,
time_of_day TEXT,
location_type TEXT
)
""")
# Memories table
conn.execute("""
CREATE TABLE IF NOT EXISTS memories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
description TEXT NOT NULL,
date TEXT,
song_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (song_id) REFERENCES songs(id)
)
""")
# Play history table (with composite primary key)
conn.execute("""
CREATE TABLE IF NOT EXISTS play_history (
user_id INTEGER NOT NULL,
song_id INTEGER NOT NULL,
played_at TEXT DEFAULT CURRENT_TIMESTAMP,
context_id INTEGER,
PRIMARY KEY (user_id, song_id, played_at),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (song_id) REFERENCES songs(id),
FOREIGN KEY (context_id) REFERENCES contexts(id)
)
""")
conn.commit()
# ============== SONGS ==============
def add_song(title: str, artist: str, album: str = None, duration: int = None,
bpm: int = None, energy_level: int = None) -> dict:
"""Add a new song."""
with get_db_connection() as conn:
cursor = conn.execute(
"""INSERT INTO songs (title, artist, album, duration, bpm, energy_level)
VALUES (?, ?, ?, ?, ?, ?)""",
(title, artist, album, duration, bpm, energy_level)
)
conn.commit()
song_id = cursor.lastrowid
return {"id": song_id, "title": title, "artist": artist, "album": album,
"duration": duration, "bpm": bpm, "energy_level": energy_level}
def get_all_songs() -> list[dict]:
"""Get all songs."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM songs")
return [dict(row) for row in cursor.fetchall()]
def get_song_by_id(song_id: int) -> Optional[dict]:
"""Get a song by ID."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM songs WHERE id = ?", (song_id,))
row = cursor.fetchone()
return dict(row) if row else None
def delete_song(song_id: int) -> bool:
"""Delete a song."""
with get_db_connection() as conn:
cursor = conn.execute("DELETE FROM songs WHERE id = ?", (song_id,))
conn.commit()
return cursor.rowcount > 0
def update_song_file(song_id: int, audio_file_path: str, file_size: int) -> bool:
"""Update song with MinIO file path and size."""
with get_db_connection() as conn:
cursor = conn.execute(
"""UPDATE songs SET audio_file_path = ?, file_size = ? WHERE id = ?""",
(audio_file_path, file_size, song_id)
)
conn.commit()
return cursor.rowcount > 0
# ============== USERS ==============
def add_user(name: str) -> dict:
"""Add a new user."""
with get_db_connection() as conn:
cursor = conn.execute("INSERT INTO users (name) VALUES (?)", (name,))
conn.commit()
user_id = cursor.lastrowid
cursor = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,))
return dict(cursor.fetchone())
def get_all_users() -> list[dict]:
"""Get all users."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM users")
return [dict(row) for row in cursor.fetchall()]
def get_user_by_id(user_id: int) -> Optional[dict]:
"""Get a user by ID."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,))
row = cursor.fetchone()
return dict(row) if row else None
# ============== PLAYLISTS ==============
def add_playlist(name: str, vibe_code: str = None) -> dict:
"""Add a new playlist."""
with get_db_connection() as conn:
cursor = conn.execute(
"INSERT INTO playlists (name, vibe_code) VALUES (?, ?)",
(name, vibe_code)
)
conn.commit()
playlist_id = cursor.lastrowid
cursor = conn.execute("SELECT * FROM playlists WHERE id = ?", (playlist_id,))
return dict(cursor.fetchone())
def get_all_playlists() -> list[dict]:
"""Get all playlists."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM playlists")
return [dict(row) for row in cursor.fetchall()]
def delete_playlist(playlist_id: int) -> bool:
"""Delete a playlist."""
with get_db_connection() as conn:
cursor = conn.execute("DELETE FROM playlists WHERE id = ?", (playlist_id,))
conn.commit()
return cursor.rowcount > 0
# ============== MEMORIES ==============
def add_memory(user_id: int, description: str, date: str = None, song_id: int = None) -> dict:
"""Add a new memory."""
with get_db_connection() as conn:
cursor = conn.execute(
"INSERT INTO memories (user_id, description, date, song_id) VALUES (?, ?, ?, ?)",
(user_id, description, date, song_id)
)
conn.commit()
memory_id = cursor.lastrowid
cursor = conn.execute("SELECT * FROM memories WHERE id = ?", (memory_id,))
return dict(cursor.fetchone())
def get_all_memories() -> list[dict]:
"""Get all memories."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM memories")
return [dict(row) for row in cursor.fetchall()]
def get_memories_by_user(user_id: int) -> list[dict]:
"""Get memories for a specific user."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM memories WHERE user_id = ?", (user_id,))
return [dict(row) for row in cursor.fetchall()]
def delete_memory(memory_id: int) -> bool:
"""Delete a memory."""
with get_db_connection() as conn:
cursor = conn.execute("DELETE FROM memories WHERE id = ?", (memory_id,))
conn.commit()
return cursor.rowcount > 0
# ============== CONTEXTS ==============
def add_context(weather: str = None, time_of_day: str = None, location_type: str = None) -> dict:
"""Add a new context."""
with get_db_connection() as conn:
cursor = conn.execute(
"INSERT INTO contexts (weather, time_of_day, location_type) VALUES (?, ?, ?)",
(weather, time_of_day, location_type)
)
conn.commit()
context_id = cursor.lastrowid
cursor = conn.execute("SELECT * FROM contexts WHERE id = ?", (context_id,))
return dict(cursor.fetchone())
def get_all_contexts() -> list[dict]:
"""Get all contexts."""
with get_db_connection() as conn:
cursor = conn.execute("SELECT * FROM contexts")
return [dict(row) for row in cursor.fetchall()]
def delete_context(context_id: int) -> bool:
"""Delete a context."""
with get_db_connection() as conn:
cursor = conn.execute("DELETE FROM contexts WHERE id = ?", (context_id,))
conn.commit()
return cursor.rowcount > 0
# ============== PLAY HISTORY ==============
def add_play_history(user_id: int, song_id: int, context_id: int = None) -> dict:
"""Add a play history entry."""
played_at = datetime.utcnow().isoformat()
with get_db_connection() as conn:
conn.execute(
"""INSERT INTO play_history (user_id, song_id, played_at, context_id)
VALUES (?, ?, ?, ?)""",
(user_id, song_id, played_at, context_id)
)
conn.commit()
return {"user_id": user_id, "song_id": song_id, "played_at": played_at, "context_id": context_id}
def get_play_history(user_id: int = None, limit: int = 50) -> list[dict]:
"""Get play history, optionally filtered by user."""
with get_db_connection() as conn:
if user_id:
cursor = conn.execute(
"""SELECT ph.*, s.title, s.artist
FROM play_history ph
JOIN songs s ON ph.song_id = s.id
WHERE ph.user_id = ?
ORDER BY ph.played_at DESC LIMIT ?""",
(user_id, limit)
)
else:
cursor = conn.execute(
"""SELECT ph.*, s.title, s.artist
FROM play_history ph
JOIN songs s ON ph.song_id = s.id
ORDER BY ph.played_at DESC LIMIT ?""",
(limit,)
)
return [dict(row) for row in cursor.fetchall()]
|