DBIx::Class::Manual::Example5.12 man page on MacOSX

Man page or keyword search:  
man Server   23457 pages
apropos Keyword Search (all sections)
Output format
MacOSX logo
[printable version]

DBIx::Class::Manual::EUserlContributed Perl DocDBIx::Class::Manual::Example(3)

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>

perl v5.12.5			  2011-05-10   DBIx::Class::Manual::Example(3)
[top]

List of man pages available for MacOSX

Copyright (c) for man pages and the logo by the respective OS vendor.

For those who want to learn more, the polarhome community provides shell access and support.

[legal] [privacy] [GNU] [policy] [cookies] [netiquette] [sponsors] [FAQ]
Tweet
Polarhome, production since 1999.
Member of Polarhome portal.
Based on Fawad Halim's script.
....................................................................
Vote for polarhome
Free Shell Accounts :: the biggest list on the net