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