NAME

DBIx::Sunny::Schema - SQL Class Builder

SYNOPSIS

package MyProj::Data::DB;

use parent qw/DBIx::Sunny::Schema/;
use Mouse::Util::TypeConstraints;

subtype 'Uint'
    => as 'Int'
    => where { $_ >= 0 };

subtype 'Natural'
    => as 'Int'
    => where { $_ > 0 };

enum 'Flag' => qw/1 0/;

no Mouse::Util::TypeConstraints;

__PACKAGE__->select_one(
    'max_id',
    'SELECT max(id) FROM member'
);

__PACKAGE__->select_row(
    'member',
    id => { isa => 'Natural' }
    'SELECT * FROM member WHERE id=?',
);

__PACAKGE__->select_all(
    'recent_article',
    public => { isa => 'Flag', default => 1 },
    offset => { isa => 'Uint', default => 0 },
    limit  => { isa => 'Uint', default => 10 },
    'SELECT * FROM articles WHERE public=? ORDER BY created_on LIMIT ?,?',
);

__PACAKGE__->select_all(
    'recent_article',
    id  => { isa => 'ArrayRef[Uint]' },
    'SELECT * FROM articles WHERE id IN(?)',
);
# This method rewrites query like 'id IN (?,?..)' with Array's value number

__PACKAGE__->query(
    'add_article',
    member_id => 'Natural',
    flag => { isa => 'Flag', default => '1' },
    subject => 'Str',
    body => 'Str',
    created_on => { isa => .. },
    <<SQL);
INSERT INTO articles (member_id, public, subject, body, created_on) 
VALUES ( ?, ?, ?, ?, ?)',
SQL

__PACKAGE__->select_one(
    'article_count_by_member',
    member_id => 'Natural',
    'SELECT COUNT(*) FROM articles WHERE member_id = ?',
);

__PACKAGE__->query(
    'update_member_article_count',
    article_count => 'Uint',
    id => 'Natural'
    'UPDATE member SET article_count = ? WHERE id = ?',
);
  
...

package main;

use MyProj::Data::DB;
use DBIx::Sunny;

my $dbh = DBIx::Sunny->connect(...);
my $db = MyProj::Data::DB->new(dbh=>$dbh,readonly=>0);

my $max = $db->max_id;
my $member_hashref = $db->member(id=>100); 
# my $member = $db->member(id=>'abc');  #validator error

my $article_arrayref = $db->recent_article( offset => 10 );

{
    my $txn = $db->dbh->txn_scope;
    $db->add_article(
        member_id => $id,
        subject => $subject,
        body => $body,
        created_on => 
    );
    my $last_insert_id = $db->dbh->last_insert_id;
    my $count = $db->article_count_by_member( id => $id );
    $db->update_member_article_count(
        article_count => $count,
        id => $id
    );
    $txn->commit;
}

DESCRIPTION

BUILDER CLASS METHODS

__PACKAGE__->select_one( $method_name, @validators, $sql );

build a select_one method named $method_name with validator. validators arguments are passed for Data::Validator. you can use Mouse's type constraint. Type constraint are also used for SQL's bind type determination.

__PACKAGE__->select_row( $method_name, @validators, $sql, [\&filter] );

build a select_row method named $method_name with validator. If a last argument is CodeRef, this CodeRef will be applied for a result row.

__PACKAGE__->select_all( $method_name, @validators, $sql, [\&filter] );

build a select_all method named $method_name with validator. If a last argument is CodeRef, this CodeRef will be applied for all result row.

__PACKAGE__->query( $method_name, @validators, $sql );

build a query method named $method_name with validator.

FILTERING and DEFLATING

FILTERING

If you passed CodeRef to builder, this CodeRef will be applied for results.

__PACAKGE__->select_all(
    'recent_article',
    limit  => { isa => 'Uint', default => 10 },
    'SELECT * FROM articles WHERE ORDER BY created_on LIMIT ?',
    sub {
        my ($row,$self)= @_;
        $row->{created_on} = DateTime::Format::MySQL->parse_datetime($row->{created_on});
        $row->{created_on}->set_time_zone("Asia/Tokyo");
    }
);

Second argument of filter CodeRef is instance object of your SQL class.

DEFLATING

If you want to deflate argument before execute SQL, you can it with adding deflater argument to validator rule.

__PACKAGE__->query(
    'add_article',
    subject => 'Str',
    body => 'Str',
    created_on => { isa => 'DateTime', deflater => sub { shift->strftime('%Y-%m-%d %H:%M:%S')  },
    <<SQL);
INSERT INTO articles (subject, body, created_on)
VALUES ( ?, ?, ?)',
SQL

METHODS

new({ dbh => DBI, readonly => ENUM(0,1) ) :DBIx::Sunny::Schema

create instance of schema. if readonly is true, query method's will raise exception.

dbh :DBI

readonly accessor for DBI database handler.

select_one($query, @bind) :Str

Shortcut for prepare, execute and fetchrow_arrayref->[0]

select_row($query, @bind) :HashRef

Shortcut for prepare, execute and fetchrow_hashref

select_all($query, @bind) :ArrayRef[HashRef]

Shortcut for prepare, execute and selectall_arrayref(.., { Slice => {} }, ..)

query($query, @bind) :Str

Shortcut for prepare, execute.

txn_scope() :DBIx::TransactionManager::Guard

return DBIx::TransactionManager::Guard object

do(@args) :Str

Shortcut for $self->dbh->do()

prepare(@args) :DBI::st

Shortcut for $self->dbh->prepare()

func(@args) :Str

Shortcut for $self->dbh->func()

last_insert_id(@args) :Str

Shortcut for $self->dbh->last_insert_id()

args(@rule) :HashRef

Shortcut for using Data::Validator. Optional deflater arguments can be used. Data::Validator instance will cache at first time.

sub retrieve_user {
    my $self = shift;
    my $args = $self->args(
        id => 'Int',
        created_on => {
            isa => 'DateTime',
            deflater => sub { shift->strftime('%Y-%m-%d %H:%M:%S')
        },
    );
    $arg->{id} ...
}

$args is validated arguments. @_ is not needed.

AUTHOR

Masahiro Nagano <kazeburo KZBRKZBR@ gmail.com>

SEE ALSO

DBI, DBIx::TransactionManager, Data::Validator

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.