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