public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
switch (part)
{
case Sql.DateParts.Quarter:
part = DateParts.Month;
number = builder.Mul(number, 3);
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
part = DateParts.Day;
break;
case Sql.DateParts.Week:
part = DateParts.Day;
number = builder.Mul(number, 7);
break;
}
var partSql = new SqlExpression(part.ToString());
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd", partSql, number, date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string function;
switch (part)
{
case Sql.DateParts.Year: function = "Add_Years"; break;
case Sql.DateParts.Quarter:
function = "Add_Months";
number = builder.Mul(number, 3);
break;
case Sql.DateParts.Month: function = "Add_Months"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.Day:
case Sql.DateParts.WeekDay: function = "Add_Days"; break;
case Sql.DateParts.Week:
function = "Add_Days";
number = builder.Mul(number, 7);
break;
case Sql.DateParts.Hour:
function = "Add_Seconds";
number = builder.Mul(number, 3600);
break;
case Sql.DateParts.Minute:
function = "Add_Seconds";
number = builder.Mul(number, 60);
break;
case Sql.DateParts.Second: function = "Add_Seconds"; break;
case Sql.DateParts.Millisecond:
function = "Add_Seconds";
number = builder.Div(number, 1000);
break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), function, date, number);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var expr = builder.Arguments[0];
var newExpr = expr.Transform(e =>
{
if (e.NodeType == ExpressionType.Convert || e.NodeType == ExpressionType.ConvertChecked)
{
var unary = (UnaryExpression)e;
var method = _method.MakeGenericMethod(unary.Operand.Type, unary.Type);
return(Expression.Call(null, method, unary.Operand));
}
return(e);
});
if (newExpr == expr)
{
builder.ResultExpression = builder.GetExpression(0);
return;
}
var sqlExpr = builder.ConvertExpressionToSql(newExpr);
sqlExpr = new QueryVisitor().Convert(sqlExpr, e =>
{
if (e is SqlFunction func && func.Name == "$Convert_Remover$")
{
return(func.Parameters[0]);
}
return(e);
});
public void Build(Sql.ISqExtensionBuilder builder)
{
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var secondsExpr = builder.Mul <int>(builder.Sub <int>(new SqlFunction(typeof(int), "Days", endDate), new SqlFunction(typeof(int), "Days", startDate)), new SqlValue(86400));
var midnight = builder.Sub <int>(new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", endDate), new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", startDate));
var resultExpr = builder.Add <int>(secondsExpr, midnight);
resultExpr = builder.GetValue <Sql.DateParts>(0) switch {
Sql.DateParts.Day => builder.Div(resultExpr, 86400),
Sql.DateParts.Hour => builder.Div(resultExpr, 3600),
Sql.DateParts.Minute => builder.Div(resultExpr, 60),
Sql.DateParts.Millisecond => builder.Add <int>(builder.Mul(resultExpr, 1000), builder.Div(builder.Sub <int>(new SqlFunction(typeof(int), "MICROSECOND", endDate), new SqlFunction(typeof(int), "MICROSECOND", startDate)), 1000)),
_ => throw new ArgumentOutOfRangeException()
};
builder.ResultExpression = resultExpr;
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var partStr = DatePartToStr(part);
var date = builder.GetExpression("date");
builder.ResultExpression = new SqlFunction(typeof(int), builder.Expression,
new SqlExpression(partStr, Precedence.Primary), date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>(0);
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var secondsExpr = builder.Mul <int>(builder.Sub <int>(
new SqlFunction(typeof(int), "Days", endDate),
new SqlFunction(typeof(int), "Days", startDate)),
new SqlValue(86400));
var midnight = builder.Sub <int>(
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", endDate),
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", startDate));
var resultExpr = builder.Add <int>(secondsExpr, midnight);
switch (part)
{
case Sql.DateParts.Day: resultExpr = builder.Div(resultExpr, 86400); break;
case Sql.DateParts.Hour: resultExpr = builder.Div(resultExpr, 3600); break;
case Sql.DateParts.Minute: resultExpr = builder.Div(resultExpr, 60); break;
case Sql.DateParts.Second: break;
case Sql.DateParts.Millisecond:
resultExpr = builder.Add <int>(
builder.Mul(resultExpr, 1000),
builder.Div(
builder.Sub <int>(
new SqlFunction(typeof(int), "MICROSECOND", endDate),
new SqlFunction(typeof(int), "MICROSECOND", startDate)),
1000));
break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = resultExpr;
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} || ' Year'"; break;
case Sql.DateParts.Quarter:
expStr = "({0}) || ' Month'";
number = builder.Mul(number, 3);
break;
case Sql.DateParts.Month: expStr = "{0} || ' Month'"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} || ' Day'"; break;
case Sql.DateParts.Week:
expStr = "({0}) || ' Day'";
number = builder.Mul(number, 7);
break;
case Sql.DateParts.Hour: expStr = "{0} || ' Hour'"; break;
case Sql.DateParts.Minute: expStr = "{0} || ' Minute'"; break;
case Sql.DateParts.Second: expStr = "{0} || ' Second'"; break;
case Sql.DateParts.Millisecond: expStr = "{0} || ' Millisecond'"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateTime", date,
new SqlExpression(expStr, Precedence.Additive, number));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} * Interval '1 Year'"; break;
case Sql.DateParts.Quarter: expStr = "{0} * Interval '1 Month' * 3"; break;
case Sql.DateParts.Month: expStr = "{0} * Interval '1 Month'"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} * Interval '1 Day'"; break;
case Sql.DateParts.Week: expStr = "{0} * Interval '1 Day' * 7"; break;
case Sql.DateParts.Hour: expStr = "{0} * Interval '1 Hour'"; break;
case Sql.DateParts.Minute: expStr = "{0} * Interval '1 Minute'"; break;
case Sql.DateParts.Second: expStr = "{0} * Interval '1 Second'"; break;
case Sql.DateParts.Millisecond: expStr = "{0} * Interval '1 Millisecond'"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTimeOffset?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} Year"; break;
case Sql.DateParts.Quarter: expStr = "({0} * 3) Month"; break;
case Sql.DateParts.Month: expStr = "{0} Month"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} Day"; break;
case Sql.DateParts.Week: expStr = "({0} * 7) Day"; break;
case Sql.DateParts.Hour: expStr = "{0} Hour"; break;
case Sql.DateParts.Minute: expStr = "{0} Minute"; break;
case Sql.DateParts.Second: expStr = "{0} Second"; break;
case Sql.DateParts.Millisecond: expStr = "({0} / 1000.0) Second"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Primary, number),
typeof(DateTime?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} * INTERVAL '1' YEAR"; break;
case Sql.DateParts.Quarter: expStr = "{0} * INTERVAL '3' MONTH"; break;
case Sql.DateParts.Month: expStr = "{0} * INTERVAL '1' MONTH"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} * INTERVAL '1' DAY"; break;
case Sql.DateParts.Week: expStr = "{0} * INTERVAL '7' DAY"; break;
case Sql.DateParts.Hour: expStr = "{0} * INTERVAL '1' HOUR"; break;
case Sql.DateParts.Minute: expStr = "{0} * INTERVAL '1' MINUTE"; break;
case Sql.DateParts.Second: expStr = "{0} * INTERVAL '1' SECOND"; break;
case Sql.DateParts.Millisecond: expStr = "{0} * INTERVAL '0.001' SECOND"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTime?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
switch (part)
{
case Sql.DateParts.Year:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 12));
break;
case Sql.DateParts.Quarter:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 3));
break;
case Sql.DateParts.Month:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", builder.GetExpression("date"), builder.GetExpression("number"));
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: builder.ResultExpression = builder.Add <DateTime>(date, number); break;
case Sql.DateParts.Week: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(number, 7)); break;
case Sql.DateParts.Hour: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 24)); break;
case Sql.DateParts.Minute: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 24)); break;
case Sql.DateParts.Second: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 60 * 24)); break;
// adding number to timestamp instead of adding interval leads to wrong result type and loose of precision
case Sql.DateParts.Millisecond: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(new SqlExpression("interval '0.001' second", Precedence.Primary), number, typeof(int))); break;
default:
throw new ArgumentOutOfRangeException();
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var expStr = builder.GetValue <Sql.DateParts>("part") switch {
Sql.DateParts.Year => "{0} + {1} Year",
Sql.DateParts.Quarter => "{0} + ({1} * 3) Month",
Sql.DateParts.Month => "{0} + {1} Month",
Sql.DateParts.DayOfYear => "{0} + {1} Day",
Sql.DateParts.Day => "{0} + {1} Day",
Sql.DateParts.WeekDay => "{0} + {1} Day",
Sql.DateParts.Week => "{0} + ({1} * 7) Day",
Sql.DateParts.Hour => "{0} + {1} Hour",
Sql.DateParts.Minute => "{0} + {1} Minute",
Sql.DateParts.Second => "{0} + {1} Second",
Sql.DateParts.Millisecond => "{0} + ({1} * 1000) Microsecond",
_ => throw new ArgumentOutOfRangeException()
};
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, 60, date, number);
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
if (!(builder.GetExpression("src") is SqlField field))
{
throw new InvalidOperationException("Can not get table");
}
var sqlTable = (SqlTable)field.Table !;
var newField = new SqlField(sqlTable, sqlTable.PhysicalName !);
builder.AddParameter("table_field", newField);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
switch (part)
{
case Sql.DateParts.Year:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 12));
break;
case Sql.DateParts.Quarter:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 3));
break;
case Sql.DateParts.Month:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", builder.GetExpression("date"), builder.GetExpression("number"));
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: builder.ResultExpression = builder.Add <DateTime>(date, number); break;
case Sql.DateParts.Week: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(number, 7)); break;
case Sql.DateParts.Hour: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 24)); break;
case Sql.DateParts.Minute: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 24)); break;
case Sql.DateParts.Second: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 60 * 24)); break;
case Sql.DateParts.Millisecond: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 1000 * 60 * 60 * 24)); break;
default:
throw new ArgumentOutOfRangeException();
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
var partStr = part switch
{
Sql.DateParts.Year => "yyyy",
Sql.DateParts.Quarter => "q",
Sql.DateParts.Month => "m",
Sql.DateParts.DayOfYear => "y",
Sql.DateParts.Day => "d",
Sql.DateParts.Week => "ww",
Sql.DateParts.WeekDay => "w",
Sql.DateParts.Hour => "h",
Sql.DateParts.Minute => "n",
Sql.DateParts.Second => "s",
_ => throw new ArgumentOutOfRangeException(),
};
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd",
new SqlValue(partStr), number, date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string partStr;
switch (part)
{
case Sql.DateParts.Year: partStr = "yyyy"; break;
case Sql.DateParts.Quarter: partStr = "q"; break;
case Sql.DateParts.Month: partStr = "m"; break;
case Sql.DateParts.DayOfYear: partStr = "y"; break;
case Sql.DateParts.Day: partStr = "d"; break;
case Sql.DateParts.Week: partStr = "ww"; break;
case Sql.DateParts.WeekDay: partStr = "w"; break;
case Sql.DateParts.Hour: partStr = "h"; break;
case Sql.DateParts.Minute: partStr = "n"; break;
case Sql.DateParts.Second: partStr = "s"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd",
new SqlValue(partStr), number, date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr = "strftime('%Y-%m-%d %H:%M:%f', {0},";
switch (part)
{
case Sql.DateParts.Year: expStr += "{1} || ' Year')"; break;
case Sql.DateParts.Quarter: expStr += "({1}*3) || ' Month')"; break;
case Sql.DateParts.Month: expStr += "{1} || ' Month')"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr += "{1} || ' Day')"; break;
case Sql.DateParts.Week: expStr += "({1}*7) || ' Day')"; break;
case Sql.DateParts.Hour: expStr += "{1} || ' Hour')"; break;
case Sql.DateParts.Minute: expStr += "{1} || ' Minute')"; break;
case Sql.DateParts.Second: expStr += "{1} || ' Second')"; break;
case Sql.DateParts.Millisecond: expStr += "({1}/1000.0) || ' Second')"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Concatenate, date, number);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} + ({1}) Year"; break;
case Sql.DateParts.Quarter: expStr = "{0} + (({1}) * 3) Month"; break;
case Sql.DateParts.Month: expStr = "{0} + ({1}) Month"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} + ({1}) Day"; break;
case Sql.DateParts.Week: expStr = "{0} + (({1}) * 7) Day"; break;
case Sql.DateParts.Hour: expStr = "{0} + ({1}) Hour"; break;
case Sql.DateParts.Minute: expStr = "{0} + ({1}) Minute"; break;
case Sql.DateParts.Second: expStr = "{0} + ({1}) Second"; break;
case Sql.DateParts.Millisecond: expStr = "{0} + (({1}) * 1000) Microsecond"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Additive, date, number);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var field = builder.GetExpression("src") as SqlField;
if (field == null)
{
throw new InvalidOperationException("Can not get table");
}
var sqlTable = (SqlTable)field.Table;
var newField = new SqlField
{
Name = sqlTable.PhysicalName,
Table = sqlTable
};
builder.AddParameter("table_field", newField);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
builder.Expression = "JSON_MODIFY({source}, {path}, {value})";
builder.AddParameter("source", builder.GetExpression(0));
var member = (MemberExpression)((LambdaExpression)((UnaryExpression)builder.Arguments[1]).Operand).Body;
builder.AddParameter("path", $"$.{member.Member.Name}");
var propertyExpression = (MemberExpression)builder.Arguments[2];
var memberExpression = (MemberExpression)propertyExpression.Expression !;
var fieldInfo = (FieldInfo)memberExpression.Member;
var valueExpression = (ConstantExpression)memberExpression.Expression !;
var value = ((PropertyInfo)propertyExpression.Member).GetValue(fieldInfo.GetValue(valueExpression.Value)) !;
builder.AddParameter("value", value.ToString() !);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
string exprStr;
var part = builder.GetValue <Sql.DateParts>("part");
switch (part)
{
case Sql.DateParts.Year: exprStr = "Year({date})"; break;
case Sql.DateParts.Quarter:
{
builder.Expression = "Month({date})";
builder.ResultExpression =
builder.Inc(builder.Div(builder.Dec(builder.ConvertToSqlExpression(Precedence.Primary)), 3));
return;
}
case Sql.DateParts.Month: exprStr = "Month({date})"; break;
case Sql.DateParts.DayOfYear:
{
var param = builder.GetExpression("date");
builder.ResultExpression = builder.Inc(
builder.Sub <int>(
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlFunction(typeof(int?), "Month", param),
new SqlFunction(typeof(int?), "Day", param),
new SqlFunction(typeof(int?), "Year", param)),
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlValue(1),
new SqlValue(1),
new SqlFunction(typeof(int?), "Year", param)))
);
return;
}
case Sql.DateParts.Day: exprStr = "Day({date})"; break;
case Sql.DateParts.Week: exprStr = "((Extend({date}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({date}))), Year({date}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int"; break;
case Sql.DateParts.WeekDay:
{
builder.Expression = "weekDay({date})";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary));
return;
}
case Sql.DateParts.Hour: exprStr = "({date}::datetime Hour to Hour)::char(3)::int"; break;
case Sql.DateParts.Minute: exprStr = "({date}::datetime Minute to Minute)::char(3)::int"; break;
case Sql.DateParts.Second: exprStr = "({date}::datetime Second to Second)::char(3)::int"; break;
case Sql.DateParts.Millisecond: exprStr = "Millisecond({date})"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.Expression = exprStr;
}
