mythtv and mythvideo

A while ago my main hard drive died. Of course I had lots of backups, but nothing that I could boot so I reinstalled the OS. Since I was reinstalling I took the opportunity to install the latest ubuntu Karmic Koala. It all went well and I slowly rebuilt my machine setting things up how I like. First I got mail (postfix) and my own version of Mailman set up. Then it was on to the websites and then I took a break. These last three days I've been battling trying to get all my movies back to a viewable state. I was using XBMC before. I was hampered by some of the magic tricks I had learned in the past to get tv out working on my cheap radeon card. Here's what I learned:

• you need to dpkg --purge gdm to stop any kind of X starting when the machine boots
• you no longer need fglrx - don't even think of trying it, it's a red herring
• you need to add vga=789 to /etc/default/grub:GRUB_CMDLINE_LINUX_DEFAULT to get /dev/fb0
• you need to usermod -a -G audio $USER and then log out an back in to be able to use the soundcard
• aplay -l lists all your soundcards
• lspci and lsusb are really useful too
• ONLY gnome desktop appears to activate the TV out properly, any other window manager just seems to hang startx
•  /usr/bin/xrandr --output S-video --set load_detection 1 --size 800x600 is also needed after the X server has started (10 seconds) to make TV out happen
• xbmc is  slow useless monkey and dead to me
• mythtv and mythvideo are the new hotness and my new best friend
• mythtv and mythvideo just work and are awesome
• you can run mythfrontend over X11 for setting up metadata but not play movies like that
• when getting metadata hit 'w' to get it
• if that fails enter some of the title
• you can easily download coverart from amazon and upload it scp $(\ls -t | head -1)  server:/var/lib/mythtv/coverart/uploaded/
• I have /Movies be only directories and symlinks to the real data files living on lots of mounted volumes.
• If you move a symlink around all your metadata is lost. see below
• I am way too anal about getting all my metadata set up - at least good titles and coverart
• You can access the database directly and mess around with values
• backup your database!
• mysqldump --defaults-file=~/lib/mysql/.my.cnf.mythtv --opt mythconverg  >mtv.$(date +%F).sql
• I must find all my old mame roms for the myth mame plugin to play things!
• it works with lirc but you need a ~/.lircrc and then you symlink to that from ~/.mythtv/
• you can run dvdshrink under wine and it actually works!

So I had most of my metadata set up but my directory structure was very flat. Moving symlinks around would loose my metadata so I wrote a quick program to allow me to move links around and keep the database up to date. Of course this little script may totally bork YOUR setup, so be careful. This is super pre-alpha at this point, I might polish it up later.

Have a look at mythvideomv.py you probably want to click on 'raw file' to get the actual file.

MySQL select optimization

This came up at work but the lessons are so general I'm putting it here in my public blog rather than my work one.

work out how your table is setup
-- SHOW CREATE TABLE tablename;

work out how much space your table is taking up
-- SHOW TABLE STATUS LIKE 'tablename';

work out which tables are using the most space (naive)
-- 'show table status \G' | egrep 'Name|Data_length:' | while read fil; do read s; echo "${s/Data_length: /} $fil"; done | sort -n

now explain your sql query
-- EXPLAIN SELECT date , SUM(number) AS number
FROM tablename
WHERE date >= '2005-04-06' AND date <= '2005-04-09' AND metric <= 7 GROUP BY date; Look at the rows column in that output and try and make some new keys that will help make it smaller. Once you do you need to add another index -- ALTER TABLE tablename ADD INDEX (days, platform); while you're doing this you can monitor progress by having a look in /var/lib/mysql/databasename/
there should be some # files
$ \ls -Fsh \#* tablename.* | cat
12K #sql-1abd_8.frm
261M #sql-1abd_8.MYD
136M #sql-1abd_8.MYI
12K tablename.frm
343M tablename.MYD
135M tablename.MYI
when the # files approch the size of the tablename files you know it's close to being done.

None of that helped me much, what DID speed up my queries was getting rid of the <= operators. This involves making two other tables one with dates in them and another with numbers DROP TABLE IF EXISTS dates; CREATE TABLE dates ( date date NOT NULL default '0000-00-00', PRIMARY KEY (date) ) TYPE=MyISAM; -- fill it like this -- for fil in $(seq 0 5000); do D=$(date +%F -d "2000-01-01 +$fil days"); -- echo "INSERT into dates (date) values ('$D');"; done | mysql blah blah blah and DROP TABLE IF EXISTS numbers; CREATE TABLE numbers ( value int unsigned NOT NULL default 0, PRIMARY KEY (value) ) TYPE=MyISAM; INSERT into numbers (value) values (0); INSERT into numbers (value) values (1); INSERT into numbers (value) values (2); INSERT into numbers (value) values (3);... then that query becomes: EXPLAIN SELECT t.date as date, SUM(t.number) AS number FROM tablename t, dates d, numbers n WHERE d.date >= '2005-04-06' AND d.date <= '2005-04-09'
AND n.value <= 7
AND d.date = t.date AND d.number = n.value
GROUP BY date;

which should be MUCH faster.

iTunes to MySQL

I've finally got something that will deal with my 43Meg iTunes Library.xml file! I found iTunes.py over at lazycat.org which I've modified to also load playlist as well as track information. It uses the most excellent pulldom python API which attempts to be more memory efficient than loading the whole dom into memory.

I then interfaced to a MySQL database with MySQLdb which was a breeze. I created a schema which would allow me to store more than one person's iTunes Library in the same database, this could lead to some interesting data mining! The SQL used to create the database is provided in sql.sql

The XML says it's UTF-8 put pulldom gets confused when it found some characters in it so I made it 'sanitize' the XML by stripping out non-ASCII characters, I know this isn't strictly a good thing, but I'll spend more time on that once I have it working more.

It's controlled by a .itdb.config file that you keep in your home directory an example one of those is provided too. I also provided an example small iTunes.xml library for development work. Loading that is a lot quicker than trying to reload your 40Meg XML file.

It's been tested on Mac OS X and Linux.

You can find the source over at:
http://code.google.com/p/wtwf/source/browse/trunk/itdb

Now I'm working on something to do something with the data. I'm using cheetah to write out HTML with data from the database.

Here's a sneaky peak at some of the expected output.


Of course once it's finished (or a little better) I'll be posting source here.