-

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