static void toXML()
{
EDbVendor dbVendor = EDbVendor.dbvoracle;
Console.WriteLine("Selected SQL dialect: " + dbVendor.ToString());
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.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
ORDER BY
e.first_name,
e.last_name; ";
int ret = sqlparser.parse();
if (ret == 0)
{
xmlVisitor xv2 = new xmlVisitor();
xv2.run(sqlparser);
Console.WriteLine(xv2.FormattedXml);
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
public static void doSearch(String filename)
{
//long t = DateTime.Now.Millisecond;
EDbVendor dbVendor = EDbVendor.dbvmssql;
Console.WriteLine("Selected SQL dialect: " + dbVendor.ToString());
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqlfilename = filename;
int ret = sqlparser.parse();
if (ret == 0)
{
functionVisitor fv = new functionVisitor();
for (int i = 0; i < sqlparser.sqlstatements.size(); i++)
{
TCustomSqlStatement sqlStatement = sqlparser.sqlstatements.get(i);
Console.WriteLine(sqlStatement.sqlstatementtype);
sqlStatement.acceptChildren(fv);
}
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
//Console.WriteLine("Time Escaped: " + (DateTime.Now.Millisecond - t));
}
public static void doIt(String sqlFilename)
{
EDbVendor dbVendor = EDbVendor.dbvmssql;
Console.WriteLine("Selected SQL dialect: " + dbVendor.ToString());
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqlfilename = sqlFilename;
int ret = sqlparser.parse();
if (ret == 0)
{
for (int i = 0;i < sqlparser.sqlstatements.size();i++)
{
iterateStmt(sqlparser.sqlstatements.get(i));
}
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
static void toXML(String sqlFilename, EDbVendor dbVendor)
{
Console.WriteLine("Selected SQL dialect: " + dbVendor.ToString());
TGSqlParser sqlparser = new TGSqlParser(dbVendor);
String inputFile = sqlFilename;
sqlparser.sqlfilename = inputFile;
string xmlFile = inputFile + ".xml";
int ret = sqlparser.parse();
if (ret == 0)
{
xmlVisitor xv2 = new xmlVisitor();
xv2.run(sqlparser);
xv2.writeToFile(xmlFile);
Console.WriteLine(xmlFile + " was generated!");
}
else
{
Console.WriteLine(sqlparser.Errormessage);
}
}
//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);
}
}
