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_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_2_statements_1st_current(self): suggestions = suggest_type('select * from ; select * from b', 'select * from ') self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ])) suggestions = suggest_type('select from a; select * from b', 'select ') self.assertSetEqual(set(suggestions), cols_etc('a', last_keyword='SELECT'))
def test_suggest_qualified_aliasable_tables_and_views(self, expression): suggestions = suggest_type(expression, expression) self.assertSetEqual(set(suggestions), set([ Table(schema='sch'), View(schema='sch'), ]))
def test_sub_select_table_name_completion(self, expression): suggestions = suggest_type(expression, expression) self.assertSetEqual(set(suggestions), set([ FromClauseItem(schema=None), Schema(), ]))
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_column_keyword_suggests_columns(self, sql): suggestions = suggest_type(sql, sql) self.assertSetEqual( set(suggestions), set([ Column(table_refs=((None, 'foo', None, False), )), ]))
def test_alter_column_type_suggests_types(self): q = 'ALTER TABLE foo ALTER COLUMN bar TYPE ' self.assertSetEqual( set(suggest_type(q, q)), set([Datatype(schema=None), Table(schema=None), Schema()]))
def test_join_using_suggests_common_columns(self, text): tables = ((None, 'abc', None, False), (None, 'def', None, False)) self.assertSetEqual( set(suggest_type(text, text)), set([ Column(table_refs=tables, require_last_table=True), ]))
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_lparen_suggests_cols(self): suggestions = suggest_type('SELECT MAX( FROM tbl', 'SELECT MAX(') self.assertSetEqual( set(suggestions), set([ Column(table_refs=((None, 'tbl', None, False), ), qualifiable=True) ]))
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_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_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_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_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_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_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_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_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_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_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_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_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_on_suggests_tables_and_join_conditions_right_side(self, sql): suggestions = suggest_type(sql, sql) tables = ((None, 'abc', None, False), (None, 'bcd', None, False)) self.assertSetEqual( set(suggestions), set(( JoinCondition(table_refs=tables, parent=None), Alias(aliases=( 'abc', 'bcd', )), )))