static void Main(string[] args)
{
string text2 = System.IO.File.ReadAllText(path_script);
Console.WriteLine("Création Dictionnaire...");
Dictionary <String, List <String> > PhysicalTableList = CreateDictionnary(text2);
Console.WriteLine("Fin création dictionnaire");
/*Lire fichier*/
File.Delete(path_test);
if (!File.Exists(path_test))
{
string text = System.IO.File.ReadAllText(path_queries);
string[] queries = text.Split("________________________________________");
/*Initialisation parser*/
var parser = new TSql130Parser(false);
/*Stocke les erreurs liés à la lecture du parser*/
IList <ParseError> errors;
/*Iniatilisation*/
MyVisitor myvisitor = new MyVisitor();
VisitorCommonTable myVisitorCommonTable = new VisitorCommonTable();
myvisitor.PhysicalTableList = PhysicalTableList;
int i = 0;
foreach (String query in queries)
{
myVisitorCommonTable.id_requete_courante = i;
var fragment = parser.Parse(new StringReader(query), out errors);
fragment.Accept(myVisitorCommonTable);
i = i + 1;
}
myvisitor.dictTableWith = myVisitorCommonTable.dict;
i = 0;
/*Pour chaque requête présent dans le fichier, on l'analyse*/
foreach (var query in queries)
{
/*Sépare chaque query et leur résultat par "______________" pour le fichier de sorti*/
myvisitor.save(query, i.ToString());
//Console.WriteLine(Environment.NewLine+"_____________________________"+query);
/*Enregistre la query en cours*/
TSqlFragment fragment = parser.Parse(new StringReader(query), out errors);
fragment.Accept(myvisitor);
myvisitor.Add();
i++;
Console.WriteLine(i);
}
myvisitor.Imprime();
}
PreTraitement preTraitement = new PreTraitement(PhysicalTableList);
preTraitement.Process();
}
static SqlError[] ParseString(string text)
{
var rd = new StringReader(text);
parser.Parse(rd, out var rawErrors);
return(rawErrors.Select(err =>
new SqlError
{
Line = err.Line,
Message = err.Message
}
).ToArray());
}
async void _build_syntax_btn_Click(object sender, RoutedEventArgs e)
{
var syntax_txt = _query_tb.Text;
var bg_default = _analysis_tb.Background;
_analysis_tb.Background = Brushes.Gold;
var analysis_txt = await Task.Factory.StartNew(() => {
var parser = new TSql130Parser(false);
IList <ParseError> errors;
var fragment = parser.Parse(new StringReader(syntax_txt), out errors);
if (errors.Count > 0)
{
return("ERROR:\n" + String.Join("\n", errors.Select(err => err.Message)));
}
fragment.Accept(new MyNaiveMutator());
var renderer = new Sql130ScriptGenerator();
string sql;
renderer.GenerateScript(fragment, out sql);
return(sql);
});
this.Dispatcher.Invoke(() => {
_analysis_tb.Text = analysis_txt;
_analysis_tb.Background = bg_default;
});
}
private bool Parse(string dataType)
{
Microsoft.SqlServer.TransactSql.ScriptDom.TSql130Parser sqlParser = new TSql130Parser(false);
IList <ParseError> errors;
TSqlFragment dataTypeFragment = sqlParser.Parse(new StringReader("declare @t " + dataType), out errors);
if (!errors.Any())
{
var dataTypeDeclaration = dataTypeFragment as TSqlScript;
var statement = dataTypeDeclaration?.Batches?[0]?.Statements?[0] as DeclareVariableStatement;
var sqlDataType = statement?.Declarations?[0].DataType as SqlDataTypeReference;
if (sqlDataType?.SqlDataTypeOption == SqlDataTypeOption.Char ||
sqlDataType?.SqlDataTypeOption == SqlDataTypeOption.VarChar)
{
DataType = sqlDataType.SqlDataTypeOption.ToString().ToLower(); // only used by user defined table type - keep it lower case
if (sqlDataType.Parameters.Count > 0 && sqlDataType.Parameters[0].LiteralType == LiteralType.Integer)
{
_maxDataSize = Int32.Parse((sqlDataType.Parameters[0] as IntegerLiteral).Value);
_dataTypeText = dataType;
return(true);
}
}
}
return(false);
}
//Script formater only for parsing the query
public string SQLScriptFormater(string sqlScript, bool onlyParse)
{
try
{
string strFormattedSQL = string.Empty;
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 + "\r\n";
}
return(strFormattedSQL);
}
else
{
return("true");
}
}
}
catch (Exception)
{
throw;
}
}
public static TSqlFragment GetFragment(string script)
{
var parser = new TSql130Parser(false);
IList <ParseError> errors;
return(parser.Parse(new StringReader(script), out errors));
}
/// <summary>
/// Uses the SQL Formatter to pretty print the code; not strictly necessary as the only place you will see it is in Profiler :)
/// However, by reparsing the code we ensure that any errors in conversion are caught.
/// </summary>
/// <exception cref="Exception">Throws a generic exception if there is a parse error</exception>
protected void FormatSQL()
{
if (reparse)
{
// use the features in the ScriptDom namespace to pretty print our T-SQL
TextReader rdr = new StringReader(SqlStmt.ToString());
IList <ParseError> errors = null;
TSql130Parser parser = new TSql130Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
rdr.Close();
if (errors.Count > 0)
{
Exception e = new Exception(string.Format("Parse Error after converstion on line {0}, column {1}: {2}", errors[0].Line, errors[0].Column, errors[0].Message));
throw e;
}
else
{
Sql130ScriptGenerator scrGen = new Sql130ScriptGenerator();
string formattedSQL = null;
scrGen.GenerateScript(tree, out formattedSQL);
cmd.CommandText = formattedSQL;
}
}
else
{
cmd.CommandText = SqlStmt.ToString();
}
}
static bool SqlHasInsertWithoutColumnList(string SQL)
{
SQLVisitor visitor = new SQLVisitor();
// visitor.TolerateTempTables = true;
TSql130Parser parser = new TSql130Parser(true);
IList <ParseError> errors;
var fragment = parser.Parse(new System.IO.StringReader(SQL), out errors);
fragment.Accept(visitor);
return(visitor.HasInsertWithoutColumnSpecification);
}
public static List <TSqlStatement> GetStatements(string script, out IList <ParseError> errors)
{
var parser = new TSql130Parser(false);
//IList<ParseError> errors;
var s = parser.Parse(new StringReader(script), out errors);
var visitor = new StatementVisitor();
s.Accept(visitor);
return(visitor.Statements);
}
public void AnalyseStatements()
{
Microsoft.SqlServer.TransactSql.ScriptDom.TSql130Parser parser = new TSql130Parser(false);
IList <ParseError> errors;
TSqlFragment fragment = parser.Parse(new StringReader(_sqlFragment), out errors);
if (!errors.Any())
{
_collector.SetSqlFragment(fragment);
fragment.Accept(new ColumnsMatchingPatternVisitor(_collector, _variableNamePattern, _dataTypeName));
}
}
public static List <CreateFunctionStatement> GetFunctions(string script)
{
var parser = new TSql130Parser(false);
IList <ParseError> errors;
var s = parser.Parse(new StringReader(script), out errors);
var visitor = new FunctionVisitor();
s.Accept(visitor);
return(visitor.Statements);
}
public static List<CreateProcedureStatement> GetProcedures(string script)
{
var parser = new TSql130Parser(false);
IList<ParseError> errors;
var s = parser.Parse(new StringReader(script), out errors);
var visitor = new ProcedureVisitor();
s.Accept(visitor);
return visitor.Statements;
}
public ParsedFragment CreateFragment(string sql)
{
if (sql == null)
{
Logger.Error($"{nameof(sql)} is null");
throw new ArgumentNullException(nameof(sql));
}
IList <ParseError> parseErrors;
var fragment = _parser.Parse(new StringReader(sql), out parseErrors);
return(new ParsedFragment
{
Fragment = fragment,
ParseErrors = parseErrors
});
}
static void Main(string[] args)
{
TextReader rdr = new StreamReader(@"c:\ScriptDom\sampleproc.sql");
IList <ParseError> errors = null;
TSql130Parser parser = new TSql130Parser(true);
TSqlFragment tree = parser.Parse(rdr, out errors);
foreach (ParseError err in errors)
{
Console.WriteLine(err.Message);
}
tree.Accept(new MyVisitor());
rdr.Dispose();
}
private string StripCommentsFromSQL(string SQL)
{
// Later on, we'll use this method to strip comments when doing comparisons
TSql130Parser parser = new TSql130Parser(true);
IList <ParseError> errors;
var fragments = parser.Parse(new System.IO.StringReader(SQL), out errors);
// clear comments
string result = string.Join(
string.Empty,
fragments.ScriptTokenStream
.Where(x => x.TokenType != TSqlTokenType.MultilineComment)
.Where(x => x.TokenType != TSqlTokenType.SingleLineComment)
.Select(x => x.Text));
return(result);
}
static void Main(string[] args)
{
try
{
if (args.Length != 2 && args.Length != 1)
{
throw new ArgumentException("", nameof(args));
}
Console.OutputEncoding = Encoding.UTF8;
TSql130Parser parser = new TSql130Parser(false);
using (FileStream stream = File.Open(args[0], FileMode.Open, FileAccess.Read, FileShare.Read))
{
StreamReader reader = new StreamReader(stream);
TSqlFragment fragment = parser.Parse(reader, out IList <ParseError> errors);
PgTranslatorVisitor translator = new PgTranslatorVisitor();
fragment.Accept(translator);
if (args.Length == 2)
{
using (StreamWriter output = File.CreateText(args[1]))
{
output.Write(translator.Buffer.ToString());
}
}
else
{
Console.WriteLine(translator.Buffer.ToString());
}
}
Environment.ExitCode = 0;
}
catch (ArgumentException ex) when(ex.ParamName == nameof(args))
{
Console.WriteLine("Usage:");
Console.WriteLine();
Console.WriteLine("TSQL2PGSQL inputfile [outputfile]");
Console.WriteLine();
Console.WriteLine("Parameters:");
Console.WriteLine("\tinputfile - T-SQL script input file name");
Console.WriteLine("\toutputfile - PostgreSQL script output file name, if omitted output to console");
Environment.ExitCode = ex.HResult;
}
}
public void ParseSQL(string sql)
{
TSql130Parser sqlParser = new TSql130Parser(false);
IList <ParseError> parseErrors;
TSqlFragment result = sqlParser.Parse(new StringReader(sql), out parseErrors);
if (parseErrors.Count > 0)
{
throw new ArgumentException(string.Join(Environment.NewLine, parseErrors.Select(p => p.Message)));
}
TSqlScript SqlScript = result as TSqlScript;
if (SqlScript.Batches.Count > 1)
{
throw new NotSupportedException("Batches.Count > 1 not supported");
}
var batch = SqlScript.Batches.First();
if (batch.Statements.Count > 1)
{
throw new NotSupportedException("Statements.Count > 1 not supported");
}
TSqlStatement statement = batch.Statements.First();
if (statement is SelectStatement)
{
ParseSelectStatement(statement as SelectStatement);
}
else
{
throw new NotImplementedException($"Query statement of type {statement.GetType().Name} not supported");
}
}
/// <summary>
/// Parameterize is the static entry point into the tool
/// It will take an existing SqlCommand object's CommandText
/// Parse it, parameterize, add a Parameters collection to the SqlCommand Object
/// and reparse to validate the changes
/// </summary>
/// <param name="cmd">a reference to thw SqlCommand object to be parameterized</param>
/// <param name="reparse">Whether or not to reparse after completion. Default value is true</param>
/// <example>
/// <code>
/// using SqlCommandFilters;
/// class MyClass
/// {
/// public static int Main()
/// {
/// SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
/// sb.InitialCatalog = "AdventureWorks";
/// sb.IntegratedSecurity = true;
/// sb.DataSource = "SQLBOBT";
/// SqlConnection con = new SqlConnection(sb.ConnectionString);
/// con.Open();
/// SqlCommand cmd = new SqlCommand();
/// cmd.Connection = con;
/// // Pick one of the TestStatements and assign it to the CommandText property of the SqlCommand object
/// cmd.CommandText = TestStatements.inSubQueryStmt;
/// SqlCommandFilters.Parameters.Parameterize(ref cmd);
/// //NOTE: the default for reparse is true and is not required
/// //If you don't want to reparse for performance reasons
/// //call it this way instead
/// SqlCommandFilters.Parameters.Parameterize(ref cmd, false);
/// }
/// }
/// </code>
/// </example>
public static void Parameterize(ref SqlCommand cmd, bool reparse = true)
{
// Capture the current CommandText in a format that the parser can work with
TextReader rdr = new StringReader(cmd.CommandText);
// Setup to parse the T-SQL
IList <ParseError> errors = null;
//Using SQL 2016 parser
TSql130Parser parser = new TSql130Parser(true);
// Using SQL 2014 parser
//TSql120Parser parser = new TSql120Parser(true);
// Using SQL 2012 parser
//TSql110Parser parser = new TSql110Parser(true);
// Get the parse tree
TSqlFragment tree = parser.Parse(rdr, out errors);
// clean up resources
rdr.Dispose();
// if we could not parse the SQL we will throw an exception.Better here then on the server
if (errors.Count > 0)
{
Exception e = new Exception(string.Format("Parse Error after converstion on line {0}, column {1}: {2}", errors[0].Line, errors[0].Column, errors[0].Message));
throw e;
}
else
{
// Use the vistor pattern to examine the parse tree
TsqlBatchVisitor visit = new TsqlBatchVisitor(cmd, reparse);
// Now walk the tree
tree.Accept(visit);
}
}
static void Main(string[] args)
{
/*Lire fichier*/
File.Delete(@"C:\Users\wilou\source\repos\SqlShareParsing\SqlShareParsing\ressources\fichier_abstraction\test.xml");
string text = System.IO.File.ReadAllText(path);
string[] queries = text.Split("________________________________________");
/*Initialisation parser*/
var parser = new TSql130Parser(false);
/*Stocke les erreurs liés à la lecture du parser*/
IList <ParseError> errors;
/*Iniatilisation*/
string text2 = System.IO.File.ReadAllText(@"C:\Users\wilou\source\repos\SqlShareParsing\SqlShareParsing\ressources\view_script.txt");
Dictionary <String, List <String> > PhysicalTableList = new Dictionary <String, List <String> >();
string[] views = Regex.Split(text2, "________________________________________");
String sDir = @"C:\Users\wilou\Documents\stage_workspace\sqlshare_data_release1\data";
foreach (String view in views)
{
Regex rx = new Regex(@"\(\[.*\]\)",
RegexOptions.Compiled | RegexOptions.IgnoreCase);
Regex rx2 = new Regex(@"\[[^\[\]\(\)]*\]\.\[[^\[\]]*\]",
RegexOptions.Compiled | RegexOptions.IgnoreCase);
// Find matches.
String matchText = "";
String matchText2 = "";
MatchCollection matches = rx.Matches(view);
MatchCollection matches2 = rx2.Matches(view);
if (matches.Count > 0)
{
matchText = matches[0].Groups[0].Value;
}
if (matches2.Count > 0)
{
matchText2 = matches2[0].Groups[0].Value;
}
if (!PhysicalTableList.ContainsKey(matchText2))
{
using (StreamWriter sw = File.AppendText(@"C:\Users\wilou\source\repos\SqlShareParsing\SqlShareParsing\ressources\myf.txt"))
{
sw.WriteLine(matchText2 + " " + matchText.Replace("(", "").Replace(")", ""));
}
//Console.WriteLine("le match est : " + matchText.Replace("(", "").Replace(")", "") + ", pour la vue nommée : " + matchText2);
PhysicalTableList.Add(matchText2, new List <String>(matchText.Replace("(", "").Replace(")", "").Split(',')));
}
}
try
{
string firstLine;
foreach (string d in Directory.GetDirectories(sDir))
{
foreach (string f in Directory.GetFiles(d))
{
using (StreamReader reader = new StreamReader(f))
{
firstLine = reader.ReadLine() ?? "";
}
string[] listIdentifier = f.Split('\\');
//Avant-dernière value du chemin de dossier pour récupérer le nom d'utilisateur
int pos1 = listIdentifier.Count() - 2;
//dernière value ..
int pos2 = listIdentifier.Count() - 1;
//Console.WriteLine("[" + listIdentifier[pos1] + "].[" + listIdentifier[pos2] + "] = " + firstLine);
PhysicalTableList.Add("[" + listIdentifier[pos1] + "].[" + listIdentifier[pos2] + "]", new List <String>(firstLine.Split(',')));
}
}
}
catch (System.Exception excpt)
{
Console.WriteLine(excpt.Message);
}
MyVisitor myvisitor = new MyVisitor();
VisitorCommonTable myVisitorCommonTable = new VisitorCommonTable();
myvisitor.PhysicalTableList = PhysicalTableList;
int i = 0;
foreach (String query in queries)
{
myVisitorCommonTable.id_requete_courante = i;
var fragment = parser.Parse(new StringReader(query), out errors);
fragment.Accept(myVisitorCommonTable);
i = i + 1;
}
myvisitor.dictTableWith = myVisitorCommonTable.dict;
i = 0;
/*Pour chaque requête présent dans le fichier, on l'analyse*/
foreach (var query in queries)
{
/*Sépare chaque query et leur résultat par "______________" pour le fichier de sorti*/
myvisitor.save(query, i.ToString());
//Console.WriteLine(Environment.NewLine+"_____________________________"+query);
/*Enregistre la query en cours*/
TSqlFragment fragment = parser.Parse(new StringReader(query), out errors);
fragment.Accept(myvisitor);
myvisitor.add();
i++;
Console.WriteLine(i);
}
/*Remise à zéro du string pour les clauses présentes dans le FROM, pour éviter les doublons avec le WhereClause*/
Console.WriteLine("FIN");
Console.ReadLine();
myvisitor.Imprime();
}
/// <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;
}
}
public static TSqlFragment GetFragment(string script)
{
var parser = new TSql130Parser(false);
IList<ParseError> errors;
return parser.Parse(new StringReader(script), out errors);
}
public static List<TSqlStatement> GetStatements(string script, out IList<ParseError> errors)
{
var parser = new TSql130Parser(false);
//IList<ParseError> errors;
var s = parser.Parse(new StringReader(script), out errors);
var visitor = new StatementVisitor();
s.Accept(visitor);
return visitor.Statements;
}