public void RemoveAt()
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add(" select * ");
builder.Add("from table");
Assert.AreEqual( " select * from table", builder.ToSqlString().ToString() );
builder.RemoveAt(0);
Assert.AreEqual( "from table", builder.ToSqlString().ToString(), "Removed the first element in the SqlStringBuilder" );
builder.Insert(0, "SELECT * ");
Assert.AreEqual( "SELECT * from table", builder.ToSqlString().ToString() );
}
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 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();
}
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(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
{
if (this.criteria.Count == 0)
{
return EmptyExpression;
}
//TODO: add default capacity
SqlStringBuilder sqlBuilder = new SqlStringBuilder();
sqlBuilder.Add("(");
for (int i = 0; i < this.criteria.Count - 1; i++)
{
sqlBuilder.Add(this.criteria[i].ToSqlString(criteria, criteriaQuery, enabledFilters));
sqlBuilder.Add(Op);
}
sqlBuilder.Add(this.criteria[this.criteria.Count - 1].ToSqlString(criteria, criteriaQuery, enabledFilters));
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();
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();
}
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 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();
}
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 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();
}
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters)
{
//TODO: add default capacity
SqlStringBuilder sqlBuilder = new SqlStringBuilder();
string[] columnNames = criteriaQuery.GetColumnsUsingProjection(criteria, _propertyName);
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)
{
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();
}
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 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 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();
}
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();
}
/// <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();
}
/// <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, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
{
SqlStringBuilder result = new SqlStringBuilder()
.Add("count(*) as y")
.Add(position.ToString())
.Add("_");
return result.ToSqlString();
}
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
{
//TODO: set default capacity
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add("not (");
builder.Add(_criterion.ToSqlString(criteria, criteriaQuery, enabledFilters));
builder.Add(")");
return builder.ToSqlString();
}
public void InsertAndAdd()
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add("col1, col2 ");
builder.Insert(0, "select ");
builder.Add("from table ");
Assert.AreEqual("select col1, col2 from table ", builder.ToSqlString().ToString());
}
public void Index()
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add(" select * ");
builder.Add("from table");
builder[0] = "SELECT * ";
Assert.AreEqual( "SELECT * from table", builder.ToSqlString().ToString() );
Assert.AreEqual( "from table", builder[1] );
}
public CriteriaLoader( IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl criteria )
: base( persister, factory )
{
this.criteria = criteria;
AddAllToPropertySpaces( persister.PropertySpaces );
resultTypes = new IType[ 1 ];
resultTypes[ 0 ] = NHibernateUtil.Entity( persister.MappedClass );
SqlStringBuilder condition = new SqlStringBuilder( 10 );
bool foundCriterion = false;
foreach( CriteriaImpl.CriterionEntry ee in criteria.IterateExpressionEntries() )
{
if( foundCriterion )
{
condition.Add( " and " );
}
SqlString sqlString = ee.Criterion.ToSqlString(
factory,
criteria.GetPersistentClass( ee.Alias ),
ee.Alias,
criteria.AliasClasses );
condition.Add( sqlString );
foundCriterion = true;
}
if( !foundCriterion )
{
condition.Add( "1=1" ); // TODO: fix this ugliness
}
StringBuilder orderBy = new StringBuilder( 30 );
bool foundOrder = false;
foreach( Order ord in criteria.IterateOrderings() )
{
if( foundOrder )
{
orderBy.Append( StringHelper.CommaSpace );
}
orderBy.Append( ord.ToSqlString( factory, criteria.CriteriaClass, Alias ) );
foundOrder = true;
}
IList associations = WalkTree( persister, Alias, factory );
InitClassPersisters( associations );
InitStatementString( associations, condition.ToSqlString(), orderBy.ToString(), factory );
PostInstantiate();
}
public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
IDictionary<string, IFilter> enabledFilters) {
var builder = new SqlStringBuilder();
builder
.Add("(")
.Add(_criterion.ToSqlString(criteria, criteriaQuery, enabledFilters))
.Add(")");
return builder.ToSqlString();
}
public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
{
SqlStringBuilder buf = new SqlStringBuilder()
.Add("count(");
if (distinct)
{
buf.Add("distinct ");
}
buf.Add(criteriaQuery.GetColumn(criteria, propertyName))
.Add(") as y")
.Add(position.ToString())
.Add("_");
return buf.ToSqlString();
}
public virtual SqlString Render(IList args, ISessionFactoryImplementor factory)
{
if (args.Count > 0)
{
throw new QueryException("function takes no arguments: " + name);
}
SqlStringBuilder buf = new SqlStringBuilder(2);
buf.Add(name);
if (hasParenthesesIfNoArguments)
{
buf.Add("()");
}
return buf.ToSqlString();
}
public void GetLimitString()
{
SqlStringBuilder builder = new SqlStringBuilder();
builder.Add("select id, col1, col2 from someTable");
SqlString limitedSql = d.GetLimitString(builder.ToSqlString(), null, new SqlString("100"));
Assert.AreEqual("select top 100 id, col1, col2 from someTable", limitedSql.ToString(),
"Bad limit SQL");
// test when building with SqlParts
builder = new SqlStringBuilder();
builder.Add("select").Add(" id, col1, col2 ").Add("from someTable");
limitedSql = d.GetLimitString(builder.ToSqlString(), null, new SqlString("100"));
Assert.AreEqual("select top 100 id, col1, col2 from someTable", limitedSql.ToString(),
"Bad limit SQL");
// now add a subselect to see if only one top gets added
builder.Add(" where id in (select id from othertable)");
limitedSql = d.GetLimitString(builder.ToSqlString(), null, new SqlString("100"));
Assert.AreEqual("select top 100 id, col1, col2 from someTable where id in (select id from othertable)",
limitedSql.ToString(),
"Bad limit SQL");
// now the distinct case
// first with simple string
builder = new SqlStringBuilder();
builder.Add("select distinct id, col1, col2 from someTable");
limitedSql = d.GetLimitString(builder.ToSqlString(), null, new SqlString("100"));
Assert.AreEqual("select distinct top 100 id, col1, col2 from someTable", limitedSql.ToString(),
"Bad limit SQL");
// now with parts
builder = new SqlStringBuilder();
builder.Add("select").Add(" distinct").Add(" id, col1, col2 from someTable");
limitedSql = d.GetLimitString(builder.ToSqlString(), null, new SqlString("100"));
Assert.AreEqual("select distinct top 100 id, col1, col2 from someTable", limitedSql.ToString(),
"Bad limit SQL");
}
public SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery)
{
SqlStringBuilder buf = new SqlStringBuilder();
for (int i = 0; i < Length; i++)
{
IProjection proj = this[i];
buf.Add(proj.ToSqlString(criteria, loc, criteriaQuery));
loc += proj.GetColumnAliases(loc).Length;
if (i < elements.Count - 1)
{
buf.Add(", ");
}
}
return buf.ToSqlString();
}
public override SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
{
SqlStringBuilder s = new SqlStringBuilder();
string[] cols = criteriaQuery.GetColumnsUsingProjection(criteria, propertyName);
for (int i = 0; i < cols.Length; i++)
{
s.Add(cols[i]);
s.Add(" as y");
s.Add((loc + i).ToString());
s.Add("_");
if (i < cols.Length - 1)
s.Add(", ");
}
return s.ToSqlString();
}
private SqlString GetColumnPositionsString(int scalarColumnIndex)
{
int startPosition = Walker.SelectClause.GetColumnNamesStartPosition(scalarColumnIndex);
SqlStringBuilder buf = new SqlStringBuilder();
int nColumns = Walker.SelectClause.ColumnNames[scalarColumnIndex].Length;
for (int i = startPosition; i < startPosition + nColumns; i++)
{
if (i > startPosition)
{
buf.Add(", ");
}
buf.Add(i.ToString(CultureInfo.InvariantCulture));
}
return buf.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 (StringHelper.IsNotEmpty(outerJoinsAfterFrom))
{
sqlBuilder.Add(outerJoinsAfterFrom);
}
if (StringHelper.IsNotEmpty(whereClause) || StringHelper.IsNotEmpty(outerJoinsAfterWhere))
{
sqlBuilder.Add(" WHERE ");
// the outerJoinsAfterWhere needs to come before where clause to properly
// handle dynamic filters
if (StringHelper.IsNotEmpty(outerJoinsAfterWhere))
{
sqlBuilder.Add(outerJoinsAfterWhere);
if (StringHelper.IsNotEmpty(whereClause))
{
sqlBuilder.Add(" AND ");
}
}
if (StringHelper.IsNotEmpty(whereClause))
{
sqlBuilder.Add(whereClause);
}
}
if (StringHelper.IsNotEmpty(groupByClause))
{
sqlBuilder.Add(" GROUP BY ")
.Add(groupByClause);
}
if (StringHelper.IsNotEmpty(havingClause))
{
sqlBuilder.Add(" HAVING ")
.Add(havingClause);
}
if (StringHelper.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: " + initialCapacity + " for the SelectSqlBuilder " +
"that needed a capacity of: " + sqlBuilder.Count + " for the table " + fromClause);
}
else if (initialCapacity > 16 && ((float)initialCapacity / sqlBuilder.Count) > 1.2)
{
log.Debug(
"The initial capacity was set too high at: " + initialCapacity + " for the SelectSqlBuilder " +
"that needed a capacity of: " + sqlBuilder.Count + " for the table " + fromClause);
}
}
return(sqlBuilder.ToSqlString());
}
public virtual SqlString ToSqlString()
{
// 5 = "INSERT INTO", tableName, " (" , ") VALUES (", and ")"
int initialCapacity = 5;
// 2 = the first column is just the columnName and columnValue
initialCapacity += 2;
// eachColumn after the first one is 4 because of the ", ", columnName
// and the ", " columnValue
if (columns.Count > 0)
{
initialCapacity += ((columns.Count - 1) * 4);
}
if (!string.IsNullOrEmpty(comment))
{
initialCapacity++;
}
SqlStringBuilder sqlBuilder = new SqlStringBuilder(initialCapacity + 2);
if (!string.IsNullOrEmpty(comment))
{
sqlBuilder.Add("/* " + comment + " */ ");
}
sqlBuilder.Add("INSERT INTO ")
.Add(tableName);
if (columns.Count == 0)
{
sqlBuilder.Add(" ").Add(factory.Dialect.NoColumnsInsertString);
}
else
{
sqlBuilder.Add(" (");
// do we need a comma before we add the column to the INSERT list
// when we get started the first column doesn't need one.
bool commaNeeded = false;
foreach (string columnName in columns.Keys)
{
// build up the column list
if (commaNeeded)
{
sqlBuilder.Add(StringHelper.CommaSpace);
}
commaNeeded = true;
sqlBuilder.Add(columnName);
}
sqlBuilder.Add(") VALUES (");
commaNeeded = false;
foreach (object obj in columns.Values)
{
if (commaNeeded)
{
sqlBuilder.Add(StringHelper.CommaSpace);
}
commaNeeded = true;
SqlType param = obj as SqlType;
if (param != null)
{
sqlBuilder.Add(Parameter.Placeholder);
}
else
{
sqlBuilder.Add((string)obj);
}
}
sqlBuilder.Add(")");
}
if (log.IsDebugEnabled())
{
if (initialCapacity < sqlBuilder.Count)
{
log.Debug("The initial capacity was set too low at: {0} for the InsertSqlBuilder that needed a capacity of: {1} for the table {2}",
initialCapacity,
sqlBuilder.Count,
tableName);
}
else if (initialCapacity > 16 && ((float)initialCapacity / sqlBuilder.Count) > 1.2)
{
log.Debug("The initial capacity was set too high at: {0} for the InsertSqlBuilder that needed a capacity of: {1} for the table {2}",
initialCapacity,
sqlBuilder.Count,
tableName);
}
}
return(sqlBuilder.ToSqlString());
}
/// <summary></summary>
public SqlString ToSqlString()
{
//TODO: add default capacity
SqlStringBuilder sqlBuilder = new SqlStringBuilder();
if (comment != null)
{
sqlBuilder.Add("/* " + comment + " */ ");
}
bool commaNeeded = false;
sqlBuilder.Add("SELECT ");
for (int i = 0; i < columnNames.Count; i++)
{
string column = columnNames[i];
string alias = GetAlias(column);
if (commaNeeded)
{
sqlBuilder.Add(StringHelper.CommaSpace);
}
sqlBuilder.Add(column);
if (alias != null && !alias.Equals(column))
{
sqlBuilder.Add(" AS ")
.Add(alias);
}
commaNeeded = true;
}
sqlBuilder.Add(" FROM ")
.Add(Dialect.AppendLockHint(lockMode, tableName));
sqlBuilder.Add(" WHERE ");
if (whereStrings.Count > 1)
{
sqlBuilder.Add(whereStrings.ToArray(), null, "AND", null, false);
}
else
{
sqlBuilder.Add(whereStrings[0]);
}
if (orderBy != null)
{
sqlBuilder.Add(orderBy);
}
if (lockMode != null)
{
sqlBuilder.Add(Dialect.GetForUpdateString(lockMode));
}
return(sqlBuilder.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());
}
/// <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());
}
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());
}
/// <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());
}
