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

use strict;
use warnings;

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

my $sqlat = SQL::Abstract::Tree->new;
is_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');

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

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]"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[screen].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[name]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[section_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[screen].[xtype]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[users_roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[me].[role_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[roles_permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[role_permissions].[role_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissions]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[role_permissions].[permission_id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "[permissionscreens]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission_screens]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "ON",
    [
      [
        "=",
        [
          [
            "-LITERAL",
            [
              "[permission_screens].[permission_id]"
            ]
          ],
          [
            "-LITERAL",
            [
              "[permission].[id]"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "JOIN",
    [
      [
        "-MISC",
        [
          [
            "-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');

is_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');

is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo DESC, ickk ASC'), [
  [
    "SELECT",
    [
      [
        "-LITERAL",
        [
          "foo"
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "bar"
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "+"
                    ]
                  ]
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "oomph"
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "-"
                    ]
                  ]
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "unf"
            ]
          ],
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "baz.g"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "/"
                    ]
                  ]
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ],
          [
            "-DESC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "buzz"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "*"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      0
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-DESC",
            [
              [
                "-LITERAL",
                [
                  "foo"
                ]
              ]
            ]
          ],
          [
            "-ASC",
            [
              [
                "-LITERAL",
                [
                  "ickk"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Crazy ORDER BY parsed correctly');


is_deeply($sqlat->parse("SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? 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"), [
  [
    "SELECT",
    [
      [
        "-MISC",
        [
          [
            "-LITERAL",
            [
              "*"
            ]
          ],
          [
            "-LITERAL",
            [
              "*"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-MISC",
        [
          [
            "-PAREN",
            [
              [
                "SELECT",
                [
                  [
                    "-LIST",
                    [
                      [
                        "-LITERAL",
                        [
                          "*"
                        ]
                      ],
                      []
                    ]
                  ]
                ]
              ],
              [
                "FROM",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-LITERAL",
                        [
                          "foobar"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "baz"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "buzz"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-LITERAL",
            [
              "foo"
            ]
          ],
          [
            "-LITERAL",
            [
              "bar"
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "WHERE",
    [
      [
        "AND",
        [
          [
            "NOT",
            []
          ],
          [
            "NOT",
            []
          ],
          [
            "NOT EXISTS",
            [
              [
                "-PAREN",
                [
                  [
                    "SELECT",
                    [
                      [
                        "-LIST",
                        [
                          [
                            "-LITERAL",
                            [
                              "'cr"
                            ]
                          ],
                          [
                            "-LITERAL",
                            [
                              "ap'"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "foo.a"
                ]
              ],
              [
                "-PLACEHOLDER",
                [
                  "?"
                ]
              ]
            ]
          ],
          [
            "NOT",
            [
              [
                "-PAREN",
                [
                  [
                    "LIKE",
                    [
                      [
                        "-LITERAL",
                        [
                          "foo.b"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "'station'"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "a"
                ]
              ],
              [
                "-LITERAL",
                [
                  "b"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [],
          []
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x1"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x2"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ]
                ]
              ],
            ],
          ],
          [
            "max",
            [
              [
                "-DESC",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-DESC",
                        [
                          [
                            "-PAREN",
                            [
                              [
                                "-LITERAL",
                                [
                                  "y"
                                ]
                              ]
                            ]
                          ]
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "x"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "z"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ]
], 'Deliberately malformed SQL parsed "correctly"');


# test for recursion warnings on huge selectors
my @lst = ('AA' .. 'zz');
#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
warnings_are {
  my $sql = sprintf 'SELECT %s FROM foo', join (', ',  (map { qq|( "$_" )| } @lst), (map { qq|"$_"| } @lst), (map { qq|"$_", ( "$_" )| } @lst) );
  my $tree = $sqlat->parse($sql);

  is_deeply( $tree, [
    [
      "SELECT",
      [
        [
          "-LIST",
          [
            (map { [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ] } @lst),
            (map { [ -LITERAL => [ qq|"$_"| ] ], [ -PAREN => [ [ -LITERAL => [ qq|"$_"| ] ] ] ] } @lst),
          ]
        ]
      ]
    ],
    [
      "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;
0f9a26cb	5	use Test::Warn;
d49e5323	6	use SQL::Abstract::Tree;
d49e5323	7
408cbda3	8	my $sqlat = SQL::Abstract::Tree->new;
6f2a5b66	9	is_deeply($sqlat->parse("SELECT a, b., FROM foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	10	[
6f2a5b66	11	"SELECT",
958f1ce4	12	[
958f1ce4	13	[
6f2a5b66	14	"-LIST",
958f1ce4	15	[
958f1ce4	16	[
6f2a5b66	17	"-LITERAL",
958f1ce4	18	[
6f2a5b66	19	"a"
	20	]
	21	],
	22	[
	23	"-LITERAL",
958f1ce4	24	[
6f2a5b66	25	"b.*"
	26	]
	27	],
	28	[
	29	"-LITERAL",
958f1ce4	30	[
6f2a5b66	31	"*"
958f1ce4	32	]
	33	]
	34	]
	35	]
6f2a5b66	36	]
	37	],
	38	[
	39	"FROM",
958f1ce4	40	[
958f1ce4	41	[
6f2a5b66	42	"-LITERAL",
958f1ce4	43	[
6f2a5b66	44	"foo"
958f1ce4	45	]
	46	]
	47	]
	48	],
	49	[
	50	"WHERE",
	51	[
	52	[
	53	"AND",
	54	[
	55	[
	56	"=",
	57	[
	58	[
6f2a5b66	59	"-LITERAL",
958f1ce4	60	[
	61	"foo.a"
	62	]
	63	],
	64	[
6f2a5b66	65	"-LITERAL",
958f1ce4	66	[
	67	1
	68	]
	69	]
	70	]
	71	],
	72	[
	73	"LIKE",
	74	[
	75	[
6f2a5b66	76	"-LITERAL",
958f1ce4	77	[
	78	"foo.b"
	79	]
	80	],
	81	[
6f2a5b66	82	"-LITERAL",
958f1ce4	83	[
	84	"'station'"
	85	]
	86	]
	87	]
	88	]
	89	]
	90	]
	91	]
	92	]
	93	], 'simple statement parsed correctly');
54750c7a	94
6f2a5b66	95	is_deeply($sqlat->parse( "SELECT * FROM (SELECT * FROM foobar) foo WHERE foo.a =1 and foo.b LIKE 'station'"), [
958f1ce4	96	[
6f2a5b66	97	"SELECT",
958f1ce4	98	[
958f1ce4	99	[
6f2a5b66	100	"-LITERAL",
958f1ce4	101	[
6f2a5b66	102	"*"
958f1ce4	103	]
958f1ce4	104	]
6f2a5b66	105	]
	106	],
	107	[
	108	"FROM",
958f1ce4	109	[
958f1ce4	110	[
6f2a5b66	111	"-MISC",
958f1ce4	112	[
958f1ce4	113	[
6f2a5b66	114	"-PAREN",
958f1ce4	115	[
	116	[
	117	"SELECT",
	118	[
	119	[
6f2a5b66	120	"-LITERAL",
958f1ce4	121	[
	122	"*"
	123	]
	124	]
	125	]
	126	],
	127	[
	128	"FROM",
	129	[
	130	[
6f2a5b66	131	"-LITERAL",
958f1ce4	132	[
	133	"foobar"
	134	]
	135	]
	136	]
	137	]
	138	]
6f2a5b66	139	],
	140	[
	141	"-LITERAL",
	142	[
	143	"foo"
	144	]
958f1ce4	145	]
	146	]
	147	]
	148	]
	149	],
	150	[
	151	"WHERE",
	152	[
	153	[
	154	"AND",
	155	[
	156	[
	157	"=",
	158	[
	159	[
6f2a5b66	160	"-LITERAL",
958f1ce4	161	[
	162	"foo.a"
	163	]
	164	],
	165	[
6f2a5b66	166	"-LITERAL",
958f1ce4	167	[
	168	1
	169	]
	170	]
	171	]
	172	],
	173	[
	174	"LIKE",
	175	[
	176	[
6f2a5b66	177	"-LITERAL",
958f1ce4	178	[
	179	"foo.b"
	180	]
	181	],
	182	[
6f2a5b66	183	"-LITERAL",
958f1ce4	184	[
	185	"'station'"
	186	]
	187	]
	188	]
	189	]
	190	]
	191	]
	192	]
	193	]
	194	], 'subquery statement parsed correctly');
54750c7a	195
6f2a5b66	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]"), [
958f1ce4	197	[
6f2a5b66	198	"SELECT",
958f1ce4	199	[
958f1ce4	200	[
6f2a5b66	201	"-LIST",
958f1ce4	202	[
958f1ce4	203	[
6f2a5b66	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	]
958f1ce4	226	]
	227	]
	228	]
6f2a5b66	229	]
	230	],
	231	[
	232	"FROM",
958f1ce4	233	[
958f1ce4	234	[
6f2a5b66	235	"-MISC",
958f1ce4	236	[
958f1ce4	237	[
6f2a5b66	238	"-LITERAL",
	239	[
	240	"[users_roles]"
	241	]
	242	],
	243	[
	244	"-LITERAL",
	245	[
	246	"[me]"
	247	]
958f1ce4	248	]
	249	]
	250	]
	251	]
	252	],
	253	[
6f2a5b66	254	"JOIN",
958f1ce4	255	[
958f1ce4	256	[
6f2a5b66	257	"-MISC",
958f1ce4	258	[
958f1ce4	259	[
6f2a5b66	260	"-LITERAL",
958f1ce4	261	[
6f2a5b66	262	"[roles]"
958f1ce4	263	]
	264	],
	265	[
6f2a5b66	266	"-LITERAL",
958f1ce4	267	[
6f2a5b66	268	"[role]"
958f1ce4	269	]
	270	]
	271	]
	272	]
	273	]
6f2a5b66	274	],
958f1ce4	275	[
6f2a5b66	276	"ON",
958f1ce4	277	[
958f1ce4	278	[
6f2a5b66	279	"=",
958f1ce4	280	[
958f1ce4	281	[
6f2a5b66	282	"-LITERAL",
958f1ce4	283	[
6f2a5b66	284	"[role].[id]"
958f1ce4	285	]
	286	],
	287	[
6f2a5b66	288	"-LITERAL",
958f1ce4	289	[
6f2a5b66	290	"[me].[role_id]"
958f1ce4	291	]
958f1ce4	292	]
6f2a5b66	293	]
	294	]
	295	]
	296	],
	297	[
	298	"JOIN",
	299	[
	300	[
	301	"-MISC",
958f1ce4	302	[
958f1ce4	303	[
6f2a5b66	304	"-LITERAL",
958f1ce4	305	[
6f2a5b66	306	"[roles_permissions]"
958f1ce4	307	]
6f2a5b66	308	],
958f1ce4	309	[
6f2a5b66	310	"-LITERAL",
958f1ce4	311	[
6f2a5b66	312	"[role_permissions]"
958f1ce4	313	]
	314	]
	315	]
	316	]
6f2a5b66	317	]
	318	],
	319	[
	320	"ON",
958f1ce4	321	[
958f1ce4	322	[
6f2a5b66	323	"=",
958f1ce4	324	[
958f1ce4	325	[
6f2a5b66	326	"-LITERAL",
958f1ce4	327	[
6f2a5b66	328	"[role_permissions].[role_id]"
	329	]
	330	],
	331	[
	332	"-LITERAL",
	333	[
	334	"[role].[id]"
958f1ce4	335	]
	336	]
	337	]
	338	]
	339	]
	340	],
	341	[
6f2a5b66	342	"JOIN",
958f1ce4	343	[
958f1ce4	344	[
6f2a5b66	345	"-MISC",
958f1ce4	346	[
958f1ce4	347	[
6f2a5b66	348	"-LITERAL",
958f1ce4	349	[
6f2a5b66	350	"[permissions]"
958f1ce4	351	]
	352	],
	353	[
6f2a5b66	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",
958f1ce4	514	[
	515	"[screen].[name]"
	516	]
	517	],
	518	[
6f2a5b66	519	"-LITERAL",
958f1ce4	520	[
	521	"[screen].[section_id]"
	522	]
	523	],
	524	[
6f2a5b66	525	"-LITERAL",
958f1ce4	526	[
	527	"[screen].[xtype]"
	528	]
	529	]
	530	]
	531	]
	532	]
	533	]
	534	], 'real life statement 1 parsed correctly');
1bb3956e	535
6f2a5b66	536	is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
958f1ce4	537	[
6f2a5b66	538	"SELECT",
958f1ce4	539	[
958f1ce4	540	[
6f2a5b66	541	"-LIST",
958f1ce4	542	[
958f1ce4	543	[
6f2a5b66	544	"-LITERAL",
958f1ce4	545	[
6f2a5b66	546	"x"
	547	]
	548	],
	549	[
	550	"-LITERAL",
958f1ce4	551	[
6f2a5b66	552	"y"
958f1ce4	553	]
	554	]
	555	]
	556	]
6f2a5b66	557	]
	558	],
	559	[
	560	"FROM",
958f1ce4	561	[
958f1ce4	562	[
6f2a5b66	563	"-LITERAL",
958f1ce4	564	[
6f2a5b66	565	"foo"
958f1ce4	566	]
	567	]
	568	]
	569	],
	570	[
	571	"WHERE",
	572	[
	573	[
	574	"IN",
	575	[
	576	[
6f2a5b66	577	"-LITERAL",
958f1ce4	578	[
	579	"x"
	580	]
	581	],
	582	[
6f2a5b66	583	"-PAREN",
958f1ce4	584	[
958f1ce4	585	[
6f2a5b66	586	"-LIST",
958f1ce4	587	[
958f1ce4	588	[
6f2a5b66	589	"-PLACEHOLDER",
958f1ce4	590	[
	591	"?"
	592	]
	593	],
	594	[
6f2a5b66	595	"-PLACEHOLDER",
958f1ce4	596	[
	597	"?"
	598	]
	599	],
	600	[
6f2a5b66	601	"-PLACEHOLDER",
958f1ce4	602	[
	603	"?"
	604	]
	605	],
	606	[
6f2a5b66	607	"-PLACEHOLDER",
958f1ce4	608	[
	609	"?"
	610	]
	611	]
	612	]
	613	]
	614	]
	615	]
	616	]
	617	]
	618	]
	619	]
	620	], 'Lists parsed correctly');
7f2dd81e	621
1ec9b9e3	622	is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo 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	],
	671	[
	672	"-PLACEHOLDER",
	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	],
705	[
706	"-PLACEHOLDER",
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	],
739	[
740	"-PLACEHOLDER",
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	"-LITERAL",
780	[
781	"foo"
782	]
783	]
784	]
785	],
786	[
787	"-ASC",
788	[
789	[
790	"-LITERAL",
791	[
792	"ickk"
793	]
794	]
795	]
796	]
797	]
798	]
799	]
800	]
801	], 'Crazy ORDER BY parsed correctly');
802
803
6f2a5b66	804	is_deeply($sqlat->parse("SELECT * * FROM (SELECT *, FROM foobar baz buzz) foo bar WHERE NOT NOT NOT EXISTS (SELECT 'cr,ap') AND foo.a = ? 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"), [
	805	[
	806	"SELECT",
	807	[
	808	[
1ec9b9e3	809	"-MISC",
6f2a5b66	810	[
	811	[
	812	"-LITERAL",
	813	[
	814	"*"
	815	]
1ec9b9e3	816	],
	817	[
	818	"-LITERAL",
	819	[
	820	"*"
	821	]
6f2a5b66	822	]
	823	]
	824	]
	825	]
	826	],
	827	[
	828	"FROM",
	829	[
	830	[
	831	"-MISC",
	832	[
	833	[
	834	"-PAREN",
	835	[
	836	[
	837	"SELECT",
	838	[
	839	[
	840	"-LIST",
	841	[
	842	[
	843	"-LITERAL",
	844	[
	845	"*"
	846	]
	847	],
	848	[]
	849	]
	850	]
	851	]
	852	],
	853	[
	854	"FROM",
	855	[
	856	[
	857	"-MISC",
	858	[
	859	[
	860	"-LITERAL",
	861	[
	862	"foobar"
	863	]
	864	],
	865	[
	866	"-LITERAL",
	867	[
	868	"baz"
	869	]
	870	],
	871	[
	872	"-LITERAL",
	873	[
	874	"buzz"
	875	]
	876	]
	877	]
	878	]
	879	]
	880	]
	881	]
	882	],
	883	[
	884	"-LITERAL",
	885	[
886	"foo"
887	]
888	],
889	[
890	"-LITERAL",
891	[
892	"bar"
893	]
894	]
895	]
896	]
897	]
898	],
899	[
900	"WHERE",
901	[
902	[
903	"AND",
904	[
905	[
906	"NOT",
907	[]
908	],
909	[
910	"NOT",
911	[]
912	],
913	[
914	"NOT EXISTS",
915	[
916	[
917	"-PAREN",
918	[
919	[
920	"SELECT",
921	[
922	[
923	"-LIST",
924	[
925	[
926	"-LITERAL",
927	[
928	"'cr"
929	]
930	],
931	[
932	"-LITERAL",
933	[
934	"ap'"
935	]
936	]
937	]
938	]
939	]
940	]
941	]
942	]
943	]
944	],
945	[
946	"=",
947	[
948	[
949	"-LITERAL",
950	[
951	"foo.a"
952	]
953	],
954	[
955	"-PLACEHOLDER",
956	[
957	"?"
958	]
959	]
960	]
961	],
962	[
963	"NOT",
964	[
965	[
966	"-PAREN",
967	[
968	[
969	"LIKE",
970	[
971	[
972	"-LITERAL",
973	[
974	"foo.b"
975	]
976	],
977	[
978	"-LITERAL",
979	[
980	"'station'"
981	]
982	]
983	]
984	]
985	]
986	]
987	]
988	],
989	[
990	"=",
991	[
992	[
993	"-LITERAL",
994	[
995	"x"
996	]
997	],
998	[
999	"-LITERAL",
1000	[
1001	"y"
1002	]
1003	]
1004	]
1005	],
1006	[
1007	"=",
1008	[
1009	[
1010	"-LITERAL",
1011	[
1012	"a"
1013	]
1014	],
1015	[
1016	"-LITERAL",
1017	[
1018	"b"
1019	]
1020	]
1021	]
1022	]
1023	]
1024	]
1025	]
1026	],
1027	[
1028	"GROUP BY",
1029	[
1030	[
1031	"-LIST",
1032	[
1033	[],
1034	[]
1035	]
1036	]
1037	]
1038	],
1039	[
1040	"ORDER BY",
1041	[
1042	[
1043	"-LIST",
1044	[
1045	[
1ec9b9e3	1046	"-ASC",
6f2a5b66	1047	[
	1048	[
	1049	"-MISC",
	1050	[
	1051	[
1ec9b9e3	1052	"-LITERAL",
6f2a5b66	1053	[
1ec9b9e3	1054	"x"
6f2a5b66	1055	]
	1056	],
	1057	[
	1058	"-LITERAL",
	1059	[
1ec9b9e3	1060	"x1"
6f2a5b66	1061	]
	1062	],
	1063	[
	1064	"-LITERAL",
	1065	[
1ec9b9e3	1066	"x2"
6f2a5b66	1067	]
	1068	],
	1069	[
	1070	"-LITERAL",
	1071	[
1ec9b9e3	1072	"y"
	1073	]
	1074	]
	1075	]
	1076	],
	1077	],
	1078	],
	1079	[
	1080	"max",
	1081	[
	1082	[
	1083	"-DESC",
	1084	[
	1085	[
	1086	"-MISC",
	1087	[
	1088	[
	1089	"-DESC",
	1090	[
	1091	[
	1092	"-PAREN",
	1093	[
	1094	[
	1095	"-LITERAL",
	1096	[
	1097	"y"
	1098	]
	1099	]
	1100	]
	1101	]
	1102	]
	1103	],
	1104	[
	1105	"-LITERAL",
	1106	[
	1107	"x"
	1108	]
	1109	],
	1110	[
	1111	"-LITERAL",
	1112	[
	1113	"z"
	1114	]
	1115	]
6f2a5b66	1116	]
	1117	]
	1118	]
	1119	]
	1120	]
	1121	]
	1122	]
	1123	]
	1124	]
	1125	]
	1126	], 'Deliberately malformed SQL parsed "correctly"');
	1127
	1128
0f9a26cb	1129	# test for recursion warnings on huge selectors
ad591616	1130	my @lst = ('AA' .. 'zz');
ad591616	1131	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	1132	warnings_are {
ad591616	1133	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	1134	my $tree = $sqlat->parse($sql);
	1135
	1136	is_deeply( $tree, [
	1137	[
	1138	"SELECT",
	1139	[
	1140	[
6f2a5b66	1141	"-LIST",
0f9a26cb	1142	[
ad591616	1143	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	1144	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	1145	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	1146	]
	1147	]
	1148	]
	1149	],
	1150	[
	1151	"FROM",
	1152	[
	1153	[
6f2a5b66	1154	"-LITERAL",
0f9a26cb	1155	[
	1156	"foo"
	1157	]
	1158	]
	1159	]
	1160	]
	1161	], 'long list parsed correctly');
	1162
	1163	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	1164	} [], 'no recursion warnings on insane SQL';
	1165
3be357b0	1166	done_testing;