Ejemplo n.º 1
0
 /// <summary>
 /// Groups the by.
 /// </summary>
 /// <param name="columns">The columns.</param>
 public void GroupBy(params string[] columns)
 {
     foreach (var column in columns)
     {
         GroupByColumns.Add(column);
     }
 }
Ejemplo n.º 2
0
        /// <summary>
        /// Returns the statement as an SQL string.
        /// </summary>
        public override String ToString()
        {
            var stringBuilder = new StringBuilder("SELECT ");

            if (!Columns.Any())
            {
                Columns.Add("*");
            }

            stringBuilder.AppendLine(String.Join(QueryBuilder.ColumnSeparator, Columns));
            stringBuilder.AppendLine(FromBuilder.ToString());

            if (GroupByColumns.Any())
            {
                stringBuilder.AppendLine("GROUP BY " + String.Join(QueryBuilder.ColumnSeparator, GroupByColumns));
            }

            if (OrderByColumns.Any())
            {
                var orderByColumns = OrderByColumns
                                     .Select(pair => pair.Key + " " + (pair.Value == SortingDirection.Ascending ? "ASC" : "DESC"))
                                     .ToArray();

                stringBuilder.AppendLine("ORDER BY " + String.Join(QueryBuilder.ColumnSeparator, orderByColumns));
            }

            return(stringBuilder.ToString().Trim());
        }
Ejemplo n.º 3
0
 public SelectQuery GroupBy(IEnumerable <string> groupByColumns)
 {
     foreach (string groupByColumn in groupByColumns)
     {
         GroupByColumns.Add(Query.ColumnSyntax(groupByColumn));
     }
     return(this);
 }
Ejemplo n.º 4
0
 public Query GroupBy(params string[] columns)
 {
     foreach (var item in columns)
     {
         GroupByColumns.Add(new Column(item));
     }
     return(this);
 }
Ejemplo n.º 5
0
 /// <inheritdoc />
 public ISelectStringBuilder GroupBy(params string[] columnNames)
 {
     foreach (var columnName in columnNames)
     {
         GroupByColumns.Add(columnName);
     }
     return(this);
 }
Ejemplo n.º 6
0
        /// <summary>
        /// Converts this SoqlQuery into a string format suitable for use in a SODA call.
        /// </summary>
        /// <returns>The string representation of this SoqlQuery.</returns>
        public override string ToString()
        {
            var sb = new StringBuilder();

            sb.AppendFormat("{0}=", SelectKey);

            if (SelectColumns.Length == 1 && SelectColumns[0] == "*")
            {
                sb.Append(SelectColumns[0]);
            }
            else
            {
                //evaluate the provided aliases
                var finalColumns = SelectColumns.Zip(SelectColumnAliases, (c, a) => String.Format("{0} AS {1}", c, a)).ToList();

                if (SelectColumns.Length > SelectColumnAliases.Length)
                {
                    //some columns were left un-aliased
                    finalColumns.AddRange(SelectColumns.Skip(SelectColumnAliases.Length));
                }

                //form the select clause
                sb.Append(String.Join(Delimiter, finalColumns));
            }

            sb.AppendFormat("&{0}={1} {2}", OrderKey, String.Join(Delimiter, OrderByColumns), OrderDirection);

            if (!String.IsNullOrEmpty(WhereClause))
            {
                sb.AppendFormat("&{0}={1}", WhereKey, WhereClause);
            }

            if (GroupByColumns != null && GroupByColumns.Any())
            {
                sb.AppendFormat("&{0}={1}", GroupKey, String.Join(Delimiter, GroupByColumns));
            }

            if (OffsetValue > 0)
            {
                sb.AppendFormat("&{0}={1}", OffsetKey, OffsetValue);
            }

            if (LimitValue > 0)
            {
                sb.AppendFormat("&{0}={1}", LimitKey, LimitValue);
            }

            if (!String.IsNullOrEmpty(SearchText))
            {
                sb.AppendFormat("&{0}={1}", SearchKey, SearchText);
            }

            return(sb.ToString());
        }
Ejemplo n.º 7
0
 public bool IsTrivial()
 {
     return((WhereExpr == null || WhereExpr.Type == ExprType.EMPTY) &&
            (GroupWhereExpr == null || GroupWhereExpr.Type == ExprType.EMPTY) &&
            !Columns.Any() &&
            !OrderByExprs.Any() &&
            !Joins.Any() &&
            RowsLimit == null &&
            RowsOffset == 0 &&
            !DistinctRecords &&
            !GroupByColumns.Any());
 }
