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