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 | |
7 | my $sqlac = SQL::Abstract::ExtraClauses->new; |
8 | |
9 | my ($sql, @bind) = $sqlac->select({ |
10 | select => [ qw(artist.id artist.name), { -func => [ json_agg => 'cd' ] } ], |
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', |
18 | having => { '>' => [ { -func => [ count => 'cd.id' ] }, 3 ] } |
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 @> ? |
27 | ORDER BY artist.name |
28 | GROUP BY artist.id |
29 | HAVING COUNT(cd.id) > ? |
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 | |
63a3e784 |
91 | done_testing; |