protected override DbCommand ParseSelect(APSqlSelectCommand command)
{
StringBuilder sb = new StringBuilder();
OracleCommand dbCmd = new OracleCommand();
using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable))
{
if ((command.Skip ?? 0) > 0)
{
writer.WriteDirect("SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM (");
writer.WriteLine();
ParseSelectInternal(command, dbCmd, writer);
writer.Idented = 0;
writer.WriteLine();
writer.WriteDirect(String.Format(") query_alias {0} ) WHERE query_rownum > {1}",
(command.Take ?? 0) > 0 ? "WHERE ROWNUM <= " + (command.Take.Value + command.Skip.Value) : "",
command.Skip.Value));
}
else
{
ParseSelectInternal(command, dbCmd, writer, command.Take);
}
}
dbCmd.CommandText = sb.ToString();
return(dbCmd);
}
private void WriteSet(ParserWriter writer, APSqlValuesClause clause, SqlCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("SET");
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
writer.Write(phrase.AssignmentExpr.SelectExpr);
writer.Write("=");
if (!TryWriteValue(writer, phrase.Value))
{
string paramName = writer.GetSuitableParameterName(phrase.ParamName);
writer.Write("@" + paramName);
AddParameter(dbCmd, paramName, phrase.Value, ParameterDirection.Input);
}
phrase = phrase.Next as APSqlSetPhrase;
}
}
}
private void WriteHaving(ParserWriter writer, APSqlWhereClause clause, OracleCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("HAVING");
APSqlWherePhrase phrase = clause.Next as APSqlWherePhrase;
if (phrase.Next == null)
{
if (phrase is APSqlConditionAndPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd);
return;
}
else if (phrase is APSqlConditionOrPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd);
return;
}
}
WriteConditionPhrase(writer, phrase, APSqlConditionJoinType.AND, dbCmd);
}
}
private void WriteSelect(ParserWriter writer, APSqlSelectCommand command)
{
writer.WriteDirect("SELECT");
if (command.SelectMode == APSqlSelectMode.DISTINCT)
{
writer.Write("DISTINCT");
}
APSqlSelectPhrase phrase = command.SelectExprClause.Next as APSqlSelectPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
WriteSelectExpression(writer, phrase.Expr);
if (!string.IsNullOrEmpty(phrase.Alias))
{
writer.Write("AS " + JudgeAliasName(phrase.Alias));
}
phrase = phrase.Next as APSqlSelectPhrase;
}
}
private void WriteGroupBy(ParserWriter writer, APSqlGroupByClause clause, OracleCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("GROUP BY");
APSqlExprPhrase phrase = clause.Next as APSqlExprPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
WriteSelectExpression(writer, phrase.Expr);
//writer.Write(phrase.Expr.SelectExpr);
phrase = phrase.Next as APSqlExprPhrase;
}
}
}
private void WriteInsert(ParserWriter writer, APSqlInsertCommand command, APSqlValuesClause clause)
{
writer.WriteDirect("INSERT INTO");
writer.Write(command.TableDef.TableName);
if (clause != null && clause.Next != null)
{
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
writer.Write("(");
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
writer.Write(phrase.AssignmentExpr.SelectExpr);
phrase = phrase.Next as APSqlSetPhrase;
}
writer.Write(")");
}
}
private void WriteInsertWithSubQuery(ParserWriter writer, APSqlInsertCommand command)
{
writer.WriteDirect("INSERT INTO");
writer.Write(command.TableDef.TableName);
if (command.SelectClause != null)
{
writer.Write("(");
APSqlSelectPhrase phrase = command.SelectClause.Next as APSqlSelectPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
WriteSelectExpression(writer, phrase.Expr);
phrase = phrase.Next as APSqlSelectPhrase;
}
writer.Write(")");
}
}
private void WriteOrderBy(ParserWriter writer, APSqlOrderByClause clause)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("ORDER BY");
APSqlOrderPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
WriteSelectExpression(writer, phrase.Expr);
if (phrase.OrderAccording == APSqlOrderAccording.Desc)
{
writer.Write("DESC");
}
phrase = phrase.Next as APSqlOrderPhrase;
}
}
}
private void WriteSelect(ParserWriter writer, APSqlSelectCommand command, int size)
{
writer.WriteDirect("SELECT");
if (command.SelectMode == APSqlSelectMode.DISTINCT)
writer.Write("DISTINCT");
if (size > 0)
writer.Write("TOP " + size);
if (command.SelectExprClause != null)
{
APSqlSelectPhrase phrase = command.SelectExprClause.Next as APSqlSelectPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
WriteSelectExpression(writer, phrase.Expr);
if (!string.IsNullOrEmpty(phrase.Alias))
writer.Write("AS " + JudgeAliasName(phrase.Alias));
phrase = phrase.Next as APSqlSelectPhrase;
}
}
}
private void WriteValues(ParserWriter writer, APSqlValuesClause clause, OracleCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("VALUES");
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
writer.Write('(');
while (phrase != null)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
if (!TryWriteValue(writer, phrase.Value))
{
string paramName = "v_" + writer.GetSuitableParameterName(phrase.ParamName);
writer.Write(":" + paramName);
AddParameter(dbCmd, paramName, phrase.Value, ParameterDirection.Input);
}
phrase = phrase.Next as APSqlSetPhrase;
}
writer.Write(')');
}
}
protected override DbCommand ParseSelect(APSqlSelectCommand command)
{
StringBuilder sb = new StringBuilder();
SqlCommand dbCmd = new SqlCommand();
using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable))
{
WriteSelect(writer, command, command.Take ?? 0);
writer.Idented++;
WriteFrom(writer, command.FromClause, dbCmd);
WriteWhere(writer, command.WhereClause, dbCmd);
if (command.Skip != null && command.Skip > 0)
{
writer.WriteLine();
if (command.WhereClause == null || command.WhereClause.Next == null)
{
writer.WriteDirect("WHERE");
}
else
{
writer.WriteDirect("AND");
}
writer.Write(command.PrimeryKeyExpr.SelectExpr);
writer.Write("NOT IN (");
{
// sub query
WriteSelect(writer, new APSqlSelectCommand(command.PrimeryKeyExpr), command.Skip.Value);
writer.Idented++;
WriteFrom(writer, command.FromClause, dbCmd);
WriteWhere(writer, command.WhereClause, dbCmd);
WriteGroupBy(writer, command.GroupByClause, dbCmd);
WriteHaving(writer, command.HavingClause, dbCmd);
WriteOrderBy(writer, command.OrderByClause);
writer.Idented--;
}
writer.Write(")");
}
WriteGroupBy(writer, command.GroupByClause, dbCmd);
WriteHaving(writer, command.HavingClause, dbCmd);
WriteOrderBy(writer, command.OrderByClause);
}
dbCmd.CommandText = sb.ToString();
return(dbCmd);
}
private void WriteFrom(ParserWriter writer, APSqlFromClause clause, OracleCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("FROM");
APSqlFromPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (phrase.JoinType == APSqlJoinType.None)
{
if (!isFirst)
{
writer.Write(',');
}
else
{
isFirst = false;
}
writer.Write(phrase.TableName);
if (phrase.AliasName != null)
{
writer.Write(phrase.AliasName);
}
}
else
{
writer.Write(GetJoinTypeString(phrase.JoinType));
writer.Write(phrase.TableName);
if (phrase.AliasName != null)
{
writer.Write(phrase.AliasName);
}
if (phrase.JoinType != APSqlJoinType.Cross && phrase.JoinOnPhrase != null)
{
WriteJoinOn(writer, phrase.JoinOnPhrase, dbCmd);
}
}
phrase = phrase.Next as APSqlFromPhrase;
}
}
}
private void WriteWhere(ParserWriter writer, APSqlWhereClause clause, OracleCommand dbCmd, int?maxReturnCount = null)
{
bool standardWhere = clause != null && clause.Next != null;
if (standardWhere || maxReturnCount != null)
{
writer.WriteLine();
writer.WriteDirect("WHERE");
if (standardWhere)
{
APSqlWherePhrase phrase = clause.Next as APSqlWherePhrase;
if (phrase.Next == null)
{
if (phrase is APSqlConditionAndPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd);
return;
}
else if (phrase is APSqlConditionOrPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd);
return;
}
}
WriteConditionPhrase(writer, phrase, APSqlConditionJoinType.AND, dbCmd);
}
if (maxReturnCount != null)
{
if (standardWhere)
{
writer.Write("AND");
}
writer.Write("ROWNUM <= " + maxReturnCount.Value);
}
}
}
private void WriteSet(ParserWriter writer, APSqlValuesClause clause, OracleCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("SET");
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
writer.Write(phrase.AssignmentExpr.SelectExpr);
writer.Write("=");
if (!TryWriteValue(writer, phrase.Value))
{
string paramName = "v_" + writer.GetSuitableParameterName(phrase.ParamName);
writer.Write(":" + paramName);
AddParameter(dbCmd, paramName, phrase.Value, ParameterDirection.Input);
}
phrase = phrase.Next as APSqlSetPhrase;
}
}
}
private void WriteUpdate(ParserWriter writer, APSqlUpdateCommand command)
{
writer.WriteDirect("UPDATE");
writer.Write(command.TableDef.TableName);
}
private void WriteInsertWithSubQuery(ParserWriter writer, APSqlInsertCommand command)
{
writer.WriteDirect("INSERT INTO");
writer.Write(command.TableDef.TableName);
if (command.SelectClause != null)
{
writer.Write("(");
APSqlSelectPhrase phrase = command.SelectClause.Next as APSqlSelectPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
WriteSelectExpression(writer, phrase.Expr);
phrase = phrase.Next as APSqlSelectPhrase;
}
writer.Write(")");
}
}
private void WriteInsert(ParserWriter writer, APSqlInsertCommand command, APSqlValuesClause clause)
{
writer.WriteDirect("INSERT INTO");
writer.Write(command.TableDef.TableName);
if (clause != null && clause.Next != null)
{
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
writer.Write("(");
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
writer.Write(phrase.AssignmentExpr.SelectExpr);
phrase = phrase.Next as APSqlSetPhrase;
}
writer.Write(")");
}
}
private void WriteDelete(ParserWriter writer, APSqlDeleteCommand command)
{
writer.WriteDirect("DELETE FROM");
writer.Write(command.TableDef.TableName);
}
private void WriteUpdate(ParserWriter writer, APSqlUpdateCommand command)
{
writer.WriteDirect("UPDATE");
writer.Write(command.TableDef.TableName);
}
private void WriteDelete(ParserWriter writer, APSqlDeleteCommand command)
{
writer.WriteDirect("DELETE FROM");
writer.Write(command.TableDef.TableName);
}
private void WriteFrom(ParserWriter writer, APSqlFromClause clause, SqlCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("FROM");
APSqlFromPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (phrase.JoinType == APSqlJoinType.None)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
writer.Write(phrase.TableName);
if (phrase.AliasName != null)
writer.Write("AS " + phrase.AliasName);
}
else
{
writer.Write(GetJoinTypeString(phrase.JoinType));
writer.Write(phrase.TableName);
if (phrase.AliasName != null)
writer.Write("AS " + phrase.AliasName);
if (phrase.JoinType != APSqlJoinType.Cross && phrase.JoinOnPhrase != null)
{
WriteJoinOn(writer, phrase.JoinOnPhrase, dbCmd);
}
}
phrase = phrase.Next as APSqlFromPhrase;
}
}
}
protected override DbCommand ParseSelect(APSqlSelectCommand command)
{
StringBuilder sb = new StringBuilder();
OracleCommand dbCmd = new OracleCommand();
using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable))
{
if ((command.Skip ?? 0) > 0)
{
writer.WriteDirect("SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM (");
writer.WriteLine();
ParseSelectInternal(command, dbCmd, writer);
writer.Idented = 0;
writer.WriteLine();
writer.WriteDirect(String.Format(") query_alias {0} ) WHERE query_rownum > {1}",
(command.Take ?? 0) > 0 ? "WHERE ROWNUM <= " + (command.Take.Value + command.Skip.Value) : "",
command.Skip.Value));
}
else
{
ParseSelectInternal(command, dbCmd, writer, command.Take);
}
}
dbCmd.CommandText = sb.ToString();
return dbCmd;
}
private void WriteOrderBy(ParserWriter writer, APSqlOrderByClause clause)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("ORDER BY");
APSqlOrderPhrase phrase = clause.Next;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
WriteSelectExpression(writer, phrase.Expr);
if (phrase.OrderAccording == APSqlOrderAccording.Desc)
writer.Write("DESC");
phrase = phrase.Next as APSqlOrderPhrase;
}
}
}
private void WriteValues(ParserWriter writer, APSqlValuesClause clause, SqlCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("VALUES");
APSqlSetPhrase phrase = clause.Next;
bool isFirst = true;
writer.Write('(');
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
if (!TryWriteValue(writer, phrase.Value))
{
string paramName = writer.GetSuitableParameterName(phrase.ParamName);
writer.Write("@" + paramName);
AddParameter(dbCmd, paramName, phrase.Value, ParameterDirection.Input);
}
phrase = phrase.Next as APSqlSetPhrase;
}
writer.Write(')');
}
}
private void WriteGroupBy(ParserWriter writer, APSqlGroupByClause clause, SqlCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("GROUP BY");
APSqlExprPhrase phrase = clause.Next as APSqlExprPhrase;
bool isFirst = true;
while (phrase != null)
{
if (!isFirst)
writer.Write(',');
else
isFirst = false;
WriteSelectExpression(writer, phrase.Expr);
//writer.Write(phrase.Expr.SelectExpr);
phrase = phrase.Next as APSqlExprPhrase;
}
}
}
protected override DbCommand ParseSelect(APSqlSelectCommand command)
{
StringBuilder sb = new StringBuilder();
SqlCommand dbCmd = new SqlCommand();
using (ParserWriter writer = new ParserWriter(sb, command.CommandNameSuitable))
{
WriteSelect(writer, command, command.Take ?? 0);
writer.Idented++;
WriteFrom(writer, command.FromClause, dbCmd);
WriteWhere(writer, command.WhereClause, dbCmd);
if (command.Skip != null && command.Skip > 0)
{
writer.WriteLine();
if (command.WhereClause == null || command.WhereClause.Next == null)
{
writer.WriteDirect("WHERE");
}
else
{
writer.WriteDirect("AND");
}
writer.Write(command.PrimeryKeyExpr.SelectExpr);
writer.Write("NOT IN (");
{
// sub query
WriteSelect(writer, new APSqlSelectCommand(command.PrimeryKeyExpr), command.Skip.Value);
writer.Idented++;
WriteFrom(writer, command.FromClause, dbCmd);
WriteWhere(writer, command.WhereClause, dbCmd);
WriteGroupBy(writer, command.GroupByClause, dbCmd);
WriteHaving(writer, command.HavingClause, dbCmd);
WriteOrderBy(writer, command.OrderByClause);
writer.Idented--;
}
writer.Write(")");
}
WriteGroupBy(writer, command.GroupByClause, dbCmd);
WriteHaving(writer, command.HavingClause, dbCmd);
WriteOrderBy(writer, command.OrderByClause);
}
dbCmd.CommandText = sb.ToString();
return dbCmd;
}
private void WriteHaving(ParserWriter writer, APSqlWhereClause clause, SqlCommand dbCmd)
{
if (clause != null && clause.Next != null)
{
writer.WriteLine();
writer.WriteDirect("HAVING");
APSqlWherePhrase phrase = clause.Next as APSqlWherePhrase;
if (phrase.Next == null)
{
if (phrase is APSqlConditionAndPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd);
return;
}
else if (phrase is APSqlConditionOrPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd);
return;
}
}
WriteConditionPhrase(writer, phrase, APSqlConditionJoinType.AND, dbCmd);
}
}
private void WriteWhere(ParserWriter writer, APSqlWhereClause clause, OracleCommand dbCmd, int? maxReturnCount = null)
{
bool standardWhere = clause != null && clause.Next != null;
if (standardWhere || maxReturnCount != null)
{
writer.WriteLine();
writer.WriteDirect("WHERE");
if (standardWhere)
{
APSqlWherePhrase phrase = clause.Next as APSqlWherePhrase;
if (phrase.Next == null)
{
if (phrase is APSqlConditionAndPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionAndPhrase).Child, APSqlConditionJoinType.AND, dbCmd);
return;
}
else if (phrase is APSqlConditionOrPhrase)
{
WriteConditionPhrase(writer, (phrase as APSqlConditionOrPhrase).Child, APSqlConditionJoinType.OR, dbCmd);
return;
}
}
WriteConditionPhrase(writer, phrase, APSqlConditionJoinType.AND, dbCmd);
}
if (maxReturnCount != null)
{
if (standardWhere)
writer.Write("AND");
writer.Write("ROWNUM <= " + maxReturnCount.Value);
}
}
}