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);
}
private static SqlString RemoveSortOrderDirection(SqlString sortExpression)
{
SqlString trimmedExpression = sortExpression.Trim();
if (trimmedExpression.EndsWithCaseInsensitive("asc"))
{
return(trimmedExpression.Substring(0, trimmedExpression.Length - 3).Trim());
}
if (trimmedExpression.EndsWithCaseInsensitive("desc"))
{
return(trimmedExpression.Substring(0, trimmedExpression.Length - 4).Trim());
}
return(trimmedExpression.Trim());
}
private static SqlString GetProjectionArgument(ICriteriaQuery criteriaQuery, ICriteria criteria,
IProjection projection, int loc)
{
SqlString sql = projection.ToSqlString(criteria, loc, criteriaQuery);
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
/// <summary>
/// Return customized limit string (for paging queries)
/// For performance reason the limit string is ommitted when querying the first page.
/// </summary>
/// <param name="querySqlString"></param>
/// <param name="offset"></param>
/// <param name="last"></param>
/// <returns></returns>
public override NHibernate.SqlCommand.SqlString GetLimitString(NHibernate.SqlCommand.SqlString querySqlString, int offset, int last)
{
if (!querySqlString.StartsWithCaseInsensitive("select "))
{
throw new ArgumentException("querySqlString should start with select", "querySqlString");
}
SqlString sqlString = querySqlString.Substring(6);
string orderSql = querySqlString.SubstringStartingWithLast("order by").ToString();
if (orderSql.Length != 0)
{
sqlString = sqlString.Substring(0, (sqlString.Length - orderSql.Length) - 1);
}
SqlStringBuilder builder = new SqlStringBuilder();
int num = offset + 1;
builder.Add("SELECT TOP ").Add(last.ToString()).Add(" ").Add(sqlString);
if (offset > 0)
{
builder.Add(" WITH FIRSTROW ").Add(num.ToString());
}
if (orderSql.Length > 0)
{
builder.Add(" ").Add(orderSql);
}
return(builder.ToSqlString());
}
public void SubstringComplex()
{
SqlString str =
new SqlString(new object[] { "select ", Parameter.Placeholder, " from table where x = ", Parameter.Placeholder });
SqlString substr7 = str.Substring(7);
Assert.AreEqual(
new SqlString(new object[] { Parameter.Placeholder, " from table where x = ", Parameter.Placeholder }), substr7);
SqlString substr10 = str.Substring(10);
Assert.AreEqual(new SqlString(new object[] { "rom table where x = ", Parameter.Placeholder }), substr10);
Assert.AreEqual(SqlString.Empty, str.Substring(200));
}
/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="hasOffset">Offset of the first row is not zero</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit, int?offsetParameterIndex, int?limitParameterIndex)
{
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
string rownumClause = GetRowNumber(querySqlString);
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder
.Add("select * from (select ")
.Add(rownumClause)
.Add(querySqlString.Substring(7))
.Add(") as tempresult where rownum ");
if (offset > 0)
{
pagingBuilder
.Add("between ")
.Add(Parameter.WithIndex(offsetParameterIndex.Value))
.Add("+1 and ")
.Add(Parameter.WithIndex(limitParameterIndex.Value));
}
else
{
pagingBuilder
.Add("<= ")
.Add(Parameter.WithIndex(limitParameterIndex.Value));
}
return(pagingBuilder.ToSqlString());
}
/// <summary>
/// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
/// for LIMIT/OFFSET support.
///
/// Produce a parametertized SQL query using positional parameters for
/// TOP and START AT (if specified).
/// </summary>
public override SqlString GetLimitString(SqlString sql, bool hasOffset)
{
int InsertionPoint = GetAfterSelectInsertPoint(sql);
if (InsertionPoint > 0)
{
SqlStringBuilder LimitBuilder = new SqlStringBuilder();
LimitBuilder.Add("SELECT");
if (InsertionPoint > 6)
{
LimitBuilder.Add(" DISTINCT ");
}
LimitBuilder.Add(" TOP ");
LimitBuilder.Add(Parameter.Placeholder);
if (hasOffset)
{
LimitBuilder.Add(" START AT ");
LimitBuilder.Add(Parameter.Placeholder);
}
LimitBuilder.Add(sql.Substring(InsertionPoint));
return(LimitBuilder.ToSqlString());
}
else
{
return(sql); // unchanged
}
}
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{
// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
// for LIMIT/OFFSET support. Does not support a limit of zero.
// FIXME - Add support for where offset is set, but limit is not.
int insertionPoint = GetAfterSelectInsertPoint(sql);
if (insertionPoint > 0)
{
SqlStringBuilder limitBuilder = new SqlStringBuilder();
limitBuilder.Add("select");
if (insertionPoint > 6)
{
limitBuilder.Add(" distinct ");
}
limitBuilder.Add(" top ");
limitBuilder.Add(limit);
if (offset != null)
{
limitBuilder.Add(" start at ");
limitBuilder.Add(offset);
}
limitBuilder.Add(sql.Substring(insertionPoint));
return(limitBuilder.ToSqlString());
}
else
{
return(sql); // unchanged
}
}
/// <summary>
/// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
/// for LIMIT/OFFSET support.
///
/// Produce a parametertized SQL query using positional parameters for
/// TOP and START AT (if specified).
/// </summary>
public override SqlString GetLimitString(SqlString sql, bool hasOffset)
{
int insertionPoint = GetAfterSelectInsertPoint(sql);
if (insertionPoint > 0)
{
SqlStringBuilder limitBuilder = new SqlStringBuilder();
limitBuilder.Add("select");
if (insertionPoint > 6)
{
limitBuilder.Add(" distinct ");
}
limitBuilder.Add(" top ");
limitBuilder.Add(Parameter.Placeholder);
if (hasOffset)
{
limitBuilder.Add(" start at ");
limitBuilder.Add(Parameter.Placeholder);
}
limitBuilder.Add(sql.Substring(insertionPoint));
return(limitBuilder.ToSqlString());
}
else
{
return(sql); // unchanged
}
}
/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">A Query in the form of a SqlString.</param>
/// <param name="hasOffset">Offset of the first row is not zero</param>
/// <returns>A new SqlString that contains the <c>LIMIT</c> clause.</returns>
public override SqlString GetLimitString(SqlString querySqlString, bool hasOffset)
{
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
string rownumClause = GetRowNumber(querySqlString);
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder
.Add("select * from (select ")
.Add(rownumClause)
.Add(querySqlString.Substring(7))
.Add(") as tempresult where rownum ");
if (hasOffset)
{
pagingBuilder
.Add("between ")
.Add(Parameter.Placeholder)
.Add("+1 and ")
.Add(Parameter.Placeholder);
}
else
{
pagingBuilder
.Add("<= ")
.Add(Parameter.Placeholder);
}
return(pagingBuilder.ToSqlString());
}
/**
* 01.06.2020: Parameter enabledFilters removed
*/
private static SqlString GetProjectionArgument(ICriteriaQuery criteriaQuery, ICriteria criteria,
IProjection projection, int loc /*,
* IDictionary<string, IFilter> enabledFilters*/)
{
/**
* 01.06.2020: Parameter enabledFilters removed
*/
SqlString sql = projection.ToSqlString(criteria, loc, criteriaQuery /*, enabledFilters*/);
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
var result = new SqlStringBuilder();
if (offset == null)
{
var insertPoint = GetAfterSelectInsertPoint(queryString);
result
.Add(queryString.Substring(0, insertPoint))
.Add(" TOP (")
.Add(limit)
.Add(") ")
.Add(queryString.Substring(insertPoint));
return(result.ToSqlString());
}
return(base.GetLimitString(queryString, offset, limit));
}
private static SqlString ProcessFromFragment(SqlString frag, JoinSequence join)
{
SqlString fromFragment = frag.Trim();
// The FROM fragment will probably begin with ', '. Remove this if it is present.
if (fromFragment.StartsWithCaseInsensitive(", "))
{
fromFragment = fromFragment.Substring(2);
}
return(fromFragment);
}
public void HashcodeEqualForEqualStringsWithDifferentHistory()
{
// Verify that sql strings that are generated in different ways, but _now_ have
// equal content, also have equal hashcodes.
SqlString sql = new SqlString(new string[] { "select", " from table" });
sql = sql.Substring(6);
SqlString sql2 = new SqlString(new string[] { " from table" });
Assert.That(sql, Is.EqualTo(sql2));
Assert.That(sql.GetHashCode(), Is.EqualTo(sql2.GetHashCode()));
}
public void Split()
{
SqlString sql = new SqlString(new string[] { "select", " alfa, beta, gamma", " from table" });
var parts1 = sql.Split(",").Select(s => s.ToString()).ToArray();
var expectedParts1 = new[] { "select alfa", " beta", " gamma from table" };
Assert.That(parts1, Is.EqualTo(expectedParts1));
SqlString sql2 = sql.Substring(6);
var parts2 = sql2.Split(",").Select(s => s.ToString()).ToArray();
var expectedParts2 = new[] { " alfa", " beta", " gamma from table" };
Assert.That(parts2, Is.EqualTo(expectedParts2));
}
public void Substring()
{
SqlStringBuilder builder = new SqlStringBuilder();
Parameter p = Parameter.Placeholder;
builder.Add(" select from table");
builder.Add(" where p = ");
builder.Add(p);
SqlString sql = builder.ToSqlString();
sql = sql.Substring(1);
Assert.AreEqual("select from table where p = ?", sql.ToString());
}
private SqlString PageByLimitOnly(SqlString limit)
{
var result = new SqlStringBuilder();
int insertPoint = GetAfterSelectInsertPoint();
return(result
.Add(_sourceQuery.Substring(0, insertPoint))
.Add(" TOP (")
.Add(limit)
.Add(") ")
.Add(_sourceQuery.Substring(insertPoint))
.ToSqlString());
}
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{
sql = sql.Trim();
bool isForUpdate = false;
if (sql.EndsWithCaseInsensitive(" for update"))
{
sql = sql.Substring(0, sql.Length - 11);
isForUpdate = true;
}
string selectColumns = ExtractColumnOrAliasNames(sql);
var pagingSelect = new SqlStringBuilder(sql.Count + 10);
if (offset != null)
{
pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( ");
}
else
{
pagingSelect.Add("select " + selectColumns + " from ( ");
}
pagingSelect.Add(sql);
if (offset != null && limit != null)
{
pagingSelect.Add(" ) row_ where rownum <=").Add(limit).Add(") where rownum_ >").Add(offset);
}
else if (limit != null)
{
pagingSelect.Add(" ) where rownum <=").Add(limit);
}
else
{
// offset is specified, but limit is not.
pagingSelect.Add(" ) row_ ) where rownum_ >").Add(offset);
}
if (isForUpdate)
{
pagingSelect.Add(" for update");
}
return(pagingSelect.ToSqlString());
}
private static SqlString GetWhereJoinFragment(IJoinable persister, string tableAlias)
{
SqlString whereJoinFragment = persister.WhereJoinFragment(tableAlias, true, false);
if (whereJoinFragment == null)
{
whereJoinFragment = SqlString.Empty;
}
else
{
whereJoinFragment = whereJoinFragment.Trim();
if (whereJoinFragment.StartsWithCaseInsensitive("and "))
{
whereJoinFragment = whereJoinFragment.Substring(4);
}
}
return(whereJoinFragment);
}
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{
if (offset == null)
{
return(new SqlString(sql, " fetch first ", limit, " rows only"));
}
ExtractColumnOrAliasNames(sql, out var selectColumns, out _, out _);
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
string rownumClause = GetRowNumber(sql);
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder
.Add("select ")
.Add(string.Join(",", selectColumns))
.Add(" from (select ")
.Add(rownumClause)
.Add(sql.Substring(7))
.Add(") as tempresult where rownum ");
if (limit != null)
{
pagingBuilder
.Add("between ")
.Add(offset)
.Add("+1 and ")
.Add(limit);
}
else
{
// We just have an offset.
pagingBuilder
.Add("> ")
.Add(offset);
}
return(pagingBuilder.ToSqlString());
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
if (offset == null)
{
return(new SqlString(querySqlString,
" fetch first ",
limit,
" rows only"));
}
/*
* "select * from (select row_number() over(orderby_clause) as rownum, "
* querySqlString_without select
* " ) as tempresult where rownum between ? and ?"
*/
string rownumClause = GetRowNumber(querySqlString);
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
pagingBuilder
.Add("select * from (select ")
.Add(rownumClause)
.Add(querySqlString.Substring(7))
.Add(") as tempresult where rownum ");
if (limit != null)
{
pagingBuilder
.Add("between ")
.Add(offset)
.Add("+1 and ")
.Add(limit);
}
else
{
// We just have an offset.
pagingBuilder
.Add("> ")
.Add(offset);
}
return(pagingBuilder.ToSqlString());
}
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{
// SQL Anywhere uses SELECT TOP n START AT m [ select list items ]
// for LIMIT/OFFSET support. Does not support a limit of zero.
var insertionPoint = GetAfterSelectInsertPoint(sql);
if (insertionPoint > 0)
{
if (limit == null && offset == null)
{
throw new ArgumentException("Cannot limit with neither a limit nor an offset");
}
var limitBuilder = new SqlStringBuilder();
limitBuilder.Add("select");
if (insertionPoint > 6)
{
limitBuilder.Add(" distinct ");
}
limitBuilder.Add(" top ");
if (limit != null)
{
limitBuilder.Add(limit);
}
else
{
// This seems supported since SQL Anywhere 12.0.1 only. No reference found for previous version,
// included 12.0.0.
limitBuilder.Add("all ");
}
if (offset != null)
{
limitBuilder.Add(" start at ");
limitBuilder.Add(offset);
}
limitBuilder.Add(sql.Substring(insertionPoint));
return(limitBuilder.ToSqlString());
}
return(sql); // unchanged
}
public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{
// SQL Anywhere 11 uses SELECT TOP n START AT m [ select list items ]
// for LIMIT/OFFSET support. Does not support a limit of zero.
// FIXME - Add support for where offset is set, but limit is not.
int insertionPoint = GetAfterSelectInsertPoint(sql);
if (insertionPoint > 0)
{
if (limit == null && offset == null)
{
throw new ArgumentException("Cannot limit with neither a limit nor an offset");
}
if (limit == null)
{
throw new NotSupportedException($"Dialect {this} does not support setting an offset without a limit");
}
SqlStringBuilder limitBuilder = new SqlStringBuilder();
limitBuilder.Add("select");
if (insertionPoint > 6)
{
limitBuilder.Add(" distinct ");
}
limitBuilder.Add(" top ");
limitBuilder.Add(limit);
if (offset != null)
{
limitBuilder.Add(" start at ");
limitBuilder.Add(offset);
}
limitBuilder.Add(sql.Substring(insertionPoint));
return(limitBuilder.ToSqlString());
}
else
{
return(sql); // unchanged
}
}
public override SqlString GetLimitString(SqlString sql, int offset, int limit, int?offsetParameterIndex, int?limitParameterIndex)
{
sql = sql.Trim();
bool hasOffset = offset > 0;
bool isForUpdate = false;
if (sql.EndsWithCaseInsensitive(" for update"))
{
sql = sql.Substring(0, sql.Length - 11);
isForUpdate = true;
}
string selectColumns = ExtractColumnOrAliasNames(sql);
var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10);
if (hasOffset)
{
pagingSelect.Add("select " + selectColumns + " from ( select row_.*, rownum rownum_ from ( ");
}
else
{
pagingSelect.Add("select " + selectColumns + " from ( ");
}
pagingSelect.Add(sql);
if (hasOffset)
{
pagingSelect.Add(" ) row_ where rownum <=").AddParameter(limitParameterIndex.Value).Add(") where rownum_ >").AddParameter(offsetParameterIndex.Value);
}
else
{
pagingSelect.Add(" ) where rownum <=").AddParameter(limitParameterIndex.Value);
}
if (isForUpdate)
{
pagingSelect.Add(" for update");
}
return(pagingSelect.ToSqlString());
}
public override SqlString GetLimitString(SqlString sql, bool hasOffset)
{
sql = sql.Trim();
bool isForUpdate = false;
if (sql.EndsWithCaseInsensitive(" for update"))
{
sql = sql.Substring(0, sql.Length - 11);
isForUpdate = true;
}
var pagingSelect = new SqlStringBuilder(sql.Parts.Count + 10);
if (hasOffset)
{
pagingSelect.Add("select * from ( select row_.*, rownum rownum_ from ( ");
}
else
{
pagingSelect.Add("select * from ( ");
}
pagingSelect.Add(sql);
if (hasOffset)
{
pagingSelect.Add(" ) row_ where rownum <=").AddParameter().Add(") where rownum_ >").AddParameter();
}
else
{
pagingSelect.Add(" ) where rownum <=").AddParameter();
}
if (isForUpdate)
{
pagingSelect.Add(" for update");
}
return(pagingSelect.ToSqlString());
}
private SqlString GetLimitString(SqlString querySqlString, object offset, object limit)
{
if (offset == null && limit == null)
{
return(querySqlString);
}
SqlStringBuilder result = new SqlStringBuilder();
if (offset == null)
{
int insertPoint = this.GetAfterSelectInsertPoint(querySqlString);
return(result
.Add(querySqlString.Substring(0, insertPoint))
.Add(" TOP (")
.AddObject(limit)
.Add(") ")
.Add(querySqlString.Substring(insertPoint))
.ToSqlString());
}
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
List <SqlString> columnsOrAliases;
Dictionary <SqlString, SqlString> aliasToColumn;
ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
SqlString fromAndWhere;
SqlString[] sortExpressions;
//don't use the order index if it is contained within a larger statement(assuming
//a statement with non matching parenthesis is part of a larger block)
if (orderIndex > 0 && HasMatchingParens(querySqlString.Substring(orderIndex).ToString()))
{
fromAndWhere = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
sortExpressions = orderBy.Substring(9).Split(",");
}
else
{
fromAndWhere = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP"), };
}
result.Add("SELECT ");
if (limit != null)
{
result.Add("TOP (").AddObject(limit).Add(") ");
}
result
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result
.Add(") as __hibernate_sort_row ")
.Add(fromAndWhere)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.AddObject(offset)
.Add(" ORDER BY query.__hibernate_sort_row");
return(result.ToSqlString());
}
public void AddWhereFragment(
JoinFragment joinFragment,
SqlString whereFragment,
QueryNode query,
FromElement fromElement,
HqlSqlWalker hqlSqlWalker)
{
if (whereFragment == null)
{
return;
}
if (!fromElement.UseWhereFragment && !joinFragment.HasThetaJoins)
{
return;
}
whereFragment = whereFragment.Trim();
if (StringHelper.IsEmpty(whereFragment.ToString()))
{
return;
}
// Forcefully remove leading ands from where fragments; the grammar will
// handle adding them
if (whereFragment.StartsWithCaseInsensitive("and"))
{
whereFragment = whereFragment.Substring(4);
}
log.Debug("Using unprocessed WHERE-fragment [" + whereFragment + "]");
SqlFragment fragment = (SqlFragment)Create(HqlSqlWalker.SQL_TOKEN, whereFragment.ToString());
fragment.SetJoinFragment(joinFragment);
fragment.FromElement = fromElement;
if (fromElement.IndexCollectionSelectorParamSpec != null)
{
fragment.AddEmbeddedParameter(fromElement.IndexCollectionSelectorParamSpec);
fromElement.IndexCollectionSelectorParamSpec = null;
}
if (hqlSqlWalker.IsFilter())
{
//if (whereFragment.IndexOfCaseInsensitive("?") >= 0)
if (whereFragment.ToString().IndexOf("?") >= 0)
{
IType collectionFilterKeyType = hqlSqlWalker.SessionFactoryHelper
.RequireQueryableCollection(hqlSqlWalker.CollectionFilterRole)
.KeyType;
CollectionFilterKeyParameterSpecification paramSpec = new CollectionFilterKeyParameterSpecification(
hqlSqlWalker.CollectionFilterRole,
collectionFilterKeyType,
0
);
fragment.AddEmbeddedParameter(paramSpec);
}
}
JoinProcessor.ProcessDynamicFilterParameters(
whereFragment,
fragment,
hqlSqlWalker
);
log.Debug("Using processed WHERE-fragment [" + fragment.Text + "]");
// Filter conditions need to be inserted before the HQL where condition and the
// theta join node. This is because org.hibernate.loader.Loader binds the filter parameters first,
// then it binds all the HQL query parameters, see org.hibernate.loader.Loader.processFilterParameters().
if (fragment.FromElement.IsFilter || fragment.HasFilterCondition)
{
if (_filters == null)
{
// Find or create the WHERE clause
IASTNode where = (IASTNode)query.WhereClause;
// Create a new FILTERS node as a parent of all filters
_filters = Create(HqlSqlWalker.FILTERS, "{filter conditions}");
// Put the FILTERS node before the HQL condition and theta joins
where.InsertChild(0, _filters);
}
// add the current fragment to the FILTERS node
_filters.AddChild(fragment);
}
else
{
if (_thetaJoins == null)
{
// Find or create the WHERE clause
IASTNode where = (IASTNode)query.WhereClause;
// Create a new THETA_JOINS node as a parent of all filters
_thetaJoins = Create(HqlSqlWalker.THETA_JOINS, "{theta joins}");
// Put the THETA_JOINS node before the HQL condition, after the filters.
if (_filters == null)
{
where.InsertChild(0, _thetaJoins);
}
else
{
_filters.AddSibling(_thetaJoins);
}
}
// add the current fragment to the THETA_JOINS node
_thetaJoins.AddChild(fragment);
}
}
/// <summary>
/// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
/// </summary>
/// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
/// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
/// <param name="last">Maximum number of rows to be returned by the query</param>
/// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
/// <remarks>
/// The <c>LIMIT</c> SQL will look like
/// <code>
///
/// SELECT
/// TOP last (columns)
/// FROM
/// (SELECT (columns), ROW_NUMBER() OVER(ORDER BY {original order by, with un-aliased column names) as __hibernate_sort_row
/// {original from}) as query
/// WHERE query.__hibernate_sort_row > offset
/// ORDER BY query.__hibernate_sort_row
///
/// </code>
///
/// Note that we need to add explicitly specify the columns, because we need to be able to use them
/// in a paged subselect. NH-1155
/// </remarks>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
{
//dont do this paging code if there is no offset, use the
//sql 2000 dialect since it wont just uses a top statement
if (offset == 0)
{
return(base.GetLimitString(querySqlString, offset, last));
}
// we have to do this in order to support parameters in order clause, the foramt
// that sql 2005 uses for paging means that we move the parameters around, which means,
// that positions are lost, so we record them before making any changes.
// NH-1528
int parameterPositon = 0;
foreach (var part in querySqlString.Parts)
{
Parameter param = part as Parameter;
if (param == null)
{
continue;
}
param.OriginalPositionInQuery = parameterPositon;
parameterPositon += 1;
}
int fromIndex = GetFromIndex(querySqlString);
SqlString select = querySqlString.Substring(0, fromIndex);
List <SqlString> columnsOrAliases;
Dictionary <SqlString, SqlString> aliasToColumn;
ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn);
int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
SqlString from;
SqlString[] sortExpressions;
//don't use the order index if it is contained within a larger statement(assuming
//a statement with non matching parenthesis is part of a larger block)
if (orderIndex > 0 && HasMatchingParens(querySqlString.Substring(orderIndex).ToString()))
{
from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
SqlString orderBy = querySqlString.Substring(orderIndex).Trim();
sortExpressions = orderBy.Substring(9).Split(",");
}
else
{
from = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP"), };
}
SqlStringBuilder result =
new SqlStringBuilder()
.Add("SELECT TOP ")
.Add(last.ToString())
.Add(" ")
.Add(StringHelper.Join(", ", columnsOrAliases))
.Add(" FROM (")
.Add(select)
.Add(", ROW_NUMBER() OVER(ORDER BY ");
AppendSortExpressions(aliasToColumn, sortExpressions, result);
result.Add(") as __hibernate_sort_row ")
.Add(from)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.Add(offset.ToString()).Add(" ORDER BY query.__hibernate_sort_row");
return(result.ToSqlString());
}
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;
}
}
/// <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);
}