X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FExample.pod;h=3f9b882e55f84312a7e084c7e0eb93f533b10541;hb=08a8d8f1b8a69ea29bcceb9f399214943a34905c;hp=2d0e2e3dcf80ff29fa0fc4c7d778f565a1b3d225;hpb=59187a3bbcf176df838b00489cb0dfc592679aad;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Example.pod b/lib/DBIx/Class/Manual/Example.pod index 2d0e2e3..3f9b882 100644 --- a/lib/DBIx/Class/Manual/Example.pod +++ b/lib/DBIx/Class/Manual/Example.pod @@ -8,370 +8,108 @@ 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 +The database structure is based on the following rules: + An artist can have many cds, and each cd belongs to just one artist. + A cd can have many tracks, and each track belongs to just one cd. -And these rules exists: +The database is implemented with the following: - one artist can have many cds - one cd belongs to one artist - one cd can have many tracks - one track belongs to one cd + table 'artist' with columns: artistid, name + table 'cd' with columns: cdid, artistid, title, year + table 'track' with columns: trackid, cdid, title +Each of the table's first columns is the primary key; any subsequent +keys are foreign keys. =head2 Installation -Install DBIx::Class via CPAN should be sufficient. - -=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 INTEGER PRIMARY KEY, - name TEXT NOT NULL - ); - - CREATE TABLE cd ( - cdid INTEGER PRIMARY KEY, - artist INTEGER NOT NULL REFERENCES artist(artistid), - title TEXT NOT NULL - ); - - CREATE TABLE track ( - 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 +You'll need to install DBIx::Class via CPAN, and you'll also need to +install sqlite3 (not sqlite) if it's not already intalled. -Change directory back from db to the directory app: +=head3 The database/tables/data - cd ../ +Your distribution already comes with a pre-filled SQLite database +F. You can see it by e.g. -Now create some more directories: + cpanm --look DBIx::Class - mkdir MyDatabase - mkdir MyDatabase/Main - mkdir MyDatabase/Main/Result - mkdir MyDatabase/Main/ResultSet +If for some reason the file is unreadable on your system, you can +recreate it as follows: -Then, create the following DBIx::Class::Schema classes: + cp -a /examples/Schema dbicapp + cd dbicapp + rm db/example.db + sqlite3 db/example.db < db/example.sql + perl insertdb.pl -MyDatabase/Main.pm: +=head3 Testing the database - package MyDatabase::Main; - use base qw/DBIx::Class::Schema/; - __PACKAGE__->load_namespaces; +Enter the example Schema directory - 1; + cd /examples/Schema +Run the script testdb.pl, which will test that the database has +successfully been filled. -MyDatabase/Main/Result/Artist.pm: +When this script is run, it should output the following: - 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'); + get_tracks_by_cd(Bad): + Leave Me Alone + Smooth Criminal + Dirty Diana - 1; + get_tracks_by_artist(Michael Jackson): + Billie Jean (from the CD 'Thriller') + Beat It (from the CD 'Thriller') + Leave Me Alone (from the CD 'Bad') + Smooth Criminal (from the CD 'Bad') + Dirty Diana (from the CD 'Bad') + get_cd_by_track(Stan): + The Marshall Mathers LP has the track 'Stan'. -MyDatabase/Main/Result/Cd.pm: + get_cds_by_artist(Michael Jackson): + Thriller + Bad - 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'); + get_artist_by_track(Dirty Diana): + Michael Jackson recorded the track 'Dirty Diana'. - 1; + get_artist_by_cd(The Marshall Mathers LP): + Eminem recorded the CD 'The Marshall Mathers LP'. -MyDatabase/Main/Result/Track.pm: +=head3 Discussion about the results - 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'); +The data model defined in this example has an artist with multiple CDs, +and a CD with multiple tracks; thus, it's simple to traverse from a +track back to a CD, and from there back to an artist. This is +demonstrated in the get_tracks_by_artist routine, where we easily walk +from the individual track back to the title of the CD that the track +came from ($track->cd->title). - 1; - - -=head3 Write a script to insert some records - -insertdb.pl - - #!/usr/bin/perl - - use strict; - use warnings; - - use MyDatabase::Main; - - 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 - - use strict; - use warnings; - - use MyDatabase::Main; - - 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_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 - -A reference implementation of the database and scripts in this example -are available in the main distribution for DBIx::Class under the -directory F. - -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. - -The F script is an excellent start for testing your database -model. +Note also that in the get_tracks_by_cd and get_tracks_by_artist +routines, the result set is called multiple times with the 'next' +iterator. In contrast, get_cd_by_track uses the 'first' result set +method, since only one CD is expected to have a specific track. 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). +appropriate L classes from the +C namespace, and any required +L classes from the +C namespace (although we did not add, nor needed +any such classes in this example). + +=head1 FURTHER QUESTIONS? -=head1 TODO +Check the list of L. -=head1 AUTHOR +=head1 COPYRIGHT AND LICENSE - sc_ from irc.perl.org#dbix-class - Kieren Diment - Nigel Metheringham +This module is free software L +by the L. You can +redistribute it and/or modify it under the same terms as the +L. =cut