Ejemplo n.º 8
0
        internal override void DeleteLinkedObjects(bool isChildOfDeleted)
        {
            // Remove any relationships this column participates in:
            UsedInRelationships.ToList().ForEach(r => r.Delete());

            if (!isChildOfDeleted)
            {
                if (Handler.CompatibilityLevel >= 1400)
                {
                    ObjectLevelSecurity.Clear();
                }

                // Remove any hierarchy levels this column is used in:
                UsedInLevels.ToList().ForEach(l => l.Delete());

                // Make sure the column is no longer used as a Sort By column:
                UsedInSortBy.ToList().ForEach(c => c.SortByColumn = null);

                // Make sure the column is no longer used in any Calculated Tables:
                foreach (var ctc in OriginForCalculatedTableColumns.ToList())
                {
                    ctc.InternalDelete();
                }
            }

            // Make sure the column is no longer used in any Variations:
            if (Handler.CompatibilityLevel >= 1400)
            {
                UsedInVariations.ToList().ForEach(v => v.Delete());
            }

            // Make sure the column is no longer used in AlternateOf's:
            if (Handler.CompatibilityLevel >= 1460)
            {
                UsedInAlternateOfs.ToList().ForEach(a => a.BaseColumn = null);
            }

            if (GroupByColumns != null)
            {
                GroupByColumns.Clear();
            }

            base.DeleteLinkedObjects(isChildOfDeleted);
        }
Ejemplo n.º 9
0
 public SelectQuery GroupBy(string groupByColumn, params string[] groupByColumns)
 {
     GroupByColumns.Add(Query.ColumnSyntax(groupByColumn));
     return(GroupBy(groupByColumns));
 }
