3 DBIx::Class::Manual::Example - Simple CD database example
7 This tutorial will guide you through the process 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),
54 trackid INTEGER PRIMARY KEY,
55 cd INTEGER NOT NULL REFERENCES cd(cdid),
59 and create the sqlite database file:
61 sqlite3 example.db < example.sql
63 =head3 Set up DBIx::Class::Schema
65 First, create some dirs and change working directory:
70 Then, create the following DBIx::Class::Schema classes:
74 package MyDatabase::Main;
75 use base qw/DBIx::Class::Schema/;
76 __PACKAGE__->load_classes(qw/Artist Cd Track/);
81 MyDatabase/Main/Artist.pm:
83 package MyDatabase::Main::Artist;
84 use base qw/DBIx::Class/;
85 __PACKAGE__->load_components(qw/Core/);
86 __PACKAGE__->table('artist');
87 __PACKAGE__->add_columns(qw/ artistid name /);
88 __PACKAGE__->set_primary_key('artistid');
89 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
94 MyDatabase/Main/Cd.pm:
96 package MyDatabase::Main::Cd;
97 use base qw/DBIx::Class/;
98 __PACKAGE__->load_components(qw/Core/);
99 __PACKAGE__->table('cd');
100 __PACKAGE__->add_columns(qw/ cdid artist title/);
101 __PACKAGE__->set_primary_key('cdid');
102 __PACKAGE__->belongs_to('artist' => 'MyDatabase::Main::Artist');
103 __PACKAGE__->has_many('tracks' => 'MyDatabase::Main::Track');
108 MyDatabase/Main/Track.pm:
110 package MyDatabase::Main::Track;
111 use base qw/DBIx::Class/;
112 __PACKAGE__->load_components(qw/Core/);
113 __PACKAGE__->table('track');
114 __PACKAGE__->add_columns(qw/ trackid cd title/);
115 __PACKAGE__->set_primary_key('trackid');
116 __PACKAGE__->belongs_to('cd' => 'MyDatabase::Main::Cd');
121 =head3 Write a script to insert some records.
127 use MyDatabase::Main;
130 my $schema = MyDatabase::Main->connect('dbi:SQLite:example.db');
132 # here's some of the sql that is going to be generated by the schema
133 # INSERT INTO artist VALUES (NULL,'Michael Jackson');
134 # INSERT INTO artist VALUES (NULL,'Eminem');
136 my @artists = (['Michael Jackson'], ['Eminem']);
137 $schema->populate('Artist', [
143 'Thriller' => 'Michael Jackson',
144 'Bad' => 'Michael Jackson',
145 'The Marshall Mathers LP' => 'Eminem',
149 foreach my $lp (keys %albums) {
150 my $artist = $schema->resultset('Artist')->search({
153 push @cds, [$lp, $artist->first];
156 $schema->populate('Cd', [
163 'Beat It' => 'Thriller',
164 'Billie Jean' => 'Thriller',
165 'Dirty Diana' => 'Bad',
166 'Smooth Criminal' => 'Bad',
167 'Leave Me Alone' => 'Bad',
168 'Stan' => 'The Marshall Mathers LP',
169 'The Way I Am' => 'The Marshall Mathers LP',
173 foreach my $track (keys %tracks) {
174 my $cdname = $schema->resultset('Cd')->search({
175 title => $tracks{$track},
177 push @tracks, [$cdname->first, $track];
180 $schema->populate('Track',[
185 =head3 Create and run the scripts
191 use MyDatabase::Main;
194 my $schema = MyDatabase::Main->connect('dbi:SQLite:example.db');
196 get_tracks_by_cd('Bad');
197 get_tracks_by_artist('Michael Jackson');
199 get_cd_by_track('Stan');
200 get_cds_by_artist('Michael Jackson');
202 get_artist_by_track('Dirty Diana');
203 get_artist_by_cd('The Marshall Mathers LP');
206 sub get_tracks_by_cd {
208 print "get_tracks_by_cd($cdtitle):\n";
209 my $rs = $schema->resultset('Track')->search(
211 'cd.title' => $cdtitle
215 prefetch => [qw/ cd /]
218 while (my $track = $rs->next) {
219 print $track->title . "\n";
224 sub get_tracks_by_artist {
225 my $artistname = shift;
226 print "get_tracks_by_artist($artistname):\n";
227 my $rs = $schema->resultset('Track')->search(
229 'artist.name' => $artistname
237 while (my $track = $rs->next) {
238 print $track->title . "\n";
244 sub get_cd_by_track {
245 my $tracktitle = shift;
246 print "get_cd_by_track($tracktitle):\n";
247 my $rs = $schema->resultset('Cd')->search(
249 'tracks.title' => $tracktitle
252 join => [qw/ tracks /],
256 print $cd->title . "\n\n";
259 sub get_cds_by_artist {
260 my $artistname = shift;
261 print "get_cds_by_artist($artistname):\n";
262 my $rs = $schema->resultset('Cd')->search(
264 'artist.name' => $artistname
267 join => [qw/ artist /],
268 prefetch => [qw/ artist /]
271 while (my $cd = $rs->next) {
272 print $cd->title . "\n";
279 sub get_artist_by_track {
280 my $tracktitle = shift;
281 print "get_artist_by_track($tracktitle):\n";
282 my $rs = $schema->resultset('Artist')->search(
284 'tracks.title' => $tracktitle
292 my $artist = $rs->first;
293 print $artist->name . "\n\n";
296 sub get_artist_by_cd {
298 print "get_artist_by_cd($cdtitle):\n";
299 my $rs = $schema->resultset('Artist')->search(
301 'cds.title' => $cdtitle
307 my $artist = $rs->first;
308 print $artist->name . "\n\n";
315 get_tracks_by_cd(Bad):
320 get_tracks_by_artist(Michael Jackson):
327 get_cd_by_track(Stan):
328 The Marshall Mathers LP
330 get_cds_by_artist(Michael Jackson):
334 get_artist_by_track(Dirty Diana):
337 get_artist_by_cd(The Marshall Mathers LP):
342 With these scripts we're relying on @INC looking in the current
343 working directory. You may want to add the MyDatabase namespaces to
344 @INC in a different way when it comes to deployment.
346 The testdb.pl script is an excellent start for testing your database
353 sc_ from irc.perl.org#dbix-class
354 Kieren Diment <kd@totaldatasolution.com>