Commit | Line | Data |
63a3e784 |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
76aa8b0d |
4 | use SQL::Abstract::Test import => [ qw(is_same_sql_bind is_same_sql) ]; |
63a3e784 |
5 | use SQL::Abstract::ExtraClauses; |
6 | |
f47b547b |
7 | my $sqlac = SQL::Abstract::ExtraClauses->new(unknown_unop_always_func => 1); |
63a3e784 |
8 | |
9 | my ($sql, @bind) = $sqlac->select({ |
f47b547b |
10 | select => [ qw(artist.id artist.name), { -json_agg => 'cd' } ], |
63a3e784 |
11 | from => [ |
6990b2aa |
12 | { artists => { -as => 'artist' } }, |
13 | -join => [ cds => as => 'cd' => on => { 'cd.artist_id' => 'artist.id' } ], |
63a3e784 |
14 | ], |
15 | where => { 'artist.genres', => { '@>', { -value => [ 'Rock' ] } } }, |
16 | order_by => 'artist.name', |
17 | group_by => 'artist.id', |
f47b547b |
18 | having => { '>' => [ { -count => 'cd.id' }, 3 ] } |
63a3e784 |
19 | }); |
20 | |
21 | is_same_sql_bind( |
22 | $sql, \@bind, |
23 | q{ |
24 | SELECT artist.id, artist.name, JSON_AGG(cd) |
6990b2aa |
25 | FROM artists AS artist JOIN cds AS cd ON cd.artist_id = artist.id |
63a3e784 |
26 | WHERE artist.genres @> ? |
63a3e784 |
27 | GROUP BY artist.id |
28 | HAVING COUNT(cd.id) > ? |
f7a20100 |
29 | ORDER BY artist.name |
63a3e784 |
30 | }, |
31 | [ [ 'Rock' ], 3 ] |
32 | ); |
33 | |
76aa8b0d |
34 | ($sql) = $sqlac->select({ |
35 | select => [ 'a' ], |
36 | from => [ { -values => [ [ 1, 2 ], [ 3, 4 ] ] }, -as => [ qw(t a b) ] ], |
37 | }); |
38 | |
39 | is_same_sql($sql, q{SELECT a FROM (VALUES (1, 2), (3, 4)) AS t(a,b)}); |
40 | |
0891ae97 |
41 | ($sql) = $sqlac->update({ |
42 | update => 'employees', |
43 | set => { sales_count => { sales_count => { '+', \1 } } }, |
44 | from => 'accounts', |
45 | where => { |
46 | 'accounts.name' => { '=' => \"'Acme Corporation'" }, |
47 | 'employees.id' => { -ident => 'accounts.sales_person' }, |
48 | } |
49 | }); |
50 | |
51 | is_same_sql( |
52 | $sql, |
53 | q{UPDATE employees SET sales_count = sales_count + 1 FROM accounts |
54 | WHERE accounts.name = 'Acme Corporation' |
55 | AND employees.id = accounts.sales_person |
56 | } |
57 | ); |
58 | |
59 | ($sql) = $sqlac->update({ |
60 | update => [ qw(tab1 tab2) ], |
61 | set => { |
62 | 'tab1.column1' => { -ident => 'value1' }, |
63 | 'tab1.column2' => { -ident => 'value2' }, |
64 | }, |
65 | where => { 'tab1.id' => { -ident => 'tab2.id' } }, |
66 | }); |
67 | |
68 | is_same_sql( |
69 | $sql, |
70 | q{UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 = value2 |
71 | WHERE tab1.id = tab2.id} |
72 | ); |
73 | |
74 | is_same_sql( |
75 | $sqlac->delete({ |
76 | from => 'x', |
77 | using => 'y', |
78 | where => { 'x.id' => { -ident => 'y.x_id' } } |
79 | }), |
80 | q{DELETE FROM x USING y WHERE x.id = y.x_id} |
81 | ); |
82 | |
83 | is_same_sql( |
84 | $sqlac->select({ |
85 | select => [ 'x.*', 'y.*' ], |
86 | from => [ 'x', -join => [ 'y', using => 'y_id' ] ], |
87 | }), |
88 | q{SELECT x.*, y.* FROM x JOIN y USING (y_id)}, |
89 | ); |
90 | |
b770fe29 |
91 | is_same_sql( |
92 | $sqlac->select({ |
93 | select => 'x.*', |
94 | from => [ { -select => { select => '*', from => 'y' } }, -as => 'x' ], |
95 | }), |
96 | q{SELECT x.* FROM (SELECT * FROM y) AS x}, |
97 | ); |
98 | |
6da32de8 |
99 | is_same_sql( |
100 | $sqlac->insert({ |
101 | into => 'foo', |
bea59460 |
102 | select => { select => '*', from => 'bar' } |
6da32de8 |
103 | }), |
104 | q{INSERT INTO foo SELECT * FROM bar} |
105 | ); |
106 | |
bea59460 |
107 | ($sql, @bind) = $sqlac->insert({ |
108 | into => 'eh', |
109 | rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] |
110 | }); |
111 | |
112 | is_same_sql_bind( |
113 | $sql, \@bind, |
114 | q{INSERT INTO eh VALUES (?, ?), (?, ?), (?, ?)}, |
115 | [ 1..6 ], |
116 | ); |
117 | |
f47b547b |
118 | is_same_sql( |
119 | $sqlac->select({ |
120 | select => '*', |
121 | from => 'foo', |
122 | where => { -not_exists => { |
123 | -select => { |
124 | select => \1, |
125 | from => 'bar', |
126 | where => { 'foo.id' => { -ident => 'bar.foo_id' } } |
127 | }, |
128 | } }, |
129 | }), |
130 | q{SELECT * FROM foo |
131 | WHERE NOT EXISTS (SELECT 1 FROM bar WHERE foo.id = bar.foo_id)}, |
132 | ); |
133 | |
578d9053 |
134 | is_same_sql( |
135 | $sqlac->select({ |
136 | select => '*', |
137 | from => 'foo', |
138 | where => { id => { |
139 | '=' => { -select => { select => { -max => 'id' }, from => 'foo' } } |
140 | } }, |
141 | }), |
142 | q{SELECT * FROM foo WHERE id = (SELECT MAX(id) FROM foo)}, |
143 | ); |
144 | |
b77d646a |
145 | { |
8637f869 |
146 | my $sqlac = $sqlac->clone |
147 | ->clauses_of( |
148 | select => ( |
149 | $sqlac->clauses_of('select'), |
150 | qw(limit offset), |
151 | ) |
152 | ); |
b77d646a |
153 | |
154 | ($sql, @bind) = $sqlac->select({ |
155 | select => '*', |
156 | from => 'foo', |
157 | limit => 10, |
158 | offset => 20, |
159 | }); |
160 | |
161 | is_same_sql_bind( |
162 | $sql, \@bind, |
163 | q{SELECT * FROM foo LIMIT ? OFFSET ?}, [ 10, 20 ] |
164 | ); |
165 | } |
166 | |
f7fd09f7 |
167 | $sql = $sqlac->select({ |
7fa7ab5c |
168 | select => { -as => [ \1, 'x' ] }, |
169 | union => { -select => { select => { -as => [ \2, 'x' ] } } }, |
f7a20100 |
170 | order_by => { -desc => 'x' }, |
171 | }); |
172 | |
173 | is_same_sql( |
174 | $sql, |
175 | q{(SELECT 1 AS x) UNION (SELECT 2 AS x) ORDER BY x DESC}, |
176 | ); |
177 | |
f7fd09f7 |
178 | $sql = $sqlac->select({ |
179 | select => '*', |
180 | from => 'foo', |
181 | except => { -select => { select => '*', from => 'foo_exclusions' } } |
182 | }); |
183 | |
184 | is_same_sql( |
185 | $sql, |
186 | q{(SELECT * FROM foo) EXCEPT (SELECT * FROM foo_exclusions)}, |
187 | ); |
188 | |
369e7844 |
189 | $sql = $sqlac->select({ |
190 | with => [ foo => { -select => { select => \1 } } ], |
191 | select => '*', |
192 | from => 'foo' |
193 | }); |
194 | |
195 | is_same_sql( |
196 | $sql, |
aed9ebda |
197 | q{WITH foo AS (SELECT 1) SELECT * FROM foo}, |
369e7844 |
198 | ); |
199 | |
af407e9a |
200 | $sql = $sqlac->update({ |
201 | _ => [ 'tree_table', -join => { |
202 | to => { -select => { |
203 | with_recursive => [ |
204 | [ tree_with_path => qw(id parent_id path) ], |
205 | { -select => { |
206 | _ => [ |
207 | qw(id parent_id), |
208 | { -as => [ |
209 | { -cast => { -as => [ id => char => 255 ] } }, |
210 | 'path' |
211 | ] }, |
212 | ], |
213 | from => 'tree_table', |
214 | where => { parent_id => undef }, |
215 | union_all => { |
216 | -select => { |
217 | _ => [ qw(t.id t.parent_id), |
218 | { -as => [ |
219 | { -concat => [ 'r.path', \q{'/'}, 't.id' ] }, |
220 | 'path', |
221 | ] }, |
222 | ], |
223 | from => [ |
224 | tree_table => -as => t => |
225 | -join => { |
226 | to => 'tree_with_path', |
227 | as => 'r', |
228 | on => { 't.parent_id' => 'r.id' }, |
229 | }, |
230 | ], |
231 | } }, |
232 | } }, |
233 | ], |
234 | select => '*', |
235 | from => 'tree_with_path' |
236 | } }, |
237 | as => 'tree', |
238 | on => { 'tree.id' => 'tree_with_path.id' }, |
239 | } ], |
240 | set => { path => { -ident => [ qw(tree path) ] } }, |
241 | }); |
242 | |
243 | is_same_sql( |
244 | $sql, |
245 | q{ |
246 | UPDATE tree_table JOIN ( |
aed9ebda |
247 | WITH RECURSIVE tree_with_path(id, parent_id, path) AS ( |
af407e9a |
248 | ( |
249 | SELECT id, parent_id, CAST(id AS char(255)) AS path |
250 | FROM tree_table |
251 | WHERE parent_id IS NULL |
252 | ) |
253 | UNION ALL |
254 | ( |
255 | SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path |
256 | FROM tree_table AS t |
257 | JOIN tree_with_path AS r ON t.parent_id = r.id |
258 | ) |
aed9ebda |
259 | ) |
af407e9a |
260 | SELECT * FROM tree_with_path |
261 | ) AS tree |
262 | ON tree.id = tree_with_path.id |
263 | SET path = tree.path |
264 | }, |
265 | ); |
266 | |
63a3e784 |
267 | done_testing; |