NAME
    SQL::Executor - Thin DBI wrapper using SQL::Maker

SYNOPSIS
      use DBI;
      use SQL::Executor;
      my $dbh = DBI->connect($dsn, $id, $pass);
      my $ex = SQL::Executor->new($dbh);
      #
      # SQL::Maker-like interfaces
      my @rows = $ex->select('SOME_TABLE', { id => 123 });
      $ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
      $ex->update('SOME_TABLE', { value => 'yyyy'}, { id => 124 } );
      $ex->delete('SOME_TABLE', { id => 124 } );
      #
      # select using SQL with named placeholder
      my @rows= $ex->select_named('SELECT id, value1 FROM SOME_TABLE WHERE value2 = :arg1', { arg1 => 'aaa' });

DESCRIPTION
    SQL::Executor is thin DBI wrapper using SQL::Maker. This module provides
    interfaces to make easier access to SQL.

    You can execute SQL via SQL::Maker-like interface in select(),
    select_row(), select_all(), select_with_fields(),
    select_row_with_fields(), select_all_with_fields(), insert(),
    insert_multi(), update() and delete().

    If you want to use more complex select query, you can use
    select_named(), select_row_named() or select_all_named() these execute
    SQL with named placeholder. If you don't want to use named placeholder,
    you can use select_by_sql(), select_row_by_sql() or select_all_by_sql()
    these execute SQL with normal placeholder('?').

