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 | |
52 | ($sql,) = $sql_maker->select( |
53 | [ |
54 | { |
55 | 'me' => 'cd' |
56 | } |
57 | ], |
58 | [ |
59 | 'me.cdid', |
60 | 'me.artist', |
61 | 'me.title', |
62 | 'me.year' |
63 | ], |
64 | undef, |
65 | [ |
66 | 'year DESC' |
67 | ], |
68 | undef, |
69 | undef |
70 | ); |
71 | |
41751122 |
72 | |
73 | |
74 | TODO: { |
75 | local $TODO = "order_by with quoting needs fixing (ash/castaway)"; |
76 | |
77 | is($sql, |
78 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY `year` DESC/, |
79 | 'quoted ORDER BY with DESC okay'); |
80 | } |
81 | |
28f137a0 |
82 | |
83 | ($sql,) = $sql_maker->select( |
84 | [ |
85 | { |
86 | 'me' => 'cd' |
87 | } |
88 | ], |
89 | [ |
90 | 'me.*' |
91 | ], |
92 | undef, |
93 | [], |
94 | undef, |
95 | undef |
96 | ); |
97 | |
98 | is($sql, q/SELECT `me`.* FROM `cd` `me`/, 'select attr with me.* is right'); |
99 | |
100 | ($sql,) = $sql_maker->select( |
101 | [ |
102 | { |
103 | 'me' => 'cd' |
104 | } |
105 | ], |
106 | [ |
107 | 'me.cdid', |
108 | 'me.artist', |
109 | 'me.title', |
110 | 'me.year' |
111 | ], |
112 | undef, |
113 | [ |
114 | \'year DESC' |
115 | ], |
116 | undef, |
117 | undef |
118 | ); |
119 | |
120 | is($sql, |
121 | q/SELECT `me`.`cdid`, `me`.`artist`, `me`.`title`, `me`.`year` FROM `cd` `me` ORDER BY year DESC/, |
122 | 'did not quote ORDER BY with scalarref'); |
123 | |
124 | my %data = ( |
125 | name => 'Bill', |
126 | order => 12 |
127 | ); |
128 | |
129 | my @binds; |
130 | |
131 | ($sql,@binds) = $sql_maker->update( |
132 | 'group', |
133 | { |
134 | 'order' => '12', |
135 | 'name' => 'Bill' |
136 | } |
137 | ); |
138 | |
139 | is($sql, |
140 | q/UPDATE `group` SET `name` = ?, `order` = ?/, |
141 | 'quoted table names for UPDATE'); |
142 | |
143 | $sql_maker->{quote_char} = [qw/[ ]/]; |
144 | |
145 | ($sql,) = $sql_maker->select( |
146 | [ |
147 | { |
148 | 'me' => 'cd' |
149 | }, |
150 | [ |
151 | { |
152 | 'artist' => 'artist', |
153 | '-join_type' => '' |
154 | }, |
155 | { |
156 | 'artist.artistid' => 'me.artist' |
157 | } |
158 | ] |
159 | ], |
160 | [ |
161 | #{ |
162 | # 'count' => '*' |
163 | #} |
164 | \'COUNT( * )' |
165 | ], |
166 | { |
167 | 'artist.name' => 'Caterwauler McCrae', |
168 | 'me.year' => 2001 |
169 | }, |
170 | [], |
171 | undef, |
172 | undef |
173 | ); |
174 | |
175 | is($sql, |
176 | q/SELECT COUNT( * ) FROM [cd] [me] JOIN [artist] [artist] ON ( [artist].[artistid] = [me].[artist] ) WHERE ( [artist].[name] = ? AND [me].[year] = ? )/, |
177 | 'got correct SQL for count query with bracket quoting'); |
178 | |
179 | |
180 | ($sql,@binds) = $sql_maker->update( |
181 | 'group', |
182 | { |
183 | 'order' => '12', |
184 | 'name' => 'Bill' |
185 | } |
186 | ); |
187 | |
188 | is($sql, |
189 | q/UPDATE [group] SET [name] = ?, [order] = ?/, |
190 | 'bracket quoted table names for UPDATE'); |