DBIx::Class, commonly known as DBIC, is a Perl module that maps relational databases to Perl code in an Object Oriented fashion. These pages describe some basic usage to start using DBIC to interrogate a MySQL database.

The Database Model

To use DBIC you first need to setup your Schema - the logical model of the database tables, views and relationships. The DBIC Schema provides an Object Oriented interface for Perl to interact with your database and consists of a number of Perl modules that inherit methods and attributes from DBIx::Class.

To create the Schema from an existing mysql database called 'robodb' on your current computer, use DBIx::Class::Schema::Loader as follows:

use strict;
use warnings;

use DBIx::Class::Schema::Loader qw/make_schema_at/;

make_schema_at(
	'Robots::Schema', 
	{debug => 0, dump_directory => './lib/',
	generate_pod => 0,  },
	['dbi:mysql:robodb:localhost:3306', 'root', ''],
);

Running this script will create a 'lib' directory in your current working directory with the necessary subdirectories of Perl modules at lib/Robots/Schema/. The generated module tree will look like this:

lib/
`-- Robots
    |-- Schema
    |   `-- Result
    |       |-- Manufacturer.pm
    |       `-- Robot.pm
    `-- Schema.pm

3 directories, 3 files

Each file in the lib/Schema/Result directory represents a table in the database as a DBIx::Class resultsource. The Schema.pm file is a DBIx::Class::Schema class and contains a 'load_namespaces' directive, which causes it to load the files in lib/Schema/Result. Thus by using the Robots::Schema we can interact with the database using DBIx::Class methods

Having setup the Schema, we can connect to the database using the 'connect' method and a suitable DSN for the connection:

use Robots::Schema;
my $schema = Robots::Schema->connect('dbi:mysql:mydb:localhost:3306', 'root', '');

Different connection strings can be used to connect to different databases using the same schema, for instance if you have a live and a development server. The connection strings can be stored in a lib/Robots/Model.pm and called accordingly

package Robots::Model;

use strict;
use Robots::Schema;

our $data_dir = '/home/dr00/Robots/data/wptestdata';

sub connect_test() {
	return Robots::Schema->connect('dbi:mysql:robodb:localhost:3306', 'root', '');
}

sub connect_live() {
	return Robots::Schema->connect('dbi:mysql:robodb:localhost:3306', 'root', '');
}

1;

Both the Robots::Model->connect_test and connect_live methods, return a DBIx::Class::Schema connection object

Table Definition

Tables (and Views) are represented as Result Sources in DBIx::Class and are defined in Perl modules located at lib/Robots/Schema/Result/. DBIx::Class::ResultSource provides methods for defining a table:

package Robots::Schema::Result::Robot;

# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("Robot");
__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "serial_no",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "model_no",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "gender",
  {
    data_type => "enum",
    default_value => "U",
    extra => { list => ["M", "F", "U"] },
    is_nullable => 0,
  },
  "manufacture_date",
  { data_type => "datetime", is_nullable => 1 },
  "registration_date",
  {
    data_type     => "timestamp",
    default_value => \"current_timestamp",
    is_nullable   => 0,
  },
  "manufacturer_id",
  { data_type => "integer", is_nullable => 0 },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.07000 @ 2010-07-20 14:34:02
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:P8xGcKAn/xPyX2WPpoYGCg


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

package Robots::Schema::Result::Manufacturer;

# Created by DBIx::Class::Schema::Loader
# DO NOT MODIFY THE FIRST PART OF THIS FILE

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("Manufacturer");
__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0 },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 50 },
);
__PACKAGE__->set_primary_key("id");


# Created by DBIx::Class::Schema::Loader v0.07000 @ 2010-07-20 14:34:02
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:x/YNU10Ud4c2N+ARtoYSyA


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

The basic classes generated by DBIx::Class::Schema::Loader use the table, add_columns and set_primary_key methods, inherited from DBIx::Class, to define the tables in the database. Relationships can be represented using has_many, belongs_to, has_one, many_to_many and might_have methods. For instance the manufacturer_id in the Robots table is a foreign key that references the 'id' field in the Manufacturer table. We can add this relationship to the Robot resultsource using:

__PACKAGE__->belongs_to('manufacturer' , 	# name of accessor method 
	'Robots::Schema::Result::Manufacturer', # related table name
	'manufacturer_id', 			# foreign key field in this table
);

Similarly, each manufacturer will have manufacturered zero or more Robots, and this relationship is represented in Manufacturer.pm as:

__PACKAGE__->has_many('robots', 		# name of accessor 
	'Robots::Schema::Result::Robot', 	# related table name 
	'manufacturer_id' 			# foreign key in related table
);

Having set up the relationship, either method can be used as methods for the resultset:

$schema->resultset('Robot')->find(1)->manufacturer->name;

$schema->resultset('Manufacturer')->find(1)->robots->first->serial_no;

View Definition

The ResultSource files created by the make_schema_at script, will represent your MySQL Views as tables. To create the ResultSources as Views, modify the generated files, adding table_class, is_virtual and view_definition properties:

