예제 #1
0
 def test_syntax_error_select_statement(self):
     query = "SELECT * FRO my_table"
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 10)
         self.assertEqual(e.message, 'syntax error at or near "FRO"')
예제 #2
0
 def test_incomplete_insert_statement(self):
     query = "INSERT INTO my_table"
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 21)
         self.assertEqual(e.message, 'syntax error at end of input')
예제 #3
0
 def test_incomplete_insert_statement(self):
     query = "INSERT INTO my_table"
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 21)
         self.assertEqual(e.message, 'syntax error at end of input')
예제 #4
0
 def test_syntax_error_select_statement(self):
     query = "SELECT * FRO my_table"
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 10)
         self.assertEqual(e.message, 'syntax error at or near "FRO"')
예제 #5
0
 def test_case_no_value(self):
     query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN "
              " ELSE 'other' END FROM test")
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 51)
         self.assertEqual(e.message, 'syntax error at or near "ELSE"')
예제 #6
0
def TestOneInput(data):
    fdp = atheris.FuzzedDataProvider(data)
    s = fdp.ConsumeString(sys.maxsize)
    try:
        parse(s)
    except PSqlParseError as e:
        None
    except UnicodeEncodeError as e:
        None
예제 #7
0
 def test_case_no_value(self):
     query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN "
              " ELSE 'other' END FROM test")
     try:
         parse(query)
         self.fail('Syntax error not generating an PSqlParseError')
     except PSqlParseError as e:
         self.assertEqual(e.cursorpos, 51)
         self.assertEqual(e.message, 'syntax error at or near "ELSE"')
예제 #8
0
    def test_select_is_null(self):
        query = "SELECT m.* FROM mytable m WHERE m.foo IS NULL"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertIsInstance(stmt.where_clause, nodes.NullTest)
        self.assertEqual(stmt.where_clause.nulltesttype, 0)

        query = "SELECT m.* FROM mytable m WHERE m.foo IS NOT NULL"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertIsInstance(stmt.where_clause, nodes.NullTest)
        self.assertEqual(stmt.where_clause.nulltesttype, 1)
예제 #9
0
    def test_select_is_null(self):
        query = "SELECT m.* FROM mytable m WHERE m.foo IS NULL"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertIsInstance(stmt.where_clause, nodes.NullTest)
        self.assertEqual(stmt.where_clause.nulltesttype, 0)

        query = "SELECT m.* FROM mytable m WHERE m.foo IS NOT NULL"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertIsInstance(stmt.where_clause, nodes.NullTest)
        self.assertEqual(stmt.where_clause.nulltesttype, 1)
예제 #10
0
def extract_nodes(content, pattern):
    class NS(object):
        pass
    ns = NS()
    ns.views = set()
    ns.dependencies = set()

    def inner(data, depth):
        if isinstance(data, dict):
            if 'relname' in data and 'schemaname' in data and re.search(pattern, data['relname']):
                entity = '.'.join([
                    data['schemaname'],
                    data['relname']
                ])
                if depth == 1:
                    ns.views.add(entity)
                else:
                    ns.dependencies.add(entity)
            else:
                for key, value in data.items():
                    inner(value, depth)
        elif isinstance(data, list) or isinstance(data, tuple):
            for item in data:
                inner(item, depth + 1)

    parsed_content = parse(content)
    inner(parsed_content, 0)
    return {
        'views': ns.views,
        'dependencies': ns.dependencies - ns.views
    }
예제 #11
0
    def test_select_union(self):
        query = "select * FROM table_one UNION select * FROM table_two"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertIsInstance(stmt.larg, nodes.SelectStmt)
        self.assertIsInstance(stmt.rarg, nodes.SelectStmt)
예제 #12
0
    def test_select_case(self):
        query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'"
                 " ELSE 'other' END FROM test")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 2)
예제 #13
0
 def test_select_where_in_many(self):
     query = (
         "SELECT * FROM my_table WHERE (a, b) in (('a', 'b'), ('c', 'd'))")
     stmt = parse(query).pop()
     self.assertEqual(2, len(stmt.where_clause.rexpr))
     for node in stmt.where_clause.rexpr:
         self.assertIsInstance(node, nodes.RowExpr)
