def func_3():
return Data(sql='distinct(min(1, 3), max(0, 1))',
tokens=[
Token(Token.FUNC, 'distinct'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.FUNC, 'min'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.NUMBER, '3'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.COMMA, ','),
Token(Token.FUNC, 'max'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '0'),
Token(Token.COMMA, ','),
Token(Token.NUMBER, '1'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Func('distinct', [
Func('min', [Number('1'), Number('3')]),
Func('max', [Number('0'), Number('1')]),
]),
],
style=['DISTINCT(MIN(1, 3), MAX(0, 1))'])
def select_3():
return Data(sql='Select 1, col1,min(3,4)',
tokens=[
Token(Token.SELECT, 'Select'),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'col1'),
Token(Token.COMMA, ','),
Token(Token.FUNC, 'min'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '3'),
Token(Token.COMMA, ','),
Token(Token.NUMBER, '4'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Select('Select', [
Number('1'),
Identifier('col1'),
Func('min', [Number('3'), Number('4')])
]),
],
style=[
'SELECT',
' 1,',
' col1,',
' MIN(3, 4)',
])
def where_3():
return Data(sql='where not x= 1 and x != 3',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.NOT, 'not'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.COMPARE, '='),
Token(Token.NUMBER, '1'),
Token(Token.LINK, 'and'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.COMPARE, '!='),
Token(Token.NUMBER, '3'),
],
statements=[
Where('where', [
Condition([
Not('not'),
Identifier('x'),
Operator('='),
Number('1'),
]),
Link('and'),
Condition([
Identifier('x'),
Operator('!='),
Number('3'),
]),
])
],
style=['WHERE', ' NOT x = 1', ' AND x != 3'])
def between_1():
return Data(sql='SELECT * FROM ys WHERE ys.id BETWEEN 91 AND 92',
tokens=[
Token(Token.SELECT, 'SELECT'),
Token(Token.IDENTIFIER, '*'),
Token(Token.FROM, 'FROM'),
Token(Token.IDENTIFIER, 'ys'),
Token(Token.WHERE, 'WHERE'),
Token(Token.IDENTIFIER, 'ys.id'),
Token(Token.BETWEEN, 'BETWEEN'),
Token(Token.NUMBER, '91'),
Token(Token.LINK, 'AND'),
Token(Token.NUMBER, '92'),
],
statements=[
Select('SELECT', [Identifier('*')]),
From('FROM', [Identifier('ys')]),
Where('WHERE', [
Condition([
Identifier('ys.id'),
Between('BETWEEN'),
Number('91'),
Link('AND'),
Number('92')
])
])
],
style=[
'SELECT', ' *', 'FROM', ' ys', 'WHERE',
' ys.id BETWEEN 91 AND 92'
])
def composition_3():
return Data(
sql=
"select p.* from p1 as p left join p2 as r on r.sk = CONCAT(p.x, '-!') where 1 = 1",
tokens=[
Token(Token.SELECT, 'select'),
Token(Token.IDENTIFIER, 'p.*'),
Token(Token.FROM, 'from'),
Token(Token.IDENTIFIER, 'p1'),
Token(Token.AS, 'as'),
Token(Token.IDENTIFIER, 'p'),
Token(Token.JOIN, 'left join'),
Token(Token.IDENTIFIER, 'p2'),
Token(Token.AS, 'as'),
Token(Token.IDENTIFIER, 'r'),
Token(Token.ON, 'on'),
Token(Token.IDENTIFIER, 'r.sk'),
Token(Token.COMPARE, '='),
Token(Token.FUNC, 'CONCAT'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, 'p.x'),
Token(Token.COMMA, ','),
Token(Token.STR, "'-!'"),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.WHERE, 'where'),
Token(Token.NUMBER, '1'),
Token(Token.COMPARE, '='),
Token(Token.NUMBER, '1')
],
statements=[
Select('select', [Identifier('p.*')]),
From('from', [
Identifier('p1', as_='as', alias='p'),
Join('left join'),
Identifier('p2', as_='as', alias='r'),
On('on',
values=[
Condition([
Identifier('r.sk'),
Operator('='),
Func('CONCAT',
args=[Identifier('p.x'),
Str("'-!'")])
])
])
]),
Where('where',
[Condition([Number('1'),
Operator('='),
Number('1')])])
],
style=[
'SELECT', ' p.*', 'FROM', ' p1 AS p',
' LEFT JOIN p2 AS r ON', " r.sk = CONCAT(p.x, '-!')",
'WHERE', ' 1 = 1'
])
def limit_2():
return Data(sql='LIMIT 65,66',
tokens=[
Token(Token.LIMIT, 'LIMIT'),
Token(Token.NUMBER, '65'),
Token(Token.COMMA, ','),
Token(Token.NUMBER, '66'),
],
statements=[
Limit(row_count=Number('66'), offset=Number('65')),
],
style=[
'LIMIT 65, 66',
])
def insert_1():
return Data(
sql='insert into table_name values ("value!", value2,3)',
tokens=[
Token(Token.INSERT, 'insert into'),
Token(Token.IDENTIFIER, 'table_name'),
Token(Token.VALUES, 'values'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.STR, '"value!"'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'value2'),
Token(Token.COMMA, ','),
Token(Token.NUMBER, '3'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Insert('insert into',
table='table_name',
values=Values(
'values',
[[Str('"value!"'),
Identifier('value2'),
Number('3')]]))
],
style=[
'INSERT INTO', ' table_name', 'VALUES',
' ("value!", value2, 3)'
])
def limit_3():
return Data(sql='LIMIT 65 offset 66',
tokens=[
Token(Token.LIMIT, 'LIMIT'),
Token(Token.NUMBER, '65'),
Token(Token.IDENTIFIER, 'offset'),
Token(Token.NUMBER, '66'),
],
statements=[
Limit(row_count=Number('65'),
offset=Number('66'),
offset_keyword='offset'),
],
style=[
'LIMIT 65 OFFSET 66',
])
def where_6():
return Data(
sql='where x in (select max(1) from k)',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IN, 'in'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.SELECT, 'select'),
Token(Token.FUNC, 'max'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '1'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.FROM, 'from'),
Token(Token.IDENTIFIER, 'k'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Where('where', [
Condition([
Identifier('x'),
Operator('in'),
SubSelect([
Select('select', [Func('max', args=[Number('1')])]),
From('from', [Identifier('k')])
])
])
])
],
style=[
'WHERE', ' x IN (', ' SELECT', ' MAX(1)',
' FROM', ' k)'
])
def group_by_2():
return Data(sql='Group by 1',
tokens=[
Token(Token.GROUP_BY, 'Group by'),
Token(Token.NUMBER, '1'),
],
statements=[
GroupBy(values=[Number('1')]),
],
style=['GROUP BY', ' 1'])
def select_1():
return Data(sql='Select 1',
tokens=[
Token(Token.SELECT, 'Select'),
Token(Token.NUMBER, '1'),
],
statements=[
Select('Select', [Number('1')]),
],
style=['SELECT', ' 1'])
def order_by_1():
return Data(sql='order by 6',
tokens=[
Token(Token.ORDER_BY, 'order by'),
Token(Token.NUMBER, '6'),
],
statements=[
OrderBy(values=[Number('6')]),
],
style=['ORDER BY', ' 6'])
def select_4():
return Data(
sql='Select 1 as 2',
tokens=[
Token(Token.SELECT, 'Select'),
Token(Token.NUMBER, '1'),
Token(Token.AS, 'as'),
Token(Token.NUMBER, '2'),
],
statements=[Select('Select', [Number('1', as_='as', alias='2')])],
style=['SELECT', ' 1 AS 2'])
def order_by_2():
return Data(sql='order by 6 Asc',
tokens=[
Token(Token.ORDER_BY, 'order by'),
Token(Token.NUMBER, '6'),
Token(Token.ASC, 'Asc'),
],
statements=[
OrderBy(values=[Number('6', sort='Asc')]),
],
style=['ORDER BY', ' 6 ASC'])
def limit_1():
return Data(sql='LIMIT 65',
tokens=[
Token(Token.LIMIT, 'LIMIT'),
Token(Token.NUMBER, '65'),
],
statements=[
Limit(row_count=Number('65')),
],
style=[
'LIMIT 65',
])
def select_2():
return Data(sql='Select 1, col1',
tokens=[
Token(Token.SELECT, 'Select'),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'col1'),
],
statements=[
Select('Select',
[Number('1'), Identifier('col1')]),
],
style=['SELECT', ' 1,', ' col1'])
def group_by_3():
return Data(
sql='Group by 1,col1',
tokens=[
Token(Token.GROUP_BY, 'Group by'),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'col1'),
],
statements=[
GroupBy(values=[Number('1'), Identifier('col1')]),
],
style=['GROUP BY', ' 1,', ' col1'])
def having_2():
return Data(sql='having not count(1) !=1',
tokens=[
Token(Token.HAVING, 'having'),
Token(Token.NOT, 'not'),
Token(Token.FUNC, 'count'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '1'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.COMPARE, '!='),
Token(Token.NUMBER, '1'),
],
statements=[
Having('having', [
Condition([
Not('not'),
Func('count', [Number('1')]),
Operator('!='),
Number('1')
])
])
],
style=['HAVING', ' NOT COUNT(1) != 1'])
def func_2():
return Data(sql='distinct(count(1))',
tokens=[
Token(Token.FUNC, 'distinct'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.FUNC, 'count'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '1'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Func('distinct', [Func('count', [Number('1')])]),
],
style=['DISTINCT(COUNT(1))'])
def group_by_4():
return Data(sql='Group by 1,col1 with rollup',
tokens=[
Token(Token.GROUP_BY, 'Group by'),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'col1'),
Token(Token.WITH_ROLLUP, 'with rollup'),
],
statements=[
GroupBy(values=[Number('1'),
Identifier('col1')],
with_rollup='with rollup'),
],
style=['GROUP BY', ' 1,', ' col1', ' WITH ROLLUP'])
def where_1():
return Data(
sql='where x= 1',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.COMPARE, '='),
Token(Token.NUMBER, '1')
],
statements=[
Where('where',
[Condition([Identifier('x'),
Operator('='),
Number('1')])])
],
style=['WHERE', ' x = 1'])
def order_by_4():
return Data(sql='order by 6 DESC,col2 ASC',
tokens=[
Token(Token.ORDER_BY, 'order by'),
Token(Token.NUMBER, '6'),
Token(Token.DESC, 'DESC'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'col2'),
Token(Token.ASC, 'ASC'),
],
statements=[
OrderBy(values=[
Number('6', sort='DESC'),
Identifier('col2', sort='ASC')
])
],
style=['ORDER BY', ' 6 DESC,', ' col2 ASC'])
def having_1():
return Data(
sql='having col1 !=1',
tokens=[
Token(Token.HAVING, 'having'),
Token(Token.IDENTIFIER, 'col1'),
Token(Token.COMPARE, '!='),
Token(Token.NUMBER, '1'),
],
statements=[
Having(
'having',
[Condition([Identifier('col1'),
Operator('!='),
Number('1')])])
],
style=['HAVING', ' col1 != 1'])
def where_4():
return Data(
sql='where x in (1, "3")',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IN, 'in'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.NUMBER, '1'),
Token(Token.COMMA, ','),
Token(Token.STR, '"3"'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Where('where', [
Condition([
Identifier('x'),
Operator('in'), [Number('1'), Str('"3"')]
])
])
],
style=['WHERE', ' x IN (', ' 1,', ' "3")'])
def where_7():
return Data(
sql='where x in (select * from k) Or c = 3',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IN, 'in'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.SELECT, 'select'),
Token(Token.IDENTIFIER, '*'),
Token(Token.FROM, 'from'),
Token(Token.IDENTIFIER, 'k'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.LINK, 'Or'),
Token(Token.IDENTIFIER, 'c'),
Token(Token.COMPARE, '='),
Token(Token.NUMBER, '3'),
],
statements=[
Where('where', [
Condition([
Identifier('x'),
Operator('in'),
SubSelect([
Select('select', [Identifier('*')]),
From('from', [Identifier('k')])
])
]),
Link('Or'),
Condition([Identifier('c'),
Operator('='),
Number('3')])
])
],
style=[
'WHERE', ' x IN (', ' SELECT', ' *',
' FROM', ' k)', ' OR c = 3'
])
([Token(Token.WHERE, ''),
Token(Token.IDENTIFIER, '')], InvalidCondition),
])
def test_parse_exceptions(tokens, exception):
with pytest.raises(exception):
list(parse(tokens))
def test_parse_identifier_with_exception():
with pytest.raises(InvalidIdentifier):
_parse_identifier([])
@pytest.mark.parametrize(('tokens', 'expected_value', 'expected_count'), [
([Token(Token.IDENTIFIER, 'ident')], Identifier('ident'), 1),
([Token(Token.NUMBER, '1')], Number('1'), 1),
([Token(Token.IDENTIFIER, 'x'),
Token(Token.IDENTIFIER, 't')], Identifier('x', alias='t'), 2),
([
Token(Token.IDENTIFIER, 'x'),
Token(Token.AS, 'as'),
Token(Token.IDENTIFIER, 't')
], Identifier('x', as_='as', alias='t'), 3),
])
def test_parse_identifier(tokens, expected_value, expected_count):
result, count = _parse_identifier(tokens)
assert count == expected_count
assert result == expected_value
