MDL: Maintain the XBMC Library

One of the most frustrating things about XBMC is the static nature of its library.  The forums are full of people asking “How do you force the XBMC library to refresh?”  Many of them are asking specifically about the thumbnails and fanart.  My biggest concern is the metadata.

I have a mild case of OCD and am constantly tweaking the information about my movie collection.  Unfortunately, XBMC doesn’t ever pick up this information.  You can refresh movies manually, one by one.  But that process is tedious at best.

You can also delete the database and rebuild it.  But that kind of defeats the purpose of having a library, doesn’t it?  If you go this path you lose watch history, ratings and other information.

I think that I have developed a script that accomplishes what I need.  It removes just enough information to force re-population without getting rid of the things that I care about.  I currently have it setup to run once per week, which should be more than sufficient for my needs.

The script is in PowerShell, but shouldn’t be overly difficult to rewrite in just about anything.  A couple notes:

a) I am not worried about refreshing pictures here.  Though that can be accomplished pretty easily by deleting the thumbnail cache.

b) Use at your own risk.  I am not 100% certain that this is stable in all circumstances.  I’ve run it a bunch on my setup, but make no guarantees what it will do to yours.

c) I’m pretty sure its inefficient, I am not a programmer by nature.  If you find methods to improve it, please do so and post your results back here.

# You should be able to edit here and the rest of the script should work
# With the very important exception of the section marked !!!!LOOK AT ME!!!! below.
$xbmcuserid = "user"
$xbmcpassword = "pass"
$xbmcIP = "192.168.1.2"
$xbmcPort = "8000"
$mySQLIP = "192.168.1.2"
$url = "http://" + $xbmcIP + ":" + $xbmcPort + "/jsonrpc"
$xbmcDB = "xbmc_video"

# Connect to the mySQL database
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$XBMCConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$XBMCConnection.ConnectionString = "database=" + $xbmcDB + ";server=" + $mySQLIP + ";Persist Security Info=false;user id=" + $xbmcuserid + ";pwd=" + $xbmcpassword
$XBMCConnection.Open()

# Make a copy of the movie table
$copyCommand = $XBMCConnection.CreateCommand()
$copyCommand.CommandText = "CREATE TABLE movie_copy SELECT * FROM movie"
$copyCommand.ExecuteNonQuery()

# Clean out the movie table
$cleanCommand = $XBMCConnection.CreateCommand()
$cleanCommand.CommandText = "DELETE FROM movie WHERE idMovie > 0"
$cleanCommand.ExecuteNonQuery()

# !!!!!!!!!!!!!!LOOK AT ME!!!!!!!!!!!!!!
# You are going to need to clean out the hashes associated with the movies or the scan will just skip past everything
# You can clear out all of the hashes (movies and TVs) but that will cause everything to rescan
# I prefer to just scan the stuff that I care about YMMV
# !!!!!!!!!!!!!!LOOK AT ME!!!!!!!!!!!!!!
$cleanHashCommand = $XBMCConnection.CreateCommand()
$cleanHashCommand.CommandText = "UPDATE path` SET `strHash` = '0' WHERE strPath like '%Movies/%';"
$cleanHashCommand.ExecuteNonQuery()

# Ask XBMC to look for new videos - this will reload the NFOs into the database
$command = '{"jsonrpc": "2.0", "method": "VideoLibrary.Scan", "id": 1}'
$bytes = [System.Text.Encoding]::ASCII.GetBytes($command)
$web = [System.Net.WebRequest]::Create($url)
$web.Method = "POST"
$web.ContentLength = $bytes.Length
$web.ContentType = "application/x-www-form-urlencoded"
$stream = $web.GetRequestStream()
$stream.Write($bytes,0,$bytes.Length)
$stream.close()
$reader = New-Object System.IO.Streamreader -ArgumentList $web.GetResponse().GetResponseStream()
$reader.ReadToEnd()
$reader.Close()

# Wait for 30 minutes, this is probably me being lazy. 
# I can't think of a way to see if the scanning has been completed or not
start-sleep -s 1800

# Reset the movie ids back to what they were. 
# This is required if you a) care about Recent Movies being accurate.  b) you use sets c) probably something else too
$ResetIDCommand = $XBMCConnection.CreateCommand()
$ResetIDCommand.CommandText = "UPDATE " + $xbmcDB + ".movie_copy t1, " + $xbmcDB + ".movie t2 SET t2.idMovie=t1.idMovie WHERE t1.idFile = t2.idFile"
$ResetIDCommand.ExecuteNonQuery()

# Drop the backed up table
$DropCommand = $XBMCConnection.CreateCommand()
$DropCommand.CommandText = "DROP TABLE " + $xbmcDB + ".movie_copy"
$DropCommand.ExecuteNonQuery()

# Clean the database
# Don't know if this is necessary, but it can't hurt, right?
$command = '{"jsonrpc": "2.0", "method": "VideoLibrary.Clean", "id": 1}'
$bytes = [System.Text.Encoding]::ASCII.GetBytes($command)
$web = [System.Net.WebRequest]::Create($url)
$web.Method = "POST"
$web.ContentLength = $bytes.Length
$web.ContentType = "application/x-www-form-urlencoded"
$stream = $web.GetRequestStream()
$stream.Write($bytes,0,$bytes.Length)
$stream.close()
$reader = New-Object System.IO.Streamreader -ArgumentList $web.GetResponse().GetResponseStream()
$reader.ReadToEnd()
$reader.Close()