Updates to Intro
[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / ExampleSchema.pod
CommitLineData
022e0893 1=head1 NAME
2
3DBIx::Class::Manual::Example - Simple CD database example
4
5=head1 DESCRIPTION
6
a15b8059 7This tutorial will guide you through the proeccess of setting up and
8testing a very basic CD database using SQLite, with DBIx::Class::Schema
9as the database frontend.
022e0893 10
11The 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
18And 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 28Install DBIx::Class via CPAN should be sufficient.
022e0893 29
a15b8059 30=head3 Create the database/tables.
022e0893 31
a15b8059 32First make and change the directory:
022e0893 33
a15b8059 34 mkdir app
35 cd app
022e0893 36
a15b8059 37This example uses SQLite which is a dependency of DBIx::Class, so you
38shouldn't have to install extra software.
022e0893 39
a15b8059 40Save 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 57and create the sqlite database file:
58
59sqlite3 example.db < example.sql
022e0893 60
61=head3 Set up DBIx::Class::Schema
62
63First, create some dirs and change working directory:
64
f8d800d0 65 mkdir MyDatabase
66 mkdir MyDatabase/Main
022e0893 67
022e0893 68Then, create the following DBIx::Class::Schema classes:
69
f8d800d0 70MyDatabase/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 79MyDatabase/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 92MyDatabase/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 106MyDatabase/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
121insertdb.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
185testdb.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
312It 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
341With these scripts we're relying on @INC looking in the current
f8d800d0 342working directory. You may want to add the MyDatabase namespaces to
343@INC in a different way when it comes to deployemnt.
a15b8059 344
345The testdb.pl script is an excellent start for testing your database
346model.
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