private void removeObjectFromOrderBy(TCustomSqlStatement stmt, TObjectName column) { if (!(stmt is TSelectSqlStatement)) { return; } TSelectSqlStatement select = (TSelectSqlStatement)stmt; TOrderBy orderBy = select.OrderbyClause; if (orderBy == null) { return; } for (int i = 0; i < orderBy.Items.Count; i++) { TOrderByItem item = orderBy.Items.getOrderByItem(i); if (item.startToken.posinlist <= column.startToken.posinlist && item.endToken.posinlist >= column.endToken.posinlist) { orderBy.Items.removeElementAt(i); break; } } if (orderBy.Items.Count == 0) { select.OrderbyClause = null; } }
private void parseProcedureStmt(TStoredProcedureSqlStatement procedureStmt) { if (procedureStmt.StoredProcedureName == null) { return; } ProcedureMetaData procedureMetaData = getProcedureMetaData(procedureStmt.StoredProcedureName); procedureMetaData = getProcedureMetaData(procedureMetaData, true); TObjectName procedureName = procedureStmt.StoredProcedureName; procedure procedure = new procedure(); procedure.name = procedureMetaData.DisplayName; procedure.owner = getOwnerString(procedureMetaData); procedure.coordinate = procedureName.startToken.lineNo + "," + procedureName.startToken.columnNo; procedure.highlightInfo = procedureName.startToken.offset + "," + (procedureName.endToken.offset - procedureName.startToken.offset + procedureName.endToken.astext.Length); List <procedure> procedureList = getProcedureList(procedures.Item1); procedureList.Add(procedure); procedures.Item1.procedures = procedureList.ToArray(); parseProcedureLineage(procedureStmt, procedureMetaData, procedure); }
private void removeObjectFromStatement(TCustomSqlStatement stmt, TObjectName column) { if (column.Location == ESqlClause.resultColumn) { removeObjectFromResultColumn(stmt, column); } else if (column.Location == ESqlClause.where) { removeObjectFromWhereCondition(stmt, column); } else if (column.Location == ESqlClause.orderby) { removeObjectFromOrderBy(stmt, column); } else if (column.Location == ESqlClause.groupby) { removeObjectFromGroupBy(stmt, column); } else if (column.Location == ESqlClause.having) { removeObjectFromHavingClause(stmt, column); } else if (column.Location == ESqlClause.set) { removeObjectFromSetClause(stmt, column); } else { Console.WriteLine("Not yet implements removing column from " + column.Location); } }
private void removeObjectFromGroupBy(TCustomSqlStatement stmt, TObjectName column) { if (!(stmt is TSelectSqlStatement)) { return; } TSelectSqlStatement select = (TSelectSqlStatement)stmt; TGroupBy groupBy = select.GroupByClause; if (groupBy == null) { return; } for (int i = 0; i < groupBy.Items.Count; i++) { TGroupByItem item = groupBy.Items.getGroupByItem(i); if (item.startToken.posinlist <= column.startToken.posinlist && item.endToken.posinlist >= column.endToken.posinlist) { groupBy.Items.removeGroupByItem(i); break; } } if (groupBy.Items.Count == 0) { select.GroupByClause = null; } }
protected internal static void iterateStmt(TCustomSqlStatement stmt) { // System.out.println(stmt.sqlstatementtype.toString()); for (int i = 0;i < stmt.tables.size();i++) { gudusoft.gsqlparser.nodes.TTable table = stmt.tables.getTable(i); string table_name = table.Name; Console.WriteLine("Analyzing: " + table_name + " <- " + stmt.sqlstatementtype); for (int j = 0; j < table.LinkedColumns.size(); j++) { TObjectName objectName = table.LinkedColumns.getObjectName(j); string column_name = table_name + "." + objectName.ColumnNameOnly.ToLower(); if (!objectName.TableDetermined) { column_name = "?." + objectName.ColumnNameOnly.ToLower(); } Console.WriteLine("Analyzing: " + column_name + " in " + stmt.sqlstatementtype + " " + objectName.Location); } } for (int i = 0;i < stmt.Statements.size();i++) { iterateStmt(stmt.Statements.get(i)); } }
public static TTable getTable(TCustomSqlStatement stmt, TObjectName column) { IEnumerator iter = modelBindingMap.Values.GetEnumerator(); while (iter.MoveNext()) { object key = iter.Current; if (key is Table) { key = ((Table)key).TableObject; } else if (key is QueryTable) { key = ((QueryTable)key).TableObject; } else { continue; } TTable table = (TTable)key; if (table.Subquery == stmt) { continue; } if (column.TableString != null && column.TableString.Trim().Length > 0) { if (table.AliasName != null) { if (!table.AliasName.Equals(column.TableString)) { continue; } else { return(table); } } } TObjectName[] columns = getTableColumns(table); for (int i = 0; i < columns.Length; i++) { TObjectName columnName = columns[i]; if ("*".Equals(columnName.ColumnNameOnly)) { continue; } if (columnName == column) { return(table); } } } return(null); }
protected override bool Execute(CodeActivityContext context) { object obj = ID.Get(context); int? IntId = null; string StrId = null; Guid? GuidId = null; if (obj == null) { Error.Set(context, "Не определен Идентификатор объекта"); return(false); } try { if (ObjectType == enumObjectTypeForName.HierLev1 || ObjectType == enumObjectTypeForName.HierLev2 || ObjectType == enumObjectTypeForName.HierLev3 || ObjectType == enumObjectTypeForName.PS || ObjectType == enumObjectTypeForName.Section || ObjectType == enumObjectTypeForName.TI) { IntId = Convert.ToInt32(obj); } if (ObjectType == enumObjectTypeForName.Balance_PS || ObjectType == enumObjectTypeForName.Balance_HierLev0 || ObjectType == enumObjectTypeForName.Balance_HierLev3 || ObjectType == enumObjectTypeForName.User || ObjectType == enumObjectTypeForName.Formula) { StrId = Convert.ToString(obj); } } catch { Error.Set(context, "Ошибка преобразования идентификатора объекта"); return(false); } TObjectName res = null; try { res = ARM_Service.ALARM_GetObjectName(ObjectType, IntId, StrId, GuidId, PathDelim); } catch (Exception ex) { Error.Set(context, ex.Message); } if (res != null) { ObjName.Set(context, res.Name); ObjPath.Set(context, res.Path); Error.Set(context, res.Error); } return(string.IsNullOrEmpty(Error.Get(context))); }
public override void preVisit(TMssqlExecute statement) { if (statement.ModuleName != null) { TObjectName procedureName = statement.ModuleName; ProcedureMetaData procedureMetaData = getProcedureMetaData(procedureName); setProcedureDlinage(procedureMetaData, procedureName); } }
public override void preVisit(TFunctionCall node) { if (node.FunctionName != null) { TObjectName procedureName = node.FunctionName; ProcedureMetaData procedureMetaData = getProcedureMetaData(procedureName); setProcedureDlinage(procedureMetaData, procedureName); } }
public override void preVisit(TCallStatement statement) { if (statement.RoutineName != null) { TObjectName procedureName = statement.RoutineName; ProcedureMetaData procedureMetaData = getProcedureMetaData(procedureName); setProcedureDlinage(procedureMetaData, procedureName); } }
private void deleteColumn(TCustomSqlStatement stmt) { for (int k = 0; k < stmt.tables.size(); k++) { TTable table = stmt.tables.getTable(k); if (table.TableName == null) { continue; } bool isThisTable = true; isThisTable = table.TableName.TableString.Equals(this.sourceTable, StringComparison.OrdinalIgnoreCase); if (!isThisTable) { continue; } if (!string.ReferenceEquals(this.sourceSchema, null) && table.TableName != null) { if (table.TableName.SchemaString != null) { isThisTable = table.TableName.SchemaString.Equals(this.sourceSchema, StringComparison.OrdinalIgnoreCase); } else { isThisTable = false; } } if (!isThisTable) { continue; } for (int m = 0; m < table.LinkedColumns.size(); m++) { TObjectName column = table.LinkedColumns.getObjectName(m); if (column.ColumnToken != null) { if (column.ColumnNameOnly.Equals(this.sourceColumn, StringComparison.OrdinalIgnoreCase)) { removeObjectFromStatement(stmt, column); this.renamedObjectsNum++; } } } } for (int j = 0; j < stmt.Statements.size(); j++) { deleteColumn(stmt.Statements.get(j)); } }
private void removeObjectFromResultColumn(TCustomSqlStatement stmt, TObjectName column) { if (stmt.ResultColumnList != null) { for (int i = 0; i < stmt.ResultColumnList.Count; i++) { TResultColumn resultSetColumn = stmt.ResultColumnList.getResultColumn(i); TExpression expression = resultSetColumn.Expr; switch (expression.ExpressionType) { case EExpressionType.simple_object_name_t: if (column.startToken == expression.startToken && column.endToken == expression.endToken) { stmt.ResultColumnList.removeResultColumn(i); return; } break; } } } if (stmt is TInsertSqlStatement) { TInsertSqlStatement insert = (TInsertSqlStatement)stmt; if (insert.ColumnList == null) { return; } for (int i = 0; i < insert.ColumnList.Count; i++) { TObjectName insertColumn = insert.ColumnList.getObjectName(i); if (column.startToken == insertColumn.startToken && column.endToken == insertColumn.endToken) { if (insert.Values != null) { for (int j = 0; j < insert.Values.Count; j++) { TMultiTarget target = insert.Values[j]; if (target.ColumnList != null && target.ColumnList.Count == insert.ColumnList.Count) { target.ColumnList.removeResultColumn(i); } } } insert.ColumnList.removeObjectName(i); return; } } } }
public void testViewAlias() { TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvoracle); sqlparser.sqltext = "CREATE OR REPLACE FORCE VIEW \"DEV_602_MEDIUM_TC\".\"AP20_INVOICE_DISTRIBUTIONS\" (\"A$ACCRUAL_POSTED_FLAG\", \"A$DISTR$ACCOUNT\", \"A$DISTR$COMPANY\", \"A$DISTR$DEPARTMENT\", \"A$DISTR$PRODUCT\", \"A$DISTR$SUB_ACCOUNT\", \"A$EXPENDITURE_ORGANIZATION_NAM\", \"A$GL_DATE\", \"A$INVOICE_DATE\", \"A$INVOICE_NUMBER\", \"A$POSTED_FLAG\", \"A$PROJECT_NAME\", \"A$PROJECT_NUMBER\", \"A$VENDOR_NAME\", \"A$VENDOR_SITE_CODE\", \"A$ZZ__________________________\", \"APLIAB$OPERATIONS_ACCOUNTING_F\", \"ACCRUAL_POSTED_FLAG\", \"AMOUNT\", \"AMOUNT_BASE\", \"ASSETS_ADDITION_CODE\", \"ASSETS_TRACKING_FLAG\", \"BASE_AMOUNT\", \"BASE_CURRENCY_CODE\", \"BASE_INVOICE_PRICE_VARIANCE\", \"BASE_QUANTITY_VARIANCE\", \"DESCRIPTION\", \"DISTR$ACCOUNT\", \"DISTR$COMPANY\", \"DISTR$DEPARTMENT\", \"DISTR$OPERATIONS_ACCOUNTING_FL\", \"DISTR$PRODUCT\", \"DISTR$SUB_ACCOUNT\", \"DISTRIBUTION_CREATION_DATE\", \"DISTRIBUTION_LINE_NUMBER\", \"EARLIEST_SETTLEMENT_DATE\", \"EXCHANGE_DATE\", \"EXCHANGE_RATE\", \"EXCHANGE_RATE_TYPE\", \"EXCHANGE_RATE_VARIANCE\", \"EXPENDITURE_ITEM_DATE\", \"EXPENDITURE_ORGANIZATION_NAME\", \"EXPENDITURE_TYPE\", \"EXPENSE_JUSTIFICATION\", \"FINAL_MATCH_FLAG\", \"GL_DATE\", \"HOLD_MATCH_STATUS\", \"INV$ATTRIBUTE_CATEGORY\", \"INV$MISC_VENDOR_ADDRESS\", \"INV$MISC_VENDOR_CITY\", \"INV$MISC_VENDOR_NAME\", \"INV$MISC_VENDOR_STATE\", \"INV$MISC_VENDOR_ZIP\", \"INCOME_TAX_REGION\", \"INCOME_TAX_TYPE\", \"INVOICE_CURRENCY_CODE\", \"INVOICE_DATE\", \"INVOICE_NUMBER\", \"INVOICE_PAYMENT_STATUS\", \"INVOICE_PRICE_VARIANCE\", \"INVOICE_PRICE_VARIANCE_BASE\", \"INVOICE_SOURCE\", \"INVOICE_TYPE_LOOKUP_CODE\", \"JOURNAL_BATCH_DESCRIPTION\", \"JOURNAL_BATCH_NAME\", \"JOURNAL_BATCH_POSTED_DATE\", \"JOURNAL_BATCH_STATUS\", \"LINE_TYPE_LOOKUP_CODE\", \"PA_ADDITION_FLAG\", \"PA_QUANTITY\", \"PERIOD_NAME\", \"POSTED_AMOUNT\", \"POSTED_AMOUNT_BASE\", \"POSTED_BASE_AMOUNT\", \"POSTED_FLAG\", \"PREPAY_AMOUNT_REMAINING\", \"PREPAY_AMOUNT_REMAINING_BASE\", \"PRICE_VAR$OPERATIONS_ACCOUNTIN\", \"PROJECT_NAME\", \"PROJECT_NUMBER\", \"QUANTITY_INVOICED\", \"QUANTITY_VARIANCE\", \"REVERSAL_FLAG\", \"STAT_AMOUNT\", \"TASK_NAME\", \"TASK_NUMBER\", \"UNIT_PRICE\", \"UNIT_PRICE_BASE\", \"VAT_CODE\", \"VENDOR_EXCHANGE_DATE\", \"VENDOR_EXCHANGE_RATE\", \"VENDOR_EXCHANGE_RATE_TYPE\", \"VENDOR_NAME\", \"VENDOR_PREPAY_AMOUNT\", \"VENDOR_PREPAY_AMOUNT_BASE\", \"VENDOR_SITE_CODE\", \"Z$$_________________________\", \"Z$AP20_INVOICE_DISTRIBUTIONS\", \"Z$AP20_INVOICES\", \"Z$GL_CHART_OF_ACCOUNTS\") AS\n" + " SELECT\n" + "/*+ RULE */\n" + " IDSTR.ACCRUAL_POSTED_FLAG A$Accrual_Posted_Flag,\n" + " Distr.SEGMENT3 A$Distr$Account,\n" + " Distr.SEGMENT1 A$Distr$Company,\n" + " Distr.SEGMENT2 A$Distr$Department,\n" + " Distr.SEGMENT5 A$Distr$Product,\n" + " Distr.SEGMENT4 A$Distr$Sub_Account,\n" + " Expenditure_Organization_Name.NAME A$Expenditure_Organization_Nam,\n" + " IDSTR.ACCOUNTING_DATE A$Gl_Date,\n" + " INV.INVOICE_DATE A$Invoice_Date,\n" + " INV.INVOICE_NUM A$Invoice_Number,\n" + " IDSTR.POSTED_FLAG A$Posted_Flag,\n" + " PROJ.NAME A$Project_Name,\n" + " PROJ.SEGMENT1 A$Project_Number,\n" + " VEND.VENDOR_NAME A$Vendor_Name,\n" + " VCODE.VENDOR_SITE_CODE A$Vendor_Site_Code,\n" + " 'A$ZZ__________________________Copyright Noetix Corporation 1992-2009'\n" + "A$ZZ__________________________,\n" + " APLiab.SEGMENT1 ||'-'||APLiab.SEGMENT2 ||'-'||APLiab.SEGMENT3\n" + "||'-'||APLiab.SEGMENT4 ||'-'||APLiab.SEGMENT5 APLiab$Operations_Accounting_F,\n" + " IDSTR.ACCRUAL_POSTED_FLAG Accrual_Posted_Flag,\n" + " NVL(IDSTR.AMOUNT,0) Amount,\n" + " NVL(IDSTR.BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.AMOUNT,0),NULL)) Amount_Base,\n" + " NVL(IDSTR.ASSETS_ADDITION_FLAG,'N') Assets_Addition_Code,\n" + " IDSTR.ASSETS_TRACKING_FLAG Assets_Tracking_Flag,\n" + " NVL(IDSTR.BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.AMOUNT,0),NULL)) Base_Amount,\n" + " BOOK.CURRENCY_CODE Base_Currency_Code,\n" + " IDSTR.BASE_INVOICE_PRICE_VARIANCE Base_Invoice_Price_Variance,\n" + " IDSTR.BASE_QUANTITY_VARIANCE Base_Quantity_Variance,\n" + " IDSTR.DESCRIPTION Description,\n" + " Distr.SEGMENT3 Distr$Account,\n" + " Distr.SEGMENT1 Distr$Company,\n" + " Distr.SEGMENT2 Distr$Department,\n" + " Distr.SEGMENT1 ||'-'||Distr.SEGMENT2 ||'-'||Distr.SEGMENT3\n" + "||'-'||Distr.SEGMENT4 ||'-'||Distr.SEGMENT5 Distr$Operations_Accounting_Fl,\n" + " Distr.SEGMENT5 Distr$Product,\n" + " Distr.SEGMENT4 Distr$Sub_Account,\n" + " IDSTR.CREATION_DATE Distribution_Creation_Date,\n" + " IDSTR.DISTRIBUTION_LINE_NUMBER Distribution_Line_Number,\n" + " INV.EARLIEST_SETTLEMENT_DATE Earliest_Settlement_Date,\n" + " IDSTR.EXCHANGE_DATE Exchange_Date,\n" + " IDSTR.EXCHANGE_RATE Exchange_Rate,\n" + " IDSTR.EXCHANGE_RATE_TYPE Exchange_Rate_Type,\n" + " IDSTR.EXCHANGE_RATE_VARIANCE Exchange_Rate_Variance,\n" + " IDSTR.EXPENDITURE_ITEM_DATE Expenditure_Item_Date,\n" + " Expenditure_Organization_Name.NAME Expenditure_Organization_Name,\n" + " IDSTR.EXPENDITURE_TYPE Expenditure_Type,\n" + " IDSTR.JUSTIFICATION Expense_Justification,\n" + " NVL(IDSTR.FINAL_MATCH_FLAG,'N') Final_Match_Flag,\n" + " IDSTR.ACCOUNTING_DATE Gl_Date,\n" + " SUBSTR (DECODE (IDSTR.MATCH_STATUS_FLAG, 'N', 'Not Tested for Approval',\n" + "'T', 'Failed Approval Testing', 'A', 'Approved', NULL,\n" + "'Not Tested for Approval'\n" + ", 'Undefined Value: ' || IDSTR.MATCH_STATUS_FLAG), 1, 23) Hold_Match_Status,\n" + " INV.ATTRIBUTE_CATEGORY INV$ATTRIBUTE_CATEGORY,\n" + " decode(INV.ATTRIBUTE_CATEGORY,\n" + "'One-Time',INV.ATTRIBUTE4,null) INV$Misc_Vendor_Address,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE5,null) INV$Misc_Vendor_City,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE3,null) INV$Misc_Vendor_Name,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE6,null) INV$Misc_Vendor_State,\n" + " decode(\n" + "INV.ATTRIBUTE_CATEGORY,'One-Time',INV.ATTRIBUTE7,null) INV$Misc_Vendor_Zip,\n" + " IDSTR.INCOME_TAX_REGION Income_Tax_Region,\n" + " IDSTR.TYPE_1099 Income_Tax_Type,\n" + " INV.INVOICE_CURRENCY_CODE Invoice_Currency_Code,\n" + " INV.INVOICE_DATE Invoice_Date,\n" + " INV.INVOICE_NUM Invoice_Number,\n" + " DECODE(INV.PAYMENT_STATUS_FLAG,'N'\n" + ",'No','Y','Yes','P','Partial',INV.PAYMENT_STATUS_FLAG) Invoice_Payment_Status,\n" + " IDSTR.INVOICE_PRICE_VARIANCE Invoice_Price_Variance,\n" + " NVL(IDSTR.BASE_INVOICE_PRICE_VARIANCE,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,\n" + "IDSTR.INVOICE_PRICE_VARIANCE,NULL)) Invoice_Price_Variance_Base,\n" + " INV.SOURCE Invoice_Source,\n" + " INV.INVOICE_TYPE_LOOKUP_CODE Invoice_Type_Lookup_Code,\n" + " GLBTC.DESCRIPTION Journal_Batch_Description,\n" + " GLBTC.NAME Journal_Batch_Name,\n" + " GLBTC.POSTED_DATE Journal_Batch_Posted_Date,\n" + " GLBTC.STATUS Journal_Batch_Status,\n" + " IDSTR.LINE_TYPE_LOOKUP_CODE Line_Type_Lookup_Code,\n" + " NVL(IDSTR.PA_ADDITION_FLAG,'Y') Pa_Addition_Flag,\n" + " NVL(IDSTR.PA_QUANTITY,0) Pa_Quantity,\n" + " IDSTR.PERIOD_NAME Period_Name,\n" + " NVL(IDSTR.POSTED_AMOUNT,0) Posted_Amount,\n" + " NVL(IDSTR.POSTED_BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.POSTED_AMOUNT,0),NULL)) Posted_Amount_Base,\n" + " NVL(IDSTR.POSTED_BASE_AMOUNT,DECODE(INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE,NVL(IDSTR.POSTED_AMOUNT,0),NULL)) Posted_Base_Amount,\n" + " IDSTR.POSTED_FLAG Posted_Flag,\n" + " NVL (IDSTR.PREPAY_AMOUNT_REMAINING, DECODE (INV.INVOICE_TYPE_LOOKUP_CODE,\n" + "'PREPAYMENT', DECODE(INV.PAYMENT_STATUS_FLAG,'Y', DECODE(\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE,\n" + "'ITEM', IDSTR.AMOUNT,0), 0), 0)) Prepay_Amount_Remaining,\n" + " (NVL (IDSTR.PREPAY_AMOUNT_REMAINING, DECODE(INV.INVOICE_TYPE_LOOKUP_CODE,\n" + "'PREPAYMENT', DECODE(INV.PAYMENT_STATUS_FLAG,'Y', DECODE(\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE,'ITEM', IDSTR.AMOUNT,0), 0), 0)) * NVL (\n" + "IDSTR.EXCHANGE_RATE, DECODE (INV.INVOICE_CURRENCY_CODE,\n" + "BOOK.CURRENCY_CODE, 1,NULL))) Prepay_Amount_Remaining_Base,\n" + " Price_Var.SEGMENT1 ||'-'||Price_Var.SEGMENT2 ||'-'||Price_Var.SEGMENT3 ||\n" + "'-'\n" + "||Price_Var.SEGMENT4 ||'-'||Price_Var.SEGMENT5 Price_Var$Operations_Accountin,\n" + " PROJ.NAME Project_Name,\n" + " PROJ.SEGMENT1 Project_Number,\n" + " NVL(IDSTR.QUANTITY_INVOICED,0) Quantity_Invoiced,\n" + " IDSTR.QUANTITY_VARIANCE Quantity_Variance,\n" + " NVL(IDSTR.REVERSAL_FLAG,'N') Reversal_Flag,\n" + " IDSTR.STAT_AMOUNT Stat_Amount,\n" + " TASK.TASK_NAME Task_Name,\n" + " TASK.TASK_NUMBER Task_Number,\n" + " IDSTR.UNIT_PRICE Unit_Price,\n" + " (IDSTR.UNIT_PRICE)*NVL(IDSTR.EXCHANGE_RATE,\n" + "DECODE(INV.INVOICE_CURRENCY_CODE,BOOK.CURRENCY_CODE,1,NULL)) Unit_Price_Base,\n" + " APTXC.NAME Vat_Code,\n" + " INV.EXCHANGE_DATE Vendor_Exchange_Date,\n" + " INV.EXCHANGE_RATE Vendor_Exchange_Rate,\n" + " INV.EXCHANGE_RATE_TYPE Vendor_Exchange_Rate_Type,\n" + " VEND.VENDOR_NAME Vendor_Name,\n" + " CASE WHEN IDSTR.LINE_TYPE_LOOKUP_CODE ='PREPAY' OR (\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE = 'TAX' AND\n" + "IDSTR.PREPAY_TAX_PARENT_ID\n" + "IS NOT NULL) THEN IDSTR.AMOUNT ELSE NULL END Vendor_Prepay_Amount,\n" + " CASE WHEN IDSTR.LINE_TYPE_LOOKUP_CODE ='PREPAY' OR (\n" + "IDSTR.LINE_TYPE_LOOKUP_CODE = 'TAX' AND\n" + "IDSTR.PREPAY_TAX_PARENT_ID IS NOT NULL) THEN NVL(IDSTR.BASE_AMOUNT,\n" + "DECODE(INV.INVOICE_CURRENCY_CODE,BOOK.CURRENCY_CODE,\n" + "NVL(IDSTR.AMOUNT,0),NULL)) ELSE NULL END Vendor_Prepay_Amount_Base,\n" + " VCODE.VENDOR_SITE_CODE Vendor_Site_Code,\n" + " 'Z$$_________________________' Z$$_________________________,\n" + " IDSTR.rowid Z$AP20_Invoice_Distributions,\n" + " INV.rowid Z$AP20_Invoices,\n" + " Distr.rowid Z$GL_Chart_Of_Accounts\n" + " FROM\n" + " HR.HR_ALL_ORGANIZATION_UNITS_TL Expenditure_Organization_Name,\n" + " GL.GL_CODE_COMBINATIONS Price_Var,\n" + " GL.GL_CODE_COMBINATIONS Distr,\n" + " GL.GL_CODE_COMBINATIONS APLiab,\n" + " PO.PO_VENDORS VEND,\n" + " PO.PO_VENDOR_SITES_ALL VCODE,\n" + " PA.PA_TASKS TASK,\n" + " PA.PA_PROJECTS_ALL PROJ,\n" + " AP.AP_TAX_CODES_ALL APTXC,\n" + " GL.GL_SETS_OF_BOOKS BOOK,\n" + " GL.GL_JE_BATCHES GLBTC,\n" + " GL.GL_JE_HEADERS GLHDR,\n" + " GL.GL_JE_LINES GLLIN,\n" + " GL.GL_IMPORT_REFERENCES GLREF,\n" + " AP.AP_ACCOUNTING_EVENTS_ALL AE,\n" + " AP.AP_AE_HEADERS_ALL AEH,\n" + " AP.AP_AE_LINES_ALL AEL,\n" + " AP.AP_INVOICES_ALL INV,\n" + " DEV_586_SMALL_DE.AP20_OU_ACL_Map_Base XMAP,\n" + " AP.AP_INVOICE_DISTRIBUTIONS_ALL IDSTR\n" + "WHERE 'Copyright Noetix Corporation 1992-2009' is not null\n" + " AND IDSTR.INVOICE_ID = INV.INVOICE_ID\n" + " AND NVL (IDSTR.ORG_ID, -9999) = XMAP.ORG_ID\n" + " AND XMAP.APPLICATION_INSTANCE = '45'\n" + " /*** SET_OF_BOOKS_ID => 1 ***/\n" + " /*** CHART_OF_ACCOUNTS_ID => 101 ***/\n" + " /*** ORG_ID => 204 ***/\n" + " AND IDSTR.SET_OF_BOOKS_ID = XMAP.SET_OF_BOOKS_ID\n" + " AND IDSTR.ACCOUNTING_EVENT_ID = AE.ACCOUNTING_EVENT_ID (+)\n" + " AND AE.SOURCE_TABLE (+)= 'AP_INVOICES'\n" + " AND (AE.SOURCE_ID IS NULL OR\n" + " AE.SOURCE_ID = INV.INVOICE_ID)\n" + " AND AE.ACCOUNTING_EVENT_ID = AEH.ACCOUNTING_EVENT_ID (+)\n" + " AND IDSTR.INVOICE_DISTRIBUTION_ID = AEL.SOURCE_ID (+)\n" + " AND IDSTR.DIST_CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID (+)\n" + " AND AEL.SOURCE_TABLE (+) = 'AP_INVOICE_DISTRIBUTIONS'\n" + " AND ( ( AEL.SOURCE_ID IS NULL) OR\n" + " ( AEL.AE_LINE_ID = ( SELECT MIN(AEL1.AE_LINE_ID)\n" + " FROM AP.AP_AE_LINES_ALL AEL1\n" + " WHERE AEL1.AE_HEADER_ID = AEL.AE_HEADER_ID\n" + " AND AEL1.SOURCE_TABLE = 'AP_INVOICE_DISTRIBUTIONS'\n" + " AND AEL1.SOURCE_ID = AEL.SOURCE_ID\n" + " AND AEL1.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID)) )\n" + " AND ((AEL.AE_HEADER_ID IS NULL OR AEH.AE_HEADER_ID IS NULL) OR\n" + " AEL.AE_HEADER_ID = AEH.AE_HEADER_ID)\n" + " AND (AEH.AE_HEADER_ID IS NULL OR\n" + " AEH.SET_OF_BOOKS_ID = IDSTR.SET_OF_BOOKS_ID)\n" + " AND AEL.GL_SL_LINK_ID = GLREF.GL_SL_LINK_ID (+)\n" + " AND GLREF.GL_SL_LINK_TABLE (+) = 'APECL'\n" + " AND GLREF.JE_HEADER_ID = GLLIN.JE_HEADER_ID (+)\n" + " AND GLREF.JE_LINE_NUM = GLLIN.JE_LINE_NUM (+)\n" + " AND GLLIN.JE_HEADER_ID = GLHDR.JE_HEADER_ID (+)\n" + " AND GLHDR.JE_BATCH_ID = GLBTC.JE_BATCH_ID (+)\n" + " AND IDSTR.TAX_CODE_ID = APTXC.TAX_ID (+)\n" + " AND BOOK.SET_OF_BOOKS_ID = XMAP.SET_OF_BOOKS_ID\n" + " AND BOOK.SET_OF_BOOKS_ID +0 = IDSTR.SET_OF_BOOKS_ID\n" + " AND NVL (AE.ORG_ID(+), IDSTR.ORG_ID) = IDSTR.ORG_ID\n" + " AND NVL (AEH.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (AEL.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (APTXC.ORG_ID, XMAP.ORG_ID) =XMAP.ORG_ID\n" + " AND NVL (INV.ORG_ID, -9999) =XMAP.ORG_ID\n" + " AND PROJ.PROJECT_ID(+) = IDSTR.PROJECT_ID\n" + " AND TASK.TASK_ID(+) = IDSTR.TASK_ID\n" + " AND INV.VENDOR_SITE_ID = VCODE.VENDOR_SITE_ID(+)\n" + " AND NVL (VCODE.ORG_ID, XMAP.ORG_ID ) = XMAP.ORG_ID\n" + " AND VEND.VENDOR_ID +0 = INV.VENDOR_ID\n" + " AND IDSTR.ACCTS_PAY_CODE_COMBINATION_ID = APLiab.CODE_COMBINATION_ID(+)\n" + " AND IDSTR.DIST_CODE_COMBINATION_ID = Distr.CODE_COMBINATION_ID\n" + " AND IDSTR.PRICE_VAR_CODE_COMBINATION_ID = Price_Var.CODE_COMBINATION_ID(+)\n" + " AND IDSTR.EXPENDITURE_ORGANIZATION_ID\n" + "= Expenditure_Organization_Name.ORGANIZATION_ID(+)\n" + " AND Expenditure_Organization_Name.LANGUAGE (+) = NOETIX_ENV_PKG.GET_LANGUAGE"; Assert.IsTrue(sqlparser.parse() == 0); TCreateViewSqlStatement createView = (TCreateViewSqlStatement)sqlparser.sqlstatements.get(0); TViewAliasClause va = createView.ViewAliasClause; TObjectName o = va.ViewAliasItemList.getViewAliasItem(0).Alias; Assert.IsTrue(o.ToString().Equals("\"A$ACCRUAL_POSTED_FLAG\"", StringComparison.CurrentCultureIgnoreCase)); }
private void removeObjectFromWhereCondition(TCustomSqlStatement stmt, TObjectName column) { TWhereClause where = stmt.WhereClause; if (where == null) { return; } TExpression condition = where.Condition; removeObjectFromExpression(condition, column); if (where.Condition.ToScript().Trim().Length == 0) { stmt.WhereClause = null; } }
private ProcedureMetaData getProcedureMetaData(TObjectName procedureName) { ProcedureMetaData procedureMetaData = new ProcedureMetaData(vendor, strict); procedureMetaData.Name = procedureName.PartString == null ? procedureName.ObjectString : procedureName.PartString; procedureMetaData.SchemaName = procedureName.SchemaString; if (isNotEmpty(procedureName.DatabaseString)) { procedureMetaData.CatalogName = procedureName.DatabaseString; } else { procedureMetaData.CatalogName = database; } return(procedureMetaData); }
internal virtual string getColumnWithBaseTable(TObjectName objectName) { string ret = ""; TTable table = null; bool find = false; TCustomSqlStatement lcStmt = statement; while ((lcStmt != null) && (!find)) { for (int i = 0; i < lcStmt.tables.size(); i++) { table = lcStmt.tables.getTable(i); for (int j = 0; j < table.LinkedColumns.size(); j++) { if (objectName == table.LinkedColumns.getObjectName(j)) { if (table.BaseTable) { ret = table.TableName + "." + objectName.ColumnNameOnly; } else { //derived table if (table.AliasClause != null) { ret = table.AliasClause.ToString() + "." + objectName.ColumnNameOnly; } else { ret = objectName.ColumnNameOnly; } ret += "(column in derived table)"; } find = true; break; } } } if (!find) { lcStmt = lcStmt.ParentStmt; } } return(ret); }
private string getOwnerString(TObjectName objectName) { StringBuilder buffer = new StringBuilder(); if (!SQLUtil.isEmpty(objectName.ServerString)) { buffer.Append(objectName.ServerString).Append("."); } if (!SQLUtil.isEmpty(objectName.DatabaseString)) { buffer.Append(objectName.DatabaseString).Append("."); } if (!SQLUtil.isEmpty(objectName.SchemaString)) { buffer.Append(objectName.SchemaString); } return(buffer.ToString().ToUpper()); }
private void removeObjectFromSetClause(TCustomSqlStatement stmt, TObjectName column) { if (stmt is TUpdateSqlStatement) { TUpdateSqlStatement update = (TUpdateSqlStatement)stmt; if (update.ResultColumnList != null) { for (int i = 0; i < update.ResultColumnList.Count; i++) { TResultColumn resultcolumn = update.ResultColumnList.getResultColumn(i); if (resultcolumn.startToken.posinlist <= column.startToken.posinlist && resultcolumn.endToken.posinlist >= column.endToken.posinlist) { update.ResultColumnList.removeResultColumn(i); break; } } } } }
public virtual bool exprVisit(TParseTreeNode pNode, bool isLeafNode) { TExpression expr = (TExpression)pNode; switch ((expr.ExpressionType)) { case EExpressionType.simple_object_name_t: TObjectName obj = expr.ObjectOperand; if (obj.ObjectType != TObjectName.ttobjNotAObject) { Console.WriteLine(getColumnWithBaseTable(obj)); } break; case EExpressionType.function_t: functionCallVisitor fcv = new functionCallVisitor(statement); expr.FunctionCall.accept(fcv); break; } return(true); }
internal virtual ProcedureMetaData getProcedureMetaData(TObjectName procedureName) { ProcedureMetaData procedureMetaData = outerInstance.getProcedureMetaData(parentProcedure, procedureName); procedureMetaData = outerInstance.getProcedureMetaData(procedureMetaData, false); if (procedureMetaData == null) { return(null); } if (string.ReferenceEquals(procedureMetaData.CatalogName, null)) { procedureMetaData.CatalogName = parentProcedure.CatalogName; procedureMetaData.CatalogDisplayName = parentProcedure.CatalogDisplayName; } if (string.ReferenceEquals(procedureMetaData.SchemaName, null)) { procedureMetaData.SchemaName = parentProcedure.SchemaName; procedureMetaData.SchemaDisplayName = parentProcedure.SchemaDisplayName; } return(procedureMetaData); }
public virtual void testOracleBindVariable() { parser.sqltext = "UPDATE Art SET Desc=:Desc WHERE ID=:ID"; Assert.IsTrue(parser.parse() == 0); TUpdateSqlStatement updateStmt = (TUpdateSqlStatement)parser.sqlstatements.get(0); TResultColumn setClause = updateStmt.ResultColumnList.getResultColumn(0); TExpression assignment = setClause.Expr; // create a vairable TObjectName bindVar = new TObjectName(new TSourceToken(":bindVar"), EDbObjectType.variable); TExpression newVariable = new TExpression(EExpressionType.simple_object_name_t); newVariable.ObjectOperand = bindVar; //set new varaible assignment.RightOperand = newVariable; //Console.WriteLine(updateStmt.ToScript()); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , updateStmt.ToScript() , "UPDATE Art SET Desc=:bindVar WHERE ID=:ID" )); }
private void removeObjectFromHavingClause(TCustomSqlStatement stmt, TObjectName column) { if (!(stmt is TSelectSqlStatement)) { return; } TSelectSqlStatement select = (TSelectSqlStatement)stmt; TGroupBy groupBy = select.GroupByClause; if (groupBy == null) { return; } TExpression expression = groupBy.HavingClause; removeObjectFromExpression(expression, column); if (expression.ToScript().Trim().Length == 0) { groupBy.HavingClause = null; } }
protected internal virtual void analyzeStmt(TCustomSqlStatement stmt, int pNest) { TTable lcTable = null; TObjectName lcColumn = null; string tn = "", cn = ""; if (stmt is TStoredProcedureSqlStatement) { spList.Push((TStoredProcedureSqlStatement)stmt); TInfoRecord spRecord = new TInfoRecord(EDbObjectType.procedure); spRecord.SPName = spList.Peek().StoredProcedureName; } //System.out.println( numberOfSpace(pNest)+ stmt.sqlstatementtype); infos.Append(numberOfSpace(pNest) + stmt.sqlstatementtype + newline); for (int i = 0; i < stmt.tables.size(); i++) { //if (stmt.tables.getTable(i).isBaseTable()) //{ lcTable = stmt.tables.getTable(i); TInfoRecord tableRecord = new TInfoRecord(lcTable); tableRecord.FileName = this.sqlFileName; if (spList.Count > 0) { tableRecord.SPName = spList.Peek().StoredProcedureName; } infoList.Add(tableRecord); if (lcTable.TableType == ETableSource.subquery) { tn = "(subquery, alias:" + lcTable.AliasName + ")"; } else { tn = lcTable.TableName.ToString(); if (lcTable.LinkTable != null) { tn = tn + "(" + lcTable.LinkTable.TableName.ToString() + ")"; } else if (lcTable.CTEName) { tn = tn + "(CTE)"; } } //System.out.println(numberOfSpace(pNest+1)+tn.getName()); if ((showTableEffect) && (lcTable.BaseTable)) { infos.Append(numberOfSpace(pNest + 1) + tn + "(" + lcTable.EffectType + ")" + newline); } else { infos.Append(numberOfSpace(pNest + 1) + tn + newline); } tableColumnList.Append("," + tn); if (!((lcTable.TableType == ETableSource.subquery) || (lcTable.CTEName))) { if (lcTable.LinkTable != null) { // tablelist.append(lcTable.getLinkTable().toString()+newline); tablelist.Add(lcTable.LinkTable.ToString()); } else { // tablelist.append(lcTable.toString()+newline); tablelist.Add(lcTable.TableName.ToString()); } } for (int j = 0; j < stmt.tables.getTable(i).LinkedColumns.size(); j++) { lcColumn = stmt.tables.getTable(i).LinkedColumns.getObjectName(j); TInfoRecord columnRecord = new TInfoRecord(tableRecord, EDbObjectType.column); columnRecord.Column = lcColumn; infoList.Add(columnRecord); cn = lcColumn.ColumnNameOnly; //System.out.println(numberOfSpace(pNest+2)+cn.getColumnNameOnly()); if (showColumnLocation) { string posStr = ""; // if ( lcColumn.getColumnToken() != null) { // TSourceToken lcStartToken = lcColumn.getColumnToken(); // posStr ="("+ lcStartToken.lineNo+","+lcStartToken.columnNo+ ")"; // } infos.Append(numberOfSpace(pNest + 3) + lcColumn.ColumnNameOnly + posStr + "(" + lcColumn.Location + ")" + newline); } else { infos.Append(numberOfSpace(pNest + 3) + lcColumn.ColumnNameOnly + newline); } if (!((lcTable.TableType == ETableSource.subquery) || (lcTable.CTEName))) { if ((listStarColumn) || (!(lcColumn.ColumnNameOnly.Equals("*")))) { if (lcTable.LinkTable != null) { fieldlist.Add(lcTable.LinkTable.TableName + dotChar + cn); } else { fieldlist.Add(tn + dotChar + cn); } } } tableColumnList.Append("," + tn + dotChar + cn); } //} } if (stmt.OrphanColumns.size() > 0) { infos.Append(numberOfSpace(pNest + 1) + " orphan columns:" + newline); string oc = ""; for (int k = 0; k < stmt.OrphanColumns.size(); k++) { TInfoRecord columnRecord = new TInfoRecord(EDbObjectType.column); columnRecord.Column = stmt.OrphanColumns.getObjectName(k); columnRecord.FileName = this.sqlFileName; infoList.Add(columnRecord); oc = stmt.OrphanColumns.getObjectName(k).ColumnNameOnly; // stmt.getOrphanColumns().getObjectName(k).toString(); if (showColumnLocation) { infos.Append(numberOfSpace(pNest + 3) + oc + "(" + stmt.OrphanColumns.getObjectName(k).Location + ")" + newline); } else { infos.Append(numberOfSpace(pNest + 3) + oc + newline); } if ((linkOrphanColumnToFirstTable) && (stmt.FirstPhysicalTable != null)) { if ((listStarColumn) || (!(oc.Equals("*", StringComparison.CurrentCultureIgnoreCase)))) { fieldlist.Add(stmt.FirstPhysicalTable.ToString() + dotChar + oc); } columnRecord.Table = stmt.FirstPhysicalTable; } else { fieldlist.Add("missed" + dotChar + oc); } tableColumnList.Append(",missed" + dotChar + oc + newline); } } for (int i = 0; i < stmt.Statements.size(); i++) { analyzeStmt(stmt.Statements.get(i), pNest + 1); } if (stmt is TStoredProcedureSqlStatement) { spList.Pop(); } }
internal virtual void setProcedureDlinage(ProcedureMetaData procedureMetaData, TObjectName procedureName) { if (procedureMetaData == null) { return; } sourceProcedure sourceProcedure = new sourceProcedure(); sourceProcedure.name = procedureMetaData.DisplayName; sourceProcedure.owner = outerInstance.getOwnerString(procedureMetaData); sourceProcedure.coordinate = procedureName.startToken.lineNo + "," + procedureName.startToken.columnNo; sourceProcedure.highlightInfo = procedureName.startToken.offset + "," + (procedureName.endToken.offset - procedureName.startToken.offset + procedureName.endToken.astext.Length); List <sourceProcedure> sourceProcedureList = getSourceProcedureList(targetProcedure); sourceProcedureList.Add(sourceProcedure); targetProcedure.sourceProcedures = sourceProcedureList.ToArray(); }
protected internal static void iterateStmt(TCustomSqlStatement stmt) { for (int i = 0; i < stmt.tables.size(); i++) { TTable table = stmt.tables.getTable(i); string table_name = table.Name; DBTable dbtable = new DBTable(); Dictionary <long, Column> dictColumns = new Dictionary <long, Column>(); if (Utilities.DictParsedTables.ContainsKey(table_name)) { dbtable = Utilities.DictParsedTables[table_name]; dictColumns = dbtable.DictColumns; } // If the column occurs first time, we will have to fetch the column id and set it for that column from the database. if (dbtable.Table_Object_Id == 0) { long Table_Object_id = 0; string Table_Schema_Name = ""; SqlConnection scon = new SqlConnection(); //Connection string example with Windows Authentication mode or Integrated Security mode. string ConnectionString = @"Data Source=DESKTOP-R4E3L7J\LEARNINGOWL; Initial Catalog=AdventureWorks2016; Asynchronous Processing=True; Integrated Security=True; Connect Timeout=30"; using (SqlConnection connection = new SqlConnection(ConnectionString)) { // select top 1 c.object_id as 'Table_Object_id', object_Name(c.object_id) as 'TableName', c.column_id, c.name as ColumnName from sys.all_columns c where object_Name(c.object_id) = '" + column.Name + "' and c.name = '" + table_name + "'" SqlCommand cmd = new SqlCommand("select top 1 c.object_id as 'Table_Object_id', OBJECT_SCHEMA_NAME(c.object_id) as 'Table_Schema_Name' from sys.all_columns c where object_name(c.object_id) = '" + table_name + "'", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Table_Object_id = Convert.ToInt64(reader["Table_Object_Id"]); Table_Schema_Name = reader["Table_Schema_Name"].ToString(); } connection.Close(); } // Assign the column dictionary along with the dbtable object back to the global dbtable dictionary if (dbtable.Table_Object_Id != Table_Object_id && Table_Object_id != 0) { dbtable.Table_Object_Id = Table_Object_id; dbtable.Table_Schema_Name = Table_Schema_Name; } } // Assign/Reassign name to the tableboject. dbtable.name = table_name; // Increment the occurence count of the table in the workload dbtable.TotalNumOfOccurrences += 1; // Increment the count for the type of occurence if (stmt.sqlstatementtype == ESqlStatementType.sstselect || stmt.sqlstatementtype == ESqlStatementType.sstmssqlselect) { if (dbtable.DictOccurencesStmts.ContainsKey(ESqlStatementType.sstselect.ToString())) { dbtable.DictOccurencesStmts[ESqlStatementType.sstselect.ToString()] += 1; } else { dbtable.DictOccurencesStmts[ESqlStatementType.sstselect.ToString()] = 1; } } else if (stmt.sqlstatementtype == ESqlStatementType.sstupdate || stmt.sqlstatementtype == ESqlStatementType.sstmssqlupdatetext) { if (dbtable.DictOccurencesStmts.ContainsKey(ESqlStatementType.sstupdate.ToString())) { dbtable.DictOccurencesStmts[ESqlStatementType.sstupdate.ToString()] += 1; } else { dbtable.DictOccurencesStmts[ESqlStatementType.sstupdate.ToString()] = 1; } } else if (stmt.sqlstatementtype == ESqlStatementType.sstinsert) { if (dbtable.DictOccurencesStmts.ContainsKey(ESqlStatementType.sstinsert.ToString())) { dbtable.DictOccurencesStmts[ESqlStatementType.sstinsert.ToString()] += 1; } else { dbtable.DictOccurencesStmts[ESqlStatementType.sstinsert.ToString()] = 1; } } // Loop on each of the columns in each table. for (int j = 0; j < table.LinkedColumns.size(); j++) { Column column = new Column(); TObjectName objectName = table.LinkedColumns.getObjectName(j); // check if the column is present in the column dictionary already so the same values could be updated further. if (dictColumns.Where(col => col.Value.Name == objectName.ColumnNameOnly.ToLower()).Any()) { column = dictColumns.FirstOrDefault(col => col.Value.Name == objectName.ColumnNameOnly.ToLower()).Value; } else { // Set the column name for first time occurence column.Name = objectName.ColumnNameOnly.ToLower(); } // Set the occurence irrespective of the item being found previously in dictionary or not. column.TotalNumOfOccurrences += 1; // Switch over the location of the column and update the occurence accrodingly. switch (objectName.Location) { case ESqlClause.insertColumn: { column.InsertOccurences += 1; break; } case ESqlClause.insertValues: { // This insert is being counted as Projection since this is the column value to be inserted and so it will projected to be inserted in the second table. column.ProjectOccurences += 1; break; } case ESqlClause.forUpdate: { column.UpdateOccurences += 1; break; } case ESqlClause.resultColumn: { column.ProjectOccurences += 1; break; } case ESqlClause.where: { column.WhereOccurences += 1; break; } case ESqlClause.having: { column.HavingOccurences += 1; break; } case ESqlClause.groupby: { column.GroupByOccurences += 1; break; } case ESqlClause.orderby: { column.OrderByOccurences += 1; break; } case ESqlClause.joinCondition: { column.UsedForJoinOccurrences += 1; break; } case ESqlClause.join: { column.UsedForJoinOccurrences += 1; break; } case ESqlClause.unknown: { column.UnknownOccurences += 1; break; } default: break; } // Checks the clause operator used for this column if any and maintains it. if (stmt.WhereClause != null) { // Check if this column has been used with this operator before and update the count. if (column.WhereComparisonOperators.ContainsKey(stmt.WhereClause.Condition.ComparisonType.ToString())) { column.WhereComparisonOperators[stmt.WhereClause.Condition.ComparisonType.ToString()] += 1; } else // If never used before, update the dictionary to maintain the usage of the column with this operator. { column.WhereComparisonOperators[stmt.WhereClause.Condition.ComparisonType.ToString()] = 1; } } // If the column occurs first time, we will have to fetch the column id and set it for that column from the database. if (column.Column_Object_Id == 0) { long Column_Object_id = 0; SqlConnection scon = new SqlConnection(); //Connection string example with Windows Authentication mode or Integrated Security mode. string ConnectionString = @"Data Source=DESKTOP-R4E3L7J\LEARNINGOWL; Initial Catalog=AdventureWorks2016; Asynchronous Processing=True; Integrated Security=True; Connect Timeout=30"; using (SqlConnection connection = new SqlConnection(ConnectionString)) { // select top 1 c.object_id as 'Table_Object_id', object_Name(c.object_id) as 'TableName', c.column_id, c.name as ColumnName from sys.all_columns c where object_Name(c.object_id) = '" + column.Name + "' and c.name = '" + table_name + "'" SqlCommand cmd = new SqlCommand("select top 1 c.column_id as ColumnName from sys.all_columns c where Object_Schema_name(c.object_id) = '" + dbtable.Table_Schema_Name + "' and object_name(c.object_id) = '" + table_name + "' and c.name = '" + column.Name + "'", connection); connection.Open(); Object obj = cmd.ExecuteScalar(); Column_Object_id = ((obj != null)? Convert.ToInt64(obj.ToString()) :0); // Collect data about the Seletivity and reduction factor of the columns in consideration. if (Column_Object_id != 0) { // Get the selectivity of this column and update it in the column properties. cmd = new SqlCommand(@"select cast( cast(count(1) as decimal(18,2)) / cast(count(distinct " + column.Name + @") as decimal(18,2) ) as decimal(18,2)) as Selectivity from " + dbtable.Table_Schema_Name + @"." + table_name, connection); obj = cmd.ExecuteScalar(); // Selectivity = Distinct Values / Total values // Reduction Factor = 1 / Selectivity if (float.Parse(obj.ToString()) != 0.0) { column.Selectivity = float.Parse(obj.ToString()); column.ReductionFactor = 1 / column.Selectivity; } } connection.Close(); } // Assign the column dictionary along with the dbtable object back to the global dbtable dictionary if (dictColumns.FirstOrDefault(col => col.Value.Name == column.Name).Key != Column_Object_id) { column.Column_Object_Id = Column_Object_id; dictColumns[Column_Object_id] = column; } } else { dictColumns[column.Column_Object_Id] = column; } dbtable.DictColumns = dictColumns; Utilities.DictParsedTables[table_name] = dbtable; } } ////Check and return the where clause if any for this statement. //if (stmt.WhereClause != null) //{ // // We set the expression to be the entire where clause condition of this statement. // TExpression expr = stmt.WhereClause.Condition; // //This it the call to parse the expression tree in post order. // expr.postOrderTraverse(new exprVisitor()); //} for (int i = 0; i < stmt.Statements.size(); i++) { iterateStmt(stmt.Statements.get(i)); } }
protected internal virtual void run(string pQuery, bool isFile) { queryStr = pQuery; if (isFile) { sqlParser.sqlfilename = pQuery; } else { sqlParser.sqltext = pQuery; } int iRet = sqlParser.parse(); if (iRet != 0) { if (isConsole) { Console.WriteLine(sqlParser.Errormessage); } else { throw new Exception(sqlParser.Errormessage); } return; } outList.Length = 0; tablelist.Clear(); fieldlist.Clear(); indexList.Clear(); for (int i = 0; i < sqlParser.sqlstatements.size(); i++) { analyzeStmt(sqlParser.sqlstatements.get(i), 0); } // print detailed info if (showDetail) { bool includingTitle = true; for (int i = 0; i < infoList.Count; i++) { if (i > 0) { includingTitle = !(infoList[i].DbObjectType == infoList[i - 1].DbObjectType); } outputResult(infoList[i].printMe(includingTitle)); } } // print summary info if (showSummary) { removeDuplicateAndSort(tablelist); removeDuplicateAndSort(fieldlist); removeDuplicateAndSort(indexList); printArray("Tables:", tablelist); printArray("Fields:", fieldlist); if (showIndex && (indexList.Count > 0)) { printArray("Indexs:", indexList); } } // print tree structure if (showTreeStructure) { outputResult(infos.ToString()); } if (showBySQLClause) { List <ETableEffectType> tableEffectTypes = new List <ETableEffectType>(); List <ESqlClause> columnClauses = new List <ESqlClause>(); for (int i = 0; i < infoList.Count; i++) { if (infoList[i].DbObjectType == EDbObjectType.table) { if (!tableEffectTypes.Contains(infoList[i].Table.EffectType)) { tableEffectTypes.Add(infoList[i].Table.EffectType); } } } outputResult("Tables:"); for (int j = 0; j < tableEffectTypes.Count; j++) { outputResult("\t" + tableEffectTypes[j].ToString()); for (int i = 0; i < infoList.Count; i++) { if (infoList[i].DbObjectType == EDbObjectType.table) { TTable lcTable = infoList[i].Table; if (lcTable.EffectType == tableEffectTypes[j]) { outputResult("\t\t" + lcTable.ToString() + "(" + lcTable.TableName.coordinate() + ")"); } } } } // column for (int i = 0; i < infoList.Count; i++) { if (infoList[i].DbObjectType == EDbObjectType.column) { if (!columnClauses.Contains(infoList[i].Column.Location)) { columnClauses.Add(infoList[i].Column.Location); } } } outputResult("Columns:"); for (int j = 0; j < columnClauses.Count; j++) { outputResult("\t" + columnClauses[j].ToString()); for (int i = 0; i < infoList.Count; i++) { if (infoList[i].DbObjectType == EDbObjectType.column) { TObjectName lcColumn = infoList[i].Column; if (lcColumn.Location == columnClauses[j]) { outputResult("\t\t" + infoList[i].FullColumnName + "(" + lcColumn.coordinate() + ")"); } } } } } }
public virtual ProcedureMetaData getProcedureMetaData(ProcedureMetaData parentProcedure, TObjectName procedureName) { ProcedureMetaData procedureMetaData = new ProcedureMetaData(vendor, strict); procedureMetaData.Name = procedureName.PartString == null ? procedureName.ObjectString : procedureName.PartString; if (procedureName.SchemaString != null) { procedureMetaData.SchemaName = procedureName.SchemaString; } else { procedureMetaData.SchemaName = parentProcedure.SchemaName; procedureMetaData.SchemaDisplayName = parentProcedure.SchemaDisplayName; } if (isNotEmpty(procedureName.DatabaseString)) { procedureMetaData.CatalogName = procedureName.DatabaseString; } else { procedureMetaData.CatalogName = parentProcedure.CatalogName; procedureMetaData.CatalogDisplayName = parentProcedure.CatalogDisplayName; } return(procedureMetaData); }
private void removeObjectFromExpression(TExpression expression, TObjectName column) { expression.postOrderTraverse(new exprVisitor(column)); }
public exprVisitor(TObjectName column) { this.column = column; }
private void analyzeSqlStatement(procedureInfo procedureInfo, TCustomSqlStatement stmt) { if (stmt is TMssqlBlock) { TMssqlBlock block = (TMssqlBlock)stmt; if (block.BodyStatements != null) { for (int i = 0; i < block.BodyStatements.size(); i++) { analyzeSqlStatement(procedureInfo, block.BodyStatements.get(i)); } } } else if (stmt is TMssqlIfElse) { TMssqlIfElse ifElse = (TMssqlIfElse)stmt; if (ifElse.Stmt != null) { analyzeSqlStatement(procedureInfo, ifElse.Stmt); } if (ifElse.Condition != null) { } if (ifElse.ElseStmt != null) { analyzeSqlStatement(procedureInfo, ifElse.ElseStmt); } } else if (stmt is TMssqlDeclare) { TMssqlDeclare declareStmt = (TMssqlDeclare)stmt; if (declareStmt.Subquery != null && declareStmt.Subquery.ToString().Trim().Length > 0) { analyzeSqlStatement(procedureInfo, declareStmt.Subquery); } } else if (stmt is TMssqlExecute && ((TMssqlExecute)stmt).ModuleName != null) { TMssqlExecute executeStmt = (TMssqlExecute)stmt; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.SP; operateInfo.objectUsed = executeStmt.ModuleName.ToString().Trim(); operateInfo.usageType = usageType.Exec; procedureInfo.operates.Add(operateInfo); } else if (stmt is TCreateTableSqlStatement) { TCreateTableSqlStatement createStmt = (TCreateTableSqlStatement)stmt; TColumnDefinitionList columns = createStmt.ColumnList; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = createStmt.TargetTable.ToString().Trim(); operateInfo.usageType = usageType.Create; for (int i = 0; i < columns.size(); i++) { TColumnDefinition column = columns.getColumn(i); operateInfo.columns.Add(column.ColumnName.ToString()); } procedureInfo.operates.Add(operateInfo); } else if (stmt is TInsertSqlStatement) { TInsertSqlStatement insertStmt = (TInsertSqlStatement)stmt; TObjectNameList columns = insertStmt.ColumnList; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = insertStmt.TargetTable.ToString().Trim(); operateInfo.usageType = usageType.Insert; if (columns != null) { for (int i = 0; i < columns.size(); i++) { TObjectName column = columns.getObjectName(i); operateInfo.columns.Add(column.ToString()); } } procedureInfo.operates.Add(operateInfo); // if (insertStmt.ExecStmt != null) // { // analyzeSqlStatement(procedureInfo, insertStmt.ExecStmt); // } } else if (stmt is TUpdateSqlStatement) { TUpdateSqlStatement updateStmt = (TUpdateSqlStatement)stmt; TResultColumnList columns = updateStmt.ResultColumnList; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = updateStmt.TargetTable.ToString().Trim(); operateInfo.usageType = usageType.Update; for (int i = 0; i < columns.size(); i++) { TResultColumn column = columns.getResultColumn(i); operateInfo.columns.Add(column.Expr.LeftOperand.ToString()); } procedureInfo.operates.Add(operateInfo); } else if (stmt is TDeleteSqlStatement) { TDeleteSqlStatement deleteStmt = (TDeleteSqlStatement)stmt; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = deleteStmt.TargetTable.ToString().Trim(); operateInfo.usageType = usageType.Delete; procedureInfo.operates.Add(operateInfo); } else if (stmt is TMssqlDropTable) { TMssqlDropTable dropStmt = (TMssqlDropTable)stmt; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = dropStmt.TargetTable.ToString().Trim(); operateInfo.usageType = usageType.Drop; procedureInfo.operates.Add(operateInfo); } else if (stmt is TDropTableSqlStatement) { TDropTableSqlStatement dropStmt = (TDropTableSqlStatement)stmt; operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = dropStmt.TableName.ToString().Trim(); operateInfo.usageType = usageType.Drop; procedureInfo.operates.Add(operateInfo); } else if (stmt is TSelectSqlStatement) { TSelectSqlStatement selectStmt = (TSelectSqlStatement)stmt; List <columnInfo> columnInfos = new List <columnInfo>(); List <tableInfo> tableInfos = new List <tableInfo>(); tableTokensInStmt(columnInfos, tableInfos, selectStmt); LinkedHashMap <tableInfo, List <columnInfo> > columnMap = new LinkedHashMap <tableInfo, List <columnInfo> >(); for (int i = 0; i < columnInfos.Count; i++) { columnInfo column = columnInfos[i]; tableInfo table = column.table; if (columnMap.ContainsKey(table)) { List <columnInfo> columns = (List <columnInfo>)columnMap[table]; bool flag = false; foreach (columnInfo temp in columns) { if (temp.ToString().Equals(column.ToString(), StringComparison.CurrentCultureIgnoreCase)) { flag = true; break; } } if (!flag) { columns.Add(column); } } else { List <columnInfo> columns = new List <columnInfo>(); columnMap[table] = columns; columns.Add(column); } } for (int i = 0; i < tableInfos.Count; i++) { operateInfo operateInfo = new operateInfo(); operateInfo.objectType = objectType.Table; operateInfo.objectUsed = tableInfos[i].ToString(); if (tableInfos[i].stmt is TSelectSqlStatement && ((TSelectSqlStatement)tableInfos[i].stmt).IntoClause != null) { operateInfo.usageType = usageType.Insert; } else { operateInfo.usageType = usageType.Read; } if (columnMap.ContainsKey(tableInfos[i])) { foreach (columnInfo column in (List <columnInfo>)columnMap[tableInfos[i]]) { operateInfo.columns.Add(column.ToString()); operateInfo.objectUsed = column.table.ToString(); } } procedureInfo.operates.Add(operateInfo); } } }