Commit | Line | Data |
41751122 |
1 | #!/usr/bin/perl |
32eab2da |
2 | |
3 | use strict; |
41751122 |
4 | use warnings; |
5 | use Test::More; |
32eab2da |
6 | |
35d55ea7 |
7 | use SQL::Abstract::Test import => [qw/is_same_sql_bind is_same_bind/]; |
32eab2da |
8 | |
9 | use SQL::Abstract; |
10 | |
32eab2da |
11 | my @data = ( |
12 | { |
13 | user => 'nwiger', |
14 | name => 'Nathan Wiger', |
15 | phone => '123-456-7890', |
16 | addr => 'Yeah, right', |
17 | city => 'Milwalkee', |
18 | state => 'Minnesota', |
19 | }, |
20 | |
21 | { |
22 | user => 'jimbo', |
23 | name => 'Jimbo Bobson', |
24 | phone => '321-456-0987', |
25 | addr => 'Yo Momma', |
26 | city => 'Yo City', |
27 | state => 'Minnesota', |
28 | }, |
29 | |
30 | { |
31 | user => 'mr.hat', |
32 | name => 'Mr. Garrison', |
33 | phone => '123-456-7890', |
34 | addr => undef, |
35 | city => 'South Park', |
36 | state => 'CO', |
37 | }, |
38 | |
39 | { |
40 | user => 'kennyg', |
41 | name => undef, |
42 | phone => '1-800-Sucky-Sucky', |
43 | addr => 'Mr. Garrison', |
44 | city => undef, |
45 | state => 'CO', |
46 | }, |
47 | |
48 | { |
49 | user => 'barbara_streisand', |
50 | name => 'MechaStreisand!', |
51 | phone => 0, |
52 | addr => -9230992340, |
53 | city => 42, |
54 | state => 'CO', |
55 | }, |
56 | ); |
57 | |
6dcf723c |
58 | |
35d55ea7 |
59 | plan tests => (@data * 5 + 2); |
6dcf723c |
60 | |
35d55ea7 |
61 | # test insert() and values() for reentrancy |
62 | my($insert_hash, $insert_array, $numfields); |
63 | my $a_sql = SQL::Abstract->new; |
64 | my $h_sql = SQL::Abstract->new; |
32eab2da |
65 | |
35d55ea7 |
66 | for my $record (@data) { |
32eab2da |
67 | |
35d55ea7 |
68 | my $values = [ map { $record->{$_} } sort keys %$record ]; |
32eab2da |
69 | |
35d55ea7 |
70 | my ($h_stmt, @h_bind) = $h_sql->insert('h_table', $record); |
71 | my ($a_stmt, @a_bind) = $a_sql->insert('a_table', $values ); |
72 | |
73 | # init from first run, should not change afterwards |
74 | $insert_hash ||= $h_stmt; |
75 | $insert_array ||= $a_stmt; |
76 | $numfields ||= @$values; |
77 | |
78 | is ( $a_stmt, $insert_array, 'Array-based insert statement unchanged' ); |
79 | is ( $h_stmt, $insert_hash, 'Hash-based insert statement unchanged' ); |
80 | |
81 | is_deeply ( \@a_bind, \@h_bind, 'Bind values match after both insert() calls' ); |
82 | is_deeply ( [$h_sql->values ($record)] , \@h_bind, 'values() output matches bind values after insert()' ); |
32eab2da |
83 | |
35d55ea7 |
84 | is ( scalar @h_bind, $numfields, 'Number of fields unchanged' ); |
32eab2da |
85 | } |
86 | |
35d55ea7 |
87 | # test values() with literal sql |
397986c4 |
88 | # |
89 | # NOTE: |
90 | # The example is deliberately complicated by the addition of a literal ? in xfunc |
91 | # This is an intentional test making sure literal ? remains untouched. |
92 | # It is rather impractical in the field, as the user will have to insert |
93 | # a bindvalue for the literal position(s) in the correct offset of \@bind |
35d55ea7 |
94 | { |
95 | my $sql = SQL::Abstract->new; |
96 | |
397986c4 |
97 | my $data = { event => 'rapture', time => \ 'now()', xfunc => \ 'somefunc(?)', stuff => 'fluff', }; |
35d55ea7 |
98 | |
99 | my ($stmt, @bind) = $sql->insert ('table', $data); |
100 | |
101 | is_same_sql_bind ( |
102 | $stmt, |
103 | \@bind, |
397986c4 |
104 | 'INSERT INTO table ( event, stuff, time, xfunc) VALUES ( ?, ?, now(), somefunc (?) )', |
35d55ea7 |
105 | [qw/rapture fluff/], # event < stuff |
106 | ); |
107 | |
108 | is_same_bind ( |
109 | [$sql->values ($data)], |
bab725ce |
110 | [@bind], |
35d55ea7 |
111 | 'values() output matches that of initial bind' |
474e3335 |
112 | );# || diag "Corresponding SQL statement: $stmt"; |
35d55ea7 |
113 | } |