Perl

Program

Program: DB Music Ingester
Version: 0.1

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 inserts the data into a table called "iTunes" in a database called "music."

To create the iTunes table in MySQL run this command within MySQL:

create table iTunes(TrackID int NOT NULL, name varchar(150), artist varchar(100), album varchar(150), genre varchar(30), year int, date_added date, play_count int, last_played datetime, rating int, PRIMARY KEY(TrackID));

Here are some queries you could use against the database:
  • Total number of songs: select count(*) from iTunes;
  • Total number of albums: select count(*) from (Select album from iTunes group by album) as derived;
  • List of albums, with artist: select album, artist from iTunes group by album order by album;
  • List of songs, with artist, album: select song, artist, album from iTunes order by song;
  • Songs per artist: select artist, count(*) from iTunes group by artist;
  • Songs per year: select year, count(*) from iTunes group by year;
  • Songs by year added: select DATE_FORMAT(date_added, '%Y') as year, count(*) from iTunes group by year;
  • Songs per genre: select count(*), genre from iTunes group by genre;
  • Songs per artist (Top 100): select artist, count(*) from iTunes group by artist order by count(*) desc limit 100;
  • Songs per genre: select genre, count(*) from iTunes group by genre;
  • Plays per artist (Top 100): select artist, sum(play_count) as total_plays from iTunes group by artist order by total_plays desc limit 100;
  • Plays per genre: select genre, sum(play_count) as total_plays from iTunes group by genre order by total_plays desc;
  • Plays per album (Top 100): select album, sum(play_count) as total_plays from iTunes 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 iTunes group by artist order by total_plays desc limit 100;
  • Download or view code

    
    
    
    
    

    Back