xref: /dports/databases/p5-DBIx-Custom/DBIx-Custom-0.40/t/common.t

use Test::More;
use strict;
use warnings;
use Encode qw/encode_utf8/;
use FindBin;
use Scalar::Util 'isweak';

 $ENV{DBIX_CUSTOM_SUPPRESS_DEPRECATION} = '0.39';

plan skip_all => $ENV{DBIX_CUSTOM_SKIP_MESSAGE} || 'common.t is always skipped'
  unless $ENV{DBIX_CUSTOM_TEST_RUN}
    && eval { DBIx::Custom->connect; 1 };

plan 'no_plan';

# Dot to under score
sub u($) {
  my $value = shift;
  $value =~ s/\./_/g;
  return $value;
}

sub u2($) {
  my $value = shift;
  $value =~ s/\./__/g;
  return $value;
}

sub hy($) {
  my $value = shift;
  $value =~ s/\./-/g;
  return $value;
}

sub colon2 {
  my $value = shift;
  $value =~ s/\./::/g;
  return $value;
}

sub table_only {
  my $value = shift;
  $value =~ s/^.+\.//;
  return $value;
}

# Global variable
my $table1;
my $table2;
my $table2_alias;
my $table3;
my $key1;
my $key2;
my $key3;
my $key4;
my $key5;
my $key6;
my $key7;
my $key8;
my $key9;
my $key10;
my $create_table1;
my $create_table1_2;
my $create_table1_type;
my $create_table1_highperformance;
my $create_table2;
my $create_table2_2;
my $create_table3;
my $create_table_reserved;
my ($q, $p);
my $date_typename;
my $datetime_typename;
my $date_datatype;
my $datetime_datatype;
my $user_table_info;

# Global setting
{
  my $dbi = DBIx::Custom->connect;

  $table1 = $dbi->table1;
  $table2 = $dbi->table2;
  $table2_alias = $dbi->table2_alias;
  $table3 = $dbi->table3;
  $key1 = $dbi->key1;
  $key2 = $dbi->key2;
  $key3 = $dbi->key3;
  $key4 = $dbi->key4;
  $key5 = $dbi->key5;
  $key6 = $dbi->key6;
  $key7 = $dbi->key7;
  $key8 = $dbi->key8;
  $key9 = $dbi->key9;
  $key10 = $dbi->key10;
  $create_table1 = $dbi->create_table1;
  $create_table1_2 = $dbi->create_table1_2;
  $create_table1_type = $dbi->create_table1_type;
  $create_table1_highperformance = $dbi->create_table1_highperformance;
  $create_table2 = $dbi->create_table2;
  $create_table2_2 = $dbi->create_table2_2;
  $create_table3 = $dbi->create_table3;
  $create_table_reserved = $dbi->create_table_reserved;
  ($q, $p) = $dbi->_qp;
  $date_typename = $dbi->date_typename;
  $datetime_typename = $dbi->datetime_typename;
  $date_datatype = $dbi->date_datatype;
  $datetime_datatype = $dbi->datetime_datatype;
}

{
  package MyModel2::Base1;

  use strict;
  use warnings;

  use base 'DBIx::Custom::Model';

  package MyModel2::table1;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::table2;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::TABLE1;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::TABLE2;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }


  package MyModel2::main::table1;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::main::table2;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::dbix_custom::table1;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }

  package MyModel2::dbix_custom::table2;

  use strict;
  use warnings;

  use base 'MyModel2::Base1';

  sub insert {
    my ($self, $param) = @_;

    return $self->SUPER::insert($param);
  }

  sub list { shift->select; }
}

# Check safety character
{
  my $dbi = DBIx::Custom->connect;

  # Check safety character - insert
  eval{$dbi->insert({';' => 1}, table => $table1)};
  like($@, qr/";" is not safety column name in values clause/);

  # Check safety character - update
  eval{$dbi->update({';' => 1}, table => $table1, where => {$key1 => 1})};
  like($@, qr/";" is not safety column name in assign clause/);

  # Check safety character - select
  eval { $dbi->select(table => $table1, where => {';' => 1}) };
  like($@, qr/";" is not safety column name in where clause/);
}

# model
{
  # model - table name is different
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->create_model(name => 'foo', table => $table1);

    $dbi->model('foo')->insert({$key1 => 1, $key2 => 2});
    is_deeply($dbi->model('foo')->select->all, [{$key1 => 1, $key2 => 2}]);
  }

  # model - one argument is table name
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->create_model($table1, join => ['aaa']);
    is_deeply($dbi->model($table1)->join, ['aaa']);
    $dbi->model($table1)->join([]);

    $dbi->model($table1)->insert({$key1 => 1, $key2 => 2});
    is_deeply($dbi->model($table1)->select->all, [{$key1 => 1, $key2 => 2}]);
  }
}

# DBI compatible connect arguments
{
  my $dbi_tmp = DBIx::Custom->new;

  my $dbi = DBIx::Custom->connect($dbi_tmp->dsn, $dbi_tmp->user, $dbi_tmp->password, {RaiseError => 0}, {last_sql => 'A'});

  is($dbi->last_sql, 'A');
  ok(!$dbi->dbh->{RaiseError});

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  {
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2},
      {$key1 => 3, $key2 => 4}], "table");
  }
}

# mycolumn and column
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);

  $dbi->include_model('MyModel6');

  $dbi->separator('__');
  $dbi->setup_model;
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 1, $key3 => 3}, table => $table2);
  my $model = $dbi->model($table1);

  {
    my $result = $model->select(
      column => [$model->mycolumn, $model->column($table2)],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
  }

  {
    my $result = $model->select(
      column => [$model->mycolumn, $model->column($table2 => '*')],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
  }

  {
    my $result = $model->select(
      column => [
        {__MY__ => '*'},
        {$table2 => '*'}
      ],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
  }

  {
    my $result = $model->select(
      column => [
        {__MY2__ => '*'},
        {$table2 => '*'}
      ],
      where => {"$table1.$key1" => 1},
      mytable_symbol => '__MY2__'
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
  }

  {
    my $original = $model->dbi->mytable_symbol;
    $model->dbi->mytable_symbol('__MY2__');
    my $result = $model->select(
      column => [
        {__MY2__ => '*'},
        {$table2 => '*'}
      ],
      where => {"$table1.$key1" => 1},
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
    $model->dbi->mytable_symbol($original);
  }
}


# get_columns_from_db
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);

  my $columns = $dbi->get_columns_from_db($table1);
  is_deeply($columns, [$key1, $key2, $key3, $key4, $key5]);
}

# query option
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);

  my $param = {$key1 => 1, $key2 => 2};
  my $query = $dbi->insert($param, table => $table1, query => 1);

  my $sth = $dbi->dbh->prepare($query->sql);
  $sth->execute(@{$query->bind_values});

  $param = {$key1 => 3, $key2 => 4};
  $query->param($param);
  $query->build;
  $sth->execute(@{$query->bind_values});

  my $result = $dbi->select(table => $table1);
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
}

# insert
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->register_filter(
    three_times => sub { $_[0] * 3 }
  );
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1, filter => {$key1 => 'three_times'});
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 3, $key2 => 2}]);
  $dbi->delete_all(table => $table1);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => \"'1'", $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1,
  wrap => {$key1 => sub { "$_[0] - 1" }});
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 0, $key2 => 2}, {$key1 => 3, $key2 => 4}], "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1};
  $dbi->insert($param, table => $table1, ctime => $key2);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1};
  $dbi->insert($param, table => $table1, mtime => $key3);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key3}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1};
  $dbi->insert($param, table => $table1, ctime => $key2, mtime => $key3);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
  like($row->{$key3}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
  is($row->{$key2}, $row->{$key3});
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $model = $dbi->create_model(table => $table1, ctime => $key2);
  my $param = {$key1 => 1};
  $model->insert($param);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row   = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1};
  my $model = $dbi->create_model(table => $table1, mtime => $key3);
  $model->insert($param);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row   = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key3}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1};
  my $model = $dbi->create_model(table => $table1, ctime=> $key2, mtime => $key3);
  $model->insert($param);
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key1 => 1});
  my $row   = $result->one;
  is($row->{$key1}, 1);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
  like($row->{$key3}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
  is($row->{$key2}, $row->{$key3});
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert([{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}] , table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert([{$key1 => 1}, {$key1 => 3}] ,
    table => $table1,
    mtime => $key2,
    ctime => $key3
  );
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is($rows->[0]->{$key1}, 1);
  is($rows->[1]->{$key1}, 3);
  like($rows->[0]->{$key2}, qr/\d{2}:/);
  like($rows->[1]->{$key2}, qr/\d{2}:/);
  like($rows->[0]->{$key3}, qr/\d{2}:/);
  like($rows->[1]->{$key3}, qr/\d{2}:/);
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert([{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}] ,
  table => $table1, filter => {$key1 => sub { $_[0] * 2 }});
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 2, $key2 => 2}, {$key1 => 6, $key2 => 4}], "basic");
}

# Create table
{
  my $dbi = DBIx::Custom->connect;
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    my $model = $dbi->create_model(table => $table1);
    $model->insert({$key1 => 1, $key2 => 2});
    is_deeply($model->select->all, [{$key1 => 1, $key2 => 2}]);
  }
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    my $model = $dbi->create_model(table => $table1);
    $model->insert({$key1 => 1, $key2 => 2});
    is_deeply($model->select($key1)->all, [{$key1 => 1}]);
  }
}

# execute reuse option (this option become no more meaning)
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  my $reuse = {};
  for my $i (1 .. 2) {
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1, reuse => $reuse);
  }
  my $rows = $dbi->select(table => $table1)->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 1, $key2 => 2}]);
}

# Get user table info
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  eval { $dbi->execute("drop table $table3") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);
  $dbi->execute($create_table3);
  $user_table_info = $dbi->get_table_info(exclude => $dbi->exclude_table);
}

# DBIx::Custom::Result test
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);

  $dbi->delete_all(table => $table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  my $source = "select $key1, $key2 from $table1";
  {
    my $result = $dbi->execute($source);
    my @rows = ();
    while (my $row = $result->fetch) {
      push @rows, [@$row];
    }
    is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");
  }

  {
    my $result = $dbi->execute($source);
    my @rows = ();
    while (my $row = $result->fetch_hash) {
      push @rows, {%$row};
    }
    is_deeply(\@rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "fetch_hash");
  }

  {
    my $result = $dbi->execute($source);
    my $rows = $result->fetch_all;
    is_deeply($rows, [[1, 2], [3, 4]]);
  }

  {
    my $result = $dbi->execute($source);
    my $rows = $result->fetch_hash_all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "all");
  }

  is_deeply($dbi->select($key1, table => $table1)->values, [1, 3]);

  is($dbi->select('count(*)', table => $table1)->value, 2);
  ok(!defined $dbi->select($key1, table => $table1, where => {$key1 => 10})->value);
}

