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