Rockbox Stat Tracking

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.

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

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

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:

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

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:

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")?;

    //...
}

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")?;

    //...
}

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;

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; 

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:

1. Pages need to be static.
2. Pages need to be JavaScript-free.

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);

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 %}

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.

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