Commit | Line | Data |
d49e5323 |
1 | use strict; |
2 | use warnings; |
3 | |
3be357b0 |
4 | use Test::More; |
408cbda3 |
5 | use Test::Deep; |
0f9a26cb |
6 | use Test::Warn; |
d49e5323 |
7 | use SQL::Abstract::Tree; |
8 | |
408cbda3 |
9 | my $sqlat = SQL::Abstract::Tree->new; |
54750c7a |
10 | |
257ecc8a |
11 | cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [ |
958f1ce4 |
12 | [ |
13 | [ |
14 | "SELECT", |
15 | [ |
16 | [ |
17 | "LIST", |
18 | [ |
19 | [ |
20 | "LITERAL", |
21 | [ |
22 | "a" |
23 | ] |
24 | ], |
25 | [ |
26 | "LITERAL", |
27 | [ |
257ecc8a |
28 | "b.*" |
958f1ce4 |
29 | ] |
30 | ], |
31 | [ |
32 | "LITERAL", |
33 | [ |
257ecc8a |
34 | "*" |
958f1ce4 |
35 | ] |
36 | ] |
37 | ] |
38 | ] |
39 | ] |
40 | ], |
41 | [ |
42 | "FROM", |
43 | [ |
44 | [ |
45 | "LITERAL", |
46 | [ |
47 | "foo" |
48 | ] |
49 | ] |
50 | ] |
51 | ] |
52 | ], |
53 | [ |
54 | "WHERE", |
55 | [ |
56 | [ |
57 | "AND", |
58 | [ |
59 | [ |
60 | "=", |
61 | [ |
62 | [ |
63 | "LITERAL", |
64 | [ |
65 | "foo.a" |
66 | ] |
67 | ], |
68 | [ |
69 | "LITERAL", |
70 | [ |
71 | 1 |
72 | ] |
73 | ] |
74 | ] |
75 | ], |
76 | [ |
77 | "LIKE", |
78 | [ |
79 | [ |
80 | "LITERAL", |
81 | [ |
82 | "foo.b" |
83 | ] |
84 | ], |
85 | [ |
86 | "LITERAL", |
87 | [ |
88 | "'station'" |
89 | ] |
90 | ] |
91 | ] |
92 | ] |
93 | ] |
94 | ] |
95 | ] |
96 | ] |
97 | ], 'simple statement parsed correctly'); |
54750c7a |
98 | |
958f1ce4 |
99 | cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"), [ |
100 | [ |
101 | [ |
102 | "SELECT", |
103 | [ |
104 | [ |
105 | "LITERAL", |
106 | [ |
107 | "*" |
108 | ] |
109 | ] |
110 | ] |
111 | ], |
112 | [ |
113 | "FROM", |
114 | [ |
115 | [ |
116 | "PAREN", |
117 | [ |
118 | [ |
119 | [ |
120 | "SELECT", |
121 | [ |
122 | [ |
123 | "LITERAL", |
124 | [ |
125 | "*" |
126 | ] |
127 | ] |
128 | ] |
129 | ], |
130 | [ |
131 | "FROM", |
132 | [ |
133 | [ |
134 | "LITERAL", |
135 | [ |
136 | "foobar" |
137 | ] |
138 | ] |
139 | ] |
140 | ] |
141 | ] |
142 | ] |
143 | ] |
144 | ] |
145 | ] |
146 | ], |
147 | [ |
148 | "WHERE", |
149 | [ |
150 | [ |
151 | "AND", |
152 | [ |
153 | [ |
154 | "=", |
155 | [ |
156 | [ |
157 | "LITERAL", |
158 | [ |
159 | "foo.a" |
160 | ] |
161 | ], |
162 | [ |
163 | "LITERAL", |
164 | [ |
165 | 1 |
166 | ] |
167 | ] |
168 | ] |
169 | ], |
170 | [ |
171 | "LIKE", |
172 | [ |
173 | [ |
174 | "LITERAL", |
175 | [ |
176 | "foo.b" |
177 | ] |
178 | ], |
179 | [ |
180 | "LITERAL", |
181 | [ |
182 | "'station'" |
183 | ] |
184 | ] |
185 | ] |
186 | ] |
187 | ] |
188 | ] |
189 | ] |
190 | ] |
191 | ], 'subquery statement parsed correctly'); |
54750c7a |
192 | |
958f1ce4 |
193 | cmp_deeply($sqlat->parse("SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"), [ |
194 | [ |
195 | [ |
196 | "SELECT", |
197 | [ |
198 | [ |
199 | "LITERAL", |
200 | [ |
201 | "*" |
202 | ] |
203 | ] |
204 | ] |
205 | ], |
206 | [ |
207 | "FROM", |
208 | [ |
209 | [ |
210 | "LITERAL", |
211 | [ |
212 | "lolz" |
213 | ] |
214 | ] |
215 | ] |
216 | ] |
217 | ], |
218 | [ |
219 | "WHERE", |
220 | [ |
221 | [ |
222 | "AND", |
223 | [ |
224 | [ |
225 | "PAREN", |
226 | [ |
227 | [ |
228 | "=", |
229 | [ |
230 | [ |
231 | "LITERAL", |
232 | [ |
233 | "foo.a" |
234 | ] |
235 | ], |
236 | [ |
237 | "LITERAL", |
238 | [ |
239 | 1 |
240 | ] |
241 | ] |
242 | ] |
243 | ] |
244 | ] |
245 | ], |
246 | [ |
247 | "LIKE", |
248 | [ |
249 | [ |
250 | "LITERAL", |
251 | [ |
252 | "foo.b" |
253 | ] |
254 | ], |
255 | [ |
256 | "LITERAL", |
257 | [ |
258 | "'station'" |
259 | ] |
260 | ] |
261 | ] |
262 | ] |
263 | ] |
264 | ] |
265 | ] |
266 | ] |
267 | ], 'simple statement with parens in where parsed correctly'); |
54750c7a |
268 | |
958f1ce4 |
269 | cmp_deeply($sqlat->parse( "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]"), [ |
270 | [ |
271 | [ |
272 | [ |
273 | [ |
274 | [ |
275 | [ |
276 | [ |
277 | [ |
278 | [ |
279 | [ |
280 | [ |
281 | [ |
282 | [ |
283 | "SELECT", |
284 | [ |
285 | [ |
286 | "LIST", |
287 | [ |
288 | [ |
289 | "LITERAL", |
290 | [ |
291 | "[screen].[id]" |
292 | ] |
293 | ], |
294 | [ |
295 | "LITERAL", |
296 | [ |
297 | "[screen].[name]" |
298 | ] |
299 | ], |
300 | [ |
301 | "LITERAL", |
302 | [ |
303 | "[screen].[section_id]" |
304 | ] |
305 | ], |
306 | [ |
307 | "LITERAL", |
308 | [ |
309 | "[screen].[xtype]" |
310 | ] |
311 | ] |
312 | ] |
313 | ] |
314 | ] |
315 | ], |
316 | [ |
317 | "FROM", |
318 | [ |
319 | [ |
320 | [ |
321 | "LITERAL", |
322 | [ |
323 | "[users_roles]" |
324 | ] |
325 | ], |
326 | [ |
327 | "LITERAL", |
328 | [ |
329 | "[me]" |
330 | ] |
331 | ] |
332 | ] |
333 | ] |
334 | ] |
335 | ], |
336 | [ |
337 | "JOIN", |
338 | [ |
339 | [ |
340 | [ |
341 | "LITERAL", |
342 | [ |
343 | "[roles]" |
344 | ] |
345 | ], |
346 | [ |
347 | "LITERAL", |
348 | [ |
349 | "[role]" |
350 | ] |
351 | ] |
352 | ] |
353 | ] |
354 | ] |
355 | ], |
356 | [ |
357 | "ON", |
358 | [ |
359 | [ |
360 | "=", |
361 | [ |
362 | [ |
363 | "LITERAL", |
364 | [ |
365 | "[role].[id]" |
366 | ] |
367 | ], |
368 | [ |
369 | "LITERAL", |
370 | [ |
371 | "[me].[role_id]" |
372 | ] |
373 | ] |
374 | ] |
375 | ] |
376 | ] |
377 | ] |
378 | ], |
379 | [ |
380 | "JOIN", |
381 | [ |
382 | [ |
383 | [ |
384 | "LITERAL", |
385 | [ |
386 | "[roles_permissions]" |
387 | ] |
388 | ], |
389 | [ |
390 | "LITERAL", |
391 | [ |
392 | "[role_permissions]" |
393 | ] |
394 | ] |
395 | ] |
396 | ] |
397 | ] |
398 | ], |
399 | [ |
400 | "ON", |
401 | [ |
402 | [ |
403 | "=", |
404 | [ |
405 | [ |
406 | "LITERAL", |
407 | [ |
408 | "[role_permissions].[role_id]" |
409 | ] |
410 | ], |
411 | [ |
412 | "LITERAL", |
413 | [ |
414 | "[role].[id]" |
415 | ] |
416 | ] |
417 | ] |
418 | ] |
419 | ] |
420 | ] |
421 | ], |
422 | [ |
423 | "JOIN", |
424 | [ |
425 | [ |
426 | [ |
427 | "LITERAL", |
428 | [ |
429 | "[permissions]" |
430 | ] |
431 | ], |
432 | [ |
433 | "LITERAL", |
434 | [ |
435 | "[permission]" |
436 | ] |
437 | ] |
438 | ] |
439 | ] |
440 | ] |
441 | ], |
442 | [ |
443 | "ON", |
444 | [ |
445 | [ |
446 | "=", |
447 | [ |
448 | [ |
449 | "LITERAL", |
450 | [ |
451 | "[permission].[id]" |
452 | ] |
453 | ], |
454 | [ |
455 | "LITERAL", |
456 | [ |
457 | "[role_permissions].[permission_id]" |
458 | ] |
459 | ] |
460 | ] |
461 | ] |
462 | ] |
463 | ] |
464 | ], |
465 | [ |
466 | "JOIN", |
467 | [ |
468 | [ |
469 | [ |
470 | "LITERAL", |
471 | [ |
472 | "[permissionscreens]" |
473 | ] |
474 | ], |
475 | [ |
476 | "LITERAL", |
477 | [ |
478 | "[permission_screens]" |
479 | ] |
480 | ] |
481 | ] |
482 | ] |
483 | ] |
484 | ], |
485 | [ |
486 | "ON", |
487 | [ |
488 | [ |
489 | "=", |
490 | [ |
491 | [ |
492 | "LITERAL", |
493 | [ |
494 | "[permission_screens].[permission_id]" |
495 | ] |
496 | ], |
497 | [ |
498 | "LITERAL", |
499 | [ |
500 | "[permission].[id]" |
501 | ] |
502 | ] |
503 | ] |
504 | ] |
505 | ] |
506 | ] |
507 | ], |
508 | [ |
509 | "JOIN", |
510 | [ |
511 | [ |
512 | [ |
513 | "LITERAL", |
514 | [ |
515 | "[screens]" |
516 | ] |
517 | ], |
518 | [ |
519 | "LITERAL", |
520 | [ |
521 | "[screen]" |
522 | ] |
523 | ] |
524 | ] |
525 | ] |
526 | ] |
527 | ], |
528 | [ |
529 | "ON", |
530 | [ |
531 | [ |
532 | "=", |
533 | [ |
534 | [ |
535 | "LITERAL", |
536 | [ |
537 | "[screen].[id]" |
538 | ] |
539 | ], |
540 | [ |
541 | "LITERAL", |
542 | [ |
543 | "[permission_screens].[screen_id]" |
544 | ] |
545 | ] |
546 | ] |
547 | ] |
548 | ] |
549 | ] |
550 | ], |
551 | [ |
552 | "WHERE", |
553 | [ |
554 | [ |
555 | "PAREN", |
556 | [ |
557 | [ |
558 | "=", |
559 | [ |
560 | [ |
561 | "LITERAL", |
562 | [ |
563 | "[me].[user_id]" |
564 | ] |
565 | ], |
566 | [ |
567 | "PLACEHOLDER", |
568 | [ |
569 | "?" |
570 | ] |
571 | ] |
572 | ] |
573 | ] |
574 | ] |
575 | ] |
576 | ] |
577 | ] |
578 | ], |
579 | [ |
580 | "GROUP BY", |
581 | [ |
582 | [ |
583 | "LIST", |
584 | [ |
585 | [ |
586 | "LITERAL", |
587 | [ |
588 | "[screen].[id]" |
589 | ] |
590 | ], |
591 | [ |
592 | "LITERAL", |
593 | [ |
594 | "[screen].[name]" |
595 | ] |
596 | ], |
597 | [ |
598 | "LITERAL", |
599 | [ |
600 | "[screen].[section_id]" |
601 | ] |
602 | ], |
603 | [ |
604 | "LITERAL", |
605 | [ |
606 | "[screen].[xtype]" |
607 | ] |
608 | ] |
609 | ] |
610 | ] |
611 | ] |
612 | ] |
613 | ], 'real life statement 1 parsed correctly'); |
1bb3956e |
614 | |
958f1ce4 |
615 | cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [ |
616 | [ |
617 | [ |
618 | "SELECT", |
619 | [ |
620 | [ |
621 | "LIST", |
622 | [ |
623 | [ |
624 | "LITERAL", |
625 | [ |
626 | "x" |
627 | ] |
628 | ], |
629 | [ |
630 | "LITERAL", |
631 | [ |
632 | "y" |
633 | ] |
634 | ] |
635 | ] |
636 | ] |
637 | ] |
638 | ], |
639 | [ |
640 | "FROM", |
641 | [ |
642 | [ |
643 | "LITERAL", |
644 | [ |
645 | "foo" |
646 | ] |
647 | ] |
648 | ] |
649 | ] |
650 | ], |
651 | [ |
652 | "WHERE", |
653 | [ |
654 | [ |
655 | "IN", |
656 | [ |
657 | [ |
658 | "LITERAL", |
659 | [ |
660 | "x" |
661 | ] |
662 | ], |
663 | [ |
664 | "PAREN", |
665 | [ |
666 | [ |
667 | "LIST", |
668 | [ |
669 | [ |
670 | "PLACEHOLDER", |
671 | [ |
672 | "?" |
673 | ] |
674 | ], |
675 | [ |
676 | "PLACEHOLDER", |
677 | [ |
678 | "?" |
679 | ] |
680 | ], |
681 | [ |
682 | "PLACEHOLDER", |
683 | [ |
684 | "?" |
685 | ] |
686 | ], |
687 | [ |
688 | "PLACEHOLDER", |
689 | [ |
690 | "?" |
691 | ] |
692 | ] |
693 | ] |
694 | ] |
695 | ] |
696 | ] |
697 | ] |
698 | ] |
699 | ] |
700 | ] |
701 | ], 'Lists parsed correctly'); |
7f2dd81e |
702 | |
0f9a26cb |
703 | # test for recursion warnings on huge selectors |
704 | warnings_are { |
705 | my $sql = sprintf 'SELECT %s FROM foo', join (', ', map { qq|"$_"| } 'aa' .. 'zz' ); |
706 | my $tree = $sqlat->parse($sql); |
707 | |
708 | is_deeply( $tree, [ |
709 | [ |
710 | "SELECT", |
711 | [ |
712 | [ |
713 | "LIST", |
714 | [ |
715 | map { [ "LITERAL", [ qq|"$_"| ] ] } ('aa' .. 'zz') |
716 | ] |
717 | ] |
718 | ] |
719 | ], |
720 | [ |
721 | "FROM", |
722 | [ |
723 | [ |
724 | "LITERAL", |
725 | [ |
726 | "foo" |
727 | ] |
728 | ] |
729 | ] |
730 | ] |
731 | ], 'long list parsed correctly'); |
732 | |
733 | is( $sqlat->unparse($tree), $sql, 'roundtrip ok'); |
734 | } [], 'no recursion warnings on insane SQL'; |
735 | |
3be357b0 |
736 | done_testing; |