3 DBIx::Class::Manual::Example - Simple CD database example
7 This tutorial will guide you through the process of setting up and
8 testing a very basic CD database using SQLite, with DBIx::Class::Schema
9 as the database frontend.
11 The database consists of the following:
13 table 'artist' with columns: artistid, name
14 table 'cd' with columns: cdid, artist, title, year
15 table 'track' with columns: trackid, cd, title
18 And these rules exists:
20 one artist can have many cds
21 one cd belongs to one artist
22 one cd can have many tracks
23 one track belongs to one cd
28 Install DBIx::Class via CPAN should be sufficient.
30 =head3 Create the database/tables
32 First make and change the directory:
39 This example uses SQLite which is a dependency of DBIx::Class, so you
40 shouldn't have to install extra software.
42 Save the following into a example.sql in the directory db
45 artistid INTEGER PRIMARY KEY,
50 cdid INTEGER PRIMARY KEY,
51 artist INTEGER NOT NULL REFERENCES artist(artistid),
57 trackid INTEGER PRIMARY KEY,
58 cd INTEGER NOT NULL REFERENCES cd(cdid),
62 and create the SQLite database file:
64 sqlite3 example.db < example.sql
66 =head3 Set up DBIx::Class::Schema
68 Change directory back from db to the directory app:
72 Now create some more directories:
76 mkdir MyApp/Schema/Result
77 mkdir MyApp/Schema/ResultSet
79 Then, create the following DBIx::Class::Schema classes:
83 package MyApp::Schema;
84 use base qw/DBIx::Class::Schema/;
85 __PACKAGE__->load_namespaces;
90 MyApp/Schema/Result/Artist.pm:
92 package MyApp::Schema::Result::Artist;
93 use base qw/DBIx::Class::Core/;
94 __PACKAGE__->table('artist');
95 __PACKAGE__->add_columns(qw/ artistid name /);
96 __PACKAGE__->set_primary_key('artistid');
97 __PACKAGE__->has_many('cds' => 'MyApp::Schema::Result::Cd');
102 MyApp/Schema/Result/Cd.pm:
104 package MyApp::Schema::Result::Cd;
105 use base qw/DBIx::Class::Core/;
106 __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
107 __PACKAGE__->table('cd');
108 __PACKAGE__->add_columns(qw/ cdid artist title year/);
109 __PACKAGE__->set_primary_key('cdid');
110 __PACKAGE__->belongs_to('artist' => 'MyApp::Schema::Result::Artist');
111 __PACKAGE__->has_many('tracks' => 'MyApp::Schema::Result::Track');
116 MyApp/Schema/Result/Track.pm:
118 package MyApp::Schema::Result::Track;
119 use base qw/DBIx::Class::Core/;
120 __PACKAGE__->table('track');
121 __PACKAGE__->add_columns(qw/ trackid cd title /);
122 __PACKAGE__->set_primary_key('trackid');
123 __PACKAGE__->belongs_to('cd' => 'MyApp::Schema::Result::Cd');
128 =head3 Write a script to insert some records
139 my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');
141 my @artists = (['Michael Jackson'], ['Eminem']);
142 $schema->populate('Artist', [
148 'Thriller' => 'Michael Jackson',
149 'Bad' => 'Michael Jackson',
150 'The Marshall Mathers LP' => 'Eminem',
154 foreach my $lp (keys %albums) {
155 my $artist = $schema->resultset('Artist')->find({
158 push @cds, [$lp, $artist->id];
161 $schema->populate('Cd', [
168 'Beat It' => 'Thriller',
169 'Billie Jean' => 'Thriller',
170 'Dirty Diana' => 'Bad',
171 'Smooth Criminal' => 'Bad',
172 'Leave Me Alone' => 'Bad',
173 'Stan' => 'The Marshall Mathers LP',
174 'The Way I Am' => 'The Marshall Mathers LP',
178 foreach my $track (keys %tracks) {
179 my $cdname = $schema->resultset('Cd')->find({
180 title => $tracks{$track},
182 push @tracks, [$cdname->id, $track];
185 $schema->populate('Track',[
190 =head3 Create and run the test scripts
201 my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');
202 # for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd
203 # driver, e.g perldoc L<DBD::mysql>.
205 get_tracks_by_cd('Bad');
206 get_tracks_by_artist('Michael Jackson');
208 get_cd_by_track('Stan');
209 get_cds_by_artist('Michael Jackson');
211 get_artist_by_track('Dirty Diana');
212 get_artist_by_cd('The Marshall Mathers LP');
215 sub get_tracks_by_cd {
217 print "get_tracks_by_cd($cdtitle):\n";
218 my $rs = $schema->resultset('Track')->search(
220 'cd.title' => $cdtitle
226 while (my $track = $rs->next) {
227 print $track->title . "\n";
232 sub get_tracks_by_artist {
233 my $artistname = shift;
234 print "get_tracks_by_artist($artistname):\n";
235 my $rs = $schema->resultset('Track')->search(
237 'artist.name' => $artistname
245 while (my $track = $rs->next) {
246 print $track->title . "\n";
252 sub get_cd_by_track {
253 my $tracktitle = shift;
254 print "get_cd_by_track($tracktitle):\n";
255 my $rs = $schema->resultset('Cd')->search(
257 'tracks.title' => $tracktitle
260 join => [qw/ tracks /],
264 print $cd->title . "\n\n";
267 sub get_cds_by_artist {
268 my $artistname = shift;
269 print "get_cds_by_artist($artistname):\n";
270 my $rs = $schema->resultset('Cd')->search(
272 'artist.name' => $artistname
275 join => [qw/ artist /],
278 while (my $cd = $rs->next) {
279 print $cd->title . "\n";
286 sub get_artist_by_track {
287 my $tracktitle = shift;
288 print "get_artist_by_track($tracktitle):\n";
289 my $rs = $schema->resultset('Artist')->search(
291 'tracks.title' => $tracktitle
299 my $artist = $rs->first;
300 print $artist->name . "\n\n";
303 sub get_artist_by_cd {
305 print "get_artist_by_cd($cdtitle):\n";
306 my $rs = $schema->resultset('Artist')->search(
308 'cds.title' => $cdtitle
314 my $artist = $rs->first;
315 print $artist->name . "\n\n";
322 get_tracks_by_cd(Bad):
327 get_tracks_by_artist(Michael Jackson):
334 get_cd_by_track(Stan):
335 The Marshall Mathers LP
337 get_cds_by_artist(Michael Jackson):
341 get_artist_by_track(Dirty Diana):
344 get_artist_by_cd(The Marshall Mathers LP):
349 A reference implementation of the database and scripts in this example
350 are available in the main distribution for DBIx::Class under the
351 directory F<examples/Schema>.
353 With these scripts we're relying on @INC looking in the current
354 working directory. You may want to add the MyApp namespaces to
355 @INC in a different way when it comes to deployment.
357 The F<testdb.pl> script is an excellent start for testing your database
360 This example uses L<DBIx::Class::Schema/load_namespaces> to load in the
361 appropriate L<Result|DBIx::Class::Manual::ResultClass> classes from the
362 C<MyApp::Schema::Result> namespace, and any required
363 L<ResultSet|DBIx::Class::ResultSet> classes from the
364 C<MyApp::Schema::ResultSet> namespace (although we created the directory
365 in the directions above we did not add, or need to add, any resultset
368 =head1 FURTHER QUESTIONS?
370 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
372 =head1 COPYRIGHT AND LICENSE
374 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
375 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
376 redistribute it and/or modify it under the same terms as the
377 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.