Commit | Line | Data |
d49e5323 |
1 | use strict; |
2 | use warnings; |
3 | |
3be357b0 |
4 | use Test::More; |
d49e5323 |
5 | use SQL::Abstract::Tree; |
6 | |
54750c7a |
7 | subtest no_formatting => sub { |
8 | my $sqlat = SQL::Abstract::Tree->new; |
9 | |
10 | { |
11 | my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; |
12 | my $expected_sql = |
13 | "SELECT a, b, c FROM foo WHERE foo.a = 1 AND foo.b LIKE 'station' "; |
14 | is($sqlat->format($sql), $expected_sql, |
15 | 'simple statement formatted correctly' |
16 | ); |
17 | } |
18 | |
19 | { |
20 | my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; |
21 | my $expected_sql = |
22 | "SELECT * FROM (SELECT * FROM foobar ) WHERE foo.a = 1 AND foo.b LIKE 'station' "; |
23 | is($sqlat->format($sql), $expected_sql, |
24 | 'subquery statement formatted correctly' |
25 | ); |
26 | } |
27 | |
28 | { |
29 | my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; |
30 | my $expected_sql = |
31 | "SELECT * FROM lolz WHERE (foo.a = 1) AND foo.b LIKE 'station' "; |
32 | |
33 | is($sqlat->format($sql), $expected_sql, |
34 | 'simple statement with parens in where formatted correctly' |
35 | ); |
36 | } |
1bb3956e |
37 | |
38 | { |
39 | my $sql = "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"; |
40 | my $expected_sql = |
41 | "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ([me].[user_id] = ?) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] "; |
42 | |
43 | is($sqlat->format($sql), $expected_sql, |
44 | 'real life statement 1 formatted correctly' |
45 | ); |
46 | } |
54750c7a |
47 | done_testing; |
48 | }; |
49 | |
50 | subtest console_monochrome => sub { |
51 | my $sqlat = SQL::Abstract::Tree->new({ |
52 | profile => 'console_monochrome', |
53 | }); |
54 | |
55 | { |
56 | my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; |
57 | my $expected_sql = |
58 | qq{SELECT a, b, c \n} . |
59 | qq{ FROM foo \n} . |
60 | qq{ WHERE foo.a = 1 AND foo.b LIKE 'station' }; |
61 | is($sqlat->format($sql), $expected_sql, |
62 | 'simple statement formatted correctly' |
63 | ); |
64 | } |
65 | |
66 | { |
67 | my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; |
68 | my $expected_sql = |
69 | qq{SELECT * \n} . |
70 | qq{ FROM (\n} . |
71 | qq{ SELECT * \n} . |
72 | qq{ FROM foobar \n} . |
73 | qq{ ) \n} . |
74 | qq{ WHERE foo.a = 1 AND foo.b LIKE 'station' }; |
75 | |
76 | is($sqlat->format($sql), $expected_sql, |
77 | 'subquery statement formatted correctly' |
78 | ); |
79 | } |
80 | |
81 | { |
82 | my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; |
83 | my $expected_sql = |
84 | qq{SELECT * \n} . |
85 | qq{ FROM lolz \n} . |
86 | qq{ WHERE (foo.a = 1) AND foo.b LIKE 'station' }; |
87 | |
88 | is($sqlat->format($sql), $expected_sql, |
89 | 'simple statement with parens in where formatted correctly' |
90 | ); |
91 | } |
1bb3956e |
92 | |
93 | { |
94 | my $sql = "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"; |
95 | my $expected_sql = |
96 | "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] \n". |
97 | " FROM [users_roles] [me] \n" . |
98 | " JOIN [roles] [role] \n" . |
99 | " ON [role].[id] = [me].[role_id] \n" . |
100 | " JOIN [roles_permissions] [role_permissions] \n" . |
101 | " ON [role_permissions].[role_id] = [role].[id] \n" . |
102 | " JOIN [permissions] [permission] \n" . |
103 | " ON [permission].[id] = [role_permissions].[permission_id] \n" . |
104 | " JOIN [permissionscreens] [permission_screens] \n" . |
105 | " ON [permission_screens].[permission_id] = [permission].[id] \n" . |
106 | " JOIN [screens] [screen] \n" . |
107 | " ON [screen].[id] = [permission_screens].[screen_id] \n" . |
108 | " WHERE ([me].[user_id] = ?) \n" . |
109 | " GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] "; |
110 | |
111 | my $gotten = $sqlat->format($sql); |
112 | is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); |
113 | } |
54750c7a |
114 | done_testing; |
115 | }; |
a24cc3a0 |
116 | |
7e5600e9 |
117 | subtest html => sub { |
118 | my $sqlat = SQL::Abstract::Tree->new({ |
119 | profile => 'html', |
120 | }); |
121 | |
122 | { |
123 | my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; |
124 | my $expected_sql = |
125 | qq{<span class="select">SELECT</span> a, b, c <br />\n} . |
126 | qq{ <span class="from">FROM</span> foo <br />\n} . |
127 | qq{ <span class="where">WHERE</span> foo.a = 1 AND foo.b LIKE 'station' }; |
128 | is($sqlat->format($sql), $expected_sql, |
129 | 'simple statement formatted correctly' |
130 | ); |
131 | } |
132 | |
133 | { |
134 | my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; |
135 | my $expected_sql = |
136 | qq{<span class="select">SELECT</span> * <br />\n} . |
137 | qq{ <span class="from">FROM</span> (<br />\n} . |
138 | qq{ <span class="select">SELECT</span> * <br />\n} . |
139 | qq{ <span class="from">FROM</span> foobar <br />\n} . |
140 | qq{ ) <br />\n} . |
141 | qq{ <span class="where">WHERE</span> foo.a = 1 AND foo.b LIKE 'station' }; |
142 | |
143 | is($sqlat->format($sql), $expected_sql, |
144 | 'subquery statement formatted correctly' |
145 | ); |
146 | } |
147 | |
148 | { |
149 | my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; |
150 | my $expected_sql = |
151 | qq{<span class="select">SELECT</span> * <br />\n} . |
152 | qq{ <span class="from">FROM</span> lolz <br />\n} . |
153 | qq{ <span class="where">WHERE</span> (foo.a = 1) AND foo.b LIKE 'station' }; |
154 | |
155 | is($sqlat->format($sql), $expected_sql, |
156 | 'simple statement with parens in where formatted correctly' |
157 | ); |
158 | } |
1bb3956e |
159 | |
160 | { |
161 | my $sql = "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"; |
162 | my $expected_sql = |
163 | qq{<span class="select">SELECT</span> [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] <br />\n}. |
164 | qq{ <span class="from">FROM</span> [users_roles] [me] <br />\n}. |
165 | qq{ <span class="join">JOIN</span> [roles] [role] <br />\n}. |
166 | qq{ <span class="on">ON</span> [role].[id] = [me].[role_id] <br />\n}. |
167 | qq{ <span class="join">JOIN</span> [roles_permissions] [role_permissions] <br />\n}. |
168 | qq{ <span class="on">ON</span> [role_permissions].[role_id] = [role].[id] <br />\n}. |
169 | qq{ <span class="join">JOIN</span> [permissions] [permission] <br />\n}. |
170 | qq{ <span class="on">ON</span> [permission].[id] = [role_permissions].[permission_id] <br />\n}. |
171 | qq{ <span class="join">JOIN</span> [permissionscreens] [permission_screens] <br />\n}. |
172 | qq{ <span class="on">ON</span> [permission_screens].[permission_id] = [permission].[id] <br />\n}. |
173 | qq{ <span class="join">JOIN</span> [screens] [screen] <br />\n}. |
174 | qq{ <span class="on">ON</span> [screen].[id] = [permission_screens].[screen_id] <br />\n}. |
175 | qq{ <span class="where">WHERE</span> ([me].[user_id] = ?) <br />\n}. |
176 | qq{ <span class="group-by">GROUP BY</span> [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] }; |
177 | |
178 | my $gotten = $sqlat->format($sql); |
179 | is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); |
180 | } |
7e5600e9 |
181 | done_testing; |
182 | }; |
183 | |
184 | subtest configuration => sub { |
185 | my $sqlat = SQL::Abstract::Tree->new({ |
186 | profile => 'console_monochrome', |
187 | indent_string => "\t", |
188 | indent_amount => 1, |
189 | newline => "\r\n", |
190 | }); |
191 | |
192 | { |
193 | my $sql = "SELECT a, b, c FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"; |
194 | my $expected_sql = |
195 | qq{SELECT a, b, c \r\n} . |
196 | qq{\tFROM foo \r\n} . |
197 | qq{\tWHERE foo.a = 1 AND foo.b LIKE 'station' }; |
198 | is($sqlat->format($sql), $expected_sql, |
199 | 'simple statement formatted correctly' |
200 | ); |
201 | } |
202 | |
203 | { |
204 | my $sql = "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"; |
205 | my $expected_sql = |
206 | qq{SELECT * \r\n} . |
207 | qq{\tFROM (\r\n} . |
208 | qq{\t\tSELECT * \r\n} . |
209 | qq{\t\t\tFROM foobar \r\n} . |
210 | qq{\t) \r\n} . |
211 | qq{\tWHERE foo.a = 1 AND foo.b LIKE 'station' }; |
212 | |
213 | is($sqlat->format($sql), $expected_sql, |
214 | 'subquery statement formatted correctly' |
215 | ); |
216 | } |
217 | |
218 | { |
219 | my $sql = "SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"; |
220 | my $expected_sql = |
221 | qq{SELECT * \r\n} . |
222 | qq{\tFROM lolz \r\n} . |
223 | qq{\tWHERE (foo.a = 1) AND foo.b LIKE 'station' }; |
224 | |
225 | is($sqlat->format($sql), $expected_sql, |
226 | 'simple statement with parens in where formatted correctly' |
227 | ); |
228 | } |
1bb3956e |
229 | |
230 | { |
231 | my $sql = "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"; |
232 | my $expected_sql = |
233 | "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] \r\n". |
234 | "\tFROM [users_roles] [me] \r\n" . |
235 | "\tJOIN [roles] [role] \r\n" . |
236 | "\t\tON [role].[id] = [me].[role_id] \r\n" . |
237 | "\tJOIN [roles_permissions] [role_permissions] \r\n" . |
238 | "\t\tON [role_permissions].[role_id] = [role].[id] \r\n" . |
239 | "\tJOIN [permissions] [permission] \r\n" . |
240 | "\t\tON [permission].[id] = [role_permissions].[permission_id] \r\n" . |
241 | "\tJOIN [permissionscreens] [permission_screens] \r\n" . |
242 | "\t\tON [permission_screens].[permission_id] = [permission].[id] \r\n" . |
243 | "\tJOIN [screens] [screen] \r\n" . |
244 | "\t\tON [screen].[id] = [permission_screens].[screen_id] \r\n" . |
245 | "\tWHERE ([me].[user_id] = ?) \r\n" . |
246 | "\tGROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] "; |
247 | |
248 | my $gotten = $sqlat->format($sql); |
249 | is($gotten, $expected_sql, 'real life statement 1 formatted correctly'); |
250 | } |
7e5600e9 |
251 | done_testing; |
252 | }; |
253 | |
3be357b0 |
254 | done_testing; |
d49e5323 |
255 | # stuff we want: |
d49e5323 |
256 | # Max Width |