New exportable functions: is_literal_value($) and is_plain_value($)
[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("CASE WHEN FOO() > BAR()"), [
537   [
538     "-MISC",
539     [
540       [
541         "-LITERAL",
542         [
543           "CASE"
544         ]
545       ],
546       [
547         "-LITERAL",
548         [
549           "WHEN"
550         ]
551       ]
552     ]
553   ],
554   [
555     ">",
556     [
557       [
558         "FOO",
559         [
560           [
561             "-PAREN",
562             []
563           ]
564         ]
565       ],
566       [
567         "BAR",
568         [
569           [
570             "-PAREN",
571             []
572           ]
573         ]
574       ]
575     ]
576   ]
577 ]);
578
579 is_deeply($sqlat->parse("SELECT [me].[id], ROW_NUMBER ( ) OVER (ORDER BY (SELECT 1)) AS [rno__row__index] FROM bar"), [
580   [
581     "SELECT",
582     [
583       [
584         "-LIST",
585         [
586           [
587             "-LITERAL",
588             [
589               "[me].[id]"
590             ]
591           ],
592           [
593             "AS",
594             [
595               [
596                 "ROW_NUMBER() OVER",
597                 [
598                   [
599                     "-PAREN",
600                     [
601                       [
602                         "ORDER BY",
603                         [
604                           [
605                             "-PAREN",
606                             [
607                               [
608                                 "SELECT",
609                                 [
610                                   [
611                                     "-LITERAL",
612                                     [
613                                       1
614                                     ]
615                                   ]
616                                 ]
617                               ]
618                             ]
619                           ]
620                         ]
621                       ]
622                     ]
623                   ]
624                 ]
625               ],
626               [
627                 "-LITERAL",
628                 [
629                   "[rno__row__index]"
630                 ]
631               ]
632             ]
633           ]
634         ]
635       ]
636     ]
637   ],
638   [
639     "FROM",
640     [
641       [
642         "-LITERAL",
643         [
644           "bar"
645         ]
646       ]
647     ]
648   ]
649 ]);
650
651
652 is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
653   [
654     "SELECT",
655     [
656       [
657         "-LIST",
658         [
659           [
660             "-LITERAL",
661             [
662               "x"
663             ]
664           ],
665           [
666             "-LITERAL",
667             [
668               "y"
669             ]
670           ]
671         ]
672       ]
673     ]
674   ],
675   [
676     "FROM",
677     [
678       [
679         "-LITERAL",
680         [
681           "foo"
682         ]
683       ]
684     ]
685   ],
686   [
687     "WHERE",
688     [
689       [
690         "IN",
691         [
692           [
693             "-LITERAL",
694             [
695               "x"
696             ]
697           ],
698           [
699             "-PAREN",
700             [
701               [
702                 "-LIST",
703                 [
704                   [
705                     "-PLACEHOLDER",
706                     [
707                       "?"
708                     ]
709                   ],
710                   [
711                     "-PLACEHOLDER",
712                     [
713                       "?"
714                     ]
715                   ],
716                   [
717                     "-PLACEHOLDER",
718                     [
719                       "?"
720                     ]
721                   ],
722                   [
723                     "-PLACEHOLDER",
724                     [
725                       "?"
726                     ]
727                   ]
728                 ]
729               ]
730             ]
731           ]
732         ]
733       ]
734     ]
735   ]
736 ], 'Lists parsed correctly');
737
738 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'), [
739   [
740     "SELECT",
741     [
742       [
743         "-LITERAL",
744         [
745           "foo"
746         ]
747       ]
748     ]
749   ],
750   [
751     "FROM",
752     [
753       [
754         "-LITERAL",
755         [
756           "bar"
757         ]
758       ]
759     ]
760   ],
761   [
762     "ORDER BY",
763     [
764       [
765         "-LIST",
766         [
767           [
768             "-DESC",
769             [
770               [
771                 "-MISC",
772                 [
773                   [
774                     "-LITERAL",
775                     [
776                       "x"
777                     ]
778                   ],
779                   [
780                     "-LITERAL",
781                     [
782                       "+"
783                     ]
784                   ],
785                   [
786                     "-PLACEHOLDER",
787                     [
788                       "?"
789                     ]
790                   ]
791                 ]
792               ],
793             ]
794           ],
795           [
796             "-LITERAL",
797             [
798               "oomph"
799             ]
800           ],
801           [
802             "-DESC",
803             [
804               [
805                 "-MISC",
806                 [
807                   [
808                     "-LITERAL",
809                     [
810                       "y"
811                     ]
812                   ],
813                   [
814                     "-LITERAL",
815                     [
816                       "-"
817                     ]
818                   ],
819                   [
820                     "-PLACEHOLDER",
821                     [
822                       "?"
823                     ]
824                   ],
825                 ]
826               ],
827             ]
828           ],
829           [
830             "-LITERAL",
831             [
832               "unf"
833             ]
834           ],
835           [
836             "-ASC",
837             [
838               [
839                 "-MISC",
840                 [
841                   [
842                     "-LITERAL",
843                     [
844                       "baz.g"
845                     ]
846                   ],
847                   [
848                     "-LITERAL",
849                     [
850                       "/"
851                     ]
852                   ],
853                   [
854                     "-PLACEHOLDER",
855                     [
856                       "?"
857                     ]
858                   ],
859                 ]
860               ],
861             ]
862           ],
863           [
864             "-DESC",
865             [
866               [
867                 "-MISC",
868                 [
869                   [
870                     "-LITERAL",
871                     [
872                       "buzz"
873                     ]
874                   ],
875                   [
876                     "-LITERAL",
877                     [
878                       "*"
879                     ]
880                   ],
881                   [
882                     "-LITERAL",
883                     [
884                       0
885                     ]
886                   ]
887                 ]
888               ]
889             ]
890           ],
891           [
892             "-DESC",
893             [
894               [
895                 "LIKE",
896                 [
897                   [
898                     "-LITERAL",
899                     [
900                       "foo"
901                     ]
902                   ],
903                   [
904                     "-PLACEHOLDER",
905                     [
906                       "?"
907                     ]
908                   ],
909                 ],
910               ],
911             ]
912           ],
913           [
914             "-ASC",
915             [
916               [
917                 "-LITERAL",
918                 [
919                   "ickk"
920                 ]
921               ]
922             ]
923           ]
924         ]
925       ]
926     ]
927   ]
928 ], 'Crazy ORDER BY parsed correctly');
929
930 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 z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
931   [
932     "-LITERAL",
933     [
934       "META"
935     ]
936   ],
937   [
938     "SELECT",
939     [
940       [
941         "-MISC",
942         [
943           [
944             "-LITERAL",
945             [
946               "*"
947             ]
948           ],
949           [
950             "-LITERAL",
951             [
952               "*"
953             ]
954           ]
955         ]
956       ]
957     ]
958   ],
959   [
960     "FROM",
961     [
962       [
963         "-MISC",
964         [
965           [
966             "-PAREN",
967             [
968               [
969                 "SELECT",
970                 [
971                   [
972                     "-LIST",
973                     [
974                       [
975                         "-LITERAL",
976                         [
977                           "*"
978                         ]
979                       ],
980                       []
981                     ]
982                   ]
983                 ]
984               ],
985               [
986                 "FROM",
987                 [
988                   [
989                     "-MISC",
990                     [
991                       [
992                         "-LITERAL",
993                         [
994                           "foobar"
995                         ]
996                       ],
997                       [
998                         "-LITERAL",
999                         [
1000                           "baz"
1001                         ]
1002                       ],
1003                       [
1004                         "-LITERAL",
1005                         [
1006                           "buzz"
1007                         ]
1008                       ]
1009                     ]
1010                   ]
1011                 ]
1012               ]
1013             ]
1014           ],
1015           [
1016             "-LITERAL",
1017             [
1018               "foo"
1019             ]
1020           ],
1021           [
1022             "-LITERAL",
1023             [
1024               "bar"
1025             ]
1026           ]
1027         ]
1028       ]
1029     ]
1030   ],
1031   [
1032     "WHERE",
1033     [
1034       [
1035         "AND",
1036         [
1037           [
1038             "NOT",
1039             []
1040           ],
1041           [
1042             "NOT",
1043             []
1044           ],
1045           [
1046             "NOT EXISTS",
1047             [
1048               [
1049                 "-PAREN",
1050                 [
1051                   [
1052                     "SELECT",
1053                     [
1054                       [
1055                         "-LIST",
1056                         [
1057                           [
1058                             "-LITERAL",
1059                             [
1060                               "'cr"
1061                             ]
1062                           ],
1063                           [
1064                             "-LITERAL",
1065                             [
1066                               "ap'"
1067                             ]
1068                           ]
1069                         ]
1070                       ]
1071                     ]
1072                   ]
1073                 ]
1074               ]
1075             ]
1076           ],
1077           [
1078             "-MISC",
1079             [
1080               [
1081                 "=",
1082                 [
1083                   [
1084                     "-LITERAL",
1085                     [
1086                       "foo.a"
1087                     ]
1088                   ],
1089                   [
1090                     "-PLACEHOLDER",
1091                     [
1092                       "?"
1093                     ]
1094                   ],
1095                 ],
1096               ],
1097               [
1098                 "-LITERAL",
1099                 [
1100                   "STUFF"
1101                 ]
1102               ],
1103             ],
1104           ],
1105           [
1106             'moar',
1107             [
1108               [
1109                 '-PAREN',
1110                 [
1111                   [
1112                     '-LITERAL',
1113                     [
1114                       'stuff'
1115                     ]
1116                  ]
1117                 ]
1118               ]
1119             ]
1120           ],
1121           [
1122             "NOT",
1123             [
1124               [
1125                 "-PAREN",
1126                 [
1127                   [
1128                     "LIKE",
1129                     [
1130                       [
1131                         "-LITERAL",
1132                         [
1133                           "foo.b"
1134                         ]
1135                       ],
1136                       [
1137                         "-LITERAL",
1138                         [
1139                           "'station'"
1140                         ]
1141                       ]
1142                     ]
1143                   ]
1144                 ]
1145               ]
1146             ]
1147           ],
1148           [
1149             "=",
1150             [
1151               [
1152                 "-LITERAL",
1153                 [
1154                   "x"
1155                 ]
1156               ],
1157               [
1158                 "-LITERAL",
1159                 [
1160                   "y"
1161                 ]
1162               ]
1163             ]
1164           ],
1165           [
1166             'IN',
1167             [
1168               [
1169                 '-LITERAL',
1170                 [
1171                   'z',
1172                 ],
1173               ],
1174               [
1175                 '-PAREN',
1176                 [
1177                   [
1178                     '-PAREN',
1179                     [
1180                       [
1181                         '-LIST',
1182                         [
1183                           [
1184                             '-LITERAL',
1185                             [
1186                               '1'
1187                             ]
1188                           ],
1189                           [
1190                             '-LITERAL',
1191                             [
1192                               '2'
1193                             ]
1194                           ],
1195                         ],
1196                       ],
1197                     ],
1198                   ],
1199                 ],
1200               ],
1201             ],
1202           ],
1203           [
1204             "=",
1205             [
1206               [
1207                 "-LITERAL",
1208                 [
1209                   "a"
1210                 ]
1211               ],
1212               [
1213                 "-LITERAL",
1214                 [
1215                   "b"
1216                 ]
1217               ]
1218             ]
1219           ]
1220         ]
1221       ]
1222     ]
1223   ],
1224   [
1225     "GROUP BY",
1226     [
1227       [
1228         "-LIST",
1229         [
1230           [],
1231           []
1232         ]
1233       ]
1234     ]
1235   ],
1236   [
1237     "ORDER BY",
1238     [
1239       [
1240         "-LIST",
1241         [
1242           [
1243             "-ASC",
1244             [
1245               [
1246                 "-MISC",
1247                 [
1248                   [
1249                     "-LITERAL",
1250                     [
1251                       "x"
1252                     ]
1253                   ],
1254                   [
1255                     "-LITERAL",
1256                     [
1257                       "x1"
1258                     ]
1259                   ],
1260                   [
1261                     "-LITERAL",
1262                     [
1263                       "x2"
1264                     ]
1265                   ],
1266                   [
1267                     "-LITERAL",
1268                     [
1269                       "y"
1270                     ]
1271                   ]
1272                 ]
1273               ],
1274             ],
1275           ],
1276           [
1277                 "-DESC",
1278                 [
1279                   [
1280                     "-MISC",
1281                     [
1282                       [
1283                         "-DESC",
1284                         [
1285                           [
1286                             "max",
1287                             [
1288                               [
1289                                 "-PAREN",
1290                                 [
1291                                   [
1292                                     "-LITERAL",
1293                                     [
1294                                       "y"
1295                                     ]
1296                                   ]
1297                                 ]
1298                               ]
1299                             ],
1300                           ]
1301                         ]
1302                       ],
1303                       [
1304                         "-LITERAL",
1305                         [
1306                           "x"
1307                         ]
1308                       ],
1309                   [
1310                     "-LITERAL",
1311                     [
1312                       "z"
1313                     ]
1314                   ]
1315                 ]
1316               ]
1317             ]
1318           ]
1319         ]
1320       ]
1321     ]
1322   ]
1323 ], 'Deliberately malformed SQL parsed "correctly"');
1324
1325
1326 # test for recursion warnings on huge selectors
1327 my @lst = ('AA' .. 'zz');
1328 #@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
1329 warnings_are {
1330   my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
1331   my $tree = $sqlat->parse($sql);
1332
1333   is_deeply( $tree, [
1334     [
1335       "SELECT",
1336       [
1337         [
1338           "-LIST",
1339           [
1340             (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1341             (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
1342             (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
1343           ]
1344         ]
1345       ]
1346     ],
1347     [
1348       "FROM",
1349       [
1350         [
1351           "-LITERAL",
1352           [
1353             "foo"
1354           ]
1355         ]
1356       ]
1357     ]
1358   ], 'long list parsed correctly');
1359
1360   is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
1361 } [], 'no recursion warnings on insane SQL';
1362
1363 done_testing;