public string ProcessSql(string sql, CommandLineOptions opts)
{
var newSchema = opts.NewSchema;
var parser = new TSql150Parser(false, SqlEngineType.All);
var sqlReader = new StringReader(sql);
var fragment = parser.Parse(sqlReader, out var errors);
if (errors.Any())
{
throw new Exception("Parsing failed");
}
var script = fragment as TSqlScript;
var idRefactorer = new SqlIdRefactorer();
Debug.Assert(script?.Batches != null, "script?.Batches != null");
script.AcceptChildren(idRefactorer);
var newScript = idRefactorer.ProcessIdChanges(sql, newSchema);
// Console.WriteLine($"[Done] {newScript.StringPreview()}...");
return(newScript);
}
public List <ParseResults> Parse(string procText)
{
var parser = new TSql150Parser(true);
var textReader = new StringReader(procText);
var sqlTree = parser.Parse(textReader, out var errors);
if (errors.Count > 0)
{
throw new ParserException(errors);
}
var visitor = new Visitor();
sqlTree.Accept(visitor);
TSqlScript sqlScript = (TSqlScript)sqlTree;
Debug.Assert(sqlScript.Batches.Count == 1, "Currently our visitor doesn't support multiple batches.");
Debug.Assert(visitor.Results.Count == sqlScript.Batches[0].Statements.Count, "Statements processed by visitor don't match statements counted by parser.");
int i = 0;
foreach (var stm in sqlScript.Batches[0].Statements)
{
visitor.Results[i++].SetStatementText(procText.Substring(stm.StartOffset, stm.FragmentLength));
}
return(visitor.Results);
}
public SqlToExpressionParsing()
{
TablesMetadata tablesMetadata = new TablesMetadata();
tablesMetadata.AddTable(new TableMetadata("project", typeof(Project)));
var queryExecutor = new QueryExecutor(
new TableResolver(),
new DefaultFromTableExecutorFactory(),
new DefaultWhereExecutorFactory(),
new DefaultGroupByExecutorFactory(),
new DefaultSelectExecutorFactory(),
new DefaultOrderByExecutorFactory(),
new DefaultOffsetExecutorFactory(),
new DefaultDistinctExecutorFactory(),
new DefaultAggregateFunctionFactory());
sqlExecutor = new SqlExecutor(
new AntlrSqlParser(),
tablesMetadata,
queryExecutor,
new DefaultSearchExpressionProvider(),
new DefaultOperationsProvider());
sql150Parser = new TSql150Parser(true);
}
private void button1_Click(object sender, EventArgs e)
{
using (var rdr = new StringReader(textBox1.Text))
{
IList <ParseError> errors = null;
var parser = new TSql150Parser(true, SqlEngineType.All);
var tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
MyVisitor checker = new MyVisitor();
tree.Accept(checker);
if (checker.containsOnlySelects)
{
MessageBox.Show("Looks ok!");
}
else
{
MessageBox.Show("The code contains something other than SELECT statements!");
}
}
}
/// <summary>
/// A SqlObject which contains information about the name and schema of the given object full name.
/// </summary>
/// <param name="fullName">Full name of object, including schema (if it exists).</param>
/// <exception cref="InvalidOperationException">If the name can't be parsed</exception>
public SqlObject(string fullName)
{
var parser = new TSql150Parser(false);
var stringReader = new StringReader(fullName);
SchemaObjectName tree = parser.ParseSchemaObjectName(stringReader, out IList <ParseError> errors);
if (errors.Count > 0)
{
string errorMessages = "Encountered error(s) while parsing schema and object name:\n";
foreach (ParseError err in errors)
{
errorMessages += $"{err.Message}\n";
}
throw new InvalidOperationException(errorMessages);
}
var visitor = new TSqlObjectFragmentVisitor();
tree.Accept(visitor);
this.Schema = visitor.schemaName;
this.QuotedSchema = this.Schema == SCHEMA_NAME_FUNCTION ? this.Schema : this.Schema.AsSingleQuotedString();
this.Name = visitor.objectName;
this.QuotedName = this.Name.AsSingleQuotedString();
this.FullName = this.Schema == SCHEMA_NAME_FUNCTION ? this.Name : $"{this.Schema}.{this.Name}";
this.QuotedFullName = this.FullName.AsSingleQuotedString();
this.BracketQuotedFullName = this.Schema == SCHEMA_NAME_FUNCTION?this.Name.AsBracketQuotedString() : $"{this.Schema.AsBracketQuotedString()}.{this.Name.AsBracketQuotedString()}";
}
static void Main(string[] args)
{
if (args.Count() != 1)
{
Console.WriteLine(JsonConvert.SerializeObject(new { error = "you must inform only one argument" }));
return;
}
var parser = new TSql150Parser(true);
var tree = parser.Parse(new StringReader(args.First()), out var errors);
if (errors.Count > 0)
{
Console.Write(JsonConvert.SerializeObject(new { error = errors.Select(error => error.Message) }));
return;
}
var settings = new JsonSerializerSettings
{
TypeNameHandling = TypeNameHandling.Objects,
ContractResolver = new TSqlFragmentContractResolver()
};
Console.WriteLine(JsonConvert.SerializeObject(tree, settings));
}
public string ProcessSql(List <string> sqls, CommandLineOptions opts)
{
var tableResult = string.Empty;
var procResult = string.Empty;
var funcResult = string.Empty;
var tableTypeResult = string.Empty;
var indexResult = string.Empty;
var batchSeparator = Environment.NewLine + "GO" + Environment.NewLine;
var parser = new TSql150Parser(false, SqlEngineType.All);
foreach (var processedFragment in sqls)
{
var sqlReader = new StringReader(processedFragment);
var fragment = parser.Parse(sqlReader, out var errors);
if (errors.Any())
{
throw new Exception("Parsing failed");
}
// TODO: Assumes exactly one statement per file, not ideal.
var visitor = new ScriptTypeGetter();
fragment.Accept(visitor);
if (visitor.IsTable)
{
tableResult += processedFragment + batchSeparator;
}
else if (visitor.IsProcedure)
{
procResult += processedFragment + batchSeparator;
}
else if (visitor.IsFunction)
{
funcResult += processedFragment + batchSeparator;
}
else if (visitor.IsTableType)
{
tableTypeResult += processedFragment + batchSeparator;
}
else if (visitor.IsIndex)
{
indexResult += processedFragment + batchSeparator;
}
else
{
tableResult += processedFragment + batchSeparator;
}
}
var result = tableResult + batchSeparator +
indexResult + batchSeparator +
tableTypeResult + batchSeparator +
funcResult + batchSeparator +
procResult + batchSeparator;
return(result);
}
static void Main(string[] args)
{
var SqlParser = new TSql150Parser(false);
IList <ParseError> errors = new List <ParseError>();
var sql = SqlParser.Parse(new StringReader(""), out errors);
typeof(TSqlScript).
}
private TSqlFragment ParseSqlFile()
{
using (var stream = File.OpenRead(_sqlFile))
using (var reader = new StreamReader(stream))
{
var parser = new TSql150Parser(true);
return(parser.Parse(reader, out var errors));
}
}
public ScriptingService(IMetadataService metadata, IQueryExecutor executor)
{
Parser = new TSql150Parser(false, SqlEngineType.Standalone);
Generator = new Sql150ScriptGenerator(new SqlScriptGeneratorOptions()
{
AlignClauseBodies = true
});
ScriptExecutor = executor;
MetadataService = metadata;
}
private static (TSqlFragment sqlTree, IList <ParseError> errors) ParseSql(string procText)
{
var parser = new TSql150Parser(true);
using (var textReader = new StringReader(procText))
{
var sqlTree = parser.Parse(textReader, out var errors);
return(sqlTree, errors);
}
}
private IEnumerable <TSqlFragment> ParseSqlFiles()
{
foreach (var sqlFile in _sqlFiles)
{
using (var stream = File.OpenRead(sqlFile))
using (var reader = new StreamReader(stream))
{
var parser = new TSql150Parser(true);
yield return(parser.Parse(reader, out var errors));
}
}
}
public void Format()
{
_ai.TrackEvent("Format SQL");
var dom = new TSql150Parser(true);
var fragment = dom.Parse(new StringReader(_editor.Text), out var errors);
if (errors.Count != 0)
{
return;
}
new Sql150ScriptGenerator().GenerateScript(fragment, out var sql);
_editor.Text = sql;
}
private void Parse()
{
Errors.Text = "";
MaxDepth = Int32.Parse(DepthText.Text);
var parser = new TSql150Parser(true);
var script = parser.Parse(new StringReader(GetText()), out IList <ParseError> errors);
if (errors.Count > 0)
{
Errors.Text = "";
foreach (var e in errors)
{
Errors.Text += "Error: " + e.Message + " at: " + e.Offset + "\r\n";
}
return;
}
var enumerator = new EnumeratorVisitor();
script.Accept(enumerator);
Results.Items.Clear();
foreach (var node in enumerator.Nodes)
{
foreach (var i in GetChildren(node))
{
Results.Items.Add(i);
}
}
Tokens.Items.Clear();
var newItem = new TreeViewItem();
newItem.Header = "Tokens";
newItem.IsExpanded = true;
foreach (var t in script.ScriptTokenStream)
{
var newChild = new TreeViewItem();
newChild.Header = string.Format("{0} : {1} : {2} : {3}", t.TokenType, t.Text, t.Offset, t.Column);
newItem.Items.Add(newChild);
newChild.Tag = t;
}
Tokens.Items.Add(newItem);
}
static void Main(string[] args)
{
using (var rdr = new StreamReader(@"c:\ScriptDom\sampleproc.sql"))
{
IList <ParseError> errors = null;
// pass the reader to the scriptdom
var parser = new TSql150Parser(true, SqlEngineType.All);
var tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
}
}
static void Main(string[] args)
{
using (var rdr = new StreamReader(@"c:\ScriptDom\sampleproc.sql"))
{
IList <ParseError> errors = null;
var parser = new TSql150Parser(true, SqlEngineType.All);
var tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
// walk the tree now
tree.Accept(new myVisitor());
}
}
void ParseSource()
{
using (StringReader sr = new StringReader(MSource.Text))
{
IList <ParseError> errors;
TSql150Parser parser = new TSql150Parser(true);
TSqlScript script = parser.Parse(sr, out errors) as TSqlScript;
CreateTableStatement cts = null;
foreach (TSqlBatch b in script.Batches)
{
foreach (TSqlStatement statement in b.Statements)
{
if (!(statement is CreateTableStatement))
{
continue;
}
cts = (CreateTableStatement)statement;
break;
}
}
foreach (var i in items)
{
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = "";
if (cts != null)
{
//string s = i.Item2.Translate(cts.Definition, i.Item1);
string s = i.Item2.TranslateExt(cts, i.Item1);
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = s;
}
else
{
if ((errors != null) && (errors.Count > 0))
{
(i.Item1.Panel1.Controls.Find("textbox", true)[0] as TextBox).Text = errors[0].Message;
}
}
}
}
}
private static void VerificationParse(bool enable, string finalSql)
{
var parser = new TSql150Parser(false, SqlEngineType.All);
if (!enable)
{
return;
}
var resultReader = new StringReader(finalSql);
parser.Parse(resultReader, out var finalErrors);
if (!finalErrors.Any())
{
return;
}
PrintParserErrors(finalErrors);
throw new Exception("Result of the SQL file is not valid. This should not happen.");
}
public string Handle(PrettyPrintCommand message)
{
var sqlParser = new TSql150Parser(false, SqlEngineType.All);
IList <ParseError> parseErrros = new List <ParseError>();
TSqlFragment res = null;
using (TextReader sr = new StringReader(message.Text))
{
res = sqlParser.Parse(sr, out parseErrros);
}
if (parseErrros.Count == 0)
{
return(SQLFormatter.Format(res));
}
else
{
return(this.prettyPrintXml(message.Text));
}
}
private void button1_Click(object sender, EventArgs e)
{
using (var rdr = new StringReader(textBox1.Text))
{
IList <ParseError> errors = null;
var parser = new TSql150Parser(true, SqlEngineType.All);
var tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
var scrGen = new Sql150ScriptGenerator();
string formattedSQL = null;
scrGen.GenerateScript(tree, out formattedSQL);
textBox2.Text = formattedSQL;
}
}
static void Main(string[] args)
{
var rdr = new StreamReader(@"c:\ScriptDom\sampleproc.sql");
IList <ParseError> errors = null;
var parser = new TSql150Parser(true, SqlEngineType.All);
var tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
ScriptDomWalk(tree, "root");
using (var wr = new StreamWriter(@"c:\temp\scrdom.xml"))
{
wr.Write(result);
wr.Flush();
}
}
public (XDocument, IEnumerable <IDiagnosticItem>) Extract(string source)
{
IList <IDiagnosticItem> diagnostics = new List <IDiagnosticItem>();
XDocument document = null;
TSql150Parser p = new TSql150Parser(false);
TextReader r = new StringReader(source);
TSqlFragment fragment = p.Parse(r, out IList <ParseError> l);
if (l.Any())
{
foreach (var e in l)
{
diagnostics.Add(new DiagnosticItem(e));
}
}
else
{
document = TSQLVisitor.Generate(fragment);
}
return(document, diagnostics);
}
/// <summary>
/// Initializes a new instance of the <see cref="Procedure"/> class.
/// </summary>
/// <param name="prefix">The prefix used on stored procedure names.</param>
public Procedure(dac.TSqlObject tSqlObject)
{
this.Name = tSqlObject.Name.Parts.Last();
this.Schema = tSqlObject.Name.Parts.First();
var parser = new TSql150Parser(false);
IList <ParseError> errors;
var frag = parser.Parse(new StringReader(tSqlObject.GetScript()), out errors);
var selectVisitor = new SqlSharpener.SelectVisitor();
frag.Accept(selectVisitor);
var depends = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
.Where(x => x.ObjectType.Name == "Column")
.ToList();
if (depends.Count() > 0)
{
var bodyColumnTypes = depends
.GroupBy(bd => string.Join(".", bd.Name.Parts))
.Select(grp => grp.First())
.ToDictionary(
key => string.Join(".", key.Name.Parts),
val => new DataType
{
Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).FirstOrDefault()?.Name.Parts.Last()),
Nullable = dac.Column.Nullable.GetValue <bool>(val)
},
StringComparer.InvariantCultureIgnoreCase);
var unions = selectVisitor.Nodes.OfType <BinaryQueryExpression>().Select(bq => GetQueryFromUnion(bq)).Where(x => x != null);
var selects = selectVisitor.Nodes.OfType <QuerySpecification>().Concat(unions);
this.Selects = selects.Select(s => new Select(s, bodyColumnTypes)).ToList();
}
}
public static void Main()
{
var parser = new TSql150Parser(true, SqlEngineType.Standalone);
var fragment = parser.Parse(new StringReader("select top 1 [Column1],[Column2],[Column3] from [table] where [id] = 2;"), out IList <ParseError> errors);
foreach (var error in errors)
{
Console.WriteLine($"Line: {error.Line} Column:{error.Column} Number: {error.Number} Message: {error.Message}");
}
foreach (var token in fragment.ScriptTokenStream)
{
Console.WriteLine($"{token.TokenType,20}: `{token.Text}`");
}
var items = TSqlSelectVisitor.VisitSelectStatements(fragment);
var definedTypes = typeof(Sql100ScriptGenerator).Assembly.DefinedTypes;
foreach (var definedType in definedTypes)
{
var baseType = definedType.BaseType;
if (typeof(TableReference).IsAssignableFrom(baseType))
{
}
}
var types = typeof(Sql100ScriptGenerator).Assembly.DefinedTypes.Where(
x => x.BaseType.Name == "TableReference");
var props = typeof(ISqlScriptGeneratorOptions).GetProperties().OrderBy(x => x.Name);
var sb = new StringBuilder();
foreach (var prop in props)
{
string type = "";
switch (prop.PropertyType.Name.ToLower())
{
case "string":
{
type = "xs:string";
break;
}
case "boolean":
{
type = "xs:boolean";
break;
}
case "int32":
{
type = "xs:int";
break;
}
default:
{
type = prop.Name;
break;
}
}
//sb.AppendLine($"<xs:element name=\"{prop.Name}\" type=\"{type}\" />");
sb.AppendLine($"<xs:element ref=\"{prop.Name}\" minOccurs=\"0\" />");
}
Console.Write(sb);
Clipboard.SetText(sb.ToString());
Debug.Print("");
}
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);
}
}
