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 | |
35d55ea7 |
58 | # test insert() and values() for reentrancy |
59 | my($insert_hash, $insert_array, $numfields); |
60 | my $a_sql = SQL::Abstract->new; |
61 | my $h_sql = SQL::Abstract->new; |
32eab2da |
62 | |
35d55ea7 |
63 | for my $record (@data) { |
32eab2da |
64 | |
35d55ea7 |
65 | my $values = [ map { $record->{$_} } sort keys %$record ]; |
32eab2da |
66 | |
35d55ea7 |
67 | my ($h_stmt, @h_bind) = $h_sql->insert('h_table', $record); |
68 | my ($a_stmt, @a_bind) = $a_sql->insert('a_table', $values ); |
69 | |
70 | # init from first run, should not change afterwards |
71 | $insert_hash ||= $h_stmt; |
72 | $insert_array ||= $a_stmt; |
73 | $numfields ||= @$values; |
74 | |
75 | is ( $a_stmt, $insert_array, 'Array-based insert statement unchanged' ); |
76 | is ( $h_stmt, $insert_hash, 'Hash-based insert statement unchanged' ); |
77 | |
78 | is_deeply ( \@a_bind, \@h_bind, 'Bind values match after both insert() calls' ); |
79 | is_deeply ( [$h_sql->values ($record)] , \@h_bind, 'values() output matches bind values after insert()' ); |
32eab2da |
80 | |
35d55ea7 |
81 | is ( scalar @h_bind, $numfields, 'Number of fields unchanged' ); |
32eab2da |
82 | } |
83 | |
35d55ea7 |
84 | # test values() with literal sql |
397986c4 |
85 | # |
86 | # NOTE: |
87 | # The example is deliberately complicated by the addition of a literal ? in xfunc |
88 | # This is an intentional test making sure literal ? remains untouched. |
89 | # It is rather impractical in the field, as the user will have to insert |
90 | # a bindvalue for the literal position(s) in the correct offset of \@bind |
35d55ea7 |
91 | { |
92 | my $sql = SQL::Abstract->new; |
93 | |
c59982bb |
94 | my $data = { |
95 | event => 'rapture', |
96 | stuff => 'fluff', |
97 | time => \ 'now()', |
98 | xfunc => \ 'xfunc(?)', |
99 | yfunc => ['yfunc(?)', 'ystuff' ], |
100 | zfunc => \['zfunc(?)', 'zstuff' ], |
101 | zzlast => 'zzstuff', |
102 | }; |
35d55ea7 |
103 | |
104 | my ($stmt, @bind) = $sql->insert ('table', $data); |
105 | |
106 | is_same_sql_bind ( |
107 | $stmt, |
108 | \@bind, |
c59982bb |
109 | 'INSERT INTO table ( event, stuff, time, xfunc, yfunc, zfunc, zzlast) VALUES ( ?, ?, now(), xfunc (?), yfunc(?), zfunc(?), ? )', |
110 | [qw/rapture fluff ystuff zstuff zzstuff/], # event < stuff |
35d55ea7 |
111 | ); |
112 | |
113 | is_same_bind ( |
114 | [$sql->values ($data)], |
bab725ce |
115 | [@bind], |
35d55ea7 |
116 | 'values() output matches that of initial bind' |
c59982bb |
117 | ) || diag "Corresponding SQL statement: $stmt"; |
35d55ea7 |
118 | } |
10e6c946 |
119 | |
120 | done_testing; |