public void StandardFormatReparsingReformatting(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
TSqlStandardFormatter _treeFormatter = GetFormatter("");
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
Node parsed = _parser.ParseSQL(tokenized);
string outputSQL = _treeFormatter.FormatSQLTree(parsed);
var inputToSecondPass = outputSQL;
if (inputToSecondPass.StartsWith(Utils.ERROR_FOUND_WARNING))
{
inputToSecondPass = inputToSecondPass.Replace(Utils.ERROR_FOUND_WARNING, "");
}
ITokenList tokenizedAgain = _tokenizer.TokenizeSQL(inputToSecondPass);
Node parsedAgain = _parser.ParseSQL(tokenizedAgain);
string formattedAgain = _treeFormatter.FormatSQLTree(parsedAgain);
if (!inputSQL.Contains(Utils.REFORMATTING_INCONSISTENCY_WARNING))
{
Assert.AreEqual(outputSQL, formattedAgain, "first-pass formatted vs reformatted");
Utils.StripWhiteSpaceFromSqlTree(parsed);
Utils.StripWhiteSpaceFromSqlTree(parsedAgain);
Assert.AreEqual(parsed.ToXmlDoc().OuterXml.ToUpper(), parsedAgain.ToXmlDoc().OuterXml.ToUpper(), "first parse xml vs reparse xml");
}
}
public void ContentUnchangedByIdentityTokenFormatter(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
string outputSQL = _tokenFormatter.FormatSQLTokens(tokenized);
Assert.AreEqual(inputSQL, outputSQL);
}
public void ContentUnchangedByIdentityTokenFormatter(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
string outputSQL = _tokenFormatter.FormatSQLTokens(tokenized);
if (!inputSQL.Contains(Utils.INVALID_SQL_WARNING))
{
Assert.AreEqual(outputSQL, inputSQL);
}
}
public void CheckThatValidSqlRemainsUnchangedByIdentityTokenFormatter()
{
foreach (string inputSQL in Utils.FolderTextFileIterator(TestDataFolder))
{
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
string outputSQL = _tokenFormatter.FormatSQLTokens(tokenized);
if (!inputSQL.Contains("THIS TEST FILE IS NOT VALID SQL"))
{
Assert.AreEqual <string>(outputSQL, inputSQL, "input and output should be the same, as this is a valid SQL file");
}
}
}
public void CheckThatReformattingOutputSqlYieldsSameSql()
{
foreach (string inputSQL in Utils.FolderTextFileIterator(InputDataFolder))
{
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
XmlDocument parsed = _parser.ParseSQL(tokenized);
string outputSQL = _treeFormatter.FormatSQLTree(parsed);
ITokenList tokenizedAgain = _tokenizer.TokenizeSQL(outputSQL);
XmlDocument parsedAgain = _parser.ParseSQL(tokenizedAgain);
string formattedAgain = _treeFormatter.FormatSQLTree(parsedAgain);
if (!inputSQL.Contains("KNOWN SQL REFORMATTING INCONSISTENCY") && !inputSQL.Contains("THIS TEST FILE IS NOT VALID SQL"))
{
Assert.AreEqual <string>(outputSQL, formattedAgain, "reformatted SQL should be the same as first pass of formatting");
}
}
}
private void TestMarkerPosition(string inputSQLNoLineBreaks, int inputPosition)
{
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQLNoLineBreaks, inputPosition);
Assert.AreEqual(SqlTokenType.OtherNode, tokenized.MarkerToken.Type, "token type");
Assert.AreEqual("from", tokenized.MarkerToken.Value, "token value");
Assert.AreEqual(2, tokenized.MarkerPosition);
}
public void StandardFormatReparsingReformatting(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
TSqlStandardFormatter _treeFormatter = GetFormatter("");
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
XmlDocument parsed = _parser.ParseSQL(tokenized);
string outputSQL = _treeFormatter.FormatSQLTree(parsed);
ITokenList tokenizedAgain = _tokenizer.TokenizeSQL(outputSQL);
XmlDocument parsedAgain = _parser.ParseSQL(tokenizedAgain);
string formattedAgain = _treeFormatter.FormatSQLTree(parsedAgain);
if (!inputSQL.Contains(Utils.REFORMATTING_INCONSISTENCY_WARNING) && !inputSQL.Contains(Utils.INVALID_SQL_WARNING))
{
Assert.AreEqual(outputSQL, formattedAgain, "first-pass formatted vs reformatted");
Utils.StripWhiteSpaceFromSqlTree(parsed);
Utils.StripWhiteSpaceFromSqlTree(parsedAgain);
Assert.AreEqual(parsed.OuterXml.ToUpper(), parsedAgain.OuterXml.ToUpper(), "first parse xml vs reparse xml");
}
}
public void ObfuscatingFormatReformatMatch(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
XmlDocument parsedOriginal = _parser.ParseSQL(tokenized);
string obfuscatedSql = _obfuscatingFormatter.FormatSQLTree(parsedOriginal);
ITokenList tokenizedAgain = _tokenizer.TokenizeSQL(obfuscatedSql);
XmlDocument parsedAgain = _parser.ParseSQL(tokenizedAgain);
string unObfuscatedSql = _standardFormatter.FormatSQLTree(parsedAgain);
Utils.StripCommentsFromSqlTree(parsedOriginal);
string standardFormattedSql = _standardFormatter.FormatSQLTree(parsedOriginal);
if (!inputSQL.Contains(Utils.INVALID_SQL_WARNING))
{
Assert.AreEqual(standardFormattedSql, unObfuscatedSql, "standard-formatted vs obfuscatd and reformatted");
}
}
public void ExpectedParseTree(string FileName)
{
XmlDocument expectedXmlDoc = new XmlDocument();
expectedXmlDoc.PreserveWhitespace = true;
expectedXmlDoc.Load(Path.Combine(Utils.GetTestContentFolder(Utils.PARSEDSQLFOLDER), FileName));
string inputSql = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSql);
Node parsed = _parser.ParseSQL(tokenized);
Assert.AreEqual(expectedXmlDoc.OuterXml, parsed.ToXmlDoc().OuterXml);
}
public void ObfuscatingFormatReformatMatch(string FileName)
{
string inputSQL = Utils.GetTestFileContent(FileName, Utils.INPUTSQLFOLDER);
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSQL);
Node parsedOriginal = _parser.ParseSQL(tokenized);
string obfuscatedSql = _obfuscatingFormatter.FormatSQLTree(parsedOriginal);
var inputToSecondPass = obfuscatedSql;
if (inputToSecondPass.StartsWith(Utils.ERROR_FOUND_WARNING))
{
inputToSecondPass = inputToSecondPass.Replace(Utils.ERROR_FOUND_WARNING, "");
}
ITokenList tokenizedAgain = _tokenizer.TokenizeSQL(inputToSecondPass);
Node parsedAgain = _parser.ParseSQL(tokenizedAgain);
string unObfuscatedSql = _standardFormatter.FormatSQLTree(parsedAgain);
Utils.StripCommentsFromSqlTree(parsedOriginal);
string standardFormattedSql = _standardFormatter.FormatSQLTree(parsedOriginal);
Assert.AreEqual(standardFormattedSql, unObfuscatedSql, "standard-formatted vs obfuscatd and reformatted");
}
public void CheckThatParseTreeMatchesExpectedParseTree()
{
foreach (FileInfo xmlFile in new DirectoryInfo(ParsedDataFolder).GetFiles())
{
XmlDocument expectedXmlDoc = new XmlDocument();
expectedXmlDoc.PreserveWhitespace = true;
expectedXmlDoc.Load(xmlFile.FullName);
string inputSql = File.ReadAllText(Path.Combine(InputDataFolder, xmlFile.Name));
ITokenList tokenized = _tokenizer.TokenizeSQL(inputSql);
XmlDocument parsed = _parser.ParseSQL(tokenized);
Assert.AreEqual <string>(expectedXmlDoc.OuterXml, parsed.OuterXml, string.Format("Parse Tree Xml does not match for file {0}", xmlFile.Name));
}
}
private void DoFormatting()
{
var tokenizedSql = _tokenizer.TokenizeSQL(txt_Input.Text, txt_Input.SelectionStart);
//if (!splitContainer4.Panel2Collapsed && !splitContainer5.Panel1Collapsed)
//txt_TokenizedSql.Text = tokenizedSql.PrettyPrint();
var parsedSql = _parser.ParseSQL(tokenizedSql);
//if (!splitContainer4.Panel2Collapsed && !splitContainer5.Panel2Collapsed)
//txt_ParsedXml.Text = parsedSql.ToXmlDoc().OuterXml;
//webBrowser_OutputSql.SetHTML(_formatter.FormatSQLTree(parsedSql));
webBrowser_OutputSql.SetHTML(_formatter.FormatSQLTreeByPrefix(parsedSql, prefix.Text.Trim().ToLower()));
}
private void DoFormatting()
{
strErrorReturn = "";
const string txtNewLine = "\r\n---------------------------------------------------------------------------------------------------------------------------------------------------------\r\n\r\n";
StringBuilder errorReturn = new StringBuilder();
errorReturn.Append(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\r\n");
errorReturn.Append("输入SQL：\r\n" + txt_input.Text + txtNewLine);
string _inputSql = txt_input.Text.ToLower();
if (Properties.Settings.Default.AllUpper)
{
_inputSql = _inputSql.ToUpper();
}
string[] sqlInput = _inputSql.Split(new string[] { ";" }, StringSplitOptions.None);
string SqlHtml = string.Empty;
int index = 0;
foreach (var sql in sqlInput)
{
string sql_new = string.Empty;
if (sql.Trim() != "")
{
index++;
sql_new = sql;
if (Properties.Settings.Default.addSemicolon)
{
sql_new += ";";
}
var tokenizedSql = _tokenizer.TokenizeSQL(sql_new, sql_new.Length);
var parsedSql = _parser.ParseSQL(tokenizedSql);
string subSqlHtml = _formatter.FormatSQLTree(parsedSql);
if (!subSqlHtml.ToLower().Contains(">comment<") &&
!subSqlHtml.ToLower().Contains(">comment<") && !subSqlHtml.ToLower().Contains(">string<") &&
!subSqlHtml.ToLower().Contains(">int<") && !subSqlHtml.ToLower().Contains(">bigint<") &&
!subSqlHtml.ToLower().Contains(">partitioned<") &&
chk_default.Checked && SubstringCount(subSqlHtml, "\r\n") > 110)
{
Properties.Settings.Default.ExpandCommaLists = false;
Properties.Settings.Default.ExpandCaseStatements = false;
Properties.Settings.Default.AsAlign = false;
SetFormatter();
subSqlHtml = _formatter.FormatSQLTree(parsedSql);
Properties.Settings.Default.ExpandCommaLists = true;
Properties.Settings.Default.ExpandCaseStatements = true;
Properties.Settings.Default.AsAlign = true;
SetFormatter();
}
errorReturn.Append("第 " + index + " 个子SQL字段标记结果：\r\n" + tokenizedSql.PrettyPrint() + txtNewLine);
errorReturn.Append("第 " + index + " 个子SQL解析结果：\r\n" + parsedSql.ToXmlDoc().OuterXml + txtNewLine);
errorReturn.Append("第 " + index + " 个子SQL生成html结果：\r\n" + subSqlHtml + txtNewLine);
SqlHtml += subSqlHtml;
}
}
if (!chk_coloring.Checked)
{
SqlHtml = SqlHtml.Replace("#00AA00;", "#000000;").Replace("#AA0000;", "#000000;").Replace("#AA00AA;", "#000000;").Replace("#0000AA;", "#000000;").Replace("#777777;", "#000000;");
}
webBrowser_output.SetHTML(SqlHtml);
errorReturn.Append("最终生成html：\r\n" + SqlHtml);
strErrorReturn = errorReturn.ToString();
if (errorReturn.ToString().Contains(MessagingConstants.FormatErrorDefaultMessage))
{
string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "logs";
WriteLog.WriteFormatLog(path, "formatErrLog.log", strErrorReturn);
}
}