Unset collapse in even more cases
[dbsrgits/DBIx-Class.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
96849b7f 30=head3 Create the database/tables
3f341474 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,
299ca323 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
48580715 61and create the SQLite database file:
3f341474 62
299ca323 63 sqlite3 example.db < example.sql
3f341474 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:
299ca323 81
90efcf94 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;
d88ecca6 92 use base qw/DBIx::Class::Core/;
90efcf94 93 __PACKAGE__->table('artist');
94 __PACKAGE__->add_columns(qw/ artistid name /);
95 __PACKAGE__->set_primary_key('artistid');
fb7e51d7 96 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Result::Cd');
3f341474 97
90efcf94 98 1;
3f341474 99
100
fb7e51d7 101MyDatabase/Main/Result/Cd.pm:
3f341474 102
fb7e51d7 103 package MyDatabase::Main::Result::Cd;
d88ecca6 104 use base qw/DBIx::Class::Core/;
105 __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
90efcf94 106 __PACKAGE__->table('cd');
107 __PACKAGE__->add_columns(qw/ cdid artist title/);
108 __PACKAGE__->set_primary_key('cdid');
fb7e51d7 109 __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Result::Artist');
110 __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Result::Track');
3f341474 111
90efcf94 112 1;
3f341474 113
114
fb7e51d7 115MyDatabase/Main/Result/Track.pm:
3f341474 116
fb7e51d7 117 package MyDatabase::Main::Result::Track;
d88ecca6 118 use base qw/DBIx::Class::Core/;
90efcf94 119 __PACKAGE__->table('track');
d88ecca6 120 __PACKAGE__->add_columns(qw/ trackid cd title /);
90efcf94 121 __PACKAGE__->set_primary_key('trackid');
fb7e51d7 122 __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Result::Cd');
3f341474 123
90efcf94 124 1;
3f341474 125
126
96849b7f 127=head3 Write a script to insert some records
3f341474 128
129insertdb.pl
130
59187a3b 131 #!/usr/bin/perl
90efcf94 132
90efcf94 133 use strict;
59187a3b 134 use warnings;
135
136 use MyDatabase::Main;
90efcf94 137
d3c2fbd8 138 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
90efcf94 139
299ca323 140 # here's some of the SQL that is going to be generated by the schema
90efcf94 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) {
0f330864 158 my $artist = $schema->resultset('Artist')->find({
49e87fbb 159 name => $albums{$lp}
160 });
0f330864 161 push @cds, [$lp, $artist->id];
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) {
0f330864 182 my $cdname = $schema->resultset('Cd')->find({
49e87fbb 183 title => $tracks{$track},
184 });
0f330864 185 push @tracks, [$cdname->id, $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
59187a3b 197 #!/usr/bin/perl
90efcf94 198
90efcf94 199 use strict;
59187a3b 200 use warnings;
201
202 use MyDatabase::Main;
90efcf94 203
d3c2fbd8 204 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
48580715 205 # for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd
e8cc984c 206 # driver, e.g perldoc L<DBD::mysql>.
90efcf94 207
208 get_tracks_by_cd('Bad');
209 get_tracks_by_artist('Michael Jackson');
210
211 get_cd_by_track('Stan');
212 get_cds_by_artist('Michael Jackson');
213
214 get_artist_by_track('Dirty Diana');
215 get_artist_by_cd('The Marshall Mathers LP');
216
217
218 sub get_tracks_by_cd {
49e87fbb 219 my $cdtitle = shift;
220 print "get_tracks_by_cd($cdtitle):\n";
221 my $rs = $schema->resultset('Track')->search(
222 {
223 'cd.title' => $cdtitle
224 },
225 {
226 join => [qw/ cd /],
90efcf94 227 }
49e87fbb 228 );
229 while (my $track = $rs->next) {
230 print $track->title . "\n";
231 }
232 print "\n";
90efcf94 233 }
234
235 sub get_tracks_by_artist {
49e87fbb 236 my $artistname = shift;
237 print "get_tracks_by_artist($artistname):\n";
238 my $rs = $schema->resultset('Track')->search(
239 {
240 'artist.name' => $artistname
241 },
242 {
243 join => {
244 'cd' => 'artist'
245 },
90efcf94 246 }
49e87fbb 247 );
248 while (my $track = $rs->next) {
249 print $track->title . "\n";
250 }
251 print "\n";
90efcf94 252 }
299ca323 253
254
90efcf94 255 sub get_cd_by_track {
49e87fbb 256 my $tracktitle = shift;
257 print "get_cd_by_track($tracktitle):\n";
258 my $rs = $schema->resultset('Cd')->search(
259 {
260 'tracks.title' => $tracktitle
261 },
262 {
263 join => [qw/ tracks /],
264 }
265 );
266 my $cd = $rs->first;
267 print $cd->title . "\n\n";
90efcf94 268 }
299ca323 269
90efcf94 270 sub get_cds_by_artist {
49e87fbb 271 my $artistname = shift;
272 print "get_cds_by_artist($artistname):\n";
273 my $rs = $schema->resultset('Cd')->search(
274 {
275 'artist.name' => $artistname
276 },
277 {
278 join => [qw/ artist /],
90efcf94 279 }
49e87fbb 280 );
281 while (my $cd = $rs->next) {
282 print $cd->title . "\n";
283 }
284 print "\n";
90efcf94 285 }
286
287
288
289 sub get_artist_by_track {
49e87fbb 290 my $tracktitle = shift;
291 print "get_artist_by_track($tracktitle):\n";
292 my $rs = $schema->resultset('Artist')->search(
293 {
294 'tracks.title' => $tracktitle
295 },
296 {
297 join => {
298 'cds' => 'tracks'
299 }
300 }
301 );
302 my $artist = $rs->first;
303 print $artist->name . "\n\n";
90efcf94 304 }
305
306 sub get_artist_by_cd {
49e87fbb 307 my $cdtitle = shift;
308 print "get_artist_by_cd($cdtitle):\n";
309 my $rs = $schema->resultset('Artist')->search(
310 {
311 'cds.title' => $cdtitle
312 },
313 {
314 join => [qw/ cds /],
315 }
316 );
317 my $artist = $rs->first;
318 print $artist->name . "\n\n";
90efcf94 319 }
3f341474 320
321
322
323It should output:
324
90efcf94 325 get_tracks_by_cd(Bad):
326 Dirty Diana
327 Smooth Criminal
328 Leave Me Alone
3f341474 329
90efcf94 330 get_tracks_by_artist(Michael Jackson):
331 Beat it
332 Billie Jean
333 Dirty Diana
334 Smooth Criminal
335 Leave Me Alone
3f341474 336
90efcf94 337 get_cd_by_track(Stan):
338 The Marshall Mathers LP
3f341474 339
90efcf94 340 get_cds_by_artist(Michael Jackson):
341 Thriller
342 Bad
3f341474 343
90efcf94 344 get_artist_by_track(Dirty Diana):
345 Michael Jackson
3f341474 346
90efcf94 347 get_artist_by_cd(The Marshall Mathers LP):
348 Eminem
3f341474 349
350=head1 Notes
351
48580715 352A reference implementation of the database and scripts in this example
d3c2fbd8 353are available in the main distribution for DBIx::Class under the
7d04b107 354directory F<t/examples/Schema>.
d3c2fbd8 355
3f341474 356With these scripts we're relying on @INC looking in the current
357working directory. You may want to add the MyDatabase namespaces to
880a1a0c 358@INC in a different way when it comes to deployment.
3f341474 359
7d04b107 360The F<testdb.pl> script is an excellent start for testing your database
3f341474 361model.
362
299ca323 363This example uses L<DBIx::Class::Schema/load_namespaces> to load in the
364appropriate L<Row|DBIx::Class::Row> classes from the MyDatabase::Main::Result namespace,
365and any required resultset classes from the MyDatabase::Main::ResultSet
366namespace (although we created the directory in the directions above we
367did not add, or need to add, any resultset classes).
fb7e51d7 368
3f341474 369=head1 TODO
370
371=head1 AUTHOR
372
373 sc_ from irc.perl.org#dbix-class
374 Kieren Diment <kd@totaldatasolution.com>
fb7e51d7 375 Nigel Metheringham <nigelm@cpan.org>
3f341474 376
377=cut