public void ApplyParametersComplexTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.SQL14); Assert.IsTrue(sqlStatement.Parameters.Count == 1); string appliedParameterSql = sqlStatement.ApplyParameters(); Assert.IsFalse(appliedParameterSql.Contains("@")); string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 3454); sqlStatement = nhibernateFormatter.TryParsSql(Properties.SecureResources.SQL7); appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsFalse(appliedParameterSql.Contains("@")); sqlStatement = nhibernateFormatter.TryParsSql(Properties.SecureResources.SQL8); appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsFalse(appliedParameterSql.Contains("@")); } catch (Exception exception) { Assert.IsNotInstanceOfType(exception, typeof(Exception)); } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public void ParseTest() { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.SQLInsert1); Assert.IsTrue(sqlStatement.Parameters.Count == 4); string appliedParameterSql = sqlStatement.ApplyParameters(); Assert.IsFalse(appliedParameterSql.Contains("@")); string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 161); }
public void ApplyParametersSqlColesceSelectTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:" }; sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.ColesceSelectSql); Assert.IsTrue(sqlStatement.Parameters.Count == 3); string appliedParameterSql = sqlStatement.ApplyParameters(); Assert.IsFalse(appliedParameterSql.Contains("@")); string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 371); } catch (Exception exception) { Assert.IsNotInstanceOfType(exception, typeof(Exception)); } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
static void fileMonitor_Changed(object sender, FileSystemEventArgs e) { fileMonitor.EnableRaisingEvents = false; StringBuilder taskMessage = new StringBuilder(); try { System.Console.WriteLine(); System.Console.WriteLine("Formatting started. Please wait until the task completes ...\n"); taskMessage.AppendLine("Formatting completed...\n"); taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("Attempted to write the fomratted SQL to:\n{0}", Path.Combine(consoleOptions.WorkingDirectory, outputFileName))); // create a new output file for each monitored session setupFiles(consoleOptions.WorkingDirectory, true, true); if (e.ChangeType == WatcherChangeTypes.Changed) { NHibernateSqlOutputFormatter nhibernateFormatter; taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("Mode set to <{0}>", consoleOptions.Mode)); switch (consoleOptions.Mode) { case "1": // < V13 nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:", Environment.NewLine }; break; case "2": // V13 nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:", "\'w3wp.exe\'", Environment.NewLine }; nhibernateFormatter.IgnoreFilters = new string[] { "NHibernate.Hql.Ast.ANTLR.HqlSqlWalker", "NHibernate.Hql.Ast.ANTLR.HqlSqlGenerator", "NHibernate.AdoNet.AbstractBatcher", "NHibernate.Loader.Loader" }; break; case "3": // LinqPad nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "-- EndRegion" }; break; case "4": // Members V13 nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:", "\'w3wp.exe\'" }; break; default: nhibernateFormatter = new NHibernateSqlOutputFormatter(); break; } bool containErrors = false; string appliedFormattedSql = string.Empty; IList<ISqlStatement> sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(File.ReadAllText(Path.Combine(consoleOptions.WorkingDirectory, inputFileName))); foreach (var sqlStatement in sqlStatements) { appliedFormattedSql = sqlStatement.ApplySuggestedFormat(); if ((sqlStatement is SqlUpdateStatement || sqlStatement is SqlInsertStatement) && consoleOptions.SafeMode.ToLower() == "on") { appliedFormattedSql = appliedFormattedSql.InsertBetween("/*", "*/"); } // Log any handled exceptions (these are exceptions that dont break the iteration of creating SQL statements). if (sqlStatement.SqlStatementParsingException != null) { containErrors = true; logErrorMessage(sqlStatement.SqlStatementParsingException, (SqlSelectStatement)sqlStatement); } else using (StreamWriter streamWriter = outputFile.AppendText()) { streamWriter.WriteLine(appliedFormattedSql); streamWriter.WriteLine(""); } } if (containErrors) { taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("Errors detected and logged to:\n{0}", Path.Combine(consoleOptions.WorkingDirectory, errorFileName))); } taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("File monitoring complete and logged to:{0}", outputFile.FullName)); } } catch (ParameterParsingException exception) { logErrorMessage(getLogErrorMessage(exception.Message, exception.ActualSql, exception.ParameterSection)); taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("Critical errors that stopped the application detected and logged to:\n{0}", Path.Combine(consoleOptions.WorkingDirectory, errorFileName))); } catch (Exception exception) { logErrorMessage(getLogErrorMessage(exception.Message, string.Empty, string.Empty)); taskMessage.AppendLine(); taskMessage.AppendLine(string.Format("Critical errors that stopped the application detected and logged to:\n{0}", Path.Combine(consoleOptions.WorkingDirectory, errorFileName))); taskMessage.AppendLine(); taskMessage.AppendLine("Errors may occur if you are attempting to parse a file in the incorrect mode."); } finally { // hack for file system watcher firing unnecessary events fileMonitor.EnableRaisingEvents = true; writeMessage(taskMessage.ToString()); } }
// ( get the sql select part ( unsupported currently UNIONS or nested SQL Statements such as a Union (does this even ever happen in NHibernate? ) ) private void SetSelectParts() { List<string> words = new List<string>(); StringBuilder builder = new StringBuilder(); try { for (int ix = 0; ix < Sql.Length; ix++) { var current = Sql[ix]; var previous = ix > 0 ? Sql[ix - 1] : ' '; var next = ix + 1 < Sql.Length ? Sql[ix + 1] : ' '; builder.Append(current); if (current == ' ') { var cleaned = builder.ToString().SplitAndKeep(new string[] {"(", ")", "[", "]" }); if (cleaned.Length > 0) { for (int iy = 0; iy < cleaned.Length; iy++) { words.Add(cleaned[iy].Trim().ToLower()); } } else { words.Add(builder.ToString().Trim().ToLower()); } builder = new StringBuilder(); } if (words.Count > 0 && words[words.Count - 1] == "from" && words.Count(p => p == "(") == words.Count(p => p == ")")) { if (Sql.Length > (ix + "select".Length)) { if (char.IsLetter(next) || Sql.Substring(ix + 1, "select".Length).ToLower() == "select") { _lastFromIndex = ix - ("from".Length + 1); break; } } } } int length = _lastFromIndex <= 0 ? "select".Length + 1 : _lastFromIndex - ("select".Length + 1); string select = Sql.Substring("select".Length + 1, length).Trim(); // ( attempt to get the inline select statement ( this is select statements used as column names ) ) IList<ISqlStatement> sqlSelectStatements = new NHibernateSqlOutputFormatter().GetSqlFromInlineSelect(select); // ( set the select part of current sql statement and all sql statement column names ) SelectPart = new SqlSelectPart() { Value = select, SqlStatements = sqlSelectStatements }; } catch { throw; } }
// ( get the sql from part from the last index of the from clause found ) private void SetFromParts() { try { var indexOfLastWhere = 0; var fromPart = string.Empty; int fromindex = Regex.Matches(Sql.Substring(_lastFromIndex), @"\bfrom\b", RegexOptions.IgnoreCase).Count; if (fromindex <= 0) return; var fromStatement = Sql.Substring(_lastFromIndex, Sql.Length - _lastFromIndex); var whereMatches = Regex.Matches(fromStatement, "where", RegexOptions.IgnoreCase); // ( no from clause ) if (whereMatches.Count == 0) { fromPart = Sql.Substring(_lastFromIndex + "from".Length + 1, fromStatement.Length - ("from".Length + 1)); } // ( simple from clause ) else { indexOfLastWhere = whereMatches[whereMatches.Count - 1].Index + (Sql.Length - fromStatement.Length); fromPart = Sql.Substring(_lastFromIndex + "from".Length + 1, (indexOfLastWhere - _lastFromIndex) - ("from".Length + 1)).Trim(); } // ( parameter still exists ) if (fromPart.Count(e => e == ';') > 1) { fromPart = fromPart.Substring(0, fromPart.IndexOf(';') + 1); } // ( attempt to get the inline select statement ( this is select statements used as table names ) ) IList<ISqlStatement> sqlSelectStatements = new NHibernateSqlOutputFormatter().GetSqlFromInlineSelect(fromPart); // ( set the from part of current sql statement and all sql statement table names ) FromPart = new SqlFromPart() { Value = fromPart, SqlStatements = sqlSelectStatements }; // ( set the last index of the where clause found ) _lastWhereIndex = indexOfLastWhere; } catch { throw; } }
public void ApplySuggestedFormatTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:" }; IList<ISqlStatement> sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL1); var sqlStatement = sqlStatements[0]; var sqlSelectStatement = sqlStatement as SqlSelectStatement; string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 3454); Assert.IsTrue(sqlStatement.ColumnNames.Count == 48); Assert.IsTrue(sqlStatement.Parameters.Count == 1); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 3061); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 6); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 30); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 29); sqlStatement = sqlStatements[1]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 454); Assert.IsTrue(sqlStatement.ColumnNames.Count == 11); Assert.IsTrue(sqlStatement.Parameters.Count == 1); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 361); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 23); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 21); sqlStatement = sqlStatements[2]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 2136); Assert.IsTrue(sqlStatement.ColumnNames.Count == 29); Assert.IsTrue(sqlStatement.Parameters.Count == 4); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 1121); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 595); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 121); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL3); sqlStatement = sqlStatements[3]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 366); Assert.IsTrue(sqlStatement.ColumnNames.Count == 4); Assert.IsTrue(sqlStatement.Parameters.Count == 2); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 93); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 32); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 48); sqlStatement = sqlStatements[5]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 378); Assert.IsTrue(sqlStatement.ColumnNames.Count == 4); Assert.IsTrue(sqlStatement.Parameters.Count == 2); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 93); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 32); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 48); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL5); sqlStatement = sqlStatements[7]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 1634); Assert.IsTrue(sqlStatement.ColumnNames.Count == 8); Assert.IsTrue(sqlStatement.Parameters.Count == 0); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 1016); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 5); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 325); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 65); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL6); sqlStatement = sqlStatements[8]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 1811); Assert.IsTrue(sqlStatement.ColumnNames.Count == 27); Assert.IsTrue(sqlStatement.Parameters.Count == 6); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 931); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 270); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 187); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL7); sqlStatement = sqlStatements[9]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 885); Assert.IsTrue(sqlStatement.ColumnNames.Count == 2); Assert.IsTrue(sqlStatement.Parameters.Count == 56); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 45); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 33); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 426); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL8); sqlStatement = sqlStatements[10]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 282); Assert.IsTrue(sqlStatement.ColumnNames.Count == 1); Assert.IsTrue(sqlStatement.Parameters.Count == 1); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 26); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 33); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 46); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL9); sqlStatement = sqlStatements[11]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 735); Assert.IsTrue(sqlStatement.ColumnNames.Count == 17); Assert.IsTrue(sqlStatement.Parameters.Count == 1); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 588); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 27); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 37); sqlStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL10); sqlStatement = sqlStatements[12]; sqlSelectStatement = sqlStatement as SqlSelectStatement; appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 156); Assert.IsTrue(sqlStatement.ColumnNames.Count == 1); Assert.IsTrue(sqlStatement.Parameters.Count == 1); Assert.IsTrue(sqlSelectStatement.SelectPart.Value.Length == 27); Assert.IsTrue(sqlSelectStatement.SelectPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.FromPart.Value.Length == 27); Assert.IsTrue(sqlSelectStatement.FromPart.SqlStatements.Count == 0); Assert.IsTrue(sqlSelectStatement.WherePart.Value.Length == 43); IList<ISqlStatement> sqlSelectStatements = nhibernateFormatter.GetSqlFromDebugOutput(Properties.SecureResources.SQL3); foreach (var statement in sqlSelectStatements) { string sql = statement.ApplySuggestedFormat(); #if DEBUG Debug.WriteLine(sql); #endif } } catch { throw; } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public void ApplySuggestedFormatNoColumnTableNameAliasTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { Environment.NewLine }; sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.NoColumnOrTableNameAliases); Assert.IsTrue(sqlStatement.Parameters.Count == 1); string appliedParameterSql = sqlStatement.ApplyParameters(); Assert.IsFalse(appliedParameterSql.Contains("@")); string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 195); } catch { throw; } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public void ApplySuggestedFormatAliasQuotedValuesTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { Environment.NewLine }; sqlStatement = nhibernateFormatter.TryParsSql(SecureResources.ColumnNameAliasQuotedValues); Assert.IsTrue(sqlStatement.Parameters.Count == 2); string appliedParameterSql = sqlStatement.ApplyParameters(); Assert.IsFalse(appliedParameterSql.Contains("@")); string appliedFormatSql = sqlStatement.ApplySuggestedFormat(); Assert.IsTrue(appliedFormatSql.Length == 1375); } catch (Exception exception) { Assert.IsNotInstanceOfType(exception, typeof(Exception)); } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public void SqlWhereExistsTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:" }; IList<ISqlStatement> sqlSelectStatements = nhibernateFormatter.GetSqlFromDebugOutput(SecureResources.SQLWhereExists); Assert.IsTrue(sqlSelectStatements.Count == 4); foreach (var sqlStatement in sqlSelectStatements) { string sql = sqlStatement.ApplySuggestedFormat(); #if DEBUG Debug.WriteLine(sql); #endif } } catch { throw; } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public void ApplySuggestedFormatSqlStartIdentifierTest() { try { NHibernateSqlOutputFormatter nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { "NHibernate.SQL:", Environment.NewLine }; IList<ISqlStatement> sqlSelectStatements = nhibernateFormatter.GetSqlFromDebugOutput(SecureResources.SQL3); Assert.IsTrue(sqlSelectStatements.Count == 4); foreach (var sqlStatement in sqlSelectStatements) { string sql = sqlStatement.ApplySuggestedFormat(); #if DEBUG Debug.WriteLine(sql); #endif } } catch (Exception exception) { Assert.IsNotInstanceOfType(exception, typeof(Exception)); } finally { SecureResources.ResourceManager.ReleaseAllResources(); } }
public NHibernateSqlOutputRedirector() { nhibernateFormatter = new NHibernateSqlOutputFormatter(); nhibernateFormatter.SqlIdentifiers = new string[] { ";" }; }
public void Init() { context = new NHibernateSqlOutputFormatter(); context.SqlIdentifiers = new string[] { "NHibernate.SQL:" }; }