DBIx-Custom

 view release on metacpan or  search on metacpan

lib/DBIx/Custom.pm  view on Meta::CPAN

      . _subname;
  }
}
sub DESTROY {}

# DEPRECATED
sub helper {
  my $self = shift;
  
  _deprecate('0.39', "DBIx::Custom::helper method is DEPRECATED!");
  
  # Register method
  my $methods = ref $_[0] eq 'HASH' ? $_[0] : {@_};
  $self->{_methods} = {%{$self->{_methods} || {}}, %$methods};
  
  return $self;
}

# DEPRECATED
sub update_or_insert {

  _deprecate('0.39', "DBIx::Custom::update_or_insert method is DEPRECATED!");

  my ($self, $param, %opt) = @_;
  confess "update_or_insert method need primary_key and id option "
    unless defined $opt{id} && defined $opt{primary_key};
  my $statement_opt = $opt{option} || {};

  my $rows = $self->select(%opt, %{$statement_opt->{select} || {}})->all;
  if (@$rows == 0) {
    return $self->insert($param, %opt, %{$statement_opt->{insert} || {}});
  }
  elsif (@$rows == 1) {
    return 0 unless keys %$param;
    return $self->update($param, %opt, %{$statement_opt->{update} || {}});
  }
  else { confess "selected row must be one " . _subname }
}

# DEPRECATED
sub count {
  _deprecate('0.39', "DBIx::Custom::count method is DEPRECATED!");
  shift->select(column => 'count(*)', @_)->fetch_one->[0]
}

1;

=head1 NAME

DBIx::Custom - DBI extension to execute insert, update, delete, and select easily

=head1 SYNOPSIS

  use DBIx::Custom;
  
  # Connect
  my $dbi = DBIx::Custom->connect(
    "dbi:mysql:database=dbname",
    'ken',
    '!LFKD%$&',
    {mysql_enable_utf8 => 1}
  );
  
  # Create model
  $dbi->create_model('book');
  
  # Insert 
  $dbi->model('book')->insert({title => 'Perl', author => 'Ken'});
  
  # Update 
  $dbi->model('book')->update({title => 'Perl', author => 'Ken'}, where  => {id => 5});
  
  # Delete
  $dbi->model('book')->delete(where => {author => 'Ken'});
  
  # Select
  my $result = $dbi->model('book')->select(['title', 'author'], where  => {author => 'Ken'});
  
  # Select, more complex
  #   select book.title as book.title,
  #     book.author as book.author,
  #     comnapy.name as company.name
  #   form book
  #     left outer join company on book.company_id = company.id
  #   where book.author = ?
  #   order by id limit 0, 5
  my $result = $dbi->model('book')->select(
    [
      {book => [qw/title author/]},
      {company => ['name']}
    ],
    where  => {'book.author' => 'Ken'},
    join => ['left outer join company on book.company_id = company.id'],
    append => 'order by id limit 0, 5'
  );
  
  # Get all rows or only one row
  my $rows = $result->all;
  my $row = $result->one;
  
  # Execute SQL with named place holder
  my $result = $dbi->execute(
    "select id from book where author = :author and title like :title",
    {author => 'ken', title => '%Perl%'}
  );
  
=head1 DESCRIPTION

L<DBIx::Custom> is L<DBI> wrapper module to execute SQL easily.
This module have the following features.

=over 4

=item *

Execute C<insert>, C<update>, C<delete>, or C<select> statement easily

=item *

Create C<where> clause flexibly

lib/DBIx/Custom.pm  view on Meta::CPAN

If C<user_table_info> is set, C<each_table> use C<user_table_info>
to find table info.

=head1 METHODS

L<DBIx::Custom> inherits all methods from L<Object::Simple>
and use all methods of L<DBI>
and implements the following new ones.

=head2 available_datatype

  print $dbi->available_datatype;

Get available data types. You can use these data types
in C<type rule>'s C<from1> and C<from2> section.

=head2 available_typename

  print $dbi->available_typename;

Get available type names. You can use these type names in
C<type_rule>'s C<into1> and C<into2> section.

=head2 assign_clause

  my $assign_clause = $dbi->assign_clause({title => 'a', age => 2});

Create assign clause

  title = :title, author = :author

This is used to create update clause.

  "update book set " . $dbi->assign_clause({title => 'a', age => 2});

=head2 column

  my $column = $dbi->column(book => ['author', 'title']);

Create column clause. The following column clause is created.

  book.author as "book.author",
  book.title as "book.title"

You can change separator by C<separator> attribute.

  # Separator is hyphen
  $dbi->separator('-');
  
  book.author as "book-author",
  book.title as "book-title"
  
