/// <summary> /// /// </summary> /// <param name="condition"></param> public override void AddCondition(SqlString condition) { //TODO: this seems hackish if ( afterFrom.ToSqlString().ToString().IndexOf(condition.Trim().ToString()) < 0 && afterWhere.ToSqlString().ToString().IndexOf(condition.Trim().ToString()) < 0) { if (!condition.StartsWith(" and ")) { afterWhere.Add(" and "); } afterWhere.Add(condition); } }
public override bool AddCondition(SqlString condition) { //TODO: this seems hackish if ( afterFrom.ToString().IndexOf(condition.Trim().ToString()) < 0 && afterWhere.ToString().IndexOf(condition.Trim().ToString()) < 0) { if (!condition.StartsWithCaseInsensitive(" and ")) { afterWhere.Add(" and "); } afterWhere.Add(condition); return true; } return false; }
public JoinSequence AddCondition(SqlString condition) { if (condition.Trim().Length != 0) { if (!condition.StartsWithCaseInsensitive(" and ")) conditions.Add(" and "); conditions.Add(condition); } return this; }
/// <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); } }
public override bool AddCondition(SqlString condition) { //TODO: this seems hackish var trimCondition = condition.Trim().ToString(); if (trimCondition.Length > 0 && afterFrom.ToString().IndexOf(trimCondition, StringComparison.Ordinal) < 0 && afterWhere.ToString().IndexOf(trimCondition, StringComparison.Ordinal) < 0) { if (!condition.StartsWithCaseInsensitive(" and ")) { afterWhere.Add(" and "); } afterWhere.Add(condition); return(true); } return(false); }
public void TrimBeginStringEndParam() { Parameter p1 = Parameter.Placeholder; SqlString sql = new SqlString(new object[] { " extra space ", p1 }); sql = sql.Trim(); Assert.AreEqual("extra space ?", sql.ToString()); }
public void TrimBeginParamEndString() { Parameter p1 = Parameter.Placeholder; SqlString sql = new SqlString(new object[] { p1, " extra space " }); sql = sql.Trim(); Assert.AreEqual("? extra space", sql.ToString()); }
public void TrimAllString() { SqlString sql = new SqlString(new string[] { " extra space", " in the middle", " at the end " }); sql = sql.Trim(); Assert.AreEqual("extra space in the middle at the end", sql.ToString()); }
/// <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> /// Obtain an <c>IDbCommand</c> with all parameters pre-bound. Bind positional parameters, /// named parameters, and limit parameters. /// </summary> /// <remarks> /// Creates an IDbCommand object and populates it with the values necessary to execute it against the /// database to Load an Entity. /// </remarks> /// <param name="sqlString">The SqlString to convert into a prepared IDbCommand.</param> /// <param name="parameters">The <see cref="QueryParameters"/> to use for the IDbCommand.</param> /// <param name="scroll">TODO: find out where this is used...</param> /// <param name="session">The SessionImpl this Command is being prepared in.</param> /// <returns>An IDbCommand that is ready to be executed.</returns> protected virtual IDbCommand PrepareQueryCommand( SqlString sqlString, QueryParameters parameters, bool scroll, ISessionImplementor session ) { Dialect.Dialect dialect = session.Factory.Dialect; RowSelection selection = parameters.RowSelection; bool useLimit = UseLimit( selection, dialect ); bool hasFirstRow = GetFirstRow( selection ) > 0; bool useOffset = hasFirstRow && useLimit && dialect.SupportsLimitOffset; //TODO: In .Net all resultsets are scrollable (we receive an IDataReader), so this is not needed bool scrollable = session.Factory.IsScrollableResultSetsEnabled && ( scroll || ( hasFirstRow && !useOffset ) ); if( useLimit ) { sqlString = dialect.GetLimitString( sqlString.Trim(), useOffset ? GetFirstRow( selection ) : 0, GetMaxOrLimit( dialect, selection ) ); } IDbCommand command = session.Batcher.PrepareQueryCommand( sqlString, scrollable ); try { // Added in NH - not in H2.1 if( selection != null && selection.Timeout != RowSelection.NoValue ) { command.CommandTimeout = selection.Timeout; } int colIndex = 0; if( useLimit && dialect.BindLimitParametersFirst ) { colIndex += BindLimitParameters( command, colIndex, selection, session ); } colIndex += BindPositionalParameters( command, parameters, colIndex, session ); colIndex += BindNamedParameters( command, parameters.NamedParameters, colIndex, session ); if( useLimit && !dialect.BindLimitParametersFirst ) { colIndex += BindLimitParameters( command, colIndex, selection, session ); } if( !useLimit ) { SetMaxRows( command, selection ); } if( selection != null ) { if( selection.Timeout != RowSelection.NoValue ) { command.CommandTimeout = selection.Timeout; } // H2.1 handles FetchSize here - not ported } } catch( HibernateException ) { session.Batcher.CloseQueryCommand( command, null ); throw; } catch( Exception sqle ) { ADOExceptionReporter.LogExceptions( sqle ); session.Batcher.CloseQueryCommand( command, null ); throw; } return command; }
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(); }
public void TrimAllParam() { Parameter p1 = new Parameter( "p1" ); Parameter p2 = new Parameter( "p2" ); SqlString sql = new SqlString( new object[] { p1, p2 } ); sql = sql.Trim(); Assert.AreEqual( ":p1:p2", sql.ToString() ); }
public void TrimBeginStringEndParam() { Parameter p1 = new Parameter( "p1" ); SqlString sql = new SqlString( new object[] { " extra space ", p1 } ); sql = sql.Trim(); Assert.AreEqual( "extra space :p1", sql.ToString() ); }
public void TrimBeginParamEndString() { Parameter p1 = new Parameter( "p1" ); SqlString sql = new SqlString( new object[] {p1, " extra space "} ); sql = sql.Trim(); Assert.AreEqual( ":p1 extra space", sql.ToString() ); }
public void TrimAllParam() { Parameter p1 = Parameter.Placeholder; Parameter p2 = Parameter.Placeholder; SqlString sql = new SqlString(new object[] { p1, p2 }); sql = sql.Trim(); Assert.AreEqual("??", sql.ToString()); }
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(); }
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(); }
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); } }
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; }
/// <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> /// /// </summary> /// <param name="condition"></param> public override void AddCondition( SqlString condition ) { //TODO: this seems hackish if( afterFrom.ToSqlString().ToString().IndexOf( condition.Trim().ToString() ) < 0 && afterWhere.ToSqlString().ToString().IndexOf( condition.Trim().ToString() ) < 0 ) { if( !condition.StartsWith( " and " ) ) { afterWhere.Add( " and " ); } afterWhere.Add( condition ); } }