1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use Test::More;
6
7use SQL::Abstract::Test import => [qw/is_same_sql_bind is_same_bind/];
8
9use SQL::Abstract;
10
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
58
59plan tests => (@data * 5  +  2);
60
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;
65
66for my $record (@data) {
67
68  my $values = [ map { $record->{$_} } sort keys %$record ];
69
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()' );
83
84  is ( scalar @h_bind, $numfields, 'Number of fields unchanged' );
85}
86
87# test values() with literal sql
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
94{
95  my $sql = SQL::Abstract->new;
96
97  my $data = { 
98    event => 'rapture',
99    stuff => 'fluff',
100    time => \ 'now()',
101    xfunc => \ 'xfunc(?)',
102    yfunc => ['yfunc(?)', 'ystuff' ],
103    zfunc => \['zfunc(?)', 'zstuff' ],
104    zzlast => 'zzstuff',
105  };
106
107  my ($stmt, @bind) = $sql->insert ('table', $data);
108
109  is_same_sql_bind (
110    $stmt,
111    \@bind,
112    'INSERT INTO table ( event, stuff, time, xfunc, yfunc, zfunc, zzlast) VALUES ( ?, ?, now(), xfunc (?), yfunc(?), zfunc(?), ? )',
113    [qw/rapture fluff ystuff zstuff zzstuff/],  # event < stuff
114  );
115
116  is_same_bind (
117    [$sql->values ($data)],
118    [@bind],
119    'values() output matches that of initial bind'
120  ) || diag "Corresponding SQL statement: $stmt";
121}
122