Exporting playlists from mythmusic

In my new house I don’t have any free to air TV – reception is too poor.  So my mythtv installation is less useful than it could be – it’s primarily a setup that records TV.  It also plays music, but it’s not inspiring at it.

My frontend died and needed replacing, and after fighting for a while I couldn’t get LIRC/remote controls going with the new hardware.  I looked around and Kodi (an evolution of XBMC) looks to have a really good remote app that goes on my iPhone.  Way better.

I’ve installed Kodi and it’s working well after some fiddling around.  But I had a lot of music playlists in mythtv.  I want them in Kodi.  Kodi can import standard m3u playlists, mythtv doesn’t have a standard export for them.

After googling around a bit, I decided to write a perl script that does it.  Without further ado, here it is for anyone that might need such a thing.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# MySQL database configuration
my $dsn = "DBI:mysql:mythconverg";
my $username = "root";
my $password = 'Massey01';

# path prefix - haven't dealt with storage groups, so set this to your storage group path
my $prefix = "/usr/share/mythtv/server/mp3/";

# connect to database
my $dbh  = DBI->connect($dsn,$username,$password);

# create temporary table for holding song list
my $sql = "CREATE temporary table tmp_playlist_songs (
  playlist_id int(11),
  playlist_name varchar(255),
  song_id int(11)
)";
my $create_table = $dbh->prepare($sql);

# create the table
$create_table->execute();
$create_table->finish();

# setup the insert statement ready for use
$sql = "INSERT INTO tmp_playlist_songs( playlist_id, playlist_name, song_id )
        VALUES (?,?,?)";

my $insert_stmt = $dbh->prepare($sql);

# select all playlists from the database
$sql = "SELECT playlist_id,
               playlist_name,
               playlist_songs
           FROM music_playlists";
my $get_playlists = $dbh->prepare($sql);
# execute the query
$get_playlists->execute();

my $playlist_id;
my $playlist_name;
my $playlist_songs;

# iterate over each playlist
while( my @row = $get_playlists->fetchrow_array() ) {
  $playlist_id = $row[0];
  $playlist_name = $row[1];
  $playlist_songs = $row[2];

  my @songs = split( ',', $playlist_songs );

  # insert the songs into the temporary table
  foreach my $song ( @songs ) {
    $insert_stmt->execute($playlist_id, $playlist_name, $song);
  }

  # select out the song filenames
  $sql = "SELECT path, filename FROM
            tmp_playlist_songs,
            music_songs,
            music_directories
          WHERE
            tmp_playlist_songs.song_id = music_songs.song_id AND
            music_songs.directory_id = music_directories.directory_id";
  my $get_playlist_detail = $dbh->prepare($sql);

  $get_playlist_detail->execute();

  open my $file_handle, ">$playlist_name.m3u";

  while( my @song_row = $get_playlist_detail->fetchrow_array() ) {
    print $file_handle $prefix . $song_row[0] . '/' . $song_row[1];
    print $file_handle "\n";
  }
  $get_playlist_detail->finish();

  close $file_handle;

  # clear out the temporary table
  $sql = "DELETE FROM tmp_playlist_songs";
  my $delete_playlist = $dbh->prepare($sql);
  $delete_playlist->execute();
}
$get_playlists->finish();
$insert_stmt->finish();

# disconnect from the MySQL database
$dbh->disconnect();

Hope it’s useful for someone.

Advertisements