use Test::More;

use strict;

use warnings;

use utf8;

use Encode qw/encode_utf8 decode_utf8/;

use Data::Dumper;

$SIG{__WARN__} = sub { warn $_[0] unless $_[0] =~ /DEPRECATED/};

BEGIN {

    eval { require DBD::SQLite; 1 }

        or plan skip_all => 'DBD::SQLite required';

    eval { DBD::SQLite->VERSION >= 1.25 }

        or plan skip_all => 'DBD::SQLite >= 1.25 required';

    plan 'no_plan';

    use_ok('DBIx::Custom');

}

use FindBin;

use lib "$FindBin::Bin/dbix-custom-core-sqlite";

# Function for test name

sub test { print "# $_[0]\n" }

# Constant varialbes for test

my $CREATE_TABLE = {

    0 => 'create table table1 (key1 char(255), key2 char(255));',

    1 => 'create table table1 (key1 char(255), key2 char(255), key3 char(255), key4 char(255), key5 char(255));',

    2 => 'create table table2 (key1 char(255), key3 char(255));',

    3 => 'create table table1 (key1 Date, key2 datetime);',

    4 => 'create table table3 (key3 int, key4 int);'

};

my $SELECT_SOURCES = {

    0 => 'select * from table1;'

};

my $DROP_TABLE = {

    0 => 'drop table table1'

};

my $NEW_ARGS = {

    0 => {dsn => 'dbi:SQLite:dbname=:memory:'}

};

# Variables

my $dbi;

my $sth;

my $source;

my @sources;

my $select_SOURCE;

my $insert_SOURCE;

my $update_SOURCE;

my $param;

my $params;

my $sql;

my $result;

my $row;

my @rows;

my $rows;

my $query;

my @queries;

my $select_query;

my $insert_query;

my $update_query;

my $ret_val;

my $infos;

my $model;

my $model2;

my $where;

my $update_param;

my $insert_param;

my $join;

# Prepare table

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

test 'DBIx::Custom::Result test';

$source = "select key1, key2 from table1";

$query = $dbi->create_query($source);

$result = $dbi->execute($query);

@rows = ();

while (my $row = $result->fetch) {

    push @rows, [@$row];

}

is_deeply(\@rows, [[1, 2], [3, 4]], "fetch");

$result = $dbi->execute($query);

@rows = ();

while (my $row = $result->fetch_hash) {

    push @rows, {%$row};

}

is_deeply(\@rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "fetch_hash");

$result = $dbi->execute($query);

$rows = $result->fetch_all;

is_deeply($rows, [[1, 2], [3, 4]], "fetch_all");

$result = $dbi->execute($query);

$rows = $result->fetch_hash_all;

is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "all");

test 'Insert query return value';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{0});

$source = "insert into table1 {insert_param key1 key2}";

$query = $dbi->execute($source, {}, query => 1);

$ret_val = $dbi->execute($query, param => {key1 => 1, key2 => 2});

ok($ret_val);

test 'Direct query';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{0});

$insert_SOURCE = "insert into table1 {insert_param key1 key2}";

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2}]);

test 'Filter basic';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice       => sub { $_[0] * 2}, 

                    three_times => sub { $_[0] * 3});

$insert_SOURCE  = "insert into table1 {insert_param key1 key2};";

$insert_query = $dbi->execute($insert_SOURCE, {}, query => 1);

$insert_query->filter({key1 => 'twice'});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows = $result->filter({key2 => 'three_times'})->all;

is_deeply($rows, [{key1 => 2, key2 => 6}], "filter fetch_filter");

$dbi->execute($DROP_TABLE->{0});

test 'Filter in';

$dbi->execute($CREATE_TABLE->{0});

$insert_SOURCE  = "insert into table1 {insert_param key1 key2};";

$insert_query = $dbi->execute($insert_SOURCE, {}, query => 1);

$dbi->execute($insert_query, param => {key1 => 2, key2 => 4});

$select_SOURCE = "select * from table1 where {in table1.key1 2} and {in table1.key2 2}";

$select_query = $dbi->execute($select_SOURCE,{}, query => 1);

$select_query->filter({'table1.key1' => 'twice'});

$result = $dbi->execute($select_query, param => {'table1.key1' => [1,5], 'table1.key2' => [2,4]});

$rows = $result->all;

