James' Tech Blog

The title of the post is a little confusing, I’ll admit that straight away. Here is the problem. I had a bunch of series AVI files name as such: 101.avi 102.avi 103.avi … ad nauseum. This was great, but episode titles make it easier to pick out which one I really want to watch. Especially when I’m using UPNP. So I renamed all my files to be like 101.Rose.avi. Great, super easy to tell what the episode is about!

Good thing mythtv is smart and made hashes of all the files. Now it knows that 101.Rose.avi might as well be 101.avi. Awesome! If I had trailers, poster-art, and other metadata it would be right there with the files with new names. Unfortunately, what it didn’t change was the title. So when I look at my videos in mythfrontend 101.Rose.avi is called 101. The titles didn’t update. Well, this is good behavior. If we’re going to track which file is which by hashes, then we don’t want to change the title just because the filename changed (or path I’m presuming).

This is not the behavior I wanted though. I searched a little bit, but couldn’t find anything. I did find out the structure of videometadata though. That gave me the clue to just write a script to update the titles based on the filenames. Here we go, in glorious PHP:

//replace master_IP with your MySQL IP address and password with whatever your password is
$con = mysql_connect("master_IP","mythtv","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mythconverg", $con);
//if you want all the files the next line is good. Otherwise see line after that
$myquery = "SELECT intid,title,filename FROM videometadata";
//I did it for a subset of files which is why I used LIKE to match part of the path in the filename field
//$myquery = "SELECT intid,title,filename FROM videometadata WHERE filename ". 'LIKE "%who%"';
$myquery_rows = mysql_query($myquery);
while ($myrow = mysql_fetch_array($myquery_rows)) {
//update query
$new_title = substr(basename($myrow['filename']), 0, strrpos(basename($myrow['filename']), '.'));
$up_query = "UPDATE videometadata SET title = '" . $new_title . "' WHERE intid = '" . $myrow['intid'] . "'";
print $up_query;
//mysql_query($up_query); //uncomment this after you're satisfied with the output
}
mysql_close($con);

To protect the innocent I have it just print what it would change the titles to. All you have to do is uncomment the mysql_query line. Sorry the code plugin I have squashed my tabs. Not a deal breaker, but it would be easier to read. Also, the code plugin doesn’t like php tags you’ll have to add them. The next script is probably the one you want to run first. It just prints the intid, title, and filename (which in my case included path) for each file. This is handy to see if the filenames/titles match. Also, to see if your update went well.

$con = mysql_connect("master_IP","mythtv","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("mythconverg", $con);

//if you want all the files the next line is good. Otherwise see line after that
$myquery = "SELECT intid,title,filename FROM videometadata";
//I did it for a subset of files which is why I used LIKE to match part of the path in the filename field
//$myquery = "SELECT intid,title,filename FROM videometadata WHERE filename ". 'LIKE "%who%"';
$myquery_rows = mysql_query($myquery);
while ($myrow = mysql_fetch_array($myquery_rows)) {
  print $myrow['intid'] . " | " . $myrow['title'] . " | " . $myrow['filename'] ."\n";
  }
mysql_close($con);

Please don’t go running amok in your database and blame me for it getting screwed. Use mysqldump before running the update script. Although it should be pretty innocuous because you can always use mythfrontend to rebuild the videometadata database. Though you would lose any metadata you had input for your files. Happy hunting! Maybe I’ll port this to a proper scripting language like PERL or python. Or maybe someone’s nice enough to do it for the rest of us???