static SqlTextNormalizer()
{
Sweeper = new Thread(() =>
{
try
{
while (true)
{
var toDelete = cachedQueries.Where(t => t.Value.ReferenceCount < 10).ToList();
foreach (var el in toDelete)
{
NormalizedSqlText nst = null;
cachedQueries.TryRemove(el.Key, out nst);
}
Thread.Sleep(30000);
}
}
catch (Exception e)
{
logger.Error(e.Message);
logger.Error(e.StackTrace);
}
});
Sweeper.IsBackground = true;
Sweeper.Name = "SqlTextNormalizer.CacheSweeper";
Sweeper.Start();
}
public NormalizedSqlText NormalizeSqlText(string sql, int spid)
{
try
{
NormalizedSqlText result = null;
int hashCode = sql.GetHashCode();
if (cachedQueries.TryGetValue(hashCode, out result))
{
if (result != null && result.OriginalText == sql)
{
result.ReferenceCount++;
return(result);
}
}
result = NormalizeSqlText(sql, spid, true);
logger.Trace("NormalizeSqlText:[{0}]: {1}", spid, sql);
if (result != null)
{
logger.Trace("NormalizeSqlText:[{0}]: {1}", spid, result.NormalizedText);
result.ReferenceCount = 1;
cachedQueries.TryAdd(hashCode, result);
}
return(result);
}
catch (Exception)
{
throw;
}
}
public NormalizedSqlText NormalizeSqlText(string sql, int spid, bool spreadCsv)
{
NormalizedSqlText result = new NormalizedSqlText();
result.OriginalText = sql;
result.NormalizedText = sql;
if (sql == null)
{
result.OriginalText = "";
result.NormalizedText = "";
result.Statement = "";
return(result);
}
sql = sql.Trim();
if (TruncateTo1024 && sql.Length > 1024000)
{
result.Statement = "{SQL>1MB}";
result.NormalizedText = "{SQL>1MB}";
return(result);
}
bool flag1 = false;
bool flag2 = false;
int num = 0;
if ((sql == "sp_reset_connection") || (sql == "exec sp_reset_connection"))
{
return(null);
}
sql = FixComments(sql);
sql = _spaces.Replace(sql, " ").ToUpper(CultureInfo.InvariantCulture);
sql = _doubleApostrophe.Replace(sql, "{STR}");
Match matchPrepExecRpc = _prepExecRpc.Match(sql);
if (matchPrepExecRpc.Success)
{
sql = matchPrepExecRpc.Groups["statement"].ToString();
result.Statement = sql;
result.NormalizedText = sql;
}
Match matchPrepareSql = _prepareSql.Match(sql);
if (matchPrepareSql.Success)
{
if (matchPrepareSql.Groups["preptype"].ToString().ToLower() == "sp_prepare")
{
flag2 = true;
}
//num = !(match3.Groups["stmtnum"].ToString() == "NULL") ? Convert.ToInt32(match3.Groups["stmtnum"].ToString()) : 0;
sql = matchPrepareSql.Groups["remaining"].ToString();
Match matchPreppedSqlStatement = _preppedSqlStatement.Match(sql);
if (matchPreppedSqlStatement.Success)
{
sql = matchPreppedSqlStatement.Groups["statement"].ToString();
sql = _doubleApostrophe.Replace(sql, "'${string}'");
result.Statement = sql;
result.NormalizedText = sql;
}
flag1 = true;
}
Match matchExecPrepped = _execPrepped.Match(sql);
if (matchExecPrepped.Success)
{
num = Convert.ToInt32(matchExecPrepped.Groups["stmtnum"].ToString());
if (prepSql.ContainsKey((object)(spid.ToString() + "_" + num.ToString())))
{
result.NormalizedText = TruncateSql("{PREPARED} " + prepSql[(object)(spid.ToString() + "_" + num.ToString())].ToString());
return(result);
}
}
Match matchExecUnprep = _execUnprep.Match(sql);
if (matchExecUnprep.Success)
{
num = Convert.ToInt32(matchExecUnprep.Groups["stmtnum"].ToString());
string str = spid.ToString() + "_" + num.ToString();
if (prepSql.ContainsKey((object)str))
{
sql = prepSql[(object)str].ToString();
prepSql.Remove((object)(spid.ToString() + "_" + num.ToString()));
result.NormalizedText = TruncateSql("{UNPREPARING} " + sql);
return(result);
}
}
Match matchCursor = _cursor.Match(sql);
if (matchCursor.Success)
{
sql = matchCursor.Groups["statement"].ToString();
sql = _doubleApostrophe.Replace(sql, "'${string}'");
result.Statement = sql;
result.NormalizedText = "{CURSOR} " + sql;
}
Match matchCursorPrepexec = _cursorPrepExec.Match(sql);
if (matchCursorPrepexec.Success)
{
sql = matchCursorPrepexec.Groups["statement"].ToString();
sql = _doubleApostrophe.Replace(sql, "'${string}'");
result.Statement = sql;
result.NormalizedText = "{CURSOR} " + sql;
}
Match matchSpExecuteSql = _spExecuteSql.Match(sql);
if (matchSpExecuteSql.Success)
{
sql = matchSpExecuteSql.Groups["statement"].ToString();
result.Statement = sql;
result.NormalizedText = sql;
}
Match matchSpExecuteSqlWithStatement = _spExecuteSqlWithStatement.Match(sql);
if (matchSpExecuteSqlWithStatement.Success)
{
sql = matchSpExecuteSqlWithStatement.Groups["statement"].ToString();
result.Statement = sql;
result.NormalizedText = sql;
}
if (!_brackets.Match(sql).Success)
{
Match matchDbAndObjectName = _dbAndObjectName.Match(sql);
if (matchDbAndObjectName.Success)
{
sql = matchDbAndObjectName.Groups["object"].ToString();
}
else
{
Match matchObjectName = _objectName.Match(sql);
if (matchObjectName.Success)
{
sql = matchObjectName.Groups["object"].ToString();
}
}
if (sql == "SP_CURSOR" || sql == "SP_CURSORFETCH" || (sql == "SP_CURSORCLOSE" || sql == "SP_RESET_CONNECTION"))
{
return(null);
}
}
if (sql.Contains("EXEC") && sql.Contains("READONLY"))
{
Match matchTVPExecute = _TVPExecute.Match(sql);
if (matchTVPExecute.Success)
{
result.Statement = sql;
result.NormalizedText = "EXECUTE " + matchTVPExecute.Groups["object"].ToString();
}
}
result.NormalizedText = _paramNameValue.Replace(result.NormalizedText, "@${paramname} = {STR}");
result.NormalizedText = _emptyString.Replace(result.NormalizedText, "{STR}");
result.NormalizedText = _stringConstant.Replace(result.NormalizedText, "{STR}");
result.NormalizedText = _unicodeConstant.Replace(result.NormalizedText, "{NSTR}");
result.NormalizedText = _binaryConstant.Replace(result.NormalizedText, "{BINARY}");
result.NormalizedText = _numericConstant.Replace(result.NormalizedText, "${prefix}{##}");
result.NormalizedText = _inClause.Replace(result.NormalizedText, "{IN}");
if (spreadCsv)
{
result.NormalizedText = _spreadCsv.Replace(result.NormalizedText, ", ");
}
result.NormalizedText = _spaces.Replace(result.NormalizedText, " ");
result.NormalizedText = TruncateSql(result.NormalizedText);
if (flag1 && num != 0)
{
var theKey = (object)(spid.ToString() + "_" + num.ToString());
if (!prepSql.ContainsKey(theKey))
{
prepSql.Add(theKey, sql);
}
else
{
prepSql[theKey] = sql;
}
}
if (flag2)
{
result.NormalizedText = TruncateSql("{PREPARING} " + sql);
return(result);
}
if (flag1 && !flag2)
{
result.NormalizedText = TruncateSql("{PREPARED} " + sql);
return(result);
}
result.NormalizedText = TruncateSql(result.NormalizedText);
return(result);
}
