예제 #1
0
        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);
        }
예제 #2
0
        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);
                }
            }
        }
예제 #3
0
        public static void doIt()
        {
            TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvmssql);

            sqlparser.sqltext = "select employee_id,last_name,sal\n" + "from employees\n" + "where department_id = 90\n" + "group by employee_id having sal>10\n" + "order by last_name;";

            sqlparser.parse();

            TSelectSqlStatement select = (TSelectSqlStatement)sqlparser.sqlstatements.get(0);

            gudusoft.gsqlparser.nodes.TTable table = select.tables.getTable(0);
            TObjectName o;

            Console.WriteLine("Select statement, find out what clause a TObjectName belongs to:");
            for (int i = 0; i < table.LinkedColumns.size(); i++)
            {
                o = table.LinkedColumns.getObjectName(i);
                Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
            }

            sqlparser.sqltext = "insert into emp e1 (e1.lastname,job) values('scott',10);";
            sqlparser.parse();

            TInsertSqlStatement insert = (TInsertSqlStatement)sqlparser.sqlstatements.get(0);

            table = insert.tables.getTable(0);

            Console.WriteLine("\n\nInsert statement, find out what clause a TObjectName belongs to:");
            for (int i = 0; i < table.LinkedColumns.size(); i++)
            {
                o = table.LinkedColumns.getObjectName(i);
                Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
            }

            sqlparser.sqltext = "update employees\n" + "set department_ID = 70\n" + "where employee_id = 113;";
            sqlparser.parse();


            TUpdateSqlStatement update = (TUpdateSqlStatement)sqlparser.sqlstatements.get(0);

            table = update.tables.getTable(0);
            Console.WriteLine("\n\nUpdate statement, find out what clause a TObjectName belongs to:");
            for (int i = 0; i < table.LinkedColumns.size(); i++)
            {
                o = table.LinkedColumns.getObjectName(i);
                Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
            }

            sqlparser.sqltext = "delete from employees E\n" + "where employee_id = \n" + "(select employee_sal\n" + "from emp_history\n" + "where employee_id = e.employee_id);";
            sqlparser.parse();


            TDeleteSqlStatement delete = (TDeleteSqlStatement)sqlparser.sqlstatements.get(0);

            table = delete.tables.getTable(0);

            Console.WriteLine("\n\nDelete statement, find out what clause a TObjectName belongs to:");
            for (int i = 0; i < table.LinkedColumns.size(); i++)
            {
                o = table.LinkedColumns.getObjectName(i);
                Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
            }


            // subquery in where clause
            select = (TSelectSqlStatement)delete.Statements.get(0);
            gudusoft.gsqlparser.nodes.TTable table1 = select.tables.getTable(0);
            Console.WriteLine("\nSubquery in delete statement, find out what clause a TObjectName belongs to:");
            for (int i = 0; i < table1.LinkedColumns.size(); i++)
            {
                o = table1.LinkedColumns.getObjectName(i);
                Console.WriteLine(o.ToString() + "\t\t\tlocation:" + o.Location);
            }
        }