/// <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);
}
