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()

Why Scalia's Broccoli Analogy Wasn't Stupid

Mostly because Scalia wasn't making an analogy - he was challenging the government to define a limiting principle.  If it is OK for the government to force someone to buy insurance at what point is it not OK for the government to force someone to buy something that it feels is important?


Scalia may have well have asked - if Congress thinks that forcing citizens to purchase yo-yos because Congress believes that every citizen owning yo-yos will prevent an invasion by little purple men from Alpha Centauri would that be Constitutional?  If it is not Constitutional what is the distinguishing difference between the two cases?

In other words - at what point is Congress prohibited from telling someone that they must buy something?  "Health care is special" is not a limiting principle because it opens the courts to infinite justifications around 'this market is special because:' type of arguments.  For example, yo-yos are special because of their unique place in prohibiting invasion by little-purple men from Alpha Centauri.

Question of the Day

Why are CEO's held liable for any action that their company takes (Sarbanes-Oxley), but public officials aren't even responsible for their own actions (qualified immunity)?

http://www.scotusblog.com/about/petition-of-the-day-explained/

Please God No

I got an email from Consumer Reports today advocating tighter regulation of medical devices by the FDA.  Here is my response with a copy of the original email below:

This approach would simply raise the costs and increase the time associated with getting medical devices into the hands of the people that need them most.  We have already seen how the FDA approval process for drugs has slowed innovation and increased time to market by decades.  The last thing in the world that I want to see is this failed model super-imposed on medical devices.

_________________________________

Dear ,

It's a nightmare scenario. The implant that fixed your knee or your heart may actually be a ticking time bomb that could disable or kill you.

This isn't science fiction. Millions of medical devices including artificial hips, contact lens solution, heart stents, and pacemakers are being recalled - 700 different products a year.

And the vast majority of recalled products were never safety tested in humans, because the manufacturers claimed they were "similar" to products already on the market.

Tell Congress we don't want to be guinea pigs anymore!

Every new prescription drug must undergo rigorous testing on humans before it can be sold, even if it is similar to another drug already in use. Not so with medical devices. Because of this loophole in our safety laws, more than 90 percent of medical devices aren't safety tested before being sold nor are they routinely tracked afterwards to identify safety problems.

For example, a metal hip implant marketed by Johnson & Johnson was approved in 2005 without first undergoing clinical safety trials. It was recalled five years later after having a 1 in 8 failure rate in the UK, and releasing potentially toxic metals into the body. Countless patients had to undergo a second, painful 'revision' surgery.

It's time safety standards for medical devices are as strong as those for prescription drugs! E-mail your members now!

The device industry has unleashed an army of lobbyists and they don't want things to change. It will take a wave of consumers weighing in to make sure medical devices are safe and effective. Please take action, then forward this to friends and family. Thank you!

Sincerely,

Jim Guest

President, Consumer Reports

101 Truman Ave

Yonkers, NY 10703