# Named placeholder
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
  {
    my $source = "select * from $table1 where $key1 = :$key1 and $key2 = :$key2";
    my $result = $dbi->execute($source, {$key1 => 1, $key2 => 2});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }
  {
    my $source = "select * from $table1 where $key1 = \n:$key1\n and $key2 = :$key2";
    my $result = $dbi->execute($source, {$key1 => 1, $key2 => 2});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }
  {
    my $source = "select * from $table1 where $key1 = :$key1 or $key1 = :$key1";
    my $result = $dbi->execute($source, {$key1 => [1, 2]});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }
  {
    my $source = "select * from $table1 where $key1 = :$table1.$key1 and $key2 = :$table1.$key2";
    my $result = $dbi->execute(
      $source,
      {"$table1.$key1" => 1, "$table1.$key2" => 1},
      filter => {"$table1.$key2" => sub { $_[0] * 2 }}
    );
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }


  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => '2011-10-14 12:19:18', $key2 => 2}, table => $table1);
    my $source = "select * from $table1 where $key1 = '2011-10-14 12:19:18' and $key2 = :$key2";
    my $result = $dbi->execute(
      $source,
      {$key2 => 2},
    );

    my $rows = $result->all;
    like($rows->[0]->{$key1}, qr/2011-10-14 12:19:18/);
    is($rows->[0]->{$key2}, 2);
  }

  {
    $dbi->delete_all(table => $table1);
    $dbi->insert({$key1 => 'a:b c:d', $key2 => 2}, table => $table1);
    my $source = "select * from $table1 where $key1 = 'a\\:b c\\:d' and $key2 = :$key2";
    my $result = $dbi->execute(
      $source,
      {$key2 => 2},
    );
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 'a:b c:d', $key2 => 2}]);
  }

  # Error case
  eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};
  ok($@, "connect error");
}

# update_or_insert
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->update_or_insert(
    {$key2 => 2},
    table => $table1,
    primary_key => $key1,
    id => 1
  );
  {
    my $row = $dbi->select(id => 1, table => $table1, primary_key => $key1)->one;
    is_deeply($row, {$key1 => 1, $key2 => 2}, "basic");
  }

  $dbi->update_or_insert(
    {$key2 => 3},
    table => $table1,
    primary_key => $key1,
    id => 1
  );
  {
    my $rows = $dbi->select(id => 1, table => $table1, primary_key => $key1)->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 3}], "basic");
  }

  eval {
    $dbi->update_or_insert(
      {$key2 => 3},
      table => $table1,
    );
  };

  like($@, qr/primary_key/);

  eval {
    $dbi->insert({$key1 => 1}, table => $table1);
    $dbi->update_or_insert(
      {$key2 => 3},
      table => $table1,
      primary_key => $key1,
      id => 1
    );
  };
  like($@, qr/one/);

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->update_or_insert(
    {},
    table => $table1,
    primary_key => $key1,
    id => 1
  );
  my $row = $dbi->select(id => 1, table => $table1, primary_key => $key1)->one;
  is($row->{$key1}, 1);

  {
    my $affected;
    eval {
      $affected = $dbi->update_or_insert(
        {},
        table => $table1,
        primary_key => $key1,
        id => 1
      );
    };
    is($affected, 0);
  }
}

# model update_or_insert
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  my $model = $dbi->create_model(
    table => $table1,
    primary_key => $key1
  );
  $model->update_or_insert({$key2 => 2}, id => 1);
  my $row = $model->select(id => 1)->one;
  is_deeply($row, {$key1 => 1, $key2 => 2}, "basic");

  eval {
    $model->insert({$key1 => 1});
    $model->update_or_insert(
      {$key2 => 3},
      id => 1
    );
  };
  like($@, qr/one/);
}

# bind filter
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);

  $dbi->register_filter(
    three_times => sub { $_[0] * 3 }
  );
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1, filter => {$key1 => 'three_times'});
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 3, $key2 => 2}], "filter");
}

# update
{
  my $dbi = DBIx::Custom->connect;

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "basic");
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "basic");
  }

  {
    $dbi->execute("delete from $table1");
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => 12}, table => $table1, where => {$key2 => 2, $key3 => 3});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 12, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "update key same as search key");
  }

  {
    $dbi->update({$key2 => [12]}, table => $table1, where => {$key2 => 2, $key3 => 3});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 12, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "update key same as search key : param is array ref");
  }
  $dbi->execute("delete from $table1");
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
  $dbi->register_filter(twice => sub { $_[0] * 2 });
  $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1},
              filter => {$key2 => sub { $_[0] * 2 }});

  {
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 22, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "filter");
  }

  {
    my $result = $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1}, append => '   ');

    eval{$dbi->update(table => $table1)};
    like($@, qr/where/, "not contain where");
  }
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    my $where = $dbi->where;
    $where->clause(['and', "$key1 = :$key1", "$key2 = :$key2"]);
    $where->param({$key1 => 1, $key2 => 2});
    $dbi->update({$key1 => 3}, table => $table1, where => $where);
    my $result = $dbi->select(table => $table1);
    is_deeply($result->all, [{$key1 => 3, $key2 => 2}], 'update() where');
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->update(
      {$key1 => 3},
      table => $table1,
      where => [
        ['and', "$key1 = :$key1", "$key2 = :$key2"],
        {$key1 => 1, $key2 => 2}
      ]
    );
    my $result = $dbi->select(table => $table1);
    is_deeply($result->all, [{$key1 => 3, $key2 => 2}], 'update() where');
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    my $where = $dbi->where;
    $where->clause(['and', "$key2 = :$key2"]);
    $where->param({$key2 => 2});
    $dbi->update({$key1 => 3}, table => $table1, where => $where);
    my $result = $dbi->select(table => $table1);
    is_deeply($result->all, [{$key1 => 3, $key2 => 2}], 'update() where');
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "basic");
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => 11}, table => $table1, where => {$key1 => 1},
    wrap => {$key2 => sub { "$_[0] - 1" }});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 10, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "basic");
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
    $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
    $dbi->update({$key2 => \"'11'"}, table => $table1, where => {$key1 => 1});
    my $result = $dbi->execute("select * from $table1 order by $key1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
      {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
      "basic");
  }
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
  my $param = {$key2 => 11};
  $dbi->update($param, table => $table1, where => {$key1 => 1});
  is_deeply($param, {$key2 => 11});
  my $result = $dbi->execute("select * from $table1 order by $key1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
    "basic");
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
  my $param = {$key2 => 11};
  $dbi->update($param, table => $table1, where => {$key2 => 2});
  is_deeply($param, {$key2 => 11});
  my $result = $dbi->execute("select * from $table1 order by $key1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
    "basic");
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key3 => 4};
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
  $dbi->update($param, table => $table1, mtime => $key2, where => {$key1 => 1});
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key3 => 4});
  my $row   = $result->one;
  is($row->{$key3}, 4);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key3 => 4};
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
  $dbi->update($param, table => $table1, mtime => $key2, where => {$key3 => 3});
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key3 => 4});
  my $row   = $result->one;
  is($row->{$key3}, 4);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $model = $dbi->create_model(table => $table1, mtime => $key2);
  my $param = {$key3 => 4};
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
  $model->update($param, where => {$key1 => 1});
  my $result = $dbi->select(table => $table1);
  is_deeply($param, {$key3 => 4});
  my $row   = $result->one;
  is($row->{$key3}, 4);
  like($row->{$key2}, qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/);
}

# update_all
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);
  $dbi->register_filter(twice => sub { $_[0] * 2 });
  $dbi->update_all({$key2 => 10}, table => $table1, filter => {$key2 => 'twice'});
  my $result = $dbi->execute("select * from $table1");
  my $rows = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 20, $key3 => 3, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 20, $key3 => 8, $key4 => 9, $key5 => 10}],
    "filter");
}

# delete
{
  my $dbi = DBIx::Custom->connect;

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    $dbi->delete(table => $table1, where => {$key1 => 1});
    my $result = $dbi->execute("select * from $table1");
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 3, $key2 => 4}], "basic");
  }

  {
    $dbi->execute("delete from $table1");
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    $dbi->register_filter(twice => sub { $_[0] * 2 });
    $dbi->delete(table => $table1, where => {$key2 => 1}, filter => {$key2 => 'twice'});
    my $result = $dbi->execute("select * from $table1");
    my $rows   = $result->all;
    is_deeply($rows, [{$key1 => 3, $key2 => 4}], "filter");
  }

  $dbi->delete(table => $table1, where => {$key1 => 1}, append => '   ');

  $dbi->delete_all(table => $table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  $dbi->delete(table => $table1, where => {$key1 => 1, $key2 => 2});
  my $rows = $dbi->select(table => $table1)->all;
  is_deeply($rows, [{$key1 => 3, $key2 => 4}], "delete multi key");

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    my $where = $dbi->where;
    $where->clause(['and', "$key1 = :$key1", "$key2 = :$key2"]);
    $where->param({ke1 => 1, $key2 => 2});
    $dbi->delete(table => $table1, where => $where);
    my $result = $dbi->select(table => $table1);
    is_deeply($result->all, [{$key1 => 3, $key2 => 4}], 'delete() where');
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    $dbi->delete(
      table => $table1,
      where => [
        ['and', "$key1 = :$key1", "$key2 = :$key2"],
        {ke1 => 1, $key2 => 2}
      ]
    );
    my $result = $dbi->select(table => $table1);
    is_deeply($result->all, [{$key1 => 3, $key2 => 4}], 'delete() where');
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->delete(table => $table1, where => {$key1 => 1}, prefix => '    ');
    my $result = $dbi->execute("select * from $table1");
    $rows   = $result->all;
    is_deeply($rows, [], "basic");
  }
}

# delete error
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  eval{$dbi->delete(table => $table1)};
  like($@, qr/where/, "where key-value pairs not specified");
}

# delete_all
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  $dbi->delete_all(table => $table1);
  my $result = $dbi->execute("select * from $table1");
  my $rows   = $result->all;
  is_deeply($rows, [], "basic");
}

# select
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  {
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2},
      {$key1 => 3, $key2 => 4}], "table");
  }

  {
    my $rows = $dbi->select(table => $table1, column => [$key1])->all;
    is_deeply($rows, [{$key1 => 1}, {$key1 => 3}], "table and columns and where key");
  }

  {
    my $rows = $dbi->select(table => $table1, where => {$key1 => 1})->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}], "table and columns and where key");
  }

  {
    my $rows = $dbi->select(table => $table1, column => [$key1], where => {$key1 => 3})->all;
    is_deeply($rows, [{$key1 => 3}], "table and columns and where key");
  }

  {
    $dbi->register_filter(decrement => sub { $_[0] - 1 });
    my $rows = $dbi->select(table => $table1, where => {$key1 => 2}, filter => {$key1 => 'decrement'})
              ->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}], "filter");
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    my $row = $dbi->select($key1, table => $table1)->one;
    is_deeply($row, {$key1 => 1});
  }

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  $dbi->insert({$key1 => 5, $key2 => 6}, table => $table1);

  {
    my $rows = $dbi->select(table => $table1, where => {$key1 => [1, 5]})->all;
    is_deeply($rows, [
      {$key1 => 1, $key2 => 2},
      {$key1 => 5, $key2 => 6}
    ], "table");
  }

  {
    my $rows = $dbi->select(table => $table1, where => {$key1 => []})->all;
    is_deeply($rows, [], "table");
  }

  # fetch filter
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->register_filter(
      three_times => sub { $_[0] * 3 }
    );
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    my $result = $dbi->select(table => $table1);
    $result->filter({$key1 => 'three_times'});
    my $row = $result->one;
    is_deeply($row, {$key1 => 3, $key2 => 2});
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    my $result = $dbi->select(column => [$key1, $key1, $key2], table => $table1);
    $result->filter({$key1 => 'three_times'});
    my $row = $result->fetch_one;
    is_deeply($row, [3, 3, 2]);
  }
}

