public static SqlExpression StringConcat(
[Type(typeof(string))] SqlExpression str0,
[Type(typeof(string))] SqlExpression str1,
[Type(typeof(string))] SqlExpression str2)
{
return(SqlDml.Concat(SqlDml.Concat(str0, str1), str2));
}
protected static SqlConcat DateTimeToStringIso(SqlExpression dateTime)
{
var date = SqlDml.Substring(dateTime, 0, 10);
var time = SqlDml.Substring(dateTime, 11, 8);
return(SqlDml.Concat(date, SqlDml.Literal("T"), time));
}
public void ConcatTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Concat("a", SqlDml.Trim(" b")));
Console.WriteLine(sqlDriver.Compile(select).GetCommandText());
}
public override void CreateExpressionIndexTest()
{
// Creating index
var t = schema.Tables[TableName];
var i = t.CreateIndex(ExpressionIndexName);
var tr = SqlDml.TableRef(t);
i.CreateIndexColumn(tr["first"]);
i.CreateIndexColumn(tr["second"]);
i.CreateIndexColumn(SqlDml.Concat(tr["first"], " ", tr["second"]));
i.CreateIndexColumn(SqlDml.Concat(tr["second"], " ", tr["first"]));
ExecuteNonQuery(SqlDdl.Create(i));
// Extracting index and checking its properties
var c2 = ExtractCatalog();
var s2 = c2.DefaultSchema;
var t2 = s2.Tables[TableName];
var i2 = t2.Indexes[ExpressionIndexName];
Assert.IsNotNull(i2);
Assert.AreEqual(4, i2.Columns.Count);
Assert.IsTrue(!i2.Columns[2].Expression.IsNullReference());
Assert.IsTrue(!i2.Columns[3].Expression.IsNullReference());
}
public static SqlExpression StringRemove(SqlExpression _this,
[Type(typeof(int))] SqlExpression startIndex,
[Type(typeof(int))] SqlExpression count)
{
return(SqlDml.Concat(
SqlDml.Substring(_this, SqlDml.Literal(0), startIndex),
SqlDml.Substring(_this, startIndex + count)));
}
public static SqlExpression StringInsert(SqlExpression _this,
[Type(typeof(int))] SqlExpression startIndex,
[Type(typeof(string))] SqlExpression value)
{
return(SqlDml.Concat(SqlDml.Concat(
SqlDml.Substring(_this, 0, startIndex), value),
SqlDml.Substring(_this, startIndex, SqlDml.CharLength(_this) - startIndex)));
}
public void ConcatTest()
{
SqlSelect select = SqlDml.Select();
select.Columns.Add(SqlDml.Concat("a", "b"));
//select.Columns.Add("User: " + SqlDml.SessionUser()); //NOTE: Not supported by MySQL.
Console.WriteLine(SqlDriver.Compile(select).GetCommandText());
}
private static SqlExpression GenericLike(SqlExpression _this,
SqlExpression patternExpression, bool percentAtStart, bool percentAtEnd)
{
const string percent = "%";
const string ground = "_";
const string escape = "^";
const string escapeEscape = "^^";
const string escapeGround = "^_";
const string escapePercent = "^%";
var stringPattern = patternExpression as SqlLiteral <string>;
var charPattern = patternExpression as SqlLiteral <char>;
if (ReferenceEquals(stringPattern, null) && ReferenceEquals(charPattern, null))
{
SqlExpression result = SqlDml.Replace(patternExpression, SqlDml.Literal(escape), SqlDml.Literal(escapeEscape));
result = SqlDml.Replace(result, SqlDml.Literal(ground), SqlDml.Literal(escapeGround));
result = SqlDml.Replace(result, SqlDml.Literal(percent), SqlDml.Literal(escapePercent));
if (percentAtStart)
{
result = SqlDml.Concat(SqlDml.Literal(percent), result);
}
if (percentAtEnd)
{
result = SqlDml.Concat(result, SqlDml.Literal(percent));
}
result = SqlDml.Like(_this, result, escape);
return(result);
}
var originalPattern = !ReferenceEquals(stringPattern, null)
? stringPattern.Value
: charPattern.Value.ToString();
var escapedPattern = new StringBuilder(originalPattern);
escapedPattern
.Replace(escape, escapeEscape)
.Replace(percent, escapePercent)
.Replace(ground, escapeGround);
bool escaped = escapedPattern.Length > originalPattern.Length;
if (percentAtStart)
{
escapedPattern.Insert(0, percent);
}
if (percentAtEnd)
{
escapedPattern.Append(percent);
}
var pattern = escapedPattern.ToString();
return(escaped
? SqlDml.Like(_this, pattern, escape)
: SqlDml.Like(_this, pattern));
}
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));
}
public void Test017()
{
string nativeSql = "SELECT c.CustomerId, c.FirstName|| ', ' || c.LastName as FullName FROM customer c";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlSelect select = SqlDml.Select(customer);
select.Columns.Add(customer["CustomerId"]);
select.Columns.Add(SqlDml.Concat(SqlDml.Concat(customer["FirstName"], SqlDml.Literal(", ")), customer["LastName"]), "FullName");
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public void Test017()
{
string nativeSql = "SELECT c.customer_id, CONCAT(c.first_name, CONCAT(', ', c.last_name)) as FullName FROM customer c";
SqlTableRef customer = SqlDml.TableRef(schema.Tables["customer"], "c");
SqlSelect select = SqlDml.Select(customer);
select.Columns.Add(customer["customer_id"]);
select.Columns.Add(SqlDml.Concat(customer["first_name"], SqlDml.Concat(SqlDml.Literal(", "), customer["last_name"])),
"FullName"
);
Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
}
public override void Visit(SqlBinary node)
{
switch (node.NodeType)
{
// Bit XOR is not supported by SQLite
// but it can be easily emulated using remaining bit operators
case SqlNodeType.BitXor:
// A ^ B = (A | B) & ~(A & B)
var replacement = SqlDml.BitAnd(
SqlDml.BitOr(node.Left, node.Right),
SqlDml.BitNot(SqlDml.BitAnd(node.Left, node.Right)));
replacement.AcceptVisitor(this);
return;
case SqlNodeType.DateTimePlusInterval:
DateTimeAddInterval(node.Left, node.Right).AcceptVisitor(this);
return;
case SqlNodeType.DateTimeMinusInterval:
DateTimeAddInterval(node.Left, -node.Right).AcceptVisitor(this);
return;
case SqlNodeType.DateTimeMinusDateTime:
case SqlNodeType.DateTimeOffsetMinusDateTimeOffset:
DateTimeSubtractDateTime(node.Left, node.Right).AcceptVisitor(this);
return;
case SqlNodeType.DateTimeOffsetPlusInterval:
SqlDml.Concat(
DateTimeAddInterval(DateTimeOffsetExtractDateTimeAsString(node.Left), node.Right),
DateTimeOffsetExtractOffsetAsString(node.Left))
.AcceptVisitor(this);
return;
case SqlNodeType.DateTimeOffsetMinusInterval:
SqlDml.Concat(
DateTimeAddInterval(DateTimeOffsetExtractDateTimeAsString(node.Left), -node.Right),
DateTimeOffsetExtractOffsetAsString(node.Left))
.AcceptVisitor(this);
return;
default:
base.Visit(node);
return;
}
}
/// <inheritdoc/>
public override void Visit(SqlFunctionCall node)
{
switch (node.FunctionType)
{
case SqlFunctionType.Concat:
var exprs = new SqlExpression[node.Arguments.Count];
node.Arguments.CopyTo(exprs, 0);
Visit(SqlDml.Concat(exprs));
return;
case SqlFunctionType.DateTimeTruncate:
Visit(SqlDml.Cast(node.Arguments[0], new SqlValueType("Date")));
return;
case SqlFunctionType.IntervalToMilliseconds:
Visit(CastToLong(node.Arguments[0]) / NanosecondsPerMillisecond);
return;
case SqlFunctionType.IntervalConstruct:
case SqlFunctionType.IntervalToNanoseconds:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.DateTimeAddMonths:
Visit(DateAddMonth(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeAddYears:
Visit(DateAddYear(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeConstruct:
Visit(DateAddDay(DateAddMonth(DateAddYear(SqlDml.Cast(SqlDml.Literal(new DateTime(2001, 1, 1)), SqlType.DateTime),
node.Arguments[0] - 2001),
node.Arguments[1] - 1),
node.Arguments[2] - 1));
return;
case SqlFunctionType.DateTimeToStringIso:
Visit(DateTimeToStringIso(node.Arguments[0]));
return;
}
base.Visit(node);
}
protected override void CreateTable()
{
Table t;
t = schema.CreateTable(TableName);
t.CreateColumn("first", new SqlValueType(SqlType.VarChar, 50));
t.CreateColumn("second", new SqlValueType(SqlType.VarChar, 50));
var c1 = t.CreateColumn("third", new SqlValueType(SqlType.VarChar));
var c2 = t.CreateColumn("forth", new SqlValueType(SqlType.VarChar));
var tr = SqlDml.TableRef(t);
c1.Expression = SqlDml.Concat(tr["first"], " ", tr["second"]);
c1.IsPersisted = false;
c1.IsNullable = true;
c2.Expression = SqlDml.Concat(tr["second"], " ", tr["first"]);
c2.IsPersisted = false;
c2.IsNullable = true;
ExecuteNonQuery(SqlDdl.Create(t));
}
private static SqlExpression OffsetToOffsetAsString(SqlExpression offset)
{
var sign = '+';
var offsetAsInt = offset as SqlLiteral <int>;
var offsetAsDouble = offset as SqlLiteral <double>;
if (offsetAsInt != null)
{
if (offsetAsInt.Value < 0)
{
sign = '-';
offset = -offset;
}
}
else if (offsetAsDouble != null)
{
if (offsetAsDouble.Value < 0)
{
sign = '-';
offset = -offset;
}
}
return(SqlDml.Concat(sign, ToStringWithLeadZero(CastToInt(offset / 60), 2), ':', ToStringWithLeadZero(CastToInt(offset % 60), 2)));
}
/// <inheritdoc/>
public override void Visit(SqlFunctionCall node)
{
switch (node.FunctionType)
{
case SqlFunctionType.Truncate:
var argument = node.Arguments[0];
SqlDml.FunctionCall("TRUNCATE", argument, SqlDml.Literal(0)).AcceptVisitor(this);
return;
case SqlFunctionType.Concat:
var exprs = new SqlExpression[node.Arguments.Count];
node.Arguments.CopyTo(exprs, 0);
Visit(SqlDml.Concat(exprs));
return;
case SqlFunctionType.CharLength:
SqlDml.FunctionCall(translator.Translate(SqlFunctionType.CharLength), node.Arguments[0]).AcceptVisitor(this);
// SqlDml.CharLength(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.PadLeft:
case SqlFunctionType.PadRight:
SqlHelper.GenericPad(node).AcceptVisitor(this);
return;
case SqlFunctionType.Rand:
SqlDml.FunctionCall(translator.Translate(SqlFunctionType.Rand)).AcceptVisitor(this);
return;
case SqlFunctionType.Square:
SqlDml.Power(node.Arguments[0], 2).AcceptVisitor(this);
return;
case SqlFunctionType.IntervalToMilliseconds:
Visit(CastToLong(node.Arguments[0]) / NanosecondsPerMillisecond);
return;
case SqlFunctionType.IntervalConstruct:
case SqlFunctionType.IntervalToNanoseconds:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.DateTimeAddMonths:
Visit(DateAddMonth(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeAddYears:
Visit(DateAddYear(node.Arguments[0], node.Arguments[1]));
return;
case SqlFunctionType.DateTimeConstruct:
Visit(DateAddDay(DateAddMonth(DateAddYear(SqlDml.Literal(new DateTime(2001, 1, 1)),
node.Arguments[0] - 2001),
node.Arguments[1] - 1),
node.Arguments[2] - 1));
return;
case SqlFunctionType.DateTimeToStringIso:
Visit(DateTimeToStringIso(node.Arguments[0]));
return;
}
base.Visit(node);
}
protected SqlExpression ConstructDateTimeOffsetFromExpressions(SqlExpression datetimeStringExpression, SqlExpression offsetStringExpression)
{
return(SqlDml.Cast(SqlDml.Concat(datetimeStringExpression, " ", offsetStringExpression), SqlType.DateTimeOffset));
}
public static SqlExpression BuildAddressString(SqlExpression countryExpression, SqlExpression streetExpression, SqlExpression buildingExpression)
{
return(SqlDml.Concat(countryExpression, SqlDml.Literal(", "), streetExpression, SqlDml.Literal("-"), buildingExpression));
}
public override void Visit(SqlFunctionCall node)
{
switch (node.FunctionType)
{
case SqlFunctionType.CharLength:
(SqlDml.FunctionCall("LENGTH", node.Arguments) / 2).AcceptVisitor(this);
return;
case SqlFunctionType.PadLeft:
case SqlFunctionType.PadRight:
return;
case SqlFunctionType.Concat:
var nod = node.Arguments[0];
return;
case SqlFunctionType.Round:
// Round should always be called with 2 arguments
if (node.Arguments.Count == 1)
{
Visit(SqlDml.FunctionCall(translator.Translate(SqlFunctionType.Round), node.Arguments[0], SqlDml.Literal(0)));
return;
}
break;
case SqlFunctionType.Truncate:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.IntervalConstruct:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.IntervalToNanoseconds:
Visit(CastToLong(node.Arguments[0]));
return;
case SqlFunctionType.IntervalToMilliseconds:
Visit(CastToLong(node.Arguments[0] / NanosecondsPerMillisecond));
return;
case SqlFunctionType.DateTimeAddMonths:
DateAddMonth(node.Arguments[0], node.Arguments[1]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeAddYears:
DateAddYear(node.Arguments[0], node.Arguments[1]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeTruncate:
DateTimeTruncate(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeConstruct:
DateAddDay(DateAddMonth(DateAddYear(SqlDml.Literal(new DateTime(2001, 1, 1)),
node.Arguments[0] - 2001),
node.Arguments[1] - 1),
node.Arguments[2] - 1).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeToStringIso:
DateTimeToStringIso(node.Arguments[0]).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetAddMonths:
SqlDml.Concat(DateAddMonth(DateTimeOffsetExtractDateTimeAsString(node.Arguments[0]), node.Arguments[1]), DateTimeOffsetExtractOffsetAsString(node.Arguments[0])).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetAddYears:
SqlDml.Concat(DateAddYear(DateTimeOffsetExtractDateTimeAsString(node.Arguments[0]), node.Arguments[1]), DateTimeOffsetExtractOffsetAsString(node.Arguments[0])).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetConstruct:
SqlDml.Concat(node.Arguments[0], OffsetToOffsetAsString(node.Arguments[1])).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetToLocalTime:
SqlDml.Concat(DateTimeOffsetToLocalDateTime(node.Arguments[0]), ServerOffsetAsString()).AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetToUtcTime:
SqlDml.Concat(DateTimeOffsetToUtcDateTime(node.Arguments[0]), "+00:00").AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeOffsetTimeOfDay:
SqlDml.DateTimeMinusDateTime(
DateTimeOffsetExtractDateTimeAsString(node.Arguments[0]),
DateTimeTruncate(DateTimeOffsetExtractDateTimeAsString(node.Arguments[0])))
.AcceptVisitor(this);
return;
case SqlFunctionType.DateTimeToDateTimeOffset:
SqlDml.Concat(DateTime(node.Arguments[0]), ServerOffsetAsString()).AcceptVisitor(this);
return;
}
base.Visit(node);
}
/// Truncate string from start, if length larger resultStringLength; Add lead zero, if length less resultStringLength
/// (2, 3) => "002"; (41, 3) => "041", (4321, 3) => "321"
private static SqlExpression ToStringWithLeadZero(SqlExpression expression, int resultStringLength)
{
return(SqlDml.Substring(SqlDml.Concat(new String('0', resultStringLength), expression), -resultStringLength - 1, resultStringLength));
}
private static SqlExpression DateAddYear(SqlExpression date, SqlExpression years)
{
return(SqlDml.FunctionCall("STRFTIME", DateTimeFormat, date, SqlDml.Concat(years, " ", "YEARS")));
}
private static SqlExpression DateAddSeconds(SqlExpression date, SqlExpression seconds)
{
return(SqlDml.FunctionCall("STRFTIME", DateTimeFormat, date, SqlDml.Concat(seconds, " ", "SECONDS")));
}
private static SqlExpression DateAddDay(SqlExpression date, SqlExpression days)
{
return(SqlDml.FunctionCall("STRFTIME", DateTimeFormat, date, SqlDml.Concat(days, " ", "DAYS")));
}
private static SqlExpression DateAddMonth(SqlExpression date, SqlExpression months)
{
return(SqlDml.FunctionCall("STRFTIME", DateTimeFormat, date, SqlDml.Concat(months, " ", "MONTHS")));
}