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