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; }