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 <[email protected]>
Louis Moore <[email protected]> PostgreSQL adaptation
=cut
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
