public override SqlString OnPrepareStatement(SqlString sql)
{
Match match = Regex.Match(sql.ToString());
String tableName = match.Groups[1].Value;
String tableAlias = match.Groups[2].Value;
sql = sql.Substring(match.Groups[2].Index);
sql = sql.Replace(tableAlias, tableName);
sql = sql.Insert(0, "delete from ");
Int32 orderByIndex = sql.IndexOfCaseInsensitive(" order by ");
if (orderByIndex > 0)
{
sql = sql.Substring(0, orderByIndex);
}
int limitIndex = sql.IndexOfCaseInsensitive(" limit ");
if (limitIndex > 0)
{
sql = sql.Substring(0, limitIndex);
}
return(sql);
}
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"));
}
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()));
}
private static int GetAfterSelectInsertPoint(SqlString sql)
{
Int32 selectPosition;
if ((selectPosition = sql.IndexOfCaseInsensitive("select distinct")) >= 0)
{
return(selectPosition + 15); // "select distinct".Length;
}
if ((selectPosition = sql.IndexOfCaseInsensitive("select")) >= 0)
{
return(selectPosition + 6); // "select".Length;
}
throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
}
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 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)));
}
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);
}
private int GetFromIndex()
{
string subselect = _sourceQuery.GetSubselectString().ToString();
int fromIndex = _sourceQuery.IndexOfCaseInsensitive(subselect);
if (fromIndex == -1)
{
fromIndex = _sourceQuery.ToString().ToLowerInvariant().IndexOf(subselect.ToLowerInvariant());
}
return(fromIndex);
}
private static void ExtractColumnOrAliasNames(SqlString select, out List <SqlString> columnsOrAliases,
out Dictionary <SqlString, SqlString> aliasToColumn)
{
columnsOrAliases = new List <SqlString>();
aliasToColumn = new Dictionary <SqlString, SqlString>();
IList <SqlString> tokens = new QuotedAndParenthesisStringTokenizer(select).GetTokens();
int index = 0;
while (index < tokens.Count)
{
SqlString token = tokens[index];
int nextTokenIndex = index += 1;
if (token.StartsWithCaseInsensitive("select"))
{
continue;
}
if (token.StartsWithCaseInsensitive("distinct"))
{
continue;
}
if (token.StartsWithCaseInsensitive(","))
{
continue;
}
if (token.StartsWithCaseInsensitive("from"))
{
break;
}
// handle composite expressions like "2 * 4 as foo"
while ((nextTokenIndex < tokens.Count) && (tokens[nextTokenIndex].StartsWithCaseInsensitive("as") == false && tokens[nextTokenIndex].StartsWithCaseInsensitive(",") == false))
{
SqlString nextToken = tokens[nextTokenIndex];
token = token.Append(nextToken);
nextTokenIndex = index += 1;
}
// if there is no alias, the token and the alias will be the same
SqlString alias = token;
bool isFunctionCallOrQuotedString = token.IndexOfCaseInsensitive("'") >= 0 || token.IndexOfCaseInsensitive("(") >= 0;
// this is heuristic guess, if the expression contains ' or (, it is probably
// not appropriate to just slice parts off of it
if (isFunctionCallOrQuotedString == false)
{
// its a simple column reference, so lets set the alias to the
// column name minus the table qualifier if it exists
int dot = token.IndexOfCaseInsensitive(".");
if (dot != -1)
{
alias = token.Substring(dot + 1);
}
}
// notice! we are checking here the existence of "as" "alias", two
// tokens from the current one
if (nextTokenIndex + 1 < tokens.Count)
{
SqlString nextToken = tokens[nextTokenIndex];
if (nextToken.IndexOfCaseInsensitive("as") >= 0)
{
SqlString tokenAfterNext = tokens[nextTokenIndex + 1];
alias = tokenAfterNext;
index += 2; //skip the "as" and the alias
}
}
columnsOrAliases.Add(alias);
aliasToColumn[alias] = token;
}
}
private static bool HasCollectionFilterParam(SqlString sqlFragment)
{
return(sqlFragment.IndexOfCaseInsensitive("?") < 0);
}
private static bool HasDynamicFilterParam(SqlString sqlFragment)
{
return(sqlFragment.IndexOfCaseInsensitive(ParserHelper.HqlVariablePrefix) < 0);
}
/// <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();
_queryCache[sqlString] = ret;
return(ret);
}
public void IndexOfNonCompacted()
{
SqlString str = new SqlString(new object[] { "select ", " from" });
Assert.AreEqual(6, str.IndexOfCaseInsensitive(" "));
}