[scpubgit/Q-Branch.git] / t / 11unparse.t

use strict;
use warnings;

use Test::More;
use Test::Deep;
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');

done_testing;
Commit	Line	Data
d49e5323	1	use strict;
	2	use warnings;
	3
3be357b0	4	use Test::More;
408cbda3	5	use Test::Deep;
d49e5323	6	use SQL::Abstract::Tree;
d49e5323	7
408cbda3	8	my $sqlat = SQL::Abstract::Tree->new;
54750c7a	9
257ecc8a	10	cmp_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	98	cmp_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	192	cmp_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	268	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]"), [
	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	614	cmp_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	702	done_testing;