GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position)
{
var response = new GetCompletionResponse();
string[] array = null;
var sqlStatement = new SqlParser(text);
var tokens = sqlStatement.Tokens;
var index = sqlStatement.FindToken(position);
if (index >= 0 && index < tokens.Count)
{
var token = sqlStatement.Tokens[index];
var value = token.Value;
}
if (array == null)
{
var sqlObject = sqlStatement.FindSqlObject(index);
string commandText = null;
if (sqlObject != null)
{
string name;
switch (sqlObject.Type)
{
case SqlObjectTypes.Table | SqlObjectTypes.View:
commandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME";
break;
case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
commandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME";
break;
case SqlObjectTypes.Table:
commandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME";
break;
case SqlObjectTypes.Column:
name = sqlObject.ParentName;
commandText = $@"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{name}'
ORDER BY ORDINAL_POSITION";
break;
case SqlObjectTypes.Value:
var items = sqlObject.ParentName.Split('.');
var 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)
{
commandText = $"select distinct top 10 {columnName} from {tableName} (nolock) order by 1";
}
}
break;
}
}
if (commandText != null)
{
Trace.WriteLine(commandText);
var list = new List <string>();
try
{
var executor = connection.Connection.CreateCommandExecutor();
list = executor.ExecuteReader(new ExecuteReaderRequest(commandText), 128, dataRecord => dataRecord.GetString(0)).ToList();
}
catch
{
}
array = new string[list.Count];
list.CopyTo(array);
}
}
// TODO response.Items = array;
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);
}
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();
string[] items = null;
var sqlStatement = new SqlParser(text);
sqlStatement.FindToken(position, out var previousToken, out var currentToken);
if (currentToken != null)
{
response.StartPosition = currentToken.StartPosition;
response.Length = currentToken.EndPosition - currentToken.StartPosition + 1;
}
else
{
response.StartPosition = position;
response.Length = 0;
}
var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken);
string commandText = null;
var cs = new OracleConnectionStringBuilder(connection.ConnectionString);
var userId = cs.UserID;
if (sqlObject != null)
{
string[] parts;
string owner;
switch (sqlObject.Type)
{
case SqlObjectTypes.Table:
var oracleName = new OracleName(userId, sqlObject.Name);
commandText = $@"select TABLE_NAME
from SYS.ALL_TABLES
where OWNER = '{oracleName.Owner}'
order by TABLE_NAME";
sqlObject.ParentName = oracleName.Owner;
break;
case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
var name = sqlObject.Name;
if (name != null)
{
parts = name.Split('.');
if (parts.Length > 1)
{
owner = parts[0].ToUpper();
sqlObject.ParentName = owner;
name = parts[1];
}
else
{
owner = userId;
sqlObject.ParentName = owner;
}
}
else
{
owner = userId;
sqlObject.ParentName = owner;
}
commandText =
$@"select OBJECT_NAME
from SYS.ALL_OBJECTS
where OWNER = '{owner}'
and OBJECT_TYPE in('TABLE','VIEW')
order by OBJECT_NAME";
sqlObject.Name = null;
break;
case SqlObjectTypes.Column:
var parentName = sqlObject.ParentName;
if (parentName != null)
{
parts = parentName.Split('.');
string tableName;
if (parts.Length == 2)
{
owner = parts[0].ToUpper();
tableName = parts[1].ToUpper();
}
else
{
owner = userId;
tableName = sqlObject.ParentName.ToUpper();
sqlObject.ParentName = owner + '.' + tableName;
}
commandText =
$@"select COLUMN_NAME
from SYS.ALL_TAB_COLUMNS
where OWNER = '{owner}'
and TABLE_NAME = '{tableName}'
order by COLUMN_ID";
}
break;
case SqlObjectTypes.Function:
oracleName = new OracleName(userId, sqlObject.ParentName);
commandText =
$@"select OBJECT_NAME
from SYS.ALL_OBJECTS
where OWNER = '{oracleName.Owner
}'
and OBJECT_TYPE = 'FUNCTION'
order by OBJECT_NAME";
sqlObject.ParentName = oracleName.Owner;
break;
//case SqlObjectTypes.Procedure:
// oracleName = new OracleName( userId, sqlObject.Name );
// break;
default:
break;
}
}
if (commandText != null)
{
var sb = new StringBuilder();
sb.Append(_objectExplorer.SchemasNode.Connection.DataSource);
sb.Append('.');
sb.Append(sqlObject.Type);
sb.Append('.');
var parentName = sqlObject.ParentName;
if (parentName != null)
{
sb.Append(parentName.ToUpper());
}
var name = sqlObject.Name;
if (!string.IsNullOrEmpty(name))
{
sb.Append('.');
sb.Append(name.ToUpper());
}
var key = sb.ToString();
var applicationData = DataCommanderApplication.Instance.ApplicationData;
var folderName = ConfigurationNodeName.FromType(typeof(OracleProvider)) + ConfigurationNode.Delimiter + "CompletionCache";
var folder = applicationData.CreateNode(folderName);
var contains = folder.Attributes.TryGetAttributeValue(key, out items);
response.FromCache = contains;
if (!contains)
{
var executor = connection.Connection.CreateCommandExecutor();
var table = executor.ExecuteDataTable(new ExecuteReaderRequest(commandText));
var count = table.Rows.Count;
items = new string[count];
for (var i = 0; i < count; i++)
{
items[i] = (string)table.Rows[i][0];
}
folder.Attributes.Add(key, items, null);
}
}
// SqlStatement sqlStatement = new SqlStatement(text);
// SqlObject sqlObject = sqlStatement.FindSqlObject(position);
// string commandText = null;
//
// switch (sqlObject.Type)
// {
// case SqlObjectType.TableOrView:
// string owner = connection.Database;
// commandText = string.Format("select * from (select table_name from all_tables where owner = '{0}' union select view_name from all_views where owner = '{0}') order by 1",owner);
// break;
// }
// string commandText;
//
// switch (word)
// {
// case "from":
// commandText = "select * from (select table_name from all_tables where owner = '{0}' union select view_name from all_views where owner = '{0}') order by 1";
// commandText = string.Format(commandText,objectBrowser.SchemasNode.SelectedSchema);
// break;
//
// case "table":
// case "update":
// commandText = string.Format("select table_name from all_tables where owner='{0}'",connection.DataSource);
// break;
//
// default:
// commandText = null;
// break;
// }
//
// string[] items = null;
//
// if (commandText != null)
// {
// string key = objectBrowser.SchemasNode.Connection.DataSource + "." +
// objectBrowser.SchemasNode.SelectedSchema + '.' + word;
//
// Folder appData = Application.Instance.ApplicationData.CurrentType;
//
// if (appData.SubFolders.Contains("CompletionCache"))
// {
// appData = (Folder)appData.SubFolders["CompletionCache"];
// }
// else
// {
// Folder folder = new Folder(appData,"CompletionCache");
// }
//
// bool containsKey = appData.Properties.ContainsKey(key);
//
// if (containsKey)
// {
// items = (string[])appData.Properties[key];
// }
// else
// {
// Cursor.Current = Cursors.WaitCursor;
// ArrayList list = new ArrayList();
// IDataReader dataReader = DataHelper.ExecuteReader(commandText,connection.Wrapped);
//
// while (dataReader.Read())
// list.Add(dataReader.GetString(0));
//
// items = new string[list.Count];
// list.CopyTo(items);
//
// appData.Properties[key] = items;
//
// Cursor.Current = Cursors.Default;
// }
// }
//
// return items;
throw new NotImplementedException();
//response.Items = items;
//return response;
}
GetCompletionResponse IProvider.GetCompletion(ConnectionBase connection, IDbTransaction transaction, string text, int position)
{
var response = new GetCompletionResponse();
var sqlStatement = new SqlParser(text);
var tokens = sqlStatement.Tokens;
sqlStatement.FindToken(position, out var previousToken, out var currentToken);
if (currentToken != null)
{
response.StartPosition = currentToken.StartPosition;
response.Length = currentToken.EndPosition - currentToken.StartPosition + 1;
var value = currentToken.Value;
}
else
{
response.StartPosition = position;
response.Length = 0;
}
var sqlObject = sqlStatement.FindSqlObject(previousToken, currentToken);
if (sqlObject != null)
{
string commandText = null;
response.FromCache = false;
switch (sqlObject.Type)
{
case SqlObjectTypes.Table:
commandText = @"
select name
from sqlite_master
where type = 'table'
order by name collate nocase";
break;
case SqlObjectTypes.Table | SqlObjectTypes.View | SqlObjectTypes.Function:
commandText = @"
select name
from sqlite_master
where type = 'table'
order by name collate nocase";
break;
case SqlObjectTypes.Index:
commandText = @"
select name
from sqlite_master
where type = 'index'
order by name collate nocase";
break;
case SqlObjectTypes.Column:
commandText = $"PRAGMA table_info({sqlObject.ParentName});";
break;
}
if (commandText != null)
{
var executor = DbCommandExecutorFactory.Create(connection.Connection);
response.Items = executor.ExecuteReader(new ExecuteReaderRequest(commandText), 128, dataRecord =>
{
var name = dataRecord.GetStringOrDefault(0);
return((IObjectName) new ObjectName(name));
}).ToList();
}
}
return(response);
}