예제 #14
0
    def test_select_case(self):
        query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'"
                 " ELSE 'other' END FROM test")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 2)
예제 #15
0
    def test_update_array(self):
        query = ("UPDATE tictactoe "
                 "SET board[1:3][1:3] = "
                 "'{{"
                 ","
                 ","
                 "},{"
                 ","
                 ","
                 "},{"
                 ","
                 ","
                 "}}' "
                 "WHERE game = 1")
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.UpdateStmt)
        self.assertEqual(len(stmt.target_list), 1)
        self.assertIsInstance(stmt.target_list[0], nodes.ResTarget)
        indirection = stmt.target_list[0].indirection
        self.assertEqual(len(indirection), 2)
        self.assertIsInstance(indirection[0], nodes.AIndices)
        self.assertIsInstance(indirection[1], nodes.AIndices)
        self.assertIsInstance(indirection[0].lidx, nodes.AConst)
        self.assertIsInstance(indirection[0].uidx, nodes.AConst)
예제 #16
0
 def test_update_subquery(self):
     query = ("UPDATE dataset SET a = 5 WHERE "
              "id IN (SELECT * from table_one) OR"
              " age IN (select * from table_two)")
     stmt = parse(query).pop()
     self.assertEqual(stmt.tables(),
                      {'table_one', 'table_two', 'dataset'})
예제 #17
0
    def test_select_union(self):
        query = "select * FROM table_one UNION select * FROM table_two"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertIsInstance(stmt.larg, nodes.SelectStmt)
        self.assertIsInstance(stmt.rarg, nodes.SelectStmt)
예제 #18
0
    def test_select_one_column_where(self):
        query = ("SELECT col1 FROM my_table "
                 "WHERE my_attribute LIKE 'condition'"
                 " AND other = 5.6 AND extra > 5")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 1)
        target = stmt.target_list[0]
        self.assertIsInstance(target, nodes.ResTarget)
        self.assertEqual(target.val.fields[0].val, 'col1')

        self.assertIsInstance(stmt.where_clause, nodes.BoolExpr)
        self.assertEqual(len(stmt.where_clause.args), 3)

        one = stmt.where_clause.args[0]
        self.assertIsInstance(one, nodes.AExpr)
        self.assertEqual(str(one.lexpr.fields[0].val), 'my_attribute')
        self.assertEqual(str(one.name[0]), '~~')
        self.assertEqual(str(one.rexpr.val), 'condition')

        two = stmt.where_clause.args[1]
        self.assertIsInstance(two, nodes.AExpr)
        self.assertEqual(str(two.lexpr.fields[0].val), 'other')
        self.assertEqual(str(two.name[0]), '=')
        self.assertEqual(float(two.rexpr.val), 5.6)

        three = stmt.where_clause.args[2]
        self.assertIsInstance(three, nodes.AExpr)
        self.assertEqual(str(three.lexpr.fields[0].val), 'extra')
        self.assertEqual(str(three.name[0]), '>')
        self.assertEqual(int(three.rexpr.val), 5)
예제 #19
0
 def test_update_subquery(self):
     query = ("UPDATE dataset SET a = 5 WHERE "
              "id IN (SELECT * from table_one) OR"
              " age IN (select * from table_two)")
     stmt = parse(query).pop()
     self.assertEqual(stmt.tables(),
                      {'table_one', 'table_two', 'dataset'})
예제 #20
0
    def test_select_one_column_where(self):
        query = ("SELECT col1 FROM my_table "
                 "WHERE my_attribute LIKE 'condition'"
                 " AND other = 5.6 AND extra > 5")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 1)
        target = stmt.target_list[0]
        self.assertIsInstance(target, nodes.ResTarget)
        self.assertEqual(target.val.fields[0].val, 'col1')

        self.assertIsInstance(stmt.where_clause, nodes.BoolExpr)
        self.assertEqual(len(stmt.where_clause.args), 3)

        one = stmt.where_clause.args[0]
        self.assertIsInstance(one, nodes.AExpr)
        self.assertEqual(str(one.lexpr.fields[0].val), 'my_attribute')
        self.assertEqual(str(one.name[0]), '~~')
        self.assertEqual(str(one.rexpr.val), 'condition')

        two = stmt.where_clause.args[1]
        self.assertIsInstance(two, nodes.AExpr)
        self.assertEqual(str(two.lexpr.fields[0].val), 'other')
        self.assertEqual(str(two.name[0]), '=')
        self.assertEqual(float(two.rexpr.val), 5.6)

        three = stmt.where_clause.args[2]
        self.assertIsInstance(three, nodes.AExpr)
        self.assertEqual(str(three.lexpr.fields[0].val), 'extra')
        self.assertEqual(str(three.name[0]), '>')
        self.assertEqual(int(three.rexpr.val), 5)
