/// <summary>
/// Appends a new RIGHT OUTER JOIN between this table an the specified clause matching on the comparison
/// </summary>
/// <param name="foreign"></param>
/// <param name="compare"></param>
/// <returns></returns>
public DBJoin RightJoin(DBClause foreign, DBComparison compare)
{
DBJoin join = DBJoin.Join(foreign, JoinType.RightOuter, compare);
this.Joins.Add(join);
return(join);
}
/// <summary>
/// Appends a new INNER JOIN between this table an the specified clause matching with the specified comparison
/// </summary>
/// <param name="foreign"></param>
/// <param name="compare"></param>
/// <returns></returns>
public DBJoin InnerJoin(DBClause foreign, DBComparison compare)
{
DBJoin join = DBJoin.InnerJoin(foreign, compare);
this.Joins.Add(join);
return(join);
}
public DBTableSet On(DBClause parent, Compare comp, DBClause child)
{
DBComparison compare = DBComparison.Compare(parent, comp, child);
this.Last = this.Root.On(compare);
return(this);
}
/// <summary>
/// Appends restrictions to the last joined clauses and this sub query
/// </summary>
/// <param name="compare"></param>
/// <returns></returns>
public DBClause On(DBComparison compare)
{
IDBJoinable join = (IDBJoinable)this.Joins[this.Joins.Count - 1];
join.On(compare);
return((DBClause)join);
}
/// <summary>
/// Appends a new [type] JOIN between this table an the specified clause matching on the comparison
/// </summary>
/// <param name="table"></param>
/// <param name="type"></param>
/// <param name="comp"></param>
/// <returns></returns>
public DBJoin Join(DBClause table, JoinType type, DBComparison comp)
{
DBJoin join = DBJoin.Join(table, type, comp);
this.Joins.Add(join);
return(join);
}
public DBUpdateQuery Where(DBComparison compare)
{
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
/// <summary>
/// Creates the first where clause in this DBDeleteQuery using the fields and a series of DBClauses that are evaluated at execution time
/// </summary>
/// <param name="fld">The field which will be compared to the specified values</param>
/// <param name="values">The set of DBClauses to be evaluated and compared</param>
/// <returns>Itself so further statements can be chained</returns>
public DBDeleteQuery WhereIn(DBField fld, params DBClause[] values)
{
DBComparison compare = DBComparison.In(fld, values);
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
/// <summary>
/// Creates a new join of the specified type and the ON set to the specified comparison
/// </summary>
/// <param name="jointo"></param>
/// <param name="joinType"></param>
/// <param name="comp"></param>
/// <returns></returns>
public static DBJoin Join(DBClause jointo, JoinType joinType, DBComparison comp)
{
DBJoinRef jref = new DBJoinRef();
jref.JoinType = joinType;
jref.JoinedTo = jointo;
jref.Comparison = comp;
return(jref);
}
/// <summary>
/// Adds a comparison to the last join for this table
/// </summary>
/// <param name="compare"></param>
/// <returns></returns>
public DBClause On(DBComparison compare)
{
if (this.HasJoins == false)
{
throw new InvalidOperationException("No joined tables or sub queries to join to");
}
IDBJoinable join = (IDBJoinable)this.Joins[this.Joins.Count - 1];
join.On(compare);
return((DBClause)join);
}
/// <summary>
/// Creates a further where clause in this DBDeleteQuery using the specified comparison.
/// Then combines with the previous filter in a boolean AND operation
/// </summary>
/// <param name="comparison">The comparison filter</param>
/// <returns>Itself so further statements can be combined</returns>
public DBDeleteQuery AndWhere(DBComparison comparison)
{
if (null == _where)
{
throw new NullReferenceException(Errors.CannotAppendWhereClauseWithoutInitial);
}
_where = _where.And(comparison);
_last = _where;
return(this);
}
/// <summary>
/// Creates the first where clause in this DBDeleteQuery using the fields and a sub select that is evaluated at execution time
/// </summary>
/// <param name="field">The field which will be compared to the specified values</param>
/// <param name="select">The sub select to be evaluated and compared</param>
/// <returns>Itself so further statements can be chained</returns>
public DBDeleteQuery WhereIn(string field, DBSelectQuery select)
{
DBField fld = DBField.Field(field);
DBSubQuery subsel = DBSubQuery.SubSelect(select);
DBComparison compare = DBComparison.In(fld, subsel);
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
public static DBFilterSet Where(DBComparison compare)
{
if (null == compare)
{
throw new ArgumentNullException("compare");
}
DBFilterSet fs = new DBFilterSet();
fs.Filters = compare;
return(fs);
}
/// <summary>
/// Appends or sets the condition to this joins comparison
/// </summary>
/// <param name="compare"></param>
/// <returns></returns>
public DBClause On(DBComparison compare)
{
if (this.Comparison != null)
{
return(this.And(compare));
}
else
{
this._comp = compare;
}
return(this);
}
/// <summary>
/// Creates an AND'd collection of comparisons
/// </summary>
/// <param name="none"></param>
/// <returns></returns>
public static DBComparison None(DBComparison[] none)
{
DBMultiComparisonRef multi = new DBMultiComparisonRef();
List <DBComparison> not = new List <DBComparison>(none.Length);
foreach (DBComparison one in none)
{
not.Add(DBComparison.Not(one));
}
multi.All = not.ToArray();
multi.Operation = Operator.And;
return(multi);
}
//
// static statement factory methods
#region public static DBFilterSet Where(DBComparison compare) + 2 overloads
public static DBFilterSet Where(DBClause left, Compare op, DBClause right)
{
if (null == left)
{
throw new ArgumentNullException("left");
}
else if (null == right)
{
throw new ArgumentNullException("right");
}
DBComparison comp = DBComparison.Compare(left, op, right);
return(Where(comp));
}
public DBTableSet InnerJoin(DBTable table, DBComparison compare)
{
if (this.Root is DBTable)
{
((DBTable)this.Root).InnerJoin(table, compare);
}
else if (this.Root is DBSubQuery)
{
((DBSubQuery)this.Root).InnerJoin(table, compare);
}
else
{
throw new InvalidOperationException("Current root does not support joins");
}
this.Last = table;
return(this);
}
/// <summary>
/// Creates the first where clause in this DBDeleteQuery using the fields and a series of constant values
/// </summary>
/// <param name="field">The field which will be compared to the psecified values</param>
/// <param name="values">The set of values to limit the deletion to</param>
/// <returns>Itself so further statements can be chained</returns>
public DBDeleteQuery WhereIn(string field, params object[] values)
{
DBField fld = DBField.Field(field);
List <DBClause> items = new List <DBClause>();
if (values != null && values.Length > 0)
{
foreach (object val in values)
{
items.Add(DBConst.Const(val));
}
}
DBComparison compare = DBComparison.In(fld, items.ToArray());
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
public DBTableSet InnerJoin(DBTable table, DBClause parent, Compare op, DBClause child)
{
DBComparison comp = DBComparison.Compare(parent, op, child);
if (this.Root is DBTable)
{
((DBTable)this.Root).InnerJoin(table, comp);
}
else if (this.Root is DBSubQuery)
{
((DBSubQuery)this.Root).InnerJoin(table, comp);
}
else
{
throw new InvalidOperationException("Current root does not support joins");
}
this.Last = table;
return(this);
}
/// <summary>
/// Appends to the WHERE with an AND (compare AND compare AND compare)
/// </summary>
/// <param name="any">All the comparisons to join into a single or statement</param>
/// <returns></returns>
public DBDeleteQuery OrWhereAll(params DBComparison[] all)
{
DBComparison joined = DBComparison.All(all);
return(OrWhere(joined));
}
//
// static methods
//
#region public static DBJoin InnerJoin(DBReference jointo, DBFieldRef from, DBFieldRef to, ComparisonOperator comparison)
/// <summary>
/// Creates a new INNER JOIN with the ON set to the comparison of from and to
/// </summary>
/// <param name="jointo"></param>
/// <param name="from"></param>
/// <param name="to"></param>
/// <param name="comparison"></param>
/// <returns></returns>
public static DBJoin InnerJoin(DBClause jointo, DBClause from, DBClause to, Compare comparison)
{
DBComparison compref = DBComparison.Compare(from, comparison, to);
return(InnerJoin(jointo, compref));
}
/// <summary>
/// Creates a new INNER JOIN with the ON set to the comparison
/// </summary>
/// <param name="jointo"></param>
/// <param name="comp"></param>
/// <returns></returns>
public static DBJoin InnerJoin(DBClause jointo, DBComparison comp)
{
return(Join(jointo, JoinType.InnerJoin, comp));
}
/// <summary>
/// Appends a WHERE comparison for a collection of AND NOT'd clause WHERE (NOT (A=1)) AND (NOT (A=3)) AND (NOT ...
/// </summary>
/// <param name="none"></param>
/// <returns></returns>
public DBUpdateQuery WhereNone(params DBComparison[] none)
{
DBComparison joined = DBComparison.None(none);
return(this.Where(joined));
}
protected virtual DBClause DoRead(string element, XmlReader reader, XmlReaderContext context)
{
DBClause c = null;
switch (element)
{
case (XmlHelper.Select):
c = DBSelectQuery.Select();
break;
case (XmlHelper.Delete):
c = DBDeleteQuery.Delete();
break;
case (XmlHelper.Update):
c = DBUpdateQuery.Update();
break;
case (XmlHelper.Insert):
c = DBInsertQuery.InsertInto();
break;
case (XmlHelper.Script):
c = DBQuery.Script();
break;
case (XmlHelper.Use):
c = DBUseQuery.Use();
break;
case (XmlHelper.Table):
c = DBTable.Table();
break;
case (XmlHelper.Fields):
c = DBSelectSet.Select();
break;
case (XmlHelper.AField):
c = DBField.Field();
break;
case (XmlHelper.AllFields):
c = DBField.AllFields();
break;
case (XmlHelper.From):
c = DBTableSet.From();
break;
case (XmlHelper.Where):
c = DBFilterSet.Where();
break;
case (XmlHelper.Group):
c = DBGroupBySet.GroupBy();
break;
case (XmlHelper.Order):
c = DBOrderSet.OrderBy();
break;
case (XmlHelper.Assignments):
c = DBAssignSet.Assign();
break;
case (XmlHelper.Values):
c = DBValueSet.Values();
break;
case (XmlHelper.Join):
c = DBJoin.Join();
break;
case (XmlHelper.Function):
c = DBFunction.Function();
break;
case (XmlHelper.Constant):
c = DBConst.Null();
break;
case (XmlHelper.Top):
c = DBTop.Top();
break;
case (XmlHelper.UnaryOp):
c = DBComparison.Not();
break;
case (XmlHelper.Compare):
c = DBComparison.Compare();
break;
case (XmlHelper.Between):
c = DBComparison.Between();
break;
case (XmlHelper.Parameter):
//parameter is a special case.
//we add them to akeyed colection if they are not already registered
//then at the end we set the values at the end
string name = reader.GetAttribute(XmlHelper.Name);
DBParam aparam;
if (context.Parameters.TryGetParameter(name, out aparam))
{
c = aparam;
}
else
{
aparam = DBParam.Param();
aparam.Name = name;
context.Parameters.Add(aparam);
c = aparam;
}
break;
case (XmlHelper.OrderBy):
c = DBOrder.OrderBy();
break;
case (XmlHelper.Calculation):
c = DBCalc.Calculate();
break;
case (XmlHelper.Aggregate):
c = DBAggregate.Aggregate();
break;
case (XmlHelper.ValueGroup):
c = DBValueGroup.Empty();
break;
case (XmlHelper.BooleanOperator):
c = DBBooleanOp.Compare();
break;
case (XmlHelper.Assign):
c = DBAssign.Assign();
break;
case (XmlHelper.InnerSelect):
c = DBSubQuery.SubSelect();
break;
case (XmlHelper.Multiple):
c = DBMultiComparisonRef.Many();
break;
case (XmlHelper.QueryOptionSet):
c = new DBQueryHintOptionSet();
break;
case (XmlHelper.QueryOption):
c = DBQueryHintOption.QueryOption();
break;
case (XmlHelper.CreateSproc):
c = DBCreateProcedureQuery.CreateProcedure();
break;
case (XmlHelper.Declare):
c = DBDeclaration.Declare();
break;
case (XmlHelper.CreateTable):
c = DBCreateTableQuery.Table();
break;
case (XmlHelper.ColumnDefinition):
c = DBColumn.Column();
break;
case (XmlHelper.PrimaryKey):
c = DBPrimaryKey.PrimaryKey();
break;
case (XmlHelper.ForeignKey):
c = DBForeignKey.ForeignKey();
break;
case (XmlHelper.CreateIndex):
c = DBCreateIndexQuery.Index();
break;
case (XmlHelper.CreateView):
c = DBCreateViewQuery.CreateView();
break;
case (XmlHelper.CreateSequence):
c = DBCreateSequenceQuery.Sequence();
break;
case (XmlHelper.DropTable):
c = DBDropTableQuery.DropTable();
break;
case (XmlHelper.DropIndex):
c = DBDropIndexQuery.DropIndex();
break;
case (XmlHelper.DropView):
c = DBDropViewQuery.DropView();
break;
case (XmlHelper.DropSproc):
c = DBDropProcedureQuery.DropProcedure();
break;
case (XmlHelper.DropSequence):
c = DBDropSequenceQuery.DropSequence();
break;
default:
throw XmlHelper.CreateException("The XML data could not be deserialized because the element {1} was not recognised. {0}", reader, null, element);
}
if (c != null)
{
c.ReadXml(reader, context);
}
return(c);
}
/// <summary>
/// Appends a WHERE comparison for a collection of OR'd clauses e.g WHERE (A=1) OR (A=2) OR (A=3) OR....
/// </summary>
/// <param name="any"></param>
/// <returns></returns>
public DBUpdateQuery WhereAny(params DBComparison[] any)
{
DBComparison joined = DBComparison.Any(any);
return(this.Where(joined));
}
/// <summary>
/// Appends a WHERE comparison for a collection of AND'd clauses e.g WHERE (A=1) AND (B=2) AND (C=3) AND...
/// </summary>
/// <param name="all"></param>
/// <returns></returns>
public DBUpdateQuery WhereAll(params DBComparison[] all)
{
DBComparison joined = DBComparison.All(all);
return(this.Where(joined));
}
public DBFilterSet Or(DBClause left, Compare op, DBClause clause)
{
return(Or(DBComparison.Compare(left, op, clause)));
}
//
// Interface Implementations
//
#region IDBJoinable Members
DBClause IDBJoinable.On(DBComparison comp)
{
return(this.On(comp));
}
public DBTableSet On(DBComparison comp)
{
this.Last = this.Root.On(comp);
return(this);
}
/// <summary>
/// Appends a new LEFT OUTER JOIN between this table and the specified table matching between this tables parentfield and the other tables child field
/// </summary>
/// <param name="tbl"></param>
/// <param name="parent"></param>
/// <param name="comp"></param>
/// <param name="child"></param>
/// <returns></returns>
public DBJoin LeftJoin(DBTable tbl, DBClause parent, Compare comp, DBClause child)
{
DBComparison c = DBComparison.Compare(parent, comp, child);
return(LeftJoin(tbl, c));
}
/// <summary>
/// Appends to the WHERE with an AND ((NOT compare) AND (NOT compare) AND (NOT compare))
/// </summary>
/// <param name="any">All the comparisons to join into a single or statement</param>
/// <returns></returns>
public DBDeleteQuery OrWhereNone(params DBComparison[] none)
{
DBComparison joined = DBComparison.None(none);
return(OrWhere(joined));
}