is_deeply($rows, [{key1 => 2, key2 => 4}], "filter");

test 'DBIx::Custom::SQLTemplate basic tag';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";

$query = $dbi->execute($source, {}, query => 1);

$result = $dbi->execute($query, param => {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");

$source = "select * from table1 where key1 = :key1 and {<> key2} and {< key3} and {> key4} and {>= key5};";

$query = $dbi->execute($source, {}, query => 1);

$result = $dbi->execute($query, {key1 => 1, key2 => 3, key3 => 4, key4 => 3, key5 => 5});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag1");

$source = "select * from table1 where {<= key1} and {like key2};";

$query = $dbi->execute($source, {}, query => 1);

$result = $dbi->execute($query, param => {key1 => 1, key2 => '%2%'});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic tag2");

test 'DIB::Custom::SQLTemplate in tag';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$source = "select * from table1 where {in key1 2};";

$query = $dbi->execute($source, {}, query => 1);

$result = $dbi->execute($query, param => {key1 => [9, 1]});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");

test 'DBIx::Custom::SQLTemplate insert tag';

$dbi->execute("delete from table1");

$insert_SOURCE = 'insert into table1 {insert_param key1 key2 key3 key4 key5}';

$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}], "basic");

test 'DBIx::Custom::SQLTemplate update tag';

$dbi->execute("delete from table1");

$insert_SOURCE = "insert into table1 {insert_param key1 key2 key3 key4 key5}";

$dbi->execute($insert_SOURCE, param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->execute($insert_SOURCE, param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$update_SOURCE = 'update table1 {update_param key1 key2 key3 key4} where {= key5}';

$dbi->execute($update_SOURCE, param => {key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5});

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 1, key3 => 1, key4 => 1, key5 => 5},

                  {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10}], "basic");

test 'parameter';

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$source = "select * from table1 where key1 = :key1 and key2 = :key2";

$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);

$source = "select * from table1 where key1 = \n:key1\n and key2 = :key2";

$result = $dbi->execute($source, param => {key1 => 1, key2 => 2});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);

$source = "select * from table1 where key1 = :key1 or key1 = :key1";

$result = $dbi->execute($source, param => {key1 => [1, 2]});

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);

$source = "select * from table1 where key1 = :table1.key1 and key2 = :table1.key2";

$result = $dbi->execute(

    $source,

    param => {'table1.key1' => 1, 'table1.key2' => 1},

    filter => {'table1.key2' => sub { $_[0] * 2 }}

);

$rows = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5}]);

test 'Error case';

eval {DBIx::Custom->connect(dsn => 'dbi:SQLit')};

ok($@, "connect error");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

eval{$dbi->execute("{p }", {}, query => 1)};

ok($@, "create_query invalid SQL template");

test 'insert';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");

$dbi->execute('delete from table1');

$dbi->register_filter(

    twice       => sub { $_[0] * 2 },

    three_times => sub { $_[0] * 3 }

);

$dbi->default_bind_filter('twice');

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => 'three_times'});

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 3, key2 => 4}], "filter");

$dbi->default_bind_filter(undef);

$dbi->execute($DROP_TABLE->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$rows = $dbi->select(table => 'table1')->all;

is_deeply($rows, [{key1 => 1, key2 => 2}], 'insert append');

eval{$dbi->insert(table => 'table1', noexist => 1)};

like($@, qr/noexist/, "invalid");

eval{$dbi->insert(table => 'table', param => {';' => 1})};

like($@, qr/safety/);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->quote('"');

$dbi->execute('create table "table" ("select")');

$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});

$dbi->insert(table => 'table', param => {select => 1});

$result = $dbi->execute('select * from "table"');

$rows   = $result->all;

is_deeply($rows, [{select => 2}], "reserved word");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 2}, {key1 => 3, key2 => 4}], "basic");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");

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

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4}, prefix => 'or replace');

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");

test 'update';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$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(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$dbi->update(table => 'table1', param => {key2 => 12}, where => {key2 => 2, key3 => 3});

$result = $dbi->execute($SELECT_SOURCES->{0});

$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(table => 'table1', param => {key2 => [12]}, where => {key2 => 2, key3 => 3});

$result = $dbi->execute($SELECT_SOURCES->{0});

$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(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->update(table => 'table1', param => {key2 => 11}, where => {key1 => 1},

              filter => {key2 => sub { $_[0] * 2 }});

$result = $dbi->execute($SELECT_SOURCES->{0});

$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");

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

eval{$dbi->update(table => 'table1', noexist => 1)};

like($@, qr/noexist/, "invalid");

eval{$dbi->update(table => 'table1')};

like($@, qr/where/, "not contain where");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$where = $dbi->where;

$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);

$where->param({key1 => 1, key2 => 2});

$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);

