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