Commit | Line | Data |
28f137a0 |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use vars qw($TESTING); |
5 | $TESTING = 1; |
6 | use Test; |
7 | |
8 | # use a BEGIN block so we print our plan before SQL::Abstract is loaded |
9 | BEGIN { plan tests => 4 } |
10 | |
11 | use SQL::Abstract; |
12 | |
13 | sub is { |
14 | my ($got, $expect, $msg) = @_; |
15 | ok($got eq $expect) or |
16 | warn "got [${got}]\ninstead of [${expect}]\nfor test ${msg}\n\n"; |
17 | } |
18 | |
19 | my $sql_maker = SQL::Abstract->new; |
20 | |
21 | $sql_maker->{quote_char} = '`'; |
22 | $sql_maker->{name_sep} = '.'; |
23 | |
24 | my ($sql,) = $sql_maker->select( |
25 | [ |
26 | { |
27 | 'me' => 'cd' |
28 | }, |
29 | [ |
30 | { |
31 | 'artist' => 'artist', |
32 | '-join_type' => '' |
33 | }, |
34 | { |
35 | 'artist.artistid' => 'me.artist' |
36 | } |
37 | ] |
38 | ], |
39 | [ |
40 | #{ |
41 | # 'count' => '*' |
42 | #} |
43 | \'COUNT( * )' |
44 | ], |
45 | { |
46 | 'artist.name' => 'Caterwauler McCrae', |
47 | 'me.year' => 2001 |
48 | }, |
49 | [], |
50 | undef, |
51 | undef |
52 | ); |
53 | |
54 | is($sql, |
55 | q/SELECT COUNT( * ) FROM `cd` `me` JOIN `artist` `artist` ON ( `artist`.`artistid` = `me`.`artist` ) WHERE ( `artist`.`name` = ? AND `me`.`year` = ? )/, |
56 | 'got correct SQL for count query with quoting'); |
57 | |
58 | ($sql,) = $sql_maker->select( |
59 | [ |
60 | { |
61 | 'me' => 'cd' |
62 | } |
63 | ], |
64 | [ |
65 | 'me.cdid', |
66 | 'me.artist', |
67 | 'me.title', |
68 | 'me.year' |
69 | ], |
70 | undef, |
71 | [ |
72 | 'year DESC' |
73 | ], |
74 | undef, |
75 | undef |
76 | ); |
77 | |
78 | #TODO: { |
79 | # local $TODO = "order_by with quoting needs fixing (ash/castaway)"; |
80 | # |
81 | # is($sql, |
82 | # q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, |
83 | # 'quoted ORDER BY with DESC okay'); |
84 | #} |
85 | |
86 | ($sql,) = $sql_maker->select( |
87 | [ |
88 | { |
89 | 'me' => 'cd' |
90 | } |
91 | ], |
92 | [ |
93 | 'me.*' |
94 | ], |
95 | undef, |
96 | [], |
97 | undef, |
98 | undef |
99 | ); |
100 | |
101 | is($sql, q/SELECT `me`.* FROM `cd` `me`/, 'select attr with me.* is right'); |
102 | |
103 | ($sql,) = $sql_maker->select( |
104 | [ |
105 | { |
106 | 'me' => 'cd' |
107 | } |
108 | ], |
109 | [ |
110 | 'me.cdid', |
111 | 'me.artist', |
112 | 'me.title', |
113 | 'me.year' |
114 | ], |
115 | undef, |
116 | [ |
117 | \'year DESC' |
118 | ], |
119 | undef, |
120 | undef |
121 | ); |
122 | |
123 | is($sql, |
124 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, |
125 | 'did not quote ORDER BY with scalarref'); |
126 | |
127 | my %data = ( |
128 | name => 'Bill', |
129 | order => 12 |
130 | ); |
131 | |
132 | my @binds; |
133 | |
134 | ($sql,@binds) = $sql_maker->update( |
135 | 'group', |
136 | { |
137 | 'order' => '12', |
138 | 'name' => 'Bill' |
139 | } |
140 | ); |
141 | |
142 | is($sql, |
143 | q/UPDATE `group` SET `name` = ?, `order` = ?/, |
144 | 'quoted table names for UPDATE'); |
145 | |
146 | $sql_maker->{quote_char} = [qw/[ ]/]; |
147 | |
148 | ($sql,) = $sql_maker->select( |
149 | [ |
150 | { |
151 | 'me' => 'cd' |
152 | }, |
153 | [ |
154 | { |
155 | 'artist' => 'artist', |
156 | '-join_type' => '' |
157 | }, |
158 | { |
159 | 'artist.artistid' => 'me.artist' |
160 | } |
161 | ] |
162 | ], |
163 | [ |
164 | #{ |
165 | # 'count' => '*' |
166 | #} |
167 | \'COUNT( * )' |
168 | ], |
169 | { |
170 | 'artist.name' => 'Caterwauler McCrae', |
171 | 'me.year' => 2001 |
172 | }, |
173 | [], |
174 | undef, |
175 | undef |
176 | ); |
177 | |
178 | is($sql, |
179 | q/SELECT COUNT( * ) FROM [cd] [me] JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/, |
180 | 'got correct SQL for count query with bracket quoting'); |
181 | |
182 | |
183 | ($sql,@binds) = $sql_maker->update( |
184 | 'group', |
185 | { |
186 | 'order' => '12', |
187 | 'name' => 'Bill' |
188 | } |
189 | ); |
190 | |
191 | is($sql, |
192 | q/UPDATE [group] SET [name] = ?, [order] = ?/, |
193 | 'bracket quoted table names for UPDATE'); |