[mythtv-users] List of recordings by disc drive

[Stephen Worthington]

On Sun, 08 Feb 2015 22:11:52 -0500, you wrote:

>
>I wonder if someone has already written a MySQL query to extract 
>a list of recordings by disc drive?
>
>When I am using Dolphin to manage a device, the file name 
>appears to be a channel number plus a time stamp.  Having to 
>play the video for minutes to identify the show/movie and 
>episode is tedious.  It would be helpful to have a list that I 
>could look up 1382_2012011303000.mpg and find that it is Back To 
>The Future IV, or the Simpsons episode where Homer becomes 
>intelligent.
>
>The ideal query would extract a file with the:  device, file 
>name, and the recording information (series name, episode 
>title...).
>
>I've been reluctant to write my own, as I'm concerned about 
>unintentionally modifying the database.
>
>Has anyone done this who would like to share it?  If not, maybe 
>a hint about the tables to access and linkage?
>
>Mike

An SQL select statement is unable to ever alter the database, so they
are pretty safe to use.  The only thing to watch out for is if you are
recording and you do a very long query (usually one that does a join
on unindexed fields in large tables).  That can make the drive the
database is no so busy that a recording going to the same drive fails.

Do not use the other SQL commands (eg delete, update, drop) which
alter the database and you should be fine.

Finding where a recording file is stored can be a problem as MythTV
does not store the path in the database, just the filename.  It checks
all the storage group paths in turn until it finds the file.  I get
around that by having the mlocate package installed.  That keeps a
database that is updated daily of where every file on the system is
stored.  The I can use the locate command to find a MythTV file.  For
example:

root at mypvr:~# locate 1001_20110206185900.mpg
/home/stephen/.mythtv/remotecache/1001_20110206185900.mpg.png
/mnt/rec3boot3/home/stephen/.mythtv/remotecache/1001_20110206185900.mpg.png
/mnt/rec5/recordings/1001_20110206185900.mpg
/mnt/rec5/recordings/1001_20110206185900.mpg.-1.100x75.png
/mnt/rec5/recordings/1001_20110206185900.mpg.png

That method does not work for recordings done too recently as the
mlocate database will not have been updated.  You can force the
database update to happen by using the updatedb command, but it takes
a while for a full scan of all your drives to be done.

It is also possible to write a batch script that takes a filename and
looks up the database for where the storage groups are and looks for
the file in each possible location.  I have always meant to sit down
and write one for myself, but have never gotten around to doing it.
Let me know if you would like me to do that.