+

Preamble: Digital Sovereignity & Rockbox

+

+ I've been building up a pretty sizeable collection of digital music + over the last couple of years. I think there's a lot of value in owning + the music I pay for and being able to choose how I listen to it. + Purchasing music also allows me to support artists in a more direct + and substantial way than the fractions of cents for using streaming services, + but that's more of a happy consequence than some moral obligation I feel. +

+

+ Over the years, I've enjoyed listening to my music in a variety of ways. + For years I kept all of my music files on all of my devices and used + various local music clients depending on the platform, most notably mpd + and ncmpcpp on linux. Eventually, as I charged headlong into the glorious + world of self-hosting, I began using a central Jellyfin media server that + I stream music and video from. It's super convenient, and works on all of + my devices (including my TV!). +

+

+ My media server is great, and it's been the primary way I listen to music + for a while now. But it has limitations. For example, I don't expose my media + server to the internet, so I'm unable to stream from it while I'm out and + about. And even if I could, the bandwidth requirements would be pretty high. + I figured I would need a dedicated music player if I wanted to take my music + library on the go, and settled on the HIFI Walker H2 after reading some + online recommendations. The ability to install Rockbox, an open-source firmware, + was a big factor in my decision. I couldn't tell you how the device works + out of the box, since I flashed the firmware pretty much immediately once I got it, + but I've been super impressed with how the device works while running Rockbox. +

+

Screenshot of Rockbox player showing cool theme. — I'm using a modified version of the InfoMatrix-v2 theme, which looks great.

+ Rockbox comes with many codecs for common audio formats including FLAC and MP3. The + device boots extremely quickly, and the interface is snappy. Virtually every aspect + of the user experience is tweakable and customizable to a crazy degree. I've even begun + listening to music on my player even at home, since a device specifically for the + purpose provides less distraction while I'm trying to be productive. +

+

+ All this to say I'm pretty much sold on Rockbox. But there's certain things I + still miss from my days of being a user of popular services like Spotify with + fancy APIs and data tracking. Things like Spotify wrapped or third-party apps + for visualizing playback statistics are a fun way to see what my listening history + looks like and could potentially be used to help find more music that I'd enjoy. + This is why when I noticed that Rockbock has a playback logging feature, a little + lightbulb lit up over my head. +

+

Generating and Parsing Logs

+

+ Rockbox has a feature that logs playback information to a text file. This feature can + be enabled by setting Playback Settings > Logging to "On". With this setting enabled, a + new line gets added to the end of the .rockbox/playback.log file every time you play a track, + containing info about what you played and when. +

+

+ The logging feature is actually already used by the LastFM scrobbler plugin that comes preloaded with + Rockbox, which is probably the simplest way to get insights into your playback. However, + I personally want to avoid using third-party services as much as possible, because it's more fun. +

+

+ If I take a look at a logfile generated after a bit of listening, I'll see that I've wound up with + a series of lines that each look something like this: +

+

1758478258:336689:336718:/<microSD0>/Music/This Is The Glasshouse/This Is The Glasshouse - 867/This Is The Glasshouse - 867 - 01 Streetlight By Streetlight.flac

+

An example of a log entry for "Streetlight by Streetlight" by This is the Glasshouse. +

+ +

+

+ I wasn't really able to find any information online about the format of these logs, but they appear + to be simple enough to figure out. From what I can tell, each event is broken up into 4 pieces: +

Timestamp: The number of milliseconds since the UNIX epoch. +
Playback Duration: The amount of the song that was played, in milliseconds. +
Total Track Length: The length of the played track, in milliseconds. +
File Path: An absolute path to the file containing the track on the filesystem. +

+ All of this is enough to know what I was listening to and when. I can use the file path to check for + audio tags which can help glean even more information about my listening habits. +

+

Now that I have this information and know how to interpret it, I'm ready to start processing it!

+

Analyzing Playback History

+

+ In order to get some useful information out of my playback history, I think it's a good idea to start by + building + a database. I created a sqlite database with the following tables: + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
songs
id i64 PK
title String
artists JSON
album_id i64?
genre String?
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + +
albums
id i64 PK
title String
artist String
cover_art Blob?
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + +
history
id i64 PK
timestamp Datetime
duration i64
song_id i64
+
+ I can add more columns later, but this is a good place to start. +

songs
id	i64	PK
title	String
artists	JSON
album_id	i64?
genre	String?

albums
id	i64	PK
title	String
artist	String
cover_art	Blob?

history
id	i64	PK
timestamp	Datetime
duration	i64
song_id	i64

+

+ Now, as I read through the logfile line-by-line, I can check if each album exists before + inserting it into the database: +

+

for line in log_file.lines().flatten() {
+    println!("{line}");
+    // Skip comments
+    if line.starts_with("#") {
+        continue;
+    }
+    let chunks = line.split(":").collect::>();
+
+    let timestamp = DateTime::from_timestamp_secs(
+        i64::from_str_radix(chunks[0], 10).context("Failed to parse timestamp")?,
+    )
+    .context("Failed to convert timestamp")?;
+
+    // Load tags from file on device
+    let file_path = chunks[chunks.len() - 1][1..]
+        .split_once("/")
+        .context("Missing file")?
+        .1;
+    let tags = Tag::new()
+        .read_from_path(args.mount_point.join(file_path))
+        .context("Failed to read audio tags")?;
+
+    //...
+}

+

Parsing log entry and loading audio metadata.

+ +

+

