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': []}
])
