Esempio n. 1
0
    GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position)
    {
        var response = new GetCompletionResponse
        {
            FromCache = false
        };

        var sqlStatement = new SqlParser(text);
        var tokens       = sqlStatement.Tokens;

        sqlStatement.FindToken(position, out var previousToken, out var currentToken);

        if (currentToken != null)
        {
            var parts          = new IdentifierParser(new StringReader(currentToken.Value)).Parse();
            var lastPart       = parts.Last();
            var lastPartLength = lastPart != null ? lastPart.Length : 0;
            response.StartPosition = currentToken.EndPosition - lastPartLength + 1;
            response.Length        = lastPartLength;
        }
        else
        {
            response.StartPosition = position;
            response.Length        = 0;
        }

        var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken);

        if (sqlObject != null)
        {
            var statements = new List <string>();

            switch (sqlObject.Type)
            {
            case SqlObjectTypes.Database:
                statements.Add(SqlServerObject.GetDatabases());
                break;

            case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
            {
                var nameParts = new IdentifierParser(new StringReader(sqlObject.Name ?? string.Empty)).Parse().ToList();
                var name      = new DatabaseObjectMultipartName(connection.Database, nameParts);

                switch (nameParts.Count)
                {
                case 0:
                case 1:
                    statements.Add(SqlServerObject.GetDatabases());
                    statements.Add(SqlServerObject.GetTables(name.Database, new[] { "BASE TABLE", "SYSTEM VIEW" }));
                    break;

                case 2:
                    statements.Add(SqlServerObject.GetTables(name.Database, new[] { "BASE TABLE", "SYSTEM VIEW" }));
                    break;
                }
            }
            break;

            case SqlObjectTypes.Column:
            {
                var nameParts = new IdentifierParser(new StringReader(sqlObject.ParentName ?? string.Empty)).Parse().ToList();
                var name      = new DatabaseObjectMultipartName(connection.Database, nameParts);
                statements.Add(SqlServerObject.GetColumns(name.Database, name.Name));
            }
            break;
            }

            var objectNames = new List <IObjectName>();
            var executor    = DbCommandExecutorFactory.Create(connection.Connection);
            foreach (var statement in statements)
            {
                var items = executor.ExecuteReader(new ExecuteReaderRequest(statement), 128, dataRecord => new ObjectName(null, dataRecord.GetString(0)));
                objectNames.AddRange(items);
            }

            response.Items = objectNames;
        }

        return(response);
    }
