/// <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.Sql, 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 var mquery = _configuration[_target, ufp.Subject].Query; if (String.IsNullOrEmpty(mquery)) { throw new IncompleteConfigurationException(String.Format("Unable to construct query. Missing relationship between {0} and {1}", _target.DisplayName, ufp.Subject.DisplayName)); } 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, mquery, ufp.Alias, ufp.Subject.Sql, _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}] ", ((ISqlSubject)field.Subject).Sql, 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()); }
/// <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()); }
/// <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 = (ISqlSubject)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); } return(root); }
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(); }