예제 #21
0
    def test_select_order_by(self):
        query = "SELECT * FROM my_table ORDER BY field DESC NULLS FIRST"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.sort_clause), 1)
        self.assertIsInstance(stmt.sort_clause[0], nodes.SortBy)
        self.assertIsInstance(stmt.sort_clause[0].node, nodes.ColumnRef)
        self.assertEqual(stmt.sort_clause[0].sortby_dir, 2)
        self.assertEqual(stmt.sort_clause[0].sortby_nulls, 1)

        query = "SELECT * FROM my_table ORDER BY field USING @>"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt.sort_clause[0], nodes.SortBy)
        self.assertIsInstance(stmt.sort_clause[0].node, nodes.ColumnRef)
        self.assertEqual(len(stmt.sort_clause[0].use_op), 1)
        self.assertIsInstance(stmt.sort_clause[0].use_op[0], nodes.String)
        self.assertEqual(stmt.sort_clause[0].use_op[0].val, '@>')
예제 #22
0
    def test_update_to_default(self):
        query = "UPDATE my_table SET the_value = DEFAULT"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.UpdateStmt)
        self.assertEqual(len(stmt.target_list), 1)
        self.assertIsInstance(stmt.target_list[0], nodes.ResTarget)
        self.assertIsInstance(stmt.target_list[0].val, nodes.SetToDefault)
예제 #23
0
 def test_select_with(self):
     query = ("WITH fake_table AS (SELECT SUM(countable) AS total "
              "FROM inner_table GROUP BY groupable) "
              "SELECT * FROM fake_table")
     stmt = parse(query).pop()
     self.assertEqual('SelectStmt',
                      stmt.with_clause.queries['fake_table'].type)
     self.assertIsNone(stmt.with_clause.recursive)
예제 #24
0
    def test_select_order_by(self):
        query = "SELECT * FROM my_table ORDER BY field DESC NULLS FIRST"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.sort_clause), 1)
        self.assertIsInstance(stmt.sort_clause[0], nodes.SortBy)
        self.assertIsInstance(stmt.sort_clause[0].node, nodes.ColumnRef)
        self.assertEqual(stmt.sort_clause[0].sortby_dir, 2)
        self.assertEqual(stmt.sort_clause[0].sortby_nulls, 1)

        query = "SELECT * FROM my_table ORDER BY field USING @>"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt.sort_clause[0], nodes.SortBy)
        self.assertIsInstance(stmt.sort_clause[0].node, nodes.ColumnRef)
        self.assertEqual(len(stmt.sort_clause[0].use_op), 1)
        self.assertIsInstance(stmt.sort_clause[0].use_op[0], nodes.String)
        self.assertEqual(stmt.sort_clause[0].use_op[0].val, '@>')
예제 #25
0
 def test_select_all_no_where(self):
     query = "SELECT * FROM my_table"
     stmt = parse(query).pop()
     self.assertEqual(stmt.type, "SelectStmt")
     self.assertIsNone(stmt.where_clause)
     target = stmt.target_list.targets.pop()
     self.assertDictEqual({'A_Star': {}},
                          target['val']['ColumnRef']['fields'][0])
예제 #26
0
    def test_update_to_default(self):
        query = "UPDATE my_table SET the_value = DEFAULT"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.UpdateStmt)
        self.assertEqual(len(stmt.target_list), 1)
        self.assertIsInstance(stmt.target_list[0], nodes.ResTarget)
        self.assertIsInstance(stmt.target_list[0].val, nodes.SetToDefault)
예제 #27
0
def main():
    statements = psqlparse.parse(
        'select col1+col2  from persona as tabla1 , departamento where edad>35 and city="Paris" or sexo is not null'
    )
    allData = list()
    for statement in statements:
        allData.append(get_info(statement))
    select_match_case(allData[0]['fields'])
    print('done')