=head2 connect
  
  # DBI compatible arguments
  my $dbi = DBIx::Custom->connect(
    "dbi:mysql:database=dbname",
    'ken',
    '!LFKD%$&',
    {mysql_enable_utf8 => 1}
  );
  
  # pass DBIx::Custom attributes
  my $dbi = DBIx::Custom->connect(
    dsn => "dbi:mysql:database=dbname",
    user => 'ken',
    password => '!LFKD%$&',
    option => {mysql_enable_utf8 => 1}
  );

Connect to the database and create a new L<DBIx::Custom> object.

L<DBIx::Custom> is a wrapper of L<DBI>.
C<AutoCommit> and C<RaiseError> options are true, 
and C<PrintError> option is false by default.

=head2 create_model
  
  $dbi->create_model('book');
  $dbi->create_model(
    'book',
    join => [
      'inner join company on book.comparny_id = company.id'
    ]
  );
  $dbi->create_model(
    table => 'book',
    join => [
      'inner join company on book.comparny_id = company.id'
    ],
  );

Create L<DBIx::Custom::Model> object and initialize model.
Model columns attribute is automatically set.
You can use this model by using C<model> method.

  $dbi->model('book')->select(...);

You can use model name which different from table name

  $dbi->create_model(name => 'book1', table => 'book');
  $dbi->model('book1')->select(...);

  $dbi->create_model(
    table => 'book',
    join => [
      'inner join company on book.comparny_id = company.id'
    ],
  );

C<column_name_lc> option change column names to lower case.

  $dbi->create_model(
    table => 'book',
    join => [
      'inner join company on book.comparny_id = company.id'
    ],
    column_name_lc => 1,
  );

=head2 dbh

  my $dbh = $dbi->dbh;

Get L<DBI> database handle. if C<connector> is set, you can get
database handle through C<connector> object.

=head2 delete

lib/DBIx/Custom.pm  view on Meta::CPAN

  my $book_model = $dbi->model('book');
  my $company_model = $dbi->model('company');

You can include full-qualified table name like C<main.book>

  lib / MyModel.pm
      / MyModel / main / book.pm
                       / company.pm

  my $main_book = $self->model('main.book');

See L<DBIx::Custom::Model> to know model features.

=head2 like_value

  my $like_value = $dbi->like_value

Code reference which return a value for the like value.

  sub { "%$_[0]%" }

=head2 mapper

  my $mapper = $dbi->mapper(param => $param);

Create a new L<DBIx::Custom::Mapper> object.

=head2 merge_param

  my $param = $dbi->merge_param({key1 => 1}, {key1 => 1, key2 => 2});

Merge parameters. The following new parameter is created.

  {key1 => [1, 1], key2 => 2}

If same keys contains, the value is converted to array reference.

=head2 model

  my $model = $dbi->model('book');

Get a L<DBIx::Custom::Model> object
create by C<create_model> or C<include_model>

=head2 mycolumn

  my $column = $dbi->mycolumn(book => ['author', 'title']);

Create column clause for myself. The following column clause is created.

  book.author as author,
  book.title as title

=head2 new

  my $dbi = DBIx::Custom->new(
    dsn => "dbi:mysql:database=dbname",
    user => 'ken',
    password => '!LFKD%$&',
    option => {mysql_enable_utf8 => 1}
  );

Create a new L<DBIx::Custom> object.

=head2 not_exists

  my $not_exists = $dbi->not_exists;

DBIx::Custom::NotExists object, indicating the column is not exists.
This is used in C<param> of L<DBIx::Custom::Where> .

=head2 order

  my $order = $dbi->order;

Create a new L<DBIx::Custom::Order> object.

=head2 q

  my $quooted = $dbi->q("title");

Quote string by value of C<quote>.

=head2 register_filter

  $dbi->register_filter(
    # Time::Piece object to database DATE format
    tp_to_date => sub {
      my $tp = shift;
      return $tp->strftime('%Y-%m-%d');
    },
    # database DATE format to Time::Piece object
    date_to_tp => sub {
      my $date = shift;
      return Time::Piece->strptime($date, '%Y-%m-%d');
    }
  );
  
Register filters, used by C<filter> option of many methods.

=head2 select

  my $result = $dbi->select(
    column => ['author', 'title'],
    table  => 'book',
    where  => {author => 'Ken'},
  );
  
Execute select statement.

You can pass odd number arguments. first argument is C<column>.

  my $result = $dbi->select(['author', 'title'], table => 'book');

B<OPTIONS>

C<select> method use all of C<execute> method's options,
and use the following new ones.

=over 4