[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("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 a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
  [
    "-LITERAL",
    [
      "META"
    ]
  ],
  [
    "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"
                ]
              ],
              [
                "-MISC",
                [
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "STUFF"
                    ]
                  ]
                ],
              ],
              [
                'moar',
                [
                  [
                    '-PAREN',
                    [
                      [
                        '-LITERAL',
                        [
                          'stuff'
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "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",
                    [
                      [
                        "-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
e2a120ce	803	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 a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
6f30911f	804	[
	805	"-LITERAL",
	806	[
	807	"META"
	808	]
	809	],
6f2a5b66	810	[
	811	"SELECT",
	812	[
	813	[
1ec9b9e3	814	"-MISC",
6f2a5b66	815	[
	816	[
	817	"-LITERAL",
	818	[
	819	"*"
	820	]
1ec9b9e3	821	],
	822	[
	823	"-LITERAL",
	824	[
	825	"*"
	826	]
6f2a5b66	827	]
	828	]
	829	]
	830	]
	831	],
	832	[
	833	"FROM",
	834	[
	835	[
	836	"-MISC",
	837	[
	838	[
	839	"-PAREN",
	840	[
	841	[
	842	"SELECT",
	843	[
	844	[
	845	"-LIST",
	846	[
	847	[
	848	"-LITERAL",
	849	[
	850	"*"
	851	]
	852	],
	853	[]
	854	]
	855	]
	856	]
	857	],
	858	[
	859	"FROM",
	860	[
	861	[
	862	"-MISC",
	863	[
	864	[
	865	"-LITERAL",
	866	[
	867	"foobar"
	868	]
	869	],
	870	[
	871	"-LITERAL",
	872	[
	873	"baz"
	874	]
	875	],
	876	[
	877	"-LITERAL",
	878	[
	879	"buzz"
	880	]
	881	]
	882	]
	883	]
	884	]
	885	]
	886	]
	887	],
	888	[
	889	"-LITERAL",
	890	[
891	"foo"
892	]
893	],
894	[
895	"-LITERAL",
896	[
897	"bar"
898	]
899	]
900	]
901	]
902	]
903	],
904	[
905	"WHERE",
906	[
907	[
908	"AND",
909	[
910	[
911	"NOT",
912	[]
913	],
914	[
915	"NOT",
916	[]
917	],
918	[
919	"NOT EXISTS",
920	[
921	[
922	"-PAREN",
923	[
924	[
925	"SELECT",
926	[
927	[
928	"-LIST",
929	[
930	[
931	"-LITERAL",
932	[
933	"'cr"
934	]
935	],
936	[
937	"-LITERAL",
938	[
939	"ap'"
940	]
941	]
942	]
943	]
944	]
945	]
946	]
947	]
948	]
949	],
950	[
951	"=",
952	[
953	[
954	"-LITERAL",
955	[
956	"foo.a"
957	]
958	],
959	[
6f30911f	960	"-MISC",
6f2a5b66	961	[
6f30911f	962	[
	963	"-PLACEHOLDER",
	964	[
	965	"?"
	966	]
	967	],
	968	[
	969	"-LITERAL",
	970	[
	971	"STUFF"
	972	]
	973	]
	974	],
e2a120ce	975	],
	976	[
	977	'moar',
	978	[
	979	[
	980	'-PAREN',
	981	[
	982	[
	983	'-LITERAL',
	984	[
	985	'stuff'
	986	]
	987	]
	988	]
	989	]
	990	]
6f2a5b66	991	]
	992	]
	993	],
	994	[
	995	"NOT",
	996	[
	997	[
	998	"-PAREN",
	999	[
	1000	[
	1001	"LIKE",
	1002	[
	1003	[
	1004	"-LITERAL",
	1005	[
	1006	"foo.b"
	1007	]
	1008	],
	1009	[
	1010	"-LITERAL",
	1011	[
	1012	"'station'"
	1013	]
	1014	]
	1015	]
	1016	]
	1017	]
	1018	]
	1019	]
	1020	],
	1021	[
	1022	"=",
	1023	[
	1024	[
	1025	"-LITERAL",
	1026	[
	1027	"x"
	1028	]
	1029	],
	1030	[
	1031	"-LITERAL",
	1032	[
	1033	"y"
	1034	]
	1035	]
	1036	]
	1037	],
	1038	[
	1039	"=",
	1040	[
	1041	[
	1042	"-LITERAL",
	1043	[
	1044	"a"
	1045	]
	1046	],
	1047	[
	1048	"-LITERAL",
	1049	[
	1050	"b"
	1051	]
	1052	]
	1053	]
	1054	]
1055	]
1056	]
1057	]
1058	],
1059	[
1060	"GROUP BY",
1061	[
1062	[
1063	"-LIST",
1064	[
1065	[],
1066	[]
1067	]
1068	]
1069	]
1070	],
1071	[
1072	"ORDER BY",
1073	[
1074	[
1075	"-LIST",
1076	[
1077	[
1ec9b9e3	1078	"-ASC",
6f2a5b66	1079	[
	1080	[
	1081	"-MISC",
	1082	[
	1083	[
1ec9b9e3	1084	"-LITERAL",
6f2a5b66	1085	[
1ec9b9e3	1086	"x"
6f2a5b66	1087	]
	1088	],
	1089	[
	1090	"-LITERAL",
	1091	[
1ec9b9e3	1092	"x1"
6f2a5b66	1093	]
	1094	],
	1095	[
	1096	"-LITERAL",
	1097	[
1ec9b9e3	1098	"x2"
6f2a5b66	1099	]
	1100	],
	1101	[
	1102	"-LITERAL",
	1103	[
1ec9b9e3	1104	"y"
	1105	]
	1106	]
	1107	]
	1108	],
	1109	],
	1110	],
	1111	[
	1112	"max",
	1113	[
	1114	[
	1115	"-DESC",
	1116	[
	1117	[
	1118	"-MISC",
	1119	[
	1120	[
6f30911f	1121	"-MISC",
1ec9b9e3	1122	[
1ec9b9e3	1123	[
6f30911f	1124	"-DESC",
1ec9b9e3	1125	[
1ec9b9e3	1126	[
6f30911f	1127	"-PAREN",
1ec9b9e3	1128	[
6f30911f	1129	[
	1130	"-LITERAL",
	1131	[
	1132	"y"
	1133	]
	1134	]
1ec9b9e3	1135	]
	1136	]
	1137	]
6f30911f	1138	],
	1139	[
	1140	"-LITERAL",
	1141	[
	1142	"x"
	1143	]
	1144	],
1ec9b9e3	1145	]
	1146	],
	1147	[
	1148	"-LITERAL",
	1149	[
	1150	"z"
	1151	]
	1152	]
6f2a5b66	1153	]
	1154	]
	1155	]
	1156	]
	1157	]
	1158	]
	1159	]
	1160	]
	1161	]
	1162	]
	1163	], 'Deliberately malformed SQL parsed "correctly"');
	1164
	1165
0f9a26cb	1166	# test for recursion warnings on huge selectors
ad591616	1167	my @lst = ('AA' .. 'zz');
ad591616	1168	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	1169	warnings_are {
ad591616	1170	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	1171	my $tree = $sqlat->parse($sql);
	1172
	1173	is_deeply( $tree, [
	1174	[
	1175	"SELECT",
	1176	[
	1177	[
6f2a5b66	1178	"-LIST",
0f9a26cb	1179	[
ad591616	1180	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	1181	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	1182	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	1183	]
	1184	]
	1185	]
	1186	],
	1187	[
	1188	"FROM",
	1189	[
	1190	[
6f2a5b66	1191	"-LITERAL",
0f9a26cb	1192	[
	1193	"foo"
	1194	]
	1195	]
	1196	]
	1197	]
	1198	], 'long list parsed correctly');
	1199
	1200	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	1201	} [], 'no recursion warnings on insane SQL';
	1202
3be357b0	1203	done_testing;