Commit | Line | Data |
c42752fc |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
4a2c263b |
4 | use SQL::Abstract::Test import => [ qw(is_same_sql_bind is_same_sql) ]; |
df5d0507 |
5 | use SQL::Abstract; |
c42752fc |
6 | |
d10d5b94 |
7 | my $sqlac = SQL::Abstract->new->plugin('+ExtraClauses'); |
df5d0507 |
8 | |
54a2a1e5 |
9 | is_deeply( |
10 | [ $sqlac->statement_list ], |
11 | [ sort qw(select update insert delete) ], |
12 | ); |
13 | |
c42752fc |
14 | my ($sql, @bind) = $sqlac->select({ |
7250aa13 |
15 | select => [ qw(artist.id artist.name), { -json_agg => 'cd' } ], |
c42752fc |
16 | from => [ |
b99e9a14 |
17 | { artists => { -as => 'artist' } }, |
18 | -join => [ cds => as => 'cd' => on => { 'cd.artist_id' => 'artist.id' } ], |
c42752fc |
19 | ], |
20 | where => { 'artist.genres', => { '@>', { -value => [ 'Rock' ] } } }, |
21 | order_by => 'artist.name', |
22 | group_by => 'artist.id', |
7250aa13 |
23 | having => { '>' => [ { -count => 'cd.id' }, 3 ] } |
c42752fc |
24 | }); |
25 | |
26 | is_same_sql_bind( |
27 | $sql, \@bind, |
28 | q{ |
29 | SELECT artist.id, artist.name, JSON_AGG(cd) |
b99e9a14 |
30 | FROM artists AS artist JOIN cds AS cd ON cd.artist_id = artist.id |
c42752fc |
31 | WHERE artist.genres @> ? |
c42752fc |
32 | GROUP BY artist.id |
33 | HAVING COUNT(cd.id) > ? |
2b0b3d43 |
34 | ORDER BY artist.name |
c42752fc |
35 | }, |
36 | [ [ 'Rock' ], 3 ] |
37 | ); |
38 | |
4a2c263b |
39 | ($sql) = $sqlac->select({ |
40 | select => [ 'a' ], |
41 | from => [ { -values => [ [ 1, 2 ], [ 3, 4 ] ] }, -as => [ qw(t a b) ] ], |
42 | }); |
43 | |
44 | is_same_sql($sql, q{SELECT a FROM (VALUES (1, 2), (3, 4)) AS t(a,b)}); |
45 | |
e0eb8d26 |
46 | ($sql) = $sqlac->update({ |
47 | update => 'employees', |
48 | set => { sales_count => { sales_count => { '+', \1 } } }, |
49 | from => 'accounts', |
50 | where => { |
51 | 'accounts.name' => { '=' => \"'Acme Corporation'" }, |
52 | 'employees.id' => { -ident => 'accounts.sales_person' }, |
53 | } |
54 | }); |
55 | |
56 | is_same_sql( |
57 | $sql, |
58 | q{UPDATE employees SET sales_count = sales_count + 1 FROM accounts |
59 | WHERE accounts.name = 'Acme Corporation' |
60 | AND employees.id = accounts.sales_person |
61 | } |
62 | ); |
63 | |
64 | ($sql) = $sqlac->update({ |
65 | update => [ qw(tab1 tab2) ], |
66 | set => { |
67 | 'tab1.column1' => { -ident => 'value1' }, |
68 | 'tab1.column2' => { -ident => 'value2' }, |
69 | }, |
70 | where => { 'tab1.id' => { -ident => 'tab2.id' } }, |
71 | }); |
72 | |
73 | is_same_sql( |
74 | $sql, |
75 | q{UPDATE tab1, tab2 SET tab1.column1 = value1, tab1.column2 = value2 |
76 | WHERE tab1.id = tab2.id} |
77 | ); |
78 | |
79 | is_same_sql( |
80 | $sqlac->delete({ |
81 | from => 'x', |
82 | using => 'y', |
83 | where => { 'x.id' => { -ident => 'y.x_id' } } |
84 | }), |
85 | q{DELETE FROM x USING y WHERE x.id = y.x_id} |
86 | ); |
87 | |
88 | is_same_sql( |
89 | $sqlac->select({ |
90 | select => [ 'x.*', 'y.*' ], |
91 | from => [ 'x', -join => [ 'y', using => 'y_id' ] ], |
92 | }), |
93 | q{SELECT x.*, y.* FROM x JOIN y USING (y_id)}, |
94 | ); |
95 | |
1b0749b8 |
96 | is_same_sql( |
97 | $sqlac->select({ |
98 | select => 'x.*', |
99 | from => [ { -select => { select => '*', from => 'y' } }, -as => 'x' ], |
100 | }), |
101 | q{SELECT x.* FROM (SELECT * FROM y) AS x}, |
102 | ); |
103 | |
f8d50921 |
104 | is_same_sql( |
105 | $sqlac->insert({ |
106 | into => 'foo', |
93253a11 |
107 | select => { select => '*', from => 'bar' } |
f8d50921 |
108 | }), |
109 | q{INSERT INTO foo SELECT * FROM bar} |
110 | ); |
111 | |
93253a11 |
112 | ($sql, @bind) = $sqlac->insert({ |
113 | into => 'eh', |
114 | rowvalues => [ [ 1, 2 ], [ 3, 4 ], [ 5, 6 ] ] |
115 | }); |
116 | |
117 | is_same_sql_bind( |
118 | $sql, \@bind, |
119 | q{INSERT INTO eh VALUES (?, ?), (?, ?), (?, ?)}, |
120 | [ 1..6 ], |
121 | ); |
122 | |
7250aa13 |
123 | is_same_sql( |
124 | $sqlac->select({ |
125 | select => '*', |
126 | from => 'foo', |
127 | where => { -not_exists => { |
128 | -select => { |
129 | select => \1, |
130 | from => 'bar', |
131 | where => { 'foo.id' => { -ident => 'bar.foo_id' } } |
132 | }, |
133 | } }, |
134 | }), |
135 | q{SELECT * FROM foo |
136 | WHERE NOT EXISTS (SELECT 1 FROM bar WHERE foo.id = bar.foo_id)}, |
137 | ); |
138 | |
abf13001 |
139 | is_same_sql( |
140 | $sqlac->select({ |
141 | select => '*', |
142 | from => 'foo', |
143 | where => { id => { |
144 | '=' => { -select => { select => { -max => 'id' }, from => 'foo' } } |
145 | } }, |
146 | }), |
147 | q{SELECT * FROM foo WHERE id = (SELECT MAX(id) FROM foo)}, |
148 | ); |
149 | |
68525dce |
150 | { |
34570093 |
151 | my $sqlac = $sqlac->clone |
152 | ->clauses_of( |
153 | select => ( |
154 | $sqlac->clauses_of('select'), |
155 | qw(limit offset), |
156 | ) |
157 | ); |
68525dce |
158 | |
159 | ($sql, @bind) = $sqlac->select({ |
160 | select => '*', |
161 | from => 'foo', |
162 | limit => 10, |
163 | offset => 20, |
164 | }); |
165 | |
166 | is_same_sql_bind( |
167 | $sql, \@bind, |
168 | q{SELECT * FROM foo LIMIT ? OFFSET ?}, [ 10, 20 ] |
169 | ); |
170 | } |
171 | |
d175037f |
172 | $sql = $sqlac->select({ |
75d47dd0 |
173 | select => { -as => [ \1, 'x' ] }, |
174 | union => { -select => { select => { -as => [ \2, 'x' ] } } }, |
2b0b3d43 |
175 | order_by => { -desc => 'x' }, |
176 | }); |
177 | |
178 | is_same_sql( |
179 | $sql, |
180 | q{(SELECT 1 AS x) UNION (SELECT 2 AS x) ORDER BY x DESC}, |
181 | ); |
182 | |
d175037f |
183 | $sql = $sqlac->select({ |
184 | select => '*', |
185 | from => 'foo', |
186 | except => { -select => { select => '*', from => 'foo_exclusions' } } |
187 | }); |
188 | |
189 | is_same_sql( |
190 | $sql, |
191 | q{(SELECT * FROM foo) EXCEPT (SELECT * FROM foo_exclusions)}, |
192 | ); |
193 | |
1ba47f38 |
194 | $sql = $sqlac->select({ |
195 | with => [ foo => { -select => { select => \1 } } ], |
196 | select => '*', |
197 | from => 'foo' |
198 | }); |
199 | |
200 | is_same_sql( |
201 | $sql, |
541af914 |
202 | q{WITH foo AS (SELECT 1) SELECT * FROM foo}, |
1ba47f38 |
203 | ); |
204 | |
f9f1fdcd |
205 | $sql = $sqlac->update({ |
206 | _ => [ 'tree_table', -join => { |
207 | to => { -select => { |
208 | with_recursive => [ |
209 | [ tree_with_path => qw(id parent_id path) ], |
210 | { -select => { |
211 | _ => [ |
212 | qw(id parent_id), |
213 | { -as => [ |
214 | { -cast => { -as => [ id => char => 255 ] } }, |
215 | 'path' |
216 | ] }, |
217 | ], |
218 | from => 'tree_table', |
219 | where => { parent_id => undef }, |
220 | union_all => { |
221 | -select => { |
222 | _ => [ qw(t.id t.parent_id), |
223 | { -as => [ |
224 | { -concat => [ 'r.path', \q{'/'}, 't.id' ] }, |
225 | 'path', |
226 | ] }, |
227 | ], |
228 | from => [ |
229 | tree_table => -as => t => |
230 | -join => { |
231 | to => 'tree_with_path', |
232 | as => 'r', |
233 | on => { 't.parent_id' => 'r.id' }, |
234 | }, |
235 | ], |
236 | } }, |
237 | } }, |
238 | ], |
239 | select => '*', |
240 | from => 'tree_with_path' |
241 | } }, |
242 | as => 'tree', |
243 | on => { 'tree.id' => 'tree_with_path.id' }, |
244 | } ], |
245 | set => { path => { -ident => [ qw(tree path) ] } }, |
246 | }); |
247 | |
248 | is_same_sql( |
249 | $sql, |
250 | q{ |
251 | UPDATE tree_table JOIN ( |
541af914 |
252 | WITH RECURSIVE tree_with_path(id, parent_id, path) AS ( |
f9f1fdcd |
253 | ( |
254 | SELECT id, parent_id, CAST(id AS char(255)) AS path |
255 | FROM tree_table |
256 | WHERE parent_id IS NULL |
257 | ) |
258 | UNION ALL |
259 | ( |
260 | SELECT t.id, t.parent_id, CONCAT(r.path, '/', t.id) AS path |
261 | FROM tree_table AS t |
262 | JOIN tree_with_path AS r ON t.parent_id = r.id |
263 | ) |
541af914 |
264 | ) |
f9f1fdcd |
265 | SELECT * FROM tree_with_path |
266 | ) AS tree |
267 | ON tree.id = tree_with_path.id |
268 | SET path = tree.path |
269 | }, |
270 | ); |
271 | |
2329b891 |
272 | |
273 | ($sql, @bind) = $sqlac->insert({ |
888a0c49 |
274 | with => [ |
2329b891 |
275 | faculty => { |
276 | -select => { |
277 | _ => [qw /p.person p.email/], |
278 | from => [ person => -as => 'p' ], |
279 | where => { |
280 | 'p.person_type' => 'faculty', |
281 | 'p.person_status' => { '!=' => 'pending' }, |
282 | 'p.default_license_id' => undef, |
283 | }, |
284 | }, |
285 | }, |
286 | grandfather => { |
287 | -insert => { |
288 | into => 'license', |
289 | fields => [ qw(kind expires_on valid_from) ], |
290 | select => { |
888a0c49 |
291 | select => [\(qw('grandfather' '2017-06-30' '2016-07-01'))], |
2329b891 |
292 | from => 'faculty', |
293 | }, |
294 | returning => 'license_id', |
295 | } |
296 | }, |
297 | ], |
298 | into => 'license_person', |
6d3bcf02 |
299 | fields => [ qw(person_id license_id) ], |
2329b891 |
300 | select => { |
301 | _ => ['person_id', 'license_id'], |
302 | from => ['grandfather'], |
303 | where => { |
304 | 'a.index' => { -ident => 'b.index' }, |
305 | }, |
306 | }, |
307 | }); |
308 | |
309 | is_same_sql_bind( |
310 | $sql, \@bind, |
311 | q{ |
312 | WITH faculty AS ( |
313 | SELECT p.person, p.email FROM person AS p |
314 | WHERE ( |
315 | p.default_license_id IS NULL |
316 | AND p.person_status != ? |
317 | AND p.person_type = ? |
318 | ) |
319 | ), grandfather AS ( |
320 | INSERT INTO license (kind, expires_on, valid_from) |
321 | SELECT 'grandfather', '2017-06-30', '2016-07-01' |
322 | FROM faculty RETURNING license_id |
6d3bcf02 |
323 | ) INSERT INTO license_person (person_id, license_id) |
2329b891 |
324 | SELECT person_id, license_id FROM grandfather WHERE a.index = b.index |
325 | }, |
326 | [ qw(pending faculty) ], |
327 | ); |
328 | |
b1904bd5 |
329 | |
330 | ($sql, @bind) = $sqlac->delete({ |
888a0c49 |
331 | with => [ |
b1904bd5 |
332 | instructors => { |
333 | -select => { |
334 | _ => [qw/p.person_id email default_license_id/], |
335 | from => [ |
336 | person => -as => 'p', |
337 | -join => { |
338 | to => 'license_person', |
339 | as => 'lp', |
340 | on => { 'lp.person_id' => 'p.person_id' }, |
888a0c49 |
341 | }, |
b1904bd5 |
342 | -join => { |
343 | to => 'license', |
344 | as => 'l', |
345 | on => { 'l.license_id' => 'lp.license_id' }, |
888a0c49 |
346 | }, |
b1904bd5 |
347 | ], |
348 | where => { |
349 | 'p.person_type' => 'faculty', |
350 | 'p.person_status' => { '!=' => 'pending' }, |
351 | 'l.kind' => 'pending', |
352 | }, |
353 | group_by => [qw/ p.person_id /], |
354 | having => { '>' => [ { -count => 'l.license_id' }, 1 ] } |
355 | }, |
356 | }, |
357 | deletable_licenses => { |
358 | -select => { |
359 | _ => [qw/lp.ctid lp.person_id lp.license_id/], |
360 | from => [ |
361 | instructors => -as => 'i', |
362 | -join => { |
363 | to => 'license_person', |
364 | as => 'lp', |
365 | on => { 'lp.person_id' => 'i.person_id' }, |
888a0c49 |
366 | }, |
b1904bd5 |
367 | -join => { |
368 | to => 'license', |
369 | as => 'l', |
370 | on => { 'l.license_id' => 'lp.license_id' }, |
888a0c49 |
371 | }, |
b1904bd5 |
372 | ], |
373 | where => { |
888a0c49 |
374 | 'lp.license_id' => { |
b1904bd5 |
375 | '<>' => {-ident => 'i.default_license_id'} |
376 | }, |
377 | 'l.kind' => 'pending', |
378 | }, |
379 | }, |
380 | }, |
381 | ], |
382 | from => 'license_person', |
383 | where => { |
888a0c49 |
384 | ctid => { -in => |
b1904bd5 |
385 | { |
386 | -select => { |
888a0c49 |
387 | _ => ['ctid'], |
b1904bd5 |
388 | from => 'deletable_licenses', |
389 | } |
390 | } |
391 | } |
392 | } |
393 | }); |
394 | |
395 | is_same_sql_bind( |
396 | $sql, \@bind, |
397 | q{ |
398 | with instructors as ( |
399 | select p.person_id, email, default_license_id |
400 | from person as p |
401 | join license_person as lp on lp.person_id = p.person_id |
402 | join license as l on l.license_id = lp.license_id |
888a0c49 |
403 | where l.kind = ? |
404 | AND p.person_status != ? |
b1904bd5 |
405 | AND p.person_type = ? |
406 | group by p.person_id |
407 | having COUNT(l.license_id) > ?), |
408 | deletable_licenses as ( |
409 | select lp.ctid, lp.person_id, lp.license_id |
410 | from instructors as i |
411 | join license_person as lp on lp.person_id = i.person_id |
412 | join license as l on l.license_id = lp.license_id |
413 | where l.kind = ? |
414 | and lp.license_id <> i.default_license_id |
415 | ) |
888a0c49 |
416 | delete from license_person |
b1904bd5 |
417 | where ctid IN ( |
418 | (select ctid from deletable_licenses) |
419 | ) |
420 | }, |
421 | [qw( |
422 | pending pending faculty 1 pending |
423 | )] |
424 | ); |
425 | |
48b71e3b |
426 | ($sql, @bind) = $sqlac->update({ |
427 | _ => ['survey'], |
428 | set => { |
429 | license_id => { -ident => 'info.default_license_id' }, |
430 | }, |
888a0c49 |
431 | from => [ |
48b71e3b |
432 | -select => { |
433 | select => [qw( s.survey_id p.default_license_id p.person_id)], |
434 | from => [ |
435 | person => -as => 'p', |
436 | -join => { |
437 | to => 'class', |
438 | as => 'c', |
439 | on => { 'c.faculty_id' => 'p.person_id' }, |
888a0c49 |
440 | }, |
48b71e3b |
441 | -join => { |
442 | to => 'survey', |
443 | as => 's', |
444 | on => { 's.class_id' => 'c.class_id' }, |
888a0c49 |
445 | }, |
48b71e3b |
446 | ], |
447 | where => { 'p.institution_id' => { -value => 15031 } }, |
448 | }, |
449 | -as => 'info', |
450 | ], |
451 | where => { |
452 | 'info.survey_id' => { -ident => 'survey.survey_id' }, |
453 | } |
454 | }); |
455 | |
456 | is_same_sql_bind( |
457 | $sql, \@bind, |
458 | q{ |
459 | update survey |
460 | set license_id=info.default_license_id |
461 | from ( |
462 | select s.survey_id, p.default_license_id, p.person_id |
463 | from person AS p |
464 | join class AS c on c.faculty_id = p.person_id |
465 | join survey AS s on s.class_id = c.class_id |
466 | where p.institution_id = ? |
467 | ) AS info |
468 | where info.survey_id=survey.survey_id |
469 | }, |
470 | [qw( |
471 | 15031 |
472 | )] |
473 | ); |
474 | |
c42752fc |
475 | done_testing; |