1DBIx::Class::Manual::ExUasmeprleC(o3n)tributed Perl DocuDmBeInxt:a:tCiloanss::Manual::Example(3)
2
3
4

NAME

6       DBIx::Class::Manual::Example - Simple CD database example
7

DESCRIPTION

9       This tutorial will guide you through the process of setting up and
10       testing a very basic CD database using SQLite, with DBIx::Class::Schema
11       as the database frontend.
12
13       The database consists of the following:
14
15         table 'artist' with columns:  artistid, name
16         table 'cd'     with columns:  cdid, artist, title
17         table 'track'  with columns:  trackid, cd, title
18
19       And these rules exists:
20
21         one artist can have many cds
22         one cd belongs to one artist
23         one cd can have many tracks
24         one track belongs to one cd
25
26   Installation
27       Install DBIx::Class via CPAN should be sufficient.
28
29       Create the database/tables
30
31       First make and change the directory:
32
33         mkdir app
34         cd app
35         mkdir db
36         cd db
37
38       This example uses SQLite which is a dependency of DBIx::Class, so you
39       shouldn't have to install extra software.
40
41       Save the following into a example.sql in the directory db
42
43         CREATE TABLE artist (
44           artistid INTEGER PRIMARY KEY,
45           name TEXT NOT NULL
46         );
47
48         CREATE TABLE cd (
49           cdid INTEGER PRIMARY KEY,
50           artist INTEGER NOT NULL REFERENCES artist(artistid),
51           title TEXT NOT NULL
52         );
53
54         CREATE TABLE track (
55           trackid INTEGER PRIMARY KEY,
56           cd INTEGER NOT NULL REFERENCES cd(cdid),
57           title TEXT NOT NULL
58         );
59
60       and create the SQLite database file:
61
62         sqlite3 example.db < example.sql
63
64       Set up DBIx::Class::Schema
65
66       Change directory back from db to the directory app:
67
68         cd ../
69
70       Now create some more directories:
71
72         mkdir MyDatabase
73         mkdir MyDatabase/Main
74         mkdir MyDatabase/Main/Result
75         mkdir MyDatabase/Main/ResultSet
76
77       Then, create the following DBIx::Class::Schema classes:
78
79       MyDatabase/Main.pm:
80
81         package MyDatabase::Main;
82         use base qw/DBIx::Class::Schema/;
83         __PACKAGE__->load_namespaces;
84
85         1;
86
87       MyDatabase/Main/Result/Artist.pm:
88
89         package MyDatabase::Main::Result::Artist;
90         use base qw/DBIx::Class::Core/;
91         __PACKAGE__->table('artist');
92         __PACKAGE__->add_columns(qw/ artistid name /);
93         __PACKAGE__->set_primary_key('artistid');
94         __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Result::Cd');
95
96         1;
97
98       MyDatabase/Main/Result/Cd.pm:
99
100         package MyDatabase::Main::Result::Cd;
101         use base qw/DBIx::Class::Core/;
102         __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
103         __PACKAGE__->table('cd');
104         __PACKAGE__->add_columns(qw/ cdid artist title/);
105         __PACKAGE__->set_primary_key('cdid');
106         __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Result::Artist');
107         __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Result::Track');
108
109         1;
110
111       MyDatabase/Main/Result/Track.pm:
112
113         package MyDatabase::Main::Result::Track;
114         use base qw/DBIx::Class::Core/;
115         __PACKAGE__->table('track');
116         __PACKAGE__->add_columns(qw/ trackid cd title /);
117         __PACKAGE__->set_primary_key('trackid');
118         __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Result::Cd');
119
120         1;
121
122       Write a script to insert some records
123
124       insertdb.pl
125
126         #!/usr/bin/perl -w
127
128         use MyDatabase::Main;
129         use strict;
130
131         my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
132
133         #  here's some of the SQL that is going to be generated by the schema
134         #  INSERT INTO artist VALUES (NULL,'Michael Jackson');
135         #  INSERT INTO artist VALUES (NULL,'Eminem');
136
137         my @artists = (['Michael Jackson'], ['Eminem']);
138         $schema->populate('Artist', [
139            [qw/name/],
140            @artists,
141         ]);
142
143         my %albums = (
144           'Thriller' => 'Michael Jackson',
145           'Bad' => 'Michael Jackson',
146           'The Marshall Mathers LP' => 'Eminem',
147         );
148
149         my @cds;
150         foreach my $lp (keys %albums) {
151           my $artist = $schema->resultset('Artist')->find({
152             name => $albums{$lp}
153           });
154           push @cds, [$lp, $artist->id];
155         }
156
157         $schema->populate('Cd', [
158           [qw/title artist/],
159           @cds,
160         ]);
161
162
163         my %tracks = (
164           'Beat It'         => 'Thriller',
165           'Billie Jean'     => 'Thriller',
166           'Dirty Diana'     => 'Bad',
167           'Smooth Criminal' => 'Bad',
168           'Leave Me Alone'  => 'Bad',
169           'Stan'            => 'The Marshall Mathers LP',
170           'The Way I Am'    => 'The Marshall Mathers LP',
171         );
172
173         my @tracks;
174         foreach my $track (keys %tracks) {
175           my $cdname = $schema->resultset('Cd')->find({
176             title => $tracks{$track},
177           });
178           push @tracks, [$cdname->id, $track];
179         }
180
181         $schema->populate('Track',[
182           [qw/cd title/],
183           @tracks,
184         ]);
185
186       Create and run the test scripts
187
188       testdb.pl:
189
190         #!/usr/bin/perl -w
191
192         use MyDatabase::Main;
193         use strict;
194
195         my $schema = MyDatabase::Main->connect('dbi:SQLite:db/example.db');
196         # for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd
197         # driver, e.g perldoc L<DBD::mysql>.
198
199         get_tracks_by_cd('Bad');
200         get_tracks_by_artist('Michael Jackson');
201
202         get_cd_by_track('Stan');
203         get_cds_by_artist('Michael Jackson');
204
205         get_artist_by_track('Dirty Diana');
206         get_artist_by_cd('The Marshall Mathers LP');
207
208
209         sub get_tracks_by_cd {
210           my $cdtitle = shift;
211           print "get_tracks_by_cd($cdtitle):\n";
212           my $rs = $schema->resultset('Track')->search(
213             {
214               'cd.title' => $cdtitle
215             },
216             {
217               join     => [qw/ cd /],
218             }
219           );
220           while (my $track = $rs->next) {
221             print $track->title . "\n";
222           }
223           print "\n";
224         }
225
226         sub get_tracks_by_artist {
227           my $artistname = shift;
228           print "get_tracks_by_artist($artistname):\n";
229           my $rs = $schema->resultset('Track')->search(
230             {
231               'artist.name' => $artistname
232             },
233             {
234               join => {
235                 'cd' => 'artist'
236               },
237             }
238           );
239           while (my $track = $rs->next) {
240             print $track->title . "\n";
241           }
242           print "\n";
243         }
244
245
246         sub get_cd_by_track {
247           my $tracktitle = shift;
248           print "get_cd_by_track($tracktitle):\n";
249           my $rs = $schema->resultset('Cd')->search(
250             {
251               'tracks.title' => $tracktitle
252             },
253             {
254               join     => [qw/ tracks /],
255             }
256           );
257           my $cd = $rs->first;
258           print $cd->title . "\n\n";
259         }
260
261         sub get_cds_by_artist {
262           my $artistname = shift;
263           print "get_cds_by_artist($artistname):\n";
264           my $rs = $schema->resultset('Cd')->search(
265             {
266               'artist.name' => $artistname
267             },
268             {
269               join     => [qw/ artist /],
270             }
271           );
272           while (my $cd = $rs->next) {
273             print $cd->title . "\n";
274           }
275           print "\n";
276         }
277
278
279
280         sub get_artist_by_track {
281           my $tracktitle = shift;
282           print "get_artist_by_track($tracktitle):\n";
283           my $rs = $schema->resultset('Artist')->search(
284             {
285               'tracks.title' => $tracktitle
286             },
287             {
288               join => {
289                 'cds' => 'tracks'
290               }
291             }
292           );
293           my $artist = $rs->first;
294           print $artist->name . "\n\n";
295         }
296
297         sub get_artist_by_cd {
298           my $cdtitle = shift;
299           print "get_artist_by_cd($cdtitle):\n";
300           my $rs = $schema->resultset('Artist')->search(
301             {
302               'cds.title' => $cdtitle
303             },
304             {
305               join     => [qw/ cds /],
306             }
307           );
308           my $artist = $rs->first;
309           print $artist->name . "\n\n";
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

Notes

340       A reference implementation of the database and scripts in this example
341       are available in the main distribution for DBIx::Class under the
342       directory t/examples/Schema.
343
344       With these scripts we're relying on @INC looking in the current working
345       directory.  You may want to add the MyDatabase namespaces to @INC in a
346       different way when it comes to deployment.
347
348       The testdb.pl script is an excellent start for testing your database
349       model.
350
351       This example uses "load_namespaces" in DBIx::Class::Schema to load in
352       the appropriate Row classes from the MyDatabase::Main::Result
353       namespace, and any required resultset classes from the
354       MyDatabase::Main::ResultSet namespace (although we created the
355       directory in the directions above we did not add, or need to add, any
356       resultset classes).
357

TODO

AUTHOR

360         sc_ from irc.perl.org#dbix-class
361         Kieren Diment <kd@totaldatasolution.com>
362         Nigel Metheringham <nigelm@cpan.org>
363
364
365
366perl v5.12.0                      2010-05-12   DBIx::Class::Manual::Example(3)