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