Database structure

To use DBIx::Class, we need to teach it about the layout of the underlying database. Several methods are supported. The built-in method involves defining your database structure as a set of perl modules. The other oft used method is to import the definitions from an existing database using the module DBIx::Class::Schema::Loader.

Using Loader

TODO

Manual Result class creation (and understanding Loader results)

CREATE TABLE

Standard basic table creation in SQL:

CREATE TABLE users (
   id INTEGER AUTO_INCREMENT,
   username VARCHAR(255),
   dob DATE,
   realname VARCHAR(255),
   password VARCHAR(255)
);

We'll get to tables with references (foreign keys) later, here's the translation to DBIx::Class:

The recommended version:

package MyDatabase::Schema::Result::User;
use strict;
use warnings;

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

__PACKAGE__->table('users');
__PACKAGE__->add_columns(
  id => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  username => {
    data_type => 'varchar',
    size => 255,
  },
  dob => {
    data_type => 'date',
  },
  realname => {
    data_type => 'varchar',
    size => 255,
  },
  password => {
    data_type => 'varchar',
    size => 255,
  },
 );
 __PACKAGE__->set_primary_key('id');
 __PACKAGE__->add_unique_constraint('uniq_username' => ['username']);
 1;

The snappy version:

package MyDatabase::Schema::Result::User;
use strict;
use warnings;

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

__PACKAGE__->table('users');
__PACKAGE__->add_columns(
  id => {
    data_type => 'integer',
    is_auto_increment => 1,
  },
  qw/username dob realname password/
 );
 __PACKAGE__->set_primary_key('id');

 1;

This shows a minimal Result class to represent our "users" table. DBIx::Class itself does not use or care about the field types or lengths. However many external components exist on CPAN, and some of may use this information.

Table creation with references:

A relational database isn't worth much if we don't actually use references and constraints, so here is an example which constrains the user_id column to only contain id values from the users table.

CREATE TABLE posts (
  id INTEGER AUTO_INCREMENT,
  user_id INTEGER,
  created_date DATETIME,
  title VARCHAR(255),
  post TEXT,
  INDEX posts_idx_user_id (user_id),
  PRIMARY KEY (id),
  CONSTRAINT posts_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id)
);

In DBIx::Class this is achieved by adding relationship definitions to the class:

package MyDatabase::Schema::Result::Post;
use strict;
use warnings;
use base 'DBIx::Class::Core';

__PACKAGE__->table('posts');
__PACKAGE__->add_columns(
    id => {
        data_type => 'integer',
        is_auto_increment => 1,
    },
    user_id => {
      data_type => 'integer',
    },
    created_date => {
      data_type => 'datetime',
    },
    title => {
      data_type => 'varchar',
      size => 255,
    },
    post => {
      data_type => 'text',
    },
 );

__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to('user', 'MyDatabase::Schema::Result::User', 'user_id');
1;

The belongs_to relation allows us to refer to the user who authored a post as the object representing the user, rather than as just the integer tha database uses as the linking information.

To allow access from the user object back to the posts they have written, we need to define another relationship in the User class:

__PACKAGE__->has_many('posts', 'MyDatabase::Schema::Result::Post', 'user_id');

CREATE VIEW

In SQL, a simple view that returns all users and their posts:

CREATE VIEW userposts 
AS
SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
FROM users 
JOIN posts ON (users.id = posts.user_id)

In DBIx::Class this can have a Result Class of its own:

package MyDatabase::Schema::Result::UserPosts;

use base qw/DBIx::Class::Core/;

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

__PACKAGE__->table('user_posts');
__PACKAGE__->result_source_instance->is_virtual(1);
__PACKAGE__->result_source_instance->view_definition(
"SELECT posts.user_id, users.username, users.dob, users.realname, posts.createddate, posts.title, posts.post
FROM users 
JOIN posts ON (users.id = posts.user_id)"
);
__PACKAGE__->add_columns(
    user_id => {
      data_type => 'integer',
    },
  username => {
    data_type => 'varchar',
    size => 255,
  },
  dob => {
    data_type => 'date',
  },
  realname => {
    data_type => 'varchar',
    size => 255,
  },
  created_date => {
    data_type => 'datetime',
  },
  title => {
    data_type => 'varchar',
    size => 255,
  },
  post => {
    data_type => 'text',
  },

);
__PACKAGE__->set_primary_key('user_id, post_id');

CREATE INDEX

For a detailed explanation on how these work, refer to Describing your database in the Tutorial.

@draft

Last modified: 2012-02-06T12:15:05

Home

Other pages

Things I like