/// <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());
}
/// <summary>
/// Removes the <c>as someColumnAlias</c> clause from a <c>SqlString</c> representing a column expression.
/// Consider using <c>CriterionUtil.GetColumn...</c> methods instead.
/// </summary>
/// <param name="sql">The <c>SqlString</c> representing a column expression which might be aliased.</param>
/// <returns><paramref name="sql" /> if it was not aliased, otherwise an un-aliased <c>SqlString</c> representing the column.</returns>
public static SqlString RemoveAsAliasesFromSql(SqlString sql)
{
int index = sql.LastIndexOfCaseInsensitive(" as ");
if (index < 0)
{
return(sql);
}
return(sql.Substring(0, index));
}
public override SqlString GetLimitString(SqlString queryString, SqlString offset, SqlString limit)
{
SqlStringBuilder result = new SqlStringBuilder();
if (offset == null)
{
int insertPoint = GetAfterSelectInsertPoint(queryString);
return result
.Add(queryString.Substring(0, insertPoint))
.Add(" TOP (")
.Add(limit)
.Add(") ")
.Add(queryString.Substring(insertPoint))
.ToSqlString();
}
return base.GetLimitString(queryString, offset, limit);
}
/// <summary>
/// Adds condition to buffer without adding " and " prefix. Existing " and" prefix is removed
/// </summary>
protected void AddBareCondition(SqlStringBuilder buffer, SqlString condition)
{
if (SqlStringHelper.IsEmpty(condition))
{
return;
}
buffer.Add(
condition.StartsWithCaseInsensitive(" and ")
? condition.Substring(4)
: condition);
}
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);
sql2.Compact();
var parts2 = sql2.Split(",").Select(s => s.ToString()).ToArray();
var expectedParts2 = new[] { " alfa", " beta", " gamma from table" };
Assert.That(parts2, Is.EqualTo(expectedParts2));
}
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());
}
/// <summary>
/// Sets the SqlString for the OUTER JOINs.
/// </summary>
/// <remarks>
/// All of the Sql needs to be included in the SELECT. No OUTER JOINS will automatically be
/// added.
/// </remarks>
/// <param name="outerJoinsAfterFrom">The outerJoinsAfterFrom to set</param>
/// <param name="outerJoinsAfterWhere">The outerJoinsAfterWhere to set</param>
/// <returns>The SqlSelectBuilder</returns>
public SqlSelectBuilder SetOuterJoins(SqlString outerJoinsAfterFrom, SqlString outerJoinsAfterWhere)
{
this.outerJoinsAfterFrom = outerJoinsAfterFrom;
SqlString tmpOuterJoinsAfterWhere = outerJoinsAfterWhere.Trim();
if (tmpOuterJoinsAfterWhere.StartsWithCaseInsensitive("and"))
{
tmpOuterJoinsAfterWhere = tmpOuterJoinsAfterWhere.Substring(4);
}
this.outerJoinsAfterWhere = tmpOuterJoinsAfterWhere;
return(this);
}
/// <summary>
///
/// </summary>
/// <param name="fragment"></param>
public void AddSelectFragmentString(SqlString fragment)
{
if (fragment.SqlParts.Length > 0 && fragment.StartsWith(","))
{
fragment = fragment.Substring(1);
}
fragment = fragment.Trim();
if (fragment.SqlParts.Length > 0)
{
if (selectBuilder.Count > 0)
{
selectBuilder.Add(StringHelper.CommaSpace);
}
selectBuilder.Add(fragment);
}
}
/// <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 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 Substring2Complex()
{
SqlString str =
new SqlString(new object[] { "select ", Parameter.Placeholder, " from table where x = ", Parameter.Placeholder });
SqlString substr7_10 = str.Substring(7, 10);
Assert.AreEqual(new SqlString(new object[] { Parameter.Placeholder, " from tab" }), substr7_10);
SqlString substr10_200 = str.Substring(10, 200);
Assert.AreEqual(new SqlString(new object[] { "rom table where x = ", Parameter.Placeholder }), substr10_200);
SqlString substr200_10 = str.Substring(200, 10);
Assert.AreEqual(SqlString.Empty, substr200_10);
}
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;
}
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(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();
}
/// <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="offset">Offset of the first row to be returned by the query (zero-based)</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param>
/// <param name="limitParameterIndex">Optionally, the Limit parameter index</param>
/// <returns>A new <see cref="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();
}
public static SqlString RemoveAsAliasesFromSql(SqlString sql)
{
return sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as "));
}
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.Parts.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();
}
/// <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 * FROM (
/// SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ {sort direction 1} [, __hibernate_sort_expr_2__ {sort direction 2}, ...]) as row, query.* FROM (
/// {original select query part}, {sort field 1} as __hibernate_sort_expr_1__ [, {sort field 2} as __hibernate_sort_expr_2__, ...]
/// {remainder of original query minus the order by clause}
/// ) query
/// ) page WHERE page.row > offset
///
/// </code>
/// </remarks>
public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
{
int parenthesis = 0;
int fromIndex = 0;
string querystring = querySqlString.ToString();
string currentchar;
int querystringlength = querystring.Length;
for (int i = 0; i < querystringlength; i++)
{
currentchar = querystring.Substring(i, 1);
//if ( ((i + 6) <= querystring.Length) && (querystring.Substring(i, 6).ToLower() == " from ") && (parenthesis == 0))
if (((i + 6) <= querystringlength) && (querystring.Substring(i, 6).IndexOf(" from ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
{
fromIndex = i;
break;
}
if (currentchar == "(")
parenthesis++;
if (currentchar == ")")
parenthesis--;
}
SqlString select = querySqlString.Substring(0, fromIndex);
int orderIndex = 0;
querystringlength = querystring.Length;
for (int i = querystringlength - 1; i >= 0; i--)
{
currentchar = querystring.Substring(i, 1);
if (((i + 10) <= querystringlength) && (querystring.Substring(i, 10).IndexOf(" order by ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
{
orderIndex = i;
break;
}
if (currentchar == "(")
parenthesis++;
if (currentchar == ")")
parenthesis--;
}
SqlString from;
string[] sortExpressions;
if (orderIndex > 0)
{
from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
string orderBy = querySqlString.Substring(orderIndex).ToString().Trim();
//sortExpressions = orderBy.Substring(9).Split(',');
sortExpressions = getSortExpression(orderBy.Substring(9));
}
else
{
from = querySqlString.Substring(fromIndex).Trim();
// Use dummy sort to avoid errors
sortExpressions = new string[] { "CURRENT_TIMESTAMP" };
}
SqlStringBuilder result = new SqlStringBuilder()
.Add("SELECT TOP ")
.Add(last.ToString())
.Add(" * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ");
for (int i = 1; i <= sortExpressions.Length; i++)
{
if (i > 1)
result.Add(", ");
result.Add("__hibernate_sort_expr_")
.Add(i.ToString())
.Add("__");
if (sortExpressions[i - 1].Trim().EndsWith("desc", StringComparison.InvariantCultureIgnoreCase))
result.Add(" DESC");
}
result.Add(") as row, query.* FROM (")
.Add(select);
for (int i = 1; i <= sortExpressions.Length; i++)
{
string sortExpression = sortExpressions[i - 1].Trim();
if (sortExpression.EndsWith("desc", StringComparison.InvariantCultureIgnoreCase)) {
sortExpression = sortExpression.Remove(sortExpression.Length - 4);
}
result.Add(", ")
.Add(sortExpression)
.Add(" as __hibernate_sort_expr_")
.Add(i.ToString())
.Add("__");
}
result.Add(" ")
.Add(from)
.Add(") query ) page WHERE page.row > ")
.Add(offset.ToString());
bool ordering = false;
for (int i = 1; i <= sortExpressions.Length; i++)
{
if (ordering == false)
{
result.Add(" order by ");
ordering = true;
}
if (i > 1)
result.Add(", ");
result.Add("__hibernate_sort_expr_")
.Add(i.ToString())
.Add("__");
if (sortExpressions[i - 1].Trim().EndsWith("desc", StringComparison.InvariantCultureIgnoreCase))
result.Add(" DESC");
}
return result.ToSqlString();
}
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
}
}
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>
/// 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 static SqlString RemoveAsAliasesFromSql(SqlString sql)
{
int index = sql.LastIndexOfCaseInsensitive(" as ");
if (index < 0) return sql;
return sql.Substring(0, index);
}
/// <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();
}
/// <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.</param>
/// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
/// <param name="limit">Maximum number of rows to be returned by the query</param>
/// <param name="offsetParameterIndex">Optionally, the Offset parameter index</param>
/// <param name="limitParameterIndex">Optionally, the Limit parameter index</param>
/// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
/// <remarks>
/// Note that we need to 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 limit, int? offsetParameterIndex, int? limitParameterIndex)
{
SqlStringBuilder result = new SqlStringBuilder();
if (offset == 0)
{
int insertPoint = this.GetAfterSelectInsertPoint(querySqlString);
return result
.Add(querySqlString.Substring(0, insertPoint))
.Add(" TOP (")
.Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
.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 TOP (")
.Add(limitParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(limitParameterIndex.Value))
.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(fromAndWhere)
.Add(") as query WHERE query.__hibernate_sort_row > ")
.Add(offsetParameterIndex == null ? Parameter.Placeholder : Parameter.WithIndex(offsetParameterIndex.Value))
.Add(" ORDER BY query.__hibernate_sort_row");
return result.ToSqlString();
}
/// <summary></summary>
public SqlString ToQuerySqlString()
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add("select ");
if (distinct)
{
builder.Add("distinct ");
}
SqlString from = joins.ToFromFragmentString;
if (from.StartsWith(","))
{
from = from.Substring(1);
}
else if (from.StartsWith(" inner join"))
{
from = from.Substring(11);
}
builder.Add(selectBuilder.ToSqlString())
.Add(" from")
.Add(from);
SqlString part1 = joins.ToWhereFragmentString.Trim();
SqlString part2 = whereBuilder.ToSqlString().Trim();
bool hasPart1 = part1.SqlParts.Length > 0;
bool hasPart2 = part2.SqlParts.Length > 0;
if (hasPart1 || hasPart2)
{
builder.Add(" where ");
}
if (hasPart1)
{
builder.Add(part1.Substring(4));
}
if (hasPart2)
{
if (hasPart1)
{
builder.Add(" and (");
}
builder.Add(part2);
if (hasPart1)
{
builder.Add(")");
}
}
if (groupBy.Length > 0)
{
builder.Add(" group by ").Add(groupBy.ToString());
}
if (having.Length > 0)
{
builder.Add(" having ").Add(having.ToString());
}
if (orderBy.Length > 0)
{
builder.Add(" order by ").Add(orderBy.ToString());
}
return(builder.ToSqlString());
}
public static SqlString RemoveAsAliasesFromSql(SqlString sql)
{
return(sql.Substring(0, sql.LastIndexOfCaseInsensitive(" as ")));
}
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
{
/*
* "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 != null && limit != null)
{
pagingBuilder
.Add("between ")
.Add(offset)
.Add("+1 and ")
.Add(limit);
}
else if (limit != null)
{
pagingBuilder
.Add("<= ")
.Add(limit);
}
else
{
// We just have an offset.
pagingBuilder
.Add("> ")
.Add(offset);
}
return pagingBuilder.ToSqlString();
}
/// <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>
///
/// </summary>
/// <param name="fragment"></param>
public void AddSelectFragmentString(SqlString fragment)
{
if (fragment.StartsWithCaseInsensitive(","))
{
fragment = fragment.Substring(1);
}
fragment = fragment.Trim();
if (fragment.Length > 0)
{
if (selectBuilder.Count > 0)
{
selectBuilder.Add(StringHelper.CommaSpace);
}
selectBuilder.Add(fragment);
}
}
/// <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;
}