def test_2_statements_2nd_current():
    suggestions = suggest_type(
        "select * from a; select * from ", "select * from a; select * from "
    )
    assert set(suggestions) == set([FromClauseItem(schema=None), Schema()])

    suggestions = suggest_type(
        "select * from a; select  from b", "select * from a; select "
    )
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "b", None, False),), qualifiable=True),
                Function(schema=None),
                Keyword("SELECT"),
            ]
        )
    )

    # Should work even if first statement is invalid
    suggestions = suggest_type(
        "select * from; select * from ", "select * from; select * from "
    )
    assert set(suggestions) == set([FromClauseItem(schema=None), Schema()])
def test_3_statements_2nd_current():
    suggestions = suggest_type(
        "select * from a; select * from ; select * from c",
        "select * from a; select * from ",
    )
    assert set(suggestions) == set([FromClauseItem(schema=None), Schema()])

    suggestions = suggest_type(
        "select * from a; select  from b; select * from c", "select * from a; select "
    )
    assert set(suggestions) == cols_etc("b", last_keyword="SELECT")
def test_specials_suggestion_datatype(sql, rel_type):
    suggestions = suggest_type(sql, sql)
    if rel_type == Function:
        assert suggestions == (Schema(), rel_type(schema=None, usage="special"))
    else:
        assert suggestions == (Schema(), rel_type(schema=None))

    sql = sql + 'abc.'
    suggestions = suggest_type(sql, sql)
    if rel_type == Function:
        assert suggestions == (rel_type(schema='abc', usage="special"),)
    else:
        assert suggestions == (rel_type(schema='abc'),)
def test_suggest_qualified_tables_views_functions_and_joins(expression):
    suggestions = suggest_type(expression, expression)
    tbls = tuple([(None, "foo", None, False)])
    assert (
        set(suggestions)
        == set([FromClauseItem(schema="sch", table_refs=tbls), Join(tbls, "sch")])
    )
def test_suggest_columns_after_multiple_joins():
    sql = """select * from t1
            inner join t2 ON
              t1.id = t2.t1_id
            inner join t3 ON
              t2.id = t3."""
    suggestions = suggest_type(sql, sql)
    assert Column(table_refs=((None, "t3", None, False),)) in set(suggestions)
def test_on_suggests_aliases_and_join_conditions(sql):
    suggestions = suggest_type(sql, sql)
    tables = ((None, "abc", "a", False), (None, "bcd", "b", False))
    assert (
        set(suggestions)
        == set(
            (JoinCondition(table_refs=tables, parent=None), Alias(aliases=("a", "b")))
        )
    )
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)
    tbls = tuple([(None, "abc", tbl_alias or None, False)])
    assert (
        set(suggestion)
        == set(
            [FromClauseItem(schema=None, table_refs=tbls), Schema(), Join(tbls, None)]
        )
    )
def test_left_join_with_comma():
    text = "select * from foo f left join bar b,"
    suggestions = suggest_type(text, text)
    # tbls should also include (None, 'bar', 'b', False)
    # but there's a bug with commas
    tbls = tuple([(None, "foo", "f", False)])
    assert (
        set(suggestions)
        == set([FromClauseItem(schema=None, table_refs=tbls), Schema()])
    )
def test_distinct_suggests_cols(text):
    suggestions = suggest_type(text, text)
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=(), local_tables=(), qualifiable=True),
                Function(schema=None),
                Keyword("DISTINCT"),
            ]
        )
    )
def test_statements_in_function_body(text):
    suggestions = suggest_type(text, text[:text.find("  ") + 1])
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "foo", None, False),), qualifiable=True),
                Function(schema=None),
                Keyword("SELECT"),
            ]
        )
    )
def test_col_comma_suggests_cols():
    suggestions = suggest_type("SELECT a, b, FROM tbl", "SELECT a, b,")
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "tbl", None, False),), qualifiable=True),
                Function(schema=None),
                Keyword("SELECT"),
            ]
        )
    )
def test_select_suggests_cols_and_funcs():
    suggestions = suggest_type("SELECT ", "SELECT ")
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=(), qualifiable=True),
                Function(schema=None),
                Keyword("SELECT"),
            ]
        )
    )
def test_suggest_after_join_with_one_table(expression):
    suggestions = suggest_type(expression, expression)
    tables = ((None, "foo", None, False),)
    assert (
        set(suggestions)
        == set(
            [
                FromClauseItem(schema=None, table_refs=tables),
                Join(((None, "foo", None, False),), None),
                Schema(),
            ]
        )
    )
