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:
37 This example uses SQLite which is a dependency of DBIx::Class, so you
38 shouldn't have to install extra software.
40 Save the following into a example.sql
43 artistid INTEGER PRIMARY KEY,
48 cdid INTEGER PRIMARY KEY,
49 artist INTEGER NOT NULL REFERENCES artist(artistid),
54 trackid INTEGER PRIMARY KEY,
55 cd INTEGER NOT NULL REFERENCES cd(cdid),
59 and create the sqlite database file:
61 sqlite3 example.db < example.sql
63 =head3 Set up DBIx::Class::Schema
65 First, create some dirs and change working directory:
70 Then, create the following DBIx::Class::Schema classes:
74 package MyDatabase::Main;
75 use base qw/DBIx::Class::Schema/;
76 __PACKAGE__->load_classes(qw/Artist Cd Track/);
81 MyDatabase/Main/Artist.pm:
83 package MyDatabase::Main::Artist;
84 use base qw/DBIx::Class/;
85 __PACKAGE__->load_components(qw/PK::Auto Core/);
86 __PACKAGE__->table('artist');
87 __PACKAGE__->add_columns(qw/ artistid name /);
88 __PACKAGE__->set_primary_key('artistid');
89 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
94 MyDatabase/Main/Cd.pm:
96 package MyDatabase::Main::Cd;
97 use base qw/DBIx::Class/;
98 __PACKAGE__->load_components(qw/PK::Auto Core/);
99 __PACKAGE__->table('cd');
100 __PACKAGE__->add_columns(qw/ cdid artist title/);
101 __PACKAGE__->set_primary_key('cdid');
102 __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Artist');
103 __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Track');
108 MyDatabase/Main/Track.pm:
110 package MyDatabase::Main::Track;
111 use base qw/DBIx::Class/;
112 __PACKAGE__->load_components(qw/PK::Auto Core/);
113 __PACKAGE__->table('track');
114 __PACKAGE__->add_columns(qw/ trackid cd title/);
115 __PACKAGE__->set_primary_key('trackid');
116 __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Cd');
121 =head3 Write a script to insert some records.
127 use MyDatabase::Main;
130 my $schema = MyDatabase::Main->connect('dbi:SQLite:example.db');
132 # here's some of the sql that is going to be generated by the schema
133 # INSERT INTO artist VALUES (NULL,'Michael Jackson');
134 # INSERT INTO artist VALUES (NULL,'Eminem');
136 my @artists = (['Michael Jackson'], ['Eminem']);
137 $schema->populate('Artist', [
143 'Thriller' => 'Michael Jackson',
144 'Bad' => 'Michael Jackson',
145 'The Marshall Mathers LP' => 'Eminem',
149 foreach my $lp (keys %albums) {
150 my $artist = $schema->resultset('Artist')->search({
153 push @cds, [$lp, $artist->first];
156 $schema->populate('Cd', [
163 'Beat It' => 'Thriller',
164 'Billie Jean' => 'Thriller',
165 'Dirty Diana' => 'Bad',
166 'Smooth Criminal' => 'Bad',
167 'Leave Me Alone' => 'Bad',
168 'Stan' => 'The Marshall Mathers LP',
169 'The Way I Am' => 'The Marshall Mathers LP',
173 foreach my $track (keys %tracks) {
174 my $cdname = $schema->resultset('Cd')->search({
175 title => $tracks{$track},
177 push @tracks, [$cdname->first, $track];
180 $schema->populate('Track',[
185 =head3 Create and run the test scripts
191 use MyDatabase::Main;
194 my $schema = MyDatabase::Main->connect('dbi:SQLite:example.db');
195 # for other DSNs, e.g. MySql, see the perldoc for the relevant dbd
196 # driver, e.g perldoc L<DBD::mysql>.
198 get_tracks_by_cd('Bad');
199 get_tracks_by_artist('Michael Jackson');
201 get_cd_by_track('Stan');
202 get_cds_by_artist('Michael Jackson');
204 get_artist_by_track('Dirty Diana');
205 get_artist_by_cd('The Marshall Mathers LP');
208 sub get_tracks_by_cd {
210 print "get_tracks_by_cd($cdtitle):\n";
211 my $rs = $schema->resultset('Track')->search(
213 'cd.title' => $cdtitle
217 prefetch => [qw/ cd /]
220 while (my $track = $rs->next) {
221 print $track->title . "\n";
226 sub get_tracks_by_artist {
227 my $artistname = shift;
228 print "get_tracks_by_artist($artistname):\n";
229 my $rs = $schema->resultset('Track')->search(
231 'artist.name' => $artistname
239 while (my $track = $rs->next) {
240 print $track->title . "\n";
246 sub get_cd_by_track {
247 my $tracktitle = shift;
248 print "get_cd_by_track($tracktitle):\n";
249 my $rs = $schema->resultset('Cd')->search(
251 'tracks.title' => $tracktitle
254 join => [qw/ tracks /],
258 print $cd->title . "\n\n";
261 sub get_cds_by_artist {
262 my $artistname = shift;
263 print "get_cds_by_artist($artistname):\n";
264 my $rs = $schema->resultset('Cd')->search(
266 'artist.name' => $artistname
269 join => [qw/ artist /],
270 prefetch => [qw/ artist /]
273 while (my $cd = $rs->next) {
274 print $cd->title . "\n";
281 sub get_artist_by_track {
282 my $tracktitle = shift;
283 print "get_artist_by_track($tracktitle):\n";
284 my $rs = $schema->resultset('Artist')->search(
286 'tracks.title' => $tracktitle
294 my $artist = $rs->first;
295 print $artist->name . "\n\n";
298 sub get_artist_by_cd {
300 print "get_artist_by_cd($cdtitle):\n";
301 my $rs = $schema->resultset('Artist')->search(
303 'cds.title' => $cdtitle
309 my $artist = $rs->first;
310 print $artist->name . "\n\n";
317 get_tracks_by_cd(Bad):
322 get_tracks_by_artist(Michael Jackson):
329 get_cd_by_track(Stan):
330 The Marshall Mathers LP
332 get_cds_by_artist(Michael Jackson):
336 get_artist_by_track(Dirty Diana):
339 get_artist_by_cd(The Marshall Mathers LP):
344 With these scripts we're relying on @INC looking in the current
345 working directory. You may want to add the MyDatabase namespaces to
346 @INC in a different way when it comes to deployment.
348 The testdb.pl script is an excellent start for testing your database
355 sc_ from irc.perl.org#dbix-class
356 Kieren Diment <kd@totaldatasolution.com>