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 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 where_10():
return Data(sql='where x = (select max(*) from k)',
tokens=[
Token(Token.WHERE, 'where'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.COMPARE, '='),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.SELECT, 'select'),
Token(Token.FUNC, 'max'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, '*'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.FROM, 'from'),
Token(Token.IDENTIFIER, 'k'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Where('where', [
Condition([
Identifier('x'),
Operator('='),
SubSelect([
Select('select',
[Func('max', args=[Identifier('*')])]),
From('from', [Identifier('k')])
])
])
])
],
style=[
'WHERE', ' x = (', ' SELECT',
' MAX(*)', ' FROM', ' k)'
])
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 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 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 from_9():
return Data(sql='From x join r on Xyz(r.id) = Abc(x.r)',
tokens=[
Token(Token.FROM, 'From'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.JOIN, 'join'),
Token(Token.IDENTIFIER, 'r'),
Token(Token.ON, 'on'),
Token(Token.FUNC, 'Xyz'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, 'r.id'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.COMPARE, '='),
Token(Token.FUNC, 'Abc'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, 'x.r'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
From('From',
values=[
Identifier('x'),
Join('join'),
Identifier('r'),
On('on',
values=[
Condition([
Func('Xyz', args=[Identifier('r.id')]),
Operator('='),
Func('Abc', args=[Identifier('x.r')]),
])
])
])
],
style=[
'FROM', ' x', ' JOIN r ON',
' XYZ(r.id) = ABC(x.r)'
])
def func_4():
return Data(sql='Sum(price) as Summ',
tokens=[
Token(Token.FUNC, 'Sum'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, 'price'),
Token(Token.PARENTHESIS_CLOSE, ')'),
Token(Token.AS, 'as'),
Token(Token.IDENTIFIER, 'Summ')
],
statements=[
Func('Sum',
args=[Identifier('price')],
as_='as',
alias='Summ'),
],
style=['SUM(price) AS Summ'])
def func_1():
return Data(sql='CONCAT(last_name,", ",first_name)',
tokens=[
Token(Token.FUNC, 'CONCAT'),
Token(Token.PARENTHESIS_OPEN, '('),
Token(Token.IDENTIFIER, 'last_name'),
Token(Token.COMMA, ','),
Token(Token.STR, '", "'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'first_name'),
Token(Token.PARENTHESIS_CLOSE, ')'),
],
statements=[
Func('CONCAT',
args=[
Identifier('last_name'),
Str('", "'),
Identifier('first_name')
]),
],
style=['CONCAT(last_name, ", ", first_name)'])
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'])