public override void AddJoin(string tableName, string alias, string[] fkColumns, string[] pkColumns, JoinType joinType)
{
switch (joinType)
{
case JoinType.InnerJoin:
AddCrossJoin(tableName, alias);
break;
case JoinType.LeftOuterJoin:
afterFrom.Add(StringHelper.CommaSpace).Add("outer ").Add(tableName).Add(" ").Add(alias);
break;
case JoinType.RightOuterJoin:
int i = GetPrevTableInsertPoint(afterFrom.ToSqlString());
afterFrom.Insert(i, "outer ");
break;
case JoinType.FullJoin:
throw new NotSupportedException("join type not supported by Informix");
default:
throw new AssertionFailure("undefined join type");
}
for (int j = 0; j < fkColumns.Length; j++)
{
HasThetaJoins = true;
afterWhere.Add(" and " + fkColumns[j]);
afterWhere.Add("=" + alias + StringHelper.Dot + pkColumns[j]);
}
}
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();
}
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 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 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 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>
/// 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 ?"
*/
SqlStringBuilder pagingBuilder = new SqlStringBuilder();
bool isInOrderBy = false;
StringBuilder orderByStringBuilder = new StringBuilder();
// build a new query and extract the order by part
foreach( object sqlPart in querySqlString.SqlParts )
{
string sqlPartString = sqlPart as string;
if( sqlPartString != null )
{
if( sqlPartString.ToLower( System.Globalization.CultureInfo.InvariantCulture ).TrimStart().StartsWith( "order by" ) )
{
isInOrderBy = true;
}
}
if( isInOrderBy && sqlPart is string )
{
orderByStringBuilder.Append( ( string ) sqlPart );
}
else
{
pagingBuilder.AddObject( sqlPart );
}
}
string rownumClause = "rownumber() over(" + orderByStringBuilder.ToString() + ") as rownum, ";
// Add the rownum clause first, right after the original select
pagingBuilder.Insert( 1, rownumClause );
// Add the rest
pagingBuilder.Insert( 0, "select * from (" );
pagingBuilder.Add( ") as tempresult " );
// Add the where clause
pagingBuilder.Add( "where rownum " );
if( hasOffset )
{
pagingBuilder.Add( "between " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) ).Add("+1");
pagingBuilder.Add( " and " );
pagingBuilder.Add( new Parameter( "p2", new Int32SqlType() ) );
}
else
{
pagingBuilder.Add( "<= " );
pagingBuilder.Add( new Parameter( "p1", new Int32SqlType() ) );
}
return pagingBuilder.ToSqlString();
}
/// <summary></summary>
public SqlString ToFragmentString()
{
SqlStringBuilder 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++ )
{
if( Null.Equals( values[ i ] ) )
{
allowNull = true;
}
else if ( NotNull.Equals( values[ i ] ) )
{
throw new ArgumentOutOfRangeException( "not null makes no sense for in expression" ) ;
}
else
{
if( commaNeeded )
{
buf.Add( StringHelper.CommaSpace );
}
buf.Add( ( string ) values[ i ] );
// 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
{
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( "=" + values[ 0 ] );
}
}
return buf.ToSqlString();
}
