No tabs, no trailers
[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 LIKE ? 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                     "-PLACEHOLDER",
671                     [
672                       "?"
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                     "-PLACEHOLDER",
705                     [
706                       "?"
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                     "-PLACEHOLDER",
739                     [
740                       "?"
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                 "LIKE",
780                 [
781                   [
782                     "-LITERAL",
783                     [
784                       "foo"
785                     ]
786                   ],
787                   [
788                     "-PLACEHOLDER",
789                     [
790                       "?"
791                     ]
792                   ],
793                 ],
794               ],
795             ]
796           ],
797           [
798             "-ASC",
799             [
800               [
801                 "-LITERAL",
802                 [
803                   "ickk"
804                 ]
805               ]
806             ]
807           ]
808         ]
809       ]
810     ]
811   ]
812 ], 'Crazy ORDER BY parsed correctly');
813
814 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"), [
815   [
816     "-LITERAL",
817     [
818       "META"
819     ]
820   ],
821   [
822     "SELECT",
823     [
824       [
825         "-MISC",
826         [
827           [
828             "-LITERAL",
829             [
830               "*"
831             ]
832           ],
833           [
834             "-LITERAL",
835             [
836               "*"
837             ]
838           ]
839         ]
840       ]
841     ]
842   ],
843   [
844     "FROM",
845     [
846       [
847         "-MISC",
848         [
849           [
850             "-PAREN",
851             [
852               [
853                 "SELECT",
854                 [
855                   [
856                     "-LIST",
857                     [
858                       [
859                         "-LITERAL",
860                         [
861                           "*"
862                         ]
863                       ],
864                       []
865                     ]
866                   ]
867                 ]
868               ],
869               [
870                 "FROM",
871                 [
872                   [
873                     "-MISC",
874                     [
875                       [
876                         "-LITERAL",
877                         [
878                           "foobar"
879                         ]
880                       ],
881                       [
882                         "-LITERAL",
883                         [
884                           "baz"
885                         ]
886                       ],
887                       [
888                         "-LITERAL",
889                         [
890                           "buzz"
891                         ]
892                       ]
893                     ]
894                   ]
895                 ]
896               ]
897             ]
898           ],
899           [
900             "-LITERAL",
901             [
902               "foo"
903             ]
904           ],
905           [
906             "-LITERAL",
907             [
908               "bar"
909             ]
910           ]
911         ]
912       ]
913     ]
914   ],
915   [
916     "WHERE",
917     [
918       [
919         "AND",
920         [
921           [
922             "NOT",
923             []
924           ],
925           [
926             "NOT",
927             []
928           ],
929           [
930             "NOT EXISTS",
931             [
932               [
933                 "-PAREN",
934                 [
935                   [
936                     "SELECT",
937                     [
938                       [
939                         "-LIST",
940                         [
941                           [
942                             "-LITERAL",
943                             [
944                               "'cr"
945                             ]
946                           ],
947                           [
948                             "-LITERAL",
949                             [
950                               "ap'"
951                             ]
952                           ]
953                         ]
954                       ]
955                     ]
956                   ]
957                 ]
958               ]
959             ]
960           ],
961           [
962             "-MISC",
963             [
964               [
965                 "=",
966                 [
967                   [
968                     "-LITERAL",
969                     [
970                       "foo.a"
971                     ]
972                   ],
973                   [
974                     "-PLACEHOLDER",
975                     [
976                       "?"
977                     ]
978                   ],
979                 ],
980               ],
981               [
982                 "-LITERAL",
983                 [
984                   "STUFF"
985                 ]
986               ],
987             ],
988           ],
989           [
990             'moar',
991             [
992               [
993                 '-PAREN',
994                 [
995                   [
996                     '-LITERAL',
997                     [
998                       'stuff'
999                     ]
1000                  ]
1001                 ]
1002               ]
1003             ]
1004           ],
1005           [
1006             "NOT",
1007             [
1008               [
1009                 "-PAREN",
1010                 [
1011                   [
1012                     "LIKE",
1013                     [
1014                       [
1015                         "-LITERAL",
1016                         [
1017                           "foo.b"
1018                         ]
1019                       ],
1020                       [
1021                         "-LITERAL",
1022                         [
1023                           "'station'"
1024                         ]
1025                       ]
1026                     ]
1027                   ]
1028                 ]
1029               ]
1030             ]
1031           ],
1032           [
1033             "=",
1034             [
1035               [
1036                 "-LITERAL",
1037                 [
1038                   "x"
1039                 ]
1040               ],
1041               [
1042                 "-LITERAL",
1043                 [
1044                   "y"
1045                 ]
1046               ]
1047             ]
1048           ],
1049           [
1050             "=",
1051             [
1052               [
1053                 "-LITERAL",
1054                 [
1055                   "a"
1056                 ]
1057               ],
1058               [
1059                 "-LITERAL",
1060                 [
1061                   "b"
1062                 ]
1063               ]
1064             ]
1065           ]
1066         ]
1067       ]
1068     ]
1069   ],
1070   [
1071     "GROUP BY",
1072     [
1073       [
1074         "-LIST",
1075         [
1076           [],
1077           []
1078         ]
1079       ]
1080     ]
1081   ],
1082   [
1083     "ORDER BY",
1084     [
1085       [
1086         "-LIST",
1087         [
1088           [
1089             "-ASC",
1090             [
1091               [
1092                 "-MISC",
1093                 [
1094                   [
1095                     "-LITERAL",
1096                     [
1097                       "x"
1098                     ]
1099                   ],
1100                   [
1101                     "-LITERAL",
1102                     [
1103                       "x1"
1104                     ]
1105                   ],
1106                   [
1107                     "-LITERAL",
1108                     [
1109                       "x2"
1110                     ]
1111                   ],
1112                   [
1113                     "-LITERAL",
1114                     [
1115                       "y"
1116                     ]
1117                   ]
1118                 ]
1119               ],
1120             ],
1121           ],
1122           [
1123                 "-DESC",
1124                 [
1125                   [
1126                     "-MISC",
1127                     [
1128                       [
1129                         "-DESC",
1130                         [
1131                           [
1132                             "max",
1133                             [
1134                               [
1135                                 "-PAREN",
1136                                 [
1137                                   [
1138                                     "-LITERAL",
1139                                     [
1140                                       "y"
1141                                     ]
1142                                   ]
1143                                 ]
1144                               ]
1145                             ],
1146                           ]
1147                         ]
1148                       ],
1149                       [
1150                         "-LITERAL",
1151                         [
1152                           "x"
1153                         ]
1154                       ],
1155                   [
1156                     "-LITERAL",
1157                     [
1158                       "z"
1159                     ]
1160                   ]
1161                 ]
1162               ]
1163             ]
1164           ]
1165         ]
1166       ]
1167     ]
1168   ]
1169 ], 'Deliberately malformed SQL parsed "correctly"');
1170
1171
1172 # test for recursion warnings on huge selectors
1173 my @lst = ('AA' .. 'zz');
1174 #@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
1175 warnings_are {
1176   my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
1177   my $tree = $sqlat->parse($sql);
1178
1179   is_deeply( $tree, [
1180     [
1181       "SELECT",
1182       [
1183         [
1184           "-LIST",
1185           [
1186             (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1187             (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
1188             (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1189           ]
1190         ]
1191       ]
1192     ],
1193     [
1194       "FROM",
1195       [
1196         [
1197           "-LITERAL",
1198           [
1199             "foo"
1200           ]
1201         ]
1202       ]
1203     ]
1204   ], 'long list parsed correctly');
1205
1206   is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
1207 } [], 'no recursion warnings on insane SQL';
1208
1209 done_testing;