8735ffedb0d568b5222a9ba09f152aae2f0a56a6
[scpubgit/Q-Branch.git] / t / 11parser.t
1 use strict;
2 use warnings;
3
4 use Test::More;
5 use Test::Warn;
6 use SQL::Abstract::Tree;
7
8 my $sqlat = SQL::Abstract::Tree->new;
9 is_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
10   [
11     "SELECT",
12     [
13       [
14         "-LIST",
15         [
16           [
17             "-LITERAL",
18             [
19               "a"
20             ]
21           ],
22           [
23             "-LITERAL",
24             [
25               "b.*"
26             ]
27           ],
28           [
29             "-LITERAL",
30             [
31               "*"
32             ]
33           ]
34         ]
35       ]
36     ]
37   ],
38   [
39     "FROM",
40     [
41       [
42         "-LITERAL",
43         [
44           "foo"
45         ]
46       ]
47     ]
48   ],
49   [
50     "WHERE",
51     [
52       [
53         "AND",
54         [
55           [
56             "=",
57             [
58               [
59                 "-LITERAL",
60                 [
61                   "foo.a"
62                 ]
63               ],
64               [
65                 "-LITERAL",
66                 [
67                   1
68                 ]
69               ]
70             ]
71           ],
72           [
73             "LIKE",
74             [
75               [
76                 "-LITERAL",
77                 [
78                   "foo.b"
79                 ]
80               ],
81               [
82                 "-LITERAL",
83                 [
84                   "'station'"
85                 ]
86               ]
87             ]
88           ]
89         ]
90       ]
91     ]
92   ]
93 ], 'simple statement parsed correctly');
94
95 is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
96   [
97     "SELECT",
98     [
99       [
100         "-LITERAL",
101         [
102           "*"
103         ]
104       ]
105     ]
106   ],
107   [
108     "FROM",
109     [
110       [
111         "-MISC",
112         [
113           [
114             "-PAREN",
115             [
116               [
117                 "SELECT",
118                 [
119                   [
120                     "-LITERAL",
121                     [
122                       "*"
123                     ]
124                   ]
125                 ]
126               ],
127               [
128                 "FROM",
129                 [
130                   [
131                     "-LITERAL",
132                     [
133                       "foobar"
134                     ]
135                   ]
136                 ]
137               ]
138             ]
139           ],
140           [
141             "-LITERAL",
142             [
143               "foo"
144             ]
145           ]
146         ]
147       ]
148     ]
149   ],
150   [
151     "WHERE",
152     [
153       [
154         "AND",
155         [
156           [
157             "=",
158             [
159               [
160                 "-LITERAL",
161                 [
162                   "foo.a"
163                 ]
164               ],
165               [
166                 "-LITERAL",
167                 [
168                   1
169                 ]
170               ]
171             ]
172           ],
173           [
174             "LIKE",
175             [
176               [
177                 "-LITERAL",
178                 [
179                   "foo.b"
180                 ]
181               ],
182               [
183                 "-LITERAL",
184                 [
185                   "'station'"
186                 ]
187               ]
188             ]
189           ]
190         ]
191       ]
192     ]
193   ]
194 ], 'subquery statement parsed correctly');
195
196 is_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]"), [
197   [
198     "SELECT",
199     [
200       [
201         "-LIST",
202         [
203           [
204             "-LITERAL",
205             [
206               "[screen].[id]"
207             ]
208           ],
209           [
210             "-LITERAL",
211             [
212               "[screen].[name]"
213             ]
214           ],
215           [
216             "-LITERAL",
217             [
218               "[screen].[section_id]"
219             ]
220           ],
221           [
222             "-LITERAL",
223             [
224               "[screen].[xtype]"
225             ]
226           ]
227         ]
228       ]
229     ]
230   ],
231   [
232     "FROM",
233     [
234       [
235         "-MISC",
236         [
237           [
238             "-LITERAL",
239             [
240               "[users_roles]"
241             ]
242           ],
243           [
244             "-LITERAL",
245             [
246               "[me]"
247             ]
248           ]
249         ]
250       ]
251     ]
252   ],
253   [
254     "JOIN",
255     [
256       [
257         "-MISC",
258         [
259           [
260             "-LITERAL",
261             [
262               "[roles]"
263             ]
264           ],
265           [
266             "-LITERAL",
267             [
268               "[role]"
269             ]
270           ]
271         ]
272       ]
273     ]
274   ],
275   [
276     "ON",
277     [
278       [
279         "=",
280         [
281           [
282             "-LITERAL",
283             [
284               "[role].[id]"
285             ]
286           ],
287           [
288             "-LITERAL",
289             [
290               "[me].[role_id]"
291             ]
292           ]
293         ]
294       ]
295     ]
296   ],
297   [
298     "JOIN",
299     [
300       [
301         "-MISC",
302         [
303           [
304             "-LITERAL",
305             [
306               "[roles_permissions]"
307             ]
308           ],
309           [
310             "-LITERAL",
311             [
312               "[role_permissions]"
313             ]
314           ]
315         ]
316       ]
317     ]
318   ],
319   [
320     "ON",
321     [
322       [
323         "=",
324         [
325           [
326             "-LITERAL",
327             [
328               "[role_permissions].[role_id]"
329             ]
330           ],
331           [
332             "-LITERAL",
333             [
334               "[role].[id]"
335             ]
336           ]
337         ]
338       ]
339     ]
340   ],
341   [
342     "JOIN",
343     [
344       [
345         "-MISC",
346         [
347           [
348             "-LITERAL",
349             [
350               "[permissions]"
351             ]
352           ],
353           [
354             "-LITERAL",
355             [
356               "[permission]"
357             ]
358           ]
359         ]
360       ]
361     ]
362   ],
363   [
364     "ON",
365     [
366       [
367         "=",
368         [
369           [
370             "-LITERAL",
371             [
372               "[permission].[id]"
373             ]
374           ],
375           [
376             "-LITERAL",
377             [
378               "[role_permissions].[permission_id]"
379             ]
380           ]
381         ]
382       ]
383     ]
384   ],
385   [
386     "JOIN",
387     [
388       [
389         "-MISC",
390         [
391           [
392             "-LITERAL",
393             [
394               "[permissionscreens]"
395             ]
396           ],
397           [
398             "-LITERAL",
399             [
400               "[permission_screens]"
401             ]
402           ]
403         ]
404       ]
405     ]
406   ],
407   [
408     "ON",
409     [
410       [
411         "=",
412         [
413           [
414             "-LITERAL",
415             [
416               "[permission_screens].[permission_id]"
417             ]
418           ],
419           [
420             "-LITERAL",
421             [
422               "[permission].[id]"
423             ]
424           ]
425         ]
426       ]
427     ]
428   ],
429   [
430     "JOIN",
431     [
432       [
433         "-MISC",
434         [
435           [
436             "-LITERAL",
437             [
438               "[screens]"
439             ]
440           ],
441           [
442             "-LITERAL",
443             [
444               "[screen]"
445             ]
446           ]
447         ]
448       ]
449     ]
450   ],
451   [
452     "ON",
453     [
454       [
455         "=",
456         [
457           [
458             "-LITERAL",
459             [
460               "[screen].[id]"
461             ]
462           ],
463           [
464             "-LITERAL",
465             [
466               "[permission_screens].[screen_id]"
467             ]
468           ]
469         ]
470       ]
471     ]
472   ],
473   [
474     "WHERE",
475     [
476       [
477         "-PAREN",
478         [
479           [
480             "=",
481             [
482               [
483                 "-LITERAL",
484                 [
485                   "[me].[user_id]"
486                 ]
487               ],
488               [
489                 "-PLACEHOLDER",
490                 [
491                   "?"
492                 ]
493               ]
494             ]
495           ]
496         ]
497       ]
498     ]
499   ],
500   [
501     "GROUP BY",
502     [
503       [
504         "-LIST",
505         [
506           [
507             "-LITERAL",
508             [
509               "[screen].[id]"
510             ]
511           ],
512           [
513             "-LITERAL",
514             [
515               "[screen].[name]"
516             ]
517           ],
518           [
519             "-LITERAL",
520             [
521               "[screen].[section_id]"
522             ]
523           ],
524           [
525             "-LITERAL",
526             [
527               "[screen].[xtype]"
528             ]
529           ]
530         ]
531       ]
532     ]
533   ]
534 ], 'real life statement 1 parsed correctly');
535
536 is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
537   [
538     "SELECT",
539     [
540       [
541         "-LIST",
542         [
543           [
544             "-LITERAL",
545             [
546               "x"
547             ]
548           ],
549           [
550             "-LITERAL",
551             [
552               "y"
553             ]
554           ]
555         ]
556       ]
557     ]
558   ],
559   [
560     "FROM",
561     [
562       [
563         "-LITERAL",
564         [
565           "foo"
566         ]
567       ]
568     ]
569   ],
570   [
571     "WHERE",
572     [
573       [
574         "IN",
575         [
576           [
577             "-LITERAL",
578             [
579               "x"
580             ]
581           ],
582           [
583             "-PAREN",
584             [
585               [
586                 "-LIST",
587                 [
588                   [
589                     "-PLACEHOLDER",
590                     [
591                       "?"
592                     ]
593                   ],
594                   [
595                     "-PLACEHOLDER",
596                     [
597                       "?"
598                     ]
599                   ],
600                   [
601                     "-PLACEHOLDER",
602                     [
603                       "?"
604                     ]
605                   ],
606                   [
607                     "-PLACEHOLDER",
608                     [
609                       "?"
610                     ]
611                   ]
612                 ]
613               ]
614             ]
615           ]
616         ]
617       ]
618     ]
619   ]
620 ], 'Lists parsed correctly');
621
622 is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo DESC, ickk ASC'), [
623   [
624     "SELECT",
625     [
626       [
627         "-LITERAL",
628         [
629           "foo"
630         ]
631       ]
632     ]
633   ],
634   [
635     "FROM",
636     [
637       [
638         "-LITERAL",
639         [
640           "bar"
641         ]
642       ]
643     ]
644   ],
645   [
646     "ORDER BY",
647     [
648       [
649         "-LIST",
650         [
651           [
652             "-DESC",
653             [
654               [
655                 "-MISC",
656                 [
657                   [
658                     "-LITERAL",
659                     [
660                       "x"
661                     ]
662                   ],
663                   [
664                     "-LITERAL",
665                     [
666                       "+"
667                     ]
668                   ]
669                 ]
670               ],
671               [
672                 "-PLACEHOLDER",
673                 [
674                   "?"
675                 ]
676               ]
677             ]
678           ],
679           [
680             "-LITERAL",
681             [
682               "oomph"
683             ]
684           ],
685           [
686             "-DESC",
687             [
688               [
689                 "-MISC",
690                 [
691                   [
692                     "-LITERAL",
693                     [
694                       "y"
695                     ]
696                   ],
697                   [
698                     "-LITERAL",
699                     [
700                       "-"
701                     ]
702                   ]
703                 ]
704               ],
705               [
706                 "-PLACEHOLDER",
707                 [
708                   "?"
709                 ]
710               ]
711             ]
712           ],
713           [
714             "-LITERAL",
715             [
716               "unf"
717             ]
718           ],
719           [
720             "-ASC",
721             [
722               [
723                 "-MISC",
724                 [
725                   [
726                     "-LITERAL",
727                     [
728                       "baz.g"
729                     ]
730                   ],
731                   [
732                     "-LITERAL",
733                     [
734                       "/"
735                     ]
736                   ]
737                 ]
738               ],
739               [
740                 "-PLACEHOLDER",
741                 [
742                   "?"
743                 ]
744               ]
745             ]
746           ],
747           [
748             "-DESC",
749             [
750               [
751                 "-MISC",
752                 [
753                   [
754                     "-LITERAL",
755                     [
756                       "buzz"
757                     ]
758                   ],
759                   [
760                     "-LITERAL",
761                     [
762                       "*"
763                     ]
764                   ],
765                   [
766                     "-LITERAL",
767                     [
768                       0
769                     ]
770                   ]
771                 ]
772               ]
773             ]
774           ],
775           [
776             "-DESC",
777             [
778               [
779                 "-LITERAL",
780                 [
781                   "foo"
782                 ]
783               ]
784             ]
785           ],
786           [
787             "-ASC",
788             [
789               [
790                 "-LITERAL",
791                 [
792                   "ickk"
793                 ]
794               ]
795             ]
796           ]
797         ]
798       ]
799     ]
800   ]
801 ], 'Crazy ORDER BY parsed correctly');
802
803 is_deeply( $sqlat->parse("META SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? STUFF moar(stuff) and not (foo.b LIKE 'station') and x = y and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
804   [
805     "-LITERAL",
806     [
807       "META"
808     ]
809   ],
810   [
811     "SELECT",
812     [
813       [
814         "-MISC",
815         [
816           [
817             "-LITERAL",
818             [
819               "*"
820             ]
821           ],
822           [
823             "-LITERAL",
824             [
825               "*"
826             ]
827           ]
828         ]
829       ]
830     ]
831   ],
832   [
833     "FROM",
834     [
835       [
836         "-MISC",
837         [
838           [
839             "-PAREN",
840             [
841               [
842                 "SELECT",
843                 [
844                   [
845                     "-LIST",
846                     [
847                       [
848                         "-LITERAL",
849                         [
850                           "*"
851                         ]
852                       ],
853                       []
854                     ]
855                   ]
856                 ]
857               ],
858               [
859                 "FROM",
860                 [
861                   [
862                     "-MISC",
863                     [
864                       [
865                         "-LITERAL",
866                         [
867                           "foobar"
868                         ]
869                       ],
870                       [
871                         "-LITERAL",
872                         [
873                           "baz"
874                         ]
875                       ],
876                       [
877                         "-LITERAL",
878                         [
879                           "buzz"
880                         ]
881                       ]
882                     ]
883                   ]
884                 ]
885               ]
886             ]
887           ],
888           [
889             "-LITERAL",
890             [
891               "foo"
892             ]
893           ],
894           [
895             "-LITERAL",
896             [
897               "bar"
898             ]
899           ]
900         ]
901       ]
902     ]
903   ],
904   [
905     "WHERE",
906     [
907       [
908         "AND",
909         [
910           [
911             "NOT",
912             []
913           ],
914           [
915             "NOT",
916             []
917           ],
918           [
919             "NOT EXISTS",
920             [
921               [
922                 "-PAREN",
923                 [
924                   [
925                     "SELECT",
926                     [
927                       [
928                         "-LIST",
929                         [
930                           [
931                             "-LITERAL",
932                             [
933                               "'cr"
934                             ]
935                           ],
936                           [
937                             "-LITERAL",
938                             [
939                               "ap'"
940                             ]
941                           ]
942                         ]
943                       ]
944                     ]
945                   ]
946                 ]
947               ]
948             ]
949           ],
950           [
951             "=",
952             [
953               [
954                 "-LITERAL",
955                 [
956                   "foo.a"
957                 ]
958               ],
959               [
960                 "-MISC",
961                 [
962                   [
963                     "-PLACEHOLDER",
964                     [
965                       "?"
966                     ]
967                   ],
968                   [
969                     "-LITERAL",
970                     [
971                       "STUFF"
972                     ]
973                   ]
974                 ],
975               ],
976               [
977                 'moar',
978                 [
979                   [
980                     '-PAREN',
981                     [
982                       [
983                         '-LITERAL',
984                         [
985                           'stuff'
986                         ]
987                       ]
988                     ]
989                   ]
990                 ]
991               ]
992             ]
993           ],
994           [
995             "NOT",
996             [
997               [
998                 "-PAREN",
999                 [
1000                   [
1001                     "LIKE",
1002                     [
1003                       [
1004                         "-LITERAL",
1005                         [
1006                           "foo.b"
1007                         ]
1008                       ],
1009                       [
1010                         "-LITERAL",
1011                         [
1012                           "'station'"
1013                         ]
1014                       ]
1015                     ]
1016                   ]
1017                 ]
1018               ]
1019             ]
1020           ],
1021           [
1022             "=",
1023             [
1024               [
1025                 "-LITERAL",
1026                 [
1027                   "x"
1028                 ]
1029               ],
1030               [
1031                 "-LITERAL",
1032                 [
1033                   "y"
1034                 ]
1035               ]
1036             ]
1037           ],
1038           [
1039             "=",
1040             [
1041               [
1042                 "-LITERAL",
1043                 [
1044                   "a"
1045                 ]
1046               ],
1047               [
1048                 "-LITERAL",
1049                 [
1050                   "b"
1051                 ]
1052               ]
1053             ]
1054           ]
1055         ]
1056       ]
1057     ]
1058   ],
1059   [
1060     "GROUP BY",
1061     [
1062       [
1063         "-LIST",
1064         [
1065           [],
1066           []
1067         ]
1068       ]
1069     ]
1070   ],
1071   [
1072     "ORDER BY",
1073     [
1074       [
1075         "-LIST",
1076         [
1077           [
1078             "-ASC",
1079             [
1080               [
1081                 "-MISC",
1082                 [
1083                   [
1084                     "-LITERAL",
1085                     [
1086                       "x"
1087                     ]
1088                   ],
1089                   [
1090                     "-LITERAL",
1091                     [
1092                       "x1"
1093                     ]
1094                   ],
1095                   [
1096                     "-LITERAL",
1097                     [
1098                       "x2"
1099                     ]
1100                   ],
1101                   [
1102                     "-LITERAL",
1103                     [
1104                       "y"
1105                     ]
1106                   ]
1107                 ]
1108               ],
1109             ],
1110           ],
1111           [
1112             "max",
1113             [
1114               [
1115                 "-DESC",
1116                 [
1117                   [
1118                     "-MISC",
1119                     [
1120                       [
1121                         "-MISC",
1122                         [
1123                           [
1124                             "-DESC",
1125                             [
1126                               [
1127                                 "-PAREN",
1128                                 [
1129                                   [
1130                                     "-LITERAL",
1131                                     [
1132                                       "y"
1133                                     ]
1134                                   ]
1135                                 ]
1136                               ]
1137                             ]
1138                           ],
1139                           [
1140                             "-LITERAL",
1141                             [
1142                               "x"
1143                             ]
1144                           ],
1145                         ]
1146                       ],
1147                       [
1148                         "-LITERAL",
1149                         [
1150                           "z"
1151                         ]
1152                       ]
1153                     ]
1154                   ]
1155                 ]
1156               ]
1157             ]
1158           ]
1159         ]
1160       ]
1161     ]
1162   ]
1163 ], 'Deliberately malformed SQL parsed "correctly"');
1164
1165
1166 # test for recursion warnings on huge selectors
1167 my @lst = ('AA' .. 'zz');
1168 #@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
1169 warnings_are {
1170   my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
1171   my $tree = $sqlat->parse($sql);
1172
1173   is_deeply( $tree, [
1174     [
1175       "SELECT",
1176       [
1177         [
1178           "-LIST",
1179           [
1180             (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1181             (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
1182             (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1183           ]
1184         ]
1185       ]
1186     ],
1187     [
1188       "FROM",
1189       [
1190         [
1191           "-LITERAL",
1192           [
1193             "foo"
1194           ]
1195         ]
1196       ]
1197     ]
1198   ], 'long list parsed correctly');
1199
1200   is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
1201 } [], 'no recursion warnings on insane SQL';
1202
1203 done_testing;