# filters
{
  my $dbi = DBIx::Custom->new;

  is($dbi->filters->{decode_utf8}->(encode_utf8('あ')),
    'あ', "decode_utf8");

  is($dbi->filters->{encode_utf8}->('あ'),
    encode_utf8('あ'), "encode_utf8");
}

# transaction1
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->begin_work;
    $dbi->dbh->{AutoCommit} = 0;
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->rollback;
    $dbi->dbh->{AutoCommit} = 1;

    my $result = $dbi->select(table => $table1);
    ok(! $result->fetch_one, "rollback");
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->begin_work;
    $dbi->dbh->{AutoCommit} = 0;
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
    $dbi->commit;
    $dbi->dbh->{AutoCommit} = 1;
    my $result = $dbi->select(table => $table1);
    is_deeply(scalar $result->all, [{$key1 => 1, $key2 => 2}, {$key1 => 2, $key2 => 3}],
      "commit");
  }
}

# execute
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  {
    local $Carp::Verbose = 0;
    eval{$dbi->execute("select * frm $table1")};
    like($@, qr/\Qselect * frm $table1/, "fail prepare");
    like($@, qr/\.t /, "fail : not verbose");
  }
  {
    local $Carp::Verbose = 1;
    eval{$dbi->execute("select * frm $table1")};
    like($@, qr/Custom.*\.t /s, "fail : verbose");
  }
}

# transaction2
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);

  $dbi->begin_work;

  eval {
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    die "Error";
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  };

  $dbi->rollback if $@;

  {
    my $result = $dbi->select(table => $table1);
    my $rows = $result->all;
    is_deeply($rows, [], "rollback");
  }

  $dbi->begin_work;

  eval {
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  };

  $dbi->commit unless $@;

  {
    my $result = $dbi->select(table => $table1);
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], "commit");
  }

  $dbi->dbh->{AutoCommit} = 0;
  eval{ $dbi->begin_work };
  ok($@, "exception");
  $dbi->dbh->{AutoCommit} = 1;
}

# execute
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  {
    local $Carp::Verbose = 0;
    eval{$dbi->execute("select * frm $table1")};
    like($@, qr/\Qselect * frm $table1/, "fail prepare");
    like($@, qr/\.t /, "fail : not verbose");
  }
  {
    local $Carp::Verbose = 1;
    eval{$dbi->execute("select * frm $table1")};
    like($@, qr/Custom.*\.t /s, "fail : verbose");
  }
}

# helper
{
  my $dbi = DBIx::Custom->connect;

  $dbi->helper(
    one => sub { 1 }
  );
  $dbi->helper(
    two => sub { 2 }
  );
  $dbi->helper({
    twice => sub {
      my $self = shift;
      return $_[0] * 2;
    }
  });

  is($dbi->one, 1, "first");
  is($dbi->two, 2, "second");
  is($dbi->twice(5), 10 , "second");

  eval {$dbi->XXXXXX};
  ok($@, "not exists");
}

# connect super
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    is($dbi->select(table => $table1)->one->{$key1}, 1);
  }

  {
    my $dbi = DBIx::Custom->new;
    $dbi->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    is($dbi->select(table => $table1)->one->{$key1}, 1);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    is($dbi->select(table => $table1)->one->{$key1}, 1);
  }
}

# empty where select
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  my $result = $dbi->select(table => $table1, where => {});
  my $row = $result->one;
  is_deeply($row, {$key1 => 1, $key2 => 2});
}

# where
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    {
      my $where = $dbi->where->clause(['and', "$key1 = :$key1", "$key2 = :$key2"]);
      is("$where", "where ( $key1 = :$key1 and $key2 = :$key2 )", 'no param');
    }

    {
      my $where = $dbi->where
        ->clause(['and', "$key1 = :$key1", "$key2 = :$key2"])
        ->param({$key1 => 1});

      my $result = $dbi->select(
        table => $table1,
        where => $where
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $result = $dbi->select(
        table => $table1,
        where => [
          ['and', "$key1 = :$key1", "$key2 = :$key2"],
          {$key1 => 1}
        ]
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where
        ->clause(['and', "$key1 = :$key1", "$key2 = :$key2"])
        ->param({$key1 => 1, $key2 => 2});
      my $result = $dbi->select(
        table => $table1,
        where => $where
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where
        ->clause(['and', "$key1 = :$key1", "$key2 = :$key2"])
        ->param({});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }

    {
      my $where = $dbi->where
        ->clause(['and', ['or', "$key1 > :$key1", "$key1 < :$key1"], "$key2 = :$key2"])
        ->param({$key1 => [0, 3], $key2 => 2});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where;
      my $result = $dbi->select(
        table => $table1,
        where => $where
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }

    eval {
      my $where = $dbi->where
                 ->clause(['uuu']);
      my $result = $dbi->select(
        table => $table1,
        where => $where
      );
    };
    ok($@);

    {
      my $where = $dbi->where;
      is("$where", '');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 2])
        ->param({$key1 => [1, 3]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }

    {
      my $where = $dbi->where
         ->clause(['or', ("$key1 = :$key1") x 2])
         ->param({$key1 => [1]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 2])
        ->param({$key1 => 1});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where
        ->clause("$key1 = :$key1")
        ->param({$key1 => 1});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [$dbi->not_exists, 1, 3]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [1, $dbi->not_exists, 3]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [1, 3, $dbi->not_exists]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [1, $dbi->not_exists, $dbi->not_exists]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [$dbi->not_exists, 1, $dbi->not_exists]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [$dbi->not_exists, $dbi->not_exists, 1]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => [$dbi->not_exists, $dbi->not_exists, $dbi->not_exists]});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['or', ("$key1 = :$key1") x 3])
        ->param({$key1 => []});
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');
    }

    {
      my $where = $dbi->where
        ->clause(['and',"$key1 is not null", "$key2 is not null" ]);
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}], 'not_exists');

      eval {$dbi->where(ppp => 1) };
      like($@, qr/invalid/);
    }

    {
      my $where = $dbi->where(
        clause => ['and', ['or'], ['and', "$key1 = :$key1", "$key2 = :$key2"]],
        param => {$key1 => 1, $key2 => 2}
      );
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where(
        clause => ['and', ['or'], ['or', ":$key1", ":$key2"]],
        param => {}
      );
      my $result = $dbi->select(
        table => $table1,
        where => $where,
      );
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }

    {
      my $where = $dbi->where;
      $where->clause(['and', ":${key1}{=}"]);
      $where->param({$key1 => undef});
      my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $where = $dbi->where;
      $where->clause(['or', ":${key1}{=}", ":${key1}{=}"]);
      $where->param({$key1 => [undef, undef]});
      {
        my $result = $dbi->execute("select * from $table1 $where", {$key1 => [1, 0]});
        my $row = $result->all;
        is_deeply($row, [{$key1 => 1, $key2 => 2}]);
      }
      {
        my $result = $dbi->execute("select * from $table1 $where", {$key1 => [0, 1]});
        my $row = $result->all;
        is_deeply($row, [{$key1 => 1, $key2 => 2}]);
      }
    }
  }
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => '00:00:00'}, table => $table1);
    $dbi->insert({$key1 => 1, $key2 => '3'}, table => $table1);
    my $where = $dbi->where
      ->clause(['and', "$key1 = :$key1", "$key2 = '00:00:00'"])
      ->param({$key1 => 1});

    my $result = $dbi->select(
      table => $table1,
      where => $where
    );
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => '00:00:00'}]);
  }

  # table not specify exception
  {
    my $dbi = DBIx::Custom->connect;
    eval {$dbi->select($key1)};
    ok($@);

    eval{DBIx::Custom->connect(dsn => undef)};
    ok($@);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->register_filter(twice => sub { $_[0] * 2 });
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1,
               filter => {$key1 => 'twice'});
    my $row = $dbi->select(table => $table1)->one;
    is_deeply($row, {$key1 => 2, $key2 => 2});
    eval {$dbi->insert({$key1 => 1, $key2 => 2}, table => $table1,
               filter => {$key1 => 'no'}) };
    like($@, qr//);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->register_filter(one => sub { 1 });
    my $result = $dbi->select(table => $table1);
    eval {$result->filter($key1 => 'no')};
    like($@, qr/not registered/);
  }
}

# option
{
  my $dbi = DBIx::Custom->connect(option => {PrintError => 1});
  ok($dbi->dbh->{PrintError});
}

# DBIx::Custom::Result stash()
{
  my $result = DBIx::Custom::Result->new;
  is_deeply($result->stash, {}, 'default');
  $result->stash->{foo} = 1;
  is($result->stash->{foo}, 1, 'get and set');
}

# values_clause
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key1 => 1, $key2 => 2};
  my $values_clause = $dbi->values_clause($param);
  my $sql = <<"EOS";
insert into $table1 $values_clause
EOS
  $dbi->execute($sql, $param, table => $table1);
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key2}, 2);
}

# mycolumn
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);

  $dbi->include_model('MyModel7');

  $dbi->setup_model;
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 1, $key3 => 3}, table => $table2);
  my $model = $dbi->model($table1);
  {
    my $result = $model->select(
      column => [
        $model->mycolumn,
        $model->column($table2)
      ]
    );
    is_deeply($result->one,
      {$key1 => 1, $key2 => 2, "$table2.$key1" => 1, "$table2.$key3" => 3});
  }

  {
    my $result = $model->select(
      column => [
        $model->mycolumn([$key1]),
        $model->column($table2 => [$key1])
      ]
    );
    is_deeply($result->one,
            {$key1 => 1, "$table2.$key1" => 1});
  }

  {
    my $result = $model->select(
      column => [
        $model->mycolumn([$key1]),
        {$table2 => [$key1]}
      ]
    );
    is_deeply($result->one,
            {$key1 => 1, "$table2.$key1" => 1});
  }
}

# merge_param
{
  my $dbi = DBIx::Custom->new;

  {
    my $params = [
      {$key1 => 1, $key2 => 2, $key3 => 3},
      {$key1 => 1, $key2 => 2},
    ];
    my $param = $dbi->merge_param($params->[0], $params->[1]);
    is_deeply($param, {$key1 => [1, 1], $key2 => [2, 2], $key3 => 3});
  }

  {
    my $params = [
      {$key1 => [1, 2], $key2 => $dbi->not_exists, $key3 => [1, 2]},
      {$key1 => [3, 4], $key2 => [2, 3], $key3 => $dbi->not_exists}
    ];
    my $param = $dbi->merge_param($params->[0], $params->[1]);
    is_deeply($param, {$key1 => [1, 2, 3, 4], $key2 => [$dbi->not_exists, 2, 3], $key3 => [1, 2, $dbi->not_exists]});
  }
}