package Robots::Schema::Result::RobotMaker;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table_class('DBIx::Class::ResultSource::View');

__PACKAGE__->table("RobotMaker");

__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(
	"SELECT t1.serial_no AS serial_no, t1.model_no AS model_no, 
	t2.name AS name
	FROM Robot as t1 join  Manufacturer as t2
	where t1.manufacturer_id = t2.id");	

__PACKAGE__->add_columns(
  "serial_no",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "model_no",
  { data_type => "varchar", is_nullable => 0, size => 50 },
  "name",
  { data_type => "varchar", is_nullable => 0, size => 50 },
);


1;

Table Properties

Various methods are provided by DBIx::Class::ResultSource to query properties of defined resultsources.

use strict;
use warnings;

use Test::More qw/no_plan/;

use Robots::Model;

my $source = Robots::Model->connect_live->source('Robot');

ok($source->has_column('serial_no'), "has_column method");

my $info = $source->column_info('serial_no');
cmp_ok($info->{data_type}, 'eq', 'varchar', 'data_type from column_info method');
cmp_ok($info->{is_nullable}, 'eq', '0', 'is_nullable from column_info method');
cmp_ok($info->{size}, '==', '50', 'size from column_info method');

my @columns = $source->columns;
cmp_ok(scalar(@columns), '==', 7, 'column count from columns method');
cmp_ok($columns[0], 'eq', 'id', 'first column from columns method');
cmp_ok($columns[-1], 'eq', 'manufacturer_id', 'last column from columns method');

my @primaries = $source->primary_columns;
cmp_ok(scalar(@primaries), '==', 1, 'primary key count from primaries method');
cmp_ok($primaries[0], 'eq', 'id', 'first primary key from primaries method');
cmp_ok($primaries[-1], 'eq', 'id', 'last primary key from primaries method');

my @relationships = $source->relationships();
cmp_ok($relationships[0], 'eq', 'manufacturer', 'first relationship from relationships method');

ok($source->has_relationship('manufacturer'), 'has_relationship method');

Constructing Queries

DBIC provides a ResultSet class to represent a database query. The simplest resultset is a query on a table with no conditions specified, equivalent to 'SELECT * FROM tablename'. To construct the ResultSet, call the resultset method on a Schema object, specifying the tablename as the parameter.

my $rs = $schema->resultset('Robot');

Constructing the ResultSet does not execute the query. Instead it simply prepares the SQL statement to be executed later. Complex queries can be constructed from a ResultSet using the search method, which takes two hashref parameters: the first specifies search conditions and the second the attributes for the search

use strict;
use warnings;
use Test::More qw/no_plan/;

use Robots::Model;

my $schema = Robots::Model->connect_live();

my $test;
my $rs;

$test = "SELECT * FROM Robot WHERE gender = 'M'";
$rs = $schema->resultset('Robot')->search(
	{ gender => 'M'} 
);
cmp_ok($rs->first->id, 'eq', '1', $test);

$test = "SELECT * FROM Robot " . 
"WHERE gender = 'M' AND model_no = '23-45-E'";
$rs = $schema->resultset('Robot')->search(
	{ gender => 'M', model_no => '23-45-E' } 
);
cmp_ok($rs->first->id, 'eq', '2', $test);

$test = "SELECT serial_no, model_no FROM Robot";
$rs = $schema->resultset('Robot')->search(
	undef, 
	{ columns => [qw/serial_no model_no/] } 
);
cmp_ok($rs->first->serial_no, "eq", 'FF5886D1A', $test); 
ok(!defined($rs->first->id));

$test = "SELECT serial_no, model_no " . 
"FROM Robot WHERE manufacturer_id = 3";
$rs = $schema->resultset('Robot')->search(
	{ manufacturer_id => 3 }, 
	{ columns => [qw/serial_no model_no/] }
);
cmp_ok($rs->first->serial_no, "eq", 'SD9WE', $test); 
ok(!defined($rs->first->id));

$test = "SELECT id FROM Robot " . 
"WHERE manufacturer_id = 3 " . 
"ORDER BY registration_date DESC";
$rs = $schema->resultset('Robot')->search(
	{ manufacturer_id => 3 }, 
	{ columns => ['id'], 
	  order_by => {-desc => 'registration_date'} }
);
cmp_ok($rs->first->id, '==', '10', $test);

$test = "SELECT me.id FROM Robot me " . 
"JOIN Manufacturer manufacturer " . 
"ON manufacturer.id = me.manufacturer_id " . 
"WHERE manufacturer.name = 'AAA Droids' " . 
"ORDER BY registration_date DESC";
$rs = $schema->resultset('Robot')->search(
	{ 'manufacturer.name' => 'AAA Droids' }, 
	{ join => 'manufacturer',
	  columns => ['id'], 
	  order_by => {-desc => 'registration_date'} }
);
cmp_ok($rs->first->id, '==', '10', $test);

$test = "SELECT me.id manufacturer.id " . 
"FROM Robot me JOIN Manufacturer manufacturer " . 
"ON manufacturer.id = me.manufacturer_id " . 
"WHERE manufacturer.name = 'AAA Droids' " . 
"ORDER BY registration_date DESC";
$rs = $schema->resultset('Robot')->search(
	{ 'manufacturer.name' => 'AAA Droids' }, 
	{ join => 'manufacturer',
	  columns => [qw/me.id manufacturer.id/], 
	  order_by => {-desc => 'registration_date'} }
);
cmp_ok($rs->first->id, '==', '10', $test);
cmp_ok($rs->first->manufacturer->id, '==', '3', "Use relationship to get duplicate column");

$test = "SELECT * FROM Robot " . 
"WHERE model_no LIKE 'Hydro%'";
$rs = $schema->resultset('Robot')->search(
	{ model_no => { 'like', 'Hydro%' } }
);
cmp_ok($rs->first->id, '==', '3', $test);

$test = "SELECT * FROM Robot " . 
"WHERE model_no LIKE 'Hydro%' " . 
"AND manufacture_date LIKE '2007%'";
$rs = $schema->resultset('Robot')->search(
	{ model_no => { 'like', 'Hydro%' },
	  manufacture_date => { 'like', '2007%'} }
);
cmp_ok($rs->first->id, '==', '9', $test);

$test = "SELECT * FROM Robot " . 
"WHERE ( model_no = 'Hydro6' AND gender = 'M' ) " . 
"OR serial_no = '123834732'";
$rs = $schema->resultset('Robot')->search(
  { -or => [
      -and => [ model_no => 'Hydro-6', gender => 'M' ],
      serial_no => '123834732'
    ]
  }
);
cmp_ok($rs->count, '==', 5, $test);

$test = "SELECT model_no, manufacturer_id " .
"FROM Robot GROUP BY model_no, manufacturer_id";
$rs = $schema->resultset('Robot')->search(
	{}, 
	{ columns => [qw/model_no manufacturer_id/],
	  group_by => [qw/model_no manufacturer_id/] }
);
cmp_ok($rs->count, '==', 7, $test);

$test = "SELECT model_no, manufacturer_id " . 
"FROM Robot WHERE " . 
"(model_no IN ('1133G', 'FFAB') or manufacturer_id = 'AA Bots') " .
"AND cost > '23.33' " .
"AND manufacture_date BETWEEN '2007-07-01' AND '2010-01-01'";
$rs = $schema->resultset('Robot')->search({
 	cost => { '>' => '23.33'}, 
	manufacture_date => { 'BETWEEN' => ['2007-07-01', '2010-01-01'] },
	-or => [model_no => { 'IN' => [qw/1133G FFAB/] }, 
		manufacturer_id => 'AA Bots' ] }, 
	{columns => [qw/model_no, manufacturer_id/]}
);
ok($rs->count, $test);

$test = "SELECT name, COUNT( robots.id ) " . 
"FROM Manufacturer me LEFT JOIN Robot robots " . 
"ON robots.manufacturer_id = me.id " . 
"GROUP BY name";
$rs = $schema->resultset('Manufacturer')->search( {},
	{ join => [qw/robots/],
	  select => ['name', { count => 'robots.id' } ],
	  as => [qw/name robo_count/],
	  group_by => [qw/name/]
	}
);
cmp_ok($rs->first->name, 'eq', 'AAA Droids', $test);
cmp_ok($rs->first->get_column('robo_count'), '==', 3, "Use get_column method to get aliased field");

$test = "SELECT name FROM Manufacturer " .
"WHERE id IN " . 
"(SELECT manufacturer_id " . 
	"FROM Robot WHERE id > '4' " . 
	"GROUP BY manufacturer_id )";
my $sub_query_rs = $schema->resultset('Robot')->search(
	{ id => { '>', 4 } },
	{ distinct => 1 }
);
$rs = $schema->resultset('Manufacturer')->search({
	id => { 'IN' => $sub_query_rs->get_column('manufacturer_id')->as_query }},
	{columns => 'name'}
);
cmp_ok($rs->first->name, 'eq', 'AAA Droids', $test);

Executing Queries

When constructing a ResultSet, the query is not executed on the SQL database until one of the following methods is called on the ResultSet:

find

finds a row based on its primary key or unique constraint

next

returns the next element in the resultset. Normally used in a while loop to iterate over each record

all

returns an array of row objects

first

returns an object for the first result

single

returns the row from a query that only produces one row

count

returns a count of the rows for the query

Create DDL from Schema

DBIC provides a create_ddl_dir method, that will translate your schema into Data Definition Language suitable for a variety of different databases. This is useful for transferring your model from one RDBMS to another. Use create_ddl_dir as follows:

use strict;
use warnings;

use Robots::Model;

my $schema = Robots::Model->connect_live();

$schema->create_ddl_dir(['XML', 'MySQL', 'SQLite' ],
	'0.1',
	'.dbiscriptdir/',
	);