GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DBIX::SUNNY::SCHEMA (3)

.ds Aq ’

NAME

DBIx::Sunny::Schema - SQL Class Builder

CONTENTS

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 Arrays 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.
Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DBIX::SUNNY::SCHEMA (3) 2016-03-17

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.