# select() param option
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table2);
  $dbi->insert({$key1 => 1, $key3 => 4}, table => $table2);
  $dbi->insert({$key1 => 2, $key3 => 5}, table => $table2);
  {
    my $rows = $dbi->select(
      table => $table1,
      column => "$table1.$key1 as " . u("${table1}_$key1") . ", $key2, $key3",
      where   => {"$table1.$key2" => 3},
      join  => ["inner join (select * from $table2 where :$table2.${key3}{=})" .
                " $q$table2$p on $table1.$key1 = $q$table2$p.$key1"],
      param => {"$table2.$key3" => 5}
    )->all;
    is_deeply($rows, [{u"${table1}_$key1" => 2, $key2 => 3, $key3 => 5}]);
  }

  {
    my $rows = $dbi->select(
      table => $table1,
      column => "$table1.$key1 as " . u("${table1}_$key1") . ", $key2, $key3",
      where   => {"$table1.$key2" => 3},
      join  => "inner join (select * from $table2 where :$table2.${key3}{=})" .
               " $q$table2$p on $table1.$key1 = $q$table2$p.$key1",
      param => {"$table2.$key3" => 5}
    )->all;
    is_deeply($rows, [{u"${table1}_$key1" => 2, $key2 => 3, $key3 => 5}]);
  }
}

# select() string where
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
  {
    my $rows = $dbi->select(
      table => $table1,
      where => ["$key1 = :$key1 and $key2 = :$key2", {$key1 => 1, $key2 => 2}]
    )->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
    my $rows = $dbi->select(
      table => $table1,
      where => [
        "$key1 = :$key1 and $key2 = :$key2",
        {$key1 => 1, $key2 => 2}
      ]
    )->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
    my $rows = $dbi->select(
      table => $table1,
      where => [
        "$key1 = :$key1 and $key2 = :$key2",
        {$key1 => 1, $key2 => 2}
      ]
    )->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
  }
}

# delete() string where
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
    $dbi->delete(
      table => $table1,
      where => ["$key1 = :$key1 and $key2 = :$key2", {$key1 => 1, $key2 => 2}]
    );
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 2, $key2 => 3}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
    $dbi->delete(
      table => $table1,
      where => [
        "$key1 = :$key1 and $key2 = :$key2",
         {$key1 => 1, $key2 => 2}
      ]
    );
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 2, $key2 => 3}]);
  }
}

# update() string where
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->update(
      {$key1 => 5},
      table => $table1,
      where => ["$key1 = :$key1 and $key2 = :$key2", {$key1 => 1, $key2 => 2}]
    );
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 5, $key2 => 2}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->update(
      {$key1 => 5},
      table => $table1,
      where => [
        "$key1 = :$key1 and $key2 = :$key2",
        {$key1 => 1, $key2 => 2}
      ]
    );
    my $rows = $dbi->select(table => $table1)->all;
    is_deeply($rows, [{$key1 => 5, $key2 => 2}]);
  }
}

# insert id and primary_key option
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert(
    {$key3 => 3},
    primary_key => [$key1, $key2],
    table => $table1,
    id => [1, 2],
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key2}, 2);
  is($dbi->select(table => $table1)->one->{$key3}, 3);

  $dbi->insert(
    {$key3 => 3},
    primary_key => [$key1, $key2],
    table => $table1,
    id => [1, 2],
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key2}, 2);
  is($dbi->select(table => $table1)->one->{$key3}, 3);

  $dbi->delete_all(table => $table1);
  $dbi->insert(
    {$key2 => 2, $key3 => 3},
    primary_key => $key1,
    table => $table1,
    id => 0,
  );

  is($dbi->select(table => $table1)->one->{$key1}, 0);
  is($dbi->select(table => $table1)->one->{$key2}, 2);
  is($dbi->select(table => $table1)->one->{$key3}, 3);

  $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert(
    {$key3 => 3},
    primary_key => $key1,
    table => $table1,
    id => bless({value => 1}, 'AAAA'),
    filter => {$key1 => sub { shift->{value} }}
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key3}, 3);

  $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert(
    {$key3 => 3},
    primary_key => [$key1, $key2],
    table => $table1,
    id => 1,
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  ok(!$dbi->select(table => $table1)->one->{$key2});
  is($dbi->select(table => $table1)->one->{$key3}, 3);

  $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert(
    {$key3 => 3},
    primary_key => [$key1, $key2],
    table => $table1,
    id => [1, 2],
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key2}, 2);
  is($dbi->select(table => $table1)->one->{$key3}, 3);
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  my $param = {$key3 => 3, $key2 => 4};
  $dbi->insert(
    $param,
    primary_key => [$key1, $key2],
    table => $table1,
    id => [1, 2],
  );
  is($dbi->select(table => $table1)->one->{$key1}, 1);
  is($dbi->select(table => $table1)->one->{$key2}, 4);
  is($dbi->select(table => $table1)->one->{$key3}, 3);
  is_deeply($param, {$key3 => 3, $key2 => 4});
}

# model insert id and primary_key option
{
 {
    my $dbi = DBIx::Custom->connect;

    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);

    $dbi->include_model('MyModel5');

    $dbi->model($table1)->insert(
      {$key3 => 3},
      id => [1, 2],
    );
    my $result = $dbi->model($table1)->select;
    my $row = $result->one;
    is($row->{$key1}, 1);
    is($row->{$key2}, 2);
    is($row->{$key3}, 3);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->include_model('MyModel5');
    $dbi->model($table1)->insert(
      {$key3 => 3},
      id => [1, 2]
    );
    my $result = $dbi->model($table1)->select;
    my $row = $result->one;
    is($row->{$key1}, 1);
    is($row->{$key2}, 2);
    is($row->{$key3}, 3);
  }
}

# update and id option
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
    $dbi->update(
      {$key3 => 4},
      table => $table1,
      primary_key => [$key1, $key2],
      id => [1, 2],
    );
    is($dbi->select(table => $table1)->one->{$key1}, 1);
    is($dbi->select(table => $table1)->one->{$key2}, 2);
    is($dbi->select(table => $table1)->one->{$key3}, 4);

    $dbi->delete_all(table => $table1);
    $dbi->insert({$key1 => 0, $key2 => 2, $key3 => 3}, table => $table1);
    $dbi->update(
      {$key3 => 4},
      table => $table1,
      primary_key => $key1,
      id => 0,
    );
    is($dbi->select(table => $table1)->one->{$key1}, 0);
    is($dbi->select(table => $table1)->one->{$key2}, 2);
    is($dbi->select(table => $table1)->one->{$key3}, 4);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
    $dbi->update(
      {$key3 => 4},
      table => $table1,
      primary_key => [$key1, $key2],
      id => [1, 2]
    );
    is($dbi->select(table => $table1)->one->{$key1}, 1);
    is($dbi->select(table => $table1)->one->{$key2}, 2);
    is($dbi->select(table => $table1)->one->{$key3}, 4);

  }

  {
    # model update and id option
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->include_model('MyModel5');
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
    $dbi->model($table1)->update(
      {$key3 => 4},
      id => [1, 2],
    );
    my $result = $dbi->model($table1)->select;
    my $row = $result->one;
    is($row->{$key1}, 1);
    is($row->{$key2}, 2);
    is($row->{$key3}, 4);
  }
}

# delete and id option
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
  $dbi->delete(
    table => $table1,
    primary_key => [$key1, $key2],
    id => [1, 2],
  );
  is_deeply($dbi->select(table => $table1)->all, []);

  $dbi->insert({$key1 => 0, $key2 => 2, $key3 => 3}, table => $table1);
  $dbi->delete(
    table => $table1,
    primary_key => $key1,
    id => 0,
  );
  is_deeply($dbi->select(table => $table1)->all, []);
}

# model delete and id option
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  eval { $dbi->execute("drop table $table3") };
  $dbi->execute($create_table1_2);
  $dbi->execute($create_table2_2);
  $dbi->execute($create_table3);
  $dbi->include_model('MyModel5');
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
  $dbi->model($table1)->delete(id => [1, 2]);
  is_deeply($dbi->select(table => $table1)->all, []);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table2);
  $dbi->model($table1)->delete(id => [1, 2]);
  is_deeply($dbi->select(table => $table1)->all, []);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table3);
  $dbi->model($table3)->delete(id => [1, 2]);
  is_deeply($dbi->select(table => $table3)->all, []);
}

# select and id option
{
  my $dbi = DBIx::Custom->connect;
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_2);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
    my $result = $dbi->select(
      table => $table1,
      primary_key => [$key1, $key2],
      id => [1, 2]
    );
    my $row = $result->one;
    is($row->{$key1}, 1);
    is($row->{$key2}, 2);
    is($row->{$key3}, 3);
  }

  {
    $dbi->delete_all(table => $table1);
    $dbi->insert({$key1 => 0, $key2 => 2, $key3 => 3}, table => $table1);
    my $result = $dbi->select(
      table => $table1,
      primary_key => $key1,
      id => 0,
    );
    my $row = $result->one;
    is($row->{$key1}, 0);
    is($row->{$key2}, 2);
    is($row->{$key3}, 3);
  }

  {
    $dbi->delete_all(table => $table1);
    $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3}, table => $table1);
    my $result = $dbi->select(
      table => $table1,
      primary_key => [$key1, $key2],
      id => [1, 2]
    );
    my $row = $result->one;
    is($row->{$key1}, 1);
    is($row->{$key2}, 2);
    is($row->{$key3}, 3);
  }
}
# column separator is default
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);
  $dbi->include_model('MyModel6');
  $dbi->setup_model;
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 1, $key3 => 3}, table => $table2);
  my $model = $dbi->model($table1);

  {
    my $result = $model->select(
      column => [$model->column($table2)],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {"$table2.$key1" => 1, "$table2.$key3" => 3});
  }

  {
    my $result = $model->select(
      column => [$model->column($table2 => [$key1, $key3])],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {"$table2.$key1" => 1, "$table2.$key3" => 3});
  }
}

