/// <summary>
/// Visits a ConditionalCase builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitConditionalCase(ConditionalCase item)
{
}
/// <summary>
/// Generates the text for a ConditionalCase builder.
/// </summary>
/// <param name="item">The ConditionCase builder to generate the text for.</param>
protected internal override void VisitConditionalCase(ConditionalCase item)
{
if (!item.Branches.Any())
{
throw new SQLGenerationException(Resources.EmptyCaseExpression);
}
writer.Write("CASE");
foreach (ConditionalCaseBranch branch in item.Branches)
{
writer.Write(" WHEN ");
branch.Condition.Accept(forSubCommand());
writer.Write(" THEN ");
branch.Value.Accept(forSubCommand());
}
if (item.Default != null)
{
writer.Write(" ELSE ");
item.Default.Accept(forSubCommand());
}
writer.Write(" END");
}
private void buildConditionList(MatchResult result, ConditionalCase options)
{
MatchResult condition = result.Matches[SqlGrammar.ConditionList.Condition];
buildCondition(condition, options);
MatchResult conditionListPrime = result.Matches[SqlGrammar.ConditionList.ConditionListPrime];
buildConditionListPrime(conditionListPrime, options);
}
private void buildConditionListPrime(MatchResult result, ConditionalCase options)
{
MatchResult conditionResult = result.Matches[SqlGrammar.ConditionListPrime.Condition.Name];
if (conditionResult.IsMatch)
{
MatchResult firstResult = conditionResult.Matches[SqlGrammar.ConditionListPrime.Condition.First];
buildCondition(firstResult, options);
MatchResult remainingResult = conditionResult.Matches[SqlGrammar.ConditionListPrime.Condition.Remaining];
buildConditionListPrime(remainingResult, options);
return;
}
MatchResult elseResult = result.Matches[SqlGrammar.ConditionListPrime.Else.Name];
if (elseResult.IsMatch)
{
MatchResult valueResult = elseResult.Matches[SqlGrammar.ConditionListPrime.Else.Value];
options.Default = (IProjectionItem)buildArithmeticItem(valueResult);
return;
}
MatchResult emptyResult = result.Matches[SqlGrammar.ConditionListPrime.Empty];
if (emptyResult.IsMatch)
{
return;
}
throw new InvalidOperationException();
}
private ConditionalCase buildConditionalCase(MatchResult result)
{
ConditionalCase options = new ConditionalCase();
MatchResult conditionListResult = result.Matches[SqlGrammar.ConditionalCase.ConditionList];
buildConditionList(conditionListResult, options);
return options;
}
private void buildCondition(MatchResult result, ConditionalCase options)
{
MatchResult expressionResult = result.Matches[SqlGrammar.Condition.Filter];
IFilter innerFilter = buildOrFilter(expressionResult);
MatchResult valueResult = result.Matches[SqlGrammar.Condition.Value];
IProjectionItem value = (IProjectionItem)buildArithmeticItem(valueResult);
FilterGroup filterGroup = new FilterGroup(Conjunction.And, innerFilter);
filterGroup.Optimize();
options.AddBranch(filterGroup, value);
}
public void TestSelect_ConditionalCase_MultipleCases()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
ConditionalCase options = new ConditionalCase();
options.AddBranch(new EqualToFilter(column, new NumericLiteral(0)), new StringLiteral("Sunday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(1)), new StringLiteral("Monday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(2)), new StringLiteral("Tuesday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(3)), new StringLiteral("Wednesday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(4)), new StringLiteral("Thursday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(5)), new StringLiteral("Friday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(6)), new StringLiteral("Saturday"));
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE WHEN Table.Column = 0 THEN 'Sunday' WHEN Table.Column = 1 THEN 'Monday' WHEN Table.Column = 2 THEN 'Tuesday' WHEN Table.Column = 3 THEN 'Wednesday' WHEN Table.Column = 4 THEN 'Thursday' WHEN Table.Column = 5 THEN 'Friday' WHEN Table.Column = 6 THEN 'Saturday' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
public void TestSelect_ConditionalCase_Else()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
ConditionalCase options = new ConditionalCase();
options.AddBranch(new EqualToFilter(column, new StringLiteral("Admin")), new StringLiteral("Administrator"));
options.Default = new StringLiteral("User");
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE WHEN Table.Column = 'Admin' THEN 'Administrator' ELSE 'User' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
/// <summary>
/// Visits a ConditionalCase builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitConditionalCase(ConditionalCase item)
{
}