if let Some(existing_album) =
+    sqlx::query("SELECT id FROM albums WHERE title=$1 AND artist=$2")
+        .bind(album_title)
+        .bind(album_artist)
+        .fetch_optional(&mut *db)
+        .await
+        .context("Failed to execute query to find existing album")?
+{
+    let album_id: i64 = existing_album.get("id");
+    info!("Album already exists, id {album_id}");
+    //...
+} else {
+    info!("Inserting new album: {album_title} by {album_artist}");
+    //...
+    let result = sqlx::query(
+        "INSERT INTO albums (title, artist, cover_art) VALUES ($1, $2, $3);",
+    )
+    .bind(album_title)
+    .bind(album_artist)
+    .bind(cover)
+    .execute(&mut *db)
+    .await
+    .context("Failed to execute query to insert album into database")?;
+
+    //...
+}

+

Checking for an album with matching artist and title before creating a new row in the + database.

+ + I did something similar with the songs and history tables, basically building up a cache + of history information and skipping anything that's already in the database on repeat runs. +

+

+ With this database constructed, it's pretty easy to get a bunch of different information + about my listening. For example (forgive me if my SQL skills are kind of ass lol): +

+

SELECT 
+    songs.title AS song_title,
+    songs.artists AS song_artists,
+    songs.genre AS song_genre,
+    albums.title AS album_title,
+    albums.artist AS album_artist,
+    history.timestamp AS timestamp,
+    history.duration AS duration
+FROM history
+CROSS JOIN songs ON songs.id = history.song_id
+CROSS JOIN albums ON albums.id = songs.album_id
+ORDER BY timestamp DESC;

+

Querying for a list of each history entry along with track metadata, sorted from most to + least recent.

+ +

+

SELECT 
+    songs.genre,
+    SUM(history.duration) AS total_duration
+FROM history
+CROSS JOIN songs ON history.song_id = songs.id
+GROUP BY genre
+ORDER BY total_duration DESC
+LIMIT 10;

+

Querying for the top 10 most listened genres by playtime.

+ +

+

+ It's all well and good to be able to view this information using a database client, + but it would be really cool if I could visualize this data somehow. +

+

Visualizing this Data Somehow

+

+ I wanted to make this data available on my website for people to view, and for a bunch of mostly trivial + reasons I won't get into here, I have a couple of requirements for pages on this site: +

Pages need to be static. +
Pages need to be JavaScript-free. +

+ This means any chart rendering needs to be done automatically at build time before + deploying. I don't currently use a static site generator for my site (just for fun), + so I'm basically going to need to write one specifically to generate this page. +

+

+ I won't get too deep into the specifics of how I queried the database and generated each visualization + on + the page, but I can explain the visualizations I created using the queries from the previous section. + For the + listening history I wanted to generate a table displaying the information. To accomplish this, I first + used a combination of sqlx's ability to convert a row to a struct and serde to serialize + the rows as JSON values. +

+

#[derive(Serialize, Deserialize, FromRow)]
+struct HistoryEntry {
+    song_title: String,
+    song_artists: Value,
+    timestamp: DateTime<Utc>,
+    duration: i64,
+    album_title: String,
+    album_artist: Option<String>,
+    song_genre: Option<String>,
+}
+
+//...later
+let history = sqlx::query_as::<_, HistoryEntry>(
+    /* SELECT... */
+).fetch_all(&mut *db).await;
+
+//...later still, tera context accepts
+let mut context = tera::Context::new();
+context.insert("history", &history);
+

+

Struct definition for a history entry, allowing conversion from a sqlx row and + de/serialization from/to JSON.

+ +

+

+ In order to keep the generation as painless as possible, I decided to use the Tera template + engine, which allows me to define a template HTML file and substitute in values from + a context which I can define before rendering. In the case of the table, I can just generate a <tr> + matching the data for each item: +

+

{% macro history_table(history) %}
+<h3>Playback History</h3>
+<div class="table-container">
+    <table>
+        <thead>
+            <tr>
+                <th>Timestamp</th>
+                <th>Played Duration</th>
+                <th>Title</th>
+                <th>Artists</th>
+                <th>Album</th>
+                <th>Genre</th>
+            </tr>
+        </thead>
+        <tbody>
+            {% for item in history %}<tr>
+                <td>{{ item.timestamp | date(format="%Y-%m-%d %H:%M:%S") }}</td>
+                <td>{{ item.duration | hms }}</td>
+                <td>{{ item.song_title }}</td>
+                <td>{{ item.song_artists }}</td>
+                <td>{{ item.album_title }}</td>
+                <td>{{ item.song_genre }}</td>
+            </tr>
+            {% endfor %}
+        </tbody>
+    </table>
+</div>
+{% endmacro history_table %}

+

+ A Tera macro for generating a table from a list of playback history items. + I used a macro so I can re-use this later if I want to add time range views. + (last month, year, etc.) +

+ +

+

+ I wrote similar macros for each of the visualizations I wanted to create. Most are + easy, but for my top 10 genres I wanted to display a pie chart. I found a pretty decent + data visualization crate called charming that's able to render to html, however + the output contains javascript so it's a no-go for me. Luckily, it can also render to + an SVG which I can embed nicely within the page. +

+ +

Here's one I generated just now.

+ +

+

+ And that's pretty much all there is to it! The finished thing can be found here. +

+

Rockbox Stat Tracking

Preamble: Digital Sovereignity & Rockbox

Generating and Parsing Logs

Analyzing Playback History

Visualizing this Data Somehow