METHODS
  new($dbh, $option_href)
    $dbh: Database Handler $option_href: option

    available option is as follows

    *   allow_empty_condition (BOOL default 1): allow empty condition(where)
        in select/delete/update

    *   callback (coderef): specify callback coderef. callback is called for
        each select* method

    *   check_empty_bind (BOOL default 0): if TRUE(1), select*_named() do
        not accept unbound parameter, see named_bind() for detail.

    These callbacks are useful for making row object.

      my $ex = SQL::Executor->new($dbh, {
          callback => sub {
              my ($self, $row, $table_name, $select_id) = @_;
              return CallBack::Class->new($row);
          },
      });

      my $row = $ex->select_by_sql($sql1, \@binds1, 'TEST');
      # $row isa 'CallBack::Class'

  connect($dsn, $user, $pass, $option_for_dbi, $option_href)
    $dsn: DSN $user: database user $pass: database password
    $option_href_for_dbi: options passed to DBI $option_href: option for
    SQL::Executor (options are same as new() method)

    connect database and create SQL::Executor instance. using this method,
    SQL::Executor uses managed connection and transaction via DBIx::Handler

  dbh()
    return database handler

  select($table_name, $where, $option)
    select row(s). parameter is the same as select method in SQL::Maker. But
    array ref for filed names are not needed. In array context, this method
    behaves the same as select_all. In scalar context, this method behaves
    the same as select_one

  select_row($table_name, $where, $option)
    select only one row. parameter is the same as select method in
    SQL::Maker. But array ref for filed names are not needed. this method
    returns hash ref and it is the same as return value in DBI's
    selectrow_hashref/fetchrow_hashref.

  select_all($table_name, $where, $option)
    select all rows. parameter is the same as select method in SQL::Maker.
    But array ref for filed names are not needed. this method returns array
    that is composed of hash refs. (hash ref is same as DBI's
    selectrow_hashref/fetchrow_hashref).

  select_itr($table_name, $where, $option)
    select and returns iterator. parameter is the same as select method in
    SQL::Maker. But array ref for field names are not needed. Iterator is
    SQL::Executor::Iterator object.

      my $itr = select_itr('SOME_TABLE', { name => 'aaa' });
      while( my $row = $itr->next ) {
          # ... using row
      }

  select_named($sql, $params_href, $table_name)
    select row(s). In array context, this method behaves the same as
    select_all_with_fields. In scalar context, this method behaves the same
    as select_one_with_fileds

    You can use named placeholder in SQL like this,

      my $ex = SQL::Executor->new($dbh);
      my $row = $ex->select_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });

    $table_name is used for callback.

  select_row_named($sql, $params_href, $table_name)
    select only one row. You can use named placeholder in SQL like this,

      my $ex = SQL::Executor->new($dbh);
      my $row = $ex->select_row_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });

    this method returns hash ref and it is the same as return value in DBI's
    selectrow_hashref/fetchrow_hashref.

    $table_name is used for callback.

  select_all_named($sql, $params_href, $table_name)
    select all rows. You can use named placeholder in SQL like this,

      my $ex = SQL::Executor->new($dbh);
      my @rows = $ex->select_all_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });

    this method returns array that is composed of hash refs. (hash ref is
    same as DBI's selectrow_hashref/fetchrow_hashref). $table_name is used
    for callback.

  select_itr_named($sql, $params_href, $table_name)
    select and returns iterator. You can use named placeholder in SQL like
    this,

      my $ex = SQL::Executor->new($dbh);
      my $itr = $ex->select_itr_named("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 1234 });

    $table_name is used for callback.

  named_bind($sql, $params_href, $check_empty_bind)
    returns sql which is executable in execute_query() and parameters for
    bind.

      my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { id => 123 });
      # $sql   =>  "SELECT * FROM SOME_TABLE WHERE id = ?"
      # @binds => (123)

    parameter $check_empty_bind is optional. By default (or set
    $check_empty_bind=0), named_bind() accepts unbound parameter like this,

      my ($sql, @binds) = named_bind("SELECT * FROM SOME_TABLE WHERE id = :id", { });# do not bind :id
      # $sql   =>  "SELECT * FROM SOME_TABLE WHERE id = ?"
      # @binds => (undef)

    if $check_empty_bind is 1, named_bind() dies when unbound parameter is
    specified.

  select_by_sql($sql, \@binds, $table_name)
    select row(s). In array context, this method behaves the same as
    select_all_with_fields. In scalar context, this method behaves the same
    as select_one_with_fileds

      my $ex = SQL::Executor->new($dbh);
      my $row = $ex->select_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);

    $table_name is only used for callback.

  select_row_by_sql($sql, \@binds, $table_name)
    select only one row.

      my $ex = SQL::Executor->new($dbh);
      my $row = $ex->select_row_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);

    this method returns hash ref and it is the same as return value in DBI's
    selectrow_hashref/fetchrow_hashref.

  select_all_by_sql($sql, \@binds, $table_name)
    select all rows.

      my $ex = SQL::Executor->new($dbh);
      my @rows = $ex->select_all_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);

    this method returns array that is composed of hash refs. (hash ref is
    same as DBI's selectrow_hashref/fetchrow_hashref).

  select_itr_by_sql($sql, \@binds, $table_name)
    select and returns iterator

      my $ex = SQL::Executor->new($dbh);
      my $itr = $ex->select_itr_by_sql("SELECT * FROM SOME_TABLE WHERE id = ?", [1234]);

    Iterator is SQL::Executor::Iterator object.

  select_with_fields($table_name, $fields_aref, $where, $option)
    select row(s). parameter is the same as select method in SQL::Maker. In
    array context, this method behaves the same as select_all_with_fields.
    In scalar context, this method behaves the same as
    select_one_with_fileds

  select_row_with_fields($table_name, $fields_aref, $where, $option)
    select only one row. parameter is the same as select method in
    SQL::Maker. this method returns hash ref and it is the same as return
    value in DBI's selectrow_hashref/fetchrow_hashref.

  select_all_with_fields($table_name, $fields_aref, $where, $option)
    select all rows. parameter is the same as select method in SQL::Maker.
    But array ref for filed names are not needed. this method returns array
    that is composed of hash refs. (hash ref is same as DBI's
    selectrow_hashref/fetchrow_hashref).

  select_itr_with_fields($table_name, $fields_aref, $where, $option)
    select and return iterator object(SQL::Executor::Iterator). parameter is
    the same as select method in SQL::Maker.

  insert($table_name, $values)
    Do INSERT statement. parameter is the same as select method in
    SQL::Maker.

  insert_multi($table_name, @args)
    Do INSERT-multi statement using SQL::Maker::Plugin::InsertMulti.

  insert_on_duplicate($table_name, $insert_value_href, $update_value_href)
    Do "INSERT ... ON DUPLICATE KEY UPDATE" query (works only MySQL) using
    SQL::Maker::Plugin::InsertOnDuplicate.

    this method is available when SQL::Maker >= 1.09 is installed. If older
    version is installed, you will got error like "Can't locate
    SQL/Maker/Plugin/InsertOnDuplicate.pm in @INC ..."

  delete($table_name, $where)
    Do DELETE statement. parameter is the same as select method in
    SQL::Maker.

  update($table_name, $set, $where)
    Do UPDATE statement. parameter is the same as select method in
    SQL::Maker.

  execute_query($sql, \@binds)
    execute query and returns statement handler($sth).

  execute_query_named($sql, $params_href)
    execute query with named placeholder and returns statement
    handler($sth).

  disable_callback()
    disable callback temporarily,

  restore_callback()
    restore disabled callback.

  last_insert_id(@args)
    If driver is mysql, return $dbh->{mysql_insertid}.If driver is SQLite,
    return $dbh->sqlite_last_insert_rowid. If other driver is used, return
    $dbh->last_insert_id(@args)

  select_id()
    generate id for select statament. but by default, id is not generated.
    If you want to generate id, please override

How to use Transaction.
    When create instance using connect() method, you can use DBIx::Handler's
    transaction management,

      use SQL::Executor;
      my $ex = SQL::Executor->connect($dsn, $id, $pass);
      my $txn = $ex->handler->txn_scope();
      $ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
      $ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
      $txn->commit();

    Or You can use DBI's transaction (begin_work and commit).

      use DBI;
      use SQL::Executor;
      my $dbh = DBI->connect($dsn, $id, $pass);
      my $ex = SQL::Executor->new($dbh);
      $dbh->begin_work();
      $ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
      $ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
      $dbh->commit();

    Or you can also use transaction management modules like
    DBIx::TransactionManager.

      use DBI;
      use SQL::Executor;
      use DBIx::TransactionManager;
      my $dbh = DBI->connect($dsn, $id, $pass);
      my $ex = SQL::Executor->new($dbh);
      my $tm = DBIx::TransactionManager->new($dbh);
      my $txn = $tm->txn_scope;
      $ex->insert('SOME_TABLE', { id => 124, value => 'xxxx'} );
      $ex->insert('SOME_TABLE', { id => 125, value => 'yyy'} );
      $txn->commit;

FAQ
  Why don't you use DBIx::Simple?
    *   I want to use SQL::Maker.

    *   When I need to use complex query, I want to use named placeholder.

AUTHOR
    Takuya Tsuchida <tsucchi {at} cpan.org>

SEE ALSO
    DBI, SQL::Maker, DBIx::Simple

    Codes for named placeholder is taken from Teng's search_named.

LICENSE
    Copyright (C) Takuya Tsuchida

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