Commit | Line | Data |
022e0893 |
1 | =head1 NAME |
2 | |
3 | DBIx::Class::Manual::Example - Simple CD database example |
4 | |
5 | =head1 DESCRIPTION |
6 | |
7 | This tutorial will guide you through the proeccess of setting up and testing a very basic CD database using Mysql, with DBIx::Class::Schema as the database frontend. |
8 | |
9 | The database consists of the following: |
10 | |
11 | table 'artist' with columns: artistid, name |
12 | table 'cd' with columns: cdid, artist, title |
13 | table 'track' with columns: trackid, cd, title |
14 | |
15 | |
16 | And these rules exists: |
17 | |
18 | one artist can have many cds |
19 | one cd belongs to one artist |
20 | one cd can have many tracks |
21 | one track belongs to one cd |
22 | |
23 | |
24 | =head2 Installation |
25 | |
26 | =head3 Create the database/tables and populate them with a few records |
27 | |
28 | CREATE DATABASE cdtestdb ; |
29 | USE cdtestdb; |
30 | |
31 | CREATE TABLE artist ( |
32 | artistid INT NOT NULL AUTO_INCREMENT , |
33 | name CHAR( 40 ) NOT NULL , |
34 | PRIMARY KEY ( artistid ) |
35 | ); |
36 | |
37 | CREATE TABLE cd ( |
38 | cdid INT NOT NULL AUTO_INCREMENT , |
39 | artist INT NOT NULL , |
40 | title CHAR( 40 ) NOT NULL , |
41 | PRIMARY KEY ( cdid ) |
42 | ); |
43 | |
44 | CREATE TABLE track ( |
45 | trackid INT NOT NULL AUTO_INCREMENT , |
46 | cd INT NOT NULL , |
47 | title CHAR( 40 ) NOT NULL , |
48 | PRIMARY KEY ( trackid ) |
49 | ; |
50 | |
51 | |
52 | INSERT INTO artist VALUES |
53 | (NULL,'Michael Jackson'), |
54 | (NULL,'Eminem'); |
55 | |
56 | INSERT INTO cd VALUES |
57 | (NULL,'1','Thriller'), |
58 | (NULL,'1','Bad'), |
59 | (NULL,'2','The Marshall Mathers LP'); |
60 | |
61 | INSERT INTO track VALUES |
62 | (NULL,'1','Beat it'), |
63 | (NULL,'1','Billie Jean'), |
64 | (NULL,'2','Dirty Diana'), |
65 | (NULL,'2','Smooth Criminal'), |
66 | (NULL,'2','Leave Me Alone'), |
67 | (NULL,'3','Stan'), |
68 | (NULL,'3','The Way I Am'); |
69 | |
70 | |
71 | |
72 | =head3 Set up DBIx::Class::Schema |
73 | |
74 | First, create some dirs and change working directory: |
75 | |
76 | mkdir app |
77 | mkdir app/DB |
78 | mkdir app/DB/Main |
79 | cd app |
80 | |
81 | |
82 | Then, create the following DBIx::Class::Schema classes: |
83 | |
84 | DB/Main.pm: |
85 | |
86 | package DB::Main; |
87 | use base qw/DBIx::Class::Schema/; |
88 | __PACKAGE__->load_classes(qw/Artist CD Track/); |
89 | |
90 | 1; |
91 | |
92 | |
93 | DB/Main/Artist.pm: |
94 | |
95 | package DB::Main::Artist; |
96 | use base qw/DBIx::Class/; |
97 | __PACKAGE__->load_components(qw/Core/); |
98 | __PACKAGE__->table('artist'); |
99 | __PACKAGE__->add_columns(qw/ artistid name /); |
100 | __PACKAGE__->set_primary_key('artistid'); |
101 | __PACKAGE__->has_many('cds' => 'DB::Main::CD'); |
102 | |
103 | 1; |
104 | |
105 | |
106 | DB/Main/CD.pm: |
107 | |
108 | package DB::Main::CD; |
109 | use base qw/DBIx::Class/; |
110 | __PACKAGE__->load_components(qw/Core/); |
111 | __PACKAGE__->table('cd'); |
112 | __PACKAGE__->add_columns(qw/ cdid artist title/); |
113 | __PACKAGE__->set_primary_key('cdid'); |
114 | __PACKAGE__->belongs_to('artist' => 'DB::Main::Artist'); |
115 | __PACKAGE__->has_many('tracks' => 'DB::Main::Track'); |
116 | |
117 | 1; |
118 | |
119 | |
120 | DB/Main/Track.pm: |
121 | |
122 | package DB::Main::Track; |
123 | use base qw/DBIx::Class/; |
124 | __PACKAGE__->load_components(qw/Core/); |
125 | __PACKAGE__->table('track'); |
126 | __PACKAGE__->add_columns(qw/ trackid cd title/); |
127 | __PACKAGE__->set_primary_key('trackid'); |
128 | __PACKAGE__->belongs_to('cd' => 'DB::Main::CD'); |
129 | |
130 | 1; |
131 | |
132 | |
133 | =head3 Create and run the test script |
134 | |
135 | testdb.pl: |
136 | |
137 | #!/usr/bin/perl -w |
138 | |
139 | use DB::Main; |
140 | use strict; |
141 | |
f870ba4f |
142 | my $schema = DB::Main->connect('dbi:mysql:cdtestdb', 'testuser', 'testpass'); |
022e0893 |
143 | |
144 | get_tracks_by_cd('Bad'); |
145 | get_tracks_by_artist('Michael Jackson'); |
146 | |
147 | get_cd_by_track('Stan'); |
148 | get_cds_by_artist('Michael Jackson'); |
149 | |
150 | get_artist_by_track('Dirty Diana'); |
151 | get_artist_by_cd('The Marshall Mathers LP'); |
152 | |
153 | |
154 | sub get_tracks_by_cd { |
155 | my $cdtitle = shift; |
156 | print "get_tracks_by_cd($cdtitle):\n"; |
157 | my $rs = $schema->resultset('Track')->search( |
158 | { |
159 | 'cd.title' => $cdtitle |
160 | }, |
161 | { |
162 | join => [qw/ cd /], |
163 | prefetch => [qw/ cd /] |
164 | } |
165 | ); |
166 | while (my $track = $rs->next) { |
167 | print $track->title . "\n"; |
168 | } |
169 | print "\n"; |
170 | } |
171 | |
172 | sub get_tracks_by_artist { |
173 | my $artistname = shift; |
174 | print "get_tracks_by_artist($artistname):\n"; |
175 | my $rs = $schema->resultset('Track')->search( |
176 | { |
177 | 'artist.name' => $artistname |
178 | }, |
179 | { |
180 | join => { |
181 | 'cd' => 'artist' |
182 | }, |
183 | } |
184 | ); |
185 | while (my $track = $rs->next) { |
186 | print $track->title . "\n"; |
187 | } |
188 | print "\n"; |
189 | } |
190 | |
191 | |
192 | |
193 | sub get_cd_by_track { |
194 | my $tracktitle = shift; |
195 | print "get_cd_by_track($tracktitle):\n"; |
196 | my $rs = $schema->resultset('CD')->search( |
197 | { |
198 | 'tracks.title' => $tracktitle |
199 | }, |
200 | { |
201 | join => [qw/ tracks /], |
202 | } |
203 | ); |
204 | my $cd = $rs->first; |
205 | print $cd->title . "\n\n"; |
206 | } |
207 | |
208 | sub get_cds_by_artist { |
209 | my $artistname = shift; |
210 | print "get_cds_by_artist($artistname):\n"; |
211 | my $rs = $schema->resultset('CD')->search( |
212 | { |
213 | 'artist.name' => $artistname |
214 | }, |
215 | { |
216 | join => [qw/ artist /], |
217 | prefetch => [qw/ artist /] |
218 | } |
219 | ); |
220 | while (my $cd = $rs->next) { |
221 | print $cd->title . "\n"; |
222 | } |
223 | print "\n"; |
224 | } |
225 | |
226 | |
227 | |
228 | sub get_artist_by_track { |
229 | my $tracktitle = shift; |
230 | print "get_artist_by_track($tracktitle):\n"; |
231 | my $rs = $schema->resultset('Artist')->search( |
232 | { |
233 | 'tracks.title' => $tracktitle |
234 | }, |
235 | { |
236 | join => { |
237 | 'cds' => 'tracks' |
238 | } |
239 | } |
240 | ); |
241 | my $artist = $rs->first; |
242 | print $artist->name . "\n\n"; |
243 | } |
244 | |
245 | sub get_artist_by_cd { |
246 | my $cdtitle = shift; |
247 | print "get_artist_by_cd($cdtitle):\n"; |
248 | my $rs = $schema->resultset('Artist')->search( |
249 | { |
250 | 'cds.title' => $cdtitle |
251 | }, |
252 | { |
253 | join => [qw/ cds /], |
254 | } |
255 | ); |
256 | my $artist = $rs->first; |
257 | print $artist->name . "\n\n"; |
258 | } |
259 | |
260 | |
261 | |
262 | It should output: |
263 | |
264 | get_tracks_by_cd(Bad): |
265 | Dirty Diana |
266 | Smooth Criminal |
267 | Leave Me Alone |
268 | |
269 | get_tracks_by_artist(Michael Jackson): |
270 | Beat it |
271 | Billie Jean |
272 | Dirty Diana |
273 | Smooth Criminal |
274 | Leave Me Alone |
275 | |
276 | get_cd_by_track(Stan): |
277 | The Marshall Mathers LP |
278 | |
279 | get_cds_by_artist(Michael Jackson): |
280 | Thriller |
281 | Bad |
282 | |
283 | get_artist_by_track(Dirty Diana): |
284 | Michael Jackson |
285 | |
286 | get_artist_by_cd(The Marshall Mathers LP): |
287 | Eminem |
288 | |
289 | =head1 AUTHOR |
290 | |
291 | sc_ |
292 | |
293 | =cut |