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);
}
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);
}