Commit | Line | Data |
f5aab26e |
1 | use strict; |
2 | use warnings; |
3 | use Test::More; |
952f9e2d |
4 | use Test::Exception; |
f5aab26e |
5 | |
6 | use SQL::Abstract; |
7 | |
5aad8cf3 |
8 | use SQL::Abstract::Test import => ['is_same_sql_bind']; |
10e6c946 |
9 | my @cases = |
f5aab26e |
10 | ( |
11 | { |
12 | given => \'colA DESC', |
13 | expects => ' ORDER BY colA DESC', |
761a495a |
14 | expects_quoted => ' ORDER BY `colA` DESC', |
f5aab26e |
15 | }, |
16 | { |
17 | given => 'colA', |
18 | expects => ' ORDER BY colA', |
19 | expects_quoted => ' ORDER BY `colA`', |
20 | }, |
71519cf6 |
21 | { # it may look odd, but this is the desired behaviour (mst) |
22 | given => 'colA DESC', |
23 | expects => ' ORDER BY colA DESC', |
24 | expects_quoted => ' ORDER BY `colA DESC`', |
25 | }, |
f5aab26e |
26 | { |
27 | given => [qw/colA colB/], |
28 | expects => ' ORDER BY colA, colB', |
29 | expects_quoted => ' ORDER BY `colA`, `colB`', |
30 | }, |
71519cf6 |
31 | { # it may look odd, but this is the desired behaviour (mst) |
32 | given => ['colA ASC', 'colB DESC'], |
33 | expects => ' ORDER BY colA ASC, colB DESC', |
34 | expects_quoted => ' ORDER BY `colA ASC`, `colB DESC`', |
35 | }, |
f5aab26e |
36 | { |
37 | given => {-asc => 'colA'}, |
38 | expects => ' ORDER BY colA ASC', |
39 | expects_quoted => ' ORDER BY `colA` ASC', |
40 | }, |
41 | { |
42 | given => {-desc => 'colB'}, |
43 | expects => ' ORDER BY colB DESC', |
44 | expects_quoted => ' ORDER BY `colB` DESC', |
45 | }, |
46 | { |
47 | given => [{-asc => 'colA'}, {-desc => 'colB'}], |
48 | expects => ' ORDER BY colA ASC, colB DESC', |
49 | expects_quoted => ' ORDER BY `colA` ASC, `colB` DESC', |
50 | }, |
1cfa1db3 |
51 | { |
52 | given => ['colA', {-desc => 'colB'}], |
53 | expects => ' ORDER BY colA, colB DESC', |
54 | expects_quoted => ' ORDER BY `colA`, `colB` DESC', |
55 | }, |
b6475fb1 |
56 | { |
57 | given => undef, |
58 | expects => '', |
59 | expects_quoted => '', |
60 | }, |
994edb77 |
61 | |
62 | { |
63 | given => [{-desc => [ qw/colA colB/ ] }], |
64 | expects => ' ORDER BY colA DESC, colB DESC', |
65 | expects_quoted => ' ORDER BY `colA` DESC, `colB` DESC', |
66 | }, |
67 | { |
68 | given => [{-desc => [ qw/colA colB/ ] }, {-asc => 'colC'}], |
69 | expects => ' ORDER BY colA DESC, colB DESC, colC ASC', |
70 | expects_quoted => ' ORDER BY `colA` DESC, `colB` DESC, `colC` ASC', |
71 | }, |
72 | { |
73 | given => [{-desc => [ qw/colA colB/ ] }, {-asc => [ qw/colC colD/ ] }], |
74 | expects => ' ORDER BY colA DESC, colB DESC, colC ASC, colD ASC', |
75 | expects_quoted => ' ORDER BY `colA` DESC, `colB` DESC, `colC` ASC, `colD` ASC', |
76 | }, |
77 | { |
78 | given => [{-desc => [ qw/colA colB/ ] }, {-desc => 'colC' }], |
79 | expects => ' ORDER BY colA DESC, colB DESC, colC DESC', |
80 | expects_quoted => ' ORDER BY `colA` DESC, `colB` DESC, `colC` DESC', |
81 | }, |
b8b747f7 |
82 | { |
952f9e2d |
83 | given => [{ -asc => 'colA' }, { -desc => [qw/colB/] }, { -asc => [qw/colC colD/] }], |
84 | expects => ' ORDER BY colA ASC, colB DESC, colC ASC, colD ASC', |
85 | expects_quoted => ' ORDER BY `colA` ASC, `colB` DESC, `colC` ASC, `colD` ASC', |
b8b747f7 |
86 | }, |
293149c5 |
87 | { |
88 | given => { -desc => \['colA LIKE ?', 'test'] }, |
89 | expects => ' ORDER BY colA LIKE ? DESC', |
90 | expects_quoted => ' ORDER BY colA LIKE ? DESC', |
f267b646 |
91 | bind => ['test'], |
92 | }, |
93 | { |
16c7f71c |
94 | given => \['colA LIKE ? DESC', 'test'], |
95 | expects => ' ORDER BY colA LIKE ? DESC', |
96 | expects_quoted => ' ORDER BY colA LIKE ? DESC', |
97 | bind => ['test'], |
98 | }, |
99 | { |
f267b646 |
100 | given => [ { -asc => \['colA'] }, { -desc => \['colB LIKE ?', 'test'] }, { -asc => \['colC LIKE ?', 'tost'] }], |
101 | expects => ' ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC', |
102 | expects_quoted => ' ORDER BY colA ASC, colB LIKE ? DESC, colC LIKE ? ASC', |
103 | bind => [qw/test tost/], |
293149c5 |
104 | }, |
36e3ea6c |
105 | { |
0f87e4f0 |
106 | given => [ { -ASC => 'colA', -NULLS => 'FIRST' }, { -DESC => 'colB', -NULLS => 'LAST' } ], |
36e3ea6c |
107 | expects => ' ORDER BY colA NULLS FIRST, colB DESC NULLS LAST', |
108 | expects_quoted => ' ORDER BY `colA` NULLS FIRST, `colB` DESC NULLS LAST', |
109 | }, |
110 | { |
111 | given => [ { -asc => 'colA', -nulls => 'first' }, { -desc => 'colB', -nulls => 'last' } ], |
112 | expects => ' ORDER BY colA NULLS FIRST, colB DESC NULLS LAST', |
113 | expects_quoted => ' ORDER BY `colA` NULLS FIRST, `colB` DESC NULLS LAST', |
114 | }, |
115 | { |
116 | given => { -asc => [qw/colA colB/], -nulls => 'first' } , |
117 | expects => ' ORDER BY colA NULLS FIRST, colB NULLS FIRST', |
118 | expects_quoted => ' ORDER BY `colA` NULLS FIRST, `colB` NULLS FIRST', |
119 | }, |
f5aab26e |
120 | ); |
121 | |
6dcf723c |
122 | my $sql = SQL::Abstract->new; |
123 | my $sqlq = SQL::Abstract->new({quote_char => '`'}); |
124 | |
f267b646 |
125 | for my $case( @cases) { |
126 | my ($stat, @bind); |
127 | |
128 | ($stat, @bind) = $sql->_order_by($case->{given}); |
129 | is_same_sql_bind ( |
130 | $stat, |
131 | \@bind, |
132 | $case->{expects}, |
133 | $case->{bind} || [], |
134 | ); |
135 | |
136 | ($stat, @bind) = $sqlq->_order_by($case->{given}); |
137 | is_same_sql_bind ( |
138 | $stat, |
139 | \@bind, |
140 | $case->{expects_quoted}, |
141 | $case->{bind} || [], |
142 | ); |
f5aab26e |
143 | } |
952f9e2d |
144 | |
145 | throws_ok ( |
146 | sub { $sql->_order_by({-desc => 'colA', -asc => 'colB' }) }, |
36e3ea6c |
147 | qr/hash passed .+ must have exactly one of/, |
952f9e2d |
148 | 'Undeterministic order exception', |
149 | ); |
150 | |
151 | throws_ok ( |
152 | sub { $sql->_order_by({-desc => [ qw/colA colB/ ], -asc => [ qw/colC colD/ ] }) }, |
36e3ea6c |
153 | qr/hash passed .+ must have exactly one of/, |
952f9e2d |
154 | 'Undeterministic order exception', |
155 | ); |
36e3ea6c |
156 | |
157 | throws_ok( |
158 | sub { $sql->_order_by({-wibble => "fleem" }) }, |
b75e9884 |
159 | qr/invalid key -wibble in hash/, |
36e3ea6c |
160 | 'Invalid order exception', |
161 | ); |
162 | |
163 | throws_ok( |
164 | sub { $sql->_order_by({-nulls => "fleem" }) }, |
165 | qr/invalid value for -nulls/, |
166 | 'Invalid nulls exception', |
167 | ); |
c771453a |
168 | |
10e6c946 |
169 | done_testing; |