Clarification cascade_* attribute defaults documentation
[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
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
90efcf94 131 #!/usr/bin/perl -w
132
133 use MyDatabase::Main;
134 use strict;
135
d3c2fbd8 136 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
90efcf94 137
299ca323 138 # here's some of the SQL that is going to be generated by the schema
90efcf94 139 # INSERT INTO artist VALUES (NULL,'Michael Jackson');
140 # INSERT INTO artist VALUES (NULL,'Eminem');
141
142 my @artists = (['Michael Jackson'], ['Eminem']);
143 $schema->populate('Artist', [
49e87fbb 144 [qw/name/],
145 @artists,
90efcf94 146 ]);
147
148 my %albums = (
49e87fbb 149 'Thriller' => 'Michael Jackson',
150 'Bad' => 'Michael Jackson',
151 'The Marshall Mathers LP' => 'Eminem',
152 );
90efcf94 153
154 my @cds;
155 foreach my $lp (keys %albums) {
0f330864 156 my $artist = $schema->resultset('Artist')->find({
49e87fbb 157 name => $albums{$lp}
158 });
0f330864 159 push @cds, [$lp, $artist->id];
90efcf94 160 }
161
162 $schema->populate('Cd', [
49e87fbb 163 [qw/title artist/],
164 @cds,
90efcf94 165 ]);
166
167
168 my %tracks = (
49e87fbb 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',
90efcf94 176 );
177
178 my @tracks;
179 foreach my $track (keys %tracks) {
0f330864 180 my $cdname = $schema->resultset('Cd')->find({
49e87fbb 181 title => $tracks{$track},
182 });
0f330864 183 push @tracks, [$cdname->id, $track];
90efcf94 184 }
185
186 $schema->populate('Track',[
49e87fbb 187 [qw/cd title/],
188 @tracks,
90efcf94 189 ]);
3f341474 190
e8cc984c 191=head3 Create and run the test scripts
3f341474 192
193testdb.pl:
194
90efcf94 195 #!/usr/bin/perl -w
196
197 use MyDatabase::Main;
198 use strict;
199
d3c2fbd8 200 my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
48580715 201 # for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd
e8cc984c 202 # driver, e.g perldoc L<DBD::mysql>.
90efcf94 203
204 get_tracks_by_cd('Bad');
205 get_tracks_by_artist('Michael Jackson');
206
207 get_cd_by_track('Stan');
208 get_cds_by_artist('Michael Jackson');
209
210 get_artist_by_track('Dirty Diana');
211 get_artist_by_cd('The Marshall Mathers LP');
212
213
214 sub get_tracks_by_cd {
49e87fbb 215 my $cdtitle = shift;
216 print "get_tracks_by_cd($cdtitle):\n";
217 my $rs = $schema->resultset('Track')->search(
218 {
219 'cd.title' => $cdtitle
220 },
221 {
222 join => [qw/ cd /],
90efcf94 223 }
49e87fbb 224 );
225 while (my $track = $rs->next) {
226 print $track->title . "\n";
227 }
228 print "\n";
90efcf94 229 }
230
231 sub get_tracks_by_artist {
49e87fbb 232 my $artistname = shift;
233 print "get_tracks_by_artist($artistname):\n";
234 my $rs = $schema->resultset('Track')->search(
235 {
236 'artist.name' => $artistname
237 },
238 {
239 join => {
240 'cd' => 'artist'
241 },
90efcf94 242 }
49e87fbb 243 );
244 while (my $track = $rs->next) {
245 print $track->title . "\n";
246 }
247 print "\n";
90efcf94 248 }
299ca323 249
250
90efcf94 251 sub get_cd_by_track {
49e87fbb 252 my $tracktitle = shift;
253 print "get_cd_by_track($tracktitle):\n";
254 my $rs = $schema->resultset('Cd')->search(
255 {
256 'tracks.title' => $tracktitle
257 },
258 {
259 join => [qw/ tracks /],
260 }
261 );
262 my $cd = $rs->first;
263 print $cd->title . "\n\n";
90efcf94 264 }
299ca323 265
90efcf94 266 sub get_cds_by_artist {
49e87fbb 267 my $artistname = shift;
268 print "get_cds_by_artist($artistname):\n";
269 my $rs = $schema->resultset('Cd')->search(
270 {
271 'artist.name' => $artistname
272 },
273 {
274 join => [qw/ artist /],
90efcf94 275 }
49e87fbb 276 );
277 while (my $cd = $rs->next) {
278 print $cd->title . "\n";
279 }
280 print "\n";
90efcf94 281 }
282
283
284
285 sub get_artist_by_track {
49e87fbb 286 my $tracktitle = shift;
287 print "get_artist_by_track($tracktitle):\n";
288 my $rs = $schema->resultset('Artist')->search(
289 {
290 'tracks.title' => $tracktitle
291 },
292 {
293 join => {
294 'cds' => 'tracks'
295 }
296 }
297 );
298 my $artist = $rs->first;
299 print $artist->name . "\n\n";
90efcf94 300 }
301
302 sub get_artist_by_cd {
49e87fbb 303 my $cdtitle = shift;
304 print "get_artist_by_cd($cdtitle):\n";
305 my $rs = $schema->resultset('Artist')->search(
306 {
307 'cds.title' => $cdtitle
308 },
309 {
310 join => [qw/ cds /],
311 }
312 );
313 my $artist = $rs->first;
314 print $artist->name . "\n\n";
90efcf94 315 }
3f341474 316
317
318
319It should output:
320
90efcf94 321 get_tracks_by_cd(Bad):
322 Dirty Diana
323 Smooth Criminal
324 Leave Me Alone
3f341474 325
90efcf94 326 get_tracks_by_artist(Michael Jackson):
327 Beat it
328 Billie Jean
329 Dirty Diana
330 Smooth Criminal
331 Leave Me Alone
3f341474 332
90efcf94 333 get_cd_by_track(Stan):
334 The Marshall Mathers LP
3f341474 335
90efcf94 336 get_cds_by_artist(Michael Jackson):
337 Thriller
338 Bad
3f341474 339
90efcf94 340 get_artist_by_track(Dirty Diana):
341 Michael Jackson
3f341474 342
90efcf94 343 get_artist_by_cd(The Marshall Mathers LP):
344 Eminem
3f341474 345
346=head1 Notes
347
48580715 348A reference implementation of the database and scripts in this example
d3c2fbd8 349are available in the main distribution for DBIx::Class under the
7d04b107 350directory F<t/examples/Schema>.
d3c2fbd8 351
3f341474 352With these scripts we're relying on @INC looking in the current
353working directory. You may want to add the MyDatabase namespaces to
880a1a0c 354@INC in a different way when it comes to deployment.
3f341474 355
7d04b107 356The F<testdb.pl> script is an excellent start for testing your database
3f341474 357model.
358
299ca323 359This example uses L<DBIx::Class::Schema/load_namespaces> to load in the
360appropriate L<Row|DBIx::Class::Row> classes from the MyDatabase::Main::Result namespace,
361and any required resultset classes from the MyDatabase::Main::ResultSet
362namespace (although we created the directory in the directions above we
363did not add, or need to add, any resultset classes).
fb7e51d7 364
3f341474 365=head1 TODO
366
367=head1 AUTHOR
368
369 sc_ from irc.perl.org#dbix-class
370 Kieren Diment <kd@totaldatasolution.com>
fb7e51d7 371 Nigel Metheringham <nigelm@cpan.org>
3f341474 372
373=cut