# separator
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);

  $dbi->create_model(
    table => $table1,
    join => [
     "left outer join $table2 on $table1.$key1 = $table2.$key1"
    ],
    primary_key => [$key1],
  );
  my $model2 = $dbi->create_model(
    table => $table2,
  );

  {
    $dbi->setup_model;
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 1, $key3 => 3}, table => $table2);
    my $model = $dbi->model($table1);
    my $result = $model->select(
      column => [
        $model->mycolumn,
        {$table2 => [$key1, $key3]}
      ],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, "$table2.$key1" => 1, "$table2.$key3" => 3});
    is_deeply($model2->select->one, {$key1 => 1, $key3 => 3});
  }

  {
    $dbi->separator('__');
    my $model = $dbi->model($table1);
    my $result = $model->select(
      column => [
        $model->mycolumn,
        {$table2 => [$key1, $key3]}
      ],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
            {$key1 => 1, $key2 => 2, u2"${table2}__$key1" => 1, u2"${table2}__$key3" => 3});
    is_deeply($model2->select->one, {$key1 => 1, $key3 => 3});
  }

  {
    $dbi->separator('-');
    my $model = $dbi->model($table1);
    my $result = $model->select(
      column => [
        $model->mycolumn,
        {$table2 => [$key1, $key3]}
      ],
      where => {"$table1.$key1" => 1}
    );
    is_deeply($result->one,
      {$key1 => 1, $key2 => 2, hy"$table2-$key1" => 1, hy"$table2-$key3" => 3});
    is_deeply($model2->select->one, {$key1 => 1, $key3 => 3});
  }
}

# filter_off
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);

  $dbi->create_model(
    table => $table1,
    join => [
     "left outer join $table2 on $table1.$key1 = $table2.$key1"
    ],
    primary_key => [$key1],
  );
  $dbi->setup_model;
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  my $model = $dbi->model($table1);
  my $result = $model->select(column => $key1);
  $result->filter($key1 => sub { $_[0] * 2 });
  is_deeply($result->one, {$key1 => 2});
}

# available_datetype
{
  my $dbi = DBIx::Custom->connect;
  ok($dbi->can('available_datatype'));
}

# select prefix option
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  my $rows = $dbi->select(prefix => "$key1,", column => $key2, table => $table1)->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 2}], "table");
}

# mapper
{
  {
    my $dbi = DBIx::Custom->connect;
    my $param = $dbi->mapper(param => {id => 1, author => 'Ken', price => 1900})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author" => sub { '%' . $_[0] . '%' }],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    is_deeply($param, {"$table1.id" => 1, "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  my $dbi = DBIx::Custom->connect;

  {
    my $param = $dbi->mapper(param => {id => 1, author => 'Ken', price => 1900})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author" => $dbi->like_value],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    is_deeply($param, {"$table1.id" => 1, "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  {
    my $param = $dbi->mapper(param => {id => 0, author => 0, price => 0})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author" => sub { '%' . $_[0] . '%' }],
      price => ["$table1.price", sub { '%' . $_[0] . '%' }, sub { $_[0] eq 0 }]
    );
    is_deeply($param, {"$table1.id" => 0, "$table1.author" => '%0%', "$table1.price" => '%0%'});
  }

  {
    my $param = $dbi->mapper(param => {id => '', author => '', price => ''})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author" => sub { '%' . $_[0] . '%' }],
      price => ["$table1.price", sub { '%' . $_[0] . '%' }, sub { $_[0] eq 1 }]
    );
    is_deeply($param, {});
  }

  {
    my $param = $dbi->mapper(param => {id => undef, author => undef, price => undef})->map(
      id => {key => "$table1.id"},
      price => {key => "$table1.price", condition => 'exists'}
    );
    is_deeply($param, {"$table1.price" => undef});
  }

  {
    my $param = $dbi->mapper(param => {price => 'a'})->map(
      id => {key => "$table1.id", condition => 'exists'},
      price => ["$table1.price", sub { '%' . $_[0] }, 'exists']
    );
    is_deeply($param, {"$table1.price" => '%a'});
  }

  {
    my $param = $dbi->mapper(param => {price => 'a'}, condition => 'exists')->map(
      id => {key => "$table1.id"},
      price => ["$table1.price", sub { '%' . $_[0] }]
    );
    is_deeply($param, {"$table1.price" => '%a'});
  }

  {
    my $param = $dbi->mapper(param => {price => 'a', author => 'b'})->map(
      price => sub { '%' . $_[0] },
      author => 'book.author'
    );
    is_deeply($param, {price => '%a', 'book.author' => 'b'});
  }

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => undef}, condition => 'defined')->map;
    $where->param($param);
    my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }

  {
    my $where = $dbi->where;
    $where->clause(['or', ":${key1}{=}", ":${key1}{=}"]);

    {
      my $param = $dbi->mapper(param => {$key1 => [undef, undef]}, condition => 'exists')->map;
      my $result = $dbi->execute("select * from $table1 $where", {$key1 => [1, 0]});
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $result = $dbi->execute("select * from $table1 $where", {$key1 => [0, 1]});
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}]);
    }
  }

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => [undef, undef]}, condition => 'defined')->map;
    $where->param($param);

    {
      my $result = $dbi->execute("select * from $table1 $where", {$key1 => [1, 0]});
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }

    {
      my $result = $dbi->execute("select * from $table1 $where", {$key1 => [0, 1]});
      my $row = $result->all;
      is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
    }
  }

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => 0}, condition => 'length')
    ->pass([$key1, $key2])->map;
    $where->param($param);
    my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => 2}]);
  }

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => ''}, condition => 'length')->map;
    $where->param($param);
    my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => 5}, condition => sub { ($_[0] || '') eq 5 })
    ->pass([$key1, $key2])->map;
    $where->param($param);
    my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => 2}]);
  }

  {
    my $where = $dbi->where;
    $where->clause(['and', ":${key1}{=}"]);
    my $param = $dbi->mapper(param => {$key1 => 7}, condition => sub { ($_[0] || '') eq 5 })->map;
    $where->param($param);
    my $result = $dbi->execute("select * from $table1 $where", {$key1 => 1});
    my $row = $result->all;
    is_deeply($row, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => 1, author => 'Ken', price => 1900})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    $where->param($param);
    is_deeply($where->param, {"$table1.id" => 1, "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => 0, author => 0, price => 0})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }],
      price => ["$table1.price", sub { '%' . $_[0] . '%' }, sub { $_[0] eq 0 }]
    );
    $where->param($param);
    is_deeply($where->param, {"$table1.id" => 0, "$table1.author" => '%0%', "$table1.price" => '%0%'});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => '', author => '', price => ''})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }],
      price => ["$table1.price", sub { '%' . $_[0] . '%' }, sub { $_[0] eq 1 }]
    );
    $where->param($param);
    is_deeply($where->param, {});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => undef, author => undef, price => undef}, condition => 'exists')->map(
      id => {key => "$table1.id"},
      price => {key => "$table1.price", condition => 'exists'}
    );
    is_deeply($param, {"$table1.id"  => undef,"$table1.price" => undef});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {price => 'a'})->map(
      id => {key => "$table1.id", condition => 'exists'},
      price => ["$table1.price", sub { '%' . $_[0] }, 'exists']
    );
    is_deeply($param, {"$table1.price" => '%a'});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => [1, 2], author => 'Ken', price => 1900})->map(
      id => {key => "$table1.id"},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    is_deeply($param, {"$table1.id" => [1, 2], "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => ['', ''], author => 'Ken', price => 1900}, condition => 'length')->map(
      id => {key => "$table1.id"},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    is_deeply($param, {"$table1.id" => [$dbi->not_exists, $dbi->not_exists], "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => ['', ''], author => 'Ken', price => 1900})->map(
      id => {key => "$table1.id", condition => 'length'},
      author => ["$table1.author", sub { '%' . $_[0] . '%' }, 'defined'],
      price => {key => "$table1.price", condition => sub { $_[0] eq 1900 }}
    );
    is_deeply($param, {"$table1.id" => [$dbi->not_exists, $dbi->not_exists], "$table1.author" => '%Ken%',
    "$table1.price" => 1900});
  }

  {
    my $where = $dbi->where;
    my $param = $dbi->mapper(param => {id => 'a', author => 'b', price => 'c'}, pass => [qw/id author/])
      ->map(price => {key => 'book.price'});
    is_deeply($param, {id => 'a', author => 'b', 'book.price' => 'c'});
  }

  {
    my $param = $dbi->mapper(param => {author => 'Ken',})->map(
      author => ["$table1.author" => '%<value>%'],
    );
    is_deeply($param, {"$table1.author" => '%Ken%'});
  }

  {
    my $param = $dbi->mapper(param => {author => 'Ken'})->map(
      author => ["$table1.author" => 'p'],
    );
    is_deeply($param, {"$table1.author" => 'p'});
  }

  {
    my $param = $dbi->mapper(param => {author => 'Ken',})->map(
      author => {value => '%<value>%'}
    );
    is_deeply($param, {"author" => '%Ken%'});
  }
}

# order
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 1}, table => $table1);
  $dbi->insert({$key1 => 1, $key2 => 3}, table => $table1);
  $dbi->insert({$key1 => 2, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 2, $key2 => 4}, table => $table1);
  my $order = $dbi->order;

  {
    $order->prepend($key1, "$key2 desc");
    my $result = $dbi->select(table => $table1, append => $order);
    is_deeply($result->all, [{$key1 => 1, $key2 => 3}, {$key1 => 1, $key2 => 1},
      {$key1 => 2, $key2 => 4}, {$key1 => 2, $key2 => 2}]);
  }

  {
    $order->prepend("$key1 desc");
    my $result = $dbi->select(table => $table1, append => $order);
    is_deeply($result->all, [{$key1 => 2, $key2 => 4}, {$key1 => 2, $key2 => 2},
    {$key1 => 1, $key2 => 3}, {$key1 => 1, $key2 => 1}]);
  }
}

# DBIx::Custom header
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  my $result = $dbi->execute("select $key1 as h1, $key2 as h2 from $table1");
  is_deeply([map { lc } @{$result->header}], [qw/h1 h2/]);
  $result->sth->finish;
}

# Named placeholder :name(operater) syntax
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);

  {
    my $source = "select * from $table1 where :${key1}{=} and :${key2}{=}";
    my $result = $dbi->execute($source, {$key1 => 1, $key2 => 2});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }

  {
    my $source = "select * from $table1 where :${key1}{ = } and :${key2}{=}";
    my $result = $dbi->execute($source, {$key1 => 1, $key2 => 2});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }

  {
    my $source = "select * from $table1 where :${key1}{<} and :${key2}{=}";
    my $result = $dbi->execute($source, {$key1 => 5, $key2 => 2});
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }

  {
    my $source = "select * from $table1 where :$table1.${key1}{=} and :$table1.${key2}{=}";
    my $result = $dbi->execute(
      $source,
      {"$table1.$key1" => 1, "$table1.$key2" => 1},
      filter => {"$table1.$key2" => sub { $_[0] * 2 }}
    );
    my $rows = $result->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}]);
  }
}

# result
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  {
    my $result = $dbi->select(table => $table1);
    my @rows = ();
    while (my $row = $result->fetch) {
      push @rows, [@$row];
    }
    is_deeply(\@rows, [[1, 2], [3, 4]]);
  }

  {
    my $result = $dbi->select(table => $table1);
    my @rows = ();
    while (my $row = $result->fetch_hash) {
      push @rows, {%$row};
    }
    is_deeply(\@rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }
}

