public static void Main_dlineageRelation(string[] args)
{
if (args.Length < 1)
{
Console.WriteLine("Usage: DlineageRelation [/f <path_to_sql_file>] [/d <path_to_directory_includes_sql_files>] [/t <database type>] [/o <output file path>]");
Console.WriteLine("/f: Option, specify the sql file path to analyze dlineage relation.");
Console.WriteLine("/d: Option, specify the sql directory path to analyze dlineage relation.");
Console.WriteLine("/t: Option, set the database type. Support oracle, mysql, mssql, db2, netezza, teradata, informix, sybase, postgresql, hive, greenplum and redshift, the default type is oracle");
Console.WriteLine("/o: Option, write the output stream to the specified file.");
return;
}
FileInfo sqlFiles = null;
IList <string> argList = new List <string>(args);
if (argList.IndexOf("/f") != -1 && argList.Count > argList.IndexOf("/f") + 1)
{
sqlFiles = new FileInfo(args[argList.IndexOf("/f") + 1]);
if (!sqlFiles.Exists || sqlFiles.Attributes == FileAttributes.Directory)
{
Console.WriteLine(sqlFiles + " is not a valid file.");
return;
}
}
else if (argList.IndexOf("/d") != -1 && argList.Count > argList.IndexOf("/d") + 1)
{
sqlFiles = new FileInfo(args[argList.IndexOf("/d") + 1]);
if (sqlFiles.Attributes != FileAttributes.Directory)
{
Console.WriteLine(sqlFiles + " is not a valid directory.");
return;
}
}
else
{
Console.WriteLine("Please specify a sql file path or directory path to analyze dlineage.");
return;
}
EDbVendor vendor = EDbVendor.dbvoracle;
int index = argList.IndexOf("/t");
if (index != -1 && args.Length > index + 1)
{
if (args[index + 1].Equals("mssql", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvmssql;
}
else if (args[index + 1].Equals("db2", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvdb2;
}
else if (args[index + 1].Equals("mysql", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvmysql;
}
else if (args[index + 1].Equals("netezza", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvnetezza;
}
else if (args[index + 1].Equals("teradata", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvteradata;
}
else if (args[index + 1].Equals("oracle", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvoracle;
}
else if (args[index + 1].Equals("informix", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvinformix;
}
else if (args[index + 1].Equals("sybase", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvsybase;
}
else if (args[index + 1].Equals("postgresql", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvpostgresql;
}
else if (args[index + 1].Equals("hive", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvhive;
}
else if (args[index + 1].Equals("greenplum", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvgreenplum;
}
else if (args[index + 1].Equals("redshift", StringComparison.CurrentCultureIgnoreCase))
{
vendor = EDbVendor.dbvredshift;
}
}
string outputFile = null;
index = argList.IndexOf("/o");
if (index != -1 && args.Length > index + 1)
{
outputFile = args[index + 1];
}
System.IO.FileStream writer = null;
StreamWriter sw = null;
if (!string.ReferenceEquals(outputFile, null))
{
try
{
writer = new System.IO.FileStream(outputFile, System.IO.FileMode.Create, System.IO.FileAccess.Write);
sw = new StreamWriter(writer);
Console.SetOut(sw);
}
catch (FileNotFoundException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
DlineageRelation relation = new DlineageRelation();
Dlineage dlineage = new Dlineage(sqlFiles, vendor, false, false);
StringBuilder errorBuffer = new StringBuilder();
columnImpactResult impactResult = relation.generateColumnImpact(dlineage, errorBuffer);
string result = relation.generateDlineageRelation(dlineage, impactResult);
if (!string.ReferenceEquals(result, null))
{
Console.WriteLine(result);
if (writer != null)
{
Console.Error.WriteLine(result);
}
}
if (errorBuffer.Length > 0)
{
Console.Error.WriteLine("Error log:\n" + errorBuffer.ToString());
}
try
{
if (sw != null && writer != null)
{
sw.Close();
writer.Close();
}
}
catch (IOException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
public HtmlOutputConfig(GFmtOpt option, EDbVendor dbVendor)
{
render = new HtmlRenderUtil(this, option, dbVendor);
}
public removeColumn(EDbVendor vendor, string sqltext)
{
sqlparser = new TGSqlParser(vendor);
sqlparser.sqltext = sqltext;
}
public TAntiSQLInjection(EDbVendor dbVendor)
{
this.sqlParser = new TGSqlParser(dbVendor);
this.enabledStatements = new List <ESqlStatementType>();
this.enabledStatements.Add(ESqlStatementType.sstselect);
}
public ProcedureRelationScanner(Tuple <procedureImpactResult, IList <ProcedureMetaData> > procedures, EDbVendor vendor, string sqlText, bool strict, string database)
{
this.strict = strict;
this.vendor = vendor;
this.database = database;
this.procedures = procedures;
TGSqlParser parser = new TGSqlParser(vendor);
parser.sqltext = sqlText;
checkDDL(parser);
}
static void Main(string[] args)
{
if (args.Length == 0)
{
Console.WriteLine("Usage: formatsql [/f <script file>] [/t <database type>] [/o <output file path>] [/p] [/h] [/r] [/c]");
Console.WriteLine("/f: Option, specify the sql script file path.");
Console.WriteLine("/t: Option, set the database type. Support oracle, mysql, mssql and db2, the default type is oracle.");
Console.WriteLine("/o: Option, write the output stream to the specified file.");
Console.WriteLine("/p: Option, format sql as plain style.");
Console.WriteLine("/h: Option, format sql as HTML style.");
Console.WriteLine("/r: Option, format sql as RTF style.");
Console.WriteLine("/c: option, use the custom format color and font setting.");
Console.ReadLine();
return;
}
string sqltext = @"SELECT e.last_name AS name,
e.commission_pct comm,
e.salary * 12 ""Annual Salary""
FROM scott.employees AS e
WHERE e.salary > 1000 or 1=1
ORDER BY
e.first_name,
e.last_name;";
EDbVendor vendor = Common.GetEDbVendor(args);
List <string> argList = new List <string>(args);
int index = argList.IndexOf("/f");
FileInfo file = null;
if (index != -1 && args.Length > index + 1)
{
file = new FileInfo(args[index + 1]);
}
string outputFile = null;
index = argList.IndexOf("/o");
if (index != -1 && args.Length > index + 1)
{
outputFile = args[index + 1];
}
System.IO.StreamWriter writer = null;
if (!string.ReferenceEquals(outputFile, null))
{
try
{
writer = new StreamWriter(outputFile);
Console.SetOut(writer);
}
catch (FileNotFoundException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
bool html = argList.IndexOf("/h") != -1;
bool rtf = argList.IndexOf("/r") != -1;
bool custom = argList.IndexOf("/c") != -1;
if (html || rtf)
{
if (file != null)
{
ppInHtml(vendor, file, rtf, custom);
}
else
{
ppInHtml(vendor, sqltext, rtf, custom);
}
}
else
{
if (file != null)
{
pp(vendor, file);
}
else
{
pp(vendor, sqltext);
}
}
try
{
if (writer != null)
{
writer.Close();
}
}
catch (IOException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
public static void Main(string[] args)
{
EDbVendor dbVendor = Common.GetEDbVendor(args);
if (args.Length < 2)
{
displayInitInformation();
return;
}
IList <string> argList = new List <string>(args);
FileInfo sqlFile = null;
FileInfo outputFile = null;
if (argList.IndexOf("/f") != -1 && argList.Count > argList.IndexOf("/f") + 1)
{
sqlFile = new FileInfo(args[argList.IndexOf("/f") + 1]);
if (!sqlFile.Exists)
{
Console.WriteLine(sqlFile + " is not a valid file.");
return;
}
}
if (argList.IndexOf("/d") != -1 && argList.Count > argList.IndexOf("/d") + 1)
{
sqlFile = new FileInfo(args[argList.IndexOf("/d") + 1]);
if (!sqlFile.Attributes.HasFlag(FileAttributes.Directory))
{
Console.WriteLine(sqlFile + " is not a valid directory.");
return;
}
}
if (sqlFile == null)
{
displayInitInformation();
return;
}
if (argList.IndexOf("/o") != -1 && argList.Count > argList.IndexOf("/o") + 1)
{
outputFile = new FileInfo(args[argList.IndexOf("/o") + 1]);
if (!outputFile.Exists)
{
if (!outputFile.Directory.Exists)
{
Directory.CreateDirectory(outputFile.Directory.FullName);
}
}
}
System.IO.FileStream writer = null;
StreamWriter sw = null;
if (outputFile != null)
{
try
{
writer = new System.IO.FileStream(outputFile.FullName, System.IO.FileMode.Create, System.IO.FileAccess.Write);
sw = new StreamWriter(writer);
Console.SetOut(sw);
}
catch (FileNotFoundException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
TGetTableColumn getTableColumn = new TGetTableColumn(dbVendor);
getTableColumn.showDetail = false;
getTableColumn.showSummary = true;
getTableColumn.showTreeStructure = false;
getTableColumn.showBySQLClause = false;
getTableColumn.showJoin = false;
getTableColumn.showColumnLocation = true;
getTableColumn.linkOrphanColumnToFirstTable = false;
getTableColumn.showIndex = true;
getTableColumn.showDatatype = true;
getTableColumn.showTableEffect = false;
if (argList.IndexOf("/showDetail") != -1)
{
getTableColumn.showSummary = false;
getTableColumn.showDetail = true;
}
else if (argList.IndexOf("/showTreeStructure") != -1)
{
getTableColumn.showSummary = false;
getTableColumn.showTreeStructure = true;
}
else if (argList.IndexOf("/showBySQLClause") != -1)
{
getTableColumn.showSummary = false;
getTableColumn.showBySQLClause = true;
}
else if (argList.IndexOf("/showJoin") != -1)
{
getTableColumn.showSummary = false;
getTableColumn.showJoin = true;
}
getTableColumn.runFile(sqlFile);
try
{
if (sw != null && writer != null)
{
sw.Close();
writer.Close();
}
else
{
Console.ReadLine();
}
}
catch (IOException e)
{
Console.WriteLine(e.ToString());
Console.Write(e.StackTrace);
}
}
//public static List<NestedStmtWhereClauses> StatementWhereClauses = new List<NestedStmtWhereClauses>();
public static void ParseWorkload(String sqlFilename)
{
EDbVendor dbVendor = EDbVendor.dbvmssql;
Console.WriteLine("\n");
Console.WriteLine("Selected SQL dialect: " + ((dbVendor.ToString() == "dbvmssql") ? "Transact SQL for Microsoft SQL Server" : "SQL for Oracle."));
Console.WriteLine("\n");
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqlfilename = sqlFilename;
int ret = sqlparser.parse();
if (ret == 0)
{
Console.WriteLine("###################### Parsing Started : " + sqlFilename);
// Print output Headers
var consoletable = new ConsoleTable("Statement Number", "Statement Type", "Parse OK ?", "Query Text [Truncated]");
for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
{
ESqlStatementType sqlStatementType = sqlparser.sqlstatements.get(i).sqlstatementtype; // Get the statementtype of the sql qwuery
if (sqlStatementType == ESqlStatementType.sstselect || sqlStatementType == ESqlStatementType.sstupdate) // Take the statement in dictionary only if it is a DML(SELECT/UPDATE) statement
{
// We have taken iterate statement inside if block since parsong for "Use Database", "Go", "Set variable values", etc is not useful for our applicaiton scope.
iterateStmt(sqlparser.sqlstatements.get(i));
// Create a new query object
SQLQueryStmt query = new SQLQueryStmt();
// get the query text of the sql statement from the workload
query.QueryText = sqlparser.sqlstatements.get(i).String;
// check if this statement appeared for the first time and add it to the dictionary, else increment the count of already added statement from the dictionary.
if (Utilities.DictWorkloadQueries.Where(qry => qry.Value.QueryText == query.QueryText).Any()) // If the statement is occuring again.
{
Guid guid = Utilities.DictWorkloadQueries.FirstOrDefault(qry => qry.Value.QueryText == query.QueryText).Key; // Get the queryId of the SQL already in the Dictionary.
Utilities.DictWorkloadQueries[guid].NumOfOccurences += 1; // Increment the count of occurence.
}
else // If the statement occurs for the first time.
{
query.QueryId = Guid.NewGuid(); // New key for query
Utilities.DictWorkloadQueries.Add(query.QueryId, query); // Add the query to Dictionary.
Utilities.DictWorkloadQueries[query.QueryId].NumOfOccurences = 1;
}
consoletable.AddRow("Parsed Statement[" + i + "]", (sqlStatementType == ESqlStatementType.sstselect)?"SELECT": "OTHER", "Successful", query.QueryText.Replace(System.Environment.NewLine, " ").Substring(0, 35) + "...");
}
}
consoletable.Write(Format.MarkDown);
Console.WriteLine();
Console.WriteLine("###################### " + sqlFilename + " Parsing Complete ####################");
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
public ViewParser(IDictionary <TableMetaData, IList <ColumnMetaData> > tableColumns, EDbVendor vendor, string sqlText, bool strict, string database)
{
this.database = database;
this.strict = strict;
this.vendor = vendor;
this.tableColumns = tableColumns;
TGSqlParser parser = new TGSqlParser(vendor);
parser.sqltext = sqlText;
parser.MetaDatabase = new MetaDB(tableColumns, strict);
checkDDL(parser);
}
internal static EDbVendor GetEDbVendor(string[] args, EDbVendor defaultVendor)
{
List <string> argList = new List <string>(args);
int index = argList.IndexOf("/t");
EDbVendor dbVendor = defaultVendor;
if (index != -1 && args.Length > index + 1)
{
if (args[index + 1].Equals("mssql", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvmssql;
}
else if (args[index + 1].Equals("db2", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvdb2;
}
else if (args[index + 1].Equals("mysql", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvmysql;
}
else if (args[index + 1].Equals("netezza", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvnetezza;
}
else if (args[index + 1].Equals("teradata", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvteradata;
}
else if (args[index + 1].Equals("oracle", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvoracle;
}
else if (args[index + 1].Equals("informix", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvinformix;
}
else if (args[index + 1].Equals("sybase", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvsybase;
}
else if (args[index + 1].Equals("postgresql", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvpostgresql;
}
else if (args[index + 1].Equals("hive", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvhive;
}
else if (args[index + 1].Equals("greenplum", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvgreenplum;
}
else if (args[index + 1].Equals("redshift", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvredshift;
}
else if (args[index + 1].Equals("mdx", StringComparison.CurrentCultureIgnoreCase))
{
dbVendor = EDbVendor.dbvmdx;
}
}
return(dbVendor);
}
public static EDbVendor GetEDbVendor(string[] args)
{
EDbVendor dbVendor = EDbVendor.dbvoracle;
return(GetEDbVendor(args, dbVendor));
}
public HtmlRenderUtil(HtmlOutputConfig config, GFmtOpt option, EDbVendor dbVendor)
{
this.config = config;
this.option = option;
this.dbVendor = dbVendor;
}