Esempio n. 2
0
    GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position)
    {
        var response = new GetCompletionResponse
        {
            FromCache = false
        };
        List <IObjectName> array = null;
        var sqlStatement         = new SqlParser(text);
        var tokens = sqlStatement.Tokens;

        sqlStatement.FindToken(position, out var previousToken, out var currentToken);

        if (currentToken != null)
        {
            var parts    = new IdentifierParser(new StringReader(currentToken.Value)).Parse().ToList();
            var lastPart = parts.Count > 0
                ? parts.Last()
                : null;
            var lastPartLength = lastPart != null
                ? lastPart.Length
                : 0;
            response.StartPosition = currentToken.EndPosition - lastPartLength + 1;
            response.Length        = lastPartLength;
            var value = currentToken.Value;
            if (value.Length > 0 && value[0] == '@')
            {
                if (value.IndexOf("@@") == 0)
                {
                    array = _keyWords.Where(k => k.StartsWith(value)).Select(keyWord => (IObjectName) new NonSqlObjectName(keyWord)).ToList();
                }
                else
                {
                    var list = new SortedList <string?, object>();

                    for (var i = 0; i < tokens.Count; i++)
                    {
                        var token   = tokens[i];
                        var keyWord = token.Value;

                        if (keyWord != null && keyWord.Length >= 2 && keyWord.IndexOf(value) == 0 && keyWord != value)
                        {
                            if (!list.ContainsKey(token.Value))
                            {
                                list.Add(token.Value, null);
                            }
                        }
                    }

                    array = list.Keys.Select(keyWord => (IObjectName) new NonSqlObjectName(keyWord)).ToList();
                }
            }
        }
        else
        {
            response.StartPosition = position;
            response.Length        = 0;
        }

        if (array == null)
        {
            var    sqlObject   = sqlStatement.FindSqlObject(previousToken, currentToken);
            string commandText = null;

            if (sqlObject != null)
            {
                DatabaseObjectMultipartName name;
                int i;

                switch (sqlObject.Type)
                {
                case SqlObjectTypes.Database:
                    commandText = SqlServerObject.GetDatabases();
                    break;

                case SqlObjectTypes.Table:
                case SqlObjectTypes.View:
                case SqlObjectTypes.Function:
                case SqlObjectTypes.Table | SqlObjectTypes.View:
                case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
                {
                    name = new DatabaseObjectMultipartName(connection.Database, sqlObject.Name);
                    var nameParts = sqlObject.Name != null
                            ? new IdentifierParser(new StringReader(sqlObject.Name)).Parse().ToList()
                            : null;
                    var namePartsCount = nameParts != null
                            ? nameParts.Count
                            : 0;
                    var statements = new List <string>();

                    switch (namePartsCount)
                    {
                    case 0:
                    case 1:
                    {
                        statements.Add(SqlServerObject.GetDatabases());
                        statements.Add(SqlServerObject.GetSchemas());

                        var objectTypes = sqlObject.Type.ToObjectTypes();
                        statements.Add(SqlServerObject.GetObjects("dbo", objectTypes));
                    }
                    break;

                    case 2:
                        if (nameParts[0] != null)
                        {
                            statements.Add(SqlServerObject.GetSchemas(nameParts[0]));

                            var objectTypes = sqlObject.Type.ToObjectTypes();
                            statements.Add(SqlServerObject.GetObjects(nameParts[0], objectTypes));
                        }

                        break;

                    case 3:
                    {
                        if (nameParts[0] != null && nameParts[1] != null)
                        {
                            var objectTypes = sqlObject.Type.ToObjectTypes();
                            statements.Add(SqlServerObject.GetObjects(nameParts[0], nameParts[1], objectTypes));
                        }
                    }
                    break;
                    }

                    commandText = statements.Count > 0
                            ? string.Join("\r\n", statements)
                            : null;
                }
                break;

                case SqlObjectTypes.Column:
                    name = new DatabaseObjectMultipartName(connection.Database, sqlObject.ParentName);
                    string[] owners;

                    if (name.Schema != null)
                    {
                        owners = new[] { name.Schema }
                    }
                    ;
                    else
                    {
                        owners = new[] { "dbo", "sys" }
                    };

                    var sb = new StringBuilder();
                    for (i = 0; i < owners.Length; i++)
                    {
                        if (i > 0)
                        {
                            sb.Append(',');
                        }

                        sb.AppendFormat("'{0}'", owners[i]);
                    }

                    var ownersString = sb.ToString();
                    commandText = string.Format(@"declare @schema_id int
select  top 1 @schema_id = s.schema_id
from    [{0}].sys.schemas s
where   s.name  in({1})

if @schema_id is not null
begin
    declare @object_id int
    select  @object_id = o.object_id
    from    [{0}].sys.all_objects o
    where   o.name = '{2}'
            and o.schema_id = @schema_id
            and o.type in('S','U','TF','V')

    if @object_id is not null
    begin
        select  name
        from [{0}].sys.all_columns c
        where c.object_id = @object_id
        order by column_id
    end
end", name.Database, ownersString, name.Name);
                    break;

                case SqlObjectTypes.Procedure:
                    name = new DatabaseObjectMultipartName(connection.Database, sqlObject.Name);

                    if (name.Schema == null)
                    {
                        name.Schema = "dbo";
                    }

                    commandText = string.Format(@"select
     s.name
    ,o.name
from [{0}].sys.objects o
join [{0}].sys.schemas s
on o.schema_id = s.schema_id
where   o.type in('P','X')
order by 1", name.Database);
                    break;

                case SqlObjectTypes.Trigger:
                    commandText = "select name from sysobjects where xtype = 'TR' order by name";
                    break;

                case SqlObjectTypes.Value:
                    var items = sqlObject.ParentName.Split('.');
                    i = items.Length - 1;
                    var    columnName       = items[i];
                    string tableNameOrAlias = null;
                    if (i > 0)
                    {
                        i--;
                        tableNameOrAlias = items[i];
                    }

                    if (tableNameOrAlias != null)
                    {
                        var contains = sqlStatement.Tables.TryGetValue(tableNameOrAlias, out var tableName);
                        if (contains)
                        {
                            string where;
                            var tokenIndex = previousToken.Index + 1;
                            if (tokenIndex < tokens.Count)
                            {
                                var token      = tokens[tokenIndex];
                                var tokenValue = token.Value;
                                var indexofAny = tokenValue.IndexOfAny(new[] { '\r', '\n' });
                                if (indexofAny >= 0)
                                {
                                    tokenValue = tokenValue.Substring(0, indexofAny);
                                }

                                string?like;
                                if (tokenValue.Length > 0)
                                {
                                    if (tokenValue.Contains('%'))
                                    {
                                        like = tokenValue;
                                    }
                                    else
                                    {
                                        like = tokenValue + '%';
                                    }
                                }
                                else
                                {
                                    like = "%";
                                }

                                where = $"where {columnName} like N'{like}'";
                            }
                            else
                            {
                                where = null;
                            }

                            commandText = $@"select distinct {columnName}
from
(
    select top 1000 {columnName}
    from {tableName} (readpast)
    {where}
) t";
                        }
                    }

                    break;
                }
            }

            if (commandText != null)
            {
                Log.Write(LogLevel.Trace, "commandText:\r\n{0}", commandText);
                var list = new List <IObjectName>();
                try
                {
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.OpenAsync(CancellationToken.None).Wait();
                    }

                    var executor = connection.Connection.CreateCommandExecutor();
                    executor.ExecuteReader(new ExecuteReaderRequest(commandText, null, transaction), dataReader =>
                    {
                        while (true)
                        {
                            var fieldCount = dataReader.FieldCount;
                            while (dataReader.Read())
                            {
                                string schemaName;
                                string objectName;

                                if (fieldCount == 1)
                                {
                                    schemaName = null;
                                    objectName = dataReader[0].ToString();
                                }
                                else
                                {
                                    schemaName = dataReader.GetStringOrDefault(0);
                                    objectName = dataReader.GetString(1);
                                }

                                list.Add(new ObjectName(schemaName, objectName));
                            }

                            if (!dataReader.NextResult())
                            {
                                break;
                            }
                        }
                    });
                }
                catch
                {
                }

                array = list;
            }
        }

        response.Items = array;
        return(response);
    }