Difference between revisions of "Mythadder.py"

From MythTV Official Wiki
Jump to: navigation, search
m
 
(14 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
{{wrongtitle|mythadder.py}}
 
{{wrongtitle|mythadder.py}}
−
I've got a large collection 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 create a [http://code.google.com/p/mythadder/ solution]. This 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.
+
{{Script info
 +
|author=Pestilence
 +
|short=archive storage manager for mythvideo
 +
|long=A python script and UDEV rule intended to manage automatic insertion and deletion of content on removable media into MythVideo.
 +
|category=MythVideo
 +
|file=mythadder.py
 +
|S23=yes
 +
|S231=yes
 +
|S24=yes}}
  
−
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.
+
{{VersionNote|.22|For the .22 version, go [http://code.google.com/p/mythadder/ here]}}
 +
I've got a large collection of hard drives full of HD 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.
  
−
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.
+
I decided to create a [http://code.google.com/p/mythadder/ solution]. This 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.
−
 
 
−
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.
+
They appear in mythvideo and you can update metadata, download fanart and whatnot. When you remove the drive, the script launches again and copies the updated metadata back into its own table and removes the videometadata rows.  That way, MythTv doesn't freak out when it can't find the files.
  
−
Also, keep in mind that I wrote this not expecting for it to be incorporated into MythTV, so it's intentionally designed to stay out of the way and hopefully survive upgrades without much hassle.  I would love for it to make it into MythTV, however.
+
It tracks files by partition uuid and inode number, so you can rename the files on the drives and the drives can come up on different mount points without confusing it. It also handles new files and deleted files without any problem.
−
 
 
−
 
 
−
== 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>
 
−
}}
 
  
 +
One other benefit is that it keeps a complete list of all the video on your external drives in its table. I'm going to be adding a page to mythweb to browse that eventually.
  
 
== udev rule ==
 
== udev rule ==
Line 89: Line 47:
 
#
 
#
  
−
# add your video file extensions here
+
# to turn off logging, use 'none'
−
extensions = [".avi",".mkv",".ts",".m2ts",".mpg",".mp4"]
+
loglevel = 'important,general'
−
 
+
logFile = '/var/log/mythtv/mythadder'
−
# 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
 
# seconds to wait for mount after udev event
 
mountWait  = 10  
 
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.
 
# 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.
 
# This is my python 'hello world', so be gentle.
 +
 +
MASCHEMA = 1001
  
 
#
 
#
Line 113: Line 64:
  
 
import os
 
import os
 +
import sys
 
import commands
 
import commands
 
import re
 
import re
 
import time
 
import time
−
import MySQLdb
+
from MythTV import MythDB, MythLog, Video
  
−
def doLog(logFile, output):
+
LOG = MythLog(module='mythadder.py', lstr=logLevel)
−
if logFile:
+
if logFile:
−
FILE = open(logFile,"a")
+
    LOG.LOGFILE = open(logFile, 'a')
−
FILE.writelines(output)
 
  
−
FILE.close()
+
def prepTable(db):
 +
    if db.settings.NULL['mythadder.DBSchemaVer'] is None:
 +
        # create new table
 +
        c = db.cursor()
 +
        c.execute("""
 +
            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;""")
 +
        c.close()
 +
        db.settings.NULL['mythadder.DBSchemaVer'] = MASCHEMA
 +
    elif db.settings.NULL['mythadder.DBSchemaVer'] > MASCHEMA:
 +
        # schema is too new, exit
 +
        sys.exit(1)
 +
    else:
 +
        while db.settings.NULL['mythadder.DBSchemaVer'] < MASCHEMA:
 +
            # if schema == some version
 +
            # perform these tasks
 +
            break
  
−
output = []
 
 
inodes = []
 
inodes = []
  
Line 132: Line 131:
 
uuid  = os.environ.get('ID_FS_UUID',False)
 
uuid  = os.environ.get('ID_FS_UUID',False)
 
label  = os.environ.get('ID_FS_LABEL',False)
 
label  = os.environ.get('ID_FS_LABEL',False)
−
 
−
doLog(logFile, '\n' + time.ctime() + '\n')
 
  
 
if device:
 
if device:
−
doLog(logFile, action + ' ' + device + ' ' + label + ' ' + uuid + '\n')
+
    LOG(LOG.IMPORTANT, "%s %s" % (device, action), "%s at %s" % (label, uuid))
  
−
#
+
    #
−
# the drive is connected
+
    # the drive is connected
−
#
+
    #
−
if action == 'add':
+
    if action == 'add':
−
# connect to db
+
        # connect to db
−
try:
+
        try:
−
db = MySQLdb.connect(host = dbHost, user = dbUser, passwd = dbPassword, db = dbDatabase)
+
            db = MythDB()
−
except MySQLdb.Error, e:
+
            prepTable(db)
−
doLog(logFile, "Error %d: %s" % (e.args[0], e.args[1]))
+
        except Exception, e:
 +
            LOG(LOG.IMPORTANT, e.args[0])
 +
            sys.exit(1)
  
−
cursor = db.cursor()
+
        cursor = db.cursor()
  
−
regex = re.compile(device)
+
        regex = re.compile(device)
−
time.sleep(mountWait) # wait a few seconds until the drive is mounted
+
        time.sleep(mountWait) # wait a few seconds until the drive is mounted
−
mount_output = commands.getoutput('mount -v')
+
        mount_output = commands.getoutput('mount -v')
−
for line in mount_output.split('\n'):
+
        for line in mount_output.split('\n'):
−
if regex.match(line):
+
            if regex.match(line):
−
mount_point = line.split(' type ')[0].split(' on ')[1]
+
                mount_point = line.split(' type ')[0].split(' on ')[1]
−
doLog(logFile, 'mounted at ' + mount_point + '\n')
+
                LOG(LOG.IMPORTANT, "Disk mounted at "+mountpoint)
  
−
for directory in os.walk(mount_point):
+
        cursor.execute("""SELECT extension FROM videotypes WHERE f_ignore=0""")
−
for file in directory[2]:
+
        extensions = zip(*cursor.fetchall())[0]
−
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')
+
        for directory in os.walk(mount_point):
 +
            for file in directory[2]:
 +
                if file.rsplit('.',1)[1] in extensions:
 +
                    thisFile = directory[0] + '/' + file
 +
                    thisBasename = os.path.basename(thisFile)
 +
                    thisInode = str(os.stat(thisFile).st_ino)
  
−
inodes.append(thisInode)
+
                    LOG(LOG.IMPORTANT, "File found at inode "+thisInode, thisFile)
 +
                    inodes.append(thisInode)
 
 
−
# insert each file that matches our extensions or update if it's already in the table
+
                    # insert each file that matches our extensions or update if it's already in the table
−
sql = """
+
                    sql = """
−
INSERT INTO  
+
                            INSERT INTO  
−
z_removablevideos  
+
                                z_removablevideos  
−
SET partitionuuid = %s  
+
                            SET partitionuuid = %s  
−
,partitionlabel = %s  
+
                                ,partitionlabel = %s  
−
,fileinode = %s  
+
                                ,fileinode = %s  
−
,intid = 0  
+
                                ,intid = 0  
−
,title = %s  
+
                                ,title = %s  
−
,subtitle = ''  
+
                                ,subtitle = ''  
−
,director = ''  
+
                                ,director = ''  
−
,rating = ''  
+
                                ,rating = ''  
−
,inetref = ''  
+
                                ,inetref = ''  
−
,year = 0  
+
                                ,year = 0  
−
,userrating = 0.0  
+
                                ,userrating = 0.0  
−
,showlevel = 1  
+
                                ,showlevel = 1  
−
,filename = %s  
+
                                ,filename = %s  
−
,coverfile = ''  
+
                                ,coverfile = ''  
−
,host = ''  
+
                                ,host = ''  
−
ON DUPLICATE KEY UPDATE  
+
                            ON DUPLICATE KEY UPDATE  
−
partitionlabel = %s  
+
                                partitionlabel = %s  
−
,filename = %s;"""
+
                                ,filename = %s;"""
−
#doLog(logFile, sql  %  (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile) + '\n')
+
                    try:
−
try:
+
                        cursor.execute(sql, (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile))
−
cursor.execute(sql, (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile))
+
                    except Exception, e:
−
except MySQLdb.Error, e:
+
                        LOG(LOG.IMPORTANT, e.args[0])
−
doLog(logFile, "Error %d: %s" % (e.args[0], e.args[1]))
 
  
−
break
+
        inodeList = ','.join(inodes)
−
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)
 
 
−
# delete any rows for files that were deleted from the disk
+
        try:
−
# there seems to be a bug in the mysql package that fails to handle the
+
            cursor.execute(sql)
−
# tuples for this query because of the inode list so we're letting python do the substitution here
+
        except MySQLdb.Error, e:
−
sql = """
+
            LOG(LOG.IMPORTANT, e.args[0])
−
DELETE FROM  
+
 
−
z_removablevideos  
+
        # insert anything from our table that already has an id from mythtv
−
WHERE
+
        sql = """
−
partitionuuid = '%s' AND
+
            INSERT INTO videometadata (
−
fileinode NOT IN (%s) ;""" % (uuid,  inodeList)
+
                intid
−
#doLog(logFile, sql  + '\n')
+
                ,title
−
try:
+
                ,subtitle
−
cursor.execute(sql)
+
                ,director
−
except MySQLdb.Error, e:
+
                ,plot
−
doLog(logFile, "Error %d: %s" % (e.args[0], e.args[1]))
+
                ,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 ;"""  
 +
        try:
 +
            cursor.execute(sql, (uuid))
 +
        except Exception, e:
 +
            LOG(LOG.IMPORTANT, e.args[0])
  
−
# insert anything from our table that already has an id from mythtv
+
        # 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 = """
+
        sql = """
−
INSERT INTO videometadata (
+
            SELECT
−
intid
+
                title
−
,title
+
                ,subtitle
−
,subtitle
+
                ,director
−
,director
+
                ,plot
−
,plot
+
                ,rating
−
,rating
+
                ,inetref
−
,inetref
+
                ,year
−
,year
+
                ,userrating
−
,userrating
+
                ,length
−
,length
+
                ,season
−
,season
+
                ,episode
−
,episode
+
                ,showlevel
−
,showlevel
+
                ,filename
−
,filename
+
                ,coverfile
−
,coverfile
+
                ,childid
−
,childid
+
                ,browse
−
,browse
+
                ,watched
−
,watched
+
                ,playcommand
−
,playcommand
+
                ,category
−
,category
+
                ,trailer
−
,trailer
+
                ,host
−
,host
+
                ,screenshot
−
,screenshot
+
                ,banner
−
,banner
+
                ,fanart
−
,fanart
+
                ,insertdate
−
,insertdate)
+
                ,fileinode
−
SELECT
+
            FROM  
−
intid
+
                z_removablevideos  
−
,title
+
            WHERE
−
,subtitle
+
                partitionuuid = %s AND
−
,director
+
                intid = 0 ;"""  
−
,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
+
        try:
−
sql = """
+
            cursor.execute(sql,  (uuid))
−
SELECT
+
            data = cursor.fetchall()
−
title
+
        except Exception, e:
−
,subtitle
+
            LOG(LOG.IMPORTANT, e.args[0])
−
,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
+
        # insert one row from new videos and capture the id it gets assigned
−
sql = """
+
        sql = """
−
INSERT INTO videometadata (
+
            INSERT INTO videometadata (
−
title
+
                title
−
,subtitle
+
                ,subtitle
−
,director
+
                ,director
−
,plot
+
                ,plot
−
,rating
+
                ,rating
−
,inetref
+
                ,inetref
−
,year
+
                ,year
−
,userrating
+
                ,userrating
−
,length
+
                ,length
−
,season
+
                ,season
−
,episode
+
                ,episode
−
,showlevel
+
                ,showlevel
−
,filename
+
                ,filename
−
,coverfile
+
                ,coverfile
−
,childid
+
                ,childid
−
,browse
+
                ,browse
−
,watched
+
                ,watched
−
,playcommand
+
                ,playcommand
−
,category
+
                ,category
−
,trailer
+
                ,trailer
−
,host
+
                ,host
−
,screenshot
+
                ,screenshot
−
,banner
+
                ,banner
−
,fanart
+
                ,fanart
−
,insertdate)
+
                ,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);
+
            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;"""  
+
            SELECT LAST_INSERT_ID() AS intid;"""  
−
#doLog(logFile, sql + '\n')
+
        for row in data:
−
for row in data :
+
            try:
−
#doLog(logFile, row + '\n')
+
                cursor.execute(sql, row)
−
try:
+
            except Exception, e:
−
# I can't believe it's 2010 and the basic mysql library for python doesn't put results into an associative array
+
                LOG(LOG.IMPORTANT, e.args[0])
−
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] ))
+
            cursor.nextset()
−
except MySQLdb.Error, e:
+
            intid = cursor.fetchone()[0]
−
doLog(logFile, "Error %d: %s" % (e.args[0], e.args[1]))
+
 
−
cursor.nextset()
+
            # update our table with the intid from mythtv so we can remove the rows when the drive is disconnected
−
intid = cursor.fetchone()[0]
+
            sql2 = """
−
+
                UPDATE z_removablevideos
−
# update our table with the intid from mythtv so we can remove the rows when the drive is disconnected
+
                SET intid = %s
−
sql2 = """
+
                WHERE partitionuuid = %s AND fileinode = %s
−
UPDATE z_removablevideos
+
            """
−
SET intid = %s
+
            try:
−
WHERE partitionuuid = %s AND fileinode = %s
+
                cursor.execute(sql2, (intid,  uuid, row[25]))
−
"""
+
            except Exception, e:
−
try:
+
                LOG(LOG.IMPORTANT, e.args[0])
−
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.
+
    # the drive is being removed.
−
#
+
    #
−
if action == 'remove':
+
    if action == 'remove':
−
# connect to db
+
        # connect to db
−
try:
+
        try:
−
db = MySQLdb.connect(host = dbHost, user = dbUser, passwd = dbPassword, db = dbDatabase)
+
            db = MythDB()
−
except MySQLdb.Error, e:
+
            prepTable(db)
−
doLog(logFile, "Error %d: %s" % (e.args[0], e.args[1]))
+
        except Exception, e:
 +
            LOG(LOG.IMPORTANT, e.args[0])
  
−
cursor = db.cursor()
+
        cursor = db.cursor()
 
 
−
# update everything in our table to catch metadata changes done inside mythtv
+
        # update everything in our table to catch metadata changes done inside mythtv
−
sql = """
+
        sql = """
−
UPDATE  
+
            UPDATE  
−
z_removablevideos rv,  videometadata vm
+
                z_removablevideos rv,  videometadata vm
−
SET
+
            SET
−
rv.title = vm.title
+
                rv.title = vm.title
−
,rv.subtitle = vm.subtitle
+
                ,rv.subtitle = vm.subtitle
−
,rv.director = vm.director
+
                ,rv.director = vm.director
−
,rv.plot = vm.plot
+
                ,rv.plot = vm.plot
−
,rv.rating = vm.rating
+
                ,rv.rating = vm.rating
−
,rv.inetref = vm.inetref
+
                ,rv.inetref = vm.inetref
−
,rv.year = vm.year
+
                ,rv.year = vm.year
−
,rv.userrating = vm.userrating
+
                ,rv.userrating = vm.userrating
−
,rv.length = vm.length
+
                ,rv.length = vm.length
−
,rv.season = vm.season
+
                ,rv.season = vm.season
−
,rv.episode = vm.episode
+
                ,rv.episode = vm.episode
−
,rv.showlevel = vm.showlevel
+
                ,rv.showlevel = vm.showlevel
−
,rv.filename = vm.filename
+
                ,rv.filename = vm.filename
−
,rv.coverfile = vm.coverfile
+
                ,rv.coverfile = vm.coverfile
−
,rv.childid = vm.childid
+
                ,rv.childid = vm.childid
−
,rv.browse = vm.browse
+
                ,rv.browse = vm.browse
−
,rv.watched = vm.watched
+
                ,rv.watched = vm.watched
−
,rv.playcommand = vm.playcommand
+
                ,rv.playcommand = vm.playcommand
−
,rv.category = vm.category
+
                ,rv.category = vm.category
−
,rv.trailer = vm.trailer
+
                ,rv.trailer = vm.trailer
−
,rv.host = vm.host
+
                ,rv.host = vm.host
−
,rv.screenshot = vm.screenshot
+
                ,rv.screenshot = vm.screenshot
−
,rv.banner = vm.banner
+
                ,rv.banner = vm.banner
−
,rv.fanart = vm.fanart
+
                ,rv.fanart = vm.fanart
−
WHERE  
+
            WHERE  
−
rv.intid = vm.intid AND
+
                rv.intid = vm.intid AND
−
rv.partitionuuid = %s;"""
+
                rv.partitionuuid = %s;"""
−
try:
+
        try:
−
cursor.execute(sql, (uuid))
+
            cursor.execute(sql, uuid)
−
except MySQLdb.Error, e:
+
        except Exception, e:
−
doLog(logFile,  "Error %d: %s" % (e.args[0], e.args[1]))
+
            LOG(LOG.IMPORTANT, e.args[0])
−
 
 
−
# 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)
+
        # 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:
 +
            LOG(LOG.IMPORTANT, e.args[0])
  
  

Latest revision as of 01:52, 12 November 2010

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



Author Pestilence
Description A python script and UDEV rule intended to manage automatic insertion and deletion of content on removable media into MythVideo.
Supports Version23.png  Version231.png Version24.png  


Version:
.22
 For the .22 version, go here

I've got a large collection of hard drives full of HD 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 create a solution. This 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. When you remove the drive, the script launches again and copies the updated metadata back into its own table and removes the videometadata rows. That way, MythTv 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 and the drives can come up on different mount points without confusing it. It also handles new files and deleted files without any problem.

One other benefit is that it keeps a complete list of all the video on your external drives in its table. I'm going to be adding a page to mythweb to browse that eventually.

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
#

# to turn off logging, use 'none'
loglevel = 'important,general'
logFile = '/var/log/mythtv/mythadder'

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

# 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.

MASCHEMA = 1001

#
# code
#

import os
import sys
import commands
import re
import time
from MythTV import MythDB, MythLog, Video

LOG = MythLog(module='mythadder.py', lstr=logLevel)
if logFile:
    LOG.LOGFILE = open(logFile, 'a')

def prepTable(db):
    if db.settings.NULL['mythadder.DBSchemaVer'] is None:
        # create new table
        c = db.cursor()
        c.execute("""
            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;""")
        c.close()
        db.settings.NULL['mythadder.DBSchemaVer'] = MASCHEMA
    elif db.settings.NULL['mythadder.DBSchemaVer'] > MASCHEMA:
        # schema is too new, exit
        sys.exit(1)
    else:
        while db.settings.NULL['mythadder.DBSchemaVer'] < MASCHEMA:
            # if schema == some version
            # perform these tasks
            break

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)

if device:
    LOG(LOG.IMPORTANT, "%s %s" % (device, action), "%s at %s" % (label, uuid))

    #
    # the drive is connected
    #
    if action == 'add':
        # connect to db
        try:		
            db = MythDB()
            prepTable(db)
        except Exception, e:
            LOG(LOG.IMPORTANT, e.args[0])
            sys.exit(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]
                LOG(LOG.IMPORTANT, "Disk mounted at "+mountpoint)

        cursor.execute("""SELECT extension FROM videotypes WHERE f_ignore=0""")
        extensions = zip(*cursor.fetchall())[0]

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

                    LOG(LOG.IMPORTANT, "File found at inode "+thisInode, thisFile)
                    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;"""
                    try:
                        cursor.execute(sql, (uuid, label,  thisInode,  thisBasename,  thisFile,  label,  thisFile))
                    except Exception, e:
                        LOG(LOG.IMPORTANT, e.args[0])

        inodeList = ','.join(inodes)
		
        # 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)
		
        try:
            cursor.execute(sql)
        except MySQLdb.Error, e:
            LOG(LOG.IMPORTANT, e.args[0])

        # 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 ;""" 
        try:
            cursor.execute(sql, (uuid))
        except Exception, e:
            LOG(LOG.IMPORTANT, e.args[0])

        # 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 ;""" 

        try:
            cursor.execute(sql,  (uuid))
            data = cursor.fetchall()
        except Exception, e:
            LOG(LOG.IMPORTANT, e.args[0])

        # 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;""" 
        for row in data:
            try:
                cursor.execute(sql, row)
            except Exception, e:
                LOG(LOG.IMPORTANT, e.args[0])
            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 Exception, e:
                LOG(LOG.IMPORTANT, e.args[0])

    #
    # the drive is being removed.
    #
    if action == 'remove':
        # connect to db
        try:
            db = MythDB()
            prepTable(db)		
        except Exception, e:
            LOG(LOG.IMPORTANT, e.args[0])

        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 Exception, e:
            LOG(LOG.IMPORTANT, e.args[0])

        # 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:
            LOG(LOG.IMPORTANT, e.args[0])
Retrieved from "http://www.mythtv.org/wiki?title=Mythadder.py&oldid=49975"