Promote AS to a binop of sorts
[scpubgit/Q-Branch.git] / t / 11parser.t
1 use strict;
2 use warnings;
3
4 use Test::More;
5 use Test::Deep;
6 use Test::Warn;
7 use SQL::Abstract::Tree;
8
9 my $sqlat = SQL::Abstract::Tree->new;
10
11 cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
12   [
13     [
14       "SELECT",
15       [
16         [
17           "LIST",
18           [
19             [
20               "LITERAL",
21               [
22                 "a"
23               ]
24             ],
25             [
26               "LITERAL",
27               [
28                 "b.*"
29               ]
30             ],
31             [
32               "LITERAL",
33               [
34                 "*"
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');
98
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');
192
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');
268
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');
614
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');
702
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
736 done_testing;