SQLite and banshee: query from command line?

mstrimel · 07-26-2015, 08:28 AM

hi,
My music collection is fully curated in Banshee, and i LOVE the "Smart Playlists".

I need a CLI way to export my Banshee Smart Playlists. Reason being, I am using Music Player Daemon to drive my whole-house audio. (Exporting each Smart Playlist from the Banshee GUI is way too time consuming and can't be done via cron).

I am NOT a programmer, but i see strings like the following in Banshee's SQLite database (CoreSmartPlaylists table)

Code:

<request><query banshee-version="1"><and><equals><field name="genre" /><string>Acid Jazz</string></equals><greaterThanEquals><field name="rating" /><int>3</int></greaterThanEquals></and></query></request>

Clearly these parameters are being used by Banshee to create my Smart Playlists, but how? This is not any query format that i can recognize or parse [no surprise, given that i know nothing : ) ]

As a fun newbie project, i am willing to research the heck out of SQLite3 and bash, to turn this text string into some kind of CLI query that will output results to a file. BUT, can someone give me a hint to get going? What's the concept for turning that string into a query?

And is that even the right question?
thanks!
Mary

norobro · 07-26-2015, 11:13 AM

EDIT: Disregard the info below, obviously I need to work on my reading comprehension. Rereading your post I see that you are way past that.

I'll take a look at that table and see if I can figure anything out.

_________________________________________________________________________________________________

You don't say what distro you are using, but you will need the sqlite command line interface. On Debain the package is named, oddly, sqlite3.

Then at the command line enter:

Code:

$ sqlite3 /path/to/banshee.db

Read the man page (man sqlite3) to see a list of available commands or you can enter ".help" at the prompt. For example to see all of the tables in a database you enter ".tables":

Code:

$ sqlite3 banshee.db 
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .tables
CoreAlbums                CoreRemovedTracks         CoverArtDownloads       
CoreArtists               CoreShuffleModifications  HyenaModelVersions      
CoreCacheModels           CoreShufflers             IaItems                 
CoreConfiguration         CoreShuffles              LastfmStations          
CorePlaylistEntries       CoreSmartPlaylistEntries  PodcastEnclosures       
CorePlaylists             CoreSmartPlaylists        PodcastItems            
CorePrimarySources        CoreTracks                PodcastSyndications

I don't use banshee but I experimented a little and you can get data out like so:

Code:

$ sqlite3 banshee.db 'select Title from CoreTracks' > tracks.txt

HTH

mstrimel · 07-26-2015, 08:06 PM

Quote:

Originally Posted by norobro

I don't use banshee but I experimented a little and you can get data out like so:

Code:

$ sqlite3 banshee.db 'select Title from CoreTracks' > tracks.txt

HTH

Thank you, that last bit does actually help tons; i did not know that sqlite3 could export to a file using the ">" symbol. I got it to export the track list for a particular playlist by looking up the playlist ID manually:

Code:

sqlite3 /home/mary/.config/banshee-1/bansheebackupmns.db 'select TrackID FROM CoreSmartPlaylistEntries WHERE SmartPlaylistID=18' > tracks.txt

So to really dumb it down for me: i need to write a shell script that will create a unique text file for each SmartPlaylist (using its name, not its ID) and then fill that text file with the actual track names. I have no idea HOW, but i think that is the "what".

Any suggetions welcome!

norobro · 07-27-2015, 08:24 AM

The banshee.db is a relational database so it is possible to get the playlist names and the track names. Try playing around with like the following:

Code:

sqlite> select coresmartplaylists.name, corealbums.title, trackid, coretracks.title from coresmartplaylistentries join coresmartplaylists using(smartplaylistid) join coretracks using(trackid) join corealbums on coretracks.albumid=corealbums.albumid;

Executing that command from the command line should give you a file that contains all of your playlists which you will be able to parse, in bash using awk or sed, to separate the different smart play lists into separate files.

mstrimel · 07-27-2015, 07:25 PM

Oh yeah, I see that "join" is the key concept here ... Something like this is exactly what I need.
Can't wait to sit down and try this. Thank you!!

mstrimel · 07-29-2015, 12:06 PM

Hello,
So, one problem I am having right off the bat, is that sqlite3 does not seem to recognize when I do a statement involving the [table].[column] construct.

To return song names and artist names, and associate them with smart playlist names, I believe I need to join AT LEAST three tables, maybe four, in my query [CoreSmartPlaylistEntries (to identify the tracks by trackId and playlist ID), CoreSmartPlaylists (to associate playlistID with a playlist name), CoreTracks (to associate trackId with song name) and CoreArtists (to associate artist name).

To test this on a particular playlist (#5), I am using a SELECT ... FROM, then a series of JOIN ... ON, followed by a "WHERE CoreSmartPlaylists.PlaylistID=5;"

The error I'm getting back is that "CoreSmartPlaylists.PlaylistID is ambiguous" or that "there is no such field as CoreSmartPlaylists.PlaylistID". If I truncate it to just PlaylistID it also complains.

Sorry I am not in front of my computer to give the exact commands. But why in principle would SQLite3 ever reject "CoreSmartPlaylists.PlaylistID" as ambigouos? [I've also tried flipping the order to PlaylistID.CoresmartPlaylists but I think it still gives the error.]

Thanks for any suggestions!
Mary

norobro · 07-29-2015, 03:26 PM

When you get a chance please post your query.

I imported a CD and created a smart play list with one song from the CD.
Query:

Code:

sqlite> .headers on
sqlite> select name, corealbums.artistname, corealbums.title, trackid, coretracks.title 
FROM CoreSmartPlaylistEntries
join coresmartplaylists using(smartplaylistid)
join coretracks using(trackid)
join corealbums on coretracks.albumid=corealbums.albumid
where coresmartplaylists.smartplaylistid=1;

Although it's probably not exactly what you are looking for, here's the output:

Code:

Name|ArtistName|Title|TrackID|Title
Test Smart Play List|Sybil|Cold Drink|1|All I Like

mstrimel · 07-29-2015, 07:14 PM

Your query works perfectly, thanks.

My error: I had failed to list in my SELECT statement all of the columns that i was after [i thought i was supposed to list them in the JOIN statements for each of their tables, not the SELECT, but i was just super wrong!].

Now, to automate this proces, I propose to:

1. embed these SQLITE3 statements into a bash shell script that will output their results to a file named [smartplaylist].txt
2. have my bash shell script run these statements sequentially for SmartPlaylistID's [1,2,3...n]
3. add awk commands to the shell script that will format the resulting txt files into playlists recognized by mpd

Is that a reasonable game plan? This will be my first script, but i'm very motivated :-)

Thank You!!!

norobro · 07-29-2015, 08:31 PM

You're welcome!

The only problem that I see with your method is that you will have to hard code your queries. You can put the query in a loop and iterate your play list numbers, but then how will your loop know when to stop?

Another way to approach it is to perform one query to get all of your play lists from the db and then parse the output file.

Give the following a try from the command line:

Code:

$ sqlite3 banshee.db 'select smartplaylistid, name, corealbums.artistname, corealbums.title, trackid, coretracks.title 
FROM CoreSmartPlaylistEntries
join coresmartplaylists using(smartplaylistid)
join coretracks using(trackid)
join corealbums on coretracks.albumid=corealbums.albumid ' > lists.txt

Code:

$ awk -F "|" '{print >> $1".txt"}' lists.txt

The awk command should separate your play lists into files named 1.txt, 2.txt etc.

I'll leave the parsing of the files to you.

Norm