/// <summary>
/// Format sql files with TSqlFormatter.
/// </summary>
/// <param name="config">The configuration.</param>
public void FormatSqlFiles(Config config)
{
Trace.TraceInformation("FormatSqlFiles - BEGIN");
var formatOptions = new TSqlStandardFormatterOptions
{
NewClauseLineBreaks = config.SqlFormatterOptions.NewClauseLineBreaks,
ExpandBetweenConditions = config.SqlFormatterOptions.ExpandBetweenConditions,
ExpandBooleanExpressions = config.SqlFormatterOptions.ExpandBooleanExpressions,
ExpandCaseStatements = config.SqlFormatterOptions.ExpandCaseStatements,
ExpandCommaLists = config.SqlFormatterOptions.ExpandCommaLists,
ExpandInLists = config.SqlFormatterOptions.ExpandInLists,
IndentString = config.SqlFormatterOptions.IndentString,
MaxLineWidth = config.SqlFormatterOptions.MaxLineWidth,
SpaceAfterExpandedComma = config.SqlFormatterOptions.SpaceAfterExpandedComma,
SpacesPerTab = config.SqlFormatterOptions.SpacesPerTab,
KeywordStandardization = config.SqlFormatterOptions.KeywordStandardization,
NewStatementLineBreaks = config.SqlFormatterOptions.NewStatementLineBreaks,
TrailingCommas = config.SqlFormatterOptions.TrailingCommas,
UppercaseKeywords = config.SqlFormatterOptions.UppercaseKeywords
};
var sqlFiles = FindSqlFiles();
foreach (var sqlFilePath in sqlFiles)
{
FileUtils.FormatSqlFile(sqlFilePath, formatOptions);
}
Trace.TraceInformation("FormatSqlFiles - END");
}
private static string FormatSql(string sql)
{
Console.WriteLine($"[Formatting] resulting SQL code.");
var options = new TSqlStandardFormatterOptions
{
KeywordStandardization = true,
IndentString = "\t",
SpacesPerTab = 4,
MaxLineWidth = 999,
NewStatementLineBreaks = 2,
NewClauseLineBreaks = 1,
TrailingCommas = false,
SpaceAfterExpandedComma = false,
ExpandBetweenConditions = true,
ExpandBooleanExpressions = true,
ExpandCaseStatements = true,
ExpandCommaLists = true,
BreakJoinOnSections = true,
UppercaseKeywords = true,
ExpandInLists = true
};
var parsingError = false;
var formatter = new TSqlStandardFormatter(options);
var formattingManager = new PoorMansTSqlFormatterLib.SqlFormattingManager(formatter);
var formattedOutput = formattingManager.Format(sql, ref parsingError);
return(formattedOutput);
}
public void Options_Deserialize_OverrideAll()
{
var options = new TSqlStandardFormatterOptions(
"IndentString= "
+ ",SpacesPerTab=2"
+ ",MaxLineWidth=100"
+ ",ExpandCommaLists=false"
+ ",TrailingCommas=True"
+ ",SpaceAfterExpandedComma=true"
+ ",ExpandBooleanExpressions=False"
+ ",ExpandCaseStatements=false"
+ ",ExpandBetweenConditions=false"
+ ",BreakJoinOnSections=true"
+ ",UppercaseKeywords=false"
+ ",HTMLColoring=true"
+ ",KeywordStandardization=true"
);
Assert.AreEqual(" ", options.IndentString);
Assert.AreEqual(2, options.SpacesPerTab);
Assert.AreEqual(100, options.MaxLineWidth);
Assert.IsFalse(options.ExpandCommaLists);
Assert.IsTrue(options.TrailingCommas);
Assert.IsTrue(options.SpaceAfterExpandedComma);
Assert.IsFalse(options.ExpandBooleanExpressions);
Assert.IsFalse(options.ExpandCaseStatements);
Assert.IsFalse(options.ExpandBetweenConditions);
Assert.IsTrue(options.BreakJoinOnSections);
Assert.IsFalse(options.UppercaseKeywords);
Assert.IsTrue(options.HTMLColoring);
Assert.IsTrue(options.KeywordStandardization);
}
internal static void FormatSql()
{
Gateway.SetCurrentLanguage(LangType.L_SQL);
var text = GetCurrentText();
var options = new TSqlStandardFormatterOptions
{
BreakJoinOnSections = _settings.BreakJoinOnSections,
ExpandBetweenConditions = _settings.ExpandBetweenConditions,
ExpandBooleanExpressions = _settings.ExpandBooleanExpressions,
ExpandCaseStatements = _settings.ExpandCaseStatements,
ExpandCommaLists = _settings.ExpandCommaList,
ExpandInLists = false,
HTMLColoring = false,
IndentString = _settings.IndentString,
KeywordStandardization = _settings.KeywordStandardization,
MaxLineWidth = _settings.MaxLineWidth,
NewClauseLineBreaks = 0,
NewStatementLineBreaks = 0,
SpaceAfterExpandedComma = _settings.SpaceAfterExpandedComma,
SpacesPerTab = _settings.SpacesPerTab,
TrailingCommas = _settings.TrailingCommas,
UppercaseKeywords = _settings.UppercaseKeywords
};
var innerFormatter = new TSqlStandardFormatter(options);
var tokenizer = new TSqlStandardTokenizer();
var parser = new TSqlStandardParser();
var parsedSql = parser.ParseSQL(tokenizer.TokenizeSQL(text));
Editor.SetText(innerFormatter.FormatSQLTree(parsedSql));
}
/// <summary>
/// Formats the SQL file.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="formatOptions">The format options.</param>
public static void FormatSqlFile(string fileName, TSqlStandardFormatterOptions formatOptions)
{
if (!File.Exists(fileName))
{
_log.Error($"Die Datei '{fileName}' existiert nicht.");
}
else
{
var standardFormatter = new TSqlStandardFormatter(formatOptions);
var sqlFormatManager = new SqlFormattingManager
{
Formatter = standardFormatter
};
_log.Info($"Formatiere SQL der Datei '{fileName}' und speichere als {_destEncoding.GetInfoString()}.");
var sql = File.ReadAllText(fileName);
var errorsEncountered = false;
sql = sqlFormatManager.Format(sql, ref errorsEncountered);
if (errorsEncountered)
{
_log.Warn("Es sind Fehler während der Formatierung aufgetreten!");
if (sql.StartsWith(FormatterWarningMsg))
{
sql = sql.Replace(FormatterWarningMsg, "");
}
}
File.WriteAllText(fileName, sql, _destEncoding);
}
}
private TSqlStandardFormatter GetFormatter(string configString)
{
TSqlStandardFormatter outFormatter;
if (!_formatters.TryGetValue(configString, out outFormatter))
{
var options = new TSqlStandardFormatterOptions(configString);
outFormatter = new TSqlStandardFormatter(options);
_formatters.Add(configString, outFormatter);
}
return(outFormatter);
}
private TSqlStandardFormatter GetFormatter(string configString)
{
TSqlStandardFormatter outFormatter;
if (!_formatters.TryGetValue(configString, out outFormatter))
{
//defaults are as per the object, except disabling colorized/htmlified output
var options = new TSqlStandardFormatterOptions(configString);
options.HTMLColoring = false;
outFormatter = new TSqlStandardFormatter(options);
}
return(outFormatter);
}
public void Options_New_DefaultsToOriginalOptions()
{
var options = new TSqlStandardFormatterOptions();
Assert.AreEqual("\t", options.IndentString);
Assert.AreEqual(4, options.SpacesPerTab);
Assert.AreEqual(999, options.MaxLineWidth);
Assert.IsTrue(options.ExpandCommaLists);
Assert.IsFalse(options.TrailingCommas);
Assert.IsFalse(options.SpaceAfterExpandedComma);
Assert.IsTrue(options.ExpandBooleanExpressions);
Assert.IsTrue(options.ExpandCaseStatements);
Assert.IsTrue(options.ExpandBetweenConditions);
Assert.IsFalse(options.BreakJoinOnSections);
Assert.IsTrue(options.UppercaseKeywords);
Assert.IsFalse(options.HTMLColoring);
Assert.IsFalse(options.KeywordStandardization);
}
public void Options_Deserialize_RoundTrip()
{
var expected = new TSqlStandardFormatterOptions
{
IndentString = " ",
SpacesPerTab = 2,
MaxLineWidth = 100,
ExpandCommaLists = false,
TrailingCommas = true,
SpaceAfterExpandedComma = true,
ExpandBooleanExpressions = false,
ExpandCaseStatements = false,
ExpandBetweenConditions = false,
BreakJoinOnSections = true,
UppercaseKeywords = false,
HTMLColoring = true,
KeywordStandardization = true
};
var serializedString = expected.ToSerializedString();
var actual = new TSqlStandardFormatterOptions(serializedString);
Assert.AreEqual(expected.IndentString, actual.IndentString);
Assert.AreEqual(expected.SpacesPerTab, actual.SpacesPerTab);
Assert.AreEqual(expected.MaxLineWidth, actual.MaxLineWidth);
Assert.AreEqual(expected.ExpandCommaLists, actual.ExpandCommaLists);
Assert.AreEqual(expected.TrailingCommas, actual.TrailingCommas);
Assert.AreEqual(expected.SpaceAfterExpandedComma, actual.SpaceAfterExpandedComma);
Assert.AreEqual(expected.ExpandBooleanExpressions, actual.ExpandBooleanExpressions);
Assert.AreEqual(expected.ExpandCaseStatements, actual.ExpandCaseStatements);
Assert.AreEqual(expected.ExpandBetweenConditions, actual.ExpandBetweenConditions);
Assert.AreEqual(expected.BreakJoinOnSections, actual.BreakJoinOnSections);
Assert.AreEqual(expected.UppercaseKeywords, actual.UppercaseKeywords);
Assert.AreEqual(expected.HTMLColoring, actual.HTMLColoring);
Assert.AreEqual(expected.KeywordStandardization, actual.KeywordStandardization);
}
private TSqlStandardFormatter GetFormatter(string configString)
{
TSqlStandardFormatter outFormatter;
if (!_formatters.TryGetValue(configString, out outFormatter))
{
//var options = new TSqlStandardFormatterOptions
//{
// KeywordStandardization = true,
// IndentString = "\t",
// SpacesPerTab = 4,
// MaxLineWidth = 999,
// NewStatementLineBreaks = 2,
// NewClauseLineBreaks = 1,
// TrailingCommas = false,
// SpaceAfterExpandedComma = false,
// ExpandBetweenConditions = true,
// ExpandBooleanExpressions = true,
// ExpandCaseStatements = true,
// ExpandCommaLists = true,
// BreakJoinOnSections = false,
// UppercaseKeywords = true,
// ExpandInLists = true
//};
var options = new TSqlStandardFormatterOptions(configString);
options.IndentString = new String(' ', 2);
options.TrailingCommas = true;
options.ExpandInLists = true;
//options.NewStatementLineBreaks = 2;
outFormatter = new TSqlStandardFormatter(options);
_formatters.Add(configString, outFormatter);
}
return(outFormatter);
}
public void Options_Deserialize_TrailingCommasTrue()
{
var options = new TSqlStandardFormatterOptions("TrailingCommas=True");
Assert.IsTrue(options.TrailingCommas);
}
