#!/usr/bin/perl -w
use MyDB::Main;
use strict;
use Data::Dump qw( dump );
my $schema = MyDB::Main->connect('dbi:SQLite:example.db');
my $dbh = $schema->storage->dbh;
$dbh->do(
qq{
CREATE TABLE artist (
artistid INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);}
) or die;
$dbh->do(
qq{
CREATE TABLE cd (
cdid INTEGER PRIMARY KEY AUTOINCREMENT,
artist INTEGER NOT NULL REFERENCES artist(artistid),
title TEXT NOT NULL
);
}
) or die;
$dbh->do(
qq{
CREATE TABLE track (
trackid INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL
);
}
) or die;
$dbh->do(
qq{
CREATE TABLE cd_track_join (
id INTEGER PRIMARY KEY AUTOINCREMENT,
cdid INTEGER NOT NULL REFERENCES cd(cdid),
trackid INTEGER NOT NULL REFERENCES track(trackid)
);
}
) or die;
# here's some of the sql that is going to be generated by the schema
# INSERT INTO artist VALUES (NULL,'Michael Jackson');
# INSERT INTO artist VALUES (NULL,'Eminem');
my @artists = ( ['Michael Jackson'], ['Eminem'] );
$schema->populate( 'Artist', [ [qw/name/], @artists, ] );
my %albums = (
'Thriller' => 'Michael Jackson',
'Bad' => 'Michael Jackson',
'The Marshall Mathers LP' => 'Eminem',
);
my @cds;
foreach my $lp ( sort keys %albums ) {
my $artist
= $schema->resultset('Artist')->search( { name => $albums{$lp} } );
push @cds, [ $lp, $artist->first->artistid ];
}
#warn dump \@cds;
$schema->populate( 'Cd', [ [qw/title artist/], @cds, ] );
my %tracks = (
'Beat It' => 'Thriller',
'Billie Jean' => 'Thriller',
'Dirty Diana' => 'Bad',
'Smooth Criminal' => 'Bad',
'Leave Me Alone' => 'Bad',
'Stan' => 'The Marshall Mathers LP',
'The Way I Am' => 'The Marshall Mathers LP',
);
$schema->populate( 'Track', [ ['title'], map { [$_] } sort keys %tracks ] );
my @tracks;
foreach my $track_title ( sort keys %tracks ) {
my $cdid = $schema->resultset('Cd')
->search( { title => $tracks{$track_title}, } )->first->cdid;
my $trackid
= $schema->resultset('Track')->find( { title => $track_title } )
->trackid;
push @tracks, [ $cdid, $trackid ];
}
$schema->populate( 'CdTrackJoin', [ [qw/cdid trackid/], @tracks, ] );