예제 #28
0
    def test_insert_returning(self):
        query = "INSERT INTO my_table(id) VALUES (5) RETURNING id, \"date\""
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)
        self.assertEqual(len(stmt.returning_list), 2)
        self.assertIsInstance(stmt.returning_list[0], nodes.ResTarget)
        self.assertEqual(str(stmt.returning_list[0].val.fields[0]), 'id')
        self.assertIsInstance(stmt.returning_list[1], nodes.ResTarget)
        self.assertEqual(str(stmt.returning_list[1].val.fields[0]), 'date')
예제 #29
0
    def test_insert_returning(self):
        query = "INSERT INTO my_table(id) VALUES (5) RETURNING id, \"date\""
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)
        self.assertEqual(len(stmt.returning_list), 2)
        self.assertIsInstance(stmt.returning_list[0], nodes.ResTarget)
        self.assertEqual(str(stmt.returning_list[0].val.fields[0]), 'id')
        self.assertIsInstance(stmt.returning_list[1], nodes.ResTarget)
        self.assertEqual(str(stmt.returning_list[1].val.fields[0]), 'date')
예제 #30
0
    def test_select_locks(self):
        query = "SELECT m.* FROM mytable m FOR UPDATE"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.locking_clause), 1)
        self.assertIsInstance(stmt.locking_clause[0], nodes.LockingClause)
        self.assertEqual(stmt.locking_clause[0].strength, 4)

        query = "SELECT m.* FROM mytable m FOR SHARE of m nowait"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.locking_clause), 1)
        self.assertIsInstance(stmt.locking_clause[0], nodes.LockingClause)
        self.assertEqual(stmt.locking_clause[0].strength, 2)
        self.assertEqual(len(stmt.locking_clause[0].locked_rels), 1)
        self.assertIsInstance(stmt.locking_clause[0].locked_rels[0],
                              nodes.RangeVar)
        self.assertEqual(stmt.locking_clause[0].locked_rels[0].relname, 'm')
        self.assertEqual(stmt.locking_clause[0].wait_policy, 2)
예제 #31
0
    def test_function_call(self):
        query = "SELECT * FROM my_table WHERE ST_Intersects(geo1, geo2)"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        func_call = stmt.where_clause
        self.assertIsInstance(func_call, nodes.FuncCall)
        self.assertEqual(str(func_call.funcname[0]), 'st_intersects')
        self.assertEqual(str(func_call.args[0].fields[0]), 'geo1')
        self.assertEqual(str(func_call.args[1].fields[0]), 'geo2')
예제 #32
0
    def test_function_call(self):
        query = "SELECT * FROM my_table WHERE ST_Intersects(geo1, geo2)"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        func_call = stmt.where_clause
        self.assertIsInstance(func_call, nodes.FuncCall)
        self.assertEqual(str(func_call.funcname[0]), 'st_intersects')
        self.assertEqual(str(func_call.args[0].fields[0]), 'geo1')
        self.assertEqual(str(func_call.args[1].fields[0]), 'geo2')
예제 #33
0
 def test_select_one_column_where(self):
     query = "SELECT col1 FROM my_table WHERE my_attribute LIKE condition"
     stmt = parse(query).pop()
     self.assertEqual(stmt.type, "SelectStmt")
     self.assertIsInstance(stmt.where_clause, WhereClause)
     target = stmt.target_list.targets.pop()
     self.assertDictEqual({'str': 'col1'},
                          target['val']['ColumnRef']['fields'][0]['String'])
     self.assertDictEqual({'str': '~~'},
                          stmt.where_clause.obj['A_Expr']['name'][0]['String'])
예제 #34
0
    def test_select_locks(self):
        query = "SELECT m.* FROM mytable m FOR UPDATE"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.locking_clause), 1)
        self.assertIsInstance(stmt.locking_clause[0], nodes.LockingClause)
        self.assertEqual(stmt.locking_clause[0].strength, 4)

        query = "SELECT m.* FROM mytable m FOR SHARE of m nowait"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.locking_clause), 1)
        self.assertIsInstance(stmt.locking_clause[0], nodes.LockingClause)
        self.assertEqual(stmt.locking_clause[0].strength, 2)
        self.assertEqual(len(stmt.locking_clause[0].locked_rels), 1)
        self.assertIsInstance(stmt.locking_clause[0].locked_rels[0],
                              nodes.RangeVar)
        self.assertEqual(stmt.locking_clause[0].locked_rels[0].relname, 'm')
        self.assertEqual(stmt.locking_clause[0].wait_policy, 2)
