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);
}
Processed = true;
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 selectNode = new SelectClauseNode("select");
selectNode.AddNode(new FragmentNode(" count(*) "));
var fromNode = new FromClauseNode("from");
fromNode.AddNode(new FragmentNode(" ( "));
fromNode.AddNode(subStatement);
fromNode.AddNode(new FragmentNode(") t_"));
var result = new SelectStatementNode();
result.SelectClauseNode = selectNode;
result.FromClauseNode = fromNode;
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;
var subStatement = new SelectStatementNode();
subStatement.SelectClauseNode = node.SelectClauseNode;
subStatement.FromClauseNode = node.FromClauseNode;
subStatement.WhereClauseNode = node.WhereClauseNode;
subStatement.GroupByClauseNode = node.GroupByClauseNode;
subStatement.HavingClauseNode = node.HavingClauseNode;
subStatement.OrderByClauseNode = node.OrderByClauseNode;
var selectNode = new SelectClauseNode("select");
selectNode.AddNode(new FragmentNode(" * "));
var fromNode = new FromClauseNode("from");
fromNode.AddNode(new FragmentNode($" ( select temp_.*, rownum {RowNumberColumnName} from ( "));
fromNode.AddNode(subStatement);
fromNode.AddNode(new FragmentNode(" ) temp_ ) "));
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 "));
}
whereNode.AddNode(new FragmentNode($"{RowNumberColumnName} <= "));
var bias = Offset < 0 ? 0 : Offset;
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);
}
protected ISqlNode AddTopNode(SelectStatementNode node)
{
var selectNode = new SelectClauseNode(node.SelectClauseNode.WordNode);
selectNode.AddNode(new FragmentNode($" top ({Limit})"));
foreach (var child in node.SelectClauseNode.Children)
{
selectNode.AddNode(child);
}
var result = new SelectStatementNode();
result.SelectClauseNode = selectNode;
result.FromClauseNode = node.FromClauseNode;
result.WhereClauseNode = node.WhereClauseNode;
result.GroupByClauseNode = node.GroupByClauseNode;
result.HavingClauseNode = node.HavingClauseNode;
result.OrderByClauseNode = node.OrderByClauseNode;
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 selectNode = new SelectClauseNode("select");
selectNode.AddNode(new FragmentNode(" found_rows()"));
var result = new SelectStatementNode();
result.SelectClauseNode = selectNode;
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);
}