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 == 'extract_entry_symbols(_entryid)'
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 == 'enter_entry(_title, _text)'
def test_join_alias_search_without_aliases1(completer):
text = 'SELECT * FROM blog.Entries JOIN blog.e'
result = get_result(completer, text)
assert result[:2] == [
table('Entries', -1),
join('EntAccLog ON EntAccLog.EntryID = Entries.EntryID', -1)
]
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_wildcard_column_expansion_with_table_qualifier(
completer, text, expected):
position = len('SELECT users.*')
completions = get_result(completer, text, position)
expected = [wildcard_expansion(expected)]
assert expected == completions
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_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_function(completer, text):
position = len('SELECT *')
completions = get_result(completer, text, position)
col_list = 'x'
expected = [wildcard_expansion(col_list)]
assert expected == completions
def test_wildcard_column_expansion_with_two_tables(completer):
text = 'SELECT * FROM "select" JOIN users u ON true'
position = len('SELECT *')
completions = get_result(completer, text, position)
cols = ('"select".id, "select".insert, "select"."ABC", '
'u.id, u.parentid, u.email, u.first_name, u.last_name')
expected = [wildcard_expansion(cols)]
assert completions == expected
def test_wildcard_column_expansion_with_alias_qualifier(completer):
text = 'SELECT p.* FROM custom.products p'
position = len('SELECT p.*')
completions = get_result(completer, text, position)
col_list = 'id, p.product_name, p.price'
expected = [wildcard_expansion(col_list)]
assert expected == completions
def test_wildcard_column_expansion_with_two_tables_and_parent(completer):
text = 'SELECT "select".* FROM public."select" JOIN custom.users u ON true'
position = len('SELECT "select".*')
completions = get_result(completer, text, position)
col_list = 'id, "select"."localtime", "select"."ABC"'
expected = [wildcard_expansion(col_list)]
assert expected == completions
def test_wildcard_column_expansion_with_table_qualifier(completer):
text = 'SELECT "select".* FROM public."select"'
position = len('SELECT "select".*')
completions = get_result(completer, text, position)
col_list = 'id, "select"."localtime", "select"."ABC"'
expected = [wildcard_expansion(col_list)]
assert expected == completions
def test_wildcard_column_expansion_with_two_tables(completer):
text = 'SELECT * FROM public."select" JOIN custom.users ON true'
position = len('SELECT *')
completions = get_result(completer, text, position)
cols = ('"select".id, "select"."localtime", "select"."ABC", '
'users.id, users.phone_number')
expected = [wildcard_expansion(cols)]
assert completions == expected
def test_learn_table_names(completer):
history = 'SELECT * FROM users; SELECT * FROM orders; SELECT * FROM users'
completer.extend_query_history(history)
text = 'SELECT * FROM '
completions = get_result(completer, text)
# `users` should be higher priority than `orders` (used more often)
users = table('users')
orders = table('orders')
assert completions.index(users) < completions.index(orders)
def test_suggested_join_conditions_with_same_table_twice(completer, text):
result = get_result(completer, text)
assert result == [
fk_join('u2.userid = u.id'),
fk_join('u2.userid = users.id'),
name_join('u2.userid = "Users".userid'),
name_join('u2.username = "******".username'),
alias('u'),
alias('u2'),
alias('users'),
alias('"Users"')
]
def test_table_names_after_from(completer, text):
result = get_result(completer, text)
assert set(result) == set(testdata.schemas_and_from_clause_items())
assert [c.text for c in result] == [
'public',
'orders',
'"select"',
'users',
'"Users"',
'functions',
'user_emails',
'_custom_fun()',
'custom_fun()',
'custom_func1()',
'custom_func2()',
'set_returning_func(x := , y := )',
]
def test_column_alias_search(completer):
result = get_result(completer, 'SELECT et FROM blog.Entries E',
len('SELECT et'))
cols = ('EntryText', 'EntryTitle', 'EntryID')
assert result[:3] == [column(c, -2) for c in cols]
def test_join_alias_search_without_aliases2(completer):
text = 'SELECT * FROM blog.Entries JOIN blog.et'
result = get_result(completer, text)
assert result[0] == join(
'EntryTags ON EntryTags.EntryID = Entries.EntryID', -2)
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_alias_search_with_aliases1(completer):
text = 'SELECT * FROM blog.e'
result = get_result(completer, text)
assert result[0] == table('Entries E', -1)
def test_alias_search_with_aliases2(completer):
text = 'SELECT * FROM blog.et'
result = get_result(completer, text)
assert result[0] == table('EntryTags ET', -2)
def test_wildcard_column_expansion_with_insert(completer, text):
position = text.index('*') + 1
completions = get_result(completer, text, position)
expected = [wildcard_expansion('ordered_date, status')]
assert expected == completions
def test_column_alias_search_qualified(completer):
result = get_result(completer, 'SELECT E.ei FROM blog.Entries E',
len('SELECT E.ei'))
cols = ('EntryID', 'EntryTitle')
assert result[:3] == [column(c, -2) for c in cols]
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_keyword_casing_upper(keyword_casing, expected, texts):
for text in texts:
completer = testdata.get_completer({'keyword_casing': keyword_casing})
completions = get_result(completer, text)
assert expected in [cpl.text for cpl in completions]
def test_schema_object_order(completer):
result = get_result(completer, 'SELECT * FROM u')
assert result[:3] == [
table(t, pos=-1) for t in ('users', 'custom."Users"', 'custom.users')
]
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
