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