public override string ToHql(IAlias alias)
{
var processed = string.Concat(alias.Name, ".", PropertyName);
if (ProcessPropertyName != null)
{
processed = ProcessPropertyName(processed);
}
return string.Concat(processed, " ", Op, Value);
}
void IAliasConsumer.Add(IAlias alias)
{
if (alias == null)
{
return;
}
if (this.alias != null)
{
throw new Exception("Alias already exixts");
}
this.alias = alias;
Change(alias, null);
alias.OnChange += Change;
}
public void Substring()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Substring(person["Name"], 2, 4);
QueryResult result = engine.Compile(func);
Assert.Equal("SUBSTRING(\"person\".\"name\", @p0, @p1)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 2,
["@p1"] = 4
}, result.Parameters);
}
public void Add_Enumerable()
{
IAlias person = sql.Alias("person");
IOperator op = sql.BitAnd.Add(new List<object> { person["Id"], 1, 2 });
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Id\" & @p0 & @p1", result.Sql);
Assert.Equal(new Dictionary<string, object>
{
["@p0"] = 1,
["@p1"] = 2
}, result.Parameters);
}
public void Add_Params()
{
IAlias person = sql.Alias("person");
IOperator op = sql.Modulo.Add(person["Salary"], 100m, 200m);
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Salary\" % @p0 % @p1", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 100m,
["@p1"] = 200m
}, result.Parameters);
}
internal IAlias BindCriteriaByAlias(IAlias alias, string path, string aliasName)
{
// is this Join already existing (based on aliasName)
Join join = BindNamedAlias(aliasName);
if (join == null)
{
join = new Join(path, aliasName);
_joins.Add(new Tuple <IAlias, Join>(alias, join));
}
return(join);
}
/// <summary>
/// Adds a table to the FROM statement, with given short name.
/// </summary>
/// <param name="alias">Alias that contains table name and short name.</param>
/// <returns>The query itself.</returns>
/// <remarks>This overload requires that alias has a table name.</remarks>
public SqlQuery From(IAlias alias)
{
if (alias == null)
{
throw new ArgumentNullException("alias");
}
if (string.IsNullOrEmpty(alias.Table))
{
throw new ArgumentOutOfRangeException("alias.table");
}
return(From(alias.Table, alias));
}
public void Add_Enumerable()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte")
.Add(new List <IColumn> {
person["Id"], person["Name"]
})
.As(sql.Query.Select(person["Id"], person["Name"]).From(person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" (\"Id\", \"Name\") AS (SELECT \"person\".\"Id\", \"person\".\"Name\" "
+ "FROM \"person\")", result.Sql);
}
public void Case_Value_Then_Column()
{
IAlias person = sql.Alias("person");
ICase caseWhen = sql.Case(person["Name"])
.When("abcd", person["Name"]);
QueryResult result = engine.Compile(caseWhen);
Assert.Equal("CASE \"person\".\"Name\" WHEN @p0 THEN \"person\".\"Name\" END", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "abcd"
}, result.Parameters);
}
public void Add_Enumerable()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
IDelete delete = sql.Delete().Add(new List <IAlias>()
{
person, dept
});
QueryResult result = engine.Compile(delete);
Assert.Equal("DELETE \"person\", \"dept\"", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Case_Value_Null()
{
IAlias person = sql.Alias("person");
ICase caseWhen = sql.Case(null)
.When(person["SurName"], "abcd");
QueryResult result = engine.Compile(caseWhen);
Assert.Equal("CASE NULL WHEN \"person\".\"SurName\" THEN @p0 END", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "abcd"
}, result.Parameters);
}
public void When()
{
IAlias person = sql.Alias("person");
ICase caseWhen = sql.Case()
.When(person["Name"].IsNull(), "abcd");
QueryResult result = engine.Compile(caseWhen);
Assert.Equal("CASE WHEN \"person\".\"Name\" IS NULL THEN @p0 END", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "abcd"
}, result.Parameters);
}
public void Add_Params()
{
IAlias person = sql.Alias("person");
ISubList list = sql.SubList.Add(person["Id"], 1, "text");
QueryResult result = engine.Compile(list);
Assert.Equal("\"person\".\"Id\", @p0, @p1", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 1,
["@p1"] = "text"
}, result.Parameters);
}
public void Replace()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Replace(person["Name"], "a", "b");
QueryResult result = engine.Compile(func);
Assert.Equal("REPLACE(\"person\".\"name\", @p0, @p1)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "a",
["@p1"] = "b"
}, result.Parameters);
}
/// <summary>
/// Adds a subquery to the FROM statement, with given short name.
/// </summary>
/// <param name="subQuery">A subquery</param>
/// <param name="alias">Alias that contains the short name.</param>
/// <returns>The query itself.</returns>
/// <remarks>This overload requires that alias has a table name.</remarks>
public SqlQuery From(ISqlQuery subQuery, IAlias alias)
{
if (subQuery == null)
{
throw new ArgumentNullException("subQuery");
}
if (alias == null)
{
throw new ArgumentNullException("alias");
}
return(From(subQuery.ToString(), alias));
}
public void Where_Value()
{
IAlias person = sql.Alias("person");
IQuery query = sql.Query
.Where(person["Id"].Eq(1));
QueryResult result = engine.Compile(query);
Assert.Equal("WHERE \"person\".\"Id\" = @p0", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 1
}, result.Parameters);
}
internal IAlias BindCriteriaByAlias(IAlias alias, string path, string aliasName, string type = null, Action <IHqlExpressionFactory> withPredicate = null)
{
// is this Join already existing (based on aliasName)
Join join = BindNamedAlias(aliasName);
if (join == null)
{
join = new Join(path, aliasName, type, withPredicate);
_joins.Add(new Tuple <IAlias, Join>(alias, join));
}
return(join);
}
public void Add_Params(decimal value)
{
IAlias person = sql.Alias("person");
IOperator op = sql.Subtract.Add(person["Salary"], 100m, value);
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Salary\" - @p0 - @p1", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 100m,
["@p1"] = value
}, result.Parameters);
}
public void Add_Params(int value)
{
IAlias person = sql.Alias("person");
IOperator op = sql.BitXor.Add(person["Id"], 1, value);
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Id\" ^ @p0 ^ @p1", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 1,
["@p1"] = value
}, result.Parameters);
}
public SqlQuery LeftJoin(IAlias alias, ICriteria onCriteria)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(alias.Table))
throw new ArgumentNullException("alias.table");
var join = new LeftJoin(alias.Table, alias.Name, onCriteria);
Join(join);
return this;
}
public void Order_Not_Column()
{
IAlias person = sql.Alias("person");
IOrderBy orderBy = sql.OrderBy()
.Add(sql.Case
.When(person["Name"].IsNotNull(), person["Name"])
.Else(person["SurName"])).Desc;
QueryResult result = engine.Compile(orderBy);
Assert.Equal("ORDER BY CASE WHEN \"person\".\"Name\" IS NOT NULL THEN \"person\".\"Name\" "
+ "ELSE \"person\".\"SurName\" END DESC", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
/// <summary>
/// Adds a field of a given table alias to the SELECT statement.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="field">A field that only name will be used. It won't be set as a target.</param>
/// <returns>The query itself.</returns>
/// <remarks>No column name is set for the selected field.
/// Also field is not set as a target, unlike field only overload, only field name is used.</remarks>
public static SqlQuery Select(this SqlQuery query, IAlias alias, IField field)
{
if (alias == null)
{
throw new ArgumentNullException("alias");
}
if (field == null)
{
throw new ArgumentNullException("field");
}
return(query.Select(alias.NameDot + field));
}
/// <summary>
/// Adds a field of a given table alias to the ORDER BY clause.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="fieldName">A field name of the aliased table.</param>
/// <param name="desc">True to add " DESC" keyword to the expression.</param>
/// <returns>The query itself.</returns>
public SqlQuery OrderBy(IAlias alias, string fieldName, bool desc = false)
{
if (alias == null)
{
throw new ArgumentNullException("alias");
}
if (string.IsNullOrEmpty(fieldName))
{
throw new ArgumentNullException("field");
}
return(OrderBy(alias.NameDot + fieldName, desc));
}
/// <summary>
/// Adds a field of a given table alias to the GROUP BY clause.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="fieldName">A field name of the aliased table.</param>
/// <returns>The query itself.</returns>
public SqlQuery GroupBy(IAlias alias, string fieldName)
{
if (alias == null)
{
throw new ArgumentNullException("alias");
}
if (string.IsNullOrEmpty(fieldName))
{
throw new ArgumentNullException("field");
}
return(GroupBy(alias.NameDot + fieldName));
}
public void Add_Params(object value)
{
IAlias person = sql.Alias("person");
ISelect select = sql.Select().Add(person["Name"], ", ", person["SurName"], value);
QueryResult result = engine.Compile(select);
Assert.Equal("SELECT \"person\".\"Name\", @p0, \"person\".\"SurName\", @p1", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = ", ",
["@p1"] = value
}, result.Parameters);
}
public void Add_Enumerable()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("cte1").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("cte2").As(sql.Query.Select(dept.All).From(dept));
IWith with = sql.With.Add(new List <ICte> {
cte1, cte2
});
QueryResult result = engine.Compile(with);
Assert.Equal("WITH \"cte1\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"cte2\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public void Builder_Object_Enumerable()
{
IAlias person = sql.Alias("person");
IOperator op = sql.In(person["Id"], new int[] { 1, 2, 3 });
QueryResult result = engine.Compile(op);
Assert.Equal("\"person\".\"Id\" IN (@p0, @p1, @p2)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 1,
["@p1"] = 2,
["@p2"] = 3
}, result.Parameters);
}
public void Set_Enumerable()
{
IAlias person = sql.Alias("person");
IQuery query = sql.Query.Update()
.Set(person["Image"], new byte[] { 1, 2, 3 })
.From(person);
QueryResult result = engine.Compile(query);
Assert.Equal("UPDATE \"person\" SET \"Image\" = @p0", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = new byte[] { 1, 2, 3 }
}, result.Parameters);
}
public void Set_Column()
{
IAlias person = sql.Alias("person");
IQuery query = sql.Query.Update()
.Set(person["Name"], "abcd")
.From(person);
QueryResult result = engine.Compile(query);
Assert.Equal("UPDATE \"person\" SET \"Name\" = @p0", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "abcd"
}, result.Parameters);
}
public void With_Enumerable()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("personCte").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("deptCte").As(sql.Query.Select(dept.All).From(dept));
IQuery query = sql.Query.With(new List <IQueryFragment> {
cte1, cte2
});
QueryResult result = engine.Compile(query);
Assert.Equal("WITH \"personCte\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"deptCte\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public void Trim_Character_Both()
{
engine.AddFunction(FunctionName.Trim, FunctionHelper.TrimBoth);
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Trim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(@p0 FROM \"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = ","
}, result.Parameters);
}
public void With_Recursive()
{
engine.Options.WithRecursive = true;
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("cte1").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("cte2").As(sql.Query.Select(dept.All).From(dept));
IWith with = sql.With.Add(cte1).Add(cte2);
QueryResult result = engine.Compile(with);
Assert.Equal("WITH RECURSIVE \"cte1\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"cte2\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public SqlQuery LeftJoin(string toTable, IAlias alias, ICriteria onCriteria)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(toTable))
throw new ArgumentNullException("alias.table");
var join = new LeftJoin(toTable, alias.Name, onCriteria);
Join(join);
var haveJoins = alias as IHaveJoins;
if (haveJoins != null)
AliasWithJoins[alias.Name] = haveJoins;
return this;
}
/// <summary>
/// Adds a table to the FROM statement with an alias.
/// When it is called more than once, puts a comma between table names (cross join)
/// </summary>
/// <param name="table">Table</param>
/// <param name="alias">Alias for the table</param>
/// <returns>The query itself.</returns>
public SqlQuery From(string table, IAlias alias)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (aliasExpressions != null &&
aliasExpressions.ContainsKey(alias.Name))
throw new ArgumentOutOfRangeException("{0} alias is used more than once in the query!");
From(table);
from.Append(' ');
from.Append(alias.Name);
AliasExpressions.Add(alias.Name, table + " " + alias.Name);
var haveJoins = alias as IHaveJoins;
if (haveJoins != null)
AliasWithJoins[alias.Name] = haveJoins;
return this;
}
/// <exception cref="System.Exception"></exception>
private void AddAlias(string storedLetter, string runtimeLetter)
{
RemoveAlias();
alias = CreateAlias(storedLetter, runtimeLetter);
Fixture().ConfigureAtRuntime(new _IRuntimeConfigureAction_104(this));
Reopen();
}
/// <exception cref="System.Exception"></exception>
private void RemoveAlias()
{
if (alias != null)
{
Fixture().ConfigureAtRuntime(new _IRuntimeConfigureAction_114(this));
alias = null;
}
Reopen();
}
public abstract string ToHql(IAlias alias);
/// <summary>
/// Belirtilen numerik tablo alias'ı (başına T konarak) ve alanın adını aralarına
/// nokta koyarak içeren yeni bir kriter oluşturur.</summary>
/// <param name="alias">
/// Join aliası (T1 gibi kullanılır)</param>
/// <param name="field">
/// Alan nesnesi (zorunlu).</param>
public Criteria(IAlias alias, IField field)
: this(alias.Name, field.Name)
{
}
/// <summary>
/// Adds a table to the FROM statement, with given short name.
/// </summary>
/// <param name="alias">Alias that contains table name and short name.</param>
/// <returns>The query itself.</returns>
/// <remarks>This overload requires that alias has a table name.</remarks>
public SqlQuery From(IAlias alias)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(alias.Table))
throw new ArgumentOutOfRangeException("alias.table");
return From(alias.Table, alias);
}
/// <summary>
/// Adds a subquery to the FROM statement, with given short name.
/// </summary>
/// <param name="subQuery">A subquery</param>
/// <param name="alias">Alias that contains the short name.</param>
/// <returns>The query itself.</returns>
/// <remarks>This overload requires that alias has a table name.</remarks>
public SqlQuery From(ISqlQuery subQuery, IAlias alias)
{
if (subQuery == null)
throw new ArgumentNullException("subQuery");
if (alias == null)
throw new ArgumentNullException("alias");
return From(subQuery.ToString(), alias);
}
public virtual void RemoveAlias(IAlias alias)
{
_config.RemoveAlias(alias);
}
public virtual void AddAlias(IAlias alias)
{
_config.AddAlias(alias);
}
/// <summary>
/// Adds a field of a given table alias to the GROUP BY clause.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="fieldName">A field name of the aliased table.</param>
/// <returns>The query itself.</returns>
public SqlQuery GroupBy(IAlias alias, string fieldName)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(fieldName))
throw new ArgumentNullException("field");
return GroupBy(alias.NameDot + fieldName);
}
/// <summary>
/// Adds a field of a given table alias to the ORDER BY clause.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="fieldName">A field name of the aliased table.</param>
/// <param name="desc">True to add " DESC" keyword to the expression.</param>
/// <returns>The query itself.</returns>
public SqlQuery OrderBy(IAlias alias, string fieldName, bool desc = false)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(fieldName))
throw new ArgumentNullException("field");
return OrderBy(alias.NameDot + fieldName, desc);
}
public IAliasFactory Named(string alias)
{
Current = _query.BindNamedAlias(alias);
return this;
}
/// <summary>
/// Adds a field of a given table alias to the SELECT statement.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="field">A field that only name will be used. It won't be set as a target.</param>
/// <returns>The query itself.</returns>
/// <remarks>No column name is set for the selected field.
/// Also field is not set as a target, unlike field only overload, only field name is used.</remarks>
public static SqlQuery Select(this SqlQuery query, IAlias alias, IField field)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (field == null)
throw new ArgumentNullException("field");
return query.Select(alias.NameDot + field);
}
/// <summary>
/// Adds a table to the FROM statement with an alias.
/// When it is called more than once, puts a comma between table names (cross join)
/// </summary>
/// <param name="table">Table</param>
/// <param name="alias">Alias for the table</param>
/// <returns>The query itself.</returns>
public SqlQuery From(string table, IAlias alias)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (aliases != null &&
aliases.ContainsKey(alias.Name))
throw new ArgumentOutOfRangeException("{0} alias is used more than once in the query!");
From(table);
from.Append(' ');
from.Append(alias.Name);
((ISqlQueryExtensible)this).Aliases.Add(alias.Name, table + " " + alias.Name);
return this;
}
/// <summary>
/// Belirtilen numerik tablo alias'ı (başına T konarak) ve alanın adını aralarına
/// nokta koyarak içeren yeni bir kriter oluşturur.</summary>
/// <param name="alias">
/// Join aliası (T1 gibi kullanılır)</param>
/// <param name="field">
/// Alan nesnesi (zorunlu).</param>
public Criteria(IAlias alias, string field)
: this(alias.Name, field)
{
}
public void AddAlias(IAlias alias)
{
if (null == alias)
{
throw new ArgumentNullException("alias");
}
Aliases().Add(alias);
}
public void RemoveAlias(IAlias alias)
{
if (null == alias)
{
throw new ArgumentNullException("alias");
}
Aliases().Remove(alias);
}
/// <summary>
/// Adds a field of a given table alias to the SELECT statement with a column name.
/// </summary>
/// <param name="alias">A table alias that will be prepended to the field name with "." between</param>
/// <param name="fieldName">A field name of the aliased table.</param>
/// <param name="columnName">A column name</param>
/// <returns>The query itself.</returns>
public SqlQuery Select(IAlias alias, string fieldName, string columnName)
{
if (alias == null)
throw new ArgumentNullException("alias");
if (string.IsNullOrEmpty(fieldName))
throw new ArgumentNullException("fieldName");
if (string.IsNullOrEmpty(columnName))
throw new ArgumentNullException("columnName");
var expression = alias.NameDot + fieldName;
columns.Add(new Column(expression, columnName, intoIndex, null));
EnsureJoinsInExpression(expression);
return this;
}
