/// <summary> /// Search Sql alias including subqueries /// </summary> /// <param name="alias">Alias string</param> /// <returns>Sql Alias</returns> public ONSqlAlias GetOnSqlAliasInSql(string alias) { // owner from ONSqlAlias lOnSqlAlias = null; if (mFromAlias.ContainsKey(alias)) { return(mFromAlias[alias]); } // Search in subqueries foreach (object parameter in ParametersItem) { ONSqlSelect onSubSelect = parameter as ONSqlSelect; if (onSubSelect != null) { lOnSqlAlias = onSubSelect.GetOnSqlAliasInSql(alias); } if (lOnSqlAlias != null) { return(lOnSqlAlias); } } return(lOnSqlAlias); }
/// <summary> /// Constructor for LeftJoin sentences /// </summary> /// <param name="joinType">Type of join</param> /// <param name="fatherAlias">Father alias</param> /// <param name="alias">Alias name</param> /// <param name="table">Table name</param> /// <param name="onPath">Sql to solve</param> public ONSqlAlias(JoinType joinType, ONSqlAlias fatherAlias, string alias, string table, ONPath onPath, string facet) { JoiningType = joinType; Alias = alias; Table = table; OnPath = onPath; Facet = facet; FatherAlias = fatherAlias; if (FatherAlias != null) FatherAlias.ChildAlias.Add(this); }
/// <summary> /// Search table of the path and facet /// </summary> /// <param name="facet">Facet name</param> /// <param name="onPath">Path to solve</param> /// <returns>Table name</returns> public string GetTable(string facet, ONPath onPath) { ONSqlPath lOnSqlPath = new ONSqlPath(onPath, facet); ONSqlAlias lOnSqlAlias = GetOnSqlAlias(lOnSqlPath); // Path no exist if (lOnSqlAlias == null) { return(""); } return(lOnSqlAlias.Table); }
/// <summary> /// Search alias /// </summary> /// <param name="facet">Facet name</param> /// <param name="onPath">Path to solve</param> /// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param> /// <returns>Alias string</returns> public string GetAlias(string facet, ONPath onPath, bool isLinkedTo) { ONSqlPath lOnSqlPath = new ONSqlPath(onPath, facet, isLinkedTo); ONSqlAlias lOnSqlAlias = GetOnSqlAlias(lOnSqlPath); // Path no exist if (lOnSqlAlias == null) { return(""); } return(lOnSqlAlias.Alias); }
/// <summary> /// Constructor for LeftJoin sentences /// </summary> /// <param name="joinType">Type of join</param> /// <param name="fatherAlias">Father alias</param> /// <param name="alias">Alias name</param> /// <param name="table">Table name</param> /// <param name="onPath">Sql to solve</param> public ONSqlAlias(JoinType joinType, ONSqlAlias fatherAlias, string alias, string table, ONPath onPath, string facet) { JoiningType = joinType; Alias = alias; Table = table; OnPath = onPath; Facet = facet; FatherAlias = fatherAlias; if (FatherAlias != null) { FatherAlias.ChildAlias.Add(this); } }
/// <summary> /// Find or create an alias for this table with left/right join /// </summary> /// <param name="joinType">Type of join</param> /// <param name="fatherAlias">Father alias</param> /// <param name="table">Table</param> /// <param name="onPath">Path to solve</param> /// <param name="facet">Facet of the path to solve</param> /// <param name="force">Add the almost the last alias to the select</param> /// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param> /// <returns></returns> public string CreateAlias(JoinType joinType, string fatherAlias, string table, ONPath onPath, string facet, bool force, bool isLinkedTo) { ONSqlPath lOnSqlPath = new ONSqlPath(onPath, facet, isLinkedTo); ONSqlAlias lOnSqlAlias = GetOnSqlAlias(lOnSqlPath); if ((lOnSqlAlias == null) || (force && !mFrom.ContainsKey(lOnSqlPath))) { // Search father alias ONSqlAlias lOnSqlAliasFather = null; if (fatherAlias != "") { lOnSqlAliasFather = GetOnSqlAlias(fatherAlias); lOnSqlAlias = new ONSqlAlias(joinType, lOnSqlAliasFather, GenerateAlias(table), table, onPath, facet); } else { lOnSqlAlias = new ONSqlAlias(joinType, null, GenerateAlias(table), table, onPath, facet); } // Path no exist mFrom.Add(lOnSqlPath, lOnSqlAlias); mFromAlias.Add(lOnSqlAlias.Alias, lOnSqlAlias); } // Change the left join to inner join if it is needed if ((joinType == JoinType.InnerJoin) && (onPath != null)) { ONSqlAlias lTemp = lOnSqlAlias; do { if (mFrom.ContainsKey(lOnSqlPath)) { lTemp.JoiningType = JoinType.InnerJoin; lTemp = lTemp.FatherAlias; if (lTemp != null) { lOnSqlPath = new ONSqlPath(lTemp.OnPath, lTemp.Facet, isLinkedTo); } } else { lTemp = null; } }while (lTemp != null); } return(lOnSqlAlias.Alias); }
/// <summary> /// Search Sql alias /// </summary> /// <param name="alias">Alias string</param> /// <returns>Sql Alias</returns> public ONSqlAlias GetOnSqlAlias(string alias) { ONSqlAlias lOnSqlAlias = null; if (mFromAlias.ContainsKey(alias)) { lOnSqlAlias = mFromAlias[alias]; } if ((lOnSqlAlias == null) && (SuperQuery != null)) { return(SuperQuery.GetOnSqlAlias(alias)); } return(lOnSqlAlias); }
public override bool Equals(object onSqlAlias) { ONSqlAlias lOnSqlAlias = onSqlAlias as ONSqlAlias; string lAlias = onSqlAlias as string; if (lOnSqlAlias != null) { return(string.Compare(Alias, lOnSqlAlias.Alias, true) == 0); } if (lAlias != null) { return(string.Compare(Alias, lAlias, true) == 0); } return(false); }
/// <summary> /// Search Sql alias /// </summary> /// <param name="lOnSqlPath">Sql paht</param> /// <returns></returns> public ONSqlAlias GetOnSqlAlias(ONSqlPath lOnSqlPath) { ONSqlAlias lOnSqlAlias = null; foreach (KeyValuePair <ONSqlPath, ONSqlAlias> lFrom in mFrom) { if ((lFrom.Key.OnPath == lOnSqlPath.OnPath) && (lFrom.Key.Facet == lOnSqlPath.Facet) && ((lFrom.Key.IslinkedTo == lOnSqlPath.IslinkedTo) || (lOnSqlPath.IslinkedTo))) { return(lFrom.Value); } } if ((lOnSqlAlias == null) && (SuperQuery != null)) { return(SuperQuery.GetOnSqlAlias(lOnSqlPath)); } return(lOnSqlAlias); }
/// <summary> /// Generate Join of the Sql sentence /// </summary> /// <param name="onSqlAlias">Sql node in the LeftJoin Tree</param> /// <returns></returns> private void GenerateSQL_Join(ONSqlAlias onSqlAlias, StringBuilder from, StringBuilder where) { foreach (ONSqlAlias lOnSqlAliasChild in onSqlAlias.ChildAlias) { if (GetOnSqlAlias(lOnSqlAliasChild.Alias) != null) { if (lOnSqlAliasChild.JoiningType == JoinType.InnerJoin) { from.Append(" INNER JOIN "); } else { from.Append(" LEFT OUTER JOIN "); } from.Append(lOnSqlAliasChild.Table); from.Append(" "); from.Append(lOnSqlAliasChild.Alias); from.Append(" ON "); StringBuilder lWheres = new StringBuilder(); foreach (string lWhere in lOnSqlAliasChild.Wheres) { if (lWheres.Length != 0) { lWheres.Append(" AND "); } lWheres.Append("("); lWheres.Append(lWhere); lWheres.Append(")"); } from.Append(lWheres); GenerateSQL_Join(lOnSqlAliasChild, from, where); } } }
/// <summary> /// Find or create an alias for this table with left/right join /// </summary> /// <param name="joinType">Type of join</param> /// <param name="fatherAlias">Father alias</param> /// <param name="table">Table</param> /// <param name="onPath">Path to solve</param> /// <param name="facet">Facet of the path to solve</param> /// <param name="force">Add the almost the last alias to the select</param> /// <param name="isLinkedTo">The alias belongs to a role in a linked To element</param> /// <returns></returns> public string CreateAlias(JoinType joinType, string fatherAlias, string table, ONPath onPath, string facet, bool force, bool isLinkedTo) { ONSqlPath lOnSqlPath = new ONSqlPath(onPath, facet, isLinkedTo); ONSqlAlias lOnSqlAlias = GetOnSqlAlias(lOnSqlPath); if ((lOnSqlAlias == null) || (force && !mFrom.ContainsKey(lOnSqlPath))) { // Search father alias ONSqlAlias lOnSqlAliasFather = null; if (fatherAlias != "") { lOnSqlAliasFather = GetOnSqlAlias(fatherAlias); lOnSqlAlias = new ONSqlAlias(joinType, lOnSqlAliasFather, GenerateAlias(table), table, onPath, facet); } else lOnSqlAlias = new ONSqlAlias(joinType, null, GenerateAlias(table), table, onPath, facet); // Path no exist mFrom.Add(lOnSqlPath, lOnSqlAlias); mFromAlias.Add(lOnSqlAlias.Alias, lOnSqlAlias); } // Change the left join to inner join if it is needed if ((joinType == JoinType.InnerJoin) && (onPath != null)) { ONSqlAlias lTemp = lOnSqlAlias; do { if (mFrom.ContainsKey(lOnSqlPath)) { lTemp.JoiningType = JoinType.InnerJoin; lTemp = lTemp.FatherAlias; if(lTemp != null) lOnSqlPath = new ONSqlPath(lTemp.OnPath, lTemp.Facet, isLinkedTo); } else lTemp = null; } while (lTemp != null); } return lOnSqlAlias.Alias; }
/// <summary> /// Generate Join of the Sql sentence /// </summary> /// <param name="onSqlAlias">Sql node in the LeftJoin Tree</param> /// <returns></returns> private void GenerateSQL_Join(ONSqlAlias onSqlAlias, StringBuilder from, StringBuilder where) { foreach (ONSqlAlias lOnSqlAliasChild in onSqlAlias.ChildAlias) { if (GetOnSqlAlias(lOnSqlAliasChild.Alias) != null) { if (lOnSqlAliasChild.JoiningType == JoinType.InnerJoin) from.Append(" INNER JOIN "); else from.Append(" LEFT OUTER JOIN "); from.Append(lOnSqlAliasChild.Table); from.Append(" "); from.Append(lOnSqlAliasChild.Alias); from.Append(" ON "); StringBuilder lWheres = new StringBuilder(); foreach (string lWhere in lOnSqlAliasChild.Wheres) { if (lWheres.Length != 0) lWheres.Append(" AND "); lWheres.Append("("); lWheres.Append(lWhere); lWheres.Append(")"); } from.Append(lWheres); GenerateSQL_Join(lOnSqlAliasChild, from, where); } } }
/// <summary> /// Add where conjunctions to this alias /// </summary> /// <param name="alias">Alias string</param> /// <param name="wheres">Where conjuncions</param> public void AddAliasWhere(string alias, params string[] wheres) { ONSqlAlias lOnSqlAlias = GetOnSqlAlias(alias); lOnSqlAlias.Wheres.AddRange(wheres); }