private void JoinViaIn(SqlStatement statement, SqlSelect @select)
{
SqlTableRef table = GetStatementTable(statement);
SqlExpression where = GetStatementWhere(statement);
JoinedTableRef = table;
PrimaryIndexMapping indexMapping = PrimaryIndexes[0];
var columns = new List <ColumnInfo>();
foreach (ColumnInfo columnInfo in indexMapping.PrimaryIndex.KeyColumns.Keys)
{
SqlSelect s = select.ShallowClone();
foreach (ColumnInfo column in columns)
{
SqlBinary ex = SqlDml.Equals(SqlDml.TableColumn(s.From, column.Name), SqlDml.TableColumn(table, column.Name));
s.Where = s.Where.IsNullReference() ? ex : SqlDml.And(s.Where, ex);
}
s.Columns.Clear();
s.Columns.Add(SqlDml.TableColumn(s.From, columnInfo.Name));
SqlBinary @in = SqlDml.In(SqlDml.TableColumn(table, columnInfo.Name), s);
@where = @where.IsNullReference() ? @in : SqlDml.And(@where, @in);
columns.Add(columnInfo);
}
SetStatementWhere(statement, where);
}
private void JoinViaFrom(SqlStatement statement, SqlSelect select)
{
var queryRef = SqlDml.QueryRef(select, "source");
SetStatementFrom(statement, queryRef);
var sqlTableRef = GetStatementTable(statement);
SqlExpression whereExpression = null;
PrimaryIndexMapping indexMapping = PrimaryIndexes[0];
foreach (ColumnInfo columnInfo in indexMapping.PrimaryIndex.KeyColumns.Keys)
{
var leftColumn = queryRef.Columns[columnInfo.Name];
var rightColumn = sqlTableRef == null?GetStatementTable(statement).Columns[columnInfo.Name] : sqlTableRef.Columns[columnInfo.Name];
if (leftColumn == null || rightColumn == null)
{
throw new InvalidOperationException("Source query doesn't contain one of key columns of updated table.");
}
var columnEqualityExperssion = SqlDml.Equals(queryRef.Columns[columnInfo.Name], sqlTableRef.Columns[columnInfo.Name]);
if (whereExpression == null)
{
whereExpression = columnEqualityExperssion;
}
else
{
whereExpression = SqlDml.And(whereExpression, columnEqualityExperssion);
}
}
SetStatementWhere(statement, whereExpression);
}
public SqlExpression IntToBoolean(SqlExpression expression)
{
// optimization: omitting IntToBoolean(BooleanToInt(x)) sequences
if (expression.NodeType == SqlNodeType.Cast)
{
var operand = ((SqlCast)expression).Operand;
if (operand.NodeType == SqlNodeType.Case)
{
var _case = (SqlCase)operand;
if (_case.Count == 1)
{
var firstCaseItem = _case.First();
var whenTrue = firstCaseItem.Value as SqlLiteral <int>;
var whenFalse = _case.Else as SqlLiteral <int>;
if (!ReferenceEquals(whenTrue, null) &&
!ReferenceEquals(whenFalse, null) &&
whenTrue.Value == 1 &&
whenFalse.Value == 0)
{
return(firstCaseItem.Key);
}
}
}
}
return(SqlDml.Equals(expression, 1));
}
public void Test026()
{
string nativeSql = @"SELECT
p.CustomerId,
p.Commission
FROM
invoice p
WHERE
p.Commission = (SELECT MIN(Commission) AS LowestCommission FROM invoice)";
SqlTableRef invoice1 = SqlDml.TableRef(schema.Tables["invoice"], "p1");
SqlTableRef invoice2 = SqlDml.TableRef(schema.Tables["invoice"], "p2");
SqlSelect innerSelect = SqlDml.Select(invoice2);
innerSelect.Columns.Add(SqlDml.Min(invoice2["Commission"]));
SqlSelect select = SqlDml.Select(invoice1);
select.Columns.Add(invoice1["CustomerId"]);
select.Columns.Add(invoice1["Commission"]);
select.Where = SqlDml.Equals(invoice1["Commission"], innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test026()
{
string nativeSql = @"SELECT
p.customer_id,
p.amount
FROM
payment p
WHERE
p.amount = (SELECT MAX(amount) AS LowestPayment FROM payment)";
SqlTableRef payment1 = SqlDml.TableRef(schema.Tables["payment"], "p1");
SqlTableRef payment2 = SqlDml.TableRef(schema.Tables["payment"], "p2");
SqlSelect innerSelect = SqlDml.Select(payment2);
innerSelect.Columns.Add(SqlDml.Max(payment2["amount"]));
SqlSelect select = SqlDml.Select(payment1);
select.Columns.Add(payment1["customer_id"]);
select.Columns.Add(payment1["amount"]);
select.Where = SqlDml.Equals(payment1["amount"], innerSelect);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
private void JoinViaIn(SqlStatement statement, SqlSelect select)
{
var table = GetStatementTable(statement);
var where = GetStatementWhere(statement);
JoinedTableRef = table;
var indexMapping = PrimaryIndexes[0];
var columns = new List <ColumnInfo>();
foreach (var columnInfo in indexMapping.PrimaryIndex.KeyColumns.Keys)
{
var s = (SqlSelect)select.Clone();
foreach (var column in columns)
{
var ex = SqlDml.Equals(s.From.Columns[column.Name], table.Columns[column.Name]);
s.Where = s.Where.IsNullReference() ? ex : SqlDml.And(s.Where, ex);
}
var existingColumns = s.Columns.ToChainedBuffer();
s.Columns.Clear();
var columnToAdd = existingColumns.First(c => c.Name.Equals(columnInfo.Name, StringComparison.Ordinal));
s.Columns.Add(columnToAdd);
var @in = SqlDml.In(SqlDml.TableColumn(table, columnInfo.Name), s);
where = where.IsNullReference() ? @in : SqlDml.And(where, @in);
columns.Add(columnInfo);
}
SetStatementWhere(statement, where);
}
public static SqlExpression EnumHasFlag(SqlExpression _this,
[Type(typeof(Enum))] SqlExpression value)
{
var right = GetSqlLiterExpression(value);
var a = right is SqlLiteral <bool>;
return(SqlDml.Equals(SqlDml.BitAnd(GetSqlLiterExpression(_this), right), right));
}
public void DeleteTest02()
{
var tableRef = SqlDml.TableRef(Catalog.DefaultSchema.Tables["Department"]);
var update = SqlDml.Delete(tableRef);
update.Where = SqlDml.Equals(tableRef.Columns["Name"], SqlDml.Literal("Human Resources Department"));
Console.WriteLine(Driver.Compile(update).GetCommandText());
using (var command = Connection.CreateCommand(update)) {
command.ExecuteNonQuery();
}
}
public void UpdateTest04()
{
var tableRef = SqlDml.TableRef(Catalog.DefaultSchema.Tables["Department"]);
var update = SqlDml.Update(tableRef);
update.Values.Add(tableRef.Columns["ModifiedDate"], SqlDml.Literal(DateTime.Now.Date));
update.Where = SqlDml.Equals(tableRef.Columns["Name"], SqlDml.Literal("Human Resources Department"));
update.Limit = SqlDml.Native("100");
Console.WriteLine(Driver.Compile(update).GetCommandText());
using (var command = Connection.CreateCommand(update)) {
command.ExecuteNonQuery();
}
}
private SqlExpression TryTranslateEqualitySpecialCases(SqlExpression left, SqlExpression right)
{
if (right.NodeType == SqlNodeType.Null || emptyStringIsNull && IsEmptyStringLiteral(right))
{
return(SqlDml.IsNull(left));
}
object id = null;
if (right.NodeType == SqlNodeType.Placeholder)
{
id = ((SqlPlaceholder)right).Id;
}
else if (right.NodeType == SqlNodeType.Cast && ((SqlCast)(right)).Operand.NodeType == SqlNodeType.Placeholder)
{
id = ((SqlPlaceholder)((SqlCast)(right)).Operand).Id;
}
if (id != null)
{
return(SqlDml.Variant(id, SqlDml.Equals(left, right), SqlDml.IsNull(left)));
}
return(null);
}
private SqlExpression TryTranslateCompareExpression(BinaryExpression expression)
{
bool isGoodExpression =
expression.Left.NodeType == ExpressionType.Call && expression.Right.NodeType == ExpressionType.Constant ||
expression.Right.NodeType == ExpressionType.Call && expression.Left.NodeType == ExpressionType.Constant;
if (!isGoodExpression)
{
return(null);
}
MethodCallExpression callExpression;
ConstantExpression constantExpression;
bool swapped;
if (expression.Left.NodeType == ExpressionType.Call)
{
callExpression = (MethodCallExpression)expression.Left;
constantExpression = (ConstantExpression)expression.Right;
swapped = false;
}
else
{
callExpression = (MethodCallExpression)expression.Right;
constantExpression = (ConstantExpression)expression.Left;
swapped = true;
}
var method = (MethodInfo)callExpression.Method.GetInterfaceMember() ?? callExpression.Method;
var methodType = method.DeclaringType;
// There no methods in IComparable except CompareTo so checking only DeclatingType.
bool isCompareTo = methodType == typeof(IComparable) ||
methodType.IsGenericType && methodType.GetGenericTypeDefinition() == typeof(IComparable <>);
bool isVbStringCompare = method.DeclaringType.FullName == "Microsoft.VisualBasic.CompilerServices.Operators" &&
method.Name == "CompareString" &&
method.GetParameters().Length == 3 &&
method.IsStatic;
bool isCompare = method.Name == "Compare" && method.GetParameters().Length == 2 && method.IsStatic;
if (!isCompareTo && !isCompare && !isVbStringCompare)
{
return(null);
}
if (constantExpression.Value == null)
{
return(null);
}
if (!(constantExpression.Value is int))
{
return(null);
}
var constant = (int)constantExpression.Value;
SqlExpression leftComparand = null;
SqlExpression rightComparand = null;
if (isCompareTo)
{
leftComparand = Visit(callExpression.Object);
rightComparand = Visit(callExpression.Arguments[0]);
}
if (isCompare || isVbStringCompare)
{
leftComparand = Visit(callExpression.Arguments[0]);
rightComparand = Visit(callExpression.Arguments[1]);
}
if (swapped)
{
var tmp = leftComparand;
leftComparand = rightComparand;
rightComparand = tmp;
}
if (constant > 0)
{
switch (expression.NodeType)
{
case ExpressionType.Equal:
case ExpressionType.GreaterThan:
case ExpressionType.GreaterThanOrEqual:
return(SqlDml.GreaterThan(leftComparand, rightComparand));
case ExpressionType.NotEqual:
case ExpressionType.LessThanOrEqual:
case ExpressionType.LessThan:
return(SqlDml.LessThanOrEquals(leftComparand, rightComparand));
default:
return(null);
}
}
if (constant < 0)
{
switch (expression.NodeType)
{
case ExpressionType.NotEqual:
case ExpressionType.GreaterThan:
case ExpressionType.GreaterThanOrEqual:
return(SqlDml.GreaterThanOrEquals(leftComparand, rightComparand));
case ExpressionType.Equal:
case ExpressionType.LessThanOrEqual:
case ExpressionType.LessThan:
return(SqlDml.LessThan(leftComparand, rightComparand));
default:
return(null);
}
}
switch (expression.NodeType)
{
case ExpressionType.GreaterThan:
return(SqlDml.GreaterThan(leftComparand, rightComparand));
case ExpressionType.GreaterThanOrEqual:
return(SqlDml.GreaterThanOrEquals(leftComparand, rightComparand));
case ExpressionType.Equal:
return(SqlDml.Equals(leftComparand, rightComparand));
case ExpressionType.NotEqual:
return(SqlDml.NotEquals(leftComparand, rightComparand));
case ExpressionType.LessThanOrEqual:
return(SqlDml.LessThanOrEquals(leftComparand, rightComparand));
case ExpressionType.LessThan:
return(SqlDml.LessThan(leftComparand, rightComparand));
default:
return(null);
}
}
protected override SqlExpression VisitBinary(BinaryExpression expression)
{
// handle x.CompareTo(y) > 0 and similar comparisons
SqlExpression result = TryTranslateCompareExpression(expression);
if (!result.IsNullReference())
{
return(result);
}
SqlExpression left;
SqlExpression right;
bool isEqualityCheck =
expression.NodeType == ExpressionType.Equal ||
expression.NodeType == ExpressionType.NotEqual;
bool isBooleanFixRequired = fixBooleanExpressions &&
(isEqualityCheck || expression.NodeType == ExpressionType.Coalesce) &&
(IsBooleanExpression(expression.Left) || IsBooleanExpression(expression.Right));
if (IsCharToIntConvert(expression.Left) && IsCharToIntConvert(expression.Right))
{
// chars are compared as integers, but we store them as strings and should compare them like strings.
left = Visit(GetOperand(expression.Left), isEqualityCheck);
right = Visit(GetOperand(expression.Right), isEqualityCheck);
}
else if (IsCharToIntConvert(expression.Left) && IsIntConstant(expression.Right))
{
// another case of char comparison
left = Visit(GetOperand(expression.Left), isEqualityCheck);
right = ConvertIntConstantToSingleCharString(expression.Right);
}
else if (IsIntConstant(expression.Left) && IsCharToIntConvert(expression.Right))
{
// another case of char comparison
left = ConvertIntConstantToSingleCharString(expression.Left);
right = Visit(GetOperand(expression.Right), isEqualityCheck);
}
else
{
// regular case
left = Visit(expression.Left, isEqualityCheck);
right = Visit(expression.Right, isEqualityCheck);
}
if (isBooleanFixRequired)
{
// boolean expressions should be compared as integers.
// additional check is required because some type information might be lost.
// we assume they already have correct format in that case.
if (IsBooleanExpression(expression.Left))
{
left = booleanExpressionConverter.BooleanToInt(left);
}
if (IsBooleanExpression(expression.Right))
{
right = booleanExpressionConverter.BooleanToInt(right);
}
}
//handle SQLite DateTime comparsion
if (dateTimeEmulation &&
left.NodeType != SqlNodeType.Null &&
right.NodeType != SqlNodeType.Null &&
IsComparisonExpression(expression) &&
(IsDateTimeExpression(expression.Left) || IsDateTimeExpression(expression.Right)))
{
left = SqlDml.Cast(left, SqlType.DateTime);
right = SqlDml.Cast(right, SqlType.DateTime);
}
//handle SQLite DateTimeOffset comparsion
if (dateTimeOffsetEmulation &&
left.NodeType != SqlNodeType.Null &&
right.NodeType != SqlNodeType.Null &&
IsComparisonExpression(expression) &&
(IsDateTimeOffsetExpression(expression.Left) || IsDateTimeOffsetExpression(expression.Right)))
{
left = SqlDml.Cast(left, SqlType.DateTimeOffset);
right = SqlDml.Cast(right, SqlType.DateTimeOffset);
}
// handle special cases
result = TryTranslateBinaryExpressionSpecialCases(expression, left, right);
if (!result.IsNullReference())
{
return(result);
}
// handle overloaded operators
if (expression.Method != null)
{
return(CompileMember(expression.Method, null, left, right));
}
//handle wrapped enums
SqlContainer container = left as SqlContainer;
if (container != null)
{
left = TryUnwrapEnum(container);
}
container = right as SqlContainer;
if (container != null)
{
right = TryUnwrapEnum(container);
}
switch (expression.NodeType)
{
case ExpressionType.Add:
case ExpressionType.AddChecked:
return(SqlDml.Add(left, right));
case ExpressionType.And:
return(IsBooleanExpression(expression.Left)
? SqlDml.And(left, right)
: SqlDml.BitAnd(left, right));
case ExpressionType.AndAlso:
return(SqlDml.And(left, right));
case ExpressionType.Coalesce:
SqlExpression coalesce = SqlDml.Coalesce(left, right);
if (isBooleanFixRequired)
{
coalesce = booleanExpressionConverter.IntToBoolean(coalesce);
}
return(coalesce);
case ExpressionType.Divide:
return(SqlDml.Divide(left, right));
case ExpressionType.Equal:
return(SqlDml.Equals(left, right));
case ExpressionType.ExclusiveOr:
return(SqlDml.BitXor(left, right));
case ExpressionType.GreaterThan:
return(SqlDml.GreaterThan(left, right));
case ExpressionType.GreaterThanOrEqual:
return(SqlDml.GreaterThanOrEquals(left, right));
case ExpressionType.LessThan:
return(SqlDml.LessThan(left, right));
case ExpressionType.LessThanOrEqual:
return(SqlDml.LessThanOrEquals(left, right));
case ExpressionType.Modulo:
return(SqlDml.Modulo(left, right));
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
return(SqlDml.Multiply(left, right));
case ExpressionType.NotEqual:
return(SqlDml.NotEquals(left, right));
case ExpressionType.Or:
return(IsBooleanExpression(expression.Left)
? SqlDml.Or(left, right)
: SqlDml.BitOr(left, right));
case ExpressionType.OrElse:
return(SqlDml.Or(left, right));
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
return(SqlDml.Subtract(left, right));
default:
throw new ArgumentOutOfRangeException("expression");
}
}
public static SqlExpression StringOperatorEquality(SqlExpression left, SqlExpression right)
{
return(SqlDml.Equals(left, right));
}