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=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..3f9b882 100644 --- a/lib/DBIx/Class/Manual/Example.pod +++ b/lib/DBIx/Class/Manual/Example.pod @@ -4,290 +4,112 @@ 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: +The database structure is based on the following rules: - table 'artist' with columns: artistid, name - table 'cd' with columns: cdid, artist, title - table 'track' with columns: trackid, cd, title + 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. +The database is implemented with the following: -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 + 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 -=head3 Create the database/tables and populate them with a few records - - CREATE DATABASE cdtestdb ; - USE cdtestdb; - - CREATE TABLE artist ( - artistid INT NOT NULL AUTO_INCREMENT , - name CHAR( 40 ) NOT NULL , - PRIMARY KEY ( artistid ) - ); +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. + +=head3 The database/tables/data + +Your distribution already comes with a pre-filled SQLite database +F. You can see it by e.g. + + cpanm --look DBIx::Class + +If for some reason the file is unreadable on your system, you can +recreate it as follows: - CREATE TABLE cd ( - cdid INT NOT NULL AUTO_INCREMENT , - artist INT NOT NULL , - title CHAR( 40 ) NOT NULL , - PRIMARY KEY ( cdid ) - ); + cp -a /examples/Schema dbicapp + cd dbicapp + rm db/example.db + sqlite3 db/example.db < db/example.sql + perl insertdb.pl - 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'); +=head3 Testing the database +Enter the example Schema directory + cd /examples/Schema -=head3 Set up DBIx::Class::Schema - -First, create some dirs and change working directory: - - mkdir app - mkdir app/DB - mkdir app/DB/Main - cd app - - -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/); - - 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'); - - 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; - - -DB/Main/Track.pm: - - 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; - - -=head3 Create and run the test script - -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"; - } - - - -It should output: +Run the script testdb.pl, which will test that the database has +successfully been filled. + +When this script is run, it should output the following: get_tracks_by_cd(Bad): - Dirty Diana - Smooth Criminal Leave Me Alone + Smooth Criminal + Dirty Diana get_tracks_by_artist(Michael Jackson): - Beat it - Billie Jean - Dirty Diana - Smooth Criminal - Leave Me Alone + 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 + The Marshall Mathers LP has the track 'Stan'. get_cds_by_artist(Michael Jackson): Thriller Bad get_artist_by_track(Dirty Diana): - Michael Jackson + Michael Jackson recorded the track 'Dirty Diana'. get_artist_by_cd(The Marshall Mathers LP): - Eminem + Eminem recorded the CD 'The Marshall Mathers LP'. + + +=head3 Discussion about the results + +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). + +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 +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? + +Check the list of L. -=head1 AUTHOR +=head1 COPYRIGHT AND LICENSE - sc_ +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