Loading Profile...
Need help browsing SQLite database
I am migrating from MediaMonkey to Songbird, because I love open-source software, because Songbird is incorporating bulletproof, lightweight components, and because it is being so clearly aimed at user needs. I used MM because of its excellent library, which recently switched from mdb to SQLIte. I like to be able to read the library directly, so that I can occasionally export a list of all my tag data to Excel, or write complex queries to create playlists.
I'm having problems reading main@library.songbirdnest.com.db. Using Firefox's SQLite Manager add-on, I can read the database structure, but can't find the data. Using SQLite Spy, I can view part of the data, but most of it is unintelligible. The record keys show as lengthy hexadecimal values, the file path contains %20 in place of every blank space (and upper case is lost), and the rest of my metadata (year, track, etc.) - - well, I just can't find it. Is there some trick to finding and interpreting the data in the library? Also, is there a good utility for exporting the library to Excel or MS Access?
Thanks!
I'm having problems reading main@library.songbirdnest.com.db. Using Firefox's SQLite Manager add-on, I can read the database structure, but can't find the data. Using SQLite Spy, I can view part of the data, but most of it is unintelligible. The record keys show as lengthy hexadecimal values, the file path contains %20 in place of every blank space (and upper case is lost), and the rest of my metadata (year, track, etc.) - - well, I just can't find it. Is there some trick to finding and interpreting the data in the library? Also, is there a good utility for exporting the library to Excel or MS Access?
Thanks!
I’m thankful.
1
person has this question
I have this question, too!
Tell me when someone answers.
The more people who ask this question, the more it gets noticed.
The more people who ask this question, the more it gets noticed.
-
Inappropriate?OMG! THAT'S SUPER SECRET INFOZ.
j/k.
No seriously, you're crazy. :-)
There aren't any good utilities for exporting the data out, but yeah, the SQLite Manager add-on should be a good start for browsing and exploring the schema and data (BTW, SQLite Manager also works directly in Songbird too...)
This is probably too long to really go into on GS, so if anything is unclear, I'd encourage you to stop by our IRC channel (irc.mozilla.org, #songbird) for some real-time help... but here's a quick summary of the main DB and how your data is stored.
Songbird has the concept of mediaItems. Libraries, playlists, and tracks are all mediaItems. MediaItems have properties, which map to the metadata for your tracks/playlists/etc.
If you open up the main DB you'll see a few tables:
- media_items - These contain all the mediaItems Songbird knows about
- properties - These are the namespace IDs for the various properties Songbird tracks (since add-ons and webpages can create their own properties, this needs to be extensible)
- resource_properties - These are the mappings of property keys+values to media items
Within media_items, there are a few different values:
- guid - The global unique ID representing that mediaitem
- content_url - The actual location of the track
- is_list - Whether or not this is a playlist
You can take an entry from media_items, and map its 'media_item_id' against all rows in the resource_properties table who have the same 'media_item_id'. You can then map the 'property_id' against the properties table's 'property_id' to get the track + metadata.
All that aside though... I'd really encourage you to do this differently :) Our DB schemas are always subject to change... and if all you're interested in doing is exporting your library/playlist data to something like Excel or CSV, it'd be *much* easier to write an extension to do it and use proper Songbird APIs to get at the data in a much cleaner (and more stable) API.
I’m still thinking you're crazy
The company says
this answers the question
1 Comment Add a comment
This answers the question
-
Steve ---
Wow. Thanks for making time to write such a thorough reply. I now have a much better appreciation of the library functionality and its potential for helping me organize my various music collections. I think Songbird is becoming a first-rate package, featuring virtually every tool I have on my own wish list. But most of all, I am impressed by the outgoing, cooperative nature of the development project. It's comforting to see people working together for a common good so effectively. Thanks, again, for your help.
EMPLOYEE
