use strict;

use DBI;

# connect
my $dbh = DBI->connect(
   qw(DBI:mysql:mp3:host user pass),
   { RaiseError => 1 }
);

# get existing data as one big ole 2-d array
my $songs = $dbh->selectall_arrayref("
   select artist,album,title,year
   from songs
");

# munge data into new data structure
my %hash;
foreach my $row (@$songs) {
   my ($artist,$album,$title,$year) = @$row;

   # auto-vivification is what makes this all so cool
   push @{$hash{$artist}->{$album}->{'list'}},$title;

   # getting the year is tricky - since i know they
   # are all the same - i can safely or-cache it ;)
   $hash{$artist}->{$album}->{'year'} ||= $year;
}

foreach my $artist (keys %hash) {

   # insert this artist - get unique id for albums
   $dbh->prepare("
      insert into artist(name) 
      values(?)
   ")->execute($artist);
   my $artist_id = last_id();

   foreach my $album (keys %{$hash{$artist}}) {
      my $year = $hash{$artist}->{$album}->{'year'};

      # insert this album - get unique id for songs
      $dbh->prepare("
         insert into album(title,year,artist_id)
         values(?,?,?)
      ")->execute($album,$year,$artist_id);
      my $album_id = last_id();

      foreach my $song (@{$hash{$artist}->{$album}->{'list'}}) {

         # insert this song
         $dbh->prepare("
            insert into song(title,album_id)
            values(?,?)
         ")->execute($song,$album_id);
      }
   }

}

$dbh->disconnect;

# mysql specific - other vendor's milleage will vary
sub last_id {
   $dbh->selectall_arrayref("
      select LAST_INSERT_ID()
   ")->[0]->[0]
}