private Boolean isInjected_union_set()
{
Boolean ret = false;
if (!this.e_union_set)
{
return(false);
}
if (this.sqlParser.SqlStatements.Count() == 0)
{
return(ret);
}
TCustomSqlStatement stmt = this.sqlParser.SqlStatements[0];
if (stmt.SqlStatementType != TSqlStatementType.sstSelect)
{
return(ret);
}
TSelectSqlStatement select = (TSelectSqlStatement)stmt;
if (select.SelectSetType != TSelectSetType.sltNone)
{
this.getSqlInjections().Add(new TSQLInjection(ESQLInjectionType.union_set));
ret = true;
}
return(ret);
}
public virtual void testRemoveTableAndAddJoinClause()
{
parser.sqltext = "SELECT * FROM t1,t2 where t1.f1=t2.f2";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoinList joinList = select.joins;
// let's remove t2 and where clause
joinList.removeJoin(1);
TJoinItem joinItem = new TJoinItem();
joinList.getJoin(0).JoinItems.addJoinItem(joinItem);
joinItem.JoinType = EJoinType.left;
TTable joinTable = new TTable();
joinItem.Table = joinTable;
joinTable.TableName = parser.parseObjectName("t2");
joinItem.OnCondition = parser.parseExpression("t1.f1=t2.f2");
// remove where clause
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM t1\n" +
" LEFT JOIN t2\n" +
" ON t1.f1 = t2.f2"
));
}
public virtual void testAddORConditionInWhereClause()
{
parser.sqltext = "SELECT * FROM TABLE_X where f > 0";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression1 = parser.parseExpression("c1>1");
TExpression expression2 = new TExpression();
expression2.ExpressionType = EExpressionType.logical_or_t;
TExpression parensExpr = new TExpression();
parensExpr.ExpressionType = EExpressionType.parenthesis_t;
parensExpr.LeftOperand = select.WhereClause.Condition;
expression2.LeftOperand = parensExpr;
expression2.RightOperand = expression1;
select.WhereClause.Condition = expression2;
//System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE ( f > 0 )\n" +
" OR c1 > 1"
));
}
public virtual void testAddResultColumnInSelectList()
{
parser.sqltext = "SELECT A as A_Alias, B AS B_Alias FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TResultColumnList columns = select.ResultColumnList;
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = parser.parseExpression("d");
columns.addResultColumn(resultColumn);
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = parser.parseObjectName("d_alias");
aliasClause.HasAs = true;
resultColumn.AliasClause = aliasClause;
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT a AS a_alias,\n" +
" b AS b_alias,\n" +
" d AS d_alias\n" +
"FROM table_x"
));
}
public virtual void testModifySelectList()
{
parser.sqltext = "select t1.f1, t2.f2 as f2 from table1 t1 left join table2 t2 on t1.f1 = t2.f2 ";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ResultColumnList.removeResultColumn(1);
select.ResultColumnList.removeResultColumn(0);
TResultColumn resultColumn1 = new TResultColumn();
resultColumn1.Expr = parser.parseExpression("t1.f3");
TAliasClause aliasClause1 = new TAliasClause();
aliasClause1.AliasName = parser.parseObjectName("f1");
aliasClause1.HasAs = true;
resultColumn1.AliasClause = aliasClause1;
select.ResultColumnList.addResultColumn(resultColumn1);
TResultColumn resultColumn2 = new TResultColumn();
resultColumn2.Expr = parser.parseExpression("t2.f3");
select.ResultColumnList.addResultColumn(resultColumn2);
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT t1.f3 AS f1,\n" +
" t2.f3\n" +
"FROM table1 t1\n" +
" LEFT JOIN table2 t2\n" +
" ON t1.f1 = t2.f2"
));
}
public void testValues2()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum);
sqlparser.sqltext = "SELECT f.* FROM films f, (VALUES('MGM', 'Horror'), ('UA', \n" + "'Sci-Fi')) AS t (studio, kind) WHERE f.studio = t.studio AND \n" + "f.kind = t.kind;";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
Assert.IsTrue(select.joins.size() == 2);
TTable table2 = select.joins.getJoin(1).Table;
Assert.IsTrue(table2.TableType == ETableSource.subquery);
Assert.IsTrue(table2.AliasClause.AliasName.ToString().Equals("t", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(table2.AliasClause.Columns.getObjectName(0).ToString().Equals("studio", StringComparison.CurrentCultureIgnoreCase));
select = table2.Subquery;
TValueClause valueClause = select.ValueClause;
Assert.IsTrue(valueClause.ValueRows.size() == 2);
TValueRowItem rowItem = valueClause.ValueRows.getValueRowItem(0);
Assert.IsTrue(rowItem.ExprList.getExpression(0).ToString().Equals("'MGM'", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(rowItem.ExprList.getExpression(1).ToString().Equals("'Horror'", StringComparison.CurrentCultureIgnoreCase));
rowItem = valueClause.ValueRows.getValueRowItem(1);
Assert.IsTrue(rowItem.ExprList.getExpression(0).ToString().Equals("'UA'", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(rowItem.ExprList.getExpression(1).ToString().Equals("'Sci-Fi'", StringComparison.CurrentCultureIgnoreCase));
}
private void AnalyzeSelectStmt(TSelectSqlStatement pSqlstmt)
{
SqlSelect q = new SqlSelect();
// where clause this query is rule Select
if (pSqlstmt.WhereClause != null)
{
Console.WriteLine(pSqlstmt.WhereClauseText);
q.WhereClause = pSqlstmt.WhereClauseText;
}
//column here, when column != *, indep project
List<string> names = new List<string>();
foreach (TLzField fld in pSqlstmt.Fields)
{
var lcstr ="\n\tFullname:" + fld.FieldFullname;
lcstr = lcstr + "\n\tPrefix:" + fld.FieldPrefix;
lcstr = lcstr + "\tColumn:" + fld.FieldName;
lcstr = lcstr + "\talias:" + fld.FieldAlias;
Console.WriteLine(lcstr);
names.Add(fld.FieldName);
}
q.SelectClause = names;
// join
names = new List<string>();
foreach (var table in pSqlstmt.JoinTables)
{
Console.WriteLine(table.JoinTable.TableName);
Console.WriteLine(table.JoinItems.Count());
names.Add(table.JoinTable.TableName);
}
q.SelectClause = names;
switch (pSqlstmt.SelectSetType)
{
case TSelectSetType.sltNone:
break;
case TSelectSetType.sltUnion:
// indep union rule
Console.WriteLine(pSqlstmt.SelectClauseText);
break;
case TSelectSetType.sltUnionAll:
break;
case TSelectSetType.sltMinus:
break;
case TSelectSetType.sltIntersect:
break;
case TSelectSetType.sltIntersectAll:
break;
case TSelectSetType.sltExcept:
// indep negation rule
Console.WriteLine(pSqlstmt.FromClauseText);
break;
case TSelectSetType.sltExceptAll:
break;
default:
throw new ArgumentOutOfRangeException();
}
}
internal static void processSelect(TSelectSqlStatement select)
{
TResultColumnList columns = select.ResultColumnList;
for (int i = 0; i < columns.size(); i++)
{
printColumns(columns.getResultColumn(i), select);
}
}
public virtual void testRemoveAdditionalParenthesisOfSubquery()
{
TSelectSqlStatement select = null, subquery = null;
parser.sqltext = "select * from ((select * from some_table where some_column < ?)) some_view where a_column = something";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
subquery = select.tables.getTable(0).Subquery;
subquery.ParenthesisCount = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM (SELECT *\n" +
" FROM some_table\n" +
" WHERE some_column < ?) some_view\n" +
"WHERE a_column = something"
));
parser.sqltext = "(((select a from b)) order by a)";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 0;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b))\n" +
" ORDER BY a"
));
parser.sqltext = "((((select a from b)) order by a))";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.ParenthesisCount = 1;
select.ParenthesisCountBeforeOrder = 1;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "(( SELECT a\n" +
" FROM b)\n" +
" ORDER BY a)"
));
parser.sqltext = "select * from user_table where ((username like '%admin%'));";
Assert.IsTrue(parser.parse() == 0);
select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = select.WhereClause.Condition;
select.WhereClause.Condition = expression.LeftOperand;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM user_table\n" +
"WHERE ( username LIKE '%admin%' )"
));
}
public void testMinusSetToString()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE TABLE X AS \n" + "SELECT A FROM B\n" + "minus\n" + "SELECT A FROM C";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateTableSqlStatement ct = (TCreateTableSqlStatement)sqlparser.sqlstatements.get(0);
TSelectSqlStatement select = ct.SubQuery;
Assert.IsTrue(!string.ReferenceEquals(select.ToString(), null));
}
public static object getModel(object gspModel)
{
if (gspModel == null)
{
return(null);
}
if (gspModel is TTable)
{
TTable table = (TTable)gspModel;
if (table.CTE != null)
{
return(modelBindingMap[table.CTE]);
}
if (table.Subquery != null && table.Subquery.ResultColumnList != null)
{
return(modelBindingMap[table.Subquery.ResultColumnList]);
}
}
if (gspModel is TSelectSqlStatement)
{
TSelectSqlStatement select = (TSelectSqlStatement)gspModel;
if (select.ResultColumnList != null)
{
return(modelBindingMap[select.ResultColumnList]);
}
}
object result = modelBindingMap[gspModel];
if (result == null)
{
result = createModelBindingMap[gspModel];
}
if (result == null)
{
result = insertModelBindingMap[gspModel];
}
if (result == null)
{
result = updateModelBindingMap[gspModel];
}
if (result == null)
{
result = mergeModelBindingMap[gspModel];
}
if (result == null)
{
result = viewModelBindingMap[gspModel];
}
return(result);
}
public void testCTEInCreateView()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "CREATE OR REPLACE VIEW FCT_PI_CLAIM_EVENTS_IMPACT_SUMMARY_INSIGHT AS \n" + "\n" + "WITH CLAIM_LODGEMENT AS (\n" + "\n" + " SELECT CLAIM_ID\n" + "\t , EVENT_TS AS LODGEMENT_TIMESTAMP\n" + " FROM (\t\n" + "\t \n" + " SELECT \n" + " CCR_CC_HISTORY.CLAIMID AS CLAIM_ID\n" + " , CCR_CC_HISTORY.EVENTTIMESTAMP AS EVENT_TS \n" + "\t , ROW_NUMBER() OVER (PARTITION BY CLAIMID \n" + "\t ORDER BY CCR_CC_HISTORY.EVENTTIMESTAMP DESC\n" + "\t\t\t\t\t\t\t , CCR_CC_HISTORY.ID DESC)\n" + "\t\t\t\t\t\t\t AS OPEN_EVENT_DESC_SEQ\t \n" + " FROM CCR_CC_HISTORY CCR_CC_HISTORY\n" + " \n" + " LEFT JOIN (\n" + " \tSELECT CCR_CC_TRANSACTION.CLAIMID AS CLAIM_ID\n" + " , MIN(CCR_CC_TRANSACTION.CREATETIME) AS ACTIVATION_DATETIME\n" + " FROM CCR_CC_TRANSACTION CCR_CC_TRANSACTION \n" + " GROUP BY CCR_CC_TRANSACTION.CLAIMID \t\t\t\t\t \n" + "\t ) CLAIM_ACTIVATION\n" + "\t\t \n" + " ON CLAIM_ACTIVATION.CLAIM_ID\t = CCR_CC_HISTORY.CLAIMID\n" + " \n" + " JOIN CCR_CCTL_HISTORYTYPE CCR_CCTL_HISTORYTYPE\n" + " ON CCR_CCTL_HISTORYTYPE.ID = CCR_CC_HISTORY.\"xxxx\"\n" + " AND CCR_CCTL_HISTORYTYPE.NAME IN ('xxxx', 'xxxx') \n" + " AND CCR_CC_HISTORY.EXPOSUREID IS NULL \n" + "\n" + " WHERE CCR_CC_HISTORY.EVENTTIMESTAMP <= COALESCE(ACTIVATION_DATETIME, CCR_CC_HISTORY.EVENTTIMESTAMP) \n" + "\t \n" + ") AS OPEN_REOPEN_DATES\n" + "\n" + " WHERE OPEN_EVENT_DESC_SEQ = 1\n" + "\n" + ")\n" + ", TRANSACTION_SUMMARY AS (\n" + "\n" + "SELECT *\n" + "FROM INT_PI_CLAIM_FINANCIALS_LIVE\n" + "\n" + ")\n" + " SELECT \n" + " CCR_CC_CLAIM.ID AS CLAIM_ID\n" + "\t\t, CCR_CC_CLAIM.CLAIMNUMBER AS CLAIM_NUMBER\n" + "\t\t, CCR_CC_POLICY.POLICYNUMBER AS POLICY_NUMBER\n" + "\t\t\n" + "\t\t \n" + "\t\t, CCR_CCTL_SC_BRAND.NAME AS BRAND\n" + "\t\t, CCR_CC_CATASTROPHE.NAME AS CATASTROPHE_NAME \n" + " , CCR_CC_CATASTROPHE.CATASTROPHENUMBER AS CATASTROPHE_CODE\n" + "\t , CCR_CCTL_SC_CLAIMTYPE.NAME AS CLAIM_TYPE\t\n" + "\t\t, DATE(CCR_CC_CLAIM.CLOSEDATE) AS CLOSED_DATE\t \n" + "\t\t, INT_PI_CLAIM_EVENT.EVENT_NAME AS EVENT_NAME\n" + "\t , CCR_CCTL_SC_CLAIMPRIORITY.NAME AS HOME_CLAIM_CATEGORY\n" + " , DATE(CLAIM_LODGEMENT.LODGEMENT_TIMESTAMP) AS LODGEMENT_DATE\n" + "\t\t, DATE(CLAIM_LODGEMENT.LODGEMENT_TIMESTAMP) AS LODGEMENT_TIME\t\t\n" + "\t\t, COALESCE(CCR_CCTL_SC_LOSSCAUSE.NAME, CCR_CCTL_LOSSCAUSE.NAME)\n" + "\t\t AS LOSS_CAUSE\t\t\n" + "\t\t, DATE(CCR_CC_CLAIM.LOSSDATE) AS LOSS_DATE\n" + "\n" + "\t , CCR_CC_ADDRESS_LOSS.POSTALCODE AS LOSS_POSTCODE\n" + "\t , CCR_CCX_SC_PAFPOSTCODES.LOCALITY AS LOSS_POSTCODE_LABEL\t \n" + "\t , UPPER(CCR_CC_ADDRESS_LOSS.CITY) AS LOSS_SUBURB\n" + "\t , CCR_CCTL_STATE_LOSS.NAME AS LOSS_STATE\n" + "\t , CASE WHEN CCR_CCTL_LOSSTYPE.NAME = 'xxxx' \n" + "\t\t THEN 'Home' \n" + "\t\t\t ELSE CCR_CCTL_LOSSTYPE.NAME \n" + "\t\t END AS LOSS_TYPE\n" + "\t , CAST(CASE WHEN CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t AND CCR_CCTL_SC_BRAND.TYPECODE = 'xxxx' \n" + "\t\t \t THEN 'xxxx'\n" + "\t WHEN CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t\t\t AND CCR_CCTL_VEHICLESTYLE.TYPECODE IN ('xxxx','xxxx','xxxx','xxxx') \n" + "\t\t\t THEN 'xxxx'\n" + "\t\t\t ELSE 'xxxx'\n" + "\t END AS CHAR(1)) AS SPECIALIST_CLAIM_FLAG\t\n" + "\t , CAST(CASE WHEN CCR_CC_INCIDENT.VEHICLEOPERABLE = 0 \n" + "\t THEN 'xxxx' \n" + "\t\t\t ELSE 'xxxx' \n" + "\t\t END AS CHAR(1)) AS VEHICLE_DRIVABLE_FLAG\n" + "\t \t\t \n" + "\n" + " \n" + "\t , CAST(1 AS INTEGER) AS CLAIM_VOLUME\n" + "\t , TRANSACTION_SUMMARY.NET_INCURRED_EX_GST \n" + "\t , TRANSACTION_SUMMARY.NET_INCURRED AS SYSTEM_COST\t\t\n" + "\t \n" + "\t \n" + "\t , CCR_CC_CLAIM_MAXTS.MAX_COMMIT_TIMESTAMP\n" + " \n" + " FROM CCR_CC_CLAIM CCR_CC_CLAIM\n" + "\t \n" + "\t JOIN TRANSACTION_SUMMARY TRANSACTION_SUMMARY\n" + "\t ON TRANSACTION_SUMMARY.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t\t \n" + " JOIN CCR_CC_POLICY CCR_CC_POLICY\n" + " ON CCR_CC_CLAIM.POLICYID = CCR_CC_POLICY.ID\n" + " \n" + " JOIN CCR_CCTL_SC_BRAND CCR_CCTL_SC_BRAND\n" + " ON CCR_CCTL_SC_BRAND.ID = CCR_CC_POLICY.SC_BRAND \t \n" + "\t \n" + " JOIN CCR_CCTL_LOSSTYPE CCR_CCTL_LOSSTYPE\n" + " ON CCR_CC_CLAIM.LOSSTYPE = CCR_CCTL_LOSSTYPE.ID\t \n" + "\t \n" + " JOIN CCR_CC_ADDRESS CCR_CC_ADDRESS_LOSS\n" + " ON CCR_CC_ADDRESS_LOSS.ID = CCR_CC_CLAIM.LOSSLOCATIONID \n" + "\t \n" + " JOIN CCR_CCTL_STATE CCR_CCTL_STATE_LOSS\n" + " ON CCR_CC_ADDRESS_LOSS.STATE = CCR_CCTL_STATE_LOSS.ID\t \n" + "\t \n" + "\t \n" + " JOIN (\n" + "\t SELECT POSTCODE\n" + "\t\t , LOCALITY\n" + "\t\t\t , ROW_NUMBER() OVER (PARTITION BY POSTCODE \n" + "\t\t\t ORDER BY LOCALITY) \n" + "\t\t\t\t\t\t\t\t\t\t AS ALPHA_ORDER \n" + "\t FROM CCR_CCX_SC_PAFPOSTCODES\n" + "\t\t ) CCR_CCX_SC_PAFPOSTCODES\n" + "\t\t \n" + " ON CCR_CCX_SC_PAFPOSTCODES.POSTCODE = CCR_CC_ADDRESS_LOSS.POSTALCODE\n" + " AND CCR_CCX_SC_PAFPOSTCODES.ALPHA_ORDER = 1 \t \n" + "\t \n" + " JOIN (SELECT MAX(COMMIT_TIMESTAMP) AS MAX_COMMIT_TIMESTAMP FROM CCR_CC_CLAIM) CCR_CC_CLAIM_MAXTS\n" + "\t ON 1=1\n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSCAUSE CCR_CCTL_SC_LOSSCAUSE\n" + " ON CCR_CCTL_SC_LOSSCAUSE.ID = CCR_CC_CLAIM.SC_LOSSCAUSE \n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSCAUSE CCR_CCTL_LOSSCAUSE\n" + " ON CCR_CCTL_LOSSCAUSE.ID = CCR_CC_CLAIM.LOSSCAUSE \n" + " \n" + "LEFT JOIN CCR_CCTL_SC_YESNOONLY CCR_CCTL_SC_YESNOONLY\n" + " ON CCR_CCTL_SC_YESNOONLY.ID = CCR_CC_CLAIM.SC_INCIDENTREPORT\n" + "\t \n" + "LEFT JOIN CLAIM_LODGEMENT CLAIM_LODGEMENT\t\t \n" + " ON CLAIM_LODGEMENT.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t\t\n" + "LEFT JOIN CCR_CC_CATASTROPHE CCR_CC_CATASTROPHE\n" + " ON CCR_CC_CATASTROPHE.ID = CCR_CC_CLAIM.CATASTROPHEID \t\n" + "\t \n" + "LEFT JOIN INT_PI_CLAIM_EVENT INT_PI_CLAIM_EVENT\n" + " ON INT_PI_CLAIM_EVENT.CLAIM_ID = CCR_CC_CLAIM.ID\n" + "\t \n" + "LEFT JOIN CCR_CC_INCIDENT CCR_CC_INCIDENT\n" + " ON CCR_CC_INCIDENT.CLAIMID = CCR_CC_CLAIM.ID\n" + "\n" + "LEFT JOIN CCR_CCTL_LOSSPARTYTYPE CCR_CCTL_LOSSPARTYTYPE_PR\n" + " ON CCR_CCTL_LOSSPARTYTYPE_PR.ID = CCR_CC_INCIDENT.SC_LOSSPARTY\n" + "\t \n" + "LEFT JOIN CCR_CCTL_LOSSPARTYTYPE CCR_CCTL_LOSSPARTYTYPE_AUTO\n" + " ON CCR_CCTL_LOSSPARTYTYPE_AUTO.ID = CCR_CC_INCIDENT.VEHICLELOSSPARTY\t \n" + "\t \n" + "LEFT JOIN CCR_CC_VEHICLE CCR_CC_VEHICLE\n" + " ON CCR_CC_VEHICLE.ID = CCR_CC_INCIDENT.VEHICLEID\n" + "\t \n" + "LEFT JOIN CCR_CCTL_VEHICLESTYLE CCR_CCTL_VEHICLESTYLE\n" + " ON CCR_CCTL_VEHICLESTYLE.ID = CCR_CC_VEHICLE.STYLE \t \n" + "\t \n" + "LEFT JOIN CCR_CCTL_SC_CLAIMPRIORITY CCR_CCTL_SC_CLAIMPRIORITY\n" + " ON CCR_CCTL_SC_CLAIMPRIORITY.ID = CCR_CC_CLAIM.SC_USERCLAIMPRIORITY\n" + "\n" + "LEFT JOIN CCR_CCTL_SC_CLAIMTYPE CCR_CCTL_SC_CLAIMTYPE\n" + " ON CCR_CCTL_SC_CLAIMTYPE.ID\t = CCR_CC_CLAIM.SC_CLAIMTYPE \t \t \n" + "\n" + " WHERE CCR_CC_CLAIM.RETIRED = 0\n" + " AND CCR_CC_POLICY.RETIRED = 0\n" + "\t \n" + "\t AND COALESCE(CCR_CCTL_SC_YESNOONLY.NAME,'xxxx') <> 'xxxx'\n" + "\t \n" + "\t AND ( CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx' \n" + "\t OR ( CCR_CCTL_LOSSPARTYTYPE_AUTO.TYPECODE = 'xxxx' AND CCR_CCTL_LOSSTYPE.TYPECODE = 'xxxx')\n" + "\t\t )\n" + "\t\t \t \n" + ";";
Assert.IsTrue(sqlparser.parse() == 0);
TCreateViewSqlStatement viewSqlStatement = (TCreateViewSqlStatement)sqlparser.sqlstatements.get(0);
Assert.IsTrue(viewSqlStatement.ViewName.ToString().Equals("FCT_PI_CLAIM_EVENTS_IMPACT_SUMMARY_INSIGHT", StringComparison.CurrentCultureIgnoreCase));
TSelectSqlStatement select = viewSqlStatement.Subquery;
Console.WriteLine(select.CteList.size());
}
public void testTreat()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT name, TREAT(VALUE(p) AS employee_t).salary salary \n" + " FROM persons p;";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn column = select.ResultColumnList.getResultColumn(1);
TExpression expr = column.Expr;
TFunctionCall f = expr.FunctionCall;
Assert.IsTrue(expr.ExpressionType == EExpressionType.object_access_t);
}
public virtual void testFromClaueJoinTable()
{
parser.sqltext = "select table1.col1, table2.col2\n" + "from table1, table2\n" + "where table1.foo > table2.foo";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.joins.removeElementAt(1);
TJoin join = new TJoin();
select.joins.addJoin(join);
//join.setWithParen(true);
join.NestedParen = 1;
TTable table1 = new TTable();
table1.TableName = parser.parseObjectName("tableX");
join.Table = table1;
TJoinItem joinItem = new TJoinItem();
join.JoinItems.addJoinItem(joinItem);
joinItem.JoinType = EJoinType.join;
TTable table2 = new TTable();
table2.TableName = parser.parseObjectName("tableY");
joinItem.Table = table2;
TObjectNameList usingColumns = new TObjectNameList();
usingColumns.addObjectName(parser.parseObjectName("id"));
joinItem.UsingColumns = usingColumns;
TAliasClause aliasClause = new TAliasClause();
aliasClause.AliasName = parser.parseObjectName("table2");
aliasClause.HasAs = true;
join.AliasClause = aliasClause;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT table1.col1,\n" +
" table2.col2\n" +
"FROM table1, (\n" +
" tablex JOIN \n" +
" tabley USING (ID)) AS table2\n" +
"WHERE table1.foo > table2.foo"
));
}
public void testExtractXML()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = "SELECT warehouse_name,\n" + " EXTRACT(warehouse_spec, '/Warehouse/Docks')\n" + " \"Number of Docks\"\n" + " FROM warehouses\n" + " WHERE warehouse_name = 'San Francisco';";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn column = select.ResultColumnList.getResultColumn(1);
TExpression expr = column.Expr;
TFunctionCall f = expr.FunctionCall;
Assert.IsTrue(f.FunctionType == EFunctionType.extractxml_t);
Assert.IsTrue(f.XMLType_Instance.ToString().Equals("warehouse_spec", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(f.XPath_String.ToString().Equals("'/Warehouse/Docks'", StringComparison.CurrentCultureIgnoreCase));
}
public void testValues1()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum);
sqlparser.sqltext = "VALUES (1, 'one'), (2, 'two'), (3, 'three');";
Assert.IsTrue(sqlparser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TValueClause valueClause = select.ValueClause;
Assert.IsTrue(valueClause.ValueRows.size() == 3);
TValueRowItem rowItem = valueClause.ValueRows.getValueRowItem(0);
Assert.IsTrue(rowItem.ExprList.getExpression(0).ToString().Equals("1", StringComparison.CurrentCultureIgnoreCase));
Assert.IsTrue(rowItem.ExprList.getExpression(1).ToString().Equals("'one'", StringComparison.CurrentCultureIgnoreCase));
}
public virtual void testModifyAlias()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);
sqlparser.sqltext = @"Select X as [Y]";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
TResultColumn resultColumn = select.ResultColumnList.getResultColumn(0);
TAliasClause aliasClause = resultColumn.AliasClause;
aliasClause.AliasName = parser.parseObjectName("\"Y\"");
//Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
}
public void testNetezzaSelectFromExternalTable()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvnetezza);
sqlparser.sqltext = "insert into \"$hist_column_access_1\"(npsid, npsinstanceid, opid, logentryid, seqid, sessionid, dbid, dbname, schemaid, schemaname, tableid, tablename, columnid, columnname, usage) select 1, * from external '/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439' (npsinstanceid int, opid bigint, logentryid bigint, seqid integer, sessionid bigint, dbid int, dbname nvarchar (128), schemaid int , schemaname nvarchar (128), tableid int, tablename nvarchar (128), columnid int, columnname nvarchar (128), usage int) using( logdir '/nz/data.1.0/hist/loading/alc_20140215_151309.473439' encoding 'internal' escapechar '\\' fillrecord true ctrlchars true crinstring true)";
Assert.IsTrue(sqlparser.parse() == 0);
TInsertSqlStatement insertSqlStatement = (TInsertSqlStatement)sqlparser.sqlstatements.get(0);
TTable table = insertSqlStatement.tables.getTable(0);
Assert.IsTrue(table.TableName.ToString().Equals("\"$hist_column_access_1\"", StringComparison.CurrentCultureIgnoreCase));
TSelectSqlStatement select = (TSelectSqlStatement)insertSqlStatement.SubQuery;
table = select.tables.getTable(0);
Assert.IsTrue(table.TableName.ToString().Equals("'/nz/data.1.0/hist/loading/alc_20140215_151309.473439/alc_co_20140215_151309.473439'", StringComparison.CurrentCultureIgnoreCase));
}
public virtual void testSetNewWhereCondition()
{
parser.sqltext = "select t1.f1 from table1 t1 where t1.f2 = 2 ";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.WhereClause.Condition = parser.parseExpression("t1.f2>2");
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT t1.f1\n" +
"FROM table1 t1\n" +
"WHERE t1.f2 > 2"
));
}
public virtual void testRemoveCTE()
{
parser.sqltext = "with test as (select id from emp)\n" + "select * from test";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.CteList = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM test"
));
}
public virtual void testRemoveWhereClause()
{
parser.sqltext = "SELECT * FROM TABLE_X where a>1 order by a";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY a"
));
}
public void testOwnStmtOfExpr()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"DELETE FROM employee e WHERE NOT EXISTS ( SELECT * FROM department d WHERE e.emp_dept = d.dpt_id)";
Assert.IsTrue(sqlparser.parse() == 0);
TDeleteSqlStatement delete = (TDeleteSqlStatement)sqlparser.sqlstatements.get(0);
TExpression condition = delete.WhereClause.Condition;
TExpression subqueryExpr = condition.RightOperand;
TSelectSqlStatement subquery = subqueryExpr.SubQuery;
condition = subquery.WhereClause.Condition;
TCustomSqlStatement ownStmt = condition.OwnerStmt;
Assert.IsTrue(ownStmt.sqlstatementtype == ESqlStatementType.sstselect);
}
public virtual void testColumnInAndOr2()
{
parser.sqltext = "SELECT m.*, \n" + " altname.last_name last_name_student, \n" + " altname.first_name first_name_student, \n" + " ccu.date_joined, \n" + " ccu.last_login, \n" + " ccu.photo_id, \n" + " ccu.last_updated \n" + "FROM summit.mstr m, \n" + " summit.alt_name altname, \n" + " smmtccon.ccn_user ccu \n" + "WHERE m.id =?\n" + " AND m.id = altname.id(+) \n" + " AND m.id = ccu.id(+) \n" + " AND altname.grad_name_ind(+) = '*'";
int ret = parser.parse();
Assert.IsTrue(ret == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = selectSqlStatement.WhereClause.Condition;
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ? and m.id = altname.id(+) and m.id = ccu.id(+)", StringComparison.OrdinalIgnoreCase));
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ? and m.id = altname.id(+)", StringComparison.OrdinalIgnoreCase));
expression.RightOperand.remove();
Assert.IsTrue(expression.ToScript().Equals("m.id = ?", StringComparison.OrdinalIgnoreCase));
}
private void parseSubQueryColumnDefinition(TInsertSqlStatement insert, TSelectSqlStatement stmt, TableMetaData tableMetaData, ColumnImpactModel columnImpactModel)
{
if (stmt.SetOperatorType != ESetOperatorType.none)
{
parseSubQueryColumnDefinition(insert, stmt.LeftStmt, tableMetaData, columnImpactModel);
parseSubQueryColumnDefinition(insert, stmt.RightStmt, tableMetaData, columnImpactModel);
}
else
{
if (insert.ColumnList != null)
{
TObjectNameList items = insert.ColumnList;
int columnCount = items.size();
string[] aliasNames = new string[columnCount];
for (int i = 0; i < items.size(); i++)
{
aliasNames[i] = items.getObjectName(i).ToString();
}
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = null;
if (i < stmt.ResultColumnList.size())
{
resultColumn = stmt.ResultColumnList.getResultColumn(i);
}
else
{
resultColumn = stmt.ResultColumnList.getResultColumn(stmt.ResultColumnList.size() - 1);
}
parseInsertColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
else if (insert.SubQuery != null && insert.SubQuery.ResultColumnList != null)
{
int columnCount = insert.SubQuery.ResultColumnList.size();
String[] aliasNames = new String[columnCount];
for (int i = 0; i < columnCount; i++)
{
TResultColumn resultColumn = insert.SubQuery.ResultColumnList.getResultColumn(i);
parseColumnDefinition(resultColumn, tableMetaData, columnImpactModel, aliasNames[i]);
}
}
}
}
public virtual void testRemoveHavingClause()
{
parser.sqltext = "SELECT\n" + "c.ID AS \"SMS.ID\"\n" + "FROM\n" + "SUMMIT.cntrb_detail c\n" + "where\n" + "c.cntrb_date >='$GivingFromDate$'\n" + "and c.cntrb_date<='$GivingThruDate$'\n" + "group by c.id\n" + "having sum(c.amt) >= '$GivingFromAmount$' and sum(c.amt) <= '$GivingThruAmount$'";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TGroupBy groupBy = selectSqlStatement.GroupByClause;
groupBy.HavingClause = null;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, selectSqlStatement.ToScript()
, "SELECT c.ID AS \"SMS.ID\"\n" +
"FROM summit.cntrb_detail c\n" +
"WHERE c.cntrb_date >= '$GivingFromDate$'\n" +
" AND c.cntrb_date <= '$GivingThruDate$'\n" +
"GROUP BY c.ID"
));
}
public virtual void testAddWhereClause2()
{
parser.sqltext = "SELECT * FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TWhereClause whereClause = new TWhereClause();
select.WhereClause = whereClause;
whereClause.Condition = parser.parseExpression("c>1");
// System.out.println(scriptGenerator.generateScript(select,true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"WHERE c > 1"
));
}
public virtual void testRemoveTableInFromClauseAndRemoveWhereClause()
{
parser.sqltext = "SELECT * FROM t1,t2 where t1.f1=t2.f2";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TJoinList joinList = select.joins;
joinList.removeElementAt(0);
select.WhereClause = null;
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM t2"
));
}
public virtual void testOracleAddHint()
{
TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle);
sqlparser.sqltext = @"select distinct emp_id from T1";
sqlparser.parse();
TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);
select.OracleHint = "/*+ FULL(products) */";
//Console.WriteLine(sqlparser.sqlstatements.get(0).ToScript());
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, @"select /*+ FULL(products) */ distinct
emp_id
from
T1"
));
}
public virtual void testColumnInAndOr1()
{
parser.sqltext = "select *\n" + "from table1 pal, table2 pualr, table3 pu\n" + "WHERE (pal.application_location_id = pualr.application_location_id \n" + " AND pu.jbp_uid = pualr.jbp_uid \n" + " AND pu.username = '******')";
int ret = parser.parse();
Assert.IsTrue(ret == 0);
TSelectSqlStatement selectSqlStatement = (TSelectSqlStatement)parser.sqlstatements.get(0);
TExpression expression = selectSqlStatement.WhereClause.Condition;
TExpressionList resultList = expression.searchColumn("application_location_id");
Assert.IsTrue(resultList.size() == 2);
TExpression expression1 = resultList.getExpression(0);
Assert.IsTrue(expression1.ExpressionType == EExpressionType.simple_object_name_t);
Assert.IsTrue(expression1.ToString().Equals("pal.application_location_id", StringComparison.CurrentCultureIgnoreCase));
expression1.remove();
Assert.IsTrue(expression.ToScript().Equals("(pu.jbp_uid = pualr.jbp_uid and pu.username = '******')", StringComparison.OrdinalIgnoreCase));
}
public virtual void testRemoveResultColumnInSelectList()
{
parser.sqltext = "SELECT A as A_Alias, B AS B_Alias FROM TABLE_X";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
TResultColumnList columns = select.ResultColumnList;
columns.removeElementAt(1);
TResultColumn resultColumn = new TResultColumn();
resultColumn.Expr = parser.parseExpression("x");
columns.addResultColumn(resultColumn);
// System.out.println(scriptGenerator.generateScript(select, true));
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT a AS a_alias,\n" +
" x\n" +
"FROM table_x"
));
}
public virtual void testRemoveItemInOrderByClause()
{
parser.sqltext = "SELECT * FROM TABLE_X order by a,b";
Assert.IsTrue(parser.parse() == 0);
TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);
select.OrderbyClause.Items.removeElementAt(1);
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x\n" +
"ORDER BY a"
));
select.OrderbyClause = null;
Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
, select.ToScript()
, "SELECT *\n" +
"FROM table_x"
));
}
