def test_select_expression(self):
# 1 2 3 4 5 6
# 0123456789012345678901234567890123456789012345678901234567890123456789
result = parse("SELECT a + b/2 + 45*c + (2/d) from dual")
expected = {
"select": {
"value": {
"add": [
"a", {
"div": ["b", {
"literal": 2
}]
}, {
"mult": [{
"literal": 45
}, "c"]
}, {
"div": [{
"literal": 2
}, "d"]
}
]
}
},
"from": "dual"
}
self.assertEqual(result, expected)
def parse_sql(sql):
query = wrap(moz_sql_parser.parse(sql))
# PULL OUT THE AGGREGATES
for s in listwrap(query.select):
val = s if s == "*" else s.value
# LOOK FOR GROUPBY COLUMN IN SELECT CLAUSE, REMOVE DUPLICATION
for g in listwrap(query.groupby):
try:
assertAlmostEqual(g.value, val, "")
g.name = s.name
s.value = None # MARK FOR REMOVAL
break
except Exception as e:
pass
if isinstance(val, Mapping):
for a in KNOWN_SQL_AGGREGATES:
if val[a]:
s.aggregate = a
s.value = val[a]
query.select = [
s for s in listwrap(query.select) if s == "*" or s.value != None
]
query.format = "table"
return query
def my_form_post():
sql_text = request.form['sql']
sql_text = sql_text.replace("`", "")
sql_text = format(sql_text, reindent=True, keyword_case='upper')
parsed_sql = parse(sql_text)
semi_sql_json = json.dumps(parsed_sql, sort_keys=False)
sql_json = json.dumps(parsed_sql, sort_keys=False, indent=4)
res_final = findkeys(semi_sql_json)
final = findtablenames(res_final)
tables_used = list(set(final))
columns = findcolumn(semi_sql_json)
html_list = jsontohtmllist(semi_sql_json)
processed_text = json2html.convert(
json=semi_sql_json,
table_attributes=
"id=\"info-table\" class=\"table table-bordered table-hover\"")
return render_template("index.html",
columns=columns,
html_list=html_list,
tables_used=tables_used,
processed_text=processed_text,
sql=sql_text,
json=sql_json)
def _parseQuery(self, sql):
self.struct = parse(sql)
if 'limit' in self.struct:
limit = self.struct.get('limit')
if isinstance(limit, int) is False:
raise SqlError('LIMIT must be integer')
if limit < 0:
raise SqlError('LIMIT must not be negative')
selectStatement = self.struct.get('select')
if isinstance(selectStatement, dict):
self.struct['select'] = [selectStatement]
fromStatements = self.struct.get('from')
if isinstance(fromStatements, list) is False:
fromStatements = [fromStatements]
self.struct['from'] = [
self._format_from_statement(x) for x in fromStatements
]
orderby = self.struct.get('orderby')
if isinstance(orderby, dict):
self.struct['orderby'] = [orderby]
def convert_string_to_expr(predicate_string):
from moz_sql_parser import parse
from pyparsing import ParseException
expr_map = {
"and": (Expressions.and_, ),
"eq": (Expressions.equal, ),
"exists": (Expressions.not_null, ),
"gt": (Expressions.greater_than, ),
"gte": (Expressions.greater_than_or_equal, ),
"lt": (Expressions.less_than, ),
"lte": (Expressions.less_than_or_equal, ),
"missing": (Expressions.is_null, ),
"neq": (Expressions.not_equal, ),
"not": (Expressions.not_, ),
"or": (Expressions.or_, )
}
dummy_query = "SELECT * FROM tbl WHERE {}".format(
predicate_string) # nosec
try:
expr = (Expressions._transform_to_binary_tuples(
Expressions._transform_between_op(
parse(dummy_query)["where"])))
return Expressions._get_expr(expr, expr_map)
except ParseException as pe:
_logger.error(
"Error parsing string expression into iceberg expression: %s" %
str(pe))
raise
def reverse_sql(query):
stmt = parse(query)
import json
# print(json.dumps(stmt, indent=4, sort_keys=True))
columns = parse_stmt(stmt)
return columns
def isSqlEqual(sql1,sql2,table):
q1 = parse(sql1)
q2 = parse(sql2)
sr1 = Construct(q1, table)
#切换约束
RC.change()
sr2 = Construct(q2, table)
print(sr1)
print(sr2)
rc = RC.get_rc_condition()
if isEqualUsingSMT(sr1,sr2,rc) == False:
print("not equal")
else:
print("equal")
def test_where_in_and_in(self):
# 1 2 3 4 5 6
# 0123456789012345678901234567890123456789012345678901234567890123456789
result = parse(
"SELECT a FROM dual WHERE a in ('r', 'g', 'b') AND b in (10, 11, 12)"
)
expected = {
"select": {
"value": "a"
},
"from": "dual",
"where": {
"and": [{
"in": [
"a",
[{
"literal": "r"
}, {
"literal": "g"
}, {
"literal": "b"
}]
]
}, {
"in": ["b", [10, 11, 12]]
}]
}
}
self.assertEqual(result, expected)
def test_issue5c_of_fork_date_cast_as_date(self):
sql = """
select
datediff('day', u.birth_date :: date, us.date_at :: date) as day_diff
from
users as u
inner join
user_sessions as us
"""
result = parse(sql)
self.assertEqual(
result,
{
"from": [
{"name": "u", "value": "users"},
{"inner join": {"name": "us", "value": "user_sessions"}},
],
"select": {
"name": "day_diff",
"value": {"datediff": [
{"literal": "day"},
{"cast": ["u.birth_date", {"date": {}}]},
{"cast": ["us.date_at", {"date": {}}]},
]},
},
},
)
def test_negative_number(self):
result = parse("select a from table1 where A=-900")
expected = {
'from': 'table1',
'where': {'eq': ['A', -900]},
'select': {'value': 'a'}
}
self.assertEqual(result, expected)
def test_neg_or_precedence(self):
result = parse("select B,C from table1 where A=-900 or B=100")
expected = {
'from': 'table1',
'where': {'or': [{'eq': ['A', -900]}, {'eq': ['B', 100]}]},
'select': [{'value': 'B'}, {'value': 'C'}]
}
self.assertEqual(result, expected)
def executer(self, sql_cmd):
"""
parse the sql cmd and execute it
"""
parsed_sql = parse(sql_cmd)
if 'select' not in parsed_sql:
raise ValueError('FileSqlManager not support non-select sql yet!')
self.__query_executor(parsed_sql.get('select', {}), parsed_sql.get('where', {}))
def test_pr19(self):
result = parse("select empid from emp where ename like 's%' ")
expected = {
'from': 'emp',
'where': {"like": ["ename", {"literal": "s%"}]},
'select': {"value": "empid"}
}
self.assertEqual(result, expected)
def test_order_by_asc(self):
result = parse("select count(1) from dual order by a asc")
expected = {
"select": {"value": {"count": 1}},
"from": "dual",
"orderby": {"value": "a", "sort": "asc"}
}
self.assertEqual(result, expected)
def test_issue5h_of_fork_extract(self):
# Ref: https://docs.aws.amazon.com/redshift/latest/dg/r_EXTRACT_function.html#r_EXTRACT_function-examples
sql = "select extract('epoch' from occurred_at)"
result = parse(sql)
self.assertEqual(
result,
{"select": {"value": {"extract": [{"literal": "epoch"}, "occurred_at"]}}},
)
def test_where_not_between(self):
result = parse("SELECT a FROM dual WHERE a NOT BETWEEN 1 and 2")
expected = {
"select": {"value": "a"},
"from": "dual",
"where": {"not_between": ["a", 1, 2]}
}
self.assertEqual(result, expected)
def test_like_in_where(self):
result = parse("select a from table1 where A like '%20%'")
expected = {
'from': 'table1',
'where': {'like': ['A', {"literal": "%20%"}]},
'select': {'value': 'a'}
}
self.assertEqual(result, expected)
def getParsedSQLQuery(context, db_version):
"""get parsed sql query"""
return parse(
re.sub(
r"\/\*[\s\S]*?\*\/",
"",
context.sql_query.replace("DB_VERSION", db_version),
))
def test_and_or(self):
sql = "SELECT * FROM dual WHERE a AND b or c"
result = parse(sql)
expected = {
"select": "*",
"from": "dual",
"where": {"or": [{"and": ["a", "b"]}, "c"]}
}
self.assertEqual(result, expected)
def test_or_and(self):
sql = "SELECT * FROM dual WHERE a OR b AND c"
result = parse(sql)
expected = {
"select": "*",
"from": "dual",
"where": {"or": ["a", {"and": ["b", "c"]}]}
}
self.assertEqual(result, expected)
def test_binary_not(self):
sql = "SELECT * FROM t WHERE ~c;"
result = parse(sql)
expected = {
"select": "*",
"from": "t",
"where": {"binary_not": "c"}
}
self.assertEqual(result, expected)
def test_binary_or(self):
sql = "SELECT * FROM t WHERE c | 4;"
result = parse(sql)
expected = {
"select": "*",
"from": "t",
"where": {"binary_or": ["c", 4]}
}
self.assertEqual(result, expected)
def test_dash_in_tablename(self):
try:
result = parse("select * from coverage-summary.source.file.covered limit 20")
self.assertTrue(False, "expecting to fail")
except Exception as e:
self.assertTrue(
all(v in str(e) for v in ["group by", "order by", "having", "limit", "where"]),
"expecting mention of other expected clauses"
)
def test_function(self):
# 0 1 2
# 0123456789012345678901234567890
result = parse("select count(1) from mytable")
expected = {
"select": {"value": {"count": 1}},
"from": "mytable"
}
self.assertEqual(result, expected)
def extract_tables(self, query):
try:
parsed = parse(query)
self.parse_with(parsed['with']),
self.parse_from(parsed['from'])
return self.froms
except ParseException:
# Maybe in the future warn about this
return []
def test_select_dots_names(self):
# 1 2 3 4 5 6
# 0123456789012345678901234567890123456789012345678901234567890123456789
result = parse("select a.b.c._d from dual")
expected = {
"select": {"value": "a.b.c._d"},
"from": "dual"
}
self.assertEqual(result, expected)
def test_function_underscore(self):
# 0 1 2
# 0123456789012345678901234567890
result = parse("select DATE_TRUNC('2019-04-12', WEEK) from mytable")
expected = {
'select': {'value': {'date_trunc': [{'literal': '2019-04-12'}, 'WEEK']}},
"from": "mytable"
}
self.assertEqual(result, expected)
def test_select_underscore_name(self):
# 1 2 3 4 5 6
# 0123456789012345678901234567890123456789012345678901234567890123456789
result = parse("select _id from dual")
expected = {
"select": {"value": "_id"},
"from": "dual"
}
self.assertEqual(result, expected)
def test_with_clause(self):
sql = (
" WITH dept_count AS ("
" SELECT deptno, COUNT(*) AS dept_count"
" FROM emp"
" GROUP BY deptno"
")"
" SELECT "
" e.ename AS employee_name,"
" dc1.dept_count AS emp_dept_count,"
" m.ename AS manager_name,"
" dc2.dept_count AS mgr_dept_count"
" FROM "
" emp e,"
" dept_count dc1,"
" emp m,"
" dept_count dc2"
" WHERE "
" e.deptno = dc1.deptno"
" AND e.mgr = m.empno"
" AND m.deptno = dc2.deptno;"
)
result = parse(sql)
expected = {
'with': {
'name': 'dept_count',
'value': {
'from': 'emp',
'groupby': {'value': 'deptno'},
'select': [
{'value': 'deptno'},
{'name': 'dept_count', 'value': {'count': '*'}}
]
}
},
'from': [
{'name': 'e', 'value': 'emp'},
{'name': 'dc1', 'value': 'dept_count'},
{'name': 'm', 'value': 'emp'},
{'name': 'dc2', 'value': 'dept_count'}
]
,
'select': [
{'name': 'employee_name', 'value': 'e.ename'},
{'name': 'emp_dept_count', 'value': 'dc1.dept_count'},
{'name': 'manager_name', 'value': 'm.ename'},
{'name': 'mgr_dept_count', 'value': 'dc2.dept_count'}
],
'where': {'and': [
{'eq': ['e.deptno', 'dc1.deptno']},
{'eq': ['e.mgr', 'm.empno']},
{'eq': ['m.deptno', 'dc2.deptno']}
]}
}
self.assertEqual(result, expected)
def test_select_quoted_name(self):
result = parse('Select a "@*#&", b as test."g.g".c from dual')
expected = {
"select": [
{"name": "@*#&", "value": "a"},
{"name": "test.g.g.c", "value": "b"}
],
"from": "dual"
}
self.assertEqual(result, expected)