Tiny POD formatting fix
[dbsrgits/DBIx-Class-Historic.git] / lib / DBIx / Class / Manual / Example.pod
CommitLineData
3f341474 1=head1 NAME
2
3DBIx::Class::Manual::Example - Simple CD database example
4
5=head1 DESCRIPTION
6
880a1a0c 7This tutorial will guide you through the process of setting up and
3f341474 8testing a very basic CD database using SQLite, with DBIx::Class::Schema
9as the database frontend.
10
11The database consists of the following:
12
90efcf94 13 table 'artist' with columns: artistid, name
14 table 'cd' with columns: cdid, artist, title
15 table 'track' with columns: trackid, cd, title
3f341474 16
17
18And these rules exists:
19
90efcf94 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
3f341474 24
25
26=head2 Installation
27
28Install DBIx::Class via CPAN should be sufficient.
29
30=head3 Create the database/tables.
31
32First make and change the directory:
33
90efcf94 34 mkdir app
35 cd app
d3c2fbd8 36 mkdir db
37 cd db
3f341474 38
39This example uses SQLite which is a dependency of DBIx::Class, so you
40shouldn't have to install extra software.
41
d3c2fbd8 42Save the following into a example.sql in the directory db
3f341474 43
90efcf94 44 CREATE TABLE artist (
49e87fbb 45 artistid INTEGER PRIMARY KEY,
46 name TEXT NOT NULL
90efcf94 47 );
3f341474 48
90efcf94 49 CREATE TABLE cd (
49e87fbb 50 cdid INTEGER PRIMARY KEY,
51 artist INTEGER NOT NULL REFERENCES artist(artistid),
52 title TEXT NOT NULL
53 );
3f341474 54
90efcf94 55 CREATE TABLE track (
49e87fbb 56 trackid INTEGER PRIMARY KEY,
57 cd INTEGER NOT NULL REFERENCES cd(cdid),
58 title TEXT NOT NULL
59 );
3f341474 60
61and create the sqlite database file:
62
63sqlite3 example.db < example.sql
64
65=head3 Set up DBIx::Class::Schema
66
d3c2fbd8 67Change directory back from db to the directory app:
68
69 cd ../
70
71Now create some more directories:
3f341474 72
90efcf94 73 mkdir MyDatabase
74 mkdir MyDatabase/Main
fb7e51d7 75 mkdir MyDatabase/Main/Result
76 mkdir MyDatabase/Main/ResultSet
3f341474 77
78Then, create the following DBIx::Class::Schema classes:
79
80MyDatabase/Main.pm:
90efcf94 81
82 package MyDatabase::Main;
83 use base qw/DBIx::Class::Schema/;
fb7e51d7 84 __PACKAGE__->load_namespaces;
3f341474 85
90efcf94 86 1;
3f341474 87
88
fb7e51d7 89MyDatabase/Main/Result/Artist.pm:
3f341474 90
fb7e51d7 91 package MyDatabase::Main::Result::Artist;
90efcf94 92 use base qw/DBIx::Class/;
e8cc984c 93 __PACKAGE__->load_components(qw/PK::Auto Core/);
90efcf94 94 __PACKAGE__->table('artist');
95 __PACKAGE__->add_columns(qw/ artistid name /);
96 __PACKAGE__->set_primary_key('artistid');
fb7e51d7 97 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Result::Cd');
3f341474 98
90efcf94 99 1;
3f341474 100
101
fb7e51d7 102MyDatabase/Main/Result/Cd.pm:
3f341474 103
fb7e51d7 104 package MyDatabase::Main::Result::Cd;
90efcf94 105 use base qw/DBIx::Class/;
e8cc984c 106 __PACKAGE__->load_components(qw/PK::Auto Core/);
90efcf94 107 __PACKAGE__->table('cd');
108 __PACKAGE__->add_columns(qw/ cdid artist title/);
109 __PACKAGE__->set_primary_key('cdid');
fb7e51d7 110 __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Result::Artist');
111 __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Result::Track');
3f341474 112
90efcf94 113 1;
3f341474 114
115
fb7e51d7 116MyDatabase/Main/Result/Track.pm:
3f341474 117
fb7e51d7 118 package MyDatabase::Main::Result::Track;
90efcf94 119 use base qw/DBIx::Class/;
e8cc984c 120 __PACKAGE__->load_components(qw/PK::Auto Core/);
90efcf94 121 __PACKAGE__->table('track');
122 __PACKAGE__->add_columns(qw/ trackid cd title/);
123 __PACKAGE__->set_primary_key('trackid');
fb7e51d7 124 __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Result::Cd');
3f341474 125
90efcf94 126 1;
3f341474 127
128
129=head3 Write a script to insert some records.
130
131insertdb.pl
132
90efcf94 133 #!/usr/bin/perl -w
134
135 use MyDatabase::Main;
136 use strict;
137
d3c2fbd8 138 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
90efcf94 139
140 # here's some of the sql that is going to be generated by the schema
141 # INSERT INTO artist VALUES (NULL,'Michael Jackson');
142 # INSERT INTO artist VALUES (NULL,'Eminem');
143
144 my @artists = (['Michael Jackson'], ['Eminem']);
145 $schema->populate('Artist', [
49e87fbb 146 [qw/name/],
147 @artists,
90efcf94 148 ]);
149
150 my %albums = (
49e87fbb 151 'Thriller' => 'Michael Jackson',
152 'Bad' => 'Michael Jackson',
153 'The Marshall Mathers LP' => 'Eminem',
154 );
90efcf94 155
156 my @cds;
157 foreach my $lp (keys %albums) {
49e87fbb 158 my $artist = $schema->resultset('Artist')->search({
159 name => $albums{$lp}
160 });
161 push @cds, [$lp, $artist->first];
90efcf94 162 }
163
164 $schema->populate('Cd', [
49e87fbb 165 [qw/title artist/],
166 @cds,
90efcf94 167 ]);
168
169
170 my %tracks = (
49e87fbb 171 'Beat It' => 'Thriller',
172 'Billie Jean' => 'Thriller',
173 'Dirty Diana' => 'Bad',
174 'Smooth Criminal' => 'Bad',
175 'Leave Me Alone' => 'Bad',
176 'Stan' => 'The Marshall Mathers LP',
177 'The Way I Am' => 'The Marshall Mathers LP',
90efcf94 178 );
179
180 my @tracks;
181 foreach my $track (keys %tracks) {
49e87fbb 182 my $cdname = $schema->resultset('Cd')->search({
183 title => $tracks{$track},
184 });
185 push @tracks, [$cdname->first, $track];
90efcf94 186 }
187
188 $schema->populate('Track',[
49e87fbb 189 [qw/cd title/],
190 @tracks,
90efcf94 191 ]);
3f341474 192
e8cc984c 193=head3 Create and run the test scripts
3f341474 194
195testdb.pl:
196
90efcf94 197 #!/usr/bin/perl -w
198
199 use MyDatabase::Main;
200 use strict;
201
d3c2fbd8 202 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
e8cc984c 203 # for other DSNs, e.g. MySql, see the perldoc for the relevant dbd
204 # driver, e.g perldoc L<DBD::mysql>.
90efcf94 205
206 get_tracks_by_cd('Bad');
207 get_tracks_by_artist('Michael Jackson');
208
209 get_cd_by_track('Stan');
210 get_cds_by_artist('Michael Jackson');
211
212 get_artist_by_track('Dirty Diana');
213 get_artist_by_cd('The Marshall Mathers LP');
214
215
216 sub get_tracks_by_cd {
49e87fbb 217 my $cdtitle = shift;
218 print "get_tracks_by_cd($cdtitle):\n";
219 my $rs = $schema->resultset('Track')->search(
220 {
221 'cd.title' => $cdtitle
222 },
223 {
224 join => [qw/ cd /],
90efcf94 225 }
49e87fbb 226 );
227 while (my $track = $rs->next) {
228 print $track->title . "\n";
229 }
230 print "\n";
90efcf94 231 }
232
233 sub get_tracks_by_artist {
49e87fbb 234 my $artistname = shift;
235 print "get_tracks_by_artist($artistname):\n";
236 my $rs = $schema->resultset('Track')->search(
237 {
238 'artist.name' => $artistname
239 },
240 {
241 join => {
242 'cd' => 'artist'
243 },
90efcf94 244 }
49e87fbb 245 );
246 while (my $track = $rs->next) {
247 print $track->title . "\n";
248 }
249 print "\n";
90efcf94 250 }
251
252
90efcf94 253 sub get_cd_by_track {
49e87fbb 254 my $tracktitle = shift;
255 print "get_cd_by_track($tracktitle):\n";
256 my $rs = $schema->resultset('Cd')->search(
257 {
258 'tracks.title' => $tracktitle
259 },
260 {
261 join => [qw/ tracks /],
262 }
263 );
264 my $cd = $rs->first;
265 print $cd->title . "\n\n";
90efcf94 266 }
267
268 sub get_cds_by_artist {
49e87fbb 269 my $artistname = shift;
270 print "get_cds_by_artist($artistname):\n";
271 my $rs = $schema->resultset('Cd')->search(
272 {
273 'artist.name' => $artistname
274 },
275 {
276 join => [qw/ artist /],
90efcf94 277 }
49e87fbb 278 );
279 while (my $cd = $rs->next) {
280 print $cd->title . "\n";
281 }
282 print "\n";
90efcf94 283 }
284
285
286
287 sub get_artist_by_track {
49e87fbb 288 my $tracktitle = shift;
289 print "get_artist_by_track($tracktitle):\n";
290 my $rs = $schema->resultset('Artist')->search(
291 {
292 'tracks.title' => $tracktitle
293 },
294 {
295 join => {
296 'cds' => 'tracks'
297 }
298 }
299 );
300 my $artist = $rs->first;
301 print $artist->name . "\n\n";
90efcf94 302 }
303
304 sub get_artist_by_cd {
49e87fbb 305 my $cdtitle = shift;
306 print "get_artist_by_cd($cdtitle):\n";
307 my $rs = $schema->resultset('Artist')->search(
308 {
309 'cds.title' => $cdtitle
310 },
311 {
312 join => [qw/ cds /],
313 }
314 );
315 my $artist = $rs->first;
316 print $artist->name . "\n\n";
90efcf94 317 }
3f341474 318
319
320
321It should output:
322
90efcf94 323 get_tracks_by_cd(Bad):
324 Dirty Diana
325 Smooth Criminal
326 Leave Me Alone
3f341474 327
90efcf94 328 get_tracks_by_artist(Michael Jackson):
329 Beat it
330 Billie Jean
331 Dirty Diana
332 Smooth Criminal
333 Leave Me Alone
3f341474 334
90efcf94 335 get_cd_by_track(Stan):
336 The Marshall Mathers LP
3f341474 337
90efcf94 338 get_cds_by_artist(Michael Jackson):
339 Thriller
340 Bad
3f341474 341
90efcf94 342 get_artist_by_track(Dirty Diana):
343 Michael Jackson
3f341474 344
90efcf94 345 get_artist_by_cd(The Marshall Mathers LP):
346 Eminem
3f341474 347
348=head1 Notes
349
d3c2fbd8 350A reference implentation of the database and scripts in this example
351are available in the main distribution for DBIx::Class under the
352directory t/examples/Schema
353
3f341474 354With these scripts we're relying on @INC looking in the current
355working directory. You may want to add the MyDatabase namespaces to
880a1a0c 356@INC in a different way when it comes to deployment.
3f341474 357
358The testdb.pl script is an excellent start for testing your database
359model.
360
fb7e51d7 361This example uses load_namespaces to load in the appropriate Row classes
362from the MyDatabase::Main::Result namespace, and any required resultset
363classes from the MyDatabase::Main::ResultSet namespace (although we
364created the directory in the directions above we did not add, or need to
365add, any resultset classes).
366
3f341474 367=head1 TODO
368
369=head1 AUTHOR
370
371 sc_ from irc.perl.org#dbix-class
372 Kieren Diment <kd@totaldatasolution.com>
fb7e51d7 373 Nigel Metheringham <nigelm@cpan.org>
3f341474 374
375=cut