NAME

       DBIx::Class::Manual::Example - Simple CD database example

DESCRIPTION

       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

       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

   Installation
       Install DBIx::Class via CPAN should be sufficient.

       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

       Set up DBIx::Class::Schema

       Change directory back from db to the directory app:

         cd ../

       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:

       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:

         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;

       MyDatabase/Main/Result/Track.pm:

         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;

       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,
         ]);

       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<DBD::mysql>.

         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

Notes

       A reference implementation of the database and scripts in this example are available in
       the main distribution for DBIx::Class under the directory t/examples/Schema.

       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 testdb.pl script is an excellent start for testing your database model.

       This example uses "load_namespaces" in DBIx::Class::Schema to load in the appropriate Row
       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).

TODO

AUTHOR

         sc_ from irc.perl.org#dbix-class
         Kieren Diment <kd@totaldatasolution.com>
         Nigel Metheringham <nigelm@cpan.org>