found drama

As this project expands and meets head-on with this neat little nuance of Apple’s “plist” ish, I find myself at a fun little x-roads. To start with, there’s a whole lot of MySQL and PHP being re-learned at the moment. The funny part is, being an iTunes user, so much of my music collection is already quite well coded, accessible, and searchable. Why bother with creating a database version of it? Or re-inventing an XML standard to mark it up? Or anything along those lines?

Because it seemed like the right thing to do.

Anyway… To briefly re-cap: Creating a meta-data system for music libraries was easy and frankly kind of fun. Take the XML idea, whip up a fairly comprehensive DTD and blamo! But it’s no fun w/o wrapping it all up into a MySQL database, is it? So define a few tables and get yourself off and running!

A few tables? (Well, it wouldn’t make sense to just store it all in one table, would it?)

Perhaps the point all along was to create more work for myself — to create projects within projects… Once I’d set up the tables in the database, I said to myself Why bother entering 5,394+ songs by hand? Recall, iTunes stores most (if not all) of the relevant information at:

• Mac OS X: ~/Music/iTunes/iTunes Music Library.xml
• Windows: …\My Documents\My Music\iTunes\iTunes Music Library.xml

What this means in practical terms is that I’ve already entered all those data. And so why re-enter it or copy/paste it 5000 times? Instead, would it not be a far better idea to code up some kind of XSL stylesheet that can run on the file and output something that can then be fed into MySQL via something along the lines of LOAD DATA INFILE 'records.cdf' INTO TABLE songs FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Or else have it spit out (NULL, 'blah', 'blah', 'blah', NULL) type lines and figure out some way of having the system use those.

So, we came up with the iTunesLib2SQL.xsl XSL stylesheet… (credit where credit is due, much of this was gleaned from this example … but since I hate Perl so damn much…)

Go ahead, give it a whirl. OS X’ers out there, make sure you’ve got Xalan and then stab this out:
$ java org.apache.xalan.xslt.Process -IN ~/Music/iTunes/iTunes\ Music\ Library.xml -XSL ~/Library/XSL/iTunesLib2SQL.xsl -OUT ~/Desktop/iTunesLib.txt
(where “~/Library/XSL/iTunesLib2SQL.xsl” is wherever you dropped the shared XSTL file…) (sorry Winfolks… don’t do Java on XP!)

Any of that look familiar?

So yeah… I’m pretty stoked about this so far. Not terribly difficult. And fun fun fun. Not sure about useful yet but whatever. “Useful” is for someone that gives a damn. Current room for growth:

1. Converting iTunes ratings to single-digit integer values that work w/ this database format  DONE
2. Figuring out how to get certain characters escaped during the XSL transform. It’d be nice if I got this far and then didn’t have the whole thing come crashing down around me b/c a song name has an apostrophe in it. Or an artist that goes by ‘Frankie “0”‘. Or carriage returns that snuck into the comments field. Or something like that.
3. Getting the output album title to translate into the corresponding “album_id” for when the INSERT statement actually runs on that track…
4. Likewise for the “artist_id”.
5. Coming up with an efficient function that will detect the missing artists and album titles and create them (along with their IDs) on-the-fly.
6. Controlling for silly screw-ups in the original data entry such as “Zero 7” vs. “Zero 7 “… (/sigh)
7. Some other crap I haven’t thought of yet…

CURRENTLY PLAYING: mix by Dan Cuthbert & Timo Rotonen — “Cranberry Juice and Ice” (check the download about halfway down that linked page)

About Rob Friesel

3 Responses to iTunes2MySQL2…