Cleaning the XBMC movie collection

I have a lot of movies in t he XBMC server and not all of those are worth watching. Generally, anything with a rating lower than 5 is probably not worth the time!

So I wanted to get a list of bad movies with ratings! You can set up XBMC to store the data in an SQL database, and that’s how I set it up. Mainly because I want to share the database between the multiple XBMC set-ups around the house. Both the SQL database and the regular SQLite database support queries, though this is tested on MySQL.

To execute a MySQL query, you need to log into the database somehow, probably phpmysql or commandline (# mysql -u root -p )  to execute the query, but if you managed to set up XBMC for MySQL, you probably won’t need help there.

All the data you need is in the database and you can find out all about how the database is defined on the XBMC wiki: XBMC databases

The two tables you need are the movie database for a list of your movies, ratings, and any other info. And the info table for information about the file name. The table names are not very self explanatory and you really need the database reference wiki page to get anywhere.  For example, the following is the movie table:

Column Name Data Type Description
idMovie integer Primary Key
c00 text Local Movie Title
c01 text Movie Plot
c02 text Movie Plot Outline
c03 text Movie Tagline
c04 text Rating Votes
c05 text Rating
c06 text Writers
c07 text Year Released
c08 text Thumbnails
c09 text IMDB ID
c10 text Title formatted for sorting
c11 text Runtime [UPnP devices see this as seconds]
c12 text MPAA Rating
c13 text [unknown – listed as Top250]
c14 text Genre
c15 text Director
c16 text Original Movie Title
c17 text [unknown – listed as Thumbnail URL Spoof]
c18 text Studio
c19 text Trailer URL
c20 text Fanart URLs
c21 text Country (Added in r29886[1]
c23 text idPath
idFile integer Foreign Key to files table

 

Next is putting it all together into a pretty SQL querry

select c00,c05,strFilename from movie join files on movie.idFile = files.idFile where c05 < 5 and c05 > 0;

This lists all the movies with a rating lower than 5, because nobody likes a bad movie. And higher than 0 because a 0 rating apparently only happens when the movie is not found. That being said, you might consider a second query to find out what movies aren’t recognized correctly 🙂

I have a fairly good idea where my files are so I didn’t need the file paths, but if you do, you can get to those by merging the “path” table into the query and adding “strPath” to the select.

And then I opened a browser and deleted all the waste of time junk.

 

As a next step, I looked into the options for cleaning the database and triggering the updates from the command line. The Event Server can do this, but there appear to be some issues, installing the package using the official Ubuntu repository removes the XBMC package; slightly weird and definitely worth looking into, but not today.

 

And for some reason, I like to kill XBMC. And it has 2 processes. And it needs a “kill -9” by the time I feel like killing. Sooooooooo, I put it into a simple bash script.

kill -9 $( ps -A | grep xbmc | grep -oE ” [0-9]* ” )

 

Hope this helps, use it at your own risk, etc, and feel free to get back with feedback!

Published by Gert

Person-at-large.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: