[dbsrgits/SQL-Abstract.git] / t / 11parser.t

use strict;
use warnings;

use Test::More;
use Test::Deep;
use Test::Warn;
use SQL::Abstract::Tree;

my $sqlat = SQL::Abstract::Tree->new;

cmp_deeply($sqlat->parse("SELECT a, b.*, * FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    [
      "SELECT",
      [
        [
          "LIST",
          [
            [
              "LITERAL",
              [
                "a"
              ]
            ],
            [
              "LITERAL",
              [
                "b.*"
              ]
            ],
            [
              "LITERAL",
              [
                "*"
              ]
            ]
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "LITERAL",
          [
            "foo"
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'simple statement parsed correctly');

cmp_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) WHERE foo.a =1 and foo.b LIKE 'station'"), [
  [
    [
      "SELECT",
      [
        [
          "LITERAL",
          [
            "*"
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "PAREN",
          [
            [
              [
                "SELECT",
                [
                  [
                    "LITERAL",
                    [
                      "*"
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "LITERAL",
                    [
                      "foobar"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "=",
            [
              [
                "LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "LITERAL",
                [
                  1
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'subquery statement parsed correctly');

cmp_deeply($sqlat->parse("SELECT * FROM lolz WHERE ( foo.a =1 ) and foo.b LIKE 'station'"), [
  [
    [
      "SELECT",
      [
        [
          "LITERAL",
          [
            "*"
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "LITERAL",
          [
            "lolz"
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "PAREN",
            [
              [
                "=",
                [
                  [
                    "LITERAL",
                    [
                      "foo.a"
                    ]
                  ],
                  [
                    "LITERAL",
                    [
                      1
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "LIKE",
            [
              [
                "LITERAL",
                [
                  "foo.b"
                ]
              ],
              [
                "LITERAL",
                [
                  "'station'"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'simple statement with parens in where parsed correctly');

cmp_deeply($sqlat->parse( "SELECT [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype] FROM [users_roles] [me] JOIN [roles] [role] ON [role].[id] = [me].[role_id] JOIN [roles_permissions] [role_permissions] ON [role_permissions].[role_id] = [role].[id] JOIN [permissions] [permission] ON [permission].[id] = [role_permissions].[permission_id] JOIN [permissionscreens] [permission_screens] ON [permission_screens].[permission_id] = [permission].[id] JOIN [screens] [screen] ON [screen].[id] = [permission_screens].[screen_id] WHERE ( [me].[user_id] = ? ) GROUP BY [screen].[id], [screen].[name], [screen].[section_id], [screen].[xtype]"), [
  [
    [
      [
        [
          [
            [
              [
                [
                  [
                    [
                      [
                        [
                          [
                            "SELECT",
                            [
                              [
                                "LIST",
                                [
                                  [
                                    "LITERAL",
                                    [
                                      "[screen].[id]"
                                    ]
                                  ],
                                  [
                                    "LITERAL",
                                    [
                                      "[screen].[name]"
                                    ]
                                  ],
                                  [
                                    "LITERAL",
                                    [
                                      "[screen].[section_id]"
                                    ]
                                  ],
                                  [
                                    "LITERAL",
                                    [
                                      "[screen].[xtype]"
                                    ]
                                  ]
                                ]
                              ]
                            ]
                          ],
                          [
                            "FROM",
                            [
                              [
                                [
                                  "LITERAL",
                                  [
                                    "[users_roles]"
                                  ]
                                ],
                                [
                                  "LITERAL",
                                  [
                                    "[me]"
                                  ]
                                ]
                              ]
                            ]
                          ]
                        ],
                        [
                          "JOIN",
                          [
                            [
                              [
                                "LITERAL",
                                [
                                  "[roles]"
                                ]
                              ],
                              [
                                "LITERAL",
                                [
                                  "[role]"
                                ]
                              ]
                            ]
                          ]
                        ]
                      ],
                      [
                        "ON",
                        [
                          [
                            "=",
                            [
                              [
                                "LITERAL",
                                [
                                  "[role].[id]"
                                ]
                              ],
                              [
                                "LITERAL",
                                [
                                  "[me].[role_id]"
                                ]
                              ]
                            ]
                          ]
                        ]
                      ]
                    ],
                    [
                      "JOIN",
                      [
                        [
                          [
                            "LITERAL",
                            [
                              "[roles_permissions]"
                            ]
                          ],
                          [
                            "LITERAL",
                            [
                              "[role_permissions]"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ],
                  [
                    "ON",
                    [
                      [
                        "=",
                        [
                          [
                            "LITERAL",
                            [
                              "[role_permissions].[role_id]"
                            ]
                          ],
                          [
                            "LITERAL",
                            [
                              "[role].[id]"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ],
                [
                  "JOIN",
                  [
                    [
                      [
                        "LITERAL",
                        [
                          "[permissions]"
                        ]
                      ],
                      [
                        "LITERAL",
                        [
                          "[permission]"
                        ]
                      ]
                    ]
                  ]
                ]
              ],
              [
                "ON",
                [
                  [
                    "=",
                    [
                      [
                        "LITERAL",
                        [
                          "[permission].[id]"
                        ]
                      ],
                      [
                        "LITERAL",
                        [
                          "[role_permissions].[permission_id]"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ],
            [
              "JOIN",
              [
                [
                  [
                    "LITERAL",
                    [
                      "[permissionscreens]"
                    ]
                  ],
                  [
                    "LITERAL",
                    [
                      "[permission_screens]"
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "ON",
            [
              [
                "=",
                [
                  [
                    "LITERAL",
                    [
                      "[permission_screens].[permission_id]"
                    ]
                  ],
                  [
                    "LITERAL",
                    [
                      "[permission].[id]"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ],
        [
          "JOIN",
          [
            [
              [
                "LITERAL",
                [
                  "[screens]"
                ]
              ],
              [
                "LITERAL",
                [
                  "[screen]"
                ]
              ]
            ]
          ]
        ]
      ],
      [
        "ON",
        [
          [
            "=",
            [
              [
                "LITERAL",
                [
                  "[screen].[id]"
                ]
              ],
              [
                "LITERAL",
                [
                  "[permission_screens].[screen_id]"
                ]
              ]
            ]
          ]
        ]
      ]
    ],
    [
      "WHERE",
      [
        [
          "PAREN",
          [
            [
              "=",
              [
                [
                  "LITERAL",
                  [
                    "[me].[user_id]"
                  ]
                ],
                [
                  "PLACEHOLDER",
                  [
                    "?"
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "LIST",
        [
          [
            "LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ]
], 'real life statement 1 parsed correctly');

cmp_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
  [
    [
      "SELECT",
      [
        [
          "LIST",
          [
            [
              "LITERAL",
              [
                "x"
              ]
            ],
            [
              "LITERAL",
              [
                "y"
              ]
            ]
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "LITERAL",
          [
            "foo"
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "IN",
        [
          [
            "LITERAL",
            [
              "x"
            ]
          ],
          [
            "PAREN",
            [
              [
                "LIST",
                [
                  [
                    "PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "PLACEHOLDER",
                    [
                      "?"
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Lists parsed correctly');

# test for recursion warnings on huge selectors
warnings_are {
  my $sql = sprintf 'SELECT %s FROM foo', join (', ',  map { qq|"$_"| } 'aa' .. 'zz' );
  my $tree = $sqlat->parse($sql);

  is_deeply( $tree, [
    [
      "SELECT",
      [
        [
          "LIST",
          [
            map { [ "LITERAL", [ qq|"$_"| ] ] } ('aa' .. 'zz')
          ]
        ]
      ]
    ],
    [
      "FROM",
      [
        [
          "LITERAL",
          [
            "foo"
          ]
        ]
      ]
    ]
  ], 'long list parsed correctly');

  is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
} [], 'no recursion warnings on insane SQL';

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