def test_col_comma_suggests_cols():
suggestions = suggest_type('SELECT a, b, FROM tbl', 'SELECT a, b,')
assert set(suggestions) == set([
Column(table_refs=((None, 'tbl', None, False), ), qualifiable=True),
Function(schema=None),
Keyword('SELECT'),
])
def test_select_suggests_cols_and_funcs():
suggestions = suggest_type('SELECT ', 'SELECT ')
assert set(suggestions) == set([
Column(table_refs=(), qualifiable=True),
Function(schema=None),
Keyword('SELECT'),
])
def test_statements_in_function_body(text):
suggestions = suggest_type(text, text[:text.find(' ') + 1])
assert set(suggestions) == set([
Column(table_refs=((None, 'foo', None, False), ), qualifiable=True),
Function(schema=None),
Keyword('SELECT'),
])
def test_distinct_and_order_by_suggestions_with_aliases():
test_args = [(
'SELECT DISTINCT FROM tbl x JOIN tbl1 y',
'SELECT DISTINCT',
'SELECT',
),
(
'SELECT * FROM tbl x JOIN tbl1 y ORDER BY ',
'SELECT * FROM tbl x JOIN tbl1 y ORDER BY ',
'ORDER BY',
)]
for arg in test_args:
text = arg[0]
text_before = arg[1]
last_keyword = arg[2]
suggestions = suggest_type(text, text_before)
assert 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_distinct_suggests_cols(text):
suggestions = suggest_type(text, text)
assert set(suggestions) == set([
Column(table_refs=(), local_tables=(), qualifiable=True),
Function(schema=None),
Keyword('DISTINCT')
])
def test_distinct_and_order_by_suggestions_with_alias_given():
test_args = [
(
'SELECT DISTINCT x. FROM tbl x JOIN tbl1 y',
'SELECT DISTINCT x.'
),
(
'SELECT * FROM tbl x JOIN tbl1 y ORDER BY x.',
'SELECT * FROM tbl x JOIN tbl1 y ORDER BY x.'
)
]
for arg in test_args:
text = arg[0]
text_before = arg[1]
suggestions = suggest_type(text, text_before)
assert 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_dot_suggests_cols_of_an_alias_where(sql):
suggestions = suggest_type(sql, sql)
assert 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():
suggestions = suggest_type('SELECT tabl. FROM tabl', 'SELECT tabl.')
assert set(suggestions) == set([
Column(table_refs=((None, 'tabl', None, False), )),
Table(schema='tabl'),
View(schema='tabl'),
Function(schema='tabl'),
])
def test_sub_select_col_name_completion():
suggestions = suggest_type('SELECT * FROM (SELECT FROM abc',
'SELECT * FROM (SELECT ')
assert set(suggestions) == set([
Column(table_refs=((None, 'abc', None, False), ), qualifiable=True),
Function(schema=None),
Keyword('SELECT'),
])
def test_join_alias_dot_suggests_cols2(sql):
suggestion = suggest_type(sql, sql)
assert set(suggestion) == set([
Column(table_refs=((None, 'def', 'd', False), )),
Table(schema='d'),
View(schema='d'),
Function(schema='d'),
])
def get_from_clause_item_matches(self, suggestion, word_before_cursor):
alias = self.generate_aliases
s = suggestion
t_sug = Table(s.schema, s.table_refs, s.local_tables)
v_sug = View(s.schema, s.table_refs)
f_sug = Function(s.schema, s.table_refs, usage='from')
return (self.get_table_matches(t_sug, word_before_cursor, alias) +
self.get_view_matches(v_sug, word_before_cursor, alias) +
self.get_function_matches(f_sug, word_before_cursor, alias))
def test_named_query_completion():
test_args = [('\\ns abc SELECT ', 'SELECT ', [
Column(table_refs=(), qualifiable=True),
Function(schema=None),
Keyword('SELECT')
]), ('\\ns abc SELECT foo ', 'SELECT foo ', (Keyword(), )),
('\\ns abc SELECT t1. FROM tabl1 t1', 'SELECT t1.', [
Table(schema='t1'),
View(schema='t1'),
Column(table_refs=((None, 'tabl1', 't1', False), )),
Function(schema='t1')
])]
for arg in test_args:
text = arg[0]
before = arg[1]
expected = arg[2]
suggestions = suggest_type(text, before)
assert set(expected) == set(suggestions)
def test_outer_table_reference_in_exists_subquery_suggests_columns():
q = 'SELECT * FROM foo f WHERE EXISTS (SELECT 1 FROM bar WHERE f.'
suggestions = suggest_type(q, q)
assert set(suggestions) == set([
Column(table_refs=((None, 'foo', 'f', False), )),
Table(schema='f'),
View(schema='f'),
Function(schema='f'),
])
def test_sub_select_dot_col_name_completion():
suggestions = suggest_type('SELECT * FROM (SELECT t. FROM tabl t', \
'SELECT * FROM (SELECT t.')
assert set(suggestions) == set([
Column(table_refs=((None, 'tabl', 't', False), )),
Table(schema='t'),
View(schema='t'),
Function(schema='t'),
])
def test_distinct_suggests_cols():
texts = ['SELECT DISTINCT ', 'INSERT INTO foo SELECT DISTINCT ']
for text in texts:
suggestions = suggest_type(text, text)
assert set(suggestions) == set([
Column(table_refs=(), local_tables=(), qualifiable=True),
Function(schema=None),
Keyword('DISTINCT')
])
def test_dot_col_comma_suggests_cols_or_schema_qualified_table():
suggestions = suggest_type('SELECT t1.a, t2. FROM tabl1 t1, tabl2 t2', \
'SELECT t1.a, t2.')
assert set(suggestions) == set([
Column(table_refs=((None, 'tabl2', 't2', False), )),
Table(schema='t2'),
View(schema='t2'),
Function(schema='t2'),
])
def test_distinct_and_order_by_suggestions_with_alias_given(text, text_before):
suggestions = suggest_type(text, text_before)
assert 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 cols_etc(self, table, schema=None, alias=None, is_function=False, parent=None, \
last_keyword=None):
"""Returns the expected select-clause suggestions for a single-table
select."""
return set([
Column(table_refs=(TableReference(schema, table, alias, is_function),), \
qualifiable=True),
Function(schema=parent),
Keyword(last_keyword)
])
def test_join_alias_dot_suggests_cols1(sql):
suggestions = suggest_type(sql, sql)
tables = ((None, 'abc', 'a', False), (None, 'def', 'd', False))
assert 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_statements_in_function_body():
texts = [ \
'''
CREATE OR REPLACE FUNCTION func() RETURNS setof int AS $$
SELECT FROM foo;
SELECT 2 FROM bar;
$$ language sql;
''', \
'''create function func2(int, varchar)
RETURNS text
language sql AS
$func$
SELECT 2 FROM bar;
SELECT FROM foo;
$func$
''',
'''
CREATE OR REPLACE FUNCTION func() RETURNS setof int AS $func$
SELECT 3 FROM foo;
SELECT 2 FROM bar;
$$ language sql;
create function func2(int, varchar)
RETURNS text
language sql AS
$func$
SELECT 2 FROM bar;
SELECT FROM foo;
$func$
''',
'''
SELECT * FROM baz;
CREATE OR REPLACE FUNCTION func() RETURNS setof int AS $func$
SELECT FROM foo;
SELECT 2 FROM bar;
$$ language sql;
create function func2(int, varchar)
RETURNS text
language sql AS
$func$
SELECT 3 FROM bar;
SELECT FROM foo;
$func$
SELECT * FROM qux;
''' \
]
for text in texts:
suggestions = suggest_type(text, text[:text.find(' ') + 1])
assert set(suggestions) == set([
Column(table_refs=((None, 'foo', None, False), ),
qualifiable=True),
Function(schema=None),
Keyword('SELECT'),
])
def test_join_alias_dot_suggests_cols2():
sqls = [
'SELECT * FROM abc a JOIN def d ON a.id = d.',
'SELECT * FROM abc a JOIN def d ON a.id = d.id AND a.id2 = d.',
]
for sql in sqls:
suggestion = suggest_type(sql, sql)
assert set(suggestion) == set([
Column(table_refs=((None, 'def', 'd', False), )),
Table(schema='d'),
View(schema='d'),
Function(schema='d'),
])
def test_distinct_and_order_by_suggestions_with_aliases(
text, text_before, last_keyword):
suggestions = suggest_type(text, text_before)
assert 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_dot_suggests_cols_of_an_alias(self):
sqls = [
'SELECT t1. FROM tabl1 t1',
'SELECT t1. FROM tabl1 t1, tabl2 t2',
'SELECT t1. FROM "tabl1" t1',
'SELECT t1. FROM "tabl1" t1, "tabl2" t2',
]
for sql in sqls:
suggestions = suggest_type(sql, 'SELECT t1.')
assert 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_an_alias_where():
sqls = [
'SELECT * FROM tabl1 t1 WHERE t1.',
'SELECT * FROM tabl1 t1, tabl2 t2 WHERE t1.',
'SELECT * FROM "tabl1" t1 WHERE t1.',
'SELECT * FROM "tabl1" t1, tabl2 t2 WHERE t1.',
]
for sql in sqls:
suggestions = suggest_type(sql, sql)
assert set(suggestions) == set([
Table(schema='t1'),
View(schema='t1'),
Column(table_refs=((None, 'tabl1', 't1', False), )),
Function(schema='t1'),
])
def test_join_alias_dot_suggests_cols1():
sqls = [
'SELECT * FROM abc a JOIN def d ON a.',
'SELECT * FROM abc a JOIN def d ON a.id = d.id AND a.',
]
for sql in sqls:
suggestions = suggest_type(sql, sql)
tables = ((None, 'abc', 'a', False), (None, 'def', 'd', False))
assert 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_2_statements_2nd_current():
suggestions = suggest_type('select * from a; select * from ', \
'select * from a; select * from ')
assert set(suggestions) == set([
FromClauseItem(schema=None),
Schema(),
])
suggestions = suggest_type('select * from a; select from b', \
'select * from a; select ')
assert 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 ')
assert set(suggestions) == set([
FromClauseItem(schema=None),
Schema(),
])
@pytest.mark.parametrize('text', [
'SELECT * FROM foo where created > now() - ',
'select * from foo where bar ',
])
def test_suggest_where_keyword(text):
# https://github.com/dbcli/mycli/issues/135
suggestions = suggest_type(text, text)
assert set(suggestions) == cols_etc('foo', last_keyword='WHERE')
@pytest.mark.parametrize(
'text, before, expected',
[('\\ns abc SELECT ', 'SELECT ', [
Column(table_refs=(), qualifiable=True),
Function(schema=None),
Keyword('SELECT')
]), ('\\ns abc SELECT foo ', 'SELECT foo ', (Keyword(), )),
('\\ns abc SELECT t1. FROM tabl1 t1', 'SELECT t1.', [
Table(schema='t1'),
View(schema='t1'),
Column(table_refs=((None, 'tabl1', 't1', False), )),
Function(schema='t1')
])])
def test_named_query_completion(text, before, expected):
suggestions = suggest_type(text, before)
assert set(expected) == set(suggestions)
def test_select_suggests_fields_from_function():
suggestions = suggest_type('SELECT FROM func()', 'SELECT ')