public void SqlCaseReplacingTest()
{
SqlCase c1 = SqlDml.Case();
SqlCase c2 = SqlDml.Case(SqlDml.Literal(1));
c2.Else = SqlDml.Null;
c2[1] = 2;
c2[2] = 3;
c2[4] = 5;
c2[6] = 7;
c1.ReplaceWith(c2);
bool passed = false;
try {
c1.ReplaceWith(1);
}
catch {
passed = true;
}
Assert.IsTrue(passed);
Assert.AreNotSame(c1, c2);
//Possibly NUnit compares items of IEnumirable
//and if they the same it sugests two objects are equals
//Assert.AreNotEqual(c1, c2);
Assert.AreEqual(c1.NodeType, c2.NodeType);
Assert.AreEqual(c1.Value, c2.Value);
Assert.AreEqual(c1.Else, c2.Else);
Assert.AreEqual(c1.Count, c2.Count);
foreach (KeyValuePair <SqlExpression, SqlExpression> p in c1)
{
Assert.AreEqual(p.Value, c2[p.Key]);
}
}
protected override SqlExpression VisitCase(SqlCase expr)
{
_depth++;
_builder.Append("CASE");
_depth++;
if (expr.Expression != null)
{
_builder.Append(" ");
Visit(expr.Expression);
}
for (int i = 0; i < expr.Whens.Expressions.Count; ++i)
{
NewLine();
VisitWhen(expr.Whens.Expressions[i]);
}
if (expr.Else != null)
{
NewLine();
_builder.Append("ELSE ");
Visit(expr.Else);
}
_depth--;
NewLine();
_builder.Append(" END");
_depth--;
return(expr);
}
public void Test012()
{
string nativeSql = @"SELECT
CASE il.TrackId
WHEN 1 THEN 'STAFF_1'
WHEN 2 THEN 'STAFF_2'
ELSE 'STAFF_OTHER'
END AS shippers,
SUM(il.UnitPrice) AS TotalUnits
FROM [invoiceline] il
GROUP BY il.TrackId";
SqlTableRef invoiceLine = SqlDml.TableRef(schema.Tables["invoiceline"], "il");
SqlSelect select = SqlDml.Select(invoiceLine);
SqlCase totalPayment = SqlDml.Case(invoiceLine["TrackId"]);
totalPayment[1] = SqlDml.Literal("STAFF_1");
totalPayment[2] = SqlDml.Literal("STAFF_2");
totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
select.Columns.Add(totalPayment, "shippers");
select.Columns.Add(SqlDml.Sum(invoiceLine["UnitPrice"]), "TotalUnits");
select.GroupBy.AddRange(invoiceLine["TrackId"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test012()
{
string nativeSql = @"SELECT
CASE p.staff_id
WHEN 1 THEN 'STAFF_1'
WHEN 2 THEN 'STAFF_2'
ELSE 'STAFF_OTHER'
END AS Staff,
SUM(p.amount) AS Total
FROM
payment p
GROUP BY
p.staff_id";
SqlTableRef payment = SqlDml.TableRef(schema.Tables["payment"], "p");
SqlSelect select = SqlDml.Select(payment);
SqlCase totalPayment = SqlDml.Case(payment["staff_id"]);
totalPayment[1] = SqlDml.Literal("STAFF_1");
totalPayment[2] = SqlDml.Literal("STAFF_2");
totalPayment.Else = SqlDml.Literal("STAFF_OTHER");
select.Columns.Add(totalPayment, "Staff");
select.Columns.Add(SqlDml.Sum(payment["amount"]), "Total");
select.GroupBy.AddRange(payment["staff_id"]);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public virtual void Visit(SqlCase node)
{
VisitInternal(node.Else);
VisitInternal(node.Value);
foreach (KeyValuePair <SqlExpression, SqlExpression> pair in node)
{
VisitInternal(pair.Key);
VisitInternal(pair.Value);
}
}
public void Visit(SqlCase node)
{
if (!node.Value.IsNullReference())
{
Visit(node.Value);
}
if (!node.Else.IsNullReference())
{
Visit(node.Else);
}
}
public void ResultWithCaseHelper()
{
var qry = SqlBuilder
.Select <UserGroup>()
.Result(x => new
{
Name = x.Name.Case(z => SqlCase.Case <UserGroup>(y => y.Id > 3, y => 3).Else(x => x.CreatedBy).End())
});
Assert.AreEqual(124, qry.CommandText.Length);
Assert.AreEqual(2, qry.CommandParameters.Count);
}
public void SqlCaseCloneTest()
{
SqlCase c = SqlDml.Case();
c.Value = SqlDml.Literal(1);
c[SqlDml.Literal(1)] = SqlDml.Literal("A");
c[SqlDml.Literal(2)] = SqlDml.Literal("B");
c.Else = SqlDml.Literal("C");
SqlCase cClone = (SqlCase)c.Clone();
Assert.AreNotEqual(c, cClone);
Assert.AreNotEqual(c.Value, cClone.Value);
Assert.AreNotEqual(c.Else, cClone.Else);
Assert.AreEqual(c.NodeType, cClone.NodeType);
Assert.AreEqual(c.Value.NodeType, cClone.Value.NodeType);
Assert.AreEqual(c.Else.NodeType, cClone.Else.NodeType);
Assert.AreEqual(c.Count, cClone.Count);
}
public void CaseHelper()
{
var day = 4;
var qry = SqlBuilder
.Select <DailySchedule>(x => new DailySchedule
{
Id = x.Id.Case(z => SqlCase.Case <DailySchedule>(x => 1) //day
.When(x => 1, x => x.ID_DailyScheduleSun)
.When(x => 2, x => x.ID_DailyScheduleMon)
.When(x => 3, x => x.ID_DailyScheduleTue)
.When(x => 4, x => x.ID_DailyScheduleWed)
.When <User>((x, y) => y.Email, (x, y) => 1)
.When(x => 5, x => x.ID_DailyScheduleThu)
.When(x => 6, x => x.ID_DailyScheduleFri)
.When(x => 7, x => x.ID_DailyScheduleSat)
.End()),
Name = x.Name.Case(z => SqlCase.Case <UserGroup>(x => day > 0, x => 3).Else(x => x.CreatedBy).End()),
});
//this result is dynamic since case param is from guid randomizer
//Assert.AreEqual("SELECT CASE @Case713e01 WHEN @Case713e02 THEN [tDailySchedule].[ID_DailyScheduleSun] WHEN @Case713e03 THEN [tDailySchedule].[ID_DailyScheduleMon] WHEN @Case713e04 THEN [tDailySchedule].[ID_DailyScheduleTue] WHEN @Case713e05 THEN [tDailySchedule].[ID_DailyScheduleWed] WHEN [Users].[Email] THEN @Case713e06 WHEN @Case713e07 THEN [tDailySchedule].[ID_DailyScheduleThu] WHEN @Case713e08 THEN [tDailySchedule].[ID_DailyScheduleFri] WHEN @Case713e09 THEN [tDailySchedule].[ID_DailyScheduleSat] END [Id], CASE WHEN (@Case5e6141 > @Case5e6142) THEN @Case5e6143 ELSE [UsersGroup].[CreatedBy] END [Name] FROM [tDailySchedule]", qry.CommandText);
Assert.AreEqual(12, qry.CommandParameters.Count);
}
protected override SqlExpression VisitCase(SqlCase expr)
{
_depth++;
_builder.Append("CASE");
_depth++;
if (expr.Expression != null)
{
_builder.Append(" ");
Visit(expr.Expression);
}
for(int i = 0; i < expr.Whens.Expressions.Count; ++i)
{
NewLine();
VisitWhen(expr.Whens.Expressions[i]);
}
if (expr.Else != null)
{
NewLine();
_builder.Append("ELSE ");
Visit(expr.Else);
}
_depth--;
NewLine();
_builder.Append(" END");
_depth--;
return expr;
}
protected virtual SqlExpression VisitCase(SqlCase expr)
{
return(expr);
}
private void BuildSelectForUniqueMatchPartial(SqlMatch node, SqlRow row, SqlSelect finalQuery)
{
bool allNull = true;
var @case = SqlDml.Case();
SqlExpression when = true;
//if all row elements are null then true
if (row.Count > 0)
{
var whenNotNeeded = false;
for (var i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
allNull = false;
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
when = i == 0
? SqlDml.IsNull(row[i])
: when && SqlDml.IsNull(row[i]);
}
if (allNull)
{
when = true;
}
if (!whenNotNeeded)
{
_ = @case.Add(when, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
var subQuery = SqlDml.Select(originalQuery);
subQuery.Columns.Add(8);
var columns = originalQuery.Columns;
SqlExpression where = null;
for (var i = 0; i < columns.Count; i++)
{
//if row[i] would be NULL then c3 would result in true,
if (row[i].NodeType != SqlNodeType.Null)
{
SqlCase c3 = SqlDml.Case();
_ = c3.Add(SqlDml.IsNull(row[i]), true);
c3.Else = row[i] == columns[i];
where = where == null ? c3 : where && c3;
}
if (node.Unique)
{
var c4 = SqlDml.Case();
_ = c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
subQuery.GroupBy.Add(c4);
}
}
subQuery.Where = where;
if (node.Unique)
{
subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
@case.Else = SqlDml.Exists(subQuery);
}
if (@case.Else == null)
{
@case.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (@case.Count > 0)
{
finalQuery.Where = @case;
}
else
{
finalQuery.Where = @case.Else;
}
}
public override string Translate(SqlCompilerContext context, SqlMatch node, MatchSection section)
{
switch (section)
{
case MatchSection.Entry:
//MATCH is not supported by PostgreSQL, we need some workaround
SqlRow row = node.Value as SqlRow;
if (row != null)
{
SqlSelect finalQuery = SqlDml.Select();
finalQuery.Columns.Add(5);
switch (node.MatchType)
{
#region SIMPLE
case SqlMatchType.None: {
bool existsNull = false;
SqlCase c = SqlDml.Case();
{
bool subQueryNeeded = true;
//if any of the row elements is NULL then true
if (row.Count > 0)
{
bool allLiteral = true; //if true then there is no NULL element
SqlExpression when1 = null;
for (int i = 0; i < row.Count; i++)
{
bool elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
//if the row element is the NULL value
if (row[i].NodeType == SqlNodeType.Null)
{
existsNull = true;
break;
}
if (allLiteral && elementIsNotLiteral)
{
allLiteral = false;
}
if (elementIsNotLiteral)
{
if (when1 == null)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 || SqlDml.IsNull(row[i]);
}
}
}
if (existsNull)
{
//Some row element is the NULL value, MATCH result is true
subQueryNeeded = false;
}
else if (allLiteral)
{
//No row element is the NULL value
subQueryNeeded = true;
}
else //(!whenNotNeeded)
{
//Check if any row element is NULL
c.Add(when1 == null ? true : when1, true);
subQueryNeeded = true;
}
}
//find row in subquery
if (subQueryNeeded)
{
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
//c.Add(Sql.Exists(q1), true);
c.Else = SqlDml.Exists(q1);
}
}
if (c.Else == null)
{
c.Else = false;
}
if (existsNull)
{
finalQuery.Where = null;
}
else if (c.Count > 0)
{
finalQuery.Where = c;
}
else
{
finalQuery.Where = c.Else;
}
break;
}
#endregion
#region FULL
case SqlMatchType.Full: {
SqlCase c1 = SqlDml.Case();
{
bool noMoreWhenNeeded = false;
bool allNull = true;
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
if (!noMoreWhenNeeded)
{
bool whenNotNeeded = false;
bool allLiteral = true;
SqlExpression when2 = true;
//if no row elements are null then subcase
for (int i = 0; i < row.Count; i++)
{
if (row[i].NodeType == SqlNodeType.Null)
{
whenNotNeeded = true;
when2 = false;
break;
}
if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
{
allLiteral = false;
}
if (i == 0)
{
when2 = SqlDml.IsNotNull(row[i]);
}
else
{
when2 = when2 && SqlDml.IsNotNull(row[i]);
}
}
if (allLiteral)
{
when2 = true;
}
if (!whenNotNeeded)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(1);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
where = columns[i] == row[i];
}
else
{
where = where && columns[i] == row[i];
}
if (node.Unique)
{
q1.GroupBy.Add(columns[i]);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Add(when2, SqlDml.Exists(q1));
}
}
//else false
c1.Else = false;
}
if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = false;
}
break;
}
#endregion
#region PARTIAL
case SqlMatchType.Partial: {
bool allNull = true;
SqlCase c1 = SqlDml.Case();
{
SqlExpression when1 = true;
//if all row elements are null then true
if (row.Count > 0)
{
bool whenNotNeeded = false;
for (int i = 0; i < row.Count; i++)
{
//if any row element is surely not the NULL value
if (row[i].NodeType == SqlNodeType.Literal)
{
allNull = false;
whenNotNeeded = true;
break;
}
if (allNull && row[i].NodeType != SqlNodeType.Null)
{
allNull = false;
}
if (i == 0)
{
when1 = SqlDml.IsNull(row[i]);
}
else
{
when1 = when1 && SqlDml.IsNull(row[i]);
}
}
if (allNull)
{
when1 = true;
}
if (!whenNotNeeded)
{
c1.Add(when1, true);
}
}
//otherwise
if (!allNull)
{
//find row in subquery
SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
SqlSelect q1 = SqlDml.Select(originalQuery);
q1.Columns.Add(8);
{
SqlTableColumnCollection columns = originalQuery.Columns;
SqlExpression where = null;
for (int i = 0; i < columns.Count; i++)
{
//if row[i] would be NULL then c3 would result in true,
if (row[i].NodeType != SqlNodeType.Null)
{
SqlCase c3 = SqlDml.Case();
c3.Add(SqlDml.IsNull(row[i]), true);
c3.Else = row[i] == columns[i];
if (where == null)
{
where = c3;
}
else
{
where = where && c3;
}
}
if (node.Unique)
{
SqlCase c4 = SqlDml.Case();
c4.Add(SqlDml.IsNull(row[i]), 0);
c4.Else = columns[i];
q1.GroupBy.Add(c4);
}
}
q1.Where = where;
if (node.Unique)
{
q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
}
}
c1.Else = SqlDml.Exists(q1);
}
}
if (c1.Else == null)
{
c1.Else = false;
}
if (allNull)
{
finalQuery.Where = null;
}
else if (c1.Count > 0)
{
finalQuery.Where = c1;
}
else
{
finalQuery.Where = c1.Else;
}
}
break;
#endregion
}
SqlMatch newNode = SqlDml.Match(SqlDml.Row(), SqlDml.SubQuery(finalQuery).Query, node.Unique, node.MatchType);
node.ReplaceWith(newNode);
return("EXISTS(SELECT '");
}
else
{
throw new InvalidOperationException(Strings.ExSqlMatchValueMustBeAnSqlRowInstance);
}
case MatchSection.Specification:
return("' WHERE EXISTS");
case MatchSection.Exit:
return(")");
}
return(string.Empty);
}
protected virtual SqlExpression VisitCase(SqlCase expr)
{
return expr;
}
