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}; }