public void BasicTest_ORDERBY()
{
SelectStmtInfo stmtInfo = new SelectStmtInfo();
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userId"
}
});
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userName"
}
});
stmtInfo.TableDescriptor = new TableDescriptor()
{
TableName = "me", TableReadType = TableReadType.NONE
};
stmtInfo.OrderBy.Add(new OrderByCondition()
{
Expression = "userId", Direction = OrderByDirection.ASC
});
IGenerator g = new DefaultSqlGenerator();
var sql = g.Generate(stmtInfo);
Assert.AreEqual("SELECT userId, userName FROM me ORDER BY userId ASC", sql);
}
public string Generate(SelectStmtInfo stmtInfo)
{
string predictWord = "";
string columnSql = "";
string tableName = "";
string orderBySql = "";
string whereSql = "";
string finalSql = "";
predictWord = Generate_PredictWord(stmtInfo);
tableName = Generate_TableName(stmtInfo);
columnSql = Generate_ColumnSql(stmtInfo);
orderBySql = Generate_OrderBySql(stmtInfo);
whereSql = Generate_WhereSql(stmtInfo);
if (string.IsNullOrEmpty(predictWord))
{
finalSql = string.Format("SELECT {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql);
}
else
{
finalSql = string.Format("SELECT {0} {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql);
}
return(finalSql);
}
private static string Generate_TableName(SelectStmtInfo stmtInfo)
{
string sql = stmtInfo.TableDescriptor.TableName;
switch (stmtInfo.TableDescriptor.TableReadType)
{
case TableReadType.NONE:
//Nothing to do
break;
case TableReadType.NOLOCK:
sql += "(NOLOCK)";
break;
case TableReadType.READPAST:
sql += "(READPAST)";
break;
default:
throw new NotSupportedException();
break;
}
return(sql);
}
public void BasicTest_Alias()
{
SelectStmtInfo stmtInfo = new SelectStmtInfo();
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userId"
}, HasAlias = true, Alias = "UID"
});
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userName"
}
});
stmtInfo.TableDescriptor = new TableDescriptor()
{
TableName = "me", TableReadType = TableReadType.NONE
};
IGenerator g = new DefaultSqlGenerator();
var sql = g.Generate(stmtInfo);
Assert.AreEqual("SELECT userId AS UID, userName FROM me", sql);
}
public void BasicTest_PREDICT_NO_WHERE_NO_ORDERBY()
{
SelectStmtInfo stmtInfo = new SelectStmtInfo();
stmtInfo.Column_PredictExists = true;
stmtInfo.Column_PredictWord = "DISTINCT";
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userId"
}
});
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userName"
}
});
stmtInfo.TableDescriptor = new TableDescriptor()
{
TableName = "me", TableReadType = TableReadType.NONE
};
IGenerator g = new DefaultSqlGenerator();
var sql = g.Generate(stmtInfo);
Assert.AreEqual("SELECT DISTINCT userId, userName FROM me", sql);
}
private static string Generate_PredictWord(SelectStmtInfo stmtInfo)
{
string predictWord = "";
if (stmtInfo.Column_PredictExists)
predictWord = stmtInfo.Column_PredictWord;
return predictWord;
}
private static string Generate_PredictWord(SelectStmtInfo stmtInfo)
{
string predictWord = "";
if (stmtInfo.Column_PredictExists)
{
predictWord = stmtInfo.Column_PredictWord;
}
return(predictWord);
}
private static string Generate_OrderBySql(SelectStmtInfo stmtInfo)
{
StringBuilder sb = new StringBuilder();
if (stmtInfo.OrderBy != null && stmtInfo.OrderBy.Count > 0)
{
sb.Append(" ORDER BY ");
foreach(var orderByTerm in stmtInfo.OrderBy)
sb.AppendFormat("{0} {1}, ", orderByTerm.Expression, orderByTerm.Direction.ToString());
}
return sb.ToString().TrimEnd(", ".ToCharArray());
}
public void BasicTest_2_Condition()
{
SelectStmtInfo stmtInfo = new SelectStmtInfo();
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userId"
}
});
stmtInfo.Columns.Add(new Column()
{
Expression = new ColumnExpression(ExpressionType.ColumnName)
{
ColumnName = "userName"
}
});
stmtInfo.TableDescriptor = new TableDescriptor()
{
TableName = "me", TableReadType = TableReadType.NONE
};
stmtInfo.WhereCondition = new WhereCondition(WhereConditionNodeType.Statement);
stmtInfo.WhereCondition.Statement_Operator = "AND";
stmtInfo.WhereCondition.Statement_LeftNode = new WhereCondition(WhereConditionNodeType.Condition);
stmtInfo.WhereCondition.Statement_LeftNode.Condition_LeftExpression = "userId";
stmtInfo.WhereCondition.Statement_LeftNode.Condition_Operator = ">";
stmtInfo.WhereCondition.Statement_LeftNode.Condition_RightExpression = "5";
stmtInfo.WhereCondition.Statement_RightNode = new WhereCondition(WhereConditionNodeType.Condition);
stmtInfo.WhereCondition.Statement_RightNode.Condition_LeftExpression = "sex";
stmtInfo.WhereCondition.Statement_RightNode.Condition_Operator = "=";
stmtInfo.WhereCondition.Statement_RightNode.Condition_RightExpression = "1";
stmtInfo.OrderBy.Add(new OrderByCondition()
{
Expression = "userId", Direction = OrderByDirection.ASC
});
IGenerator g = new DefaultSqlGenerator();
var sql = g.Generate(stmtInfo);
Assert.AreEqual("SELECT userId, userName FROM me WHERE (userId>5) AND (sex=1) ORDER BY userId ASC", sql);
}
private static string Generate_OrderBySql(SelectStmtInfo stmtInfo)
{
StringBuilder sb = new StringBuilder();
if (stmtInfo.OrderBy != null && stmtInfo.OrderBy.Count > 0)
{
sb.Append(" ORDER BY ");
foreach (var orderByTerm in stmtInfo.OrderBy)
{
sb.AppendFormat("{0} {1}, ", orderByTerm.Expression, orderByTerm.Direction.ToString());
}
}
return(sb.ToString().TrimEnd(", ".ToCharArray()));
}
private static string Generate_WhereSql(SelectStmtInfo stmtInfo)
{
if (stmtInfo.WhereCondition == null)
{
return(string.Empty);
}
var conditions = _Generate_WhereSql(stmtInfo.WhereCondition);
if (string.IsNullOrEmpty(conditions))
{
return(string.Empty);
}
return(string.Format(" WHERE {0}", conditions));
}
private static string Generate_ColumnSql(SelectStmtInfo stmtInfo)
{
StringBuilder sb = new StringBuilder();
foreach(var column in stmtInfo.Columns)
{
string cur_sql = string.Empty;
cur_sql = column.Expression.ColumnName;
if(column.HasAlias)
cur_sql+=string.Format(" AS {0}", column.Alias);
sb.AppendFormat("{0}, ", cur_sql);
}
return sb.ToString().TrimEnd(", ".ToCharArray());
}
private static string Generate_ColumnSql(SelectStmtInfo stmtInfo)
{
StringBuilder sb = new StringBuilder();
foreach (var column in stmtInfo.Columns)
{
string cur_sql = string.Empty;
cur_sql = column.Expression.ColumnName;
if (column.HasAlias)
{
cur_sql += string.Format(" AS {0}", column.Alias);
}
sb.AppendFormat("{0}, ", cur_sql);
}
return(sb.ToString().TrimEnd(", ".ToCharArray()));
}
private static string Generate_TableName(SelectStmtInfo stmtInfo)
{
string sql=stmtInfo.TableDescriptor.TableName;
switch (stmtInfo.TableDescriptor.TableReadType)
{
case TableReadType.NONE:
//Nothing to do
break;
case TableReadType.NOLOCK:
sql += "(NOLOCK)";
break;
case TableReadType.READPAST:
sql += "(READPAST)";
break;
default:
throw new NotSupportedException();
break;
}
return sql;
}
public string Generate(SelectStmtInfo stmtInfo)
{
string predictWord = "";
string columnSql = "";
string tableName = "";
string orderBySql = "";
string whereSql = "";
string finalSql = "";
predictWord = Generate_PredictWord(stmtInfo);
tableName = Generate_TableName(stmtInfo);
columnSql = Generate_ColumnSql(stmtInfo);
orderBySql = Generate_OrderBySql(stmtInfo);
whereSql = Generate_WhereSql(stmtInfo);
if(string.IsNullOrEmpty(predictWord))
finalSql = string.Format("SELECT {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql);
else
finalSql = string.Format("SELECT {0} {1} FROM {2}{3}{4}", predictWord, columnSql, tableName, whereSql, orderBySql);
return finalSql;
}
private static string Generate_WhereSql(SelectStmtInfo stmtInfo)
{
if (stmtInfo.WhereCondition == null)
return string.Empty;
var conditions=_Generate_WhereSql(stmtInfo.WhereCondition);
if(string.IsNullOrEmpty(conditions))
return string.Empty;
return string.Format(" WHERE {0}", conditions);
}
