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
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),
56 trackid INTEGER PRIMARY KEY,
57 cd INTEGER NOT NULL REFERENCES cd(cdid),
61 and create the sqlite database file:
63 sqlite3 example.db < example.sql
65 =head3 Set up DBIx::Class::Schema
67 Change directory back from db to the directory app:
71 Now create some more directories:
76 Then, create the following DBIx::Class::Schema classes:
80 package MyDatabase::Main;
81 use base qw/DBIx::Class::Schema/;
82 __PACKAGE__->load_classes(qw/Artist Cd Track/);
87 MyDatabase/Main/Artist.pm:
89 package MyDatabase::Main::Artist;
90 use base qw/DBIx::Class/;
91 __PACKAGE__->load_components(qw/PK::Auto Core/);
92 __PACKAGE__->table('artist');
93 __PACKAGE__->add_columns(qw/ artistid name /);
94 __PACKAGE__->set_primary_key('artistid');
95 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
100 MyDatabase/Main/Cd.pm:
102 package MyDatabase::Main::Cd;
103 use base qw/DBIx::Class/;
104 __PACKAGE__->load_components(qw/PK::Auto Core/);
105 __PACKAGE__->table('cd');
106 __PACKAGE__->add_columns(qw/ cdid artist title/);
107 __PACKAGE__->set_primary_key('cdid');
108 __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Artist');
109 __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Track');
114 MyDatabase/Main/Track.pm:
116 package MyDatabase::Main::Track;
117 use base qw/DBIx::Class/;
118 __PACKAGE__->load_components(qw/PK::Auto Core/);
119 __PACKAGE__->table('track');
120 __PACKAGE__->add_columns(qw/ trackid cd title/);
121 __PACKAGE__->set_primary_key('trackid');
122 __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Cd');
127 =head3 Write a script to insert some records.
133 use MyDatabase::Main;
136 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
138 # here's some of the sql that is going to be generated by the schema
139 # INSERT INTO artist VALUES (NULL,'Michael Jackson');
140 # INSERT INTO artist VALUES (NULL,'Eminem');
142 my @artists = (['Michael Jackson'], ['Eminem']);
143 $schema->populate('Artist', [
149 'Thriller' => 'Michael Jackson',
150 'Bad' => 'Michael Jackson',
151 'The Marshall Mathers LP' => 'Eminem',
155 foreach my $lp (keys %albums) {
156 my $artist = $schema->resultset('Artist')->search({
159 push @cds, [$lp, $artist->first];
162 $schema->populate('Cd', [
169 'Beat It' => 'Thriller',
170 'Billie Jean' => 'Thriller',
171 'Dirty Diana' => 'Bad',
172 'Smooth Criminal' => 'Bad',
173 'Leave Me Alone' => 'Bad',
174 'Stan' => 'The Marshall Mathers LP',
175 'The Way I Am' => 'The Marshall Mathers LP',
179 foreach my $track (keys %tracks) {
180 my $cdname = $schema->resultset('Cd')->search({
181 title => $tracks{$track},
183 push @tracks, [$cdname->first, $track];
186 $schema->populate('Track',[
191 =head3 Create and run the test scripts
197 use MyDatabase::Main;
200 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
201 # for other DSNs, e.g. MySql, see the perldoc for the relevant dbd
202 # driver, e.g perldoc L<DBD::mysql>.
204 get_tracks_by_cd('Bad');
205 get_tracks_by_artist('Michael Jackson');
207 get_cd_by_track('Stan');
208 get_cds_by_artist('Michael Jackson');
210 get_artist_by_track('Dirty Diana');
211 get_artist_by_cd('The Marshall Mathers LP');
214 sub get_tracks_by_cd {
216 print "get_tracks_by_cd($cdtitle):\n";
217 my $rs = $schema->resultset('Track')->search(
219 'cd.title' => $cdtitle
223 prefetch => [qw/ cd /]
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 /],
276 prefetch => [qw/ artist /]
279 while (my $cd = $rs->next) {
280 print $cd->title . "\n";
287 sub get_artist_by_track {
288 my $tracktitle = shift;
289 print "get_artist_by_track($tracktitle):\n";
290 my $rs = $schema->resultset('Artist')->search(
292 'tracks.title' => $tracktitle
300 my $artist = $rs->first;
301 print $artist->name . "\n\n";
304 sub get_artist_by_cd {
306 print "get_artist_by_cd($cdtitle):\n";
307 my $rs = $schema->resultset('Artist')->search(
309 'cds.title' => $cdtitle
315 my $artist = $rs->first;
316 print $artist->name . "\n\n";
323 get_tracks_by_cd(Bad):
328 get_tracks_by_artist(Michael Jackson):
335 get_cd_by_track(Stan):
336 The Marshall Mathers LP
338 get_cds_by_artist(Michael Jackson):
342 get_artist_by_track(Dirty Diana):
345 get_artist_by_cd(The Marshall Mathers LP):
350 A reference implentation of the database and scripts in this example
351 are available in the main distribution for DBIx::Class under the
352 directory t/examples/Schema
354 With these scripts we're relying on @INC looking in the current
355 working directory. You may want to add the MyDatabase namespaces to
356 @INC in a different way when it comes to deployment.
358 The testdb.pl script is an excellent start for testing your database
365 sc_ from irc.perl.org#dbix-class
366 Kieren Diment <kd@totaldatasolution.com>