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 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 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 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 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 from_4():
return Data(sql='From x t join r As z on t4.id1=z4.id2',
tokens=[
Token(Token.FROM, 'From'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IDENTIFIER, 't'),
Token(Token.JOIN, 'join'),
Token(Token.IDENTIFIER, 'r'),
Token(Token.AS, 'As'),
Token(Token.IDENTIFIER, 'z'),
Token(Token.ON, 'on'),
Token(Token.IDENTIFIER, 't4.id1'),
Token(Token.COMPARE, '='),
Token(Token.IDENTIFIER, 'z4.id2'),
],
statements=[
From('From',
values=[
Identifier('x', alias='t'),
Join('join'),
Identifier('r', as_='As', alias='z'),
On('on',
values=[
Condition([
Identifier('t4.id1'),
Operator('='),
Identifier('z4.id2')
])
])
])
],
style=[
'FROM', ' x t', ' JOIN r AS z ON',
' t4.id1 = z4.id2'
])
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 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 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 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 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 from_3():
return Data(sql='From x t, r As z',
tokens=[
Token(Token.FROM, 'From'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IDENTIFIER, 't'),
Token(Token.COMMA, ','),
Token(Token.IDENTIFIER, 'r'),
Token(Token.AS, 'As'),
Token(Token.IDENTIFIER, 'z'),
],
statements=[
From('From',
values=[
Identifier('x', alias='t'),
Identifier('r', as_='As', alias='z')
])
],
style=['FROM', ' x t,', ' r AS z'])
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 insert_4():
return Data(sql='INSERT INTO spam SELECT * FROM eggs',
tokens=[
Token(Token.INSERT, 'INSERT INTO'),
Token(Token.IDENTIFIER, 'spam'),
Token(Token.SELECT, 'SELECT'),
Token(Token.IDENTIFIER, '*'),
Token(Token.FROM, 'FROM'),
Token(Token.IDENTIFIER, 'eggs'),
],
statements=[
Insert('INSERT INTO',
table='spam',
select=[
Select('SELECT', [Identifier('*')]),
From('FROM', [Identifier('eggs')])
])
],
style=[
'INSERT INTO', ' spam', 'SELECT', ' *', 'FROM',
' eggs'
])
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'
])
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 from_6():
return Data(sql='From x t join r As z',
tokens=[
Token(Token.FROM, 'From'),
Token(Token.IDENTIFIER, 'x'),
Token(Token.IDENTIFIER, 't'),
Token(Token.JOIN, 'join'),
Token(Token.IDENTIFIER, 'r'),
Token(Token.AS, 'As'),
Token(Token.IDENTIFIER, 'z'),
],
statements=[
From('From',
values=[
Identifier('x', alias='t'),
Join('join'),
Identifier('r', as_='As', alias='z')
])
],
style=[
'FROM',
' x t',
' JOIN r AS z',
])