$result = $dbi->select(table => 'table1');

is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->update(

    table => 'table1',

    param => {key1 => 3},

    where => [

        ['and', 'key1 = :key1', 'key2 = :key2'],

        {key1 => 1, key2 => 2}

    ]

);

$result = $dbi->select(table => 'table1');

is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$where = $dbi->where;

$where->clause(['and', 'key2 = :key2']);

$where->param({key2 => 2});

$dbi->update(table => 'table1', param => {key1 => 3}, where => $where);

$result = $dbi->select(table => 'table1');

is_deeply($result->all, [{key1 => 3, key2 => 2}], 'update() where');

eval{$dbi->update(table => 'table1', param => {';' => 1})};

like($@, qr/safety/);

eval{$dbi->update(table => 'table1', param => {'key1' => 1}, where => {';' => 1})};

like($@, qr/safety/);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->quote('"');

$dbi->execute('create table "table" ("select", "update")');

$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});

$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});

$dbi->insert(table => 'table', param => {select => 1});

$dbi->update(table => 'table', where => {select => 1}, param => {update => 2});

$result = $dbi->execute('select * from "table"');

$rows   = $result->all;

is_deeply($rows, [{select => 2, update => 6}], "reserved word");

eval {$dbi->update_all(table => 'table', param => {';' => 2}) };

like($@, qr/safety/);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->reserved_word_quote('"');

$dbi->execute('create table "table" ("select", "update")');

$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});

$dbi->apply_filter('table', update => {out => sub { $_[0] * 3}});

$dbi->insert(table => 'table', param => {select => 1});

$dbi->update(table => 'table', where => {'table.select' => 1}, param => {update => 2});

$result = $dbi->execute('select * from "table"');

$rows   = $result->all;

is_deeply($rows, [{select => 2, update => 6}], "reserved word");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$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 = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");

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

$dbi->update(table => 'table1', param => {key2 => 4},

  where => {key1 => 1}, prefix => 'or replace');

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 1, key2 => 4}], "basic");

test 'update_all';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{1});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2, key3 => 3, key4 => 4, key5 => 5});

$dbi->insert(table => 'table1', param => {key1 => 6, key2 => 7, key3 => 8, key4 => 9, key5 => 10});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->update_all(table => 'table1', param => {key2 => 10}, filter => {key2 => 'twice'});

$result = $dbi->execute($SELECT_SOURCES->{0});

$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");

test 'delete';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [{key1 => 3, key2 => 4}], "basic");

$dbi->execute("delete from table1;");

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->delete(table => 'table1', where => {key2 => 1}, filter => {key2 => 'twice'});

$result = $dbi->execute($SELECT_SOURCES->{0});

$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(table => 'table1', param => {key1 => 1, key2 => 2});

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

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

$rows = $dbi->select(table => 'table1')->all;

is_deeply($rows, [{key1 => 3, key2 => 4}], "delete multi key");

eval{$dbi->delete(table => 'table1', noexist => 1)};

like($@, qr/noexist/, "invalid");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$where = $dbi->where;

$where->clause(['and', 'key1 = :key1', 'key2 = :key2']);

$where->param({ke1 => 1, key2 => 2});

$dbi->delete(table => 'table1', where => $where);

$result = $dbi->select(table => 'table1');

is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$dbi->delete(

    table => 'table1',

    where => [

        ['and', 'key1 = :key1', 'key2 = :key2'],

        {ke1 => 1, key2 => 2}

    ]

);

$result = $dbi->select(table => 'table1');

is_deeply($result->all, [{key1 => 3, key2 => 4}], 'delete() where');

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute("create table table1 (key1 char(255), key2 char(255), primary key(key1))");

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

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [], "basic");

test 'delete error';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

eval{$dbi->delete(table => 'table1')};

like($@, qr/"where" must be specified/,

         "where key-value pairs not specified");

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

like($@, qr/safety/);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->quote('"');