# fetch_all
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

  {
    my $result = $dbi->select(table => $table1);
    my $rows = $result->fetch_all;
    is_deeply($rows, [[1, 2], [3, 4]]);
  }

  {
    my $result = $dbi->select(table => $table1);
    my $rows = $result->fetch_hash_all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }

  {
    my $result = $dbi->select(table => $table1);
    $result->dbi->filters({three_times => sub { $_[0] * 3}});
    $result->filter({$key1 => 'three_times'});
    my $rows = $result->fetch_all;
    is_deeply($rows, [[3, 2], [9, 4]], "array");
  }

  {
    my $result = $dbi->select(column => [$key1, $key1, $key2], table => $table1);
    $result->dbi->filters({three_times => sub { $_[0] * 3}});
    $result->filter({$key1 => 'three_times'});
    my $rows = $result->fetch_all;
    is_deeply($rows, [[3, 3, 2], [9, 9, 4]], "array");
  }

  {
    my $result = $dbi->select(table => $table1);
    $result->dbi->filters({three_times => sub { $_[0] * 3}});
    $result->filter({$key1 => 'three_times'});
    my $rows = $result->fetch_hash_all;
    is_deeply($rows, [{$key1 => 3, $key2 => 2}, {$key1 => 9, $key2 => 4}], "hash");
  }

  # flat
  {
    my $result = $dbi->select(table => $table1);
    my $rows = [$result->flat];
    is_deeply($rows, [1, 2, 3, 4]);
  }
}

# kv
{
  my $dbi = DBIx::Custom->connect;
  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 0, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);

    my $result = $dbi->select([$key1, $key2], table => $table1, append => "order by $key1");
    my $rows = $result->kv;
    is_deeply($rows, {0 => {$key2 => 2}, 3 => {$key2 => 4}});
  }

  {
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 0, $key2 => 1}, table => $table1);
    $dbi->insert({$key1 => 0, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 5}, table => $table1);
  }

  {
    my $result = $dbi->select([$key1, $key2], table => $table1, append => "order by $key2");
    my $rows = $result->kv(multi => 1);
    is_deeply($rows, {
      0 => [
        {$key2 => 1},
        {$key2 => 2}
      ],
      3 => [
        {$key2 => 4},
        {$key2 => 5}
      ]
    });
  }

  {
    my $result = $dbi->select([$key1, $key2], table => $table1, append => "order by $key2");
    my $rows = $result->kvs;
    is_deeply($rows, {
      0 => [
        {$key2 => 1},
        {$key2 => 2}
      ],
      3 => [
        {$key2 => 4},
        {$key2 => 5}
      ]
    });
  }
}

# DBIx::Custom::Result fetch_multi
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  $dbi->insert({$key1 => 5, $key2 => 6}, table => $table1);
  my $result = $dbi->select(table => $table1);
  {
    my $rows = $result->fetch_multi(2);
    is_deeply($rows, [[1, 2], [3, 4]]);
  }
  {
    my $rows = $result->fetch_multi(2);
    is_deeply($rows, [[5, 6]]);
  }
  {
    my $rows = $result->fetch_multi(2);
    ok(!$rows);
  }
}

# DBIx::Custom::Result fetch_hash_multi
{
  my $dbi = DBIx::Custom->connect;

  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  $dbi->insert({$key1 => 5, $key2 => 6}, table => $table1);
  my $result = $dbi->select(table => $table1);
  {
    my $rows = $result->fetch_hash_multi(2);
    is_deeply($rows, [{$key1 => 1, $key2 => 2}, {$key1 => 3, $key2 => 4}]);
  }

  {
    my $rows = $result->fetch_hash_multi(2);
    is_deeply($rows, [{$key1 => 5, $key2 => 6}]);
  }

  {
    my $rows = $result->fetch_hash_multi(2);
    ok(!$rows);
  }
}

# select() after_build_sql option
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 2, $key2 => 3}, table => $table1);
  my $rows = $dbi->select(
    table => $table1,
    column => $key1,
    after_build_sql => sub {
      my $sql = shift;
      $sql = "select * from ( $sql ) t where $key1 = 1";
      return $sql;
    }
  )->all;
  is_deeply($rows, [{$key1 => 1}]);
}

# dbi method from model
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->include_model('MyModel8::MyModel1');
  $dbi->setup_model;
  my $model = $dbi->model($table1);
  eval{$model->execute("select * from $table1")};
  ok(!$@);
}

# column table option
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table2);
  $dbi->include_model('MyModel8::MyModel1');
  $dbi->setup_model;
  $dbi->execute("insert into $table1 ($key1, $key2) values (1, 2)");
  $dbi->execute("insert into $table2 ($key1, $key3) values (1, 4)");
  my $model = $dbi->model($table1);

  {
    my $result = $model->select(
      column => [
        $model->column($table2, {alias => u$table2_alias})
      ],
      where => {u($table2_alias) . ".$key3" => 4}
    );
    is_deeply($result->one,
            {u($table2_alias) . ".$key1" => 1, u($table2_alias) . ".$key3" => 4});
  }

  {
    $dbi->separator('__');
    my $result = $model->select(
      column => [
        $model->column($table2, {alias => u$table2_alias})
      ],
      where => {u($table2_alias) . ".$key3" => 4}
    );
    is_deeply($result->one,
      {u(${table2_alias}) . "__$key1" => 1, u(${table2_alias}) . "__$key3" => 4});
  }

  {
    $dbi->separator('-');
    my $result = $model->select(
      column => [
        $model->column($table2, {alias => u$table2_alias})
      ],
      where => {u($table2_alias) . ".$key3" => 4}
    );
    is_deeply($result->one,
      {u(${table2_alias}) . "-$key1" => 1, u(${table2_alias}) . "-$key3" => 4});
  }

  # create_model
  $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);

  $dbi->create_model(
    table => $table1,
    join => [
     "left outer join $table2 on $table1.$key1 = $table2.$key1"
    ],
    primary_key => [$key1]
  );
}

# model helper
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table2);
  $dbi->insert({$key1 => 1, $key3 => 3}, table => $table2);
  my $model = $dbi->create_model(
    table => $table2
  );
  $model->helper(foo => sub { shift->select(@_) });
  is_deeply($model->foo->one, {$key1 => 1, $key3 => 3});
}

# assign_clause
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);

  my $param = {$key2 => 11};
  my $assign_clause = $dbi->assign_clause($param);
  my $sql = <<"EOS";
update $table1 set $assign_clause
where $key1 = 1
EOS
  $dbi->execute($sql, $param);
  my $result = $dbi->execute("select * from $table1 order by $key1", table => $table1);
  my $rows   = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
    "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);

  my $param = {$key2 => 11, $key3 => 33};
  my $assign_clause = $dbi->assign_clause($param);
  my $sql = <<"EOS";
update $table1 set $assign_clause
where $key1 = 1
EOS
  $dbi->execute($sql, $param);
  my $result = $dbi->execute("select * from $table1 order by $key1", table => $table1);
  my $rows   = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 33, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
    "basic");
}

{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);

  $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1_2);
  $dbi->insert({$key1 => 1, $key2 => 2, $key3 => 3, $key4 => 4, $key5 => 5}, table => $table1);
  $dbi->insert({$key1 => 6, $key2 => 7, $key3 => 8, $key4 => 9, $key5 => 10}, table => $table1);

  my $param = {$key2 => 11};
  my $assign_clause = $dbi->assign_clause($param);
  my $sql = <<"EOS";
update $table1 set $assign_clause
where $key1 = 1
EOS
  $dbi->execute($sql, $param, table => $table1);
  my $result = $dbi->execute("select * from $table1 order by $key1");
  my $rows   = $result->all;
  is_deeply($rows, [{$key1 => 1, $key2 => 11, $key3 => 3, $key4 => 4, $key5 => 5},
    {$key1 => 6, $key2 => 7,  $key3 => 8, $key4 => 9, $key5 => 10}],
    "basic");
}

# Model class
{
  {
    {
      my $dbi = DBIx::Custom->connect;
      eval { $dbi->execute("drop table $table1") };
      $dbi->execute($create_table1);
      $dbi->include_model(
        MyModel1 => [
          $dbi->table1,
          $dbi->table2
        ]
      );
      my $model = $dbi->model($table1);
      $model->insert({$key1 => 'a', $key2 => 'b'});
      is_deeply($model->list->all, [{$key1 => 'a', $key2 => 'b'}], 'basic');
    }
    {
      my $dbi = DBIx::Custom->connect;
      eval { $dbi->execute("drop table $table2") };
      $dbi->execute($create_table2);
      $dbi->include_model(
        MyModel1 => [
          $dbi->table1,
          $dbi->table2
        ]
      );
      my $model = $dbi->model($table2);
      $model->insert({$key1 => 'a'});
      is_deeply($model->list->all, [{$key1 => 'a', $key3 => undef}], 'basic');
      is($dbi->models->{$table1}, $dbi->model($table1));
      is($dbi->models->{$table2}, $dbi->model($table2));
    }
  }

  {
    {
      my $dbi = DBIx::Custom->connect;
      eval { $dbi->execute("drop table $table1") };
      $dbi->execute($create_table1);
      $dbi->include_model(
        MyModel2 => [
            $table1,
            {class => $table2, name => $table2}
        ]
      );
      my $model = $dbi->model($table1);
      $model->insert({$key1 => 'a', $key2 => 'b'});
      is_deeply($model->list->all, [{$key1 => 'a', $key2 => 'b'}], 'basic');
    }
    {
      my $dbi = DBIx::Custom->connect;
      $dbi->include_model(
        MyModel2 => [
            $table1,
            {class => $table2, name => $table2}
        ]
      );
      eval { $dbi->execute("drop table $table2") };
      $dbi->execute($create_table2);
      $dbi->include_model(
        MyModel2 => [
            $table1,
            {class => $table2, name => $table2}
        ]
      );
      my $model = $dbi->model($table2);
      $model->insert({$key1 => 'a'});
      is_deeply($model->list->all, [{$key1 => 'a', $key3 => undef}], 'basic');
    }
  }
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    $dbi->include_model('MyModel4');

    {
      my $model = $dbi->model($table2);
      $model->insert({$key1 => 'a'});
      is_deeply($model->list->all, [{$key1 => 'a', $key3 => undef}], 'include all model');
    }
    {
      $dbi->insert({$key1 => 1}, table => $table1);
      my $model = $dbi->model($table1);
      is_deeply($model->list->all, [{$key1 => 1, $key2 => undef}], 'include all model');
    }
  }
}
# primary_key
{
  my $dbi = DBIx::Custom->connect;
  $dbi->include_model(
    MyModel1 => [
      $dbi->table1,
      $dbi->table2
    ]
  );
  my $model = $dbi->model($table1);
  $model->primary_key([$key1, $key2]);
  is_deeply($model->primary_key, [$key1, $key2]);
}

# columns
{
  my $dbi = DBIx::Custom->connect;
  $dbi->include_model(
    MyModel1 => [
      $dbi->table1,
      $dbi->table2
    ]
  );
  my $model = $dbi->model($table1);
  $model->columns([$key1, $key2]);
  is_deeply($model->columns, [$key1, $key2]);
}

