oscarzwagers.nl
A website inspired by Last.fm that will keep track of your listening statistics
lastfm
music
statistics
1import duckdb
2import os
3
4STATS_DATABASE_PATH = os.getenv("STATS_DATABASE_PATH") or "private/database/statistics.dev.duckdb"
5
6def create_recording_mbid__artist_mbid(conn: duckdb.DuckDBPyConnection):
7 query = """
8 CREATE TABLE recording_mbid__artist_mbid (
9 recording_mbid TEXT NOT NULL,
10 artist_mbid TEXT NOT NULL,
11 PRIMARY KEY (recording_mbid, artist_mbid)
12 );
13 """
14
15 conn.execute(query)
16
17
18def create_recording_mbid__release_group_mbid(conn: duckdb.DuckDBPyConnection):
19 query = """
20 CREATE TABLE recording_mbid__release_group_mbid (
21 recording_mbid TEXT NOT NULL,
22 release_group_mbid TEXT NOT NULL,
23 PRIMARY KEY (recording_mbid, release_group_mbid)
24 );
25 """
26
27 conn.execute(query)
28
29
30def scrobble(conn: duckdb.DuckDBPyConnection):
31 query = """
32 CREATE TABLE scrobble (
33 user_id INTEGER NOT NULL,
34 recording_mbid TEXT NOT NULL,
35 played_at TIMESTAMP NOT NULL
36 );
37
38 CREATE INDEX scrobble_user_id_idx ON scrobble(user_id);
39 CREATE INDEX scrobble_recording_mbid_idx ON scrobble(recording_mbid);
40 CREATE INDEX scrobble_played_at_idx ON scrobble(played_at);
41 """
42
43 conn.execute(query)
44
45
46def main():
47 conn = duckdb.connect(STATS_DATABASE_PATH)
48
49 create_recording_mbid__artist_mbid(conn)
50 create_recording_mbid__release_group_mbid(conn)
51 scrobble(conn)
52
53 conn.close()
54
55
56if __name__ == "__main__":
57 main()