$dbi->execute('create table "table" ("select", "update")');

$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});

$dbi->insert(table => 'table', param => {select => 1});

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

$result = $dbi->execute('select * from "table"');

$rows   = $result->all;

is_deeply($rows, [], "reserved word");

test 'delete_all';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$dbi->delete_all(table => 'table1');

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [], "basic");

test 'select';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

$rows = $dbi->select(table => 'table1')->all;

is_deeply($rows, [{key1 => 1, key2 => 2},

                  {key1 => 3, key2 => 4}], "table");

$rows = $dbi->select(table => 'table1', column => ['key1'])->all;

is_deeply($rows, [{key1 => 1}, {key1 => 3}], "table and columns and where key");

$rows = $dbi->select(table => 'table1', where => {key1 => 1})->all;

is_deeply($rows, [{key1 => 1, key2 => 2}], "table and columns and where key");

$rows = $dbi->select(table => 'table1', column => ['key1'], where => {key1 => 3})->all;

is_deeply($rows, [{key1 => 3}], "table and columns and where key");

$rows = $dbi->select(table => 'table1', append => "order by key1 desc limit 1")->all;

is_deeply($rows, [{key1 => 3, key2 => 4}], "append statement");

$dbi->register_filter(decrement => sub { $_[0] - 1 });

$rows = $dbi->select(table => 'table1', where => {key1 => 2}, filter => {key1 => 'decrement'})

            ->all;

is_deeply($rows, [{key1 => 1, key2 => 2}], "filter");

$dbi->execute($CREATE_TABLE->{2});

$dbi->insert(table => 'table2', param => {key1 => 1, key3 => 5});

$rows = $dbi->select(

    table => [qw/table1 table2/],

    column => 'table1.key1 as table1_key1, table2.key1 as table2_key1, key2, key3',

    where   => {'table1.key2' => 2},

    relation  => {'table1.key1' => 'table2.key1'}

)->all;

is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : exists where");

$rows = $dbi->select(

    table => [qw/table1 table2/],

    column => ['table1.key1 as table1_key1', 'table2.key1 as table2_key1', 'key2', 'key3'],

    relation  => {'table1.key1' => 'table2.key1'}

)->all;

is_deeply($rows, [{table1_key1 => 1, table2_key1 => 1, key2 => 2, key3 => 5}], "relation : no exists where");

eval{$dbi->select(table => 'table1', noexist => 1)};

like($@, qr/noexist/, "invalid");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->quote('"');

$dbi->execute('create table "table" ("select", "update")');

$dbi->apply_filter('table', select => {out => sub { $_[0] * 2}});

$dbi->insert(table => 'table', param => {select => 1, update => 2});

$result = $dbi->select(table => 'table', where => {select => 1});

$rows   = $result->all;

is_deeply($rows, [{select => 2, update => 2}], "reserved word");

test 'fetch filter';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->register_filter(

    twice       => sub { $_[0] * 2 },

    three_times => sub { $_[0] * 3 }

);

$dbi->default_fetch_filter('twice');

$dbi->execute($CREATE_TABLE->{0});

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

$result = $dbi->select(table => 'table1');

$result->filter({key1 => 'three_times'});

$row = $result->one;

is_deeply($row, {key1 => 3, key2 => 4}, "default_fetch_filter and filter");

test 'filters';

$dbi = DBIx::Custom->new;

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

   'あ', "decode_utf8");

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

   encode_utf8('あ'), "encode_utf8");

test 'transaction';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->dbh->begin_work;

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

$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 3});

$dbi->dbh->commit;

$result = $dbi->select(table => 'table1');

is_deeply(scalar $result->all, [{key1 => 1, key2 => 2}, {key1 => 2, key2 => 3}],

          "commit");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->dbh->begin_work(0);

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

$dbi->dbh->rollback;

$result = $dbi->select(table => 'table1');

ok(! $result->fetch_first, "rollback");

test 'cache';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->cache(1);

$dbi->execute($CREATE_TABLE->{0});

$source = 'select * from table1 where key1 = :key1 and key2 = :key2;';

$dbi->execute($source, {}, query => 1);

is_deeply($dbi->{_cached}->{$source}, 

          {sql => "select * from table1 where key1 = ? and key2 = ?;", columns => ['key1', 'key2'], tables => []}, "cache");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->{_cached} = {};

$dbi->cache(0);

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

