/// <summary>
/// Write out the TOP part of sql select statement
/// It basically writes LIMIT (X).
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write(" FIRST ");
this.TopCount.WriteSql(writer, sqlGenerator);
if (this.WithTies)
{
throw new NotSupportedException("WITH TIES");
}
writer.Write(" ");
//if (this.WithTies)
//{
// writer.Write("WITH TIES ");
//}
}
/// <summary>
/// Write this symbol out as a string for sql. This is just
/// the new name of the symbol (which could be the same as the old name).
///
/// We rename columns here if necessary.
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
if (this.NeedsRenaming)
{
string newName;
int i = sqlGenerator.AllColumnNames[this.NewName];
do
{
++i;
newName = this.Name + i.ToString(System.Globalization.CultureInfo.InvariantCulture);
} while (sqlGenerator.AllColumnNames.ContainsKey(newName));
sqlGenerator.AllColumnNames[this.NewName] = i;
// Prevent it from being renamed repeatedly.
this.NeedsRenaming = false;
this.NewName = newName;
// Add this column name to list of known names so that there are no subsequent
// collisions
sqlGenerator.AllColumnNames[newName] = 0;
}
writer.Write(SqlGenerator.QuoteIdentifier(this.NewName));
}
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
// Symbol pair should never be part of a SqlBuilder.
Debug.Assert(false);
}
/// <summary>
/// Write out a SQL select statement as a string.
/// We have to
/// <list type="number">
/// <item>Check whether the aliases extents we use in this statement have
/// to be renamed.
/// We first create a list of all the aliases used by the outer extents.
/// For each of the FromExtents( or AllJoinExtents if it is non-null),
/// rename it if it collides with the previous list.
/// </item>
/// <item>Write each of the clauses (if it exists) as a string</item>
/// </list>
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
#region Check if FROM aliases need to be renamed
// Create a list of the aliases used by the outer extents
// JoinSymbols have to be treated specially.
List <string> outerExtentAliases = null;
if ((null != outerExtents) && (0 < outerExtents.Count))
{
foreach (Symbol outerExtent in outerExtents.Keys)
{
JoinSymbol joinSymbol = outerExtent as JoinSymbol;
if (joinSymbol != null)
{
foreach (Symbol symbol in joinSymbol.FlattenedExtentList)
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(symbol.NewName);
}
}
else
{
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(outerExtent.NewName);
}
}
}
// An then rename each of the FromExtents we have
// If AllJoinExtents is non-null - it has precedence.
// The new name is derived from the old name - we append an increasing int.
List <Symbol> extentList = this.AllJoinExtents ?? this.fromExtents;
if (null != extentList)
{
foreach (Symbol fromAlias in extentList)
{
if ((null != outerExtentAliases) && outerExtentAliases.Contains(fromAlias.Name))
{
int i = sqlGenerator.AllExtentNames[fromAlias.Name];
string newName;
do
{
++i;
newName = fromAlias.Name + i.ToString(System.Globalization.CultureInfo.InvariantCulture);
}while (sqlGenerator.AllExtentNames.ContainsKey(newName));
sqlGenerator.AllExtentNames[fromAlias.Name] = i;
fromAlias.NewName = newName;
// Add extent to list of known names (although i is always incrementing, "prefix11" can
// eventually collide with "prefix1" when it is extended)
sqlGenerator.AllExtentNames[newName] = 0;
}
// Add the current alias to the list, so that the extents
// that follow do not collide with me.
if (null == outerExtentAliases)
{
outerExtentAliases = new List <string>();
}
outerExtentAliases.Add(fromAlias.NewName);
}
}
#endregion
// Increase the indent, so that the Sql statement is nested by one tab.
writer.Indent += 1; // ++ can be confusing in this context
writer.Write("SELECT ");
if (Top != null && Skip == null) // " FIRST " <TopCount>
{
// SELECT FIRST <TopCount> cannot be used in the same SELECT
// statement as the OFFSET <SkipCount> clause, so defer (Top, Skip)
// to OFFSET <SkipCount> FETCH NEXT <TopCount> ROWS ONLY.
// FIRST <n> is only supported on the outermost SELECT,
// else error "'FIRST n' clause is only allowed at the outermost level
// of a SELECT query, not in subselects, view definitions, or CTE's.".
// The TopMost checks will be done at the end of this method
Top.WriteSql(writer, sqlGenerator); // " FIRST " <TopCount>
}
if (IsDistinct)
{
writer.Write("DISTINCT ");
}
if ((null == this.select) || this.Select.IsEmpty)
{
Debug.Assert(false); // we have removed all possibilities of SELECT *.
writer.Write("*");
}
else
{
this.Select.WriteSql(writer, sqlGenerator);
}
writer.WriteLine();
writer.Write("FROM ");
this.From.WriteSql(writer, sqlGenerator);
if ((null != this.where) && !this.Where.IsEmpty)
{
writer.WriteLine();
writer.Write("WHERE ");
this.Where.WriteSql(writer, sqlGenerator);
}
if ((null != this.groupBy) && !this.GroupBy.IsEmpty)
{
writer.WriteLine();
writer.Write("GROUP BY ");
this.GroupBy.WriteSql(writer, sqlGenerator);
}
if ((null != this.orderBy) && // write the ORDER BY unless inner SELECT
!this.OrderBy.IsEmpty &&
this.IsTopMost)
{
writer.WriteLine();
writer.Write("ORDER BY ");
this.OrderBy.WriteSql(writer, sqlGenerator);
}
if (this.Skip != null) // OFFSET <SkipCount>
{ // FETCH NEXT <TopCount> ROWS ONLY
this.Skip.WriteSql(writer, sqlGenerator);
if (this.Top != null)
{
writer.Write(" FETCH NEXT ");
this.Top.TopCount.WriteSql(writer, sqlGenerator);
writer.Write(" ROWS ONLY ");
}
}
--writer.Indent;
if (IsTopMost == false) // if inner query, disallow Top and Skip
{ // because not supported by inner query
if (Top != null)
{
throw new System.NotSupportedException(
"Queryable methods First() and Take() are not supported and " +
"cannot be mapped into a FIRST <topCount> " +
"clause of an SQL subselect.");
}
if (Skip != null)
{
throw new System.NotSupportedException(
"Queryable method Skip() is not supported and " +
"cannot be mapped into an OFFSET <skipCount> " +
"clause of an SQL subselect.");
}
} // end if (IsTopMost == false) // if in inside inner query
}
/// <summary>
/// Write out the SKIP part of sql select statement
/// It basically writes OFFSET (X).
/// </summary>
/// <param name="writer"></param>
/// <param name="sqlGenerator"></param>
public void WriteSql(SqlWriter writer, SqlGenerator sqlGenerator)
{
writer.Write(" OFFSET ");
this.SkipCount.WriteSql(writer, sqlGenerator);
}
