public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { var sqlBuilder = new SqlStringBuilder(); SqlString[] columnNames = null; if (_propertyName != "*") { columnNames = CriterionUtil.GetColumnNames(_propertyName, _projection, criteriaQuery, criteria, enabledFilters); if (columnNames.Length != 1) { throw new HibernateException("Contains may only be used with single-column properties"); } } else { columnNames = new SqlString[] { new SqlString("*") }; } sqlBuilder.Add("contains(") .Add(columnNames[0]) .Add(","); sqlBuilder.Add(criteriaQuery.NewQueryParameter(GetParameterTypedValue(criteria, criteriaQuery)).Single()); sqlBuilder.Add(")"); return sqlBuilder.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters) { //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); string[] columnNames = criteriaQuery.GetColumnsUsingProjection(criteria, _propertyName); for (int i = 0; i < columnNames.Length; i++) { if (i > 0) { sqlBuilder.Add(" and "); } sqlBuilder.Add(columnNames[i]) .Add(" is null"); } if (columnNames.Length > 1) { sqlBuilder.Insert(0, "("); sqlBuilder.Add(")"); } return sqlBuilder.ToSqlString(); }
/// <summary> /// /// </summary> /// <param name="factory"></param> /// <param name="persistentClass"></param> /// <param name="alias"></param> /// <returns></returns> public override SqlString ToSqlString( ISessionFactoryImplementor factory, System.Type persistentClass, string alias, IDictionary aliasClasses ) { //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); string[ ] columnNames = AbstractCriterion.GetColumns( factory, persistentClass, _propertyName, alias, aliasClasses ); for( int i = 0; i < columnNames.Length; i++ ) { if( i > 0 ) { sqlBuilder.Add( " and " ); } sqlBuilder.Add( columnNames[ i ] ) .Add( " is null" ); } if( columnNames.Length > 1 ) { sqlBuilder.Insert( 0, "(" ); sqlBuilder.Add( ")" ); } return sqlBuilder.ToSqlString(); }
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit) { var tokenEnum = new SqlTokenizer(querySqlString).GetEnumerator(); if (!tokenEnum.TryParseUntilFirstMsSqlSelectColumn()) return null; var result = new SqlStringBuilder(querySqlString); if (!tokenEnum.TryParseUntil("order")) { result.Add(" ORDER BY CURRENT_TIMESTAMP"); } result.Add(" OFFSET "); if (offset != null) { result.Add(offset).Add(" ROWS"); } else { result.Add("0 ROWS"); } if (limit != null) { result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS ONLY"); } return result.ToSqlString(); }
/// <summary> /// Converts the ColumnNames and ColumnValues to a WhereFragment /// </summary> /// <param name="tableAlias">The Alias for the Table.</param> /// <param name="columnNames">The names of the Columns to Add to the WhereFragment</param> /// <param name="op">The operator to use between the names & values. For example " = " or "!="</param> /// <returns>A SqlString that contains the WhereFragment</returns> protected SqlString ToWhereString(string tableAlias, string[] columnNames, string op) { SqlStringBuilder sqlBuilder = new SqlStringBuilder((columnNames.Length * 2) + 5); bool andNeeded = false; for (int i = 0; i < columnNames.Length; i++) { if (string.IsNullOrEmpty(columnNames[i])) continue;// prevent empty column name if (andNeeded) { sqlBuilder.Add(" AND "); } andNeeded = true; string columnName; if (tableAlias != null && tableAlias.Length > 0) { columnName = tableAlias + StringHelper.Dot + columnNames[i]; } else { columnName = columnNames[i]; } sqlBuilder .Add(columnName) .Add(op) .AddParameter(); } return sqlBuilder.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); SqlString[] columnNames = CriterionUtil.GetColumnNames(_propertyName, _projection, criteriaQuery, criteria, enabledFilters); bool opNeeded = false; for (int i = 0; i < columnNames.Length; i++) { if (opNeeded) { sqlBuilder.Add(" or "); } opNeeded = true; sqlBuilder.Add(columnNames[i]) .Add(" is not null"); } if (columnNames.Length > 1) { sqlBuilder.Insert(0, "("); sqlBuilder.Add(")"); } return sqlBuilder.ToSqlString(); }
public SqlString ToSqlString() { if (tableName == null) throw new HibernateException("no table name defined for insert-select"); if (select == null) throw new HibernateException("no select defined for insert-select"); var buf = new SqlStringBuilder(columnNames.Count + 4); if (comment != null) { buf.Add("/* " + comment + " */ "); } buf.Add("insert into ").Add(tableName); if (!(columnNames.Count == 0)) { buf.Add(" ("); bool commaNeeded= false; foreach (var columnName in columnNames) { if(commaNeeded) { buf.Add(", "); } buf.Add(columnName); commaNeeded = true; } buf.Add(")"); } buf.Add(" ").Add(select.ToStatementString()); return buf.ToSqlString(); }
public SqlString ToSubselectString(string ukname) { string[] joinColumns = ukname == null ? StringHelper.Qualify(alias, loadable.IdentifierColumnNames) : ((IPropertyMapping) loadable).ToColumns(alias, ukname); SqlString sqlString = new SqlStringBuilder() .Add("select ") .Add(StringHelper.Join(", ", joinColumns)) .Add(queryString) .ToSqlString(); RowSelection selection = queryParameters.RowSelection; bool useLimit = Loader.Loader.UseLimit(selection, dialect); bool hasFirstRow = Loader.Loader.GetFirstRow(selection) > 0; bool useOffset = hasFirstRow && useLimit && dialect.SupportsLimitOffset; if ((useLimit || hasFirstRow) == false) return sqlString; sqlString = AppendOrderByIfNeeded(sqlString); return dialect.GetLimitString(sqlString.Trim(), useOffset ? Loader.Loader.GetFirstRow(selection) : 0, Loader.Loader.GetMaxOrLimit(dialect, selection)); }
public SqlString Render(IList args, ISessionFactoryImplementor factory) { //ANSI-SQL92 definition //<general set function> ::= //<set function type> <leftparen> [ <setquantifier> ] <value expression> <right paren> //<set function type> : := AVG | MAX | MIN | SUM | COUNT //<setquantifier> ::= DISTINCT | ALL if (args.Count < 1 || args.Count > 2) { throw new QueryException(string.Format("Aggregate {0}(): Not enough parameters (attended from 1 to 2).", name)); } else if ("*".Equals(args[args.Count - 1]) && !acceptAsterisk) { throw new QueryException(string.Format("Aggregate {0}(): invalid argument '*'.", name)); } SqlStringBuilder cmd = new SqlStringBuilder(); cmd.Add(name) .Add("("); if (args.Count > 1) { object firstArg = args[0]; if (!StringHelper.EqualsCaseInsensitive("distinct", firstArg.ToString()) && !StringHelper.EqualsCaseInsensitive("all", firstArg.ToString())) { throw new QueryException(string.Format("Aggregate {0}(): token unknow {1}.", name, firstArg)); } cmd.AddObject(firstArg).Add(" "); } cmd.AddObject(args[args.Count - 1]) .Add(")"); return cmd.ToSqlString(); }
public SqlString Render(IList args, ISessionFactoryImplementor factory) { // TODO: QueryException if args.Count<2 (not present in H3.2) bool threeArgs = args.Count > 2; object pattern = args[0]; object orgString = args[1]; object start = threeArgs ? args[2] : null; SqlStringBuilder buf = new SqlStringBuilder(); buf.Add("charindex(") .AddObject(pattern) .Add(", "); if (threeArgs) { buf.Add("right("); } buf.AddObject(orgString); if (threeArgs) { buf.Add(", char_length(") .AddObject(orgString) .Add(")-(") .AddObject(start) .Add("-1))"); } buf.Add(")"); return buf.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { ISQLFunction sqlFunction = GetFunction(criteriaQuery); List<string> tokens = new List<string>(); string replacemenToken = Guid.NewGuid().ToString("n"); for (int i = 0; i < args.Length; i++) { tokens.Add(replacemenToken); } string functionStatement = sqlFunction.Render(tokens, criteriaQuery.Factory).ToString(); string[] splitted = functionStatement.Split(new string[] {replacemenToken}, StringSplitOptions.RemoveEmptyEntries); SqlStringBuilder sb = new SqlStringBuilder(); for (int i = 0; i < splitted.Length; i++) { sb.Add(splitted[i]); if (i < args.Length) { int loc = (position + 1) * 1000 + i; SqlString projectArg = GetProjectionArgument(criteriaQuery, criteria, args[i], loc, enabledFilters); sb.Add(projectArg); } } sb.Add(" as "); sb.Add(GetColumnAliases(position)[0]); return sb.ToSqlString(); }
public SqlString Render(IList args, ISessionFactoryImplementor factory) { // TODO: QueryException if args.Count<2 (not present in H3.2) bool threeArgs = args.Count > 2; object pattern = args[0]; object orgString = args[1]; object start = threeArgs ? args[2] : null; SqlStringBuilder buf = new SqlStringBuilder(); if (threeArgs) { buf.Add("(case "); RenderPositionInSubstring(buf, pattern, orgString, start); buf.Add(" when 0 then 0 else ("); RenderPositionInSubstring(buf, pattern, orgString, start); buf.Add("+(") .AddObject(start) .Add("-1)) end)"); } else { buf.Add("charindex(") .AddObject(pattern) .Add(", ") .AddObject(orgString) .Add(")"); } return buf.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); SqlString[] columnNames = CriterionUtil.GetColumnNames(propertyName, projection, criteriaQuery, criteria, enabledFilters); if (columnNames.Length != 1) { throw new HibernateException("insensitive like may only be used with single-column properties"); } if (criteriaQuery.Factory.Dialect is PostgreSQLDialect) { sqlBuilder.Add(columnNames[0]); sqlBuilder.Add(" ilike "); } else { sqlBuilder.Add(criteriaQuery.Factory.Dialect.LowercaseFunction) .Add("(") .Add(columnNames[0]) .Add(")") .Add(" like "); } sqlBuilder.AddParameter(); return sqlBuilder.ToSqlString(); }
public SqlString Render(IList args, ISessionFactoryImplementor factory) { // DONE: QueryException if args.Count<2 (not present in H3.2) if (args.Count < 2) { throw new QueryException("position(): Not enough parameters (attended from 2 to 3)."); } bool threeArgs = args.Count > 2; object pattern = args[0]; object orgString = args[1]; object start = threeArgs ? args[2] : null; SqlStringBuilder buf = new SqlStringBuilder(); if (threeArgs) { buf.Add("(case "); RenderPositionInSubstring(buf, pattern, orgString, start); buf.Add(" when 0 then 0 else ("); RenderPositionInSubstring(buf, pattern, orgString, start); buf.Add("+") .AddObject(start) .Add("-1) end)"); } else { buf.Add("position(") .AddObject(pattern) .Add(" in ") .AddObject(orgString) .Add(")"); } return buf.ToSqlString(); }
public SqlString Render(IList args, ISessionFactoryImplementor factory) { if (args.Count < 2) { throw new QueryException("coalesce() requires at least two arguments"); } var sb = new SqlStringBuilder(); sb.Add("Switch"); sb.Add("("); for (int i = 0; i < args.Count; i++) { if (i>0) { sb.Add(","); } var arg = args[i]; sb.Add("not IsNull("); sb.AddObject(arg); sb.Add(")"); sb.Add(","); sb.AddObject(arg); } sb.Add(","); sb.Add("1=1"); sb.Add(","); sb.AddObject(args[args.Count - 1]); sb.Add(")"); return sb.ToSqlString(); }
/// <summary> /// Initializes a new instance of the <see cref="SubselectClauseExtractor"/> class. /// </summary> /// <param name="sql">The <see cref="SqlString" /> to extract the subselect clause from.</param> public SubselectClauseExtractor(SqlString sql) { builder = new SqlStringBuilder(sql.Count); this.sql = sql; lastOrderByIndex = -1; lastOrderByPartIndex = -1; }
public override SqlString Render(IList args, ISessionFactoryImplementor factory) { bool hasArgs = args.Count > 0; SqlStringBuilder buf = new SqlStringBuilder(); buf.Add(name); if (hasArgs) { buf.Add("("); for (int i = 0; i < args.Count; i++) { object arg = args[i]; if (arg is Parameter || arg is SqlString) { buf.AddObject(arg); } else { buf.Add(arg.ToString()); } if (i < (args.Count - 1)) buf.Add(", "); } buf.Add(")"); } return buf.ToSqlString(); }
public override SqlString ToSqlString( ISessionFactoryImplementor factory, System.Type persistentClass, string alias, IDictionary aliasClasses ) { if( _criteria.Count == 0 ) { return new SqlString( "1=1" ); } //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); sqlBuilder.Add( "(" ); for( int i = 0; i < _criteria.Count - 1; i++ ) { sqlBuilder.Add( ( ( ICriterion ) _criteria[ i ] ).ToSqlString( factory, persistentClass, alias, aliasClasses ) ); sqlBuilder.Add( Op ); } sqlBuilder.Add( ( ( ICriterion ) _criteria[ _criteria.Count - 1 ] ).ToSqlString( factory, persistentClass, alias, aliasClasses ) ); sqlBuilder.Add( ")" ); return sqlBuilder.ToSqlString(); }
/// <summary> /// Applies the template to passed in arguments. /// </summary> /// <param name="args">args function arguments</param> /// <param name="factory">generated SQL function call</param> /// <returns></returns> public SqlString Render(IList args, ISessionFactoryImplementor factory) { SqlStringBuilder buf = new SqlStringBuilder(); foreach (TemplateChunk tc in chunks) { if (tc.ArgumentIndex != InvalidArgumentIndex) { int adjustedIndex = tc.ArgumentIndex - 1; // Arg indices are one-based object arg = adjustedIndex < args.Count ? args[adjustedIndex] : null; // TODO: if (arg == null) QueryException is better ? if (arg != null) { if (arg is Parameter || arg is SqlString) { buf.AddObject(arg); } else { buf.Add(arg.ToString()); } } } else { buf.Add(tc.Text); } } return buf.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { //Implementation changed from H3.2 to use SqlString string[] columns = criteriaQuery.GetIdentifierColumns(criteria); SqlStringBuilder result = new SqlStringBuilder(4 * columns.Length + 2); if (columns.Length > 1) { result.Add(StringHelper.OpenParen); } for (int i = 0; i < columns.Length; i++) { if (i > 0) { result.Add(" and "); } result.Add(columns[i]) .Add(" = "); AddValueOrProjection(criteria, criteriaQuery, enabledFilters, result); } if (columns.Length > 1) { result.Add(StringHelper.ClosedParen); } return result.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { if (_criteria.Count == 0) { return EmptyExpression; } //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); sqlBuilder.Add("("); for (int i = 0; i < _criteria.Count - 1; i++) { sqlBuilder.Add( ((ICriterion) _criteria[i]).ToSqlString(criteria, criteriaQuery, enabledFilters)); sqlBuilder.Add(Op); } sqlBuilder.Add( ((ICriterion) _criteria[_criteria.Count - 1]).ToSqlString(criteria, criteriaQuery, enabledFilters)); sqlBuilder.Add(")"); return sqlBuilder.ToSqlString(); }
public override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit) { var result = new SqlStringBuilder(querySqlString); if (offset != null) { result.Add(" OFFSET ").Add(offset).Add(" ROWS"); } if (limit != null) { if (offset == null) { result.Add(" OFFSET 0 ROWS"); // According to Oracle Docs: // http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html // the 'limit' param must be 1 or higher, but we have situations with limit=0. // This leads to undetermined behaviour of Oracle DBMS. It seems that the query // was executed correctly but the execution takes pretty long time. // // Empirically estimated that adding 'OFFSET 0 ROWS' to these types of queries // ensures much faster execution. Stated above is a kind of hack to fix // described situation. } result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS ONLY"); } return result.ToSqlString(); }
/// <summary> /// /// </summary> /// <param name="factory"></param> /// <param name="persistentClass"></param> /// <param name="alias"></param> /// <returns></returns> public override SqlString ToSqlString( ISessionFactoryImplementor factory, System.Type persistentClass, string alias, IDictionary aliasClasses ) { //TODO: add default capacity SqlStringBuilder sqlBuilder = new SqlStringBuilder(); IType propertyType = AbstractCriterion.GetType( factory, persistentClass, _propertyName, aliasClasses ); string[ ] columnNames = AbstractCriterion.GetColumns( factory, persistentClass, _propertyName, alias, aliasClasses ); Parameter[ ] parameters = Parameter.GenerateParameters( factory, columnNames, propertyType ); if( columnNames.Length != 1 ) { throw new HibernateException( "insensitive like may only be used with single-column properties" ); } if( factory.Dialect is PostgreSQLDialect ) { sqlBuilder.Add( columnNames[ 0 ] ); sqlBuilder.Add( " ilike " ); } else { sqlBuilder.Add( factory.Dialect.LowercaseFunction ) .Add( "(" ) .Add( columnNames[ 0 ] ) .Add( ")" ) .Add( " like " ); } sqlBuilder.Add( parameters[ 0 ] ); return sqlBuilder.ToSqlString(); }
/// <summary> /// Initializes a new instance of the <see cref="SubselectClauseExtractor"/> class. /// </summary> /// <param name="sqlParts">The parts of an <see cref="SqlString" /> to extract the subselect clause from.</param> public SubselectClauseExtractor(object[] sqlParts) { builder = new SqlStringBuilder(sqlParts.Length); this.sqlParts = sqlParts; lastOrderByIndex = -1; lastOrderByPartIndex = -1; }
protected SqlStringBuilder WhereString(string alias, string[] columnNames, IType type, SqlString subselect, int batchSize) { if (subselect == null) { return base.WhereString(alias, columnNames, type, batchSize); } else { SqlStringBuilder buf = new SqlStringBuilder(); if (columnNames.Length > 1) { buf.Add("("); } buf.Add(StringHelper.Join(", ", StringHelper.Qualify(alias, columnNames))); if (columnNames.Length > 1) { buf.Add(")"); } buf.Add(" in ") .Add("(") .Add(subselect) .Add(")"); return buf; } }
public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { SqlStringBuilder result = new SqlStringBuilder() .Add("count(*) as y") .Add(position.ToString()) .Add("_"); return result.ToSqlString(); }
/// <summary> /// Render the function call as SQL. /// </summary> /// <param name="args">List of arguments</param> /// <param name="factory"></param> /// <returns>SQL fragment for the function.</returns> public override SqlString Render(IList args, ISessionFactoryImplementor factory) { SqlStringBuilder builder = new SqlStringBuilder(); builder.AddObject(args[0]); builder.Add("."); builder.Add(name); return builder.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { ISessionFactoryImplementor factory = criteriaQuery.Factory; var innerQuery = new CriteriaQueryTranslator( factory, criteriaImpl, //implicit polymorphism not supported (would need a union) criteriaImpl.EntityOrClassName, criteriaQuery.GenerateSQLAlias(), criteriaQuery); types = innerQuery.HasProjection ? innerQuery.ProjectedTypes : null; if (innerQuery.HasProjection == false) { throw new QueryException("Cannot use subqueries on a criteria without a projection."); } IOuterJoinLoadable persister = (IOuterJoinLoadable) factory.GetEntityPersister(criteriaImpl.EntityOrClassName); //patch to generate joins on subqueries //stolen from CriteriaLoader CriteriaJoinWalker walker = new CriteriaJoinWalker(persister, innerQuery, factory, criteriaImpl, criteriaImpl.EntityOrClassName, enabledFilters); parameters = innerQuery.GetQueryParameters(); // parameters can be inferred only after initialize the walker SqlString sql = walker.SqlString; if (criteriaImpl.FirstResult != 0 || criteriaImpl.MaxResults != RowSelection.NoValue) { int? offset = Loader.Loader.GetOffsetUsingDialect(parameters.RowSelection, factory.Dialect); int? limit = Loader.Loader.GetLimitUsingDialect(parameters.RowSelection, factory.Dialect); Parameter offsetParameter = offset.HasValue ? innerQuery.CreateSkipParameter(offset.Value) : null; Parameter limitParameter = limit.HasValue ? innerQuery.CreateTakeParameter(limit.Value) : null; sql = factory.Dialect.GetLimitString(sql, offset, limit, offsetParameter, limitParameter); } SqlStringBuilder buf = new SqlStringBuilder().Add(ToLeftSqlString(criteria, criteriaQuery)); if (op != null) { buf.Add(" ").Add(op).Add(" "); } if (quantifier != null && prefixOp) { buf.Add(quantifier).Add(" "); } buf.Add("(").Add(sql).Add(")"); if (quantifier != null && prefixOp == false) { buf.Add(" ").Add(quantifier); } return buf.ToSqlString(); }
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { InitializeInnerQueryAndParameters(criteriaQuery); if (innerQuery.HasProjection == false) { throw new QueryException("Cannot use subqueries on a criteria without a projection."); } ISessionFactoryImplementor factory = criteriaQuery.Factory; IOuterJoinLoadable persister = (IOuterJoinLoadable)factory.GetEntityPersister(criteriaImpl.EntityOrClassName); //patch to generate joins on subqueries //stolen from CriteriaLoader CriteriaJoinWalker walker = new CriteriaJoinWalker(persister, innerQuery, factory, criteriaImpl, criteriaImpl.EntityOrClassName, enabledFilters); parameters = innerQuery.GetQueryParameters(); // parameters can be inferred only after initialize the walker SqlString sql = walker.SqlString; if (criteriaImpl.FirstResult != 0 || criteriaImpl.MaxResults != RowSelection.NoValue) { int? offset = Loader.Loader.GetOffsetUsingDialect(parameters.RowSelection, factory.Dialect); int? limit = Loader.Loader.GetLimitUsingDialect(parameters.RowSelection, factory.Dialect); Parameter offsetParameter = offset.HasValue ? innerQuery.CreateSkipParameter(offset.Value) : null; Parameter limitParameter = limit.HasValue ? innerQuery.CreateTakeParameter(limit.Value) : null; sql = factory.Dialect.GetLimitString(sql, offset, limit, offsetParameter, limitParameter); } // during CriteriaImpl.Clone we are doing a shallow copy of each criterion. // this is not a problem for common criterion but not for SubqueryExpression because here we are holding the state of inner CriteriaTraslator (ICriteriaQuery). // After execution (ToSqlString) we have to clean the internal state because the next execution may be performed in a different tree reusing the same istance of SubqueryExpression. innerQuery = null; SqlStringBuilder buf = new SqlStringBuilder().Add(ToLeftSqlString(criteria, criteriaQuery)); if (op != null) { buf.Add(" ").Add(op).Add(" "); } if (quantifier != null && prefixOp) { buf.Add(quantifier).Add(" "); } buf.Add("(").Add(sql).Add(")"); if (quantifier != null && prefixOp == false) { buf.Add(" ").Add(quantifier); } return buf.ToSqlString(); }
public SqlString ToSubselectString(string ukname) { string[] joinColumns = ukname == null ? StringHelper.Qualify(alias, loadable.IdentifierColumnNames) : ((IPropertyMapping) loadable).ToColumns(alias, ukname); SqlString sqlString = new SqlStringBuilder().Add("select ").Add(StringHelper.Join(", ", joinColumns)).Add(queryString).ToSqlString(); return sqlString; }
/// <summary></summary> public SqlString ToSqlString() { // 3 = "UPDATE", tableName, "SET" int initialCapacity = 3; // will have a comma for all but the first column, and then for each column // will have a name, " = ", value so multiply by 3 if (columns.Count > 0) { initialCapacity += (columns.Count - 1) + (columns.Count * 3); } // 1 = "WHERE" initialCapacity++; // the "AND" before all but the first whereString if (whereStrings.Count > 0) { initialCapacity += (whereStrings.Count - 1); initialCapacity += whereStrings.Sum(x => x.Count); } if (!string.IsNullOrEmpty(comment)) { initialCapacity++; } var sqlBuilder = new SqlStringBuilder(initialCapacity + 2); if (!string.IsNullOrEmpty(comment)) { sqlBuilder.Add("/* " + comment + " */ "); } sqlBuilder.Add("UPDATE ") .Add(tableName) .Add(" SET "); bool assignmentsAppended = false; bool commaNeeded = false; foreach (KeyValuePair <string, object> valuePair in columns) { if (commaNeeded) { sqlBuilder.Add(StringHelper.CommaSpace); } commaNeeded = true; sqlBuilder.Add(valuePair.Key) .Add(" = "); SqlType param = valuePair.Value as SqlType; if (param != null) { sqlBuilder.Add(Parameter.Placeholder); } else { sqlBuilder.Add((string)valuePair.Value); } assignmentsAppended = true; } if (assignments != null) { if (assignmentsAppended) { sqlBuilder.Add(", "); } sqlBuilder.Add(assignments); } sqlBuilder.Add(" WHERE "); bool andNeeded = false; foreach (SqlString whereString in whereStrings) { if (andNeeded) { sqlBuilder.Add(" AND "); } andNeeded = true; sqlBuilder.Add(whereString); } if (log.IsDebugEnabled) { if (initialCapacity < sqlBuilder.Count) { log.Debug( "The initial capacity was set too low at: " + initialCapacity + " for the UpdateSqlBuilder " + "that needed a capacity of: " + sqlBuilder.Count + " for the table " + tableName); } else if (initialCapacity > 16 && ((float)initialCapacity / sqlBuilder.Count) > 1.2) { log.Debug( "The initial capacity was set too high at: " + initialCapacity + " for the UpdateSqlBuilder " + "that needed a capacity of: " + sqlBuilder.Count + " for the table " + tableName); } } return(sqlBuilder.ToSqlString()); }
/// <summary></summary> public SqlString ToSqlString() { // 4 = the "SELECT", selectClause, "FROM", fromClause are straight strings // plus the number of parts in outerJoinsAfterFrom SqlString. // 1 = the "WHERE" // plus the number of parts in outerJoinsAfterWhere SqlString. // 1 = the whereClause // 2 = the "ORDER BY" and orderByClause var joinAfterFrom = outerJoinsAfterFrom != null ? outerJoinsAfterFrom.Count : 0; var joinAfterWhere = outerJoinsAfterWhere != null ? outerJoinsAfterWhere.Count : 0; int initialCapacity = 4 + joinAfterFrom + 1 + joinAfterWhere + 1 + 2; if (!string.IsNullOrEmpty(comment)) { initialCapacity++; } SqlStringBuilder sqlBuilder = new SqlStringBuilder(initialCapacity + 2); if (!string.IsNullOrEmpty(comment)) { sqlBuilder.Add("/* " + comment + " */ "); } sqlBuilder.Add("SELECT ") .Add(selectClause) .Add(" FROM ") .Add(fromClause); if (SqlStringHelper.IsNotEmpty(outerJoinsAfterFrom)) { sqlBuilder.Add(outerJoinsAfterFrom); } if (SqlStringHelper.IsNotEmpty(whereClause) || SqlStringHelper.IsNotEmpty(outerJoinsAfterWhere)) { sqlBuilder.Add(" WHERE "); // the outerJoinsAfterWhere needs to come before where clause to properly // handle dynamic filters if (SqlStringHelper.IsNotEmpty(outerJoinsAfterWhere)) { sqlBuilder.Add(outerJoinsAfterWhere); if (SqlStringHelper.IsNotEmpty(whereClause)) { sqlBuilder.Add(" AND "); } } if (SqlStringHelper.IsNotEmpty(whereClause)) { sqlBuilder.Add(whereClause); } } if (SqlStringHelper.IsNotEmpty(groupByClause)) { sqlBuilder.Add(" GROUP BY ") .Add(groupByClause); } if (SqlStringHelper.IsNotEmpty(havingClause)) { sqlBuilder.Add(" HAVING ") .Add(havingClause); } if (SqlStringHelper.IsNotEmpty(orderByClause)) { sqlBuilder.Add(" ORDER BY ") .Add(orderByClause); } if (lockMode != null) { sqlBuilder.Add(Dialect.GetForUpdateString(lockMode)); } if (log.IsDebugEnabled()) { if (initialCapacity < sqlBuilder.Count) { log.Debug("The initial capacity was set too low at: {0} for the SelectSqlBuilder that needed a capacity of: {1} for the table {2}", initialCapacity, sqlBuilder.Count, fromClause); } else if (initialCapacity > 16 && ((float)initialCapacity / sqlBuilder.Count) > 1.2) { log.Debug("The initial capacity was set too high at: {0} for the SelectSqlBuilder that needed a capacity of: {1} for the table {2}", initialCapacity, sqlBuilder.Count, fromClause); } } return(sqlBuilder.ToSqlString()); }
/// <summary></summary> public SqlString ToSqlString() { // 3 = "UPDATE", tableName, "SET" int initialCapacity = 3; // will have a comma for all but the first column, and then for each column // will have a name, " = ", value so mulitply by 3 if (columnNames.Count > 0) { initialCapacity += (columnNames.Count - 1) + (columnNames.Count * 3); } // 1 = "WHERE" initialCapacity++; // the "AND" before all but the first whereString if (whereStrings.Count > 0) { initialCapacity += (whereStrings.Count - 1); for (int i = 0; i < whereStrings.Count; i++) { initialCapacity += (( SqlString )whereStrings[i]).Count; } } SqlStringBuilder sqlBuilder = new SqlStringBuilder(initialCapacity + 2); bool commaNeeded = false; bool andNeeded = false; sqlBuilder.Add("UPDATE ") .Add(tableName) .Add(" SET "); for (int i = 0; i < columnNames.Count; i++) { if (commaNeeded) { sqlBuilder.Add(StringHelper.CommaSpace); } commaNeeded = true; string columnName = ( string )columnNames[i]; object columnValue = columnValues[i]; sqlBuilder.Add(columnName) .Add(" = "); Parameter param = columnValue as Parameter; if (param != null) { sqlBuilder.Add(param); } else { sqlBuilder.Add(( string )columnValue); } } sqlBuilder.Add(" WHERE "); foreach (SqlString whereString in whereStrings) { if (andNeeded) { sqlBuilder.Add(" AND "); } andNeeded = true; sqlBuilder.Add(whereString, null, null, null, false); } if (log.IsDebugEnabled) { if (initialCapacity < sqlBuilder.Count) { log.Debug( "The initial capacity was set too low at: " + initialCapacity + " for the UpdateSqlBuilder " + "that needed a capacity of: " + sqlBuilder.Count + " for the table " + tableName); } else if (initialCapacity > 16 && (( float )initialCapacity / sqlBuilder.Count) > 1.2) { log.Debug( "The initial capacity was set too high at: " + initialCapacity + " for the UpdateSqlBuilder " + "that needed a capacity of: " + sqlBuilder.Count + " for the table " + tableName); } } return(sqlBuilder.ToSqlString()); }
public SqlString ToFragmentString() { var buf = new SqlStringBuilder(values.Count * 5); buf.Add(columnName); if (values.Count > 1) { // is a comma needed before the value that's about to be added - it // defaults to false because we don't need a comma right away. bool commaNeeded = false; // if a "null" is in the list of values then we need to manipulate // the SqlString a little bit more at the end. bool allowNull = false; buf.Add(" in ("); for (int i = 0; i < values.Count; i++) { object value = values[i]; if (Null.Equals(value)) { allowNull = true; } else if (NotNull.Equals(value)) { throw new NotSupportedException(string.Format("not null makes no sense for in expression (column:{0})", columnName)); } else { if (commaNeeded) { buf.Add(StringHelper.CommaSpace); } if (value is Parameter) { buf.Add((Parameter)value); } else { buf.Add((string)value); } // a value has been added into the IN clause so the next // one needs a comma before it commaNeeded = true; } } buf.Add(StringHelper.ClosedParen); // if "null" is in the list of values then add to the beginning of the // SqlString "is null or [column] (" + [rest of sqlstring here] + ")" if (allowNull) { buf.Insert(0, " is null or ").Insert(0, columnName).Insert(0, StringHelper.OpenParen).Add(StringHelper.ClosedParen); } } else { if (values.Count == 0) { throw new NotSupportedException(string.Format("Attempting to parse a null value into an sql string (column:{0}).", columnName)); } object value = values[0]; if (Null.Equals(value)) { buf.Add(" is null"); } else if (NotNull.Equals(value)) { buf.Add(" is not null "); } else { buf.Add("=").AddObject(values[0]); } } return(buf.ToSqlString()); }
public SqlString ToQuerySqlString() { SqlStringBuilder builder = new SqlStringBuilder(); builder.Add("select "); if (distinct) { builder.Add("distinct "); } SqlString from = joins.ToFromFragmentString; if (from.StartsWithCaseInsensitive(",")) { from = from.Substring(1); } else if (from.StartsWithCaseInsensitive(" inner join")) { from = from.Substring(11); } builder.Add(selectBuilder.ToSqlString()) .Add(" from") .Add(from); SqlString part1 = joins.ToWhereFragmentString.Trim(); SqlString part2 = whereBuilder.ToSqlString(); bool hasPart1 = part1.Count > 0; bool hasPart2 = part2.Count > 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.Count > 0) { builder.Add(" group by ").Add(groupBy.ToSqlString()); } if (having.Count > 0) { builder.Add(" having ").Add(having.ToSqlString()); } if (orderBy.Count > 0) { builder.Add(" order by ").Add(orderBy.ToSqlString()); } return(builder.ToSqlString()); }