public override ISqlNode VisitSelectStatementNode(SelectStatementNode node, object parameter)
{
if (Processed)
{
return(node);
}
if (RowNumberColumnSpecified)
{
return(base.VisitSelectStatementNode(node, parameter));
}
Processed = true;
var selectNode = new SelectClauseNode("select");
selectNode.AddNode(new FragmentNode(" sql_calc_found_rows"));
foreach (var child in node.SelectClauseNode.Children)
{
selectNode.AddNode(child);
}
var originalOrderBy = node.OrderByClauseNode;
OrderByClauseNode orderBy;
if (originalOrderBy != null)
{
orderBy = new OrderByClauseNode(originalOrderBy.WordNode);
foreach (var child in originalOrderBy.Children)
{
orderBy.AddNode(child);
}
}
else
{
orderBy = new OrderByClauseNode("");
}
var offset = Offset <= 0 ? "0" : Offset.ToString();
var limit = Limit <= 0 ? MaximumLimit : Limit.ToString();
orderBy.AddNode(new FragmentNode(" limit "));
orderBy.AddNode(new FragmentNode(offset));
orderBy.AddNode(new FragmentNode(", "));
orderBy.AddNode(new FragmentNode(limit));
var result = new SelectStatementNode();
// customized
//result.SelectClauseNode = node.SelectClauseNode;
result.SelectClauseNode = selectNode;
result.FromClauseNode = node.FromClauseNode;
result.WhereClauseNode = node.WhereClauseNode;
result.GroupByClauseNode = node.GroupByClauseNode;
result.HavingClauseNode = node.HavingClauseNode;
result.OrderByClauseNode = orderBy;
result.ForUpdateClauseNode = node.ForUpdateClauseNode;
result.OptionClauseNode = node.OptionClauseNode;
return(result);
}
public override ISqlNode VisitSelectStatementNode(SelectStatementNode node, object parameter)
{
if (Processed)
{
return(node);
}
if (RowNumberColumnSpecified)
{
return(base.VisitSelectStatementNode(node, parameter));
}
Processed = true;
if (!_forceOffsetFetch && Offset <= 0)
{
return(AddTopNode(node));
}
var originalOrderBy = node.OrderByClauseNode;
if (originalOrderBy == null)
{
throw new SqlTransformException(ExceptionMessageId.Esp2201);
}
var orderBy = new OrderByClauseNode(originalOrderBy.WordNode);
foreach (var child in originalOrderBy.Children)
{
orderBy.AddNode(child);
}
var offset = Offset <= 0 ? "0" : Offset.ToString();
orderBy.AddNode(new FragmentNode(" offset "));
orderBy.AddNode(new FragmentNode(offset));
orderBy.AddNode(new FragmentNode(" rows"));
if (Limit > 0)
{
orderBy.AddNode(new FragmentNode(" fetch next "));
orderBy.AddNode(new FragmentNode(Limit.ToString()));
orderBy.AddNode(new FragmentNode(" rows only"));
}
var result = new SelectStatementNode();
result.SelectClauseNode = node.SelectClauseNode;
result.FromClauseNode = node.FromClauseNode;
result.WhereClauseNode = node.WhereClauseNode;
result.GroupByClauseNode = node.GroupByClauseNode;
result.HavingClauseNode = node.HavingClauseNode;
result.OrderByClauseNode = orderBy;
result.ForUpdateClauseNode = node.ForUpdateClauseNode;
result.OptionClauseNode = node.OptionClauseNode;
return(result);
}
public object VisitOrderByClauseNode(OrderByClauseNode node, Context parameter)
{
var wordNode = node.WordNode;
wordNode.Accept(this, parameter);
foreach (var child in node.Children)
{
child.Accept(this, parameter);
}
return(null);
}
public override ISqlNode VisitSelectStatementNode(SelectStatementNode node, object parameter)
{
if (Processed)
{
return(node);
}
if (RowNumberColumnSpecified)
{
return(base.VisitSelectStatementNode(node, parameter));
}
Processed = true;
var originalOrderBy = node.OrderByClauseNode;
OrderByClauseNode orderBy;
if (originalOrderBy != null)
{
orderBy = new OrderByClauseNode(originalOrderBy.WordNode);
foreach (var child in originalOrderBy.Children)
{
orderBy.AddNode(child);
}
}
else
{
orderBy = new OrderByClauseNode("");
}
if (Limit > 0)
{
orderBy.AddNode(new FragmentNode(" limit "));
orderBy.AddNode(new FragmentNode(Limit.ToString()));
}
if (Offset >= 0)
{
orderBy.AddNode(new FragmentNode(" offset "));
orderBy.AddNode(new FragmentNode(Offset.ToString()));
}
var result = new SelectStatementNode();
result.SelectClauseNode = node.SelectClauseNode;
result.FromClauseNode = node.FromClauseNode;
result.WhereClauseNode = node.WhereClauseNode;
result.GroupByClauseNode = node.GroupByClauseNode;
result.HavingClauseNode = node.HavingClauseNode;
result.OrderByClauseNode = orderBy;
result.ForUpdateClauseNode = node.ForUpdateClauseNode;
result.OptionClauseNode = node.OptionClauseNode;
return(result);
}
private void ParseOrderByWord()
{
Validate();
var node = new OrderByClauseNode(_token);
if (IsInSelectStatementNode())
{
RemoveNodesTo <SelectStatementNode>();
var selectStatementNode = (SelectStatementNode)Peek();
selectStatementNode.OrderByClauseNode = node;
}
else
{
AppendNode(node);
}
_nodeStack.Push(node);
}
public override ISqlNode VisitSelectStatementNode(SelectStatementNode node, object parameter)
{
if (Processed)
{
return(node);
}
if (Offset > 0)
{
return(base.VisitSelectStatementNode(node, parameter));
}
Processed = true;
var originalOrderBy = node.OrderByClauseNode;
OrderByClauseNode orderBy;
if (originalOrderBy != null)
{
orderBy = new OrderByClauseNode(originalOrderBy.WordNode);
foreach (var child in originalOrderBy.Children)
{
orderBy.AddNode(child);
}
}
else
{
orderBy = new OrderByClauseNode("");
}
orderBy.AddNode(new FragmentNode($" fetch first {Limit} rows only"));
var result = new SelectStatementNode();
result.SelectClauseNode = node.SelectClauseNode;
result.FromClauseNode = node.FromClauseNode;
result.WhereClauseNode = node.WhereClauseNode;
result.GroupByClauseNode = node.GroupByClauseNode;
result.HavingClauseNode = node.HavingClauseNode;
result.OrderByClauseNode = orderBy;
result.ForUpdateClauseNode = node.ForUpdateClauseNode;
result.OptionClauseNode = node.OptionClauseNode;
return(result);
}
public override ISqlNode VisitSelectStatementNode(SelectStatementNode node, object parameter)
{
if (Processed)
{
return(node);
}
Processed = true;
var originalOrderBy = node.OrderByClauseNode;
if (originalOrderBy == null)
{
throw new SqlTransformException(ExceptionMessageId.Esp2201);
}
var subStatement = new SelectStatementNode();
subStatement.SelectClauseNode = node.SelectClauseNode;
subStatement.FromClauseNode = node.FromClauseNode;
subStatement.WhereClauseNode = node.WhereClauseNode;
subStatement.GroupByClauseNode = node.GroupByClauseNode;
subStatement.HavingClauseNode = node.HavingClauseNode;
var orderBy = new OrderByClauseNode(originalOrderBy.WordNode);
foreach (var child in originalOrderBy.Children)
{
if (child is WordNode wordNode)
{
var word = wordNode.Word;
var names = word.Split('.');
if (names.Length == 2)
{
orderBy.AddNode(new WordNode("temp_." + names[1]));
}
else
{
orderBy.AddNode(child);
}
}
else
{
orderBy.AddNode(child);
}
}
var selectNode = new SelectClauseNode("select");
selectNode.AddNode(new FragmentNode(" * "));
var fromNode = new FromClauseNode("from");
fromNode.AddNode(new FragmentNode(" ( select temp_.*, row_number() over( "));
fromNode.AddNode(orderBy);
fromNode.AddNode(new FragmentNode($" ) as {RowNumberColumnName} from ( "));
fromNode.AddNode(subStatement);
fromNode.AddNode(new FragmentNode(") as temp_ ) as temp2_ "));
var whereNode = new WhereClauseNode("where");
whereNode.AddNode(new FragmentNode(" "));
if (Offset >= 0)
{
whereNode.AddNode(new FragmentNode($"{RowNumberColumnName} > "));
whereNode.AddNode(new FragmentNode(Offset.ToString()));
}
if (Limit > 0)
{
if (Offset >= 0)
{
whereNode.AddNode(new FragmentNode(" and "));
}
var bias = Offset < 0 ? 0 : Offset;
whereNode.AddNode(new FragmentNode($"{RowNumberColumnName} <= "));
whereNode.AddNode(new FragmentNode((bias + Limit).ToString()));
}
var result = new SelectStatementNode();
result.SelectClauseNode = selectNode;
result.FromClauseNode = fromNode;
result.WhereClauseNode = whereNode;
result.ForUpdateClauseNode = node.ForUpdateClauseNode;
result.OptionClauseNode = node.OptionClauseNode;
return(result);
}
