#!/usr/bin/perl
use strict;
use DBI;

my $dbh = DBI->connect('DBI:mysql:database=ytunes;host=shaolin.wccnet.org',
                       "rex", "NO");


my %COLUMN_NAMES;
my $sth = $dbh->prepare('show columns from ytunes_mass');
$sth->execute;
while (my ($col) = $sth->fetchrow_array){
  $COLUMN_NAMES{$col} = 1;
}


my @insert_keys = keys %COLUMN_NAMES;

my $sth_insert = $dbh->prepare(
  "insert into ytunes_mass (" .
                join(',', @insert_keys) .
		") values (" .
		join(',', map { '?' } @insert_keys). ")"
);


my $file = "/Users/rex/Music/iTunes/iTunes Music Library.xml";
die unless ( -r $file );
open (F, $file) or die $!;

my %data_hash;

while (<F>) {
  chomp;
  next unless (m!<key>([^<]+)</key><(?:string|integer|date)>([^<]+)</(?:string|integer|date)>!);
  my ($key, $value) = ($1, $2);

  $key = lc($key);
  $key =~ s/ /_/g;
  
  if ( $key =~ m/track_id/ ) {
      if ( $data_hash{'kind'} =~ m/MPEG/ ) {
         insert_into_db(\%data_hash);
      }
    undef %data_hash;
  } 

  $data_hash{$key} = $value;

}

close F;

sub insert_into_db {
  my $hashref = shift;
  my %insert_data;

  foreach my $k ( @insert_keys ) {
    $insert_data{$k} = undef;
  }

  foreach my $k ( keys %{$hashref} ) {
    $insert_data{$k} = $hashref->{$k};
  }

  # print $hashref->{track_id}, " --\n";
  $sth_insert->execute(@insert_data{@insert_keys});
  # foreach my $k ( keys %{$hashref} ) {
  #   print "$k -> ", $hashref->{$k}, $/;
  # }
}