/// <summary>已重写。获取本Builder所分析的SQL语句</summary>
/// <returns></returns>
public override String ToString()
{
var sb = new StringBuilder();
sb.Append("Select ");
sb.Append(ColumnOrDefault);
sb.Append(" From ");
sb.Append(Table);
if (!Where.IsNullOrEmpty())
{
sb.Append(" Where " + Where);
}
if (!GroupBy.IsNullOrEmpty())
{
sb.Append(" Group By " + GroupBy);
}
if (!Having.IsNullOrEmpty())
{
sb.Append(" Having " + Having);
}
if (!OrderBy.IsNullOrEmpty())
{
sb.Append(" Order By " + OrderBy);
}
if (!Limit.IsNullOrEmpty())
{
sb.Append(Limit.EnsureStart(" "));
}
return(sb.ToString());
}
public sealed override StringBuilder ToString(StringBuilder sb, Dictionary <IQueryElement, IQueryElement> dic)
{
if (dic.ContainsKey(this))
{
return(sb.Append("..."));
}
dic.Add(this, this);
sb
.Append("(")
.Append(SourceID)
.Append(") ");
Select.ToString(sb, dic);
From.ToString(sb, dic);
Where.ToString(sb, dic);
GroupBy.ToString(sb, dic);
Having.ToString(sb, dic);
OrderBy.ToString(sb, dic);
if (HasUnion)
{
foreach (var u in Unions)
{
u.ToString(sb, dic);
}
}
dic.Remove(this);
return(sb);
}
public Part AddHaving(string value, string name)
{
var part = new Part(value, name);
Having.Add(part);
return(part);
}
/// <summary>
/// Processes a Having
/// </summary>
/// <param name="having">Having</param>
/// <param name="context">SPARQL Evaluation Context</param>
public virtual BaseMultiset ProcessHaving(Having having, SparqlEvaluationContext context)
{
if (context == null)
{
context = this.GetContext();
}
return(having.Evaluate(context));
}
public string Build()
{
var sql = new StringBuilder();
AppendSelect(sql);
sql.NewLine();
AppendFrom(sql);
sql.NewLine();
AppendWhere(sql);
sql.NewLineIf(Where.Any());
AppendSqlPartList(sql, GroupBy, "GROUP BY");
sql.NewLineIf(GroupBy.Any());
AppendSqlPartList(sql, Having, "HAVING");
sql.NewLineIf(Having.Any());
var paging = Paging.Enabled;
var pagingWithCte = paging && Paging.UseCte;
if (pagingWithCte)
{
var sqlCte = new StringBuilder();
sqlCte.Append("WITH CTE_MAIN AS (");
sqlCte.NewLine();
sqlCte.Append(sql);
sqlCte.NewLine();
sqlCte.Append(string.Format("), CTE_COUNT AS (SELECT COUNT(*) AS [{0}] FROM CTE_MAIN)", Paging.CteTotalCountFieldName));
sqlCte.NewLine();
sqlCte.Append("SELECT * FROM CTE_MAIN, CTE_COUNT");
sqlCte.NewLine();
AppendSqlPartList(sqlCte, OrderBy, "ORDER BY", removeTable: true);
sqlCte.NewLine();
AppendPagination(sqlCte);
sql = sqlCte;
}
else if (paging)
{
AppendSqlPartList(sql, OrderBy, "ORDER BY");
sql.NewLine();
AppendPagination(sql);
sql.NewLine();
}
else
{
AppendSqlPartList(sql, OrderBy, "ORDER BY");
}
if (Separator)
{
sql.Append(";");
}
return(Prettify(sql.ToString()));
}
public override string ToString()
{
var fields = Fields.Length == 0
? string.Empty
: Fields.Select(f => f.ToString()).Aggregate((a, b) => $"{a.ToString()}, {b.ToString()}");
return
($"GROUP BY {fields}{Having?.ToString()}");
}
/// <summary>
/// Clear all select settings.
/// </summary>
public void Clear()
{
Parent.Clear();
Joins.List.Clear();
Columns.Clear();
Columns.Clear();
Where.Clear();
OrderBy.Clear();
GroupBy.Clear();
Having.Clear();
ExecutionTime = 0;
}
///// <summary>
///// adds primary key information to query definition, or marks columns as read only, of no PK is available
///// </summary>
///// <param name="handler">used for obtain table structures with PKs</param>
//public void CompleteUpdatingInfo(IDmlfHandler handler)
//{
// var pks = new Dictionary<DmlfSource, PrimaryKeyInfo>();
// var required_pks = new Dictionary<DmlfSource, PrimaryKeyInfo>();
// // list of columns
// var usedcols = new HashSetEx<DmlfColumnRef>();
// foreach (var col in Columns)
// {
// var di = col.DisplayInfo;
// if (di == null) continue;
// var tbl = col.Source;
// if (tbl == null) tbl = handler.BaseTable;
// if (tbl == null) continue;
// var cr = col.Expr as DmlfColumnRefExpression;
// if (cr == null)
// {
// di.IsReadOnly = true;
// continue;
// }
// if (!pks.ContainsKey(tbl))
// {
// pks[tbl] = null;
// if (handler != null)
// {
// var ts = handler.GetStructure(tbl.TableOrView);
// if (ts != null)
// {
// pks[tbl] = ts.PrimaryKey;
// }
// }
// }
// var pk = pks[tbl];
// if (pk == null)
// {
// // no primary key, is readonly
// di.IsReadOnly = true;
// continue;
// }
// var pkcols = new List<string>(pk.Columns.GetNames());
// if (pkcols.Contains(cr.Column.ColumnName))
// {
// di.IsPrimaryKey = true;
// }
// usedcols.Add(new DmlfColumnRef { Source = tbl, ColumnName = cr.Column.ColumnName });
// if (di.Style == ColumnDisplayInfo.UsageStyle.Value)
// {
// required_pks[tbl] = pk;
// }
// if (di.Style == ColumnDisplayInfo.UsageStyle.Lookup)
// {
// di.IsReadOnly = true;
// }
// }
// // add missing primary key columns as hidden columns
// foreach (var pkt in required_pks)
// {
// foreach (string col in pkt.Value.Columns.GetNames())
// {
// var key = new DmlfColumnRef { Source = pkt.Key, ColumnName = col };
// if (usedcols.Contains(key)) continue;
// usedcols.Add(key);
// var nc = new DmlfResultField
// {
// DisplayInfo = new ColumnDisplayInfo
// {
// IsPrimaryKey = true,
// Style = ColumnDisplayInfo.UsageStyle.Hidden,
// },
// Expr = new DmlfColumnRefExpression
// {
// Column = new DmlfColumnRef
// {
// Source = pkt.Key,
// ColumnName = col,
// }
// }
// };
// Columns.Add(nc);
// }
// }
//}
public override void GenSql(ISqlDumper dmp)
{
dmp.Put("^select ");
if (TopRecords != null)
{
dmp.Put("^top %s ", TopRecords);
}
if (Distinct)
{
dmp.Put(" ^distinct ");
}
if (SelectAll)
{
dmp.Put("* ");
}
else
{
Columns.GenSql(dmp);
}
if (SelectIntoTable != null)
{
dmp.Put("&n^into %f ", SelectIntoTable);
}
GenerateFrom(dmp);
if (Where != null)
{
Where.GenSql(dmp);
}
if (GroupBy != null && GroupBy.Count > 0)
{
dmp.Put("&n^group ^by ");
GroupBy.GenSql(dmp);
}
if (Having != null)
{
Having.GenSql(dmp);
}
if (OrderBy != null && OrderBy.Count > 0)
{
dmp.Put("&n^order ^by ");
OrderBy.GenSql(dmp);
}
if (LimitCount != null)
{
dmp.Put("&n^limit %s ^offset %s", LimitCount, Offset);
}
}
/// <summary>
/// 获取分组子句
/// </summary>
protected string GetGroupBy()
{
if (Group.IsEmpty())
{
return(string.Empty);
}
var result = new StringBuilder();
result.AppendFormat("Group By {0}", Group);
if (!Having.IsEmpty())
{
result.AppendFormat(" Having {0}", Having);
}
return(result.ToString());
}
protected virtual string BuildHavingClause(Having having, int count)
{
var template = count == 0 ? genericHavingClause :
having.UseOr ? genericHavingOr : genericHavingAnd;
var openBlock = having.OpenBlock ? "(" : string.Empty;
var closeBlock = having.CloseBlock ? ")" : string.Empty;
var o = QueryBuilder.OperatorFormats[having.Operator];
var agg = BuildAggregate(having.Aggregate, false);
var valOp = string.Format(o, having.GetUniqueParameter());
return(string.Format(template, openBlock, agg, valOp, closeBlock));
}
public override void Dump(string itemName, string indentation)
{
base.Dump(itemName, indentation);
var indentation2 = indentation + " ";
Select.DumpList("Select", indentation2);
if (Distinct)
{
Console.WriteLine(indentation2 + "Distinct");
}
From.DumpList("From", indentation2);
Where?.Dump("Where", indentation2);
GroupBy?.DumpList("Group by", indentation2);
Having?.Dump("Having", indentation2);
OrderBy?.DumpList("Order by", indentation2);
Limit?.Dump("Limit", indentation2);
}
public TableSelectExpression Prepare(IExpressionPreparer preparer)
{
var selectExp = new TableSelectExpression {
GroupMax = GroupMax,
Distinct = Distinct,
CompositeFunction = CompositeFunction,
IsCompositeAll = IsCompositeAll
};
foreach (var column in Columns)
{
selectExp.Columns.Add(column.Prepare(preparer));
}
if (From != null)
{
selectExp.From = From.Prepare(preparer);
}
if (Into != null)
{
selectExp.Into = Into.Prepare(preparer);
}
if (whereClause != null)
{
selectExp.Where = whereClause.Prepare(preparer);
}
foreach (var column in GroupBy)
{
selectExp.GroupBy.Add(column.Prepare(preparer));
}
if (Having != null)
{
selectExp.Having = Having.Prepare(preparer);
}
if (NextComposite != null)
{
selectExp.NextComposite = NextComposite.Prepare(preparer);
}
return(selectExp);
}
protected override void AppendTo(SqlStringBuilder builder)
{
builder.Append("SELECT ");
if (Distinct)
{
builder.Append("DISTINCT ");
}
for (int i = 0; i < Items.Count; i++)
{
Items[i].AppendTo(builder);
if (i < Items.Count - 1)
{
builder.Append(", ");
}
}
if (!From.IsEmpty)
{
builder.AppendLine();
builder.Indent();
From.AppendTo(builder);
if (Where != null)
{
builder.AppendLine();
builder.Append("WHERE ");
Where.AppendTo(builder);
}
else if (Having != null)
{
builder.AppendLine();
builder.Append("HAVING ");
Having.AppendTo(builder);
}
}
// TODO: continue
}
IQueryExpression ISqlExpressionWalkable.Walk(bool skipColumns, Func <IQueryExpression, IQueryExpression> func)
{
Insert?.Walk(skipColumns, func);
Update?.Walk(skipColumns, func);
Delete?.Walk(skipColumns, func);
Select.Walk(skipColumns, func);
From.Walk(skipColumns, func);
Where.Walk(skipColumns, func);
GroupBy.Walk(skipColumns, func);
Having.Walk(skipColumns, func);
OrderBy.Walk(skipColumns, func);
if (HasUnion)
{
foreach (var union in Unions)
{
union.SelectQuery.Walk(skipColumns, func);
}
}
return(func(this));
}
public virtual SelectQuery Having(Having having)
{
this.having = having;
return(this);
}
public Select.Select Select(List<Expression> selectedItems, GroupBy.GroupBy groupBy, Having.Having having, OrderBy.OrderBy orderBy)
{
return new Select.Select(selectedItems, From, this, groupBy, having, orderBy);
}
public Select(List<Expression> selectedItems, From.From from, Where.Where where, GroupBy.GroupBy groupBy, Having.Having having, OrderBy.OrderBy orderBy)
: this(selectedItems, from, where, groupBy, having)
{
OrderBy = orderBy;
}
public Select(List<Expression> selectedItems, From.From from, Where.Where where, GroupBy.GroupBy groupBy, Having.Having having)
: this(selectedItems, from, where, groupBy)
{
Having = having;
}
public override IExplore Expolore(DelegateExpessionExplorer del)
{
List <TableClause> Tables2 = new List <TableClause>();
Tables.ForEach(a =>
{
TableClause g2 = (TableClause)a.Expolore(del);
if (g2 != null)
{
Tables2.Add(g2);
}
});
Tables.Replace(Tables2);
List <ColumnClause> Columns2 = new List <ColumnClause>();
Columns.ForEach(a =>
{
ColumnClause c = (ColumnClause)a.Expolore(del);
if (c != null)
{
Columns2.Add(c);
}
}
);
Columns.Replace(Columns2);
if (WhereExpr != null)
{
WhereExpr = (Expression)WhereExpr.Expolore(del);
}
if (GroupBys != null)
{
List <GroupByClause> GroupBys2 = new List <GroupByClause>();
GroupBys.ForEach(a =>
{
GroupByClause g2 = (GroupByClause)a.Expolore(del);
if (g2 != null)
{
GroupBys2.Add(g2);
}
});
GroupBys.Replace(GroupBys2);
}
if (Having != null)
{
Having = (Expression)Having.Expolore(del);
}
if (OrderBys != null)
{
List <OrderByClause> OrderBys2 = new List <OrderByClause>();
OrderBys.ForEach(a =>
{
OrderByClause g2 = (OrderByClause)a.Expolore(del);
if (g2 != null)
{
OrderBys2.Add(g2);
}
});
OrderBys.Replace(OrderBys2);
}
if (ExtSelects != null && ExtSelects.Count > 0)
{
var ExtSelects2 = new TokenList <ExtSelectClause>(this);
foreach (var e in ExtSelects)
{
var t = (ExtSelectClause)e.Expolore(del);
if (t != null)
{
ExtSelects2.Add(t);
}
}
ExtSelects = ExtSelects2;
}
return(base.Expolore(del));
}
/// <summary>
/// Converts the Query into it's SPARQL Algebra representation (as represented in the Leviathan API)
/// </summary>
/// <returns></returns>
public ISparqlAlgebra ToAlgebra()
{
//Firstly Transform the Root Graph Pattern to SPARQL Algebra
ISparqlAlgebra pattern;
if (this._rootGraphPattern != null)
{
if (Options.AlgebraOptimisation)
{
//If using Algebra Optimisation may use a special algebra in some cases
switch (this.SpecialType)
{
case SparqlSpecialQueryType.DistinctGraphs:
pattern = new SelectDistinctGraphs(this.Variables.First(v => v.IsResultVariable).Name);
break;
case SparqlSpecialQueryType.AskAnyTriples:
pattern = new AskAnyTriples();
break;
case SparqlSpecialQueryType.NotApplicable:
default:
//If not just use the standard transform
pattern = this._rootGraphPattern.ToAlgebra();
break;
}
}
else
{
//If not using Algebra Optimisation just use the standard transform
pattern = this._rootGraphPattern.ToAlgebra();
}
}
else
{
pattern = new Bgp();
}
//If we have a BINDINGS clause then we'll add it into the algebra here
if (this._bindings != null)
{
pattern = new Bindings(this._bindings, pattern);
}
//Then we apply any optimisers followed by relevant solution modifiers
switch (this._type)
{
case SparqlQueryType.Ask:
//Apply Algebra Optimisation is enabled
if (Options.AlgebraOptimisation)
{
pattern = this.ApplyAlgebraOptimisations(pattern);
}
return(new Ask(pattern));
case SparqlQueryType.Construct:
case SparqlQueryType.Describe:
case SparqlQueryType.DescribeAll:
case SparqlQueryType.Select:
case SparqlQueryType.SelectAll:
case SparqlQueryType.SelectAllDistinct:
case SparqlQueryType.SelectAllReduced:
case SparqlQueryType.SelectDistinct:
case SparqlQueryType.SelectReduced:
//Apply Algebra Optimisation if enabled
if (Options.AlgebraOptimisation)
{
pattern = this.ApplyAlgebraOptimisations(pattern);
}
//GROUP BY is the first thing applied
if (this._groupBy != null)
{
pattern = new GroupBy(pattern, this._groupBy);
}
//After grouping we do projection
//This will generate the values for any Project Expressions and Aggregates
pattern = new Project(pattern, this.Variables);
//Add HAVING clause after the projection
if (this._having != null)
{
pattern = new Having(pattern, this._having);
}
//We can then Order our results
//We do ordering before we do Select but after Project so we can order by any of
//the project expressions/aggregates and any variable in the results even if
//it won't be output as a result variable
if (this._orderBy != null)
{
pattern = new OrderBy(pattern, this._orderBy);
}
//After Ordering we apply Select
//Select effectively trims the results so only result variables are left
//This doesn't apply to CONSTRUCT since any variable may be used in the Construct Template
//so we don't want to eliminate anything
if (this._type != SparqlQueryType.Construct)
{
pattern = new Select(pattern, this.Variables);
}
//If we have a Distinct/Reduced then we'll apply those after Selection
if (this._type == SparqlQueryType.SelectAllDistinct || this._type == SparqlQueryType.SelectDistinct)
{
pattern = new Distinct(pattern);
}
else if (this._type == SparqlQueryType.SelectAllReduced || this._type == SparqlQueryType.SelectReduced)
{
pattern = new Reduced(pattern);
}
//Finally we can apply any limit and/or offset
if (this._limit >= 0 || this._offset > 0)
{
pattern = new Slice(pattern, this._limit, this._offset);
}
return(pattern);
default:
throw new RdfQueryException("Unable to convert unknown Query Types to SPARQL Algebra");
}
}
private void PrepareInner()
{
List <ColumnClause> ColumnsLocal = GetAllColumns();
HashSet <string> uniqCols = new HashSet <string>();
int i2 = 0;
foreach (var cs in ColumnsLocal)
{
string s = cs.ExtractAlias();
if (!string.IsNullOrEmpty(s) && !uniqCols.Contains(s))
{
cs.InternalDbAlias = s;
}
if (string.IsNullOrEmpty(cs.InternalDbAlias))
{
while (true)
{
string nm = "col" + i2.ToString();
if (uniqCols.Contains(nm))
{
i2++;
continue;
}
cs.InternalDbAlias = nm;
uniqCols.Add(cs.InternalDbAlias);
break;
}
}
}
if (GroupBys != null && GroupBys.Count > 0)
{
//подготовка group by. В колонках пока не поддерживаются агрегатные функции
for (int i = 0; i < GroupBys.Count; i++)
{
GroupByClause gb = GroupBys[i];
if (gb.Expression != null)
{
gb.Expression.Prepare();
}
}
//сверяем выражения в groupby и колонках. Всё что в колонках должно быть groupby
/*
* for (int i = 0; i < Columns.Length; i++)
* {
* ColumnSelect cs = Columns[i];
* string s1 = cs.ColumnExpressionStr.ToLower().Trim();
* var arr = GroupBys.Where(a => a.ExpressionStr.ToLower().Trim() == s1).ToArray();
* if (arr.Length == 0) throw new Exception(string.Format("Column {0} not found in groupby expression"));
* }*/
}
if (WhereExpr != null)
{
WhereExpr.Prepare();
ExpUtils.CheckWhere(WhereExpr);
}
if (Having != null)
{
Having.Prepare();
}
if (OrderBys != null && OrderBys.Count > 0)
{
//подготовка group by. В колонках пока не поддерживаются агрегатные функции
for (int i = 0; i < OrderBys.Count; i++)
{
OrderByClause gb = OrderBys[i];
if (gb.Expression != null)
{
gb.Expression.Prepare();
}
}
}
}
private string DoToStr()
{
StringBuilder sql = new StringBuilder();
sql.Append("select ");
if (Distinct)
{
sql.Append("DISTINCT ");
}
for (int i = 0; i < Columns.Count; i++)
{
var cs = Columns[i];
if (i != 0)
{
sql.Append(", ");
}
sql.Append(cs.ToStr());
}
if (Tables.Count > 0)
{
sql.Append(" from " + Tables[0].ToStr());
//if (!string.IsNullOrEmpty(Tables[0].Alias)) sql.Append(" as \"").Append(Tables[0].Alias).Append("\"");
for (int i = 1; i < Tables.Count; i++)
{
var t = Tables[i];
if (t.Join == JoinType.Cross)
{
sql.Append(" cross join ");
}
if (t.Join == JoinType.Inner)
{
sql.Append(" inner join ");
}
if (t.Join == JoinType.Full)
{
sql.Append(" full join ");
}
if (t.Join == JoinType.Left)
{
sql.Append(" left join ");
}
if (t.Join == JoinType.Right)
{
sql.Append(" right join ");
}
sql.Append(" ").Append(t.ToStr());
//if (!string.IsNullOrEmpty(t.Alias)) sql.Append(" as \"").Append(t.Alias).Append("\"");
if (t.OnExpression != null)
{
sql.Append(" on (").Append(t.OnExpression.ToStr()).Append(")");
}
}
}
if (WhereExpr != null)
{
sql.Append(" where ").Append(WhereExpr.ToStr());
}
if (GroupBys != null && GroupBys.Count > 0)
{
sql.Append(" group by");
for (int i = 0; i < GroupBys.Count; i++)
{
var g = GroupBys[i];
if (i != 0)
{
sql.Append(", ");
}
sql.Append(g.ToStr());
}
}
if (Having != null)
{
sql.Append(" having ");
sql.Append(Having.ToStr());
}
if (OrderBys != null && OrderBys.Count > 0)
{
sql.Append(" order by ");
for (int i = 0; i < OrderBys.Count; i++)
{
var g = OrderBys[i];
if (i != 0)
{
sql.Append(", ");
}
sql.Append(g.ToStr());
}
}
if (LimitRecords >= 0)
{
sql.Append(" limit " + LimitRecords.ToString());
}
if (SkipRecords > 0)
{
sql.Append(" offset " + SkipRecords.ToString());
}
return(sql.ToString());
}
/// <summary>
/// Converts the Query into it's SPARQL Algebra representation (as represented in the Leviathan API)
/// </summary>
/// <returns></returns>
public ISparqlAlgebra ToAlgebra()
{
//Depending on how the query gets built we may not have had graph pattern optimization applied
//which we should do here if query optimization is enabled
if (!this.IsOptimised && Options.QueryOptimisation)
{
this.Optimise();
}
//Firstly Transform the Root Graph Pattern to SPARQL Algebra
ISparqlAlgebra algebra;
if (this._rootGraphPattern != null)
{
if (Options.AlgebraOptimisation)
{
//If using Algebra Optimisation may use a special algebra in some cases
switch (this.SpecialType)
{
case SparqlSpecialQueryType.DistinctGraphs:
algebra = new SelectDistinctGraphs(this.Variables.First(v => v.IsResultVariable).Name);
break;
case SparqlSpecialQueryType.AskAnyTriples:
algebra = new AskAnyTriples();
break;
case SparqlSpecialQueryType.NotApplicable:
default:
//If not just use the standard transform
algebra = this._rootGraphPattern.ToAlgebra();
break;
}
}
else
{
//If not using Algebra Optimisation just use the standard transform
algebra = this._rootGraphPattern.ToAlgebra();
}
}
else
{
//No root graph pattern means empty BGP
algebra = new Bgp();
}
//If we have a top level VALUES clause then we'll add it into the algebra here
if (this._bindings != null)
{
algebra = Join.CreateJoin(algebra, new Bindings(this._bindings));
}
//Then we apply any optimisers followed by relevant solution modifiers
switch (this._type)
{
case SparqlQueryType.Ask:
//Apply Algebra Optimisation is enabled
if (Options.AlgebraOptimisation)
{
algebra = this.ApplyAlgebraOptimisations(algebra);
}
return(new Ask(algebra));
case SparqlQueryType.Construct:
case SparqlQueryType.Describe:
case SparqlQueryType.DescribeAll:
case SparqlQueryType.Select:
case SparqlQueryType.SelectAll:
case SparqlQueryType.SelectAllDistinct:
case SparqlQueryType.SelectAllReduced:
case SparqlQueryType.SelectDistinct:
case SparqlQueryType.SelectReduced:
//Apply Algebra Optimisation if enabled
if (Options.AlgebraOptimisation)
{
algebra = this.ApplyAlgebraOptimisations(algebra);
}
//GROUP BY is the first thing applied
//This applies if there is a GROUP BY or if there are aggregates
//With no GROUP BY it produces a single group of all results
if (this._groupBy != null || this._vars.Any(v => v.IsAggregate))
{
algebra = new GroupBy(algebra, this._groupBy, this._vars.Where(v => v.IsAggregate));
}
//After grouping we do projection
//We introduce an Extend for each Project Expression
foreach (SparqlVariable var in this._vars)
{
if (var.IsProjection)
{
algebra = new Extend(algebra, var.Projection, var.Name);
}
}
//Add HAVING clause after the projection
if (this._having != null)
{
algebra = new Having(algebra, this._having);
}
//We can then Order our results
//We do ordering before we do Select but after Project so we can order by any of
//the project expressions/aggregates and any variable in the results even if
//it won't be output as a result variable
if (this._orderBy != null)
{
algebra = new OrderBy(algebra, this._orderBy);
}
//After Ordering we apply Select
//Select effectively trims the results so only result variables are left
//This doesn't apply to CONSTRUCT since any variable may be used in the Construct Template
//so we don't want to eliminate anything
if (this._type != SparqlQueryType.Construct)
{
algebra = new Select(algebra, this.Variables);
}
//If we have a Distinct/Reduced then we'll apply those after Selection
if (this._type == SparqlQueryType.SelectAllDistinct || this._type == SparqlQueryType.SelectDistinct)
{
algebra = new Distinct(algebra);
}
else if (this._type == SparqlQueryType.SelectAllReduced || this._type == SparqlQueryType.SelectReduced)
{
algebra = new Reduced(algebra);
}
//Finally we can apply any limit and/or offset
if (this._limit >= 0 || this._offset > 0)
{
algebra = new Slice(algebra, this._limit, this._offset);
}
return(algebra);
default:
throw new RdfQueryException("Unable to convert unknown Query Types to SPARQL Algebra");
}
}
public string ToString(bool addExecutionTime)
{
StringBuilder sb = new StringBuilder();
string post = null;
sb.Append(Columns.ToString(ref post));
string str, where = Where.ToString();
if (!string.IsNullOrEmpty(where))
{
sb.Append(" ");
sb.Append(where);
}
str = OrderBy.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
str = GroupBy.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
if (Settings.LimitType == LimitType.Fetch &&
(Index != 0 || Count != 0))
{
sb.Append(" OFFSET ");
sb.Append(Index);
sb.Append(" ROWS");
sb.Append(" FETCH NEXT ");
sb.Append(Count);
sb.Append(" ROWS ONLY");
}
str = Having.ToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(str);
}
if (Settings.Type == DatabaseType.Oracle)
{
str = Where.LimitToString();
if (!string.IsNullOrEmpty(str))
{
string tmp = sb.ToString();
str = string.Format(str, tmp);
sb.Length = 0;
sb.Append(str);
}
}
if (Settings.Type != DatabaseType.Oracle)
{
str = Where.LimitToString();
if (!string.IsNullOrEmpty(str))
{
sb.Append(" ");
sb.Append(str);
}
}
if (post != null)
{
sb.Append(post);
}
query = sb.ToString();
if (addExecutionTime && ExecutionTime != 0)
{
sb.Clear();
sb.Append("Execution time: ");
sb.Append(ExecutionTime);
sb.Append(" ms. ");
sb.Append(Environment.NewLine);
sb.Append(query);
}
return(query);
}
public IEntityReader Having(Having having)
{
_havings.Add(having);
return(this);
}
public override void WriteToStream(IndentStream stream)
{
stream.Write("SELECT");
if (TopCount != null)
{
stream.Write(" ");
TopCount.WriteToStream(stream);
}
stream.Write(" ");
foreach (var column in Columns.Select((value, idx) => new { value, idx }))
{
if (column.idx != 0)
{
stream.Write(", ");
}
column.value.WriteToStream(stream);
}
if (IntoTable != null)
{
stream.WriteLine();
stream.Write("INTO ");
IntoTable.WriteToStream(stream);
}
if (FromSourceList.Count > 0)
{
stream.WriteLine();
stream.Write("FROM ");
stream.Indent++;
for (int i = 0; i < FromSourceList.Count; i++)
{
if (i != 0)
{
stream.WriteLine(", ");
}
var fromSource = FromSourceList[i];
fromSource.WriteToStream(stream);
}
stream.Indent--;
}
if (ForXmlExpr != null)
{
stream.WriteLine();
ForXmlExpr.WriteToStream(stream);
}
if (PivotExpr != null)
{
stream.WriteLine();
PivotExpr.WriteToStream(stream);
}
if (WhereExpr != null)
{
stream.WriteLine();
stream.Write("WHERE ");
WhereExpr.WriteToStream(stream);
}
if (GroupByList.Count > 0)
{
stream.WriteLine();
stream.Write("GROUP BY ");
GroupByList.WriteToStreamWithComma(stream);
}
if (Having != null)
{
stream.WriteLine();
Having.WriteToStream(stream);
}
if (OrderByList.Count > 0)
{
stream.WriteLine();
stream.Write("ORDER BY ");
OrderByList.WriteToStreamWithComma(stream);
}
if (OptionExpr != null)
{
stream.WriteLine();
OptionExpr.WriteToStream(stream);
}
if (UnionSelectList != null && UnionSelectList.Count > 0)
{
stream.WriteLine();
UnionSelectList.WriteToStream(stream);
}
if (IsSemicolon)
{
stream.Write(" ;");
}
}
/// <summary>
/// This function permits to get a ValueObject list by criteria in using SQL Raw.
/// </summary>
/// <remarks>
/// After tests, we have opted for SQL calls directly in the database.
/// </remarks>
/// <param name="datasetId"></param>
/// <param name="select"></param>
/// <param name="where"></param>
/// <param name="sort"></param>
/// <param name="grouping"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public async Task <IEnumerable <ValueObject> > Filter(
string[] select,
string[] where = null,
string[] sort_asc = null,
string[] sort_desc = null,
bool grouping = false,
int?page = null,
int?pageSize = null)
{
// Initialize the entity
DbSet <ValueObject> valueObjectEntity = UnitOfWork.GetDbContext()
.ValueObject;
IEnumerable <ValueObject> valueObjects = valueObjectEntity
.Include(i => i.DataSet)
.AsQueryable();
SqlRawBuilder builder = new SqlRawBuilder();
Field fieldGroup = builder.Select.AddFieldGroup(select);
LeftJoin leftJoin = new LeftJoin();
Table table = new Table();
// Grouping or not
if (grouping)
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.Sum)
.AddField("CurrentValue", FieldFormatEnum.Sum)
.AddField("FutureValue", FieldFormatEnum.Sum)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Having clause = leftJoin.AddGroupBy()
.AddFieldGroup(select)
.AddHaving();
BuildClause(clause, where);
}
else
{
leftJoin = fieldGroup
.AddField("InitialValue", FieldFormatEnum.normal)
.AddField("CurrentValue", FieldFormatEnum.normal)
.AddField("FutureValue", FieldFormatEnum.normal)
.AddLeftJoin("ValueObject", "DataSetId", "DataSet", "Id");
Where clause = leftJoin
.AddWhere();
BuildClause(clause, where);
}
// Sorting
if (sort_desc != null && sort_desc.Length > 0)
{
leftJoin.AddOrder(sort_desc, SortingEnum.Desc);
}
else if (sort_asc != null && sort_asc.Length > 0)
{
leftJoin.AddOrder(sort_asc, SortingEnum.Asc);
}
// Execute the script
valueObjects = await UnitOfWork.ValueObjectRepository.ExecuteReader(builder.GetSQL);
// Paging the results
if (page != null && page.Value > 0 && pageSize != null && pageSize.Value > 0)
{
valueObjects = valueObjects.Skip(pageSize.Value * page.Value).Take(pageSize.Value);
}
return(await Task.FromResult(valueObjects));
}
/// <summary>
/// Processes a Having.
/// </summary>
/// <param name="having">Having.</param>
/// <param name="context">SPARQL Evaluation Context.</param>
public override BaseMultiset ProcessHaving(Having having, SparqlEvaluationContext context)
{
return(ExplainAndEvaluate <Having>(having, context, base.ProcessHaving));
}
public Select.Select Select(List<Expression> selectedItems, Where.Where where, GroupBy.GroupBy groupBy, Having.Having having)
{
return new Select.Select(selectedItems, this, where, groupBy, having);
}
