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 case_1(): return Data(sql='SELECT CASE WHEN spam THEN 1 ELSE 0 FROM table', tokens=[ Token(Token.SELECT, 'SELECT'), Token(Token.CASE, 'CASE'), Token(Token.WHEN, 'WHEN'), Token(Token.IDENTIFIER, 'spam'), Token(Token.THEN, 'THEN'), Token(Token.NUMBER, '1'), Token(Token.ELSE, 'ELSE'), Token(Token.NUMBER, '0'), Token(Token.FROM, 'FROM'), Token(Token.IDENTIFIER, 'table'), ], statements=[ Select('SELECT', [ Case('CASE', [ When('WHEN', 'spam', 'THEN', '1'), Else('ELSE', '0'), ]) ]), From('FROM', [Identifier('table')]) ], style=[ 'SELECT', ' CASE', ' WHEN spam THEN 1', ' ELSE 0', 'FROM', ' table' ])
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 from_1(): return Data( sql='From x', tokens=[Token(Token.FROM, 'From'), Token(Token.IDENTIFIER, 'x')], statements=[From('From', values=[Identifier('x')])], style=['FROM', ' x'])
def group_by_1(): return Data(sql='Group by col1', tokens=[ Token(Token.GROUP_BY, 'Group by'), Token(Token.IDENTIFIER, 'col1'), ], statements=[ GroupBy(values=[Identifier('col1')]), ], style=['GROUP BY', ' col1'])
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 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 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 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 where_5(): return Data(sql='where x in (select * 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.IDENTIFIER, '*'), Token(Token.FROM, 'from'), Token(Token.IDENTIFIER, 'k'), Token(Token.PARENTHESIS_CLOSE, ')'), ], statements=[ Where('where', [ Condition([ Identifier('x'), Operator('in'), SubSelect([ Select('select', [Identifier('*')]), From('from', [Identifier('k')]) ]) ]) ]) ], style=[ 'WHERE', ' x IN (', ' SELECT', ' *', ' FROM', ' k)' ])
def multiple_statements_1(): return Data(sql='select t1.* from t1; select t2.* from t2', tokens=[ Token(Token.SELECT, 'select'), Token(Token.IDENTIFIER, 't1.*'), Token(Token.FROM, 'from'), Token(Token.IDENTIFIER, 't1'), Token(Token.SEMICOLON, ';'), Token(Token.SELECT, 'select'), Token(Token.IDENTIFIER, 't2.*'), Token(Token.FROM, 'from'), Token(Token.IDENTIFIER, 't2'), ], statements=[ Select('select', [Identifier('t1.*')]), From('from', [Identifier('t1')]), Semicolon(';'), Select('select', [Identifier('t2.*')]), From('from', [Identifier('t2')]), ], style=[ 'SELECT', ' t1.*', 'FROM', ' t1;', '', '', 'SELECT', ' t2.*', 'FROM', ' t2', ])
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 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 func_5(): return Data(sql='Now()', tokens=[ Token(Token.FUNC, 'Now'), Token(Token.PARENTHESIS_OPEN, '('), Token(Token.PARENTHESIS_CLOSE, ')') ], statements=[ Func('Now', args=[]), ], style=['NOW()'])
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 from_2(): return Data(sql='From x as t', tokens=[ Token(Token.FROM, 'From'), Token(Token.IDENTIFIER, 'x'), Token(Token.AS, 'as'), Token(Token.IDENTIFIER, 't') ], statements=[ From('From', values=[Identifier('x', as_='as', alias='t')]) ], style=['FROM', ' x AS t'])
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 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 composition_1(): return Data(sql='select * from k', tokens=[ Token(Token.SELECT, 'select'), Token(Token.IDENTIFIER, '*'), Token(Token.FROM, 'from'), Token(Token.IDENTIFIER, 'k') ], statements=[ Select('select', [Identifier('*')]), From('from', [Identifier('k')]) ], style=['SELECT', ' *', 'FROM', ' k'])
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 like_1(): return Data( sql="Select * From xs Where x Like 'A%Z'", tokens=[ Token(Token.SELECT, 'Select'), Token(Token.IDENTIFIER, '*'), Token(Token.FROM, 'From'), Token(Token.IDENTIFIER, 'xs'), Token(Token.WHERE, 'Where'), Token(Token.IDENTIFIER, 'x'), Token(Token.COMPARE, 'Like'), Token(Token.STR, "'A%Z'"), ], statements=[ Select('Select', [Identifier('*')]), From('From', [Identifier('xs')]), Where( 'Where', [Condition([Identifier('x'), Operator('Like'), Str("'A%Z'")])]) ], style=[ 'SELECT', ' *', 'FROM', ' xs', 'WHERE', " x LIKE 'A%Z'" ])
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_11(): return Data( sql='where x is null', tokens=[ Token(Token.WHERE, 'where'), Token(Token.IDENTIFIER, 'x'), Token(Token.IS, 'is'), Token(Token.NULL, 'null'), ], statements=[ Where( 'where', [Condition([Identifier('x'), Is('is'), Null('null')])]) ], style=['WHERE', ' x IS NULL'])
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_8(): return Data( sql='where x = "abc"', tokens=[ Token(Token.WHERE, 'where'), Token(Token.IDENTIFIER, 'x'), Token(Token.COMPARE, '='), Token(Token.STR, '"abc"') ], statements=[ Where('where', [Condition([Identifier('x'), Operator('='), Str('"abc"')])]) ], style=['WHERE', ' x = "abc"'])
def test_tokenize(sql, expected_tokens): tokens = [ Token(token_type, token_value) for token_type, token_value in expected_tokens ] assert_tokens(tokens, tokenize(sql))
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 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 composition_2(): return Data(sql='select * from k;', tokens=[ Token(Token.SELECT, 'select'), Token(Token.IDENTIFIER, '*'), Token(Token.FROM, 'from'), Token(Token.IDENTIFIER, 'k'), Token(Token.SEMICOLON, ';'), ], statements=[ Select('select', [Identifier('*')]), From('from', [Identifier('k')]), Semicolon(';'), ], style=[ 'SELECT', ' *', 'FROM', ' k;', ])
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))'])