Ejemplo n.º 10
0
        /// <summary>
        /// Builds the query string or SQLiteCommand
        /// </summary>
        /// <param name="buildCommand"></param>
        /// <returns></returns>
        protected object BuildQuery(bool buildCommand)
        {
            // Define local variables
            int tableIndex = 0;
            int tableCount = Tables.Count;

            // Make sure we have a table name
            if (Tables.Count == 0 || String.IsNullOrWhiteSpace(Tables[0].Name))
            {
                throw new Exception("No tables were specified for this query.");
            }

            // Start Query
            StringBuilder query = new StringBuilder("SELECT ", 256);

            query.AppendIf(Distinct, "DISTINCT ");

            // Append columns from each table
            foreach (var table in Tables)
            {
                // Define local variables
                int colCount = table.Columns.Count;
                tableIndex++;
                tableCount--;

                // Create alias for this table if there is none
                if (String.IsNullOrWhiteSpace(table.Alias))
                {
                    table.Alias = $"t{tableIndex}";
                }

                // Check if the user wants to select all columns
                if (colCount == 0)
                {
                    query.AppendFormat("{0}.*", Context.QuoteIdentifier(table.Alias));
                    query.AppendIf(tableCount > 0, ", ");
                }
                else
                {
                    // Add each result selector to the query
                    foreach (ColumnIdentifier column in table.Columns.Values)
                    {
                        // Use the internal method to append the column string to our query
                        column.AppendToQuery(query, Context, table.Alias);

                        // If we have more results to select, append Comma
                        query.AppendIf(--colCount > 0 || tableCount > 0, ", ");
                    }
                }
            }

            // === Append main Table === //
            var fromTbl = Tables[0];

            query.Append($" FROM {Context.QuoteIdentifier(fromTbl.Name)} AS {Context.QuoteIdentifier(fromTbl.Alias)}");

            // Append Joined tables
            if (Joins.Count > 0)
            {
                foreach (JoinClause clause in Joins)
                {
                    // Convert join type to string
                    switch (clause.JoinType)
                    {
                    default:
                    case JoinType.InnerJoin:
                        query.Append(" JOIN ");
                        break;

                    case JoinType.OuterJoin:
                        query.Append(" OUTER JOIN ");
                        break;

                    case JoinType.CrossJoin:
                        query.Append(" CROSS JOIN ");
                        break;

                    case JoinType.LeftJoin:
                        query.Append(" LEFT JOIN ");
                        break;
                    }

                    // Append the join statement
                    string alias = Context.QuoteIdentifier(clause.JoiningTable.Alias);
                    query.Append($"{Context.QuoteIdentifier(clause.JoiningTable.Name)} AS {alias}");

                    // Do we have an expression?
                    if (clause.ExpressionType == JoinExpressionType.On)
                    {
                        // Try and grab the table
                        var    tbl   = Tables.Where(x => x.Name.Equals(clause.FromTable, StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                        string fromT = tbl?.Alias ?? clause.FromTable;
                        query.Append(" ON ");
                        query.Append(
                            SqlExpression <WhereStatement> .CreateExpressionString(
                                $"{alias}.{Context.QuoteIdentifier(clause.JoiningColumn)}",
                                clause.ComparisonOperator,
                                new SqlLiteral(Context.QuoteIdentifier($"{fromT}.{clause.FromColumn}"))
                                )
                            );
                    }
                    else if (clause.ExpressionType == JoinExpressionType.Using)
                    {
                        var parts = clause.JoiningColumn.Split(',');
                        query.AppendFormat(" USING({0})", String.Join(", ", parts.Select(x => Context.QuoteIdentifier(x))));
                    }
                }
            }

            // Append Where Statement
            List <SQLiteParameter> parameters = new List <SQLiteParameter>();

            if (WhereStatement.HasClause)
            {
                if (buildCommand)
                {
                    query.Append(" WHERE " + WhereStatement.BuildStatement(parameters));
                }
                else
                {
                    query.Append(" WHERE " + WhereStatement.BuildStatement());
                }
            }

            // Append GroupBy
            if (GroupByColumns.Count > 0)
            {
                query.Append(" GROUP BY " + String.Join(", ", GroupByColumns.Select(x => Context.QuoteIdentifier(x))));
            }

            // Append Having
            if (HavingStatement.HasClause)
            {
                if (GroupByColumns.Count == 0)
                {
                    throw new Exception("Having statement was set without Group By");
                }

                query.Append(" HAVING " + HavingStatement.BuildStatement(parameters));
            }

            // Append OrderBy
            if (OrderByStatements.Count > 0)
            {
                int count = OrderByStatements.Count;
                query.Append(" ORDER BY");
                foreach (OrderByClause clause in OrderByStatements)
                {
                    query.Append($" {Context.QuoteIdentifier(clause.ColumnName)}");

                    // Add sorting if not default
                    query.AppendIf(clause.SortOrder == Sorting.Descending, " DESC");

                    // Append seperator if we have more orderby statements
                    query.AppendIf(--count > 0, ",");
                }
            }

            // Append Limit
            query.AppendIf(Limit > 0, " LIMIT " + Limit);
            query.AppendIf(Offset > 0, " OFFSET " + Offset);

            // Create Command
            SQLiteCommand command = null;

            if (buildCommand)
            {
                command = Context.CreateCommand(query.ToString());
                command.Parameters.AddRange(parameters.ToArray());
            }

            // Return Result
            return((buildCommand) ? command as object : query.ToString());
        }
Ejemplo n.º 11
0
 /// <summary>
 /// Creates and adds a new Groupby clause to the current query object
 /// </summary>
 /// <param name="fieldName"></param>
 public SelectQueryBuilder GroupBy(string fieldName)
 {
     GroupByColumns.Add(fieldName);
     return(this);
 }
Ejemplo n.º 12
0
 public Query GroupBy(params Column[] columns)
 {
     GroupByColumns.AddRange(columns);
     return(this);
 }
Ejemplo n.º 13
0
        /// <inheritdoc />
        public override string Build()
        {
            var sb = new StringBuilder("SELECT ");

            // Output Distinct.
            if (IsDistinct)
            {
                sb.Append("DISTINCT ");
            }

            // Output column names.
            sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");

            // Output table names.
            if (SelectedTables.Any())
            {
                sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
            }

            // Output joins.
            if (JoinStatement.Any())
            {
                foreach (var clause in JoinStatement)
                {
                    sb.AppendLine();
                    switch (clause.JoinType)
                    {
                    case JoinType.InnerJoin:
                        sb.Append("INNER JOIN ");
                        break;

                    case JoinType.LeftJoin:
                        sb.Append("LEFT JOIN ");
                        break;

                    case JoinType.RightJoin:
                        sb.Append("RIGHT JOIN ");
                        break;

                    default:
                        throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
                    }
                    sb.Append($"`{clause.ToTable}` ON ");
                    sb.Append(CreateComparisonClause(
                                  $"{clause.ToTable}.{clause.ToColumn}",
                                  clause.ComparisonOperator,
                                  new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
                }
            }

            // Output where statement.
            if (WhereStatement.Any())
            {
                sb.AppendLine();
                sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
            }

            // Output GroupBy statement.
            if (GroupByColumns.Count > 0)
            {
                sb.AppendLine();
                sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
            }

            // Output OrderBy statement.
            if (OrderByStatement.Any())
            {
                sb.AppendLine();
                sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
            }

            if (TakeRows.HasValue)
            {
                sb.AppendLine();

                if (SkipRows.HasValue)
                {
                    sb.Append($"LIMIT {SkipRows}, {TakeRows}");
                }
                else
                {
                    sb.Append($"LIMIT {TakeRows}");
                }
            }

            // Return the built query.
            return(sb.ToString());
        }
Ejemplo n.º 14
0
        /// <summary>
        /// Adds a column to the GROUP BY clause.
        /// </summary>
        public SelectBuilder GroupBy(String column)
        {
            GroupByColumns.Add(column);

            return(this);
        }
Ejemplo n.º 15
0
        public override string ToPlainQuery()
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("SELECT");

            if (SelectDistinct)
            {
                sb.Append(" DISTINCT");
            }

            if (TopN > 0)
            {
                sb.AppendFormat(" TOP {0}", TopN);
            }

            if (SelectColumns.Count > 0)
            {
                for (int i = 0; i < SelectColumns.Count; i++)
                {
                    Column col = SelectColumns[i];

                    sb.Append(" " + col.AliasedName);

                    if (i < SelectColumns.Count - 1)
                    {
                        sb.Append(",");
                    }
                }
            }
            else
            {
                sb.Append(" *");
            }

            sb.AppendFormat(" FROM {0}{1}",
                            FromTableOrQuery(),
                            FromQuery == null ? "" : $" AS {FromQuery.Item2}");

            sb.Append($" {JoinString}");

            if (WhereCondition != null)
            {
                sb.AppendFormat(" WHERE {0}", WhereCondition.ToString());
            }

            if (GroupByColumns.Count > 0)
            {
                sb.AppendFormat(" GROUP BY {0}", String.Join(",", GroupByColumns.Select(r => " " + r.FullName)));
            }

            if (HavingCondition != null)
            {
                sb.AppendFormat(" HAVING {0}", HavingCondition.ToString());
            }

            if (OrderByColumns.Count > 0)
            {
                sb.AppendFormat(" ORDER BY {0}", String.Join(",", OrderByColumns.Select(r => String.Format(" {0} {1}", String.IsNullOrEmpty(r.Item1.Alias) ? r.Item1.FullName : r.Item1.Alias, r.Item2.GetStringValue()))));
            }

            sb.Append($" {SetOpString}");

            return(sb.ToString().Trim());
        }
        /// <inheritdoc />
        public override string Build()
        {
            var sb = new StringBuilder("SELECT ");

            // Output Distinct.
            if (IsDistinct)
            {
                sb.Append("DISTINCT ");
            }

            if (!SkipRows.HasValue && TakeRows.HasValue)
            {
                sb.Append($"TOP {TakeRows} ");
                if (TopIsPercent)
                {
                    sb.Append("PERCENT ");
                }
            }

            // Output column names.
            sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");

            // Output table names.
            if (SelectedTables.Any())
            {
                sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
            }

            // Output joins.
            if (JoinStatement.Any())
            {
                foreach (var clause in JoinStatement)
                {
                    sb.AppendLine();
                    switch (clause.JoinType)
                    {
                    case JoinType.InnerJoin:
                        sb.Append("INNER JOIN ");
                        break;

                    case JoinType.OuterJoin:
                        sb.Append("OUTER JOIN ");
                        break;

                    case JoinType.LeftJoin:
                        sb.Append("LEFT JOIN ");
                        break;

                    case JoinType.RightJoin:
                        sb.Append("RIGHT JOIN ");
                        break;
                    }
                    sb.Append($"[{clause.ToTable}] ON ");
                    sb.Append(CreateComparisonClause(
                                  $"{clause.ToTable}.{clause.ToColumn}",
                                  clause.ComparisonOperator,
                                  new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
                }
            }

            // Output where statement.
            if (WhereStatement.Any())
            {
                sb.AppendLine();
                sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
            }

            // Output GroupBy statement.
            if (GroupByColumns.Count > 0)
            {
                sb.AppendLine();
                sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
            }

            // TODO: Output having statement.

            /*
             * if (Having.ClauseLevels > 0)
             * {
             *  // Check if a Group By Clause was set
             *  if (groupByColumns.Count == 0)
             *  {
             *      throw new Exception("Having statement was set without Group By");
             *  }
             *  if (buildCommand)
             *  {
             *      sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
             *  }
             *  else
             *  {
             *      sb.Append(" HAVING " + Having.BuildWhereStatement());
             *  }
             * }
             */

            // Output OrderBy statement.
            if (OrderByStatement.Any())
            {
                sb.AppendLine();
                sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");

                // Works only in SQL Server 2012 and upper.
                // TODO use BETWEEN if it require
                if (SkipRows.HasValue)
                {
                    sb.AppendLine();
                    sb.Append($"OFFSET {SkipRows} ROWS");
                    if (TakeRows.HasValue)
                    {
                        sb.AppendLine();
                        sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
                    }
                }
            }

            // Return the built query.
            return(sb.ToString());
        }