# columns
{
  my $dbi = DBIx::Custom->connect;
  $dbi->include_model(
    MyModel1 => [
      $dbi->table1,
      $dbi->table2
    ]
  );
  my $model = $dbi->model($table1);
  $model->columns([$key1, $key2]);
  is_deeply($model->columns, [$key1, $key2]);
}

# setup_model
{
  my $dbi = DBIx::Custom->connect;
  $dbi->user_table_info($user_table_info);
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table1);
  $dbi->execute($create_table2);
  $dbi->include_model(
    MyModel1 => [
      $dbi->table1,
      $dbi->table2
    ]
  );
  $dbi->setup_model;
  is_deeply([sort @{$dbi->model($table1)->columns}], [$key1, $key2]);
  is_deeply([sort @{$dbi->model($table2)->columns}], [$key1, $key3]);
}

# each_column
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table ${q}table$p") };
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  eval { $dbi->execute("drop table $table3") };
  $dbi->execute($create_table1_type);
  $dbi->execute($create_table2);

  my $infos = [];
  $dbi->each_column(sub {
    my ($self, $table, $column, $cinfo) = @_;

    if ($table =~ /^table\d/i) {
       my $info = [$table, $column, $cinfo->{COLUMN_NAME}];
       push @$infos, $info;
    }
  });
  $infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
  is_deeply($infos,
    [
      [table_only($table1), $key1, $key1],
      [table_only($table1), $key2, $key2],
      [table_only($table2), $key1, $key1],
      [table_only($table2), $key3, $key3]
    ]

  );
}

# each_table
my $user_column_info;
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table2);
  $dbi->execute($create_table1_type);

  {
    my $infos = [];
    $dbi->each_table(sub {
      my ($self, $table, $table_info) = @_;

      if ($table =~ /^table\d/i) {
        my $info = [$table, $table_info->{TABLE_NAME}];
        push @$infos, $info;
      }
    });
    $infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
    is_deeply($infos,
      [
        [table_only($table1), table_only($table1)],
        [table_only($table2), table_only($table2)],
      ]
    );

    $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table2);
    $dbi->execute($create_table1_type);
  }

  {
    my $infos = [];
    $dbi->user_table_info($user_table_info);
    $dbi->each_table(sub {
      my ($self, $table, $table_info) = @_;

      if ($table =~ /^table\d/i) {
         my $info = [$table, $table_info->{TABLE_NAME}];
         push @$infos, $info;
      }
    });
    $infos = [sort { $a->[0] cmp $b->[0] || $a->[1] cmp $b->[1] } @$infos];
    is_deeply($infos,
      [
        [table_only($table1), table_only($table1)],
        [table_only($table2), table_only($table2)],
        [table_only($table3), table_only($table3)],
      ]
    );
  }
  $user_column_info = $dbi->get_column_info(exclude_table => $dbi->exclude_table);
}

# type_rule into
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
  }

  {
    my $dbi = DBIx::Custom->connect;
    $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);

    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { '2010-' . $_[0] }
      }
    );
    $dbi->insert({$key1 => '01-01'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    like($result->one->{$key1}, qr/^2010-01-01/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => [
         [$date_typename, $datetime_typename] => sub {
            my $value = shift;
            $value =~ s/02/03/g;
            return $value;
         }
      ]
    );
    $dbi->insert({$key1 => '2010-01-02', $key2 => '2010-01-01 01:01:02'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    my $row = $result->one;
    like($row->{$key1}, qr/^2010-01-03/);
    like($row->{$key2}, qr/^2010-01-01 01:01:03/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->insert({$key1 => '2010-01-03', $key2 => '2010-01-01 01:01:03'}, table => $table1);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => [
        [$date_typename, $datetime_typename] => sub {
          my $value = shift;
          $value =~ s/02/03/g;
          return $value;
        }
      ]
    );
    my $result = $dbi->execute(
      "select * from $table1 where $key1 = :$key1 and $key2 = :$table1.$key2",
      {$key1 => '2010-01-03', "$table1.$key2" => '2010-01-01 01:01:02'}
    );
    my $row = $result->one;
    like($row->{$key1}, qr/^2010-01-03/);
    like($row->{$key2}, qr/^2010-01-01 01:01:03/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->insert({$key1 => '2010-01-03', $key2 => '2010-01-01 01:01:03'}, table => $table1);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => [
        [$date_typename, $datetime_typename] => sub {
          my $value = shift;
          $value =~ s/02/03/g;
          return $value;
        }
      ]
    );
    my $result = $dbi->execute(
      "select * from $table1 where $key1 = :$key1 and $key2 = :$table1.$key2",
      {$key1 => '2010-01-02', "$table1.$key2" => '2010-01-01 01:01:02'},
      table => $table1
    );
    my $row = $result->one;
    like($row->{$key1}, qr/^2010-01-03/);
    like($row->{$key2}, qr/2010-01-01 01:01:03/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->register_filter(convert => sub {
      my $value = shift || '';
      $value =~ s/02/03/;
      return $value;
    });
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => 'convert',
      },
      into1 => {
          $date_typename => 'convert',
      }
    );
    $dbi->insert({$key1 => '2010-02-02'}, table => $table1);

    {
      my $result = $dbi->select(table => $table1);
      like($result->fetch->[0], qr/^2010-03-03/);
    }

    {
      my $result = $dbi->select(column => [$key1, $key1], table => $table1);
      my $row = $result->fetch;
      like($row->[0], qr/^2010-03-03/);
      like($row->[1], qr/^2010-03-03/);
    }
  }
}

# type_rule and filter order
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/4/5/; return $v }
      },
      into2 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/5/6/; return $v }
      },
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/6/7/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/7/8/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'},
    table => $table1, filter => {$key1 => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }});
    my $result = $dbi->select(table => $table1);
    $result->filter($key1 => sub { my $v = shift || ''; $v =~ s/8/9/; return $v });
    like($result->fetch_one->[0], qr/^2010-01-09/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/4/5/; return $v }
      },
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    $dbi->user_column_info($user_column_info);
    $result->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/6/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/6/8/; return $v }
      }
    );
    $result->filter($key1 => sub { my $v = shift || ''; $v =~ s/8/9/; return $v });
    like($result->fetch_one->[0], qr/^2010-01-09/);
  }
}

# type_rule_off
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
      },
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1, type_rule_off => 1);
    my $result = $dbi->select(table => $table1, type_rule_off => 1);
    like($result->type_rule_off->fetch->[0], qr/^2010-01-03/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1, type_rule_off => 1);
    my $result = $dbi->select(table => $table1, type_rule_off => 1);
    like($result->one->{$key1}, qr/^2010-01-04/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/4/5/; return $v }
      },
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    like($result->one->{$key1}, qr/^2010-01-05/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/4/5/; return $v }
      },
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    like($result->fetch->[0], qr/2010-01-05/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->register_filter(ppp => sub { my $v = shift || ''; $v =~ s/3/4/; return $v });
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => 'ppp'
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    like($result->one->{$key1}, qr/^2010-01-04/);

    eval{$dbi->type_rule(
      into1 => {
        $date_typename => 'pp'
      }
    )};
    like($@, qr/not registered/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    eval {
      $dbi->type_rule(
        from1 => {
          Date => sub { $_[0] * 2 },
        }
      );
    };
    like($@, qr/lower/);

    eval {
      $dbi->type_rule(
        into1 => {
          Date => sub { $_[0] * 2 },
        }
      );
    };
    like($@, qr/lower/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/4/5/; return $v }
      },
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    $result->type_rule_off;
    like($result->one->{$key1}, qr/^2010-01-04/);
  }

  {
    my $dbi = DBIx::Custom->connect;

    {
      eval { $dbi->execute("drop table $table1") };
      $dbi->execute($create_table1_type);
      $dbi->user_column_info($user_column_info);
      $dbi->type_rule(
        from1 => {
          $date_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v },
          $datetime_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
        },
      );
      $dbi->insert({$key1 => '2010-01-03', $key2 => '2010-01-01 01:01:03'}, table => $table1);
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => {
          $date_datatype => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
        }
      );
      my $row = $result->one;
      like($row->{$key1}, qr/^2010-01-05/);
      like($row->{$key2}, qr/^2010-01-01 01:01:03/);
    }

    {
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => {
          $date_datatype => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
        }
      );
      my $row = $result->one;
      like($row->{$key1}, qr/2010-01-05/);
      like($row->{$key2}, qr/2010-01-01 01:01:03/);
    }

    {
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => {
          $date_datatype => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
        }
      );
      my $row = $result->one;
      like($row->{$key1}, qr/2010-01-05/);
      like($row->{$key2}, qr/2010-01-01 01:01:03/);
    }

    {
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => [$date_datatype => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }]
      );
      my $row = $result->one;
      like($row->{$key1}, qr/2010-01-05/);
      like($row->{$key2}, qr/2010-01-01 01:01:03/);
    }

    {
      $dbi->register_filter(five => sub { my $v = shift || ''; $v =~ s/3/5/; return $v });
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => [$date_datatype => 'five']
      );
      my $row = $result->one;
      like($row->{$key1}, qr/^2010-01-05/);
      like($row->{$key2}, qr/^2010-01-01 01:01:03/);
    }

    {
      my $result = $dbi->select(table => $table1);
      $result->type_rule(
        from1 => [$date_datatype => undef]
      );
      my $row = $result->one;
      like($row->{$key1}, qr/^2010-01-03/);
      like($row->{$key2}, qr/^2010-01-01 01:01:03/);
    }
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v },
      },
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    $result->filter($key1 => sub { my $v = shift || ''; $v =~ s/4/5/; return $v });
    like($result->one->{$key1}, qr/^2010-01-05/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1);
    my $result = $dbi->select(table => $table1);
    $result->filter($key1 => sub { my $v = shift || ''; $v =~ s/4/5/; return $v });
    like($result->fetch->[0], qr/^2010-01-05/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
      into2 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
      },
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/3/6/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/(3|6)/7/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1, type_rule_off => 1);
    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule_off->fetch_one->[0], qr/^2010-01-03/);
    }
    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule_on->fetch_one->[0], qr/^2010-01-07/);
    }
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
      into2 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
      },
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/(3|5)/6/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/6/7/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1, type_rule1_off => 1);
    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule1_off->fetch_one->[0], qr/^2010-01-05/);
    }
    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule1_on->fetch_one->[0], qr/^2010-01-07/);
    }
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/5/; return $v }
      },
      into2 => {
        $date_typename => sub { my $v = shift || ''; $v =~ s/3/4/; return $v }
      },
      from1 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/5/6/; return $v }
      },
      from2 => {
        $date_datatype => sub { my $v = shift || ''; $v =~ s/(3|6)/7/; return $v }
      }
    );
    $dbi->insert({$key1 => '2010-01-03'}, table => $table1, type_rule2_off => 1);
    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule2_off->fetch_one->[0], qr/^2010-01-06/);
    }

    {
      my $result = $dbi->select(table => $table1);
      like($result->type_rule2_on->fetch_one->[0], qr/^2010-01-07/);
    }
  }
}

