def test_column_alias(self):
with self.subTest("full form"):
query = "x AS y"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(parser, result, {
"value": {
"column": "x"
},
"as_": "AS",
"alias": "y"
})
with self.subTest("shorthand form"):
query = "x y"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(parser, result, {
"value": {
"column": "x"
},
"alias": "y"
})
def test_column_expression_call(self):
with self.subTest("no args"):
query = "now()"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(parser, result, {"function": "now"})
with self.subTest("composition"):
query = "upper('hello') || concat(' ', lower('world'))"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"left": {
"function": "upper",
"0": "'hello'"
},
"operator": "||",
"right": {
"function": "concat",
"0": "' '",
"1": {
"function": "lower",
"0": "'world'"
},
},
},
)
def test_table_alias(self):
with self.subTest("full form"):
query = "users AS u"
parser = Parser(Lexer(query))
result = parser.parse_table_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(parser, result, {
"value": {
"table": "users"
},
"as_": "AS",
"alias": "u"
})
with self.subTest("shorthand form"):
query = "users u"
parser = Parser(Lexer(query))
result = parser.parse_table_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(parser, result, {
"value": {
"table": "users"
},
"alias": "u"
})
def test_keyword_vs_function_identifier(self):
# LEFT is a keyword as well as a function
query = "LEFT JOIN"
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.LEFT, "LEFT"),
Token(token.WHITESPACE, " "),
Token(token.JOIN, "JOIN"),
Token(token.EOF, ""),
],
)
query = "LEFT()"
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.IDENTIFIER, "LEFT"),
Token(token.LPAREN, "("),
Token(token.RPAREN, ")"),
Token(token.EOF, ""),
],
)
def test_column_expression_precedence(self):
query = "- 1 + 2 * 3 + 4"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"left": {
"left": {
"operator": "-",
"right": "1"
},
"operator": "+",
"right": {
"left": "2",
"operator": "*",
"right": "3"
},
},
"operator": "+",
"right": "4",
},
)
query = "- 1 + 2 * (3 + 4)"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"left": {
"operator": "-",
"right": "1"
},
"operator": "+",
"right": {
"left": "2",
"operator": "*",
"right": {
"expression": {
"left": "3",
"operator": "+",
"right": "4"
}
},
},
},
)
def test_column_expression_and(self):
query = "x = 1 AND y = 2"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"left": {
"left": {
"column": "x"
},
"operator": "=",
"right": "1"
},
"operator": "AND",
"right": {
"left": {
"column": "y"
},
"operator": "=",
"right": "2"
},
},
)
def read_all_tokens(lexer: Lexer) -> List[Token]:
tokens = []
while True:
t = lexer.next_token()
tokens.append(t)
if t.type == token.EOF:
return tokens
def test_identifier(self):
query = """some_schema."some_table".some_field"""
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.IDENTIFIER, 'some_schema."some_table".some_field'),
Token(token.EOF, ""),
],
)
def test_simple(self):
query = "+()"
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.PLUS, "+"),
Token(token.LPAREN, "("),
Token(token.RPAREN, ")"),
Token(token.EOF, ""),
],
)
def test_is_not_null(self):
query = "x IS NOT NULL"
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.IDENTIFIER, "x"),
Token(token.WHITESPACE, " "),
Token(token.IS, "IS"),
Token(token.WHITESPACE, " "),
Token(token.NOT, "NOT"),
Token(token.WHITESPACE, " "),
Token(token.NULL, "NULL"),
Token(token.EOF, ""),
],
)
def test_select_join(self):
query = "SELECT u.id FROM users u JOIN schools ON u.school_id = schools.id"
parser = Parser(Lexer(query))
result = parser.parse_select()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"select": "SELECT",
"results": {
"0": {
"table": "u",
"column": "id"
}
},
"from_": {
"from_": "FROM",
"expression": {
"left": {
"value": {
"table": "users"
},
"alias": "u"
},
"join": "JOIN",
"right": {
"table": "schools"
},
"on": "ON",
"condition": {
"left": {
"table": "u",
"column": "school_id"
},
"operator": "=",
"right": {
"table": "schools",
"column": "id"
},
},
},
},
},
)
def test_column_expression_is_not_null(self):
query = "x IS NOT NULL"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"left": {
"column": "x"
},
"operator": "IS",
"right": {
"operator": "NOT",
"right": "NULL"
},
},
)
def test_words(self):
query = '''select 'hello' AS world, "select"'''
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.SELECT, "select"),
Token(token.WHITESPACE, " "),
Token(token.STRING, "'hello'"),
Token(token.WHITESPACE, " "),
Token(token.AS, "AS"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "world"),
Token(token.COMMA, ","),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, '"select"'),
Token(token.EOF, ""),
],
)
def test_query(self):
query = """
SELECT u.id, u.first_name || ' ' || u.last_name AS user_name
FROM users u
"""
lexer = Lexer(query)
tokens = read_all_tokens(lexer)
self.assertEqual(
tokens,
[
Token(token.WHITESPACE, "\n"),
Token(token.SELECT, "SELECT"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "u.id"),
Token(token.COMMA, ","),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "u.first_name"),
Token(token.WHITESPACE, " "),
Token(token.PIPEPIPE, "||"),
Token(token.WHITESPACE, " "),
Token(token.STRING, "' '"),
Token(token.WHITESPACE, " "),
Token(token.PIPEPIPE, "||"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "u.last_name"),
Token(token.WHITESPACE, " "),
Token(token.AS, "AS"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "user_name"),
Token(token.WHITESPACE, "\n"),
Token(token.FROM, "FROM"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "users"),
Token(token.WHITESPACE, " "),
Token(token.IDENTIFIER, "u"),
Token(token.WHITESPACE, "\n"),
Token(token.EOF, ""),
],
)
def test_column_expression(self):
query = "- 1 + 2 * 3 * 4"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
def test_column_expression_between(self):
query = "x BETWEEN a AND b"
parser = Parser(Lexer(query))
result = parser.parse_column_expression()
self.assertParsedAll(parser, result)
def test_select_full(self):
query = "SELECT COUNT(*) FROM users WHERE is_active IS TRUE GROUP BY country HAVING COUNT(*) > 0 ORDER BY COUNT(*) DESC LIMIT 10"
parser = Parser(Lexer(query))
result = parser.parse_select()
self.assertParsedAll(parser, result)
self.assertParsedStructure(
parser,
result,
{
"select": "SELECT",
"results": {
"0": {
"function": "COUNT",
"0": "*"
}
},
"from_": {
"from_": "FROM",
"expression": {
"table": "users"
}
},
"where": {
"where": "WHERE",
"expression": {
"left": {
"column": "is_active"
},
"operator": "IS",
"right": "TRUE",
},
},
"group_by": {
"0": {
"column": "country"
}
},
"having": {
"having": "HAVING",
"expression": {
"left": {
"0": "*",
"function": "COUNT"
},
"operator": ">",
"right": "0",
},
},
"order_by": {
"0": {
"order": "DESC",
"value": {
"function": "COUNT",
"0": "*"
}
},
},
"limit": {
"expression": "10",
"limit": "LIMIT"
},
},
)