/// <summary>
/// Adds the elements of an array to the end of this ExpressionCollection.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the end of this ExpressionCollection.
/// </param>
public virtual void AddRange(ISQLExpression[] items)
{
foreach (ISQLExpression item in items)
{
this.List.Add(item);
}
}
public CaseTest(ISQLExpression testExpression, ISQLExpression testResult)
{
_TestExpression = testExpression;
_TestResult = testResult;
}
public UnaryLogicExpression(UnaryLogicOperator op, ISQLExpression operand)
{
_Operator = op;
_Operand = operand;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id, {SortSelect}
// FROM
// (
// SELECT Type, Id, {SortSelect}
// FROM {OPathQuery without order by}
// ) e1
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// put in a subquery because access cannot order with alias in select
SelectStatement selectZ = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectZ.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
selectZ.SelectedAllColumns = select.SelectedAllColumns;
selectZ.FromClause.Add(select as Table);
selectZ.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectZ;
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM (select x from y where z) as x WHERE XXX
//ORDER BY [orderByClause]
// put in a subquery because access cannot order with alias in select
SelectStatement selectX = new SelectStatement(null);
foreach (ISQLExpression exp in select.SelectList)
{
if (exp is Column)
selectX.SelectList.Add(new SelectItem(null, ((Column)exp).Alias == String.Empty ? ((Column)exp).ColumnName : ((Column)exp).Alias, ""));
}
if (select.SelectList.Count == 0)
selectX.SelectedAllColumns = true;
selectX.Limit = pageSize;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = select.OrderByClause;
select.OrderByClause = new OrderByClause(select);
select = selectX;
}
return select;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
//SELECT XXX
//FROM XXX WHERE XXX
//ORDER BY [orderByClause]
//[LIMIT pageSize OFFSET topPage]
select.Limit = pageSize;
select.Offset = topPage;
return select;
}
/// <summary>
/// Adds an instance of type Expression to the end of this ExpressionCollection.
/// </summary>
/// <param name="value">
/// The Expression to be added to the end of this ExpressionCollection.
/// </param>
public virtual void Add(ISQLExpression value)
{
this.List.Add(value);
}
/// <summary>
/// Initializes a new instance of the ExpressionCollection class, containing elements
/// copied from an array.
/// </summary>
/// <param name="items">
/// The array whose elements are to be added to the new ExpressionCollection.
/// </param>
public ExpressionCollection(ISQLExpression[] items)
{
this.AddRange(items);
}
/// <summary>
/// Inserts an element into the ExpressionCollection at the specified index
/// </summary>
/// <param name="index">
/// The index at which the Expression is to be inserted.
/// </param>
/// <param name="value">
/// The Expression to insert.
/// </param>
public virtual void Insert(int index, ISQLExpression value)
{
this.List.Insert(index, value);
}
public BinaryLogicExpression(ISQLExpression leftOperand, BinaryLogicOperator op, ISQLExpression rightOperand)
{
_LeftOperand = leftOperand;
_Operator = op;
_RightOperand = rightOperand;
}
public string[] RenderQueries(ISQLExpression expression)
{
return RenderQueries(expression, null);
}
public string[] RenderQueries(ISQLExpression expression, IDriver driver)
{
if (driver != null)
_Driver = driver;
_Query = new StringBuilder();
expression.Accept(this);
ArrayList queries = new ArrayList();
string query = _Query.ToString();
int startIndex = 0, endIndex = -1;
while ((endIndex = query.IndexOf(ENDQUERY, startIndex)) != -1)
{
queries.Add(query.Substring(startIndex, endIndex - startIndex));
startIndex = endIndex + ENDQUERY.Length;
}
queries.Add(query.Substring(startIndex, query.Length - startIndex));
return (string[])queries.ToArray(typeof(string));
}
protected bool IsSelectStatement(ISQLExpression expression)
{
return expression as SelectStatement != null;
}
protected SelectStatement CheckAndAdjustExpression(ISQLExpression sqlExp, OrderByClause orderby)
{
SelectStatement select = null;
if(sqlExp is UnionStatement)
{
// put expression in a select statement
UnionStatement union = (UnionStatement)sqlExp;
union.TableAlias = "DERIVEDTBL";
select = new SelectStatement(null);
select.SelectedAllColumns = true;
select.FromClause.Add(union as Table);
foreach(OrderByClauseColumn oc in orderby)
select.OrderByClause.Add(oc);
if(union.OrderByClause.Count > 0)
union.OrderByClause.Clear();
}
else if (sqlExp is SelectStatement)
{
((SelectStatement)sqlExp).OrderByClause = orderby;
select = (SelectStatement)sqlExp;
}
return select;
}
public abstract ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize);
/// <summary>
/// Determines whether a specfic Expression value is in this ExpressionCollection.
/// </summary>
/// <param name="value">
/// The Expression value to locate in this ExpressionCollection.
/// </param>
/// <returns>
/// true if value is found in this ExpressionCollection;
/// false otherwise.
/// </returns>
public virtual bool Contains(ISQLExpression value)
{
return this.List.Contains(value);
}
/// <summary>
/// Return the zero-based index of the first occurrence of a specific value
/// in this ExpressionCollection
/// </summary>
/// <param name="value">
/// The Expression value to locate in the ExpressionCollection.
/// </param>
/// <returns>
/// The zero-based index of the first occurrence of the _ELEMENT value if found;
/// -1 otherwise.
/// </returns>
public virtual int IndexOf(ISQLExpression value)
{
return this.List.IndexOf(value);
}
public IsNullPredicate(ISQLExpression expression)
{
_Expression = expression;
}
/// <summary>
/// Removes the first occurrence of a specific Expression from this ExpressionCollection.
/// </summary>
/// <param name="value">
/// The Expression value to remove from this ExpressionCollection.
/// </param>
public virtual void Remove(ISQLExpression value)
{
this.List.Remove(value);
}
public int Add(string key, ISQLExpression value)
{
return base.Add (new DictionaryEntry(key, value));
}
public CastExpression(ITagMapping tag, ISQLExpression expression, DbType dbType)
{
_Expression = expression;
_DbType = dbType;
_TagMapping = tag;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1 || pageSize != 0)
{
// paging
//SELECT y.* FROM
//(
// SELECT x.*, [rownum n]
// FROM
// (
// SELECT e1.*
// FROM ( {OPathQuery} ) e1
// ORDER BY [SortOrderBy]
// )x
//)y
//WHERE n Between [topPage] and [topPage + pageSize - 1]
// ORDER BY n <-- add this sort to not loose order when putting this query in another one
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
select.TableAlias = "x";
selectX.SelectList.Add(new SystemObject("x", MULT, String.Empty)); // use SystemObject to have the table prefix
selectX.SelectList.Add(new SystemObject("rownum", "n"));
selectX.FromClause.Add(select as Table);
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
if(topPage > 1)
selectY.WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, "n"), BinaryLogicOperator.GreaterOrEquals, new Constant(topPage, DbType.Int32)));
if(pageSize > 0)
selectY.WhereClause.SearchCondition.Add(new BinaryLogicExpression(new Column(null, "n"), BinaryLogicOperator.LesserOrEquals, new Constant(topPage + pageSize - 1, DbType.Int32)));
selectY.OrderByClause.Add(new OrderByClauseColumn("n"));
select = selectY;
}
return select;
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="expression">The expression.</param>
private void ExecuteCommand(ISQLExpression expression)
{
string[] queries = _Dialect.RenderQueries(expression);
foreach (string q in queries)
{
if (!string.IsNullOrEmpty(q))
{
IDbCommand command = _Driver.CreateCommand(q, _Connection, _Transaction);
command.Transaction = _Transaction;
if (_Engine.TraceSqlSwitch.Enabled)
{
TraceHelpler.Trace(command, _Dialect);
}
command.ExecuteNonQuery();
}
}
}
public LikePredicate(ISQLExpression match_value, string pattern)
{
_MatchValue = match_value;
_Pattern = pattern;
}
protected bool IsJoinedTable(ISQLExpression expression)
{
if (expression.GetType() == typeof(JoinedTable))
return true;
return false;
}
public LikePredicate(ISQLExpression match_value, string pattern, char character_escape)
{
_MatchValue = match_value;
_Pattern = pattern;
_CharacterEscape = character_escape;
}
public EntitySet LoadWithSql(ISQLExpression query, string[] attributes, Hashtable columnAliasMapping, bool loadReference)
{
EntitySet es = new EntitySet();
_Connection.Open();
_Transaction = _Driver.BeginTransaction(_Connection);
#if !DEBUG
try
{
#endif
foreach (string sql in _Dialect.RenderQueries(query, Driver))
{
es = LoadSql(sql, attributes, columnAliasMapping, loadReference);
}
_Transaction.Commit();
#if !DEBUG
}
catch (Exception e)
{
_Transaction.Rollback();
throw e;
}
finally
{
#endif
_Connection.Close();
#if !DEBUG
}
#endif
return es;
}
public AggregateFunction(ITagMapping tag, AggregateFunctionEnum type, ISQLExpression exp)
{
_ValueExpression = exp;
_Type = type;
_TagMapping = tag;
}
public override ISQLExpression Page(ISQLExpression sqlExp, OrderByClause orderby, int topPage, int pageSize)
{
SelectStatement select = CheckAndAdjustExpression(sqlExp, orderby);
// Has paging ?
if (topPage != 1)
{
// paging
//SELECT TOP 100 PERCENT y.* FROM
//(
// SELECT TOP {TopMax} x.*
// FROM
// (
// SELECT TOP {TopFirstPlusMax} e1.Type, e1.Id
// {SortSelect}
// FROM ( {OPathQuery} ) e1
// {SortFrom}
// {SortOrderBy}
// )x
// {SortOrderBy_REV}
//)y
//{SortOrderBy}
SelectStatement selectY = new SelectStatement(null);
SelectStatement selectX = new SelectStatement(null);
// reverse sort order
OrderByClause sortOrderByRev = new OrderByClause(select);
foreach (OrderByClauseColumn col in select.OrderByClause)
sortOrderByRev.Add(new OrderByClauseColumn(col.ColumnName, !col.Desc));
select.Limit = topPage + pageSize - 1;
select.TableAlias = "x";
selectX.Limit = pageSize;
selectX.SelectedAllColumns = true;
selectX.FromClause.Add(select as Table);
selectX.OrderByClause = sortOrderByRev;
selectX.TableAlias = "y";
selectY.SelectedAllColumns = true;
selectY.FromClause.Add(selectX as Table);
selectY.OrderByClause = select.OrderByClause;
select = selectY;
select.Limit = -1;
}
else
{
// only sort, limit possible
//SELECT [TOP pageSize | TOP 100 PERCENT] XXX
//FROM XXX WHERE XXX
//ORDER BY [orderByClause]
select.Limit = pageSize;
}
return select;
}
public CaseExpression(ITagMapping tagMapping, ISQLExpression expressiontToEval, string column_alias) : base(tagMapping, String.Empty, String.Empty, column_alias)
{
_ExpressionToEval = expressiontToEval;
_TestExpressions = new CaseTestCollection();
}
