Created
January 24, 2023 01:55
-
-
Save derat/7b0bb596625da5a61130f8e8690c1ad2 to your computer and use it in GitHub Desktop.
Python script to dump playback history from Songbird/Nightingale
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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)) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment