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