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