예제 #35
0
 def test_select_range_function(self):
     query = ("SELECT m.name AS mname, pname "
              "FROM manufacturers m, LATERAL get_product_names(m.id) pname")
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(len(stmt.from_clause), 2)
     second = stmt.from_clause[1]
     self.assertIsInstance(second, nodes.RangeFunction)
     self.assertTrue(second.lateral)
     self.assertEqual(len(second.functions), 1)
     self.assertEqual(len(second.functions[0]), 2)
     self.assertIsInstance(second.functions[0][0], nodes.FuncCall)
예제 #36
0
 def test_select_range_function(self):
     query = ("SELECT m.name AS mname, pname "
              "FROM manufacturers m, LATERAL get_product_names(m.id) pname")
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(len(stmt.from_clause), 2)
     second = stmt.from_clause[1]
     self.assertIsInstance(second, nodes.RangeFunction)
     self.assertTrue(second.lateral)
     self.assertEqual(len(second.functions), 1)
     self.assertEqual(len(second.functions[0]), 2)
     self.assertIsInstance(second.functions[0][0], nodes.FuncCall)
예제 #37
0
    def test_insert_select(self):
        query = "INSERT INTO my_table(id, name) SELECT 1, 'some'"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)

        self.assertIsInstance(stmt.select_stmt, nodes.SelectStmt)
        targets = stmt.select_stmt.target_list
        self.assertEqual(len(targets), 2)
        self.assertIsInstance(targets[0], nodes.ResTarget)
        self.assertEqual(int(targets[0].val.val), 1)
        self.assertIsInstance(targets[1], nodes.ResTarget)
        self.assertEqual(str(targets[1].val.val), 'some')
예제 #38
0
    def test_select_subquery(self):
        query = "SELECT * FROM (SELECT something FROM dataset) AS other"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.from_clause), 1)
        sub_query = stmt.from_clause[0]
        self.assertIsInstance(sub_query, nodes.RangeSubselect)
        self.assertIsInstance(sub_query.alias, nodes.Alias)
        self.assertEqual(sub_query.alias.aliasname, 'other')
        self.assertIsInstance(sub_query.subquery, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 1)
예제 #39
0
 def test_select_type_cast(self):
     query = "SELECT 'accbf276-705b-11e7-b8e4-0242ac120002'::UUID"
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(len(stmt.target_list), 1)
     target = stmt.target_list[0]
     self.assertIsInstance(target, nodes.ResTarget)
     self.assertIsInstance(target.val, nodes.TypeCast)
     self.assertIsInstance(target.val.arg, nodes.AConst)
     self.assertEqual(target.val.arg.val.val,
                      'accbf276-705b-11e7-b8e4-0242ac120002')
     self.assertIsInstance(target.val.type_name, nodes.TypeName)
     self.assertEqual(target.val.type_name.names[0].val, "uuid")
예제 #40
0
    def test_select_subquery(self):
        query = "SELECT * FROM (SELECT something FROM dataset) AS other"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.from_clause), 1)
        sub_query = stmt.from_clause[0]
        self.assertIsInstance(sub_query, nodes.RangeSubselect)
        self.assertIsInstance(sub_query.alias, nodes.Alias)
        self.assertEqual(sub_query.alias.aliasname, 'other')
        self.assertIsInstance(sub_query.subquery, nodes.SelectStmt)

        self.assertEqual(len(stmt.target_list), 1)
예제 #41
0
 def test_select_type_cast(self):
     query = "SELECT 'accbf276-705b-11e7-b8e4-0242ac120002'::UUID"
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(len(stmt.target_list), 1)
     target = stmt.target_list[0]
     self.assertIsInstance(target, nodes.ResTarget)
     self.assertIsInstance(target.val, nodes.TypeCast)
     self.assertIsInstance(target.val.arg, nodes.AConst)
     self.assertEqual(target.val.arg.val.val,
                      'accbf276-705b-11e7-b8e4-0242ac120002')
     self.assertIsInstance(target.val.type_name, nodes.TypeName)
     self.assertEqual(target.val.type_name.names[0].val, "uuid")
