def test_suggested_multiple_column_names(completer):
result = get_result(
completer, 'SELECT id, from custom.products', len('SELECT id, ')
)
assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
'products', 'custom'
))
def test_all_schema_objects_with_aliases(completer):
text = ('SELECT * FROM ')
result = get_result(completer, text)
assert completions_to_set(result) >= completions_to_set(
[table(x) for x in ('orders o', '"select" s', 'custom.shipments s')]
+ [function(x) for x in ('func2() f',)]
)
def test_user_function_name_completion_matches_anywhere(completer):
result = get_result(completer, 'SELECT om')
assert completions_to_set(result) == completions_to_set([
function('custom_fun()', -2),
function('_custom_fun()', -2),
function('custom_func1()', -2),
function('custom_func2()', -2)])
def test_aliased_joins(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(testdata.schemas() + aliased_rels + [
join('"Users" U ON U.userid = Users.id'),
join('users u ON u.id = Users.parentid'),
join('users u ON u.parentid = Users.id'),
])
def test_cased_joins(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set([schema('PUBLIC')] + cased_rels + [
join('"Users" ON "Users".UserID = Users.ID'),
join('Users Users2 ON Users2.ID = Users.PARENTID'),
join('Users Users2 ON Users2.PARENTID = Users.ID'),
])
def test_suggest_columns_from_set_returning_function(completer):
result = get_result(
completer, 'select from set_returning_func()', len('select ')
)
assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
'set_returning_func', typ='functions'
))
def test_suggested_aliases_after_on_right_side(completer, text):
position = len(
'SELECT u.name, o.id FROM users u JOIN orders o ON o.user_id = '
)
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(
[alias('u'), alias('o')])
def test_join_functions_using_suggests_common_columns(completer):
text = '''SELECT * FROM set_returning_func() f1
INNER JOIN set_returning_func() f2 USING ('''
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
testdata.columns('set_returning_func', typ='functions')
)
def test_suggest_columns_from_aliased_set_returning_function(completer):
result = get_result(
completer, 'select f. from set_returning_func() f', len('select f.')
)
assert completions_to_set(result) == completions_to_set(
testdata.columns('set_returning_func', typ='functions')
)
def test_suggested_column_names_from_schema_qualifed_table(completer):
result = get_result(
completer, 'SELECT from custom.products', len('SELECT ')
)
assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
'products', 'custom'
))
def test_function_alias_search_with_aliases(completer):
text = 'SELECT blog.ee'
result = get_result(completer, text)
first = result[0]
assert first.start_position == -2
assert first.text == 'enter_entry(_title := , _text := )'
assert first.display_text == 'enter_entry(_title, _text)'
def test_all_schema_objects_with_casing(completer):
text = 'SELECT * FROM '
result = get_result(completer, text)
assert completions_to_set(result) >= completions_to_set(
[table(x) for x in ('Orders', '"select"', 'CUSTOM.shipments')]
+ [function(x + '()') for x in ('func2',)]
)
def test_function_alias_search_without_aliases(completer):
text = 'SELECT blog.ees'
result = get_result(completer, text)
first = result[0]
assert first.start_position == -3
assert first.text == 'extract_entry_symbols()'
assert first.display_text == 'extract_entry_symbols(_entryid)'
def test_suggested_auto_qualified_column_names_two_tables(text, completer):
position = text.index(' ') + 1
cols = [column('U.' + c.lower()) for c in cased_users_col_names]
cols += [column('"Users".' + c.lower()) for c in cased_users2_col_names]
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(
cols + testdata.functions_and_keywords())
def test_suggested_join_conditions(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set([
alias('users'),
alias('shipments'),
name_join('shipments.id = users.id'),
fk_join('shipments.user_id = users.id')])
def test_table_aliases(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(testdata.schemas() + [
table('users u'),
table('orders o' if text == 'SELECT * FROM ' else 'orders o2'),
table('"select" s'),
function('func1() f'),
function('func2() f')])
def test_table_casing(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(cased_schemas + [
table('users'),
table('Orders'),
table('"select"'),
function('Func1()'),
function('func2()')])
def test_aliases_with_casing(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(cased_schemas + [
table('users u'),
table('Orders O' if text == 'SELECT * FROM ' else 'Orders O2'),
table('"select" s'),
function('Func1() F'),
function('func2() f')])
def test_set_schema(completer):
text = ('SET SCHEMA ')
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set([
schema(u"'blog'"),
schema(u"'Custom'"),
schema(u"'custom'"),
schema(u"'public'")])
def test_suggested_multiple_column_names_with_alias(completer):
result = get_result(
completer,
'SELECT p.id, p. from custom.products p',
len('SELECT u.id, u.')
)
assert completions_to_set(result) == completions_to_set(
testdata.columns('products', 'custom'))
def test_function_column_name(completer):
for l in range(
len('SELECT * FROM Functions WHERE function:'),
len('SELECT * FROM Functions WHERE function:text') + 1
):
assert [] == get_result(
completer, 'SELECT * FROM Functions WHERE function:text'[:l]
)
def test_join_functions_on_suggests_columns_and_join_conditions(completer):
text = '''SELECT * FROM set_returning_func() f1
INNER JOIN set_returning_func() f2 ON f1.'''
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
[name_join('y = f2.y'), name_join('x = f2.x')] +
testdata.columns('set_returning_func', typ='functions')
)
def test_columns_before_keywords(completer):
text = 'SELECT * FROM orders WHERE s'
completions = get_result(completer, text)
col = column('status', -1)
kw = keyword('SELECT', -1)
assert completions.index(col) < completions.index(kw)
def test_suggested_multiple_column_names_with_dot(completer):
result = get_result(
completer,
'SELECT users.id, users. from users u',
len('SELECT users.id, users.')
)
assert completions_to_set(result) == completions_to_set(
testdata.columns('users'))
def test_suggested_aliases_after_on(completer, text):
position = len('SELECT u.name, o.id FROM users u JOIN orders o ON ')
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set([
alias('u'),
name_join('o.id = u.id'),
name_join('o.email = u.email'),
alias('o')])
def test_learn_keywords(completer):
history = 'CREATE VIEW v AS SELECT 1'
completer.extend_query_history(history)
# Now that we've used `VIEW` once, it should be suggested ahead of other
# keywords starting with v.
text = 'create v'
completions = get_result(completer, text)
assert completions[0].text == 'VIEW'
def test_wildcard_column_expansion_with_alias(completer, text):
position = text.find('*') + 1
completions = get_result(completer, text, position)
col_list = 'id, u.parentid, u.email, u.first_name, u.last_name'
expected = [wildcard_expansion(col_list)]
assert expected == completions
def test_suggested_joins(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
testdata.schemas_and_from_clause_items() + [
join('"Users" ON "Users".userid = Users.id'),
join('users users2 ON users2.id = Users.parentid'),
join('users users2 ON users2.parentid = Users.id'),
]
)
def test_suggested_tables_after_on(completer, text):
position = len('SELECT users.name, orders.id FROM users JOIN orders ON ')
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set([
name_join('orders.id = users.id'),
name_join('orders.email = users.email'),
alias('users'),
alias('orders')
])
def test_suggested_cased_always_qualified_column_names(
completer
):
text = 'SELECT from users'
position = len('SELECT ')
cols = [column('users.' + c) for c in cased_users_col_names]
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(cased_funcs + cols
+ testdata.builtin_functions() + testdata.keywords())
def test_suggest_columns_from_escaped_table_alias(completer):
result = get_result(completer, 'select * from "select" s where s.')
assert completions_to_set(result) == completions_to_set(
testdata.columns("select"))
def test_suggest_columns_after_three_way_join(completer):
text = """SELECT * FROM users u1
INNER JOIN users u2 ON u1.id = u2.id
INNER JOIN users u3 ON u2.id = u3."""
result = get_result(completer, text)
assert column("id") in result
def test_drop_alter_function(completer, action):
assert get_result(completer, action + " FUNCTION set_ret") == [
function("set_returning_func(x integer, y integer)", -len("set_ret"))
]
def test_empty_string_completion(completer):
result = get_result(completer, "")
assert completions_to_set(testdata.keywords() +
testdata.specials()) == completions_to_set(
result)
def test_select_keyword_completion(completer):
result = get_result(completer, "SEL")
assert completions_to_set(result) == completions_to_set(
[keyword("SELECT", -3)])
def test_suggested_joins_quoted_schema_qualified_table(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
testdata.schemas_and_from_clause_items() +
[join('public.users ON users.id = "Users".userid')])
def test_suggested_joins_fuzzy(completer, text):
result = get_result(completer, text)
last_word = text.split()[-1]
expected = join("users ON users.id = u.userid", -len(last_word))
assert expected in result
def test_suggested_join_conditions_with_invalid_table(completer, text, ref):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
[alias("users"), alias(ref)])
def test_suggested_join_conditions_with_invalid_qualifier(completer, text):
result = get_result(completer, text)
assert result == []
def test_suggested_cased_column_names(completer):
result = get_result(completer, "SELECT from users", len("SELECT "))
assert completions_to_set(result) == completions_to_set(
cased_funcs + cased_users_cols + testdata.builtin_functions() +
testdata.keywords())
def test_suggested_join_conditions(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
[alias("U"), alias("U2"),
fk_join("U2.userid = U.id")])
def test_suggested_column_names_from_visible_table(completer):
result = get_result(completer, "SELECT from users", len("SELECT "))
assert completions_to_set(result) == completions_to_set(
testdata.columns_functions_and_keywords("users"))
def test_list_functions_for_special(completer):
result = get_result(completer, r"\df ")
assert completions_to_set(result) == completions_to_set(
[schema("PUBLIC")] + [function(f) for f in cased_func_names])
def test_suggested_aliases_after_on_right_side(completer, text):
position = len(
"SELECT u.name, o.id FROM users u JOIN orders o ON o.user_id = ")
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(
[alias("u"), alias("o")])
def test_suggested_multiple_column_names_with_alias(completer):
result = get_result(completer, "SELECT u.id, u. from users u",
len("SELECT u.id, u."))
assert completions_to_set(result) == completions_to_set(
testdata.columns("users"))
def test_join_using_suggests_from_last_table(completer, text):
position = text.index("()") + 1
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(
[column("id"), column("email")])
def test_builtin_function_matches_only_at_start(completer):
text = "SELECT IN"
result = [c.text for c in get_result(completer, text)]
assert "MIN" not in result
def test_join_using_suggests_columns_after_first_column(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
[column("id"), column("email")])
def test_suggested_multiple_column_names(completer):
result = get_result(completer, "SELECT id, from users u",
len("SELECT id, "))
assert completions_to_set(result) == completions_to_set(
(testdata.columns_functions_and_keywords("users")))
def test_keyword_after_alter(completer):
text = "ALTER TABLE users ALTER "
expected = Completion("COLUMN", start_position=0, display_meta="keyword")
completions = get_result(completer, text)
assert expected in completions
def test_suggested_column_names_with_table_dot(completer):
result = get_result(completer, "SELECT users. from users",
len("SELECT users."))
assert completions_to_set(result) == completions_to_set(
testdata.columns("users"))
def test_no_column_qualification(text, completer):
cols = [column(c) for c in cased_users_col_names]
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(cols)
def test_set_schema(completer):
text = "SET SCHEMA "
result = get_result(completer, text)
expected = completions_to_set([schema("'public'")])
assert completions_to_set(result) == expected
def test_suggested_cased_column_names_with_alias(completer):
result = get_result(completer, "SELECT u.id, u. from users u",
len("SELECT u.id, u."))
assert completions_to_set(result) == completions_to_set(cased_users_cols)
def test_auto_escaped_col_names(completer):
result = get_result(completer, 'SELECT from "select"', len("SELECT "))
assert completions_to_set(result) == completions_to_set(
testdata.columns_functions_and_keywords("select"))
def test_suggested_column_names_in_function(completer):
result = get_result(completer, "SELECT MAX( from users",
len("SELECT MAX("))
assert completions_to_set(result) == completions_to_set(
(testdata.columns_functions_and_keywords("users")))
def test_allow_leading_double_quote_in_last_word(completer):
result = get_result(completer, 'SELECT * from "sele')
expected = table('"select"', -5)
assert expected in result
def test_suggest_datatype(text, completer):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
testdata.schemas() + testdata.types() + testdata.builtin_datatypes())
def test_cased_join_conditions(completer, text):
result = get_result(completer, text)
assert completions_to_set(result) == completions_to_set(
[alias("U"), alias("U2"),
fk_join("U2.UserID = U.ID")])
def test_suggested_auto_qualified_column_names(text, completer):
position = text.index(" ") + 1
cols = [column(c.lower()) for c in cased_users_col_names]
result = get_result(completer, text, position)
assert completions_to_set(result) == completions_to_set(
cols + testdata.functions_and_keywords())