public void ExtractTest() //TODO: Determine how to extract. Use .NET? (Malisa)
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23"));
Console.WriteLine(sqlDriver.Compile(select).GetCommandText());
}
private static SqlExpression DateTimeOffsetTimeOfDay(SqlExpression dateTimeOffset)
{
return(SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset) * (60 * 60 * NanosecondsPerSecond)
+ SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset) * (60 * NanosecondsPerSecond)
+ SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset) * NanosecondsPerSecond
+ SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset) * NanosecondsPerMillisecond);
}
private void VisitDateTimeOffset(SqlExtract node)
{
switch (node.DateTimeOffsetPart)
{
case SqlDateTimeOffsetPart.Date:
DateTimeTruncate(DateTimeOffsetExtractDateTimeAsString(node.Operand)).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.DateTime:
DateTime(DateTimeOffsetExtractDateTimeAsString(node.Operand)).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.LocalDateTime:
DateTimeOffsetToLocalDateTime(node.Operand).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.UtcDateTime:
DateTimeOffsetToUtcDateTime(node.Operand).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.Offset:
(DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand)).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.TimeZoneHour:
(DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand) / NanosecondsPerHour).AcceptVisitor(this);
return;
case SqlDateTimeOffsetPart.TimeZoneMinute:
(((DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand)) % NanosecondsPerHour) / (60 * NanosecondsPerSecond)).AcceptVisitor(this);
return;
}
Visit(SqlDml.Extract(ConvertDateTimeOffsetPartToDateTimePart(node.DateTimeOffsetPart), DateTimeOffsetExtractDateTimeAsString(node.Operand)));
}
public void ExtractTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23"));
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
public virtual void ExtractNanosecondPartTest()
{
if (IsNanosecondSupported)
{
CheckEquality(SqlDml.Extract(SqlDateTimeOffsetPart.Nanosecond, DefaultDateTimeOffset), TryTranformToLocalZone(DefaultDateTimeOffset).Millisecond * 1000000);
}
}
public virtual void DateTimeExtractDayTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.Day, new DateTime(2006, 5, 4)));
select.Columns.Add(4);
}
protected virtual SqlExpression DateTimeTruncate(SqlExpression date)
{
return(DateAddMillisecond(DateAddSecond(DateAddMinute(DateAddHour(date,
-SqlDml.Extract(SqlDateTimePart.Hour, date)),
-SqlDml.Extract(SqlDateTimePart.Minute, date)),
-SqlDml.Extract(SqlDateTimePart.Second, date)),
-SqlDml.Extract(SqlDateTimePart.Millisecond, date)));
}
public virtual void IntervalExtractMillisecondTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlIntervalPart.Millisecond, new TimeSpan(6, 5, 4, 3, 2)));
select.Columns.Add(2);
}
public virtual void IntervalExtractHourTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlIntervalPart.Hour, new TimeSpan(6, 5, 4, 3, 2)));
select.Columns.Add(5);
}
public virtual void DateTimeExtractDayOfYearTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.DayOfYear, new DateTime(2005, 2, 2)));
select.Columns.Add(33);
}
public virtual void DateTimeExtractDayOfWeekTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.DayOfWeek, new DateTime(2009, 3, 2)));
select.Columns.Add((int)DayOfWeek.Monday);
}
public virtual void DateTimeExtractMillisecondTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.Millisecond, new DateTime(2006, 5, 4, 3, 2, 1, 333)));
select.Columns.Add(333);
}
public virtual void DateTimeExtractHourTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.Hour, new DateTime(2006, 5, 4, 3, 2, 1, 333)));
select.Columns.Add(3);
}
public virtual void DateTimeExtractYearTest()
{
var select = SqlDml.Select();
select.Columns.Add(
SqlDml.Extract(SqlDateTimePart.Year, new DateTime(2006, 5, 4)));
select.Columns.Add(2006);
CompareColumnEquality(select);
}
public void Test014_9()
{
string nativeSql = @"select strftime('%H', 'now') AS Hour ";
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Hour, "NOW"), "Hour");
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test014_6()
{
string nativeSql = @"select strftime('%d', 'now') AS DayOfMonth ";
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "NOW"), "DayOfMonth");
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test014_12()
{
string nativeSql = @"select strftime('%f', 'now') AS Milliseconds ";
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Millisecond, "NOW"), "Milliseconds");
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
/// <summary>
/// Converts the specified interval expression to expression
/// that represents number of milliseconds in that interval.
/// This is a generic implementation via <see cref="SqlExtract"/>s.
/// It's suitable for any server, but can be inefficient.
/// </summary>
/// <param name="interval">The interval to convert.</param>
/// <returns>Result of conversion.</returns>
public static SqlExpression IntervalToMilliseconds(SqlExpression interval)
{
var days = SqlDml.Extract(SqlIntervalPart.Day, interval);
var hours = SqlDml.Extract(SqlIntervalPart.Hour, interval);
var minutes = SqlDml.Extract(SqlIntervalPart.Minute, interval);
var seconds = SqlDml.Extract(SqlIntervalPart.Second, interval);
var milliseconds = SqlDml.Extract(SqlIntervalPart.Millisecond, interval);
return((((days * 24L + hours) * 60L + minutes) * 60L + seconds) * 1000L + milliseconds);
}
private static SqlExpression IntervalToIsoString(SqlExpression interval, bool signed)
{
if (!signed)
{
return(SqlDml.FunctionCall("TO_CHAR", interval, "HH24:MI"));
}
var hours = SqlDml.FunctionCall("TO_CHAR", SqlDml.Extract(SqlIntervalPart.Hour, interval), "SG09");
var minutes = SqlDml.FunctionCall("TO_CHAR", SqlDml.Extract(SqlIntervalPart.Minute, interval), "FM09");
return(SqlDml.Concat(hours, ":", minutes));
}
private static SqlExpression DateTimeOffsetTruncate(SqlExpression dateTimeOffset)
{
return(SqlDml.Cast(
DateAddMillisecond(
DateAddSecond(
DateAddMinute(
DateAddHour(dateTimeOffset,
-SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset)),
-SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset)),
SqlType.DateTime));
}
public static SqlExpression DateTimeDayOfWeek(SqlExpression _this)
{
var baseExpression = ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.DayOfWeek, _this));
var context = ExpressionTranslationContext.Current;
if (context == null)
{
return(baseExpression);
}
if (context.ProviderInfo.ProviderName == WellKnown.Provider.MySql)
{
return(baseExpression - 1); //Mysql starts days of week from 1 unlike in .Net.
}
return(baseExpression);
}
public void Test024()
{
string nativeSql = @"SELECT YEAR(r.rental_date) as Year, COUNT(*) Rented
FROM rental r
GROUP BY YEAR(r.rental_date)";
SqlTableRef rental = SqlDml.TableRef(schema.Tables["rental"], "r");
SqlSelect select = SqlDml.Select(rental);
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]), "Year");
select.Columns.Add(SqlDml.Count(), "Rented");
select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]));
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test024()
{
string nativeSql = @"SELECT
strftime('%Y', PaymentDate) as Year,
COUNT(*) Required
FROM invoice r
GROUP BY strftime('%Y', PaymentDate)";
SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "r");
SqlSelect select = SqlDml.Select(invoice);
select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]), "Year");
select.Columns.Add(SqlDml.Count(), "Required");
select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]));
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
/// <summary>
/// Converts the specified interval expression to expression
/// that represents number of milliseconds in that interval.
/// This is a generic implementation via <see cref="SqlExtract"/>s.
/// It's suitable for any server, but can be inefficient.
/// </summary>
/// <param name="interval">The interval to convert.</param>
/// <returns>Result of conversion.</returns>
public static SqlExpression IntervalToNanoseconds(SqlExpression interval)
{
var nanoseconds = SqlDml.Extract(SqlIntervalPart.Nanosecond, interval);
return(IntervalToMilliseconds(interval) * 1000000L + nanoseconds);
}
public static SqlExpression DateTimeDayOfYear(SqlExpression _this)
{
return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.DayOfYear, _this)));
}
public static SqlExpression DateTimeMillisecond(SqlExpression _this)
{
return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.Millisecond, _this)));
}
public static SqlExpression TimeSpanDays(SqlExpression _this)
{
return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlIntervalPart.Day, _this)));
}
public static SqlExpression TimeSpanMilliseconds(SqlExpression _this)
{
return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlIntervalPart.Millisecond, _this)));
}
public virtual void DateTimeExtractDayTest()
{
CheckEquality(
SqlDml.Extract(SqlDateTimePart.Day, DefaultDateTime),
DefaultDateTime.Day);
}
public virtual void DateTimeExtractMonthTest()
{
CheckEquality(
SqlDml.Extract(SqlDateTimePart.Month, DefaultDateTime),
DefaultDateTime.Month);
}