# join
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
    eval { $dbi->execute("drop table $table3") };
    $dbi->execute("create table $table3 ($key3 int, $key4 int)");
    $dbi->insert({$key3 => 5, $key4 => 4}, table => $table3);
    my $rows = $dbi->select(
      table => $table1,
      column => "$table1.$key1 as " . u("${table1}_$key1") . ", $table2.$key1 as " . u("${table2}_$key1") . ", $key2, $key3",
      where   => {"$table1.$key2" => 2},
      join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1"]
    )->all;
    is_deeply($rows, [{u"${table1}_$key1" => 1, u"${table2}_$key1" => 1, $key2 => 2, $key3 => 5}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
    eval { $dbi->execute("drop table $table3") };
    $dbi->execute("create table $table3 ($key3 int, $key4 int)");
    $dbi->insert({$key3 => 5, $key4 => 4}, table => $table3);
    {
      my $rows = $dbi->select(
        table => $table1,
        column => "$table1.$key1 as " . u("${table1}_$key1") . ", $table2.$key1 as " . u("${table2}_$key1") . ", $key2, $key3",
        where   => {"$table1.$key2" => 2},
        join  => {
          clause => "left outer join $table2 on $table1.$key1 = $table2.$key1",
          table => [$table1, $table2]
        }
      )->all;
      is_deeply($rows, [{u"${table1}_$key1" => 1, u"${table2}_$key1" => 1, $key2 => 2, $key3 => 5}]);
    }

    {
      my $rows = $dbi->select(
        table => $table1,
        where   => {$key1 => 1},
        join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1"]
      )->all;
      is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $rows = $dbi->select(
        table => $table1,
        where   => {$key1 => 1},
        join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1",
                  "left outer join $table3 on $table2.$key3 = $table3.$key3"]
      )->all;
      is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $rows = $dbi->select(
        column => "$table3.$key4 as " . u2("${table3}__$key4"),
        table => $table1,
        where   => {"$table1.$key1" => 1},
        join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1",
                  "left outer join $table3 on $table2.$key3 = $table3.$key3"]
      )->all;
      is_deeply($rows, [{u2"${table3}__$key4" => 4}]);
    }

    {
      my $rows = $dbi->select(
        column => "$table1.$key1 as " . u2("${table1}__$key1"),
        table => $table1,
        where   => {"$table3.$key4" => 4},
        join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1",
                  "left outer join $table3 on $table2.$key3 = $table3.$key3"]
      )->all;
      is_deeply($rows, [{u2"${table1}__$key1" => 1}]);
    }
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
    my $rows = $dbi->select(
      table => $table1,
      column => $dbi->_tq($table1) . ".${q}$key1$p as ${q}" . u("${table1}_$key1") . "$p, " . $dbi->_tq($table2) . ".${q}$key1$p as ${q}" . u("${table2}_$key1") . "$p, ${q}$key2$p, ${q}$key3$p",
      where   => {"$table1.$key2" => 2},
      join  => ["left outer join " . $dbi->_tq($table2) . " on " . $dbi->_tq($table1) . ".${q}$key1$p = " . $dbi->_tq($table2) . ".${q}$key1$p"],
    )->all;
    is_deeply($rows, [{u"${table1}_$key1" => 1, u"${table2}_$key1" => 1, $key2 => 2, $key3 => 5}],
      'quote');

    {
      my $dbi = DBIx::Custom->connect;
      eval { $dbi->execute("drop table $table1") };
      $dbi->execute($create_table1);
      $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
      my $sql = <<"EOS";
left outer join (
select * from $table1 t1
  where t1.$key2 = (
    select max(t2.$key2) from $table1 t2
    where t1.$key1 = t2.$key1
  )
) $table3 on $table1.$key1 = $table3.$key1
EOS
      $sql =~ s/\Q.table3/_table3/g;
      my $join = [$sql];
      my $rows = $dbi->select(
        table => $table1,
        column => u($table3) . ".$key1 as " . u2("${table3}__$key1"),
        join  => $join
      )->all;
      is_deeply($rows, [{u2"${table3}__$key1" => 1}]);
    }
  }
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 1, $key3 => 4}, table => $table2);
    $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
    {
      my $result = $dbi->select(
        table => $table1,
        join => [
          "left outer join $table2 on $table2.$key2 = '4' and $table1.$key1 = $table2.$key1"
        ]
      );
      is_deeply($result->all, [{$key1 => 1, $key2 => 2}]);
    }

    {
      my $result = $dbi->select(
        table => $table1,
        column => [{$table2 => [$key3]}],
        join => [
          "left outer join $table2 on $table2.$key3 = '4' and $table1.$key1 = $table2.$key1"
        ]
      );
      is_deeply($result->all, [{"$table2.$key3" => 4}]);
    }

    {
      my $result = $dbi->select(
        table => $table1,
        column => [{$table2 => [$key3]}],
        join => [
          "left outer join $table2 on $table1.$key1 = $table2.$key1 and $table2.$key3 = '4'"
        ]
      );
      is_deeply($result->all, [{"$table2.$key3" => 4}]);
    }
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 1, $key3 => 4}, table => $table2);
    $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
    my $result = $dbi->select(
      table => $table1,
      column => [{$table2 => [$key3]}],
      join => [
        {
          clause => "left outer join $table2 on $table2.$key3 = '4' and $table1.$key1 = $table2.$key1",
          table => [$table1, $table2]
        }
      ]
    );
    is_deeply($result->all, [{"$table2.$key3" => 4}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 1, $key3 => 4}, table => $table2);
    $dbi->insert({$key1 => 1, $key3 => 1}, table => $table2);
    my $result = $dbi->select(
      table => $table1,
      column => [{$table2 => [$key3]}],
      join => [
        "left outer join $table2 on $table1.$key1 = $table2.$key1 and $table2.$key3 > '3'"
      ]
    );
    is_deeply($result->all, [{"$table2.$key3" => 4}]);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
    $dbi->insert({$key1 => 1, $key3 => 4}, table => $table2);
    $dbi->insert({$key1 => 1, $key3 => 1}, table => $table2);
    my $result = $dbi->select(
      table => $table1,
      column => [{$table2 => [$key3]}],
      join => [
        "left outer join $table2 on $table2.$key3 > '3' and $table1.$key1 = $table2.$key1"
      ]
    );
    is_deeply($result->all, [{"$table2.$key3" => 4}]);
  }
}

# columns
{
  my $dbi = DBIx::Custom->connect;
  $dbi->include_model(
    MyModel1 => [
      $dbi->table1,
      $dbi->table2
    ]
  );
  my $model = $dbi->model($table1);
}

# count
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 1, $key2 => 3}, table => $table1);
  is($dbi->count(table => $table1), 2);
  is($dbi->count(table => $table1, where => {$key2 => 2}), 1);
  {
    my $model = $dbi->create_model(table => $table1);
    is($model->count, 2);
  }

  {
    eval { $dbi->execute("drop table $table1") };
    eval { $dbi->execute("drop table $table2") };
    $dbi->execute($create_table1);
    $dbi->execute($create_table2);
    my $model = $dbi->create_model(table => $table1, primary_key => $key1);
    $model->insert({$key1 => 1, $key2 => 2});
  }
  {
    my $model = $dbi->create_model(table => $table2, primary_key => $key1,
      join => ["left outer join $table1 on $table2.$key1 = $table1.$key1"]);
    $model->insert({$key1 => 1, $key3 => 3});
    is($model->count(id => 1), 1);
    is($model->count(where => {"$table2.$key3" => 3}), 1);
  }
}

# table_alias option
{
  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->insert({$key1 => '2010-01-01'}, table => $table1);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into1 => {
        $date_typename => sub { '2010-' . $_[0] }
      }
    );
    my $result = $dbi->execute(
      "select * from $table1 TABLE1_ALIAS where :TABLE1_ALIAS.${key1}{=}",
      {"TABLE1_ALIAS.${key1}" => '01-01'},
      table_alias => {TABLE1_ALIAS => $table1}
    );
    like($result->one->{$key1}, qr/^2010-01-01/);
  }

  {
    my $dbi = DBIx::Custom->connect;
    eval { $dbi->execute("drop table $table1") };
    $dbi->execute($create_table1_type);
    $dbi->insert({$key1 => '2010-01-01'}, table => $table1);
    $dbi->user_column_info($user_column_info);
    $dbi->type_rule(
      into2 => {
        $date_typename => sub { '2010-' . $_[0] }
      }
    );
    my $result = $dbi->execute(
      "select * from $table1 TABLE1_ALIAS where :TABLE1_ALIAS.${key1}{=}",
      {"TABLE1_ALIAS.${key1}" => '01-01'},
      table_alias => {TABLE1_ALIAS => $table1}
    );
    like($result->one->{$key1}, qr/^2010-01-01/);
  }
}

# DBIx::Custom::Where join
{
  my $dbi = DBIx::Custom->connect;
  eval { $dbi->execute("drop table $table1") };
  $dbi->execute($create_table1);
  $dbi->insert({$key1 => 1, $key2 => 2}, table => $table1);
  $dbi->insert({$key1 => 3, $key2 => 4}, table => $table1);
  eval { $dbi->execute("drop table $table2") };
  $dbi->execute($create_table2);
  $dbi->insert({$key1 => 1, $key3 => 5}, table => $table2);
  eval { $dbi->execute("drop table $table3") };
  $dbi->execute("create table $table3 ($key3 int, $key4 int)");
  $dbi->insert({$key3 => 5, $key4 => 4}, table => $table3);

  {
    my $where = $dbi->where;
    $where->param({$key1 => 1});
    $where->clause(":${key1}{=}");
    $where->join(["left outer join $table3 on $table2.$key3 = $table3.$key3"]);

    my $rows = $dbi->select(
      table => $table1,
      where   => $where,
      join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1"]
    )->all;
    is_deeply($rows, [{$key1 => 1, $key2 => 2}]);
  }
  {
    my $where = $dbi->where;
    $where->param({"$table1.$key1" => 1});
    $where->clause(":$table1.${key1}{=}");
    $where->join(["left outer join $table3 on $table2.$key3 = $table3.$key3"]);

    my $rows = $dbi->select(
      column => "$table3.$key4 as " . u2("${table3}__$key4"),
      table => $table1,
      where   => $where,
      join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1"]
    )->all;
    is_deeply($rows, [{u2"${table3}__$key4" => 4}]);
  }
  {
    my $where = $dbi->where;
    $where->param({"$table3.$key4" => 4});
    $where->clause(":$table3.${key4}{=}");
    $where->join(["left outer join $table3 on $table2.$key3 = $table3.$key3"]);

    my $rows = $dbi->select(
      column => "$table1.$key1 as " . u2("${table1}__$key1"),
      table => $table1,
      where   => $where,
      join  => ["left outer join $table2 on $table1.$key1 = $table2.$key1"]
    )->all;
    is_deeply($rows, [{u2"${table1}__$key1" => 1}]);
  }
}