public static SqlScriptGenerator ScriptGeneratorFactory(SqlScriptGeneratorOptions options)
{
switch (Platform)
{
case TSqlPlatforms.Sql90:
return(new Sql90ScriptGenerator(options));
case TSqlPlatforms.Sql100:
return(new Sql100ScriptGenerator(options));
case TSqlPlatforms.SqlAzure:
return(new Sql120ScriptGenerator(options));
case TSqlPlatforms.Sql110:
return(new Sql110ScriptGenerator(options));
case TSqlPlatforms.Sql120:
return(new Sql120ScriptGenerator(options));
case TSqlPlatforms.Sql130:
return(new Sql130ScriptGenerator(options));
case TSqlPlatforms.SqlAzureV12:
return(new Sql140ScriptGenerator(options));
}
return(new Sql140ScriptGenerator(options));
}
//Add format settings to the Script Generator
private void addScriptOptionsToScriptGen(SqlScriptGeneratorOptions scriptOptions, ref Sql130ScriptGenerator srcGenScript)
{
if (scriptOptions != null)
{
srcGenScript.Options.AlignClauseBodies = scriptOptions.AlignClauseBodies;
srcGenScript.Options.AlignColumnDefinitionFields = scriptOptions.AlignColumnDefinitionFields;
srcGenScript.Options.AlignSetClauseItem = scriptOptions.AlignSetClauseItem;
srcGenScript.Options.AsKeywordOnOwnLine = scriptOptions.AsKeywordOnOwnLine;
srcGenScript.Options.IncludeSemicolons = scriptOptions.IncludeSemicolons;
srcGenScript.Options.IndentationSize = scriptOptions.IndentationSize;
srcGenScript.Options.IndentSetClause = scriptOptions.IndentSetClause;
srcGenScript.Options.IndentViewBody = scriptOptions.IndentViewBody;
srcGenScript.Options.KeywordCasing = scriptOptions.KeywordCasing;
srcGenScript.Options.MultilineInsertSourcesList = scriptOptions.MultilineInsertSourcesList;
srcGenScript.Options.MultilineInsertTargetsList = scriptOptions.MultilineInsertTargetsList;
srcGenScript.Options.MultilineSelectElementsList = scriptOptions.MultilineSelectElementsList;
srcGenScript.Options.MultilineSetClauseItems = scriptOptions.MultilineSetClauseItems;
srcGenScript.Options.MultilineViewColumnsList = scriptOptions.MultilineViewColumnsList;
srcGenScript.Options.MultilineWherePredicatesList = scriptOptions.MultilineWherePredicatesList;
srcGenScript.Options.NewLineBeforeCloseParenthesisInMultilineList = scriptOptions.NewLineBeforeCloseParenthesisInMultilineList;
srcGenScript.Options.NewLineBeforeFromClause = scriptOptions.NewLineBeforeFromClause;
srcGenScript.Options.NewLineBeforeGroupByClause = scriptOptions.NewLineBeforeGroupByClause;
srcGenScript.Options.NewLineBeforeHavingClause = scriptOptions.NewLineBeforeHavingClause;
srcGenScript.Options.NewLineBeforeJoinClause = scriptOptions.NewLineBeforeJoinClause;
srcGenScript.Options.NewLineBeforeOffsetClause = scriptOptions.NewLineBeforeOffsetClause;
srcGenScript.Options.NewLineBeforeOpenParenthesisInMultilineList = scriptOptions.NewLineBeforeOpenParenthesisInMultilineList;
srcGenScript.Options.NewLineBeforeOrderByClause = scriptOptions.NewLineBeforeOrderByClause;
srcGenScript.Options.NewLineBeforeOutputClause = scriptOptions.NewLineBeforeOutputClause;
srcGenScript.Options.NewLineBeforeWhereClause = scriptOptions.NewLineBeforeWhereClause;
srcGenScript.Options.SqlVersion = scriptOptions.SqlVersion;
srcGenScript.Options.SqlVersion = scriptOptions.SqlVersion;
}
}
public static TSqlScript Convert(string sql, out string prettySql)
{
var hasQuotedIdentifiers = false;
var parser = new TSql100Parser(hasQuotedIdentifiers);
var options = new SqlScriptGeneratorOptions();
options.SqlVersion = SqlVersion.Sql100;
options.KeywordCasing = KeywordCasing.Uppercase;
options.MultilineSelectElementsList = true;
options.MultilineWherePredicatesList = true;
IScriptFragment fragment;
IList<ParseError> errors;
using (var sr = new StringReader(sql))
{
fragment = parser.Parse(sr, out errors);
}
if (errors != null && errors.Count > 0)
{
var errorMessage = new StringBuilder();
foreach (var error in errors)
{
errorMessage.Append(error.Message);
errorMessage.Append("offset " + error.Offset);
}
throw new ApplicationException(errorMessage.ToString());
}
new Sql100ScriptGenerator(options).GenerateScript(fragment, out prettySql);
var parsedSql = fragment as TSqlScript;
return parsedSql;
}
public SqlScriptGenerator Get(SqlVersion version)
{
// TODO : upgrade ScriptDom package version to get access to newer syntax?
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions
{
SqlVersion = version,
KeywordCasing = KeywordCasing.Uppercase
};
switch (version)
{
case SqlVersion.Sql80:
return(new Sql80ScriptGenerator(options));
case SqlVersion.Sql90:
return(new Sql90ScriptGenerator(options));
case SqlVersion.Sql100:
return(new Sql100ScriptGenerator(options));
case SqlVersion.Sql110:
return(new Sql110ScriptGenerator(options));
case SqlVersion.Sql120:
default:
return(new Sql120ScriptGenerator(options));
}
}
public static void Apply(this SqlScriptGeneratorOptions source, Configuration.GenerationOptions options)
{
source.AlignClauseBodies = options.AlignClauseBodies;
source.AlignColumnDefinitionFields = options.AlignColumnDefinitionFields;
source.AlignSetClauseItem = options.AlignSetClauseItem;
source.AsKeywordOnOwnLine = options.AsKeywordOnOwnLine;
source.IncludeSemicolons = options.IncludeSemicolons;
source.IndentationSize = options.IndentationSize;
source.IndentSetClause = options.IndentSetClause;
source.IndentViewBody = options.IndentViewBody;
source.KeywordCasing = options.KeywordCasing;
source.MultilineInsertSourcesList = options.MultilineInsertSourcesList;
source.MultilineInsertTargetsList = options.MultilineInsertTargetsList;
source.MultilineSelectElementsList = options.MultilineSelectElementsList;
source.MultilineSetClauseItems = options.MultilineSetClauseItems;
source.MultilineViewColumnsList = options.MultilineViewColumnsList;
source.MultilineWherePredicatesList = options.MultilineWherePredicatesList;
source.NewLineBeforeCloseParenthesisInMultilineList = options.NewLineBeforeCloseParenthesisInMultilineList;
source.NewLineBeforeFromClause = options.NewLineBeforeFromClause;
source.NewLineBeforeGroupByClause = options.NewLineBeforeGroupByClause;
source.NewLineBeforeHavingClause = options.NewLineBeforeHavingClause;
source.NewLineBeforeJoinClause = options.NewLineBeforeJoinClause;
source.NewLineBeforeOffsetClause = options.NewLineBeforeOffsetClause;
source.NewLineBeforeOpenParenthesisInMultilineList = options.NewLineBeforeOpenParenthesisInMultilineList;
source.NewLineBeforeOrderByClause = options.NewLineBeforeOrderByClause;
source.NewLineBeforeOutputClause = options.NewLineBeforeOutputClause;
source.NewLineBeforeWhereClause = options.NewLineBeforeWhereClause;
source.SqlVersion = options.SqlVersion;
}
public Settings()
{
GeneratorOptions = new SqlScriptGeneratorOptions();
Costs = new CostThreshold {
High = 1m, Medium = 0.2m
};
}
public Settings()
{
GeneratorOptions = new SqlScriptGeneratorOptions();
Costs = new CostThreshold {
High = 1m, Medium = 0.2m
};
Clippy = new Clippy()
{
CallDelayMilliSeconds = 1500, StartEnabled = false
};
}
public SQLScripter(SqlVersion sqlVersion, SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
switch (sqlVersion)
{
case SqlVersion.Sql80:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql90:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql100:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
}
}
public MainWindow()
{
InitializeComponent();
_options = new SqlScriptGeneratorOptions();
txtIdent.Text = _options.IndentationSize.ToString();
cbxCase.SelectedIndex = (int)_options.KeywordCasing;
foreach (var child in stackChecks.Children)
{
if (child is CheckBox check)
{
var checkContent = check.Content.ToString();
PropertyInfo pinfo = typeof(SqlScriptGeneratorOptions).GetProperty(checkContent);
check.IsChecked = (bool?)pinfo?.GetValue(_options) == true;
}
}
}
public SQLScripter(SqlVersion sqlVersion, SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
switch (sqlVersion)
{
case SqlVersion.Sql80:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql90:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
case SqlVersion.Sql100:
SQLScripter100(options, quotedIdentifier, inputScript);
break;
}
}
/// <summary>
/// Analyzes the SQL text asynchronously.
/// </summary>
/// <param name="inputScript">The input script.</param>
/// <returns></returns>
public async Task <ParserResults> AnalyzeSqlTextAsync(string inputScript)
{
ParserResults results = new ParserResults();
_sqlParser = CreateSqlParser();
Sql120ScriptGenerator _scriptGen;
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions();
options.SqlVersion = _sqlVersion;
options.KeywordCasing = KeywordCasing.Uppercase;
_scriptGen = new Sql120ScriptGenerator(options);
TSqlFragment fragment;
IList <ParseError> errors;
using (StringReader sr = new StringReader(inputScript))
{
fragment = _sqlParser.Parse(sr, out errors);
}
if (errors.Count > 0)
{
results.ParsingExceptionDetail = string.Join(",", errors.Select(x => x.Message));
}
else
{
TSqlScript sqlScript = fragment as TSqlScript;
if (sqlScript != null)
{
foreach (TSqlBatch batch in sqlScript.Batches)
{
foreach (TSqlStatement statement in batch.Statements)
{
AnalyzeTsqlStatement(results, statement);
}
}
}
}
return(await Task.FromResult(results));
}
protected override void ProcessRecord()
{
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions();
options.AlignClauseBodies = alignClauseBodies;
options.AlignColumnDefinitionFields = alignColumnDefinitionFields;
options.AlignSetClauseItem = alignSetClauseItem;
options.AsKeywordOnOwnLine = asKeywordOnOwnLine;
options.IncludeSemicolons = includeSemicolons;
options.IndentationSize = indentationSize;
options.IndentSetClause = indentSetClause;
options.IndentViewBody = indentViewBody;
options.KeywordCasing = keywordCasing;
options.MultilineInsertSourcesList = multilineInsertSourcesList;
options.MultilineInsertTargetsList = multilineInsertTargetsList;
options.MultilineSelectElementsList = multilineSelectElementsList;
options.MultilineSetClauseItems = multilineSetClauseItems;
options.MultilineViewColumnsList = multilineViewColumnsList;
options.MultilineWherePredicatesList = multilineWherePredicatesList;
options.NewLineBeforeCloseParenthesisInMultilineList = newLineBeforeCloseParenthesisInMultilineList;
options.NewLineBeforeFromClause = newLineBeforeFromClause;
options.NewLineBeforeGroupByClause = newLineBeforeGroupByClause;
options.NewLineBeforeHavingClause = newLineBeforeHavingClause;
options.NewLineBeforeJoinClause = newLineBeforeJoinClause;
options.NewLineBeforeOpenParenthesisInMultilineList = newLineBeforeOpenParenthesisInMultilineList;
options.NewLineBeforeOrderByClause = newLineBeforeOrderByClause;
options.NewLineBeforeOutputClause = newLineBeforeOutputClause;
options.NewLineBeforeWhereClause = newLineBeforeWhereClause;
options.SqlVersion = sqlVersion;
try
{
SQLScripter scripter = new SQLScripter(sqlVersion, options, quotedIdentifierOff, inputScript);
WriteObject(scripter.Script);
}
catch (Exception ex)
{
ErrorRecord errorRecord = new ErrorRecord(ex, "InvalidSQLScript", ErrorCategory.InvalidData, ex.Message);
WriteError(errorRecord);
}
}
/// <summary>
/// SQL Query string parse and format
/// </summary>
/// <param name="sqlScript">Unformated and unparsed SQL Query String</param>
/// <param name="useScriptOptions">Do we use special format options or by Default. If set to useScriptOptions true and useConfFile false
/// gets the settings from Form.</param>
/// <param name="useConfFile">Do we use the configuration for formats from CustomFormatConfiguration.xlsx file?
/// Both useScriptOptions and useConfFile should be true!</param>
/// <param name="confFilePath">The full path to the file "CustomFormatConfiguration.xlsx" holding the custom format logic </param>
/// <returns></returns>
public string SQLScriptFormater(string sqlScript, bool useScriptOptions, bool useConfFile, string confFilePath)
{
try
{
string strFormattedSQL = null;
using (TextReader rdr = new StringReader(sqlScript))
{
TSql130Parser parser = new TSql130Parser(true);
IList <ParseError> errors = null;
TSqlFragment tree = parser.Parse(rdr, out errors);
if (errors.Count > 0)
{
foreach (ParseError err in errors)
{
strFormattedSQL = strFormattedSQL + err.Message + "\rn";
}
return(strFormattedSQL);
}
Sql130ScriptGenerator srcGen = new Sql130ScriptGenerator();
if (useScriptOptions)
{
//if we use the Excel ConfigFile
if (useConfFile)
{
SqlScriptGeneratorOptions optionsConfig = new SqlScriptGeneratorOptions();
//string confFilePath = System.Environment.CurrentDirectory + "\\" + "CustomFormattingSettings.xlsx";
try
{
if (!File.Exists(confFilePath))
{
throw new FileNotFoundException("File not found in App directory", "CustomFormatConfiguration.xlsx");
}
}
catch (FileNotFoundException ex)
{
MessageBox.Show("Error: " + ex.Message + "\n" + ex.FileName);
}
DataTable formatConfiguration = ReadExcelFile.getExcellToDtbl(confFilePath);
foreach (DataRow dr in formatConfiguration.Rows)
{
setScriptOptions(ref optionsConfig, null, dr);
}
addScriptOptionsToScriptGen(optionsConfig, ref srcGen);
}
//If we use the interface
else
{
//Search for our GroupBox in SQLFormatForm
GroupBox formatSettings = new GroupBox();
IEnumerable <Control> listControls = null;
foreach (System.Windows.Forms.Form form in Application.OpenForms)
{
if (form.Name == "SQLFormatForm")
{
listControls = GetAll(form, "gbFormatSettings");
}
}
var control = listControls.First();
formatSettings = control as GroupBox;
//Loop trought all controls in the group box and set format Settings
SqlScriptGeneratorOptions optionsConfig = new SqlScriptGeneratorOptions();
foreach (Control ctrl in formatSettings.Controls)
{
if ((ctrl is TextBox) || (ctrl is ComboBox) || (ctrl is CheckBox))
{
setScriptOptions(ref optionsConfig, ctrl, null);
}
}
addScriptOptionsToScriptGen(optionsConfig, ref srcGen);
}
}
srcGen.GenerateScript(tree, out strFormattedSQL);
return(strFormattedSQL);
}
}
catch (Exception)
{
throw;
}
}
static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("No input file specified");
return;
}
FileInfo input = new FileInfo(args[0]);
SqlScriptGeneratorOptions scriptOptions = new SqlScriptGeneratorOptions()
{
AlignClauseBodies = true,
AlignColumnDefinitionFields = true,
AlignSetClauseItem = true,
AsKeywordOnOwnLine = true,
IncludeSemicolons = true,
IndentationSize = 4,
IndentSetClause = true,
IndentViewBody = true,
KeywordCasing = KeywordCasing.Lowercase,
MultilineInsertSourcesList = true,
MultilineInsertTargetsList = true,
MultilineSelectElementsList = true,
MultilineSetClauseItems = true,
MultilineViewColumnsList = true,
MultilineWherePredicatesList = true,
NewLineBeforeCloseParenthesisInMultilineList = true,
NewLineBeforeFromClause = true,
NewLineBeforeGroupByClause = true,
NewLineBeforeHavingClause = true,
NewLineBeforeJoinClause = true,
NewLineBeforeOffsetClause = true,
NewLineBeforeOpenParenthesisInMultilineList = true,
NewLineBeforeOrderByClause = true,
NewLineBeforeOutputClause = true,
NewLineBeforeWhereClause = true,
SqlVersion = SqlVersion.Sql110
};
bool initialQuotedIdentifiers = false;
TSqlParser parser = new TSql80Parser(initialQuotedIdentifiers);
Sql80ScriptGenerator scriptGen = new Sql80ScriptGenerator(scriptOptions);
StreamReader sr = input.OpenText();
IList<ParseError> errors;
TSqlFragment fragment = parser.Parse(sr, out errors);
sr.Close();
if (errors.Count > 0)
{
Console.WriteLine("Parse {0} errors input stream", errors.Count);
return;
}
RaiserrorVisitor visitor = new RaiserrorVisitor();
fragment.Accept(visitor);
var newTokenStream = new List<TSqlParserToken>();
for (int n = 0; n < visitor.Nodes.Count; n++)
{
var node = visitor.Nodes[n];
var beginTokenIndex = (n == 0 ? 0 : node.FirstTokenIndex);
var firstTokenIndex = node.FirstTokenIndex;
var lastTokenIndex = node.LastTokenIndex;
var nextTokenIndex = (n < (visitor.Nodes.Count - 1) ? visitor.Nodes[n + 1].FirstTokenIndex : node.ScriptTokenStream.Count);
var msg = node.SecondParameter as StringLiteral;
int severity = 10;
int state = 0;
string errmsg = msg.Value;
RaiseErrorStatement newErr = new RaiseErrorStatement()
{
FirstParameter = CreateStringLiteral(errmsg),
SecondParameter = CreateIntegerLiteral(severity),
ThirdParameter = CreateIntegerLiteral(state)
};
var errTokens = scriptGen.GenerateTokens(newErr);
// copy token till element where replaced statement begins
for (int i = beginTokenIndex; i < firstTokenIndex; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", node.ScriptTokenStream[i].TokenType.ToString(), node.ScriptTokenStream[i].Text);
newTokenStream.Add(node.ScriptTokenStream[i]);
}
// inject new statement
for (int i = 0; i < errTokens.Count; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", errTokens[i].TokenType.ToString(), errTokens[i].Text);
newTokenStream.Add(errTokens[i]);
}
// copy tokens between last and next, if last token, till end of stream
for (int i = lastTokenIndex + 1; i < nextTokenIndex; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", node.ScriptTokenStream[i].TokenType.ToString(), node.ScriptTokenStream[i].Text);
newTokenStream.Add(node.ScriptTokenStream[i]);
}
}
TSqlFragment newFragment = parser.Parse(newTokenStream, out errors);
if (errors.Count > 0)
{
Console.WriteLine("Parse {0} errors output stream", errors.Count);
return;
}
StreamWriter sw = new StreamWriter(Console.OpenStandardOutput());
scriptGen.GenerateScript(newFragment, sw);
Console.WriteLine("-------------------------------------------------------");
for (int i = 0; i < newTokenStream.Count; i++)
{
Console.Write(newTokenStream[i].Text);
}
Console.WriteLine("-------------------------------------------------------");
}
protected override void ProcessRecord()
{
SqlScriptGeneratorOptions options = new SqlScriptGeneratorOptions();
options.AlignClauseBodies = alignClauseBodies;
options.AlignColumnDefinitionFields = alignColumnDefinitionFields;
options.AlignSetClauseItem = alignSetClauseItem;
options.AsKeywordOnOwnLine = asKeywordOnOwnLine;
options.IncludeSemicolons = includeSemicolons;
options.IndentationSize = indentationSize;
options.IndentSetClause = indentSetClause;
options.IndentViewBody = indentViewBody;
options.KeywordCasing = keywordCasing;
options.MultilineInsertSourcesList = multilineInsertSourcesList;
options.MultilineInsertTargetsList = multilineInsertTargetsList;
options.MultilineSelectElementsList = multilineSelectElementsList;
options.MultilineSetClauseItems = multilineSetClauseItems;
options.MultilineViewColumnsList = multilineViewColumnsList;
options.MultilineWherePredicatesList = multilineWherePredicatesList;
options.NewLineBeforeCloseParenthesisInMultilineList = newLineBeforeCloseParenthesisInMultilineList;
options.NewLineBeforeFromClause = newLineBeforeFromClause;
options.NewLineBeforeGroupByClause = newLineBeforeGroupByClause;
options.NewLineBeforeHavingClause = newLineBeforeHavingClause;
options.NewLineBeforeJoinClause = newLineBeforeJoinClause;
options.NewLineBeforeOpenParenthesisInMultilineList = newLineBeforeOpenParenthesisInMultilineList;
options.NewLineBeforeOrderByClause = newLineBeforeOrderByClause;
options.NewLineBeforeOutputClause = newLineBeforeOutputClause;
options.NewLineBeforeWhereClause = newLineBeforeWhereClause;
options.SqlVersion = sqlVersion;
try
{
SQLScripter scripter = new SQLScripter(sqlVersion, options, quotedIdentifierOff, inputScript);
WriteObject(scripter.Script);
}
catch (Exception ex)
{
ErrorRecord errorRecord = new ErrorRecord(ex, "InvalidSQLScript", ErrorCategory.InvalidData, ex.Message);
WriteError(errorRecord);
}
}
public Settings()
{
GeneratorOptions = new SqlScriptGeneratorOptions();
}
private void SQLScripter90(SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
Sql90ScriptGenerator scripter = new Sql90ScriptGenerator(options);
Generate(scripter, quotedIdentifier, inputScript);
}
/// <summary>
/// Simplifies a query by removing the value of certain types of token. Basically the ones that might be parameterised
/// </summary>
/// <param name="query"></param>
/// <param name="value"></param>
/// <returns></returns>
public static bool TrySimplify(string query, out string value)
{
value = "";
var parser = new TSql100Parser(true);
IList<ParseError> errors;
var result = parser.Parse(new StringReader(query), out errors);
// cannot parse, cannot simplify
if (errors.Count > 0)
{
LastError = "Cannot parse";
return false;
}
// without at least one batch with at least one statement, cannot simplify
// (should be 1 batch, 1 statement really as we are tracing StatementEnd)
var script = result as TSqlScript;
if (script == null || script.Batches.Count <= 0 && script.Batches[0].Statements.Count <= 0)
{
LastError = "Not 1 batch 1 statement";
return false;
}
// only interested in certain types of statements (date manipulation ones)
if (!HandledStatementType.Contains(script.Batches[0].Statements[0].GetType()))
{
LastError = "Not handled statement";
return false;
}
// basically remove all comments, newlines and extra whitespace
var options = new SqlScriptGeneratorOptions
{
AlignClauseBodies = false,
AlignColumnDefinitionFields = false,
AlignSetClauseItem = false,
AsKeywordOnOwnLine = false,
IncludeSemicolons = false,
IndentSetClause = false,
IndentViewBody = false,
NewLineBeforeCloseParenthesisInMultilineList = false,
NewLineBeforeFromClause = false,
NewLineBeforeGroupByClause = false,
NewLineBeforeHavingClause = false,
NewLineBeforeJoinClause = false,
NewLineBeforeOpenParenthesisInMultilineList = false,
NewLineBeforeOrderByClause = false,
NewLineBeforeOutputClause = false,
NewLineBeforeWhereClause = false,
MultilineInsertSourcesList = false,
MultilineInsertTargetsList = false,
MultilineSelectElementsList = false,
MultilineSetClauseItems = false,
MultilineViewColumnsList = false,
MultilineWherePredicatesList = false
};
var generator = new Sql100ScriptGenerator(options);
var tokens = generator.GenerateTokens(script);
var summary = new StringBuilder();
foreach (var token in tokens)
{
// replace values for parameterisable token types
if(ReplaceTokens.Contains(token.TokenType))
{
summary.Append("?");
}
else
{
summary.Append(token.Text);
}
}
// trim some junk
value = summary.ToString().TrimEnd(RemoveChars);
return true;
}
private void SQLScripter80(SqlScriptGeneratorOptions options, bool quotedIdentifier, String inputScript)
{
Sql80ScriptGenerator scripter = new Sql80ScriptGenerator(options);
Generate(scripter, quotedIdentifier, inputScript);
}
static void Main(string[] args)
{
if (args.Count() < 1)
{
Console.WriteLine("No input file specified");
return;
}
FileInfo input = new FileInfo(args[0]);
SqlScriptGeneratorOptions scriptOptions = new SqlScriptGeneratorOptions()
{
AlignClauseBodies = true,
AlignColumnDefinitionFields = true,
AlignSetClauseItem = true,
AsKeywordOnOwnLine = true,
IncludeSemicolons = true,
IndentationSize = 4,
IndentSetClause = true,
IndentViewBody = true,
KeywordCasing = KeywordCasing.Lowercase,
MultilineInsertSourcesList = true,
MultilineInsertTargetsList = true,
MultilineSelectElementsList = true,
MultilineSetClauseItems = true,
MultilineViewColumnsList = true,
MultilineWherePredicatesList = true,
NewLineBeforeCloseParenthesisInMultilineList = true,
NewLineBeforeFromClause = true,
NewLineBeforeGroupByClause = true,
NewLineBeforeHavingClause = true,
NewLineBeforeJoinClause = true,
NewLineBeforeOffsetClause = true,
NewLineBeforeOpenParenthesisInMultilineList = true,
NewLineBeforeOrderByClause = true,
NewLineBeforeOutputClause = true,
NewLineBeforeWhereClause = true,
SqlVersion = SqlVersion.Sql110
};
bool initialQuotedIdentifiers = false;
TSqlParser parser = new TSql80Parser(initialQuotedIdentifiers);
Sql80ScriptGenerator scriptGen = new Sql80ScriptGenerator(scriptOptions);
StreamReader sr = input.OpenText();
IList <ParseError> errors;
TSqlFragment fragment = parser.Parse(sr, out errors);
sr.Close();
if (errors.Count > 0)
{
Console.WriteLine("Parse {0} errors input stream", errors.Count);
return;
}
RaiserrorVisitor visitor = new RaiserrorVisitor();
fragment.Accept(visitor);
var newTokenStream = new List <TSqlParserToken>();
for (int n = 0; n < visitor.Nodes.Count; n++)
{
var node = visitor.Nodes[n];
var beginTokenIndex = (n == 0 ? 0 : node.FirstTokenIndex);
var firstTokenIndex = node.FirstTokenIndex;
var lastTokenIndex = node.LastTokenIndex;
var nextTokenIndex = (n < (visitor.Nodes.Count - 1) ? visitor.Nodes[n + 1].FirstTokenIndex : node.ScriptTokenStream.Count);
var msg = node.SecondParameter as StringLiteral;
int severity = 10;
int state = 0;
string errmsg = msg.Value;
RaiseErrorStatement newErr = new RaiseErrorStatement()
{
FirstParameter = CreateStringLiteral(errmsg),
SecondParameter = CreateIntegerLiteral(severity),
ThirdParameter = CreateIntegerLiteral(state)
};
var errTokens = scriptGen.GenerateTokens(newErr);
// copy token till element where replaced statement begins
for (int i = beginTokenIndex; i < firstTokenIndex; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", node.ScriptTokenStream[i].TokenType.ToString(), node.ScriptTokenStream[i].Text);
newTokenStream.Add(node.ScriptTokenStream[i]);
}
// inject new statement
for (int i = 0; i < errTokens.Count; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", errTokens[i].TokenType.ToString(), errTokens[i].Text);
newTokenStream.Add(errTokens[i]);
}
// copy tokens between last and next, if last token, till end of stream
for (int i = lastTokenIndex + 1; i < nextTokenIndex; i++)
{
Console.WriteLine("Token: [{0}] Text: [{1}]", node.ScriptTokenStream[i].TokenType.ToString(), node.ScriptTokenStream[i].Text);
newTokenStream.Add(node.ScriptTokenStream[i]);
}
}
TSqlFragment newFragment = parser.Parse(newTokenStream, out errors);
if (errors.Count > 0)
{
Console.WriteLine("Parse {0} errors output stream", errors.Count);
return;
}
StreamWriter sw = new StreamWriter(Console.OpenStandardOutput());
scriptGen.GenerateScript(newFragment, sw);
Console.WriteLine("-------------------------------------------------------");
for (int i = 0; i < newTokenStream.Count; i++)
{
Console.Write(newTokenStream[i].Text);
}
Console.WriteLine("-------------------------------------------------------");
}
public void SnakeCaseFile(string filePath)
{
Encoding encoding;
string f1 = null;
//try to use same encoding as source file
using (var reader = new StreamReader(filePath))
{
f1 = reader.ReadToEnd().ToLower();
encoding = reader.CurrentEncoding;
}
string sqlFileText = File.ReadAllText(filePath);
var sqlFileTextLines = File.ReadAllLines(filePath);
//File.WriteAllText(file.FullName, text);
var parser = new TSql150Parser(false);
var options = new SqlScriptGeneratorOptions();
var scriptGen = new Sql150ScriptGenerator(options)
{
};
IList <ParseError> errors;
var script2 =
parser.Parse(new StringReader(sqlFileText), out errors) as TSqlScript;
if (errors.Count > 0)
{
RaiseSqlErrorsException(errors, sqlFileText, filePath);
return;
}
//var sb = new StringBuilder(sqlFileText);
foreach (var batch in script2.Batches.Reverse())
{
foreach (var statement in batch.Statements.Reverse())
{
var snakeCaseVisitor = new SqlServerSnakeCaseVisitor2();
statement.Accept(snakeCaseVisitor);
var sortedReplacements = snakeCaseVisitor.Replacements.OrderByDescending(r => r.Offset);
foreach (var r in sortedReplacements)
{
if (filePath.EndsWith("Schedule_ProgramPattern.sql") && r.LineNumber == 1)
{
int x = 1;
}
var sb2 = new StringBuilder(sqlFileTextLines[r.LineNumber - 1]);
sb2 = sb2.Replace(r.OldValue, r.NewValue, r.Column - 1, r.OldValue.Length);
sqlFileTextLines[r.LineNumber - 1] = sb2.ToString();
}
}
}
try
{
File.WriteAllLines(filePath, sqlFileTextLines, encoding);
}
catch (UnauthorizedAccessException e)
{
Console.WriteLine(e);
}
}
/// <summary>
/// Get and assign format settings based on interface or DataTable from excel file
/// If Control == null then we use it in file mode and vice versa
/// </summary>
/// <param name="opt"> reference to SqlScriptGeneratorOptions</param>
/// <param name="ctrl"> passed Control if we will use it in Interface mode</param>
/// <param name="settingRow">passed DataRow which contain the settings</param>
private void setScriptOptions(ref SqlScriptGeneratorOptions opt, Control ctrl, DataRow settingRow)
{
CheckBox cbk = new CheckBox();
ComboBox combo = new ComboBox();
TextBox tb = new TextBox();
ScriptOptions currentScriptSetting = ScriptOptions.AlignClauseBodies;
if (ctrl != null)
{
currentScriptSetting = ctrl.Name.ToEnum <ScriptOptions>();
}
else
{
currentScriptSetting = settingRow[1].ToString().ToEnum <ScriptOptions>();
}
switch (currentScriptSetting)
{
case ScriptOptions.AlignClauseBodies:
opt.AlignClauseBodies = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.AlignColumnDefinitionFields:
opt.AlignColumnDefinitionFields = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.AlignSetClauseItem:
opt.AlignSetClauseItem = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.AsKeywordOnOwnLine:
opt.AsKeywordOnOwnLine = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.IncludeSemicolons:
cbk = ctrl as CheckBox;
opt.IncludeSemicolons = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.IndentationSize:
opt.IndentationSize = GetValueInt(ctrl, settingRow);
break;
case ScriptOptions.IndentSetClause:
opt.IndentSetClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.IndentViewBody:
opt.IndentViewBody = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.KeywordCasing:
ComboBox comboCasing = ctrl as ComboBox;
// 0 - Lowercase,1 - Uppercase,2 - PascalCase
switch (GetValueInt(ctrl, settingRow))
{
case 0:
opt.KeywordCasing = KeywordCasing.Lowercase;
break;
case 1:
opt.KeywordCasing = KeywordCasing.Uppercase;
break;
case 2:
opt.KeywordCasing = KeywordCasing.PascalCase;
break;
}
break;
case ScriptOptions.MultilineInsertSourcesList:
opt.MultilineInsertSourcesList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.MultilineInsertTargetsList:
opt.MultilineInsertTargetsList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.MultilineSelectElementsList:
opt.MultilineSelectElementsList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.MultilineSetClauseItems:
opt.MultilineSetClauseItems = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.MultilineViewColumnsList:
opt.MultilineViewColumnsList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.MultilineWherePredicatesList:
opt.MultilineWherePredicatesList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeCloseParenthesisInMultilineList:
opt.NewLineBeforeCloseParenthesisInMultilineList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeFromClause:
opt.NewLineBeforeFromClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeGroupByClause:
opt.NewLineBeforeGroupByClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeHavingClause:
opt.NewLineBeforeHavingClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeJoinClause:
opt.NewLineBeforeJoinClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeOffsetClause:
opt.NewLineBeforeOffsetClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeOpenParenthesisInMultilineList:
opt.NewLineBeforeOpenParenthesisInMultilineList = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeOrderByClause:
opt.NewLineBeforeOrderByClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeOutputClause:
opt.NewLineBeforeOutputClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.NewLineBeforeWhereClause:
opt.NewLineBeforeWhereClause = GetValueBool(ctrl, settingRow);
break;
case ScriptOptions.SqlVersion:
//Sql2005 = 0,
//Sql2000 = 1,
//Sql2008 = 2,
//Sql2012 = 3,
//Sql2014 = 4,
//Sql2016 = 5,
//Sql2017 = 6
switch (GetValueInt(ctrl, settingRow))
{
case 0:
opt.SqlVersion = SqlVersion.Sql90;
break;
case 1:
opt.SqlVersion = SqlVersion.Sql80;
break;
case 2:
opt.SqlVersion = SqlVersion.Sql100;
break;
case 3:
opt.SqlVersion = SqlVersion.Sql110;
break;
case 4:
opt.SqlVersion = SqlVersion.Sql120;
break;
case 5:
opt.SqlVersion = SqlVersion.Sql130;
break;
case 6:
opt.SqlVersion = SqlVersion.Sql140;
break;
}
break;
default:
break;
}
}
public Settings()
{
GeneratorOptions = new SqlScriptGeneratorOptions();
Costs = new CostThreshold {High = 1m, Medium = 0.2m};
Clippy = new Clippy(){CallDelayMilliSeconds = 1500, StartEnabled = false};
}
public TSqlScriptGenerator(TextWriter writer, SqlScriptGeneratorOptions options)
{
_writer = writer;
_generator = new Sql150ScriptGenerator(options);
_builder = new TSqlScriptBuilder();
}
public Settings()
{
GeneratorOptions = new SqlScriptGeneratorOptions();
Costs = new CostThreshold {High = 1m, Medium = 0.2m};
}