DBIx::Sunny::Schema - SQL Class Builder
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; }
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.
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.
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.
build a query method named $method_name with validator.
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.
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
create instance of schema. if readonly is true, query method's will raise exception.
readonly accessor for DBI database handler.
Shortcut for prepare, execute and fetchrow_arrayref->[0]
Shortcut for prepare, execute and fetchrow_hashref
Shortcut for prepare, execute and selectall_arrayref(.., { Slice => {} }, ..)
Shortcut for prepare, execute.
return DBIx::TransactionManager::Guard object
Shortcut for $self->dbh->do()
Shortcut for $self->dbh->prepare()
Shortcut for $self->dbh->func()
Shortcut for $self->dbh->last_insert_id()
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.
Masahiro Nagano <kazeburo KZBRKZBR@ gmail.com>
DBI, DBIx::TransactionManager, Data::Validator
DBI
DBIx::TransactionManager
Data::Validator
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::Sunny, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Sunny
CPAN shell
perl -MCPAN -e shell install DBIx::Sunny
For more information on module installation, please visit the detailed CPAN module installation guide.