def test_function_alias_search_without_aliases(self, completer):
text = 'SELECT blog.ees'
result = get_result(completer, text)
first = result[0]
self.assertEqual(first.start_position, -3)
self.assertEqual(first.text, 'extract_entry_symbols()')
self.assertEqual(first.display, 'extract_entry_symbols(_entryid)')
def test_function_alias_search_with_aliases(self, completer):
text = 'SELECT blog.ee'
result = get_result(completer, text)
first = result[0]
self.assertEqual(first.start_position, -2)
self.assertEqual(first.text, 'enter_entry(_title := , _text := )')
self.assertEqual(first.display, 'enter_entry(_title, _text)')
def test_join_alias_search_without_aliases1(self, completer):
text = 'SELECT * FROM blog.Entries JOIN blog.e'
result = get_result(completer, text)
self.assertListEqual(result[:2], [
table('Entries', -1),
join('EntAccLog ON EntAccLog.EntryID = Entries.EntryID', -1)
])
def test_function_column_name(self, completer):
for l in range(len('SELECT * FROM Functions WHERE function:'),
len('SELECT * FROM Functions WHERE function:text') + 1):
self.assertListEqual(
[],
get_result(completer,
'SELECT * FROM Functions WHERE function:text'[:l]))
def test_columns_before_keywords(self, completer):
text = 'SELECT * FROM orders WHERE s'
completions = get_result(completer, text)
col = column('status', -1)
kw = keyword('SELECT', -1)
self.assertTrue(completions.index(col) < completions.index(kw))
def test_wildcard_column_expansion_with_function(self, completer, text):
position = len('SELECT *')
completions = get_result(completer, text, position)
col_list = 'x'
expected = [wildcard_expansion(col_list)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_alias(self, 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)]
self.assertListEqual(expected, completions)
def test_learn_keywords(self, 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)
self.assertEqual(completions[0].text, 'VIEW')
def test_wildcard_column_expansion_with_alias_qualifier(self, 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)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_table_qualifier(self, 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)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_two_tables(self, 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)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_table_qualifier(self, completer, text_and_expected):
text = text_and_expected[0]
expected = text_and_expected[1]
position = len('SELECT users.*')
completions = get_result(completer, text, position)
expected = [wildcard_expansion(expected)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_two_tables_and_parent(self, completer):
text = 'SELECT "select".* FROM "select" JOIN users u ON true'
position = len('SELECT "select".*')
completions = get_result(completer, text, position)
col_list = 'id, "select".insert, "select"."ABC"'
expected = [wildcard_expansion(col_list)]
self.assertListEqual(expected, completions)
def test_wildcard_column_expansion_with_two_tables(self, 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)]
self.assertListEqual(expected, completions)
def test_suggested_join_conditions_with_same_table_twice(self, completer, text):
result = get_result(completer, text)
self.assertListEqual(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_learn_table_names(self, 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')
self.assertTrue(completions.index(users) < completions.index(orders))
def test_table_names_after_from(self, completer, text):
result = get_result(completer, text)
self.assertSetEqual(set(result), set(
testdata.schemas_and_from_clause_items()))
self.assertTrue([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_builtin_function_matches_only_at_start(self, completer):
text = 'SELECT IN'
result = [c.text for c in get_result(completer, text)]
self.assertTrue('MIN' not in result)
def test_join_alias_search_without_aliases2(self, completer):
text = 'SELECT * FROM blog.Entries JOIN blog.et'
result = get_result(completer, text)
self.assertEqual(
result[0],
join('EntryTags ON EntryTags.EntryID = Entries.EntryID', -2))
def test_column_alias_search_qualified(self, completer):
result = get_result(completer, 'SELECT E.ei FROM blog.Entries E',
len('SELECT E.ei'))
cols = ('EntryID', 'EntryTitle')
self.assertListEqual(result[:3], [column(c, -2) for c in cols])
def test_wildcard_column_expansion_with_insert(self, completer, text):
position = text.index('*') + 1
completions = get_result(completer, text, position)
expected = [wildcard_expansion('ordered_date, status')]
self.assertListEqual(expected, completions)
def test_schema_object_order(self, completer):
result = get_result(completer, 'SELECT * FROM u')
self.assertListEqual(result[:3], [
table(t, pos=-1)
for t in ('users', 'custom."Users"', 'custom.users')
])
def test_alias_search_with_aliases2(self, completer):
text = 'SELECT * FROM blog.et'
result = get_result(completer, text)
self.assertEqual(result[0], table('EntryTags ET', -2))
def test_drop_alter_function(self, completer, action):
self.assertListEqual(get_result(completer, action + ' FUNCTION set_ret'),
[function('set_returning_func(x integer, y integer)', -len('set_ret'))])
def test_keyword_casing_upper(self, keyword_casing, expected, texts):
for text in texts:
completer = testdata.get_completer(
{'keyword_casing': keyword_casing})
completions = get_result(completer, text)
self.assertTrue(expected in [cpl.text for cpl in completions])
def test_alias_search_with_aliases1(self, completer):
text = 'SELECT * FROM blog.e'
result = get_result(completer, text)
self.assertEqual(result[0], table('Entries E', -1))
