Deleting XBMC shows from disk based on Watched status – part 1

As the avid reader knows, I am a fan of XBMC. So much so that I have it setup on every TV in the house, which means I also have a centralized mysql database to keep them all in synch. That part works great, and everything is going well. My problem is that I am lazy about deleting stuff I’ve watched, and I tend to wait until my drive is nearly full before going in to delete the old episodes. This involves a manual audit of a lot of different directories, and is a bit cumbersome.

So let’s change that.

Query mysql to see what you’ve already watched

First, we need to know how to tell if we’ve watched a show within XBMC. This is a dead simple query:

use MyVideos75;
SELECT * FROM `episodeview` WHERE `playCount` IS NOT NULL;

Getting a CSV list via bash

But, we want to output that in a way we can mangle with bash, and we certainly don’t need all of that extra information.. I’m certain this could be accomplished within mysql, but for this case we don’t really need to be bothered with that since we have linux tools available.

For the purposes of this experiment, I’ve decided that the fields I care about are “c18” (full path to the file), “strFileName” (prettier version of episode name), “playCount“, and “lastPlayed“.

# echo “SELECT * FROM episodeview WHERE playCount IS NOT NULL;”|mysql -B -u root -p -D MyVideos75 | awk -F’\t’ ‘{print $21 “|” $28 “|” $30 “|” $31}’ > /tmp/watched.log

Why are we using pipes here instead of commas as a field separator? Because there’s a huge chance of commas being included in the episode names, and an incredibly low chance of there being a pipe, so it makes it easier to sort and awk later. A more reasonable person might do some sanitization on the data, but ain’t nobody got time for that.

Mangling the data a bit

Great! Now we have enough data that we can wrangle it a bit to highlight the bits we care about. For me, I’m not willing to take a “burn it all” approach to deleting stuff I’ve already watched, because there are certain shows that I’d like to be able to see again and I’m not ready to part with. But, I’d like to at least do what I can to help some of it float to the top.

In my case, I store the full path of the smb share in the database (it helps with multiple XBMC’s running), so firstly I need to strip that out with some sed.

# echo “SELECT * FROM episodeview WHERE playCount IS NOT NULL;”|mysql -B -u root -p -D MyVideos75 | awk -F’\t’ ‘{print $21 “|” $28 “|” $30 “|” $31}’ | sed ‘s/smb:\/\/MY-IP-ADDRESS//g’ > /tmp/watched.log

Coming up next — Parsing this data to find and delete shows, and then updating the XBMC Database to reflect these changes!

Leave a Reply

Your email address will not be published. Required fields are marked *