public virtual void testCreateAndPredicate()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvoracle);
TExpression left = sqlParser.parseExpression("salary");
TExpression right = sqlParser.parseExpression("20");
TExpression left2 = sqlParser.parseExpression("location");
TExpression right2 = sqlParser.parseExpression("'NY'");
TExpression c1 = new TExpression(EExpressionType.simple_comparison_t, left, right, EComparisonType.greaterThanOrEqualTo);
//c1.ExpressionType = EExpressionType.simple_comparison_t;
//c1.ComparisonType = EComparisonType.greaterThanOrEqualTo;
//c1.LeftOperand = left;
//c1.RightOperand = right;
TExpression c2 = new TExpression(EExpressionType.simple_comparison_t, left2, right2, EComparisonType.equalsTo);
//c2.ExpressionType = EExpressionType.simple_comparison_t;
//c2.ComparisonType = EComparisonType.equalsTo;
//c2.LeftOperand = left2;
//c2.RightOperand = right2;
TExpression c3 = new TExpression(EExpressionType.logical_and_t, c1, c2);
//c3.ExpressionType = EExpressionType.logical_and_t;
//c3.LeftOperand = c1;
//c3.RightOperand = c2;
Assert.IsTrue(verifyScript(EDbVendor.dbvoracle, "salary >= 20 and location = 'NY'", c3.ToScript()));
}
public UnitTestDB2()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvdb2);
}
public UTClientNoetix()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvoracle);
}
static void ppInHtml(EDbVendor dbVendor, string inputsql, bool rtf, bool custom)
{
TGSqlParser parser = new TGSqlParser(dbVendor);
parser.sqltext = inputsql;
outputHtmlFormat(parser, rtf, custom);
}
static void ppInHtml(EDbVendor dbVendor, FileInfo sqlfile, bool rtf, bool custom)
{
TGSqlParser parser = new TGSqlParser(dbVendor);
parser.sqlfilename = sqlfile.FullName;
outputHtmlFormat(parser, rtf, custom);
}
public TGetTableColumn(EDbVendor pDBVendor)
{
dbVendor = pDBVendor;
sqlParser = new TGSqlParser(dbVendor);
//sqlParser.setMetaDatabase(new myMetaDB());
tablelist = new List <string>();
fieldlist = new List <string>();
indexList = new List <string>();
infoList = new List <TInfoRecord>();
spList = new Stack <TStoredProcedureSqlStatement>();
infos = new StringBuilder();
functionlist = new StringBuilder();
schemalist = new StringBuilder();
triggerlist = new StringBuilder();
sequencelist = new StringBuilder();
databaselist = new StringBuilder();
tableColumnList = new StringBuilder();
outList = new StringBuilder();
isConsole = true;
listStarColumn = false;
showTreeStructure = false;
showTableEffect = false;
showColumnLocation = false;
linkOrphanColumnToFirstTable = true;
showDatatype = false;
showIndex = false;
}
public UnitTestImpala()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvimpala);
}
public static bool TryParse(string sql, ISqlDialect dialect, string tablePrefix, IDictionary <string, object> parameters, out string query, out IEnumerable <string> messages)
{
try
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = sql;
int ret = sqlparser.parse();
if (ret == 0)
{
// Console.WriteLine("Great!");
var sqlParser = new SqlParser(sql, dialect, tablePrefix, parameters);
query = sqlParser.Evaluate();
messages = Array.Empty <string>();
return(true);
}
query = null;
messages = new[] { sqlparser.Errormessage };
return(false);
// Console.WriteLine("Syntax error detected: {0}",sqlparser.Errormessage);
}
catch (SqlParserException se)
{
query = null;
messages = new string[] { se.Message };
}
catch (Exception e)
{
query = null;
messages = new string[] { "Unexpected error: " + e.Message };
}
return(false);
}
public void testEmptyLines3()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "CREATE FUNCTION dbo.isoweek (@DATE datetime)\n" + "RETURNS INT\n" + "WITH EXECUTE AS caller\n" + "AS\n" + "BEGIN\n" + " DECLARE @ISOweek INT\n" + " \n" + " \n" + " SET @ISOweek= datepart(wk,@DATE)+1\n" + " -datepart(wk,CAST(datepart(yy,@DATE) AS CHAR(4))+'0104')\n" + " \n" + "--Special cases: Jan 1-3 may belong to the previous year\n" + " IF (@ISOweek=0)\n" + " SET @ISOweek=dbo.isoweek(CAST(datepart(yy,@DATE)-1\n" + " AS CHAR(4))+'12'+ CAST(24+datepart(DAY,@DATE) AS CHAR(2)))+1\n" + "--Special case: Dec 29-31 may belong to the next year\n" + " IF ((datepart(mm,@DATE)=12) AND\n" + " ((datepart(dd,@DATE)-datepart(dw,@DATE))>= 28))\n" + " SET @ISOweek=1\n" + " \n" + " \n" + " \n" + " \n" + " RETURN(@ISOweek)\n" + "END;\n" + "GO ";
sqlparser.parse();
option.emptyLines = TEmptyLinesOption.EloRemove;
option.insertBlankLineInBatchSqls = false;
string result = FormatterFactory.pp(sqlparser, option);
// Assert.IsTrue(result.trim().equalsIgnoreCase("CREATE FUNCTION dbo.Isoweek (@DATE DATETIME\n" +
// ") \n" +
// "RETURNS INT WITH EXECUTE AS caller \n" +
// "AS \n" +
// " BEGIN \n" +
// " DECLARE @ISOweek INT \n" +
// " SET @ISOweek= Datepart(wk,@DATE) + 1 - Datepart(wk,Cast(Datepart(yy,@DATE) AS CHAR(4))+'0104') \n" +
// "--Special cases: Jan 1-3 may belong to the previous year \n" +
// " IF ( @ISOweek = 0 ) SET @ISOweek=dbo.Isoweek(Cast(Datepart(yy,@DATE)-1 AS CHAR(4))+'12'+ Cast(24+Datepart(DAY,@DATE) AS CHAR(2))) + 1 \n" +
// "--Special case: Dec 29-31 may belong to the next year \n" +
// " IF ( ( Datepart(mm,@DATE) = 12 )\n" +
// " AND ( ( Datepart(dd,@DATE) - Datepart(dw,@DATE) ) >= 28 ) ) SET @ISOweek=1 \n" +
// " RETURN(@ISOweek) \n" +
// " END;\n" +
// "GO"));
Console.WriteLine("this is a bug, need to be fixed");
}
static void Main(string[] args)
{
int c = Environment.TickCount;
if (args.Length == 0)
{
Console.WriteLine("{0} scriptfile", "syntaxcheck");
return;
}
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.Sqlfilename = args[0];
int iRet = sqlparser.Parse();
if (iRet == 0)
{
foreach (TCustomSqlStatement stmt in sqlparser.SqlStatements)
{
printStmt(stmt);
}
}
else
{
Console.WriteLine("Syntax error found in input sql:");
Console.WriteLine(sqlparser.ErrorMessages);
}
}
public removeCondition(FileInfo sqlFile, EDbVendor vendor, LinkedHashMap <string, string> conditionMap)
{
TGSqlParser sqlparser = new TGSqlParser(vendor);
sqlparser.sqlfilename = sqlFile.FullName;
remove(sqlparser, conditionMap);
}
public void testMultiSetOperator()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT CAST( MULTISET( SELECT empno, empname FROM emp) AS emp_tab_t ) emptab\n" + "FROM DUAL;";
Assert.IsTrue(sqlparser.parse() == 0);
}
public void testKeepKeyword()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "create view rc_backup_set\n" + "as\n" + "select decode(keep_options, 0, 'no', 'yes') keep\n" + "from db, bs\n" + "where db.db_key = bs.db_key";
Assert.IsTrue(sqlparser.parse() == 0);
}
public void testTranslate()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "create view wm$all_locks_view as select t.table_owner, t.table_name,\n" + " decode(sys.lt_ctx_pkg.getltlockinfo(translate(t.info using char_cs),'row_lockmode'), 'e', 'exclusive', 's', 'shared') lock_mode,\n" + " sys.lt_ctx_pkg.getltlockinfo(translate(t.info using char_cs),'row_lockuser') lock_owner,\n" + " sys.lt_ctx_pkg.getltlockinfo(translate(t.info using char_cs),'row_lockstate') locking_state\n" + "from (select table_owner, table_name, info from\n" + " table( cast(sys.ltadm.get_lock_table() as wm$lock_table_type))) t\n" + "with read only";
Assert.IsTrue(sqlparser.parse() == 0);
}
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));
}
static void pp(EDbVendor dbVendor, string inputsql)
{
TGSqlParser parser = new TGSqlParser(dbVendor);
parser.sqltext = inputsql;
outputPlainFormat(parser);
}
public void testRenameTable()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "ALTER TABLE X RENAME TO Y;";
Assert.IsTrue(sqlparser.parse() == 0);
TAlterTableStatement alterTable = (TAlterTableStatement)sqlparser.sqlstatements.get(0);
Assert.IsTrue(alterTable.TableName.ToString().Equals("X", StringComparison.CurrentCultureIgnoreCase));
TAlterTableOption ato = alterTable.AlterTableOptionList.getAlterTableOption(0);
Assert.IsTrue(ato.OptionType == EAlterTableOptionType.RenameTable);
Assert.IsTrue(ato.NewTableName.ToString().Equals("Y", StringComparison.CurrentCultureIgnoreCase));
sqlparser.sqltext = "ALTER TABLE EVRST_HIST_NK..CLC_D_ACCOUNT_STATUS_HIER RENAME TO EVRST_HIST_NK..CLC_D_ACCOUNT_STATUS_HIER_ARCH";
Assert.IsTrue(sqlparser.parse() == 0);
alterTable = (TAlterTableStatement)sqlparser.sqlstatements.get(0);
Assert.IsTrue(alterTable.TableName.ToString().Equals("EVRST_HIST_NK..CLC_D_ACCOUNT_STATUS_HIER", StringComparison.CurrentCultureIgnoreCase));
ato = alterTable.AlterTableOptionList.getAlterTableOption(0);
Assert.IsTrue(ato.OptionType == EAlterTableOptionType.RenameTable);
Assert.IsTrue(ato.NewTableName.ToString().Equals("EVRST_HIST_NK..CLC_D_ACCOUNT_STATUS_HIER_ARCH", StringComparison.CurrentCultureIgnoreCase));
}
static void pp(EDbVendor dbVendor, FileInfo sqlfile)
{
TGSqlParser parser = new TGSqlParser(dbVendor);
parser.sqlfilename = sqlfile.FullName;
outputPlainFormat(parser);
}
public UnitTestMDX()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvmdx);
}
public UnitTestNetezza()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvnetezza);
}
public void TestMethodDBVendor()
{
TGSqlParser sqlparser = null;
int ret, success = 0, total = 0;
string dbstr = "";
foreach (EDbVendor dbVendor in (EDbVendor[])Enum.GetValues(typeof(EDbVendor)))
{
try
{
if ((dbVendor != EDbVendor.dbvaccess) && (dbVendor != EDbVendor.dbvansi) &&
(dbVendor != EDbVendor.dbvfirebird) && (dbVendor != EDbVendor.dbvgeneric) && (dbVendor != EDbVendor.dbvodbc) && (dbVendor != EDbVendor.dbvmdx))
{
total++;
sqlparser = new TGSqlParser(dbVendor);
sqlparser.sqltext = "select 2 from t";
ret = sqlparser.parse();
dbstr = dbstr + dbVendor.ToString() + ",";
success++;
Console.WriteLine("{0}", dbVendor.ToString());
}
}
catch
{
Console.WriteLine("{0}(not supported)", dbVendor.ToString());
}
}
Console.WriteLine("Version:{0}, Release date:{1}, Full version = {2}, \nDb: {3}/{4}, {5}", TBaseType.versionId, TBaseType.releaseDate, TBaseType.full_edition, success, total, dbstr);
}
public UnitTestSnowflake()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvsnowflake);
}
public UnitTestInformix()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvinformix);
}
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 void TestClusterBy()
{
TGSqlParser sqlParser = new TGSqlParser(EDbVendor.dbvsnowflake);
sqlParser.sqltext = @"create table ""TestSchema"".""TestTable""(
col1 int,
""col2"" int
)
cluster
by
(
""col2""
,
col1
)";
Assert.IsTrue(sqlParser.parse() == 0);
Assert.IsTrue(sqlParser.sqlstatements[0].sqlstatementtype == ESqlStatementType.sstcreatetable);
TCreateTableSqlStatement sql = (TCreateTableSqlStatement)sqlParser.sqlstatements[0];
Assert.IsTrue(sql.TableOptions.Count == 1);
TCreateTableOption tableOption = sql.TableOptions[0];
Assert.IsTrue(tableOption.CreateTableOptionType == ECreateTableOption.etoClusterBy);
Assert.IsTrue(tableOption.ExpressionList.Count == 2);
// Console.WriteLine(tableOption.ExpressionList.Count);
Assert.IsTrue(tableOption.ExpressionList[0].ToString().Equals("\"col2\"", StringComparison.CurrentCultureIgnoreCase));
}
public UnitTestSybase()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvsybase);
}
private List <string> GetColumnAlia(string exp)
{
//string exp = "select itemname as textcol_1,ordercycle as longcol_1, name myname from itemdata where itemno= p_form0000000083_m.userdefine_1";
List <string> list = new List <string>();
TGSqlParser sqlparser = new TGSqlParser(TDbVendor.DbVMssql);
sqlparser.SqlText.Text = exp;
int ret = sqlparser.Parse();
if (ret == 0)
{
TSelectSqlStatement sqlstmt;
sqlstmt = (TSelectSqlStatement)sqlparser.SqlStatements[0];
//sqlstmt.Fields
foreach (TLzField field in sqlstmt.Fields)
{
//Console.WriteLine("field alia: " + field.FieldAlias);
if (string.IsNullOrEmpty(field.FieldAlias))
{
list.Add(field.Name);
}
else
{
list.Add(field.FieldAlias);
}
}
}
return(list);
}
public UnitTestPostgreSQL()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvpostgresql);
}
public UnitTestTeradata()
{
//
//TODO: 在此处添加构造函数逻辑
//
parser = new TGSqlParser(EDbVendor.dbvteradata);
}
public void testSelect_keywords_alignOption()
{
GFmtOpt option = GFmtOptFactory.newInstance(this.GetType().Name + "." + new System.Diagnostics.StackTrace().GetFrame(0).GetMethod().Name);
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = "DELETE FROM job_history jh \n" + "WHERE employee_id = (SELECT employee_id \n" + "FROM employee e \n" + "WHERE jh.employee_id = e.employee_id \n" + "AND start_date = (SELECT Min(start_date) \n" + "FROM job_history jh \n" + "WHERE jh.employee_id = e.employee_id) \n" + "AND 5 > (SELECT Count( * ) \n" + "FROM job_history jh \n" + "WHERE jh.employee_id = e.employee_id \n" + "GROUP BY employee_id \n" + "HAVING Count( * ) >= 4)); ";
sqlparser.parse();
option.selectKeywordsAlignOption = TAlignOption.AloRight;
string result = FormatterFactory.pp(sqlparser, option);
//Console.WriteLine(result);
//Console.WriteLine("DELETE FROM job_history jh\n" + " WHERE employee_id = (SELECT employee_id\n" + " FROM employee e\n" + " WHERE jh.employee_id = e.employee_id\n" + " AND start_date = (SELECT Min(start_date)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id)\n" + " AND 5 > ( SELECT Count(*)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id\n" + " GROUP BY employee_id\n" + " HAVING Count(*) >= 4));");
// Assert.IsTrue(result.Trim().Equals("DELETE FROM job_history jh\n" + " WHERE employee_id = (SELECT employee_id\n" + " FROM employee e\n" + " WHERE jh.employee_id = e.employee_id\n" + " AND start_date = (SELECT Min(start_date)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id)\n" + " AND 5 > ( SELECT Count(*)\n" + " FROM job_history jh\n" + " WHERE jh.employee_id = e.employee_id\n" + " GROUP BY employee_id\n" + " HAVING Count(*) >= 4));", StringComparison.OrdinalIgnoreCase));
// Assert.IsTrue(result.trim().equalsIgnoreCase("DELETE FROM job_history jh\n" +
// " WHERE employee_id = (SELECT employee_id\n" +
// " FROM employee e\n" +
// " WHERE jh.employee_id = e.employee_id\n" +
// " AND start_date = (SELECT Min(start_date)\n" +
// " FROM job_history jh\n" +
// " WHERE jh.employee_id = e.employee_id)\n" +
// " AND 5 > ( SELECT Count(*)\n" +
// " FROM job_history jh\n" +
// " WHERE jh.employee_id = e.employee_id\n" +
// " GROUP BY employee_id HAVING Count(*) >= 4));"));
// System.out.println(result);
}
private void SetParserSyntax(string syntax)
{
try
{
switch (syntax)
{
case "MSSQL":
case "MSSQLCE":
case "VISTADB":
if (parser.DbVendor != TDbVendor.DbVMssql)
{
parser = new TGSqlParser(TDbVendor.DbVMssql);
}
break;
case "POSTGRESQL":
case "ORACLE":
if (parser.DbVendor != TDbVendor.DbVOracle)
{
parser = new TGSqlParser(TDbVendor.DbVOracle);
}
break;
case "MYSQL":
if (parser.DbVendor != TDbVendor.DbVMysql)
{
parser = new TGSqlParser(TDbVendor.DbVMysql);
}
break;
case "SYBASE":
if (parser.DbVendor != TDbVendor.DbVSybase)
{
parser = new TGSqlParser(TDbVendor.DbVSybase);
}
break;
case "ACCESS":
if (parser.DbVendor != TDbVendor.DbVAccess)
{
parser = new TGSqlParser(TDbVendor.DbVAccess);
}
break;
default:
parser = new TGSqlParser(TDbVendor.DbVGeneric);
break;
}
}
catch { }
}
public GeneralSQLParser()
{
TDbVendor db = TDbVendor.DbVMssql;
sqlparser = new TGSqlParser(db);
}
private void analyseRequest()
{
Cursor.Current = Cursors.WaitCursor;
//for more format options, please check document
lzbasetype.gFmtOpt.Select_Columnlist_Style = TAlignStyle.asStacked;
lzbasetype.gFmtOpt.Select_Columnlist_Comma = TLinefeedsCommaOption.lfAfterComma;
lzbasetype.gFmtOpt.SelectItemInNewLine = false;
lzbasetype.gFmtOpt.AlignAliasInSelectList = true;
lzbasetype.gFmtOpt.TreatDistinctAsVirtualColumn = false;
//setup more format options ...
lzbasetype.gFmtOpt.linenumber_enabled = false;
lzbasetype.gFmtOpt.HighlightingFontname = "Courier New";
lzbasetype.gFmtOpt.HighlightingFontsize = 10;
//for other elements you want to customize, please check document
lzbasetype.gFmtOpt.HighlightingElements[(int)TLzHighlightingElement.sfkIdentifer].SetForegroundInRGB("#008000");
lzbasetype.gFmtOpt.HighlightingElements[(int)TLzHighlightingElement.sfkIdentifer].StyleBold = true;
lzbasetype.gFmtOpt.HighlightingElements[(int)TLzHighlightingElement.sfkIdentifer].StyleItalic = false;
lzbasetype.gFmtOpt.HighlightingElements[(int)TLzHighlightingElement.sfkIdentifer].StyleStrikeout = false;
lzbasetype.gFmtOpt.HighlightingElements[(int)TLzHighlightingElement.sfkIdentifer].StyleUnderline = false;
TGSqlParser parser = new TGSqlParser(TDbVendor.DbVOracle);
parser.SqlText.Text = rtbEditor.Text;
int i = parser.PrettyPrint();
if (i == 0)
{
rtbEditor.Rtf = parser.ToRTF(TOutputFmt.ofrtf);
}
Cursor.Current = Cursors.Default;
}
