Beispiel #1
0
        public virtual void testReplaceOrderByItemAndAddSortType()
        {
            parser.sqltext = "SELECT * FROM TABLE_X order by a";
            Assert.IsTrue(parser.parse() == 0);
            TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            select.OrderbyClause.Items.removeElementAt(0);
            TOrderBy orderBy = select.OrderbyClause;

            TOrderByItem orderByItem = new TOrderByItem();

            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("b");
            orderByItem.SortOrder = ESortType.asc;

            orderByItem = new TOrderByItem();
            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a1");
            orderByItem.SortOrder = ESortType.desc;



            //System.out.println(scriptGenerator.generateScript(select,true));
            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "ORDER BY b ASC,\n" +
                                                           "         a1 DESC"
                                                           ));
        }
        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.removeOrderByItem(i);
                    break;
                }
            }

            if (orderBy.Items.Count == 0)
            {
                select.OrderbyClause = null;
            }
        }
        public virtual void printColumns(TOrderBy orderBy, TCustomSqlStatement statement)
        {
            Console.WriteLine("Referenced columns:");
            orderByVisitor obv = new orderByVisitor(statement);

            orderBy.accept(obv);
        }
Beispiel #4
0
        public virtual void testAddOrderByClause()
        {
            parser.sqltext = "SELECT * FROM TABLE_X";
            Assert.IsTrue(parser.parse() == 0);
            TSelectSqlStatement select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            TOrderBy orderBy = new TOrderBy();

            select.OrderbyClause = orderBy;
            TOrderByItem orderByItem = new TOrderByItem();

            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a");
            orderByItem.SortOrder = ESortType.desc;

            //System.out.println(scriptGenerator.generateScript(select,true));
            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "ORDER BY a DESC"
                                                           ));

            parser.sqltext = "SELECT * FROM TABLE_X where a>1";
            Assert.IsTrue(parser.parse() == 0);
            select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            orderBy = new TOrderBy();
            select.OrderbyClause = orderBy;
            orderByItem          = new TOrderByItem();
            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a");
            orderByItem.SortOrder = ESortType.desc;

            //  System.out.println(scriptGenerator.generateScript(select,true));
            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "WHERE    a > 1\n" +
                                                           "ORDER BY a DESC"
                                                           ));

            parser.sqltext = "SELECT * FROM TABLE_X where a>1 group by a having count(*) > 1";
            Assert.IsTrue(parser.parse() == 0);
            select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            orderBy = new TOrderBy();
            select.OrderbyClause = orderBy;
            orderByItem          = new TOrderByItem();
            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a");
            orderByItem.SortOrder = ESortType.asc;

            // System.out.println(scriptGenerator.generateScript(select,true));
            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "WHERE    a > 1\n" +
                                                           "GROUP BY a\n" +
                                                           "HAVING  count(*) > 1\n" +
                                                           "ORDER BY a ASC"
                                                           ));

            parser.sqltext = "SELECT * FROM TABLE_X where a>1 group by a having count(*) > 1 order by c desc";
            Assert.IsTrue(parser.parse() == 0);
            select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            orderByItem = new TOrderByItem();
            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a");
            orderByItem.SortOrder = ESortType.asc;
            select.OrderbyClause.Items.addOrderByItem(orderByItem);
            //  System.out.println(scriptGenerator.generateScript(select,true));
            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "WHERE    a > 1\n" +
                                                           "GROUP BY a\n" +
                                                           "HAVING  count(*) > 1\n" +
                                                           "ORDER BY c DESC,\n" +
                                                           "         a ASC"
                                                           ));

            parser.sqltext = "SELECT * FROM TABLE_X";
            Assert.IsTrue(parser.parse() == 0);
            select = (TSelectSqlStatement)parser.sqlstatements.get(0);

            TWhereClause whereClause = new TWhereClause();

            whereClause.Condition = parser.parseExpression("a>1 and b>2");
            select.WhereClause    = whereClause;
            //select.addWhereClause("a>1 and b>2") ;

            orderBy = new TOrderBy();
            select.OrderbyClause = orderBy;
            orderByItem          = new TOrderByItem();
            orderBy.Items.addElement(orderByItem);
            orderByItem.SortKey   = parser.parseExpression("a");
            orderByItem.SortOrder = ESortType.desc;

            //System.out.println(scriptGenerator.generateScript(select,true));

            Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle
                                                           , select.ToScript()
                                                           , "SELECT   *\n" +
                                                           "FROM     table_x\n" +
                                                           "WHERE    a > 1\n" +
                                                           "         AND b > 2\n" +
                                                           "ORDER BY a DESC"
                                                           ));
        }