is(scalar keys %{$dbi->{_cached}}, 0, 'not cache');

test 'execute';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

{

    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");

}

eval{$dbi->execute('select * from table1', no_exists => 1)};

like($@, qr/wrong/, "invald SQL");

$query = $dbi->execute('select * from table1 where key1 = :key1', {}, query => 1);

$dbi->dbh->disconnect;

eval{$dbi->execute($query, param => {key1 => {a => 1}})};

ok($@, "execute fail");

{

    local $Carp::Verbose = 0;

    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};

    like($@, qr/\Q.t /, "caller spec : not vebose");

}

{

    local $Carp::Verbose = 1;

    eval{$dbi->execute('select * from table1 where {0 key1}', {}, query => 1)};

    like($@, qr/QueryBuilder.*\.t /s, "caller spec : not vebose");

}

test 'transaction';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->begin_work;

eval {

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

    die "Error";

    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

};

$dbi->rollback if $@;

$result = $dbi->select(table => 'table1');

$rows = $result->all;

is_deeply($rows, [], "rollback");

$dbi->begin_work;

eval {

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

    $dbi->insert(table => 'table1', param => {key1 => 3, key2 => 4});

};

$dbi->commit unless $@;

$result = $dbi->select(table => 'table1');

$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;

test 'method';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->method(

    one => sub { 1 }

);

$dbi->method(

    two => sub { 2 }

);

$dbi->method({

    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");

test 'out filter';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->register_filter(three_times => sub { $_[0] * 3});

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 

              'key2' => {out => 'three_times', in => 'twice'});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$row   = $result->fetch_hash_first;

is_deeply($row, {key1 => 2, key2 => 6}, "insert");

$result = $dbi->select(table => 'table1');

$row   = $result->one;

is_deeply($row, {key1 => 6, key2 => 12}, "insert");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->register_filter(three_times => sub { $_[0] * 3});

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'three_times'}, 

              'key2' => {out => 'three_times', in => 'twice'});

$dbi->apply_filter(

    'table1', 'key1' => {out => undef}

); 

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$row   = $result->one;

is_deeply($row, {key1 => 1, key2 => 6}, "insert");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'twice'}

);

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 2}, filter => {key1 => undef});

$dbi->update(table => 'table1', param => {key1 => 2}, where => {key2 => 2});

$result = $dbi->execute($SELECT_SOURCES->{0});

$row   = $result->one;

is_deeply($row, {key1 => 4, key2 => 2}, "update");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'twice'}

);

$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1=> undef});

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

$result = $dbi->execute($SELECT_SOURCES->{0});

$rows   = $result->all;

is_deeply($rows, [], "delete");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'twice'}

);

$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});

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

$result->filter({'key2' => 'twice'});

$rows   = $result->all;

is_deeply($rows, [{key1 => 4, key2 => 4}], "select");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'twice'}

);

$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});

$result = $dbi->execute("select * from table1 where key1 = :key1 and key2 = :key2;",

                        param => {key1 => 1, key2 => 2},

                        table => ['table1']);

$rows   = $result->all;

is_deeply($rows, [{key1 => 4, key2 => 2}], "execute");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->apply_filter(

    'table1', 'key1' => {out => 'twice', in => 'twice'}

);

$dbi->insert(table => 'table1', param => {key1 => 2, key2 => 2}, filter => {key1 => undef});

$result = $dbi->execute("select * from {table table1} where key1 = :key1 and key2 = :key2;",

                        param => {key1 => 1, key2 => 2});

$rows   = $result->all;

is_deeply($rows, [{key1 => 4, key2 => 2}], "execute table tag");

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

$dbi->execute($CREATE_TABLE->{2});

$dbi->register_filter(twice => sub { $_[0] * 2 });

$dbi->register_filter(three_times => sub { $_[0] * 3 });

$dbi->apply_filter(

    'table1', 'key2' => {out => 'twice', in => 'twice'}

);

$dbi->apply_filter(

    'table2', 'key3' => {out => 'three_times', in => 'three_times'}

);

$dbi->insert(table => 'table1', param => {key1 => 5, key2 => 2}, filter => {key2 => undef});

$dbi->insert(table => 'table2', param => {key1 => 5, key3 => 6}, filter => {key3 => undef});