def test_dot_suggests_cols_of_an_alias_where(sql):
    suggestions = suggest_type(sql, sql)
    assert (
        set(suggestions)
        == set(
            [
                Table(schema="t1"),
                View(schema="t1"),
                Column(table_refs=((None, "tabl1", "t1", False),)),
                Function(schema="t1"),
            ]
        )
    )
def test_dot_suggests_cols_of_a_table_or_schema_qualified_table():
    suggestions = suggest_type("SELECT tabl. FROM tabl", "SELECT tabl.")
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "tabl", None, False),)),
                Table(schema="tabl"),
                View(schema="tabl"),
                Function(schema="tabl"),
            ]
        )
    )
def test_suggest_after_join_with_two_tables(expression):
    suggestions = suggest_type(expression, expression)
    tables = tuple([(None, "foo", None, False), (None, "bar", None, False)])
    assert (
        set(suggestions)
        == set(
            [
                FromClauseItem(schema=None, table_refs=tables),
                Join(tables, None),
                Schema(),
            ]
        )
    )
def test_join_alias_dot_suggests_cols2(sql):
    suggestion = suggest_type(sql, sql)
    assert (
        set(suggestion)
        == set(
            [
                Column(table_refs=((None, "def", "d", False),)),
                Table(schema="d"),
                View(schema="d"),
                Function(schema="d"),
            ]
        )
    )
def test_sub_select_col_name_completion():
    suggestions = suggest_type(
        "SELECT * FROM (SELECT  FROM abc", "SELECT * FROM (SELECT "
    )
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "abc", None, False),), qualifiable=True),
                Function(schema=None),
                Keyword("SELECT"),
            ]
        )
    )
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 (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "foo", "f", False),)),
                Table(schema="f"),
                View(schema="f"),
                Function(schema="f"),
            ]
        )
    )
def test_join_alias_dot_suggests_cols1(sql):
    suggestions = suggest_type(sql, sql)
    tables = ((None, "abc", "a", False), (None, "def", "d", False))
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "abc", "a", False),)),
                Table(schema="a"),
                View(schema="a"),
                Function(schema="a"),
                JoinCondition(table_refs=tables, parent=(None, "abc", "a", False)),
            ]
        )
    )
def test_sub_select_dot_col_name_completion():
    suggestions = suggest_type(
        "SELECT * FROM (SELECT t. FROM tabl t", "SELECT * FROM (SELECT t."
    )
    assert (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "tabl", "t", False),)),
                Table(schema="t"),
                View(schema="t"),
                Function(schema="t"),
            ]
        )
    )
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 (
        set(suggestions)
        == set(
            [
                Column(table_refs=((None, "tabl2", "t2", False),)),
                Table(schema="t2"),
                View(schema="t2"),
                Function(schema="t2"),
            ]
        )
    )
def test_distinct_and_order_by_suggestions_with_alias_given(text, text_before):
    suggestions = suggest_type(text, text_before)
    assert (
        set(suggestions)
        == set(
            [
                Column(
                    table_refs=(TableReference(None, "tbl", "x", False),),
                    local_tables=(),
                    qualifiable=False,
                ),
                Table(schema="x"),
                View(schema="x"),
                Function(schema="x"),
            ]
        )
    )
def test_distinct_and_order_by_suggestions_with_aliases(
    text, text_before, last_keyword
):
    suggestions = suggest_type(text, text_before)
    assert (
        set(suggestions)
        == set(
            [
                Column(
                    table_refs=(
                        TableReference(None, "tbl", "x", False),
                        TableReference(None, "tbl1", "y", False),
                    ),
                    local_tables=(),
                    qualifiable=True,
                ),
                Function(schema=None),
                Keyword(last_keyword),
            ]
        )
    )
def test_where_equals_any_suggests_columns_or_keywords():
    text = "SELECT * FROM tabl WHERE foo = ANY("
    suggestions = suggest_type(text, text)
    assert set(suggestions) == cols_etc("tabl", last_keyword="WHERE")
def test_suggest_where_keyword(text):
    # https://github.com/dbcli/mycli/issues/135
    suggestions = suggest_type(text, text)
    assert set(suggestions) == cols_etc("foo", last_keyword="WHERE")
def test_invalid_sql():
    # issue 317
    text = "selt *"
    suggestions = suggest_type(text, text)
    assert suggestions == (Keyword(),)
def test_alias_suggests_keywords(text):
    suggestions = suggest_type(text, text)
    assert suggestions == (Keyword(),)
def test_identifier_suggests_types_in_parentheses(text):
    assert (
        set(suggest_type(text, text))
        == set([Datatype(schema=None), Table(schema=None), Schema()])
    )
def test_after_as(expression):
    suggestions = suggest_type(expression, expression)
    assert set(suggestions) == set()