jellezijlstra / sqltree Goto Github PK

https://docs.aws.amazon.com/redshift/latest/dg/r_patternmatching_condition_like.html

e.g.
with a as (select 1 from a union all select 2 from a) select 1

print(format("select 1 AS x y"))
SELECT 1 AS x y

should be select 1 AS x y

print(format("select x cross join y "))
Traceback (most recent call last):
File "", line 1, in
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/formatter.py", line 821, in format
sqltree(sql, dialect), dialect=dialect, line_length=line_length, indent=indent
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/api.py", line 12, in sqltree
return parse(tokens, dialect)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 964, in parse
return _parse_statement(p)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 984, in _parse_statement
_assert_done(p)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 970, in _assert_done
raise ParseError.from_unexpected_token(remaining, expected)
sqltree.parser.ParseError: Unexpected 'CROSS' (expected EOF)
0: select x cross join y

print(format("select 1E-2 "))
SELECT 1e - 2

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

select 1 from (select a from b) as a is working but select 1 from (select a from b) isn't

There should be a nicely usable API. Some ideas:

sqltree format "select *"  # prints formatted SQL
sqltree parse "select *"  # prints pretty-printed parse tree
sqltree translate --from=mysql --to=sqlite "select *"  # prints SQL changed to work with SQLite

MySQL supports queries like SELECT COUNT(*) c FROM t. We should support this but first I need to figure out what exactly the allowed syntax is.

print(format("select '''y' "))

print(format("select '''y' "))
Traceback (most recent call last):
File "", line 1, in
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/formatter.py", line 821, in format
sqltree(sql, dialect), dialect=dialect, line_length=line_length, indent=indent
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/api.py", line 12, in sqltree
return parse(tokens, dialect)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 964, in parse
return _parse_statement(p)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 984, in _parse_statement
_assert_done(p)
File "/Users/toby_mao/dev/sqlglot/env/lib/python3.8/site-packages/sqltree/parser.py", line 970, in _assert_done
raise ParseError.from_unexpected_token(remaining, expected)
sqltree.parser.ParseError: Unexpected "'y'" (expected EOF)
0: select '''y'

in sql, the escape for a quote is another quote

Provide a Transformer class deriving from Visitor that produces a new AST. The default transfoormer should just return a copy of the original tree.

print(format("select x from foo left join bar on foo.a = bar.a right join baz on baz.a = bar.b"))
SELECT x
FROM
foo
LEFTJOIN
bar
ON foo.a = bar.a
RIGHTJOIN
baz
ON baz.a = bar.b

should be left join and right join

sqltree should support multiple SQL dialects, as implemented by major databases. I'm interested initially in Redshift, Presto, and MySQL.

I added a Dialect enum, but that's probably not enough. We should also add version support (--dialect=mysql --version=5.7.0), because sometimes syntax changes between versions. Instead, there should be a Dialect class that gets passed along to the parser, tokenizer, formatter, etc. It should have methods for every syntactic detail that changes between dialects.

https://trino.io/docs/current/functions/datetime.html

We should provide a Fixit rule that formats SQL embedded in Python strings.

Ideas for how it would work:

• Configure a list of functions that map to Dialects (presto.query -> Presto dialect object); use that dialect for parsing and formatting the SQL.
• Also configure variable names: sql = "some string" -> format it with some default dialect
• Should also handle f-strings by using placeholders.