Fixed interjecting arrayrefref into a where clause
[dbsrgits/SQL-Abstract.git] / t / 03values.t
CommitLineData
41751122 1#!/usr/bin/perl
32eab2da 2
3use strict;
41751122 4use warnings;
5use Test::More;
32eab2da 6
35d55ea7 7use SQL::Abstract::Test import => [qw/is_same_sql_bind is_same_bind/];
32eab2da 8
9use SQL::Abstract;
10
32eab2da 11my @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 59plan tests => (@data * 5 + 2);
6dcf723c 60
35d55ea7 61# test insert() and values() for reentrancy
62my($insert_hash, $insert_array, $numfields);
63my $a_sql = SQL::Abstract->new;
64my $h_sql = SQL::Abstract->new;
32eab2da 65
35d55ea7 66for 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}