/ 
DBIx-Class-0.082801_01
River stage three • 349 direct dependents • 459 total dependents
/ DBIx::Class::Manual::Example

NAME

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

DESCRIPTION

This tutorial will guide you through the process of setting up and testing a very basic CD database using SQLite, with DBIx::Class::Schema as the database frontend.

The database consists of the following:

table 'artist' with columns:  artistid, name
table 'cd'     with columns:  cdid, artist, title, year
table 'track'  with columns:  trackid, cd, title

And these rules exists:

one artist can have many cds
one cd belongs to one artist
one cd can have many tracks
one track belongs to one cd

Installation

Install DBIx::Class via CPAN should be sufficient.

Create the database/tables

First make and change the directory:

mkdir app
cd app
mkdir db
cd db

This example uses SQLite which is a dependency of DBIx::Class, so you shouldn't have to install extra software.

Save the following into a example.sql in the directory db

CREATE TABLE artist (
  artistid INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE cd (
  cdid INTEGER PRIMARY KEY,
  artist INTEGER NOT NULL REFERENCES artist(artistid),
  title TEXT NOT NULL
);

CREATE TABLE track (
  trackid INTEGER PRIMARY KEY,
  cd INTEGER NOT NULL REFERENCES cd(cdid),
  title TEXT NOT NULL
);

and create the SQLite database file:

sqlite3 example.db < example.sql

Set up DBIx::Class::Schema

Change directory back from db to the directory app:

cd ../

Now create some more directories:

mkdir MyApp
mkdir MyApp/Schema
mkdir MyApp/Schema/Result
mkdir MyApp/Schema/ResultSet

Then, create the following DBIx::Class::Schema classes:

MyApp/Schema.pm:

package MyApp::Schema;
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_namespaces;

1;

MyApp/Schema/Result/Artist.pm:

package MyApp::Schema::Result::Artist;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(qw/ artistid name /);
__PACKAGE__->set_primary_key('artistid');
__PACKAGE__->has_many('cds' => 'MyApp::Schema::Result::Cd');

1;

MyApp/Schema/Result/Cd.pm:

package MyApp::Schema::Result::Cd;
use base qw/DBIx::Class::Core/;
__PACKAGE__->load_components(qw/InflateColumn::DateTime/);
__PACKAGE__->table('cd');
__PACKAGE__->add_columns(qw/ cdid artist title year/);
__PACKAGE__->set_primary_key('cdid');
__PACKAGE__->belongs_to('artist' => 'MyApp::Schema::Result::Artist');
__PACKAGE__->has_many('tracks' => 'MyApp::Schema::Result::Track');

1;

MyApp/Schema/Result/Track.pm:

package MyApp::Schema::Result::Track;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('track');
__PACKAGE__->add_columns(qw/ trackid cd title /);
__PACKAGE__->set_primary_key('trackid');
__PACKAGE__->belongs_to('cd' => 'MyApp::Schema::Result::Cd');

1;

Write a script to insert some records

insertdb.pl

#!/usr/bin/perl

use strict;
use warnings;

use MyApp::Schema;

my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');

my @artists = (['Michael Jackson'], ['Eminem']);
$schema->populate('Artist', [
   [qw/name/],
   @artists,
]);

my %albums = (
  'Thriller' => 'Michael Jackson',
  'Bad' => 'Michael Jackson',
  'The Marshall Mathers LP' => 'Eminem',
);

my @cds;
foreach my $lp (keys %albums) {
  my $artist = $schema->resultset('Artist')->find({
    name => $albums{$lp}
  });
  push @cds, [$lp, $artist->id];
}

$schema->populate('Cd', [
  [qw/title artist/],
  @cds,
]);


my %tracks = (
  'Beat It'         => 'Thriller',
  'Billie Jean'     => 'Thriller',
  'Dirty Diana'     => 'Bad',
  'Smooth Criminal' => 'Bad',
  'Leave Me Alone'  => 'Bad',
  'Stan'            => 'The Marshall Mathers LP',
  'The Way I Am'    => 'The Marshall Mathers LP',
);

my @tracks;
foreach my $track (keys %tracks) {
  my $cdname = $schema->resultset('Cd')->find({
    title => $tracks{$track},
  });
  push @tracks, [$cdname->id, $track];
}

$schema->populate('Track',[
  [qw/cd title/],
  @tracks,
]);

Create and run the test scripts

testdb.pl:

#!/usr/bin/perl

use strict;
use warnings;

use MyApp::Schema;

my $schema = MyApp::Schema->connect('dbi:SQLite:db/example.db');
# for other DSNs, e.g. MySQL, see the perldoc for the relevant dbd
# driver, e.g perldoc L<DBD::mysql>.

get_tracks_by_cd('Bad');
get_tracks_by_artist('Michael Jackson');

get_cd_by_track('Stan');
get_cds_by_artist('Michael Jackson');

get_artist_by_track('Dirty Diana');
get_artist_by_cd('The Marshall Mathers LP');


sub get_tracks_by_cd {
  my $cdtitle = shift;
  print "get_tracks_by_cd($cdtitle):\n";
  my $rs = $schema->resultset('Track')->search(
    {
      'cd.title' => $cdtitle
    },
    {
      join     => [qw/ cd /],
    }
  );
  while (my $track = $rs->next) {
    print $track->title . "\n";
  }
  print "\n";
}

sub get_tracks_by_artist {
  my $artistname = shift;
  print "get_tracks_by_artist($artistname):\n";
  my $rs = $schema->resultset('Track')->search(
    {
      'artist.name' => $artistname
    },
    {
      join => {
        'cd' => 'artist'
      },
    }
  );
  while (my $track = $rs->next) {
    print $track->title . "\n";
  }
  print "\n";
}


sub get_cd_by_track {
  my $tracktitle = shift;
  print "get_cd_by_track($tracktitle):\n";
  my $rs = $schema->resultset('Cd')->search(
    {
      'tracks.title' => $tracktitle
    },
    {
      join     => [qw/ tracks /],
    }
  );
  my $cd = $rs->first;
  print $cd->title . "\n\n";
}

sub get_cds_by_artist {
  my $artistname = shift;
  print "get_cds_by_artist($artistname):\n";
  my $rs = $schema->resultset('Cd')->search(
    {
      'artist.name' => $artistname
    },
    {
      join     => [qw/ artist /],
    }
  );
  while (my $cd = $rs->next) {
    print $cd->title . "\n";
  }
  print "\n";
}



sub get_artist_by_track {
  my $tracktitle = shift;
  print "get_artist_by_track($tracktitle):\n";
  my $rs = $schema->resultset('Artist')->search(
    {
      'tracks.title' => $tracktitle
    },
    {
      join => {
        'cds' => 'tracks'
      }
    }
  );
  my $artist = $rs->first;
  print $artist->name . "\n\n";
}

sub get_artist_by_cd {
  my $cdtitle = shift;
  print "get_artist_by_cd($cdtitle):\n";
  my $rs = $schema->resultset('Artist')->search(
    {
      'cds.title' => $cdtitle
    },
    {
      join     => [qw/ cds /],
    }
  );
  my $artist = $rs->first;
  print $artist->name . "\n\n";
}

It should output:

get_tracks_by_cd(Bad):
Dirty Diana
Smooth Criminal
Leave Me Alone

get_tracks_by_artist(Michael Jackson):
Beat it
Billie Jean
Dirty Diana
Smooth Criminal
Leave Me Alone

get_cd_by_track(Stan):
The Marshall Mathers LP

get_cds_by_artist(Michael Jackson):
Thriller
Bad

get_artist_by_track(Dirty Diana):
Michael Jackson

get_artist_by_cd(The Marshall Mathers LP):
Eminem

Notes

A reference implementation of the database and scripts in this example are available in the main distribution for DBIx::Class under the directory examples/Schema.

With these scripts we're relying on @INC looking in the current working directory. You may want to add the MyApp namespaces to @INC in a different way when it comes to deployment.

The testdb.pl script is an excellent start for testing your database model.

This example uses "load_namespaces" in DBIx::Class::Schema to load in the appropriate Result classes from the MyApp::Schema::Result namespace, and any required ResultSet classes from the MyApp::Schema::ResultSet namespace (although we created the directory in the directions above we did not add, or need to add, any resultset classes).

FURTHER QUESTIONS?

Check the list of additional DBIC resources.

COPYRIGHT AND LICENSE

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.