private Join[] GetJoinsFromOrderBy(EntityMap sourceEntity, string sourceAlias, OrderBy node)
{
// traverse the join tree, building a flat array of Join objects
ArrayList joinList = new ArrayList();
AddOrderByJoinsToList(joinList, sourceEntity, sourceAlias, node.OrderByJoins, 1);
return (Join[])joinList.ToArray(typeof(Join));
}
private void WriteSqlQuery(TextWriter w, Expression expr)
{
switch( expr.NodeType )
{
case NodeType.Property:
{
Property property = (Property)expr;
EntityMap map = _maps[property.OwnerClass];
FieldMap field = map.GetFieldMap(property.Name);
if( field == null )
{
throw new Exception("Property '" + property.Name + "' could not be found for entity type '" + property.OwnerClass + "'.");
}
string alias;
if( !field.IsLookup )
{
alias = GetAlias(property);
}
else // lookup field
{
LookupMap lookup = (LookupMap)field;
alias = (string)_lookupFieldToAliasMap[lookup.FieldAlias];
}
if( alias == null )
{
throw new Exception("Could not find table alias for property '" + property.Name + "' in entity '" + property.OwnerClass + "'.");
}
WriteSqlColumn(w, alias, field.Field);
return;
}
case NodeType.Parameter:
{
Query.Parameter node = (Query.Parameter)expr;
string name;
if( !_provider.NoNamedParameters )
{
name = _provider.ParameterPrefix + "P" + node.Ordinal;
}
else
{
name = "?";
}
_parameterTable.Add( new OPathParameter(name, node.ValueType) );
w.Write(name);
return;
}
case NodeType.Literal:
{
Literal node = (Literal)expr;
WriteLiteral(w, node.Value);
return;
}
case NodeType.Binary:
{
Binary node = (Binary)expr;
bool isFormat;
string keyword = GetSqlKeyword(node.Operator, out isFormat);
if( isFormat )
{
WriteFormat(w, keyword, node.Left, node.Right);
}
else
{
w.Write('(');
WriteSqlQuery(w, node.Left);
w.Write(' ');
w.Write(keyword);
w.Write(' ');
WriteSqlQuery(w, node.Right);
w.Write(')');
}
return;
}
case NodeType.Unary:
{
Unary node = (Unary)expr;
if( node.Operator == UnaryOperator.Exists )
{
w.Write(GetSqlKeyword(node.Operator));
w.Write("\n(\n");
WriteSqlQuery(w, node.Operand);
w.Write("\n)");
}
else if( node.Operator == UnaryOperator.IsNull )
{
w.Write('(');
WriteSqlQuery(w, node.Operand);
w.Write(' ');
w.Write(GetSqlKeyword(node.Operator));
w.Write(')');
}
else
{
w.Write(GetSqlKeyword(node.Operator));
w.Write('(');
WriteSqlQuery(w, node.Operand);
w.Write(')');
}
return;
}
case NodeType.Filter:
{
Filter filter = (Filter)expr;
// the only supported filters are the root and ones directly below an exists node
if( filter.Owner != null && (filter.Owner.NodeType != NodeType.Unary || (filter.Owner as Unary).Operator != UnaryOperator.Exists) )
{
throw new NotSupportedException("Filter with source type '" + filter.Source.NodeType + "' was not expected.");
}
EntityMap entity = _maps[filter.ValueType];
string entityAlias = GetNextAlias();
filter.Alias = entityAlias;
bool whereStarted = false;
if( filter.Source.NodeType == NodeType.TypeFilter ) // root filter/outer select
{
Join[] lookupJoins;
WriteSelectClause(w, entity, entityAlias, out lookupJoins);
Join[] joins;
if( _orderByNode != null )
{
Join[] orderByJoins = GetJoinsFromOrderBy(entity, entityAlias, _orderByNode);
joins = new Join[lookupJoins.Length + orderByJoins.Length];
lookupJoins.CopyTo(joins, 0);
orderByJoins.CopyTo(joins, lookupJoins.Length);
}
else
{
joins = lookupJoins;
}
WriteFromClause(w, entity.Table, entityAlias, joins);
if( entity.BaseEntity != null )
{
WriteSubEntityConstraint(w, entityAlias, entity.TypeField, entity.TypeValue, ref whereStarted);
}
WriteFilterConstraints(w, filter, whereStarted);
// add the default sort order if the entity has one defined but the query is not ordered
if( _orderByNode == null && entity.SortOrder != null && entity.SortOrder.Length > 0 )
{
w.Write("\nORDER BY ");
w.Write(entity.SortOrder);
}
}
else if( filter.Source.NodeType == NodeType.Property || filter.Source.NodeType == NodeType.Filter ) // nested filter/subquery
{
Expression source = filter.Source;
while( source.NodeType == NodeType.Filter )
{
source = (source as Filter).Source;
}
if( source.NodeType != NodeType.Property ) throw new Exception("Could not find source property for filter.");
Property relProperty = (Property)source;
if( !relProperty.IsRelational ) throw new Exception("Expected source property for Filter node to be relational.");
RelationMap relation = relProperty.RelationMap;
EntityMap sourceEntity = _maps[relProperty.OwnerClass];
string sourceAlias = GetAlias(relProperty);
string relationConstraint = ReplaceTableNameWithAlias(relation.Filter, sourceEntity.Table, sourceAlias);
relationConstraint = ReplaceTableNameWithAlias(relationConstraint, entity.Table, entityAlias);
switch( relation.Relationship )
{
case Relationship.Parent:
{
w.Write("SELECT *");
WriteFromClause(w, entity.Table, entityAlias, null);
if( entity.BaseEntity != null )
{
WriteSubEntityConstraint(w, entityAlias, entity.TypeField, entity.TypeValue, ref whereStarted);
}
WriteJoinCondition(w, sourceAlias, relation.Fields, entityAlias, GetFieldNames(entity.KeyFields), relationConstraint, true, ref whereStarted);
WriteFilterConstraints(w, filter, whereStarted);
break;
}
case Relationship.Child:
{
w.Write("SELECT *");
WriteFromClause(w, entity.Table, entityAlias, null);
if( entity.BaseEntity != null )
{
WriteSubEntityConstraint(w, entityAlias, entity.TypeField, entity.TypeValue, ref whereStarted);
}
WriteJoinCondition(w, sourceAlias, GetFieldNames(sourceEntity.KeyFields), entityAlias, relation.Fields, relationConstraint, true, ref whereStarted);
WriteFilterConstraints(w, filter, whereStarted);
break;
}
case Relationship.Many:
{
ManyMap junctionMap = (ManyMap)relation;
string junctionAlias = GetNextAlias();
relationConstraint = ReplaceTableNameWithAlias(relationConstraint, junctionMap.Table, junctionAlias);
// write the junction and child table, inner joined together in one select
w.Write("SELECT *");
WriteFromClause(w, junctionMap.Table, junctionAlias, null);
w.Write(", ");
WriteSqlTable(w, entity.Table, entityAlias);
if( entity.BaseEntity != null )
{
WriteSubEntityConstraint(w, entityAlias, entity.TypeField, entity.TypeValue, ref whereStarted);
}
WriteJoinCondition(w, sourceAlias, GetFieldNames(sourceEntity.KeyFields), junctionAlias, junctionMap.Source, null, true, ref whereStarted);
WriteJoinCondition(w, junctionAlias, junctionMap.Dest, entityAlias, GetFieldNames(entity.KeyFields), relationConstraint, true, ref whereStarted);
WriteFilterConstraints(w, filter, whereStarted);
break;
}
default:
{
throw new NotSupportedException("Relationship type '" + relation.Relationship + "' is not supported.");
}
}
}
else
{
throw new NotImplementedException("Filter with source type '" + filter.Source.NodeType + "' was not expected.");
}
return;
}
case NodeType.Function:
{
Function node = (Function)expr;
bool isFormat;
string keyword = GetSqlKeyword(node.Operator, out isFormat);
if( isFormat )
{
WriteFormat(w, keyword, node.Params);
}
else
{
if( node.Operator != FunctionOperator.In )
{
throw new NotSupportedException("Function operator '" + node.Operator + "' was not expected.");
}
w.Write('(');
WriteSqlQuery(w, node.Params[0]);
w.Write(' ');
w.Write(keyword);
w.Write(" (");
for( int i = 1; i < node.Params.Length; i++ )
{
if( i > 1 ) w.Write(", ");
WriteSqlQuery(w, node.Params[i]);
}
w.Write("))");
}
return;
}
case NodeType.OrderBy:
{
OrderBy node = (OrderBy)expr;
_orderByNode = node;
Filter filter = (node.Source as Filter);
if( filter == null )
{
throw new Exception("Expected source of OrderBy node to be Filter.");
}
WriteSqlQuery(w, filter);
w.Write("\nORDER BY ");
for( int i = 0; i < node.OrderByItems.Count; i++ )
{
OrderByItem item = node.OrderByItems[i];
FieldMap field = item.FieldInfo;
if( field == null )
{
throw new Exception("Field info does not exists for OrderByItem '" + item.Item + "'.");
}
string alias = (item.Join == null) ? filter.Alias : item.Join.Alias;
if( i > 0 ) w.Write(", ");
WriteSqlColumn(w, alias, field.Field);
w.Write( (item.Ascending) ? " ASC" : " DESC");
}
return;
}
case NodeType.Empty:
{
return;
}
default:
{
throw new NotSupportedException("Expression type '" + expr.GetType() + "' is not currently supported.");
}
}
}