public override SqlString GetLimitString(SqlString querySqlString, int offset, int limit)
{
if (querySqlString.IndexOfCaseInsensitive(" ORDER BY ") < 0)
{
querySqlString = querySqlString.Append(" ORDER BY GETDATE()");
}
return querySqlString.Append(string.Format(" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", offset, limit));
}
public void Append()
{
SqlString sql = new SqlString(new string[] { "select", " from table" });
SqlString postAppendSql = sql.Append(" where A=B");
Assert.IsFalse(sql == postAppendSql, "should be a new object");
Assert.AreEqual(1, postAppendSql.Count);
sql = postAppendSql;
postAppendSql = sql.Append(new SqlString(" and C=D"));
Assert.AreEqual(1, postAppendSql.Count);
Assert.AreEqual("select from table where A=B and C=D", postAppendSql.ToString());
}
public void Count()
{
SqlString sql =
new SqlString(
new object[] { "select", " from table where a = ", Parameter.Placeholder, " and b = ", Parameter.Placeholder });
Assert.AreEqual(4, sql.Count, "Count with no nesting failed.");
sql = sql.Append(new SqlString(new object[] { " more parts ", " another part " }));
Assert.AreEqual(5, sql.Count, "Added a SqlString to a SqlString");
}
/// <summary>
/// Render the SQL fragment
/// </summary>
public virtual SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
{
if(projection!=null)
{
SqlString sb=new SqlString();
SqlString produced = this.projection.ToSqlString(criteria, 0, criteriaQuery, new Dictionary<string, IFilter>());
SqlString truncated = NHibernate.Util.StringHelper.RemoveAsAliasesFromSql(produced);
sb = sb.Append(truncated);
sb = sb.Append(ascending ? " asc" : " desc");
return sb;
}
string[] columns = criteriaQuery.GetColumnAliasesUsingProjection(criteria, propertyName);
StringBuilder fragment = new StringBuilder();
ISessionFactoryImplementor factory = criteriaQuery.Factory;
for (int i = 0; i < columns.Length; i++)
{
// TODO H3: bool lower = _ignoreCase && type.SqlTypes( factory )[ i ] == Types.VARCHAR
bool lower = false;
if (lower)
{
fragment.Append(factory.Dialect.LowercaseFunction)
.Append("(");
}
fragment.Append(columns[i]);
if (lower)
{
fragment.Append(")");
}
fragment.Append(ascending ? " asc" : " desc");
if (i < columns.Length - 1)
{
fragment.Append(", ");
}
}
return new SqlString(fragment.ToString());
}
public SqlUpdateBuilder AppendAssignmentFragment(SqlString fragment)
{
if (assignments == null)
{
assignments = fragment;
}
else
{
assignments.Append(", ").Append(fragment);
}
return(this);
}
public OuterJoinableAssociation(IAssociationType joinableType, String lhsAlias, String[] lhsColumns, String rhsAlias,
JoinType joinType, SqlString withClause, ISessionFactoryImplementor factory,
IDictionary<string, IFilter> enabledFilters)
{
this.joinableType = joinableType;
this.lhsAlias = lhsAlias;
this.lhsColumns = lhsColumns;
this.rhsAlias = rhsAlias;
this.joinType = joinType;
joinable = joinableType.GetAssociatedJoinable(factory);
rhsColumns = JoinHelper.GetRHSColumnNames(joinableType, factory);
on = new SqlString(joinableType.GetOnCondition(rhsAlias, factory, enabledFilters));
if (StringHelper.IsNotEmpty(withClause))
on = on.Append(" and ( ").Append(withClause).Append(" )");
this.enabledFilters = enabledFilters; // needed later for many-to-many/filter application
}
public override SqlString AppendIdentitySelectToInsert(SqlString insertString)
{
return insertString.Append("\nselect @@identity");
}
public SqlUpdateBuilder AppendAssignmentFragment(SqlString fragment)
{
// SqlString is immutable
assignments = assignments == null ? fragment : assignments.Append(", ").Append(fragment);
return(this);
}
private SqlString AddWhereFragment(SqlString sql)
{
if (!hasWhere)
return sql;
return sql.Append(" and ").Append(sqlWhereString);
}
public override SqlString AddIdentifierOutParameterToInsert(SqlString insertString, string identifierColumnName, string parameterName)
{
return insertString.Append(" returning " + identifierColumnName);
}
public void Count()
{
SqlString sql = new SqlString( new object[] {"select", " from table where a = ", new Parameter( "p1" ), " and b = " , new Parameter( "p2" ) } );
Assert.AreEqual( 5, sql.Count, "Count with no nesting failed." );
sql = sql.Append( new SqlString( new object[] {" more parts ", " another part "} ) );
Assert.AreEqual( 7, sql.Count, "Added a SqlString to a SqlString" );
SqlString nestedSql = new SqlString( new object[] { "nested 1", "nested 2" } );
sql = sql.Append( new SqlString( new object[] { nestedSql, " not nested 1", " not nested 2" } ) );
Assert.AreEqual( 11, sql.Count, "Added 2 more levels of nesting" );
}
private void AggregateQueriesInformation()
{
sqlString = new SqlString();
foreach (AbstractQueryImpl query in queries)
{
QueryParameters queryParameters = query.GetQueryParameters();
queryParameters.ValidateParameters();
query.VerifyParameters();
IQueryTranslator[] queryTranslators =
session.GetQueries(query.ExpandParameterLists(queryParameters.NamedParameters), false);
foreach (QueryTranslator translator in queryTranslators)
{
translators.Add(translator);
parameters.Add(queryParameters);
queryParameters = GetFilteredQueryParameters(queryParameters, translator);
SqlCommandInfo commandInfo = translator.GetQueryStringAndTypes(session, queryParameters);
sqlString = sqlString.Append(commandInfo.Text).Append(dialect.MultipleQueriesSeparator).Append(Environment.NewLine);
types.AddRange(commandInfo.ParameterTypes);
}
}
}
protected override SqlString ApplyLocks( SqlString sqlSelectString, IDictionary lockModes, Dialect.Dialect dialect )
{
if ( lockModes == null || lockModes.Count == 0 )
{
return sqlSelectString;
}
else
{
ForUpdateFragment fragment = new ForUpdateFragment( lockModes );
return sqlSelectString.Append( fragment.ToSqlStringFragment( dialect ) );
}
}
public override SqlString AppendIdentitySelectToInsert(SqlString insertSql)
{
return insertSql.Append("; " + IdentitySelectString);
}
private SqlString AppendOrderByIfNeeded(SqlString sqlString)
{
SqlString orderByOrEmpty = queryParameters.FilteredSQL.SubstringStartingWithLast("order by");
return sqlString.Append(orderByOrEmpty);
}
public override SqlString AddIdentitySelectToInsert(SqlString insertSql, string identityColumn, string tableName)
{
return insertSql.Append("; " + GetIdentitySelectString(identityColumn, tableName));
}
/// <summary>
/// Modifies the given SQL by applying the appropriate updates for the specified
/// lock modes and key columns.
/// </summary>
/// <param name="sql">the SQL string to modify </param>
/// <param name="aliasedLockModes">a map of lock modes indexed by aliased table names. </param>
/// <param name="keyColumnNames">a map of key columns indexed by aliased table names. </param>
/// <returns> the modified SQL string. </returns>
/// <remarks>
/// The behavior here is that of an ANSI SQL <tt>SELECT FOR UPDATE</tt>. This
/// method is really intended to allow dialects which do not support
/// <tt>SELECT FOR UPDATE</tt> to achieve this in their own fashion.
/// </remarks>
public virtual SqlString ApplyLocksToSql(SqlString sql, IDictionary aliasedLockModes, IDictionary keyColumnNames)
{
return sql.Append(new ForUpdateFragment(this, aliasedLockModes, keyColumnNames).ToSqlStringFragment());
}
protected override SqlString ApplyLocks( SqlString sql, IDictionary lockModes, Dialect.Dialect dialect )
{
if( lockModes == null || lockModes.Count == 0 )
{
return sql;
}
else
{
IDictionary aliasedLockModes = new Hashtable();
IEnumerator keys = lockModes.Keys.GetEnumerator();
object key;
while ( keys.MoveNext() )
{
key = keys.Current;
aliasedLockModes.Add( GetAliasName( (String) key ), lockModes[key] );
}
return sql.Append(new ForUpdateFragment(aliasedLockModes).ToSqlStringFragment(dialect));
}
}
protected SqlString MergeOrderings(SqlString ass, SqlString orderBy)
{
if (ass.Length == 0)
return orderBy;
else if (orderBy.Length == 0)
return ass;
else
return ass.Append(StringHelper.CommaSpace).Append(orderBy);
}
public override SqlString AppendIdentitySelectToInsert(NHibernate.SqlCommand.SqlString insertString)
{
return(insertString.Append(";" + IdentitySelectString));
}