def test_cte_does_not_crash(self): sql = 'WITH CTE AS (SELECT F.* FROM Foo F WHERE F.Bar > 23) SELECT C.* FROM CTE C WHERE C.FooID BETWEEN 123 AND 234;' for i in range(len(sql)): try: suggest_type(sql[:i + 1], sql[:i + 1]) except Exception as e: self.fail('Failed with %s' % e)
def test_3_statements_2nd_current(self): suggestions = suggest_type('select * from a; select * from ; select * from c', 'select * from a; select * from ') self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ])) suggestions = suggest_type('select * from a; select from b; select * from c', 'select * from a; select ') self.assertSetEqual(set(suggestions), cols_etc('b', last_keyword='SELECT'))
def test_into_suggests_tables_and_schemas(self): suggestions = suggest_type('INSERT INTO ', 'INSERT INTO ') self.assertSetEqual(set(suggestions), set([ Table(schema=None), View(schema=None), Schema(), ]))
def test_sub_select_table_name_completion_with_outer_table(self, expression): suggestions = suggest_type(expression, expression) tbls = tuple([(None, 'foo', None, False)]) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None, table_refs=tbls), Schema(), ]))
def test_suggest_qualified_tables_views_functions_and_joins(self, expression): suggestions = suggest_type(expression, expression) tbls = tuple([(None, 'foo', None, False)]) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema='sch', table_refs=tbls), Join(tbls, 'sch'), ]))
def test_col_comma_suggests_cols(self): suggestions = suggest_type('SELECT a, b, FROM tbl', 'SELECT a, b,') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'tbl', None, False),), qualifiable=True), Function(schema=None), Keyword('SELECT'), ]))
def test_table_comma_suggests_tables_and_schemas(self): suggestions = suggest_type('SELECT a, b FROM tbl1, ', 'SELECT a, b FROM tbl1, ') self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ]))
def test_statements_in_function_body(self, text): suggestions = suggest_type(text, text[: text.find(' ') + 1]) self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'foo', None, False),), qualifiable=True), Function(schema=None), Keyword('SELECT'), ]))
def test_handle_unrecognized_kw_generously(self): sql = 'SELECT * FROM sessions WHERE session = 1 AND ' suggestions = suggest_type(sql, sql) expected = Column(table_refs=((None, 'sessions', None, False),), qualifiable=True) self.assertTrue(expected in set(suggestions))
def test_distinct_suggests_cols(self, text): suggestions = suggest_type(text, text) self.assertSetEqual(set(suggestions), set([ Column(table_refs=(), local_tables=(), qualifiable=True), Function(schema=None), Keyword('DISTINCT') ]))
def test_suggests_tables_views_and_schemas(self, expression): suggestions = suggest_type(expression, expression) self.assertSetEqual(set(suggestions), set([ Table(schema=None), View(schema=None), Schema(), ]))
def test_select_suggests_cols_and_funcs(self): suggestions = suggest_type('SELECT ', 'SELECT ') self.assertSetEqual(set(suggestions), set([ Column(table_refs=(), qualifiable=True), Function(schema=None), Keyword('SELECT'), ]))
def test_dot_suggests_cols_of_an_alias_where(self, sql): suggestions = suggest_type(sql, sql) self.assertSetEqual(set(suggestions), set([ Table(schema='t1'), View(schema='t1'), Column(table_refs=((None, 'tabl1', 't1', False),)), Function(schema='t1'), ]))
def test_insert_into_lparen_partial_text_suggests_cols(self): suggestions = suggest_type('INSERT INTO abc (i', 'INSERT INTO abc (i') self.assertEqual(suggestions, ( Column( table_refs=((None, 'abc', None, False),), context='insert' ), ))
def test_join_alias_dot_suggests_cols2(self, sql): suggestions = suggest_type(sql, sql) self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'def', 'd', False),)), Table(schema='d'), View(schema='d'), Function(schema='d'), ]))
def test_dot_suggests_cols_of_a_table_or_schema_qualified_table(self): suggestions = suggest_type('SELECT tabl. FROM tabl', 'SELECT tabl.') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'tabl', None, False),)), Table(schema='tabl'), View(schema='tabl'), Function(schema='tabl'), ]))
def test_suggest_after_join_with_one_table(self, expression): suggestions = suggest_type(expression, expression) tables = ((None, 'foo', None, False),) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None, table_refs=tables), Join(((None, 'foo', None, False),), None), Schema(), ]))
def test_sub_select_col_name_completion(self): suggestions = suggest_type('SELECT * FROM (SELECT FROM abc', 'SELECT * FROM (SELECT ') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'abc', None, False),), qualifiable=True), Function(schema=None), Keyword('SELECT'), ]))
def test_suggest_after_join_with_two_tables(self, expression): suggestions = suggest_type(expression, expression) tables = tuple([(None, 'foo', None, False), (None, 'bar', None, False)]) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None, table_refs=tables), Join(tables, None), Schema(), ]))
def test_sub_select_dot_col_name_completion(self): suggestions = suggest_type('SELECT * FROM (SELECT t. FROM tabl t', 'SELECT * FROM (SELECT t.') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'tabl', 't', False),)), Table(schema='t'), View(schema='t'), Function(schema='t'), ]))
def test_dot_col_comma_suggests_cols_or_schema_qualified_table(self): suggestions = suggest_type('SELECT t1.a, t2. FROM tabl1 t1, tabl2 t2', 'SELECT t1.a, t2.') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'tabl2', 't2', False),)), Table(schema='t2'), View(schema='t2'), Function(schema='t2'), ]))
def test_outer_table_reference_in_exists_subquery_suggests_columns(self): q = 'SELECT * FROM foo f WHERE EXISTS (SELECT 1 FROM bar WHERE f.' suggestions = suggest_type(q, q) self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'foo', 'f', False),)), Table(schema='f'), View(schema='f'), Function(schema='f'), ]))
def test_suggest_columns_after_multiple_joins(self): sql = '''select * from t1 inner join t2 ON t1.id = t2.t1_id inner join t3 ON t2.id = t3.''' suggestions = suggest_type(sql, sql) self.assertTrue( Column(table_refs=((None, 't3', None, False),)) in set(suggestions))
def test_left_join_with_comma(self): text = 'select * from foo f left join bar b,' suggestions = suggest_type(text, text) # tbls should also include (None, 'bar', 'b', False) # but there's a bug with commas tbls = tuple([(None, 'foo', 'f', False)]) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None, table_refs=tbls), Schema(), ]))
def test_join_alias_dot_suggests_cols1(self, sql): suggestions = suggest_type(sql, sql) tables = ((None, 'abc', 'a', False), (None, 'def', 'd', False)) self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'abc', 'a', False),)), Table(schema='a'), View(schema='a'), Function(schema='a'), JoinCondition(table_refs=tables, parent=(None, 'abc', 'a', False)) ]))
def test_distinct_and_order_by_suggestions_with_alias_given(self, text, text_before): suggestions = suggest_type(text, text_before) self.assertSetEqual(set(suggestions), set([ Column( table_refs=(TableReference(None, 'tbl', 'x', False),), local_tables=(), qualifiable=False ), Table(schema='x'), View(schema='x'), Function(schema='x'), ]))
def test_join_suggests_tables_and_schemas(self): tbl_aliases = ('', 'foo',) join_types = ('', 'INNER', 'LEFT', 'RIGHT OUTER',) for table in tbl_aliases: for join in join_types: text = 'SELECT * FROM abc {0} {1} JOIN '.format(table, join) suggestions = suggest_type(text, text) tbls = tuple([(None, 'abc', table or None, False)]) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None, table_refs=tbls), Schema(), Join(tbls, None), ]))
def test_2_statements_2nd_current(self): suggestions = suggest_type('select * from a; select * from ', 'select * from a; select * from ') self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ])) suggestions = suggest_type('select * from a; select from b', 'select * from a; select ') self.assertSetEqual(set(suggestions), set([ Column(table_refs=((None, 'b', None, False),), qualifiable=True), Function(schema=None), Keyword('SELECT') ])) # Should work even if first statement is invalid suggestions = suggest_type('select * from; select * from ', 'select * from; select * from ') self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ]))
def test_distinct_and_order_by_suggestions_with_aliases(self, text, text_before, last_keyword): suggestions = suggest_type(text, text_before) self.assertSetEqual(set(suggestions), set([ Column( table_refs=( TableReference(None, 'tbl', 'x', False), TableReference(None, 'tbl1', 'y', False), ), local_tables=(), qualifiable=True ), Function(schema=None), Keyword(last_keyword) ]))
def test_where_in_suggests_columns(self, expression): suggestions = suggest_type(expression, expression) self.assertSetEqual(set(suggestions), cols_etc( 'tabl', last_keyword='WHERE'))