3 DBIx::Class::Manual::Example - Simple CD database example
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.
11 The database consists of the following:
13 table 'artist' with columns: artistid, name
14 table 'cd' with columns: cdid, artist, title
15 table 'track' with columns: trackid, cd, title
18 And these rules exists:
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
28 Install DBIx::Class via CPAN should be sufficient.
30 =head3 Create the database/tables.
32 First make and change the directory:
37 This example uses SQLite which is a dependency of DBIx::Class, so you
38 shouldn't have to install extra software.
40 Save the following into a example.sql
43 artistid INTEGER PRIMARY KEY,
48 cdid INTEGER PRIMARY KEY,
49 artist INTEGER NOT NULL REFERENCES artist(artistid),
53 trackid INTEGER PRIMARY KEY,
54 cd INTEGER NOT NULL REFERENCES cd(cdid),
55 title TEXT NOT NULL) ;
57 and create the sqlite database file:
59 sqlite3 example.db < example.sql
61 =head3 Set up DBIx::Class::Schema
63 First, create some dirs and change working directory:
68 Then, create the following DBIx::Class::Schema classes:
73 use base qw/DBIx::Class::Schema/;
74 __PACKAGE__->load_classes(qw/Artist Cd Track/);
81 package DB::Main::Artist;
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');
87 __PACKAGE__->has_many('cds' => 'DB::Main::Cd');
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');
100 __PACKAGE__->belongs_to('artist' => 'DB::Main::Artist');
101 __PACKAGE__->has_many('tracks' => 'DB::Main::Track');
108 package DB::Main::Track;
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');
114 __PACKAGE__->belongs_to('cd' => 'DB::Main::Cd');
119 =head3 Write a script to insert some records.
128 my $schema = DB::Main->connect('dbi:SQLite:example.db');
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');
134 my @artists = (['Michael Jackson'], ['Eminem']);
135 $schema->populate('Artist', [
141 'Thriller' => 'Michael Jackson',
142 'Bad' => 'Michael Jackson',
143 'The Marshall Mathers LP' => 'Eminem',
147 foreach my $lp (keys %albums) {
148 my $artist = $schema->resultset('Artist')->search({
151 push @cds, [$lp, $artist->first];
154 $schema->populate('Cd', [
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',
171 foreach my $track (keys %tracks) {
172 my $cdname = $schema->resultset('Cd')->search({
173 title => $tracks{$track},
175 push @tracks, [$cdname->first, $track];
178 $schema->populate('Track',[
183 =head3 Create and run the scripts
192 my $schema = DB::Main->connect('dbi:SQLite:example.db');
194 get_tracks_by_cd('Bad');
195 get_tracks_by_artist('Michael Jackson');
197 get_cd_by_track('Stan');
198 get_cds_by_artist('Michael Jackson');
200 get_artist_by_track('Dirty Diana');
201 get_artist_by_cd('The Marshall Mathers LP');
204 sub get_tracks_by_cd {
206 print "get_tracks_by_cd($cdtitle):\n";
207 my $rs = $schema->resultset('Track')->search(
209 'cd.title' => $cdtitle
213 prefetch => [qw/ cd /]
216 while (my $track = $rs->next) {
217 print $track->title . "\n";
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(
227 'artist.name' => $artistname
235 while (my $track = $rs->next) {
236 print $track->title . "\n";
243 sub get_cd_by_track {
244 my $tracktitle = shift;
245 print "get_cd_by_track($tracktitle):\n";
246 my $rs = $schema->resultset('Cd')->search(
248 'tracks.title' => $tracktitle
251 join => [qw/ tracks /],
255 print $cd->title . "\n\n";
258 sub get_cds_by_artist {
259 my $artistname = shift;
260 print "get_cds_by_artist($artistname):\n";
261 my $rs = $schema->resultset('Cd')->search(
263 'artist.name' => $artistname
266 join => [qw/ artist /],
267 prefetch => [qw/ artist /]
270 while (my $cd = $rs->next) {
271 print $cd->title . "\n";
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(
283 'tracks.title' => $tracktitle
291 my $artist = $rs->first;
292 print $artist->name . "\n\n";
295 sub get_artist_by_cd {
297 print "get_artist_by_cd($cdtitle):\n";
298 my $rs = $schema->resultset('Artist')->search(
300 'cds.title' => $cdtitle
306 my $artist = $rs->first;
307 print $artist->name . "\n\n";
314 get_tracks_by_cd(Bad):
319 get_tracks_by_artist(Michael Jackson):
326 get_cd_by_track(Stan):
327 The Marshall Mathers LP
329 get_cds_by_artist(Michael Jackson):
333 get_artist_by_track(Dirty Diana):
336 get_artist_by_cd(The Marshall Mathers LP):
341 With these scripts we're relying on @INC looking in the current
342 working directory. You may want to add the DB namespaces to @INC in a
343 different way when it comes to deployemnt.
345 The testdb.pl script is an excellent start for testing your database
352 sc_ from IRC. Please credit yourself properly!
353 Kieren Diment <kd@totaldatasolution.com>