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