Python script to dump playback history from Songbird/Nightingale

dump_songbird_plays.py

#!/usr/bin/env python3

# dump_songbird_plays.py - Dumps Songbird/Nightingale playback history.
#
# Written in 2023 by Daniel Erat.
#
# To the extent possible under law, the author has dedicated all copyright
# and related and neighboring rights to this software to the public domain
# worldwide. This software is distributed without any warranty.
#
# http://creativecommons.org/publicdomain/zero/1.0/

import json
import sqlite3
from contextlib import closing

# This script reads database files from the discontinued Songbird/Nightingale
# music player and dumps the playback history in as JSON objects (hopefully)
# suitable for submission to ListenBrainz.
#
# More discussion:
# https://community.metabrainz.org/t/songbird-nightingale-player/620339

def load_songs():
    """
    Returns a dict of 'track_metadata' objects keyed by media_items.guid:
    https://listenbrainz.readthedocs.io/en/latest/users/json.html#payload-json-details
    """
    # CREATE TABLE media_items (
    #   media_item_id integer primary key autoincrement,  /* implicit index creation */
    #   guid text unique not null, /* implicit index creation */
    #   created integer not null,
    #   updated integer not null,
    #   content_url text not null,
    #   content_mime_type text,
    #   content_length integer,
    #   content_hash text,
    #   hidden integer not null check(hidden in (0, 1)),
    #   media_list_type_id integer,
    #   is_list integer not null check(is_list in (0, 1)) default 0,
    #   metadata_hash_identity text
    # );
    # CREATE TABLE properties (
    #   property_id integer primary key autoincrement, /* implicit index creation */
    #   property_name text not null unique /* implicit index creation */
    # );
    # CREATE TABLE resource_properties (
    #   media_item_id integer not null,
    #   property_id integer not null,
    #   obj text not null,
    #   obj_searchable text,
    #   obj_sortable text collate library_collate,
    #   obj_secondary_sortable text collate library_collate,
    #   primary key (media_item_id, property_id)
    # );
    songs = {}
    with closing(sqlite3.connect('main@library.songbirdnest.com.db')) as conn:
        with closing(conn.cursor()) as cur:
            cur.execute(
                '''
                SELECT mi.guid, rp.obj, p.property_name
                FROM media_items mi
                JOIN resource_properties rp
                  ON rp.media_item_id = mi.media_item_id
                JOIN properties p ON p.property_id = rp.property_id
                ''')
            for row in cur.fetchall():
                guid, val, prop = row
                song = songs.setdefault(guid, {})
                if prop == 'http://songbirdnest.com/data/1.0#trackName':
                    song['track_name'] = val
                elif prop == 'http://songbirdnest.com/data/1.0#albumName':
                    song['release_name'] = val
                elif prop == 'http://songbirdnest.com/data/1.0#artistName':
                    song['artist_name'] = val
                elif prop == 'http://songbirdnest.com/data/1.0#trackNumber':
                    song['tracknumber'] = int(val)
                elif prop == 'http://songbirdnest.com/data/1.0#duration':
                    song['duration_ms'] = int(val) // 1000

    return songs


def load_plays(songs):
    """
    Returns a list of listen reports:
    https://listenbrainz.readthedocs.io/en/latest/users/json.html#submission-json
    """
    reports = []

    # CREATE TABLE playback_history_entries (
    #   entry_id integer primary key autoincrement, /*implicit index creation*/
    #   library_guid text not null,
    #   media_item_guid text not null,
    #   play_time integer not null,
    #   play_duration integer
    # );
    with closing(sqlite3.connect('playbackhistory@songbirdnest.com.db')) as conn:
        with closing(conn.cursor()) as cur:
            cur.execute(
                '''
                SELECT media_item_guid, play_time, play_duration
                FROM playback_history_entries
                ''')
            for row in cur.fetchall():
                guid, start, dur = row
                song = songs[guid]
                if song:
                    reports.append({
                        'listened_at': round(int(start) / 1000000),
                        'track_metadata': song,
                    })

    return reports


if __name__ == '__main__':
    for r in load_plays(load_songs()):
        print(json.dumps(r))