/// <summary> /// Returns a string "table1 join table2 on x = y join table3 on a = b" /// </summary> /// <param name="paths"></param> /// <returns></returns> protected override string GetFromClause(UniqueFieldPaths paths) { throw new NotImplementedException(); }
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> /// 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(); }
/// <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 "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 "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(); }