[Dbix-class] PostgreSQL version of DBIx::Class::Manual::Example

lbmlist at hethcote.com lbmlist at hethcote.com
Tue Feb 3 22:40:20 GMT 2009


I did a translation from sqlite to postgres in case anyone can use it I'm 
sending it along.

=head1 NAME

DBIx::Class::Manual::ExamplePg - Simple CD PostgreSQL database example

=head1 DESCRIPTION

This tutorial will guide you through the process of setting up and
testing a very basic CD database using PostgreSQL, 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
   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


=head2 Installation

Install DBIx::Class via CPAN should be sufficient.

=head3 Create the database/tables.

First make and change the directory:

   mkdir pgapp
   cd pgapp
   mkdir db
   cd db

This example uses PostgreSQL.

Create a role to own the database and a database

     $ createuser -P dbicuser
     Enter password for new role: <dbicpass>
     Enter it again: <dbicpass>
     Shall the new role be a superuser? (y/n) n
     Shall the new role be allowed to create databases? (y/n) n
     Shall the new role be allowed to create more new roles? (y/n) n
     CREATE ROLE
     $ createdb -O dbicuser example
     CREATE DATABASE

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

   SET default_tablespace = '';
   SET default_with_oids = false;


   CREATE SEQUENCE artist_seq
      INCREMENT BY 1
      NO MAXVALUE
      NO MINVALUE
      CACHE 1;

   CREATE TABLE artist (
      artistid INTEGER DEFAULT nextval('artist_seq'::regclass) NOT NULL PRIMARY KEY,
      name TEXT NOT NULL
    );

   CREATE SEQUENCE cd_seq
       INCREMENT BY 1
       NO MAXVALUE
       NO MINVALUE
       CACHE 1;

   CREATE TABLE cd (
     cdid INTEGER  DEFAULT nextval('cd_seq'::regclass) PRIMARY KEY,
     artist INTEGER NOT NULL REFERENCES artist(artistid),
     title TEXT NOT NULL
   );


   CREATE SEQUENCE track_seq
       INCREMENT BY 1
       NO MAXVALUE
       NO MINVALUE
       CACHE 1;

   CREATE TABLE track (
     trackid INTEGER  DEFAULT nextval('track_seq'::regclass)  PRIMARY KEY,
       cd      INTEGER NOT NULL REFERENCES cd(cdid),
       title   TEXT NOT NULL
   );


and create the postgresql database:

   $ psql -Udbicuser example
   Password for user dbicuser: <dbicpass>
   example=> \i example.sql
   SET
   SET
   CREATE SEQUENCE
   psql:example.sql:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "artist_pkey" for table "artist"
   CREATE TABLE
   CREATE SEQUENCE
   psql:example.sql:26: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "cd_pkey" for table "cd"
   CREATE TABLE
   CREATE SEQUENCE
psql:example.sql:40: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "track_pkey" for table "track"
   CREATE TABLE
   example=> \dt
            List of relations
    Schema |  Name  | Type  |  Owner
   --------+--------+-------+----------
    public | artist | table | dbicuser
    public | cd     | table | dbicuser
    public | track  | table | dbicuser
   (3 rows)


head3 Set up DBIx::Class::Schema

Change directory back from db to the directory app:

   cd ../

Now create a temporary perl module tmpMyDatabase.pm:

   package MyDatabase::Main;
   use base qw/DBIx::Class::Schema::Loader/;

   __PACKAGE__->loader_options(
       relationships           => 1,
       # debug                 => 1,
   );


and a script MyDatabase.pl to build the stuff:

   use tmpMyDatabase qw/ Main /;

   MyDatabase::Main->dump_to_dir('./');

   MyDatabase:Main->connect('dbi:Pg:dbname=example','dbicuser','dbicpass');

Then, create the DBIx::Class::Schema classes by running the script:

   $ perl MyDatabase.pl
   Dumping manual schema for MyDatabase::Main to directory ./ ...
   Schema dump completed.

Which creates:

MyDatabase/

MyDatabase/Main.pm:

   package MyDatabase::Main;

   use strict;
   use warnings;

   use base 'DBIx::Class::Schema';

   __PACKAGE__->load_classes;


   # Created by DBIx::Class::Schema::Loader v0.04005 @ 2009-02-03 11:12:35
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:T192VIBub++Oxb5S9bpUYA


   # You can replace this text with custom content, and it will be preserved on regeneration
   1;


