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)
Beispiel #2
0
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
Beispiel #3
0
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)
Beispiel #4
0
    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]
Beispiel #5
0
    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
Beispiel #6
0
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")
Beispiel #8
0
 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)
Beispiel #9
0
    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": {}}]},
                    ]},
                },
            },
        )
Beispiel #10
0
 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)
Beispiel #11
0
 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)
Beispiel #12
0
 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', {}))
Beispiel #13
0
 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)
Beispiel #14
0
 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)
Beispiel #15
0
 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)
Beispiel #17
0
 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)
Beispiel #25
0
 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 []
Beispiel #26
0
 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)
Beispiel #28
0
 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)
Beispiel #30
0
 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)