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'