def test_grouping_identifiers():
s = 'select foo.bar from "myscheme"."table" where fail. order'
parsed = bplsqlparse.parse(s)[0]
assert str(parsed) == s
assert isinstance(parsed.tokens[2], sql.Identifier)
assert isinstance(parsed.tokens[6], sql.Identifier)
assert isinstance(parsed.tokens[8], sql.Where)
s = 'select * from foo where foo.id = 1'
parsed = bplsqlparse.parse(s)[0]
assert str(parsed) == s
assert isinstance(parsed.tokens[-1].tokens[-1].tokens[0], sql.Identifier)
s = 'select * from (select "foo"."id" from foo)'
parsed = bplsqlparse.parse(s)[0]
assert str(parsed) == s
assert isinstance(parsed.tokens[-1].tokens[3], sql.Identifier)
s = "INSERT INTO `test` VALUES('foo', 'bar');"
parsed = bplsqlparse.parse(s)[0]
types = [l.ttype for l in parsed.tokens if not l.is_whitespace]
assert types == [T.DML, T.Keyword, None, T.Keyword, None, T.Punctuation]
s = "select 1.0*(a+b) as col, sum(c)/sum(d) from myschema.mytable"
parsed = bplsqlparse.parse(s)[0]
assert len(parsed.tokens) == 7
assert isinstance(parsed.tokens[2], sql.IdentifierList)
assert len(parsed.tokens[2].tokens) == 4
identifiers = list(parsed.tokens[2].get_identifiers())
assert len(identifiers) == 2
assert identifiers[0].get_alias() == "col"
def test_grouping_identifier_function():
p = bplsqlparse.parse('foo() as bar')[0]
assert isinstance(p.tokens[0], sql.Identifier)
assert isinstance(p.tokens[0].tokens[0], sql.Function)
p = bplsqlparse.parse('foo()||col2 bar')[0]
assert isinstance(p.tokens[0], sql.Identifier)
assert isinstance(p.tokens[0].tokens[0], sql.Operation)
assert isinstance(p.tokens[0].tokens[0].tokens[0], sql.Function)
def test_parse_child_of():
s = '(col1, col2)'
p = bplsqlparse.parse(s)[0]
assert p.tokens[0].tokens[1].is_child_of(p.tokens[0])
s = 'select foo'
p = bplsqlparse.parse(s)[0]
assert not p.tokens[2].is_child_of(p.tokens[0])
assert p.tokens[2].is_child_of(p)
def test_qualified_function():
p = bplsqlparse.parse('foo()')[0].tokens[0]
assert p.get_parent_name() is None
assert p.get_real_name() == 'foo'
p = bplsqlparse.parse('foo.bar()')[0].tokens[0]
assert p.get_parent_name() == 'foo'
assert p.get_real_name() == 'bar'
def test_grouping_comparison_exclude():
# make sure operators are not handled too lazy
p = bplsqlparse.parse('(=)')[0]
assert isinstance(p.tokens[0], sql.Parenthesis)
assert not isinstance(p.tokens[0].tokens[1], sql.Comparison)
p = bplsqlparse.parse('(a=1)')[0]
assert isinstance(p.tokens[0].tokens[1], sql.Comparison)
p = bplsqlparse.parse('(a>=1)')[0]
assert isinstance(p.tokens[0].tokens[1], sql.Comparison)
def test_wildcard_multiplication():
p = bplsqlparse.parse('select * from dual')[0]
assert p.tokens[2].ttype == T.Wildcard
p = bplsqlparse.parse('select a0.* from dual a0')[0]
assert p.tokens[2][2].ttype == T.Wildcard
p = bplsqlparse.parse('select 1 * 2 from dual')[0]
assert p.tokens[2][2].ttype == T.Operator
def test_identifier_with_operators():
# issue 53
p = bplsqlparse.parse('foo||bar')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Operation)
# again with whitespaces
p = bplsqlparse.parse('foo || bar')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Operation)
def test_aliased_column_without_as():
p = bplsqlparse.parse('foo bar')[0].tokens
assert len(p) == 1
assert p[0].get_real_name() == 'foo'
assert p[0].get_alias() == 'bar'
p = bplsqlparse.parse('foo.bar baz')[0].tokens[0]
assert p.get_parent_name() == 'foo'
assert p.get_real_name() == 'bar'
assert p.get_alias() == 'baz'
def test_grouping_where():
s = 'select * from foo where bar = 1 order by id desc'
p = bplsqlparse.parse(s)[0]
assert str(p) == s
assert len(p.tokens) == 14
s = 'select x from (select y from foo where bar = 1) z'
p = bplsqlparse.parse(s)[0]
assert str(p) == s
assert isinstance(p.tokens[-1].tokens[0].tokens[-2], sql.Where)
def test_aliased_function_without_as():
p = bplsqlparse.parse('foo() bar')[0].tokens[0]
assert p.get_parent_name() is None
assert p.get_real_name() == 'foo'
assert p.get_alias() == 'bar'
p = bplsqlparse.parse('foo.bar() baz')[0].tokens[0]
assert p.get_parent_name() == 'foo'
assert p.get_real_name() == 'bar'
assert p.get_alias() == 'baz'
def test_issue227_gettype_cte():
select_stmt = bplsqlparse.parse('SELECT 1, 2, 3 FROM foo;')
assert select_stmt[0].get_type() == 'SELECT'
with_stmt = bplsqlparse.parse('WITH foo AS (SELECT 1, 2, 3)'
'SELECT * FROM foo;')
assert with_stmt[0].get_type() == 'SELECT'
with2_stmt = bplsqlparse.parse("""
WITH foo AS (SELECT 1 AS abc, 2 AS def),
bar AS (SELECT * FROM something WHERE x > 1)
INSERT INTO elsewhere SELECT * FROM foo JOIN bar;""")
assert with2_stmt[0].get_type() == 'INSERT'
def test_sqlite_identifiers():
# Make sure we still parse sqlite style escapes
p = bplsqlparse.parse('[col1],[col2]')[0].tokens
id_names = [id_.get_name() for id_ in p[0].get_identifiers()]
assert len(p) == 1
assert isinstance(p[0], sql.IdentifierList)
assert id_names == ['[col1]', '[col2]']
p = bplsqlparse.parse('[col1]+[col2]')[0]
types = [tok.ttype for tok in p.flatten()]
assert types == [T.Name, T.Operator, T.Name]
def test_comparison_with_keywords():
# issue90
# in fact these are assignments, but for now we don't distinguish them
p = bplsqlparse.parse('foo = NULL')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Comparison)
assert len(p.tokens[0].tokens) == 5
assert p.tokens[0].left.value == 'foo'
assert p.tokens[0].right.value == 'NULL'
# make sure it's case-insensitive
p = bplsqlparse.parse('foo = null')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Comparison)
def test_grouping_identifier_as_invalid():
# issue8
p = bplsqlparse.parse('foo as select *')[0]
assert len(p.tokens), 5
assert isinstance(p.tokens[0], sql.Identifier)
assert len(p.tokens[0].tokens) == 1
assert p.tokens[2].ttype == T.Keyword
def test_comparison_with_strings():
# issue148
p = bplsqlparse.parse("foo = 'bar'")[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Comparison)
assert p.tokens[0].right.value == "'bar'"
assert p.tokens[0].right.ttype == T.String.Single
def test_pprint():
p = bplsqlparse.parse('select a0, b0, c0, d0, e0 from '
'(select * from dual) q0 where 1=1 and 2=2')[0]
output = StringIO()
p._pprint_tree(f=output)
pprint = '\n'.join([
" 0 DML 'select'", " 1 Whitespace ' '",
" 2 IdentifierList 'a0, b0...'", " | 0 Identifier 'a0'",
" | | 0 Name 'a0'", " | 1 Punctuation ','", " | 2 Whitespace ' '",
" | 3 Identifier 'b0'", " | | 0 Name 'b0'", " | 4 Punctuation ','",
" | 5 Whitespace ' '", " | 6 Identifier 'c0'", " | | 0 Name 'c0'",
" | 7 Punctuation ','", " | 8 Whitespace ' '",
" | 9 Identifier 'd0'", " | | 0 Name 'd0'", " | 10 Punctuation ','",
" | 11 Whitespace ' '", " | 12 Float 'e0'", " 3 Whitespace ' '",
" 4 Keyword 'from'", " 5 Whitespace ' '", " 6 Identifier '(selec...'",
" | 0 Parenthesis '(selec...'", " | | 0 Punctuation '('",
" | | 1 DML 'select'", " | | 2 Whitespace ' '",
" | | 3 Wildcard '*'", " | | 4 Whitespace ' '",
" | | 5 Keyword 'from'", " | | 6 Whitespace ' '",
" | | 7 Identifier 'dual'", " | | | 0 Name 'dual'",
" | | 8 Punctuation ')'", " | 1 Whitespace ' '",
" | 2 Identifier 'q0'", " | | 0 Name 'q0'", " 7 Whitespace ' '",
" 8 Where 'where ...'", " | 0 Keyword 'where'",
" | 1 Whitespace ' '", " | 2 Comparison '1=1'",
" | | 0 Integer '1'", " | | 1 Comparison '='",
" | | 2 Integer '1'", " | 3 Whitespace ' '", " | 4 Keyword 'and'",
" | 5 Whitespace ' '", " | 6 Comparison '2=2'",
" | | 0 Integer '2'", " | | 1 Comparison '='",
" | | 2 Integer '2'", ""
])
assert output.getvalue() == pprint
def test_non_ascii():
_test_non_ascii = u"insert into test (id, name) values (1, 'тест');"
s = _test_non_ascii
stmts = bplsqlparse.parse(s)
assert len(stmts) == 1
statement = stmts[0]
assert text_type(statement) == s
assert statement._pprint_tree() is None
s = _test_non_ascii.encode('utf-8')
stmts = bplsqlparse.parse(s, 'utf-8')
assert len(stmts) == 1
statement = stmts[0]
assert text_type(statement) == _test_non_ascii
assert statement._pprint_tree() is None
def test_parse_has_ancestor():
s = 'foo or (bar, baz)'
p = bplsqlparse.parse(s)[0]
baz = p.tokens[-1].tokens[1].tokens[-1]
assert baz.has_ancestor(p.tokens[-1].tokens[1])
assert baz.has_ancestor(p.tokens[-1])
assert baz.has_ancestor(p)
def test_parse_access_symbol():
# see issue27
t = bplsqlparse.parse('select a.[foo bar] as foo')[0].tokens
assert isinstance(t[-1], sql.Identifier)
assert t[-1].get_name() == 'foo'
assert t[-1].get_real_name() == '[foo bar]'
assert t[-1].get_parent_name() == 'a'
def test_grouping_identifier_invalid():
p = bplsqlparse.parse('a.')[0]
assert isinstance(p.tokens[0], sql.Identifier)
assert p.tokens[0].has_alias() is False
assert p.tokens[0].get_name() is None
assert p.tokens[0].get_real_name() is None
assert p.tokens[0].get_parent_name() == 'a'
def test_parse_square_brackets_notation_isnt_too_greedy():
# see issue153
t = bplsqlparse.parse('[foo], [bar]')[0].tokens
assert isinstance(t[0], sql.IdentifierList)
assert len(t[0].tokens) == 4
assert t[0].tokens[0].get_real_name() == '[foo]'
assert t[0].tokens[-1].get_real_name() == '[bar]'
def test_issue40():
# make sure identifier lists in subselects are grouped
p = bplsqlparse.parse(('SELECT id, name FROM '
'(SELECT id, name FROM bar) as foo'))[0]
assert len(p.tokens) == 7
assert p.tokens[2].__class__ == sql.IdentifierList
assert p.tokens[-1].__class__ == sql.Identifier
assert p.tokens[-1].get_name() == 'foo'
sp = p.tokens[-1].tokens[0]
assert sp.tokens[3].__class__ == sql.IdentifierList
# make sure that formatting works as expected
s = bplsqlparse.format(
'SELECT id == name FROM '
'(SELECT id, name FROM bar)', reindent=True)
assert s == '\n'.join([
'SELECT id == name', 'FROM', ' (SELECT id,', ' name',
' FROM bar)'
])
s = bplsqlparse.format(
'SELECT id == name FROM '
'(SELECT id, name FROM bar) as foo',
reindent=True)
assert s == '\n'.join([
'SELECT id == name', 'FROM', ' (SELECT id,', ' name',
' FROM bar) as foo'
])
def test_parse_multistatement():
sql1 = 'select * from foo;'
sql2 = 'select * from bar;'
stmts = bplsqlparse.parse(sql1 + sql2)
assert len(stmts) == 2
assert str(stmts[0]) == sql1
assert str(stmts[1]) == sql2
def test_split_comment_end_of_line():
sql = ('select * from foo; -- foo\n' 'select * from bar;')
stmts = bplsqlparse.parse(sql)
assert len(stmts) == 2
assert ''.join(str(q) for q in stmts) == sql
# make sure the comment belongs to first query
assert str(stmts[0]) == 'select * from foo; -- foo\n'
def test_split_comment_with_umlaut():
sql = (u'select * from foo;\n'
u'-- Testing an umlaut: ä\n'
u'select * from bar;')
stmts = bplsqlparse.parse(sql)
assert len(stmts) == 2
assert ''.join(text_type(q) for q in stmts) == sql
def test_split_semicolon():
sql1 = 'select * from foo;'
sql2 = "select * from foo where bar = 'foo;bar';"
stmts = bplsqlparse.parse(''.join([sql1, sql2]))
assert len(stmts) == 2
assert str(stmts[0]) == sql1
assert str(stmts[1]) == sql2
def test_comparison_with_floats():
# issue145
p = bplsqlparse.parse('foo = 25.5')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Comparison)
assert len(p.tokens[0].tokens) == 5
assert p.tokens[0].left.value == 'foo'
assert p.tokens[0].right.value == '25.5'
def test_dont_alias_keywords():
# The _group_left_right function had a bug where the check for the
# left side wasn't handled correctly. In one case this resulted in
# a keyword turning into an identifier.
p = bplsqlparse.parse('FROM AS foo')[0]
assert len(p.tokens) == 5
assert p.tokens[0].ttype is T.Keyword
assert p.tokens[2].ttype is T.Keyword
def test_issue78(s, func_name, result):
# the bug author provided this nice examples, let's use them!
p = bplsqlparse.parse(s)[0]
i = p.tokens[2]
assert isinstance(i, sql.Identifier)
func = getattr(i, func_name)
assert func() == result
def test_comparison_with_parenthesis():
# issue23
p = bplsqlparse.parse('(3 + 4) = 7')[0]
assert len(p.tokens) == 1
assert isinstance(p.tokens[0], sql.Comparison)
comp = p.tokens[0]
assert isinstance(comp.left, sql.Parenthesis)
assert comp.right.ttype is T.Number.Integer
