public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit) { if (querySqlString.IndexOfCaseInsensitive(" ORDER BY ") < 0) { querySqlString = querySqlString.Append(" ORDER BY GETDATE()"); } return querySqlString.Append(string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offset, limit)); }
public static SqlString Apply(SqlString sql) { if (text == null) return sql; var s = "from " + theTable; var index = sql.IndexOfCaseInsensitive(s); var indexOfSpaceAfterAlias = sql.IndexOf(" ", index + s.Length +1, sql.Length, StringComparison.InvariantCultureIgnoreCase); if (indexOfSpaceAfterAlias == -1) return sql; return sql.Insert(indexOfSpaceAfterAlias, text); }
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit) { var builder = new SqlStringBuilder(queryString); if (queryString.IndexOfCaseInsensitive(" ORDER BY ") < 0) builder.Add(" ORDER BY GETDATE()"); builder.Add(" OFFSET "); if (offset == null) builder.Add("0"); else builder.Add(offset); builder.Add(" ROWS"); if (limit != null) { builder.Add(" FETCH NEXT "); builder.Add(limit); builder.Add(" ROWS ONLY"); } return builder.ToSqlString(); }
public SqlString[] Split(string splitter) { int iterations = 0; SqlString temp = Compact(); List <SqlString> results = new List <SqlString>(); int index; do { index = temp.IndexOfCaseInsensitive(splitter); int locationOfComma = index == -1 ? temp.Length : index; if (iterations++ > 100) { Debugger.Break(); } results.Add(temp.Substring(0, locationOfComma)); temp = temp.Substring(locationOfComma + 1); } while (index != -1); return(results.ToArray()); }
public override SqlString ApplyLocksToSql(SqlString sql, IDictionary<string, LockMode> aliasedLockModes, IDictionary<string, string[]> keyColumnNames) { // TODO: merge additional lockoptions support in Dialect.applyLocksToSql var buffer = new StringBuilder(sql.ToString()); int correction = 0; foreach (KeyValuePair<string, LockMode> entry in aliasedLockModes) { LockMode mode = entry.Value; if (mode.GreaterThan(LockMode.Read)) { string alias = entry.Key; int start = -1; int end = -1; if (sql.EndsWith(" " + alias)) { start = (sql.Length - alias.Length) + correction; end = start + alias.Length; } else { int position = sql.IndexOfCaseInsensitive(" " + alias + " "); if (position <= -1) position = sql.IndexOfCaseInsensitive(" " + alias + ","); if (position > -1) { start = position + correction + 1; end = start + alias.Length; } } if (start > -1) { string lockHint = AppendLockHint(mode, alias); buffer.Remove(start, end - start + 1); buffer.Insert(start, lockHint); correction += (lockHint.Length - alias.Length); } } } return new SqlString(buffer.ToString()); }
public void IndexOfNonCompacted() { SqlString str = new SqlString(new object[] { "select ", " from" }); Assert.AreEqual(6, str.IndexOfCaseInsensitive(" ")); }
public void IndexOf() { SqlString str = new SqlString(new object[] { "select ", Parameter.Placeholder, " from table where x = ", Parameter.Placeholder }); Assert.AreEqual(0, str.IndexOfCaseInsensitive("select")); Assert.AreEqual(1, str.IndexOfCaseInsensitive("el")); Assert.AreEqual(7 + 1 + 6, str.IndexOfCaseInsensitive("table")); }
private static bool HasCollectionFilterParam(SqlString sqlFragment) { return sqlFragment.IndexOfCaseInsensitive("?") < 0; }
private static bool HasDynamicFilterParam(SqlString sqlFragment) { return sqlFragment.IndexOfCaseInsensitive(ParserHelper.HqlVariablePrefix) < 0; }
private static int GetFromIndex(SqlString querySqlString) { string subselect = querySqlString.GetSubselectString().ToString(); int fromIndex = querySqlString.IndexOfCaseInsensitive(subselect); if (fromIndex == -1) { fromIndex = querySqlString.ToString().ToLowerInvariant().IndexOf(subselect.ToLowerInvariant()); } return fromIndex; }
/// <summary> ///Jet engine has the following from clause syntax: ///<code> /// tableexpression[, tableexpression]* ///</code> ///where tableexpression is: ///<code> /// tablename [(INNER |LEFT | RIGHT) JOIN [(] tableexpression [)] ON ...] ///</code> ///where the parenthesises are necessary if the "inner" tableexpression is not just a single tablename. ///Additionally INNER JOIN cannot be nested in LEFT | RIGHT JOIN. ///To translate the simple non-parenthesized joins to the jet syntax, the following transformation must be done: ///<code> /// A join B on ... join C on ... join D on ..., E join F on ... join G on ..., H join I on ..., J ///has to be translated as: /// (select * from ((A join B on ...) join C on ...) join D on ...) as crazyAlias1, (select * from (E join F on ...) join G on ...) as crazyAlias2, (select * from H join I on ...) as crazyAlias3, J ///</code> /// </summary> /// <param name="sqlString">the sqlstring to transform</param> /// <returns>sqlstring with parenthesized joins.</returns> private SqlString FinalizeJoins(SqlString sqlString) { if (_queryCache.Contains(sqlString)) { return (SqlString)_queryCache[sqlString]; } var beginOfFrom = sqlString.IndexOfCaseInsensitive(FromClause); var endOfFrom = sqlString.IndexOfCaseInsensitive(WhereClause); var beginOfOrderBy = sqlString.IndexOfCaseInsensitive(OrderByClause); if (beginOfFrom < 0) { return sqlString; } if (beginOfOrderBy < 0) { if (endOfFrom < 0) { endOfFrom = sqlString.Length; } } else { endOfFrom = beginOfOrderBy; } var fromClause = sqlString.Substring(beginOfFrom, endOfFrom - beginOfFrom).ToString(); var wherePart = sqlString.Substring(endOfFrom); var fromClauseInWhere = wherePart.IndexOfCaseInsensitive(FromClause); var transformedFrom = TransformFromClause(fromClause); var processWhereJoins = string.Empty; if (fromClauseInWhere > -1) //has where clause, inspect other joins { var whereClause = wherePart.Substring(0, fromClauseInWhere); var criteria = wherePart.Substring(fromClauseInWhere).ToString(); processWhereJoins = whereClause + TransformFromClause(criteria); } //put it all together again var final = new SqlStringBuilder(sqlString.Count + 1); final.Add(sqlString.Substring(0, beginOfFrom)); final.Add(transformedFrom); if (string.IsNullOrEmpty(processWhereJoins)) { final.Add(sqlString.Substring(endOfFrom)); } else { final.Add(processWhereJoins); } SqlString ret = final.ToSqlString(); RestoreMissingParameters(sqlString, ref ret); _queryCache[sqlString] = ret; return ret; }
/// <summary> ///Jet engine has the following from clause syntax: ///<code> /// tableexpression[, tableexpression]* ///</code> ///where tableexpression is: ///<code> /// tablename [(INNER |LEFT | RIGHT) JOIN [(] tableexpression [)] ON ...] ///</code> ///where the parenthesises are necessary if the "inner" tableexpression is not just a single tablename. ///Additionally INNER JOIN cannot be nested in LEFT | RIGHT JOIN. ///To translate the simple non-parenthesized joins to the jet syntax, the following transformation must be done: ///<code> /// A join B on ... join C on ... join D on ..., E join F on ... join G on ..., H join I on ..., J ///has to be translated as: /// (select * from ((A join B on ...) join C on ...) join D on ...) as crazyAlias1, (select * from (E join F on ...) join G on ...) as crazyAlias2, (select * from H join I on ...) as crazyAlias3, J ///</code> /// </summary> /// <param name="sqlString">the sqlstring to transform</param> /// <returns>sqlstring with parenthesized joins.</returns> private SqlString FinalizeJoins(SqlString sqlString) { if (_queryCache.Contains(sqlString)) { return (SqlString) _queryCache[sqlString]; } var beginOfFrom = sqlString.IndexOfCaseInsensitive(FromClause); var endOfFrom = sqlString.IndexOfCaseInsensitive(WhereClause); var beginOfOrderBy = sqlString.IndexOfCaseInsensitive(OrderByClause); if (beginOfFrom < 0) { return sqlString; } if (beginOfOrderBy < 0) { if (endOfFrom < 0) { endOfFrom = sqlString.Length; } } else { endOfFrom = beginOfOrderBy; } var fromClause = sqlString.Substring(beginOfFrom, endOfFrom - beginOfFrom).ToString(); var transformedFrom = TransformFromClause(fromClause); //put it all together again var final = new SqlStringBuilder(sqlString.Count + 1); final.Add(sqlString.Substring(0, beginOfFrom)); final.Add(transformedFrom); final.Add(sqlString.Substring(endOfFrom)); SqlString ret = final.ToSqlString(); _queryCache[sqlString] = ret; return ret; }