def test_df_suggests_schema_or_function(): suggestions = suggest_type('\\df xxx', '\\df xxx') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'function', 'schema': []}, {'type': 'schema'}]) suggestions = suggest_type('\\df myschema.xxx', '\\df myschema.xxx') assert suggestions == [{'type': 'function', 'schema': 'myschema'}]
def test_3_statements_2nd_current(): suggestions = suggest_type( 'select * from a; select * from ; select * from c', 'select * from a; select * from ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }]) suggestions = suggest_type( 'select * from a; select from b; select * from c', 'select * from a; select ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'b', None)] }, { 'type': 'function', 'schema': [] }])
def test_d_dot_suggests_schema_qualified_tables_or_views(): suggestions = suggest_type('\d myschema.', '\d myschema.') assert suggestions == [{'type': 'table', 'schema': 'myschema'}, {'type': 'view', 'schema': 'myschema'}] suggestions = suggest_type('\d myschema.xxx', '\d myschema.xxx') assert suggestions == [{'type': 'table', 'schema': 'myschema'}, {'type': 'view', 'schema': 'myschema'}]
def test_df_suggests_schema_or_function(): suggestions = suggest_type('\\df xxx', '\\df xxx') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'function', 'schema': [] }, { 'type': 'schema' }]) suggestions = suggest_type('\\df myschema.xxx', '\\df myschema.xxx') assert suggestions == [{'type': 'function', 'schema': 'myschema'}]
def test_d_suggests_tables_views_and_schemas(): suggestions = suggest_type('\d ', '\d ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'schema'}, {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}]) suggestions = suggest_type('\d xxx', '\d xxx') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'schema'}, {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}])
def test_2_statements_1st_current(): suggestions = suggest_type('select * from ; select * from b', 'select * from ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'} ]) suggestions = suggest_type('select from a; select * from b', 'select ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'a', None)]}, {'type': 'function', 'schema': []} ])
def test_join_suggests_tables_and_schemas(tbl_alias, join_type): text = 'SELECT * FROM abc {0} {1} JOIN '.format(tbl_alias, join_type) suggestion = suggest_type(text, text) assert sorted_dicts(suggestion) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'}])
def test_dot_suggests_cols_of_an_alias_where(sql): suggestions = suggest_type(sql, sql) assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': 't1'}, {'type': 'view', 'schema': 't1'}, {'type': 'column', 'tables': [(None, 'tabl1', 't1')]}, {'type': 'function', 'schema': 't1'}])
def test_expression_suggests_tables_views_and_schemas(expression): suggestions = suggest_type(expression, expression) assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'} ])
def test_dot_suggests_cols_of_a_table_or_schema_qualified_table(): suggestions = suggest_type('SELECT tabl. FROM tabl', 'SELECT tabl.') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'tabl', None)]}, {'type': 'table', 'schema': 'tabl'}, {'type': 'view', 'schema': 'tabl'}, {'type': 'function', 'schema': 'tabl'}])
def test_schema_qualified_dT_suggests_datatypes(): text = '\\dT foo.' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'datatype', 'schema': 'foo' }])
def test_table_comma_suggests_tables_and_schemas(): suggestions = suggest_type('SELECT a, b FROM tbl1, ', 'SELECT a, b FROM tbl1, ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'}])
def test_dT_suggests_schema_or_datatypes(): text = '\\dT ' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts( [{'type': 'schema'}, {'type': 'datatype', 'schema': []}, ])
def test_where_equals_any_suggests_columns_or_keywords(): text = 'SELECT * FROM tabl WHERE foo = ANY(' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'tabl', None)]}, {'type': 'function', 'schema': []}, {'type': 'keyword'} ])
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 sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'tabl2', 't2')]}, {'type': 'table', 'schema': 't2'}, {'type': 'view', 'schema': 't2'}, {'type': 'function', 'schema': 't2'}])
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 suggestions == [ {'type': 'column', 'tables': [(None, 'foo', 'f')]}, {'type': 'table', 'schema': 'f'}, {'type': 'view', 'schema': 'f'}, {'type': '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 sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'tabl', 't')]}, {'type': 'table', 'schema': 't'}, {'type': 'view', 'schema': 't'}, {'type': 'function', 'schema': 't'}])
def test_left_join_with_comma(): text = 'select * from foo f left join bar b,' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'} ])
def test_join_alias_dot_suggests_cols2(): suggestion = suggest_type('SELECT * FROM abc a JOIN def d ON a.', 'SELECT * FROM abc a JOIN def d ON a.id = d.') assert sorted_dicts(suggestion) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'def', 'd')]}, {'type': 'table', 'schema': 'd'}, {'type': 'view', 'schema': 'd'}, {'type': 'function', 'schema': 'd'}])
def test_truncate_suggests_tables_and_schemas(): suggestions = suggest_type('TRUNCATE ', 'TRUNCATE ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'schema' }])
def test_expression_suggests_qualified_tables_views_and_schemas(expression): suggestions = suggest_type(expression, expression) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': 'sch' }, { 'type': 'view', 'schema': 'sch' }])
def test_d_dot_suggests_schema_qualified_tables_or_views(): suggestions = suggest_type('\d myschema.', '\d myschema.') assert suggestions == [{ 'type': 'table', 'schema': 'myschema' }, { 'type': 'view', 'schema': 'myschema' }] suggestions = suggest_type('\d myschema.xxx', '\d myschema.xxx') assert suggestions == [{ 'type': 'table', 'schema': 'myschema' }, { 'type': 'view', 'schema': 'myschema' }]
def test_join_using_suggests_common_columns(col_list): text = 'select * from abc inner join def using (' + col_list assert suggest_type(text, text) == [{ 'type': 'column', 'tables': [(None, 'abc', None), (None, 'def', None)], 'drop_unique': True }]
def test_select_suggests_cols_and_funcs(): suggestions = suggest_type('SELECT ', 'SELECT ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [] }, { 'type': 'function', 'schema': [] }])
def test_select_suggests_cols_with_qualified_table_scope(): suggestions = suggest_type('SELECT FROM sch.tabl', 'SELECT ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [('sch', 'tabl', None)] }, { 'type': 'function', 'schema': [] }])
def test_col_comma_suggests_cols(): suggestions = suggest_type('SELECT a, b, FROM tbl', 'SELECT a, b,') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tbl', None)] }, { 'type': 'function', 'schema': [] }])
def test_where_in_suggests_columns(expression): suggestions = suggest_type(expression, expression) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tabl', None)] }, { 'type': 'function', 'schema': [] }])
def test_partially_typed_col_name_suggests_col_names(): suggestions = suggest_type('SELECT * FROM tabl WHERE col_n', 'SELECT * FROM tabl WHERE col_n') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tabl', None)] }, { 'type': 'function', 'schema': [] }])
def test_into_suggests_tables_and_schemas(): suggestion = suggest_type('INSERT INTO ', 'INSERT INTO ') assert sorted_dicts(suggestion) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_sub_select_table_name_completion(expression): suggestion = suggest_type(expression, expression) assert sorted_dicts(suggestion) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_sub_select_multiple_col_name_completion(): suggestions = suggest_type('SELECT * FROM (SELECT a, FROM abc', 'SELECT * FROM (SELECT a, ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'abc', None)] }, { 'type': 'function', 'schema': [] }])
def test_identifier_suggests_types_in_parentheses(text): assert sorted_dicts(suggest_type(text, text)) == sorted_dicts([{ 'type': 'datatype', 'schema': [] }, { 'type': 'table', 'schema': [] }, { 'type': 'schema' }])
def test_cast_operator_suggests_types(text): assert sorted_dicts(suggest_type(text, text)) == sorted_dicts([{ 'type': 'datatype', 'schema': [] }, { 'type': 'table', 'schema': [] }, { 'type': 'schema' }])
def test_alter_column_type_suggests_types(): q = 'ALTER TABLE foo ALTER COLUMN bar TYPE ' assert sorted_dicts(suggest_type(q, q)) == sorted_dicts([{ 'type': 'datatype', 'schema': [] }, { 'type': 'table', 'schema': [] }, { 'type': 'schema' }])
def test_table_comma_suggests_tables_and_schemas(): suggestions = suggest_type('SELECT a, b FROM tbl1, ', 'SELECT a, b FROM tbl1, ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_left_join_with_comma(): text = 'select * from foo f left join bar b,' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_join_suggests_tables_and_schemas(tbl_alias, join_type): text = 'SELECT * FROM abc {0} {1} JOIN '.format(tbl_alias, join_type) suggestion = suggest_type(text, text) assert sorted_dicts(suggestion) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_dT_suggests_schema_or_datatypes(): text = '\\dT ' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([ { 'type': 'schema' }, { 'type': 'datatype', 'schema': [] }, ])
def test_d_suggests_tables_views_and_schemas(): suggestions = suggest_type('\d ', '\d ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'schema' }, { 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }]) suggestions = suggest_type('\d xxx', '\d xxx') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'schema' }, { 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }])
def test_where_equals_any_suggests_columns_or_keywords(): text = 'SELECT * FROM tabl WHERE foo = ANY(' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tabl', None)] }, { 'type': 'function', 'schema': [] }, { 'type': 'keyword' }])
def test_2_statements_2nd_current(): suggestions = suggest_type('select * from a; select * from ', 'select * from a; select * from ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'} ]) suggestions = suggest_type('select * from a; select from b', 'select * from a; select ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'b', None)]}, {'type': 'function', 'schema': []} ]) # Should work even if first statement is invalid suggestions = suggest_type('select * from; select * from ', 'select * from; select * from ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'table', 'schema': []}, {'type': 'view', 'schema': []}, {'type': 'schema'} ])
def test_2_statements_2nd_current(): suggestions = suggest_type('select * from a; select * from ', 'select * from a; select * from ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }]) suggestions = suggest_type('select * from a; select from b', 'select * from a; select ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'b', None)] }, { 'type': 'function', 'schema': [] }]) # Should work even if first statement is invalid suggestions = suggest_type('select * from; select * from ', 'select * from; select * from ') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': [] }, { 'type': 'view', 'schema': [] }, { 'type': 'schema' }])
def test_dot_suggests_cols_of_a_table_or_schema_qualified_table(): suggestions = suggest_type('SELECT tabl. FROM tabl', 'SELECT tabl.') assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tabl', None)] }, { 'type': 'table', 'schema': 'tabl' }, { 'type': 'view', 'schema': 'tabl' }, { 'type': 'function', 'schema': 'tabl' }])
def test_dot_suggests_cols_of_an_alias_where(sql): suggestions = suggest_type(sql, sql) assert sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'table', 'schema': 't1' }, { 'type': 'view', 'schema': 't1' }, { 'type': 'column', 'tables': [(None, 'tabl1', 't1')] }, { 'type': 'function', 'schema': 't1' }])
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 suggestions == [{ 'type': 'column', 'tables': [(None, 'foo', 'f')] }, { 'type': 'table', 'schema': 'f' }, { 'type': 'view', 'schema': 'f' }, { 'type': '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 sorted_dicts(suggestions) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'tabl', 't')] }, { 'type': 'table', 'schema': 't' }, { 'type': 'view', 'schema': 't' }, { 'type': 'function', 'schema': 't' }])
def test_join_alias_dot_suggests_cols2(): suggestion = suggest_type('SELECT * FROM abc a JOIN def d ON a.', 'SELECT * FROM abc a JOIN def d ON a.id = d.') assert sorted_dicts(suggestion) == sorted_dicts([{ 'type': 'column', 'tables': [(None, 'def', 'd')] }, { 'type': 'table', 'schema': 'd' }, { 'type': 'view', 'schema': 'd' }, { 'type': 'function', 'schema': 'd' }])
def test_slash_d_suggests_special(): suggestions = suggest_type('\\d', '\\d') assert sorted_dicts(suggestions) == sorted_dicts( [{'type': 'special'}])
def test_c_suggests_databases(command): suggestions = suggest_type(command, command) assert suggestions == [{'type': 'database'}]
def test_schema_qualified_dT_suggests_datatypes(): text = '\\dT foo.' suggestions = suggest_type(text, text) assert sorted_dicts(suggestions) == sorted_dicts( [{'type': 'datatype', 'schema': 'foo'}])
def test_cast_operator_suggests_schema_qualified_types(text): assert sorted_dicts(suggest_type(text, text)) == sorted_dicts([ {'type': 'datatype', 'schema': 'bar'}, {'type': 'table', 'schema': 'bar'}])
def test_leading_whitespace_ok(): cmd = '\\dn ' whitespace = ' ' suggestions = suggest_type(whitespace + cmd, whitespace + cmd) assert suggestions == suggest_type(cmd, cmd)
def test_alter_column_type_suggests_types(): q = 'ALTER TABLE foo ALTER COLUMN bar TYPE ' assert sorted_dicts(suggest_type(q, q)) == sorted_dicts([ {'type': 'datatype', 'schema': []}, {'type': 'table', 'schema': []}, {'type': 'schema'}])
def test_where_in_suggests_columns(expression): suggestions = suggest_type(expression, expression) assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': [(None, 'tabl', None)]}, {'type': 'function', 'schema': []} ])
def test_identifier_suggests_types_in_parentheses(text): assert sorted_dicts(suggest_type(text, text)) == sorted_dicts([ {'type': 'datatype', 'schema': []}, {'type': 'table', 'schema': []}, {'type': 'schema'}])
def test_dn_suggests_schemata(): suggestions = suggest_type('\\dn ', '\\dn ') assert suggestions == [{'type': 'schema'}] suggestions = suggest_type('\\dn xxx', '\\dn xxx') assert suggestions == [{'type': 'schema'}]
def test_alias_suggests_keywords(text): suggestions = suggest_type(text, text) assert suggestions == [{'type': 'keyword'}]
def test_invalid_sql(): # issue 317 text = 'selt *' suggestions = suggest_type(text, text) assert suggestions == [{'type': 'keyword'}]
def test_lparen_suggests_cols(): suggestion = suggest_type('SELECT MAX( FROM tbl', 'SELECT MAX(') assert suggestion == [ {'type': 'column', 'tables': [(None, 'tbl', None)]} ]
def test_select_suggests_cols_and_funcs(): suggestions = suggest_type('SELECT ', 'SELECT ') assert sorted_dicts(suggestions) == sorted_dicts([ {'type': 'column', 'tables': []}, {'type': 'function', 'schema': []} ])