The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

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.