itunes music library database

itunes.sql

/* create tables to store itunes music library data for quick access 
   - rkumar 2010 July
*/
drop table tracks;
create table tracks (
album_artist VARCHAR(50),
album_rating_computed INTEGER,
album_rating VARCHAR(50),
album VARCHAR(50),
all_items VARCHAR(50),
application_version VARCHAR(50),
artist VARCHAR(50),
artwork_count INTEGER,
bpm INTEGER,
bit_rate INTEGER,
comments VARCHAR(50),
compilation VARCHAR(50),
composer VARCHAR(50),
date_added DATE,
date_modified DATE,
disabled VARCHAR(50),
disc_count INTEGER,
disc_number INTEGER,
distinguished_kind INTEGER,
features INTEGER,
file_folder_count INTEGER,
file_type INTEGER,
genre VARCHAR(50),
hd VARCHAR(50),
has_video VARCHAR(50),
kind VARCHAR(50),
library_folder_count INTEGER,
library_persistent_id VARCHAR(100),
location VARCHAR(100),
major_version INTEGER,
minor_version INTEGER,
movie VARCHAR(50),
music_folder VARCHAR(50),
name VARCHAR(100),
persistent_id INTEGER,
play_count INTEGER,
play_date_utc DATE,
play_date INTEGER,
playlist_id INTEGER,
playlist_items VARCHAR(50),
playlist_persistent_id VARCHAR(100),
part_of_gapless_album VARCHAR(50),
rating INTEGER,
sample_rate INTEGER,
show_content_ratings VARCHAR(50),
size INTEGER,
skip_count INTEGER,
skip_date DATE,
sort_album VARCHAR(50),
sort_artist VARCHAR(50),
sort_composer VARCHAR(50),
sort_name VARCHAR(50),
stop_time INTEGER,
total_time INTEGER,
track_count INTEGER,
track_id INTEGER PRIMARY KEY,
track_number INTEGER,
track_type VARCHAR(50),
tracks VARCHAR(50),
video_width INTEGER,
video_height INTEGER,
volume_adjustment INTEGER,
year INTEGER );

drop table playlists;
CREATE TABLE playlists (name VARCHAR(100), track_id INTEGER);

Little bit of a noob question... I've created the itunes.sqlite file (I've never worked with sqlite before), but when using your iTunes parsing script, I keep getting:

./xml2db.rb:46:in `': itunes.sqlite must exist with tables in it - use http://gist.github.com/508400 (RuntimeError)

I'm running "sqlite3 itunes.sqlite" and pasting the queries. Am I doing something wrong?

I guess since you are running this script the first time, the table does not exist, so "drop table" is failing. I suggest you remove the TWO "drop table" commands and then run the script. HTH.

Hello, I'm getting the same error, that 'itunes.sqlite must exist with tables in it' but I have created the itunes.sqlite file (both using the SQL Manager add-in in Firefox and the sqlite3 command line) but in both cases, even though the itunes.sqlite exist, in the same directory as xml2db.rb, the error is raised that they don't exist. Oh, and they do have both the tracks and playlists tables. Any suggestions?