private bool CanLookupMapsUseSameJoin(LookupMap a, LookupMap b)
{
if (string.Compare(a.Table, b.Table, true) != 0)
{
return(false);
}
if (a.Source.Length != b.Source.Length)
{
return(false);
}
if (a.Dest.Length != b.Dest.Length)
{
return(false);
}
for (int i = 0; i < a.Source.Length; i++)
{
if (string.Compare(a.Source[i], b.Source[i], true) != 0)
{
return(false);
}
}
for (int i = 0; i < a.Dest.Length; i++)
{
if (string.Compare(a.Dest[i], b.Dest[i], true) != 0)
{
return(false);
}
}
return(true);
}
private void WriteSelectClause(TextWriter w, EntityMap entity, string entityAlias, out Join[] lookupJoins)
{
w.Write("SELECT ");
int columnCount = 0;
ArrayList joins = new ArrayList();
ArrayList joinMaps = new ArrayList();
// add all core entity fields and lookup columns
for (int i = 0; i < entity.Fields.Length; i++)
{
if (columnCount > 0)
{
w.Write(", ");
}
FieldMap field = entity.Fields[i];
if (!field.IsLookup)
{
WriteSqlColumn(w, entityAlias, field.Field);
}
else // lookup column
{
LookupMap lookup = (LookupMap)field;
// see if we already have a join setup for the table associated to this lookup field
string tableAlias = null;
for (int j = 0; j < joinMaps.Count; j++)
{
if (CanLookupMapsUseSameJoin((LookupMap)joinMaps[j], lookup))
{
tableAlias = (joins[j] as Join).TableAlias;
break;
}
}
// create a new join if an existing one was not found above
if (tableAlias == null)
{
tableAlias = GetNextAlias();
StringWriter joinCondition = new StringWriter();
bool whereStarted = false;
WriteJoinCondition(joinCondition, entityAlias, lookup.Source, tableAlias, lookup.Dest, null, false, ref whereStarted);
joins.Add(new Join(lookup.Table, tableAlias, joinCondition.ToString(), 1));
joinMaps.Add(lookup);
}
WriteSqlColumn(w, tableAlias, lookup.Field, lookup.FieldAlias);
// add lookup to the map - in case we need the alias later during query generation
_lookupFieldToAliasMap.Add(lookup.FieldAlias, tableAlias);
}
columnCount += 1;
}
// add any sub fields
if (entity.SubFields.Keys.Count > 0)
{
foreach (string subField in entity.SubFields.Keys)
{
if (columnCount > 0)
{
w.Write(", ");
}
WriteSqlColumn(w, entityAlias, subField);
columnCount += 1;
}
}
// add type field if specified
if (entity.TypeField != null && entity.TypeField.Length > 0)
{
if (columnCount > 0)
{
w.Write(", ");
}
WriteSqlColumn(w, entityAlias, entity.TypeField);
columnCount += 1;
}
// return our array of joins
lookupJoins = (Join[])joins.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.");
}
}
}
