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!
blog.cone 