private static void BuildSingleValue(
StringBuilder outputBuilder, ConnectorBase conn,
string firstTableName, object value, ValueObjectType valueType,
string otherTableName, object otherValue, ValueObjectType otherType,
Query relatedQuery,
TableSchema rightTableSchema, string rightTableName)
{
if (valueType == ValueObjectType.Value)
{
if (value is Query)
{
outputBuilder.Append('(');
outputBuilder.Append(((Query)value).BuildCommand(conn));
outputBuilder.Append(')');
}
else if (value is WhereList)
{
outputBuilder.Append('(');
((WhereList)value).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName);
outputBuilder.Append(')');
}
else if (value is ICollection)
{
ICollection collIn = value as ICollection;
StringBuilder sbIn = new StringBuilder();
sbIn.Append('(');
bool first = true;
TableSchema schema = null;
if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (relatedQuery != null)
{
if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
}
foreach (object objIn in collIn)
{
if (first) first = false;
else sbIn.Append(',');
if (schema != null)
{
sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), objIn, conn, relatedQuery));
}
else
{
sbIn.Append(conn.PrepareValue(objIn, relatedQuery));
}
}
if (first)
{
sbIn.Append("NULL"); // Avoid exceptions, create a NULL list, where the condition will always return FALSE
}
sbIn.Append(')');
outputBuilder.Append(sbIn.ToString());
}
else if (otherType == ValueObjectType.ColumnName)
{
TableSchema schema = null;
if (object.ReferenceEquals(otherTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (relatedQuery != null)
{
if (otherTableName == null || !relatedQuery.TableAliasMap.TryGetValue(otherTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
}
if (schema != null)
{
// Try to match value type to the other value type
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)otherValue), value, conn, relatedQuery));
}
else
{
// Format it according to generic rules
outputBuilder.Append(conn.PrepareValue(value, relatedQuery));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(value, relatedQuery));
}
}
else if (valueType == ValueObjectType.ColumnName)
{
if (firstTableName != null)
{
if (object.ReferenceEquals(firstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.WrapFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.WrapFieldName(firstTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.WrapFieldName((string)value));
}
else
{
outputBuilder.Append(value == null ? @"NULL" : value);
}
}
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery)
{
BuildCommand(outputBuilder, isFirst, conn, relatedQuery, null, null);
}
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery, TableSchema rightTableSchema, string rightTableName)
{
if (!isFirst)
{
switch (Condition)
{
case WhereCondition.AND:
outputBuilder.Append(@" AND ");
break;
case WhereCondition.OR:
outputBuilder.Append(@" OR ");
break;
}
}
// The list is empty?
if (Comparison == WhereComparison.None &&
(First is WhereList && ((WhereList)First).Count == 0)
)
{
outputBuilder.Append(@"1"); // dump a dummy TRUE condition to fill the blank
return;
}
if (First is WhereList)
{
outputBuilder.Append('(');
((WhereList)First).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName);
outputBuilder.Append(')');
}
else
{
BuildSingleValue(
outputBuilder, conn,
FirstTableName, First, FirstType,
SecondTableName, Second, SecondType,
relatedQuery, rightTableSchema, rightTableName);
if (Comparison != WhereComparison.None)
{
switch (Comparison)
{
case WhereComparison.EqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS ");
else outputBuilder.Append(@" = ");
break;
case WhereComparison.NotEqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS NOT ");
else outputBuilder.Append(@" <> ");
break;
case WhereComparison.GreaterThan:
outputBuilder.Append(@" > ");
break;
case WhereComparison.GreaterThanOrEqual:
outputBuilder.Append(@" >= ");
break;
case WhereComparison.LessThan:
outputBuilder.Append(@" < ");
break;
case WhereComparison.LessThanOrEqual:
outputBuilder.Append(@" <= ");
break;
case WhereComparison.Is:
outputBuilder.Append(@" IS ");
break;
case WhereComparison.IsNot:
outputBuilder.Append(@" IS NOT ");
break;
case WhereComparison.Like:
outputBuilder.Append(@" LIKE ");
break;
case WhereComparison.Between:
outputBuilder.Append(@" BETWEEN ");
break;
case WhereComparison.In:
outputBuilder.Append(@" IN ");
break;
case WhereComparison.NotIn:
outputBuilder.Append(@" NOT IN ");
break;
}
BuildSingleValue(
outputBuilder, conn,
SecondTableName, Second, SecondType,
FirstTableName, First, FirstType,
relatedQuery, rightTableSchema, rightTableName);
if (Comparison == WhereComparison.Between)
{
outputBuilder.Append(@" AND ");
BuildSingleValue(
outputBuilder, conn,
ThirdTableName, Third, ThirdType,
FirstTableName, First, FirstType,
relatedQuery, rightTableSchema, rightTableName);
}
else if (Comparison == WhereComparison.Like)
{
outputBuilder.Append(' ');
outputBuilder.Append(conn.LikeEscapingStatement);
outputBuilder.Append(' ');
}
}
}
}
public Where(WhereCondition condition, Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value)
{
Condition = condition;
First = query;
FirstType = ValueObjectType.Value;
Comparison = comparison;
Second = value;
SecondType = valueType;
}
public Where(WhereCondition condition, Query query, WhereComparison comparison, string tableName, string columnName)
{
Condition = condition;
First = query;
FirstType = ValueObjectType.Value;
Comparison = comparison;
SecondTableName = tableName;
Second = columnName;
SecondType = ValueObjectType.ColumnName;
}
public Query Having(WhereCondition condition, Query query, WhereComparison comparison, string tableName, string columnName)
{
return Having(new Where(condition, query, comparison, tableName, columnName));
}
public Query Having(WhereCondition condition, Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value)
{
return Having(new Where(condition, query, comparison, value, valueType));
}
public Query AddSelect(Query query, string alias = null)
{
return Select(query, alias);
}
public Query Having(Query query, WhereComparison comparison, string tableName, string columnName)
{
return Having(WhereCondition.AND, query, comparison, tableName, columnName);
}
public Query Select(Query query, string alias = null)
{
this.QueryMode = QueryMode.Select;
if (_ListSelect == null) _ListSelect = new SelectColumnList();
_ListSelect.Add(new SelectColumn(query, alias));
return this;
}
public string Build(ConnectorBase conn, Query relatedQuery = null)
{
string ret = "";
if (Type == ValueObjectType.ColumnName)
{
if (TableName != null && TableName.Length > 0)
{
ret += conn.WrapFieldName(TableName);
ret += ".";
}
ret += conn.WrapFieldName(Value.ToString());
}
else if (Type == ValueObjectType.Value)
{
ret += @"(" + conn.PrepareValue(Value, relatedQuery) + @")";
}
else
{
ret += Value;
}
return ret;
}
public void BuildCommand(StringBuilder outputBuilder, bool isFirst, ConnectorBase conn, Query relatedQuery, TableSchema rightTableSchema, string rightTableName)
{
if (!isFirst)
{
switch (Condition)
{
case WhereCondition.AND:
outputBuilder.Append(@" AND ");
break;
case WhereCondition.OR:
outputBuilder.Append(@" OR ");
break;
}
}
if (Comparision == WhereComparision.None && // Its not a comparison
// And there's no list or the list is empty
(!(First is WhereList) || ((WhereList)First).Count == 0) &&
// And it's not a literal expression
FirstType != ValueObjectType.Literal &&
FirstType != ValueObjectType.Value
)
{
outputBuilder.Append(@"1"); // dump a dummy TRUE condition to fill the blank
return;
}
if (First is WhereList)
{
outputBuilder.Append('(');
((WhereList)First).BuildCommand(outputBuilder, conn, relatedQuery, rightTableSchema, rightTableName);
outputBuilder.Append(')');
}
else
{
if (FirstType == ValueObjectType.Value)
{
if (SecondType == ValueObjectType.ColumnName)
{
if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)Second), First, conn));
}
else
{
TableSchema schema;
if (SecondTableName == null || !relatedQuery.TableAliasMap.TryGetValue(SecondTableName, out schema))
{
schema = relatedQuery.Schema;
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)Second), First, conn));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(First));
}
}
else if (FirstType == ValueObjectType.ColumnName)
{
if (FirstTableName != null)
{
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(FirstTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)First));
}
else
{
outputBuilder.Append(First == null ? @"NULL" : First);
}
if (Comparision != WhereComparision.None)
{
switch (Comparision)
{
case WhereComparision.EqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS ");
else outputBuilder.Append(@" = ");
break;
case WhereComparision.NotEqualsTo:
if (First == null || Second == null) outputBuilder.Append(@" IS NOT ");
else outputBuilder.Append(@" <> ");
break;
case WhereComparision.GreaterThan:
outputBuilder.Append(@" > ");
break;
case WhereComparision.GreaterThanOrEqual:
outputBuilder.Append(@" >= ");
break;
case WhereComparision.LessThan:
outputBuilder.Append(@" < ");
break;
case WhereComparision.LessThanOrEqual:
outputBuilder.Append(@" <= ");
break;
case WhereComparision.Is:
outputBuilder.Append(@" IS ");
break;
case WhereComparision.IsNot:
outputBuilder.Append(@" IS NOT ");
break;
case WhereComparision.Like:
outputBuilder.Append(@" LIKE ");
break;
case WhereComparision.Between:
outputBuilder.Append(@" BETWEEN ");
break;
case WhereComparision.In:
outputBuilder.Append(@" IN ");
break;
case WhereComparision.NotIn:
outputBuilder.Append(@" NOT IN ");
break;
}
if (Comparision != WhereComparision.In && Comparision != WhereComparision.NotIn)
{
if (SecondType == ValueObjectType.Value)
{
if (Second is Query)
{
outputBuilder.Append('(');
outputBuilder.Append(((Query)Second).BuildCommand(conn));
outputBuilder.Append(')');
}
else
{
if (FirstType == ValueObjectType.ColumnName)
{
// Match SECOND value to FIRST's column type
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(Query.PrepareColumnValue(rightTableSchema.Columns.Find((string)First), Second, conn));
}
else
{
TableSchema schema;
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Second, conn));
}
}
else
{
outputBuilder.Append(conn.PrepareValue(Second));
}
}
}
else if (SecondType == ValueObjectType.ColumnName)
{
if (SecondTableName != null)
{
if (object.ReferenceEquals(SecondTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(SecondTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)Second));
}
else
{
if (Second == null) outputBuilder.Append(@"NULL");
else outputBuilder.Append(Second);
}
}
else
{
if (Second is Query) outputBuilder.AppendFormat(@"({0})", Second.ToString());
else
{
ICollection collIn = Second as ICollection;
if (collIn != null)
{
StringBuilder sbIn = new StringBuilder();
sbIn.Append('(');
bool first = true;
TableSchema schema = null;
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
foreach (object objIn in collIn)
{
if (first) first = false; else sbIn.Append(',');
if (schema != null) sbIn.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), objIn, conn));
else sbIn.Append(conn.PrepareValue(objIn));
}
sbIn.Append(')');
outputBuilder.Append(sbIn.ToString());
}
else outputBuilder.Append(Second);
}
}
if (Comparision == WhereComparision.Between)
{
outputBuilder.Append(@" AND ");
if (ThirdType == ValueObjectType.Value)
{
if (FirstType == ValueObjectType.ColumnName)
{
TableSchema schema = null;
if (object.ReferenceEquals(FirstTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
schema = rightTableSchema;
}
else
{
if (FirstTableName == null || !relatedQuery.TableAliasMap.TryGetValue(FirstTableName, out schema))
{
schema = relatedQuery.Schema;
}
}
outputBuilder.Append(Query.PrepareColumnValue(schema.Columns.Find((string)First), Third, conn));
}
else outputBuilder.Append(conn.PrepareValue(Third));
}
else if (ThirdType == ValueObjectType.ColumnName)
{
if (ThirdTableName != null)
{
if (object.ReferenceEquals(ThirdTableName, JoinColumnPair.RIGHT_TABLE_PLACEHOLDER_ID))
{
outputBuilder.Append(conn.EncloseFieldName(rightTableName));
}
else
{
outputBuilder.Append(conn.EncloseFieldName(ThirdTableName));
}
outputBuilder.Append('.');
}
outputBuilder.Append(conn.EncloseFieldName((string)Third));
}
else outputBuilder.Append(Third == null ? @"NULL" : Third);
}
if (Comparision == WhereComparision.Like)
{
outputBuilder.Append(' ');
outputBuilder.Append(conn.LikeEscapingStatement);
outputBuilder.Append(' ');
}
}
}
}
public Query Where(Query query, WhereComparison comparison, object value, ValueObjectType valueType = ValueObjectType.Value)
{
return Where(new Where(WhereCondition.AND, query, comparison, value, valueType));
}
public Query Where(Query query, WhereComparison comparison, string tableName, string columnName)
{
return Where(new Where(WhereCondition.AND, query, comparison, tableName, columnName));
}
