Takuya Tsuchida
and 1 contributors

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)

handle_exception($sql, $binds_aref, $err_message)

show error message. you can override this method in subclass to provide customized error message.

default error message is like this,

Error <I>$error_message</I> sql: <I>$sql</I>, binds: [<I>$binds_aref</I>]\n

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.