Joab Jackson, MusicDBIngest program

What it does: This Perl command-line run program will extract information in the iTunes XML library and insert it in a database (where it then can be further analyzed). Specifically, for each entry from the "iTunes Music Library.xml" file , it extracts the Name, Track ID, Artist, Album, Genre, Year, Date Added, Play Count, Play Date UTC and Rating fields. It discards any audiobook, podcast or radio entries. All this information is placed into a MySQL database (I used MySQL v. 5.1 when building this program).

Prequisites: Perl must be install in the machine. The program must be place in the same folder as the "iTunes Music Library.xml" file. This version of the program inserst the data into a table called "collection" in a database called "music."

Collection requires the following fields with the following data types, in this order: key (int(11)), name (varchar(150)), artist (varchar(100)), album (varchar(150)), genre (varchar(30)), year (int(11)), date_added (date), play_count (int(11)), last_played (datetime), rating (int((11)). The key is the primary key and must be non-null.

Here are some queries you could use against the database:

Total number of songs: select count(*) from collection;

Total number of albums: select count(*) from (Select album from collection group by album) as derived;

List of albums, with artist: select album, artist from collection group by album order by album;

List of songs, with artist, album: select song, artist, album from collection order by song;

Songs per artist: select artist, count(*) from collection group by artist;

Songs per year: select year, count(*) from collection group by year;

Songs by year added: select DATE_FORMAT(date_added, '%Y') as year, count(*) from collection group by year;

Songs per genre: select count(*), genre from collection group by genre;

Songs per artist (Top 100): select artist, count(*) from collection group by artist order by count(*) desc limit 100;

Songs per genre: select genre, count(*) from collection group by genre;

Plays per artist (Top 100): select artist, sum(play_count) as total_plays from collection group by artist order by total_plays desc limit 100;

Plays per genre: select genre, sum(play_count) as total_plays from collection group by genre order by total_plays desc;

Plays per album (Top 100): select album, sum(play_count) as total_plays from collection group by album order by total_plays desc limit 100;

Average plays per artist (Top 100): select artist, sum(play_count)/count(*) as total_plays from collection group by artist order by total_plays desc limit 100;

Download

View the source code

iTunes Music Ingester by Joab Jackson is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License.

iTunes is an Apple trademark.

Home