MyDatabase/Main/Artist.pm:

   package MyDatabase::Main::Artist;

   use strict;
   use warnings;

   use base 'DBIx::Class';

   __PACKAGE__->load_components("Core");
   __PACKAGE__->table("artist");
   __PACKAGE__->add_columns(
     "artistid",
     {
       data_type => "integer",
       default_value => "nextval('artist_seq'::regclass)",
       is_nullable => 0,
       size => 4,
     },
     "name",
     {
       data_type => "text",
       default_value => undef,
       is_nullable => 0,
       size => undef,
     },
   );
   __PACKAGE__->set_primary_key("artistid");
   __PACKAGE__->add_unique_constraint("artist_pkey", ["artistid"]);
   __PACKAGE__->has_many(
     "cds",
     "MyDatabase::Main::Cd",
     { "foreign.artist" => "self.artistid" },
   );


   # Created by DBIx::Class::Schema::Loader v0.04005 @ 2009-02-03 11:12:35
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:FaUB1nMwHsLk4JiRq70Ezg


   # You can replace this text with custom content, and it will be preserved on regeneration
   1;


MyDatabase/Main/Cd.pm:

   package MyDatabase::Main::Cd;

   use strict;
   use warnings;

   use base 'DBIx::Class';

   __PACKAGE__->load_components("Core");
   __PACKAGE__->table("cd");
   __PACKAGE__->add_columns(
     "cdid",
     {
       data_type => "integer",
       default_value => "nextval('cd_seq'::regclass)",
       is_nullable => 0,
       size => 4,
     },
     "artist",
     { data_type => "integer", default_value => undef, is_nullable => 0, size => 4 },
     "title",
     {
       data_type => "text",
       default_value => undef,
       is_nullable => 0,
       size => undef,
     },
   );
   __PACKAGE__->set_primary_key("cdid");
   __PACKAGE__->add_unique_constraint("cd_pkey", ["cdid"]);
   __PACKAGE__->belongs_to("artist", "MyDatabase::Main::Artist", { artistid => "artist" });
   __PACKAGE__->has_many(
     "tracks",
     "MyDatabase::Main::Track",
     { "foreign.cd" => "self.cdid" },
   );


   # Created by DBIx::Class::Schema::Loader v0.04005 @ 2009-02-03 11:12:35
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:TJDDzBtZ/SYKObB9Pg3lvA


   # You can replace this text with custom content, and it will be preserved on regeneration
   1;


MyDatabase/Main/Track.pm:

   package MyDatabase::Main::Track;

   use strict;
   use warnings;

   use base 'DBIx::Class';

   __PACKAGE__->load_components("Core");
   __PACKAGE__->table("track");
   __PACKAGE__->add_columns(
     "trackid",
     {
       data_type => "integer",
       default_value => "nextval('track_seq'::regclass)",
       is_nullable => 0,
       size => 4,
     },
     "cd",
     { data_type => "integer", default_value => undef, is_nullable => 0, size => 4 },
     "title",
     {
       data_type => "text",
       default_value => undef,
       is_nullable => 0,
       size => undef,
     },
   );
   __PACKAGE__->set_primary_key("trackid");
   __PACKAGE__->add_unique_constraint("track_pkey", ["trackid"]);
   __PACKAGE__->belongs_to("cd", "MyDatabase::Main::Cd", { cdid => "cd" });


   # Created by DBIx::Class::Schema::Loader v0.04005 @ 2009-02-03 11:12:35
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:V6Mb/346FC4IML71CJ3puw


   # You can replace this text with custom content, and it will be preserved on regeneration
   1;


=head3 Write a script to insert some records.

insertdb.pl

   #!/usr/bin/perl -w

   use MyDatabase::Main;
   use strict;

   my $schema = MyDatabase::Main->connect('dbi:Pg:dbname=example','dbicuser','dbicpass');

   #  here's some of the sql that is going to be generated by the schema
   #  INSERT INTO artist VALUES (NULL,'Michael Jackson');
   #  INSERT INTO artist VALUES (NULL,'Eminem');

   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')->search({
       name => $albums{$lp}
     });
     push @cds, [$lp, $artist->first];
   }

   $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')->search({
       title => $tracks{$track},
     });
     push @tracks, [$cdname->first, $track];
   }

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

=head3 Create and run the test scripts

testdb.pl:

   #!/usr/bin/perl -w

   use MyDatabase::Main;
   use strict;

   my $schema = MyDatabase::Main->connect('dbi:Pg:dbname=example','dbicuser','dbicpass');
   # 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 /],
         prefetch => [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 /],
         prefetch => [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

=head1 Notes

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

With these scripts we're relying on @INC looking in the current
working directory.  You may want to add the MyDatabase 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.

=head1 TODO

=head1 AUTHOR

   sc_ from irc.perl.org#dbix-class
   Kieren Diment <kd at totaldatasolution.com>
   Louis Moore <lbmoore at hethcote.com> PostgreSQL adaptation

=cut



More information about the DBIx-Class mailing list