public static void Highlighting(RichTextBox richTextBox, DatabaseType databaseType, bool keepPosition = true, int?startIndex = null, int?stopIndex = null)
{
int start = richTextBox.SelectionStart;
var dataTypes = DataTypeManager.GetDataTypeSpecifications(databaseType).Select(item => item.Name);
var keywords = KeywordManager.GetKeywords(databaseType);
var functions = FunctionManager.GetFunctionSpecifications(databaseType).Select(item => item.Name).Except(keywords);
string dataTypesRegex = $@"\b({string.Join("|", dataTypes)})\b";
string keywordsRegex = $@"\b({string.Join("|", keywords)})\b";
string functionsRegex = $@"\b({string.Join("|", functions)})\b";
string stringRegex = $@"(['][^'^(^)]*['])";
Highlighting(richTextBox, dataTypesRegex, RegexOptions.IgnoreCase, Color.Blue);
Highlighting(richTextBox, keywordsRegex, RegexOptions.IgnoreCase, Color.Blue);
Highlighting(richTextBox, functionsRegex, RegexOptions.IgnoreCase, ColorTranslator.FromHtml("#FF00FF"));
Highlighting(richTextBox, stringRegex, RegexOptions.IgnoreCase, Color.Red);
string commentString = databaseType == DatabaseType.MySql ? "#" : "--";
string commentRegex = $@"({commentString}).*[\n]?";
Highlighting(richTextBox, commentRegex, RegexOptions.IgnoreCase, Color.Green);
richTextBox.SelectionStart = keepPosition ? start : 0;
richTextBox.SelectionLength = 0;
richTextBox.Focus();
}
public void SetupIntellisence()
{
this.intellisenseSetuped = true;
this.enableIntellisense = true;
this.keywords = KeywordManager.GetKeywords(this.DatabaseType);
this.builtinFunctions = FunctionManager.GetFunctionSpecifications(this.DatabaseType);
this.schemaInfo = DataStore.GetSchemaInfo(this.DatabaseType);
this.allWords = SqlWordFinder.FindWords(this.DatabaseType, "");
this.dbOwners = this.allWords.Where(item => item.Type == SqlWordTokenType.Owner).Select(item => item.Text).ToList();
}
public override void Translate()
{
if (this.sourceDbType == this.targetDbType)
{
return;
}
this.LoadMappings();
this.sourceFuncSpecs = FunctionManager.GetFunctionSpecifications(this.sourceDbType);
this.targetFuncSpecs = FunctionManager.GetFunctionSpecifications(this.targetDbType);
foreach (TokenInfo token in this.functions)
{
List <FunctionFomular> fomulars = this.GetFunctionFomulars(token.Symbol);
foreach (FunctionFomular fomular in fomulars)
{
string name = fomular.Name;
bool useBrackets = false;
MappingFunctionInfo targetFunctionInfo = this.GetMappingFunctionInfo(name, out useBrackets);
if (!string.IsNullOrEmpty(targetFunctionInfo.Name))
{
if (targetFunctionInfo.Name.ToUpper().Trim() != name.ToUpper().Trim())
{
string oldExp = fomular.Expression;
string newExp = this.ReplaceValue(fomular.Expression, name, targetFunctionInfo.Name);
fomular.Expression = newExp;
token.Symbol = this.ReplaceValue(token.Symbol, oldExp, newExp);
}
}
Dictionary <string, string> dictDataType = null;
string newExpression = this.ParseFomular(this.sourceFuncSpecs, this.targetFuncSpecs, fomular, targetFunctionInfo, out dictDataType);
if (newExpression != fomular.Expression)
{
token.Symbol = this.ReplaceValue(token.Symbol, fomular.Expression, newExpression);
}
}
}
}
private void ProcessTokens()
{
ScriptTokenExtracter tokenExtracter = new ScriptTokenExtracter(this.Script);
IEnumerable <TokenInfo> tokens = tokenExtracter.Extract();
IEnumerable <string> keywords = KeywordManager.GetKeywords(this.TargetInterpreter.DatabaseType);
IEnumerable <string> functions = FunctionManager.GetFunctionSpecifications(this.TargetInterpreter.DatabaseType).Select(item => item.Name);
this.columnTranslator = new ColumnTranslator(this.SourceInterpreter, this.TargetInterpreter, null);
columnTranslator.LoadMappings();
Func <TokenInfo, bool> changeValue = (token) =>
{
string oldSymbol = token.Symbol;
bool hasChanged = false;
if (this.TargetInterpreter.DatabaseType == DatabaseType.SqlServer)
{
token.Symbol = "@" + token.Symbol.TrimStart('@');
hasChanged = true;
if (!this.ReplacedVariables.ContainsKey(oldSymbol))
{
this.ReplacedVariables.Add(oldSymbol, token.Symbol);
}
}
else if (this.SourceInterpreter.DatabaseType == DatabaseType.SqlServer)
{
token.Symbol = token.Symbol.TrimStart('@');
if (keywords.Contains(token.Symbol.ToUpper()) || functions.Contains(token.Symbol.ToUpper()))
{
token.Symbol = token.Symbol + "_";
}
hasChanged = true;
if (!this.ReplacedVariables.ContainsKey(oldSymbol))
{
this.ReplacedVariables.Add(oldSymbol, token.Symbol);
}
}
return(hasChanged);
};
this.ReplaceTokens(tokens);
foreach (TokenInfo token in tokens.Where(item => item != null && (item.Type == TokenType.ParameterName || item.Type == TokenType.VariableName)))
{
changeValue(token);
}
IEnumerable <string> aliases = tokens.Where(item => item.Symbol != null && item.Type == TokenType.Alias).Select(item => item.Symbol);
foreach (TokenInfo token in tokens)
{
if (token.Symbol == null)
{
continue;
}
if (token.Type == TokenType.DataType)
{
TableColumn tableColumn = this.CreateTableColumn(token.Symbol);
columnTranslator.ConvertDataType(tableColumn);
token.Symbol = this.TargetInterpreter.ParseDataType(tableColumn);
}
else if (token is TableName tableName)
{
if (tableName.Name != null && tableName.Name.Symbol != null)
{
string alias = tableName.Alias == null ? "" : tableName.Alias.ToString();
token.Symbol = $"{ this.GetQuotedName(tableName.Name.ToString(), token.Type)}" + (string.IsNullOrEmpty(alias) ? "" : " " + alias);
}
}
else if (token is ColumnName columnName)
{
string columnContent = "";
if (columnName.Name != null)
{
string strTableName = "";
if (columnName.TableName != null)
{
strTableName = (!aliases.Contains(columnName.TableName.Symbol) ?
this.GetQuotedName(columnName.TableName.ToString().Trim('.'), token.Type) :
columnName.TableName.Symbol)
+ ".";
}
string strColName = this.GetQuotedName(columnName.Name.ToString().Trim('.'), token.Type);
columnContent = $"{strTableName}{strColName}";
if (columnName.Alias != null && !string.IsNullOrEmpty(columnName.Alias.Symbol))
{
string alias = columnName.Alias.ToString();
columnContent += $" AS {this.GetQuotedName(alias, token.Type)}";
}
token.Symbol = columnContent;
}
}
else if (token.Type == TokenType.TableName || token.Type == TokenType.ColumnName)
{
if (!aliases.Contains(token.Symbol))
{
token.Symbol = this.GetQuotedName(token.Symbol.Trim('.'), token.Type);
}
}
else if (token.Type == TokenType.RoutineName)
{
token.Symbol = this.GetQuotedName(token.Symbol, token.Type);
}
else if (token.Type == TokenType.Condition ||
token.Type == TokenType.OrderBy ||
token.Type == TokenType.GroupBy
)
{
if (token.Tokens.Count == 0)
{
token.Symbol = this.GetQuotedString(token.Symbol);
}
}
else if (token.Type == TokenType.Alias)
{
if (token.Symbol != null && !token.Symbol.Contains(" "))
{
token.Symbol = token.Symbol.Trim(this.TrimChars);
}
}
#region Replace parameter and variable name
if (token.Type != TokenType.ParameterName && token.Type != TokenType.VariableName)
{
if (this.ReplacedVariables.ContainsKey(token.Symbol))
{
token.Symbol = this.ReplacedVariables[token.Symbol];
}
else if (this.ReplacedVariables.Any(item => token.Symbol.Contains(item.Key)))
{
foreach (var kp in this.ReplacedVariables)
{
string prefix = "";
foreach (var c in kp.Key)
{
if (!Regex.IsMatch(c.ToString(), "[_a-zA-Z]"))
{
prefix += c;
}
else
{
break;
}
}
string excludePattern = $@"[`""\[]\b({kp.Key})\b[`""\]]";
string pattern = "";
if (prefix.Length == 0)
{
pattern = $@"\b({kp.Key})\b";
}
else
{
pattern = $@"([{prefix}]\b({kp.Key.Substring(prefix.Length)})\b)";
}
if (!Regex.IsMatch(token.Symbol, excludePattern))
{
foreach (Match match in Regex.Matches(token.Symbol, pattern))
{
if (kp.Value.StartsWith("@") && token.Symbol.Contains(kp.Value))
{
continue;
}
token.Symbol = Regex.Replace(token.Symbol, pattern, kp.Value);
}
}
}
}
}
#endregion
}
#region Nested token handle
if (this.nameWithQuotation)
{
var nestedTokens = tokens.Where(item => this.IsNestedToken(item));
foreach (TokenInfo nestedToken in nestedTokens)
{
List <string> replacedSymbols = new List <string>();
var childTokens = this.GetNestedTokenChildren(nestedToken);
foreach (var token in childTokens)
{
if (token.Symbol == null)
{
continue;
}
string[] items = token.Symbol.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
foreach (string item in items)
{
string trimedItem = item.Trim(this.TrimChars);
if (aliases.Contains(trimedItem))
{
continue;
}
if (nameRegex.IsMatch(trimedItem))
{
Regex doubleQuotationRegex = new Regex($@"[""]\b({trimedItem})\b[""]");
Regex matchRegex = new Regex($@"[{this.SourceInterpreter.QuotationLeftChar}]?\b({trimedItem})\b[{this.SourceInterpreter.QuotationRightChar}]?");
string quotedValue = $"{this.TargetInterpreter.QuotationLeftChar}{trimedItem}{this.TargetInterpreter.QuotationRightChar}";
if (!nestedToken.Symbol.Contains(quotedValue))
{
bool doubleQuotationMatched = doubleQuotationRegex.IsMatch(nestedToken.Symbol);
if (doubleQuotationMatched)
{
nestedToken.Symbol = doubleQuotationRegex.Replace(nestedToken.Symbol, trimedItem);
}
bool matched = matchRegex.IsMatch(nestedToken.Symbol);
if (matched)
{
nestedToken.Symbol = matchRegex.Replace(nestedToken.Symbol, quotedValue);
replacedSymbols.Add(token.Symbol);
}
}
}
}
}
}
}
#endregion
this.Script.Owner = null;
this.Script.Name.Symbol = this.TargetInterpreter.GetQuotedString(this.DbObject.Name);
}
public static List <SqlWord> FindWords(DatabaseType databaseType, string search, SqlWordTokenType tokenType = SqlWordTokenType.None, string parentName = null)
{
List <SqlWord> words = new List <SqlWord>();
//if (IsTypeMatched(tokenType, SqlWordTokenType.Keyword))
//{
// var keywords = KeywordManager.GetKeywords(databaseType).Where(item => Contains(item, search));
// words.AddRange(keywords.Select(item => new SqlWord() { Type = SqlWordTokenType.Keyword, Text = item }));
//}
if (IsTypeMatched(tokenType, SqlWordTokenType.BuiltinFunction))
{
var builtinFunctions = FunctionManager.GetFunctionSpecifications(databaseType).Where(item => ContainsWithNull(item.Name, search));
words.AddRange(builtinFunctions.Select(item => new SqlWord()
{
Type = SqlWordTokenType.BuiltinFunction, Text = item.Name, Source = item
}));
}
SchemaInfo schemaInfo = DataStore.GetSchemaInfo(databaseType);
if (schemaInfo != null)
{
if (IsTypeMatched(tokenType, SqlWordTokenType.Owner))
{
var owners = schemaInfo.Tables.Where(item => ContainsWithNull(item.Owner, search)).Select(item => item.Owner).Distinct();
words.AddRange(owners.Select(item => new SqlWord()
{
Type = SqlWordTokenType.Owner, Text = item, Source = item
}));
}
FilterDbObjects(words, schemaInfo.Functions, SqlWordTokenType.Function, tokenType, search, parentName);
FilterDbObjects(words, schemaInfo.Tables, SqlWordTokenType.Table, tokenType, search, parentName);
FilterDbObjects(words, schemaInfo.Views, SqlWordTokenType.View, tokenType, search, parentName);
if (tokenType == SqlWordTokenType.TableColumn)
{
IEnumerable <TableColumn> columns = schemaInfo.TableColumns;
if (!string.IsNullOrEmpty(parentName))
{
columns = schemaInfo.TableColumns.Where(item => item.TableName.ToUpper() == parentName.ToUpper());
}
if (!string.IsNullOrEmpty(search))
{
columns = columns.Where(item => ContainsWithNull(item.Name, search));
}
words.AddRange(columns.Select(item => new SqlWord()
{
Type = SqlWordTokenType.TableColumn, Text = item.Name, Source = item
}));
}
}
return(words);
}
protected string HandleDefinition(string definition, List <TSQLToken> tokens, out bool changed)
{
this.sourceFuncSpecs = FunctionManager.GetFunctionSpecifications(this.sourceDbInterpreter.DatabaseType);
this.targetFuncSpecs = FunctionManager.GetFunctionSpecifications(this.targetDbInterpreter.DatabaseType);
changed = false;
string newDefinition = definition;
foreach (TSQLToken token in tokens)
{
string text = token.Text;
string functionExpression = null;
switch (token.Type)
{
case TSQLTokenType.SystemIdentifier:
functionExpression = this.GetFunctionExpression(token, definition);
break;
case TSQLTokenType.Identifier:
switch (text.ToUpper())
{
case "CAST":
case "TO_NUMBER":
functionExpression = this.GetFunctionExpression(token, definition);
break;
}
break;
case TSQLTokenType.Keyword:
break;
}
if (!string.IsNullOrEmpty(functionExpression))
{
bool useBrackets = false;
MappingFunctionInfo targetFunctionInfo = this.GetMappingFunctionInfo(text, out useBrackets);
FunctionFomular fomular = new FunctionFomular(functionExpression);
Dictionary <string, string> dictDataType = null;
string newExpression = this.ParseFomular(this.sourceFuncSpecs, this.targetFuncSpecs, fomular, targetFunctionInfo, out dictDataType);
if (newExpression != fomular.Expression)
{
newDefinition = this.ReplaceValue(newDefinition, fomular.Expression, newExpression);
changed = true;
}
if (dictDataType != null)
{
this.convertedDataTypes.AddRange(dictDataType.Values);
}
if (!string.IsNullOrEmpty(targetFunctionInfo.Args) && changed)
{
if (!this.convertedFunctions.Contains(targetFunctionInfo.Name))
{
this.convertedFunctions.Add(targetFunctionInfo.Name);
}
}
}
}
return(newDefinition);
}