예제 #42
0
    def test_insert_select(self):
        query = "INSERT INTO my_table(id, name) SELECT 1, 'some'"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)

        self.assertIsInstance(stmt.select_stmt, nodes.SelectStmt)
        targets = stmt.select_stmt.target_list
        self.assertEqual(len(targets), 2)
        self.assertIsInstance(targets[0], nodes.ResTarget)
        self.assertEqual(int(targets[0].val.val), 1)
        self.assertIsInstance(targets[1], nodes.ResTarget)
        self.assertEqual(str(targets[1].val.val), 'some')
예제 #43
0
    def test_select_case(self):
        query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'"
                 " ELSE 'other' END FROM test")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.CaseExpr)
        self.assertIsNone(target.val.arg)
        self.assertEqual(len(target.val.args), 2)
        self.assertIsInstance(target.val.args[0], nodes.CaseWhen)
        self.assertIsInstance(target.val.args[0].expr, nodes.AExpr)
        self.assertIsInstance(target.val.args[0].result, nodes.AConst)
        self.assertIsInstance(target.val.defresult, nodes.AConst)

        query = ("SELECT CASE a.value WHEN 0 THEN '1' ELSE '2' END FROM "
                 "sometable a")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 1)
        target = stmt.target_list[0]
        self.assertIsInstance(target.val, nodes.CaseExpr)
        self.assertIsInstance(target.val.arg, nodes.ColumnRef)
예제 #44
0
    def test_select_join(self):
        query = "SELECT * FROM table_one JOIN table_two USING (common)"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.from_clause), 1)
        join_expr = stmt.from_clause[0]
        self.assertIsInstance(join_expr, nodes.JoinExpr)

        self.assertIsInstance(join_expr.larg, nodes.RangeVar)
        self.assertEqual(join_expr.larg.relname, 'table_one')

        self.assertIsInstance(join_expr.rarg, nodes.RangeVar)
        self.assertEqual(join_expr.rarg.relname, 'table_two')
예제 #45
0
    def test_select_with(self):
        query = ("WITH fake_table AS (SELECT SUM(countable) AS total "
                 "FROM inner_table GROUP BY groupable) "
                 "SELECT * FROM fake_table")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertIsInstance(stmt.with_clause, nodes.WithClause)
        self.assertEqual(len(stmt.with_clause.ctes), 1)
        self.assertIsNone(stmt.with_clause.recursive)

        with_query = stmt.with_clause.ctes[0]
        self.assertEqual(with_query.ctename, 'fake_table')
        self.assertIsInstance(with_query.ctequery, nodes.SelectStmt)
예제 #46
0
    def test_select_case(self):
        query = ("SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two'"
                 " ELSE 'other' END FROM test")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.CaseExpr)
        self.assertIsNone(target.val.arg)
        self.assertEqual(len(target.val.args), 2)
        self.assertIsInstance(target.val.args[0], nodes.CaseWhen)
        self.assertIsInstance(target.val.args[0].expr, nodes.AExpr)
        self.assertIsInstance(target.val.args[0].result, nodes.AConst)
        self.assertIsInstance(target.val.defresult, nodes.AConst)

        query = ("SELECT CASE a.value WHEN 0 THEN '1' ELSE '2' END FROM "
                 "sometable a")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 1)
        target = stmt.target_list[0]
        self.assertIsInstance(target.val, nodes.CaseExpr)
        self.assertIsInstance(target.val.arg, nodes.ColumnRef)
예제 #47
0
    def test_select_with(self):
        query = ("WITH fake_table AS (SELECT SUM(countable) AS total "
                 "FROM inner_table GROUP BY groupable) "
                 "SELECT * FROM fake_table")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertIsInstance(stmt.with_clause, nodes.WithClause)
        self.assertEqual(len(stmt.with_clause.ctes), 1)
        self.assertIsNone(stmt.with_clause.recursive)

        with_query = stmt.with_clause.ctes[0]
        self.assertEqual(with_query.ctename, 'fake_table')
        self.assertIsInstance(with_query.ctequery, nodes.SelectStmt)
