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_truncate_strings():
sql = "update foo set value = '{0}';".format('x' * 1000)
formatted = bplsqlparse.format(sql, truncate_strings=10)
assert formatted == "update foo set value = 'xxxxxxxxxx[...]';"
formatted = bplsqlparse.format(sql,
truncate_strings=3,
truncate_char='YYY')
assert formatted == "update foo set value = 'xxxYYY';"
def test_identifiercase(self):
sql = 'select * from bar; -- select foo\n'
res = bplsqlparse.format(sql, identifier_case='upper')
assert res == 'select * from BAR; -- select foo\n'
res = bplsqlparse.format(sql, identifier_case='capitalize')
assert res == 'select * from Bar; -- select foo\n'
res = bplsqlparse.format(sql.upper(), identifier_case='lower')
assert res == 'SELECT * FROM bar; -- SELECT FOO\n'
def test_python(self):
sql = 'select * from foo;'
f = lambda sql: bplsqlparse.format(sql, output_format='python')
assert f(sql) == "sql = 'select * from foo;'"
f = lambda sql: bplsqlparse.format(
sql, output_format='python', reindent=True)
assert f(sql) == '\n'.join(
["sql = ('select * '", " 'from foo;')"])
def test_keywordcase(self):
sql = 'select * from bar; -- select foo\n'
res = bplsqlparse.format(sql, keyword_case='upper')
assert res == 'SELECT * FROM bar; -- select foo\n'
res = bplsqlparse.format(sql, keyword_case='capitalize')
assert res == 'Select * From bar; -- select foo\n'
res = bplsqlparse.format(sql.upper(), keyword_case='lower')
assert res == 'select * from BAR; -- SELECT FOO\n'
def test_php(self):
sql = 'select * from foo;'
f = lambda sql: bplsqlparse.format(sql, output_format='php')
assert f(sql) == '$sql = "select * from foo;";'
f = lambda sql: bplsqlparse.format(
sql, output_format='php', reindent=True)
assert f(sql) == '\n'.join(
['$sql = "select * ";', '$sql .= "from foo;";'])
def test_strip_comments_single(self):
sql = 'select *-- statement starts here\nfrom foo'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select * from foo'
sql = 'select * -- statement starts here\nfrom foo'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select * from foo'
sql = 'select-- foo\nfrom -- bar\nwhere'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select from where'
def test_duplicate_linebreaks(self):
# issue3
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'select c1 -- column1\nfrom foo'
assert f(s) == '\n'.join(['select c1 -- column1', 'from foo'])
s = 'select c1 -- column1\nfrom foo'
r = bplsqlparse.format(s, reindent=True, strip_comments=True)
assert r == '\n'.join(['select c1', 'from foo'])
s = 'select c1\nfrom foo\norder by c1'
assert f(s) == '\n'.join(['select c1', 'from foo', 'order by c1'])
s = 'select c1 from t1 where (c1 = 1) order by c1'
assert f(s) == '\n'.join(
['select c1', 'from t1', 'where (c1 = 1)', 'order by c1'])
def test_keywords_between(self):
# issue 14
# don't break AND after BETWEEN
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'and foo between 1 and 2 and bar = 3'
assert f(s) == '\n'.join(
['', 'and foo between 1 and 2', 'and bar = 3'])
def test_strip_comments_multi(self):
sql = '/* sql starts here */\nselect'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select'
sql = '/* sql starts here */ select'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select'
sql = '/*\n * sql starts here\n */\nselect'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select'
sql = 'select (/* sql starts here */ select 2)'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select (select 2)'
sql = 'select (/* sql /* starts here */ select 2)'
res = bplsqlparse.format(sql, strip_comments=True)
assert res == 'select (select 2)'
def test_issue207_runaway_format():
sql = 'select 1 from (select 1 as one, 2 as two, 3 from dual) t0'
p = bplsqlparse.format(sql, reindent=True)
assert p == '\n'.join([
"select 1", "from", " (select 1 as one,", " 2 as two,",
" 3", " from dual) t0"
])
def test_format_accepts_encoding(load_file):
# issue20
sql = load_file('test_cp1251.sql', 'cp1251')
formatted = bplsqlparse.format(sql, reindent=True, encoding='cp1251')
tformatted = u'insert into foo\nvalues (1); -- Песня про надежду'
assert formatted == tformatted
def main(args=None):
parser = create_parser()
args = parser.parse_args(args)
if args.filename == '-': # read from stdin
if PY2:
data = getreader(args.encoding)(sys.stdin).read()
else:
data = TextIOWrapper(sys.stdin.buffer,
encoding=args.encoding).read()
else:
try:
data = ''.join(open(args.filename, 'r', args.encoding).readlines())
except IOError as e:
return _error(u'Failed to read {0}: {1}'.format(args.filename, e))
if args.outfile:
try:
stream = open(args.outfile, 'w', args.encoding)
except IOError as e:
return _error(u'Failed to open {0}: {1}'.format(args.outfile, e))
else:
stream = sys.stdout
formatter_opts = vars(args)
try:
formatter_opts = bplsqlparse.formatter.validate_options(formatter_opts)
except PLSQLParseError as e:
return _error(u'Invalid options: {0}'.format(e))
s = bplsqlparse.format(data, **formatter_opts)
stream.write(s)
stream.flush()
return 0
def test_case(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'case when foo = 1 then 2 when foo = 3 then 4 else 5 end'
assert f(s) == '\n'.join([
'case', ' when foo = 1 then 2', ' when foo = 3 then 4',
' else 5', 'end'
])
def test_identifier_list_with_wrap_after(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True, wrap_after=14)
s = 'select foo, bar, baz from table1, table2 where 1 = 2'
assert f(s) == '\n'.join([
'select foo, bar,', ' baz', 'from table1, table2',
'where 1 = 2'
])
def test_stmts(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'select foo; select bar'
assert f(s) == 'select foo;\n\nselect bar'
s = 'select foo'
assert f(s) == 'select foo'
s = 'select foo; -- test\n select bar'
assert f(s) == 'select foo; -- test\n\nselect bar'
def test_except_formatting():
sql = 'SELECT 1 FROM foo WHERE 2 = 3 EXCEPT SELECT 2 FROM bar WHERE 1 = 2'
formatted = bplsqlparse.format(sql, reindent=True)
tformatted = '\n'.join([
'SELECT 1', 'FROM foo', 'WHERE 2 = 3', 'EXCEPT', 'SELECT 2',
'FROM bar', 'WHERE 1 = 2'
])
assert formatted == tformatted
def test_python_multiple_statements_with_formatting(self):
sql = 'select * from foo; select 1 from dual'
f = lambda sql: bplsqlparse.format(
sql, output_format='python', reindent=True)
assert f(sql) == '\n'.join([
"sql = ('select * '", " 'from foo;')", "sql2 = ('select 1 '",
" 'from dual')"
])
def test_identifier_list_with_functions(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = ("select 'abc' as foo, coalesce(col1, col2)||col3 as bar,"
"col3 from my_table")
assert f(s) == '\n'.join([
"select 'abc' as foo,",
" coalesce(col1, col2)||col3 as bar,", " col3",
"from my_table"
])
def test_identifier_list_comment_first(self):
f = lambda sql: bplsqlparse.format(
sql, reindent=True, comma_first=True)
# not the 3: It cleans up whitespace too!
s = 'select foo, bar, baz from table where foo in (1, 2,3)'
assert f(s) == '\n'.join([
'select foo', ' , bar', ' , baz', 'from table',
'where foo in (1', ' , 2', ' , 3)'
])
def test_having_produces_newline():
sql = ('select * from foo, bar where bar.id = foo.bar_id '
'having sum(bar.value) > 100')
formatted = bplsqlparse.format(sql, reindent=True)
expected = [
'select *', 'from foo,', ' bar', 'where bar.id = foo.bar_id',
'having sum(bar.value) > 100'
]
assert formatted == '\n'.join(expected)
def test_format_column_ordering():
# issue89
sql = 'select * from foo order by c1 desc, c2, c3;'
formatted = bplsqlparse.format(sql, reindent=True)
expected = '\n'.join([
'select *', 'from foo', 'order by c1 desc,', ' c2,',
' c3;'
])
assert formatted == expected
def test_identifier_list(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'select foo, bar, baz from table1, table2 where 1 = 2'
assert f(s) == '\n'.join([
'select foo,', ' bar,', ' baz', 'from table1,',
' table2', 'where 1 = 2'
])
s = 'select a.*, b.id from a, b'
assert f(s) == '\n'.join(
['select a.*,', ' b.id', 'from a,', ' b'])
def test_parse_sql_with_binary():
# See https://github.com/andialbrecht/bplsqlparse/pull/88
# digest = '|ËêplL4¡høN{'
digest = '\x82|\xcb\x0e\xea\x8aplL4\xa1h\x91\xf8N{'
sql = "select * from foo where bar = '{0}'".format(digest)
formatted = bplsqlparse.format(sql, reindent=True)
tformatted = "select *\nfrom foo\nwhere bar = '{0}'".format(digest)
if PY2:
tformatted = tformatted.decode('unicode-escape')
assert formatted == tformatted
def test_where(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'select * from foo where bar = 1 and baz = 2 or bzz = 3;'
assert f(s) == '\n'.join([
'select *', 'from foo', 'where bar = 1', ' and baz = 2',
' or bzz = 3;'
])
s = 'select * from foo where bar = 1 and (baz = 2 or bzz = 3);'
assert f(s) == '\n'.join([
'select *', 'from foo', 'where bar = 1', ' and (baz = 2',
' or bzz = 3);'
])
def test_join(self):
f = lambda sql: bplsqlparse.format(sql, reindent=True)
s = 'select * from foo join bar on 1 = 2'
assert f(s) == '\n'.join(['select *', 'from foo', 'join bar on 1 = 2'])
s = 'select * from foo inner join bar on 1 = 2'
assert f(s) == '\n'.join(
['select *', 'from foo', 'inner join bar on 1 = 2'])
s = 'select * from foo left outer join bar on 1 = 2'
assert f(s) == '\n'.join(
['select *', 'from foo', 'left outer join bar on 1 = 2'])
s = 'select * from foo straight_join bar on 1 = 2'
assert f(s) == '\n'.join(
['select *', 'from foo', 'straight_join bar on 1 = 2'])
def test_issue90():
sql = ('UPDATE "gallery_photo" SET "owner_id" = 4018, "deleted_at" = NULL,'
' "width" = NULL, "height" = NULL, "rating_votes" = 0,'
' "rating_score" = 0, "thumbnail_width" = NULL,'
' "thumbnail_height" = NULL, "price" = 1, "description" = NULL')
formatted = bplsqlparse.format(sql, reindent=True)
tformatted = '\n'.join([
'UPDATE "gallery_photo"', 'SET "owner_id" = 4018,',
' "deleted_at" = NULL,', ' "width" = NULL,',
' "height" = NULL,', ' "rating_votes" = 0,',
' "rating_score" = 0,', ' "thumbnail_width" = NULL,',
' "thumbnail_height" = NULL,', ' "price" = 1,',
' "description" = NULL'
])
assert formatted == tformatted
def test_issue315_utf8_by_default():
# Make sure the lexer can handle utf-8 string by default correctly
# digest = '齐天大圣.カラフルな雲.사랑해요'
# The digest contains Chinese, Japanese and Korean characters
# All in 'utf-8' encoding.
digest = (
'\xe9\xbd\x90\xe5\xa4\xa9\xe5\xa4\xa7\xe5\x9c\xa3.'
'\xe3\x82\xab\xe3\x83\xa9\xe3\x83\x95\xe3\x83\xab\xe3\x81\xaa\xe9'
'\x9b\xb2.'
'\xec\x82\xac\xeb\x9e\x91\xed\x95\xb4\xec\x9a\x94')
sql = "select * from foo where bar = '{0}'".format(digest)
formatted = bplsqlparse.format(sql, reindent=True)
tformatted = "select *\nfrom foo\nwhere bar = '{0}'".format(digest)
if PY2:
tformatted = tformatted.decode('utf-8')
assert formatted == tformatted
def test_notransform_of_quoted_crlf(self):
# Make sure that CR/CR+LF characters inside string literals don't get
# affected by the formatter.
s1 = "SELECT some_column LIKE 'value\r'"
s2 = "SELECT some_column LIKE 'value\r'\r\nWHERE id = 1\n"
s3 = "SELECT some_column LIKE 'value\\'\r' WHERE id = 1\r"
s4 = "SELECT some_column LIKE 'value\\\\\\'\r' WHERE id = 1\r\n"
f = lambda x: bplsqlparse.format(x)
# Because of the use of
assert f(s1) == "SELECT some_column LIKE 'value\r'"
assert f(s2) == "SELECT some_column LIKE 'value\r'\nWHERE id = 1\n"
assert f(s3) == "SELECT some_column LIKE 'value\\'\r' WHERE id = 1\n"
assert (
f(s4) == "SELECT some_column LIKE 'value\\\\\\'\r' WHERE id = 1\n")
def test_preserve_ws(self):
# preserve at least one whitespace after subgroups
f = lambda sql: bplsqlparse.format(sql, strip_whitespace=True)
s = 'select\n* /* foo */ from bar '
assert f(s) == 'select * /* foo */ from bar'
