X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FExample.pod;h=fe2cf9ef123c226512fa44e066272009316ffc5a;hb=48580715af3072905f2c71dc27e7f70f21a11338;hp=c31c739039c0cc59e089a50660a27edc9df68967;hpb=022e08938d8fed4bbfc4a9d2e7c00f54a48c7bd1;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Example.pod b/lib/DBIx/Class/Manual/Example.pod index c31c739..fe2cf9e 100644 --- a/lib/DBIx/Class/Manual/Example.pod +++ b/lib/DBIx/Class/Manual/Example.pod @@ -4,290 +4,370 @@ DBIx::Class::Manual::Example - Simple CD database example =head1 DESCRIPTION -This tutorial will guide you through the proeccess of setting up and testing a very basic CD database using Mysql, with DBIx::Class::Schema as the database frontend. +This tutorial will guide you through the process of setting up and +testing a very basic CD database using SQLite, with DBIx::Class::Schema +as the database frontend. The database consists of the following: - table 'artist' with columns: artistid, name - table 'cd' with columns: cdid, artist, title - table 'track' with columns: trackid, cd, title + table 'artist' with columns: artistid, name + table 'cd' with columns: cdid, artist, title + table 'track' with columns: trackid, cd, title And these rules exists: - one artist can have many cds - one cd belongs to one artist - one cd can have many tracks - one track belongs to one cd + one artist can have many cds + one cd belongs to one artist + one cd can have many tracks + one track belongs to one cd =head2 Installation -=head3 Create the database/tables and populate them with a few records +Install DBIx::Class via CPAN should be sufficient. - CREATE DATABASE cdtestdb ; - USE cdtestdb; +=head3 Create the database/tables + +First make and change the directory: + + mkdir app + cd app + mkdir db + cd db + +This example uses SQLite which is a dependency of DBIx::Class, so you +shouldn't have to install extra software. + +Save the following into a example.sql in the directory db CREATE TABLE artist ( - artistid INT NOT NULL AUTO_INCREMENT , - name CHAR( 40 ) NOT NULL , - PRIMARY KEY ( artistid ) + artistid INTEGER PRIMARY KEY, + name TEXT NOT NULL ); CREATE TABLE cd ( - cdid INT NOT NULL AUTO_INCREMENT , - artist INT NOT NULL , - title CHAR( 40 ) NOT NULL , - PRIMARY KEY ( cdid ) + cdid INTEGER PRIMARY KEY, + artist INTEGER NOT NULL REFERENCES artist(artistid), + title TEXT NOT NULL ); CREATE TABLE track ( - trackid INT NOT NULL AUTO_INCREMENT , - cd INT NOT NULL , - title CHAR( 40 ) NOT NULL , - PRIMARY KEY ( trackid ) - ; - - - INSERT INTO artist VALUES - (NULL,'Michael Jackson'), - (NULL,'Eminem'); - - INSERT INTO cd VALUES - (NULL,'1','Thriller'), - (NULL,'1','Bad'), - (NULL,'2','The Marshall Mathers LP'); - - INSERT INTO track VALUES - (NULL,'1','Beat it'), - (NULL,'1','Billie Jean'), - (NULL,'2','Dirty Diana'), - (NULL,'2','Smooth Criminal'), - (NULL,'2','Leave Me Alone'), - (NULL,'3','Stan'), - (NULL,'3','The Way I Am'); + trackid INTEGER PRIMARY KEY, + cd INTEGER NOT NULL REFERENCES cd(cdid), + title TEXT NOT NULL + ); +and create the SQLite database file: + sqlite3 example.db < example.sql =head3 Set up DBIx::Class::Schema -First, create some dirs and change working directory: +Change directory back from db to the directory app: + + cd ../ - mkdir app - mkdir app/DB - mkdir app/DB/Main - cd app +Now create some more directories: + + mkdir MyDatabase + mkdir MyDatabase/Main + mkdir MyDatabase/Main/Result + mkdir MyDatabase/Main/ResultSet - Then, create the following DBIx::Class::Schema classes: -DB/Main.pm: - - package DB::Main; - use base qw/DBIx::Class::Schema/; - __PACKAGE__->load_classes(qw/Artist CD Track/); +MyDatabase/Main.pm: + + package MyDatabase::Main; + use base qw/DBIx::Class::Schema/; + __PACKAGE__->load_namespaces; + + 1; + + +MyDatabase/Main/Result/Artist.pm: + + package MyDatabase::Main::Result::Artist; + use base qw/DBIx::Class::Core/; + __PACKAGE__->table('artist'); + __PACKAGE__->add_columns(qw/ artistid name /); + __PACKAGE__->set_primary_key('artistid'); + __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Result::Cd'); + + 1; + + +MyDatabase/Main/Result/Cd.pm: - 1; + package MyDatabase::Main::Result::Cd; + use base qw/DBIx::Class::Core/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime/); + __PACKAGE__->table('cd'); + __PACKAGE__->add_columns(qw/ cdid artist title/); + __PACKAGE__->set_primary_key('cdid'); + __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Result::Artist'); + __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Result::Track'); + 1; -DB/Main/Artist.pm: - package DB::Main::Artist; - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/Core/); - __PACKAGE__->table('artist'); - __PACKAGE__->add_columns(qw/ artistid name /); - __PACKAGE__->set_primary_key('artistid'); - __PACKAGE__->has_many('cds' => 'DB::Main::CD'); +MyDatabase/Main/Result/Track.pm: - 1; + package MyDatabase::Main::Result::Track; + use base qw/DBIx::Class::Core/; + __PACKAGE__->table('track'); + __PACKAGE__->add_columns(qw/ trackid cd title /); + __PACKAGE__->set_primary_key('trackid'); + __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Result::Cd'); + 1; -DB/Main/CD.pm: - package DB::Main::CD; - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/Core/); - __PACKAGE__->table('cd'); - __PACKAGE__->add_columns(qw/ cdid artist title/); - __PACKAGE__->set_primary_key('cdid'); - __PACKAGE__->belongs_to('artist' => 'DB::Main::Artist'); - __PACKAGE__->has_many('tracks' => 'DB::Main::Track'); - - 1; +=head3 Write a script to insert some records - -DB/Main/Track.pm: +insertdb.pl - package DB::Main::Track; - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/Core/); - __PACKAGE__->table('track'); - __PACKAGE__->add_columns(qw/ trackid cd title/); - __PACKAGE__->set_primary_key('trackid'); - __PACKAGE__->belongs_to('cd' => 'DB::Main::CD'); - - 1; + #!/usr/bin/perl -w + use MyDatabase::Main; + use strict; -=head3 Create and run the test script + my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db'); + + # 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 (keys %albums) { + my $artist = $schema->resultset('Artist')->find({ + name => $albums{$lp} + }); + push @cds, [$lp, $artist->id]; + } + + $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', + ); + + my @tracks; + foreach my $track (keys %tracks) { + my $cdname = $schema->resultset('Cd')->find({ + title => $tracks{$track}, + }); + push @tracks, [$cdname->id, $track]; + } + + $schema->populate('Track',[ + [qw/cd title/], + @tracks, + ]); + +=head3 Create and run the test scripts testdb.pl: - #!/usr/bin/perl -w - - use DB::Main; - use strict; - - my $schema = DB::Main->connect('dbi:mysql:cdtestdb', 'serval', 'TNOownage'); - - get_tracks_by_cd('Bad'); - get_tracks_by_artist('Michael Jackson'); - - get_cd_by_track('Stan'); - get_cds_by_artist('Michael Jackson'); - - get_artist_by_track('Dirty Diana'); - get_artist_by_cd('The Marshall Mathers LP'); - - - sub get_tracks_by_cd { - my $cdtitle = shift; - print "get_tracks_by_cd($cdtitle):\n"; - my $rs = $schema->resultset('Track')->search( - { - 'cd.title' => $cdtitle - }, - { - join => [qw/ cd /], - prefetch => [qw/ cd /] - } - ); - while (my $track = $rs->next) { - print $track->title . "\n"; - } - print "\n"; - } - - sub get_tracks_by_artist { - my $artistname = shift; - print "get_tracks_by_artist($artistname):\n"; - my $rs = $schema->resultset('Track')->search( - { - 'artist.name' => $artistname - }, - { - join => { - 'cd' => 'artist' - }, - } - ); - while (my $track = $rs->next) { - print $track->title . "\n"; - } - print "\n"; - } - - - - sub get_cd_by_track { - my $tracktitle = shift; - print "get_cd_by_track($tracktitle):\n"; - my $rs = $schema->resultset('CD')->search( - { - 'tracks.title' => $tracktitle - }, - { - join => [qw/ tracks /], - } - ); - my $cd = $rs->first; - print $cd->title . "\n\n"; - } - - sub get_cds_by_artist { - my $artistname = shift; - print "get_cds_by_artist($artistname):\n"; - my $rs = $schema->resultset('CD')->search( - { - 'artist.name' => $artistname - }, - { - join => [qw/ artist /], - prefetch => [qw/ artist /] - } - ); - while (my $cd = $rs->next) { - print $cd->title . "\n"; - } - print "\n"; - } - - - - sub get_artist_by_track { - my $tracktitle = shift; - print "get_artist_by_track($tracktitle):\n"; - my $rs = $schema->resultset('Artist')->search( - { - 'tracks.title' => $tracktitle - }, - { - join => { - 'cds' => 'tracks' - } - } - ); - my $artist = $rs->first; - print $artist->name . "\n\n"; - } - - sub get_artist_by_cd { - my $cdtitle = shift; - print "get_artist_by_cd($cdtitle):\n"; - my $rs = $schema->resultset('Artist')->search( - { - 'cds.title' => $cdtitle - }, - { - join => [qw/ cds /], - } - ); - my $artist = $rs->first; - print $artist->name . "\n\n"; - } + #!/usr/bin/perl -w + + use MyDatabase::Main; + use strict; + + my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db'); + # for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd + # driver, e.g perldoc L. + + get_tracks_by_cd('Bad'); + get_tracks_by_artist('Michael Jackson'); + + get_cd_by_track('Stan'); + get_cds_by_artist('Michael Jackson'); + + get_artist_by_track('Dirty Diana'); + get_artist_by_cd('The Marshall Mathers LP'); + + + sub get_tracks_by_cd { + my $cdtitle = shift; + print "get_tracks_by_cd($cdtitle):\n"; + my $rs = $schema->resultset('Track')->search( + { + 'cd.title' => $cdtitle + }, + { + join => [qw/ cd /], + } + ); + while (my $track = $rs->next) { + print $track->title . "\n"; + } + print "\n"; + } + + sub get_tracks_by_artist { + my $artistname = shift; + print "get_tracks_by_artist($artistname):\n"; + my $rs = $schema->resultset('Track')->search( + { + 'artist.name' => $artistname + }, + { + join => { + 'cd' => 'artist' + }, + } + ); + while (my $track = $rs->next) { + print $track->title . "\n"; + } + print "\n"; + } + + + sub get_cd_by_track { + my $tracktitle = shift; + print "get_cd_by_track($tracktitle):\n"; + my $rs = $schema->resultset('Cd')->search( + { + 'tracks.title' => $tracktitle + }, + { + join => [qw/ tracks /], + } + ); + my $cd = $rs->first; + print $cd->title . "\n\n"; + } + + sub get_cds_by_artist { + my $artistname = shift; + print "get_cds_by_artist($artistname):\n"; + my $rs = $schema->resultset('Cd')->search( + { + 'artist.name' => $artistname + }, + { + join => [qw/ artist /], + } + ); + while (my $cd = $rs->next) { + print $cd->title . "\n"; + } + print "\n"; + } + + + + sub get_artist_by_track { + my $tracktitle = shift; + print "get_artist_by_track($tracktitle):\n"; + my $rs = $schema->resultset('Artist')->search( + { + 'tracks.title' => $tracktitle + }, + { + join => { + 'cds' => 'tracks' + } + } + ); + my $artist = $rs->first; + print $artist->name . "\n\n"; + } + + sub get_artist_by_cd { + my $cdtitle = shift; + print "get_artist_by_cd($cdtitle):\n"; + my $rs = $schema->resultset('Artist')->search( + { + 'cds.title' => $cdtitle + }, + { + join => [qw/ cds /], + } + ); + my $artist = $rs->first; + print $artist->name . "\n\n"; + } It should output: - get_tracks_by_cd(Bad): - Dirty Diana - Smooth Criminal - Leave Me Alone + get_tracks_by_cd(Bad): + Dirty Diana + Smooth Criminal + Leave Me Alone + + get_tracks_by_artist(Michael Jackson): + Beat it + Billie Jean + Dirty Diana + Smooth Criminal + Leave Me Alone + + get_cd_by_track(Stan): + The Marshall Mathers LP + + get_cds_by_artist(Michael Jackson): + Thriller + Bad + + get_artist_by_track(Dirty Diana): + Michael Jackson + + get_artist_by_cd(The Marshall Mathers LP): + Eminem + +=head1 Notes - get_tracks_by_artist(Michael Jackson): - Beat it - Billie Jean - Dirty Diana - Smooth Criminal - Leave Me Alone +A reference implementation of the database and scripts in this example +are available in the main distribution for DBIx::Class under the +directory F. - get_cd_by_track(Stan): - The Marshall Mathers LP +With these scripts we're relying on @INC looking in the current +working directory. You may want to add the MyDatabase namespaces to +@INC in a different way when it comes to deployment. - get_cds_by_artist(Michael Jackson): - Thriller - Bad +The F script is an excellent start for testing your database +model. - get_artist_by_track(Dirty Diana): - Michael Jackson +This example uses L to load in the +appropriate L classes from the MyDatabase::Main::Result namespace, +and any required resultset classes from the MyDatabase::Main::ResultSet +namespace (although we created the directory in the directions above we +did not add, or need to add, any resultset classes). - get_artist_by_cd(The Marshall Mathers LP): - Eminem +=head1 TODO =head1 AUTHOR - sc_ + sc_ from irc.perl.org#dbix-class + Kieren Diment + Nigel Metheringham =cut