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