3 DBIx::Class::Manual::Example - Simple CD database example
7 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.
9 The database consists of the following:
11 table 'artist' with columns: artistid, name
12 table 'cd' with columns: cdid, artist, title
13 table 'track' with columns: trackid, cd, title
16 And these rules exists:
18 one artist can have many cds
19 one cd belongs to one artist
20 one cd can have many tracks
21 one track belongs to one cd
26 =head3 Create the database/tables and populate them with a few records
28 CREATE DATABASE cdtestdb ;
32 artistid INT NOT NULL AUTO_INCREMENT ,
33 name CHAR( 40 ) NOT NULL ,
34 PRIMARY KEY ( artistid )
38 cdid INT NOT NULL AUTO_INCREMENT ,
40 title CHAR( 40 ) NOT NULL ,
45 trackid INT NOT NULL AUTO_INCREMENT ,
47 title CHAR( 40 ) NOT NULL ,
48 PRIMARY KEY ( trackid )
52 INSERT INTO artist VALUES
53 (NULL,'Michael Jackson'),
57 (NULL,'1','Thriller'),
59 (NULL,'2','The Marshall Mathers LP');
61 INSERT INTO track VALUES
63 (NULL,'1','Billie Jean'),
64 (NULL,'2','Dirty Diana'),
65 (NULL,'2','Smooth Criminal'),
66 (NULL,'2','Leave Me Alone'),
68 (NULL,'3','The Way I Am');
72 =head3 Set up DBIx::Class::Schema
74 First, create some dirs and change working directory:
82 Then, create the following DBIx::Class::Schema classes:
87 use base qw/DBIx::Class::Schema/;
88 __PACKAGE__->load_classes(qw/Artist CD Track/);
95 package DB::Main::Artist;
96 use base qw/DBIx::Class/;
97 __PACKAGE__->load_components(qw/Core/);
98 __PACKAGE__->table('artist');
99 __PACKAGE__->add_columns(qw/ artistid name /);
100 __PACKAGE__->set_primary_key('artistid');
101 __PACKAGE__->has_many('cds' => 'DB::Main::CD');
108 package DB::Main::CD;
109 use base qw/DBIx::Class/;
110 __PACKAGE__->load_components(qw/Core/);
111 __PACKAGE__->table('cd');
112 __PACKAGE__->add_columns(qw/ cdid artist title/);
113 __PACKAGE__->set_primary_key('cdid');
114 __PACKAGE__->belongs_to('artist' => 'DB::Main::Artist');
115 __PACKAGE__->has_many('tracks' => 'DB::Main::Track');
122 package DB::Main::Track;
123 use base qw/DBIx::Class/;
124 __PACKAGE__->load_components(qw/Core/);
125 __PACKAGE__->table('track');
126 __PACKAGE__->add_columns(qw/ trackid cd title/);
127 __PACKAGE__->set_primary_key('trackid');
128 __PACKAGE__->belongs_to('cd' => 'DB::Main::CD');
133 =head3 Create and run the test script
142 my $schema = DB::Main->connect('dbi:mysql:cdtestdb', 'testuser', 'testpass');
144 get_tracks_by_cd('Bad');
145 get_tracks_by_artist('Michael Jackson');
147 get_cd_by_track('Stan');
148 get_cds_by_artist('Michael Jackson');
150 get_artist_by_track('Dirty Diana');
151 get_artist_by_cd('The Marshall Mathers LP');
154 sub get_tracks_by_cd {
156 print "get_tracks_by_cd($cdtitle):\n";
157 my $rs = $schema->resultset('Track')->search(
159 'cd.title' => $cdtitle
163 prefetch => [qw/ cd /]
166 while (my $track = $rs->next) {
167 print $track->title . "\n";
172 sub get_tracks_by_artist {
173 my $artistname = shift;
174 print "get_tracks_by_artist($artistname):\n";
175 my $rs = $schema->resultset('Track')->search(
177 'artist.name' => $artistname
185 while (my $track = $rs->next) {
186 print $track->title . "\n";
193 sub get_cd_by_track {
194 my $tracktitle = shift;
195 print "get_cd_by_track($tracktitle):\n";
196 my $rs = $schema->resultset('CD')->search(
198 'tracks.title' => $tracktitle
201 join => [qw/ tracks /],
205 print $cd->title . "\n\n";
208 sub get_cds_by_artist {
209 my $artistname = shift;
210 print "get_cds_by_artist($artistname):\n";
211 my $rs = $schema->resultset('CD')->search(
213 'artist.name' => $artistname
216 join => [qw/ artist /],
217 prefetch => [qw/ artist /]
220 while (my $cd = $rs->next) {
221 print $cd->title . "\n";
228 sub get_artist_by_track {
229 my $tracktitle = shift;
230 print "get_artist_by_track($tracktitle):\n";
231 my $rs = $schema->resultset('Artist')->search(
233 'tracks.title' => $tracktitle
241 my $artist = $rs->first;
242 print $artist->name . "\n\n";
245 sub get_artist_by_cd {
247 print "get_artist_by_cd($cdtitle):\n";
248 my $rs = $schema->resultset('Artist')->search(
250 'cds.title' => $cdtitle
256 my $artist = $rs->first;
257 print $artist->name . "\n\n";
264 get_tracks_by_cd(Bad):
269 get_tracks_by_artist(Michael Jackson):
276 get_cd_by_track(Stan):
277 The Marshall Mathers LP
279 get_cds_by_artist(Michael Jackson):
283 get_artist_by_track(Dirty Diana):
286 get_artist_by_cd(The Marshall Mathers LP):