Difference between revisions of "Mythadder.py"

From MythTV Official Wiki
Jump to: navigation, search
(udev rule)
Line 11: Line 11:
  
 
I've only tried it on my own system, so I can only prove it works on Ubuntu with udev, python, and the python mysql library with USB drives.
 
I've only tried it on my own system, so I can only prove it works on Ubuntu with udev, python, and the python mysql library with USB drives.
 +
 +
I realize (now) that this isn't using the Python bindings.  I learned of their existence after I wrote this.
 +
 +
 +
== table definition ==
 +
{{Code box|z_removablevideos.sql|
 +
<pre>
 +
--
 +
-- Table structure for table `z_removablevideos`
 +
--
 +
 +
CREATE TABLE IF NOT EXISTS `z_removablevideos` (
 +
  `partitionuuid` varchar(100) NOT NULL,
 +
  `partitionlabel` varchar(50) NOT NULL,
 +
  `fileinode` int(11) NOT NULL,
 +
  `intid` int(10) unsigned NOT NULL,
 +
  `title` varchar(128) NOT NULL,
 +
  `subtitle` text NOT NULL,
 +
  `director` varchar(128) NOT NULL,
 +
  `plot` text,
 +
  `rating` varchar(128) NOT NULL,
 +
  `inetref` varchar(255) NOT NULL,
 +
  `year` int(10) unsigned NOT NULL,
 +
  `userrating` float NOT NULL,
 +
  `length` int(10) unsigned NOT NULL,
 +
  `season` smallint(5) unsigned NOT NULL default '0',
 +
  `episode` smallint(5) unsigned NOT NULL default '0',
 +
  `showlevel` int(10) unsigned NOT NULL,
 +
  `filename` text NOT NULL,
 +
  `coverfile` text NOT NULL,
 +
  `childid` int(11) NOT NULL default '-1',
 +
  `browse` tinyint(1) NOT NULL default '1',
 +
  `watched` tinyint(1) NOT NULL default '0',
 +
  `playcommand` varchar(255) default NULL,
 +
  `category` int(10) unsigned NOT NULL default '0',
 +
  `trailer` text,
 +
  `host` text NOT NULL,
 +
  `screenshot` text,
 +
  `banner` text,
 +
  `fanart` text,
 +
  `insertdate` timestamp NULL default CURRENT_TIMESTAMP,
 +
  PRIMARY KEY  (`partitionuuid`,`fileinode`),
 +
  KEY `director` (`director`),
 +
  KEY `title` (`title`),
 +
  KEY `partitionuuid` (`partitionuuid`)
 +
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 +
 +
</pre>
 +
}}
 +
  
 
== udev rule ==
 
== udev rule ==

Revision as of 12:03, 17 April 2010

Important.png Note: The correct title of this article is mythadder.py. It appears incorrectly here due to technical restrictions.

I've got a large collection (like 40) of hard drives full of high def content I recorded from cable before 5c encryption ruined the fun. I've always wanted a way to keep track of it and watch it from within mythvideo.

I decided to learn python and code up a solution. What I wrote is a script that's launched by udev when a USB drive is attached, scans the mount point for video files, inserts rows into a separate table in the mythtv db, then copies those rows into the videometadata table.

They appear in mythvideo and you can update metadata, download fanart and whatnot, and when you remove the drive, the script launches again. It then copies the updated metadata back into its own table and removes the rows it created from myth's own videometadata table so it doesn't freak out when it can't find the files.

It tracks files by partition uuid and inode number, so you can rename the files on the drives without confusing it. It also handles new files and deleted files without any problem.

One other benefit is that it leaves me with a complete list of all the video on my external drives. I'm going to be adding a page to mythweb to browse that catalog eventually.

I've only tried it on my own system, so I can only prove it works on Ubuntu with udev, python, and the python mysql library with USB drives.

I realize (now) that this isn't using the Python bindings. I learned of their existence after I wrote this.


table definition

Script.png z_removablevideos.sql

--
-- Table structure for table `z_removablevideos`
--

CREATE TABLE IF NOT EXISTS `z_removablevideos` (
  `partitionuuid` varchar(100) NOT NULL,
  `partitionlabel` varchar(50) NOT NULL,
  `fileinode` int(11) NOT NULL,
  `intid` int(10) unsigned NOT NULL,
  `title` varchar(128) NOT NULL,
  `subtitle` text NOT NULL,
  `director` varchar(128) NOT NULL,
  `plot` text,
  `rating` varchar(128) NOT NULL,
  `inetref` varchar(255) NOT NULL,
  `year` int(10) unsigned NOT NULL,
  `userrating` float NOT NULL,
  `length` int(10) unsigned NOT NULL,
  `season` smallint(5) unsigned NOT NULL default '0',
  `episode` smallint(5) unsigned NOT NULL default '0',
  `showlevel` int(10) unsigned NOT NULL,
  `filename` text NOT NULL,
  `coverfile` text NOT NULL,
  `childid` int(11) NOT NULL default '-1',
  `browse` tinyint(1) NOT NULL default '1',
  `watched` tinyint(1) NOT NULL default '0',
  `playcommand` varchar(255) default NULL,
  `category` int(10) unsigned NOT NULL default '0',
  `trailer` text,
  `host` text NOT NULL,
  `screenshot` text,
  `banner` text,
  `fanart` text,
  `insertdate` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`partitionuuid`,`fileinode`),
  KEY `director` (`director`),
  KEY `title` (`title`),
  KEY `partitionuuid` (`partitionuuid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


udev rule

Script.png /etc/udev/rules.d/99-mythadder.rules

SUBSYSTEM=="block", ENV{DEVTYPE}=="partition", RUN+="/usr/bin/python /usr/bin/mythadder.py"

Source code

PythonIcon.png mythadder.py

#!/usr/bin/python
# mythadder - automatically add video files on removable media to the mythvideo database upon connect/mount
# and remove them on disconnect.  Your distro should be set up to automount usb storage within 'mountWait' seconds after
# connection.
#
# requires udev and a rule like - SUBSYSTEM=="block", ENV{DEVTYPE}=="partition", RUN+="/usr/bin/python /usr/bin/mythadder.py"
# to launch it - there's a .rules file in this archive you can use
#
# requires the python mysqldb library.  on ubuntu, apt-get install python python-mysqldb.
#

#
# configuration section
#

# add your video file extensions here
extensions = [".avi",".mkv",".ts",".m2ts",".mpg",".mp4"]

# to turn off logging, use False (no quotes) rather than this string
# this default assumes that /var/log/mythtv exists
logFile	= "/var/log/mythtv/mythadder" 

# seconds to wait for mount after udev event
mountWait  = 10 

#database config - CHANGE THIS TO REFLECT YOUR CONFIGURATION
dbHost	 = '192.168.1.1'
dbUser	 = 'mythtv'
dbPassword = 'supersecretpasswordofdoomseriouslyhuuuuurrrrrrr'
dbDatabase = 'mythconverg'

# Don't change anything below this unless you are a real python programmer and I've done something really dumb.
# This is my python 'hello world', so be gentle.

#
# code
#

import os
import commands
import re
import time
import MySQLdb

def doLog(logFile, output):
	if logFile:
		FILE = open(logFile,"a")
		FILE.writelines(output)

		FILE.close()

output = []
inodes = []

device = os.environ.get('DEVNAME',False)
action = os.environ.get('ACTION',False)
uuid   = os.environ.get('ID_FS_UUID',False)
label  = os.environ.get('ID_FS_LABEL',False)

doLog(logFile, '\n' + time.ctime() + '\n')

if device:
	doLog(logFile, action + ' ' + device + ' ' + label + ' ' + uuid + '\n')

	#
	# the drive is connected
	#
	if action == 'add':
		# connect to db
		try:		
			db = MySQLdb.connect(host = dbHost, user = dbUser, passwd = dbPassword, db = dbDatabase)
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		cursor = db.cursor()

		regex = re.compile(device)
		time.sleep(mountWait) # wait a few seconds until the drive is mounted
		mount_output = commands.getoutput('mount -v')
		for line in mount_output.split('\n'):
			if regex.match(line):
				mount_point = line.split(' type ')[0].split(' on ')[1]
				doLog(logFile, 'mounted at ' + mount_point + '\n')

		for directory in os.walk(mount_point):
			for file in directory[2]:
				for ext in extensions:
					if file.endswith(ext):
						thisFile = directory[0] + '/' + file
						thisBasename = os.path.basename(thisFile)
						thisInode = str(os.stat(thisFile).st_ino)

						output.append('found file at inode ' + thisInode + ':' + thisFile + '\n')

						inodes.append(thisInode)
						
						# insert each file that matches our extensions or update if it's already in the table
						sql = """
							INSERT INTO 
								z_removablevideos 
							SET partitionuuid = %s 
								,partitionlabel = %s 
								,fileinode = %s 
								,intid = 0 
								,title = %s 
								,subtitle = '' 
								,director = '' 
								,rating = '' 
								,inetref = '' 
								,year = 0 
								,userrating = 0.0 
								,showlevel = 1 
								,filename = %s 
								,coverfile = '' 
								,host = '' 
							ON DUPLICATE KEY UPDATE 
								partitionlabel = %s 
								,filename = %s;"""
						#doLog(logFile, sql  %  (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile) + '\n')
						try:
							cursor.execute(sql, (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile))
						except MySQLdb.Error, e:
							doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

						break
		inodeList = ','.join(inodes)
		#doLog(logFile, inodeList)
		
		# delete any rows for files that were deleted from the disk
		# there seems to be a bug in the mysql package that fails to handle the 
		# tuples for this query because of the inode list so we're letting python do the substitution here
		sql = """
			DELETE FROM 
				z_removablevideos 
			WHERE
				partitionuuid = '%s' AND
				fileinode NOT IN (%s) ;""" % (uuid,  inodeList)
		#doLog(logFile, sql  + '\n')
		try:
			cursor.execute(sql)
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		# insert anything from our table that already has an id from mythtv
		sql = """
			INSERT INTO videometadata (
				intid 
				,title
				,subtitle
				,director
				,plot
				,rating
				,inetref
				,year
				,userrating
				,length
				,season
				,episode
				,showlevel
				,filename
				,coverfile
				,childid
				,browse
				,watched
				,playcommand
				,category
				,trailer
				,host
				,screenshot
				,banner
				,fanart
				,insertdate)	
			SELECT
				intid 
				,title
				,subtitle
				,director
				,plot
				,rating
				,inetref
				,year
				,userrating
				,length
				,season
				,episode
				,showlevel
				,filename
				,coverfile
				,childid
				,browse
				,watched
				,playcommand
				,category
				,trailer
				,host
				,screenshot
				,banner
				,fanart
				,insertdate
			FROM
				z_removablevideos
			WHERE
				partitionuuid = %s AND
				intid != 0 ;""" 
		#doLog(logFile, sql % (uuid) + '\n')
		try:
			cursor.execute(sql, (uuid))
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		# get all our rows that have never been in mythtv before so we can insert them one at a time and capture the resulting mythtv id
		sql = """
			SELECT 				
				title
				,subtitle
				,director
				,plot
				,rating
				,inetref
				,year
				,userrating
				,length
				,season
				,episode
				,showlevel
				,filename
				,coverfile
				,childid
				,browse
				,watched
				,playcommand
				,category
				,trailer
				,host
				,screenshot
				,banner
				,fanart
				,insertdate
				,fileinode
			FROM 
				z_removablevideos 
			WHERE
				partitionuuid = %s AND
				intid = 0 ;""" 
		#doLog(logFile, sql % (uuid) + '\n')
		try:
			cursor.execute(sql,  (uuid))
			data = cursor.fetchall()
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		# insert one row from new videos and capture the id it gets assigned
		sql = """
			INSERT INTO videometadata (
				title
				,subtitle
				,director
				,plot
				,rating
				,inetref
				,year
				,userrating
				,length
				,season
				,episode
				,showlevel
				,filename
				,coverfile
				,childid
				,browse
				,watched
				,playcommand
				,category
				,trailer
				,host
				,screenshot
				,banner
				,fanart
				,insertdate)
			VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
			
			SELECT  LAST_INSERT_ID() AS intid;""" 
		#doLog(logFile, sql + '\n')
		for row in data :
			#doLog(logFile, row + '\n')
			try:
				# I can't believe it's 2010 and the basic mysql library for python doesn't put results into an associative array
				cursor.execute(sql, (row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24] ))
			except MySQLdb.Error, e:
				doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))
			cursor.nextset()
			intid = cursor.fetchone()[0]
			
			# update our table with the intid from mythtv so we can remove the rows when the drive is disconnected
			sql2 = """
				UPDATE z_removablevideos
				SET intid = %s
				WHERE partitionuuid = %s AND fileinode = %s
			"""
			try:
				cursor.execute(sql2, (intid,  uuid, row[25]))
			except MySQLdb.Error, e:
				doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

	#
	# the drive is being removed.
	#
	if action == 'remove':
		# connect to db
		try:		
			db = MySQLdb.connect(host = dbHost, user = dbUser, passwd = dbPassword, db = dbDatabase)
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		cursor = db.cursor()
		
		# update everything in our table to catch metadata changes done inside mythtv
		sql = """
			UPDATE 
				z_removablevideos rv,  videometadata vm
			SET
				rv.title = vm.title
				,rv.subtitle = vm.subtitle
				,rv.director = vm.director
				,rv.plot = vm.plot
				,rv.rating = vm.rating
				,rv.inetref = vm.inetref
				,rv.year = vm.year
				,rv.userrating = vm.userrating
				,rv.length = vm.length
				,rv.season = vm.season
				,rv.episode = vm.episode
				,rv.showlevel = vm.showlevel
				,rv.filename = vm.filename
				,rv.coverfile = vm.coverfile
				,rv.childid = vm.childid
				,rv.browse = vm.browse
				,rv.watched = vm.watched
				,rv.playcommand = vm.playcommand
				,rv.category = vm.category
				,rv.trailer = vm.trailer
				,rv.host = vm.host
				,rv.screenshot = vm.screenshot
				,rv.banner = vm.banner
				,rv.fanart = vm.fanart
			WHERE 
				rv.intid = vm.intid AND
				rv.partitionuuid = %s;"""
		try:
			cursor.execute(sql, (uuid))
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))

		# and finally delete all the rows in mythtv that match rows in our table for the drive being removed
		sql = """
			DELETE  
				vm
			FROM
				videometadata vm, z_removablevideos rv
			WHERE 
				rv.intid = vm.intid AND
				rv.partitionuuid = %s;"""
		try:
			cursor.execute(sql, (uuid))
		except MySQLdb.Error, e:
			doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))


doLog(logFile, output)