Thinking that I was up for a challenge, I decided to spend the day figuring out how to put the music in my Banshee library onto a Microsoft Zune. Since my library contains a good number of FLAC files that I’ve ripped in from my CD collection, my solution called for a caching system that converts the FLAC files to mp3s and stores them so that the playlist can be changed without having to re-convert the FLAC files into something that the Zune can play on every sync. My weapons of choice for the project were a Windows XP instance running inside of Sun Virtual Box, and 126 lines of perl script.
As previously mentioned, steps 2 through 6 were accomplished by way of a perl script that I can run as often as I like:
#/usr/local/bin/perl
#Requirements: libdbd-sqlite3-perl, flac, lame
#We need database support
use DBI;
#Database path – change this to reflect your user environment
my $dbpath = “dbi:SQLite:dbname=/home/jon/.config/banshee-1/banshee.db”;
#Playlist name – change this to reflect the playlist that you want to export
my $plistname = “Favorites”;
#Cache Path – the path to the directory where you’ve been caching converted FLAC files
my $cachepath = “/home/jon/Storage/mp3Cache/”;
#Music Path – the path to the folder where your music collection is actually stored
my $musicpath = “/home/jon/Music/”;
#Connect to the database – no username/password
my $dbh = DBI->connect($dbpath,”",”",{RaiseError => 1, AutoCommit => 0});
if(!$dbh) {
print “Could not connect to database $dbpath”,”\n”,”Exiting”;
exit;
}
#Pull the list of FLAC files for conversion and caching
my $flac = $dbh->selectall_arrayref(“SELECT sme.TrackID, ct.Title, car.Name AS ‘Artist’, ca.Title AS ‘Album’, ct.Uri, ct.Duration AS ‘Length’ FROM corealbums AS ca, coreartists AS car, coresmartplaylistentries AS sme INNER JOIN coretracks AS ct ON sme.TrackID = ct.TrackID WHERE sme.SmartPlaylistID = (SELECT `SmartPlaylistID` FROM `coresmartplaylists` WHERE `Name` = ‘$plistname’) AND ca.AlbumID = ct.AlbumID AND car.ArtistID = ct.ArtistID AND ct.MimeType LIKE ‘%flac’”);
#open the m3u file to write the cached items to
open my $m3u, ‘>’, $cachepath.$plistname.’_cached.m3u’ or die “Error trying to open cache m3u playlist for overwrite. Do you have write permissions in $cachepath ?”;
print $m3u “#EXTM3U\r\n\r\n”; #note windows \r\n here
#add /music to $cachepath so that files are in a subdirectory, away from the m3u file
$cachepath = $cachepath.”music/”;
if( ! -e $cachepath ) {
`mkdir “$cachepath”`;
}
#loop through the files and check if they need to be cached
foreach my $i (@$flac) {
my ($trackid, $title, $artist, $album, $uri, $length) = @$i;
#correct the uri by removing the file:// prefix and reverting the uri escaping
$uri = substr $uri, 7;
$uri =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
#fix time into seconds
$length = int($length/1000);
#check if the flac file has already been converted and cached at cachepath
#if not, convert it and put it at cachepath.
my $path = $cachepath . $artist . ‘/’ . $album . ‘/’ . $title . ‘.mp3′;
if( ! -e $path ) {
#file dne, convert it
print “\nTrack: $title by $artist has not yet been cached, converting…”,”\n”;
#make sure that the file actually exists before attempting to convert it
if( ! -e $uri ) {
print “WARNING: Track $title by $artist does not exist at $uri”,”\n”;
} else {
#ensure that cache album/artist directories exist
my $partpath = $cachepath.$artist;
if( ! -d $partpath ) {
`mkdir “$partpath”`;
}
$partpath = $partpath.’/’.$album;
if( ! -d $partpath ) {
`mkdir “$partpath”`;l
}
#do the conversion – we’re chaining flac and lame here, reading in the flac file from $uri, and putting the resulting mp3 at $path
`flac -cd “$uri” | lame -h – “$path”`;
}
}
#add the track to the m3u file – note that these entries are relative to the location of the m3u file in the root of $cachepath
#the paths use a backslash and a \r\n newline so that they work correctly on windows
print $m3u “#EXTINF:$length,$artist – $title\r\n”;
print $m3u ‘\\music\\’.$artist.’\\’.$album.’\\’.$title.’.mp3′,”\r\n\r\n”;
}
#close the m3u file in the cachepath directory
close $m3u;
#TODO: scan the m3u file and delete any files that aren’t in it from the cache directory
#Pull the list of MP3 files and dump them into an m3u file
my $flac = $dbh->selectall_arrayref(“SELECT sme.TrackID, ct.Title, car.Name AS ‘Artist’, ca.Title AS ‘Album’, ct.Uri, ct.Duration AS ‘Length’ FROM corealbums AS ca, coreartists AS car, coresmartplaylistentries AS sme INNER JOIN coretracks AS ct ON sme.TrackID = ct.TrackID WHERE sme.SmartPlaylistID = (SELECT `SmartPlaylistID` FROM `coresmartplaylists` WHERE `Name` = ‘$plistname’) AND ca.AlbumID = ct.AlbumID AND car.ArtistID = ct.ArtistID AND ct.MimeType LIKE ‘%mp3′”);
#open the m3u file to write the cached items to
open my $m3u, ‘>’, $musicpath.$plistname.’.m3u’ or die “Error trying to open music folder m3u playlist for overwrite. Do you have write permissions in $musicpath ?”;
print $m3u “#EXTM3U\r\n\r\n”; #note windows \r\n here
#loop through the files and check if they need to be cached
foreach my $i (@$flac) {
my ($trackid, $title, $artist, $album, $uri, $length) = @$i;
#correct the uri to become a windows file path
$uri = substr $uri, 7; #remove file:// prefix
$uri =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg; #correct uri encoding
$uri =~ s/$musicpath//g; #remove musicpath prefix
$uri =~ s/\//\\/g; #change forward slashes to backslashes
$uri = ‘\\’.$uri; #add the leading backslash
#fix time into seconds
$length = int($length/1000);
#add the track to the m3u file – note that these entries are relative to the location of the m3u file in the root of $cachepath
#the paths use a backslash and a \r\n newline so that they work correctly on windows
print $m3u “#EXTINF:$length,$artist – $title\r\n”;
print $m3u $uri,”\r\n\r\n”;
}
#close the m3u file and the database connection
close $m3u;
$dbh->disconnect;
Sorry for the horrible formatting.
The only snag that I hit during the entire process was really my fault – I have a tendency to overcomplicate things, and did so on this project by initially writing the script to output a *.zpl file instead of a *.m3u file. That didn’t work at all, and I ended up simplifying the script greatly by just outputting an *.m3u file and hoping for the best.
On the off chance that the Zune jukebox software refuses to properly update its playlists after you change the *.m3u files, first try deleting them from the application, and then restarting it. If that doesn’t work, you can write a Windows batch script with code similar to the following:
This script deletes all files from the Zune playlists directory, and then copies each of the *.m3u files that we created with the above perl script directly into the Zune playlists directory. This should force the application to get it’s act together.
Overall, I’m happy with this patchwork job. It allows me to use the Zune on Linux, which is great because the Zune really is a beautiful piece of hardware. Now if only the libmtp guys could get it working natively, without a WinXP VM…