예제 #48
0
    def test_select_join(self):
        query = "SELECT * FROM table_one JOIN table_two USING (common)"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.from_clause), 1)
        join_expr = stmt.from_clause[0]
        self.assertIsInstance(join_expr, nodes.JoinExpr)

        self.assertIsInstance(join_expr.larg, nodes.RangeVar)
        self.assertEqual(join_expr.larg.relname, 'table_one')

        self.assertIsInstance(join_expr.rarg, nodes.RangeVar)
        self.assertEqual(join_expr.rarg.relname, 'table_two')
예제 #49
0
    def test_select_window(self):
        query = "SELECT salary, sum(salary) OVER () FROM empsalary"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertIsNone(target.val.over.order_clause)
        self.assertIsNone(target.val.over.partition_clause)

        query = ("SELECT salary, sum(salary) "
                 "OVER (ORDER BY salary) "
                 "FROM empsalary")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertEqual(len(target.val.over.order_clause), 1)
        self.assertIsInstance(target.val.over.order_clause[0], nodes.SortBy)
        self.assertIsNone(target.val.over.partition_clause)

        query = ("SELECT salary, avg(salary) "
                 "OVER (PARTITION BY depname) "
                 "FROM empsalary")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertIsNone(target.val.over.order_clause)
        self.assertEqual(len(target.val.over.partition_clause), 1)
        self.assertIsInstance(target.val.over.partition_clause[0],
                              nodes.ColumnRef)
예제 #50
0
    def test_select_window(self):
        query = "SELECT salary, sum(salary) OVER () FROM empsalary"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertIsNone(target.val.over.order_clause)
        self.assertIsNone(target.val.over.partition_clause)

        query = ("SELECT salary, sum(salary) "
                 "OVER (ORDER BY salary) "
                 "FROM empsalary")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertEqual(len(target.val.over.order_clause), 1)
        self.assertIsInstance(target.val.over.order_clause[0], nodes.SortBy)
        self.assertIsNone(target.val.over.partition_clause)

        query = ("SELECT salary, avg(salary) "
                 "OVER (PARTITION BY depname) "
                 "FROM empsalary")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.target_list), 2)
        target = stmt.target_list[1]
        self.assertIsInstance(target.val, nodes.FuncCall)
        self.assertIsInstance(target.val.over, nodes.WindowDef)
        self.assertIsNone(target.val.over.order_clause)
        self.assertEqual(len(target.val.over.partition_clause), 1)
        self.assertIsInstance(target.val.over.partition_clause[0],
                              nodes.ColumnRef)
예제 #51
0
    def test_insert_no_where(self):
        query = "INSERT INTO my_table(id, name) VALUES(1, 'some')"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)
        self.assertIsNone(stmt.returning_list)

        self.assertEqual(stmt.relation.relname, 'my_table')

        self.assertEqual(len(stmt.cols), 2)
        self.assertEqual(stmt.cols[0].name, 'id')
        self.assertEqual(stmt.cols[1].name, 'name')

        self.assertIsInstance(stmt.select_stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.select_stmt.values_lists), 1)
예제 #52
0
 def test_select_array(self):
     query = ("SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) "
              "WITH ORDINALITY")
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(len(stmt.from_clause), 1)
     outer = stmt.from_clause[0]
     self.assertIsInstance(outer, nodes.RangeFunction)
     self.assertTrue(outer.ordinality)
     self.assertEqual(len(outer.functions), 1)
     inner = outer.functions[0][0]
     self.assertIsInstance(inner, nodes.FuncCall)
     self.assertEqual(len(inner.args), 1)
     self.assertIsInstance(inner.args[0], nodes.AArrayExpr)
     self.assertEqual(len(inner.args[0].elements), 6)
예제 #53
0
    def test_insert_no_where(self):
        query = "INSERT INTO my_table(id, name) VALUES(1, 'some')"
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.InsertStmt)
        self.assertIsNone(stmt.returning_list)

        self.assertEqual(stmt.relation.relname, 'my_table')

        self.assertEqual(len(stmt.cols), 2)
        self.assertEqual(stmt.cols[0].name, 'id')
        self.assertEqual(stmt.cols[1].name, 'name')

        self.assertIsInstance(stmt.select_stmt, nodes.SelectStmt)
        self.assertEqual(len(stmt.select_stmt.values_lists), 1)
