Deal with authorship properly, in a future-sustainable fashion
[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
5b545397 14 table 'cd' with columns: cdid, artist, title, year
90efcf94 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
a5bd5d88 73 mkdir MyApp
74 mkdir MyApp/Schema
75 mkdir MyApp/Schema/Result
76 mkdir MyApp/Schema/ResultSet
3f341474 77
78Then, create the following DBIx::Class::Schema classes:
79
a5bd5d88 80MyApp/Schema.pm:
299ca323 81
a5bd5d88 82 package MyApp::Schema;
90efcf94 83 use base qw/DBIx::Class::Schema/;
fb7e51d7 84 __PACKAGE__->load_namespaces;
3f341474 85
90efcf94 86 1;
3f341474 87
88
a5bd5d88 89MyApp/Schema/Result/Artist.pm:
3f341474 90
a5bd5d88 91 package MyApp::Schema::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');
a5bd5d88 96 __PACKAGE__->has_many('cds' => 'MyApp::Schema::Result::Cd');
3f341474 97
90efcf94 98 1;
3f341474 99
100
a5bd5d88 101MyApp/Schema/Result/Cd.pm:
3f341474 102
a5bd5d88 103 package MyApp::Schema::Result::Cd;
d88ecca6 104 use base qw/DBIx::Class::Core/;
105 __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
90efcf94 106 __PACKAGE__->table('cd');
5b545397 107 __PACKAGE__->add_columns(qw/ cdid artist title year/);
90efcf94 108 __PACKAGE__->set_primary_key('cdid');
a5bd5d88 109 __PACKAGE__->belongs_to('artist' => 'MyApp::Schema::Result::Artist');
110 __PACKAGE__->has_many('tracks' => 'MyApp::Schema::Result::Track');
3f341474 111
90efcf94 112 1;
3f341474 113
114
a5bd5d88 115MyApp/Schema/Result/Track.pm:
3f341474 116
a5bd5d88 117 package MyApp::Schema::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');
a5bd5d88 122 __PACKAGE__->belongs_to('cd' => 'MyApp::Schema::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
a5bd5d88 136 use MyApp::Schema;
90efcf94 137
a5bd5d88 138 my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');
90efcf94 139
90efcf94 140 my @artists = (['Michael Jackson'], ['Eminem']);
141 $schema->populate('Artist', [
49e87fbb 142 [qw/name/],
143 @artists,
90efcf94 144 ]);
145
146 my %albums = (
49e87fbb 147 'Thriller' => 'Michael Jackson',
148 'Bad' => 'Michael Jackson',
149 'The Marshall Mathers LP' => 'Eminem',
150 );
90efcf94 151
152 my @cds;
153 foreach my $lp (keys %albums) {
0f330864 154 my $artist = $schema->resultset('Artist')->find({
49e87fbb 155 name => $albums{$lp}
156 });
0f330864 157 push @cds, [$lp, $artist->id];
90efcf94 158 }
159
160 $schema->populate('Cd', [
49e87fbb 161 [qw/title artist/],
162 @cds,
90efcf94 163 ]);
164
165
166 my %tracks = (
49e87fbb 167 'Beat It' => 'Thriller',
168 'Billie Jean' => 'Thriller',
169 'Dirty Diana' => 'Bad',
170 'Smooth Criminal' => 'Bad',
171 'Leave Me Alone' => 'Bad',
172 'Stan' => 'The Marshall Mathers LP',
173 'The Way I Am' => 'The Marshall Mathers LP',
90efcf94 174 );
175
176 my @tracks;
177 foreach my $track (keys %tracks) {
0f330864 178 my $cdname = $schema->resultset('Cd')->find({
49e87fbb 179 title => $tracks{$track},
180 });
0f330864 181 push @tracks, [$cdname->id, $track];
90efcf94 182 }
183
184 $schema->populate('Track',[
49e87fbb 185 [qw/cd title/],
186 @tracks,
90efcf94 187 ]);
3f341474 188
e8cc984c 189=head3 Create and run the test scripts
3f341474 190
191testdb.pl:
192
59187a3b 193 #!/usr/bin/perl
90efcf94 194
90efcf94 195 use strict;
59187a3b 196 use warnings;
197
a5bd5d88 198 use MyApp::Schema;
90efcf94 199
a5bd5d88 200 my $schema = MyApp::Schema->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
5b545397 350directory F<examples/Schema>.
d3c2fbd8 351
3f341474 352With these scripts we're relying on @INC looking in the current
a5bd5d88 353working directory. You may want to add the MyApp 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
5b545397 360appropriate L<Result|DBIx::Class::Manual::ResultClass> classes from the
361C<MyApp::Schema::Result> namespace, and any required
362L<ResultSet|DBIx::Class::ResultSet> classes from the
363C<MyApp::Schema::ResultSet> namespace (although we created the directory
364in the directions above we did not add, or need to add, any resultset
365classes).
fb7e51d7 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