$result = $dbi->select(

     table => ['table1', 'table2'],

     column => ['key2', 'key3'],

     where => {'table1.key2' => 1, 'table2.key3' => 2}, relation => {'table1.key1' => 'table2.key1'});

$result->filter({'key2' => 'twice'});

$rows   = $result->all;

is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join");

$result = $dbi->select(

     table => ['table1', 'table2'],

     column => ['key2', 'key3'],

     where => {'key2' => 1, 'key3' => 2}, relation => {'table1.key1' => 'table2.key1'});

$result->filter({'key2' => 'twice'});

$rows   = $result->all;

is_deeply($rows, [{key2 => 4, key3 => 18}], "select : join : omit");

test 'each_column';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{2});

$dbi->execute($CREATE_TABLE->{3});

$infos = [];

$dbi->each_column(sub {

    my ($self, $table, $column, $cinfo) = @_;

    if ($table =~ /^table/) {

         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, 

    [

        ['table1', 'key1', 'key1'],

        ['table1', 'key2', 'key2'],

        ['table2', 'key1', 'key1'],

        ['table2', 'key3', 'key3']

    ]

);

test 'each_table';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{2});

$dbi->execute($CREATE_TABLE->{3});

$infos = [];

$dbi->each_table(sub {

    my ($self, $table, $table_info) = @_;

    if ($table =~ /^table/) {

         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, 

    [

        ['table1', 'table1'],

        ['table2', 'table2'],

    ]

);

test 'limit';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 4});

$dbi->insert(table => 'table1', param => {key1 => 1, key2 => 6});

$dbi->register_tag(

    limit => sub {

        my ($count, $offset) = @_;

        my $s = '';

        $s .= "limit $count";

        $s .= " offset $offset" if defined $offset;

        return [$s, []];

    }

);

$rows = $dbi->select(

  table => 'table1',

  where => {key1 => 1},

  append => "order by key2 {limit 1 0}"

)->all;

is_deeply($rows, [{key1 => 1, key2 => 2}]);

$rows = $dbi->select(

  table => 'table1',

  where => {key1 => 1},

  append => "order by key2 {limit 2 1}"

)->all;

is_deeply($rows, [{key1 => 1, key2 => 4},{key1 => 1, key2 => 6}]);

$rows = $dbi->select(

  table => 'table1',

  where => {key1 => 1},

  append => "order by key2 {limit 1}"

)->all;

is_deeply($rows, [{key1 => 1, key2 => 2}]);

test 'connect super';

{

    package MyDBI;

    use base 'DBIx::Custom';

    sub connect {

        my $self = shift->SUPER::connect(@_);

        return $self;

    }

    sub new {

        my $self = shift->SUPER::new(@_);

        return $self;

    }

}

$dbi = MyDBI->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

is($dbi->select(table => 'table1')->one->{key1}, 1);

$dbi = MyDBI->new($NEW_ARGS->{0});

$dbi->connect;

$dbi->execute($CREATE_TABLE->{0});

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

is($dbi->select(table => 'table1')->one->{key1}, 1);

{

    package MyDBI2;

    use base 'DBIx::Custom';

    sub connect {

        my $self = shift->SUPER::new(@_);

        $self->connect;

        return $self;

    }

}

$dbi = MyDBI->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

is($dbi->select(table => 'table1')->one->{key1}, 1);

test 'end_filter';

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$result = $dbi->select(table => 'table1');

$result->filter(key1 => sub { $_[0] * 2 }, key2 => sub { $_[0] * 4 });

$result->end_filter(key1 => sub { $_[0] * 3 }, key2 => sub { $_[0] * 5 });

$row = $result->fetch_first;

is_deeply($row, [6, 40]);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$result = $dbi->select(table => 'table1');

$result->filter([qw/key1 key2/] => sub { $_[0] * 2 });

$result->end_filter([[qw/key1 key2/] => sub { $_[0] * 3 }]);

$row = $result->fetch_first;

is_deeply($row, [6, 12]);

$dbi = DBIx::Custom->connect($NEW_ARGS->{0});

$dbi->execute($CREATE_TABLE->{0});

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

$result = $dbi->select(table => 'table1');

$result->filter([[qw/key1 key2/] => sub { $_[0] * 2 }]);

$result->end_filter([qw/key1 key2/] => sub { $_[0] * 3 });

$row = $result->fetch_first;

is_deeply($row, [6, 12]);