public void RenderColumnName(IDbColumn column, StringBuilder output)
{
// Special characters must be enclosed in quotes.
output.Append("\"");
output.Append(column.ColumnName);
output.Append("\"");
}
/// <summary>
/// Adds a column-value pair to the insert list.
/// </summary>
/// <param name="column">Column. <b>Null</b> is not allowed.</param>
/// <param name="value">Value that is inserted in the specified column. May be <b>null</b>.</param>
public void AddExpression(IDbColumn column, object value)
{
if (column == null)
throw new ArgumentNullException("column", Messages.InsertStatement_ColMayNoBeNull);
this.columnsAndValues.Add(column, value);
}
public void RenderAlias(IDbColumn column, StringBuilder output)
{
// Special characters must be enclosed in quotes.
output.Append('"');
output.Append(column.Alias);
output.Append('"');
}
public void RenderAlias(IDbColumn column, StringBuilder output)
{
// Special characters must be enclosed in brackets.
output.Append("[");
output.Append(column.Alias);
output.Append("]");
}
public void RenderAlias(IDbColumn column, StringBuilder output)
{
// Special characters must be enclosed by backtick characters.
output.Append("`");
output.Append(column.Alias);
output.Append("`");
}
/// <summary>
/// Initializes a new FieldError structure.
/// </summary>
/// <remarks>Validates the given value and sets the <see cref="ErrorCode"/> property.</remarks>
/// <param name="field">Field.</param>
/// <param name="value">Value that has been tested.</param>
public FieldError(IDbColumn field, object value)
{
this.field = field;
this.value = value;
FieldValidator validator = new FieldValidator(field);
this.errorCode = validator.Validate(value);
}
/// <summary>
/// Creates a new update expression that sets the given value.
/// </summary>
/// <param name="column">Column which is to be changed.</param>
/// <param name="newValue">New value. May be <b>null</b>.</param>
/// <returns>Expression.</returns>
public static UpdateExpression Value(IDbColumn column, object newValue)
{
if (newValue != null)
return new UpdateExpression(column, newValue);
else
return UpdateExpressionFactory.Null(column);
}
private void InitColumn(IDbColumn column)
{
this.item = column;
this.itemType = SqlItemType.Column;
this.dataType = column.DataType;
this.dbType = column.DbType;
this.alias = column.Alias;
}
/// <summary>
/// if column's ref and name are the same, then this two columns are the same
/// for groupping. this will make columns that have different alias name treated as the same column
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <returns></returns>
private static bool CheckSameColumnForGrouping(IDbColumn x, IDbColumn y)
{
if (x == null || y == null)
{
return(false);
}
return(x.Ref.Equals(y.Ref) && x.Name.Equals(y.Name));
}
/// <summary>
/// Returns the number of items in a group.
/// </summary>
/// <param name="field">Field. Null is not allowed.</param>
/// <param name="distinct">Specifies that COUNT returns the number of unique nonnull values.</param>
/// <param name="alias">Function alias. May be null.</param>
/// <returns>COUNT function.</returns>
public static AggregateFunction Count(IDbColumn field, bool distinct, string alias)
{
if (field == null)
throw new ArgumentNullException("field", Messages.AggregateFunctionFactory_FieldMayNotBeNull);
List<IRenderSql> functionParams = new List<IRenderSql>();
functionParams.Add(new PredicateItem(field, SqlItemType.Column));
return new AggregateFunction("COUNT", distinct, alias, functionParams, typeof(int));
}
// As a conveniance, insert items are internaly represented as instances of PredicateItem class.
// It is expected that all DBMSs render predicate and insert items in the same manner.
#region CTor.
/// <summary>
/// Creates a new column-value pair.
/// </summary>
/// <param name="column">Column to which the value is to be inserted.</param>
/// <param name="value">Value. May be <b>null</b>.</param>
internal InsertExpression(IDbColumn column, object value)
{
if (value != null)
this.ValueExpression = new PredicateItem(value, column.DbType, column.MaxLengthIfText, column.GetPrefixedAlias());
else
this.ValueExpression = new PredicateItem(DBNull.Value, column.DbType, column.MaxLengthIfText, column.GetPrefixedAlias());
this.Column = column;
}
/// <summary>
/// Creates a new SET expression.
/// </summary>
/// <param name="expressionType">Expression type.</param>
/// <param name="column">Column which is to be changed.</param>
/// <param name="sourceColumns">Columns required by the expression to compute the new value.
/// Required to properly render the FROM clause when columns from other tables are used.
/// <b>Null</b> if the expression doesn't use any columns.</param>
/// <param name="item">SQL item that computes/contains the new value.</param>
/// <param name="itemType">Item type.</param>
internal UpdateExpression(UpdateExpressionType expressionType, IDbColumn column, DbColumnCollection sourceColumns, object item, SqlItemType itemType)
: this(item, itemType)
{
this.ExpressionType = expressionType;
this.column = column;
if (sourceColumns == null || sourceColumns.Count == 0)
this.sourceColumns = new IDbColumn[0];
else
this.sourceColumns = sourceColumns.ToArray();
}
private static int IndexOfColumnInArray(IDbColumn[] array, string columnName)
{
for (int idx = 0; idx < array.Length; idx++)
{
if (array[idx].ColumnName == columnName)
return idx;
}
return -1;
}
public IDbColumn BuildColumn(IDbColumn column)
{
return(new SqlColumn
{
Name = column.Name,
Ref = column.Ref,
ValType = BuildType(column.ValType.DotNetType),
Alias = column.Alias,
OutputOption = column.OutputOption
});
}
public void RenderAlias(IDbColumn column, StringBuilder output)
{
// Special characters must be enclosed in quotes.
bool hasSpecChars = TextUtil.HasSpecialChars(column.Alias);
if (hasSpecChars)
output.Append("\"");
output.Append(column.Alias);
if (hasSpecChars)
output.Append("\"");
}
private static DbRelation JoinLevelsInSubTree(IDbTable prevLevel, IDbTable nextLevel, DbRelation recursiveRelation)
{
int keyLen = recursiveRelation.ChildForeignKey.Length;
IDbColumn[] parentPkCols = new IDbColumn[keyLen];
IDbColumn[] childFkCols = new IDbColumn[keyLen];
for (int keyPart = 0; keyPart < keyLen; keyPart++)
{
string pkPartName = recursiveRelation.ParentPrimaryKey[keyPart].ColumnName;
parentPkCols[keyPart] = nextLevel.Columns.GetByColumnName(pkPartName);
string fkPartName = recursiveRelation.ChildForeignKey[keyPart].ColumnName;
childFkCols[keyPart] = prevLevel.Columns.GetByColumnName(fkPartName);
}
DbRelation joinLevels = new DbRelation(nextLevel, parentPkCols, prevLevel, childFkCols, null);
return joinLevels;
}
private static void AppendColumnListInBrackets(InsertStatement insert, DbmsType dbms, StringBuilder output, IDbColumn autoIdField, bool mustAppendAutoIdField)
{
output.Append(" (");
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
colValPair.Column.RenderColumnName(dbms, output);
output.Append(", ");
}
if (mustAppendAutoIdField)
{
autoIdField.RenderColumnName(dbms, output);
output.Append(", ");
}
// Remove last ", " string.
output.Remove(output.Length - 2, 2);
output.Append(")");
}
public bool Equals(IDbColumn other)
{
if (ReferenceEquals(null, other))
{
return(false);
}
if (ReferenceEquals(this, other))
{
return(true);
}
return(SqlDataType == other.SqlDataType &&
string.Equals(ColumnName, other.ColumnName) &&
IsNullable == other.IsNullable &&
DataType == other.DataType &&
MaxLength == other.MaxLength &&
IsAutoIncrement == other.IsAutoIncrement);
}
internal ContainsPredicate(ContainsTermType type, IDbColumn field, string term, bool negate)
{
if (term == null)
throw new ArgumentNullException("term", Messages.ContainsPredicate_NullIsNotAllowed);
// throw new ArgumentException("CONTAINS predicate may only be used with textual field. " + field.ColumnName + " is not textual.", "field");
if (!DBTypeUtil.IsText(field.DataType))
throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Messages.ContainsPredicate_FieldXIsNotTextual, field.ColumnName), "field");
term = CleanContainsParameter(term);
if (term.Length == 0)
throw new ArgumentException(Messages.ContainsPredicate_TermIsNotValid, "term");
this.TermType = type;
PredicateItems = new PredicateItem[]
{
new PredicateItem(field),
new PredicateItem(term, DbType.String, field.MaxLengthIfText, field.GetPrefixedAlias())
};
Negate = negate;
}
private static bool IsNullableFk(IDbColumn[] fk)
{
foreach (IDbColumn fkPart in fk)
{
if (fkPart.IsNullable)
return true;
}
return false;
}
private static object MaxWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
// Stop condition: final level reached (tree depth is measured).
object maximum = null;
bool isLocalConn = !conn.IsOpen;
try
{
if (isLocalConn)
conn.OpenConnection();
endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
bool reachedEndLevel = false;
int level = beginAtLevel;
do
{
SelectStatement maxAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default);
maxAtLevel.SelectList.Clear();
IDbColumn comparedColumn = maxAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
maxAtLevel.SelectList.Add(AggregateFunctionFactory.Max(comparedColumn, "levelMax"));
DataTable results = maxAtLevel.Execute(conn);
object levelMax = results.Rows[0]["levelMax"];
maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable);
reachedEndLevel = (level >= endAtLevel);
level++;
} while (!reachedEndLevel);
}
finally
{
if (isLocalConn)
conn.CloseConnection();
}
if (maximum == null)
maximum = DBNull.Value;
return maximum;
}
private static object MaxOfAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
{
// Stop condition: count returns zero.
object maximum = null;
bool isLocalConn = !conn.IsOpen;
try
{
if (isLocalConn)
conn.OpenConnection();
bool reachedEndLevel = false;
int level = beginAtLevel;
do
{
object levelMax;
int nodeCount;
FetchMaxFromColumnAndCountAllNodesAtLevel(conn, rootEntity, recursiveRelation, level, column.ColumnName, out levelMax, out nodeCount);
maximum = MaxOfNullableValues(maximum as IComparable, levelMax as IComparable);
reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
level++;
} while (!reachedEndLevel);
}
finally
{
if (isLocalConn)
conn.CloseConnection();
}
if (maximum == null)
maximum = DBNull.Value;
return maximum;
}
/// <summary>
/// Creates a new validator for the given column.
/// </summary>
/// <param name="column">Database column.</param>
public FieldValidator(IDbColumn column)
{
this.type = column.DataType;
this.minValue = column.MinValue;
this.maxValue = column.MaxValue;
this.allowNull = column.IsNullable || column.IsAutoGenerated;
this.autoIncrement = column.AutoIncrement;
this.typeClass = GetTypeClass(this.type);
}
/// <summary>Retrieves the maximum column value in the hierarchy under the current entity.</summary>
/// <param name="conn">Connection-transcation context to use.</param>
/// <param name="rootEntity">Root entity.</param>
/// <param name="column">Column to compare. Must belong to the current entity's table.</param>
/// <param name="recursiveRelation">Recursive relation which defines a hierarchy.</param>
/// <param name="leafFilter">The search condition which restricts the rows processed. Often a criteria which filters out deactivated records.</param>
/// <param name="beginAtLevel">Zero-based index of the first level to process. Zero indicates that comparison starts at current entity,
/// one indicates that comparison starts at the level directly beneath it etc.</param>
/// <param name="endAtLevel">Zero-based index of the last level to process. Zero indicates that comparison ends at current entity,
/// one indicates that comparison ends at the level directly beneath it etc. <see cref="System.Int32.MaxValue"/> indicates that operation ends at leaf nodes.</param>
/// <returns>Maximum value or <see cref="DBNull.Value"/>.</returns>
public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
EnsureRecursiveRelation(recursiveRelation);
EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);
object maximum;
if (SearchCondition.IsNullOrEmpty(leafFilter))
maximum = MaxOfAll(conn, rootEntity, column, recursiveRelation, beginAtLevel, endAtLevel);
else
maximum = MaxWithLeafFilter(conn, rootEntity, column, recursiveRelation, leafFilter, beginAtLevel, endAtLevel);
return maximum;
}
private static object SumAll(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, int beginAtLevel, int endAtLevel)
{
// Stop condition: count returns zero.
object totalSum = null;
bool isLocalConn = !conn.IsOpen;
try
{
if (isLocalConn)
conn.OpenConnection();
bool reachedEndLevel = false;
int level = beginAtLevel;
do
{
SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, null, level, LevelQuerySelectList.Default);
sumAtLevel.SelectList.Clear();
IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum"));
sumAtLevel.SelectList.Add(AggregateFunctionFactory.Count("nodeCount"));
DataTable results = sumAtLevel.Execute(conn);
object levelSum = results.Rows[0]["levelSum"];
int nodeCount = Convert.ToInt32(results.Rows[0]["nodeCount"], CultureInfo.InvariantCulture);
totalSum = SumNullableValues(column, totalSum, levelSum);
reachedEndLevel = (nodeCount == 0) || (level >= endAtLevel);
level++;
} while (!reachedEndLevel);
}
finally
{
if (isLocalConn)
conn.CloseConnection();
}
return totalSum;
}
private static object SumNullableValues(IDbColumn col, object x, object y)
{
object sum;
if (x == null || x == DBNull.Value)
{
sum = y;
}
else if (y == null || y == DBNull.Value)
{
sum = x;
}
else
{
if (DBTypeUtil.IsInteger(col.DataType))
{
// Try to return 32bit number. This is what most developers expect.
long bigsum = Convert.ToInt64(x, CultureInfo.InvariantCulture) + Convert.ToInt64(y, CultureInfo.InvariantCulture);
if (bigsum > int.MaxValue)
sum = bigsum;
else
sum = Convert.ToInt32(bigsum, CultureInfo.InvariantCulture);
}
else if (col.DataType == typeof(decimal))
{
// Try not to loose precision.
decimal preciseSum = Convert.ToDecimal(x, CultureInfo.InvariantCulture) + Convert.ToDecimal(y, CultureInfo.InvariantCulture);
sum = preciseSum;
}
else if (DBTypeUtil.IsFloatingPointNumber(col.DataType))
{
double bigsum = Convert.ToDouble(x, CultureInfo.InvariantCulture) + Convert.ToDouble(y, CultureInfo.InvariantCulture);
if (bigsum > float.MaxValue)
sum = bigsum;
else
sum = Convert.ToSingle(bigsum, CultureInfo.InvariantCulture);
}
else
{
//throw new ArgumentException("Only numbers can be summed. Column '" + col.ColumnName + "' type is: " + col.DataType.Name, "col");
throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, Messages.GenericHierarchicalQueryExecutor_OnyNumberMayBeSummedColXTypeIsY, col.ColumnName, col.DataType.Name), "col");
}
}
return sum;
}
/// <summary>
/// Initializes a new FieldError structure.
/// </summary>
/// <remarks>Used when the value has already been validate and the error code has already been computed.</remarks>
/// <param name="field">Field.</param>
/// <param name="errorCode">Error.</param>
/// <param name="value">Value that has been tested.</param>
public FieldError(IDbColumn field, FieldErrorCode errorCode, object value)
{
this.field = field;
this.errorCode = errorCode;
this.value = value;
}
private static object SumWithLeafFilter(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
{
// Stop condition: final level reached (tree depth is measured).
object totalSum = null;
bool isLocalConn = !conn.IsOpen;
try
{
if (isLocalConn)
conn.OpenConnection();
endAtLevel = GetEndLevel(conn, rootEntity, recursiveRelation, endAtLevel);
bool reachedEndLevel = false;
int level = beginAtLevel;
do
{
SelectStatement sumAtLevel = CreateSelectFromLevelQuery(rootEntity, recursiveRelation, leafFilter, level, LevelQuerySelectList.Default);
sumAtLevel.SelectList.Clear();
IDbColumn summedColumn = sumAtLevel.FromTable.Columns.GetByColumnName(column.ColumnName);
sumAtLevel.SelectList.Add(AggregateFunctionFactory.Sum(summedColumn, false, "levelSum"));
DataTable results = sumAtLevel.Execute(conn);
object levelSum = results.Rows[0]["levelSum"];
totalSum = SumNullableValues(column, totalSum, levelSum);
reachedEndLevel = (level >= endAtLevel);
level++;
} while (!reachedEndLevel);
}
finally
{
if (isLocalConn)
conn.CloseConnection();
}
return totalSum;
}
/// <summary>
/// Initializes a new instance of the RelationBucket class and adds an initial relation to the bucket using <see cref="Add(DbRelation)"/> method.
/// </summary>
/// <param name="parent">Parent/master table.</param>
/// <param name="child">Child/data table.</param>
/// <param name="childForeignKey">Child foreign key.</param>
public RelationBucket(IDbTable parent, IDbTable child, IDbColumn childForeignKey)
{
Add(parent, child, childForeignKey);
}
/// <summary>
/// Adds a relation to the bucket.
/// </summary>
/// <param name="parent">Parent/master table.</param>
/// <param name="child">Child/data table.</param>
/// <param name="childForeignKey">Child foreign key.</param>
/// <remarks>Child table is connected as outer. Parent table is connected as outer if foreign
/// key is made of exactly one nullable column. If parallel relations are used then tables must
/// use aliases, otherwise an error will occur.</remarks>
/// <exception cref="ArgumentException">is generated if the bucket already contains a relation
/// which contains the same tables with the same aliases as the new relation.</exception>
public void Add(IDbTable parent, IDbTable child, IDbColumn childForeignKey)
{
DbRelation relation = new DbRelation(parent, child, childForeignKey);
Add(relation);
}
/// <summary>
/// Creates a new UPDATE statement that updates rows in the given table.
/// </summary>
/// <param name="table">The table to update.</param>
/// <param name="columnToUpdate">First columns in the <see cref="UpdateList"/>. Additional items may be specified in <see cref="UpdateList"/> collection.</param>
/// <param name="newValue">New value of the specified field. May be <b>null</b>.</param>
public UpdateStatement(IDbTable table, IDbColumn columnToUpdate, object newValue)
: this(table)
{
this.UpdateList.Add(columnToUpdate, newValue);
}
/// <summary>
/// Creates a new update expression that copies the value from another column.
/// </summary>
/// <param name="targetColumn">Column which is to be changed.</param>
/// <param name="sourceColumn">Source column that contains the new value.
/// May belong to same or other table which is joined through <see cref="UpdateStatement.Where"/> property.</param>
/// <returns>Expression.</returns>
public static UpdateExpression OtherColumn(IDbColumn targetColumn, IDbColumn sourceColumn)
{
DbColumnCollection sourceCols = new DbColumnCollection();
sourceCols.Add(sourceColumn);
return new UpdateExpression(UpdateExpressionType.OtherColumn, targetColumn, sourceCols, sourceColumn, SqlItemType.Column);
}
/// <summary>
/// Creates a new update expression that changes a column to <b>null</b>.
/// </summary>
/// <param name="column">Column which is to be changed.</param>
/// <returns>Expression.</returns>
public static UpdateExpression Null(IDbColumn column)
{
return new UpdateExpression(column, DBNull.Value);
}
