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 = result_set(completer, text)
assert result == set(
[name_join('y = f2.y'), name_join('x = f2.x')] +
testdata.columns('set_returning_func', typ='functions'))
def test_cased_joins(completer, text):
result = result_set(completer, text)
assert result == 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_schema_qualified_function_name(completer):
text = 'SELECT custom.func'
result = result_set(completer, text)
assert result == set([
function('func3()', -len('func')),
function('set_returning_func()', -len('func'))
])
def test_aliased_joins(completer, text):
result = result_set(completer, text)
assert result == 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_suggested_joins(completer, text):
result = result_set(completer, text)
assert result == 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_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 = result_set(completer, text, position)
assert result == set(cased_funcs + cols + testdata.builtin_functions() +
testdata.keywords())
def test_builtin_function_name_completion(completer):
result = result_set(completer, 'SELECT MA')
assert result == set([
function('MAX', -2),
keyword('MAXEXTENTS', -2),
keyword('MATERIALIZED VIEW', -2)
])
def test_user_function_name_completion_matches_anywhere(completer):
result = result_set(completer, 'SELECT om')
assert result == set([
function('custom_fun()', -2),
function('_custom_fun()', -2),
function('custom_func1()', -2),
function('custom_func2()', -2)
])
def test_suggested_join_conditions(completer, text):
result = result_set(completer, text)
assert result == set([
alias('users'),
alias('shipments'),
name_join('shipments.id = users.id'),
fk_join('shipments.user_id = users.id')
])
def test_aliases_with_casing(completer, text):
result = result_set(completer, text)
assert result == 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_table_casing(completer, text):
result = result_set(completer, text)
assert result == set(cased_schemas + [
table('users'),
table('Orders'),
table('"select"'),
function('Func1()'),
function('func2()')
])
def test_set_schema(completer):
text = ('SET SCHEMA ')
result = result_set(completer, text)
assert result == set([
schema(u"'blog'"),
schema(u"'Custom'"),
schema(u"'custom'"),
schema(u"'public'")
])
def test_suggested_aliases_after_on(completer, text):
position = len('SELECT u.name, o.id FROM users u JOIN orders o ON ')
result = result_set(completer, text, position)
assert result == set([
alias('u'),
name_join('o.id = u.id'),
name_join('o.email = u.email'),
alias('o')
])
def test_table_aliases(completer, text):
result = result_set(completer, text)
assert result == 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_user_function_name_completion(completer):
result = result_set(completer, 'SELECT cu')
assert result == set([
function('custom_fun()', -2),
function('_custom_fun()', -2),
function('custom_func1()', -2),
function('custom_func2()', -2),
keyword('CURRENT', -2),
])
def test_suggested_tables_after_on(completer, text):
position = len('SELECT users.name, orders.id FROM users JOIN orders ON ')
result = result_set(completer, text, position)
assert result == set([
name_join('orders.id = users.id'),
name_join('orders.email = users.email'),
alias('users'),
alias('orders')
])
def test_suggest_columns_from_cte(completer):
result = result_set(
completer, 'WITH cte AS (SELECT foo, bar FROM baz) SELECT FROM cte',
len('WITH cte AS (SELECT foo, bar FROM baz) SELECT '))
expected = ([
Completion('foo', 0, display_meta='column'),
Completion('bar', 0, display_meta='column'),
] + testdata.functions_and_keywords())
assert set(expected) == result
def test_suggested_table_names_with_schema_dot2(completer, text,
use_leading_double_quote):
if use_leading_double_quote:
text += '"'
start_position = -1
else:
start_position = 0
result = result_set(completer, text)
assert result == set(testdata.from_clause_items('Custom', start_position))
def test_suggest_cte_names(completer):
text = '''
WITH cte1 AS (SELECT a, b, c FROM foo),
cte2 AS (SELECT d, e, f FROM bar)
SELECT * FROM
'''
result = result_set(completer, text)
expected = set([
Completion('cte1', 0, display_meta='table'),
Completion('cte2', 0, display_meta='table'),
])
assert expected <= result
def test_suggestions_after_on(completer, text):
position = len(
'SELECT x.id, y.product_name FROM custom.products x JOIN custom.products y ON '
)
result = result_set(completer, text, position)
assert result == set([
alias('x'),
alias('y'),
name_join('y.price = x.price'),
name_join('y.product_name = x.product_name'),
name_join('y.id = x.id')
])
def test_duplicate_table_aliases(completer, text):
result = result_set(completer, text)
assert result == set(testdata.schemas() + [
table('orders o2'),
table('users u'),
table('"Users" U'),
table('"select" s'),
view('user_emails ue'),
view('functions f'),
function('_custom_fun() cf'),
function('custom_fun() cf'),
function('custom_func1() cf'),
function('custom_func2() cf'),
function('set_returning_func(x := , y := ) srf',
display='set_returning_func(x, y) srf'),
])
def test_duplicate_aliases_with_casing(completer, text):
result = result_set(completer, text)
assert result == set([
schema('PUBLIC'),
table('Orders O2'),
table('Users U'),
table('"Users" U'),
table('"select" s'),
view('User_Emails UE'),
view('Functions F'),
function('_custom_fun() cf'),
function('Custom_Fun() CF'),
function('Custom_Func1() CF'),
function('custom_func2() cf'),
function('set_returning_func(x := , y := ) srf',
display='set_returning_func(x, y) srf'),
])
def test_suggested_column_names_from_qualified_shadowed_table(completer, text):
result = result_set(completer, text, position=text.find(' ') + 1)
assert result == set(
testdata.columns_functions_and_keywords('users', 'custom'))
def test_suggested_column_names_from_shadowed_visible_table(completer, table):
result = result_set(completer, 'SELECT FROM ' + table, len('SELECT '))
assert result == set(testdata.columns_functions_and_keywords('users'))
def test_all_schema_objects_with_aliases(completer):
text = ('SELECT * FROM ')
result = result_set(completer, text)
assert result >= set(
[table(x) for x in ('orders o', '"select" s', 'custom.shipments s')] +
[function(x) for x in ('func2() f', 'custom.func3() f')])
def test_all_schema_objects_with_casing(completer):
text = 'SELECT * FROM '
result = result_set(completer, text)
assert result >= set(
[table(x) for x in ('Orders', '"select"', 'CUSTOM.shipments')] +
[function(x + '()') for x in ('func2', 'CUSTOM.func3')])
def test_all_schema_objects(completer):
text = ('SELECT * FROM ')
result = result_set(completer, text)
assert result >= set(
[table(x) for x in ('orders', '"select"', 'custom.shipments')] +
[function(x + '()') for x in ('func2', 'custom.func3')])
def test_schema_or_visible_table_completion(completer, text):
result = result_set(completer, text)
assert result == set(testdata.schemas_and_from_clause_items())
def test_suggest_columns_from_quoted_table(completer, text):
position = len('SELECT U.')
result = result_set(completer, text, position)
assert result == set(testdata.columns('Users', 'custom'))
def test_suggest_columns_from_aliased_set_returning_function(completer):
result = result_set(completer,
'select f. from custom.set_returning_func() f',
len('select f.'))
assert result == set(
testdata.columns('set_returning_func', 'custom', 'functions'))
