From Code to Community: Sponsoring The Perl and Raku Conference 2025 Learn more

YORHEL / SQL-Interp-1.27 / t / dbi.t 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118

              
# Tests of DBIx::Interp
use strict;
use lib 't/lib';
use DBD::Mock;
use Test::More 'no_plan';
use Data::Dumper;
use DBIx::Interp qw(:all);
use DBI qw(:sql_types);
my $dbh = DBI->connect('DBI:Mock:', '', '')
    or die "Cannot create handle: $DBI::errstr\n";
my $dbx = DBIx::Interp->new($dbh);
my @data1   = (['a', 'b'], ['c', 'd']);
my @result1 = (['color', 'size'], @data1);
my $x = 5;
my $y = 6;
# test of use parameter inheritance
BEGIN {
    use_ok('DBIx::Interp',
        'dbi_interp', 'sql_interp' ); # 0.3
}
# selectall_arrayref
$dbh->{mock_add_resultset} = \@result1;
is_deeply(
    $dbx->selectall_arrayref_i("SELECT * FROM mytable WHERE x IN", [1,2]),
    \@data1,
    'selectall_arrayref'
);
is($dbh->{mock_all_history}->[0]{statement},
   'SELECT * FROM mytable WHERE x IN (?, ?)');
is_deeply($dbh->{mock_all_history}->[0]{bound_params}, [1, 2]);
# prepare
my $stx = $dbx->prepare_i();
is(ref($stx), 'DBIx::Interp::STX');
# max_sths
$stx->max_sths(2);
is($stx->max_sths(), 2);
# execute
$dbh->{mock_clear_history} = 1;
$dbh->{mock_add_resultset} = \@result1;
$stx->execute_i('SELECT * FROM mytable WHERE y IN', [2,3]);
is_deeply(
    $stx->fetchall_arrayref(),
    \@data1,
    'fetchall_arrayref'
);
is($dbh->{mock_all_history}->[0]{statement},
   'SELECT * FROM mytable WHERE y IN (?, ?)');
is_deeply($dbh->{mock_all_history}->[0]{bound_params}, [2, 3]);
# execute (same SQL)
$dbh->{mock_clear_history} = 1;
$dbh->{mock_add_resultset} = \@result1;
$stx->execute_i('SELECT * FROM mytable WHERE y IN', [4,5]);
is_deeply(
    $stx->fetchall_arrayref(),
    \@data1,
    'fetchall_arrayref'
);
is($stx->sth()->{mock_statement},
   'SELECT * FROM mytable WHERE y IN (?, ?)');
is_deeply($stx->sth()->{mock_params}, [4, 5]);
# execute (new SQL)
$dbh->{mock_clear_history} = 1;
$dbh->{mock_add_resultset} = \@result1;
$stx->execute_i('SELECT * FROM mytable WHERE y IN', [4,5,6]);
is_deeply(
    $stx->fetchall_arrayref(),
    \@data1,
    'fetchall_arrayref'
);
is($stx->sth()->{mock_statement},
   'SELECT * FROM mytable WHERE y IN (?, ?, ?)');
is_deeply($stx->sth()->{mock_params}, [4, 5, 6]);
is(scalar(keys %{$stx->sths()}), 2, 'two sths in stx');
# execute (new SQL)
$dbh->{mock_clear_history} = 1;
$dbh->{mock_add_resultset} = \@result1;
$stx->execute_i('SELECT * FROM mytable WHERE y IN', [4,5,6,7]);
is_deeply(
    $stx->fetchall_arrayref(),
    \@data1,
    'fetchall_arrayref'
);
is($stx->sth()->{mock_statement},
   'SELECT * FROM mytable WHERE y IN (?, ?, ?, ?)');
is_deeply($stx->sth()->{mock_params}, [4, 5, 6, 7]);
is(scalar(keys %{$stx->sths()}), 2, 'two sths in stx still');
my $h2 = {a => 1, b => 2};
my $h2_keys = [sort keys %$h2];
my $h2_values = [map {$h2->{$_}} sort keys %$h2];
# bind_param
$dbh->{mock_clear_history} = 1;
$dbh->{mock_add_resultset} = \@result1;
$dbx->selectall_arrayref_i("SELECT * FROM mytable WHERE x=", \$x,
    "AND y=", sql_type(\$y, type => SQL_INTEGER),
    "AND", sql_type($h2, type => SQL_DATETIME),
    "AND x IN", sql_type([4, 5], type => SQL_VARCHAR)
);
is_deeply(
    $dbh->{mock_all_history}->[0]{statement},
    "SELECT * FROM mytable WHERE x= ? AND y= ? AND "."($h2_keys->[0]=? AND $h2_keys->[1]=?) AND x IN (?, ?)"
);
# note: DBD::Mock doesn't save bind param type to test?