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