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" )); }