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.

Rails, MYSQL and clustered primary keys

Rails by default creates every table with a primary key of ‘id’.  This is a sequence generated key that is globally unique.  There are benefits for insert performance, but depending on your usage model query performance can be impacted by this scheme.

Consider the case of an application, a (the stereotypical) blog that has posts and comments.  Imagine that this is a reasonably high volume blog, so we have lots of posts, and lots of comments, and the nature of the blog is that old posts attract comments over time.

Continue reading