[scpubgit/Q-Branch.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("CASE WHEN FOO() > BAR()"), [
  [
    "-MISC",
    [
      [
        "-LITERAL",
        [
          "CASE"
        ]
      ],
      [
        "-LITERAL",
        [
          "WHEN"
        ]
      ]
    ]
  ],
  [
    ">",
    [
      [
        "FOO",
        [
          [
            "-PAREN",
            []
          ]
        ]
      ],
      [
        "BAR",
        [
          [
            "-PAREN",
            []
          ]
        ]
      ]
    ]
  ]
]);

is_deeply($sqlat->parse("SELECT [me].[id], ROW_NUMBER ( ) OVER (ORDER BY (SELECT 1)) AS [rno__row__index] FROM bar"), [
  [
    "SELECT",
    [
      [
        "-LIST",
        [
          [
            "-LITERAL",
            [
              "[me].[id]"
            ]
          ],
          [
            "AS",
            [
              [
                "ROW_NUMBER() OVER",
                [
                  [
                    "-PAREN",
                    [
                      [
                        "ORDER BY",
                        [
                          [
                            "-PAREN",
                            [
                              [
                                "SELECT",
                                [
                                  [
                                    "-LITERAL",
                                    [
                                      1
                                    ]
                                  ]
                                ]
                              ]
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ],
              [
                "-LITERAL",
                [
                  "[rno__row__index]"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "FROM",
    [
      [
        "-LITERAL",
        [
          "bar"
        ]
      ]
    ]
  ]
]);


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 LIKE ? 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",
            [
              [
                "LIKE",
                [
                  [
                    "-LITERAL",
                    [
                      "foo"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
            ]
          ],
          [
            "-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 z in ((1, 2)) 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'"
                            ]
                          ]
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "-MISC",
            [
              [
                "=",
                [
                  [
                    "-LITERAL",
                    [
                      "foo.a"
                    ]
                  ],
                  [
                    "-PLACEHOLDER",
                    [
                      "?"
                    ]
                  ],
                ],
              ],
              [
                "-LITERAL",
                [
                  "STUFF"
                ]
              ],
            ],
          ],
          [
            'moar',
            [
              [
                '-PAREN',
                [
                  [
                    '-LITERAL',
                    [
                      'stuff'
                    ]
                 ]
                ]
              ]
            ]
          ],
          [
            "NOT",
            [
              [
                "-PAREN",
                [
                  [
                    "LIKE",
                    [
                      [
                        "-LITERAL",
                        [
                          "foo.b"
                        ]
                      ],
                      [
                        "-LITERAL",
                        [
                          "'station'"
                        ]
                      ]
                    ]
                  ]
                ]
              ]
            ]
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "x"
                ]
              ],
              [
                "-LITERAL",
                [
                  "y"
                ]
              ]
            ]
          ],
          [
            'IN',
            [
              [
                '-LITERAL',
                [
                  'z',
                ],
              ],
              [
                '-PAREN',
                [
                  [
                    '-PAREN',
                    [
                      [
                        '-LIST',
                        [
                          [
                            '-LITERAL',
                            [
                              '1'
                            ]
                          ],
                          [
                            '-LITERAL',
                            [
                              '2'
                            ]
                          ],
                        ],
                      ],
                    ],
                  ],
                ],
              ],
            ],
          ],
          [
            "=",
            [
              [
                "-LITERAL",
                [
                  "a"
                ]
              ],
              [
                "-LITERAL",
                [
                  "b"
                ]
              ]
            ]
          ]
        ]
      ]
    ]
  ],
  [
    "GROUP BY",
    [
      [
        "-LIST",
        [
          [],
          []
        ]
      ]
    ]
  ],
  [
    "ORDER BY",
    [
      [
        "-LIST",
        [
          [
            "-ASC",
            [
              [
                "-MISC",
                [
                  [
                    "-LITERAL",
                    [
                      "x"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x1"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "x2"
                    ]
                  ],
                  [
                    "-LITERAL",
                    [
                      "y"
                    ]
                  ]
                ]
              ],
            ],
          ],
          [
                "-DESC",
                [
                  [
                    "-MISC",
                    [
                      [
                        "-DESC",
                        [
                          [
                            "max",
                            [
                              [
                                "-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
08e16360	536	is_deeply($sqlat->parse("CASE WHEN FOO() > BAR()"), [
	537	[
	538	"-MISC",
	539	[
	540	[
	541	"-LITERAL",
	542	[
	543	"CASE"
	544	]
	545	],
	546	[
	547	"-LITERAL",
	548	[
	549	"WHEN"
	550	]
	551	]
	552	]
	553	],
	554	[
	555	">",
	556	[
	557	[
	558	"FOO",
	559	[
	560	[
	561	"-PAREN",
	562	[]
	563	]
	564	]
	565	],
	566	[
	567	"BAR",
	568	[
	569	[
	570	"-PAREN",
	571	[]
	572	]
	573	]
	574	]
	575	]
	576	]
	577	]);
	578
4247c384	579	is_deeply($sqlat->parse("SELECT [me].[id], ROW_NUMBER ( ) OVER (ORDER BY (SELECT 1)) AS [rno__row__index] FROM bar"), [
	580	[
	581	"SELECT",
	582	[
	583	[
	584	"-LIST",
	585	[
	586	[
	587	"-LITERAL",
	588	[
	589	"[me].[id]"
	590	]
	591	],
	592	[
	593	"AS",
	594	[
	595	[
	596	"ROW_NUMBER() OVER",
	597	[
	598	[
	599	"-PAREN",
	600	[
	601	[
	602	"ORDER BY",
	603	[
	604	[
	605	"-PAREN",
	606	[
	607	[
	608	"SELECT",
	609	[
	610	[
	611	"-LITERAL",
	612	[
	613	1
	614	]
	615	]
	616	]
	617	]
	618	]
	619	]
	620	]
	621	]
	622	]
	623	]
	624	]
	625	],
	626	[
	627	"-LITERAL",
	628	[
	629	"[rno__row__index]"
	630	]
	631	]
	632	]
	633	]
	634	]
	635	]
	636	]
	637	],
	638	[
	639	"FROM",
	640	[
	641	[
	642	"-LITERAL",
643	[
644	"bar"
645	]
646	]
647	]
648	]
649	]);
650
08e16360	651
6f2a5b66	652	is_deeply($sqlat->parse("SELECT x, y FROM foo WHERE x IN (?, ?, ?, ?)"), [
958f1ce4	653	[
6f2a5b66	654	"SELECT",
958f1ce4	655	[
958f1ce4	656	[
6f2a5b66	657	"-LIST",
958f1ce4	658	[
958f1ce4	659	[
6f2a5b66	660	"-LITERAL",
958f1ce4	661	[
6f2a5b66	662	"x"
	663	]
	664	],
	665	[
	666	"-LITERAL",
958f1ce4	667	[
6f2a5b66	668	"y"
958f1ce4	669	]
	670	]
	671	]
	672	]
6f2a5b66	673	]
	674	],
	675	[
	676	"FROM",
958f1ce4	677	[
958f1ce4	678	[
6f2a5b66	679	"-LITERAL",
958f1ce4	680	[
6f2a5b66	681	"foo"
958f1ce4	682	]
	683	]
	684	]
	685	],
	686	[
	687	"WHERE",
	688	[
	689	[
	690	"IN",
	691	[
	692	[
6f2a5b66	693	"-LITERAL",
958f1ce4	694	[
	695	"x"
	696	]
	697	],
	698	[
6f2a5b66	699	"-PAREN",
958f1ce4	700	[
958f1ce4	701	[
6f2a5b66	702	"-LIST",
958f1ce4	703	[
958f1ce4	704	[
6f2a5b66	705	"-PLACEHOLDER",
958f1ce4	706	[
	707	"?"
	708	]
	709	],
	710	[
6f2a5b66	711	"-PLACEHOLDER",
958f1ce4	712	[
	713	"?"
	714	]
	715	],
	716	[
6f2a5b66	717	"-PLACEHOLDER",
958f1ce4	718	[
	719	"?"
	720	]
	721	],
	722	[
6f2a5b66	723	"-PLACEHOLDER",
958f1ce4	724	[
	725	"?"
	726	]
	727	]
	728	]
	729	]
	730	]
	731	]
	732	]
	733	]
	734	]
	735	]
	736	], 'Lists parsed correctly');
7f2dd81e	737
c84a4321	738	is_deeply($sqlat->parse('SELECT foo FROM bar ORDER BY x + ? DESC, oomph, y - ? DESC, unf, baz.g / ? ASC, buzz * 0 DESC, foo LIKE ? DESC, ickk ASC'), [
1ec9b9e3	739	[
	740	"SELECT",
	741	[
	742	[
	743	"-LITERAL",
	744	[
	745	"foo"
	746	]
	747	]
	748	]
	749	],
	750	[
	751	"FROM",
	752	[
	753	[
	754	"-LITERAL",
	755	[
	756	"bar"
	757	]
	758	]
	759	]
	760	],
	761	[
	762	"ORDER BY",
	763	[
	764	[
	765	"-LIST",
	766	[
	767	[
	768	"-DESC",
	769	[
	770	[
	771	"-MISC",
	772	[
	773	[
	774	"-LITERAL",
	775	[
	776	"x"
	777	]
	778	],
	779	[
	780	"-LITERAL",
	781	[
	782	"+"
	783	]
b4085a1a	784	],
	785	[
	786	"-PLACEHOLDER",
	787	[
	788	"?"
	789	]
1ec9b9e3	790	]
	791	]
	792	],
1ec9b9e3	793	]
	794	],
	795	[
	796	"-LITERAL",
	797	[
	798	"oomph"
	799	]
	800	],
	801	[
	802	"-DESC",
	803	[
	804	[
	805	"-MISC",
	806	[
	807	[
	808	"-LITERAL",
	809	[
	810	"y"
	811	]
	812	],
	813	[
	814	"-LITERAL",
	815	[
	816	"-"
	817	]
b4085a1a	818	],
	819	[
	820	"-PLACEHOLDER",
	821	[
	822	"?"
	823	]
	824	],
1ec9b9e3	825	]
1ec9b9e3	826	],
1ec9b9e3	827	]
	828	],
	829	[
	830	"-LITERAL",
	831	[
	832	"unf"
	833	]
	834	],
	835	[
	836	"-ASC",
	837	[
	838	[
	839	"-MISC",
	840	[
	841	[
	842	"-LITERAL",
	843	[
	844	"baz.g"
	845	]
	846	],
	847	[
	848	"-LITERAL",
	849	[
	850	"/"
	851	]
b4085a1a	852	],
	853	[
	854	"-PLACEHOLDER",
	855	[
	856	"?"
	857	]
	858	],
1ec9b9e3	859	]
1ec9b9e3	860	],
1ec9b9e3	861	]
	862	],
	863	[
	864	"-DESC",
	865	[
	866	[
	867	"-MISC",
	868	[
	869	[
	870	"-LITERAL",
	871	[
	872	"buzz"
	873	]
	874	],
	875	[
	876	"-LITERAL",
	877	[
	878	"*"
	879	]
	880	],
	881	[
	882	"-LITERAL",
	883	[
	884	0
	885	]
	886	]
	887	]
	888	]
	889	]
	890	],
	891	[
	892	"-DESC",
	893	[
	894	[
c84a4321	895	"LIKE",
1ec9b9e3	896	[
c84a4321	897	[
	898	"-LITERAL",
	899	[
	900	"foo"
	901	]
	902	],
	903	[
	904	"-PLACEHOLDER",
	905	[
	906	"?"
	907	]
	908	],
	909	],
	910	],
1ec9b9e3	911	]
	912	],
	913	[
	914	"-ASC",
	915	[
	916	[
	917	"-LITERAL",
	918	[
	919	"ickk"
	920	]
	921	]
	922	]
	923	]
	924	]
	925	]
	926	]
	927	]
	928	], 'Crazy ORDER BY parsed correctly');
	929
7d273452	930	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 z in ((1, 2)) and a = b and GROUP BY , ORDER BY x x1 x2 y asc, max(y) desc x z desc"), [
6f30911f	931	[
	932	"-LITERAL",
	933	[
	934	"META"
	935	]
	936	],
6f2a5b66	937	[
	938	"SELECT",
	939	[
	940	[
1ec9b9e3	941	"-MISC",
6f2a5b66	942	[
	943	[
	944	"-LITERAL",
	945	[
	946	"*"
	947	]
1ec9b9e3	948	],
	949	[
	950	"-LITERAL",
	951	[
	952	"*"
	953	]
6f2a5b66	954	]
	955	]
	956	]
	957	]
	958	],
	959	[
	960	"FROM",
	961	[
	962	[
	963	"-MISC",
	964	[
	965	[
	966	"-PAREN",
	967	[
	968	[
	969	"SELECT",
	970	[
	971	[
	972	"-LIST",
	973	[
	974	[
	975	"-LITERAL",
	976	[
	977	"*"
	978	]
	979	],
	980	[]
	981	]
	982	]
	983	]
	984	],
	985	[
	986	"FROM",
	987	[
	988	[
	989	"-MISC",
	990	[
	991	[
	992	"-LITERAL",
	993	[
	994	"foobar"
	995	]
	996	],
	997	[
	998	"-LITERAL",
	999	[
	1000	"baz"
	1001	]
	1002	],
	1003	[
	1004	"-LITERAL",
	1005	[
	1006	"buzz"
	1007	]
	1008	]
	1009	]
	1010	]
	1011	]
	1012	]
	1013	]
	1014	],
	1015	[
	1016	"-LITERAL",
	1017	[
1018	"foo"
1019	]
1020	],
1021	[
1022	"-LITERAL",
1023	[
1024	"bar"
1025	]
1026	]
1027	]
1028	]
1029	]
1030	],
1031	[
1032	"WHERE",
1033	[
1034	[
1035	"AND",
1036	[
1037	[
1038	"NOT",
1039	[]
1040	],
1041	[
1042	"NOT",
1043	[]
1044	],
1045	[
1046	"NOT EXISTS",
1047	[
1048	[
1049	"-PAREN",
1050	[
1051	[
1052	"SELECT",
1053	[
1054	[
1055	"-LIST",
1056	[
1057	[
1058	"-LITERAL",
1059	[
1060	"'cr"
1061	]
1062	],
1063	[
1064	"-LITERAL",
1065	[
1066	"ap'"
1067	]
1068	]
1069	]
1070	]
1071	]
1072	]
1073	]
1074	]
1075	]
1076	],
1077	[
b4085a1a	1078	"-MISC",
6f2a5b66	1079	[
6f2a5b66	1080	[
b4085a1a	1081	"=",
6f2a5b66	1082	[
6f30911f	1083	[
b4085a1a	1084	"-LITERAL",
6f30911f	1085	[
b4085a1a	1086	"foo.a"
6f30911f	1087	]
	1088	],
	1089	[
b4085a1a	1090	"-PLACEHOLDER",
6f30911f	1091	[
b4085a1a	1092	"?"
6f30911f	1093	]
b4085a1a	1094	],
6f30911f	1095	],
e2a120ce	1096	],
e2a120ce	1097	[
b4085a1a	1098	"-LITERAL",
	1099	[
	1100	"STUFF"
	1101	]
	1102	],
	1103	],
	1104	],
	1105	[
	1106	'moar',
	1107	[
	1108	[
	1109	'-PAREN',
e2a120ce	1110	[
e2a120ce	1111	[
b4085a1a	1112	'-LITERAL',
e2a120ce	1113	[
b4085a1a	1114	'stuff'
e2a120ce	1115	]
b4085a1a	1116	]
e2a120ce	1117	]
6f2a5b66	1118	]
	1119	]
	1120	],
	1121	[
	1122	"NOT",
	1123	[
	1124	[
	1125	"-PAREN",
	1126	[
	1127	[
	1128	"LIKE",
	1129	[
	1130	[
	1131	"-LITERAL",
	1132	[
	1133	"foo.b"
	1134	]
	1135	],
	1136	[
	1137	"-LITERAL",
	1138	[
	1139	"'station'"
	1140	]
	1141	]
	1142	]
	1143	]
	1144	]
	1145	]
	1146	]
	1147	],
	1148	[
	1149	"=",
	1150	[
	1151	[
	1152	"-LITERAL",
	1153	[
	1154	"x"
	1155	]
	1156	],
	1157	[
	1158	"-LITERAL",
	1159	[
	1160	"y"
	1161	]
	1162	]
	1163	]
	1164	],
	1165	[
7d273452	1166	'IN',
	1167	[
	1168	[
	1169	'-LITERAL',
	1170	[
	1171	'z',
	1172	],
	1173	],
	1174	[
	1175	'-PAREN',
	1176	[
	1177	[
	1178	'-PAREN',
	1179	[
	1180	[
	1181	'-LIST',
	1182	[
	1183	[
	1184	'-LITERAL',
	1185	[
	1186	'1'
	1187	]
	1188	],
	1189	[
	1190	'-LITERAL',
	1191	[
	1192	'2'
	1193	]
	1194	],
	1195	],
	1196	],
	1197	],
	1198	],
	1199	],
	1200	],
	1201	],
	1202	],
	1203	[
6f2a5b66	1204	"=",
	1205	[
	1206	[
	1207	"-LITERAL",
	1208	[
	1209	"a"
	1210	]
	1211	],
	1212	[
	1213	"-LITERAL",
	1214	[
	1215	"b"
	1216	]
	1217	]
	1218	]
	1219	]
	1220	]
	1221	]
	1222	]
	1223	],
	1224	[
	1225	"GROUP BY",
	1226	[
	1227	[
	1228	"-LIST",
	1229	[
	1230	[],
	1231	[]
	1232	]
	1233	]
	1234	]
	1235	],
	1236	[
	1237	"ORDER BY",
	1238	[
	1239	[
	1240	"-LIST",
	1241	[
	1242	[
1ec9b9e3	1243	"-ASC",
6f2a5b66	1244	[
	1245	[
	1246	"-MISC",
	1247	[
	1248	[
1ec9b9e3	1249	"-LITERAL",
6f2a5b66	1250	[
1ec9b9e3	1251	"x"
6f2a5b66	1252	]
	1253	],
	1254	[
	1255	"-LITERAL",
	1256	[
1ec9b9e3	1257	"x1"
6f2a5b66	1258	]
	1259	],
	1260	[
	1261	"-LITERAL",
	1262	[
1ec9b9e3	1263	"x2"
6f2a5b66	1264	]
	1265	],
	1266	[
	1267	"-LITERAL",
	1268	[
1ec9b9e3	1269	"y"
	1270	]
	1271	]
	1272	]
	1273	],
	1274	],
	1275	],
	1276	[
1ec9b9e3	1277	"-DESC",
	1278	[
	1279	[
	1280	"-MISC",
	1281	[
	1282	[
c84a4321	1283	"-DESC",
1ec9b9e3	1284	[
1ec9b9e3	1285	[
c84a4321	1286	"max",
1ec9b9e3	1287	[
1ec9b9e3	1288	[
6f30911f	1289	"-PAREN",
1ec9b9e3	1290	[
6f30911f	1291	[
	1292	"-LITERAL",
	1293	[
	1294	"y"
	1295	]
	1296	]
1ec9b9e3	1297	]
1ec9b9e3	1298	]
c84a4321	1299	],
c84a4321	1300	]
1ec9b9e3	1301	]
	1302	],
	1303	[
	1304	"-LITERAL",
	1305	[
c84a4321	1306	"x"
1ec9b9e3	1307	]
c84a4321	1308	],
	1309	[
	1310	"-LITERAL",
	1311	[
	1312	"z"
6f2a5b66	1313	]
	1314	]
	1315	]
	1316	]
	1317	]
	1318	]
	1319	]
	1320	]
	1321	]
	1322	]
	1323	], 'Deliberately malformed SQL parsed "correctly"');
	1324
	1325
0f9a26cb	1326	# test for recursion warnings on huge selectors
ad591616	1327	my @lst = ('AA' .. 'zz');
ad591616	1328	#@lst = ('AAA' .. 'zzz'); # if you really want to wait a while
0f9a26cb	1329	warnings_are {
ad591616	1330	my $sql = sprintf 'SELECT %s FROM foo', join (', ', (map { qq\|( "$_" )\| } @lst), (map { qq\|"$_"\| } @lst), (map { qq\|"$_", ( "$_" )\| } @lst) );
0f9a26cb	1331	my $tree = $sqlat->parse($sql);
	1332
	1333	is_deeply( $tree, [
	1334	[
	1335	"SELECT",
	1336	[
	1337	[
6f2a5b66	1338	"-LIST",
0f9a26cb	1339	[
ad591616	1340	(map { [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
6f2a5b66	1341	(map { [ -LITERAL => [ qq\|"$_"\| ] ] } @lst),
ad591616	1342	(map { [ -LITERAL => [ qq\|"$_"\| ] ], [ -PAREN => [ [ -LITERAL => [ qq\|"$_"\| ] ] ] ] } @lst),
0f9a26cb	1343	]
	1344	]
	1345	]
	1346	],
	1347	[
	1348	"FROM",
	1349	[
	1350	[
6f2a5b66	1351	"-LITERAL",
0f9a26cb	1352	[
	1353	"foo"
	1354	]
	1355	]
	1356	]
	1357	]
	1358	], 'long list parsed correctly');
	1359
	1360	is( $sqlat->unparse($tree), $sql, 'roundtrip ok');
	1361	} [], 'no recursion warnings on insane SQL';
	1362
3be357b0	1363	done_testing;