예제 #54
0
    def test_select_all_no_where(self):
        query = "SELECT * FROM my_table"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertIsNone(stmt.where_clause)

        self.assertEqual(len(stmt.target_list), 1)
        target = stmt.target_list[0]
        self.assertIsInstance(target, nodes.ResTarget)
        self.assertIsInstance(target.val.fields[0], nodes.AStar)

        self.assertEqual(len(stmt.from_clause), 1)
        from_clause = stmt.from_clause[0]
        self.assertIsInstance(from_clause, nodes.RangeVar)
        self.assertEqual(from_clause.relname, 'my_table')
예제 #55
0
    def test_update_array(self):
        query = ("UPDATE tictactoe "
                 "SET board[1:3][1:3] = "
                 "'{{" "," "," "},{" "," "," "},{" "," "," "}}' "
                 "WHERE game = 1")
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.UpdateStmt)
        self.assertEqual(len(stmt.target_list), 1)
        self.assertIsInstance(stmt.target_list[0], nodes.ResTarget)
        indirection = stmt.target_list[0].indirection
        self.assertEqual(len(indirection), 2)
        self.assertIsInstance(indirection[0], nodes.AIndices)
        self.assertIsInstance(indirection[1], nodes.AIndices)
        self.assertIsInstance(indirection[0].lidx, nodes.AConst)
        self.assertIsInstance(indirection[0].uidx, nodes.AConst)
예제 #56
0
    def test_update_multi_assign(self):
        query = ("UPDATE accounts "
                 "SET (contact_first_name, contact_last_name) "
                 "= (SELECT first_name, last_name FROM salesmen "
                 "WHERE salesmen.id = accounts.sales_id)")
        stmt = parse(query).pop()

        self.assertIsInstance(stmt, nodes.UpdateStmt)
        self.assertEqual(len(stmt.target_list), 2)
        self.assertIsInstance(stmt.target_list[0], nodes.ResTarget)
        first = stmt.target_list[0]
        self.assertIsInstance(first, nodes.ResTarget)
        self.assertEqual(first.name, 'contact_first_name')
        self.assertIsInstance(first.val, nodes.MultiAssignRef)
        self.assertEqual(first.val.ncolumns, 2)
        self.assertEqual(first.val.colno, 1)
        self.assertIsInstance(first.val.source, nodes.SubLink)
        self.assertIsInstance(first.val.source.subselect, nodes.SelectStmt)
예제 #57
0
    def test_select_from_values(self):
        query = ("SELECT * FROM "
                 "(VALUES (1, 'one'), (2, 'two')) AS t (num, letter)")
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(len(stmt.from_clause), 1)
        self.assertIsInstance(stmt.from_clause[0], nodes.RangeSubselect)

        alias = stmt.from_clause[0].alias
        self.assertIsInstance(alias, nodes.Alias)
        self.assertEqual(alias.aliasname, 't')
        self.assertEqual(['num', 'letter'], [str(v) for v in alias.colnames])

        subquery = stmt.from_clause[0].subquery
        self.assertIsInstance(subquery, nodes.SelectStmt)
        self.assertEqual(len(subquery.values_lists), 2)
        self.assertEqual([1, 'one'], [v.val.val
                                      for v in subquery.values_lists[0]])
예제 #58
0
 def test_where_in_expr(self):
     query = "SELECT * FROM my_table WHERE (a, b) in ('a', 'b')"
     stmt = parse(query).pop()
     self.assertIsInstance(stmt, nodes.SelectStmt)
     self.assertEqual(stmt.tables(), {'my_table'})
예제 #59
0
    def test_select_union(self):
        query = "select * FROM table_one UNION select * FROM table_two"
        stmt = parse(query).pop()
        self.assertIsInstance(stmt, nodes.SelectStmt)

        self.assertEqual(stmt.tables(), {'table_one', 'table_two'})
예제 #60
0
 def test_delete(self):
     query = ("DELETE FROM dataset USING table_one "
              "WHERE x = y OR x IN (SELECT * from table_two)")
     stmt = parse(query).pop()
     self.assertEqual(stmt.tables(), {'dataset', 'table_one',
                                      'table_two'})