public static bool In(this TSQLTokenType type, params TSQLTokenType[] types)
{
return(types.Any(t => type == t));
}
private string ParseDefinition(string definition)
{
var tokens = this.ParseTokens(definition);
bool changed = false;
definition = this.HandleDefinition(definition, tokens, out changed);
StringBuilder sb = new StringBuilder();
bool ignore = false;
if (changed)
{
tokens = this.ParseTokens(definition);
}
TSQLTokenType previousType = TSQLTokenType.Whitespace;
string previousText = "";
for (int i = 0; i < tokens.Count; i++)
{
if (ignore)
{
ignore = false;
continue;
}
var token = tokens[i];
var tokenType = token.Type;
string text = token.Text;
switch (tokenType)
{
case TSQLTokenType.Identifier:
if (dataTypes.Contains(text))
{
sb.Append(text);
continue;
}
var nextToken = i + 1 < tokens.Count ? tokens[i + 1] : null;
//Remove owner name
if (nextToken != null && nextToken.Text.Trim() != "(" &&
text.Trim('"') == sourceOwnerName && i + 1 < tokens.Count && tokens[i + 1].Text == "."
)
{
ignore = true;
continue;
}
else if (nextToken != null && nextToken.Text.Trim() == "(") //function handle
{
IEnumerable<FunctionMapping> funcMappings = functionMappings.FirstOrDefault(item => item.Any(t => t.DbType == sourceDbInterpreter.DatabaseType.ToString() && t.Function.Split(',').Any(m => m.ToLower() == text.ToLower())));
if (funcMappings != null)
{
string targetFunction = funcMappings.FirstOrDefault(item => item.DbType == targetDbInterpreter.DatabaseType.ToString())?.Function.Split(',')?.FirstOrDefault();
if (!string.IsNullOrEmpty(targetFunction))
{
sb.Append(targetFunction);
}
}
else
{
sb.Append(text);
}
}
else
{
sb.Append($"{targetDbInterpreter.QuotationLeftChar}{text.Trim('"')}{targetDbInterpreter.QuotationRightChar}");
}
break;
case TSQLTokenType.StringLiteral:
if (previousType != TSQLTokenType.Whitespace && previousText.ToLower() == "as")
{
sb.Append($"{targetDbInterpreter.QuotationLeftChar}{text.Trim('\'', '"')}{targetDbInterpreter.QuotationRightChar}");
}
else
{
sb.Append(text);
}
break;
case TSQLTokenType.SingleLineComment:
case TSQLTokenType.MultilineComment:
continue;
case TSQLTokenType.Keyword:
switch (text.ToUpper())
{
case "AS":
if (targetDbInterpreter is OracleInterpreter)
{
var previousKeyword = (from t in tokens where t.Type == TSQLTokenType.Keyword && t.EndPosition < token.BeginPosition select t).LastOrDefault();
if (previousKeyword != null && previousKeyword.Text.ToUpper() == "FROM")
{
continue;
}
}
break;
}
sb.Append(token.Text);
break;
default:
sb.Append(token.Text);
break;
}
if (!string.IsNullOrWhiteSpace(text))
{
previousText = text;
previousType = tokenType;
}
}
definition = sb.ToString();
#region Handle join cluase for mysql which has no "on", so it needs to make up that.
try
{
if (this.sourceDbInterpreter.GetType() == typeof(MySqlInterpreter))
{
bool hasError = false;
string formattedDefinition = this.FormatSql(definition, out hasError);
if (!hasError)
{
string[] lines = formattedDefinition.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
Regex joinRegex = new Regex("\\b(join)\\b", RegexOptions.IgnoreCase);
Regex onRegex = new Regex("\\b(on)\\b", RegexOptions.IgnoreCase);
Regex wordRegex = new Regex("([a-zA-Z(]+)", RegexOptions.IgnoreCase);
sb = new StringBuilder();
foreach (string line in lines)
{
bool hasChanged = false;
if (joinRegex.IsMatch(line))
{
string leftStr = line.Substring(line.ToLower().LastIndexOf("join") + 4);
if (!onRegex.IsMatch(line) && !wordRegex.IsMatch(leftStr))
{
hasChanged = true;
sb.AppendLine($"{line} ON 1=1 ");
}
}
if (!hasChanged)
{
sb.AppendLine(line);
}
}
definition = sb.ToString();
}
}
}
catch (Exception ex)
{
FeedbackInfo info = new FeedbackInfo() { InfoType = FeedbackInfoType.Error, Message = ExceptionHelper.GetExceptionDetails(ex), Owner = this };
FeedbackHelper.Feedback(info);
}
#endregion
return definition.Trim();
}
public string BuildDefinition(List <TSQL.Tokens.TSQLToken> tokens)
{
StringBuilder sb = new StringBuilder();
this.sourceOwnerName = DbInterpreterHelper.GetOwnerName(sourceDbInterpreter);
int ignoreCount = 0;
TSQLTokenType previousType = TSQLTokenType.Whitespace;
string previousText = "";
for (int i = 0; i < tokens.Count; i++)
{
if (ignoreCount > 0)
{
ignoreCount--;
continue;
}
var token = tokens[i];
var tokenType = token.Type;
string text = token.Text;
switch (tokenType)
{
case TSQLTokenType.Identifier:
var nextToken = i + 1 < tokens.Count ? tokens[i + 1] : null;
if (this.sourceDbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
if ((text == "dbo" || text == "[dbo]") && this.TargetDbOwner?.ToLower() != "dbo")
{
if (nextToken != null && nextToken.Text == ".")
{
ignoreCount++;
}
continue;
}
}
if (convertedDataTypes.Contains(text))
{
sb.Append(text);
continue;
}
//Remove owner name
if (nextToken != null && nextToken.Text.Trim() != "(" &&
text.Trim('"') == sourceOwnerName && i + 1 < tokens.Count && tokens[i + 1].Text == "."
)
{
ignoreCount++;
continue;
}
else if (nextToken != null && nextToken.Text.Trim() == "(") //function handle
{
if (this.convertedFunctions.Contains(text))
{
sb.Append(text);
continue;
}
string textWithBrackets = text.ToLower() + "()";
bool useBrackets = false;
MappingFunctionInfo targetFunctionInfo = this.GetMappingFunctionInfo(text, out useBrackets);
if (targetFunctionInfo.Name.ToLower() != text.ToLower())
{
string targetFunction = targetFunctionInfo.Name;
if (!string.IsNullOrEmpty(targetFunction))
{
sb.Append(targetFunction);
}
if (useBrackets)
{
ignoreCount += 2;
}
}
else
{
if (text.StartsWith(this.sourceDbInterpreter.QuotationLeftChar.ToString()) && text.EndsWith(this.sourceDbInterpreter.QuotationRightChar.ToString()))
{
sb.Append(this.GetQuotedString(text.Trim(this.sourceDbInterpreter.QuotationLeftChar, this.sourceDbInterpreter.QuotationRightChar)));
}
else
{
sb.Append(text);
}
}
}
else
{
sb.Append(this.GetQuotedString(text));
}
break;
case TSQLTokenType.StringLiteral:
if (previousType != TSQLTokenType.Whitespace && previousText.ToLower() == "as")
{
sb.Append(this.GetQuotedString(text));
}
else
{
sb.Append(text);
}
break;
case TSQLTokenType.SingleLineComment:
case TSQLTokenType.MultilineComment:
continue;
case TSQLTokenType.Keyword:
switch (text.ToUpper())
{
case "AS":
if (targetDbInterpreter is OracleInterpreter)
{
var previousKeyword = (from t in tokens where t.Type == TSQLTokenType.Keyword && t.EndPosition < token.BeginPosition select t).LastOrDefault();
if (previousKeyword != null && previousKeyword.Text.ToUpper() == "FROM")
{
continue;
}
}
break;
}
sb.Append(text);
break;
default:
sb.Append(text);
break;
}
if (!string.IsNullOrWhiteSpace(text))
{
previousText = text;
previousType = tokenType;
}
}
return(sb.ToString());
}
