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'))
