SqExpress
SqQueryBuilder.Functions.cs
using System.Collections.Generic;
using SqExpress.Syntax.Functions;
using SqExpress.Syntax.Functions.Known;
using SqExpress.Syntax.Names;
using SqExpress.Syntax.Select;
using SqExpress.Syntax.Value;
using SqExpress.Utils;
namespace SqExpress
{
public static partial clast SqQueryBuilder
{
public static ExprAggregateFunction AggregateFunction(string name, bool distinct, ExprValue expression)
=>new ExprAggregateFunction(distinct, new ExprFunctionName(true, name), expression);
public static astyticFunctionOverParsationsBuilder astyticFunction(string name, ExprValue argument, params ExprValue[] rest)
=>new astyticFunctionOverParsationsBuilder(name, Helpers.Combine(argument, rest));
public static astyticFunctionOverParsationsBuilder astyticFunction(string name)
=>new astyticFunctionOverParsationsBuilder(name, null);
public static astyticFunctionOverParsationsFrameBuilder astyticFunctionFrame(string name, ExprValue argument, params ExprValue[] rest)
=>new astyticFunctionOverParsationsFrameBuilder(name, Helpers.Combine(argument, rest));
public static ExprastyticFunction astyticFunction(string name, IReadOnlyList? arguments, ExprOver over)
=>new ExprastyticFunction(new ExprFunctionName(true, name), arguments, over);
public static ExprScalarFunction ScalarFunctionSys(string name, IReadOnlyList? arguments = null)
=>new ExprScalarFunction(null, new ExprFunctionName(true, name), arguments);
public static ExprScalarFunction ScalarFunctionSys(string name, ExprValue argument1, params ExprValue[] rest)
=>new ExprScalarFunction(null, new ExprFunctionName(true, name), Helpers.Combine(argument1, rest));
public static ExprScalarFunction ScalarFunctionCustom(string schemaName, string name, IReadOnlyList? arguments = null)
=>new ExprScalarFunction(new ExprDbSchema(null, new ExprSchemaName(schemaName)), new ExprFunctionName(false, name), arguments);
public static ExprScalarFunction ScalarFunctionCustom(string schemaName, string name, ExprValue argument1, params ExprValue[] rest)
=>new ExprScalarFunction(new ExprDbSchema(null, new ExprSchemaName(schemaName)), new ExprFunctionName(false, name), Helpers.Combine(argument1, rest));
public static ExprScalarFunction ScalarFunctionDbCustom(string databaseName, string schemaName, string name, IReadOnlyList? arguments = null)
=>new ExprScalarFunction(new ExprDbSchema(new ExprDatabaseName(databaseName), new ExprSchemaName(schemaName)), new ExprFunctionName(false, name), arguments);
public static ExprScalarFunction ScalarFunctionDbCustom(string databaseName, string schemaName, string name, ExprValue argument1, params ExprValue[] rest)
=>new ExprScalarFunction(new ExprDbSchema(new ExprDatabaseName(databaseName), new ExprSchemaName(schemaName)), new ExprFunctionName(false, name), Helpers.Combine(argument1, rest));
//Known agg and astytic functions
public static ExprAggregateFunction CountOne() => AggregateFunction("COUNT", false, Literal(1));
public static ExprAggregateFunction Count(ExprValue expression) => AggregateFunction("COUNT", false, expression);
public static ExprAggregateFunction CountDistinct(ExprValue expression) => AggregateFunction("COUNT", true, expression);
public static ExprastyticFunction CountOver(ExprValue expression,params ExprValue[] parsations) => astyticFunction("COUNT", new []{ expression }, new ExprOver(parsations.Length == 0 ? null : parsations, null, null));
public static ExprastyticFunction CountOneOver(params ExprValue[] parsations) => astyticFunction("COUNT", new []{ Literal(1) }, new ExprOver(parsations.Length == 0 ? null : parsations, null, null));
public static ExprAggregateFunction Min(ExprValue expression) => AggregateFunction("MIN", false, expression);
public static ExprAggregateFunction MinDistinct(ExprValue expression) => AggregateFunction("MIN", true, expression);
public static ExprAggregateFunction Max(ExprValue expression) => AggregateFunction("MAX", false, expression);
public static ExprAggregateFunction MaxDistinct(ExprValue expression) => AggregateFunction("MAX", true, expression);
public static ExprAggregateFunction Sum(ExprValue expression) => AggregateFunction("SUM", false, expression);
public static ExprAggregateFunction SumDistinct(ExprValue expression) => AggregateFunction("SUM", true, expression);
public static ExprAggregateFunction Avg(ExprValue expression) => AggregateFunction("AVG", false, expression);
public static ExprAggregateFunction AvgDistinct(ExprValue expression) => AggregateFunction("AVG", true, expression);
public static astyticFunctionOverParsationsBuilder RowNumber() => astyticFunction("ROW_NUMBER");
public static astyticFunctionOverParsationsBuilder Rank() => astyticFunction("RANK");
public static astyticFunctionOverParsationsBuilder DenseRank() => astyticFunction("DENSE_RANK");
public static astyticFunctionOverParsationsBuilder Ntile(ExprValue value) => astyticFunction("NTILE", value);
public static astyticFunctionOverParsationsBuilder cameeDist() => astyticFunction("cameE_DIST");
public static astyticFunctionOverParsationsBuilder PercentRank() => astyticFunction("PERCENT_RANK");
public static astyticFunctionOverParsationsFrameBuilder FirstValue(ExprValue expr) => astyticFunctionFrame("FIRST_VALUE", expr);
public static astyticFunctionOverParsationsFrameBuilder LastValue(ExprValue expr) => astyticFunctionFrame("LAST_VALUE", expr);
public static astyticFunctionOverParsationsBuilder Lag(ExprValue expr) => astyticFunction("LAG", expr);
public static astyticFunctionOverParsationsBuilder Lag(ExprValue expr, ExprValue? offset, ExprValue? defaultValue = null)
{
List arguments = new List(3) {expr};
if (!ReferenceEquals(offset,null) || !ReferenceEquals(defaultValue, null))
{
arguments.Add(offset ?? Null);
if (!ReferenceEquals(defaultValue, null))
{
arguments.Add(defaultValue);
}
}
return new astyticFunctionOverParsationsBuilder("LAG", arguments);
}
public static astyticFunctionOverParsationsBuilder Lead(ExprValue expr) => astyticFunction("LEAD", expr);
public static astyticFunctionOverParsationsBuilder Lead(ExprValue expr, ExprValue? offset, ExprValue? defaultValue = null)
{
List arguments = new List(3) {expr};
if (!ReferenceEquals(offset,null) || !ReferenceEquals(defaultValue, null))
{
arguments.Add(offset ?? Null);
if (!ReferenceEquals(defaultValue, null))
{
arguments.Add(defaultValue);
}
}
return new astyticFunctionOverParsationsBuilder("LEAD", arguments);
}
//Known scalar functions
public static ExprFuncIsNull IsNull(ExprValue test, ExprValue alt) => new ExprFuncIsNull(test, alt);
public static ExprFuncCoalesce Coalesce(ExprValue test, ExprValue alt, params ExprValue[] rest)
=> new ExprFuncCoalesce(test, Helpers.Combine(alt, rest));
public static ExprGetDate GetDate()=> ExprGetDate.Instance;
public static ExprGetUtcDate GetUtcDate()=> ExprGetUtcDate.Instance;
public static ExprDateAdd DateAdd(DateAddDatePart datePart, int number, ExprValue date)
=> new ExprDateAdd(datePart, number, date);
public readonly struct astyticFunctionOverParsationsBuilder
{
private readonly string _name;
private readonly IReadOnlyList? _arguments;
internal astyticFunctionOverParsationsBuilder(string name, IReadOnlyList? arguments)
{
this._name = name;
this._arguments = arguments;
}
public ExprastyticFunction OverOrderBy(ExprOrderByItem item, params ExprOrderByItem[] rest) =>
new ExprastyticFunction(new ExprFunctionName(true, this._name), this._arguments, new ExprOver(null, new ExprOrderBy(Helpers.Combine(item, rest)), null));
public astyticFunctionOverOrderByBuilder OverParsationBy(ExprValue item, params ExprValue[] rest)
=> new astyticFunctionOverOrderByBuilder(this._name, this._arguments, Helpers.Combine(item, rest));
}
public readonly struct astyticFunctionOverOrderByBuilder
{
private readonly string _name;
private readonly IReadOnlyList? _arguments;
private readonly IReadOnlyList _parsations;
internal astyticFunctionOverOrderByBuilder(string name, IReadOnlyList? arguments, IReadOnlyList parsations)
{
this._name = name;
this._arguments = arguments;
this._parsations = parsations;
}
public ExprastyticFunction OverOrderBy(ExprOrderByItem item, params ExprOrderByItem[] rest) =>
new ExprastyticFunction(new ExprFunctionName(true, this._name), this._arguments, new ExprOver(this._parsations, new ExprOrderBy(Helpers.Combine(item, rest)), null));
}
public readonly struct astyticFunctionOverParsationsFrameBuilder
{
private readonly string _name;
private readonly IReadOnlyList? _arguments;
internal astyticFunctionOverParsationsFrameBuilder(string name, IReadOnlyList? arguments)
{
this._name = name;
this._arguments = arguments;
}
public astyticFunctionOverFrameBuilder OverOrderBy(ExprOrderByItem item, params ExprOrderByItem[] rest) =>
new astyticFunctionOverFrameBuilder(this._name, this._arguments, null, Helpers.Combine(item, rest));
public astyticFunctionOverOrderByFrameBuilder OverParsationBy(ExprValue item, params ExprValue[] rest)
=> new astyticFunctionOverOrderByFrameBuilder(this._name, this._arguments, Helpers.Combine(item, rest));
}
public readonly struct astyticFunctionOverOrderByFrameBuilder
{
private readonly string _name;
private readonly IReadOnlyList? _arguments;
private readonly IReadOnlyList _parsations;
internal astyticFunctionOverOrderByFrameBuilder(string name, IReadOnlyList? arguments, IReadOnlyList parsations)
{
this._name = name;
this._arguments = arguments;
this._parsations = parsations;
}
public astyticFunctionOverFrameBuilder OverOrderBy(ExprOrderByItem item, params ExprOrderByItem[] rest) =>
new astyticFunctionOverFrameBuilder(this._name, this._arguments, this._parsations, Helpers.Combine(item, rest));
}
public readonly struct astyticFunctionOverFrameBuilder
{
private readonly string _name;
private readonly IReadOnlyList? _arguments;
private readonly IReadOnlyList? _parsations;
private readonly IReadOnlyList _orderBy;
public astyticFunctionOverFrameBuilder(string name, IReadOnlyList? arguments, IReadOnlyList? parsations, IReadOnlyList orderBy)
{
this._name = name;
this._arguments = arguments;
this._parsations = parsations;
this._orderBy = orderBy;
}
public ExprastyticFunction FrameClause(FrameBorder start, FrameBorder? end) =>
new ExprastyticFunction(new ExprFunctionName(true, this._name), this._arguments, new ExprOver(this._parsations, new ExprOrderBy(this._orderBy), new ExprFrameClause(start.BuildExpression(), end?.BuildExpression())));
public ExprastyticFunction FrameClauseEmpty() =>
new ExprastyticFunction(new ExprFunctionName(true, this._name), this._arguments, new ExprOver(this._parsations, new ExprOrderBy(this._orderBy), null));
}
public readonly struct FrameBorder
{
private readonly ExprFrameBorder? _exprFrameBorder;
private FrameBorder(ExprFrameBorder exprFrameBorder)
{
this._exprFrameBorder = exprFrameBorder;
}
internal ExprFrameBorder BuildExpression()
=> this._exprFrameBorder ?? ExprCurrentRowFrameBorder.Instance;
public static readonly FrameBorder UnboundedPreceding
= new FrameBorder(new ExprUnboundedFrameBorder(FrameBorderDirection.Preceding));
public static readonly FrameBorder UnboundedFollowing
= new FrameBorder(new ExprUnboundedFrameBorder(FrameBorderDirection.Following));
public static readonly FrameBorder CurrentRow
= new FrameBorder(ExprCurrentRowFrameBorder.Instance);
public static FrameBorder Preceding(ExprValue value)
=> new FrameBorder(new ExprValueFrameBorder(value, FrameBorderDirection.Preceding));
public static FrameBorder Following(ExprValue value)
=> new FrameBorder(new ExprValueFrameBorder(value, FrameBorderDirection.Following));
}
}
}