/// <summary>
/// Create a RootFieldPath from a list of various paths. Indexing this root path will result in finding the correct (unique) field path.
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
public static UniqueFieldPaths FromPaths(IList <IFieldPath> paths)
{
var root = new UniqueFieldPaths();
foreach (var path in paths)
{
var subject = path[0].Subject;
var uniquePath = root._paths.Find(m => m.Subject.Equals(subject));
if (uniquePath == null)
{
root._paths.Add(uniquePath = new UniqueFieldPath(subject));
}
uniquePath.Add(path);
/*var list = root._root[path[0].Subject];
* var ufp = list.Find(m => m.Field.Equals(path[0]));
* if (ufp == null)
* {
* ufp = new UniqueFieldPath() {};
* ufp.Add(path);
* list.Add(ufp);
* }*/
}
return(root);
}
/// <summary>
/// Returns a string "field1, field2, field3"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetColumnDefinitions(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var c in _columns)
{
sb.AppendFormat(String.Concat("{0}.[{1}]", (AliasColumns ? " AS [{2}]" : string.Empty), ", "),
paths[c].Alias, c.Last.SourceName, c.Description);
}
return(sb.Remove(sb.Length - 2, 2).ToString());
}
/// <summary>
/// Returns a string "table1 join table2 on x = y join table3 on a = b"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetFromClause(UniqueFieldPaths paths)
{
// Target subject will be q0
// All other subjects aliased from q1 up
var sb = new StringBuilder();
sb.AppendFormat("({0}) AS {1} ", _target.Source, paths[new FieldPath(_target.IdField)].Alias);
foreach (var ufp in paths)
{
// if this isn't part of our original target, join through the matrix
if (!ufp.Subject.Equals(_target))
{
// else top level subject not included - join with matrix
sb.AppendFormat("LEFT OUTER JOIN ({1}) AS m{2} ON m{2}.FromID = {0}.[{4}] LEFT OUTER JOIN ({3}) AS {2} ON m{2}.ToID = {2}.[{5}] ",
paths[new FieldPath(_target.IdField)].Alias,
_configuration[_target, ufp.Subject].Query,
ufp.Alias,
ufp.Subject.Source,
_target.IdField.SourceName,
ufp.Subject.IdField.SourceName);
}
// ok so the first part of UniqueFieldPath is all good, now join the rest
var queue = new Queue <UniqueFieldPath>();
foreach (var f in ufp)
{
queue.Enqueue(f);
}
while (queue.Count > 0)
{
var field = queue.Dequeue();
sb.AppendFormat("LEFT OUTER JOIN ({0}) AS {1} ON {1}.[{4}] = {2}.[{3}] ",
field.Subject.Source,
field.Alias,
field.Predecessor.Alias,
field.Field.SourceName,
field.Subject.IdField.SourceName);
foreach (var child in field)
{
queue.Enqueue(child);
}
}
}
return(sb.ToString());
}
/// <summary>
/// Returns a string "GROUP BY column1, column2"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetGroupByClause(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var g in _groupBy)
{
sb.AppendFormat("{0}.[{1}], ", paths[g].Alias, g.Last.SourceName);
}
if (sb.Length == 0)
{
return(null);
}
sb.Insert(0, " GROUP BY ");
return(sb.Remove(sb.Length - 2, 2).ToString());
}
/// <summary>
/// Returns a string "SORT BY column1 asc, column2 desc"
/// </summary>
/// <param name="paths"></param>
/// <returns></returns>
protected virtual string GetOrderByClause(UniqueFieldPaths paths)
{
var sb = new StringBuilder();
foreach (var s in _orderBy)
{
sb.AppendFormat("{0}.[{1}] {2}, ", paths[s.Path].Alias, s.Path.Last.SourceName, s.Direction);
}
if (sb.Length == 0)
{
return(null);
}
sb.Insert(0, " ORDER BY ");
return(sb.Remove(sb.Length - 2, 2).ToString());
}
private void AssignAliases(UniqueFieldPaths paths)
{
int i = 1;
foreach (var path in paths)
{
var queue = new Queue <UniqueFieldPath>();
queue.Enqueue(path);
while (queue.Count > 0)
{
var fields = queue.Dequeue();
fields.Alias = String.Concat("q", i++);
foreach (var f in fields)
{
queue.Enqueue(f);
}
}
}
}
/// <summary>
/// Generate a command using the current columns, target and parameters
/// </summary>
/// <param name="dbCommandType"></param>
/// <returns></returns>
public virtual void UpdateCommand(IDbCommand cmd)
{
Validate();
// compile all required field paths; this will ensure every table we need to hit will be present
List <IFieldPath> paths = new List <IFieldPath>();
paths.Add(new FieldPath(_target.IdField));
paths.AddRange(_columns);
paths.AddRange(_orderBy.ConvertAll <IFieldPath>(s => s.Path));
paths.AddRange(_groupBy);
SqlString where = null;
if (_where != null)
{
where = _where.ToSqlString().Flatten();
paths.AddRange(where.Parts.FindAll(o => o is IFieldPath).ConvertAll <IFieldPath>(o => (IFieldPath)o));
}
// add all field paths and calculate unique joins for all of them
var aliases = UniqueFieldPaths.FromPaths(paths);
AssignAliases(aliases);
var sb = new StringBuilder();
sb.AppendFormat("SELECT {0} FROM {1}", GetColumnDefinitions(aliases), GetFromClause(aliases));
cmd.Parameters.Clear();
if (where != null)
{
// loop over parts of Where clause
// if field, lookup alias and concat
// if string, just concat
// if parameter, add placeholder text, then add paramater to Command
var whereStr = new StringBuilder();
int paramIdx = 0;
foreach (var part in where.Parts)
{
if (part is IFieldPath)
{
whereStr.AppendFormat("{0}.[{1}]", aliases[(IFieldPath)part].Alias, ((IFieldPath)part).Last.SourceName);
}
else if (part is SqlStringParameter)
{
var p = (SqlStringParameter)part;
var cmdp = cmd.CreateParameter();
cmdp.ParameterName = String.Concat("@", paramIdx++);
cmdp.Value = p.Value;
cmd.Parameters.Add(cmdp);
whereStr.Append(cmdp.ParameterName);
}
else
{
whereStr.Append(part);
}
}
if (whereStr.Length > 0)
{
sb.Append(" WHERE ");
sb.Append(whereStr);
}
}
sb.Append(GetOrderByClause(aliases));
sb.Append(GetGroupByClause(aliases));
cmd.CommandText = sb.ToString();
}
