Created
August 29, 2013 15:13
-
-
Save jeffa/6379389 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use strict; | |
use warnings; | |
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] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment