} // parseTableItem()

        private FromItem parseAllJoinItems(String itemToken)
        {
            String[]      separators = { "(?i) JOIN " };
            String[]      joinSplit  = itemToken.Split(separators, StringSplitOptions.RemoveEmptyEntries);
            List <String> joinsList  = new List <String>();

            for (int i = 0; i < joinSplit.Length - 1; i++)
            {
                joinSplit[i]     = joinSplit[i].Trim();
                joinSplit[i + 1] = joinSplit[i + 1].Trim();
                String leftPart  = joinSplit[i].Substring(0, joinSplit[i].LastIndexOf(" "));
                String joinType  = joinSplit[i].Substring(joinSplit[i].LastIndexOf(" "));
                String rightPart = (i + 1 == joinSplit.Length - 1) ?
                                   joinSplit[i + 1] :
                                   joinSplit[i + 1].Substring(0, joinSplit[i + 1].LastIndexOf(" "));
                joinsList.Add((leftPart + " " + joinType + " JOIN " + rightPart).Replace(" +", " "));
                String rightTable   = rightPart.Substring(0, rightPart.ToUpper().LastIndexOf(" ON "));
                String nextJoinType = joinSplit[i + 1].Substring(joinSplit[i + 1].LastIndexOf(" "));
                joinSplit[i + 1] = rightTable + " " + nextJoinType;
            }
            HashSet <FromItem> fromItems    = new HashSet <FromItem>();
            FromItem           leftFromItem = null;

            foreach (String token in joinsList)
            {
                leftFromItem = parseJoinItem(leftFromItem, token, fromItems);
            }
            return(leftFromItem);
        } // parseAllJoinItems()
        }                             // getTableFromItems()

        public static List <FromItem> getTableFromItems(FromItem item)
        {
            List <FromItem> result = new List <FromItem>();

            if (item.getTable() != null)
            {
                result.Add(item);
            }
            else if (item.getSubQuery() != null)
            {
                FromItem[] sqItems = getTableFromItems(item.getSubQuery());
                for (int i = 0; i < sqItems.Length; i++)
                {
                    result.Add(sqItems[i]);
                }
            }
            else if (item.getJoin() != JoinType.None)
            {
                FromItem leftSide = item.getLeftSide();
                result.AddRange(getTableFromItems(leftSide));

                FromItem rightSide = item.getRightSide();
                result.AddRange(getTableFromItems(rightSide));
            }
            else
            {
                throw new InvalidOperationException("FromItem was neither of Table type, SubQuery type or Join type: " + item);
            }
            return(result);
        }     // getTableFromItems()
示例#3
0
        public DBRefList <T> Retrieve <T>(Clause clause = null) where T : TableEntity
        {
            var script = new SQLScript(Accessor, Commons.Objects.Enums.SQLTYPE.SELECT);
            var from   = new FromItem <T>(Accessor);
            var select = new SelectItem(Accessor, from);

            script.AddItems(select, from);
            if (clause != null)
            {
                var where = new WhereItem(Accessor, clause, from);
                script.AddItems(where);
            }

            var list = new DBRefList <T>();

            var table = Accessor.Retrieve(script.ExportCommand());

            if (table != null && table.Rows.Count > 0)
            {
                foreach (var row in table.Rows)
                {
                    list.NewEntity().LoadFrom(row as DataRow, 0);
                }
            }
            return(list);
        }
示例#4
0
        private string BuildSQLText_SELECT()
        {
            if (_list.Count <= 0)
            {
                return(null);
            }

            var buffer = new StringBuilder();

            //1.Parts:SELECT
            if (!_list.Any(itm => itm is SelectItem))
            {
                throw new ApplicationException("The script is missing select-item!");
            }
            SelectItem select = null;

            _list.Where(itm => itm is SelectItem).ToList().ForEach(itm =>
            {
                select = select == null ? (SelectItem)itm : select + (SelectItem)itm;
            });
            buffer.AppendLine(select.Text);

            //2.Parts:FROM
            if (!_list.Any(itm => itm is FromItem))
            {
                throw new ApplicationException("The script is missing from-item!");
            }
            if (_list.Count(itm => itm is FromItem) > 1)
            {
                throw new ApplicationException("The script contains multiple from-items!");
            }
            FromItem from = _list.First(itm => itm is FromItem) as FromItem;

            buffer.AppendLine(from.Text);

            //3.Parts:JOIN
            if (_list.Any(itm => itm is JoinItem))
            {
                _list.Where(itm => itm is JoinItem).ToList().ForEach(join =>
                {
                    buffer.AppendLine(join.Text);
                });
            }

            //4.Parts:WHERE
            //if (!_list.Any(itm => itm is WhereItem)) throw new ApplicationException("The script is missing where-item!");
            WhereItem where = null;
            if (_list.Any(itm => itm is WhereItem))
            {
                _list.Where(itm => itm is WhereItem).ToList().ForEach(itm =>
                {
                    where = where == null ? (WhereItem)itm : where + (WhereItem)itm;
                });
                buffer.AppendLine(where.Text);
            }

            return(buffer.ToString());
        }
        public override QsiTableNode VisitFromItem(FromItem item)
        {
            if (item.getAlias()?.getAliasColumns()?.size() > 0)
            {
                throw new QsiException(QsiError.Syntax);
            }

            return(base.VisitFromItem(item));
        }
        } // parseAllJoinItems()

        // this method will be documented based on this example itemToken: FOO f
        // INNER JOIN BAR b ON f.id = b.id
        private FromItem parseJoinItem(FromItem leftFromItem, String itemToken, HashSet <FromItem> fromItems)
        {
            int indexOfJoin = itemToken.ToUpper().IndexOf(" JOIN ");

            // firstPart = "FOO f INNER"
            String firstPart = itemToken.Substring(0, indexOfJoin).Trim();

            // secondPart = "BAR b ON f.id = b.id"
            String secondPart = itemToken.Substring(indexOfJoin + " JOIN ".Length).Trim();

            int indexOfJoinType = firstPart.LastIndexOf(" ");

            // joinTypeString = "INNER"
            String   joinTypeString = firstPart.Substring(indexOfJoinType).Trim().ToUpper();
            JoinType joinType       = (JoinType)Enum.Parse(typeof(JoinType), joinTypeString);

            // firstTableToken = "FOO f"
            String firstTableToken = firstPart.Substring(0, indexOfJoinType).Trim();

            int indexOfOn = secondPart.ToUpper().IndexOf(" ON ");

            // secondTableToken = "BAR b"
            String secondTableToken = secondPart.Substring(0, indexOfOn).Trim();

            FromItem leftSide  = parseTableItem(firstTableToken);
            FromItem rightSide = parseTableItem(secondTableToken);

            fromItems.Add(leftSide);
            fromItems.Add(rightSide);

            // onClausess = ["f.id = b.id"]
            String[]     separators = { " AND " };
            String[]     onClauses  = secondPart.Substring(indexOfOn + " ON ".Length).Split(separators, StringSplitOptions.RemoveEmptyEntries);
            SelectItem[] leftOn     = new SelectItem[onClauses.Length];
            SelectItem[] rightOn    = new SelectItem[onClauses.Length];
            for (int i = 0; i < onClauses.Length; i++)
            {
                String onClause      = onClauses[i];
                int    indexOfEquals = onClause.IndexOf("=");
                // leftPart = "f.id"
                String leftPart = onClause.Substring(0, indexOfEquals).Trim();
                // rightPart = "b.id"
                String rightPart = onClause.Substring(indexOfEquals + 1).Trim();

                FromItem[] items = new FromItem[fromItems.Count];
                fromItems.CopyTo(items);
                leftOn[i]  = findSelectItem(leftPart, items);
                rightOn[i] = findSelectItem(rightPart, items);
            }

            FromItem leftItem = (leftFromItem != null) ? leftFromItem : leftSide;
            FromItem result   = new FromItem(joinType, leftItem, rightSide, leftOn, rightOn);

            result.setQuery(_query);
            return(result);
        } // parseJoinItem()
示例#7
0
        public List <FromItem> ParseTableSources(TableSourcesContext node)
        {
            List <FromItem> fromItems = new List <FromItem>();

            TableSourceContext[] tables = node.tableSource();

            foreach (TableSourceContext table in tables)
            {
                if (table is TableSourceBaseContext tb)
                {
                    TableSourceItemContext tsi = tb.tableSourceItem();
                    FromItem fromItem          = new FromItem();

                    if (tsi is SubqueryTableItemContext subquery)
                    {
                        fromItem.SubSelectStatement = this.ParseSelectStatement(subquery.selectStatement());

                        UidContext uid = subquery.uid();

                        if (uid != null)
                        {
                            fromItem.Alias = new TokenInfo(uid)
                            {
                                Type = TokenType.Alias
                            };
                        }
                    }
                    else
                    {
                        TableName tableName = this.ParseTableName(tsi);

                        fromItem.TableName = tableName;

                        JoinPartContext[] joins = tb.joinPart();

                        if (joins != null && joins.Length > 0)
                        {
                            foreach (JoinPartContext join in joins)
                            {
                                JoinItem joinItem = this.ParseJoin(join);

                                fromItem.JoinItems.Add(joinItem);
                            }
                        }
                    }

                    fromItems.Add(fromItem);
                }
            }

            return(fromItems);
        }
示例#8
0
        } // constructor

        // Override
        public void parse(String delim, String itemToken) // throws MetaModelException
        {
            if ("*".Equals(itemToken))
            {
                _query.selectAll();
                return;
            }

            String alias        = null;
            int    indexOfAlias = itemToken.ToUpper().LastIndexOf(" AS ");

            if (indexOfAlias != -1)
            {
                alias     = itemToken.Substring(indexOfAlias + " AS ".Length);
                itemToken = itemToken.Substring(0, indexOfAlias).Trim();
            }

            try
            {
                SelectItem selectItem = findSelectItem(itemToken);
                if (selectItem == null)
                {
                    throw new QueryParserException("Not capable of parsing SELECT token: " + itemToken);
                }

                if (alias != null)
                {
                    selectItem.setAlias(alias);
                }

                _query.select(selectItem);
            }
            catch (MultipleSelectItemsParsedException e)
            {
                FromItem fromItem = e.getFromItem();
                if (fromItem != null)
                {
                    _query.selectAll(fromItem);
                }
                else
                {
                    throw e;
                }
            }
        } // parse()
        } // constructor

        // @Override
        public void parse(String delim, String itemToken)
        {
            FromItem fromItem;

            int parenthesisStart = itemToken.IndexOf('(');

            if (parenthesisStart != -1)
            {
                if (parenthesisStart != 0)
                {
                    throw new QueryParserException("Not capable of parsing FROM token: " + itemToken
                                                   + ". Expected parenthesis to start at first character.");
                }
                int parenthesisEnd = itemToken.IndexOf(')', parenthesisStart);
                if (parenthesisEnd == -1)
                {
                    throw new QueryParserException("Not capable of parsing FROM token: " + itemToken
                                                   + ". Expected end parenthesis.");
                }

                String subQueryString = itemToken.Substring(parenthesisStart + 1, parenthesisEnd);
                logger.debug("Parsing sub-query: {0}", subQueryString);

                Query subQuery = new QueryParser(_dataContext, subQueryString).parse();
                fromItem = new FromItem(subQuery);

                string alias = itemToken.Substring(parenthesisEnd + 1).Trim();
                if (!alias.IsEmpty())
                {
                    fromItem.setAlias(alias);
                }
            }
            else if (itemToken.ToUpper().IndexOf(" JOIN ") != -1)
            {
                fromItem = parseAllJoinItems(itemToken);
            }
            else
            {
                fromItem = parseTableItem(itemToken);
            }

            _query.from(fromItem);
        } // parse()
        // @Override
        public SatisfiedFromBuilder on(Column left, Column right) // throws IllegalArgumentException
        {
            if (left == null)
            {
                throw new ArgumentException("left cannot be null");
            }

            if (right == null)
            {
                throw new ArgumentException("right cannot be null");
            }
            getQuery().getFromClause().removeItem(leftItem);

            SelectItem [] leftOn   = new SelectItem[] { new SelectItem(left) };
            SelectItem[]  rightOn  = new SelectItem[] { new SelectItem(right) };
            FromItem      fromItem = new FromItem(joinType, leftItem, rightItem, leftOn, rightOn);

            getQuery().from(fromItem);

            return(this);
        }
        private Column findColumnInAliasedTable(Column column, FromItem fromItem, String aliasPart, String columnPart)
        {
            if (column != null)
            {
                // ensure that if the column has already been found, return it
                return(column);
            }

            Table table = fromItem.getTable();

            if (table != null)
            {
                String alias = fromItem.getAlias();
                if (alias != null && alias.Equals(aliasPart))
                {
                    column = table.getColumnByName(columnPart);
                }
            }
            else
            {
                FromItem leftSide = fromItem.getLeftSide();
                column = findColumnInAliasedTable(column, leftSide, aliasPart, columnPart);
                FromItem rightSide = fromItem.getRightSide();
                column = findColumnInAliasedTable(column, rightSide, aliasPart, columnPart);
                if (column != null)
                {
                    Query subQuery = fromItem.getSubQuery();
                    if (subQuery != null)
                    {
                        List <FromItem> items = subQuery.getFromClause().getItems();
                        foreach (FromItem subQueryFromItem in items)
                        {
                            column = findColumnInAliasedTable(column, subQueryFromItem, aliasPart, columnPart);
                        }
                    }
                }
            }

            return(column);
        }
        } // constructor

        public DataSet executeQuery(Query query)
        {
            List <SelectItem>  selectItems        = query.getSelectClause().getItems();
            List <FromItem>    fromItems          = query.getFromClause().getItems();
            List <FilterItem>  whereItems         = query.getWhereClause().getItems();
            List <SelectItem>  whereSelectItems   = query.getWhereClause().getEvaluatedSelectItems();
            List <GroupByItem> groupByItems       = query.getGroupByClause().getItems();
            List <SelectItem>  groupBySelectItems = query.getGroupByClause().getEvaluatedSelectItems();
            List <SelectItem>  havingSelectItems  = query.getHavingClause().getEvaluatedSelectItems();
            List <SelectItem>  orderBySelectItems = query.getOrderByClause().getEvaluatedSelectItems();

            List <FilterItem>  havingItems  = query.getHavingClause().getItems();
            List <OrderByItem> orderByItems = query.getOrderByClause().getItems();

            int firstRow = (query.getFirstRow() == null ? 1  : query.getFirstRow());
            int maxRows  = (query.getMaxRows() == null ? -1 : query.getMaxRows());

            if (maxRows == 0)
            {
                // no rows requested - no reason to do anything
                return(new EmptyDataSet(selectItems));
            }

            // check certain common query types that can often be optimized by
            // subclasses
            bool singleFromItem = fromItems.Count == 1;
            bool noGrouping     = groupByItems.IsEmpty() && havingItems.IsEmpty();

            if (singleFromItem && noGrouping)
            {
                FromItem fromItem = query.getFromClause().getItem(0);
                Table    table    = fromItem.getTable();
                if (table != null)
                {
                    // check for SELECT COUNT(*) queries
                    if (selectItems.Count == 1)
                    {
                        SelectItem selectItem = query.getSelectClause().getItem(0);
                        if (SelectItem.isCountAllItem(selectItem))
                        {
                            bool functionApproximationAllowed = selectItem.isFunctionApproximationAllowed();
                            if (isMainSchemaTable(table))
                            {
                                logger.debug("Query is a COUNT query with {} where items. Trying executeCountQuery(...)",
                                             whereItems.Count);
                                NNumber count = executeCountQuery(table, whereItems, functionApproximationAllowed);
                                if (count == null)
                                {
                                    logger.debug(
                                        "DataContext did not return any count query results. Proceeding with manual counting.");
                                }
                                else
                                {
                                    List <Row>    data   = new List <Row>(1);
                                    DataSetHeader header = new SimpleDataSetHeader(new SelectItem[] { selectItem });
                                    data.Add(new DefaultRow(header, new Object[] { count }));
                                    return(new InMemoryDataSet(header, data));
                                }
                            }
                        }
                    }

                    bool is_simple_select = isSimpleSelect(query.getSelectClause());
                    if (is_simple_select)
                    {
                        // check for lookup query by primary key
                        if (whereItems.Count == 1)
                        {
                            FilterItem whereItem  = whereItems[0];
                            SelectItem selectItem = whereItem.getSelectItem();
                            if (!whereItem.isCompoundFilter() && selectItem != null && selectItem.getColumn() != null)
                            {
                                Column column = selectItem.getColumn();
                                if (column.isPrimaryKey() && OperatorType.EQUALS_TO.Equals(whereItem.getOperator()))
                                {
                                    logger.debug(
                                        "Query is a primary key lookup query. Trying executePrimaryKeyLookupQuery(...)");
                                    if (table != null)
                                    {
                                        if (isMainSchemaTable(table))
                                        {
                                            Object operand = whereItem.getOperand();
                                            Row    row     = executePrimaryKeyLookupQuery(table, selectItems, column, operand);
                                            if (row == null)
                                            {
                                                logger.debug(
                                                    "DataContext did not return any GET query results. Proceeding with manual lookup.");
                                            }
                                            else
                                            {
                                                DataSetHeader header = new SimpleDataSetHeader(selectItems);
                                                return(new InMemoryDataSet(header, row));
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        // check for simple queries with or without simple criteria
                        if (orderByItems.IsEmpty())
                        {
                            DataSet ds = null;

                            // no WHERE criteria set
                            if (whereItems.IsEmpty())
                            {
                                ds = materializeTable(table, selectItems, firstRow, maxRows);
                                return(ds);
                            }

                            ds = materializeTable(table, selectItems, whereItems, firstRow, maxRows);
                            return(ds);
                        }
                    }
                }
            }

            // Creates a list for all select items that are needed to execute query
            // (some may only be used as part of a filter, but not shown in result)
            List <SelectItem> workSelectItems = CollectionUtils.concat(true, selectItems, whereSelectItems,
                                                                       groupBySelectItems, havingSelectItems, orderBySelectItems);

            // Materialize the tables in the from clause
            DataSet[] fromDataSets = new DataSet[fromItems.Count];
            for (int i = 0; i < fromDataSets.Length; i++)
            {
                FromItem fromItem = fromItems[i];
                fromDataSets[i] = materializeFromItem(fromItem, workSelectItems);
            }

            // Execute the query using the raw data
            DataSet dataSet = null; // MetaModelHelper.getCarthesianProduct(fromDataSets, whereItems);

            // we can now exclude the select items imposed by the WHERE clause (and
            // should, to make the aggregation process faster)
            workSelectItems = CollectionUtils.concat(true, selectItems, groupBySelectItems, havingSelectItems,
                                                     orderBySelectItems);

            if (groupByItems.Count > 0)
            {
                dataSet = MetaModelHelper.getGrouped(workSelectItems, dataSet, groupByItems);
            }
            else
            {
                dataSet = MetaModelHelper.getAggregated(workSelectItems, dataSet);
            }
            dataSet = MetaModelHelper.getFiltered(dataSet, havingItems);

            if (query.getSelectClause().isDistinct())
            {
                dataSet = MetaModelHelper.getSelection(selectItems, dataSet);
                dataSet = MetaModelHelper.getDistinct(dataSet);
                dataSet = MetaModelHelper.getOrdered(dataSet, orderByItems);
            }
            else
            {
                dataSet = MetaModelHelper.getOrdered(dataSet, orderByItems);
                dataSet = MetaModelHelper.getSelection(selectItems, dataSet);
            }

            dataSet = MetaModelHelper.getPaged(dataSet, firstRow, maxRows);
            return(dataSet);
        } // executeQuery()
        } // executePrimaryKeyLookupQuery()

        protected DataSet materializeFromItem(FromItem fromItem, List <SelectItem> selectItems)
        {
            DataSet  dataSet;
            JoinType joinType = fromItem.getJoin();

            if (fromItem.getTable() != null)
            {
                // We need to materialize a single table
                Table             table = fromItem.getTable();
                List <SelectItem> selectItemsToMaterialize = new List <SelectItem>();

                foreach (SelectItem selectItem in selectItems)
                {
                    FromItem selectedFromItem = selectItem.getFromItem();
                    if (selectedFromItem != null)
                    {
                        if (selectedFromItem.equals(fromItem))
                        {
                            selectItemsToMaterialize.Add(selectItem.replaceFunction(null));
                        }
                    }
                    else
                    {
                        // the select item does not specify a specific
                        // from-item
                        Column selectedColumn = selectItem.getColumn();
                        if (selectedColumn != null)
                        {
                            // we assume that if the table matches, we will use the
                            // column
                            if (selectedColumn.getTable() != null && selectedColumn.getTable().Equals(table))
                            {
                                selectItemsToMaterialize.Add(selectItem.replaceFunction(null));
                            }
                        }
                    }
                }

                if (logger.isDebugEnabled())
                {
                    logger.debug("calling materializeTable(" + table.getName() + "," + selectItemsToMaterialize + ",1,-1");
                }

                // Dispatching to the concrete subclass of
                // QueryPostprocessDataContextStrategy
                dataSet = materializeTable(table, selectItemsToMaterialize, 1, -1);
            }
            else if (joinType != JoinType.None)
            {
                // We need to (recursively) materialize a joined FromItem
                if (fromItem.getLeftSide() == null || fromItem.getRightSide() == null)
                {
                    throw new ArgumentException("Joined FromItem requires both left and right side: " + fromItem);
                }
                DataSet[] fromItemDataSets = new DataSet[2];

                // materialize left side
                List <SelectItem> leftOn = NArrays.AsList(fromItem.getLeftOn());
                fromItemDataSets[0] = materializeFromItem(fromItem.getLeftSide(),
                                                          CollectionUtils.concat(true, selectItems, leftOn));

                // materialize right side
                List <SelectItem> rightOn = NArrays.AsList(fromItem.getRightOn());
                fromItemDataSets[1] = materializeFromItem(fromItem.getRightSide(),
                                                          CollectionUtils.concat(true, selectItems, rightOn));

                FilterItem[] onConditions = new FilterItem[leftOn.Count];
                for (int i = 0; i < onConditions.Length; i++)
                {
                    FilterItem whereItem = new FilterItem(leftOn[i], OperatorType.EQUALS_TO, rightOn[i]);
                    onConditions[i] = whereItem;
                }

                switch (joinType)
                {
                case JoinType.INNER:
                    dataSet = MetaModelHelper.getCarthesianProduct(fromItemDataSets, onConditions);
                    break;

                case JoinType.LEFT:
                    dataSet = MetaModelHelper.getLeftJoin(fromItemDataSets[0], fromItemDataSets[1], onConditions);
                    break;

                case JoinType.RIGHT:
                    dataSet = MetaModelHelper.getRightJoin(fromItemDataSets[0], fromItemDataSets[1], onConditions);
                    break;

                default:
                    throw new ArgumentException("FromItem type not supported: " + fromItem);
                }
            }
            else if (fromItem.getSubQuery() != null)
            {
                // We need to (recursively) materialize a subquery
                dataSet = executeQuery(fromItem.getSubQuery());
            }
            else
            {
                throw new ArgumentException("FromItem type not supported: " + fromItem);
            }
            if (dataSet == null)
            {
                throw new ArgumentException("FromItem was not succesfully materialized: " + fromItem);
            }
            return(dataSet);
        } // materializeFromItem()
示例#14
0
 public override string ToString()
 {
     return("From: " + (FromItem == null ? "NULL" : FromItem.ToString()) + " -> To: " + (ToItem == null ? "NULL" : ToItem.ToString()));
 }
示例#15
0
 public MultipleSelectItemsParsedException(FromItem fromItem)
 {
     _fromItem = fromItem;
 }
示例#16
0
        } // parse()

        /**
         * Finds/creates a SelectItem based on the given expression. Unlike the
         * {@link #parse(String, String)} method, this method will not actually add
         * the selectitem to the query.
         *
         * @param expression
         * @return
         *
         * @throws MultipleSelectItemsParsedException
         *             if an expression yielding multiple select-items (such as "*")
         *             was passed in the expression
         */
        public SelectItem findSelectItem(String expression) // throws MultipleSelectItemsParsedException
        {
            if ("*".Equals(expression))
            {
                throw new MultipleSelectItemsParsedException(null);
            }

            if ("COUNT(*)".Equals(expression, StringComparison.CurrentCultureIgnoreCase))
            {
                return(SelectItem.getCountAllItem());
            }

            String unmodifiedExpression = expression;

            bool         functionApproximation;
            FunctionType function;

            int startParenthesis = expression.IndexOf('(');

            if (startParenthesis > 0 && expression.EndsWith(")"))
            {
                functionApproximation = (expression.StartsWith(SelectItem.FUNCTION_APPROXIMATION_PREFIX));
                String functionName = expression.Substring(
                    (functionApproximation ? SelectItem.FUNCTION_APPROXIMATION_PREFIX.Length : 0), startParenthesis);
                function = FunctionTypeFactory.get(functionName.ToUpper());
                if (function != null)
                {
                    expression = expression.Substring(startParenthesis + 1, expression.Length - 1).Trim();
                    if (function is CountAggregateFunction && "*".Equals(expression))
                    {
                        SelectItem select_item = SelectItem.getCountAllItem();
                        select_item.setFunctionApproximationAllowed(functionApproximation);
                        return(select_item);
                    }
                }
            }
            else
            {
                function = null;
                functionApproximation = false;
            }

            String   columnName = null;
            FromItem fromItem   = null;

            // attempt to find from item by cutting up the string in prefix and
            // suffix around dot.
            {
                int splitIndex = expression.LastIndexOf('.');
                while (fromItem == null && splitIndex != -1)
                {
                    String prefix = expression.Substring(0, splitIndex);
                    columnName = expression.Substring(splitIndex + 1);
                    fromItem   = _query.getFromClause().getItemByReference(prefix);

                    splitIndex = expression.LastIndexOf('.', splitIndex - 1);
                }
            }

            if (fromItem == null)
            {
                if (_query.getFromClause().getItemCount() == 1)
                {
                    fromItem   = _query.getFromClause().getItem(0);
                    columnName = expression;
                }
                else
                {
                    fromItem   = null;
                    columnName = null;
                }
            }

            if (fromItem != null)
            {
                if ("*".Equals(columnName))
                {
                    throw new MultipleSelectItemsParsedException(fromItem);
                }
                else if (fromItem.getTable() != null)
                {
                    Column column = fromItem.getTable().getColumnByName(columnName);
                    int    offset = -1;
                    while (function == null && column == null)
                    {
                        // check for MAP_VALUE shortcut syntax
                        offset = columnName.IndexOf('.', offset + 1);
                        if (offset == -1)
                        {
                            break;
                        }

                        String part1 = columnName.Substring(0, offset);
                        column = fromItem.getTable().getColumnByName(part1);
                        if (column != null)
                        {
                            String part2 = columnName.Substring(offset + 1);
                            return(new SelectItem(new MapValueFunction(), new Object[] { part2 }, column, fromItem));
                        }
                    }

                    if (column != null)
                    {
                        SelectItem select_item = new SelectItem(function, column, fromItem);
                        select_item.setFunctionApproximationAllowed(functionApproximation);
                        return(select_item);
                    }
                }
                else if (fromItem.getSubQuery() != null)
                {
                    Query      subQuery           = fromItem.getSubQuery();
                    SelectItem subQuerySelectItem = new SelectItemParser(subQuery, _allowExpressionBasedSelectItems)
                                                    .findSelectItem(columnName);
                    if (subQuerySelectItem == null)
                    {
                        return(null);
                    }
                    return(new SelectItem(subQuerySelectItem, fromItem));
                }
            }

            // if the expression is alias of some select item defined return that
            // select item
            SelectItem aliasSelectItem = MetaModelHelper.getSelectItemByAlias(_query, unmodifiedExpression);

            if (aliasSelectItem != null)
            {
                return(aliasSelectItem);
            }

            if (_allowExpressionBasedSelectItems)
            {
                SelectItem select_item = new SelectItem(function, expression, null);
                select_item.setFunctionApproximationAllowed(functionApproximation);
                return(select_item);
            }
            return(null);
        } // findSelectItem()
 public JoinFromBuilderImpl(Query query, FromItem leftItem, Table rightTable, JoinType joinType, DataContext dataContext) : base(query, dataContext)
 {
     this.joinType  = joinType;
     this.leftItem  = leftItem;
     this.rightItem = new FromItem(rightTable);
 }
 public TableFromBuilderImpl(Table table, Query query, DataContext dataContext) : base(query, dataContext)
 {
     _from_item = new FromItem(table);
     query.from(_from_item);
 }
        } // parse()

        private FromItem parseTableItem(String itemToken)
        {
            // From token can be starting with [
            String tableNameToken;
            String aliasToken;

            char startDelimiter = itemToken.Trim()[0];

            if (delimiterMap.ContainsKey(startDelimiter))
            {
                char endDelimiter = delimiterMap[startDelimiter];
                int  endIndex     = itemToken.Trim().LastIndexOf(endDelimiter, itemToken.Trim().Length);
                if (endIndex <= 0)
                {
                    throw new QueryParserException("Not capable of parsing FROM token: " + itemToken + ". Expected end "
                                                   + endDelimiter);
                }
                tableNameToken = itemToken.Trim().Substring(1, endIndex).Trim();

                if (itemToken.Trim().Substring(1 + endIndex).Trim().Equals("", StringComparison.CurrentCultureIgnoreCase))
                {
                    /*
                     * As per code in FromClause Method: getItemByReference(FromItem
                     * item, String reference) if (alias == null && table != null &&
                     * reference.equals(table.getName())) { Either we have to change
                     * the code to add alias.equals("") there or return null here.
                     */
                    aliasToken = null;
                }
                else
                {
                    aliasToken = itemToken.Trim().Substring(1 + endIndex).Trim();
                }
            }
            else
            {
                // Default assumption is space being delimiter for tablename and
                // alias.. If otherwise use DoubleQuotes or [] around tableName
                String[] tokens = itemToken.Split(' ');
                tableNameToken = tokens[0];
                if (tokens.Length == 2)
                {
                    aliasToken = tokens[1];
                }
                else if (tokens.Length == 1)
                {
                    aliasToken = null;
                }
                else
                {
                    throw new QueryParserException("Not capable of parsing FROM token: " + itemToken);
                }
            }

            Table table = _dataContext.getTableByQualifiedLabel(tableNameToken);

            if (table == null)
            {
                throw new QueryParserException("Not capable of parsing FROM token: " + itemToken);
            }

            FromItem result = new FromItem(table);

            result.setAlias(aliasToken);
            result.setQuery(_query);
            return(result);
        } // parseTableItem()
        public override StatementScriptBuilder Build(Statement statement, bool appendSeparator = true)
        {
            base.Build(statement, appendSeparator);

            if (statement is SelectStatement select)
            {
                this.BuildSelectStatement(select, appendSeparator);
            }
            else if (statement is InsertStatement insert)
            {
                this.Append($"INSERT INTO {insert.TableName}", false);

                if (insert.Columns.Count > 0)
                {
                    this.AppendLine($"({ string.Join(",", insert.Columns.Select(item => item.ToString()))})");
                }

                if (insert.SelectStatements != null && insert.SelectStatements.Count > 0)
                {
                    this.AppendChildStatements(insert.SelectStatements, true);
                }
                else
                {
                    this.AppendLine($"VALUES({string.Join(",", insert.Values.Select(item => item))});");
                }
            }
            else if (statement is UnionStatement union)
            {
                this.AppendLine(this.GetUnionTypeName(union.Type));
                this.Build(union.SelectStatement);
            }
            else if (statement is UpdateStatement update)
            {
                List <TokenInfo> tableNames = new List <TokenInfo>();

                #region Handle for mysql and plsql update join
                if (update.TableNames.Count == 1)
                {
                    string[] items = update.TableNames[0].Symbol.Split(' ');

                    if (items.Length > 1)
                    {
                        string alias = items[1];

                        bool added = false;

                        foreach (NameValueItem nameValue in update.SetItems)
                        {
                            if (nameValue.Name.Symbol.Trim().StartsWith(alias + ".", StringComparison.OrdinalIgnoreCase))
                            {
                                if (!added)
                                {
                                    tableNames.Add(new TokenInfo(alias));
                                    added = true;
                                }

                                if (nameValue.Value.Symbol?.IndexOf("SELECT", StringComparison.OrdinalIgnoreCase) > 0)
                                {
                                    string from = nameof(TSqlParser.FROM);
                                    string where = nameof(TSqlParser.WHERE);;

                                    string oldValue  = nameValue.Value.Symbol;
                                    int    fromIndex = oldValue.ToUpper().IndexOf(from);

                                    nameValue.Value.Symbol = Regex.Replace(oldValue.Substring(0, fromIndex), "SELECT ", "", RegexOptions.IgnoreCase).Trim(' ', '(');

                                    if (update.FromItems == null || update.FromItems.Count == 0)
                                    {
                                        update.FromItems = new List <FromItem>();

                                        FromItem fromItem = new FromItem()
                                        {
                                            TableName = update.TableNames[0]
                                        };

                                        int whereIndex = oldValue.ToUpper().IndexOf(where);

                                        string tableName = oldValue.Substring(fromIndex + from.Length, whereIndex - (fromIndex + from.Length) - 1);
                                        string condition = oldValue.Substring(whereIndex + where.Length).Trim(')');

                                        fromItem.JoinItems.Add(new JoinItem()
                                        {
                                            TableName = new TableName(tableName), Condition = new TokenInfo(condition)
                                        });

                                        update.FromItems.Add(fromItem);
                                    }
                                }
                            }
                        }
                    }
                }
                #endregion

                if (tableNames.Count == 0)
                {
                    tableNames.AddRange(update.TableNames);
                }

                this.AppendLine($"UPDATE {string.Join(",", tableNames)} SET");

                this.AppendLine(string.Join("," + Environment.NewLine + indent, update.SetItems.Select(item => $"{item.Name}={item.Value}")));

                if (update.FromItems != null && update.FromItems.Count > 0)
                {
                    int i = 0;
                    foreach (FromItem fromItem in update.FromItems)
                    {
                        if (i == 0)
                        {
                            this.AppendLine($"FROM {fromItem.TableName}");
                        }

                        foreach (JoinItem joinItem in fromItem.JoinItems)
                        {
                            string condition = joinItem.Condition == null ? "" : $" ON {joinItem.Condition}";

                            this.AppendLine($"{joinItem.Type} JOIN {joinItem.TableName}{condition}");
                        }

                        i++;
                    }
                }

                if (update.Condition != null && update.Condition.Symbol != null)
                {
                    this.AppendLine($"WHERE {update.Condition}");
                }

                if (update.Option != null)
                {
                    this.AppendLine(update.Option.ToString());
                }

                this.AppendLine(";");
            }
            else if (statement is DeleteStatement delete)
            {
                this.AppendLine($"DELETE FROM {delete.TableName}");

                if (delete.Condition != null)
                {
                    this.AppendLine($"WHERE {delete.Condition}");
                }

                this.AppendLine(";");
            }
            else if (statement is DeclareStatement declare)
            {
                if (declare.Type == DeclareType.Variable)
                {
                    string defaultValue = (declare.DefaultValue == null ? "" : $" = {declare.DefaultValue}");
                    this.AppendLine($"DECLARE {declare.Name} {declare.DataType}{defaultValue};");
                }
                else if (declare.Type == DeclareType.Table)
                {
                    this.AppendLine($"DECLARE {declare.Name} TABLE (");

                    int i = 0;
                    foreach (var column in declare.Table.Columns)
                    {
                        this.AppendLine($"{column.Name} {column.DataType}{(i == declare.Table.Columns.Count - 1 ? "" : ",")}");
                    }

                    this.AppendLine(")");
                }
            }
            else if (statement is SetStatement set)
            {
                if (set.Key != null && set.Value != null)
                {
                    TokenInfo valueToken = set.Value;

                    if (valueToken != null)
                    {
                        if (valueToken.Type == TokenType.RoutineName)
                        {
                            this.MakeupRoutineName(valueToken);
                        }
                        else
                        {
                            TokenInfo child = valueToken.Tokens.FirstOrDefault(item => item.Type == TokenType.RoutineName);

                            if (child != null)
                            {
                                this.MakeupRoutineName(valueToken);
                            }
                        }
                    }

                    this.AppendLine($"SET {set.Key } = {set.Value };");
                }
            }
            if (statement is IfStatement @if)
            {
                foreach (IfStatementItem item in @if.Items)
                {
                    if (item.Type == IfStatementType.IF)
                    {
                        this.AppendLine($"{item.Type} {item.Condition}");
                    }
                    else
                    {
                        this.AppendLine($"{item.Type}");
                    }

                    this.AppendLine("BEGIN");

                    if (item.Statements.Count > 0)
                    {
                        this.AppendChildStatements(item.Statements, true);
                    }
                    else
                    {
                        this.AppendLine("PRINT('BLANK!');");
                    }

                    this.AppendLine("END");
                }
            }
            else if (statement is CaseStatement @case)
            {
                string variableName = @case.VariableName.ToString();

                IfStatement ifStatement = new IfStatement();

                int i = 0;
                foreach (var item in @case.Items)
                {
                    IfStatementItem ifItem = new IfStatementItem();

                    ifItem.Type = i == 0 ? IfStatementType.IF : item.Type;

                    if (item.Type != IfStatementType.ELSE)
                    {
                        ifItem.Condition = new TokenInfo($"{variableName}={item.Condition}")
                        {
                            Type = TokenType.Condition
                        };
                    }

                    i++;
                }

                this.Build(ifStatement);
            }
            else if (statement is LoopStatement loop)
            {
                this.AppendLine($"WHILE {loop.Condition}");
                this.AppendLine("BEGIN");

                this.AppendChildStatements(loop.Statements, true);

                this.AppendLine("END");
            }
            else if (statement is WhileStatement @while)
            {
                this.AppendLine($"WHILE { @while.Condition }");
                this.AppendLine("BEGIN");

                this.AppendChildStatements(@while.Statements, true);

                this.AppendLine("END");
            }
            else if (statement is LoopExitStatement whileExit)
            {
                this.AppendLine($"IF {whileExit.Condition}");
                this.AppendLine("BEGIN");
                this.AppendLine("BREAK");
                this.AppendLine("END");
            }
            else if (statement is TryCatchStatement tryCatch)
            {
                this.AppendLine("BEGIN TRY");
                this.AppendChildStatements(tryCatch.TryStatements, true);
                this.AppendLine("END TRY");

                this.AppendLine("BEGIN CATCH");
                this.AppendChildStatements(tryCatch.CatchStatements, true);
                this.AppendLine("END CATCH");
            }
            else if (statement is ReturnStatement @return)
            {
                this.AppendLine($"RETURN {@return.Value};");
            }
            else if (statement is PrintStatement print)
            {
                this.AppendLine($"PRINT {print.Content.Symbol?.Replace("||", "+")};");
            }
            else if (statement is CallStatement execute)
            {
                this.AppendLine($"EXECUTE {execute.Name} {string.Join(",", execute.Arguments)};");
            }
            else if (statement is TransactionStatement transaction)
            {
                TransactionCommandType commandType = transaction.CommandType;

                switch (commandType)
                {
                case TransactionCommandType.BEGIN:
                    this.AppendLine("BEGIN TRANS");
                    break;

                case TransactionCommandType.COMMIT:
                    this.AppendLine("COMMIT");
                    break;

                case TransactionCommandType.ROLLBACK:
                    this.AppendLine("ROLLBACK");
                    break;
                }
            }
            else if (statement is LeaveStatement leave)
            {
                this.AppendLine("RETURN;");
            }
            else if (statement is DeclareCursorStatement declareCursor)
            {
                this.AppendLine($"DECLARE {declareCursor.CursorName} CURSOR FOR");
                this.Build(declareCursor.SelectStatement);
            }
            else if (statement is OpenCursorStatement openCursor)
            {
                this.AppendLine($"OPEN {openCursor.CursorName}");
            }
            else if (statement is FetchCursorStatement fetchCursor)
            {
                this.AppendLine($"FETCH NEXT FROM {fetchCursor.CursorName} INTO {string.Join(",", fetchCursor.Variables)}");
            }
            else if (statement is CloseCursorStatement closeCursor)
            {
                this.AppendLine($"CLOSE {closeCursor.CursorName}");

                if (closeCursor.IsEnd)
                {
                    this.AppendLine($"DEALLOCATE {closeCursor.CursorName}");
                }
            }
            else if (statement is DeallocateCursorStatement deallocateCursor)
            {
                this.AppendLine($"DEALLOCATE {deallocateCursor.CursorName}");
            }
            else if (statement is TruncateStatement truncate)
            {
                this.AppendLine($"TRUNCATE TABLE {truncate.TableName}");
            }

            return(this);
        }
示例#21
0
        private void FillSelectScript(string name, int level, SQLScriptCollection collection, WhereItem where = null, Stack <JoinItem <TableEntity> > stack = null)
        {
            if (collection == null)
            {
                throw new ArgumentNullException("collection");
            }

            var script = collection.NewSQLScript(level == 0 ? 0.ToString() : string.Format("{0}-{1}", name.Trim(), level), SQLTYPE.SELECT);
            var alias  = collection.NewTableAlias();
            var from   = new FromItem <TableEntity>(collection.accessor, Entity, alias);
            var select = new SelectItem(collection.accessor, from);

            script.AddItems(select, from);

            //Build Jions
            JoinItem <TableEntity>[] join_items = null;
            if (stack != null)
            {
                if (HasForeigns)
                {
                    join_items = new JoinItem <TableEntity> [stack.Count];
                    stack.CopyTo(join_items, 0);
                }

                TableItem table1 = from;
                JoinItem <TableEntity> table2 = null;
                while (stack.Count > 0)
                {
                    table2 = stack.Pop().Clone() as JoinItem <TableEntity>;
                    table2.ON(table1, table2.Keys.ToArray());
                    table2.Entity.AddJoinFilter(table2, collection.SQLParamCreater);
                    script.AddItems(table2);
                    table1 = table2;
                }
            }

            //Build Where
            if (level == 0)
            {
                var where1 = new WhereItem(collection.accessor, from);
                Columns.Where(c => c.IsPK).ToList().ForEach(col =>
                {
                    var param = collection.NewParameter(col.Value);
                    where1.And(where1.ColumnEquals(col.Name, param));
                });
                where = where == null ? where1 : where + where1;
            }
            if (where != null)
            {
                script.AddItem(where.Clone() as WhereItem);
            }
            var where0 = new WhereItem(collection.accessor, from);

            Entity.AddWhereFilter(where0, collection.SQLParamCreater);
            script.AddItem(where0);

            foreach (var foreign in this.Foreigns)
            {
                var nstack = join_items != null ? new Stack <JoinItem <TableEntity> >(join_items) : new Stack <JoinItem <TableEntity> >();
                nstack.Push(new JoinItem <TableEntity>(collection.accessor, Entity, alias, foreign.Keys.Select(key => key.ToKeyValuePair()).ToArray()));
                foreign.EntityInst.Schema.FillSelectScript(foreign.Name, level + 1, collection, where.Clone() as WhereItem, nstack);
            }
        }