Linux and UNIX Man Pages

Linux & Unix Commands - Search Man Pages

dbix::class::manual::example(3) [osx man page]

DBIx::Class::Manual::Example(3) 			User Contributed Perl Documentation			   DBIx::Class::Manual::Example(3)

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/ package MyDatabase::Main; use base qw/DBIx::Class::Schema/; __PACKAGE__->load_namespaces; 1; MyDatabase/Main/Result/ 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/ 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/ 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 #!/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 #!/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): "; my $rs = $schema->resultset('Track')->search( { 'cd.title' => $cdtitle }, { join => [qw/ cd /], } ); while (my $track = $rs->next) { print $track->title . " "; } print " "; } sub get_tracks_by_artist { my $artistname = shift; print "get_tracks_by_artist($artistname): "; my $rs = $schema->resultset('Track')->search( { '' => $artistname }, { join => { 'cd' => 'artist' }, } ); while (my $track = $rs->next) { print $track->title . " "; } print " "; } sub get_cd_by_track { my $tracktitle = shift; print "get_cd_by_track($tracktitle): "; my $rs = $schema->resultset('Cd')->search( { 'tracks.title' => $tracktitle }, { join => [qw/ tracks /], } ); my $cd = $rs->first; print $cd->title . " "; } sub get_cds_by_artist { my $artistname = shift; print "get_cds_by_artist($artistname): "; my $rs = $schema->resultset('Cd')->search( { '' => $artistname }, { join => [qw/ artist /], } ); while (my $cd = $rs->next) { print $cd->title . " "; } print " "; } sub get_artist_by_track { my $tracktitle = shift; print "get_artist_by_track($tracktitle): "; my $rs = $schema->resultset('Artist')->search( { 'tracks.title' => $tracktitle }, { join => { 'cds' => 'tracks' } } ); my $artist = $rs->first; print $artist->name . " "; } sub get_artist_by_cd { my $cdtitle = shift; print "get_artist_by_cd($cdtitle): "; my $rs = $schema->resultset('Artist')->search( { 'cds.title' => $cdtitle }, { join => [qw/ cds /], } ); my $artist = $rs->first; print $artist->name . " "; } 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 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
sc_ from Kieren Diment <> Nigel Metheringham <> perl v5.16.2 2012-08-16 DBIx::Class::Manual::Example(3)
Man Page

Featured Tech Videos